"""Python 3.10+ and SQLite 3.24+; run the local transaction tests."""
import hashlib
from pathlib import Path
import sqlite3
import tempfile
import unittest


def connect(path):
    db = sqlite3.connect(path)
    db.execute("CREATE TABLE IF NOT EXISTS operations (key TEXT PRIMARY KEY, digest TEXT NOT NULL, result INTEGER NOT NULL)")
    db.execute("CREATE TABLE IF NOT EXISTS counter (id INTEGER PRIMARY KEY CHECK(id=1), value INTEGER NOT NULL)")
    db.execute("INSERT OR IGNORE INTO counter VALUES (1, 0)")
    db.commit()
    return db


def apply(db, key, payload):
    digest = hashlib.sha256(payload).hexdigest()
    try:
        db.execute("BEGIN IMMEDIATE")
        old = db.execute("SELECT digest, result FROM operations WHERE key=?", (key,)).fetchone()
        if old:
            if old[0] != digest:
                raise ValueError("key reused with different payload")
            result = old[1]
        else:
            db.execute("UPDATE counter SET value=value+1 WHERE id=1")
            result = db.execute("SELECT value FROM counter WHERE id=1").fetchone()[0]
            db.execute("INSERT INTO operations VALUES (?, ?, ?)", (key, digest, result))
        db.commit()
        return result
    except Exception:
        db.rollback()
        raise


class ReplayTests(unittest.TestCase):
    def test_replay_conflict_and_reopen(self):
        with tempfile.TemporaryDirectory() as folder:
            path = Path(folder) / "operations.sqlite3"
            db = connect(path)
            self.assertEqual(apply(db, "a", b"first"), 1)
            self.assertEqual(apply(db, "a", b"first"), 1)
            with self.assertRaises(ValueError):
                apply(db, "a", b"changed")
            db.close()
            db = connect(path)
            self.assertEqual(apply(db, "a", b"first"), 1)
            self.assertEqual(apply(db, "b", b"second"), 2)
            db.close()


if __name__ == "__main__":
    unittest.main()