#!/usr/bin/env python3
"""Original local SQLite integration exercise; Python 3.11+, no dependencies.

Two temporary databases demonstrate local transactions and receiver effects.
No network, customer records or persistent files. Not distributed exactly-once proof.
"""
import json
import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory


def prepare(source, receiver):
    source.executescript("""
        CREATE TABLE cases (id TEXT PRIMARY KEY, status TEXT NOT NULL);
        CREATE TABLE outbox (id TEXT PRIMARY KEY, payload TEXT NOT NULL);
        INSERT INTO cases VALUES ('case-42', 'new');
    """)
    receiver.executescript("""
        CREATE TABLE received (id TEXT PRIMARY KEY, payload TEXT NOT NULL);
        CREATE TABLE tasks (event_id TEXT PRIMARY KEY, case_id TEXT NOT NULL);
    """)


def commit_case(source, event_id, fail=False):
    payload = json.dumps({"case_id": "case-42", "status": "ready"}, sort_keys=True)
    with source:
        source.execute("UPDATE cases SET status = 'ready' WHERE id = 'case-42'")
        if fail:
            raise RuntimeError("Injected failure before event insertion")
        source.execute("INSERT INTO outbox VALUES (?, ?)", (event_id, payload))
    return payload


def receive(receiver, event_id, payload, fail=False):
    data = json.loads(payload)
    canonical = json.dumps(data, sort_keys=True)
    with receiver:
        previous = receiver.execute("SELECT payload FROM received WHERE id = ?", (event_id,)).fetchone()
        if previous:
            if previous[0] != canonical:
                raise ValueError("Operation identity reused with conflicting payload")
            return "duplicate"
        receiver.execute("INSERT INTO received VALUES (?, ?)", (event_id, canonical))
        if fail:
            raise RuntimeError("Injected failure before receiving effect")
        receiver.execute("INSERT INTO tasks VALUES (?, ?)", (event_id, data["case_id"]))
    return "created"


def migration_demo(connection, maintain_legacy=True):
    connection.executescript("""
        CREATE TABLE owners (id TEXT PRIMARY KEY, owner_name TEXT NOT NULL);
        INSERT INTO owners VALUES ('existing', 'Queue A');
        ALTER TABLE owners ADD COLUMN owner_display_name TEXT;
        UPDATE owners SET owner_display_name = owner_name WHERE owner_display_name IS NULL;
    """)
    # Retain an old field during coexistence; omission leaves an observable stale value.
    with connection:
        connection.execute("INSERT INTO owners VALUES ('new', ?, 'Queue B')",
                           ("Queue B" if maintain_legacy else "Unassigned",))
    rows = connection.execute("SELECT id, owner_name, owner_display_name FROM owners ORDER BY id").fetchall()
    return {"rows": [{"id": key, "old_reader": old, "new_reader": new} for key, old, new in rows],
            "compatible": all(old == new for _, old, new in rows)}


def main():
    with TemporaryDirectory(prefix="fde-integration-") as directory:
        source = sqlite3.connect(Path(directory) / "source.db")
        receiver = sqlite3.connect(Path(directory) / "receiver.db")
        try:
            prepare(source, receiver)
            try:
                commit_case(source, "failed-event", fail=True)
            except RuntimeError:
                pass
            rollback = {"status": source.execute("SELECT status FROM cases").fetchone()[0],
                        "outbox_rows": source.execute("SELECT count(*) FROM outbox").fetchone()[0]}
            payload = commit_case(source, "event-42")
            try:
                receive(receiver, "event-42", payload, fail=True)
            except RuntimeError:
                pass
            receiving_rollback = receiver.execute("SELECT count(*) FROM received").fetchone()[0]
            delivery = [receive(receiver, "event-42", payload), receive(receiver, "event-42", payload)]
            conflict = False
            try:
                receive(receiver, "event-42", json.dumps({"case_id": "case-99", "status": "ready"}))
            except ValueError:
                conflict = True
            compatible = migration_demo(source)
            broken = migration_demo(receiver, maintain_legacy=False)
            report = {"scope": "Synthetic local transactions; delivery may repeat.",
                      "source_failure": rollback, "receiver_identity_rows_after_failure": receiving_rollback,
                      "delivery": delivery, "receiver_effects": receiver.execute("SELECT count(*) FROM tasks").fetchone()[0],
                      "payload_conflict_rejected": conflict,
                      "migration": compatible, "broken_compatibility": broken}
            print(json.dumps(report, indent=2))
        finally:
            source.close()
            receiver.close()


if __name__ == "__main__":
    main()