"""Caching 101: original, synthetic, offline SQLite exercise. Python 3.11+.

Only in-memory rows change. No model, credentials, network, package install or
file writes. Logical time is supplied by the scenario; it is not a benchmark.
"""
import json
import sqlite3

CHECK_CURRENT_ACCESS = True
CHECK_SOURCE_VERSION = True
TTL_SECONDS = 5
RENDERER_VERSION = "excerpt-v1"


def run():
    db = sqlite3.connect(":memory:")
    db.row_factory = sqlite3.Row
    db.executescript("""
        CREATE TABLE sources (tenant TEXT, document TEXT, version INTEGER, body TEXT,
                              PRIMARY KEY (tenant, document));
        CREATE TABLE grants (tenant TEXT, principal TEXT, document TEXT,
                             PRIMARY KEY (tenant, principal, document));
        CREATE TABLE cache (tenant TEXT, principal TEXT, document TEXT, renderer TEXT,
                            version INTEGER, expires INTEGER, body TEXT,
                            PRIMARY KEY (tenant, principal, document, renderer));
    """)
    db.executemany("INSERT INTO sources VALUES (?, ?, ?, ?)", [
        ("north", "guide-7", 1, "North fixture: use the blue checklist."),
        ("south", "guide-7", 1, "South fixture: use the amber checklist."),
    ])
    db.executemany("INSERT INTO grants VALUES (?, ?, ?)", [
        ("north", "operator-1", "guide-7"), ("south", "operator-1", "guide-7"),
    ])
    render_calls = 0
    cases = []

    def request(scenario, tenant, now, source_available=True):
        nonlocal render_calls
        # This is a trusted fixture, not identity supplied by a model or URL.
        principal, document = "operator-1", "guide-7"
        key = (tenant, principal, document, RENDERER_VERSION)
        result = {"scenario": scenario, "tenant": tenant, "logical_second": now,
                  "decision": "withheld", "cache": "not-read", "body": None,
                  "current_version": None, "returned_version": None,
                  "render_calls_this_request": 0}
        if not source_available:
            result["reason"] = "current-evidence-unavailable"
            cases.append(result)
            return

        # Metadata and permission are read on every request, including a hit.
        # This sequential exercise does not model concurrent revocation.
        current = db.execute("SELECT version FROM sources WHERE tenant=? AND document=?",
                             (tenant, document)).fetchone()
        allowed = db.execute("SELECT 1 FROM grants WHERE tenant=? AND principal=? AND document=?",
                             (tenant, principal, document)).fetchone()
        if not current:
            result["reason"] = "source-not-found"
            cases.append(result)
            return
        result["current_version"] = current["version"]
        if CHECK_CURRENT_ACCESS and not allowed:
            db.execute("DELETE FROM cache WHERE tenant=? AND principal=? AND document=? AND renderer=?", key)
            result["reason"] = "permission-revoked"
            cases.append(result)
            return

        cached = db.execute("SELECT * FROM cache WHERE tenant=? AND principal=? AND document=? AND renderer=?", key).fetchone()
        reason = "miss-empty"
        if cached:
            if CHECK_SOURCE_VERSION and cached["version"] != current["version"]:
                reason = "miss-source-changed"
            elif now >= cached["expires"]:
                reason = "miss-expired"
            else:
                result.update(decision="return-excerpt", cache="hit", reason="reusable-entry",
                              body=cached["body"], returned_version=cached["version"])
                cases.append(result)
                return

        source = db.execute("SELECT body FROM sources WHERE tenant=? AND document=?",
                            (tenant, document)).fetchone()
        # The rendered excerpt is deliberately deterministic: no inference.
        body = f"[{tenant}/{document}@{current['version']}] {source['body']}"
        render_calls += 1
        db.execute("INSERT OR REPLACE INTO cache VALUES (?, ?, ?, ?, ?, ?, ?)",
                   (*key, current["version"], now + TTL_SECONDS, body))
        result.update(decision="return-excerpt", cache=reason, reason="rendered-current-source",
                      body=body, returned_version=current["version"], render_calls_this_request=1)
        cases.append(result)

    try:
        request("cold", "north", 0)
        request("warm", "north", 1)
        request("other-tenant", "south", 2)
        db.execute("UPDATE sources SET version=2, body=? WHERE tenant='north' AND document='guide-7'",
                   ("North fixture: use the green checklist.",))
        request("source-changed", "north", 3)
        db.execute("DELETE FROM grants WHERE tenant='north'")
        request("permission-revoked", "north", 4)
        db.execute("INSERT INTO grants VALUES ('north', 'operator-1', 'guide-7')")
        # Restore a legitimate warm entry before the availability experiment.
        # Kept as an explicit setup request rather than an invisible cache write.
        request("access-restored", "north", 5)
        request("evidence-unavailable", "north", 6, source_available=False)
        request("expired", "north", 10)
        return {"lab": "caching-101", "clock": "supplied logical seconds; no sleeping",
                "render_calls": render_calls, "model_calls": 0,
                "cases": cases, "cache_rows_at_end": db.execute("SELECT count(*) FROM cache").fetchone()[0]}
    finally:
        db.close()


if __name__ == "__main__":
    print(json.dumps(run(), indent=2))