from pathlib import Path
import hashlib
import sqlite3

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((root/'database/migrations').glob('*.sqlite.sql')):
    db.executescript(migration.read_text())

db.execute("INSERT INTO companies(code,name) VALUES('SAC','سواعد')")
company=db.execute('SELECT id FROM companies').fetchone()[0]
db.execute("INSERT INTO employees(company_id,emp_code,national_id,name,job_title,work_email,employee_type,started_on) VALUES(?,'EMP-T1','1012345678','موظف اختبار','مهندس','employee@example.test','دوام كامل','2026-07-20')",(company,))
employee=db.execute('SELECT id FROM employees').fetchone()[0]
db.execute("INSERT INTO users(name,email,password_hash,role) VALUES('موظف اختبار','employee@example.test','php-password-hash','worker')")
user=db.execute('SELECT id FROM users').fetchone()[0]
db.execute('INSERT INTO user_employee_links(user_id,employee_id) VALUES(?,?)',(user,employee))
assert db.execute('SELECT u.id FROM users u JOIN user_employee_links l ON l.user_id=u.id JOIN employees e ON e.id=l.employee_id WHERE e.national_id=?',('1012345678',)).fetchone()[0]==user

try:
    db.execute("INSERT INTO employees(company_id,emp_code,national_id,name) VALUES(?,'EMP-T2','1012345678','مكرر')",(company,))
    raise AssertionError('duplicate national id accepted')
except sqlite3.IntegrityError:
    pass

raw='a'*64
token_hash=hashlib.sha256(raw.encode()).hexdigest()
db.execute("INSERT INTO password_reset_tokens(user_id,token_hash,expires_at) VALUES(?,?,datetime('now','+30 minutes'))",(user,token_hash))
assert db.execute('SELECT user_id FROM password_reset_tokens WHERE token_hash=? AND used_at IS NULL AND expires_at>CURRENT_TIMESTAMP',(hashlib.sha256(raw.encode()).hexdigest(),)).fetchone()[0]==user
db.execute('UPDATE password_reset_tokens SET used_at=CURRENT_TIMESTAMP WHERE token_hash=?',(token_hash,))
assert db.execute('SELECT COUNT(*) FROM password_reset_tokens WHERE token_hash=? AND used_at IS NULL',(token_hash,)).fetchone()[0]==0
print('OK: identity login lookup, unique identity, one-to-one employee link, single-use reset token')