"""Independent read-only oracle for the shared advanced School fixture."""
import json
import sqlite3
import sys
from pathlib import Path

if not __debug__:
    raise RuntimeError("Database verification requires Python assertions enabled")
database, phase, fixture_path = Path(sys.argv[1]).resolve(), sys.argv[2], Path(sys.argv[3])
mutation_case = phase in ("transaction-commit", "transaction-rollback", "concurrent-same", "concurrent-distinct")
assert mutation_case or phase in ("bootstrap", "seed", "query", "update", "read-update", "delete", "read-delete")
fixture = json.loads(fixture_path.read_text())
assert database.is_file(), "The application did not create its database"
with sqlite3.connect(database.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()
    def rows(table):
        return [dict(row) for row in connection.execute(f'SELECT * FROM "{table}" ORDER BY id')]
    roots, constants, schools = rows("platform_data"), rows("school_type_data"), rows("school_data")

assert len(roots) == 1 and roots[0]["id"] == 1
assert roots[0]["name"] == ("Transactional Campus" if phase == "transaction-commit" else "Campus Learning Platform") and roots[0]["base_url"] == "https://campus.example.com"
assert roots[0]["version"] == (2 if phase == "transaction-commit" else 1)
assert roots[0]["create_time"] is not None and roots[0]["update_time"] is not None
assert [(r["id"], r["name"], r["code"], r["display_order"], r["platform"]) for r in constants] == [
    (1001, "Primary", "PRIMARY", 1, 1), (1002, "Secondary", "SECONDARY", 2, 1)]
assert all(row["version"] == 1 for row in constants)
expected = [] if phase == "bootstrap" else fixture["schools"]
assert len(schools) == len(expected), schools
by_name = {r["name"]: r for r in schools}
assert len(by_name) == len(expected), "Duplicate School names"
for item in expected:
    row = by_name[item["name"]]
    revised = phase in ("update", "read-update", "delete", "read-delete") and item["name"] == "Riverside Primary School"
    deleted = phase in ("delete", "read-delete") and item["name"] == "Hill Secondary School"
    tx_changed = mutation_case and (item["name"] == "Riverside Primary School" or (phase == "concurrent-distinct" and item["name"] == "North Primary School"))
    if tx_changed:
        assert row["version"] == 2
        capacities = {"transaction-commit": [810], "transaction-rollback": [801], "concurrent-same": [811, 822], "concurrent-distinct": [811] if item["name"] == "Riverside Primary School" else [822]}
        assert row["student_capacity"] in capacities[phase]
    assert row["id"] > 0 and (tx_changed or row["version"] == (-2 if deleted else 2 if revised else 1)), row
    assert tx_changed or row["student_capacity"] == (950 if revised else item["capacity"]), row
    assert row["active"] == int(item["active"]), row
    assert row["platform"] == 1 and row["school_type"] == (1001 if item["type"] == "PRIMARY" else 1002), row
    assert row["address"] == fixture["address"] and str(row["established_date"])[:10] == fixture["establishedDate"], row
    assert row["create_time"] is not None and row["update_time"] is not None, row
print(json.dumps({"roots": roots, "constants": constants, "schools": schools}, sort_keys=True))
