"""Offline editorial example. No network, AI, credentials or production policy test."""
from datetime import date
import sqlite3

PRINCIPALS = {
    "alice": {"accountId": 17, "role": "analyst", "name": "Alice", "email": "alice@example.com"},
    "bob": {"accountId": 29, "role": "analyst", "name": "Bob", "email": "bob@example.com"},
}
PAGES = {"revenue": "paid revenue", "orders": "order count"}

def context_for(subject, selection):
    # A real application must resolve subject from its verified login session.
    principal = PRINCIPALS.get(subject)
    if principal is None:
        raise ValueError("unauthenticated")
    if set(selection) != {"page", "start", "end"}:
        raise ValueError("unexpected or missing selection field")
    if selection["page"] not in PAGES:
        raise ValueError("unsupported page")
    start, end = date.fromisoformat(selection["start"]), date.fromisoformat(selection["end"])
    if not 0 < (end - start).days <= 93:
        raise ValueError("invalid reporting window")
    account = principal["accountId"]
    if type(account) is not int or account <= 0:
        raise ValueError("invalid trusted account")
    return {
        "identity": dict(principal),
        "selection": {**selection, "metric": PAGES[selection["page"]]},
    }

def total(db, context):
    # Parameterized reference SQL, NOT an AskYourDatabase policy template.
    return db.execute("""SELECT COALESCE(SUM(amount_cents), 0)
      FROM orders WHERE account_id = ? AND status = 'paid'
      AND occurred_on >= ? AND occurred_on < ?""",
      (context["identity"]["accountId"], context["selection"]["start"], context["selection"]["end"])
    ).fetchone()[0]

def run():
    db = sqlite3.connect(":memory:")
    db.executescript("""CREATE TABLE orders (
      id TEXT, account_id INTEGER, occurred_on TEXT, status TEXT, amount_cents INTEGER);
      INSERT INTO orders VALUES
      ('A1',17,'2026-10-05','paid',10000),
      ('A2',17,'2026-10-15','paid',2500),
      ('A3',17,'2026-10-20','cancelled',9000),
      ('A4',17,'2026-11-01','paid',7000),
      ('B1',29,'2026-10-07','paid',99000);
    """)
    october = {"page": "revenue", "start": "2026-10-01", "end": "2026-11-01"}
    alice = context_for("alice", october)
    bob = context_for("bob", october)
    assert total(db, alice) == 12500
    assert total(db, bob) == 99000
    later = context_for("alice", {**october, "start": "2026-11-01", "end": "2026-12-01"})
    assert total(db, later) == 7000
    assert alice["identity"] == later["identity"]
    assert total(db, alice) == 12500  # separate request snapshot stays unchanged
    assert total(db, context_for("alice", {**october, "start": "2026-09-01", "end": "2026-10-01"})) == 0
    rejected = [
        ("unknown", october),
        ("alice", {**october, "accountId": 29}),
        ("alice", {**october, "role": "admin"}),
        ("alice", {**october, "databaseConfig": {"host": "untrusted"}}),
        ("alice", {**october, "page": "all tenants"}),
        ("alice", {**october, "start": "2026-11-01"}),
        ("alice", {**october, "start": "2026-12-01"}),
        ("alice", {**october, "start": "2026-02-30"}),
        ("alice", {**october, "start": "2026-01-01"}),
        ("alice", {"page": "revenue", "start": "2026-10-01"}),
    ]
    for subject, selection in rejected:
        try:
            context_for(subject, selection)
        except (ValueError, TypeError):
            continue
        raise AssertionError("invalid context accepted")
    db.close()
    print("PASS: 6 result/context checks; 10 rejected inputs. Alice October=12500 cents; Bob=99000; Alice November=7000.")

if __name__ == "__main__":
    run()
