# Offline, in-memory SQLite verification of the unchanged synthetic pack.
import hashlib
import json
from pathlib import Path
import sqlite3

root = Path(__file__).parent
expected_hashes = {'fixture.sql': 'ea22bd66ff6182552a4c726dbba7a2634c5e49e0726ab2b471165a1bc1736250', 'correct-query.sql': 'bce555fad1354c7b3dde4a286bfdee0fe2ea9b98b93b229c06b23112f8cbf197', 'overcounting-query.sql': 'b5b71cb2aff59ee818442585c14410c3a538181064f92a6b14fb7bf90871d97f'}
texts = {}
for name, expected in expected_hashes.items():
    text = (root / name).read_text()
    if hashlib.sha256(text.encode()).hexdigest() != expected:
        raise SystemExit("Refusing modified SQL: use the unchanged synthetic fixture.")
    texts[name] = text
connection = sqlite3.connect(":memory:")
try:
    connection.executescript(texts["fixture.sql"])
    assert connection.execute("PRAGMA foreign_keys").fetchone()[0] == 1
    correct_rows = connection.execute(texts["correct-query.sql"]).fetchall()
    overcounting_rows = connection.execute(texts["overcounting-query.sql"]).fetchall()
    assert correct_rows == [(1, 3900, 2), (2, 2700, 1)]
    assert overcounting_rows == [(1, 4200, 3), (2, 2700, 1)]
    rejected = []
    for label, statement in [
        ("foreign_key", "INSERT INTO orders VALUES (999, 999, 0)"),
        ("positive_quantity", "INSERT INTO order_items VALUES (9999, 101, 0, 100)")
    ]:
        try:
            connection.execute(statement)
        except sqlite3.IntegrityError:
            rejected.append(label)
        else:
            raise AssertionError("Expected constraint rejection: " + label)
    print(json.dumps({"fixture": "D18 synthetic only", "sqlite_version": sqlite3.sqlite_version,
        "correct_rows": correct_rows, "overcounting_rows": overcounting_rows,
        "constraints_rejected": rejected, "database": "in-memory", "network_calls": 0}))
finally:
    connection.close()