"""The two fixes: a generated column, and a CHECK constraint. Which one holds?"""
import json
import sqlite3

GOOD, BAD = '{"carried_by": "av1"}', "not json"
out = {"sqlite_version": sqlite3.sqlite_version, "cases": []}


def note(label, detail):
    print(f"  {label}: {detail}")
    out["cases"].append({"case": label, "result": detail})


print("sqlite", sqlite3.sqlite_version)

print("\nA. VIRTUAL generated column over the JSON")
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE things (id TEXT PRIMARY KEY, x INT, state_json TEXT, "
             "carried_by TEXT GENERATED ALWAYS AS "
             "(json_extract(state_json,'$.carried_by')) VIRTUAL)")
conn.execute("INSERT INTO things (id,x,state_json) VALUES ('av1',100,?)", (GOOD,))
try:
    conn.execute("INSERT INTO things (id,x,state_json) VALUES ('bad',5,?)", (BAD,))
    note("insert of a malformed row", "accepted")
except sqlite3.Error as exc:
    note("insert of a malformed row", f"{type(exc).__name__}: {exc}")
for label, sql in (("select on the generated column",
                    "SELECT id FROM things WHERE carried_by = 'av1'"),
                   ("select on the generated column, with an index",
                    None)):
    if sql is None:
        try:
            conn.execute("CREATE INDEX ixc ON things(carried_by)")
            note("building an index on it", "created")
        except sqlite3.Error as exc:
            note("building an index on it", f"{type(exc).__name__}: {exc}")
        continue
    try:
        note(label, "ok: %d rows" % len(conn.execute(sql).fetchall()))
    except sqlite3.Error as exc:
        note(label, f"{type(exc).__name__}: {exc}")

print("\nB. STORED generated column")
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE things (id TEXT PRIMARY KEY, x INT, state_json TEXT, "
             "carried_by TEXT GENERATED ALWAYS AS "
             "(json_extract(state_json,'$.carried_by')) STORED)")
conn.execute("INSERT INTO things (id,x,state_json) VALUES ('av1',100,?)", (GOOD,))
try:
    conn.execute("INSERT INTO things (id,x,state_json) VALUES ('bad',5,?)", (BAD,))
    note("insert of a malformed row", "accepted")
except sqlite3.Error as exc:
    note("insert of a malformed row", f"{type(exc).__name__}: {exc}")
try:
    note("select on the generated column",
         "ok: %d rows" % len(conn.execute(
             "SELECT id FROM things WHERE carried_by = 'av1'").fetchall()))
except sqlite3.Error as exc:
    note("select on the generated column", f"{type(exc).__name__}: {exc}")

print("\nC. CHECK (json_valid(...)) added to a table that already holds a bad row")
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE things (id TEXT PRIMARY KEY, x INT, state_json TEXT)")
conn.execute("INSERT INTO things VALUES ('bad',5,?)", (BAD,))
bad_rows = conn.execute(
    "SELECT COUNT(*) FROM things WHERE state_json IS NOT NULL "
    "AND NOT json_valid(state_json)").fetchone()[0]
note("finding the bad rows first (json_valid does not raise)", f"{bad_rows} found")

with open("fixes.json", "w") as fh:
    json.dump(out, fh, indent=2, sort_keys=True)