"""Read-only SQLite oracle: exact one-operation delta, including tombstones."""
import copy
import json
import sqlite3
import sys
from pathlib import Path

if not __debug__:
    raise RuntimeError("Order completion oracle requires assertions")
database, before_file, plan_file = map(Path, sys.argv[1:])
before, plan = json.loads(before_file.read_text()), json.loads(plan_file.read_text())
assert database.is_file()
with sqlite3.connect(database.resolve().as_uri() + "?mode=ro", uri=True) as connection:
    connection.row_factory = sqlite3.Row
    assert connection.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
    assert not connection.execute("PRAGMA foreign_key_check").fetchall()
    tables = {"roots":"commerce_platform_data", "statuses":"order_status_data",
              "orders":"customer_order_data", "items":"order_item_data"}
    after = {key:[dict(row) for row in connection.execute(f'SELECT * FROM "{table}" ORDER BY id')]
             for key, table in tables.items()}
expected = copy.deepcopy(before)
kind = plan["kind"]
assert kind in ("unchanged", "omission", "remove", "remove-conflict", "transition")
def changed(group, row):
    actual = next(item for item in after[group] if item["id"] == row["id"])
    assert actual["update_time"] is not None
    row["update_time"] = actual["update_time"]
if kind in ("omission", "remove", "remove-conflict"):
    order = next(row for row in expected["orders"] if row["order_number"] == "ORD-2026-1001")
    assert order["version"] == 1 and order["total_minor"] == 3850 and order["order_status"] == 1001
    if kind == "omission":
        order.update(order_note="Edited through an item preview", version=2)
        changed("orders", order)
    else:
        item = next(row for row in expected["items"] if row["customer_order"] == order["id"] and row["sku_code"] == "SKU-PEN")
        assert item["version"] == 1
        if kind == "remove":
            order.update(total_minor=2500, version=2)
            item["version"] = -2
            changed("orders", order)
        else:
            item.update(product_name="Pen (concurrent winner)", version=2)
        changed("items", item)
elif kind == "transition":
    order = next(row for row in expected["orders"] if row["order_number"] == plan["target"])
    assert (order["order_status"], plan["to"]) in ((1001,1002),(1001,1003),(1002,1003))
    order.update(order_status=plan["to"], version=order["version"] + 1)
    changed("orders", order)
assert after == expected, "Unexpected field, identity, timestamp, state or tombstone change"
for order in after["orders"]:
    active = [row for row in after["items"] if row["customer_order"] == order["id"] and row["version"] > 0]
    assert order["total_minor"] == sum(row["line_total_minor"] for row in active)
    assert all(row["line_total_minor"] == row["quantity"] * row["unit_price_minor"] for row in active)
print(json.dumps(after, sort_keys=True))
