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

tables = {r[0] for r in db.execute("SELECT name FROM sqlite_master WHERE type='table'")}
assert {'document_transmittals', 'document_transmittal_items'} <= tables
columns = {r[1] for r in db.execute('PRAGMA table_info(documents)')}
assert {'discipline', 'document_type', 'revision_code', 'review_status', 'review_due_on',
        'review_comments', 'reviewed_by', 'reviewed_at', 'supersedes_document_id', 'is_current'} <= columns

db.execute("INSERT INTO users(name,email,password_hash,role) VALUES('مدير الوثائق','docs@test.local','x','general-manager')")
user = db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO companies(code,name,status) VALUES('SAW','سواعد','active')")
company = db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO projects(company_id,code,name,status) VALUES(?,'P-100','مشروع الاختبار','active')", (company,))
project = db.execute('SELECT last_insert_rowid()').fetchone()[0]

base = (user, 'drawing.pdf', 'rev-a.pdf', 'application/pdf', 1200, 'مخططات', 'P-100', 'DOC-100', 'مخطط معماري')
db.execute("INSERT INTO documents(uploaded_by,original_name,stored_name,mime_type,file_size,category,project_code,document_code,title,version_no,status,approved_by,discipline,document_type,revision_code,review_status,reviewed_by,reviewed_at,is_current) VALUES(?,?,?,?,?,?,?,?,?,1,'approved',?,'معماري','مخطط','A','approved',?,CURRENT_TIMESTAMP,1)", base + (user, user))
rev_a = db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("UPDATE documents SET is_current=0,status='superseded' WHERE id=?", (rev_a,))
base_b = (user, 'drawing.pdf', 'rev-b.pdf', 'application/pdf', 1300, 'مخططات', 'P-100', 'DOC-100', 'مخطط معماري')
db.execute("INSERT INTO documents(uploaded_by,original_name,stored_name,mime_type,file_size,category,project_code,document_code,title,version_no,status,discipline,document_type,revision_code,review_status,review_due_on,supersedes_document_id,is_current) VALUES(?,?,?,?,?,?,?,?,?,2,'draft','معماري','مخطط','B','pending','2026-07-30',?,1)", base_b + (rev_a,))
rev_b = db.execute('SELECT last_insert_rowid()').fetchone()[0]
assert db.execute("SELECT COUNT(*) FROM documents WHERE document_code='DOC-100' AND is_current=1").fetchone()[0] == 1
assert db.execute("SELECT status FROM documents WHERE id=?", (rev_a,)).fetchone()[0] == 'superseded'

db.execute("UPDATE documents SET status='approved',review_status='approved',approved_by=?,reviewed_by=?,reviewed_at=CURRENT_TIMESTAMP WHERE id=? AND is_current=1", (user, user, rev_b))
db.execute("INSERT INTO document_transmittals(transmittal_code,project_id,direction,recipient,purpose,issued_on,response_due_on,status,created_by) VALUES('TR-OUT-1',?,'outbound','الاستشاري','approval','2026-07-21','2026-07-28','draft',?)", (project, user))
transmittal = db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO document_transmittal_items(transmittal_id,document_id,action_required) VALUES(?,?,'اعتماد')", (transmittal, rev_b))
assert db.execute("SELECT COUNT(*) FROM document_transmittal_items i JOIN documents d ON d.id=i.document_id WHERE i.transmittal_id=? AND d.is_current=1", (transmittal,)).fetchone()[0] == 1
db.execute("UPDATE document_transmittals SET status='issued',issued_by=?,issued_at=CURRENT_TIMESTAMP WHERE id=?", (user, transmittal))
db.execute("UPDATE document_transmittals SET status='acknowledged',acknowledged_by=?,acknowledged_at=CURRENT_TIMESTAMP WHERE id=?", (user, transmittal))
db.execute("UPDATE document_transmittal_items SET response_status='approved',response_notes='معتمد' WHERE transmittal_id=?", (transmittal,))
assert db.execute("SELECT t.status,i.response_status FROM document_transmittals t JOIN document_transmittal_items i ON i.transmittal_id=t.id WHERE t.id=?", (transmittal,)).fetchone() == ('acknowledged', 'approved')

api = (ROOT / 'public/api/operations.php').read_text()
upload = (ROOT / 'public/api/upload.php').read_text()
ui = (ROOT / 'public/assets/backend-sync.js').read_text()
page = (ROOT / 'public/app.php').read_text()
for marker in ['approve_document', 'reject_document', 'create_transmittal', 'issue_transmittal', 'acknowledge_transmittal', 'd.is_current<>1']:
    assert marker in api
for marker in ['supersedes_document_id', 'is_current', 'revision_code', 'review_due_on']:
    assert marker in upload
for marker in ['openTransmittalModal', 'issueTransmittal', 'acknowledgeTransmittal', 'rejectDocument']:
    assert marker in ui
for marker in ['documentDiscipline', 'documentType', 'documentRevision', 'documentReviewDue']:
    assert marker in page
print('OK: document revisions, review workflow, superseding, transmittal issue/acknowledgement, and stale-revision guard verified')