"""Tests SQLite schema and key SQL operations without a PHP server."""
from pathlib import Path
import sqlite3, re
source = (Path(__file__).resolve().parents[1] / 'bin' / 'setup.php').read_text()
m = re.search(r"\$pdo->exec\(<<<'SQL'\n(.*?)\nSQL\);", source, flags=re.S)
assert m, 'DDL not found'
con=sqlite3.connect(':memory:')
con.execute('PRAGMA foreign_keys = ON')
con.executescript(m.group(1))
con.execute("INSERT INTO users(username,display_name,password_hash,role,active,created_at) VALUES(?,?,?,?,1,?)",('erwin','Erwin','testhash','admin','2026-10-21 09:00:00'))
con.execute('INSERT INTO questions(category,question,answer,created_at,updated_at,created_by,updated_by) VALUES(?,?,?,?,?,?,?)',('itsme en eID','Hoe werkt itsme op Android?','Open de app en kies activeren','2026-10-21 09:00:00','2026-10-21 09:00:00',1,1))
con.execute("INSERT INTO tags(tag_key,name) VALUES(?,?)", ('itsme','ITSME'))
con.execute('INSERT INTO question_tags(question_id,tag_id) VALUES(1,1)')
for needle in ('itsme','Android','activeren'):
    pattern = '%' + needle + '%'
    result=con.execute("SELECT COUNT(*) FROM questions q WHERE (q.question LIKE ? ESCAPE '\\' OR q.answer LIKE ? ESCAPE '\\' OR EXISTS (SELECT 1 FROM question_tags qt2 JOIN tags t2 ON t2.id=qt2.tag_id WHERE qt2.question_id=q.id AND t2.name LIKE ? ESCAPE '\\'))", (pattern,pattern,pattern)).fetchone()[0]
    assert result==1, needle
con.execute('INSERT INTO visitor_events(visit_date,delta,recorded_at,recorded_by) VALUES (?,?,?,?)',('2026-10-22',1,'2026-10-22 10:00:00',1))
con.execute('INSERT INTO visitor_events(visit_date,delta,recorded_at,recorded_by) VALUES (?,?,?,?)',('2026-10-22',-1,'2026-10-22 10:01:00',1))
assert con.execute('SELECT COALESCE(SUM(delta),0) FROM visitor_events WHERE visit_date=?', ('2026-10-22',)).fetchone()[0]==0
con.execute("UPDATE questions SET answer=?,version=version+1 WHERE id=? AND version=?",('Antwoord bijgewerkt',1,1))
assert con.execute('SELECT version FROM questions').fetchone()[0]==2
con.execute('INSERT INTO roster(shift_date,user_id) VALUES(?,?)',('2026-10-22',1))
assert con.execute('SELECT COUNT(*) FROM roster').fetchone()[0]==1
print('OK: schema, zoekopdracht, tags, bezoekertelling, versiecontrole en rooster')