"""Compare REPLACE and UPSERT in a fresh in-memory database. Python 3.11+."""
import argparse
from contextlib import closing
import json
import sqlite3
import sys


STATEMENTS = {
    "replace": """INSERT OR REPLACE INTO jobs(name, status, created_at)
                  VALUES (?, ?, ?)""",
    "upsert": """INSERT INTO jobs(name, status, created_at) VALUES (?, ?, ?)
                 ON CONFLICT(name) DO UPDATE SET status = excluded.status""",
}


def open_demo():
    if sqlite3.sqlite_version_info < (3, 24, 0):
        raise RuntimeError("SQLite 3.24.0+ is required")
    options = {"isolation_level": None}
    if sys.version_info >= (3, 12):
        options["autocommit"] = True
    con = sqlite3.connect(":memory:", **options)
    try:
        con.execute("PRAGMA foreign_keys = ON")
        if con.execute("PRAGMA foreign_keys").fetchone() != (1,):
            raise RuntimeError("foreign keys are not enabled")
        con.execute("""CREATE TABLE jobs (
            name TEXT PRIMARY KEY NOT NULL,
            status TEXT NOT NULL,
            created_at TEXT NOT NULL
        )""")
        con.execute("""CREATE TABLE results (
            id INTEGER PRIMARY KEY,
            job_name TEXT NOT NULL REFERENCES jobs(name) ON DELETE CASCADE,
            value TEXT NOT NULL
        )""")
        con.execute("INSERT INTO jobs VALUES (?, ?, ?)",
                    ("nightly", "queued", "2026-01-01"))
        con.execute("INSERT INTO results VALUES (?, ?, ?)",
                    (1, "nightly", "report.csv"))
        return con
    except Exception:
        con.close()
        raise


def write_job(con, mode, name, status, created_at):
    statement = STATEMENTS[mode]
    con.execute("BEGIN")
    try:
        con.execute(statement, (name, status, created_at))
        con.execute("COMMIT")
    except Exception:
        if con.in_transaction:
            con.execute("ROLLBACK")
        raise


def snapshot(con):
    return {
        "jobs": [list(row) for row in con.execute(
            "SELECT name, status, created_at FROM jobs ORDER BY name")],
        "results": [list(row) for row in con.execute(
            "SELECT id, job_name, value FROM results ORDER BY id")],
    }


def main():
    parser = argparse.ArgumentParser(description=__doc__, allow_abbrev=False)
    parser.add_argument("--mode", choices=STATEMENTS, default="upsert")
    args = parser.parse_args()
    try:
        with closing(open_demo()) as con:
            before = snapshot(con)
            write_job(con, args.mode, "nightly", "done", "2026-02-01")
            result = {"mode": args.mode, "before": before, "after": snapshot(con)}
        print(json.dumps(result, ensure_ascii=True, sort_keys=True))
        return 0
    except (sqlite3.Error, RuntimeError) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 1


if __name__ == "__main__":
    sys.exit(main())