"""Synthetic, offline SQLite example: live rows, copied rows and a snapshot.
Run: python3 freshness-example.py
No network, AI provider, customer data or persistent database is used.
This models copies with SQL tables; it is not a vector-search or security test.
"""
import json
import sqlite3


def main():
    db = sqlite3.connect(":memory:")
    db.executescript("""
        CREATE TABLE source (id TEXT PRIMARY KEY, team TEXT, units INTEGER);
        INSERT INTO source VALUES
          ('A', 'North', 10), ('B', 'North', 20),
          ('C', 'North', 5), ('X', 'South', 99);
        CREATE TABLE indexed_copy AS SELECT * FROM source;
        CREATE TABLE historical_snapshot AS SELECT * FROM source;
    """)

    def north(table):
        # Table names below are fixed by this example, never user input.
        assert table in {"source", "indexed_copy", "historical_snapshot"}
        return db.execute(
            f"SELECT id, units FROM {table} WHERE team = ? ORDER BY id",
            ("North",),
        ).fetchall()

    original = [("A", 10), ("B", 20), ("C", 5)]
    assert north("source") == north("indexed_copy") == original
    assert sum(units for _, units in original) == 35

    # Fictional 09:05 changes, after the fictional 09:00 copy.
    db.executescript("""
        UPDATE source SET units = 12 WHERE id = 'A';
        DELETE FROM source WHERE id = 'B';
        INSERT INTO source VALUES ('D', 'North', 8);
    """)
    current = north("source")
    copied = north("indexed_copy")
    assert current == [("A", 12), ("C", 5), ("D", 8)]
    assert sum(units for _, units in current) == 25
    assert copied == original
    assert north("historical_snapshot") == original
    assert len(current) == len(copied) == 3  # Equal counts hide changed IDs.
    selected_copy_row = db.execute(
        "SELECT units FROM indexed_copy WHERE team = ? ORDER BY units DESC, id LIMIT 1",
        ("North",),
    ).fetchone()[0]
    assert selected_copy_row == 20 and selected_copy_row != 35

    # Illustrative full replacement, not an incremental indexing recipe.
    db.executescript("""
        DELETE FROM indexed_copy;
        INSERT INTO indexed_copy SELECT * FROM source;
    """)
    assert north("indexed_copy") == current
    assert north("historical_snapshot") == original
    print(json.dumps({
        "fictional_copy_time": "09:00", "fictional_source_time": "09:05",
        "copy_before_refresh": copied, "source_after_changes": current,
        "copy_total_before_refresh": 35, "source_total_after_changes": 25,
        "selected_one_row_subtotal": selected_copy_row,
        "copy_total_after_refresh": sum(n for _, n in north("indexed_copy")),
        "retained_snapshot_total": sum(n for _, n in north("historical_snapshot")),
        "checks_passed": 10,
        "scope": "offline synthetic SQLite only; no AI, vector search or permission test"
    }, indent=2))
    db.close()


if __name__ == "__main__":
    main()