"""Original, fictional SQLite teaching lab; never connects to a service or disk DB.

Run with Python 3.8+ (SQLite 3.25+). JSON goes to stdout. This is a small
compatibility demonstration, not a production migration or recovery procedure.
All amounts are fabricated CAD cents; no customer records are used.
"""

import json
import sqlite3
import sys


def fixture():
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, amount_cents INTEGER NOT NULL)")
    db.executemany("INSERT INTO orders VALUES (?, ?)", [(1, 1000), (2, 2500)])
    db.commit()
    return db


def old_total(db):
    return db.execute("SELECT SUM(amount_cents) FROM orders").fetchone()[0]


def columns(db):
    return [row[1] for row in db.execute("PRAGMA table_info(orders)")]


def additive_case():
    db = fixture()
    try:
        before = old_total(db)
        # The CAD default is appropriate only for this all-CAD fictional fixture.
        db.execute("ALTER TABLE orders ADD COLUMN currency TEXT NOT NULL DEFAULT 'CAD'")
        db.execute("INSERT INTO orders (id, amount_cents, currency) VALUES (3, 700, 'CAD')")
        db.commit()
        after = old_total(db)
        assert (before, after) == (3500, 4200)
        assert db.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 3
        return {
            "case": "additive_column_with_old_reader",
            "before_total_cents": before,
            "old_reader_after_new_write_cents": after,
            "observed": "The old explicit-column query still reads all three records.",
            "scope": "This query and fixture only; not proof all additive changes are compatible.",
        }
    finally:
        db.close()


def destructive_case():
    db, snapshot, restored = fixture(), sqlite3.connect(":memory:"), sqlite3.connect(":memory:")
    try:
        db.backup(snapshot)
        db.execute("ALTER TABLE orders RENAME COLUMN amount_cents TO amount_minor")
        db.execute("INSERT INTO orders (id, amount_minor) VALUES (3, 700)")
        db.commit()
        schema_before_old_reader = columns(db)
        try:
            old_total(db)
        except sqlite3.OperationalError as error:
            old_reader_error = str(error)
            assert "no such column: amount_cents" in old_reader_error
        else:
            raise AssertionError("Expected the old query to fail after the rename")
        assert columns(db) == schema_before_old_reader
        current_total = db.execute("SELECT SUM(amount_minor) FROM orders").fetchone()[0]
        assert current_total == 4200
        snapshot.backup(restored)
        restored_ids = [row[0] for row in restored.execute("SELECT id FROM orders ORDER BY id")]
        assert old_total(restored) == 3500 and restored_ids == [1, 2]
        return {
            "case": "renamed_column_and_old_snapshot",
            "old_reader_error": old_reader_error,
            "schema_unchanged_by_running_old_query": True,
            "current_total_cents": current_total,
            "snapshot_restored_total_cents": old_total(restored),
            "snapshot_restored_ids": restored_ids,
            "post_snapshot_record_missing": 3,
            "observed": "Running old code did not undo the schema rename. Restoring only the older snapshot omitted the later write.",
            "scope": "No log replay, replication, reconciliation, real deployment or disaster recovery was tested.",
        }
    finally:
        db.close()
        snapshot.close()
        restored.close()


def naive_dual_column_case():
    db = fixture()
    try:
        db.execute("ALTER TABLE orders ADD COLUMN amount_minor INTEGER")
        db.execute("UPDATE orders SET amount_minor = amount_cents")
        # An older application still writes only its original column.
        db.execute("UPDATE orders SET amount_cents = 1200 WHERE id = 1")
        db.commit()
        old_value = db.execute("SELECT amount_cents FROM orders WHERE id = 1").fetchone()[0]
        naive_new_value = db.execute(
            "SELECT COALESCE(amount_minor, amount_cents) FROM orders WHERE id = 1"
        ).fetchone()[0]
        assert (old_value, naive_new_value) == (1200, 1000)
        return {
            "case": "backfill_then_old_writer_update",
            "old_reader_cents": old_value,
            "naive_new_reader_cents": naive_new_value,
            "observed": "Backfilling a second column and preferring it on reads left a stale value after an old writer updated only the first column.",
            "scope": "Illustrates a mixed-version write hazard; does not prescribe a general synchronization mechanism.",
        }
    finally:
        db.close()


def main():
    if sqlite3.sqlite_version_info < (3, 25, 0):
        raise RuntimeError("This demonstration needs SQLite 3.25+ for RENAME COLUMN")
    report = {
        "fixture": "fictional CAD order amounts, three isolated in-memory scenarios",
        "runtime": {"python": sys.version.split()[0], "sqlite": sqlite3.sqlite_version},
        "network_requests": 0,
        "disk_databases": 0,
        "scenarios": [additive_case(), destructive_case(), naive_dual_column_case()],
        "limitations": [
            "No concurrent transactions, locks, performance, real traffic or client data.",
            "SQLite behavior is not evidence for PostgreSQL/MySQL migration or recovery semantics.",
            "No Git command, Kubernetes cluster, rollout, feature flag or automated recovery was executed.",
            "Examples demonstrate specific failures, not universal production readiness or recovery-time targets.",
        ],
    }
    print(json.dumps(report, indent=2))


if __name__ == "__main__":
    main()