"""Original offline FDE lab. Python 3.11+, standard library, synthetic data only.

Runs real SQLite queries, indexes, backups and restores in temporary folders.
The business timeline is supplied scenario data, never a measured service SLO.
No network, cloud resources, model calls or production database connections.
"""
from contextlib import closing
import json
import sqlite3
import tempfile
import time
from pathlib import Path


def index_experiment():
    """Compare access paths while holding query, rows and result constant."""
    with closing(sqlite3.connect(":memory:")) as db:
        db.execute("CREATE TABLE tickets (id INTEGER PRIMARY KEY, tenant TEXT, status TEXT)")
        db.executemany("INSERT INTO tickets VALUES (?, ?, ?)", [
            (i, "north" if i % 2 else "south", "open" if i % 7 == 0 else "closed")
            for i in range(1, 2001)
        ])
        sql = "SELECT id FROM tickets WHERE tenant = ? AND status = ? ORDER BY id"
        params = ("north", "open")
        before = [r[0] for r in db.execute(sql, params)]
        before_plan = [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql, params)]
        db.execute("CREATE INDEX tickets_scope_status ON tickets (tenant, status)")
        after = [r[0] for r in db.execute(sql, params)]
        after_plan = [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql, params)]
        unscoped = [r[0] for r in db.execute("SELECT id FROM tickets WHERE status = 'open'")]
        return {"before_plan": before_plan, "after_plan": after_plan,
                "same_result": before == after, "scoped_count": len(after),
                "unscoped_count": len(unscoped),
                "index_grants_access": False,
                "claim": "Observed query plans, not a latency benchmark or identity-provider test."}


EVENTS = [("T-1", -1, "open"), ("T-2", 0, "open"),
          ("T-3", 3, "open"), ("T-4", 6, "open")]


def recovery_experiment(backup_minute=0, acceptance_minute=20, schema_version=1):
    """10:00 is minute zero; declared incident begins at 10:08.

    Backup time is a supplied, transaction-consistent recovery point in this
    sequential fixture. Real backup/restore I/O executes; no outage is induced.
    """
    if not isinstance(backup_minute, int) or not 0 <= backup_minute < 8:
        raise ValueError("backup_minute must be an integer from 0 through 7")
    if not isinstance(acceptance_minute, int) or acceptance_minute < 8:
        raise ValueError("acceptance_minute must be an integer at least 8")
    with tempfile.TemporaryDirectory(prefix="fde-recovery-") as folder:
        base = Path(folder)
        with closing(sqlite3.connect(base / "live.db")) as live:
            live.execute("CREATE TABLE tickets (id TEXT PRIMARY KEY, committed_minute INTEGER, status TEXT)")
            live.execute(f"PRAGMA user_version = {int(schema_version)}")
            live.executemany("INSERT INTO tickets VALUES (?, ?, ?)",
                             [row for row in EVENTS if row[1] <= backup_minute])
            live.commit()
            with closing(sqlite3.connect(base / "backup.db")) as backup:
                live.backup(backup)
            live.executemany("INSERT INTO tickets VALUES (?, ?, ?)",
                             [row for row in EVENTS if row[1] > backup_minute])
            live.commit()
            committed = [r[0] for r in live.execute("SELECT id FROM tickets ORDER BY id")]
        start = time.perf_counter()
        with closing(sqlite3.connect(base / "backup.db")) as backup, closing(sqlite3.connect(base / "restored.db")) as restored:
            backup.backup(restored)
            integrity = restored.execute("PRAGMA integrity_check").fetchone()[0]
            version = restored.execute("PRAGMA user_version").fetchone()[0]
            ids = [r[0] for r in restored.execute("SELECT id FROM tickets ORDER BY id")]
            # A deliberately small compatibility gate for this training app.
            compatible = integrity == "ok" and version == 1
            # Derived search data can be rebuilt, but only from recovered facts.
            restored.execute("CREATE TABLE search_copy AS SELECT id, status FROM tickets")
            rebuilt = restored.execute("SELECT COUNT(*) FROM search_copy").fetchone()[0]
        elapsed = time.perf_counter() - start
        return {"scenario": {"backup_minute": backup_minute, "incident_minute": 8,
                             "acceptance_minute": acceptance_minute if compatible else None,
                             "rpo_target_minutes": 5, "rto_target_minutes": 15},
                "recovered_ids": ids, "missing_ids": sorted(set(committed) - set(ids)),
                "integrity": integrity, "schema_version": version,
                "compatible": compatible, "rebuilt_search_rows": rebuilt,
                "recovery_point_gap_minutes": 8 - backup_minute,
                "scenario_rpo_met": 8 - backup_minute <= 5,
                "scenario_interruption_minutes": acceptance_minute - 8 if compatible else None,
                "scenario_rto_met": acceptance_minute - 8 <= 15 if compatible else None,
                "measured_local_restore_and_check_seconds": round(elapsed, 6),
                "claim": "Actual local restore; supplied business timeline. No failover or production RTO proof."}


def main():
    print(json.dumps({"index": index_experiment(), "recoveries": {
        "older_backup": recovery_experiment(),
        "fresher_backup": recovery_experiment(backup_minute=6),
        "slow_service_acceptance": recovery_experiment(backup_minute=6, acceptance_minute=26),
        "incompatible_schema": recovery_experiment(backup_minute=6, schema_version=2),
    }}, indent=2))


if __name__ == "__main__":
    main()