"""Exercise the production tenant and capability SQL with two Provinces."""
from pathlib import Path
import re
import sqlite3

root = Path(__file__).resolve().parents[1]
tenant = (root / 'private/BookingSystem/tenant-context.php').read_text()
capabilities = (root / 'private/BookingSystem/capabilities.php').read_text()
query = re.search(r"\$sql = <<<'SQL'\n(.*?)\nSQL;", tenant, re.S).group(1)
query = 'SELECT * FROM (' + query + ') authorised_units WHERE 1=1 AND slug=?'
grants = re.search(r'\$q=\$pdo->prepare\("(SELECT um.role FROM active_user_memberships.*?)"\);', capabilities).group(1)

db = sqlite3.connect(':memory:')
db.create_function('NOW', 0, lambda: '2026-09-27 12:00:00')
db.executescript('''
CREATE TABLE provinces(id INTEGER PRIMARY KEY,active INTEGER);
CREATE TABLE units(id INTEGER PRIMARY KEY,province_id INTEGER,slug TEXT,active INTEGER,deleted_at TEXT);
CREATE TABLE users(id INTEGER PRIMARY KEY,active INTEGER);
CREATE TABLE user_memberships(user_id INTEGER,scope_type TEXT,unit_id INTEGER,province_id INTEGER,role TEXT,active INTEGER);
CREATE VIEW active_user_memberships AS SELECT * FROM user_memberships WHERE active=1;
CREATE TABLE province_event_hosts(province_id INTEGER,unit_id INTEGER);
CREATE TABLE tenant_roles(role_key TEXT,active INTEGER);
CREATE TABLE meetings(id INTEGER PRIMARY KEY,unit_id INTEGER);
CREATE TABLE permission_sets(id INTEGER PRIMARY KEY,active INTEGER);
CREATE TABLE scoped_permission_grants(user_id INTEGER,permission_set_id INTEGER,scope_type TEXT,province_id INTEGER,unit_id INTEGER,event_id INTEGER,active INTEGER,revoked_at TEXT,expires_at TEXT);
INSERT INTO provinces VALUES(1,1),(2,1);
INSERT INTO units VALUES(10,1,'ordinary-a',1,NULL),(11,1,'province-event-a',1,NULL),(20,2,'ordinary-b',1,NULL),(21,2,'province-event-b',1,NULL);
INSERT INTO province_event_hosts VALUES(1,11),(2,21);
INSERT INTO users VALUES(1,1),(2,1),(3,1),(4,1);
INSERT INTO user_memberships VALUES(1,'province',NULL,1,'province_secretary',1),(2,'province',NULL,1,'province_admin',1),(3,'platform',NULL,NULL,'platform_admin',1),(4,'unit',10,NULL,'admin',1);
INSERT INTO tenant_roles VALUES('province_secretary',1),('province_admin',1),('platform_admin',1),('admin',1);
''')

def permitted(user, slug):
    row = db.execute(query, (user, user, slug)).fetchone()
    if row is None:
        return False
    unit, province = row[:2]
    return bool(db.execute(grants, (user, province, unit, unit)).fetchall())

for user, allowed in {
    1: {'province-event-a'},
    2: {'ordinary-a', 'province-event-a'},
    3: {'ordinary-a', 'province-event-a', 'ordinary-b', 'province-event-b'},
    4: {'ordinary-a'},
}.items():
    for slug in ['ordinary-a', 'province-event-a', 'ordinary-b', 'province-event-b']:
        assert permitted(user, slug) == (slug in allowed), (user, slug)
print('Verified 16 cross-Province and ordinary-unit access cases.')