#!/usr/bin/env python3
import glob
import sqlite3
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys=ON")
db.executescript((ROOT / "database/schema.sqlite.sql").read_text())
for migration in sorted(glob.glob(str(ROOT / "database/migrations/*.sqlite.sql"))):
    db.executescript(Path(migration).read_text())

modules = {
    "reports", "companies", "projects", "attendance", "payroll", "finance", "customers", "tax",
    "supply-chain", "procurement", "suppliers", "subcontractors", "warehouse", "assets-operations",
    "equipment", "maintenance", "contracts", "quality-compliance", "quality", "safety", "documents",
    "approvals", "settings", "supplier-portal", "auditor-portal",
}
marks = ",".join("?" for _ in modules)
rows = list(db.execute(f"SELECT permission_key,module_name FROM permissions WHERE module_name IN ({marks}) AND (permission_key LIKE '%.view' OR permission_key LIKE '%.create' OR permission_key LIKE '%.update')", tuple(modules)))
assert len(rows) == 75, len(rows)
assert {row[1] for row in rows} == modules
for module in modules:
    assert {row[0] for row in rows if row[1] == module} == {f"{module}.view", f"{module}.create", f"{module}.update"}

def role_permissions(role):
    return {r[0] for r in db.execute("SELECT p.permission_key FROM role_permissions rp JOIN roles r ON r.id=rp.role_id JOIN permissions p ON p.id=rp.permission_id WHERE r.role_key=?", (role,))}

assert all(f"{module}.{action}" in role_permissions("general-manager") for module in modules for action in ("view", "create", "update"))
assert {"projects.view", "projects.create", "projects.update"} <= role_permissions("project-manager")
assert "tax.view" not in role_permissions("project-manager")
assert "finance.journal.post" not in role_permissions("project-manager")
assert not any(key.endswith((".view", ".create", ".update")) and key != "ai.view" for key in role_permissions("worker"))
assert {"ai.view", "ai.use"} <= role_permissions("worker")

db.execute("INSERT INTO users(name,email,password_hash,role,is_active) VALUES('اختبار','test@example.com','hash','project-manager',1)")
user_id = db.execute("SELECT id FROM users WHERE email='test@example.com'").fetchone()[0]

def effective_permissions():
    effective = set(role_permissions("project-manager"))
    for key, effect in db.execute("SELECT p.permission_key,up.effect FROM user_permissions up JOIN permissions p ON p.id=up.permission_id WHERE up.user_id=?", (user_id,)):
        effective.add(key) if effect == "allow" else effective.discard(key)
    return effective

permission_id = db.execute("SELECT id FROM permissions WHERE permission_key='projects.update'").fetchone()[0]
db.execute("INSERT INTO user_permissions(user_id,permission_id,effect) VALUES(?,?,'deny')", (user_id, permission_id))
assert "projects.update" not in effective_permissions()
permission_id = db.execute("SELECT id FROM permissions WHERE permission_key='tax.view'").fetchone()[0]
db.execute("INSERT INTO user_permissions(user_id,permission_id,effect) VALUES(?,?,'allow')", (user_id, permission_id))
assert "tax.view" in effective_permissions()
db.execute("DELETE FROM user_permissions WHERE user_id=? AND permission_id=?", (user_id, permission_id))
assert "tax.view" not in effective_permissions()

print("OK: 25 modules, 75 actions, role defaults, and employee allow/deny/inherit overrides verified")