#!/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 f in sorted(glob.glob(str(ROOT/'database/migrations/*.sqlite.sql'))):db.executescript(Path(f).read_text())
tables={r[0] for r in db.execute("SELECT name FROM sqlite_master WHERE type='table'")}
assert {'approval_requests','approval_request_events','approval_request_comments'}<=tables
columns={r[1] for r in db.execute('PRAGMA table_info(approval_requests)')}
assert {'title','description','priority','due_on','project_id','assigned_to','decision_notes','submitted_at','updated_at'}<=columns

db.execute("INSERT INTO users(name,email,password_hash,role) VALUES('طالب','requester@test.local','x','project-manager')");requester=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO users(name,email,password_hash,role) VALUES('معتمد','approver@test.local','x','projects-director')");approver=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO approval_requests(requester_id,request_type,reference_code,amount,current_role,status,title,description,priority,due_on,assigned_to,submitted_at) VALUES(?,'project','APR-1',25000,'projects-director','pending','اعتماد تغيير','تفاصيل القرار','high','2026-07-30',?,CURRENT_TIMESTAMP)",(requester,approver));request=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO approval_request_events(request_id,actor_id,action,to_status) VALUES(?,?,'submitted','pending')",(request,requester))
assert requester==db.execute('SELECT requester_id FROM approval_requests WHERE id=?',(request,)).fetchone()[0]
assert requester!=approver

db.execute("UPDATE approval_requests SET status='returned',decision_notes='أرفق تحليل الأثر',updated_at=CURRENT_TIMESTAMP WHERE id=? AND status='pending'",(request,))
db.execute("INSERT INTO approval_request_events(request_id,actor_id,action,from_status,to_status,notes) VALUES(?,?,'returned','pending','returned','أرفق تحليل الأثر')",(request,approver))
db.execute("UPDATE approval_requests SET status='pending',decision_notes=NULL,submitted_at=CURRENT_TIMESTAMP WHERE id=? AND requester_id=? AND status='returned'",(request,requester))
db.execute("INSERT INTO approval_request_events(request_id,actor_id,action,from_status,to_status) VALUES(?,?,'resubmitted','returned','pending')",(request,requester))
db.execute("INSERT INTO approval_request_comments(request_id,user_id,comment_text) VALUES(?,?,?)",(request,approver,'تمت مراجعة الأثر'))
db.execute("UPDATE approval_requests SET status='approved',approved_by=?,approved_at=CURRENT_TIMESTAMP WHERE id=? AND requester_id<>? AND status='pending'",(approver,request,approver))
db.execute("INSERT INTO approval_request_events(request_id,actor_id,action,from_status,to_status) VALUES(?,?,'approved','pending','approved')",(request,approver))
row=db.execute('SELECT status,requester_id,approved_by FROM approval_requests WHERE id=?',(request,)).fetchone();assert row[0]=='approved' and row[1]!=row[2]
assert db.execute('SELECT COUNT(*) FROM approval_request_events WHERE request_id=?',(request,)).fetchone()[0]==4
assert db.execute('SELECT COUNT(*) FROM approval_request_comments WHERE request_id=?',(request,)).fetchone()[0]==1

api=(ROOT/'public/api/approval-workflow.php').read_text();ui=(ROOT/'public/assets/backend-sync.js').read_text();page=(ROOT/'public/app.php').read_text();generic=(ROOT/'public/api/index.php').read_text()
for marker in ['create_request','decide_request','resubmit_request','cancel_request','add_comment','لا يجوز لصاحب الطلب اعتماد طلبه']:
    assert marker in api
for marker in ['loadApprovalWorkflow','openApprovalRequestModal','decideApprovalRequest','openResubmitApproval','openApprovalDetails']:
    assert marker in ui
assert 'يمنع صاحب الطلب من اعتماد طلبه' in page
assert 'استخدم مسار المهام والموافقات المخصص' in generic
print('OK: approval inbox, assignment, separation of duties, return/resubmit, decisions, comments, and immutable timeline verified')