#!/usr/bin/env python3
import glob,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())
cols={r[1] for r in db.execute('PRAGMA table_info(subcontractors)')}
assert {'legal_name','cr_number','email','trade_categories','license_expiry','insurance_expiry','saudization_percent','safety_classification','rating','risk_level','approved_by','archived_at'}<=cols
assert {'closed_by','closed_at','close_notes'}<={r[1] for r in db.execute('PRAGMA table_info(subcontracts)')}
assert db.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='subcontractor_evaluations'").fetchone()[0]==1
db.execute("INSERT INTO users(name,email,password_hash,role) VALUES('مدير','admin@test.local','x','general-manager')");u=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO companies(code,name,status) VALUES('SAC','سواعد','active')");co=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO projects(company_id,code,name,status) VALUES(?,'PRJ-1','مشروع','active')",(co,));pr=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO subcontractors(code,name,vat_number,cr_number,license_expiry,insurance_expiry,saudization_percent,safety_classification,risk_level,status,approved_by,updated_by) VALUES('SUB-1','مقاول','3100','1010','2027-12-31','2027-12-31',35,'A','medium','active',?,?)",(u,u));party=db.execute('SELECT last_insert_rowid()').fetchone()[0]
scores=(90,95,80,75);overall=round(scores[0]*.30+scores[1]*.30+scores[2]*.25+scores[3]*.15,2);assert overall==86.75
db.execute("INSERT INTO subcontractor_evaluations(subcontractor_id,evaluation_date,quality_score,safety_score,schedule_score,commercial_score,overall_score,evaluated_by) VALUES(?,'2026-07-21',?,?,?,?,?,?)",(party,*scores,overall,u))
db.execute("INSERT INTO subcontracts(contract_code,subcontractor_id,project_id,scope_of_work,contract_value,status,created_by) VALUES('SC-1',?,?,'خرسانة',100000,'active',?)",(party,pr,u));sc=db.execute('SELECT last_insert_rowid()').fetchone()[0]
assert db.execute("SELECT COUNT(*) FROM subcontracts WHERE subcontractor_id=? AND status='active'",(party,)).fetchone()[0]==1
db.execute("UPDATE subcontracts SET status='closed',closed_by=?,closed_at=CURRENT_TIMESTAMP,close_notes='إقفال' WHERE id=?",(u,sc));db.execute("UPDATE subcontractors SET status='archived',archived_at=CURRENT_TIMESTAMP WHERE id=?",(party,))
assert db.execute('SELECT status FROM subcontractors WHERE id=?',(party,)).fetchone()[0]=='archived'
api=(ROOT/'public/api/contracts.php').read_text()
for marker in ['create_subcontractor','update_subcontractor','approve_subcontractor','evaluate_subcontractor','suspend_subcontractor','archive_subcontractor','close_subcontract'] : assert marker in api
print('OK: subcontractor master, qualification, evaluation, contract guard, close, and archive verified')