from models.db import db_cursor


def initialize_database():
    """Create tables and apply non-destructive schema upgrades."""
    queries = [
        """
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL UNIQUE,
            phone VARCHAR(50),
            image_url VARCHAR(1024) NULL,
            s3_image_key VARCHAR(512) NOT NULL,
            s3_image_url VARCHAR(1024) NOT NULL,
            face_id VARCHAR(255) UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS event_images (
            id INT AUTO_INCREMENT PRIMARY KEY,
            original_s3_key VARCHAR(512) NOT NULL UNIQUE,
            processed_s3_key VARCHAR(512) NOT NULL UNIQUE,
            original_url VARCHAR(1024) NOT NULL,
            processed_url VARCHAR(1024) NOT NULL,
            uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS matches (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            photo_id INT NOT NULL,
            confidence DECIMAL(5, 2) NOT NULL,
            matched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_match (user_id, photo_id),
            CONSTRAINT fk_match_user
                FOREIGN KEY (user_id) REFERENCES users(id)
                ON DELETE CASCADE,
            CONSTRAINT fk_match_image
                FOREIGN KEY (photo_id) REFERENCES event_images(id)
                ON DELETE CASCADE
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS email_deliveries (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            event_image_id INT NOT NULL,
            sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_delivery (user_id, event_image_id),
            CONSTRAINT fk_delivery_user
                FOREIGN KEY (user_id) REFERENCES users(id)
                ON DELETE CASCADE,
            CONSTRAINT fk_delivery_image
                FOREIGN KEY (event_image_id) REFERENCES event_images(id)
                ON DELETE CASCADE
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS admin_assets (
            id INT AUTO_INCREMENT PRIMARY KEY,
            type VARCHAR(50) NOT NULL,
            image_path TEXT NOT NULL,
            uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS admin_users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS events (
            id INT AUTO_INCREMENT PRIMARY KEY,
            event_name VARCHAR(255) NOT NULL,
            event_subtitle VARCHAR(500),
            venue VARCHAR(255),
            start_date DATE,
            end_date DATE
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS day_events (
            id INT AUTO_INCREMENT PRIMARY KEY,
            day VARCHAR(50) NOT NULL,
            event_name VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """,
    ]

    with db_cursor() as (_, cursor):
        for query in queries:
            cursor.execute(query)

        cursor.execute("ALTER TABLE users MODIFY face_id VARCHAR(255) NULL")
        ensure_column(cursor, "users", "image_url", "VARCHAR(1024) NULL")
        ensure_unique_index(
            cursor, "users", "face_id", "ux_users_face_id",
            "ALTER TABLE users ADD UNIQUE INDEX ux_users_face_id (face_id)",
        )

        ensure_column(cursor, "event_images", "original_s3_key", "VARCHAR(512) NULL")
        ensure_column(cursor, "event_images", "processed_s3_key", "VARCHAR(512) NULL")
        ensure_column(cursor, "event_images", "original_url", "VARCHAR(1024) NULL")
        ensure_column(cursor, "event_images", "processed_url", "VARCHAR(1024) NULL")

        legacy_s3_key_exists = column_exists(cursor, "event_images", "s3_key")
        legacy_s3_url_exists = column_exists(cursor, "event_images", "s3_url")

        if legacy_s3_key_exists or legacy_s3_url_exists:
            update_clauses = []
            if legacy_s3_key_exists:
                update_clauses.append("original_s3_key = COALESCE(original_s3_key, s3_key)")
                update_clauses.append("processed_s3_key = COALESCE(processed_s3_key, s3_key)")
            if legacy_s3_url_exists:
                update_clauses.append("original_url = COALESCE(original_url, s3_url)")
                update_clauses.append("processed_url = COALESCE(processed_url, s3_url)")
            cursor.execute(f"UPDATE event_images SET {', '.join(update_clauses)}")

        cursor.execute("""
            ALTER TABLE event_images
            MODIFY original_s3_key VARCHAR(512) NOT NULL,
            MODIFY processed_s3_key VARCHAR(512) NOT NULL,
            MODIFY original_url VARCHAR(1024) NOT NULL,
            MODIFY processed_url VARCHAR(1024) NOT NULL
        """)

        if legacy_s3_key_exists:
            cursor.execute("ALTER TABLE event_images MODIFY s3_key VARCHAR(512) NULL")
        if legacy_s3_url_exists:
            cursor.execute("ALTER TABLE event_images MODIFY s3_url VARCHAR(1024) NULL")

        ensure_index(
            cursor, "event_images", "idx_event_images_original_s3_key",
            "CREATE UNIQUE INDEX idx_event_images_original_s3_key ON event_images (original_s3_key)",
        )
        ensure_index(
            cursor, "event_images", "idx_event_images_processed_s3_key",
            "CREATE UNIQUE INDEX idx_event_images_processed_s3_key ON event_images (processed_s3_key)",
        )

        ensure_column(cursor, "event_images", "day_event_id", "INT NULL")

        if column_exists(cursor, "event_images", "day_event_id") and _table_exists(cursor, "day_events"):
            cursor.execute("""
                SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'event_images'
                  AND CONSTRAINT_NAME = 'fk_event_images_day_event'
                LIMIT 1
            """)
            if cursor.fetchone() is None:
                try:
                    cursor.execute("""
                        ALTER TABLE event_images
                        ADD CONSTRAINT fk_event_images_day_event
                        FOREIGN KEY (day_event_id) REFERENCES day_events(id)
                        ON DELETE SET NULL
                    """)
                except Exception:
                    pass


def ensure_column(cursor, table_name, column_name, column_definition):
    if not column_exists(cursor, table_name, column_name):
        cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_definition}")


def column_exists(cursor, table_name, column_name):
    cursor.execute(
        "SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s LIMIT 1",
        (table_name, column_name),
    )
    return cursor.fetchone() is not None


def _table_exists(cursor, table_name):
    cursor.execute(
        "SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s LIMIT 1",
        (table_name,),
    )
    return cursor.fetchone() is not None


def ensure_index(cursor, table_name, index_name, create_sql):
    cursor.execute(
        "SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s LIMIT 1",
        (table_name, index_name),
    )
    if cursor.fetchone() is None:
        cursor.execute(create_sql)


def ensure_unique_index(cursor, table_name, column_name, preferred_index_name, create_sql):
    cursor.execute(
        "SELECT INDEX_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s AND NON_UNIQUE = 0 LIMIT 1",
        (table_name, column_name),
    )
    if cursor.fetchone() is None:
        ensure_index(cursor, table_name, preferred_index_name, create_sql)