#!/usr/bin/env python3
"""Copy-first, idempotent Sprint 6I-B media migration with restore proof."""
from __future__ import annotations
import argparse, json, os, sqlite3, tempfile
from pathlib import Path
from dashboard_v5.sprint6i_b_schema import apply_schema, assert_schema


def connect(path: Path) -> sqlite3.Connection:
    connection=sqlite3.connect(path); connection.execute("PRAGMA foreign_keys=ON"); return connection


def check(connection: sqlite3.Connection) -> None:
    if connection.execute("PRAGMA integrity_check").fetchall() != [("ok",)]: raise RuntimeError("integrity_check failed")
    if connection.execute("PRAGMA foreign_key_check").fetchall(): raise RuntimeError("foreign_key_check failed")


def fingerprint(connection: sqlite3.Connection) -> list[tuple[str,str]]:
    return [(str(row[0]),str(row[1] or "")) for row in connection.execute("SELECT name,sql FROM sqlite_master WHERE type IN ('table','index') ORDER BY type,name")]


def row_counts(connection: sqlite3.Connection) -> dict[str,int]:
    names=[str(row[0]) for row in connection.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND sql NOT LIKE 'CREATE VIRTUAL TABLE%' ORDER BY name")]
    return {name:int(connection.execute(f'SELECT count(*) FROM "{name}"').fetchone()[0]) for name in names}


def backup_database(source: Path,target: Path) -> None:
    if target.exists(): raise RuntimeError("backup target exists")
    target.parent.mkdir(parents=True,exist_ok=True,mode=0o700)
    descriptor=os.open(target,os.O_WRONLY|os.O_CREAT|os.O_EXCL|os.O_NOFOLLOW,0o600); os.close(descriptor)
    with connect(source) as src,connect(target) as dst: check(src); src.backup(dst); check(dst)
    os.chmod(target,0o600)


def verify_idempotent(connection: sqlite3.Connection) -> None:
    before=row_counts(connection); apply_schema(connection); connection.commit(); first=fingerprint(connection); after=row_counts(connection)
    apply_schema(connection); connection.commit()
    if first != fingerprint(connection) or after != row_counts(connection): raise RuntimeError("migration not idempotent")
    current=row_counts(connection)
    for table,count in before.items():
        if table != "dashboard_schema_migrations" and current.get(table) != count: raise RuntimeError("legacy row count changed")
    assert_schema(connection); check(connection)


def restore_proof(backup: Path) -> None:
    with tempfile.TemporaryDirectory(prefix="health-6ib-restore-") as directory:
        restored=Path(directory)/"restored.sqlite3"
        with connect(backup) as src,connect(restored) as dst: expected=row_counts(src); src.backup(dst)
        with connect(restored) as checked:
            check(checked)
            if row_counts(checked) != expected: raise RuntimeError("restore row-count mismatch")


def safe_migrate(database: Path,backup: Path) -> dict[str,str]:
    with connect(database) as source: check(source)
    backup_database(database,backup)
    with tempfile.TemporaryDirectory(prefix="health-6ib-copy-") as directory:
        candidate=Path(directory)/"candidate.sqlite3"
        with connect(backup) as src,connect(candidate) as dst: src.backup(dst)
        with connect(candidate) as copy: verify_idempotent(copy)
    restore_proof(backup)
    with connect(database) as production: verify_idempotent(production)
    return {"copy_migration":"ok","production_migration":"ok","idempotency":"ok","integrity":"ok","foreign_keys":"ok","restore_test":"ok"}


def main() -> int:
    parser=argparse.ArgumentParser(); parser.add_argument("--db",required=True,type=Path); parser.add_argument("--backup",type=Path); parser.add_argument("--verify-only",action="store_true"); args=parser.parse_args()
    if args.verify_only:
        with connect(args.db) as connection: assert_schema(connection); check(connection)
        print(json.dumps({"schema":"present","integrity":"ok","foreign_keys":"ok"},sort_keys=True)); return 0
    if args.backup is None: parser.error("--backup is required")
    print(json.dumps(safe_migrate(args.db.resolve(),args.backup.resolve()),sort_keys=True)); return 0

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