"""An isolated SQLite backup/restore demonstration; never opens a production DB."""
import argparse
import datetime
import hashlib
import json
import platform
import shutil
import sqlite3
import sys
import uuid
from pathlib import Path


def digest_rows(connection):
    rows = connection.execute("SELECT id, note FROM notes ORDER BY id").fetchall()
    return len(rows), hashlib.sha256(json.dumps(rows, separators=(",", ":")).encode()).hexdigest()


def run(output):
    folder = Path(output) / ("sqlite-demo-" + uuid.uuid4().hex[:12])
    folder.mkdir(parents=True, exist_ok=False)
    source, backup, restored = (folder / name for name in ("source.sqlite", "backup.sqlite", "restored.sqlite"))
    with sqlite3.connect(source) as writer:
        journal = writer.execute("PRAGMA journal_mode=WAL").fetchone()[0]
        writer.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, note TEXT NOT NULL)")
        writer.executemany("INSERT INTO notes(note) VALUES (?)", [(f"Synthetic note {i}",) for i in range(1000)])
        writer.execute("INSERT INTO notes(note) VALUES ('Committed before backup')")
        writer.commit()
        baseline_count, baseline_digest = digest_rows(writer)
        with sqlite3.connect(backup) as destination:
            writer.backup(destination)
        writer.execute("INSERT INTO notes(note) VALUES ('Committed after backup')")
        writer.commit()
        live_count, _ = digest_rows(writer)
        shutil.copyfile(backup, restored)
        with sqlite3.connect(restored) as checked:
            integrity = checked.execute("PRAGMA integrity_check").fetchone()[0]
            restore_count, restore_digest = digest_rows(checked)
    result = {
        "experiment": "synthetic-sqlite-backup-and-restore",
        "at_utc": datetime.datetime.now(datetime.timezone.utc).isoformat(),
        "python": platform.python_version(), "sqlite": sqlite3.sqlite_version,
        "platform": platform.system(), "journal_mode": journal,
        "writer_connection_open_during_backup": True,
        "concurrent_writes_tested": False,
        "baseline_rows": baseline_count, "live_rows_after_later_commit": live_count,
        "restored_rows": restore_count, "integrity_check": integrity,
        "rows_match_baseline": restore_digest == baseline_digest,
        "baseline_sha256": baseline_digest, "restored_sha256": restore_digest,
        "limitation": "Synthetic local demonstration; no production restore or concurrent-writer stress test.",
    }
    assert integrity == "ok" and restore_digest == baseline_digest and restore_count == 1001 and live_count == 1002
    (folder / "result.json").write_text(json.dumps(result, indent=2), encoding="utf-8")
    print(json.dumps({"result": result, "output": str(folder)}, indent=2))
    return result


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--out", required=True, help="Parent folder for a new isolated synthetic database")
    run(parser.parse_args().out)