#!/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())

for table in ('employee_service_requests','employee_policies','employee_policy_acknowledgements','employee_evaluations'):
    assert db.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?",(table,)).fetchone()[0]==1,table

db.execute("INSERT INTO companies(code,name,status) VALUES('SAC','سواعد','active')")
company=db.execute("SELECT id FROM companies WHERE code='SAC'").fetchone()[0]
db.execute("INSERT INTO projects(company_id,code,name,status) VALUES(?,'P-1','مشروع 1','active')",(company,))
project=db.execute("SELECT id FROM projects WHERE code='P-1'").fetchone()[0]

def employee(code,email,national_id):
    db.execute("INSERT INTO users(name,email,password_hash,role,is_active) VALUES(?,?,?,'worker',1)",(code,email,'hash'))
    user=db.execute('SELECT last_insert_rowid()').fetchone()[0]
    db.execute("INSERT INTO employees(company_id,project_id,emp_code,national_id,name,job_title,work_email,status) VALUES(?,?,?,?,?,'موظف',?,'active')",(company,project,code,national_id,code,email))
    emp=db.execute('SELECT last_insert_rowid()').fetchone()[0]
    db.execute('INSERT INTO user_employee_links(user_id,employee_id) VALUES(?,?)',(user,emp))
    return user,emp

user_a,emp_a=employee('EMP-A','a@example.test','1000000001')
user_b,emp_b=employee('EMP-B','b@example.test','1000000002')

annual=db.execute("SELECT id FROM leave_types WHERE type_key='annual'").fetchone()[0]
db.execute("INSERT INTO leave_requests(employee_id,leave_type_id,starts_on,ends_on,days_count,status,requested_by) VALUES(?,?,?,?,?,'approved',?)",(emp_a,annual,'2026-01-10','2026-01-14',5,user_a))
used=db.execute("SELECT COALESCE(SUM(days_count),0) FROM leave_requests WHERE employee_id=? AND leave_type_id=? AND status='approved'",(emp_a,annual)).fetchone()[0]
entitlement=db.execute('SELECT annual_days FROM leave_types WHERE id=?',(annual,)).fetchone()[0]
assert entitlement-used==16

db.execute("INSERT INTO employee_service_requests(employee_id,request_type,subject,details,status,requested_by) VALUES(?,'salary_certificate','شهادة راتب','للبنك','pending',?)",(emp_a,user_a))
request=db.execute('SELECT id FROM employee_service_requests WHERE employee_id=?',(emp_a,)).fetchone()[0]
assert db.execute('SELECT COUNT(*) FROM employee_service_requests WHERE employee_id=?',(emp_b,)).fetchone()[0]==0
db.execute("UPDATE employee_service_requests SET status='approved',reviewed_by=?,reviewed_at=CURRENT_TIMESTAMP WHERE id=? AND status='pending'",(user_b,request))
assert db.execute('SELECT status FROM employee_service_requests WHERE id=?',(request,)).fetchone()[0]=='approved'

policy=db.execute("SELECT id FROM employee_policies WHERE policy_code='POL-001'").fetchone()[0]
db.execute('INSERT INTO employee_policy_acknowledgements(policy_id,employee_id,acknowledged_by) VALUES(?,?,?)',(policy,emp_a,user_a))
try:
    db.execute('INSERT INTO employee_policy_acknowledgements(policy_id,employee_id,acknowledged_by) VALUES(?,?,?)',(policy,emp_a,user_a))
    raise AssertionError('duplicate acknowledgement accepted')
except sqlite3.IntegrityError:
    pass

db.execute("INSERT INTO employee_evaluations(employee_id,period_label,overall_score,quality_score,punctuality_score,teamwork_score,safety_score,evaluated_by,published_at) VALUES(?,'Q3 2026',4.4,90,85,94,91,?,CURRENT_TIMESTAMP)",(emp_a,user_b))
assert db.execute('SELECT overall_score FROM employee_evaluations WHERE employee_id=? AND published_at IS NOT NULL',(emp_a,)).fetchone()[0]==4.4

db.execute("INSERT INTO documents(uploaded_by,original_name,stored_name,mime_type,file_size,category,document_code,title,version_no,status) VALUES(?,'id.pdf','stored.pdf','application/pdf',100,'مستند موظف','EMP-DOC-1','هوية',1,'draft')",(user_a,))
document=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO employee_documents(employee_id,document_type,document_id,status) VALUES(?,'هوية',?,'pending_review')",(emp_a,document))
assert db.execute('SELECT COUNT(*) FROM employee_documents WHERE employee_id=?',(emp_a,)).fetchone()[0]==1
assert db.execute('SELECT COUNT(*) FROM employee_documents WHERE employee_id=?',(emp_b,)).fetchone()[0]==0

print('OK: employee self-service isolation, leave balance, requests, policies, evaluations, and documents verified')