from pathlib import Path
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.executemany("INSERT INTO users(name,email,password_hash,role) VALUES(?,?,?,?)",[('معد','a@test','x','executive-director'),('معتمد','b@test','x','general-manager')])
preparer,approver=[r[0] for r in db.execute('SELECT id FROM users ORDER BY id')]
db.execute("INSERT INTO fiscal_periods(period_code,starts_on,ends_on) VALUES('2026-07','2026-07-01','2026-07-31')")
db.execute("INSERT INTO journal_entries(entry_code,entry_date,description,status,posted_by) VALUES('JE1','2026-07-10','قيد','posted',?)",(preparer,))
original=db.execute('SELECT id FROM journal_entries').fetchone()[0]
db.executemany('INSERT INTO journal_entry_lines(journal_entry_id,account_code,debit,credit) VALUES(?,?,?,?)',[(original,'1000',100,0),(original,'4000',0,100)])
db.execute("INSERT INTO journal_entries(entry_code,entry_date,description,status,posted_by) VALUES('REV1','2026-07-11','عكس','posted',?)",(approver,))
reverse=db.execute("SELECT id FROM journal_entries WHERE entry_code='REV1'").fetchone()[0]
db.execute('INSERT INTO journal_entry_lines(journal_entry_id,account_code,debit,credit) SELECT ?,account_code,credit,debit FROM journal_entry_lines WHERE journal_entry_id=?',(reverse,original))
db.execute('INSERT INTO journal_reversals(original_entry_id,reversal_entry_id,reason,reversed_by) VALUES(?,?,?,?)',(original,reverse,'تصحيح',approver))
for entry in (original,reverse):
    debit,credit=db.execute('SELECT SUM(debit),SUM(credit) FROM journal_entry_lines WHERE journal_entry_id=?',(entry,)).fetchone()
    assert abs(debit-credit)<0.01

db.execute("INSERT INTO vat_returns(return_code,period_code,starts_on,ends_on,output_vat,input_vat,adjustment_amount,net_vat,status,prepared_by,approved_by,due_on) VALUES('VAT1','Q3','2026-07-01','2026-09-30',150,60,-10,80,'approved',?,?,'2026-10-31')",(preparer,approver))
vat=db.execute('SELECT output_vat-input_vat+adjustment_amount,net_vat,prepared_by,approved_by FROM vat_returns').fetchone()
assert vat[0]==vat[1] and vat[2]!=vat[3]
db.execute("INSERT INTO withholding_tax_entries(wht_code,payment_date,service_type,taxable_amount,rate_percent,tax_amount,status,created_by,approved_by) VALUES('WHT1','2026-07-20','خدمات',1000,5,50,'approved',?,?)",(preparer,approver))
base,rate,tax=db.execute('SELECT taxable_amount,rate_percent,tax_amount FROM withholding_tax_entries').fetchone()
assert round(base*rate/100,2)==tax
db.execute("UPDATE fiscal_periods SET status='closed',closed_by=?,closed_at=CURRENT_TIMESTAMP WHERE period_code='2026-07'",(approver,))
assert db.execute("SELECT COUNT(*) FROM journal_entries je JOIN fiscal_periods fp ON je.entry_date BETWEEN fp.starts_on AND fp.ends_on WHERE fp.status='closed' AND je.status<>'posted'").fetchone()[0]==0
print('OK: balanced journal reversal, fiscal close, VAT dual approval, withholding calculation')