"""
Populate `size` column in pg__dashboard_canifa__ultra_descriptions
with filtered size table based on size_scale.
"""

import json
import os
import sqlite3
import sys

sys.stdout.reconfigure(encoding='utf-8')

# Auto-detect DB path relative to this script
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))

# Try common DB filenames
possible_db_names = [
    "canifa_ai_dump.sqlite",
    "canifa_local.sqlite",
    "canifa_dump.sqlite"
]

DB_PATH = None
for db_name in possible_db_names:
    candidate = os.path.join(SCRIPT_DIR, db_name)
    if os.path.exists(candidate):
        DB_PATH = candidate
        print(f"✅ Found DB: {DB_PATH}")
        break

if not DB_PATH:
    # Default to first option with debug
    DB_PATH = os.path.join(SCRIPT_DIR, "canifa_ai_dump.sqlite")
    print(f"⚠️ DB not auto-found. Will try: {DB_PATH}")

def parse_size_table_from_markdown(markdown: str) -> dict:
    result = {}
    lines = markdown.strip().split('\n')
    for line in lines:
        line = line.strip()
        if line.startswith('|') and not line.startswith('|---'):
            parts = [p.strip() for p in line.split('|')]
            if len(parts) >= 4:
                size = parts[1]
                height = parts[2]
                weight = parts[3]
                result[size] = (height, weight)
    return result

def build_filtered_table(size_table: dict, available_sizes: list) -> str:
    if not size_table or not available_sizes:
        return ""
    lines = [
        "| Size | Chiều cao (cm) | Cân nặng (kg) |",
        "|------|----------------|---------------|"
    ]
    for size in available_sizes:
        size_upper = size.strip().upper()
        for table_size, (height, weight) in size_table.items():
            if table_size.upper() == size_upper:
                lines.append(f"| {size} | {height} | {weight} |")
                break
    return '\n'.join(lines)

def main():
    print(f"\n🔍 DB Path: {DB_PATH}")
    print(f"📁 Script dir: {SCRIPT_DIR}")
    print(f"📁 Current dir: {os.getcwd()}")
    print(f"❌ Exists: {os.path.exists(DB_PATH)}\n")

    if not os.path.exists(DB_PATH):
        print("❌ Database not found!")
        print("\nAvailable .sqlite files in script directory:")
        for f in os.listdir(SCRIPT_DIR):
            if f.endswith('.sqlite'):
                print(f"  - {f}")
        print("\n👉 Please edit DB_PATH in this script to point to the correct file.")
        return

    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    # Ensure column exists
    try:
        cur.execute("ALTER TABLE pg__dashboard_canifa__ultra_descriptions ADD COLUMN size TEXT")
        print("✅ Added 'size' column")
    except sqlite3.OperationalError as e:
        if "duplicate column" in str(e).lower():
            print("ℹ️ Column 'size' already exists - will skip creation")
        else:
            print(f"❌ Error adding column: {e}")
            return

    # Fetch rows to process
    cur.execute("""
        SELECT id, description_data, size_scale
        FROM pg__dashboard_canifa__ultra_descriptions
        WHERE description_data IS NOT NULL
          AND description_data != ''
          AND size_scale IS NOT NULL
          AND size_scale != ''
          AND (size IS NULL OR size = '')
    """)

    rows = cur.fetchall()
    total = len(rows)
    print(f"📊 Found {total} rows to process")

    if total == 0:
        # Check if any rows have data already
        cur.execute("SELECT COUNT(*) FROM pg__dashboard_canifa__ultra_descriptions")
        total_rows = cur.fetchone()[0]
        cur.execute("SELECT COUNT(*) FROM pg__dashboard_canifa__ultra_descriptions WHERE size IS NOT NULL")
        already_done = cur.fetchone()[0]
        print(f"✅ No rows need processing. Total: {total_rows}, Already filled: {already_done}")
        conn.close()
        return

    updated = 0
    errors = 0

    for row_id, desc_data_json, size_scale in rows:
        try:
            desc_data = json.loads(desc_data_json)
            huong_dan_size = desc_data.get('huong_dan_size', '')
            if not huong_dan_size:
                continue

            available_sizes = [s.strip() for s in size_scale.split('|') if s.strip()]
            if not available_sizes:
                continue

            size_table = parse_size_table_from_markdown(huong_dan_size)
            if not size_table:
                continue

            filtered_table = build_filtered_table(size_table, available_sizes)
            if filtered_table:
                cur.execute(
                    "UPDATE pg__dashboard_canifa__ultra_descriptions SET size = ? WHERE id = ?",
                    (filtered_table, row_id)
                )
                updated += 1
                if updated % 100 == 0:
                    print(f"  Progress: {updated}/{total}")
                    conn.commit()

        except json.JSONDecodeError as e:
            errors += 1
            print(f"  ⚠️ JSON parse error on row {row_id}: {e}")
        except Exception as e:
            errors += 1
            print(f"  ⚠️ Error on row {row_id}: {e}")

    conn.commit()

    # Verify
    cur.execute("SELECT COUNT(*) FROM pg__dashboard_canifa__ultra_descriptions WHERE size IS NOT NULL")
    count = cur.fetchone()[0]
    print(f"\n✅ Done! Updated {updated} rows. Total with size: {count}. Errors: {errors}")

    # Show sample
    cur.execute("""
        SELECT id, size_scale, size
        FROM pg__dashboard_canifa__ultra_descriptions
        WHERE size IS NOT NULL
        LIMIT 1
    """)
    sample = cur.fetchone()
    if sample:
        print("\n📄 Sample output:")
        print(f"  ID: {sample[0]}")
        print(f"  size_scale: {sample[1]}")
        print("  size (filtered):")
        print("  " + sample[2].replace('\n', '\n  '))

    conn.close()

if __name__ == "__main__":
    main()
