"""模拟工单：SQLite 本地事务，无模型、网络、资金或真实通知。"""
import hashlib
import json
import sqlite3
import tempfile
from pathlib import Path

SCHEMA = """
CREATE TABLE tickets(id TEXT PRIMARY KEY, version INTEGER, status TEXT);
CREATE TABLE operations(key TEXT PRIMARY KEY, digest TEXT, ticket TEXT, target TEXT, expected INTEGER, approved INTEGER, state TEXT);
CREATE TABLE notices(key TEXT PRIMARY KEY, sent INTEGER);
"""

def digest(payload):
    return hashlib.sha256(json.dumps(payload, sort_keys=True).encode()).hexdigest()

def propose(db, key, ticket, target, expected):
    # ①语义请求键与载荷绑定，同键异参立即拒绝。
    if target != "resolved":
        raise ValueError("不允许的目标状态")
    value = digest([ticket, target, expected])
    row = db.execute("SELECT digest FROM operations WHERE key=?", (key,)).fetchone()
    if row:
        if row[0] != value:
            raise ValueError("同键异参")
        return
    with db:
        db.execute("INSERT INTO operations VALUES(?,?,?,?,?,0,'waiting_approval')", (key, value, ticket, target, expected))

def approve(db, key):
    # ②模拟可信审批入口；生产需独立身份、权限和有效期校验。
    with db:
        count = db.execute("UPDATE operations SET approved=1 WHERE key=? AND state='waiting_approval'", (key,)).rowcount
        if count != 1:
            raise ValueError("审批对象不存在或状态不合法")

def execute(db, key, lose_response=False):
    with db:
        db.execute("BEGIN IMMEDIATE")
        row = db.execute("SELECT ticket,target,expected,approved,state FROM operations WHERE key=?", (key,)).fetchone()
        if row is None:
            raise ValueError("未知操作")
        ticket, target, expected, approved, state = row
        if state in ("partial", "completed", "needs_review"):
            # 旧请求只重新核验，绝不重放工单副作用；复用当前事务。
            _verify(db, key)
            return db.execute("SELECT state FROM operations WHERE key=?", (key,)).fetchone()[0]
        if state == "needs_reapproval":
            return state
        if not approved:
            raise PermissionError("尚未批准")
        # ③版本条件与副作用、回执和待通知项在同一事务提交。
        changed = db.execute("UPDATE tickets SET status=?,version=version+1 WHERE id=? AND version=?", (target, ticket, expected)).rowcount
        if not changed:
            db.execute("UPDATE operations SET state='needs_reapproval' WHERE key=?", (key,))
            state = "needs_reapproval"
        else:
            db.execute("INSERT INTO notices VALUES(?,0)", (key,))
            db.execute("UPDATE operations SET state='partial' WHERE key=?", (key,))
            state = "partial"
    if lose_response and state == "partial":
        raise TimeoutError("模拟提交后响应丢失")
    return state

def notify(db, key, fail=False):
    if fail:
        raise ConnectionError("模拟通知故障，保留待处理项")
    with db:
        count = db.execute("UPDATE notices SET sent=1 WHERE key=?", (key,)).rowcount
        if count != 1:
            raise ValueError("没有待通知项")

def _verify(db, key):
    # 待通知项证明历史写入已提交；state 表示最近核验，不是永远有效的成功。
    row = db.execute("SELECT o.state,t.status,t.version,o.target,o.expected,n.sent FROM operations o LEFT JOIN tickets t ON t.id=o.ticket LEFT JOIN notices n ON n.key=o.key WHERE o.key=?", (key,)).fetchone()
    if row is None:
        raise ValueError("未知操作")
    state, status, version, target, expected, sent = row
    if state in ("waiting_approval", "needs_reapproval"):
        return False
    current = status == target and version == expected + 1
    # 复核出口为保守锁存：即使外部状态再次吻合，旧请求也不自行恢复完成。
    next_state = ("needs_review" if state == "needs_review" or not current
                  else "completed" if sent == 1 else "partial")
    db.execute("UPDATE operations SET state=? WHERE key=?", (next_state, key))
    return next_state == "completed"


def verify(db, key):
    # ④结果从业务表与通知表读取，不相信模型措辞。
    with db:
        db.execute("BEGIN IMMEDIATE")
        return _verify(db, key)

def main():
    with tempfile.TemporaryDirectory() as directory:
        path = Path(directory) / "fixture.db"
        db = sqlite3.connect(path)
        db.executescript(SCHEMA)
        with db:
            db.executemany("INSERT INTO tickets VALUES(?,1,'open')", [("T1",), ("T2",), ("T3",)])
        propose(db, "request-1", "T1", "resolved", 1)
        try:
            execute(db, "request-1")
        except PermissionError:
            pass  # 预期拒绝的测试分支，不是运行时吞错。
        else:
            raise AssertionError("未审批竟然执行")
        approve(db, "request-1")
        try:
            execute(db, "request-1", lose_response=True)
        except TimeoutError:
            pass
        else:
            raise AssertionError("故障注入未触发")
        db.close()
        db = sqlite3.connect(path)  # 从磁盘恢复，不复用进程内状态。
        propose(db, "request-1", "T1", "resolved", 1)
        assert execute(db, "request-1") == "partial"
        assert db.execute("SELECT version FROM tickets WHERE id='T1'").fetchone()[0] == 2
        try:
            propose(db, "request-1", "T2", "resolved", 1)
        except ValueError:
            pass
        else:
            raise AssertionError("同键异参未拒绝")
        try:
            notify(db, "request-1", fail=True)
        except ConnectionError:
            pass
        assert not verify(db, "request-1")
        for _ in range(3):
            assert execute(db, "request-1") == "partial"
            assert not verify(db, "request-1")
            assert db.execute("SELECT version FROM tickets WHERE id='T1'").fetchone()[0] == 2
        notify(db, "request-1")
        assert verify(db, "request-1")
        assert execute(db, "request-1") == "completed"
        propose(db, "request-2", "T2", "resolved", 1)
        approve(db, "request-2")
        with db:
            db.execute("UPDATE tickets SET version=2 WHERE id='T2'")
        assert execute(db, "request-2") == "needs_reapproval"
        assert db.execute("SELECT status FROM tickets WHERE id='T2'").fetchone()[0] == "open"
        propose(db, "request-3", "T3", "resolved", 1)
        approve(db, "request-3")
        assert execute(db, "request-3") == "partial"
        notify(db, "request-3")
        with db:
            db.execute("UPDATE tickets SET status='open',version=3 WHERE id='T3'")
        assert not verify(db, "request-3")
        assert execute(db, "request-3") == "needs_review"
        assert not verify(db, "request-2")
        assert execute(db, "request-2") == "needs_reapproval"
        # 先成功核验，再发生外部变化；分别覆盖 verify/execute 首先发现失效。
        for first_check in (verify, execute):
            with db:
                db.execute("UPDATE tickets SET status='resolved',version=2 WHERE id='T1'")
                db.execute("UPDATE operations SET state='partial' WHERE key='request-1'")
            assert verify(db, "request-1")
            with db:
                db.execute("UPDATE tickets SET status='open',version=3 WHERE id='T1'")
            first_check(db, "request-1")
            for _ in range(3):
                assert not verify(db, "request-1")
                assert execute(db, "request-1") == "needs_review"
                notify(db, "request-1")
                assert db.execute("SELECT state FROM operations WHERE key='request-1'").fetchone()[0] == "needs_review"
                assert db.execute("SELECT status,version FROM tickets WHERE id='T1'").fetchone() == ("open", 3)
            assert db.execute("SELECT COUNT(*) FROM notices WHERE key='request-1'").fetchone()[0] == 1
            try:
                approve(db, "request-1")
            except ValueError:
                pass
            else:
                raise AssertionError("旧操作不能重新审批重放")
            # 单独验证锁存，不让 not current 掩盖 needs_review 分支。
            with db:
                db.execute("UPDATE tickets SET status='resolved',version=2 WHERE id='T1'")
            assert not verify(db, "request-1")
            assert execute(db, "request-1") == "needs_review"
        db.close()
        print("PASS duplicate, lost-response, stale-approval, partial, final-state, restart; completed-invalidated, no-replay; SIMULATED")

if __name__ == "__main__":
    main()