"""
migrate_sqlite_to_mysql.py — copies an existing SQLite installation (meeconic.db) into the MySQL database.

  python scripts/migrate_sqlite_to_mysql.py --sqlite /var/www/photos/backend/meeconic.db            # dry run
  python scripts/migrate_sqlite_to_mysql.py --sqlite /var/www/photos/backend/meeconic.db --apply    # copies

The target is whatever MYSQL_HOST / MYSQL_DB / ... point to (backend/.env). The schema is created first
(`init_all`), then every business table is copied with REPLACE, so the script is idempotent: running it twice,
or after the service already created a few rows, never duplicates anything.

Not copied on purpose: `rate_hits` and `leader_locks` (ephemeral), and the legacy single-school `settings`
table (installations that old were already migrated to multi-school by the previous version).

Children's names are NOT carried over (families.family_name, students.first_name / last_name are written
empty): the schools may not give us any, and the app no longer collects or shows them. The class stays.
Photos are files, not rows: copy photos/, incoming/ and thumbs/ as they are.
"""
import argparse
import sqlite3
import sys
from pathlib import Path

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

from core import config  # noqa: E402
from core.db import get_db, table_columns, table_exists  # noqa: E402

# Order matters only for readability: there are no foreign keys.
TABLES = ("schools", "school_settings", "photographers", "families", "students", "orders", "audit_logs", "app_kv")
BATCH = 500
# Personal names of children: blanked on the way in (see the module docstring).
BLANKED = {"families": {"family_name"}, "students": {"first_name", "last_name"}}


def main() -> int:
    ap = argparse.ArgumentParser()
    ap.add_argument("--sqlite", required=True, help="path to the old meeconic.db")
    ap.add_argument("--apply", action="store_true", help="actually write to MySQL (default: dry run)")
    args = ap.parse_args()

    src_path = Path(args.sqlite)
    if not src_path.is_file():
        print(f"SQLite file not found: {src_path}")
        return 1
    src = sqlite3.connect(f"file:{src_path}?mode=ro", uri=True)
    src.row_factory = sqlite3.Row
    src_tables = {r[0] for r in src.execute("SELECT name FROM sqlite_master WHERE type='table'")}

    print(f"Target: {config.MYSQL_USER}@{config.MYSQL_HOST}:{config.MYSQL_PORT}/{config.MYSQL_DB}")
    if args.apply:
        import main as app_main
        app_main.init_all()

    total = 0
    for table in TABLES:
        if table not in src_tables:
            print(f"  {table:<16} (absent from the SQLite file, skipped)")
            continue
        rows = src.execute(f"SELECT * FROM {table}").fetchall()
        if table == "app_kv":
            # Secrets generated by the old install must survive (media links stay valid); alert cooldowns and
            # organizer status are runtime state and are rebuilt.
            rows = [r for r in rows if r["key"].startswith(("secret:", "legacy_school_id", "sticky:"))]
        print(f"  {table:<16} {len(rows):>6} row(s)")
        total += len(rows)
        if not args.apply or not rows:
            continue

        with get_db() as conn:
            if not table_exists(conn, table):
                print(f"    ! table {table} does not exist in MySQL — skipped")
                continue
            dst_cols = set(table_columns(conn, table))
            cols = [c for c in rows[0].keys() if c in dst_cols]
            col_sql = ", ".join(f"`{c}`" for c in cols)
            marks = ",".join(["?"] * len(cols))
            for i in range(0, len(rows), BATCH):
                conn.executemany(
                    f"REPLACE INTO {table} ({col_sql}) VALUES ({marks})",
                    [tuple("" if c in BLANKED.get(table, ()) else r[c] for c in cols) for r in rows[i:i + BATCH]])
            conn.commit()

    print(f"{'Copied' if args.apply else 'Would copy'} {total} row(s).")
    if not args.apply:
        print("Dry run only. Re-run with --apply to write to MySQL.")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())