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

columns=lambda table:{r[1] for r in db.execute(f'PRAGMA table_info({table})')}
assert {'legal_name','address_line','city','postal_code','country_code','phone','email','website','updated_at'} <= columns('companies')
assert {'cr_number','vat_number','address_line','postal_code','phone','email','status','updated_at'} <= columns('branches')
assert db.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='company_bank_accounts'").fetchone()[0]==1

db.execute("INSERT INTO users(name,email,password_hash,role) VALUES('مدير','manager@example.test','hash','general-manager')")
user=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO companies(code,name,legal_name,cr_number,vat_number,city,status) VALUES('SAC','سواعد','شركة سواعد العربية','1010101010','310000000000003','الرياض','active')")
company=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("INSERT INTO branches(company_id,code,name,city,status) VALUES(?,'RUH','المركز الرئيسي','الرياض','active')",(company,))
branch=db.execute('SELECT last_insert_rowid()').fetchone()[0]

iban1='SA0012345678901234567890'
iban2='SA0098765432109876543210'
db.execute("INSERT INTO company_bank_accounts(company_id,branch_id,bank_name,account_name,iban,is_primary,created_by,updated_by) VALUES(?,?,'البنك الأول','سواعد',?,1,?,?)",(company,branch,iban1,user,user))
try:
    db.execute("INSERT INTO company_bank_accounts(company_id,bank_name,account_name,iban,created_by) VALUES(?,'بنك آخر','سواعد',?,?)",(company,iban1,user))
    raise AssertionError('duplicate IBAN accepted')
except sqlite3.IntegrityError:
    pass

db.execute('UPDATE company_bank_accounts SET is_primary=0 WHERE company_id=?',(company,))
db.execute("INSERT INTO company_bank_accounts(company_id,bank_name,account_name,iban,is_primary,created_by,updated_by) VALUES(?,'البنك الثاني','سواعد',?,1,?,?)",(company,iban2,user,user))
assert db.execute("SELECT COUNT(*) FROM company_bank_accounts WHERE company_id=? AND status='active' AND is_primary=1",(company,)).fetchone()[0]==1

db.execute("INSERT INTO projects(company_id,branch_id,code,name,status) VALUES(?,?,'P-1','مشروع مرتبط','active')",(company,branch))
db.execute("INSERT INTO employees(company_id,branch_id,emp_code,name,status) VALUES(?,?,'EMP-1','موظف مرتبط','active')",(company,branch))
links=db.execute("SELECT (SELECT COUNT(*) FROM projects WHERE branch_id=? AND status<>'archived')+(SELECT COUNT(*) FROM employees WHERE branch_id=? AND status IN ('active','نشط'))+(SELECT COUNT(*) FROM company_bank_accounts WHERE branch_id=? AND status='active')",(branch,branch,branch)).fetchone()[0]
assert links==3, 'branch archive guard must detect active links'
company_links=db.execute("SELECT (SELECT COUNT(*) FROM branches WHERE company_id=? AND status='active')+(SELECT COUNT(*) FROM projects WHERE company_id=? AND status<>'archived')+(SELECT COUNT(*) FROM employees WHERE company_id=? AND status IN ('active','نشط'))+(SELECT COUNT(*) FROM company_bank_accounts WHERE company_id=? AND status='active')",(company,company,company,company)).fetchone()[0]
assert company_links==5, 'company archive guard must detect active links'

db.execute("INSERT INTO branches(company_id,code,name,city,status) VALUES(?,'JED','فرع غير مرتبط','جدة','active')",(company,))
free_branch=db.execute('SELECT last_insert_rowid()').fetchone()[0]
db.execute("UPDATE branches SET status='archived' WHERE id=?",(free_branch,))
assert db.execute('SELECT status FROM branches WHERE id=?',(free_branch,)).fetchone()[0]=='archived'
assert db.execute('SELECT COUNT(*) FROM companies WHERE id=?',(company,)).fetchone()[0]==1

print('OK: company/branch master schema, IBAN uniqueness, primary account, and archive guards verified')