#!/usr/bin/env python3
"""QA Orbit, SQLite-CORE-1. stdlib only; never opens an existing DB file."""
from pathlib import Path
import json
import re
import sqlite3

HERE = Path(__file__).resolve().parent
connection = sqlite3.connect(":memory:")
connection.executescript((HERE / "01-fixture.sql").read_text(encoding="utf-8"))
expected = json.loads((HERE / "expected-results.json").read_text(encoding="utf-8"))["queries"]
source = (HERE / "02-queries.sql").read_text(encoding="utf-8")
queries = {key: sql.strip() for key, sql in re.findall(r"-- (Q\d+)\n(.*?)(?=\n\n-- Q|\Z)", source, re.S)}
if set(queries) != set(expected):
    raise AssertionError("Query IDs differ from expected-results.json")
checks = 0
for key, sql in queries.items():
    cursor = connection.execute(sql)
    actual = {"columns": [column[0] for column in cursor.description], "rows": [list(row) for row in cursor.fetchall()]}
    if actual != expected[key]:
        raise AssertionError(f"{key}: {actual!r} != {expected[key]!r}")
    checks += 1
    print(f"PASS {key}: {actual['rows']}")

# Wrong query counterexamples: expected to differ from the valid oracle.
wrong = connection.execute("SELECT u.id, COUNT(t.id) FROM users u LEFT JOIN tasks t ON t.author_id=u.id WHERE t.state='published' GROUP BY u.id ORDER BY u.id").fetchall()
assert wrong == [('u1',2),('u2',1)]
checks += 1
wrong = connection.execute("SELECT u.id, COUNT(t.id) FROM users u LEFT JOIN tasks t ON t.author_id=u.id AND t.state='published' LEFT JOIN comments c ON c.task_id=t.id GROUP BY u.id ORDER BY u.id").fetchall()
assert wrong == [('u1',3),('u2',2),('u3',0),('u4',0)]
checks += 1
assert connection.execute("SELECT id FROM users WHERE id NOT IN (SELECT assignee_id FROM tasks) ORDER BY id").fetchall() == []
checks += 1

# Each invalid mutation has its own savepoint; verify exact snapshot is unchanged.
invalid = [
    ("foreign key", "UPDATE tasks SET assignee_id='missing' WHERE id='t2'"),
    ("check state", "UPDATE tasks SET state='deleted' WHERE id='t2'"),
    ("check estimate", "UPDATE tasks SET estimate=-1 WHERE id='t2'"),
    ("not null", "UPDATE tasks SET title=NULL WHERE id='t2'"),
    ("check title", "UPDATE tasks SET title='' WHERE id='t2'"),
    ("unique", "INSERT INTO users VALUES ('u1','Duplicate')"),
]
for name, sql in invalid:
    before = list(connection.iterdump())
    connection.execute("SAVEPOINT invalid_case")
    try:
        connection.execute(sql)
    except sqlite3.IntegrityError:
        assert list(connection.iterdump()) == before, f"mutation before cleanup: {name}"
    else:
        raise AssertionError(f"Expected constraint failure: {name}")
    finally:
        connection.execute("ROLLBACK TO invalid_case")
        connection.execute("RELEASE invalid_case")
    assert list(connection.iterdump()) == before, name
    checks += 1
    print(f"PASS constraint: {name}")
connection.executescript((HERE / "03-transactions.sql").read_text(encoding="utf-8"))
assert connection.execute("SELECT title,estimate,priority FROM tasks WHERE id='t2'").fetchone() == ('Draft',None,1)
checks += 1
assert connection.execute("PRAGMA foreign_key_check").fetchall() == []
checks += 1
assert connection.execute("PRAGMA integrity_check").fetchall() == [('ok',)]
checks += 1
print(f"PASS {checks} checks; SQLite {sqlite3.sqlite_version}; fresh in-memory database")
connection.close()