#!/usr/bin/env python3
"""Explicit, idempotent migration for Dashboard V5 patient-action timestamps."""
from __future__ import annotations

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

from dashboard_v5.patient_action_schema import REQUIRED_PATIENT_ACTION_COLUMNS


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


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


def schema_status(connection: sqlite3.Connection) -> dict[str, list[str]]:
    status: dict[str, list[str]] = {}
    for table, expected in REQUIRED_PATIENT_ACTION_COLUMNS.items():
        exists = connection.execute(
            "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table,)
        ).fetchone()
        if exists is None:
            raise RuntimeError(f"required table missing: {table}")
        actual = {str(row[1]) for row in connection.execute(f'PRAGMA table_info("{table}")')}
        status[table] = [name for name, _kind in expected if name in actual]
    return status


def assert_schema(connection: sqlite3.Connection) -> None:
    status = schema_status(connection)
    missing = {
        table: [name for name, _kind in REQUIRED_PATIENT_ACTION_COLUMNS[table] if name not in names]
        for table, names in status.items()
    }
    missing = {table: names for table, names in missing.items() if names}
    if missing:
        raise RuntimeError("patient-action schema missing")


def apply_migration(connection: sqlite3.Connection) -> list[str]:
    status = schema_status(connection)
    changes: list[str] = []
    connection.execute("BEGIN IMMEDIATE")
    try:
        for table, expected in REQUIRED_PATIENT_ACTION_COLUMNS.items():
            actual = set(status[table])
            for name, kind in expected:
                if name in actual:
                    continue
                connection.execute(f'ALTER TABLE "{table}" ADD COLUMN "{name}" {kind}')
                changes.append(f"{table}.{name}")
        connection.commit()
    except Exception:
        connection.rollback()
        raise
    assert_schema(connection)
    return changes


def backup_database(source: Path, target: Path) -> None:
    target.parent.mkdir(parents=True, exist_ok=True)
    if target.exists():
        raise RuntimeError("backup target already exists")
    with connect(source) as source_connection, connect(target) as target_connection:
        check_database(source_connection)
        source_connection.backup(target_connection)
        check_database(target_connection)
    target.chmod(0o600)


def table_counts(connection: sqlite3.Connection) -> dict[str, int]:
    return {
        table: int(connection.execute(f'SELECT count(*) FROM "{table}"').fetchone()[0])
        for table in REQUIRED_PATIENT_ACTION_COLUMNS
    }


def restore_test(backup_path: Path) -> None:
    with tempfile.TemporaryDirectory(prefix="health-dashboard-restore-") as directory:
        restored = Path(directory) / "restored.sqlite3"
        source = sqlite3.connect(f"file:{backup_path.resolve()}?mode=ro", uri=True)
        target = sqlite3.connect(restored)
        try:
            source.backup(target)
            source_tables = [
                str(row[0])
                for row in source.execute(
                    "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
                )
            ]
            source_counts = {
                table: int(source.execute(f'SELECT count(*) FROM "{table}"').fetchone()[0])
                for table in source_tables
            }
        finally:
            target.close()
            source.close()
        with connect(restored) as restored_check:
            check_database(restored_check)
        restored_connection = sqlite3.connect(
            f"file:{restored.resolve()}?mode=ro", uri=True
        )
        try:
            restored_counts = {
                table: int(
                    restored_connection.execute(
                        f'SELECT count(*) FROM "{table}"'
                    ).fetchone()[0]
                )
                for table in source_tables
            }
        finally:
            restored_connection.close()
        if restored_counts != source_counts:
            raise RuntimeError("restore row-count mismatch")


def safe_migrate(database: Path, backup: Path) -> dict[str, object]:
    database = database.resolve()
    backup = backup.resolve()
    with connect(database) as connection:
        check_database(connection)
    backup_database(database, backup)
    with tempfile.TemporaryDirectory(prefix="health-patient-schema-copy-") as directory:
        copy_path = Path(directory) / "migration-copy.sqlite"
        shutil.copy2(backup, copy_path)
        with connect(copy_path) as copy_connection:
            check_database(copy_connection)
            first_copy_changes = apply_migration(copy_connection)
            second_copy_changes = apply_migration(copy_connection)
            check_database(copy_connection)
            if second_copy_changes:
                raise RuntimeError("migration is not idempotent")
    restore_test(backup)
    with connect(database) as connection:
        production_changes = apply_migration(connection)
        repeated_changes = apply_migration(connection)
        check_database(connection)
        if repeated_changes:
            raise RuntimeError("private migration is not idempotent")
    return {
        "backup": str(backup),
        "copy_changes": len(first_copy_changes),
        "production_changes": len(production_changes),
        "repeat_changes": 0,
        "integrity": "ok",
        "foreign_keys": "ok",
        "schema": "present",
        "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_database(connection)
            assert_schema(connection)
        print(json.dumps({"integrity": "ok", "foreign_keys": "ok", "schema": "present"}, sort_keys=True))
        return 0
    if args.backup is None:
        parser.error("--backup is required for migration")
    print(json.dumps(safe_migrate(args.db, args.backup), sort_keys=True))
    return 0


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