from flask import Flask
from flask_mysqldb import MySQL
from flask_cors import CORS
from app.config import Config
import logging
from werkzeug.security import generate_password_hash
from datetime import timedelta
import os
import uuid

mysql = MySQL()

def create_app():
    app = Flask(__name__,
           static_url_path='/static',
           static_folder='static')    
    # Configure CORS to allow requests from any origin
    CORS(app, resources={
        r"/api/*": {
            "origins": "*",
            "methods": ["GET", "POST", "OPTIONS"],
            "allow_headers": ["Content-Type"]
        }
    })
    
    app.config.from_object(Config)
    
    # Configure session to be permanent (expires at midnight)
    # We handle actual expiry time in the decorator using session['session_expires_at']
    app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(days=1)  # Max 1 day, actual expiry checked in decorator
    
    # Configure MySQL
    app.config['MYSQL_HOST'] = Config.MYSQL_HOST
    app.config['MYSQL_USER'] = Config.MYSQL_USER
    app.config['MYSQL_PASSWORD'] = Config.MYSQL_PASSWORD
    app.config['MYSQL_DB'] = Config.MYSQL_DB
    
    # Initialize MySQL
    mysql.init_app(app)
    
    # Only initialize tables if they don't exist
    with app.app_context():
        cursor = mysql.connection.cursor()
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        # Initialize company tables first (they are referenced by other tables)
        if not tables:  # Only initialize if no tables exist
            init_company_tables()
        ensure_admin_tables(cursor)
        ensure_company_payment_tables(cursor)
        ensure_call_campaign_tables(cursor)
        ensure_support_ticket_tables(cursor)
        mysql.connection.commit()
    
    # Register blueprints
    from app.routes.main import main_bp
    from app.routes.auth import auth_bp
    from app.routes.company import company_bp
    from app.routes.api import api_bp
    from app.routes.whatsapp import whatsapp_bp
    from app.routes.admin import admin_bp
    
    app.register_blueprint(main_bp)
    app.register_blueprint(auth_bp)
    app.register_blueprint(company_bp)
    app.register_blueprint(api_bp)
    app.register_blueprint(whatsapp_bp)
    app.register_blueprint(admin_bp)
    
    # Create tasks.py module and initialize scheduler
    from app.tasks import create_scheduler
    create_scheduler(app)
    
    return app

def column_exists(cursor, table_name, column_name):
    """Check if a column exists in a table."""
    try:
        cursor.execute("""
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = %s 
            AND COLUMN_NAME = %s
        """, (table_name, column_name))
        return cursor.fetchone()[0] > 0
    except Exception:
        return False

def init_db():
    try:
        cursor = mysql.connection.cursor()
        # ... existing table creation code ...
        mysql.connection.commit()
    except Exception as e:
        print(f"Database initialization error: {str(e)}")

def init_company_tables():
    try:
        cursor = mysql.connection.cursor()
        
        # First create the companies table (parent)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS companies (
                id VARCHAR(36) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                password VARCHAR(255) NOT NULL,
                description TEXT,
                logo_url VARCHAR(255),
                website VARCHAR(255),
                instructions TEXT,
                email VARCHAR(255) UNIQUE NOT NULL,
                status VARCHAR(20) DEFAULT 'pending',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                verification_code VARCHAR(4) NULL,
                verification_code_expires_at DATETIME NULL,
                is_verified BOOLEAN DEFAULT FALSE,
                password_reset_code VARCHAR(6) NULL,
                password_reset_expires_at DATETIME NULL,
                welcome_message TEXT
            )
        """)
        
        # Then create the dependent tables with proper foreign key constraints
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS company_faqs (
                id VARCHAR(36) PRIMARY KEY,
                company_id VARCHAR(36) NOT NULL,
                question TEXT NOT NULL,
                answer TEXT NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS chat_messages (
                id VARCHAR(36) PRIMARY KEY,
                company_id VARCHAR(36) NOT NULL,
                role VARCHAR(10) NOT NULL,
                content TEXT NOT NULL,
                session_id VARCHAR(100) NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS website_pages (
                id VARCHAR(36) PRIMARY KEY,
                company_id VARCHAR(36) NOT NULL,
                url VARCHAR(512) NOT NULL,
                page_title VARCHAR(255),
                page_content TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
                UNIQUE KEY unique_url (company_id, url)
            )
        """)

        # Add meetings table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS meetings (
                id VARCHAR(36) PRIMARY KEY,
                company_id VARCHAR(36) NOT NULL,
                customer_email VARCHAR(255) NOT NULL,
                meeting_date DATE NOT NULL,
                meeting_time TIME NOT NULL,
                calendar_link TEXT,
                status VARCHAR(50) DEFAULT 'scheduled',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
            )
        """)
        
        # Add missing columns to existing companies table if they don't exist
        try:
            if not column_exists(cursor, 'companies', 'verification_code'):
                cursor.execute("ALTER TABLE companies ADD COLUMN verification_code VARCHAR(4) NULL")
            if not column_exists(cursor, 'companies', 'verification_code_expires_at'):
                cursor.execute("ALTER TABLE companies ADD COLUMN verification_code_expires_at DATETIME NULL")
            if not column_exists(cursor, 'companies', 'is_verified'):
                cursor.execute("ALTER TABLE companies ADD COLUMN is_verified BOOLEAN DEFAULT FALSE")
            if not column_exists(cursor, 'companies', 'password_reset_code'):
                cursor.execute("ALTER TABLE companies ADD COLUMN password_reset_code VARCHAR(6) NULL")
            if not column_exists(cursor, 'companies', 'password_reset_expires_at'):
                cursor.execute("ALTER TABLE companies ADD COLUMN password_reset_expires_at DATETIME NULL")
            if not column_exists(cursor, 'companies', 'welcome_message'):
                cursor.execute("ALTER TABLE companies ADD COLUMN welcome_message TEXT")
            if not column_exists(cursor, 'companies', 'status'):
                cursor.execute("ALTER TABLE companies ADD COLUMN status VARCHAR(20) DEFAULT 'pending'")
            cursor.execute("""
                UPDATE companies
                SET status = 'approved'
                WHERE status IS NULL OR status = ''
            """)
        except Exception as alter_error:
            print(f"Column addition error (may already exist): {str(alter_error)}")
        
        mysql.connection.commit()
        print("Tables created successfully")
        
    except Exception as e:
        print(f"Company tables initialization error: {str(e)}")
        # Don't raise the error, just log it
        pass

def ensure_admin_tables(cursor):
    try:
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS admins (
                id VARCHAR(36) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                last_login_at DATETIME NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        """)

        admin_email = os.getenv('SUPERADMIN_EMAIL')
        admin_password = os.getenv('SUPERADMIN_PASSWORD')
        admin_name = os.getenv('SUPERADMIN_NAME', 'Super Admin')

        if admin_email and admin_password:
            cursor.execute("SELECT id FROM admins WHERE email = %s", (admin_email,))
            existing_admin = cursor.fetchone()
            if not existing_admin:
                hashed_password = generate_password_hash(admin_password)
                cursor.execute(
                    """
                    INSERT INTO admins (id, name, email, password)
                    VALUES (%s, %s, %s, %s)
                    """,
                    (str(uuid.uuid4()), admin_name, admin_email, hashed_password)
                )
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS admin_settings (
                id INT PRIMARY KEY,
                file_size_limit INT NOT NULL DEFAULT 25,
                mail_mailer VARCHAR(32) NULL,
                mail_host VARCHAR(255) NULL,
                mail_port INT NULL,
                mail_username VARCHAR(255) NULL,
                mail_password VARCHAR(255) NULL,
                mail_encryption VARCHAR(16) NULL,
                mail_from_address VARCHAR(255) NULL,
                mail_from_name VARCHAR(255) NULL,
                admin_email VARCHAR(255) NULL,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        """)
        try:
            if not column_exists(cursor, 'admin_settings', 'mail_mailer'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_mailer VARCHAR(32) NULL")
            if not column_exists(cursor, 'admin_settings', 'mail_host'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_host VARCHAR(255) NULL")
            if not column_exists(cursor, 'admin_settings', 'mail_port'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_port INT NULL")
            if not column_exists(cursor, 'admin_settings', 'mail_username'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_username VARCHAR(255) NULL")
            if not column_exists(cursor, 'admin_settings', 'mail_password'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_password VARCHAR(255) NULL")
            if not column_exists(cursor, 'admin_settings', 'mail_encryption'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_encryption VARCHAR(16) NULL")
            if not column_exists(cursor, 'admin_settings', 'mail_from_address'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_from_address VARCHAR(255) NULL")
            if not column_exists(cursor, 'admin_settings', 'mail_from_name'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN mail_from_name VARCHAR(255) NULL")
            if not column_exists(cursor, 'admin_settings', 'admin_email'):
                cursor.execute("ALTER TABLE admin_settings ADD COLUMN admin_email VARCHAR(255) NULL")
        except Exception as alter_error:
            print(f"Admin settings column addition error (may already exist): {str(alter_error)}")
        cursor.execute("SELECT COUNT(*) FROM admin_settings")
        if cursor.fetchone()[0] == 0:
            cursor.execute("""
                INSERT INTO admin_settings (id, file_size_limit)
                VALUES (1, 25)
            """)
    except Exception as e:
        print(f"Admin table initialization error: {str(e)}")


def ensure_company_payment_tables(cursor):
    try:
        # First ensure companies table exists (it should, but check to avoid FK errors)
        cursor.execute("SHOW TABLES LIKE 'companies'")
        companies_exists = cursor.fetchone()
        
        if not companies_exists:
            print("Warning: companies table does not exist. Creating company_package_payments without foreign key.")
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS company_package_payments (
                    id VARCHAR(36) PRIMARY KEY,
                    company_id VARCHAR(36) NOT NULL,
                    package_id INT NOT NULL,
                    stripe_session_id VARCHAR(255) UNIQUE,
                    stripe_payment_intent_id VARCHAR(255),
                    amount DECIMAL(10,2) NOT NULL,
                    currency VARCHAR(10) NOT NULL,
                    status VARCHAR(32) NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    completed_at DATETIME NULL,
                    started_at DATETIME NULL,
                    expires_at DATETIME NULL
                )
            """)
        else:
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS company_package_payments (
                    id VARCHAR(36) PRIMARY KEY,
                    company_id VARCHAR(36) NOT NULL,
                    package_id INT NOT NULL,
                    stripe_session_id VARCHAR(255) UNIQUE,
                    stripe_payment_intent_id VARCHAR(255),
                    amount DECIMAL(10,2) NOT NULL,
                    currency VARCHAR(10) NOT NULL,
                    status VARCHAR(32) NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    completed_at DATETIME NULL,
                    started_at DATETIME NULL,
                    expires_at DATETIME NULL,
                    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
                    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE RESTRICT
                )
            """)
        
        # Add columns if they don't exist
        if not column_exists(cursor, 'company_package_payments', 'started_at'):
            cursor.execute("ALTER TABLE company_package_payments ADD COLUMN started_at DATETIME NULL")
        if not column_exists(cursor, 'company_package_payments', 'expires_at'):
            cursor.execute("ALTER TABLE company_package_payments ADD COLUMN expires_at DATETIME NULL")
        
        # Add duration_seconds to campaign_calls if table exists
        cursor.execute("SHOW TABLES LIKE 'campaign_calls'")
        if cursor.fetchone():
            if not column_exists(cursor, 'campaign_calls', 'duration_seconds'):
                cursor.execute("ALTER TABLE campaign_calls ADD COLUMN duration_seconds INT NULL")
    except Exception as e:
        print(f"Company package payments table initialization error: {str(e)}")


def ensure_call_campaign_tables(cursor):
    """
    Ensure supporting columns and tables exist for call campaign analytics.
    """
    try:
        cursor.execute("SHOW TABLES LIKE 'campaign_calls'")
        has_campaign_calls = cursor.fetchone()
        if has_campaign_calls:
            try:
                if not column_exists(cursor, 'campaign_calls', 'outcome'):
                    cursor.execute("ALTER TABLE campaign_calls ADD COLUMN outcome VARCHAR(32) NULL")
                if not column_exists(cursor, 'campaign_calls', 'outcome_details'):
                    cursor.execute("ALTER TABLE campaign_calls ADD COLUMN outcome_details TEXT NULL")
            except Exception as alter_error:
                print(f"Campaign calls column addition error (may already exist): {str(alter_error)}")

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS bookings (
                id VARCHAR(36) PRIMARY KEY,
                company_id VARCHAR(36) NOT NULL,
                contact_id VARCHAR(64) NOT NULL,
                campaign_call_id VARCHAR(64) NOT NULL,
                outcome VARCHAR(32) NOT NULL,
                customer_email VARCHAR(255) NULL,
                meeting_date_raw VARCHAR(255) NULL,
                meeting_time_raw VARCHAR(255) NULL,
                additional_details TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_campaign_call (campaign_call_id)
            )
        """)
    except Exception as e:
        print(f"Call campaign table initialization error: {str(e)}")


def ensure_support_ticket_tables(cursor):
    """
    Ensure support ticket tables exist.
    """
    try:
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS support_tickets (
                id VARCHAR(36) PRIMARY KEY,
                company_id VARCHAR(36) NOT NULL,
                title VARCHAR(255) NOT NULL,
                reason TEXT NOT NULL,
                priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
                status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS support_ticket_messages (
                id VARCHAR(36) PRIMARY KEY,
                ticket_id VARCHAR(36) NOT NULL,
                sender_type ENUM('company', 'admin') NOT NULL,
                sender_id VARCHAR(36) NOT NULL,
                message TEXT NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS support_ticket_read_status (
                id VARCHAR(36) PRIMARY KEY,
                ticket_id VARCHAR(36) NOT NULL,
                reader_type ENUM('company', 'admin') NOT NULL,
                reader_id VARCHAR(36) NOT NULL,
                last_read_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
                UNIQUE KEY unique_reader (ticket_id, reader_type, reader_id)
            )
        """)
    except Exception as e:
        print(f"Support ticket table initialization error: {str(e)}")