"""Original Data 101 lab: real local files and SQLite, entirely fictional data.

Python 3.11+ standard library. Each case owns and removes a temporary directory.
No cloud, network, model, embeddings or identity provider is used.
"""
import argparse
from contextlib import closing
import hashlib
import json
from pathlib import Path
import sqlite3
import tempfile

TICKET_ID = "T-104"
TRUSTED_SCOPE = "training"  # Supplied application context, not verified identity.
QUERY = "review"
CASES = ("stale-copy", "current-copy", "record-update", "record-rollback",
         "access-revoked", "source-deleted", "modified-copy")
CURRENT = {"source_id": "P-17", "revision": 2, "scope": "training",
           "text": "Revision 2: attach the review note to the ticket."}
OLD = {**CURRENT, "revision": 1,
       "text": "Revision 1: place the review note in the shared folder."}


def save_source(path, source):
    path.write_text(json.dumps(source), encoding="utf-8")


def load_source(path):
    return json.loads(path.read_text(encoding="utf-8")) if path.exists() else None


def fingerprint(text):
    return hashlib.sha256(text.encode("utf-8")).hexdigest()


def refresh(db, source):
    # A full-document teaching copy, not a vector index or ingestion service.
    db.execute("INSERT OR REPLACE INTO search_entries VALUES (?, ?, ?, ?)",
               (source["source_id"], source["revision"], source["scope"], source["text"]))


def update_ticket(db, interrupt=False):
    db.execute("BEGIN")
    try:
        changed = db.execute("UPDATE tickets SET status = ? WHERE ticket_id = ? AND scope = ?",
                             ("resolved", TICKET_ID, TRUSTED_SCOPE))
        if changed.rowcount != 1:
            raise RuntimeError("no permitted ticket to update")
        if interrupt:
            raise RuntimeError("supplied failure before the event insert")
        db.execute("INSERT INTO ticket_events (ticket_id, action) VALUES (?, ?)",
                   (TICKET_ID, "resolved"))
        db.execute("COMMIT")
        return "committed"
    except RuntimeError:
        db.execute("ROLLBACK")
        return "rolled-back"


def inspect(entry, source):
    # Check the current source as well as the derived copy's scope.
    if source is None:
        return "missing-source"
    if source["scope"] != TRUSTED_SCOPE:
        return "denied-access"
    if source["source_id"] != entry["source_id"]:
        return "source-mismatch"
    if source["revision"] != entry["revision"]:
        return "stale-revision"
    if source["text"] != entry["excerpt"]:
        return "content-mismatch"
    return "current-source"


def run_case(name):
    with tempfile.TemporaryDirectory(prefix="fde-data-101-") as directory:
        path = Path(directory) / "P-17.json"
        save_source(path, CURRENT)
        with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
            db.row_factory = sqlite3.Row
            db.execute("PRAGMA foreign_keys = ON")
            db.executescript("""
                CREATE TABLE tickets (ticket_id TEXT PRIMARY KEY, scope TEXT NOT NULL,
                    status TEXT NOT NULL, procedure_id TEXT NOT NULL);
                CREATE TABLE ticket_events (event_id INTEGER PRIMARY KEY,
                    ticket_id TEXT NOT NULL REFERENCES tickets(ticket_id), action TEXT NOT NULL);
                CREATE TABLE search_entries (source_id TEXT PRIMARY KEY, revision INTEGER NOT NULL,
                    scope TEXT NOT NULL, excerpt TEXT NOT NULL);
            """)
            db.execute("INSERT INTO tickets VALUES (?, ?, ?, ?)",
                       ("T-104", "training", "open", "P-17"))
            refresh(db, OLD)
            if name != "stale-copy":
                refresh(db, load_source(path))
            transaction = "not-requested"
            if name in ("record-update", "record-rollback"):
                transaction = update_ticket(db, interrupt=name == "record-rollback")
            elif name == "access-revoked":
                save_source(path, {**CURRENT, "scope": "restricted"})
            elif name == "source-deleted":
                path.unlink()  # Only this case's own temporary synthetic object.
            elif name == "modified-copy":
                db.execute("UPDATE search_entries SET excerpt = ? WHERE source_id = ?",
                           ("Revision 2: discard the review note.", "P-17"))

            row = db.execute("SELECT ticket_id, status, procedure_id FROM tickets "
                             "WHERE ticket_id = ? AND scope = ?",
                             (TICKET_ID, TRUSTED_SCOPE)).fetchone()
            entries = [] if row is None else db.execute(
                "SELECT source_id, revision, excerpt FROM search_entries "
                "WHERE source_id = ? AND scope = ? AND excerpt LIKE ?",
                (row["procedure_id"], TRUSTED_SCOPE, f"%{QUERY}%")).fetchall()
            source = load_source(path)
            reason = ("ticket-not-visible" if row is None else "no-search-match")
            context = []
            if entries:
                reason = inspect(entries[0], source)
                if reason == "current-source":
                    context = [dict(entries[0])]
            return {
                "case": name, "ticket": dict(row) if row is not None else None,
                "transaction": transaction,
                "ticket_events": db.execute("SELECT COUNT(*) FROM ticket_events").fetchone()[0],
                "source_exists": source is not None,
                "source_revision": source["revision"] if source else None,
                "source_text_unchanged": source is not None and fingerprint(source["text"]) == fingerprint(CURRENT["text"]),
                "index_rows": db.execute("SELECT COUNT(*) FROM search_entries").fetchone()[0],
                "matching_candidates": len(entries),
                "candidate_revision": entries[0]["revision"] if entries else None,
                "evidence_reason": reason, "context": context, "model_calls": 0,
            }


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("case", choices=("compare", *CASES), nargs="?", default="compare")
    args = parser.parse_args()
    names = CASES if args.case == "compare" else (args.case,)
    print(json.dumps({"synthetic": True,
                      "execution": "Real local JSON file reads and SQLite queries; modeled cloud storage and identity",
                      "results": [run_case(name) for name in names]}, indent=2))


if __name__ == "__main__":
    main()