"""Run beside upsert_demo.py; all databases are disposable and in-memory."""
from contextlib import closing
import json
from pathlib import Path
import sqlite3
import subprocess
import sys
from upsert_demo import open_demo, snapshot, write_job

script = Path(__file__).with_name("upsert_demo.py")
for mode in ("replace", "upsert"):
    run = subprocess.run([sys.executable, str(script), "--mode", mode],
                         text=True, capture_output=True, check=True)
    assert run.stderr == ""
    report = json.loads(run.stdout)
    assert report["before"]["results"] == [[1, "nightly", "report.csv"]]
    after = report["after"]
    assert after["jobs"][0][:2] == ["nightly", "done"]
    if mode == "replace":
        assert after["results"] == []
        assert after["jobs"][0][2] == "2026-02-01"
    else:
        assert after["results"] == report["before"]["results"]
        assert after["jobs"][0][2] == "2026-01-01"

with closing(open_demo()) as con:
    # New key inserts, the same key updates only the requested field.
    name = "daily'中文"
    write_job(con, "upsert", name, "queued", "2026-03-01")
    write_job(con, "upsert", name, "done", "2099-01-01")
    write_job(con, "upsert", "nightly", "done", "2099-01-01")
    write_job(con, "upsert", "nightly", "done", "2099-01-01")
    before = snapshot(con)
    assert before["jobs"] == [
        [name, "done", "2026-03-01"],
        ["nightly", "done", "2026-01-01"],
    ]
    assert before["results"] == [[1, "nightly", "report.csv"]]
    try:
        write_job(con, "upsert", "nightly", None, "2099-01-01")
    except sqlite3.IntegrityError:
        pass
    else:
        raise AssertionError("NOT NULL failure was expected")
    assert snapshot(con) == before
    assert not con.in_transaction
    assert con.execute("PRAGMA foreign_key_check").fetchall() == []

for args, expected in [(["--help"], 0), (["--mode", "unknown"], 2)]:
    run = subprocess.run([sys.executable, str(script), *args], capture_output=True)
    assert run.returncode == expected
print("PASS: REPLACE loss, UPSERT preservation, insert, repeat, rollback, CLI")