"""Run real SQLite statements against three SaaS relationship schemas.

Python standard library only. Each case starts with a fresh in-memory database.
Run: python3 experiment.py > results.json
No network, credentials, persistent database or third-party packages.
"""
import json
import platform
import sqlite3
from contextlib import closing

SCHEMAS = ("id_only", "composite_nullable", "composite_required")
CASES = (
    ("same_tenant_insert", "INSERT INTO tasks VALUES (1, 'a', 101)", False),
    ("cross_tenant_insert", "INSERT INTO tasks VALUES (1, 'a', 202)", False),
    ("missing_parent", "INSERT INTO tasks VALUES (1, 'a', 999)", False),
    ("null_tenant", "INSERT INTO tasks VALUES (1, NULL, 202)", False),
    ("null_project", "INSERT INTO tasks VALUES (1, 'a', NULL)", False),
    ("cross_tenant_update", "UPDATE tasks SET project_id=202 WHERE id=1", True),
    ("child_tenant_change", "UPDATE tasks SET tenant_id='b' WHERE id=1", True),
    ("delete_referenced_parent", "DELETE FROM projects WHERE id=101", True),
)
EXPECTED = {
    "id_only": [True, True, False, True, True, True, True, False],
    "composite_nullable": [True, False, False, True, True, False, False, False],
    "composite_required": [True, False, False, False, False, False, False, False],
}


def database(schema, enabled=True):
    db = sqlite3.connect(":memory:", isolation_level=None)
    db.execute(f"PRAGMA foreign_keys={'ON' if enabled else 'OFF'}")
    assert db.execute("PRAGMA foreign_keys").fetchone()[0] == int(enabled)
    db.executescript("""
        CREATE TABLE projects (
          id INTEGER PRIMARY KEY,
          tenant_id TEXT NOT NULL,
          UNIQUE(tenant_id, id)
        );
        INSERT INTO projects VALUES (101, 'a'), (202, 'b');
    """)
    required = " NOT NULL" if schema == "composite_required" else ""
    fk = ("FOREIGN KEY(project_id) REFERENCES projects(id)"
          if schema == "id_only" else
          "FOREIGN KEY(tenant_id, project_id) REFERENCES projects(tenant_id, id)")
    db.execute(f"""CREATE TABLE tasks (
        id INTEGER PRIMARY KEY,
        tenant_id TEXT{required},
        project_id INTEGER{required},
        {fk} ON DELETE RESTRICT ON UPDATE RESTRICT
    )""")
    db.execute("CREATE INDEX tasks_project ON tasks(tenant_id, project_id)")
    return db


def mutation(db, sql):
    try:
        db.execute(sql)
        return {"accepted": True, "error": None}
    except sqlite3.IntegrityError as error:
        return {"accepted": False, "error": error.sqlite_errorname}


def run():
    rows = []
    for schema in SCHEMAS:
        for index, (case, sql, seed) in enumerate(CASES):
            with closing(database(schema)) as db:
                if seed:
                    db.execute("INSERT INTO tasks VALUES (1, 'a', 101)")
                outcome = mutation(db, sql)
                assert outcome["accepted"] is EXPECTED[schema][index], (schema, case)
                rows.append({"schema": schema, "case": case, "sql": sql, **outcome,
                             "tasks_after": db.execute("SELECT * FROM tasks ORDER BY id").fetchall(),
                             "projects_after": db.execute("SELECT * FROM projects ORDER BY id").fetchall()})

    with closing(database("composite_required", enabled=False)) as db:
        db.execute("BEGIN")
        db.execute("PRAGMA foreign_keys=ON")
        during_transaction = db.execute("PRAGMA foreign_keys").fetchone()[0]
        assert during_transaction == 0
        disabled = mutation(db, "INSERT INTO tasks VALUES (1, 'a', 202)")
        db.execute("COMMIT")
        db.execute("PRAGMA foreign_keys=ON")
        violations = db.execute("PRAGMA foreign_key_check").fetchall()
        assert disabled["accepted"] and len(violations) == 1

    db = sqlite3.connect(":memory:", isolation_level=None)
    db.execute("PRAGMA foreign_keys=ON")
    db.executescript("""
        CREATE TABLE parents(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL);
        CREATE TABLE children(tenant_id TEXT NOT NULL, parent_id INTEGER NOT NULL,
          FOREIGN KEY(tenant_id, parent_id) REFERENCES parents(tenant_id, id));
        INSERT INTO parents VALUES(101, 'a');
    """)
    try:
        db.execute("INSERT INTO children VALUES('a', 101)")
        raise AssertionError("Missing parent composite uniqueness was accepted")
    except sqlite3.OperationalError as error:
        assert "foreign key mismatch" in str(error)
        missing_unique = error.sqlite_errorname
    db.close()

    return {
        "runtime": {"python": platform.python_version(), "sqlite": sqlite3.sqlite_version},
        "scope": "Actual in-memory SQLite constraints, separate fresh database per matrix cell",
        "cases": rows,
        "invalid_required_writes": {
            schema: sum(row["accepted"] for row in rows if row["schema"] == schema and
                        row["case"] in {"cross_tenant_insert", "null_tenant", "null_project",
                                        "cross_tenant_update", "child_tenant_change"})
            for schema in SCHEMAS},
        "invalid_required_writes_per_schema": 5,
        "additional_checks": {
            "disabled_enforcement": disabled,
            "enable_inside_transaction_value": during_transaction,
            "foreign_key_check_after_enable": violations,
            "parent_without_composite_unique": missing_unique,
        },
    }


if __name__ == "__main__":
    print(json.dumps(run(), indent=2) )