import os
import sqlite3
import sys

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
from common.constants import SQLITE_DB_PATH

DB_PATH = SQLITE_DB_PATH

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_product_name VARCHAR(255),
        match_gender VARCHAR(50),
        match_age VARCHAR(50),
        match_role VARCHAR(50) NOT NULL,
        score FLOAT NOT NULL,
        ai_reason TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    """
    
    print("Executing DROP and CREATE TABLE statement for pg__dashboard_canifa__ai_outfit_product_matches...")
    cursor.execute("DROP TABLE IF EXISTS 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()
