"""Disposable SQLite backup/restore demonstration. No production paths accepted."""

import json
import sqlite3
import shutil
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory


def main():
    with TemporaryDirectory(prefix="sqlite-backup-demo-") as directory:
        root = Path(directory)
        source = root / "source.db"
        backup = root / "backup.db"
        naive_copy = root / "naive-copy.db"
        restored = root / "restored.db"
        expected = [(1, "draft"), (2, "published"), (3, "review")]
        with closing(sqlite3.connect(source)) as live:
            mode = live.execute("PRAGMA journal_mode=WAL").fetchone()[0]
            live.execute("PRAGMA wal_autocheckpoint=0")
            live.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, status TEXT)")
            live.commit()
            live.execute("PRAGMA wal_checkpoint(TRUNCATE)")
            live.executemany("INSERT INTO notes VALUES (?, ?)", expected)
            live.commit()
            wal_present = Path(str(source) + "-wal").exists()
            shutil.copyfile(source, naive_copy)
            with closing(sqlite3.connect(naive_copy)) as naive:
                naive_rows = naive.execute("SELECT COUNT(*) FROM notes").fetchone()[0]
            with closing(sqlite3.connect(source.as_uri() + "?mode=ro", uri=True)) as reader:
                with closing(sqlite3.connect(backup)) as destination:
                    reader.backup(destination)
            live.execute("UPDATE notes SET status='changed' WHERE id=1")
            live.commit()
            with closing(sqlite3.connect(backup.as_uri() + "?mode=ro", uri=True)) as saved:
                with closing(sqlite3.connect(restored)) as destination:
                    saved.backup(destination)
            with closing(sqlite3.connect(restored.as_uri() + "?mode=ro", uri=True)) as check:
                integrity = check.execute("PRAGMA integrity_check").fetchall()
                rows = check.execute("SELECT id, status FROM notes ORDER BY id").fetchall()
            live_status = live.execute("SELECT status FROM notes WHERE id=1").fetchone()[0]
            if integrity != [("ok",)] or rows != expected or live_status != "changed" or naive_rows != 0:
                raise RuntimeError("Backup/restore verification failed")
            print(json.dumps({"sqlite_version": sqlite3.sqlite_version,
                              "journal_mode": mode,
                              "wal_present_at_backup": wal_present,
                              "main_file_only_copy_rows": naive_rows,
                              "restored_rows": len(rows),
                              "integrity_check": integrity[0][0],
                              "source_status_after_change": live_status,
                              "restored_status": rows[0][1],
                              "matches_backup_time_data": rows == expected,
                              "production_database_accessed": False}, indent=2))


if __name__ == "__main__":
    main()