#!/usr/bin/env python3
"""Copy-first, idempotent Sprint 6I-A migration with restore proof."""
from __future__ import annotations

import argparse
import json
import os
from pathlib import Path
import sqlite3
import tempfile

from dashboard_v5.sprint6i_a_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_counts = row_counts(connection)
    apply_schema(connection)
    connection.commit()
    first = fingerprint(connection)
    after_first_counts = row_counts(connection)
    apply_schema(connection)
    connection.commit()
    if first != fingerprint(connection) or after_first_counts != row_counts(connection):
        raise RuntimeError("migration not idempotent")
    for table, count in before_counts.items():
        if table not in {"dashboard_schema_migrations"} and row_counts(connection).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-6ia-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-6ia-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())