#!/usr/bin/env python3
"""Copy-first, idempotent Sprint 6F-C migration with private restore proof."""

from __future__ import annotations

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

from dashboard_v5.sprint6f_c_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_%' 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 migrate_copy(source: Path, target: Path) -> None:
    src = sqlite3.connect(f"file:{source.resolve()}?mode=ro", uri=True)
    dst = connect(target)
    try:
        src.backup(dst)
    finally:
        src.close()
        dst.close()


def verify_idempotent(connection: sqlite3.Connection) -> None:
    apply_schema(connection)
    connection.commit()
    first = fingerprint(connection)
    apply_schema(connection)
    connection.commit()
    second = fingerprint(connection)
    if first != second:
        raise RuntimeError("migration not idempotent")
    assert_schema(connection)
    check(connection)


def restore_proof(backup: Path) -> None:
    with tempfile.TemporaryDirectory(prefix="health-6fc-restore-") as directory:
        restored = Path(directory) / "restored.sqlite3"
        source = sqlite3.connect(f"file:{backup.resolve()}?mode=ro", uri=True)
        expected = row_counts(source)
        target = connect(restored)
        try:
            source.backup(target)
        finally:
            source.close()
            target.close()
        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-6fc-copy-") as directory:
        candidate = Path(directory) / "candidate.sqlite3"
        migrate_copy(backup, candidate)
        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())