#!/usr/bin/env python3
"""Offline transaction fixture. Python 3.9+. No model calls or external writes."""
import argparse
import sqlite3
import tempfile
import unittest
from pathlib import Path


def connect(path, isolation_level="DEFERRED"):
    options = {"isolation_level": isolation_level}
    if hasattr(sqlite3, "LEGACY_TRANSACTION_CONTROL"):
        options["autocommit"] = sqlite3.LEGACY_TRANSACTION_CONTROL
    return sqlite3.connect(path, **options)


def initialise(path):
    with connect(path) as db:
        db.execute("CREATE TABLE jobs (id TEXT PRIMARY KEY, state TEXT NOT NULL)")
        db.execute("CREATE TABLE receipts (job_id TEXT NOT NULL, result TEXT NOT NULL)")
    db.close()


def snapshot(path):
    db = connect(path)
    try:
        return (db.execute("SELECT id, state FROM jobs ORDER BY id").fetchall(),
                db.execute("SELECT job_id, result FROM receipts ORDER BY job_id").fetchall())
    finally:
        db.close()


def unsafe(path, wrap=False, begin_inside=False):
    db = connect(path)
    script = "INSERT INTO receipts VALUES ('job-1', 'done'); SELECT * FROM missing_table;"
    if begin_inside:
        script = "BEGIN; " + script + " COMMIT;"
    try:
        if wrap:
            with db:
                db.execute("INSERT INTO jobs VALUES (?, ?)", ("job-1", "done"))
                db.executescript(script)
        else:
            db.execute("INSERT INTO jobs VALUES (?, ?)", ("job-1", "done"))
            db.executescript(script)
    except sqlite3.Error:
        db.rollback()
        raise
    finally:
        db.close()


def atomic(path, job_id="job-1", fail_at=None):
    # A fresh, function-owned connection; transaction control is explicit.
    db = connect(path, isolation_level=None)
    try:
        db.execute("BEGIN IMMEDIATE")
        db.execute("INSERT INTO jobs VALUES (?, ?)", (job_id, "done"))
        if fail_at == "after_job":
            raise RuntimeError("injected failure after job")
        db.execute("INSERT INTO receipts VALUES (?, ?)", (job_id, "done"))
        if fail_at == "after_receipt":
            raise RuntimeError("injected failure after receipt")
        db.execute("COMMIT")
    except BaseException:
        if db.in_transaction:
            db.execute("ROLLBACK")
        raise
    finally:
        db.close()


class TransactionTests(unittest.TestCase):
    def setUp(self):
        self.tmp = tempfile.TemporaryDirectory()
        self.addCleanup(self.tmp.cleanup)
        self.path = Path(self.tmp.name) / "fixture.sqlite"
        initialise(self.path)

    def test_script_failure_keeps_committed_writes(self):
        with self.assertRaises(sqlite3.OperationalError):
            unsafe(self.path)
        self.assertEqual(snapshot(self.path), ([("job-1", "done")], [("job-1", "done")]))

    def test_context_manager_does_not_restore_prior_commit(self):
        with self.assertRaises(sqlite3.OperationalError):
            unsafe(self.path, wrap=True)
        self.assertEqual(snapshot(self.path), ([("job-1", "done")], [("job-1", "done")]))

    def test_begin_inside_script_cannot_include_prior_write(self):
        with self.assertRaises(sqlite3.OperationalError):
            unsafe(self.path, begin_inside=True)
        self.assertEqual(snapshot(self.path), ([("job-1", "done")], []))

    def test_failure_after_job_rolls_back(self):
        with self.assertRaises(RuntimeError):
            atomic(self.path, fail_at="after_job")
        self.assertEqual(snapshot(self.path), ([], []))

    def test_failure_after_receipt_rolls_back(self):
        with self.assertRaises(RuntimeError):
            atomic(self.path, fail_at="after_receipt")
        self.assertEqual(snapshot(self.path), ([], []))

    def test_success_commits_both(self):
        atomic(self.path)
        self.assertEqual(snapshot(self.path), ([("job-1", "done")], [("job-1", "done")]))

    def test_duplicate_job_preserves_original(self):
        atomic(self.path)
        with self.assertRaises(sqlite3.IntegrityError):
            atomic(self.path)
        self.assertEqual(snapshot(self.path), ([("job-1", "done")], [("job-1", "done")]))

    def test_parameter_is_data(self):
        value = "job'); DROP TABLE jobs; --"
        atomic(self.path, value)
        self.assertEqual(snapshot(self.path), ([(value, "done")], [(value, "done")]))


def demo():
    with tempfile.TemporaryDirectory() as folder:
        for label, operation in [("Script + rollback", unsafe),
                                 ("Explicit transaction", lambda p: atomic(p, fail_at="after_receipt"))]:
            path = Path(folder) / (label + ".sqlite")
            initialise(path)
            try:
                operation(path)
            except (sqlite3.Error, RuntimeError):
                pass
            jobs, receipts = snapshot(path)
            print(f"{label}: jobs={len(jobs)}, receipts={len(receipts)}")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--test", action="store_true")
    args = parser.parse_args()
    if args.test:
        unittest.main(argv=[__file__], verbosity=2)
    else:
        demo()
