"""One-shot copy of the accounts database from SQLite into PostgreSQL.

Run ONCE during the cutover, after ``alembic upgrade head`` has built the empty
Postgres schema. The copy goes through the mapped ORM tables, so SQLAlchemy's type
processors coerce the values automatically — CHAR(36) UUID strings → native
``uuid``, 0/1 → ``bool``, epoch floats stay floats, JSON stays JSON — which a raw
``pg_dump``/CSV path would need manual casts for.

Login tokens (``access_token``) are deliberately NOT copied: they're ephemeral, so
skipping them just makes everyone re-login once at cutover and avoids the tz/uuid
fiddliness. Integer-PK sequences are reset afterwards, or the first post-migration
insert would collide.

Usage:
    THERMOGRAPH_DATABASE_URL=postgresql+psycopg://thermograph:PW@127.0.0.1:5432/thermograph \\
      python migrate_accounts_to_pg.py --sqlite /opt/thermograph/data/accounts.sqlite
"""
import argparse
import os
import sys

from sqlalchemy import create_engine, insert, select, text

from accounts import models

# Parents before children (FK order); AccessToken is intentionally omitted.
TABLES = [
    models.User.__table__,
    models.Subscription.__table__,
    models.PushSubscription.__table__,
    models.PendingDigest.__table__,
    models.Notification.__table__,   # references user + subscription
]
# Integer surrogate-PK tables whose sequence must be advanced past the copied max.
SEQUENCE_TABLES = ["subscription", "push_subscription", "pending_digest", "notification"]


def _pg_sync_url() -> str:
    url = os.environ.get("THERMOGRAPH_DATABASE_URL", "").strip()
    if not url.startswith("postgresql"):
        sys.exit("THERMOGRAPH_DATABASE_URL must be a postgresql URL (the migration target)")
    return url.replace("+asyncpg", "+psycopg")


def migrate(sqlite_path: str) -> None:
    if not os.path.exists(sqlite_path):
        sys.exit(f"source SQLite file not found: {sqlite_path}")
    src = create_engine(f"sqlite:///{sqlite_path}", future=True)
    dst = create_engine(_pg_sync_url(), future=True)

    counts = {}
    with src.connect() as s, dst.begin() as d:
        for table in TABLES:
            rows = [dict(r._mapping) for r in s.execute(select(table))]
            if rows:
                d.execute(insert(table), rows)   # explicit PKs preserved (FKs stay intact)
            counts[table.name] = len(rows)
        # Advance each identity sequence past the copied rows.
        for name in SEQUENCE_TABLES:
            d.execute(text(
                f"SELECT setval(pg_get_serial_sequence('{name}', 'id'), "
                f"COALESCE((SELECT MAX(id) FROM {name}), 1), (SELECT COUNT(*) > 0 FROM {name}))"
            ))

    src.dispose()
    dst.dispose()
    print("Copied (access_token skipped — users re-login once):")
    for name, n in counts.items():
        print(f"  {name:18} {n}")
    print("Sequences reset for:", ", ".join(SEQUENCE_TABLES))


if __name__ == "__main__":
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--sqlite", default=os.environ.get("THERMOGRAPH_ACCOUNTS_DB")
                    or os.path.join(os.path.dirname(__file__), "..", "data", "accounts.sqlite"),
                    help="path to the source accounts.sqlite (default: data/accounts.sqlite)")
    migrate(os.path.abspath(ap.parse_args().sqlite))