"""One-shot migration: copy all data from PostgreSQL to SQLite."""
import asyncio
import json
import os
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent))

async def migrate():
    # Import PG
    import asyncpg
    DATABASE_URL = os.environ.get("DATABASE_URL")
    if not DATABASE_URL:
        print("ERROR: DATABASE_URL not set in .env")
        return
    
    pg = await asyncpg.connect(DATABASE_URL)
    
    # Import SQLite
    from web.database_sqlite import init_db, get_db
    await init_db()
    db = await get_db()
    
    # --- Projects ---
    rows = await pg.fetch("SELECT * FROM projects ORDER BY created_at")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR IGNORE INTO projects (id, status, mode, platform, character,
                   story_preview, title, created_at, result_json)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (str(r["id"]), r["status"], r["mode"], r["platform"],
                 r["character"], r["story_preview"], r["title"],
                 r["created_at"].isoformat() if r["created_at"] else "",
                 r["result_json"] if isinstance(r["result_json"], str) else json.dumps(r["result_json"]) if r["result_json"] else None),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP project {r['id']}: {e}")
    await db.commit()
    print(f"✓ Projects: {count} migrated")
    
    # --- Images ---
    rows = await pg.fetch("SELECT * FROM images ORDER BY id")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR IGNORE INTO images (project_id, scene_number, image_prompt,
                   file_path, mime_type, file_size_bytes, status, created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (str(r["project_id"]), r["scene_number"], r.get("image_prompt", ""),
                 r.get("file_path", ""), r.get("mime_type", "image/png"),
                 r.get("file_size_bytes"), r.get("status", "generated"),
                 r["created_at"].isoformat() if r.get("created_at") else "",
                 r["updated_at"].isoformat() if r.get("updated_at") else ""),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP image: {e}")
    await db.commit()
    print(f"✓ Images: {count} migrated")
    
    # --- Videos ---
    rows = await pg.fetch("SELECT * FROM videos ORDER BY id")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR IGNORE INTO videos (project_id, scene_number, video_prompt,
                   file_path, mime_type, file_size_bytes, duration_seconds, status, created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (str(r["project_id"]), r["scene_number"], r.get("video_prompt", ""),
                 r.get("file_path", ""), r.get("mime_type", "video/mp4"),
                 r.get("file_size_bytes"), r.get("duration_seconds"),
                 r.get("status", "generated"),
                 r["created_at"].isoformat() if r.get("created_at") else "",
                 r["updated_at"].isoformat() if r.get("updated_at") else ""),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP video: {e}")
    await db.commit()
    print(f"✓ Videos: {count} migrated")
    
    # --- Audios ---
    try:
        rows = await pg.fetch("SELECT * FROM audios ORDER BY id")
        count = 0
        for r in rows:
            try:
                await db.execute(
                    """INSERT OR IGNORE INTO audios (project_id, scene_number, tts_text, tts_provider,
                       file_path, mime_type, file_size_bytes, duration_seconds, status, created_at, updated_at)
                       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                    (str(r["project_id"]), r["scene_number"], r.get("tts_text", ""),
                     r.get("tts_provider", "elevenlabs"), r.get("file_path", ""),
                     r.get("mime_type", "audio/wav"), r.get("file_size_bytes"),
                     r.get("duration_seconds"), r.get("status", "generated"),
                     r["created_at"].isoformat() if r.get("created_at") else "",
                     r["updated_at"].isoformat() if r.get("updated_at") else ""),
                )
                count += 1
            except Exception as e:
                print(f"  SKIP audio: {e}")
        await db.commit()
        print(f"✓ Audios: {count} migrated")
    except Exception:
        print("✓ Audios: table not found in PG, skipping")
    
    # --- Characters ---
    rows = await pg.fetch("SELECT * FROM characters ORDER BY name")
    count = 0
    for r in rows:
        try:
            sk = r.get("style_keywords")
            nk = r.get("negative_keywords")
            hp = r.get("hard_prohibitions")
            await db.execute(
                """INSERT OR REPLACE INTO characters
                   (id, name, physical_description, style_keywords, negative_keywords,
                    hard_prohibitions, camera_settings, lighting_settings, pose_rule,
                    environment_rule, clothing_rule, anatomical_highlight_rules,
                    consequence_philosophy, shot_variety_rules,
                    thumbnail_path, is_default, created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (str(r["id"]), r["name"], r.get("physical_description", ""),
                 json.dumps(sk) if isinstance(sk, list) else sk or "[]",
                 json.dumps(nk) if isinstance(nk, list) else nk or "[]",
                 json.dumps(hp) if isinstance(hp, list) else hp or "[]",
                 r.get("camera_settings", ""), r.get("lighting_settings", ""),
                 r.get("pose_rule", ""), r.get("environment_rule", ""),
                 r.get("clothing_rule", ""), r.get("anatomical_highlight_rules", ""),
                 r.get("consequence_philosophy", ""), r.get("shot_variety_rules", ""),
                 r.get("thumbnail_path", ""), 1 if r.get("is_default") else 0,
                 r["created_at"].isoformat() if r.get("created_at") else "",
                 r["updated_at"].isoformat() if r.get("updated_at") else ""),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP character {r.get('name')}: {e}")
    await db.commit()
    print(f"✓ Characters: {count} migrated")
    
    # --- Settings ---
    rows = await pg.fetch("SELECT * FROM settings")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR REPLACE INTO settings (key, value, description)
                   VALUES (?, ?, ?)""",
                (r["key"], r["value"], r.get("description")),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP setting {r['key']}: {e}")
    await db.commit()
    print(f"✓ Settings: {count} migrated")
    
    # --- Grok Accounts ---
    rows = await pg.fetch("SELECT * FROM grok_accounts ORDER BY id")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR IGNORE INTO grok_accounts
                   (label, sso_token, sso_rw_token, user_id, is_active,
                    last_used_at, usage_count, daily_usage_count, daily_usage_reset_at,
                    created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (r["label"], r["sso_token"], r["sso_rw_token"], r["user_id"],
                 1 if r["is_active"] else 0,
                 r["last_used_at"].isoformat() if r.get("last_used_at") else None,
                 r["usage_count"], r["daily_usage_count"],
                 str(r["daily_usage_reset_at"]) if r.get("daily_usage_reset_at") else None,
                 r["created_at"].isoformat() if r.get("created_at") else "",
                 r["updated_at"].isoformat() if r.get("updated_at") else ""),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP grok account: {e}")
    await db.commit()
    print(f"✓ Grok Accounts: {count} migrated")
    
    # --- World Styles ---
    rows = await pg.fetch("SELECT * FROM world_styles ORDER BY name")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR IGNORE INTO world_styles
                   (id, name, preset_type, background_rules, floor_rules,
                    secondary_characters_rules, lighting_rules, props_rules,
                    architecture_allowed, is_preset, created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (r["id"], r["name"], r.get("preset_type", "custom"),
                 r.get("background_rules", ""), r.get("floor_rules", ""),
                 r.get("secondary_characters_rules", ""), r.get("lighting_rules", ""),
                 r.get("props_rules", ""),
                 1 if r.get("architecture_allowed") else 0,
                 1 if r.get("is_preset") else 0,
                 r["created_at"].isoformat() if r.get("created_at") else "",
                 r["updated_at"].isoformat() if r.get("updated_at") else ""),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP world style: {e}")
    await db.commit()
    print(f"✓ World Styles: {count} migrated")
    
    # --- Clothing Styles ---
    rows = await pg.fetch("SELECT * FROM clothing_styles ORDER BY name")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR IGNORE INTO clothing_styles
                   (id, name, preset_type, clothing_rules, opacity_rules,
                    max_pieces, is_preset, created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (r["id"], r["name"], r.get("preset_type", "custom"),
                 r.get("clothing_rules", ""), r.get("opacity_rules", ""),
                 r.get("max_pieces", 2),
                 1 if r.get("is_preset") else 0,
                 r["created_at"].isoformat() if r.get("created_at") else "",
                 r["updated_at"].isoformat() if r.get("updated_at") else ""),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP clothing style: {e}")
    await db.commit()
    print(f"✓ Clothing Styles: {count} migrated")
    
    # --- Production Rules ---
    rows = await pg.fetch("SELECT * FROM production_rules ORDER BY name")
    count = 0
    for r in rows:
        try:
            await db.execute(
                """INSERT OR IGNORE INTO production_rules
                   (id, name, camera_settings, pose_rule, shot_intercalation_rules,
                    death_scene_rules, child_rules, zombie_rules, consequence_philosophy,
                    scale_rule, is_preset, created_at, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (r["id"], r["name"], r.get("camera_settings", ""),
                 r.get("pose_rule", ""), r.get("shot_intercalation_rules", ""),
                 r.get("death_scene_rules", ""), r.get("child_rules", ""),
                 r.get("zombie_rules", ""), r.get("consequence_philosophy", ""),
                 r.get("scale_rule", ""),
                 1 if r.get("is_preset") else 0,
                 r["created_at"].isoformat() if r.get("created_at") else "",
                 r["updated_at"].isoformat() if r.get("updated_at") else ""),
            )
            count += 1
        except Exception as e:
            print(f"  SKIP production rules: {e}")
    await db.commit()
    print(f"✓ Production Rules: {count} migrated")
    
    await pg.close()
    print("\n✅ Migration complete! All data copied from PostgreSQL to SQLite.")
    print(f"   SQLite DB: data/pipeline.db")


if __name__ == "__main__":
    from dotenv import load_dotenv
    load_dotenv()
    asyncio.run(migrate())