#!/usr/bin/env python3
"""Safe additive Sprint 6F-B schema migration with backup and restore proof."""
from __future__ import annotations

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

from dashboard_v5.sprint6f_b_schema import (
    apply_pipeline_schema,
    apply_supplement_schema,
    assert_pipeline_schema,
    assert_supplement_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 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 restore_proof(backup: Path) -> None:
    with tempfile.TemporaryDirectory(prefix="health-6fb-restore-") as directory:
        restored = Path(directory) / "restored.sqlite3"
        source = sqlite3.connect(f"file:{backup.resolve()}?mode=ro", uri=True)
        target = connect(restored)
        try:
            source_counts = counts(source)
            source.backup(target)
        finally:
            target.close()
            source.close()
        with connect(restored) as check_connection:
            check(check_connection)
            if counts(check_connection) != source_counts:
                raise RuntimeError("restore row-count mismatch")


def safe_migrate(database: Path, backup: Path) -> dict[str, object]:
    with connect(database) as source:
        check(source)
    backup_database(database, backup)
    with tempfile.TemporaryDirectory(prefix="health-6fb-copy-") as directory:
        candidate = Path(directory) / "candidate.sqlite3"
        source = sqlite3.connect(f"file:{backup.resolve()}?mode=ro", uri=True)
        target = connect(candidate)
        try:
            source.backup(target)
        finally:
            target.close()
            source.close()
        with connect(candidate) as copy:
            first = apply_supplement_schema(copy) + apply_pipeline_schema(copy)
            second = apply_supplement_schema(copy) + apply_pipeline_schema(copy)
            check(copy)
            if second:
                raise RuntimeError("migration not idempotent")
    restore_proof(backup)
    with connect(database) as production:
        production_changes = apply_supplement_schema(production) + apply_pipeline_schema(production)
        repeated = apply_supplement_schema(production) + apply_pipeline_schema(production)
        check(production)
        if repeated:
            raise RuntimeError("production migration not idempotent")
    return {"copy_changes": first, "production_changes": production_changes, "repeat_changes": 0, "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:
            check(connection)
            assert_supplement_schema(connection)
            assert_pipeline_schema(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())