"""Which guards actually stop one malformed JSON row from failing a whole query?

SQLite's json_extract() raises "malformed JSON" rather than returning NULL. If
the call is in a WHERE clause, the *statement* fails, not the row. This probe
asks, for each candidate defence, whether the query still returns the right
answer when one bad row is added somewhere the query happens to scan.

Deterministic; no statistics. Run: python3 probe.py
"""
import json
import sqlite3

GOOD = '{"carried_by": "av1"}'
BAD = "not json"


def table(extra_index=None, check=False, bad=True, rows=400):
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    ddl = "CREATE TABLE things (id TEXT PRIMARY KEY, x INT, state_json TEXT"
    if check:
        ddl += " CHECK (state_json IS NULL OR json_valid(state_json))"
    conn.execute(ddl + ")")
    if extra_index:
        conn.execute(extra_index)
    conn.execute("INSERT INTO things VALUES ('av1', 100, ?)", (GOOD,))
    for i in range(rows):
        conn.execute("INSERT INTO things VALUES (?, ?, ?)", (f"t{i}", i % 50, GOOD))
    if bad:
        try:
            conn.execute("INSERT INTO things VALUES ('bad', 5, ?)", (BAD,))
        except sqlite3.IntegrityError as exc:
            return conn, f"refused at write: {exc}"
    return conn, None


def run(conn, sql, args=()):
    try:
        return "ok: %d rows" % len(conn.execute(sql, args).fetchall())
    except sqlite3.Error as exc:
        return "%s: %s" % (type(exc).__name__, exc)


CASES = [
    ("bare json_extract, no other filter",
     dict(),
     "SELECT id FROM things WHERE json_extract(state_json, '$.carried_by') = 'av1'"),
    ("the ->> operator instead",
     dict(),
     "SELECT id FROM things WHERE state_json ->> '$.carried_by' = 'av1'"),
    ("a second predicate that excludes the bad row",
     dict(),
     "SELECT id FROM things WHERE x = 100 "
     "AND json_extract(state_json, '$.carried_by') = 'av1'"),
    ("the same, with an index on x",
     dict(extra_index="CREATE INDEX ix ON things(x)"),
     "SELECT id FROM things WHERE x = 100 "
     "AND json_extract(state_json, '$.carried_by') = 'av1'"),
    ("json_valid() guard written first",
     dict(),
     "SELECT id FROM things WHERE json_valid(state_json) "
     "AND json_extract(state_json, '$.carried_by') = 'av1'"),
    ("json_valid() guard, and the bad row is also excluded by x",
     dict(),
     "SELECT id FROM things WHERE json_valid(state_json) AND x = 100 "
     "AND json_extract(state_json, '$.carried_by') = 'av1'"),
    ("CASE WHEN json_valid(...) THEN json_extract(...) END",
     dict(),
     "SELECT id FROM things WHERE CASE WHEN json_valid(state_json) "
     "THEN json_extract(state_json, '$.carried_by') END = 'av1'"),
    ("a CHECK constraint on the column",
     dict(check=True),
     "SELECT id FROM things WHERE json_extract(state_json, '$.carried_by') = 'av1'"),
]


def main():
    print("sqlite", sqlite3.sqlite_version)
    out = {"sqlite_version": sqlite3.sqlite_version, "cases": []}
    for label, kw, sql in CASES:
        conn, refused = table(**kw)
        clean, _ = table(bad=False, **{k: v for k, v in kw.items() if k != "bad"})
        control = run(clean, sql)
        result = refused or run(conn, sql)
        # The plan is the reason, so print it for the cases that turn on one.
        try:
            plan = " / ".join(r[3] for r in clean.execute("EXPLAIN QUERY PLAN " + sql))
        except sqlite3.Error:
            plan = "?"
        survived = result.startswith("ok") or result.startswith("refused")
        print(f"{'PASS' if survived else 'FAIL'}  {label}")
        print(f"        clean table: {control}")
        print(f"        one bad row: {result}")
        print(f"        plan: {plan}")
        out["cases"].append({"case": label, "sql": sql, "clean": control,
                             "with_bad_row": result, "plan": plan,
                             "survived": survived})
    with open("results.json", "w") as fh:
        json.dump(out, fh, indent=2, sort_keys=True)


if __name__ == "__main__":
    main()