import os
import re
import sqlite3

db_path = r"C:\canifa-idea\chatbot-canifa-feedback\backend\database\canifa_local.sqlite"
conn = sqlite3.connect(db_path)
cur = conn.cursor()

def translate_query(query: str) -> str:
    q = query
    q = re.sub(r'"dashboard_canifa"\."([a-zA-Z0-9_]+)"', r'pg__dashboard_canifa__\1', q)
    q = re.sub(r'"canifa_chat"\."([a-zA-Z0-9_]+)"', r'pg__canifa_chat__\1', q)
    q = re.sub(r'"public"\."([a-zA-Z0-9_]+)"', r'pg__public__\1', q)
    q = re.sub(r'([a-zA-Z0-9_]+\.)?`?magento_product_dimension_with_text_embedding`?', r'sr__test_db__magento_product_dimension_with_text_embedding', q)
    q = re.sub(r'TRUNCATE TABLE\s+(pg__[a-zA-Z0-9_]+)(\s+CASCADE)?;?', r'DELETE FROM \1;', q, flags=re.IGNORECASE)
    q = q.replace("::jsonb", "").replace("::uuid", "")
    return q

def ensure_tables():
    cur.execute("""
    CREATE TABLE IF NOT EXISTS pg__dashboard_canifa__ultra_descriptions (
        id INTEGER PRIMARY KEY,
        internal_ref_code TEXT,
        product_name TEXT,
        product_image_url TEXT,
        product_line TEXT,
        description_data TEXT,
        phase TEXT,
        created_at TEXT,
        updated_at TEXT,
        status TEXT,
        clean_description TEXT,
        tags TEXT,
        size_scale TEXT,
        magento_ref_code TEXT,
        base_ref_code TEXT,
        embedding TEXT,
        ai_matches TEXT
    )
    """)
    cur.execute("""
    CREATE TABLE IF NOT EXISTS sr__test_db__magento_product_dimension_with_text_embedding (
        internal_ref_code TEXT,
        magento_ref_code TEXT,
        product_color_code TEXT,
        product_name TEXT,
        color_code TEXT,
        master_color TEXT,
        product_color_name TEXT,
        season_sale TEXT,
        season TEXT,
        style TEXT,
        fitting TEXT,
        size_scale TEXT,
        graphic TEXT,
        pattern TEXT,
        weaving TEXT,
        shape_detail TEXT,
        form_neckline TEXT,
        form_sleeve TEXT,
        form_length TEXT,
        form_waistline TEXT,
        form_shoulderline TEXT,
        material TEXT,
        product_group TEXT,
        product_line_vn TEXT,
        unit_of_measure TEXT,
        sale_price REAL,
        original_price REAL,
        discount_amount REAL,
        material_group TEXT,
        product_line_en TEXT,
        age_by_product TEXT,
        gender_by_product TEXT,
        quantity_sold REAL,
        is_new_product INTEGER,
        product_image_url TEXT,
        description_text TEXT,
        product_image_url_thumbnail TEXT,
        product_web_url TEXT,
        product_web_material TEXT,
        description_text_full TEXT,
        tags TEXT,
        suggest_items TEXT,
        similar_items TEXT,
        vector TEXT
    )
    """)
    cur.execute("""
    CREATE TABLE IF NOT EXISTS pg__dashboard_canifa__chatbot_fashion_rules (
        id INTEGER PRIMARY KEY,
        anchor_category TEXT,
        occasion_tag TEXT,
        match_role TEXT,
        target_category TEXT,
        ai_reason TEXT,
        gender_target TEXT
    )
    """)
    conn.commit()

ensure_tables()

with open("migration_log.txt", "w", encoding="utf-8") as log:
    for folder in ['postgres', 'starrocks']:
        path = os.path.join(r"C:\canifa-idea\chatbot-canifa-feedback\backend\database", folder)
        if not os.path.exists(path): continue
        for file in os.listdir(path):
            if file.endswith('.sql'):
                filepath = os.path.join(path, file)
                log.write(f"Reading {file}...\n")
                log.flush()
                with open(filepath, 'r', encoding='utf-8') as f:
                    buffer = ""
                    in_statement = False
                    for line in f:
                        if line.startswith("INSERT") or line.startswith("TRUNCATE") or line.startswith("DELETE"):
                            in_statement = True
                            buffer = line
                        elif in_statement:
                            buffer += line
                        
                        if in_statement and line.strip().endswith(";"):
                            sql = translate_query(buffer)
                            try:
                                cur.execute(sql)
                            except Exception as e:
                                log.write(f"Error executing statement in {file}: {e}\n")
                                log.flush()
                            in_statement = False
                            buffer = ""

conn.commit()
conn.close()
with open("migration_log.txt", "a", encoding="utf-8") as log:
    log.write("Migration complete.\n")
