"""Deterministic SQLite retry model. No network, external services or real payments."""
import json
import sqlite3
from pathlib import Path

class LostResponse(Exception): pass
class PayloadConflict(Exception): pass

class Service:
    def __init__(self):
        self.db=sqlite3.connect(':memory:')
        self.db.executescript('''
          CREATE TABLE effects(id INTEGER PRIMARY KEY, tenant TEXT, amount INTEGER);
          CREATE TABLE requests(tenant TEXT, operation TEXT, key TEXT, amount INTEGER,
            result INTEGER, PRIMARY KEY(tenant,operation,key));
        ''')
        self.replays=0
        self.conflicts=0
    def call(self, tenant, key, amount=10, fault=None):
        row=self.db.execute('SELECT amount,result FROM requests WHERE tenant=? AND operation=? AND key=?',(tenant,'credit',key)).fetchone()
        if row:
            if row[0]!=amount:
                self.conflicts+=1
                raise PayloadConflict()
            self.replays+=1
            return row[1]
        with self.db:
            result=self.db.execute('INSERT INTO effects(tenant,amount) VALUES (?,?)',(tenant,amount)).lastrowid
            self.db.execute('INSERT INTO requests VALUES (?,?,?,?,?)',(tenant,'credit',key,amount,result))
            if fault=='before_commit': raise LostResponse()
        if fault=='after_commit': raise LostResponse()
        return result
    def count(self): return self.db.execute('SELECT count(*) FROM effects').fetchone()[0]

def run():
    cases=[
      ('Before commit', [('a','k',10,'before_commit'),('a','k',10,None)], 1,0,0),
      ('After commit', [('a','k',10,'after_commit'),('a','k',10,None)], 1,1,0),
      ('Three deliveries', [('a','k',10,None)]*3, 1,2,0),
      ('Changed payload', [('a','k',10,None),('a','k',20,None)], 1,0,1),
      ('Two tenants', [('a','k',10,None),('b','k',10,None)], 2,0,0),
      ('New retry key', [('a','k',10,None),('a','k-new',10,None)], 2,0,0),
    ]
    results=[]
    for label, calls, effects, replays, conflicts in cases:
        s=Service()
        trace=[]
        for i,args in enumerate(calls,1):
            try: outcome=f'result:{s.call(*args)}'
            except LostResponse: outcome='response lost'
            except PayloadConflict: outcome='payload conflict'
            trace.append({'attempt':i,'outcome':outcome,'effects':s.count()})
        assert (s.count(),s.replays,s.conflicts)==(effects,replays,conflicts)
        results.append({'case':label,'attempts':len(calls),'effects':s.count(),'replays':s.replays,'conflicts':s.conflicts,'trace':trace})
        s.db.close()
    return {'scope':'Sequential in-memory SQLite model; no concurrent clients or external side effects','cases':results}
if __name__=='__main__':
    first=run()
    assert first==run()
    Path(__file__).with_name('results.json').write_text(json.dumps(first,indent=2)+'\n')
    print(json.dumps(first,indent=2))