import mysql.connector

def normalize_themes():
    conn = mysql.connector.connect(
        host='3.110.46.79',
        port=3306,
        user='phpmyadmin',
        password='Shikhar@123',
        database='quamble'
    )
    cursor = conn.cursor()

    # Step 1: Fetch all existing themes and associated tables
    cursor.execute("SELECT theme, theme_bank, theme_quiz_table FROM themes")
    rows = cursor.fetchall()

    for theme, bank_table, quiz_table in rows:
        theme_lc = theme.lower() if theme else None
        bank_table_lc = bank_table.lower() if bank_table else None
        quiz_table_lc = quiz_table.lower() if quiz_table else None

        # Step 2: Rename the tables if they exist and their lowercase versions are different
        if bank_table != bank_table_lc:
            try:
                cursor.execute(f"RENAME TABLE `{bank_table}` TO `{bank_table_lc}`")
                print(f"Renamed {bank_table} → {bank_table_lc}")
            except mysql.connector.Error as e:
                print(f"Failed to rename {bank_table}: {e}")

        if quiz_table != quiz_table_lc:
            try:
                cursor.execute(f"RENAME TABLE `{quiz_table}` TO `{quiz_table_lc}`")
                print(f"Renamed {quiz_table} → {quiz_table_lc}")
            except mysql.connector.Error as e:
                print(f"Failed to rename {quiz_table}: {e}")

        # Step 3: Update the themes table
        try:
            cursor.execute("""
                UPDATE themes
                SET theme = %s,
                    theme_bank = %s,
                    theme_quiz_table = %s
                WHERE theme = %s
            """, (theme_lc, bank_table_lc, quiz_table_lc, theme))
            print(f"Updated theme '{theme}' → '{theme_lc}'")
        except mysql.connector.Error as e:
            print(f"Failed to update theme {theme}: {e}")

    conn.commit()
    cursor.close()
    conn.close()
    print("✅ All theme names and tables normalized to lowercase.")

# normalize_themes()

CRITICAL_TABLES = {'themes', 'users', 'quiz', 'quiz_response', 'feedbacks'}

def clean_up_extra_theme_tables():
    conn = mysql.connector.connect(
        host='3.110.46.79',
        port=3306,
        user='phpmyadmin',
        password='Shikhar@123',
        database='quamble'
    )
    cursor = conn.cursor()

    # Step 1: Get valid lowercase bank and quiz table names
    cursor = conn.cursor(dictionary=True)

    print("🔄 Fetching all themes...")
    cursor.execute("SELECT * FROM themes")
    rows = cursor.fetchall()

    seen_themes = {}
    themes_to_delete = []
    tables_to_drop = []

    for row in rows:
        original_theme = row['theme']
        theme_lc = original_theme.lower()
        bank_lc = row['theme_bank'].lower() if row['theme_bank'] else None
        quiz_lc = row['theme_quiz_table'].lower() if row['theme_quiz_table'] else None

        # If theme is already seen, mark this one for deletion
        if theme_lc in seen_themes:
            themes_to_delete.append(original_theme)
            if row['theme_bank'].lower() != seen_themes[theme_lc]['theme_bank'].lower():
                tables_to_drop.append(row['theme_bank'])
            if row['theme_quiz_table'].lower() != seen_themes[theme_lc]['theme_quiz_table'].lower():
                tables_to_drop.append(row['theme_quiz_table'])
        else:
            seen_themes[theme_lc] = {
                'theme': theme_lc,
                'theme_bank': bank_lc,
                'theme_quiz_table': quiz_lc
            }

            # Update theme row to lowercase
            cursor.execute("""
                UPDATE themes
                SET theme = %s, theme_bank = %s, theme_quiz_table = %s
                WHERE theme = %s
            """, (theme_lc, bank_lc, quiz_lc, original_theme))

    # Delete duplicate theme rows
    print("🗑 Deleting duplicate theme rows...")
    for theme in themes_to_delete:
        cursor.execute("DELETE FROM themes WHERE theme = %s", (theme,))

    # Drop extra tables
    print("🧹 Dropping redundant tables...")
    dropped = set()
    for table in tables_to_drop:
        table_lc = table.lower()
        if table_lc not in dropped:
            try:
                cursor.execute(f"DROP TABLE IF EXISTS `{table}`")
                print(f"✅ Dropped table: {table}")
                dropped.add(table_lc)
            except mysql.connector.Error as e:
                print(f"❌ Error dropping table {table}: {e}")

    conn.commit()
    cursor.close()
    conn.close()
    print("🎉 Normalization complete. Duplicates removed, tables cleaned.")

# --- Run it ---
# clean_up_extra_theme_tables()

def normalize_and_create_tables():
    conn = mysql.connector.connect(
        host='3.110.46.79',
        port=3306,
        user='phpmyadmin',
        password='Shikhar@123',
        database='quamble'
    )
    cursor = conn.cursor(dictionary=True)

    # Step 1: Fetch all themes
    cursor.execute("SELECT * FROM themes")
    rows = cursor.fetchall()

    seen = {}
    to_delete = []
    for row in rows:
        theme_lc = row['theme'].lower()
        bank_lc = row['theme_bank'].lower() if row['theme_bank'] else None
        quiz_lc = row['theme_quiz_table'].lower() if row['theme_quiz_table'] else None

        # Check if duplicate
        if theme_lc in seen:
            to_delete.append(row['theme'])
        else:
            seen[theme_lc] = {
                'theme': theme_lc,
                'theme_bank': bank_lc,
                'theme_quiz_table': quiz_lc
            }

            # Update this row to lowercase
            cursor.execute("""
                UPDATE themes
                SET theme = %s, theme_bank = %s, theme_quiz_table = %s
                WHERE theme = %s
            """, (theme_lc, bank_lc, quiz_lc, row['theme']))

    # Step 2: Delete duplicates
    for theme in to_delete:
        cursor.execute("DELETE FROM themes WHERE theme = %s", (theme,))

    # Step 3: Get all existing tables
    cursor.execute("SHOW TABLES")
    existing_tables = {list(row.values())[0].lower() for row in cursor.fetchall()}

    # Step 4: Get the list of required tables
    cursor.execute("SELECT theme_bank, theme_quiz_table FROM themes")
    required_tables = set()
    for row in cursor.fetchall():
        if row['theme_bank']:
            required_tables.add(row['theme_bank'].lower())
        if row['theme_quiz_table']:
            required_tables.add(row['theme_quiz_table'].lower())

    # Step 5: Drop redundant _bank and theme_ tables
    for table in existing_tables:
        if (table.endswith('_bank') or table.startswith('theme_')) and table not in required_tables:
            if table not in {'themes', 'quiz', 'quiz_response', 'users', 'feedbacks'}:
                print(f"Dropping table: {table}")
                cursor.execute(f"DROP TABLE IF EXISTS `{table}`")

    # Step 6: Create missing _bank tables
    bank_table_schema = """
        CREATE TABLE `{}` (
            ques_id INT PRIMARY KEY AUTO_INCREMENT,
            user_id_creator VARCHAR(64),
            question_by_llm MEDIUMTEXT,
            question_hash VARCHAR(640),
            correct_option_llm MEDIUMTEXT,
            question_by_master MEDIUMTEXT,
            correct_option_master MEDIUMTEXT,
            Difficulty VARCHAR(64),
            is_used VARCHAR(64),
            Issue VARCHAR(6400)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """

    theme_table_schema = """
        CREATE TABLE `{}` (
            quiz_id INT PRIMARY KEY AUTO_INCREMENT,
            user_id_creator VARCHAR(64),
            questions_by_llm MEDIUMTEXT,
            correct_options_llm MEDIUMTEXT,
            questions_by_master MEDIUMTEXT,
            correct_options_master MEDIUMTEXT,
            num_questions INT,
            user_id_attempt MEDIUMTEXT,
            user_responses MEDIUMTEXT
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """

    for table in required_tables:
        if table not in existing_tables:
            if table.endswith('_bank'):
                print(f"Creating missing _bank table: {table}")
                cursor.execute(bank_table_schema.format(table))
            elif table.startswith('theme_'):
                print(f"Creating missing theme_ table: {table}")
                cursor.execute(theme_table_schema.format(table))

    conn.commit()
    cursor.close()
    conn.close()
    print("✅ Normalization and table creation complete.")

# Run the function
normalize_and_create_tables()