#!/usr/bin/env python3
"""Original Star Techblog teaching demo. Python stdlib only; temporary SQLite DBs.
No network, payment, broker, or real customer data. Exceptions simulate selected
crash boundaries; this is NOT a process-kill/durability/concurrency benchmark.
"""
import datetime, json, pathlib, platform, sqlite3, tempfile


def run():
    with tempfile.TemporaryDirectory(prefix='outbox-demo-') as temp:
        sender = sqlite3.connect(pathlib.Path(temp)/'sender.db')
        consumer = sqlite3.connect(pathlib.Path(temp)/'consumer.db')
        sender.executescript('''CREATE TABLE orders(id TEXT PRIMARY KEY);
            CREATE TABLE outbox(id TEXT PRIMARY KEY, order_id TEXT NOT NULL, sent INTEGER NOT NULL DEFAULT 0);''')
        consumer.executescript('''CREATE TABLE processed(consumer TEXT, event_id TEXT, PRIMARY KEY(consumer,event_id));
            CREATE TABLE shipping(order_id TEXT PRIMARY KEY);''')
        rows=[]
        def count(db,table):
            assert table in {'orders','outbox','processed','shipping'}
            return db.execute('SELECT count(*) FROM '+table).fetchone()[0]
        try:
            with sender:
                sender.execute('INSERT INTO orders VALUES (?)',('rolled-back',))
                sender.execute('INSERT INTO outbox(id,order_id) VALUES (?,?)',('event-rollback','rolled-back'))
                raise RuntimeError('failure before commit')
        except RuntimeError: pass
        assert count(sender,'orders')==count(sender,'outbox')==0
        rows.append({'case':'failure_before_commit','orders':0,'outbox':0})
        with sender:
            sender.execute('INSERT INTO orders VALUES (?)',('order-1',))
            sender.execute('INSERT INTO outbox(id,order_id) VALUES (?,?)',('event-1','order-1'))
        # Stop delivery here: the committed intent survives a connection reopen.
        sender.close(); sender=sqlite3.connect(pathlib.Path(temp)/'sender.db')
        assert count(sender,'orders')==count(sender,'outbox')==1
        assert count(consumer,'shipping')==0
        rows.append({'case':'reopen_after_commit_before_delivery','orders':1,'outbox':1,'shipping':0})
        event=sender.execute('SELECT id,order_id FROM outbox WHERE sent=0').fetchone()
        def consume(event,fail=False):
            with consumer:
                cursor=consumer.execute('INSERT INTO processed VALUES (?,?) ON CONFLICT DO NOTHING',('shipping',event[0]))
                if cursor.rowcount==0: return 'duplicate'
                consumer.execute('INSERT INTO shipping VALUES (?)',(event[1],))
                if fail: raise RuntimeError('consumer failure before commit')
            return 'applied'
        try: consume(event,fail=True)
        except RuntimeError: pass
        assert count(consumer,'shipping')==count(consumer,'processed')==0
        rows.append({'case':'consumer_rollback','shipping':0,'processed':0})
        first=consume(event)
        # Simulate losing delivery acknowledgement: do NOT mark sender sent yet.
        replay=consume(event)
        with sender: sender.execute('UPDATE outbox SET sent=1 WHERE id=?',(event[0],))
        assert first=='applied' and replay=='duplicate'
        assert count(consumer,'shipping')==count(consumer,'processed')==1
        rows.append({'case':'replay_after_lost_ack','attempts':2,'first':first,'replay':replay,'shipping':1,'processed':1})
        sender.close();consumer.close()
    return {'executedAt':datetime.datetime.now(datetime.timezone.utc).isoformat(),
            'python':platform.python_version(),'sqlite':sqlite3.sqlite_version,
            'scope':'local SQLite transactions and logical failure injection; no broker, network, concurrent workers or external effects',
            'results':rows,'passed':True}

if __name__=='__main__': print(json.dumps(run(),ensure_ascii=False,indent=2))