"""Independent, read-only SQLite assertions; never seeds or changes domain data."""
import json
import sqlite3
import sys
from pathlib import Path

if not __debug__:
    raise RuntimeError("Database verification requires Python assertions enabled")
database = Path(sys.argv[1]).resolve()
expected_schools = int(sys.argv[2])
assert expected_schools in (0, 1)
assert database.is_file(), "The application did not create its SQLite database"
connection = sqlite3.connect(database.as_uri() + "?mode=ro", uri=True)
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):
    # These fixed identifiers describe the shared fixture's physical output.
    return [dict(row) for row in connection.execute(f'SELECT * FROM "{table}" ORDER BY id')]

roots = rows("platform_data")
constants = rows("school_type_data")
schools = rows("school_data")
assert len(roots) == 1 and roots[0]["id"] == 1, roots
assert roots[0]["name"] == "Campus Learning Platform", roots
assert roots[0]["base_url"] == "https://campus.example.com", roots
assert [(row["id"], row["name"], row["code"], row["display_order"]) for row in constants] == [
    (1001, "Primary", "PRIMARY", 1), (1002, "Secondary", "SECONDARY", 2)
], constants
assert all(row["platform"] == 1 for row in constants), constants
assert len(schools) == expected_schools, schools
for school in schools:
    assert school["id"] > 0 and school["version"] > 0, school
    assert school["platform"] == 1 and school["school_type"] == 1001, school
    assert school["name"] == "Riverside Primary School", school
    assert school["address"] == "12 River Road, Springfield", school
    assert school["student_capacity"] == 800 and school["active"] == 1, school
    assert str(school["established_date"])[:10] == "1995-09-01", school
    assert school["create_time"] is not None and school["update_time"] is not None, school
connection.close()
print(json.dumps({"roots": roots, "constants": constants, "schools": schools}, sort_keys=True))
