"""Synthetic, local SQLite restoration exercise. No network or client data.

Run with Python 3: python restore_lab.py
This does not back up or validate an installed application.
"""
import hashlib
import json
from pathlib import Path
import platform
import sqlite3
from tempfile import TemporaryDirectory


def exercise():
    with TemporaryDirectory(prefix="dawap-restoration-fiction-") as directory:
        root = Path(directory)
        source = sqlite3.connect(root / "source.sqlite")
        source.execute("CREATE TABLE dossier (id INTEGER PRIMARY KEY, document TEXT NOT NULL)")
        source.executemany("INSERT INTO dossier VALUES (?, ?)", [(1, "doc-a.txt"), (2, "doc-b.txt")])
        source.commit()
        documents = {"doc-a.txt": "Document fictif A", "doc-b.txt": "Document fictif B"}
        snapshot = sqlite3.connect(root / "snapshot.sqlite")
        source.backup(snapshot)
        snapshot.close()
        saved_bytes = (root / "snapshot.sqlite").read_bytes()

        def restore(name):
            path = root / (name + ".sqlite")
            path.write_bytes(saved_bytes)
            return sqlite3.connect(path)

        def missing_documents(connection, files):
            return [name for (name,) in connection.execute("SELECT document FROM dossier ORDER BY id") if name not in files]

        observations = []
        complete = restore("complete")
        missing = missing_documents(complete, documents)
        assert missing == []
        observations.append({"case": "A", "scenario": "Base et documents présents", "observed": {"rows": complete.execute("SELECT COUNT(*) FROM dossier").fetchone()[0], "missingDocuments": missing}})

        db_only = restore("db-only")
        missing = missing_documents(db_only, {})
        assert missing == ["doc-a.txt", "doc-b.txt"]
        observations.append({"case": "B", "scenario": "Base seule", "observed": {"missingDocuments": missing}})

        partial = restore("partial")
        missing = missing_documents(partial, {"doc-a.txt": documents["doc-a.txt"]})
        assert missing == ["doc-b.txt"]
        observations.append({"case": "C", "scenario": "Un document absent", "observed": {"missingDocuments": missing}})

        incompatible = restore("incompatible")
        try:
            incompatible.execute("SELECT revision FROM dossier").fetchall()
            raise AssertionError("The synthetic old schema should not have revision.")
        except sqlite3.OperationalError as error:
            assert "revision" in str(error)
            observations.append({"case": "D", "scenario": "Code demandant une colonne absente", "observed": {"sqlError": str(error)}})

        source.execute("INSERT INTO dossier VALUES (?, ?)", (3, "doc-c.txt"))
        source.commit()
        older = restore("older")
        source_ids = [row[0] for row in source.execute("SELECT id FROM dossier ORDER BY id")]
        restored_ids = [row[0] for row in older.execute("SELECT id FROM dossier ORDER BY id")]
        assert source_ids == [1, 2, 3] and restored_ids == [1, 2]
        observations.append({"case": "E", "scenario": "Écriture après la sauvegarde", "observed": {"sourceIds": source_ids, "restoredIds": restored_ids}})

        accepted_by_fictional_partner = {"ORDER-FICTION-02"}
        restored_pending = {"ORDER-FICTION-01", "ORDER-FICTION-02"}
        to_reconcile = sorted(accepted_by_fictional_partner & restored_pending)
        assert to_reconcile == ["ORDER-FICTION-02"]
        observations.append({"case": "F", "scenario": "Journal partenaire entièrement fictif", "observed": {"referenceToReconcileBeforeReplay": to_reconcile, "networkRequests": 0}})

        for connection in [source, complete, db_only, partial, incompatible, older]:
            connection.close()
        return {"python": platform.python_version(), "sqlite": sqlite3.sqlite_version, "fixtureOnly": True, "networkRequests": 0, "snapshotSHA256": hashlib.sha256(saved_bytes).hexdigest(), "observations": observations}


if __name__ == "__main__":
    print(json.dumps(exercise(), ensure_ascii=False, indent=2))