import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

def setup_mcq_table(drop_existing_table: bool = False):
    """
    Sets up the mcq_questions table with the final, comprehensive schema.
    """
    try:
        conn = psycopg2.connect(
            host=os.getenv("POSTGRES_HOST", "localhost"),
            port=os.getenv("POSTGRES_PORT", "5432"),
            user=os.getenv("POSTGRES_USER"),
            password=os.getenv("POSTGRES_PASSWORD"),
            dbname=os.getenv("POSTGRES_DB")
        )
        conn.autocommit = True
        
        with conn.cursor() as cur:
            if drop_existing_table:
                print("Dropping existing mcq_questions table...")
                cur.execute("DROP TABLE IF EXISTS mcq_questions CASCADE;")
                print("Table dropped.")

            print("Creating mcq_questions table with the NEW COMPREHENSIVE schema...")
            # --- THIS IS THE FULLY UPDATED TABLE SCHEMA ---
            cur.execute("""
                CREATE TABLE IF NOT EXISTS mcq_questions (
                    id SERIAL PRIMARY KEY,
                    curriculum TEXT,
                    grade TEXT NOT NULL,
                    subject TEXT NOT NULL,
                    unit TEXT NOT NULL,
                    concept TEXT NOT NULL,
                    question_text TEXT NOT NULL,
                    question_type TEXT,
                    difficulty_level INTEGER,
                    is_arabic BOOLEAN NOT NULL,
                    correct_answer TEXT NOT NULL,
                    wrong_answer_1 TEXT,
                    wrong_answer_2 TEXT,
                    wrong_answer_3 TEXT,
                    wrong_answer_4 TEXT,
                    question_image_url TEXT, -- Placeholder for MinIO URL
                    correct_image_url TEXT,   -- Placeholder for MinIO URL
                    wrong_image_url_1 TEXT,   -- Placeholder for MinIO URL
                    wrong_image_url_2 TEXT,   -- Placeholder for MinIO URL
                    wrong_image_url_3 TEXT,   -- Placeholder for MinIO URL
                    wrong_image_url_4 TEXT,   -- Placeholder for MinIO URL
                    hint TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );
            """)
            
            print("Creating indexes on mcq_questions table...")
            # --- UPDATED INDEX TO INCLUDE CURRICULUM ---
            cur.execute("""
                CREATE INDEX IF NOT EXISTS idx_mcq_topic 
                ON mcq_questions(curriculum, grade, is_arabic, subject, unit, concept);
            """)

            print("MCQ table setup complete.")
                
    except Exception as e:
        print(f"An error occurred during MCQ table setup: {e}")
    finally:
        if 'conn' in locals() and conn:
            conn.close()
            print("Database connection closed.")

if __name__ == "__main__":
    # To apply the new schema, run this script.
    # Set drop_existing_table=True to ensure a clean recreation.
    print("Setting up the new MCQ table structure...")
    setup_mcq_table(drop_existing_table=True)