"""Offline SQLite replacement/cascade fixture. Python 3.9+, SQLite 3.24+.
No network, model calls or persistent files. Run with --test for regression tests.
"""
import sqlite3
import sys
import unittest

REPLACE = "INSERT OR REPLACE INTO jobs(id, status) VALUES (?, ?)"
UPSERT = """INSERT INTO jobs(id, status) VALUES (?, ?)
ON CONFLICT(id) DO UPDATE SET status=excluded.status"""


def database(action="CASCADE", recursive=False):
    if action not in ("CASCADE", "RESTRICT"):
        raise ValueError("unsupported fixture action")
    db = sqlite3.connect(":memory:", isolation_level=None)
    db.execute("PRAGMA foreign_keys=ON")
    if db.execute("PRAGMA foreign_keys").fetchone() != (1,):
        db.close()
        raise RuntimeError("foreign key enforcement unavailable")
    db.execute("PRAGMA recursive_triggers=" + ("ON" if recursive else "OFF"))
    db.execute("CREATE TABLE jobs(id TEXT PRIMARY KEY NOT NULL, status TEXT NOT NULL)")
    db.execute("CREATE TABLE receipts(id TEXT PRIMARY KEY NOT NULL, job_id TEXT NOT NULL "
               "REFERENCES jobs(id) ON DELETE " + action + ", evidence TEXT NOT NULL)")
    db.execute("INSERT INTO jobs VALUES ('job-1', 'running')")
    db.execute("INSERT INTO receipts VALUES ('receipt-1', 'job-1', 'tool-returned')")
    return db


def snapshot(db):
    return (db.execute("SELECT * FROM jobs ORDER BY id").fetchall(),
            db.execute("SELECT * FROM receipts ORDER BY id").fetchall())


class ReplacementTests(unittest.TestCase):
    def setUp(self):
        self.db = database()
        self.addCleanup(self.db.close)
        self.before = snapshot(self.db)

    def test_replace_cascades_with_recursive_triggers_off(self):
        self.db.execute(REPLACE, ("job-1", "done"))
        self.assertEqual(snapshot(self.db), ([("job-1", "done")], []))
        self.assertEqual(self.db.execute("PRAGMA foreign_key_check").fetchall(), [])

    def test_replace_cascades_with_recursive_triggers_on(self):
        self.db.execute("PRAGMA recursive_triggers=ON")
        self.db.execute(REPLACE, ("job-1", "done"))
        self.assertEqual(snapshot(self.db)[1], [])

    def test_upsert_preserves_receipt_bytes(self):
        self.db.execute(UPSERT, ("job-1", "done"))
        self.assertEqual(snapshot(self.db), ([("job-1", "done")], self.before[1]))

    def test_upsert_can_insert_new_job(self):
        self.db.execute(UPSERT, ("job-2", "queued"))
        self.assertEqual(snapshot(self.db)[0], [("job-1", "running"), ("job-2", "queued")])
        self.assertEqual(snapshot(self.db)[1], self.before[1])

    def test_replace_identical_parent_still_deletes_receipt(self):
        self.db.execute(REPLACE, ("job-1", "running"))
        self.assertEqual(snapshot(self.db)[0], self.before[0])
        self.assertEqual(snapshot(self.db)[1], [])

    def test_explicit_rollback_restores_cascade(self):
        self.db.execute("BEGIN")
        self.db.execute(REPLACE, ("job-1", "done"))
        self.assertEqual(snapshot(self.db)[1], [])
        self.db.execute("ROLLBACK")
        self.assertEqual(snapshot(self.db), self.before)

    def test_restrict_refuses_replace(self):
        db = database("RESTRICT")
        self.addCleanup(db.close)
        before = snapshot(db)
        with self.assertRaises(sqlite3.IntegrityError):
            db.execute(REPLACE, ("job-1", "done"))
        self.assertEqual(snapshot(db), before)
        db.execute(UPSERT, ("job-1", "done"))
        self.assertEqual(snapshot(db)[1], before[1])

    def test_repeated_upsert_keeps_receipt(self):
        for _ in range(3):
            self.db.execute(UPSERT, ("job-1", "done"))
        self.assertEqual(snapshot(self.db), ([("job-1", "done")], self.before[1]))


def demo():
    for label, statement in (("REPLACE", REPLACE), ("UPSERT", UPSERT)):
        db = database()
        try:
            db.execute(statement, ("job-1", "done"))
            jobs, receipts = snapshot(db)
            violations = db.execute("PRAGMA foreign_key_check").fetchall()
            print(f"{label}: jobs={len(jobs)}, receipts={len(receipts)}, fk_violations={len(violations)}")
        finally:
            db.close()


if __name__ == "__main__":
    if sqlite3.sqlite_version_info < (3, 24, 0):
        raise SystemExit("SQLite 3.24.0 or later is required")
    if sys.argv[1:] == ["--test"]:
        unittest.main(argv=[sys.argv[0]], verbosity=2)
    elif sys.argv[1:]:
        raise SystemExit("usage: agent-sqlite-replace.py [--test]")
    else:
        demo()
