import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(__file__), '123.db')

def migrate():
    print(f"Connecting to database at {DB_PATH}...")
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Create the table
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS pg__dashboard_canifa__ai_outfit_product_matches (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        rule_id INTEGER NOT NULL,
        anchor_product_code VARCHAR(255) NOT NULL,
        match_product_code VARCHAR(255) NOT NULL,
        match_role VARCHAR(50) NOT NULL,
        score FLOAT NOT NULL,
        ai_reason TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    """
    
    print("Executing CREATE TABLE statement for pg__dashboard_canifa__ai_outfit_product_matches...")
    cursor.execute(create_table_sql)
    
    # Create indexes for fast lookup
    print("Creating indexes for fast lookup...")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_anchor_product ON pg__dashboard_canifa__ai_outfit_product_matches(anchor_product_code);")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_rule_id ON pg__dashboard_canifa__ai_outfit_product_matches(rule_id);")

    conn.commit()
    print("Migration successful! Table pg__dashboard_canifa__ai_outfit_product_matches created.")
    conn.close()

if __name__ == "__main__":
    migrate()
