"""Run the original SQL exercise with Python's bundled SQLite; no packages."""
import json
import pathlib
import sqlite3

p = pathlib.Path(__file__).resolve().parent
db = sqlite3.connect(':memory:')
db.executescript((p / 'setup.sql').read_text())
rows = db.execute((p / 'solution.sql').read_text()).fetchall()
expected = [('chat', 3, 3, 0, 15.0, 2, 66.7), ('email', 3, 2, 1, 115.0, 1, 33.3)]
assert rows == expected, (rows, expected)
assert db.execute('SELECT count(*) FROM messages WHERE message_key="b"').fetchone()[0] == 2
assert db.execute('SELECT count(*) FROM messages WHERE sent_min IS NULL').fetchone()[0] == 1
checks = {'sqlite_version': sqlite3.sqlite_version, 'baseline': rows, 'passed': True}
# A second delivery of an existing answer must not change ticket-level metrics.
db.execute("INSERT INTO messages VALUES (13,'e',2,40,'agent',1)")
assert db.execute((p / 'solution.sql').read_text()).fetchall() == expected
checks['duplicate_delivery_invariance'] = True
# A ticket without a reply remains in the denominator.
db.execute("INSERT INTO tickets VALUES (7,'email',0)")
email = db.execute((p / 'solution.sql').read_text()).fetchall()[1]
assert email == ('email',4,2,2,115.0,1,25.0), email
checks['new_unanswered_ticket'] = email
db.execute("INSERT INTO tickets VALUES (8,'chat',300)")
assert db.execute((p / 'solution.sql').read_text()).fetchall()[0] == expected[0]
checks['future_ticket_excluded'] = True
text = json.dumps(checks, ensure_ascii=False, indent=2)
(p / 'execution-log.json').write_text(text + '\n')
print(text)
