"""
Phase 1 — Step 1.2: built_melodies  (SQLite → Postgres)

Run on VPS:
    docker compose exec backend python -m migration.migrate_built_melodies
"""

import asyncio
import sys

from sqlalchemy.dialects.postgresql import insert as pg_insert

from melodies.orm import BuiltMelody
from migration.utils import open_sqlite, AsyncPgSession, parse_dt, parse_json, log_run, pg_count

SCRIPT = "migrate_built_melodies"


async def run() -> None:
    sqlite = await open_sqlite()
    rows = await sqlite.execute_fetchall("SELECT * FROM built_melodies")
    await sqlite.close()

    source_count = len(rows)
    print(f"Source (SQLite): {source_count} built_melodies rows")

    if source_count == 0:
        print("Nothing to migrate.")
        await log_run(SCRIPT, 0, 0, notes="source empty")
        return

    records = []
    for r in rows:
        records.append({
            "id":                  r["id"],
            "name":                r["name"],
            "pid":                 r["pid"],
            "steps":               parse_json(r["steps"], default=[]),
            "binary_path":         r["binary_path"],
            "progmem_code":        r["progmem_code"],
            "assigned_melody_ids": parse_json(r["assigned_melody_ids"], default=[]),
            "is_builtin":          bool(r["is_builtin"]) if r["is_builtin"] is not None else False,
            "created_at":          parse_dt(r["created_at"]),
            "updated_at":          parse_dt(r["updated_at"]),
        })

    async with AsyncPgSession() as session:
        async with session.begin():
            stmt = pg_insert(BuiltMelody).values(records)
            stmt = stmt.on_conflict_do_nothing(index_elements=["id"])
            await session.execute(stmt)
            dest_count = await pg_count(session, "built_melodies")

    if dest_count < source_count:
        msg = f"Count mismatch: source={source_count}  postgres={dest_count}"
        print(f"ERROR: {msg}", file=sys.stderr)
        await log_run(SCRIPT, source_count, dest_count, success=False, notes=msg)
        sys.exit(1)

    print(f"Postgres: {dest_count} rows ✓")
    await log_run(SCRIPT, source_count, dest_count)


if __name__ == "__main__":
    asyncio.run(run())
