"""Smoke + accounting-invariant tests. Run: .venv/bin/python -m pytest -q  (or python tests/test_smoke.py)"""
import os
import re
import sys
import tempfile

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from vertex import create_app
from vertex.db import create_schema, q
from vertex.ledger import balance_sheet, profit_loss, trial_balance
from vertex.seed import seed


def make_app(license_path=None, business="custom"):
    path = os.path.join(tempfile.mkdtemp(), "test.db")
    create_schema(path)
    app = create_app(database=path, license_path=license_path or os.path.join(tempfile.mkdtemp(), "none.json"))
    app.config["CSRF_ENABLED"] = False  # the tests post forms directly; test_security checks the protection
    with app.app_context():
        seed(demo=True, business=business)
    return app


def login(client, user="admin", pwd="admin123"):
    m = re.search(r'name="csrf" value="([^"]+)"', client.get("/login").get_data(as_text=True))
    r = client.post("/login", data={"username": user, "password": pwd, "csrf": m.group(1) if m else ""})
    assert r.status_code == 302, r.status_code


def test_invariants():
    app = make_app()
    with app.app_context():
        ids = [b["id"] for b in q("SELECT id FROM branches")]
        today = q("SELECT MAX(date) FROM moves", one=True)[0]
        # every posted move balances
        bad = q("SELECT move_id FROM move_lines ml JOIN moves m ON m.id = ml.move_id WHERE m.state='posted' "
                "GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        assert not bad
        for sel in [ids] + [[i] for i in ids]:
            tb = trial_balance(sel, "2000-01-01", today)
            if len(sel) == 1:
                assert sum(r["closing"] for r in tb["rows"]) == 0
            bs = balance_sheet(sel, today)
            rows = {r["label"]: r for r in bs.rows if r["kind"] != "section"}
            ta, tle = rows["Total Assets"], rows["Total Liabilities and Equity"]
            assert ta["vals"] == tle["vals"], (sel, ta["vals"], tle["vals"])
            assert ta["total"] == tle["total"], (sel, ta["total"], tle["total"])
            pl = profit_loss(sel, today[:4] + "-01-01", today)
            net = [r for r in pl.rows if r.get("label") == "Net Profit"][0]
            cye = rows["Current Year Earnings"]
            assert net["total"] == cye["total"], (net["total"], cye["total"])
            if len(sel) > 1:
                ic = [r for r in bs.rows if r.get("account") and r["account"]["intercompany"]]
                assert ic and all(r["total"] == 0 for r in ic)
        # stock valuation equals the inventory account, per branch
        from vertex.stock import inventory_account_map, stock_value_by_branch
        values = stock_value_by_branch(ids)
        for b in ids:
            gl = q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                   "JOIN accounts a ON a.id = ml.account_id WHERE m.state = 'posted' AND a.code IN ('1200', '1205') "
                   "AND ml.branch_id = ?", (b,), one=True)[0]
            assert values.get(b, 0) == gl, (b, values.get(b), gl)
            # and per account: spare parts sit on their own inventory account
            accs = inventory_account_map()
            for code in ("1200", "1205"):
                aid = q("SELECT id FROM accounts WHERE code = ?", (code,), one=True)[0]
                stock = q("SELECT COALESCE(SUM(sq.value), 0) FROM stock_quants sq JOIN warehouses w ON w.id = sq.warehouse_id "
                          "WHERE w.branch_id = ? AND sq.product_id IN (%s)" % (",".join(str(p) for p, a in accs.items()
                                                                                if a == aid) or "NULL"), (b,), one=True)[0]
                gl_a = q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                         "WHERE m.state = 'posted' AND ml.account_id = ? AND ml.branch_id = ?", (aid, b), one=True)[0]
                assert stock == gl_a, (b, code, stock, gl_a)
        assert any(r[0] for r in q("SELECT SUM(debit) FROM move_lines ml JOIN accounts a ON a.id = ml.account_id "
                                   "WHERE a.code = '1205'"))
        assert not q("SELECT 1 FROM stock_quants WHERE qty < -0.000001")
        # asset accumulated depreciation equals posted lines; disposed asset fully removed
        for a in q("SELECT * FROM assets WHERE state = 'disposed'"):
            assert a["disposal_move_id"]
        # each prepaid schedule's lines add up to its total
        for s_ in q("SELECT * FROM prepaid_schedules WHERE state != 'draft'"):
            assert q("SELECT SUM(amount) FROM prepaid_lines WHERE schedule_id = ?", (s_["id"],), one=True)[0] == s_["total"]
        for a in q("SELECT * FROM assets WHERE state = 'running'"):
            last = q("SELECT book_value FROM asset_lines WHERE asset_id = ? ORDER BY date DESC LIMIT 1", (a["id"],), one=True)
            assert last["book_value"] == a["salvage"]


def test_pages():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        inv = q("SELECT id FROM invoices WHERE state='posted' LIMIT 1", one=True)["id"]
        move = q("SELECT id FROM moves LIMIT 1", one=True)["id"]
        pay = q("SELECT id FROM payments LIMIT 1", one=True)["id"]
        bid = q("SELECT id FROM branches LIMIT 1", one=True)["id"]
        vendor = q("SELECT id FROM partners WHERE name_en = 'Gulf Safety Equipment Co.'", one=True)["id"]
    pages = ["/", "/entries/", "/entries/new", f"/entries/{move}", "/invoices/out_invoice", "/invoices/in_invoice",
             "/invoices/out_refund", "/invoices/in_refund", "/invoices/out_invoice/new", f"/invoices/view/{inv}",
             f"/invoices/print/{inv}", "/payments/", "/payments/new", f"/payments/new?invoice_id={inv}",
             f"/payments/{pay}", "/transfers/", "/transfers/new", "/reports/profit-loss", "/reports/balance-sheet",
             "/reports/trial-balance", "/reports/general-ledger", "/reports/aged/receivable?detail=1",
             "/reports/aged/payable", "/reports/vat", "/reports/balance-sheet?export=csv", f"/reports/profit-loss?b={bid}",
             "/setup/company", "/setup/branches", "/setup/accounts", "/setup/journals", "/setup/taxes",
             "/setup/currencies", "/setup/rates", "/setup/partners?type=customer", "/setup/partners/new",
             "/setup/branches/1", "/setup/rates/1", "/setup/users", "/setup/users/1", "/setup/audit", "/password",
             "/orders/sale", "/orders/purchase", "/orders/sale/new", "/orders/purchase/new", "/orders/view/1",
             "/orders/print/1", "/dashboard", "/inventory/stock", "/inventory/transfers", "/inventory/transfers/1", "/inventory/adjust",
             "/inventory/moves", "/setup/products", "/setup/products/1", "/setup/warehouses", "/setup/asset_categories",
             "/procurement/mr", "/procurement/mr/new", "/procurement/mr/1", "/procurement/pr", "/procurement/pr/new",
             "/procurement/pr/1", "/procurement/rfq", "/procurement/rfq/new", "/procurement/rfq/1",
             "/procurement/rfq/1/quote/1", "/procurement/rfq/1/print/1", "/vendors/", "/vendors/documents",
             f"/vendors/{vendor}", "/assets/", "/assets/new", "/assets/1", "/assets/4", f"/assets/new?invoice_id={inv}",
             "/prepaid/", "/prepaid/new?kind=accrual", "/prepaid/1", f"/prepaid/new?kind=prepaid&invoice_id={inv}",
             "/closing/", "/setup/license", "/reports/sales", "/reports/sales?by=customer", "/reports/sales?by=month",
             "/crm/", "/crm/list", "/crm/new", "/crm/1", "/crm/activities", "/crm/activities?owner=all", "/crm/report",
             "/pos/", "/pos/register/new", "/pos/register/1", "/pos/session/1/close", "/pos/session/1/orders",
             "/pos/session/2", "/pos/order/1/receipt", "/bank/", "/bank/new", "/bank/1", "/bank/report"]
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for p in pages:
            r = c.get(p)
            assert r.status_code == 200, (lang, p, r.status_code)
            if lang == "ar" and "print" not in p and "export" not in p and "receipt" not in p:
                assert 'dir="rtl"' in r.get_data(as_text=True), p


def test_workflow():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        b = q("SELECT * FROM branches WHERE code='DXB'", one=True)
        j = q("SELECT id FROM journals WHERE branch_id=? AND code='INV'", (b["id"],), one=True)["id"]
        bank = q("SELECT id FROM journals WHERE branch_id=? AND code='BNK'", (b["id"],), one=True)["id"]
        p = q("SELECT id FROM partners WHERE is_customer=1 LIMIT 1", one=True)["id"]
        acc = q("SELECT id FROM accounts WHERE code='4100'", one=True)["id"]
        tx = q("SELECT id FROM taxes WHERE name_en='VAT 5% (Sales)'", one=True)["id"]
    r = c.post("/invoices/out_invoice/new", data={
        "branch_id": b["id"], "journal_id": j, "partner_id": p, "date": "2026-10-01", "due_date": "2026-10-31",
        "description": ["Widget"], "account_id": [acc], "quantity": ["3"], "price_unit": ["100.50"],
        "tax_id": [tx], "action": "post"})
    assert r.status_code == 302
    inv_id = int(re.search(r"/view/(\d+)", r.headers["Location"]).group(1))
    with app.app_context():
        inv = q("SELECT * FROM invoices WHERE id=?", (inv_id,), one=True)
        assert inv["state"] == "posted" and inv["amount_total"] == 316580  # 301.50 + 15.08
    r = c.post(f"/payments/new?invoice_id={inv_id}", data={
        "branch_id": b["id"], "journal_id": bank, "partner_id": p, "partner_type": "customer",
        "payment_type": "inbound", "date": "2026-10-05", "amount": "316.58"})
    assert r.status_code == 302
    r = c.get(f"/invoices/view/{inv_id}")
    assert "Paid" in r.get_data(as_text=True)
    # unbalanced entry is refused
    r = c.post("/entries/new", data={"branch_id": b["id"], "journal_id": bank, "date": "2026-10-01",
                                     "account_id": [acc, acc], "partner_id": ["", ""], "label": ["", ""],
                                     "debit": ["10", ""], "credit": ["", "9"], "action": "post"})
    assert "not balanced" in r.get_data(as_text=True)
    # the Dubai accountant cannot see Riyadh
    c2 = app.test_client()
    login(c2, "dubai", "dubai123")
    with app.app_context():
        ruh_inv = q("SELECT i.id FROM invoices i JOIN branches b ON b.id=i.branch_id WHERE b.code='RUH' LIMIT 1",
                    one=True)["id"]
    assert c2.get(f"/invoices/view/{ruh_inv}").status_code == 302
    # viewer cannot create
    c3 = app.test_client()
    login(c3, "viewer", "viewer123")
    assert c3.get("/entries/new").status_code == 302


def test_procurement_assets_prepaid():
    app = make_app()
    c = app.test_client()
    login(c, "accountant", "account123")
    with app.app_context():
        ruh = q("SELECT id FROM branches WHERE code='RUH'", one=True)["id"]
        wh = q("SELECT id FROM warehouses WHERE branch_id=?", (ruh,), one=True)["id"]
        prod = q("SELECT id FROM products WHERE code='IND-011'", one=True)["id"]
        prospect = q("SELECT id FROM partners WHERE vendor_status='prospect'", one=True)["id"]
        gulf = q("SELECT id FROM partners WHERE name_en='Gulf Safety Equipment Co.'", one=True)["id"]
        before = q("SELECT qty FROM stock_quants WHERE product_id=? AND warehouse_id=?", (prod, wh), one=True)["qty"]
    # requisition: accountant submits, cannot approve
    r = c.post("/procurement/pr/new", data={"branch_id": ruh, "date": "2026-10-01", "department": "Ops",
                                            "product_id": [prod], "description": ["Grinder"], "quantity": ["10"],
                                            "est_price": ["9000"], "vendor_id": [""], "action": "submit"})
    pr_id = int(re.search(r"/pr/(\d+)", r.headers["Location"]).group(1))
    c.post(f"/procurement/pr/{pr_id}/approve")
    with app.app_context():
        assert q("SELECT state FROM purchase_requisitions WHERE id=?", (pr_id,), one=True)[0] == "submitted"
    m = app.test_client()
    login(m, "manager", "manager123")
    m.post(f"/procurement/pr/{pr_id}/approve")
    # PO to a prospect vendor is refused; to an approved vendor it is created
    r = m.post(f"/procurement/pr/{pr_id}/po", data={"vendor_id": prospect, "warehouse_id": wh}, follow_redirects=True)
    assert "approved vendor" in r.get_data(as_text=True)
    r = m.post(f"/procurement/pr/{pr_id}/po", data={"vendor_id": gulf, "warehouse_id": wh})
    po_id = int(re.search(r"/view/(\d+)", r.headers["Location"]).group(1))
    # 10 x 9,000 + VAT is above the 50,000 approval limit: the accountant's confirmation waits for approval
    c.post(f"/orders/view/{po_id}/confirm")
    with app.app_context():
        assert q("SELECT state FROM orders WHERE id=?", (po_id,), one=True)[0] == "to_approve"
    m.post(f"/orders/view/{po_id}/approve")
    with app.app_context():
        assert q("SELECT state FROM orders WHERE id=?", (po_id,), one=True)[0] == "confirmed"
        pk = q("SELECT * FROM pickings WHERE order_id=?", (po_id,), one=True)
        line = q("SELECT id FROM picking_lines WHERE picking_id=?", (pk["id"],), one=True)["id"]
    # receive 6 of 10: a backorder of 4 remains
    c.post(f"/inventory/transfers/{pk['id']}", data={"action": "validate", "date": "2026-10-02", f"qty_{line}": "6"})
    with app.app_context():
        assert q("SELECT qty FROM stock_quants WHERE product_id=? AND warehouse_id=?", (prod, wh), one=True)["qty"] == before + 6
        back = q("SELECT pl.quantity FROM pickings p JOIN picking_lines pl ON pl.picking_id = p.id "
                 "WHERE p.order_id=? AND p.state='ready'", (po_id,), one=True)
        assert back["quantity"] == 4
    # asset: straight line 12,000 over 12 months -> 1,000 a month
    with app.app_context():
        cat = q("SELECT id FROM asset_categories WHERE method='linear' LIMIT 1", one=True)["id"]
    r = c.post("/assets/new", data={"name_en": "Test asset", "category_id": cat, "branch_id": ruh,
                                    "acquisition_date": "2026-01-01", "start_date": "2026-01-01", "cost": "12000",
                                    "method": "linear", "life_months": "12"})
    aid = int(re.search(r"/assets/(\d+)", r.headers["Location"]).group(1))
    c.post(f"/assets/{aid}/confirm")
    c.post(f"/assets/{aid}/depreciate", data={"date": "2026-03-31"})
    with app.app_context():
        lines = q("SELECT * FROM asset_lines WHERE asset_id=? ORDER BY date", (aid,))
        assert len(lines) == 12 and all(ln["amount"] == 1000000 for ln in lines)
        assert sum(1 for ln in lines if ln["move_id"]) == 3
    # accrual with automatic reversal
    with app.app_context():
        a2300 = q("SELECT id FROM accounts WHERE code='2300'", one=True)["id"]
        a6300 = q("SELECT id FROM accounts WHERE code='6300'", one=True)["id"]
    r = c.post("/prepaid/new?kind=accrual", data={"kind": "accrual", "name": "Test accrual", "branch_id": ruh,
                                                  "balance_account_id": a2300, "pl_account_id": a6300, "total": "300",
                                                  "start_date": "2026-01-01", "months": "3", "auto_reverse": "1",
                                                  "action": "confirm"})
    sid = int(re.search(r"/prepaid/(\d+)", r.headers["Location"]).group(1))
    c.post(f"/prepaid/{sid}/post", data={"date": "2026-12-31"})
    with app.app_context():
        assert q("SELECT state FROM prepaid_schedules WHERE id=?", (sid,), one=True)[0] == "done"
        bal = q("SELECT COALESCE(SUM(ml.debit - ml.credit), 0) FROM move_lines ml JOIN moves mv ON mv.id = ml.move_id "
                "WHERE mv.state='posted' AND mv.ref = (SELECT number FROM prepaid_schedules WHERE id=?) AND ml.account_id=?",
                (sid, a2300), one=True)[0]
        assert bal == 0  # every accrual was reversed


def test_license_modules():
    from vertex.modules import make_license
    import json
    lic_path = os.path.join(tempfile.mkdtemp(), "license.json")
    with open(lic_path, "w") as f:
        json.dump(make_license("Acme", ["sales"], max_users=5), f)
    app = make_app(lic_path)
    c = app.test_client()
    login(c)
    html = c.get("/").get_data(as_text=True)
    # only licensed apps appear; modules not bought leave no trace on the home screen
    assert "/app/sales" in html and "Fixed Assets" not in html and "Available to add" not in html
    html = c.get("/orders/sale").get_data(as_text=True)
    assert "Quotations" in html and "Material Requests" not in html and "/assets/" not in html
    assert c.get("/procurement/mr").status_code == 302
    assert c.get("/assets/").status_code == 302
    assert c.get("/orders/sale").status_code == 200
    assert c.get("/orders/purchase").status_code == 302
    # tampered license falls back to accounting only
    with open(lic_path) as f:
        data = json.load(f)
    data["modules"].append("assets")
    with open(lic_path, "w") as f:
        json.dump(data, f)
    app2 = create_app(database=app.config["DATABASE"], license_path=lic_path)
    assert app2.config["LICENSE"].modules == {"accounting"} and app2.config["LICENSE"].read_only


def test_crm_pos_bank():
    app = make_app()
    c = app.test_client()
    login(c, "manager", "manager123")
    with app.app_context():
        ruh = q("SELECT id FROM branches WHERE code='RUH'", one=True)["id"]
        reg = q("SELECT id FROM pos_registers WHERE branch_id=?", (ruh,), one=True)["id"]
        helmet = q("SELECT * FROM products WHERE code='SAF-001'", one=True)
        wh = q("SELECT warehouse_id FROM pos_registers WHERE id=?", (reg,), one=True)[0]
        before = q("SELECT qty FROM stock_quants WHERE product_id=? AND warehouse_id=?", (helmet["id"], wh), one=True)["qty"]
    # CRM: lead -> quotation -> confirm marks the lead won
    r = c.post("/crm/new", data={"title": "Test deal", "company_name": "New Co", "branch_id": ruh, "stage": "new",
                                 "expected_revenue": "5000"})
    lid = int(re.search(r"/crm/(\d+)", r.headers["Location"]).group(1))
    c.post(f"/crm/{lid}/stage", data={"stage": "qualified"})
    r = c.post(f"/crm/{lid}/quotation")
    oid = int(re.search(r"/sale/(\d+)/edit", r.headers["Location"]).group(1))
    with app.app_context():
        vat = q("SELECT id FROM taxes WHERE name_en='VAT 15% (Sales)'", one=True)["id"]
        assert q("SELECT stage FROM leads WHERE id=?", (lid,), one=True)[0] == "proposal"
    c.post(f"/orders/sale/{oid}/edit", data={"partner_id": q_partner(app, lid), "branch_id": ruh, "warehouse_id": wh,
                                             "date": "2026-10-20", "product_id": [helmet["id"]], "description": ["Helmet"],
                                             "quantity": ["2"], "price_unit": ["85"], "tax_id": [vat], "action": "confirm"})
    with app.app_context():
        assert q("SELECT stage FROM leads WHERE id=?", (lid,), one=True)[0] == "won"
    # POS: sell 3 helmets for cash, card sale, refund, close short by 1.00
    r = c.post(f"/pos/register/{reg}/open", data={"opening_cash": "200"})
    sid = int(re.search(r"/session/(\d+)", r.headers["Location"]).group(1))
    r = c.post(f"/pos/session/{sid}/order", json={"items": [{"product_id": helmet["id"], "qty": 3}], "cash": "300"})
    d = r.get_json()
    assert d["ok"], d
    # 3 x 85 = 255 + 15% = 293.25; change 6.75
    assert d["total"] == "293.25" and d["change"] == "6.75"
    r = c.post(f"/pos/session/{sid}/order", json={"items": [{"product_id": helmet["id"], "qty": 1}], "card": "97.75"})
    assert r.get_json()["ok"]
    r = c.post(f"/pos/session/{sid}/order", json={"items": [{"product_id": helmet["id"], "qty": 1}], "cash": "1"})
    assert not r.get_json()["ok"]  # not enough cash
    r = c.post(f"/pos/session/{sid}/order", json={"items": [{"product_id": helmet["id"], "qty": 100000}]})
    assert "Not enough stock" in r.get_json()["error"]
    with app.app_context():
        card_order = q("SELECT id FROM pos_orders WHERE session_id=? AND paid_card > 0", (sid,), one=True)["id"]
    c.post(f"/pos/order/{card_order}/refund")
    # the card sale is refunded to the card, so expected cash = 200 opening + 293.25
    c.post(f"/pos/session/{sid}/close", data={"counted_cash": "492.25"})
    with app.app_context():
        s_ = q("SELECT * FROM pos_sessions WHERE id=?", (sid,), one=True)
        assert s_["state"] == "closed" and s_["move_id"]
        lines = q("SELECT * FROM move_lines WHERE move_id=?", (s_["move_id"],))
        assert sum(l["debit"] for l in lines) == sum(l["credit"] for l in lines)
        short = q("SELECT SUM(debit - credit) FROM move_lines WHERE move_id=? AND account_id=(SELECT id FROM accounts "
                  "WHERE code='6950')", (s_["move_id"],), one=True)[0]
        assert short == 1000  # 1.00 short
        after = q("SELECT qty FROM stock_quants WHERE product_id=? AND warehouse_id=?", (helmet["id"], wh), one=True)["qty"]
        assert after == before - 3  # POS: 3 + 1 sold, 1 refunded; the sales order delivery is still pending
    # the Dubai branch's 5% VAT replaces the product's 15%
    with app.app_context():
        dxb_session = q("SELECT s.id FROM pos_sessions s JOIN branches b ON b.id = s.branch_id "
                        "WHERE b.code = 'DXB' AND s.state = 'open'", one=True)["id"]
    r = c.post(f"/pos/session/{dxb_session}/order", json={"items": [{"product_id": helmet["id"], "qty": 2}]})
    d = r.get_json()
    assert d["ok"] and d["total"] == "178.50", d  # 2 x 85 = 170 + 5%
    # Bank: statement with a bank charge booked from the statement, then validated; BRS difference zero
    with app.app_context():
        bnk = q("SELECT * FROM journals WHERE branch_id=? AND code='BNK'", (ruh,), one=True)
        charges = q("SELECT id FROM accounts WHERE code='6500'", one=True)["id"]
    r = c.post("/bank/new", data={"journal_id": bnk["id"], "date": "2026-12-31", "balance_start": "0", "balance_end": "-25"})
    st = int(re.search(r"/bank/(\d+)", r.headers["Location"]).group(1))
    c.post(f"/bank/{st}/line", data={"date": "2026-12-31", "amount_out": "25", "description": "Monthly fee"})
    with app.app_context():
        line = q("SELECT id FROM bank_statement_lines WHERE statement_id=?", (st,), one=True)["id"]
    c.post(f"/bank/{st}/book", data={"line_id": line, "account_id": charges, "label": "Monthly fee"})
    c.post(f"/bank/{st}/validate")
    with app.app_context():
        assert q("SELECT state FROM bank_statements WHERE id=?", (st,), one=True)[0] == "reconciled"
    # CSV import parses debit/credit columns and day-first dates
    from vertex.bankrec import parse_csv
    rows = parse_csv("Date;Description;Debit;Credit\n05/10/2026;Fee;12,50;\n06/10/2026;Deposit;;1.000,00\n".replace(",50", ".50").replace("1.000,00", "1000.00"), "DD/MM/YYYY")
    assert rows == [("2026-10-05", "Fee", "", "", -12500), ("2026-10-06", "Deposit", "", "", 1000000)]


def make_empty_app():
    path = os.path.join(tempfile.mkdtemp(), "test.db")
    create_schema(path)
    app = create_app(database=path, license_path=os.path.join(tempfile.mkdtemp(), "none.json"))
    app.config["CSRF_ENABLED"] = False
    with app.app_context():
        seed(demo=False)
    return app


def do_import(c, kind, content, filename="data.csv", **opts):
    """Upload, then import with the automatic column matching. Returns the mapping page or the redirect."""
    import io
    raw = content.encode("utf-8") if isinstance(content, str) else content
    r = c.post(f"/setup/import/{kind}", data={"file": (io.BytesIO(raw), filename), "header_row": "1"},
               content_type="multipart/form-data")
    assert r.status_code == 302, r.status_code
    url = r.headers["Location"]
    assert c.get(url).status_code == 200
    data = {"remap": "1", "action": opts.pop("action", "import"), "date_format": "DD/MM/YYYY", "update_existing": "1",
            "create_partners": "1", "skip_existing": "1", "redirect_ar": "1", "redirect_stock": "1",
            "redirect_assets": "1"}
    data.update({k: v for k, v in opts.items() if v is not None})
    for k in [k for k, v in opts.items() if v is None]:
        data.pop(k, None)
    return c.post(url, data=data)


def balance(code, branch="RUH"):
    return q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
             "JOIN accounts a ON a.id = ml.account_id JOIN branches b ON b.id = ml.branch_id "
             "WHERE m.state = 'posted' AND a.code = ? AND b.code = ?", (code, branch), one=True)[0]


def test_imports():
    import io
    import openpyxl
    app = make_empty_app()
    c = app.test_client()
    login(c)
    assert c.get("/setup/import/").status_code == 200
    for kind in ("accounts", "partners", "products", "opening", "open_items", "entries", "invoices", "payments",
                 "stock", "assets"):
        for fmt in ("xlsx", "csv"):
            assert c.get(f"/setup/import/template/{kind}.{fmt}").status_code == 200
    # chart of accounts: new, type guessed from the code, existing updated
    r = do_import(c, "accounts", "Account Code,Account Name,Arabic Name,Account Type\n1015,Petty Cash,عهدة نقدية,"
                  "Bank and Cash\n6150,Telephone,,\n1010,Cash in Hand,,\n")
    assert r.status_code == 302, r.get_data(as_text=True)[:3000]
    with app.app_context():
        assert q("SELECT kind FROM accounts WHERE code='6150'", one=True)[0] == "expense"
        assert q("SELECT name_en FROM accounts WHERE code='1010'", one=True)[0] == "Cash in Hand"
    # partners from an Excel file
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append(["Customer Name", "VAT", "Type", "Credit Days"])
    ws.append(["Old Customer LLC", "399999999900003", "customer", 45])
    ws.append(["Old Supplier Co", "", "vendor", None])
    buf = io.BytesIO()
    wb.save(buf)
    r = do_import(c, "partners", buf.getvalue(), "partners.xlsx")
    assert r.status_code == 302
    with app.app_context():
        p = q("SELECT * FROM partners WHERE name_en='Old Customer LLC'", one=True)
        assert p["is_customer"] == 1 and p["payment_terms"] == 45
    # opening trial balance with Arabic column names; control accounts go to the clearing account
    tb = ("الفرع,رقم الحساب,مدين,دائن\nRUH,1020,100000,\nRUH,1100,11500,\nRUH,1200,3000,\nRUH,1620,\"90,000.00\",\n"
          "RUH,1625,,40500\nRUH,2100,,5750\nRUH,3100,,158250\n")
    r = do_import(c, "opening", tb, action="check", cutoff="2025-12-31")
    assert r.status_code == 200 and "Ready to import" in r.get_data(as_text=True)
    with app.app_context():
        assert not q("SELECT 1 FROM moves")  # a check saves nothing
    r = do_import(c, "opening", tb, cutoff="2025-12-31")
    assert r.status_code == 302
    r = do_import(c, "opening", tb, cutoff="2025-12-31")
    assert r.status_code == 200 and "already imported" in r.get_data(as_text=True)
    # open invoices and bills, opening stock, fixed assets
    r = do_import(c, "open_items", "Type,Branch,Invoice No,Invoice Date,Due Date,Customer,Balance\n"
                  "invoice,RUH,INV-OLD-1,15/11/2025,15/12/2025,Old Customer LLC,11500\n"
                  "bill,RUH,BILL-OLD-7,20/12/2025,,Old Supplier Co,5750\n")
    assert r.status_code == 302, r.get_data(as_text=True)[:3000]
    r = do_import(c, "stock", "Warehouse,Item Code,Qty,Unit Cost\nWH-RUH,SAF-001,50,42\nWH-RUH,SAF-002,81.818,11\n",
                  cutoff="2025-12-31")
    assert r.status_code == 302, r.get_data(as_text=True)[:3000]
    r = do_import(c, "assets", "Asset Code,Asset Name,Category,Branch,Purchase Date,Cost,Accumulated Depreciation,Life\n"
                  "FA-1,Delivery Van,Vehicles,RUH,01/03/2024,90000,40500,48\n", cutoff="2025-12-31")
    assert r.status_code == 302, r.get_data(as_text=True)[:3000]
    with app.app_context():
        assert balance("1100") == 11500000 and balance("2100") == -5750000
        assert balance("1620") == 90000000 and balance("1625") == -40500000
        stock_gl = balance("1200")
        assert abs(stock_gl - 3000000) <= 10, stock_gl
        from vertex.importer import clearing_balances
        ruh = [b for b in clearing_balances([b["id"] for b in q("SELECT id FROM branches")]) if b["code"] == "RUH"][0]
        assert abs(ruh["balance"]) <= 10, ruh["balance"]
        a = q("SELECT * FROM assets WHERE code='FA-1'", one=True)
        lines = q("SELECT * FROM asset_lines WHERE asset_id=? ORDER BY date", (a["id"],))
        assert lines[0]["amount"] == 40500000 and lines[0]["move_id"]
        assert len(lines) == 27 and lines[-1]["book_value"] == 0 and lines[1]["date"] == "2026-01-31"
    assert "INV-OLD-1" in c.get("/reports/aged/receivable?detail=1&date=2026-01-31").get_data(as_text=True)
    # importing the same documents again skips them
    r = do_import(c, "open_items", "Type,Branch,Invoice No,Invoice Date,Customer,Balance\n"
                  "invoice,RUH,INV-OLD-1,15/11/2025,Old Customer LLC,11500\n", action="check")
    assert "skipped" in r.get_data(as_text=True)
    # history: an unbalanced entry stops the whole file
    r = do_import(c, "entries", "Entry No,Date,Account,Debit,Credit,Description\nJV-1,10/01/2026,6150,200,,Phone\n"
                  "JV-1,10/01/2026,1015,,200,Phone\nJV-2,11/01/2026,6150,100,,x\nJV-2,11/01/2026,1015,,90,x\n",
                  branch_id="1")
    assert r.status_code == 200 and "not balanced" in r.get_data(as_text=True)
    r = do_import(c, "entries", "Entry No,Date,Account,Amount,Description\nJV-1,10/01/2026,6150,200,Phone\n"
                  "JV-1,10/01/2026,1015,-200,Phone\n", branch_id="1")
    assert r.status_code == 302
    r = do_import(c, "invoices", "Invoice No,Date,Customer,Item,Qty,Unit Price,VAT,Total\n"
                  "INV-H-1,05/01/2026,Old Customer LLC,SAF-001,2,85,15,195.50\n"
                  "INV-H-1,05/01/2026,Old Customer LLC,,1,0,15,195.50\n", branch_id="1")
    assert r.status_code == 302, r.get_data(as_text=True)[:3000]
    r = do_import(c, "payments", "Date,Customer,Amount,Invoice No\n20/01/2026,Old Customer LLC,195.50,INV-H-1\n"
                  "21/01/2026,Old Customer LLC,\"(11,500.00)\",INV-OLD-1\n", branch_id="1")
    assert r.status_code == 302, r.get_data(as_text=True)[:3000]
    with app.app_context():
        from vertex.ledger import invoice_residuals
        ids = [i["id"] for i in q("SELECT id FROM invoices WHERE number IN ('INV-H-1', 'INV-OLD-1')")]
        assert set(invoice_residuals(ids).values()) == {0}
        assert q("SELECT amount_total FROM invoices WHERE number='INV-H-1'", one=True)[0] == 195500
        batches = {b["kind"]: b["id"] for b in q("SELECT * FROM import_batches WHERE state='done'")}
    # undo: blocked while later documents use the records, allowed for the asset register
    c.post(f"/setup/import/batch/{batches['partners']}/undo")
    c.post(f"/setup/import/batch/{batches['assets']}/undo")
    with app.app_context():
        assert q("SELECT state FROM import_batches WHERE id=?", (batches["partners"],), one=True)[0] == "done"
        assert q("SELECT state FROM import_batches WHERE id=?", (batches["assets"],), one=True)[0] == "undone"
        assert not q("SELECT 1 FROM assets") and balance("1620") == 0
    # company colour
    with app.app_context():
        co = dict(q("SELECT * FROM company", one=True))
    form = {k: ("" if v is None else v) for k, v in co.items() if k != "id"}
    c.post("/setup/company", data=dict(form, accent="#0d9488"))
    assert 'style="--accent: #0d9488"' in c.get("/").get_data(as_text=True)
    c.post("/setup/company", data=dict(form, accent="red; x"))
    with app.app_context():
        assert q("SELECT accent FROM company", one=True)[0] is None
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        assert c.get("/setup/import/").status_code == 200
        assert c.get("/setup/import/opening").status_code == 200


def test_costing():
    """Returns, bill price differences, landed costs and per-product / per-partner accounts."""
    from vertex.db import commit, x
    from vertex.ledger import create_refund, post_invoice, unpost_invoice
    from vertex.stock import confirm_order, create_invoice_picking, create_order_invoice, quant, validate_picking
    app = make_app()
    c = app.test_client()
    login(c)
    # new products and partners start on the company default accounts
    with app.app_context():
        acc = {r["code"]: r["id"] for r in q("SELECT id, code FROM accounts")}
    page = c.get("/setup/products/new").get_data(as_text=True)
    assert f'<option value="{acc["4100"]}" selected' in page and f'<option value="{acc["5100"]}" selected' in page
    page = c.get("/setup/partners/new?type=customer").get_data(as_text=True)
    assert f'<option value="{acc["1100"]}" selected' in page and f'<option value="{acc["2100"]}" selected' in page
    with app.app_context():
        b = q("SELECT * FROM branches WHERE code='RUH'", one=True)
        wh = q("SELECT id FROM warehouses WHERE branch_id=? ORDER BY id LIMIT 1", (b["id"],), one=True)["id"]
        sj = q("SELECT id FROM journals WHERE branch_id=? AND code='INV'", (b["id"],), one=True)["id"]
        pj = q("SELECT id FROM journals WHERE branch_id=? AND code='BILL'", (b["id"],), one=True)["id"]
        rec2 = x("INSERT INTO accounts (code, name_en, kind) VALUES ('1105', 'Receivable - Key accounts', 'asset_receivable')")
        cust = x("INSERT INTO partners (name_en, is_customer, receivable_account_id) VALUES ('Key Customer', 1, ?)", (rec2,))
        vend = x("INSERT INTO partners (name_en, is_vendor) VALUES ('Test Supplier', 1)")
        prod = x("INSERT INTO products (code, name_en, type, sale_price, purchase_price, income_account_id, "
                 "expense_account_id) VALUES ('TST', 'Test item', 'stock', 50000, 10000, ?, ?)", (acc["4100"], acc["5100"]))

        def gl(code):
            return balance(code)

        def invoice(kind, partner, journal, qty, price, account, origin=None):
            iid = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, state, amount_untaxed, "
                    "amount_tax, amount_total, origin_id) VALUES (?,?,?,?,'2026-10-10','2026-10-10','draft',?,0,?,?)",
                    (b["id"], journal, kind, partner, int(qty * price), int(qty * price), origin))
            x("INSERT INTO invoice_lines (invoice_id, product_id, description, account_id, quantity, price_unit, subtotal, "
              "tax_amount) VALUES (?,?,'Test item',?,?,?,?,0)", (iid, prod, account, qty, price, int(qty * price)))
            post_invoice(iid, 1)
            return iid

        def move_goods(iid):
            pk = create_invoice_picking(iid, 1)
            validate_picking(pk, d="2026-10-10", user_id=1)
            return pk

        start = {k: gl(k) for k in ("1200", "2150", "5100", "1250")}
        # buy 10 @ 10.00 without an order, then 10 more @ 14.00 -> average 12.00
        move_goods(invoice("in_invoice", vend, pj, 10, 10000, acc["2150"]))
        bill2 = invoice("in_invoice", vend, pj, 10, 14000, acc["2150"])
        move_goods(bill2)
        assert quant(prod, wh) == (20, 240000)
        # sell 5 to the key customer: receivable goes to the partner's own account, COGS at 12.00
        sale = invoice("out_invoice", cust, sj, 5, 50000, acc["4100"])
        assert gl("1105") == 250000
        move_goods(sale)
        assert gl("5100") - start["5100"] == 60000
        # buy 10 more @ 20.00 -> cost moves to 15.00; a return of 2 still comes back at the 12.00 it left with
        move_goods(invoice("in_invoice", vend, pj, 10, 20000, acc["2150"]))
        ref = create_refund(sale, 1)
        x("UPDATE invoice_lines SET quantity = 2, subtotal = 100000 WHERE invoice_id = ?", (ref,))
        x("UPDATE invoices SET amount_untaxed = 100000, amount_total = 100000 WHERE id = ?", (ref,))
        post_invoice(ref, 1)
        assert gl("1105") == 150000
        ret = move_goods(ref)
        assert q("SELECT kind FROM pickings WHERE id = ?", (ret,), one=True)[0] == "return_in"
        assert gl("5100") - start["5100"] == 60000 - 24000
        assert quant(prod, wh) == (27, 180000 + 200000 + 24000)
        # return 1 to the vendor at the 14.00 bill price: GRNI clears, stock leaves at average, gap to COGS
        vref = create_refund(bill2, 1)
        x("UPDATE invoice_lines SET quantity = 1, subtotal = 14000 WHERE invoice_id = ?", (vref,))
        x("UPDATE invoices SET amount_untaxed = 14000, amount_total = 14000 WHERE id = ?", (vref,))
        post_invoice(vref, 1)
        avg = round(404000 / 27)
        cogs_before = gl("5100")
        move_goods(vref)
        assert gl("2150") == start["2150"]
        assert quant(prod, wh) == (26, 404000 - avg)
        assert gl("5100") - cogs_before == -(14000 - avg)

        # purchase order at 10.00, received, billed at 12.00 -> the 20.00 difference goes to stock (all on hand)
        def purchase(qty, price):
            oid = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, state) "
                    "VALUES ('purchase', 'PO-T', ?, ?, ?, '2026-10-11', 'draft')", (b["id"], wh, vend))
            x("INSERT INTO order_lines (order_id, product_id, description, quantity, price_unit, subtotal) "
              "VALUES (?,?,'Test item',?,?,?)", (oid, prod, qty, price, int(qty * price)))
            x("UPDATE orders SET amount_untaxed = ?, amount_total = ? WHERE id = ?", (int(qty * price), int(qty * price), oid))
            confirm_order(oid, 1)
            return oid

        def receive(oid):
            pk = q("SELECT id FROM pickings WHERE order_id = ? AND state = 'ready'", (oid,), one=True)["id"]
            validate_picking(pk, d="2026-10-12", user_id=1)

        def bill(oid, price):
            iid = create_order_invoice(oid, "2026-10-13", 1)
            x("UPDATE invoice_lines SET price_unit = ?, subtotal = quantity * ? WHERE invoice_id = ?", (price, price, iid))
            tot = q("SELECT SUM(subtotal) FROM invoice_lines WHERE invoice_id = ?", (iid,), one=True)[0]
            x("UPDATE invoices SET amount_untaxed = ?, amount_tax = 0, amount_total = ? WHERE id = ?", (tot, tot, iid))
            post_invoice(iid, 1)
            return iid

        grni0, stock0, cogs0 = gl("2150"), quant(prod, wh)[1], gl("5100")
        po = purchase(10, 10000)
        receive(po)
        b1 = bill(po, 12000)
        assert gl("2150") == grni0
        assert quant(prod, wh)[1] - stock0 == 120000 and gl("5100") == cogs0
        # cancelling the bill takes the difference back out; posting again books it again
        unpost_invoice(b1, "cancelled", 1)
        assert quant(prod, wh)[1] - stock0 == 100000 and gl("2150") == grni0 - 100000
        # billed before the goods arrive: settled when the receipt is validated
        po2 = purchase(4, 10000)
        bill(po2, 9000)
        receive(po2)
        assert quant(prod, wh)[1] - stock0 == 100000 + 36000
        assert gl("2150") == grni0 - 100000

        commit()
    # landed cost: 30.00 freight booked to the clearing account, shared over the two receipts by value
    r = c.post("/inventory/landed-costs/new", data={
        "branch_id": b["id"], "date": "2026-10-14", "method": "value", "description": ["Freight"],
        "account_id": [acc["1250"]], "amount": ["30"], "picking_id": [str(p) for p in (
            q_pickings(app, po), q_pickings(app, po2))], "action": "validate"})
    assert r.status_code == 302, r.get_data(as_text=True)
    lc = int(r.headers["Location"].rsplit("/", 1)[1])
    with app.app_context():
        assert q("SELECT state FROM landed_costs WHERE id = ?", (lc,), one=True)[0] == "done"
        assert quant(prod, wh)[1] - stock0 == 100000 + 36000 + 30000
        assert gl("1250") - start["1250"] == -30000
        # stock valuation still equals the inventory account
        from vertex.stock import stock_value_by_branch
        assert stock_value_by_branch([b["id"]])[b["id"]] == gl("1200") + gl("1205")
        assert not q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
    # month-end check: an invoice without its delivery, and a credit note without the goods back
    with app.app_context():
        from vertex.stock import stock_matching
        open_sale = invoice("out_invoice", cust, sj, 3, 50000, acc["4100"])
        open_cn = create_refund(open_sale, 1)
        post_invoice(open_cn, 1)
        commit()
        m = stock_matching([b["id"]], "2026-12-31")
        assert [(r["invoice_id"], r["qty"]) for r in m["inv_not_delivered"]] == [(open_sale, 3)]
        assert [(r["invoice_id"], r["qty"]) for r in m["credit_not_returned"]] == [(open_cn, 3)]
        assert not stock_matching([b["id"]], "2026-10-09")["inv_not_delivered"]
        x("INSERT INTO accounts (code, name_en, kind) VALUES ('1206', 'Inventory - Test', 'asset_current')")
        commit()
        before = quant(prod, wh)[1]
    assert "Invoiced, not delivered" in c.get("/reports/stock-matching?date_to=2026-12-31").get_data(as_text=True)
    # moving the product to a category with its own inventory account carries its stock value across
    r = c.post("/setup/product_categories/new", data={"name_en": "Test goods", "inventory_account_id": acc_id(app, "1206"),
                                                      "active": "1"})
    assert r.status_code == 302
    with app.app_context():
        cat = q("SELECT id FROM product_categories WHERE name_en = 'Test goods'", one=True)[0]
    r = c.post(f"/setup/products/{prod}", data={
        "code": "TST", "name_en": "Test item", "type": "stock", "category_id": cat, "sale_price": "500",
        "purchase_price": "100", "income_account_id": acc["4100"], "expense_account_id": acc["5100"], "min_qty": "0",
        "active": "1"})
    assert r.status_code == 302
    with app.app_context():
        assert balance("1206") == before
        assert q("SELECT category FROM products WHERE id = ?", (prod,), one=True)[0] == "Test goods"
        from vertex.stock import stock_value_by_branch
        assert stock_value_by_branch([b["id"]])[b["id"]] == balance("1200") + balance("1205") + balance("1206")
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for p in ("/reports/stock-matching", "/setup/product_categories", f"/setup/product_categories/{cat}",
                  "/inventory/landed-costs", "/inventory/landed-costs/new", f"/inventory/landed-costs/{lc}",
                  "/inventory/transfers?kind=return_in", "/inventory/transfers?kind=return_out",
                  f"/inventory/transfers/{ret}", f"/invoices/view/{ref}", f"/invoices/view/{sale}",
                  "/setup/products/new", "/setup/partners/new", "/setup/company"):
            assert c.get(p).status_code == 200, (lang, p)


def test_attachments():
    import io
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        inv = q("SELECT i.id, i.move_id FROM invoices i JOIN branches b ON b.id = i.branch_id "
                "WHERE i.state = 'posted' AND b.code = 'RUH' LIMIT 1", one=True)
    pdf = b"%PDF-1.4 supplier bill"
    r = c.post(f"/attachments/invoice/{inv['id']}", data={"files": [(io.BytesIO(pdf), "Supplier bill 1001.pdf"),
                                                                     (io.BytesIO(b"x,y"), "detail.csv")]},
               content_type="multipart/form-data")
    assert r.status_code == 302 and r.headers["Location"].endswith("#attachments")
    page = c.get(f"/invoices/view/{inv['id']}").get_data(as_text=True)
    assert "Supplier bill 1001.pdf" in page and "detail.csv" in page
    with app.app_context():
        aid = q("SELECT id FROM attachments WHERE filename = 'Supplier bill 1001.pdf'", one=True)[0]
    r = c.get(f"/attachments/{aid}/x.pdf")
    assert r.status_code == 200 and r.data == pdf and r.mimetype == "application/pdf"
    assert "attachment" in c.get(f"/attachments/{aid}/x.pdf?download=1").headers["Content-Disposition"]
    # the journal entry of the invoice shows the invoice's files too
    assert "Supplier bill 1001.pdf" in c.get(f"/entries/{inv['move_id']}").get_data(as_text=True)
    assert 'class="ic clip"' in c.get("/invoices/out_invoice").get_data(as_text=True)
    # file types that could run in the browser are refused
    r = c.post(f"/attachments/invoice/{inv['id']}", data={"files": [(io.BytesIO(b"<script>"), "x.html")]},
               content_type="multipart/form-data", follow_redirects=True)
    assert "not allowed" in r.get_data(as_text=True)
    # a viewer can look but not upload; another branch's user cannot even look
    v = app.test_client()
    login(v, "viewer", "viewer123")
    assert v.get(f"/attachments/{aid}/x.pdf").status_code == 200
    assert v.post(f"/attachments/invoice/{inv['id']}", data={"files": [(io.BytesIO(pdf), "y.pdf")]},
                  content_type="multipart/form-data").status_code == 302
    d = app.test_client()
    login(d, "dubai", "dubai123")
    assert d.get(f"/attachments/{aid}/x.pdf").status_code == 302
    with app.app_context():
        assert q("SELECT COUNT(*) FROM attachments", one=True)[0] == 2
    c.post(f"/attachments/{aid}/delete")
    with app.app_context():
        assert q("SELECT COUNT(*) FROM attachments", one=True)[0] == 1
    assert c.get(f"/attachments/{aid}/x.pdf").status_code == 404
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for p in ("/payments/1", "/orders/view/1", "/inventory/transfers/1", "/assets/1", "/prepaid/1"):
            assert 'id="attachments"' in c.get(p).get_data(as_text=True), p


def test_statements_email():
    app = make_app()
    app.config["MAIL_OUTBOX"] = outbox = []
    c = app.test_client()
    login(c)
    with app.app_context():
        pid = q("SELECT partner_id FROM invoices WHERE kind = 'out_invoice' GROUP BY partner_id ORDER BY COUNT(*) DESC "
                "LIMIT 1", one=True)[0]
        ids = [r[0] for r in q("SELECT id FROM branches")]
        from vertex.statements import statement
        secs = statement(pid, "customer", ids, "2026-03-01", "2026-12-31")
        for s_ in secs:
            # opening + movements = closing = the ledger; ageing + unapplied = closing
            assert s_["opening"] + s_["debit"] - s_["credit"] == s_["closing"]
            gl = q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                   "JOIN accounts a ON a.id = ml.account_id JOIN branches b ON b.id = ml.branch_id WHERE m.state = 'posted' "
                   "AND a.kind = 'asset_receivable' AND ml.partner_id = ? AND b.currency = ? AND m.date <= '2026-12-31'",
                   (pid, s_["currency"]), one=True)[0]
            assert s_["closing"] == gl and sum(s_["ageing"].values()) + s_["unapplied"] == s_["closing"]
            assert s_["lines"][-1]["balance"] == s_["closing"]
        inv = q("SELECT id, number FROM invoices WHERE partner_id = ? AND state = 'posted' AND kind = 'out_invoice' LIMIT 1",
                (pid,), one=True)
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for p in ("/statements/customer", "/statements/vendor", f"/statements/customer/{pid}",
                  f"/statements/customer/{pid}/email?kind=reminder", f"/invoices/email/{inv['id']}", "/setup/email-log"):
            assert c.get(p).status_code == 200, (lang, p)
        for p in (f"/statements/customer/{pid}/pdf", f"/invoices/pdf/{inv['id']}"):
            r = c.get(p)
            assert r.mimetype == "application/pdf" and r.data[:5] == b"%PDF-", p
    c.get("/lang/en")
    # without a mail server nothing goes out, and the attempt is logged as failed
    app.config.pop("MAIL_OUTBOX")
    r = c.post(f"/statements/customer/{pid}/email", data={"to": "ap@customer.example", "subject": "Statement", "body": "Hi"},
               follow_redirects=True)
    assert "Set up the outgoing mail server" in r.get_data(as_text=True)
    app.config["MAIL_OUTBOX"] = outbox
    # bad addresses are refused before anything is sent
    r = c.post(f"/statements/customer/{pid}/email", data={"to": "not-an-address", "subject": "S", "body": "B"},
               follow_redirects=True)
    assert "Not a valid email address" in r.get_data(as_text=True) and not outbox
    r = c.post(f"/statements/customer/{pid}/email", data={"to": "ap@customer.example", "cc": "boss@customer.example",
                                                          "subject": "Your statement", "body": "Dear customer",
                                                          "date_from": "2026-01-01", "date_to": "2026-10-31"})
    assert r.status_code == 302 and len(outbox) == 1
    msg = outbox[0]
    assert msg["To"] == "ap@customer.example" and msg["Cc"] == "boss@customer.example"
    att = [p for p in msg.iter_attachments()]
    assert att[0].get_filename().startswith("Statement_") and att[0].get_content()[:5] == b"%PDF-"
    # invoice by email
    r = c.post(f"/invoices/email/{inv['id']}", data={"to": "ap@customer.example", "subject": "Invoice", "body": "Attached"})
    assert r.status_code == 302 and outbox[-1].get_payload()[1].get_filename() == inv["number"].replace("/", "-") + ".pdf"
    # bulk: partners without an email are skipped and named
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE partners SET email = 'finance@partner.example' WHERE id = ?", (pid,))
        other = q("SELECT partner_id FROM invoices WHERE kind = 'out_invoice' AND partner_id != ? LIMIT 1", (pid,), one=True)[0]
        dbx("UPDATE partners SET email = NULL WHERE id = ?", (other,))
        dbcommit()
    r = c.post("/statements/customer/send", data={"pid": [pid, other], "kind": "reminder", "date_to": "2026-12-31"},
               follow_redirects=True)
    page = r.get_data(as_text=True)
    assert "1 emails sent" in page and "No email address" in page
    assert outbox[-1]["To"] == "finance@partner.example"
    assert outbox[-1]["Subject"].split(" — ")[0] in ("Payment reminder", "Second payment reminder", "Final notice")
    with app.app_context():
        log = q("SELECT state, COUNT(*) FROM email_log GROUP BY state", one=False)
        assert dict((r[0], r[1]) for r in log) == {"sent": 3, "failed": 1}
    # mail server settings: the password is kept when the field is left empty
    form = {k: v for k, v in [("name_en", "Vertex Trading Co."), ("currency", "SAR"), ("fy_start_month", "1"),
                              ("smtp_host", "smtp.example.com"), ("smtp_port", "587"), ("smtp_security", "starttls"),
                              ("smtp_user", "accounts"), ("smtp_password", "s3cret"), ("mail_from", "accounts@vertex.example")]}
    c.post("/setup/company", data=form)
    form["smtp_password"] = ""
    c.post("/setup/company", data=form)
    with app.app_context():
        row = q("SELECT smtp_host, smtp_password FROM company", one=True)
        assert row["smtp_host"] == "smtp.example.com" and row["smtp_password"] == "s3cret"
    assert "s3cret" not in c.get("/setup/company").get_data(as_text=True)


def test_recurring():
    app = make_app()
    app.config["MAIL_OUTBOX"] = outbox = []
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        sj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'INV'", (b["id"],), one=True)[0]
        mj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'MISC'", (b["id"],), one=True)[0]
        cust = q("SELECT id FROM partners WHERE is_customer = 1 LIMIT 1", one=True)[0]
        dbx("UPDATE partners SET email = 'ap@tenant.example' WHERE id = ?", (cust,))
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        acc = {r["code"]: r["id"] for r in q("SELECT id, code FROM accounts")}
        tax = q("SELECT id FROM taxes WHERE scope = 'sale' AND rate = 15", one=True)[0]
    for p in ("/recurring/", "/recurring/new?kind=out_invoice", "/recurring/new?kind=in_invoice", "/recurring/new?kind=entry",
              "/recurring/new?from_invoice=1", "/recurring/new?from_move=1"):
        assert c.get(p).status_code == 200, p
    # rent invoiced on the last day of each month, three times, posted and emailed
    r = c.post("/recurring/new", data={
        "kind": "out_invoice", "name": "Warehouse rent", "partner_id": cust, "branch_id": b["id"], "journal_id": sj,
        "start_date": "2026-08-31", "every": "1", "frequency": "monthly", "max_count": "3", "due_days": "15",
        "ref": "Rent {month}", "auto_post": "1", "auto_email": "1",
        "description": ["Rent for {period}"], "account_id": [acc["4200"]], "quantity": ["1"], "price_unit": ["1000"],
        "tax_id": [tax], "product_id": [""], "order_line_id": [""]})
    assert r.status_code == 302, r.get_data(as_text=True)[:500]
    rid = int(r.headers["Location"].rsplit("/", 1)[1])
    with app.app_context():
        from vertex.recurring import due_count
        assert q("SELECT next_date FROM recurring WHERE id = ?", (rid,), one=True)[0] == "2026-08-31"
        assert due_count("2026-12-31", [b["id"]]) == 3
    c.post("/recurring/run", data={"date": "2026-10-15"})
    with app.app_context():
        invs = q("SELECT i.* FROM recurring_runs r JOIN invoices i ON i.id = r.record_id WHERE r.recurring_id = ? "
                 "ORDER BY i.date", (rid,))
        assert [i["date"] for i in invs] == ["2026-08-31", "2026-09-30"]
        assert all(i["state"] == "posted" and i["amount_total"] == 1150000 for i in invs)
        assert invs[1]["ref"] == "Rent September 2026" and invs[1]["due_date"] == "2026-10-15"
        assert q("SELECT description FROM invoice_lines WHERE invoice_id = ?", (invs[0]["id"],), one=True)[0] == "Rent for 2026-08"
        assert q("SELECT next_date FROM recurring WHERE id = ?", (rid,), one=True)[0] == "2026-10-31"
    assert len(outbox) == 2 and outbox[0]["To"] == "ap@tenant.example"
    c.post("/recurring/run", data={"date": "2027-06-30"})
    with app.app_context():
        rec = q("SELECT * FROM recurring WHERE id = ?", (rid,), one=True)
        assert rec["done_count"] == 3 and rec["state"] == "done" and rec["next_date"] is None
    # a template that has created documents cannot be deleted
    c.post(f"/recurring/{rid}/delete")
    with app.app_context():
        assert q("SELECT 1 FROM recurring WHERE id = ?", (rid,), one=True)
    # monthly accrual as a draft journal entry; unbalanced lines are refused
    entry = {"kind": "entry", "name": "Audit fee accrual", "branch_id": b["id"], "journal_id": mj, "start_date": "2026-09-30",
             "every": "1", "frequency": "monthly", "ref": "Accrual {month}", "account_id": [acc["6300"], acc["2300"]],
             "partner_id": ["", ""], "label": ["Audit fee {month}", "Accrued audit fee"], "debit": ["500", ""],
             "credit": ["", "400"]}
    r = c.post("/recurring/new", data=entry)
    assert "not balanced" in r.get_data(as_text=True)
    entry["credit"] = ["", "500"]
    r = c.post("/recurring/new", data=entry)
    eid = int(r.headers["Location"].rsplit("/", 1)[1])
    # the lock date stops the run without creating anything; the error is kept on the template
    with app.app_context():
        dbx("UPDATE company SET lock_date = '2026-09-30'")
        dbcommit()
    c.post("/recurring/run", data={"date": "2026-10-31", "rid": eid})
    with app.app_context():
        rec = q("SELECT * FROM recurring WHERE id = ?", (eid,), one=True)
        assert rec["done_count"] == 0 and "2026-09-30" in rec["last_error"]
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
    c.post("/recurring/run", data={"date": "2026-10-31", "rid": eid})
    with app.app_context():
        moves = q("SELECT m.* FROM recurring_runs r JOIN moves m ON m.id = r.record_id WHERE r.recurring_id = ? ORDER BY m.date",
                  (eid,))
        assert [(m["date"], m["state"], m["ref"]) for m in moves] == [("2026-09-30", "draft", "Accrual September 2026"),
                                                                      ("2026-10-31", "draft", "Accrual October 2026")]
        assert q("SELECT last_error FROM recurring WHERE id = ?", (eid,), one=True)[0] is None
    # pause and resume
    c.post(f"/recurring/{eid}/pause")
    c.post("/recurring/run", data={"date": "2026-12-31"})
    with app.app_context():
        assert q("SELECT done_count FROM recurring WHERE id = ?", (eid,), one=True)[0] == 2
    c.post(f"/recurring/{eid}/resume")
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for p in ("/recurring/", f"/recurring/{rid}", f"/recurring/{eid}", f"/recurring/{eid}/edit", "/closing/", "/"):
            assert c.get(p).status_code == 200, (lang, p)


def test_credit_control():
    app = make_app()
    app.config["MAIL_OUTBOX"] = outbox = []
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.credit import exposure
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        sj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'INV'", (b["id"],), one=True)[0]
        acc = q("SELECT id FROM accounts WHERE code = '4100'", one=True)[0]
        cust = dbx("INSERT INTO partners (name_en, is_customer, email) VALUES ('Credit Test Co', 1, 'ap@credit.example')")
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
    def invoice(amount, d="2026-10-20", action="post"):
        r = c.post("/invoices/out_invoice/new", data={
            "branch_id": b["id"], "journal_id": sj, "partner_id": cust, "date": d, "due_date": d,
            "description": ["Goods"], "account_id": [acc], "quantity": ["1"], "price_unit": [str(amount)], "tax_id": [""],
            "product_id": [""], "order_line_id": [""], "action": action}, follow_redirects=True)
        return r.get_data(as_text=True)
    # the limit is set on the customer form (company currency)
    page = c.get(f"/setup/partners/{cust}").get_data(as_text=True)
    assert 'name="credit_limit"' in page and 'name="credit_hold"' in page
    with app.app_context():
        dbx("UPDATE partners SET credit_limit = ? WHERE id = ?", (10000000, cust))  # 10,000.00
        dbcommit()
    assert "Credit control" not in invoice(6000)
    # policy "warn": goes through with a warning
    page = invoice(5000)
    assert "Credit control" in page and "would be exceeded" in page
    with app.app_context():
        assert q("SELECT COUNT(*) FROM invoices WHERE partner_id = ? AND state = 'posted'", (cust,), one=True)[0] == 2
        assert exposure(cust)["balance"] == 11000000
    # policy "block": stopped, stays a draft, and the draft shows why
    with app.app_context():
        dbx("UPDATE company SET credit_policy = 'block'")
        dbcommit()
    page = invoice(100)
    assert "would be exceeded" in page and "Raise the credit limit" in page
    with app.app_context():
        assert q("SELECT COUNT(*) FROM invoices WHERE partner_id = ? AND state = 'posted'", (cust,), one=True)[0] == 2
        draft = q("SELECT id FROM invoices WHERE partner_id = ? AND state = 'draft'", (cust,), one=True)
    if draft:
        assert "cannot be posted" in c.get(f"/invoices/view/{draft[0]}").get_data(as_text=True)
    # a sales order is checked when it is confirmed, including orders not yet invoiced
    with app.app_context():
        dbx("UPDATE partners SET credit_limit = ? WHERE id = ?", (20000000, cust))
        oid = dbx("INSERT INTO orders (kind, number, branch_id, partner_id, date, state, amount_untaxed, amount_tax, amount_total) "
                  "VALUES ('sale', 'SO-CR', ?, ?, '2026-10-21', 'draft', 9500000, 0, 9500000)", (b["id"], cust))
        dbx("INSERT INTO order_lines (order_id, description, quantity, price_unit, subtotal) VALUES (?, 'Goods', 1, 9500000, 9500000)",
            (oid,))
        dbcommit()
    c.post(f"/orders/view/{oid}/confirm")
    with app.app_context():
        assert q("SELECT state FROM orders WHERE id = ?", (oid,), one=True)[0] == "draft"
        dbx("UPDATE orders SET amount_total = 5000000, amount_untaxed = 5000000 WHERE id = ?", (oid,))
        dbx("UPDATE order_lines SET price_unit = 5000000, subtotal = 5000000 WHERE order_id = ?", (oid,))
        dbcommit()
    c.post(f"/orders/view/{oid}/confirm")
    with app.app_context():
        assert q("SELECT state FROM orders WHERE id = ?", (oid,), one=True)[0] == "confirmed"
        assert exposure(cust)["orders"] == 5000000 and exposure(cust)["total"] == 16000000
    # credit hold stops everything, even under "warn"
    with app.app_context():
        dbx("UPDATE company SET credit_policy = 'warn'")
        dbx("UPDATE partners SET credit_hold = 1, credit_limit = NULL WHERE id = ?", (cust,))
        dbcommit()
    assert "on credit hold" in invoice(10)
    # overdue rule: an invoice more than 30 days late stops new sales
    with app.app_context():
        dbx("UPDATE partners SET credit_hold = 0 WHERE id = ?", (cust,))
        dbx("UPDATE company SET credit_overdue_days = 30, credit_policy = 'block'")
        dbx("UPDATE invoices SET due_date = '2026-01-15' WHERE partner_id = ? AND state = 'posted'", (cust,))
        dbcommit()
    assert "days overdue" in invoice(10, d="2026-11-01")
    # credit control page, and reminders that get firmer with age
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for show in ("attention", "limits", "all"):
            assert c.get(f"/credit/?show={show}").status_code == 200
    c.get("/lang/en")
    page = c.get("/credit/").get_data(as_text=True)
    assert "Credit Test Co" in page and "Long overdue" in page
    c.post("/statements/customer/send", data={"pid": [cust], "kind": "reminder", "date_to": "2026-11-01"})
    assert outbox and outbox[-1]["Subject"].startswith("Final notice")


def test_year_end():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.ledger import create_move, profit_loss, balance_sheet, trial_balance
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        mj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'MISC'", (b["id"],), one=True)[0]
        acc = {r["code"]: r["id"] for r in q("SELECT id, code FROM accounts")}
        dbx("UPDATE company SET lock_date = NULL")
        re0 = balance("3300")  # opening HR provisions brought in against retained earnings
        # a small 2025 year: 1,000 sales, 300 rent
        create_move(b["id"], mj, "2025-06-30", [{"account_id": acc["1020"], "debit": 1000000, "credit": 0},
                                                {"account_id": acc["4100"], "debit": 0, "credit": 1000000}], 1, post=True)
        create_move(b["id"], mj, "2025-07-31", [{"account_id": acc["6200"], "debit": 300000, "credit": 0},
                                                {"account_id": acc["1020"], "debit": 0, "credit": 300000}], 1, post=True)
        draft = create_move(b["id"], mj, "2025-09-30", [{"account_id": acc["6200"], "debit": 1000, "credit": 0},
                                                        {"account_id": acc["1020"], "debit": 0, "credit": 1000}], 1)
        dbcommit()
        pl_before = [r for r in profit_loss([b["id"]], "2025-01-01", "2025-12-31").rows if r.get("label") == "Net Profit"][0]["total"]
        assert pl_before == 700000
    page = c.get("/closing/?fy=2025-01-01").get_data(as_text=True)
    assert "Year-end closing" in page and "700.00" in page and "drafts in the year" in page
    # drafts in the year stop the closing
    r = c.post("/closing/year", data={"fy": "2025-01-01"}, follow_redirects=True)
    assert "Post or delete them first" in r.get_data(as_text=True)
    with app.app_context():
        dbx("DELETE FROM moves WHERE id = ?", (draft,))
        dbcommit()
    r = c.post("/closing/year", data={"fy": "2025-01-01", "lock": "1"}, follow_redirects=True)
    assert "closing entries posted" in r.get_data(as_text=True)
    with app.app_context():
        cl = q("SELECT * FROM fiscal_closings WHERE branch_id = ?", (b["id"],), one=True)
        assert cl["year_end"] == "2025-12-31" and cl["profit"] == 700000
        m = q("SELECT * FROM moves WHERE id = ?", (cl["move_id"],), one=True)
        assert m["move_type"] == "closing" and m["date"] == "2025-12-31" and m["state"] == "posted"
        assert balance("3300") - re0 == -700000
        # the year's profit and loss is unchanged; income and expense accounts are zero after closing
        pl = [r for r in profit_loss([b["id"]], "2025-01-01", "2025-12-31").rows if r.get("label") == "Net Profit"][0]["total"]
        assert pl == 700000
        tb = {r["account"]["code"]: r for r in trial_balance([b["id"]], "2025-01-01", "2025-12-31")["rows"]}
        assert tb["4100"]["closing"] == 0 and tb["6200"]["closing"] == 0 and tb["3300"]["closing"] == -700000
        pre = {r["account"]["code"]: r for r in trial_balance([b["id"]], "2025-01-01", "2025-12-31", exclude_closing=True)["rows"]}
        assert pre["4100"]["closing"] == -1000000 and "3300" not in pre
        # balance sheet at the year end: result in retained earnings, still balanced
        bs = balance_sheet([b["id"]], "2025-12-31")
        rows = {r["label"]: r for r in bs.rows if r["kind"] != "section"}
        assert rows["Total Assets"]["total"] == rows["Total Liabilities and Equity"]["total"]
        assert rows["Current Year Earnings"]["total"] == 0
        assert q("SELECT lock_date FROM company", one=True)[0] == "2025-12-31"
    # locked: cannot reopen
    r = c.post("/closing/year", data={"fy": "2025-01-01", "action": "reopen"}, follow_redirects=True)
    assert "lock date" in r.get_data(as_text=True)
    with app.app_context():
        dbx("UPDATE company SET lock_date = NULL")
        # a late entry in 2025: closing again closes only what is left
        create_move(b["id"], mj, "2025-12-15", [{"account_id": acc["6200"], "debit": 50000, "credit": 0},
                                                {"account_id": acc["1020"], "debit": 0, "credit": 50000}], 1, post=True)
        dbcommit()
    assert "Late entries" in c.get("/closing/?fy=2025-01-01").get_data(as_text=True)
    c.post("/closing/year", data={"fy": "2025-01-01"})
    with app.app_context():
        assert [r["profit"] for r in q("SELECT profit FROM fiscal_closings WHERE branch_id = ? ORDER BY id", (b["id"],))] \
            == [700000, -50000]
        assert balance("3300") - re0 == -650000
    # reopen deletes the closing entries
    r = c.post("/closing/year", data={"fy": "2025-01-01", "action": "reopen"}, follow_redirects=True)
    assert "open again" in r.get_data(as_text=True)
    with app.app_context():
        assert not q("SELECT 1 FROM fiscal_closings") and balance("3300") == re0
        assert not q("SELECT 1 FROM moves WHERE move_type = 'closing'")
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        assert c.get("/closing/?fy=2025-01-01").status_code == 200
        assert c.get("/reports/trial-balance?preclosing=1").status_code == 200


def test_cost_centres():
    app = make_app()
    c = app.test_client()
    login(c)
    for code, name in (("SALES", "Sales Department"), ("PRJ", "Project Alpha")):
        r = c.post("/setup/cost_centres/new", data={"code": code, "name_en": name, "active": "1"})
        assert r.status_code == 302
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.costcentres import profit_loss_by_cost_centre
        from vertex.stock import create_invoice_picking, validate_picking, confirm_order, create_order_invoice
        from vertex.schedules import _entry_lines
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        cc = {r["code"]: r["id"] for r in q("SELECT * FROM cost_centres")}
        acc = {r["code"]: r["id"] for r in q("SELECT id, code FROM accounts")}
        sj = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'sale'", (b["id"],), one=True)[0]
        mj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'MISC'", (b["id"],), one=True)[0]
        cust = q("SELECT id FROM partners WHERE is_customer = 1 ORDER BY id", one=True)[0]
        prod = q("SELECT p.* FROM products p JOIN stock_quants s ON s.product_id = p.id JOIN warehouses w "
                 "ON w.id = s.warehouse_id WHERE p.type = 'stock' AND w.branch_id = ? AND s.qty >= 2 "
                 "ORDER BY w.id, p.id", (b["id"],), one=True)
    # the line editors offer the cost centres
    assert "Cost Centre" in c.get("/invoices/out_invoice/new").get_data(as_text=True)
    assert "Cost Centre" in c.get("/entries/new").get_data(as_text=True)
    d = "2026-11-01"
    r = c.post("/invoices/out_invoice/new", data={
        "branch_id": b["id"], "journal_id": sj, "partner_id": cust, "date": d, "due_date": d,
        "description": ["Goods", "Consulting"], "account_id": [prod["income_account_id"], acc["4100"]],
        "quantity": ["2", "1"], "price_unit": ["500", "800"], "tax_id": ["", ""],
        "product_id": [prod["id"], ""], "order_line_id": ["", ""], "cost_centre_id": [cc["SALES"], cc["PRJ"]],
        "action": "post"})
    assert r.status_code == 302, r.get_data(as_text=True)
    inv_id = int(re.search(r"/view/(\d+)", r.headers["Location"]).group(1))
    assert "PRJ" in c.get(f"/invoices/view/{inv_id}").get_data(as_text=True)
    r = c.post("/entries/new", data={"branch_id": b["id"], "journal_id": mj, "date": d,
                                     "account_id": [acc["6200"], acc["1020"]], "partner_id": ["", ""],
                                     "label": ["Site rent", ""], "debit": ["300", ""], "credit": ["", "300"],
                                     "cost_centre_id": [cc["PRJ"], ""], "action": "post"})
    assert r.status_code == 302
    with app.app_context():
        inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
        lines = {r["label"]: r for r in q("SELECT * FROM move_lines WHERE move_id = ?", (inv["move_id"],))}
        assert lines["Goods"]["cost_centre_id"] == cc["SALES"] and lines["Consulting"]["cost_centre_id"] == cc["PRJ"]
        # the receivable line carries no cost centre
        assert all(r["cost_centre_id"] is None for k, r in lines.items() if k not in ("Goods", "Consulting"))
        # the delivery's cost of goods sold follows the invoice line's cost centre
        # posting prepared the delivery already (stock product, no order)
        pid = q("SELECT id FROM pickings WHERE invoice_id = ? AND state = 'ready'", (inv_id,), one=True)[0]
        validate_picking(pid, user_id=1)
        pk = q("SELECT move_id FROM pickings WHERE id = ?", (pid,), one=True)
        cogs = [r for r in q("SELECT * FROM move_lines WHERE move_id = ?", (pk["move_id"],)) if r["debit"]]
        assert cogs and all(r["cost_centre_id"] == cc["SALES"] for r in cogs)
        cogs_amt = sum(r["debit"] for r in cogs)
        dbcommit()
        # report: per cost centre, and the total equals the ordinary profit and loss
        rep = profit_loss_by_cost_centre([b["id"]], d, d)
        cols = [col["code"] for col in rep["cols"]]
        net = [r for r in rep["rows"] if r["kind"] == "grand"][0]
        pl = [r for r in profit_loss([b["id"]], d, d).rows if r.get("label") == "Net Profit"][0]["total"]
        assert net["total"] == pl
        assert net["vals"][cols.index("SALES")] == 1000000 - cogs_amt
        assert net["vals"][cols.index("PRJ")] == 800000 - 300000
        only_prj = [r for r in profit_loss([b["id"]], d, d, cost_centre=cc["PRJ"]).rows
                    if r.get("label") == "Net Profit"][0]["total"]
        assert only_prj == 500000
        # orders pass their cost centre to the invoice lines
        oid = dbx("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, state, amount_untaxed, "
                  "amount_tax, amount_total, cost_centre_id) VALUES ('sale', 'SO-CC', ?, NULL, ?, ?, 'draft', 100000, 0, "
                  "100000, ?)", (b["id"], cust, d, cc["PRJ"]))
        dbx("INSERT INTO order_lines (order_id, description, quantity, price_unit, subtotal) VALUES (?, 'Service', 1, "
            "100000, 100000)", (oid,))
        confirm_order(oid, 1)
        oinv = create_order_invoice(oid, d, 1)
        assert q("SELECT cost_centre_id FROM invoice_lines WHERE invoice_id = ?", (oinv,), one=True)[0] == cc["PRJ"]
        # prepaid / accrual entries analyse only the income or expense side
        sch = {"kind": "prepaid", "balance_account_id": acc["1020"], "pl_account_id": acc["6200"], "partner_id": None,
               "name": "Rent", "cost_centre_id": cc["PRJ"]}
        el = {ln["account_id"]: ln["cost_centre_id"] for ln in _entry_lines(sch, 1000)}
        assert el == {acc["6200"]: cc["PRJ"], acc["1020"]: None}
        dbx("UPDATE company SET cost_centre_required = 1")
        dbcommit()
    # with the rule on, an expense line without a cost centre is refused
    r = c.post("/entries/new", data={"branch_id": b["id"], "journal_id": mj, "date": d,
                                     "account_id": [acc["6200"], acc["1020"]], "partner_id": ["", ""],
                                     "label": ["", ""], "debit": ["10", ""], "credit": ["", "10"],
                                     "cost_centre_id": ["", ""], "action": "post"}, follow_redirects=True)
    assert "Choose a cost centre" in r.get_data(as_text=True)
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        page = c.get(f"/reports/profit-loss/cost-centres?date_from={d}&date_to={d}")
        assert page.status_code == 200
        assert "SALES" in page.get_data(as_text=True)
        assert c.get(f"/reports/profit-loss/cost-centres?date_from={d}&date_to={d}&export=csv").status_code == 200
        assert c.get(f"/reports/profit-loss?cc={cc['PRJ']}").status_code == 200
        assert c.get("/reports/profit-loss?cc=none").status_code == 200
        assert c.get(f"/reports/general-ledger?cc={cc['SALES']}&date_from={d}").status_code == 200
        assert c.get("/setup/cost_centres").status_code == 200
        for url in ("/orders/sale/new", "/assets/new", "/prepaid/new?kind=prepaid", "/recurring/new?kind=entry",
                    f"/entries/{q_move(app, inv_id)}"):
            assert c.get(url).status_code == 200, url


def test_budgets():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.ledger import create_move
        from vertex.budgets import actuals, compare
        dbx("UPDATE company SET lock_date = NULL")
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        acc = {r["code"]: r["id"] for r in q("SELECT id, code FROM accounts")}
        mj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'MISC'", (b["id"],), one=True)[0]
        dbcommit()
    assert c.get("/budgets/report", follow_redirects=True).status_code == 200  # no budget yet
    r = c.post("/budgets/new", data={"name": "Budget 2026", "fy_start": "2026-01-01", "branch_id": b["id"], "start": "empty"})
    assert r.status_code == 302 and "/edit" in r.headers["Location"]
    bid = int(re.search(r"/budgets/(\d+)/edit", r.headers["Location"]).group(1))
    assert "2026-12" in c.get(f"/budgets/{bid}/edit").get_data(as_text=True)
    form = {"name": "Budget 2026"}
    for i in range(12):
        form[f"a{acc['4200']}_{i}"] = "10000"
        form[f"a{acc['6200']}_{i}"] = "2,500.50"
    r = c.post(f"/budgets/{bid}/edit", data=form)
    assert r.status_code == 302
    with app.app_context():
        assert q("SELECT COUNT(*) FROM budget_lines WHERE budget_id = ?", (bid,), one=True)[0] == 24
        assert q("SELECT amount FROM budget_lines WHERE budget_id = ? AND account_id = ? AND period = '2026-03'",
                 (bid, acc["6200"]), one=True)[0] == 2500500
        before = actuals(b["id"], None, "2026-11-01", "2026-11-30")
        # November: service revenue 12,000 (above budget), rent 2,000 (below budget)
        create_move(b["id"], mj, "2026-11-01", [{"account_id": acc["1020"], "debit": 12000000, "credit": 0},
                                                {"account_id": acc["4200"], "debit": 0, "credit": 12000000}], 1, post=True)
        create_move(b["id"], mj, "2026-11-01", [{"account_id": acc["6200"], "debit": 2000000, "credit": 0},
                                                {"account_id": acc["1020"], "debit": 0, "credit": 2000000}], 1, post=True)
        dbcommit()
        bud = q("SELECT * FROM budgets WHERE id = ?", (bid,), one=True)
        rows = {r["account"]["code"]: r for r in compare(bud, "2026-11", "month")["rows"] if r.get("account")}
        rev, rent = rows["4200"], rows["6200"]
        assert rev["budget"] == 10000000 and rev["actual"] == 12000000 + before.get(acc["4200"], {}).get("2026-11", 0)
        assert rev["variance"] > 0 and rev["favourable"]
        assert rent["budget"] == 2500500 and rent["actual"] == 2000000 + before.get(acc["6200"], {}).get("2026-11", 0)
        year = compare(bud, "2026-11", "year")
        net = [r for r in year["rows"] if r["kind"] == "grand"][0]
        assert net["budget"] == 12 * (10000000 - 2500500)
    # a copy for next year moves the months across
    r = c.post("/budgets/new", data={"name": "Budget 2027", "fy_start": "2027-01-01", "branch_id": b["id"],
                                     "start": "copy", "copy_id": bid})
    bid2 = int(re.search(r"/budgets/(\d+)/edit", r.headers["Location"]).group(1))
    # and one from this year's actuals plus 10%
    r = c.post("/budgets/new", data={"name": "From actuals", "fy_start": "2027-01-01", "branch_id": b["id"],
                                     "start": "actuals", "pct": "10"})
    bid3 = int(re.search(r"/budgets/(\d+)/edit", r.headers["Location"]).group(1))
    with app.app_context():
        assert q("SELECT amount FROM budget_lines WHERE budget_id = ? AND account_id = ? AND period = '2027-03'",
                 (bid2, acc["6200"]), one=True)[0] == 2500500
        nov = actuals(b["id"], None, "2026-11-01", "2026-11-30")[acc["4200"]]["2026-11"]
        got = q("SELECT amount FROM budget_lines WHERE budget_id = ? AND account_id = ? AND period = '2027-11'",
                (bid3, acc["4200"]), one=True)[0]
        assert got == round(nov * 1.1 / 10) * 10
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for url in ("/budgets/", f"/budgets/{bid}", f"/budgets/{bid}?scope=year", f"/budgets/{bid}?scope=month&upto=2026-11",
                    f"/budgets/{bid}/edit", "/budgets/new", f"/budgets/{bid}?export=csv"):
            assert c.get(url).status_code == 200, url
        assert c.get("/budgets/report").status_code == 302
    r = c.post(f"/budgets/{bid3}/delete")
    with app.app_context():
        assert not q("SELECT 1 FROM budget_lines WHERE budget_id = ?", (bid3,))


def test_coa_hierarchy():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.ledger import create_move, profit_loss, trial_balance
        from vertex.coa import nest, tree
        acc = {r["code"]: r for r in q("SELECT * FROM accounts")}
        # the seeded chart is grouped: 1010 Cash on Hand sits under 111 > 11 > 1
        assert acc["111"]["is_group"] and acc["1010"]["parent_id"] == acc["111"]["id"]
        assert acc["111"]["parent_id"] == acc["11"]["id"] and acc["11"]["parent_id"] == acc["1"]["id"]
        assert not q("SELECT 1 FROM accounts WHERE is_group = 0 AND parent_id IS NULL")
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        mj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'MISC'", (b["id"],), one=True)[0]
        dbx("UPDATE company SET lock_date = NULL")
        # nothing can be posted to a group
        try:
            create_move(b["id"], mj, "2026-11-02", [{"account_id": acc["111"]["id"], "debit": 1000, "credit": 0},
                                                    {"account_id": acc["3100"]["id"], "debit": 0, "credit": 1000}],
                        1, post=True)
            raise AssertionError("posted to a group")
        except Exception as e:
            assert "group" in str(e)
        dbcommit()
        # trial balance: group lines carry the total of everything under them; the grand total is unchanged
        tb = trial_balance([b["id"]], "2026-01-01", "2026-12-31")
        flat = {r["account"]["id"]: r for r in tb["rows"]}
        rows = nest(tb["rows"], ("opening", "debit", "credit", "closing"))
        g111 = [r for r in rows if r.get("group") and r["group"]["code"] == "111"][0]
        cash = sum(r["closing"] for a, r in flat.items() if r["account"]["parent_id"] == acc["111"]["id"])
        assert g111["closing"] == cash and g111["depth"] == 3
        top = [r for r in rows if r.get("depth") == 1]
        assert sum(r["closing"] for r in top) == sum(r["closing"] for r in tb["rows"]) == 0
        assert all(r.get("group") for r in nest(tb["rows"], ("opening", "debit", "credit", "closing"), 1))
        # profit & loss: the group that is the whole section (4 Revenue) is not repeated
        st = profit_loss([b["id"]], "2026-01-01", "2026-12-31")
        rows = nest(st.rows, ("vals", "elim", "total"))
        codes = [r["group"]["code"] for r in rows if r.get("group")]
        assert "4" not in codes and "41" not in codes and "61" in codes and "6" not in codes
        net = [r for r in rows if r["kind"] == "grand"][0]
        assert net["total"] == [r for r in st.rows if r["kind"] == "grand"][0]["total"]
        assert [a["code"] for a, d in tree({a["id"]: a for a in acc.values()})][:3] == ["1", "11", "111"]
    # chart page, reports with levels, budget levels
    page = c.get("/setup/accounts").get_data(as_text=True)
    assert "Cash and Bank" in page and "data-depth=\"3\"" in page
    for url in ("/reports/trial-balance?level=1", "/reports/trial-balance?level=2&export=csv", "/reports/profit-loss?level=1",
                "/reports/balance-sheet?level=2", "/reports/balance-sheet", "/reports/profit-loss/cost-centres?level=1"):
        assert c.get(url).status_code == 200, url
    # rules on the form: a group must be the parent, types must match, an account with entries stays an account
    r = c.post(f"/setup/accounts/{acc['1020']['id']}", data={"code": "1020", "name_en": "Bank Accounts", "kind": "asset_cash",
                                                          "is_group": "1", "parent_id": acc["111"]["id"], "active": "1"})
    assert "cannot become a group" in r.get_data(as_text=True)
    r = c.post("/setup/accounts/new", data={"code": "6110", "name_en": "Bad", "kind": "expense",
                                            "parent_id": acc["111"]["id"], "active": "1"})
    assert "does not match" in r.get_data(as_text=True)
    r = c.post("/setup/accounts/new", data={"code": "6110", "name_en": "Bad", "kind": "expense",
                                            "parent_id": acc["6100"]["id"], "active": "1"})
    assert "must be a group" in r.get_data(as_text=True)
    r = c.post("/setup/accounts/new", data={"code": "613", "name_en": "Staff Costs", "kind": "expense", "is_group": "1",
                                            "parent_id": acc["6"]["id"], "active": "1"})
    assert r.status_code == 302
    with app.app_context():
        g613 = q("SELECT id FROM accounts WHERE code = '613'", one=True)[0]
    r = c.post(f"/setup/accounts/{acc['6100']['id']}", data={"code": "6100", "name_en": "Salaries & Wages", "kind": "expense",
                                                          "parent_id": g613, "active": "1"})
    assert r.status_code == 302
    r = c.post(f"/setup/accounts/{g613}", data={"code": "613", "name_en": "Staff Costs", "kind": "expense",
                                               "parent_id": acc["6100"]["id"], "is_group": "1", "active": "1"})
    assert "must be a group" in r.get_data(as_text=True)
    r = c.post(f"/setup/accounts/{acc['6']['id']}", data={"code": "6", "name_en": "Operating Expenses", "kind": "expense",
                                                         "is_group": "1", "parent_id": g613, "active": "1"})
    assert "under itself" in r.get_data(as_text=True)
    # pickers leave groups out
    form = c.get("/entries/new").get_data(as_text=True)
    assert f'value="{acc["111"]["id"]}"' not in form or "Cash and Bank" not in form
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        assert c.get("/setup/accounts").status_code == 200
        assert c.get("/budgets/1?level=1").status_code in (200, 404)


def test_hr_payroll():
    from datetime import date
    app = make_app()
    c = app.test_client()
    login(c)

    def gl(code, bid):
        return q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                 "JOIN accounts a ON a.id = ml.account_id WHERE m.state = 'posted' AND a.code = ? AND ml.branch_id = ?",
                 (code, bid), one=True)[0]

    def ledger_matches_employees():
        from vertex.payroll import loan_balance
        for b in q("SELECT id FROM branches"):
            emps = q("SELECT * FROM employees WHERE branch_id = ?", (b["id"],))
            assert -gl("2500", b["id"]) == sum(e["eosb_booked"] for e in emps), b["id"]
            assert -gl("2420", b["id"]) == sum(e["leave_booked"] for e in emps), b["id"]
            assert gl("1350", b["id"]) == sum(loan_balance(e["id"]) for e in emps), b["id"]

    with app.app_context():
        from vertex.payroll import eosb, leave_balance, compute_line, settings, loan_balance
        e = {"join_date": "2019-01-01", "law": "ksa", "basic": 8000000, "housing": 2000000, "transport": 0,
             "other_allowance": 0, "annual_leave_days": 0, "leave_opening": 0, "id": -1, "is_national": 1,
             "leave_date": None, "eosb_booked": 0, "leave_booked": 0}
        st = settings()
        years, wage, full, due = eosb(e, "2025-12-31", "termination", st)       # 7 years on 10,000
        assert round(years) == 7 and wage == 10000000
        assert abs(full - (5 * 5000000 + (years - 5) * 10000000)) < 1
        assert abs(eosb(e, "2025-12-31", "resignation", st)[3] - full * 2 / 3) < 1
        assert eosb(e, "2020-06-30", "resignation", st)[3] == 0                  # under 2 years
        assert abs(leave_balance(e, "2019-12-31") - 21) < 0.1                     # 21 days in year one
        ln = compute_line(e, "2025-03", {}, st, 2)
        assert ln["gosi_employee"] == 975000 and ln["gosi_employer"] == 1175000   # 9.75% / 11.75% of 10,000
        assert ln["net"] == 10000000 - 975000
        ex = dict(e, is_national=0)
        assert compute_line(ex, "2025-03", {}, st, 2)["gosi_employer"] == 200000  # 2% for non-Saudis
        ae = dict(e, law="uae", basic=3000000, housing=1000000, join_date="2022-01-01")
        ay = eosb(ae, "2024-12-31")
        assert abs(ay[2] - 21 * 100000 * ay[0]) < 1 and round(ay[0]) == 3          # 21 days' basic a year
        joiner = compute_line(dict(e, join_date="2025-03-17"), "2025-03", {}, st, 2)
        assert joiner["days"] == 15 and joiner["basic"] == 4000000                # half a month
        ledger_matches_employees()
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        bank = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'BNK'", (b["id"],), one=True)[0]
        n_runs = q("SELECT COUNT(*) FROM payroll_runs WHERE branch_id = ? AND state = 'paid'", (b["id"],), one=True)[0]
        assert n_runs == date.today().month - 1
    for url in ("/hr/employees", "/hr/employees?state=all", "/hr/employees/new", "/hr/employees/1", "/hr/leave",
                "/hr/leave/new", "/hr/loans", "/hr/loans/new", "/hr/loans/1", "/hr/payroll", "/hr/payroll/1",
                "/hr/payslip/1", "/hr/payroll/1/wps.csv", "/hr/settlements", "/hr/settlements/new",
                "/hr/reports/provisions", "/hr/reports/provisions?export=csv", "/hr/reports/documents", "/hr/settings"):
        for lang in ("en", "ar"):
            c.get(f"/lang/{lang}")
            r = c.get(url)
            assert r.status_code == 200, (url, lang, r.status_code)
    c.get("/lang/en")
    # a new joiner, a loan and an unpaid day, then this month's payroll
    r = c.post("/hr/employees/new", data={"code": "T100", "name_en": "Test Employee", "branch_id": b["id"],
                                          "join_date": date.today().replace(day=1).isoformat(), "law": "ksa",
                                          "basic": "4,000", "housing": "1,000", "transport": "500"})
    assert r.status_code == 302, r.get_data(as_text=True)[:500]
    eid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    r = c.post("/hr/loans/save", data={"employee_id": eid, "date": date.today().isoformat(), "amount": "1000",
                                       "installment": "400", "start_period": date.today().isoformat()[:7],
                                       "journal_id": bank, "action": "disburse"})
    assert r.status_code == 302
    period = date.today().isoformat()[:7]
    r = c.post("/hr/payroll/new", data={"branch_id": b["id"], "period": period})
    assert r.status_code == 302 and "/hr/payroll/" in r.headers["Location"]
    rid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        line = q("SELECT * FROM payroll_lines WHERE run_id = ? AND employee_id = ?", (rid, eid), one=True)
        assert line["loan_deduction"] == 400000 and line["gosi_employer"] == 100000  # 2% of 5,000
    r = c.post(f"/hr/payroll/{rid}/post", data={f"ot_{line['id']}": "10", f"add_{line['id']}": "250",
                                                f"ded_{line['id']}": "", f"unp_{line['id']}": "1"})
    assert r.status_code == 302
    with app.app_context():
        run = q("SELECT * FROM payroll_runs WHERE id = ?", (rid,), one=True)
        assert run["state"] == "posted"
        line = q("SELECT * FROM payroll_lines WHERE id = ?", (line["id"],), one=True)
        # overtime: 10 h x (5,500 / 240 + 50% x 4,000 / 240); one unpaid day = 5,500 / 30
        assert line["overtime"] == round((5500 / 240 + 0.5 * 4000 / 240) * 10, 2) * 1000
        assert line["unpaid_deduction"] == round(5500 / 30, 2) * 1000
        tot = q("SELECT SUM(gross), SUM(net), SUM(gosi_employee + gosi_employer) FROM payroll_lines WHERE run_id = ?",
                (rid,), one=True)
        mv = {r["code"]: r["bal"] for r in q("SELECT a.code, SUM(debit - credit) AS bal FROM move_lines ml JOIN accounts a "
                                             "ON a.id = ml.account_id WHERE ml.move_id = ? GROUP BY a.code", (run["move_id"],))}
        assert mv["6100"] == tot[0] and mv["2400"] == -tot[1] and mv["2410"] == -tot[2]
        ledger_matches_employees()
    assert c.post(f"/hr/payroll/{rid}/pay", data={"journal_id": bank, "date": date.today().isoformat()}).status_code == 302
    with app.app_context():
        assert q("SELECT state FROM payroll_runs WHERE id = ?", (rid,), one=True)[0] == "paid"
        assert gl("2400", b["id"]) == 0                          # every posted payroll is paid
    # leave: approved annual leave reduces the balance
    with app.app_context():
        e1 = q("SELECT * FROM employees WHERE code = 'E003'", one=True)
        before = leave_balance(e1, date.today())
    r = c.post("/hr/leave/new", data={"employee_id": e1["id"], "kind": "annual", "date_from": date.today().isoformat(),
                                      "date_to": date.today().isoformat(), "days": "3", "action": "approve"})
    assert r.status_code == 302
    with app.app_context():
        assert abs(leave_balance(e1, date.today()) - (before - 3)) < 0.01
    # final settlement on resignation: provisions used up, the difference released, loan recovered
    r = c.post("/hr/settlements/new", data={"employee_id": eid, "leave_date": date.today().isoformat(),
                                            "reason": "resignation"})
    assert r.status_code == 302
    sid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        s = q("SELECT * FROM eos_settlements WHERE id = ?", (sid,), one=True)
        assert s["eosb_due"] == 0 and s["loan_balance"] == 600000        # under 2 years; 1,000 - 400 repaid
    assert c.post(f"/hr/settlements/{sid}/post").status_code == 302
    with app.app_context():
        emp = q("SELECT * FROM employees WHERE id = ?", (eid,), one=True)
        assert emp["state"] == "left" and emp["eosb_booked"] == 0 and loan_balance(eid) == 0
        ledger_matches_employees()
    # a Saudi employee terminated: the full benefit is paid
    with app.app_context():
        e6 = q("SELECT * FROM employees WHERE code = 'E006'", one=True)
    r = c.post("/hr/settlements/new", data={"employee_id": e6["id"], "leave_date": date.today().isoformat(),
                                            "reason": "termination", "additions": "2,000"})
    sid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    c.post(f"/hr/settlements/{sid}/post")
    jed_bank = None
    with app.app_context():
        s = q("SELECT * FROM eos_settlements WHERE id = ?", (sid,), one=True)
        assert s["state"] == "posted" and s["eosb_due"] == s["eosb_full"] > 0
        assert s["net"] == s["eosb_due"] + s["leave_pay"] + 2000000
        ledger_matches_employees()
        jed_bank = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'BNK'", (e6["branch_id"],), one=True)[0]
    c.post(f"/hr/settlements/{sid}/pay", data={"journal_id": jed_bank})
    with app.app_context():
        assert q("SELECT state FROM eos_settlements WHERE id = ?", (sid,), one=True)[0] == "paid"
        assert gl("2400", e6["branch_id"]) == 0
        bad = q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        assert not bad
    for url in (f"/hr/settlements/{sid}", f"/hr/employees/{eid}", f"/hr/payroll/{rid}", "/"):
        assert c.get(url).status_code == 200, url


def test_foreign_currency():
    from datetime import date
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.fx import open_items, post_revaluation, cancel_revaluation
        from vertex.ledger import create_payment, post_invoice
        dbx("UPDATE company SET lock_date = NULL")
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        bank = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'BNK'", (b["id"],), one=True)[0]
        cust = q("SELECT id FROM partners WHERE name_en = 'Atlas Energy Inc.'", one=True)[0]
        vend = q("SELECT id FROM partners WHERE name_en = 'Rhein Industrietechnik GmbH'", one=True)[0]
        dbcommit()

    def gl(code, partner=None, move=None):
        sql = ("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
               "JOIN accounts a ON a.id = ml.account_id WHERE m.state = 'posted' AND a.code = ?")
        args = [code]
        if partner:
            sql += " AND ml.partner_id = ?"
            args.append(partner)
        if move:
            sql += " AND ml.move_id = ?"
            args.append(move)
        return q(sql, args, one=True)[0]

    for url in ("/fx/", "/invoices/out_invoice/new", "/setup/partners/new"):
        for lang in ("en", "ar"):
            c.get(f"/lang/{lang}")
            assert c.get(url).status_code == 200, (url, lang)
    c.get("/lang/en")
    # a USD invoice typed in: 1,000 USD at 3.75 -> receivable 3,750 SAR
    with app.app_context():
        inc = q("SELECT id FROM accounts WHERE code = '4200'", one=True)[0]
        ij = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'INV'", (b["id"],), one=True)[0]
    r = c.post("/invoices/out_invoice/new", data={"partner_id": cust, "branch_id": b["id"], "journal_id": ij,
                                                   "date": "2026-11-02", "due_date": "2026-12-02", "currency": "USD",
                                                   "rate": "3.75", "description": ["Consulting"], "account_id": [inc],
                                                   "quantity": ["1"], "price_unit": ["1000"], "tax_id": [""],
                                                   "action": "post"})
    assert r.status_code == 302, r.get_data(as_text=True)[:400]
    inv_id = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
        assert inv["currency"] == "USD" and inv["amount_total"] == 1000000 and inv["amount_total_base"] == 3750000
        assert gl("4200", move=inv["move_id"]) == -3750000
        ar_line = q("SELECT * FROM move_lines WHERE move_id = ? AND debit > 0", (inv["move_id"],), one=True)
        assert ar_line["currency"] == "USD" and ar_line["amount_currency"] == 1000000
    assert "USD" in c.get(f"/invoices/view/{inv_id}").get_data(as_text=True)
    assert c.get(f"/payments/new?invoice_id={inv_id}").status_code == 200
    # paid at 3.80: bank 3,800, receivable cleared by 3,750, realized gain 50
    r = c.post(f"/payments/new?invoice_id={inv_id}", data={
        "partner_type": "customer", "payment_type": "inbound", "partner_id": cust, "branch_id": b["id"],
        "journal_id": bank, "date": "2026-11-10", "amount": "1000", "currency": "USD", "rate": "3.80",
        "invoice_id": inv_id})
    assert r.status_code == 302
    with app.app_context():
        p = q("SELECT * FROM payments WHERE invoice_id = ?", (inv_id,), one=True)
        assert p["settled_base"] == 3750000 and p["currency"] == "USD"
        assert gl("1020", move=p["move_id"]) == 3800000 and gl("4920", move=p["move_id"]) == -50000
        assert q("SELECT 1 FROM invoices i WHERE id = ?", (inv_id,), one=True)
        from vertex.ledger import invoice_residuals
        assert invoice_residuals([inv_id])[inv_id] == 0 and invoice_residuals([inv_id], base=True)[inv_id] == 0
        # a EUR bill of 1,000 at 4.00, 400 paid at 4.10 (loss 40), the rest revalued at 4.20, then paid at 4.15
        pay_acc = q("SELECT id FROM accounts WHERE code = '5100'", one=True)[0]
        bj = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'BILL'", (b["id"],), one=True)[0]
        bid = dbx("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, state, amount_untaxed, "
                  "amount_tax, amount_total, currency, rate) VALUES (?,?, 'in_invoice', ?, '2026-11-03', '2026-12-03', "
                  "'draft', 1000000, 0, 1000000, 'EUR', 4.0)", (b["id"], bj, vend))
        dbx("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, subtotal, "
            "tax_amount) VALUES (?, 0, 'Pumps', ?, 1, 1000000, 1000000, 0)", (bid, pay_acc))
        post_invoice(bid, 1)
        base = {"branch_id": b["id"], "journal_id": bank, "partner_id": vend, "partner_type": "vendor",
                "payment_type": "outbound", "invoice_id": bid, "currency": "EUR"}
        p1 = create_payment({**base, "date": "2026-11-05", "amount": 400000, "rate": 4.10}, 1)
        m1 = q("SELECT move_id FROM payments WHERE id = ?", (p1,), one=True)[0]
        assert gl("1020", move=m1) == -1640000 and gl("6920", move=m1) == 40000
        assert gl("2100", partner=vend, move=m1) == 1600000
        dbx("INSERT OR REPLACE INTO exchange_rates (currency, date, rate) VALUES ('EUR', '2026-11-29', 4.20)")
        items, _ = open_items(b["id"], "2026-11-30")
        mine = [it for it in items if it["invoice"]["id"] == bid][0]
        assert mine["residual"] == 600000 and mine["carrying"] == -2400000 and mine["revalued"] == -2520000
        rid = post_revaluation(b["id"], "2026-11-30", 1)
        rv = q("SELECT * FROM fx_revaluations WHERE id = ?", (rid,), one=True)
        assert gl("6925", move=rv["move_id"]) >= 120000 and gl("6925", move=rv["reversal_move_id"]) <= -120000
        assert q("SELECT date FROM moves WHERE id = ?", (rv["reversal_move_id"],), one=True)[0] == "2026-12-01"
        dbcommit()
    for url in ("/fx/?date=2026-11-30", f"/fx/{rid}"):
        assert c.get(url).status_code == 200, url
    with app.app_context():
        p2 = create_payment({**base, "date": "2026-12-05", "amount": 600000, "rate": 4.15}, 1)
        m2 = q("SELECT move_id FROM payments WHERE id = ?", (p2,), one=True)[0]
        assert gl("2100", partner=vend, move=m2) == 2400000 and gl("6920", move=m2) == 90000
        # every document of the vendor is either open or fully cleared on the payable account
        bill_lines = q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines WHERE move_id IN "
                       "(SELECT move_id FROM invoices WHERE id = ? UNION SELECT move_id FROM payments WHERE invoice_id = ?) "
                       "AND account_id = (SELECT id FROM accounts WHERE code = '2100')", (bid, bid), one=True)[0]
        assert bill_lines == 0
        # a payment in the wrong currency is refused
        bid2 = dbx("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, state, amount_untaxed, amount_tax, "
                   "amount_total, currency, rate) VALUES (?,?, 'in_invoice', ?, '2026-11-03', 'draft', 500000, 0, 500000, "
                   "'EUR', 4.0)", (b["id"], bj, vend))
        dbx("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, subtotal, "
            "tax_amount) VALUES (?, 0, 'Parts', ?, 1, 500000, 500000, 0)", (bid2, pay_acc))
        post_invoice(bid2, 1)
        try:
            create_payment({**base, "invoice_id": bid2, "currency": None, "date": "2026-11-05", "amount": 100000}, 1)
            assert False, "SAR payment of a EUR bill accepted"
        except Exception as e:
            assert "own currency" in str(e)
        cancel_revaluation(rid, 1)
        assert not q("SELECT 1 FROM moves WHERE id = ?", (rv["move_id"],), one=True)
        bad = q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        assert not bad
    for url in (f"/invoices/view/{bid}", "/reports/aged/payable", "/reports/aged/receivable", "/statements/vendor/" + str(vend), "/statements/customer/" + str(cust),
                "/payments/", f"/invoices/print/{inv_id}", f"/invoices/pdf/{inv_id}"):
        r = c.get(url)
        assert r.status_code == 200, (url, r.status_code)


def test_cheques():
    from datetime import date, timedelta
    app = make_app()
    c = app.test_client()
    login(c)

    def gl(code, bid=None):
        return q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                 "JOIN accounts a ON a.id = ml.account_id WHERE m.state = 'posted' AND a.code = ?"
                 + (" AND ml.branch_id = ?" if bid else ""), (code, bid) if bid else (code,), one=True)[0]

    def books_match_register():
        open_r = q("SELECT COALESCE(SUM(amount), 0) FROM cheques WHERE direction = 'received' "
                   "AND state IN ('registered', 'deposited')", one=True)[0]
        open_i = q("SELECT COALESCE(SUM(amount), 0) FROM cheques WHERE direction = 'issued' "
                   "AND state IN ('registered', 'deposited')", one=True)[0]
        assert gl("1150") == open_r and gl("2160") == -open_i, (gl("1150"), open_r, gl("2160"), open_i)

    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.ledger import invoice_residuals
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        books_match_register()
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        bank = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'BNK'", (b["id"],), one=True)[0]
        invs = q("SELECT * FROM invoices WHERE branch_id = ? AND kind = 'out_invoice' AND state = 'posted' "
                 "AND currency IS NULL ORDER BY date DESC", (b["id"],))
        res = invoice_residuals([i["id"] for i in invs])
        inv = [i for i in invs if res[i["id"]] > 0][0]
        due = res[inv["id"]]
        bills = q("SELECT * FROM invoices WHERE branch_id = ? AND kind = 'in_invoice' AND state = 'posted' "
                  "AND currency IS NULL ORDER BY date DESC", (b["id"],))
        bres = invoice_residuals([i["id"] for i in bills])
        bill = [i for i in bills if bres[i["id"]] > 0][0]
    for url in ("/cheques/received", "/cheques/issued", "/cheques/received?state=all", "/cheques/issued/new",
                f"/cheques/received/new?invoice_id={inv['id']}", "/cheques/view/1"):
        for lang in ("en", "ar"):
            c.get(f"/lang/{lang}")
            assert c.get(url).status_code == 200, (url, lang)
    c.get("/lang/en")
    today = date.today()
    pdc = (today + timedelta(days=20)).isoformat()
    r = c.post(f"/cheques/received/new?invoice_id={inv['id']}", data={
        "partner_id": inv["partner_id"], "branch_id": b["id"], "cheque_no": "777001", "bank_name": "Riyad Bank",
        "issue_date": today.isoformat(), "cheque_date": pdc, "amount": f"{due / 1000:.2f}", "bank_journal_id": bank})
    assert r.status_code == 302 and "/cheques/view/" in r.headers["Location"], r.get_data(as_text=True)[:300]
    cid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        assert invoice_residuals([inv["id"]])[inv["id"]] == 0          # the invoice shows as paid
        books_match_register()
        before_bank = gl("1020", b["id"])
    # a post-dated cheque cannot clear before its date
    r = c.post(f"/cheques/view/{cid}/clear", data={"date": today.isoformat(), "bank_journal_id": bank},
               follow_redirects=True)
    assert "cannot clear before" in r.get_data(as_text=True)
    c.post(f"/cheques/view/{cid}/deposit", data={"bank_journal_id": bank})
    c.post(f"/cheques/view/{cid}/clear", data={"date": pdc, "bank_journal_id": bank})
    with app.app_context():
        ch = q("SELECT * FROM cheques WHERE id = ?", (cid,), one=True)
        assert ch["state"] == "cleared" and gl("1020", b["id"]) == before_bank + due
        books_match_register()
    # a second invoice paid by a cheque that bounces: the invoice is open again
    with app.app_context():
        res = invoice_residuals([i["id"] for i in invs])
        inv2 = [i for i in invs if res[i["id"]] > 0][0]
        due2 = res[inv2["id"]]
        recv_before = gl("1100", b["id"])
    r = c.post(f"/cheques/received/new?invoice_id={inv2['id']}", data={
        "partner_id": inv2["partner_id"], "branch_id": b["id"], "cheque_no": "777002", "issue_date": today.isoformat(),
        "cheque_date": today.isoformat(), "amount": f"{due2 / 1000:.2f}"})
    cid2 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    c.post(f"/cheques/view/{cid2}/bounce", data={"date": today.isoformat(), "reason": "Insufficient funds"})
    with app.app_context():
        assert q("SELECT state FROM cheques WHERE id = ?", (cid2,), one=True)[0] == "bounced"
        assert invoice_residuals([inv2["id"]])[inv2["id"]] == due2
        assert gl("1100", b["id"]) == recv_before
        books_match_register()
    # a post-dated cheque to a vendor, then cancelled
    r = c.post(f"/cheques/issued/new?invoice_id={bill['id']}", data={
        "partner_id": bill["partner_id"], "branch_id": b["id"], "cheque_no": "990001", "issue_date": today.isoformat(),
        "cheque_date": pdc, "amount": "100", "bank_journal_id": bank})
    cid3 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        assert invoice_residuals([bill["id"]])[bill["id"]] == bres[bill["id"]] - 100000
        books_match_register()
    c.post(f"/cheques/view/{cid3}/cancel", data={"date": today.isoformat()})
    with app.app_context():
        assert invoice_residuals([bill["id"]])[bill["id"]] == bres[bill["id"]]
        books_match_register()
        bad = q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        assert not bad
    for url in (f"/cheques/view/{cid}", f"/cheques/view/{cid2}", f"/invoices/view/{inv2['id']}", "/"):
        assert c.get(url).status_code == 200, url


def test_petty_cash():
    from datetime import date
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.pettycash import fund_balance
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        bank = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'BNK'", (b["id"],), one=True)[0]
        acc = {r["code"]: r["id"] for r in q("SELECT id, code FROM accounts")}
        vat = q("SELECT id FROM taxes WHERE name_en = 'VAT 15% (Purchases)'", one=True)[0]
    r = c.post("/petty-cash/funds/new", data={"name_en": "Site A", "branch_id": b["id"], "custodian": "Ali",
                                               "float_amount": "2,000"})
    assert r.status_code == 302
    fid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    today = date.today().isoformat()
    # spending before any cash is in the fund is refused
    r = c.post(f"/petty-cash/funds/{fid}/vouchers/new", data={"date": today, "account_id": [acc["6600"]],
               "description": ["Stationery"], "amount": ["100"], "tax_id": [vat], "action": "post"}, follow_redirects=True)
    assert "Not enough cash" in r.get_data(as_text=True)
    c.post(f"/petty-cash/funds/{fid}/topup", data={"bank_journal_id": bank, "amount": "2,000", "date": today})
    r = c.post(f"/petty-cash/funds/{fid}/vouchers/new", data={
        "date": today, "payee": "Jarir", "account_id": [acc["6600"], acc["6300"]], "description": ["Stationery", "Water"],
        "amount": ["100", "50"], "tax_id": [vat, ""], "receipt_ref": ["A1", "A2"], "action": "post"})
    assert r.status_code == 302
    vid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        f = q("SELECT * FROM petty_cash_funds WHERE id = ?", (fid,), one=True)
        v = q("SELECT * FROM petty_cash_vouchers WHERE id = ?", (vid,), one=True)
        assert v["state"] == "posted" and v["total"] == 165000                 # 100 + 15 VAT + 50
        assert fund_balance(f) == 2000000 - 165000
        lines = {r["account_id"]: r for r in q("SELECT * FROM move_lines WHERE move_id = ?", (v["move_id"],))}
        assert lines[acc["6600"]]["debit"] == 100000 and lines[acc["1400"]]["debit"] == 15000
        assert lines[f["account_id"]]["credit"] == 165000
    # count finds 5 short: Dr Cash Over / Short 5, Cr Petty Cash 5
    c.post(f"/petty-cash/funds/{fid}/count", data={"counted": "1,830", "date": today})
    with app.app_context():
        assert fund_balance(f) == 1830000
        short = q("SELECT amount FROM petty_cash_topups WHERE fund_id = ? AND kind = 'count'", (fid,), one=True)[0]
        assert short == -5000
    for url in ("/petty-cash/", f"/petty-cash/funds/{fid}", f"/petty-cash/vouchers/{vid}",
                f"/petty-cash/funds/{fid}/vouchers/new"):
        for lang in ("en", "ar"):
            c.get(f"/lang/{lang}")
            assert c.get(url).status_code == 200, (url, lang)
    with app.app_context():
        bad = q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        assert not bad


def test_stock_transfers():
    from datetime import date
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.stock import quant, stock_value_by_branch
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        ruh = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
        jed = q("SELECT * FROM branches WHERE code = 'JED'", one=True)
        main = q("SELECT * FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (ruh["id"],), one=True)
        show = q("SELECT * FROM warehouses WHERE code = 'SHW-RUH'", one=True)
        jwh = q("SELECT * FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (jed["id"],), one=True)
        prod = q("SELECT product_id FROM stock_quants WHERE warehouse_id = ? AND qty >= 4 ORDER BY qty DESC", (main["id"],),
                 one=True)[0]
        q0, v0 = quant(prod, main["id"])

    def gl(code, bid):
        return q("SELECT COALESCE(SUM(debit - credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                 "JOIN accounts a ON a.id = ml.account_id WHERE m.state = 'posted' AND a.code = ? AND ml.branch_id = ?",
                 (code, bid), one=True)[0]

    def stock_matches_ledger():
        vals = stock_value_by_branch([ruh["id"], jed["id"]])
        for b in (ruh["id"], jed["id"]):
            assert vals.get(b, 0) == gl("1200", b) + gl("1205", b), b
        sent = q("SELECT COALESCE(SUM(l.value), 0) FROM stock_transfer_lines l JOIN stock_transfers t ON t.id = l.transfer_id "
                 "WHERE t.state = 'in_transit'", one=True)[0]
        assert gl("1210", ruh["id"]) + gl("1210", jed["id"]) == sent

    with app.app_context():
        stock_matches_ledger()
    for url in ("/stock-transfers/", "/stock-transfers/new", "/stock-transfers/1"):
        for lang in ("en", "ar"):
            c.get(f"/lang/{lang}")
            assert c.get(url).status_code == 200, (url, lang)
    c.get("/lang/en")
    today = date.today().isoformat()
    # inside the branch: at once, no entry
    r = c.post("/stock-transfers/new", data={"from_warehouse_id": main["id"], "to_warehouse_id": show["id"], "date": today,
                                             "product_id": [prod], "quantity": ["2"], "action": "send"})
    assert r.status_code == 302
    t1 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        tr = q("SELECT * FROM stock_transfers WHERE id = ?", (t1,), one=True)
        assert tr["state"] == "done" and tr["send_move_id"] is None
        assert quant(prod, main["id"])[0] == q0 - 2
        stock_matches_ledger()
        q1, v1 = quant(prod, main["id"])
    # to Jeddah: in transit, then received
    r = c.post("/stock-transfers/new", data={"from_warehouse_id": main["id"], "to_warehouse_id": jwh["id"], "date": today,
                                             "product_id": [prod], "quantity": ["1"], "action": "send"})
    t2 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        tr = q("SELECT * FROM stock_transfers WHERE id = ?", (t2,), one=True)
        value = q("SELECT value FROM stock_transfer_lines WHERE transfer_id = ?", (t2,), one=True)[0]
        assert tr["state"] == "in_transit" and value > 0
        stock_matches_ledger()
        jq = quant(prod, jwh["id"])
    c.post(f"/stock-transfers/{t2}/receive", data={"date": today})
    with app.app_context():
        assert q("SELECT state FROM stock_transfers WHERE id = ?", (t2,), one=True)[0] == "done"
        assert quant(prod, jwh["id"])[0] == jq[0] + 1 and quant(prod, jwh["id"])[1] == jq[1] + value
        stock_matches_ledger()
    # sent and returned
    r = c.post("/stock-transfers/new", data={"from_warehouse_id": main["id"], "to_warehouse_id": jwh["id"], "date": today,
                                             "product_id": [prod], "quantity": ["1"], "action": "send"})
    t3 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    c.post(f"/stock-transfers/{t3}/return", data={"date": today})
    with app.app_context():
        assert q("SELECT state FROM stock_transfers WHERE id = ?", (t3,), one=True)[0] == "cancelled"
        stock_matches_ledger()
        # more than on hand is refused
        from vertex.wtransfers import create, send
        tid = create(main["id"], show["id"], today, [{"product_id": prod, "quantity": 10 ** 6}])
        try:
            send(tid)
            assert False
        except Exception as e:
            assert "Not enough stock" in str(e)
        bad = q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        assert not bad
    assert c.get(f"/stock-transfers/{t2}").status_code == 200
    assert c.get("/inventory/moves").status_code == 200


def test_exports():
    import io
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        ids = {k: q(sql, one=True)[0] for k, sql in {
            "move": "SELECT id FROM moves WHERE state = 'posted' LIMIT 1", "pay": "SELECT id FROM payments LIMIT 1",
            "pcv": "SELECT id FROM petty_cash_vouchers LIMIT 1", "chq": "SELECT id FROM cheques LIMIT 1",
            "run": "SELECT id FROM payroll_runs LIMIT 1", "slip": "SELECT id FROM payroll_lines LIMIT 1",
            "fx": "SELECT id FROM fx_revaluations LIMIT 1", "acc": "SELECT id FROM accounts WHERE code = '1020'",
            "inv": "SELECT id FROM invoices WHERE state = 'posted' LIMIT 1",
            "eos": "SELECT id FROM employees LIMIT 1"}.items()}
    reports = ["/reports/profit-loss", "/reports/balance-sheet", "/reports/trial-balance",
               f"/reports/general-ledger?account_id={ids['acc']}", "/reports/aged/receivable?detail=1",
               "/reports/aged/payable", "/reports/vat", "/reports/sales?by=customer", "/reports/stock-matching",
               "/reports/profit-loss/cost-centres", "/hr/reports/provisions", "/hr/reports/documents",
               f"/entries/{ids['move']}", f"/payments/{ids['pay']}", f"/petty-cash/vouchers/{ids['pcv']}",
               f"/cheques/view/{ids['chq']}", f"/hr/payroll/{ids['run']}", f"/hr/payslip/{ids['slip']}"]
    if ids["fx"]:
        reports.append(f"/fx/{ids['fx']}")
    from openpyxl import load_workbook
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        for url in reports:
            sep = "&" if "?" in url else "?"
            page = c.get(url)
            assert page.status_code == 200, (url, lang)
            for kind, mime in (("pdf", "application/pdf"), ("xlsx", "spreadsheetml"), ("csv", "text/csv"),
                               ("print", "application/pdf")):
                r = c.get(f"{url}{sep}export={kind}")
                assert r.status_code == 200 and mime in r.content_type, (url, kind, lang, r.status_code, r.content_type)
                if kind == "pdf":
                    assert r.data[:4] == b"%PDF"
                if kind == "xlsx":
                    load_workbook(io.BytesIO(r.data))
    c.get("/lang/en")
    for url in (f"/invoices/pdf/{ids['inv']}", "/setup/company"):
        assert c.get(url).status_code == 200, url
    # logo upload and removal
    from PIL import Image
    buf = io.BytesIO()
    Image.new("RGBA", (300, 100), (25, 23, 254, 255)).save(buf, "PNG")
    buf.seek(0)
    r = c.post("/setup/company/logo", data={"logo": (buf, "logo.png")}, content_type="multipart/form-data")
    assert r.status_code == 302
    assert c.get("/setup/company/logo.png").status_code == 200
    assert c.get("/reports/profit-loss?export=pdf").status_code == 200
    r = c.post("/setup/company/logo", data={"logo": (io.BytesIO(b"not an image"), "x.png")},
               content_type="multipart/form-data", follow_redirects=True)
    assert "not a readable image" in r.get_data(as_text=True)
    c.post("/setup/company/logo", data={"action": "remove"})
    assert c.get("/setup/company/logo.png").status_code == 404


def test_workflow_engine():
    from datetime import date, timedelta
    app = make_app()
    acc, mgr, adm = app.test_client(), app.test_client(), app.test_client()
    login(acc, "accountant", "account123")
    login(mgr, "manager", "manager123")
    login(adm)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        ruh = q("SELECT id FROM branches WHERE code = 'RUH'", one=True)[0]
        wh = q("SELECT id FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (ruh,), one=True)[0]
        gulf = q("SELECT id FROM partners WHERE name_en = 'Gulf Safety Equipment Co.'", one=True)[0]
        prod = q("SELECT id FROM products WHERE code = 'IND-011'", one=True)[0]
        tax = q("SELECT id FROM taxes WHERE name_en = 'VAT 15% (Purchases)'", one=True)[0]
        uid = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}

    def new_po(client, qty, price):
        r = client.post("/orders/purchase/new", data={"partner_id": gulf, "branch_id": ruh, "warehouse_id": wh,
                                                      "date": date.today().isoformat(), "product_id": [prod],
                                                      "description": ["Grinder"], "quantity": [str(qty)],
                                                      "price_unit": [str(price)], "tax_id": [tax], "terms": "Net 30"})
        assert r.status_code == 302, r.get_data(as_text=True)[:300]
        return int(r.headers["Location"].rstrip("/").split("/")[-1])

    def state(oid):
        with app.app_context():
            return q("SELECT state FROM orders WHERE id = ?", (oid,), one=True)[0]

    for url in ("/approvals", "/setup/workflow", "/setup/workflow/rule/new", "/setup/workflow/rule/1",
                "/setup/workflow/roles", "/setup/terms", "/orders/purchase/new"):
        for lang in ("en", "ar"):
            adm.get(f"/lang/{lang}")
            assert adm.get(url).status_code == 200, (url, lang)
    adm.get("/lang/en")
    assert "Standard purchase terms" in adm.get("/orders/purchase/new").get_data(as_text=True)
    # small PO: no rule matches, confirmed at once
    small = new_po(acc, 2, 100)
    acc.post(f"/orders/view/{small}/confirm")
    assert state(small) == "confirmed"
    # 250,000 + VAT: two steps, Procurement Manager (manager) then Finance Manager (admin)
    big = new_po(acc, 10, 25000)
    acc.post(f"/orders/view/{big}/confirm")
    assert state(big) == "to_approve"
    with app.app_context():
        assert q("SELECT terms FROM orders WHERE id = ?", (big,), one=True)[0] == "Net 30"
        from vertex.workflow import inbox
        assert [t["doc_id"] for t in inbox(uid["manager"])].count(big) == 1
        assert not [t for t in inbox(uid["admin"]) if t["doc_id"] == big]          # step 2 not started yet
        assert q("SELECT COUNT(*) FROM notifications WHERE user_id = ?", (uid["manager"],), one=True)[0] >= 1
    # the finance manager cannot act on step 1; the accountant (requester) cannot approve at all
    r = adm.post(f"/orders/view/{big}/approve", data={"comment": "ok"}, follow_redirects=True)
    assert "not an approver" in r.get_data(as_text=True)
    r = acc.post(f"/orders/view/{big}/approve", follow_redirects=True)
    assert state(big) == "to_approve"
    mgr.post(f"/orders/view/{big}/approve", data={"comment": "Price checked"})
    assert state(big) == "to_approve"
    with app.app_context():
        assert [t for t in inbox(uid["admin"]) if t["doc_id"] == big]
    page = adm.get(f"/orders/view/{big}").get_data(as_text=True)
    assert "Finance Manager" in page and "Price checked" in page
    adm.post(f"/orders/view/{big}/approve", data={"comment": ""})
    assert state(big) == "confirmed"
    # rejection needs a reason and returns the PO to draft
    rej = new_po(acc, 10, 25000)
    acc.post(f"/orders/view/{rej}/confirm")
    r = mgr.post(f"/orders/view/{rej}/reject", data={"comment": ""}, follow_redirects=True)
    assert "reason" in r.get_data(as_text=True) and state(rej) == "to_approve"
    mgr.post(f"/orders/view/{rej}/reject", data={"comment": "Get a second quote"})
    assert state(rej) == "draft"
    with app.app_context():
        assert q("SELECT COUNT(*) FROM notifications WHERE user_id = ? AND title LIKE '%rejected%'",
                 (uid["accountant"],), one=True)[0] >= 1
    # separation of duties: the only Procurement Manager submits a big PO -> nobody can approve step 1
    own = new_po(mgr, 10, 25000)
    r = mgr.post(f"/orders/view/{own}/confirm", follow_redirects=True)
    assert "Nobody can approve" in r.get_data(as_text=True) and state(own) == "draft"
    # an administrator may approve in place of the approvers, only with a reason, and it is recorded
    ovr = new_po(acc, 10, 25000)
    acc.post(f"/orders/view/{ovr}/confirm")
    adm.post(f"/orders/view/{ovr}/approve", data={"override": "1", "comment": ""})
    assert state(ovr) == "to_approve"
    adm.post(f"/orders/view/{ovr}/approve", data={"override": "1", "comment": "Manager on leave, urgent"})
    adm.post(f"/orders/view/{ovr}/approve", data={"comment": "ok"})               # step 2 as Finance Manager
    assert state(ovr) == "confirmed"
    with app.app_context():
        assert q("SELECT 1 FROM approval_events WHERE action = 'override_approve' AND comment LIKE '%leave%'", one=True)
        assert q("SELECT 1 FROM audit_log WHERE action = 'override approve'", one=True)
    # out of office: the manager delegates to the admin, who then approves step 1 on the manager's behalf
    mgr.post("/approvals/delegate", data={"delegate_id": uid["admin"], "date_from": date.today().isoformat(),
                                          "date_to": (date.today() + timedelta(days=5)).isoformat()})
    dlg = new_po(acc, 10, 25000)
    acc.post(f"/orders/view/{dlg}/confirm")
    with app.app_context():
        assert [t for t in inbox(uid["admin"]) if t["doc_id"] == dlg and t["delegated"]]
        rid = q("SELECT id FROM approval_requests WHERE doc_type = 'po' AND doc_id = ?", (dlg,), one=True)[0]
    adm.post(f"/approvals/{rid}/approve", data={"comment": "for the manager"})
    adm.post(f"/approvals/{rid}/approve", data={"comment": "finance ok"})
    assert state(dlg) == "confirmed"
    # a rule saved from the setup screen is used
    r = adm.post("/setup/workflow/rule/new", data={"doc_type": "mr", "name": "Big MRs", "min_amount": "0",
                                                    "priority": "1", "reminder_days": "1", "active": "1",
                                                    "s1_name": "Two approvers", "s1_users": [uid["admin"], uid["manager"]],
                                                    "s1_mode": "all", "s1_required": "1"})
    assert r.status_code == 302
    r = acc.post("/procurement/mr/new", data={"branch_id": ruh, "date": date.today().isoformat(), "department": "Ops",
                                              "product_id": [prod], "description": ["x"], "quantity": ["1"],
                                              "action": "submit"})
    mr = int(r.headers["Location"].rstrip("/").split("/")[-1].split("?")[0])
    mgr.post(f"/procurement/mr/{mr}/approve", data={"comment": "ok"})
    with app.app_context():
        assert q("SELECT state FROM material_requests WHERE id = ?", (mr,), one=True)[0] == "submitted"
    adm.post(f"/procurement/mr/{mr}/approve", data={"comment": "ok"})
    with app.app_context():
        assert q("SELECT state FROM material_requests WHERE id = ?", (mr,), one=True)[0] == "approved"
    assert "Net 30" in adm.get(f"/orders/print/{big}").get_data(as_text=True)
    assert adm.get("/approvals").status_code == 200


def test_projects():
    from datetime import date
    from vertex.money import to_int
    from vertex.modules import make_license
    import json
    app = make_app()
    adm, acc, mgr = app.test_client(), app.test_client(), app.test_client()
    login(adm)
    login(acc, "accountant", "account123")
    login(mgr, "manager", "manager123")
    with app.app_context():
        from vertex import projects as pj
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        p1 = q("SELECT * FROM projects WHERE code = 'PRJ-001'", one=True)
        p2 = q("SELECT * FROM projects WHERE code = 'PRJ-002'", one=True)
        uid = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
        ret_acc = q("SELECT retention_account_id FROM company", one=True)[0]
        # the project has its own cost centre
        cc = q("SELECT * FROM cost_centres WHERE id = ?", (p1["cost_centre_id"],), one=True)
        assert cc["code"] == "PRJ-001"
        s = pj.summary(p1["id"])
        # billed = mobilisation + rough-in; 10% retention held on both
        assert s["billed"] == to_int(425000), s["billed"]
        assert s["retention_balance"] == to_int(42500) and s["retention_held"] == to_int(42500)
        # retention on the ledger equals the project's figure
        bal = q("SELECT SUM(debit - credit) FROM move_lines WHERE account_id = ?", (ret_acc,), one=True)[0]
        assert bal == to_int(42500), bal
        # progress invoice: Dr AR 312,375 + Dr Retention 29,750 = Cr Revenue 297,500 + Cr VAT 44,625
        inv = q("SELECT * FROM invoices WHERE project_id = ? AND project_billing = 'progress' ORDER BY id DESC LIMIT 1",
                (p1["id"],), one=True)
        assert inv["amount_total"] == to_int(312375), inv["amount_total"]
        lines = {(r["account_id"], r["debit"], r["credit"]) for r in q("SELECT * FROM move_lines WHERE move_id = ?",
                                                                       (inv["move_id"],))}
        assert (ret_acc, to_int(29750), 0) in lines
        rev = q("SELECT SUM(credit - debit) FROM move_lines WHERE move_id = ? AND cost_centre_id = ?",
                (inv["move_id"], p1["cost_centre_id"]), one=True)[0]
        assert rev == to_int(297500)
        # committed: 3 months' hire ordered, 1 billed; actual: subcontract + hire + stock issued to the project
        assert s["committed"] == to_int(25000), s["committed"]
        stock_cost = q("SELECT SUM(ml.debit - ml.credit) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                       "WHERE m.move_type = 'stock' AND ml.cost_centre_id = ?", (p1["cost_centre_id"],), one=True)[0]
        assert stock_cost > 0
        assert s["actual"] == to_int(143000 + 12500) + stock_cost, (s["actual"], stock_cost)
        rep = pj.budget_report(p1["id"])
        by = {r["code"]: r for r in rep["rows"] if not r.get("unbudgeted")}
        assert by["5300"]["actual"] == to_int(143000) and by["5400"]["committed"] == to_int(25000)
        assert by["5100"]["actual"] == stock_cost
        assert rep["total"]["remaining"] == rep["total"]["budget"] - s["committed"] - s["actual"]
        # the waiting project MR sits with the project manager
        mr2 = q("SELECT * FROM material_requests WHERE project_id = ? AND state = 'submitted'", (p1["id"],), one=True)
        req = q("SELECT * FROM approval_requests WHERE doc_type = 'mr' AND doc_id = ? AND state = 'pending'",
                (mr2["id"],), one=True)
        assert req["project_id"] == p1["id"] and req["current_step"] == 1
        assert [r[0] for r in q("SELECT user_id FROM approval_tasks WHERE request_id = ? AND state = 'pending'",
                               (req["id"],))] == [uid["manager"]]
        # a material request without a project skips the project-manager step
        jed_mr = q("SELECT id FROM material_requests WHERE project_id IS NULL ORDER BY id LIMIT 1", one=True)[0]
        r0 = q("SELECT id FROM approval_requests WHERE doc_type = 'mr' AND doc_id = ?", (jed_mr,), one=True)[0]
        assert q("SELECT 1 FROM approval_events WHERE request_id = ? AND action = 'skipped'", (r0,), one=True)
        ruh = p1["branch_id"]
        wh = q("SELECT id FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (ruh,), one=True)[0]
        eqh = q("SELECT id FROM products WHERE code = 'EQH-001'", one=True)[0]
        sub = q("SELECT id FROM products WHERE code = 'SUB-001'", one=True)[0]
        vat = q("SELECT id FROM taxes WHERE name_en = 'VAT 15% (Purchases)'", one=True)[0]
        vend = q("SELECT id FROM partners WHERE name_en = 'Kingdom Equipment Rental'", one=True)[0]
        jbill = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'purchase' ORDER BY id LIMIT 1", (ruh,), one=True)[0]
        jed = p2["branch_id"]
        whj = q("SELECT id FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (jed,), one=True)[0]
        mid3 = q("SELECT id FROM project_milestones WHERE project_id = ? AND name LIKE 'Second fix%'", (p1["id"],),
                 one=True)[0]

    # pages, both languages, and the exports
    for url in ("/projects/", "/projects/new", f"/projects/{p1['id']}", f"/projects/{p1['id']}/edit",
                f"/projects/{p2['id']}", "/procurement/mr/new", "/orders/purchase/new", "/invoices/in_invoice/new",
                f"/procurement/mr/{mr2['id']}", "/setup/workflow/rule/1"):
        for lang in ("en", "ar"):
            adm.get(f"/lang/{lang}")
            assert adm.get(url).status_code == 200, (url, lang)
    adm.get("/lang/en")
    html = adm.get(f"/projects/{p1['id']}").get_data(as_text=True)
    assert "Budget vs committed vs actual" in html and "Rough-in works" in html and "Retention held" in html
    assert "Project manager (of the document)" in adm.get("/setup/workflow/rule/1").get_data(as_text=True)
    for kind, ctype in (("pdf", "application/pdf"), ("xlsx", "spreadsheet"), ("csv", "text/csv")):
        r = adm.get(f"/projects/{p1['id']}?export={kind}")
        assert r.status_code == 200 and ctype in r.content_type, (kind, r.content_type)
        assert adm.get(f"/projects/?export={kind}").status_code == 200
    # the manager approves the project MR (step 1), then the department approver (step 2)
    assert mgr.post(f"/approvals/{req['id']}/approve", data={"comment": "OK for site"}).status_code == 302
    with app.app_context():
        assert q("SELECT current_step FROM approval_requests WHERE id = ?", (req["id"],), one=True)[0] == 2
    adm.post(f"/approvals/{req['id']}/approve", data={"comment": "Approved"})
    with app.app_context():
        assert q("SELECT state FROM material_requests WHERE id = ?", (mr2["id"],), one=True)[0] == "approved"

    def new_po(client, branch, warehouse, project, qty, price, product=None):
        r = client.post("/orders/purchase/new", data={"partner_id": vend, "branch_id": branch, "warehouse_id": warehouse,
                                                      "project_id": project, "date": date.today().isoformat(),
                                                      "product_id": [product or eqh], "description": ["Hire"],
                                                      "quantity": [str(qty)], "price_unit": [str(price)], "tax_id": [vat]})
        assert r.status_code == 302, r.get_data(as_text=True)[:400]
        return int(r.headers["Location"].rstrip("/").split("/")[-1])

    # the PO takes the project's cost centre; within budget it is confirmed and becomes committed
    po = new_po(acc, ruh, wh, p1["id"], 1, 5000)
    with app.app_context():
        assert q("SELECT cost_centre_id FROM orders WHERE id = ?", (po,), one=True)[0] == p1["cost_centre_id"]
    acc.post(f"/orders/view/{po}/confirm")
    with app.app_context():
        assert q("SELECT state FROM orders WHERE id = ?", (po,), one=True)[0] == "confirmed"
        assert pj.summary(p1["id"])["committed"] == to_int(30000)
    # warn: over budget on PRJ-001 is allowed with a warning
    big = new_po(acc, ruh, wh, p1["id"], 1, 40000)
    r = acc.post(f"/orders/view/{big}/confirm", follow_redirects=True)
    assert "over budget" in r.get_data(as_text=True)
    with app.app_context():
        assert q("SELECT state FROM orders WHERE id = ?", (big,), one=True)[0] == "confirmed"
    # block: PRJ-002 has 60,000 for subcontract; 70,000 is refused
    blocked = new_po(acc, jed, whj, p2["id"], 1, 70000, sub)
    r = acc.post(f"/orders/view/{blocked}/confirm", follow_redirects=True)
    assert "budget exceeded" in r.get_data(as_text=True)
    with app.app_context():
        assert q("SELECT state FROM orders WHERE id = ?", (blocked,), one=True)[0] == "draft"
    # a project from another branch is refused
    r = acc.post("/orders/purchase/new", data={"partner_id": vend, "branch_id": ruh, "warehouse_id": wh,
                                               "project_id": p2["id"], "date": date.today().isoformat(),
                                               "product_id": [eqh], "description": ["x"], "quantity": ["1"],
                                               "price_unit": ["10"], "tax_id": [vat]}, follow_redirects=True)
    assert "another branch" in r.get_data(as_text=True)
    # a bill with the project: income/expense lines without a cost centre go to the project
    exp_acc = q_acc = None
    with app.app_context():
        q_acc = q("SELECT id FROM accounts WHERE code = '6600'", one=True)[0]
    r = acc.post("/invoices/in_invoice/new", data={"partner_id": vend, "branch_id": ruh, "journal_id": jbill,
                                                   "date": date.today().isoformat(), "project_id": p1["id"],
                                                   "description": ["Site water"], "account_id": [q_acc],
                                                   "quantity": ["1"], "price_unit": ["800"], "tax_id": [""],
                                                   "product_id": [""], "order_line_id": [""], "cost_centre_id": [""],
                                                   "action": "post"})
    assert r.status_code == 302, r.get_data(as_text=True)[:400]
    with app.app_context():
        bid = int(r.headers["Location"].rstrip("/").split("/")[-1])
        assert q("SELECT cost_centre_id FROM invoice_lines WHERE invoice_id = ?", (bid,), one=True)[0] == p1["cost_centre_id"]
        rep = pj.budget_report(p1["id"])
        assert {r["code"]: r for r in rep["rows"]}["6600"]["actual"] == to_int(800)
    # milestone billing: once only; a deleted draft frees the milestone again
    r = acc.post(f"/projects/{p1['id']}/milestone/{mid3}/bill")
    assert r.status_code == 302 and "/invoices/" in r.headers["Location"]
    inv3 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    r = acc.post(f"/projects/{p1['id']}/milestone/{mid3}/bill", follow_redirects=True)
    assert "already invoiced" in r.get_data(as_text=True)
    assert acc.post(f"/invoices/view/{inv3}/delete").status_code == 302
    with app.app_context():
        assert not q("SELECT 1 FROM invoices WHERE id = ?", (inv3,), one=True)
        assert not pj.milestone_billed(q("SELECT * FROM project_milestones WHERE id = ?", (mid3,), one=True))
    # retention release: all of it, then nothing left
    r = acc.post(f"/projects/{p1['id']}/retention", data={"date": date.today().isoformat()})
    assert r.status_code == 302 and "/invoices/" in r.headers["Location"]
    rel = int(r.headers["Location"].rstrip("/").split("/")[-1])
    r = acc.post(f"/projects/{p1['id']}/retention", follow_redirects=True)
    assert "no retention to release" in r.get_data(as_text=True)
    with app.app_context():
        from vertex.ledger import post_invoice
        post_invoice(rel, uid["accountant"])
        dbcommit()
        s2 = pj.summary(p1["id"])
        assert s2["retention_balance"] == 0 and s2["retention_released"] == to_int(42500)
        assert q("SELECT SUM(debit - credit) FROM move_lines WHERE account_id = ?", (ret_acc,), one=True)[0] == 0
        assert q("SELECT amount_total FROM invoices WHERE id = ?", (rel,), one=True)[0] == to_int(42500)
    # new project from the form gets its own cost centre; a closed project takes no new documents
    r = adm.post("/projects/new", data={"code": "prj-009", "name_en": "Test Job", "branch_id": ruh, "state": "active",
                                        "budget_control": "warn", "contract_value": "1000", "retention_pct": "5",
                                        "budget_account": [q_acc], "budget_desc": ["Misc"], "budget_amount": ["500"],
                                        "ms_id": [""], "ms_name": ["All"], "ms_amount": ["1000"], "ms_due": [""],
                                        "member_id": [uid["accountant"]], "member_role": ["QS"]})
    assert r.status_code == 302, r.get_data(as_text=True)[:400]
    pid9 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        p9 = q("SELECT * FROM projects WHERE id = ?", (pid9,), one=True)
        assert p9["code"] == "PRJ-009" and q("SELECT code FROM cost_centres WHERE id = ?", (p9["cost_centre_id"],),
                                             one=True)[0] == "PRJ-009"
    r = adm.post("/projects/new", data={"code": "PRJ-010", "name_en": "Too much", "branch_id": ruh,
                                        "contract_value": "100", "ms_id": [""], "ms_name": ["A"], "ms_amount": ["150"],
                                        "ms_due": [""]}, follow_redirects=True)
    assert "more than the contract value" in r.get_data(as_text=True)
    adm.post(f"/projects/{pid9}/state", data={"state": "closed"})
    r = acc.post("/orders/purchase/new", data={"partner_id": vend, "branch_id": ruh, "warehouse_id": wh,
                                               "project_id": pid9, "date": date.today().isoformat(),
                                               "product_id": [eqh], "description": ["x"], "quantity": ["1"],
                                               "price_unit": ["10"], "tax_id": [vat]}, follow_redirects=True)
    assert "not active" in r.get_data(as_text=True)
    with app.app_context():
        from vertex.ledger import trial_balance
        ids = [b["id"] for b in q("SELECT id FROM branches")]
        assert not q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        tb = trial_balance(ids, "2000-01-01", "2999-12-31")
        assert tb is not None

    # not licensed: no Projects app, no project field, no project pages
    lic_path = os.path.join(tempfile.mkdtemp(), "license.json")
    with open(lic_path, "w") as f:
        json.dump(make_license("Acme", ["procurement", "inventory"]), f)
    app2 = create_app(database=app.config["DATABASE"], license_path=lic_path)
    c = app2.test_client()
    login(c)
    for url in ("/procurement/mr/new", "/orders/purchase/new", "/invoices/in_invoice/new", f"/orders/view/{po}",
                f"/procurement/mr/{jed_mr}", "/setup/workflow/rule/1", "/setup/workflow", "/procurement/mr"):
        html = c.get(url).get_data(as_text=True)
        assert 'name="project_id"' not in html and "Project manager" not in html and "/projects/" not in html, url
    assert c.get("/projects/").status_code == 302
    assert "/projects/" not in c.get("/").get_data(as_text=True).split("Available to add")[0]


def test_procurement_upgrade():
    from datetime import date, datetime, timedelta
    from vertex.money import to_int
    app = make_app()
    adm, acc, mgr = app.test_client(), app.test_client(), app.test_client()
    login(adm)
    login(acc, "accountant", "account123")
    login(mgr, "manager", "manager123")
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        uid = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
        ruh = q("SELECT id FROM branches WHERE code = 'RUH'", one=True)[0]
        wh = q("SELECT id FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (ruh,), one=True)[0]
        sealed_open = q("SELECT * FROM rfqs WHERE sealed = 1 AND opened_at IS NULL", one=True)
        pending = q("SELECT * FROM rfqs WHERE state = 'award_pending'", one=True)
        vend = {r["name_en"]: r["id"] for r in q("SELECT id, name_en FROM partners WHERE is_vendor = 1")}
        vat = q("SELECT id FROM taxes WHERE name_en = 'VAT 15% (Purchases)'", one=True)[0]
        hire_po = q("SELECT id FROM orders WHERE partner_id = ? ORDER BY id LIMIT 1", (vend["Kingdom Equipment Rental"],),
                    one=True)[0]
        grn1 = q("SELECT id FROM grns ORDER BY id LIMIT 1", one=True)[0]
    # pages in both languages; documents as PDF
    for url in ("/procurement/rfq/new", f"/procurement/rfq/{sealed_open['id']}", f"/procurement/rfq/{pending['id']}",
                f"/procurement/rfq/{sealed_open['id']}/quote/{sealed_open['id']}" if False else "/procurement/rfq",
                f"/orders/view/{hire_po}", f"/orders/grn/{grn1}", "/setup/company", "/procurement/mr/1"):
        for lang in ("en", "ar"):
            adm.get(f"/lang/{lang}")
            assert adm.get(url).status_code == 200, (url, lang)
    adm.get("/lang/en")
    for url in (f"/orders/view/{hire_po}?export=pdf", f"/orders/grn/{grn1}?export=pdf",
                f"/procurement/rfq/{pending['id']}/print/{q_first_rv(app, pending['id'])}"):
        r = adm.get(url)
        assert r.status_code == 200 and r.data[:4] == b"%PDF", url
    html = adm.get(f"/orders/view/{hire_po}").get_data(as_text=True)
    assert "Requisition PO" not in html and "Direct PO" in html and "Accepted by vendor" in html and "GRN/" in html
    # sealed and open: no prices anywhere, cannot open before closing
    html = adm.get(f"/procurement/rfq/{sealed_open['id']}").get_data(as_text=True)
    assert "vendors have quoted" in html and "820.00" not in html and "790.00" not in html
    rv0 = q_first_rv(app, sealed_open["id"])
    assert "820" not in adm.get(f"/procurement/rfq/{sealed_open['id']}/quote/{rv0}").get_data(as_text=True)
    r = adm.post(f"/procurement/rfq/{sealed_open['id']}/open", follow_redirects=True)
    assert "only after the closing time" in r.get_data(as_text=True)
    r = adm.post(f"/procurement/rfq/{sealed_open['id']}/recommend", data={"rfq_vendor_id": rv0}, follow_redirects=True)
    assert "Open the sealed bids first" in r.get_data(as_text=True)
    # award waiting for approval: the requester cannot approve; the Procurement Manager can; the PO is confirmed
    with app.app_context():
        req = q("SELECT * FROM approval_requests WHERE doc_type = 'rfq_award' AND doc_id = ? AND state = 'pending'",
                (pending["id"],), one=True)
        assert req and req["requested_by"] == uid["accountant"]
    r = acc.post(f"/approvals/{req['id']}/approve", data={"comment": "mine"}, follow_redirects=True)
    with app.app_context():
        assert q("SELECT state FROM rfqs WHERE id = ?", (pending["id"],), one=True)[0] == "award_pending"
    r = mgr.post(f"/approvals/{req['id']}/approve", data={"comment": "Lowest compliant offer"})
    with app.app_context():
        rfq2 = q("SELECT * FROM rfqs WHERE id = ?", (pending["id"],), one=True)
        po = q("SELECT * FROM orders WHERE rfq_id = ?", (pending["id"],), one=True)
        assert rfq2["state"] == "awarded" and po["state"] == "confirmed" and po["mr_id"] == rfq2["mr_id"]
        assert po["project_id"] == rfq2["project_id"] and po["terms"] and po["amount_untaxed"] == to_int(79 * 300)
        assert po["partner_id"] == vend["Arabian Tools Trading"]

    # full flow over the screens: MR -> RFQ (no PR) -> quotes -> award -> PO -> send -> acknowledge -> GRN -> bill
    r = acc.post("/procurement/mr/new", data={"branch_id": ruh, "warehouse_id": wh, "department": "Operations",
                                              "date": date.today().isoformat(), "product_id": ["", ""],
                                              "description": ["Site survey service", "Safety signage set"],
                                              "quantity": ["1", "10"], "action": "submit"})
    mr = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        mreq = q("SELECT id FROM approval_requests WHERE doc_type = 'mr' AND doc_id = ? AND state = 'pending'",
                 (mr,), one=True)[0]
    mgr.post(f"/approvals/{mreq}/approve", data={"comment": "ok"})
    r = acc.post(f"/procurement/mr/{mr}/to_rfq")
    assert "/rfq/" in r.headers["Location"] and r.headers["Location"].endswith("/edit"), r.headers["Location"]
    rfq = int(r.headers["Location"].split("/rfq/")[1].split("/")[0])
    with app.app_context():
        assert q("SELECT state FROM material_requests WHERE id = ?", (mr,), one=True)[0] == "done"
        rl = [dict(x) for x in q("SELECT * FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (rfq,))]
        assert len(rl) == 2 and q("SELECT mr_id FROM rfqs WHERE id = ?", (rfq,), one=True)[0] == mr
    closing = (date.today() + timedelta(days=2)).isoformat()
    form = {"closing_date": closing, "closing_time": "12:00", "validity_days": "45", "payment_terms": "30 days",
            "incoterms": "DAP", "delivery_place": "Riyadh", "notes": "", "terms": "Standard",
            "vendor_ids": [vend["Gulf Safety Equipment Co."], vend["Arabian Tools Trading"]], "action": "send"}
    for k, vals in (("product_id", ["", ""]), ("item_code", ["SRV-SURV", "SGN-01"]),
                    ("description", ["Site survey service", "Safety signage set"]),
                    ("specification", ["Full report", "ISO 7010, aluminium"]), ("manufacturer", ["", "Brady"]),
                    ("part_number", ["", "BR-7010"]), ("uom", ["Job", "Set"]), ("quantity", ["1", "10"]),
                    ("required_date", [closing, closing]), ("allow_alternative", ["0", "1"]),
                    ("pr_line_id", ["", ""]), ("mr_line_id", [str(rl[0]["mr_line_id"]), str(rl[1]["mr_line_id"])])):
        form[k] = vals
    r = acc.post(f"/procurement/rfq/{rfq}/edit", data=form)
    assert r.status_code == 302, r.get_data(as_text=True)[:300]
    with app.app_context():
        rr = q("SELECT * FROM rfqs WHERE id = ?", (rfq,), one=True)
        assert rr["state"] == "sent" and rr["closing_at"] == f"{closing}T12:00" and rr["incoterms"] == "DAP"
        lines = q("SELECT * FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (rfq,))
        assert lines[1]["manufacturer"] == "Brady" and lines[1]["allow_alternative"] == 1 and lines[0]["uom"] == "Job"
        rvs = q("SELECT * FROM rfq_vendors WHERE rfq_id = ? ORDER BY id", (rfq,))
    # alternatives only where allowed
    r = acc.post(f"/procurement/rfq/{rfq}/quote/{rvs[0]['id']}", data={
        f"price_{lines[0]['id']}": "5000", f"tax_{lines[0]['id']}": vat, f"alt_{lines[0]['id']}": "1",
        f"price_{lines[1]['id']}": "300", f"tax_{lines[1]['id']}": vat, "action": "save"}, follow_redirects=True)
    assert "does not accept alternatives" in r.get_data(as_text=True)
    for rv, (p1, p2) in zip(rvs, (("5000", "300"), ("5200", "250"))):
        r = acc.post(f"/procurement/rfq/{rfq}/quote/{rv['id']}", data={
            f"price_{lines[0]['id']}": p1, f"tax_{lines[0]['id']}": vat, f"price_{lines[1]['id']}": p2,
            f"tax_{lines[1]['id']}": vat, f"offered_{lines[1]['id']}": "Brady equivalent", f"alt_{lines[1]['id']}": "1",
            "lead_days": "5", "valid_until": closing, "action": "save"})
        assert r.status_code == 302
    # past the closing time no more quotations
    with app.app_context():
        dbx("UPDATE rfqs SET closing_at = ? WHERE id = ?", ((datetime.now() - timedelta(hours=1)).strftime("%Y-%m-%dT%H:%M"), rfq))
        dbcommit()
    r = acc.post(f"/procurement/rfq/{rfq}/quote/{rvs[0]['id']}", data={f"price_{lines[0]['id']}": "1", "action": "save"},
                 follow_redirects=True)
    assert "no longer accepted" in r.get_data(as_text=True)
    # Gulf 8,000 vs Arabian 7,700: choosing Gulf needs a reason
    r = acc.post(f"/procurement/rfq/{rfq}/recommend", data={"rfq_vendor_id": rvs[0]["id"], "warehouse_id": wh},
                 follow_redirects=True)
    assert "not the lowest quotation" in r.get_data(as_text=True)
    r = acc.post(f"/procurement/rfq/{rfq}/recommend", data={"rfq_vendor_id": rvs[0]["id"], "warehouse_id": wh,
                                                            "reason": "Faster delivery, survey team on site"})
    with app.app_context():
        areq = q("SELECT id FROM approval_requests WHERE doc_type = 'rfq_award' AND doc_id = ? AND state = 'pending'",
                 (rfq,), one=True)[0]
    r = mgr.post(f"/approvals/{areq}/approve", data={"comment": "agreed"})
    with app.app_context():
        po = q("SELECT * FROM orders WHERE rfq_id = ?", (rfq,), one=True)
        assert po["state"] == "confirmed" and po["partner_id"] == vend["Gulf Safety Equipment Co."]
        assert "Faster delivery" in po["notes"]
        pol = q("SELECT * FROM order_lines WHERE order_id = ? ORDER BY sequence", (po["id"],))
    html = acc.get(f"/orders/view/{po['id']}").get_data(as_text=True)
    assert "Requisition PO" in html and "Receive (GRN)" in html and "Send to vendor" in html
    r = acc.post(f"/orders/view/{po['id']}/send", follow_redirects=True)
    assert "Set up email" in r.get_data(as_text=True)
    acc.post(f"/orders/view/{po['id']}/mark_sent")
    r = acc.post(f"/orders/view/{po['id']}/ack", data={"ack_state": "rejected"}, follow_redirects=True)
    assert "reason" in r.get_data(as_text=True)
    acc.post(f"/orders/view/{po['id']}/ack", data={"ack_state": "accepted", "ack_ref": "GSE-SO-1", "ack_date": closing})
    with app.app_context():
        o = q("SELECT * FROM orders WHERE id = ?", (po["id"],), one=True)
        assert o["sent_at"] and o["ack_state"] == "accepted" and o["expected_date"] == closing
    # GRN: only the requester (accountant) for this MR; not more than ordered
    r = mgr.get(f"/orders/view/{po['id']}/grn", follow_redirects=True)
    assert "Only the requester" in r.get_data(as_text=True)
    r = acc.post(f"/orders/view/{po['id']}/grn", data={"date": date.today().isoformat(), f"qty_{pol[1]['id']}": "11"},
                 follow_redirects=True)
    assert "more than the quantity still to receive" in r.get_data(as_text=True)
    r = acc.post(f"/orders/view/{po['id']}/grn", data={"date": date.today().isoformat(), f"qty_{pol[0]['id']}": "1",
                                                     f"qty_{pol[1]['id']}": "6", "delivery_note": "DN-55"})
    assert r.status_code == 302 and "/orders/grn/" in r.headers["Location"]
    gid = int(r.headers["Location"].rstrip("/").split("/")[-1])
    # bill for everything ordered: 10 signs billed, 6 received -> three-way match warning (policy warn)
    r = acc.post(f"/orders/view/{po['id']}/invoice")
    bill = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        from vertex.procurement import match_issues
        issues = match_issues(bill)
        assert len(issues) == 1 and "received 6" in issues[0], issues
        dbx("UPDATE company SET match_policy = 'block'")
        dbcommit()
    r = acc.post(f"/invoices/view/{bill}/post", follow_redirects=True)
    assert "Three-way match failed" in r.get_data(as_text=True)
    with app.app_context():
        assert q("SELECT state FROM invoices WHERE id = ?", (bill,), one=True)[0] == "draft"
        # bill only what was received, at a price 5% above the PO: still stopped by the 2% price tolerance
        dbx("UPDATE invoice_lines SET quantity = 6, price_unit = ? WHERE invoice_id = ? AND order_line_id = ?",
            (to_int(315), bill, pol[1]["id"]))
        dbcommit()
        assert any("price above the PO" in i for i in match_issues(bill))
        dbx("UPDATE invoice_lines SET price_unit = ? WHERE invoice_id = ? AND order_line_id = ?",
            (to_int(250) if pol[1]["price_unit"] == to_int(250) else pol[1]["price_unit"], bill, pol[1]["id"]))
        il = q("SELECT * FROM invoice_lines WHERE invoice_id = ?", (bill,))
        from vertex.ledger import compute_invoice_line
        for ln in il:
            sub, tx = compute_invoice_line(ln["quantity"], ln["price_unit"], 15, 2)
            dbx("UPDATE invoice_lines SET subtotal = ?, tax_amount = ? WHERE id = ?", (sub, tx, ln["id"]))
        tot = q("SELECT SUM(subtotal), SUM(tax_amount) FROM invoice_lines WHERE invoice_id = ?", (bill,), one=True)
        dbx("UPDATE invoices SET amount_untaxed = ?, amount_tax = ?, amount_total = ? WHERE id = ?",
            (tot[0], tot[1], tot[0] + tot[1], bill))
        dbcommit()
        assert match_issues(bill) == []
    r = acc.post(f"/invoices/view/{bill}/post")
    with app.app_context():
        assert q("SELECT state FROM invoices WHERE id = ?", (bill,), one=True)[0] == "posted"
    # a billed GRN cannot be cancelled
    r = acc.post(f"/orders/grn/{gid}", follow_redirects=True)
    assert "already billed" in r.get_data(as_text=True)
    # MR straight to a PO (no PR, no RFQ)
    r = acc.post("/procurement/mr/new", data={"branch_id": ruh, "warehouse_id": wh, "date": date.today().isoformat(),
                                              "product_id": [""], "description": ["Courier"], "quantity": ["1"],
                                              "action": "submit"})
    mr3 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        mreq = q("SELECT id FROM approval_requests WHERE doc_type = 'mr' AND doc_id = ? AND state = 'pending'",
                 (mr3,), one=True)[0]
    mgr.post(f"/approvals/{mreq}/approve", data={"comment": "ok"})
    r = acc.post(f"/procurement/mr/{mr3}/to_po", data={"vendor_id": vend["National Logistics Co."]})
    po3 = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        assert q("SELECT mr_id FROM orders WHERE id = ?", (po3,), one=True)[0] == mr3
        assert not q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")


def q_first_rv(app, rfq_id):
    with app.app_context():
        return q("SELECT id FROM rfq_vendors WHERE rfq_id = ? ORDER BY id LIMIT 1", (rfq_id,), one=True)[0]


def test_supplier_onboarding():
    import io
    import json
    from vertex.modules import make_license
    app = make_app()
    app.config["MAIL_OUTBOX"] = outbox = []
    pub, adm, mgr, other = app.test_client(), app.test_client(), app.test_client(), app.test_client()
    login(adm)
    login(mgr, "manager", "manager123")

    def csrf(client, url):
        html = client.get(url).get_data(as_text=True)
        return re.search(r'name="csrf" value="([^"]+)"', html).group(1)

    def body(msg):
        return msg.get_body(("plain",)).get_content()

    def pdf(name):
        return (io.BytesIO(b"%PDF-1.4 test"), name)

    base = {"company_en": "Al Fajr Trading Est.", "company_ar": "مؤسسة الفجر التجارية", "cr_no": "1010123456",
            "vat_no": "310123456700003", "category": "Industrial supplies", "contact_person": "Majed Saleh",
            "email": "Majed@AlFajr.example", "phone": "+966 50 111 2222", "bank_name": "Al Rajhi Bank",
            "iban": "SA03 8000 0000 6080 1016 7519", "account_name": "Al Fajr Trading Est.", "declaration": "1"}

    def form(client, extra=None, files=True):
        data = dict(base, csrf=csrf(client, "/supplier/register"), **(extra or {}))
        if files:
            data.update({"doc_type_1": "Commercial Registration", "file_1": pdf("cr.pdf"), "number_1": "1010123456",
                         "doc_type_2": "VAT Certificate", "file_2": pdf("vat.pdf"),
                         "doc_type_3": "Bank Letter", "file_3": pdf("bank.pdf")})
        return client.post("/supplier/register", data=data, content_type="multipart/form-data", follow_redirects=True)

    def nregs():
        with app.app_context():
            return q("SELECT COUNT(*) FROM vendor_registrations", one=True)[0]

    # public pages need no sign-in, in both languages
    for url in ("/supplier/register", "/portal/login"):
        for lang in ("en", "ar"):
            pub.get(f"/lang/{lang}")
            assert pub.get(url).status_code == 200, (url, lang)
    pub.get("/lang/en")
    start = nregs()
    # no CSRF token, robots, invalid data, missing documents: nothing is saved
    r = pub.post("/supplier/register", data=base, follow_redirects=True)
    assert "form expired" in r.get_data(as_text=True)
    form(pub, {"website2": "http://spam"})
    assert "IBAN is not valid" in form(pub, {"iban": "SA00 1234"}).get_data(as_text=True)
    assert "15 digits" in form(pub, {"vat_no": "12345"}).get_data(as_text=True)
    assert "Attach these documents" in form(pub, files=False).get_data(as_text=True)
    assert nregs() == start
    # a valid registration: waits for review, the supplier gets an acknowledgement
    r = form(pub)
    html = r.get_data(as_text=True)
    assert "your registration was received" in html and "REG/" in html
    with app.app_context():
        reg = q("SELECT * FROM vendor_registrations ORDER BY id DESC LIMIT 1", one=True)
        assert reg["state"] == "submitted" and reg["email"] == "majed@alfajr.example" and reg["iban"].startswith("SA038")
        assert q("SELECT COUNT(*) FROM vendor_registration_docs WHERE registration_id = ?", (reg["id"],), one=True)[0] == 3
        req = q("SELECT * FROM approval_requests WHERE doc_type = 'vendor_registration' AND doc_id = ? AND state = 'pending'",
                (reg["id"],), one=True)
        assert req and req["requested_by"] is None
    assert any("majed@alfajr.example" in m["To"] and reg["number"] in body(m) for m in outbox)
    # the same VAT number cannot register twice while it is reviewed
    assert "already being reviewed" in form(pub).get_data(as_text=True)
    # staff review: list, detail, document; ask for more information
    for url in ("/vendors/registrations", f"/vendors/registrations/{reg['id']}", "/vendors/registrations?state=all"):
        for lang in ("en", "ar"):
            adm.get(f"/lang/{lang}")
            assert adm.get(url).status_code == 200, (url, lang)
    adm.get("/lang/en")
    assert "Al Fajr Trading" in adm.get("/vendors/registrations").get_data(as_text=True)
    with app.app_context():
        doc = q("SELECT id FROM vendor_registration_docs WHERE registration_id = ? LIMIT 1", (reg["id"],), one=True)[0]
    assert adm.get(f"/vendors/registrations/doc/{doc}").status_code == 200
    n0 = len(outbox)
    adm.post(f"/vendors/registrations/{reg['id']}", data={"action": "info", "message": "Upload a stamped bank letter."})
    mail = [m for m in outbox[n0:] if "majed@alfajr.example" in m["To"]]
    assert mail and "stamped bank letter" in body(mail[0])
    link = re.search(r"(http\S+/supplier/register/\S+)", body(mail[0])).group(1)
    path = link.split("localhost", 1)[-1]
    with app.app_context():
        assert q("SELECT state FROM vendor_registrations WHERE id = ?", (reg["id"],), one=True)[0] == "info_requested"
        assert q("SELECT state FROM approval_requests WHERE id = ?", (req["id"],), one=True)[0] == "cancelled"
    html = pub.get(path).get_data(as_text=True)
    assert "stamped bank letter" in html and "Al Fajr Trading" in html
    data = dict(base, csrf=csrf(pub, path), doc_type_1="Bank Letter", file_1=pdf("bank-stamped.pdf"))
    r = pub.post(path, data=data, content_type="multipart/form-data", follow_redirects=True)
    assert "your registration was received" in r.get_data(as_text=True)
    assert "no longer valid" in pub.get(path).get_data(as_text=True)  # the correction link works once
    with app.app_context():
        assert q("SELECT state FROM vendor_registrations WHERE id = ?", (reg["id"],), one=True)[0] == "submitted"
        req2 = q("SELECT * FROM approval_requests WHERE doc_type = 'vendor_registration' AND doc_id = ? AND state = 'pending'",
                 (reg["id"],), one=True)
    # two-step approval: procurement then finance; then the vendor and its portal user exist
    n0 = len(outbox)
    mgr.post(f"/approvals/{req2['id']}/approve", data={"comment": "ok"})
    with app.app_context():
        assert q("SELECT partner_id FROM vendor_registrations WHERE id = ?", (reg["id"],), one=True)[0] is None
    adm.post(f"/approvals/{req2['id']}/approve", data={"comment": "IBAN checked"})
    with app.app_context():
        reg = q("SELECT * FROM vendor_registrations WHERE id = ?", (reg["id"],), one=True)
        p = q("SELECT * FROM partners WHERE id = ?", (reg["partner_id"],), one=True)
        assert reg["state"] == "approved" and p["vendor_status"] == "approved" and p["iban"] == reg["iban"]
        assert p["vat_no"] == "310123456700003" and p["account_name"] == "Al Fajr Trading Est."
        assert q("SELECT COUNT(*) FROM vendor_documents WHERE partner_id = ?", (p["id"],), one=True)[0] == 4
        pu = q("SELECT * FROM portal_users WHERE partner_id = ?", (p["id"],), one=True)
        assert pu["email"] == "majed@alfajr.example" and not pu["password_hash"] and pu["activation_hash"]
    mail = [m for m in outbox[n0:] if "majed@alfajr.example" in m["To"] and "/portal/activate/" in body(m)]
    assert len(mail) == 1 and "never send passwords" in body(mail[0])
    act = re.search(r"(http\S+/portal/activate/\S+)", body(mail[0])).group(1).split("localhost", 1)[-1]
    # activation: strong password only, works once, then the supplier is signed in to the portal
    tok = csrf(pub, act)
    r = pub.post(act, data={"csrf": tok, "password": "short1", "confirm": "short1"}, follow_redirects=True)
    assert "at least 10 characters" in r.get_data(as_text=True)
    r = pub.post(act, data={"csrf": tok, "password": "Fajr2026secure", "confirm": "Fajr2026secure"}, follow_redirects=True)
    html = r.get_data(as_text=True)
    assert "Your account is active" in html and "Al Fajr Trading" in html
    assert "•••• 7519" in pub.get("/portal/company").get_data(as_text=True)
    assert "no longer valid" in pub.get(act).get_data(as_text=True)
    # a portal session never opens staff pages
    r = pub.get("/invoices/out_invoice")
    assert r.status_code == 302 and "/login" in r.headers["Location"]
    pub.get("/portal/logout")
    assert pub.get("/portal/").status_code == 302
    # sign in; wrong passwords lock the account
    tok = csrf(other, "/portal/login")
    r = other.post("/portal/login", data={"csrf": tok, "email": "MAJED@alfajr.example", "password": "Fajr2026secure"})
    assert r.status_code == 302 and r.headers["Location"].endswith("/portal/")
    other.get("/portal/logout")
    tok = csrf(other, "/portal/login")
    for _ in range(5):
        r = other.post("/portal/login", data={"csrf": tok, "email": "majed@alfajr.example", "password": "wrong-pass1"},
                       follow_redirects=True)
    r = other.post("/portal/login", data={"csrf": tok, "email": "majed@alfajr.example", "password": "Fajr2026secure"},
                   follow_redirects=True)
    assert "Too many attempts" in r.get_data(as_text=True)
    # staff: vendor page shows the portal user; access can be removed
    html = adm.get(f"/vendors/{p['id']}").get_data(as_text=True)
    assert "Supplier portal access" in html and "majed@alfajr.example" in html
    adm.post(f"/vendors/{p['id']}/portal-user", data={"uid": pu["id"], "action": "deactivate"})
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE portal_users SET locked_until = NULL WHERE id = ?", (pu["id"],))
        dbcommit()
    r = other.post("/portal/login", data={"csrf": tok, "email": "majed@alfajr.example", "password": "Fajr2026secure"},
                   follow_redirects=True)
    assert "Invalid email or password" in r.get_data(as_text=True)
    # an expired activation link is refused
    n0 = len(outbox)
    adm.post(f"/vendors/{p['id']}/portal-user", data={"email": "accounts@alfajr.example", "full_name": "Accounts"})
    link = re.search(r"(http\S+/portal/activate/\S+)", body(outbox[-1])).group(1).split("localhost", 1)[-1]
    with app.app_context():
        dbx("UPDATE portal_users SET activation_expires = '2000-01-01T00:00:00' WHERE email = 'accounts@alfajr.example'")
        dbcommit()
    assert "no longer valid" in pub.get(link).get_data(as_text=True)
    # reject the demo registration still waiting
    with app.app_context():
        waiting = q("SELECT id FROM vendor_registrations WHERE state = 'submitted'", one=True)[0]
    mgr.post(f"/vendors/registrations/{waiting}", data={"action": "reject", "reason": "Not needed now"})
    with app.app_context():
        assert q("SELECT state, reject_reason FROM vendor_registrations WHERE id = ?", (waiting,), one=True)[:] == \
            ("rejected", "Not needed now")
        assert not q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
    # without the Supplier Portal licence the public pages do not exist
    lic_path = os.path.join(tempfile.mkdtemp(), "license.json")
    with open(lic_path, "w") as f:
        json.dump(make_license("Acme", ["vendors", "procurement"]), f)
    app2 = create_app(database=app.config["DATABASE"], license_path=lic_path)
    c = app2.test_client()
    assert c.get("/supplier/register").status_code == 404 and c.get("/portal/login").status_code == 404
    login(c)
    html = c.get("/vendors/").get_data(as_text=True)
    assert "Supplier registrations" not in html and c.get("/vendors/registrations").status_code == 404
    assert "Supplier portal access" not in c.get(f"/vendors/{p['id']}").get_data(as_text=True)


def test_vendor_portal():
    import io
    from datetime import date
    from vertex.money import to_int
    app = make_app()
    app.config["MAIL_OUTBOX"] = outbox = []
    ven, adm, acc = app.test_client(), app.test_client(), app.test_client()
    login(adm)
    login(acc, "accountant", "account123")

    def csrf(client, url):
        return re.search(r'name="csrf" value="([^"]+)"', client.get(url).get_data(as_text=True)).group(1)

    tok = csrf(ven, "/portal/login")
    r = ven.post("/portal/login", data={"csrf": tok, "email": "sales@gulfsafety.example", "password": "Supplier2026"})
    assert r.status_code == 302
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        gulf = q("SELECT * FROM partners WHERE name_en = 'Gulf Safety Equipment Co.'", one=True)
        sealed = q("SELECT * FROM rfqs WHERE sealed = 1 AND opened_at IS NULL", one=True)
        rv = q("SELECT * FROM rfq_vendors WHERE rfq_id = ? AND partner_id = ?", (sealed["id"], gulf["id"]), one=True)
        lines = q("SELECT * FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (sealed["id"],))
        closed_rfq = q("SELECT r.* FROM rfqs r JOIN rfq_vendors rv ON rv.rfq_id = r.id WHERE r.state = 'award_pending' "
                       "AND rv.partner_id = ?", (gulf["id"],), one=True)
        not_mine = q("SELECT id FROM rfqs WHERE id NOT IN (SELECT rfq_id FROM rfq_vendors WHERE partner_id = ?)",
                     (gulf["id"],), one=True)
        other_po = q("SELECT id FROM orders WHERE kind = 'purchase' AND partner_id != ? LIMIT 1", (gulf["id"],), one=True)[0]
        gulf_po = q("SELECT * FROM orders WHERE partner_id = ? AND kind = 'purchase' AND state = 'confirmed' ORDER BY id",
                    (gulf["id"],), one=True)
        vi_seed = q("SELECT * FROM vendor_invoices WHERE partner_id = ?", (gulf["id"],), one=True)
        bank = q("SELECT * FROM bank_changes WHERE partner_id = ? AND state = 'pending'", (gulf["id"],), one=True)
        old_iban = gulf["iban"]
        uid = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
    # every portal page, both languages
    for url in ("/portal/", "/portal/rfqs", f"/portal/rfqs/{sealed['id']}", "/portal/orders", f"/portal/orders/{gulf_po['id']}",
                "/portal/invoices", "/portal/payments", "/portal/documents", "/portal/company"):
        for lang in ("en", "ar"):
            ven.get(f"/lang/{lang}")
            assert ven.get(url).status_code == 200, (url, lang)
    ven.get("/lang/en")
    assert ven.get(f"/portal/rfqs/{sealed['id']}/pdf").data[:4] == b"%PDF"
    assert ven.get(f"/portal/orders/{gulf_po['id']}?pdf=1").data[:4] == b"%PDF"
    # only its own records
    if not_mine:
        assert ven.get(f"/portal/rfqs/{not_mine[0]}").status_code == 404
    assert ven.get(f"/portal/orders/{other_po}").status_code == 404
    # sealed RFQ: the vendor sees its own prices, never the other vendor's; staff see nothing
    html = ven.get(f"/portal/rfqs/{sealed['id']}").get_data(as_text=True)
    assert 'value="820.00"' in html and "790" not in html
    tok = csrf(ven, f"/portal/rfqs/{sealed['id']}")
    data = {"csrf": tok, "action": "save", "lead_days": "7", "valid_until": "2099-01-01", "quote_ref": "GSE-Q-2"}
    for ln, price in zip(lines, ("805", "93")):
        data[f"price_{ln['id']}"] = price
        data[f"tax_{ln['id']}"] = ""
    r = ven.post(f"/portal/rfqs/{sealed['id']}", data=data, follow_redirects=True)
    assert "quotation was submitted" in r.get_data(as_text=True)
    with app.app_context():
        rv2 = q("SELECT * FROM rfq_vendors WHERE id = ?", (rv["id"],), one=True)
        assert rv2["submitted_via"] == "portal" and rv2["quote_ref"] == "GSE-Q-2"
        assert q("SELECT price FROM rfq_quotes WHERE rfq_vendor_id = ? AND rfq_line_id = ?", (rv["id"], lines[0]["id"]),
                 one=True)[0] == to_int(805)
    staff = adm.get(f"/procurement/rfq/{sealed['id']}").get_data(as_text=True)
    assert "805" not in staff and "Sealed" in staff
    # closed RFQ: read only, a late quotation is refused
    if closed_rfq:
        html = ven.get(f"/portal/rfqs/{closed_rfq['id']}").get_data(as_text=True)
        assert "closed" in html and "Submit quotation" not in html
        r = ven.post(f"/portal/rfqs/{closed_rfq['id']}", data={"csrf": tok, "action": "save"}, follow_redirects=True)
        assert "no longer accepted" in r.get_data(as_text=True) or "Enter at least one price" in r.get_data(as_text=True)
    # purchase order: rejecting needs a reason; accepting is recorded on the PO
    r = ven.post(f"/portal/orders/{gulf_po['id']}", data={"csrf": tok, "ack_state": "rejected"}, follow_redirects=True)
    assert "reason" in r.get_data(as_text=True)
    ven.post(f"/portal/orders/{gulf_po['id']}", data={"csrf": tok, "ack_state": "accepted", "ack_ref": "GSE-SO-55",
                                                    "ack_date": "2099-02-01"})
    with app.app_context():
        o = q("SELECT * FROM orders WHERE id = ?", (gulf_po["id"],), one=True)
        assert o["ack_state"] == "accepted" and o["ack_ref"] == "GSE-SO-55"
    # invoices: a PDF is required, numbers cannot repeat
    inv_data = {"csrf": tok, "invoice_no": "GSE-INV-9001", "invoice_date": date.today().isoformat(),
                "amount_untaxed": "1000", "amount_tax": "150"}
    r = ven.post("/portal/invoices", data=dict(inv_data, file=(io.BytesIO(b"not a pdf"), "x.pdf")),
                 content_type="multipart/form-data", follow_redirects=True)
    assert "not a valid PDF" in r.get_data(as_text=True)
    r = ven.post("/portal/invoices", data=dict(inv_data, file=(io.BytesIO(b"%PDF-1.4 x"), "inv.pdf")),
                 content_type="multipart/form-data", follow_redirects=True)
    assert "Invoice sent" in r.get_data(as_text=True)
    r = ven.post("/portal/invoices", data=dict(inv_data, file=(io.BytesIO(b"%PDF-1.4 x"), "inv.pdf")),
                 content_type="multipart/form-data", follow_redirects=True)
    assert "already sent" in r.get_data(as_text=True)
    with app.app_context():
        vi_new = q("SELECT * FROM vendor_invoices WHERE invoice_no = 'GSE-INV-9001'", one=True)
        assert vi_new["amount_total"] == to_int(1150)
        fm_notes = q("SELECT COUNT(*) FROM notifications WHERE title LIKE '%GSE-INV-9001%'", one=True)[0]
        assert fm_notes >= 1
    # AP inbox: the invoice becomes a draft bill with the vendor's file; the other one is returned
    for url in ("/vendors/ap-inbox", f"/vendors/ap-inbox/{vi_seed['id']}", "/vendors/bank-changes",
                f"/vendors/bank-changes/{bank['id']}", f"/vendors/{gulf['id']}"):
        for lang in ("en", "ar"):
            adm.get(f"/lang/{lang}")
            assert adm.get(url).status_code == 200, (url, lang)
    adm.get("/lang/en")
    assert adm.get(f"/vendors/ap-inbox/{vi_seed['id']}/file").status_code == 200
    with app.app_context():
        ruh = q("SELECT id FROM branches WHERE code = 'RUH'", one=True)[0]
    r = acc.post(f"/vendors/ap-inbox/{vi_seed['id']}", data={"action": "accept", "branch_id": ruh})
    assert r.status_code == 302 and "/invoices/" in r.headers["Location"], r.headers.get("Location")
    bill = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        b = q("SELECT * FROM invoices WHERE id = ?", (bill,), one=True)
        assert b["ref"] == "GSE-INV-7781" and b["partner_id"] == gulf["id"] and b["kind"] == "in_invoice"
        assert q("SELECT COUNT(*) FROM attachments WHERE model = 'invoice' AND record_id = ?", (bill,), one=True)[0] == 1
        assert q("SELECT state, invoice_id FROM vendor_invoices WHERE id = ?", (vi_seed["id"],), one=True)[:] == ("accepted", bill)
    assert "Being processed" in ven.get("/portal/invoices").get_data(as_text=True)
    r = acc.post(f"/vendors/ap-inbox/{vi_new['id']}", data={"action": "reject", "reason": "No PO number on the invoice"})
    html = ven.get("/portal/invoices").get_data(as_text=True)
    assert "Returned" in html and "No PO number" in html
    # payments page shows the posted bills with what is still owed
    assert "Payments made to you" in ven.get("/portal/payments").get_data(as_text=True)
    # document renewal: accepted by staff, it joins the vendor's documents
    with app.app_context():
        du = q("SELECT * FROM vendor_doc_updates WHERE partner_id = ? AND state = 'pending'", (gulf["id"],), one=True)
        ndocs = q("SELECT COUNT(*) FROM vendor_documents WHERE partner_id = ?", (gulf["id"],), one=True)[0]
    assert "Changes waiting for review" in adm.get(f"/vendors/{gulf['id']}").get_data(as_text=True)
    acc.post(f"/vendors/doc-updates/{du['id']}/approve")
    with app.app_context():
        assert q("SELECT COUNT(*) FROM vendor_documents WHERE partner_id = ?", (gulf["id"],), one=True)[0] == ndocs + 1
    # bank change: nothing changes until finance approves; one request at a time
    with app.app_context():
        assert q("SELECT iban FROM partners WHERE id = ?", (gulf["id"],), one=True)[0] == old_iban
    r = ven.post("/portal/company", data={"csrf": tok, "bank_name": "X", "iban": "SA0380000000608010167519",
                                          "account_name": "Gulf", "file": (io.BytesIO(b"%PDF-1.4"), "l.pdf")},
                 content_type="multipart/form-data", follow_redirects=True)
    assert "being verified" in r.get_data(as_text=True)
    with app.app_context():
        req = q("SELECT * FROM approval_requests WHERE doc_type = 'bank_change' AND doc_id = ? AND state = 'pending'",
                (bank["id"],), one=True)
        assert [x_[0] for x_ in q("SELECT user_id FROM approval_tasks WHERE request_id = ? AND state = 'pending'",
                                 (req["id"],))] == [uid["admin"]]
    acc.post(f"/approvals/{req['id']}/approve", data={"comment": "ok"})
    with app.app_context():
        assert q("SELECT state FROM bank_changes WHERE id = ?", (bank["id"],), one=True)[0] == "pending"
    adm.post(f"/approvals/{req['id']}/approve", data={"comment": "Called the vendor on the known number"})
    with app.app_context():
        p = q("SELECT * FROM partners WHERE id = ?", (gulf["id"],), one=True)
        assert p["iban"] == "SA4420000001234567891234" and p["bank_name"] == "Riyad Bank" and p["swift"] == "RIBLSARI"
        assert q("SELECT state FROM bank_changes WHERE id = ?", (bank["id"],), one=True)[0] == "approved"
        assert q("SELECT 1 FROM audit_log WHERE action = 'bank_change_approved' AND record_id = ?", (gulf["id"],), one=True)
    # staff editing a vendor's IBAN also goes through approval
    with app.app_context():
        arab = q("SELECT * FROM partners WHERE name_en = 'Arabian Tools Trading'", one=True)
    r = acc.post(f"/vendors/{arab['id']}", data={"vendor_category": arab["vendor_category"] or "", "payment_terms": "30",
                                                 "bank_name": "Alinma Bank", "iban": "SA03 8000 0000 6080 1016 7519",
                                                 "account_name": "Arabian Tools Trading", "swift": ""},
                 follow_redirects=True)
    assert "waiting for finance approval" in r.get_data(as_text=True)
    with app.app_context():
        assert q("SELECT iban FROM partners WHERE id = ?", (arab["id"],), one=True)[0] == arab["iban"]
        c2 = q("SELECT * FROM bank_changes WHERE partner_id = ? AND state = 'pending'", (arab["id"],), one=True)
        req2 = q("SELECT id FROM approval_requests WHERE doc_type = 'bank_change' AND doc_id = ? AND state = 'pending'",
                 (c2["id"],), one=True)[0]
    adm.post(f"/approvals/{req2}/approve", data={"comment": "ok"})
    with app.app_context():
        assert q("SELECT iban FROM partners WHERE id = ?", (arab["id"],), one=True)[0] == "SA0380000000608010167519"
        assert not q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")


def test_invoice_types():
    from datetime import date
    from vertex.money import to_int
    app = make_app()
    acc, adm = app.test_client(), app.test_client()
    login(acc, "accountant", "account123")
    login(adm)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE company SET lock_date = NULL")
        dbcommit()
        c = q("SELECT * FROM company", one=True)
        adv_acc = c["advance_account_id"]
        ruh = q("SELECT id FROM branches WHERE code = 'RUH'", one=True)[0]
        jinv = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'INV'", (ruh,), one=True)[0]
        cust = q("SELECT id FROM partners WHERE name_en = 'Gulf Horizon Contracting'", one=True)[0]
        rev = q("SELECT id FROM accounts WHERE code = '4100'", one=True)[0]
        vat = q("SELECT id FROM taxes WHERE name_en = 'VAT 15% (Sales)'", one=True)[0]
        out_vat = q("SELECT account_id FROM taxes WHERE id = ?", (vat,), one=True)[0]
        seeded_pro = q("SELECT id FROM invoices WHERE invoice_type = 'proforma'", one=True)[0]
        seeded_dn = q("SELECT id FROM invoices WHERE invoice_type = 'debit_note'", one=True)[0]
        seeded_simple = q("SELECT id FROM invoices WHERE invoice_type = 'simplified'", one=True)[0]

    def new_invoice(itype, price, account=rev, action="post", extra=None):
        data = {"partner_id": cust, "branch_id": ruh, "journal_id": jinv, "date": date.today().isoformat(),
                "invoice_type": itype, "description": ["Work"], "account_id": [account], "quantity": ["1"],
                "price_unit": [str(price)], "tax_id": [vat], "product_id": [""], "order_line_id": [""],
                "cost_centre_id": [""], "action": action, **(extra or {})}
        r = acc.post("/invoices/out_invoice/new", data=data, follow_redirects=False)
        assert r.status_code == 302, r.get_data(as_text=True)[:500]
        return int(r.headers["Location"].rstrip("/").split("/")[-1])

    def entry(inv_id):
        with app.app_context():
            mv = q("SELECT move_id FROM invoices WHERE id = ?", (inv_id,), one=True)[0]
            return {(r["account_id"]): (r["debit"], r["credit"]) for r in q(
                "SELECT account_id, SUM(debit) AS debit, SUM(credit) AS credit FROM move_lines WHERE move_id = ? "
                "GROUP BY account_id", (mv,))}

    # advance: booked to Advances from Customers whatever account was typed
    adv = new_invoice("advance", 10000)
    e = entry(adv)
    assert e[adv_acc] == (0, to_int(10000)) and e[out_vat] == (0, to_int(1500)), e
    assert rev not in e
    # final invoice: 30,000 less the 10,000 advance -> customer owes 23,000
    fin = new_invoice("standard", 30000, action="save")
    r = acc.post(f"/invoices/view/{fin}/deduct_advance", data={"advance_id": adv, "amount": "12000"}, follow_redirects=True)
    assert "more than what is left" in r.get_data(as_text=True)
    acc.post(f"/invoices/view/{fin}/deduct_advance", data={"advance_id": adv, "amount": "10000"})
    with app.app_context():
        inv = q("SELECT * FROM invoices WHERE id = ?", (fin,), one=True)
        assert inv["amount_total"] == to_int(23000), inv["amount_total"]
    # editing the draft keeps the deduction
    acc.post(f"/invoices/out_invoice/{fin}/edit", data={"partner_id": cust, "branch_id": ruh, "journal_id": jinv,
             "date": date.today().isoformat(), "invoice_type": "standard", "description": ["Work"], "account_id": [rev],
             "quantity": ["1"], "price_unit": ["30000"], "tax_id": [vat], "product_id": [""], "order_line_id": [""],
             "cost_centre_id": [""], "action": "save"})
    with app.app_context():
        assert q("SELECT amount_total FROM invoices WHERE id = ?", (fin,), one=True)[0] == to_int(23000)
    html = acc.get(f"/invoices/print/{fin}").get_data(as_text=True)
    assert "Tax Invoice (Final)" in html and "Less: advance invoice" in html
    acc.post(f"/invoices/view/{fin}/post")
    e = entry(fin)
    assert e[rev] == (0, to_int(30000)) and e[adv_acc] == (to_int(10000), 0)
    assert e[out_vat] == (to_int(1500), to_int(4500)) or (e[out_vat][1] - e[out_vat][0]) == to_int(3000), e
    with app.app_context():
        assert q("SELECT SUM(debit - credit) FROM move_lines WHERE account_id = ? AND move_id IN "
                 "(SELECT move_id FROM invoices WHERE id IN (?, ?))", (adv_acc, adv, fin), one=True)[0] == 0
    # the advance is used up; it cannot be deducted again, nor reset while deducted
    fin2 = new_invoice("standard", 5000, action="save")
    r = acc.post(f"/invoices/view/{fin2}/deduct_advance", data={"advance_id": adv, "amount": "1000"}, follow_redirects=True)
    assert "not available" in r.get_data(as_text=True)
    r = acc.post(f"/invoices/view/{adv}/draft", follow_redirects=True)
    assert "deducted on a posted invoice" in r.get_data(as_text=True)
    # a negative line typed on the advances account is refused (it would not be tracked)
    bad = new_invoice("standard", 1000, action="save")
    with app.app_context():
        dbx("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, subtotal, "
            "tax_amount) VALUES (?, 9, 'manual', ?, 1, ?, ?, 0)", (bad, adv_acc, -to_int(500), -to_int(500)))
        dbx("UPDATE invoices SET amount_untaxed = amount_untaxed - ?, amount_total = amount_total - ? WHERE id = ?",
            (to_int(500), to_int(500), bad))
        dbcommit()
    r = acc.post(f"/invoices/view/{bad}/post", follow_redirects=True)
    assert "Deduct advance" in r.get_data(as_text=True)
    # proforma: never posted, issued with its own number, converted once into a draft tax invoice
    with app.app_context():
        tb_before = q("SELECT COUNT(*), SUM(debit) FROM move_lines", one=True)[:]
    pro = new_invoice("proforma", 8000, action="save")
    r = acc.post(f"/invoices/view/{pro}/post", follow_redirects=True)
    assert "not posted" in r.get_data(as_text=True)
    acc.post(f"/invoices/view/{pro}/issue_proforma")
    with app.app_context():
        p = q("SELECT * FROM invoices WHERE id = ?", (pro,), one=True)
        assert p["state"] == "proforma" and p["number"].startswith("PRO/") and not p["move_id"]
        assert q("SELECT COUNT(*), SUM(debit) FROM move_lines", one=True)[:] == tb_before
    assert "This is not a tax invoice" in acc.get(f"/invoices/print/{pro}").get_data(as_text=True)
    assert acc.get(f"/invoices/pdf/{pro}").data[:4] == b"%PDF"
    assert p["number"] in acc.get("/invoices/out_invoice?type=proforma").get_data(as_text=True)
    assert p["number"] not in acc.get("/invoices/out_invoice").get_data(as_text=True)
    r = acc.post(f"/invoices/view/{pro}/convert")
    conv = int(r.headers["Location"].rstrip("/").split("/")[-1])
    with app.app_context():
        cv = q("SELECT * FROM invoices WHERE id = ?", (conv,), one=True)
        assert cv["invoice_type"] == "standard" and cv["proforma_id"] == pro and cv["amount_untaxed"] == to_int(8000)
        assert q("SELECT state FROM invoices WHERE id = ?", (pro,), one=True)[0] == "converted"
    r = acc.post(f"/invoices/view/{pro}/convert", follow_redirects=True)
    assert "not allowed" in r.get_data(as_text=True) or "already converted" in r.get_data(as_text=True)
    acc.post(f"/invoices/view/{conv}/post")
    # debit note on the final invoice: needs the reason, then Dr AR / Cr revenue / Cr VAT
    r = acc.post(f"/invoices/view/{fin}/debit_note")
    dn = int(r.headers["Location"].rstrip("/").split("/edit")[0].split("/")[-1])
    data = {"partner_id": cust, "branch_id": ruh, "journal_id": jinv, "date": date.today().isoformat(),
            "invoice_type": "debit_note", "origin_id": fin, "description": ["Extra"], "account_id": [rev],
            "quantity": ["1"], "price_unit": ["2000"], "tax_id": [vat], "product_id": [""], "order_line_id": [""],
            "cost_centre_id": [""], "action": "post"}
    r = acc.post(f"/invoices/out_invoice/{dn}/edit", data=data, follow_redirects=True)
    assert "reason for the debit note" in r.get_data(as_text=True)
    acc.post(f"/invoices/out_invoice/{dn}/edit", data=dict(data, note_reason="Price correction"))
    with app.app_context():
        d_ = q("SELECT * FROM invoices WHERE id = ?", (dn,), one=True)
        assert d_["state"] == "posted" and d_["origin_id"] == fin and d_["amount_total"] == to_int(2300)
    html = acc.get(f"/invoices/print/{dn}").get_data(as_text=True)
    assert "Debit Note" in html and "Price correction" in html
    assert "Simplified Tax Invoice" in acc.get(f"/invoices/print/{seeded_simple}").get_data(as_text=True)
    # pages and the advances report, both languages
    for url in ("/invoices/advances", "/invoices/advances?open=1", f"/invoices/view/{seeded_pro}", f"/invoices/view/{seeded_dn}",
                f"/invoices/view/{fin}", "/invoices/out_invoice/new?type=advance", "/invoices/out_invoice?type=advance",
                "/setup/company"):
        for lang in ("en", "ar"):
            adm.get(f"/lang/{lang}")
            assert adm.get(url).status_code == 200, (url, lang)
    adm.get("/lang/en")
    assert adm.get("/invoices/advances?export=pdf").data[:4] == b"%PDF"
    with app.app_context():
        assert not q("SELECT move_id FROM move_lines GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        # Advances from Customers = advances invoiced - deducted (seeded clinic advance fully deducted; ours too)
        bal = q("SELECT SUM(credit - debit) FROM move_lines WHERE account_id = ?", (adv_acc,), one=True)[0]
        assert bal == 0, bal


def test_security():
    import json
    from vertex.modules import make_license
    app = make_app()
    app.config["CSRF_ENABLED"] = True
    c = app.test_client()
    # login itself needs the token
    r = c.post("/login", data={"username": "admin", "password": "admin123"}, follow_redirects=True)
    assert "form expired" in r.get_data(as_text=True)
    login(c)
    html = c.get("/").get_data(as_text=True)
    assert "demo password that is published" in html
    tok = re.search(r'name="csrf-token" content="([^"]+)"', html).group(1)
    # a forged POST (no token, or a wrong one) is refused and changes nothing
    with app.app_context():
        n = q("SELECT COUNT(*) FROM approval_roles", one=True)[0]
    for data in ({"name_en": "Forged"}, {"name_en": "Forged", "csrf": "wrong"}):
        c.post("/setup/workflow/roles", data=data)
    with app.app_context():
        assert q("SELECT COUNT(*) FROM approval_roles", one=True)[0] == n
    c.post("/setup/workflow/roles", data={"name_en": "Real", "csrf": tok})
    with app.app_context():
        assert q("SELECT COUNT(*) FROM approval_roles", one=True)[0] == n + 1
    # script POSTs send the header
    r = c.post("/branch", data={"branch": "all"}, headers={"X-CSRF-Token": tok})
    assert r.status_code == 302 and "expired" not in (r.headers.get("Location") or "")
    # headers and cookie
    r = c.get("/")
    assert r.headers["X-Frame-Options"] == "SAMEORIGIN" and r.headers["X-Content-Type-Options"] == "nosniff"
    assert app.config["SESSION_COOKIE_SAMESITE"] == "Lax" and app.config["SESSION_COOKIE_HTTPONLY"]
    # five wrong passwords lock the account, even with the right one afterwards
    d = app.test_client()
    for _ in range(5):
        m = re.search(r'name="csrf" value="([^"]+)"', d.get("/login").get_data(as_text=True)).group(1)
        d.post("/login", data={"username": "viewer", "password": "nope", "csrf": m})
    m = re.search(r'name="csrf" value="([^"]+)"', d.get("/login").get_data(as_text=True)).group(1)
    r = d.post("/login", data={"username": "viewer", "password": "viewer123", "csrf": m}, follow_redirects=True)
    assert "Too many attempts" in r.get_data(as_text=True)
    with app.app_context():
        assert q("SELECT COUNT(*) FROM audit_log WHERE action = 'login_failed'", one=True)[0] >= 5
    # password rules: short, no digits, demo passwords are refused
    for bad in ("short1", "longpassword", "admin123"):
        r = c.post("/password", data={"current": "admin123", "new": bad, "confirm": bad, "csrf": tok},
                   follow_redirects=True)
        assert "Password changed" not in r.get_data(as_text=True), bad
    r = c.post("/password", data={"current": "admin123", "new": "Vertex2026x", "confirm": "Vertex2026x", "csrf": tok},
               follow_redirects=True)
    assert "Password changed" in r.get_data(as_text=True) and "demo password that is published" not in \
        c.get("/").get_data(as_text=True)
    # viewers cannot change anything
    v = app.test_client()
    login(v, "manager", "manager123")
    # backups: made, listed, downloaded as a zip containing the database (the provider granted backups to IT)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE company SET prime_grants = 'backup'")
        dbcommit()
    r = c.post("/setup/backups", data={"csrf": tok}, follow_redirects=True)
    assert "Backup created" in r.get_data(as_text=True)
    name = re.search(r"(vertex-\d{8}-\d{6}-manual\.zip)", r.get_data(as_text=True)).group(1)
    z = c.get(f"/setup/backups/{name}")
    assert z.status_code == 200 and z.data[:2] == b"PK"
    import io, zipfile
    names = zipfile.ZipFile(io.BytesIO(z.data)).namelist()
    assert "vertex.db" in names and any(n_.startswith("uploads/") for n_ in names)
    assert c.get("/setup/backups/../vertex.db").status_code == 404
    assert v.get("/setup/backups").status_code == 302  # admin only
    # portal: forgot password gives the same answer for unknown addresses and emails a link to real ones
    app.config["MAIL_OUTBOX"] = outbox = []
    p = app.test_client()
    for email in ("nobody@nowhere.example", "sales@gulfsafety.example"):
        m = re.search(r'name="csrf" value="([^"]+)"', p.get("/portal/forgot").get_data(as_text=True)).group(1)
        r = p.post("/portal/forgot", data={"csrf": m, "email": email}, follow_redirects=True)
        assert "If this address has a portal account" in r.get_data(as_text=True)
    assert len(outbox) == 1 and "/portal/activate/" in outbox[0].get_body(("plain",)).get_content()
    # the daily job reminds vendors of documents about to expire
    with app.app_context(), app.test_request_context():
        from datetime import date, timedelta
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.vendorportal import expiry_reminders
        gulf = q("SELECT id FROM partners WHERE name_en = 'Gulf Safety Equipment Co.'", one=True)[0]
        dbx("INSERT INTO vendor_documents (partner_id, doc_type, number, expiry_date) VALUES (?, 'ISO Certificate', 'ISO-1', ?)",
            (gulf, (date.today() + timedelta(days=7)).isoformat()))
        dbcommit()
        n0 = len(outbox)
        assert expiry_reminders() >= 1 and len(outbox) > n0
    # an external referrer is never followed
    r = c.get("/lang/en", headers={"Referer": "https://evil.example/x"})
    assert "evil.example" not in r.headers["Location"]


def test_partner_ledger_and_notes():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        cust = q("SELECT partner_id FROM invoices WHERE kind = 'out_invoice' AND state = 'posted' LIMIT 1", one=True)[0]
        pick = q("SELECT id FROM pickings WHERE kind = 'out' AND state = 'done' LIMIT 1", one=True)[0]
        from vertex.ledger import company
    for url in ("/reports/partner-ledger", "/reports/partner-ledger?side=vendor", "/reports/partner-ledger?side=all",
                f"/reports/partner-ledger?side=customer&partner_id={cust}&date_from=2000-01-01"):
        for lang in ("en", "ar"):
            c.get(f"/lang/{lang}")
            assert c.get(url).status_code == 200, (url, lang)
        for kind in ("pdf", "xlsx", "csv"):
            assert c.get(url + ("&" if "?" in url else "?") + f"export={kind}").status_code == 200
    c.get("/lang/en")
    # closing balance of a customer = its receivable lines on the ledger
    html = c.get(f"/reports/partner-ledger?side=customer&partner_id={cust}&date_from=2000-01-01&date_to=2999-12-31").get_data(as_text=True)
    with app.app_context():
        bal = q("SELECT SUM(ml.debit - ml.credit) FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a "
                "ON a.id = ml.account_id WHERE m.state = 'posted' AND ml.partner_id = ? AND a.kind = 'asset_receivable'",
                (cust,), one=True)[0]
    from vertex.money import fmt
    assert fmt(bal, 2) in html
    # delivery note prints without prices
    r = c.get(f"/inventory/transfers/{pick}?export=pdf")
    assert r.status_code == 200 and r.data[:4] == b"%PDF"
    # cost centres on expense lines only: revenue no longer needs one
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from vertex.costcentres import needs_cost_centre
        rev = q("SELECT id FROM accounts WHERE code = '4100'", one=True)[0]
        exp = q("SELECT id FROM accounts WHERE code = '6200'", one=True)[0]
        assert needs_cost_centre(rev) and needs_cost_centre(exp)
        dbx("UPDATE company SET cost_centre_scope = 'expense'")
        dbcommit()
        assert not needs_cost_centre(rev) and needs_cost_centre(exp)
    assert "Available to add" not in c.get("/").get_data(as_text=True)


def test_partner_accounts():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        def kind_total(kind):
            return q("SELECT SUM(ml.debit - ml.credit) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                     "JOIN accounts a ON a.id = ml.account_id WHERE m.state='posted' AND a.kind = ?", (kind,), one=True)[0] or 0
        ar0, ap0 = kind_total("asset_receivable"), kind_total("liability_payable")
        cust = q("SELECT partner_id FROM invoices WHERE kind='out_invoice' AND state='posted' GROUP BY partner_id "
                 "ORDER BY COUNT(*) DESC LIMIT 1", one=True)[0]
        cbal = q("SELECT SUM(ml.debit - ml.credit) FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a "
                 "ON a.id = ml.account_id WHERE m.state='posted' AND ml.partner_id = ? AND a.kind='asset_receivable'",
                 (cust,), one=True)[0]
        f = {k: (v if v is not None else "") for k, v in dict(q("SELECT * FROM company", one=True)).items()}
    f.update(partner_accounts="each")
    r = c.post("/setup/company", data=f, follow_redirects=True)
    assert r.status_code == 200 and b"partner accounts opened" in r.data
    with app.app_context():
        own = q("SELECT * FROM accounts WHERE partner_id = ? AND kind='asset_receivable'", (cust,), one=True)
        assert own and own["code"].startswith("1100-") and not own["is_group"]
        assert q("SELECT receivable_account_id FROM partners WHERE id = ?", (cust,), one=True)[0] == own["id"]
        # totals unchanged; the customer's whole balance now sits on its own account; shared account is empty for it
        assert kind_total("asset_receivable") == ar0 and kind_total("liability_payable") == ap0
        on_own = q("SELECT SUM(ml.debit - ml.credit) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                   "WHERE m.state='posted' AND ml.account_id = ?", (own["id"],), one=True)[0]
        assert on_own == cbal
        left = q("SELECT COUNT(*) FROM (SELECT ml.partner_id, ml.account_id, SUM(ml.debit-ml.credit) b FROM move_lines ml "
                 "JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id WHERE m.state='posted' "
                 "AND a.kind IN ('asset_receivable','liability_payable') AND a.partner_id IS NULL AND ml.partner_id IS NOT NULL "
                 "GROUP BY 1, 2 HAVING b != 0)", one=True)[0]
        assert left == 0, left
        assert q("SELECT SUM(debit) - SUM(credit) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                 "WHERE m.state='posted'", one=True)[0] == 0
        # running again opens nothing new
        from vertex.partneraccts import open_all, sweep
        assert open_all()[0] == 0 and sweep() == 0
        # a brand-new customer gets its own account on save
    r = c.post("/setup/partners/new", data={"name_en": "Brand New Customer", "is_customer": "1", "active": "1"},
               follow_redirects=True)
    assert r.status_code == 200
    with app.app_context():
        pid = q("SELECT id FROM partners WHERE name_en = 'Brand New Customer'", one=True)[0]
        acc = q("SELECT * FROM accounts WHERE partner_id = ?", (pid,), one=True)
        assert acc and acc["name_en"] == "Brand New Customer"
        from vertex.ledger import partner_account
        assert partner_account(pid, True) == acc["id"]
    assert c.get("/setup/accounts").status_code == 200
    assert c.get("/reports/partner-ledger").status_code == 200


def test_pos_with_csrf():
    """The POS sale screen posts by script: with the security check on, a sale must still save."""
    import re
    app = make_app()
    app.config["CSRF_ENABLED"] = True
    c = app.test_client()
    login(c)
    with app.app_context():
        s = q("SELECT id FROM pos_sessions WHERE state = 'open' LIMIT 1", one=True)
        prod = q("SELECT id FROM products WHERE active=1 AND type != 'consumable' AND sale_price > 0 LIMIT 1", one=True)
    if not s:
        return
    html = c.get(f"/pos/session/{s[0]}").get_data(as_text=True)
    tok = re.search(r"'X-CSRF-Token': '([^']+)'", html).group(1)
    body = {"cash": "1000", "items": [{"product_id": prod[0], "qty": 1, "discount": 0}]}
    assert c.post(f"/pos/session/{s[0]}/order", json=body).status_code == 400
    r = c.post(f"/pos/session/{s[0]}/order", json=body, headers={"X-CSRF-Token": tok})
    assert r.status_code == 200 and r.get_json()["ok"], r.get_data(as_text=True)


def test_code_editor():
    app = make_app()
    app.config["TESTING"] = True  # "Apply & restart" only runs the load check, never restarts the test process
    c = app.test_client()
    login(c)
    # a normal admin does not see it and cannot open it
    assert c.get("/setup/code/").status_code in (302, 403)
    assert c.post("/setup/code/save", data={"path": "app.py", "content": "x"}).status_code in (302, 403)
    assert "Code Editor" not in c.get("/").get_data(as_text=True)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE users SET level = 'prime' WHERE username = 'admin'")
        dbcommit()
    assert c.get("/setup/code/").status_code == 200
    for bad in ("../instance/vertex.db", "instance/vertex.db", "/etc/passwd", "vertex/../../x.py", ".venv/bin/python"):
        assert c.get("/setup/code/?path=" + bad).status_code == 404, bad
    rel = "vertex/templates/setup/code_search.html"
    import os
    from vertex.views.codeedit import BASE
    full = os.path.join(BASE, rel)
    original = open(full, encoding="utf-8").read()
    try:
        assert c.get("/setup/code/?path=" + rel).status_code == 200
        # a broken template is refused and the file is untouched
        r = c.post("/setup/code/save", data={"path": rel, "content": original.replace("{% endblock %}", "{% endblok %}", 1)})
        assert r.status_code == 400 and open(full, encoding="utf-8").read() == original
        # broken Python is refused too
        r = c.post("/setup/code/save", data={"path": "vertex/money.py", "content": "def broken(:\n"})
        assert r.status_code == 400
        # a good change saves, is live, and is in the history
        new = original.replace("{{ t('Find in files') }}</h1>", "{{ t('Find in files') }} ✓</h1>", 1)
        r = c.post("/setup/code/save", data={"path": rel, "content": new.replace("\n", "\r\n"), "note": "test"})
        assert r.status_code == 302 and open(full, encoding="utf-8").read() == new
        assert "✓</h1>" in c.get("/setup/code/search").get_data(as_text=True)
        with app.app_context():
            cid = q("SELECT MAX(id) FROM code_changes", one=True)[0]
        assert c.get(f"/setup/code/history/{cid}").status_code == 200
        assert c.get("/setup/code/history").status_code == 200
        assert c.post(f"/setup/code/history/{cid}/restore").status_code == 302
        assert open(full, encoding="utf-8").read() == original
        assert c.get("/setup/code/search?q=superadmin_required").status_code == 200
        # restart = full load check in a separate process (no real restart under tests)
        r = c.post("/setup/code/restart")
        assert r.status_code == 302, r.get_data(as_text=True)[-500:]
    finally:
        with open(full, "w", encoding="utf-8") as f:
            f.write(original)


def test_cash_flow():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        from vertex.ledger import cash_flow, profit_loss
        ids = [r[0] for r in q("SELECT id FROM branches")]
        for bid in ids:
            for d1, d2 in (("2026-01-01", "2026-12-31"), ("2026-04-01", "2026-06-30")):
                st = cash_flow([bid], d1, d2)
                rows = {r["label"]: r["total"] for r in st.rows if r["kind"] != "section"}
                # opening cash + net change = closing cash, and the profit line is the P&L's net profit
                assert rows["Cash and bank at the beginning of the period"] + rows["Net increase / (decrease) in cash"] \
                    == rows["Cash and bank at the end of the period"]
                pl = [r for r in profit_loss([bid], d1, d2).rows if r.get("label") == "Net Profit"][0]["total"]
                assert rows["Net Profit"] == pl
                cash = q("SELECT COALESCE(SUM(ml.debit - ml.credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                         "JOIN accounts a ON a.id = ml.account_id WHERE m.state = 'posted' AND a.kind = 'asset_cash' "
                         "AND ml.branch_id = ? AND m.date <= ?", (bid, d2), one=True)[0]
                assert rows["Cash and bank at the end of the period"] == cash
        cash_flow(ids, "2026-01-01", "2026-12-31")  # consolidated, translated
    for lang in ("en", "ar"):
        c.get(f"/lang/{lang}")
        assert c.get("/reports/cash-flow").status_code == 200
        assert c.get("/reports/cash-flow?group=1&date_from=2026-01-01&date_to=2026-12-31").status_code == 200
    for kind in ("pdf", "xlsx", "csv"):
        assert c.get(f"/reports/cash-flow?export={kind}").status_code == 200


def test_duplicate_and_pager():
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        inv = q("SELECT * FROM invoices WHERE kind = 'in_invoice' AND state = 'posted' LIMIT 1", one=True)
        nlines = q("SELECT COUNT(*) FROM invoice_lines WHERE invoice_id = ? AND advance_invoice_id IS NULL", (inv["id"],), one=True)[0]
        mv = q("SELECT * FROM moves WHERE move_type = 'entry' AND state = 'posted' LIMIT 1", one=True)
        gen = q("SELECT id FROM moves WHERE move_type != 'entry' LIMIT 1", one=True)[0]
    v = c.get(f"/invoices/view/{inv['id']}").get_data(as_text=True)
    assert f"copy={inv['id']}" in v and "app.js" in v and "VX_T" in v
    html = c.get(f"/invoices/in_invoice/new?copy={inv['id']}").get_data(as_text=True)
    assert "Copy of" in html and html.count('name="description"') >= nlines
    assert c.get(f"/invoices/out_invoice/new?copy={inv['id']}").status_code == 404   # a bill is not copied as an invoice
    html = c.get(f"/entries/new?copy={mv['id']}").get_data(as_text=True)
    assert "Copy of" in html and 'name="account_id"' in html
    assert c.get(f"/entries/new?copy={gen}").status_code == 302                      # document entries: duplicate the document
    with app.app_context():
        assert q("SELECT COUNT(*) FROM invoices WHERE id > ?", (inv["id"] + 100000,), one=True)[0] == 0  # nothing saved by opening


def test_vendor_numbers_and_search():
    from datetime import date
    app = make_app()
    c = app.test_client()
    login(c)
    today = date.today().isoformat()
    with app.app_context():
        ruh = q("SELECT id FROM branches WHERE code = 'RUH'", one=True)[0]
        jbill = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'purchase'", (ruh,), one=True)[0]
        vend, other = [r[0] for r in q("SELECT id FROM partners WHERE is_vendor = 1 ORDER BY id LIMIT 2")]
        acc = q("SELECT id FROM accounts WHERE code = '6600'", one=True)[0]

    def bill(partner, ref, action="post"):
        return c.post("/invoices/in_invoice/new", data={
            "partner_id": partner, "branch_id": ruh, "journal_id": jbill, "date": today, "ref": ref,
            "description": ["Supplies"], "account_id": [acc], "quantity": ["1"], "price_unit": ["100"], "tax_id": [""],
            "product_id": [""], "order_line_id": [""], "cost_centre_id": [""], "action": action}, follow_redirects=True)

    assert "Vendor invoice no." in c.get("/invoices/in_invoice/new").get_data(as_text=True)
    r = bill(vend, "INV3840")
    assert "Posted" in r.get_data(as_text=True) or r.status_code == 200
    with app.app_context():
        first = q("SELECT id, state FROM invoices WHERE ref = 'INV3840'", one=True)
        assert first and first["state"] == "posted"
    # same vendor + same number (any case / spaces) is refused, also as a draft
    for ref, action in (("INV3840", "post"), (" inv3840 ", "draft")):
        html = bill(vend, ref, action).get_data(as_text=True)
        assert "is already booked for this vendor" in html, ref
    with app.app_context():
        assert q("SELECT COUNT(*) FROM invoices WHERE lower(trim(ref)) = 'inv3840'", one=True)[0] == 1
    # another vendor may use the same number
    bill(other, "INV3840")
    with app.app_context():
        assert q("SELECT COUNT(*) FROM invoices WHERE ref = 'INV3840'", one=True)[0] == 2
    # cancelling the first frees the number
    with app.app_context():
        from vertex.ledger import check_vendor_ref, AccountingError
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE invoices SET state = 'cancelled' WHERE id = ?", (first["id"],))
        assert check_vendor_ref(vend, "in_invoice", "INV3840") == "INV3840"
        # required number (Company setting)
        dbx("UPDATE company SET require_vendor_ref = 1"); dbcommit()
        try:
            check_vendor_ref(vend, "in_invoice", "", posting=True)
            assert False
        except AccountingError:
            pass
        assert check_vendor_ref(vend, "in_invoice", "", posting=False) is None
        assert check_vendor_ref(vend, "out_invoice", "X", posting=True) == "X"   # customers: not checked
        dbx("UPDATE company SET require_vendor_ref = 0"); dbcommit()
        # cheques: the same received cheque cannot be registered twice
        from vertex.cheques import register
        cust = q("SELECT id FROM partners WHERE is_customer = 1 LIMIT 1", one=True)[0]
        data = {"direction": "received", "branch_id": ruh, "partner_id": cust, "invoice_id": None, "cheque_no": "778899",
                "bank_name": "Al Rajhi", "issue_date": today, "cheque_date": today, "amount": 1000000, "bank_journal_id": None,
                "notes": ""}
        register(dict(data), 1)
        try:
            register(dict(data, cheque_no=" 778899 "), 1)
            assert False
        except AccountingError as e:
            assert "already registered" in str(e)
        register(dict(data, bank_name="SNB"), 1)   # same number from another bank is a different cheque
        dbcommit()
    # Odoo-style search: chips per field, combined; the tabs keep the search
    html = c.get("/invoices/in_invoice?s=ref:INV3840").get_data(as_text=True)
    assert 'class="facet"' in html and "INV3840" in html and html.count("data-href=") >= 1
    with app.app_context():
        n_ref = q("SELECT COUNT(*) FROM invoices WHERE kind='in_invoice' AND ref LIKE '%INV3840%'", one=True)[0]
    assert html.count('data-href="/invoices/view/') == n_ref
    html = c.get("/invoices/in_invoice?s=ref:INV3840&s=amount:100").get_data(as_text=True)
    assert html.count('data-href="/invoices/view/') == n_ref
    assert c.get("/invoices/in_invoice?s=ref:INV3840&s=amount:999.5").get_data(as_text=True).count('data-href="/invoices/view/') == 0
    tabs = c.get("/invoices/out_invoice?s=partner:noor").get_data(as_text=True)
    assert "s=partner:noor&amp;payment=overdue" in tabs
    for url in ("/invoices/out_invoice?s=product:a", "/entries/?s=account:1100&s=amount:100", "/payments/?partner_type=customer&s=invoice:INV",
                "/orders/sale?s=product:a", "/inventory/transfers?s=source:PO", "/procurement/mr?s=product:a",
                "/procurement/rfq?s=vendor:a", "/inventory/landed-costs?s=number:LC", "/invoices/out_invoice?q=noor"):
        r = c.get(url)
        assert r.status_code == 200, url


def test_numbers_bulk_notes_export_favorites():
    from datetime import date
    import io, zipfile
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        ruh, dxb = [q("SELECT id FROM branches WHERE code = ?", (b,), one=True)[0] for b in ("RUH", "DXB")]
        inv = q("SELECT * FROM invoices WHERE kind = 'out_invoice' AND state = 'posted' AND branch_id = ? "
                "AND invoice_type = 'standard' ORDER BY id DESC LIMIT 1", (dxb,), one=True)
        jd = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'sale'", (ruh,), one=True)[0]
        lines = q("SELECT * FROM invoice_lines WHERE invoice_id = ?", (inv["id"],))
    # an issued number cannot move to another branch after reset to draft
    assert c.post(f"/invoices/view/{inv['id']}/draft").status_code == 302
    data = {"partner_id": inv["partner_id"], "branch_id": ruh, "journal_id": jd, "date": inv["date"],
            "description": [l["description"] for l in lines], "account_id": [l["account_id"] for l in lines],
            "quantity": [l["quantity"] for l in lines], "price_unit": [l["price_unit"] / 1000 for l in lines],
            "tax_id": ["" for l in lines], "product_id": ["" for l in lines], "order_line_id": ["" for l in lines],
            "cost_centre_id": ["" for l in lines]}
    html = c.post(f"/invoices/out_invoice/{inv['id']}/edit", data=data, follow_redirects=True).get_data(as_text=True)
    assert "was already issued in" in html
    with app.app_context():
        assert q("SELECT branch_id, number FROM invoices WHERE id = ?", (inv["id"],), one=True)[0] == dxb
    # bulk: post the draft again from the list
    r = c.post("/invoices/bulk", data={"action": "post", "ids": [inv["id"]]}, headers={"Referer": "http://localhost/invoices/out_invoice"})
    assert r.status_code == 302
    with app.app_context():
        assert q("SELECT state FROM invoices WHERE id = ?", (inv["id"],), one=True)[0] == "posted"
        two = [r[0] for r in q("SELECT id FROM invoices WHERE kind = 'out_invoice' AND state = 'posted' LIMIT 2")]
    r = c.post("/invoices/bulk", data={"action": "pdf", "ids": two})
    assert r.status_code == 200 and r.mimetype == "application/zip"
    assert len(zipfile.ZipFile(io.BytesIO(r.data)).namelist()) == 2
    assert c.post("/entries/bulk", data={"action": "post", "ids": []}).status_code == 302
    # notes & history on the document
    v = c.get(f"/invoices/view/{inv['id']}").get_data(as_text=True)
    assert "Notes &amp; history" in v and "Reset to draft" in v
    c.post(f"/attachments/invoice/{inv['id']}/note", data={"body": "Customer asked for a copy by fax"})
    assert "Customer asked for a copy by fax" in c.get(f"/invoices/view/{inv['id']}").get_data(as_text=True)
    # list export to Excel
    r = c.post("/ux/export", json={"title": "Customer Invoices", "columns": [{"label": "Number"}, {"label": "Total", "num": True}],
                                   "rows": [{"cells": ["INV/1", "1,234.50"]}, {"cells": ["INV/2", "(80.00)"]}]})
    assert r.status_code == 200 and r.data[:2] == b"PK"
    from openpyxl import load_workbook
    ws = load_workbook(io.BytesIO(r.data)).active
    vals = [c_.value for row in ws.iter_rows() for c_ in row if c_.value is not None]
    assert "INV/1" in vals and any(isinstance(v_, (int, float)) and abs(v_ - 1234.5) < 0.001 for v_ in vals)
    # favourites
    c.post("/ux/favorites", data={"path": "/invoices/out_invoice", "name": "Overdue Noor", "query": "s=partner:noor&payment=overdue"})
    html = c.get("/invoices/out_invoice").get_data(as_text=True)
    assert "Overdue Noor" in html and "s=partner:noor&amp;payment=overdue" in html
    with app.app_context():
        fid = q("SELECT id FROM saved_filters WHERE name = 'Overdue Noor'", one=True)[0]
    c.post(f"/ux/favorites/{fid}/delete")
    assert "Overdue Noor" not in c.get("/invoices/out_invoice").get_data(as_text=True)
    # group by keeps through the filter form; lists still render in Arabic
    c.get("/lang/ar")
    for url in ("/invoices/out_invoice?g=1", "/entries/?g=2:m", "/payments/", f"/invoices/view/{inv['id']}"):
        assert c.get(url).status_code == 200, url
    c.get("/lang/en")
    # the viewer cannot log notes or bulk-post
    v2 = app.test_client(); login(v2, "viewer", "viewer123")
    assert v2.post(f"/attachments/invoice/{inv['id']}/note", data={"body": "x"}).status_code in (302, 403)
    with app.app_context():
        assert q("SELECT COUNT(*) FROM doc_notes WHERE body = 'x'", one=True)[0] == 0


def test_bill_creates_receipt():
    from datetime import date
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        ruh = q("SELECT id FROM branches WHERE code = 'RUH'", one=True)[0]
        jbill = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'purchase'", (ruh,), one=True)[0]
        vend = q("SELECT id FROM partners WHERE is_vendor = 1 LIMIT 1", one=True)[0]
        prod = q("SELECT * FROM products WHERE type = 'stock' LIMIT 1", one=True)
        acc = q("SELECT id FROM accounts WHERE code = '1300'", one=True) or q("SELECT id FROM accounts WHERE kind='asset_current' LIMIT 1", one=True)
    r = c.post("/invoices/in_invoice/new", data={
        "partner_id": vend, "branch_id": ruh, "journal_id": jbill, "date": date.today().isoformat(), "ref": "AUTO-REC-1",
        "description": [prod["name_en"]], "account_id": [acc[0]], "quantity": ["5"], "price_unit": ["10"], "tax_id": [""],
        "product_id": [prod["id"]], "order_line_id": [""], "cost_centre_id": [""], "action": "post"}, follow_redirects=True)
    html = r.get_data(as_text=True)
    assert "is ready in" in html, html[:300]
    with app.app_context():
        bill = q("SELECT id FROM invoices WHERE ref = 'AUTO-REC-1'", one=True)[0]
        pk = q("SELECT * FROM pickings WHERE invoice_id = ?", (bill,), one=True)
        assert pk and pk["kind"] == "in" and pk["state"] == "ready"
        assert q("SELECT SUM(quantity) FROM picking_lines WHERE picking_id = ?", (pk["id"],), one=True)[0] == 5
    # it is listed under Receipts, and posting again (reset + post) does not create a second one
    assert pk["number"] in c.get("/inventory/transfers?kind=in").get_data(as_text=True)
    c.post(f"/invoices/view/{bill}/draft")
    c.post(f"/invoices/view/{bill}/post")
    with app.app_context():
        assert q("SELECT COUNT(*) FROM pickings WHERE invoice_id = ? AND state != 'cancelled'", (bill,), one=True)[0] == 1


def test_business_types():
    import re
    # a trading company: no trace of projects, retention or progress billing anywhere
    app = make_app(business="trading")
    c = app.test_client()
    login(c)
    with app.app_context():
        assert q("SELECT business_type FROM company", one=True)[0] == "trading"
        assert q("SELECT COUNT(*) FROM projects", one=True)[0] == 0
        assert q("SELECT COUNT(*) FROM rfqs", one=True)[0] >= 2
        iid = q("SELECT id FROM invoices WHERE kind = 'out_invoice' AND state = 'posted' LIMIT 1", one=True)[0]
    home = c.get("/").get_data(as_text=True)
    assert "Projects" not in home and "Point of Sale" in home
    for url in ("/invoices/out_invoice/new", "/invoices/out_invoice", f"/invoices/view/{iid}", "/procurement/mr/new",
                "/orders/purchase/new", "/invoices/in_invoice/new", "/setup/company", "/reports/profit-loss"):
        html = c.get(url).get_data(as_text=True)
        assert c.get(url).status_code == 200, url
        for word in ("Progress Tax Invoice", 'name="project_id"', "Retention"):
            assert word not in html, (url, word)
    assert c.get("/projects/").status_code == 302
    # switching Projects on (custom) brings it back; nothing was deleted. Only with the provider's grant:
    r = c.post("/setup/license/modules", data={"business_type": "custom", "mod": ["sales", "inventory", "projects"]})
    assert "Projects" not in c.get("/").get_data(as_text=True)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        dbx("UPDATE company SET prime_grants = 'modules'")
        dbcommit()
    r = c.post("/setup/license/modules", data={"business_type": "custom", "mod": ["sales", "inventory", "projects"]})
    assert r.status_code == 302
    assert "Projects" in c.get("/").get_data(as_text=True)
    assert "Point of Sale" not in c.get("/").get_data(as_text=True)
    with app.app_context():
        mods = q("SELECT enabled_modules FROM company", one=True)[0].split(",")
        assert "accounting" in mods and "pos" not in mods
    # back to trading with the preset
    c.post("/setup/license/modules", data={"business_type": "trading"})
    assert "Projects" not in c.get("/").get_data(as_text=True)
    # a contractor: projects, no cash register or CRM
    app2 = make_app(business="contracting")
    c2 = app2.test_client()
    login(c2)
    home = c2.get("/").get_data(as_text=True)
    assert "Projects" in home and "Point of Sale" not in home and ">CRM<" not in home
    assert "Progress Tax Invoice" in c2.get("/invoices/out_invoice").get_data(as_text=True)
    assert c2.get("/pos/").status_code == 302
    with app2.app_context():
        assert q("SELECT name_en FROM company", one=True)[0] == "Vertex Contracting Co."
        # the books balance in both
        for a in (app, app2):
            pass
    for a in (app, app2):
        with a.app_context():
            assert q("SELECT SUM(debit) - SUM(credit) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                     "WHERE m.state = 'posted'", one=True)[0] == 0


def test_levels():
    app = make_app()
    c = app.test_client()
    login(c)                       # demo admin = the customer's system administrator (IT)
    with app.app_context():
        from vertex.db import x as dbx, commit as dbcommit
        from werkzeug.security import generate_password_hash
        assert q("SELECT level FROM users WHERE username = 'admin'", one=True)[0] == "super"
        dbx("INSERT INTO users (username, password_hash, full_name, role, all_branches, active, level) "
            "VALUES ('vendor', ?, 'Software provider', 'admin', 1, 1, 'prime')", (generate_password_hash("Vendor2026x", method="pbkdf2:sha256"),))
        dbcommit()
        prime_id = q("SELECT id FROM users WHERE username = 'vendor'", one=True)[0]
        mgr = q("SELECT id FROM users WHERE username = 'manager'", one=True)[0]
        nb = q("SELECT COUNT(*) FROM branches", one=True)[0]
    # IT admin: manages users, cannot see or touch the provider, cannot make a system administrator
    assert c.get("/setup/users").status_code == 200
    assert "<b>vendor</b>" not in c.get("/setup/users").get_data(as_text=True)
    assert c.get(f"/setup/users/{prime_id}").status_code == 404
    c.post(f"/setup/users/{mgr}", data={"username": "manager", "role": "admin", "level": "super", "active": "1"})
    with app.app_context():
        assert q("SELECT level FROM users WHERE id = ?", (mgr,), one=True)[0] is None
    # ... no modules, branches, backups or code without the provider's grant
    assert c.post("/setup/license/modules", data={"business_type": "trading"}).status_code == 302
    with app.app_context():
        assert q("SELECT business_type FROM company", one=True)[0] == "custom"
    c.post("/setup/branches/new", data={"code": "XX1", "name_en": "Test", "currency": "SAR"})
    assert c.get("/setup/backups").status_code == 302
    assert c.get("/setup/access").status_code == 302
    assert c.get("/setup/code/").status_code in (302, 403)
    with app.app_context():
        assert q("SELECT COUNT(*) FROM branches", one=True)[0] == nb
    home = c.get("/").get_data(as_text=True)
    assert "Code Editor" not in home and "Provider Access" not in home
    # a normal admin cannot manage users at all
    c2 = app.test_client(); login(c2, "manager", "manager123")
    assert c2.get("/setup/users").status_code == 302
    # the provider: sees everything, grants backups to IT
    p = app.test_client(); login(p, "vendor", "Vendor2026x")
    assert "<b>vendor</b>" in p.get("/setup/users").get_data(as_text=True)
    assert p.get("/setup/access").status_code == 200 and p.get("/setup/code/").status_code == 200
    p.post("/setup/access", data={"backup": "1"})
    assert c.get("/setup/backups").status_code == 200
    with app.app_context():
        assert q("SELECT prime_grants FROM company", one=True)[0] == "backup"
    # the provider makes the manager a system administrator
    p.post(f"/setup/users/{mgr}", data={"username": "manager", "role": "accountant", "level": "super", "active": "1",
                                         "all_branches": "1"})
    with app.app_context():
        r = q("SELECT level, role FROM users WHERE id = ?", (mgr,), one=True)
        assert r["level"] == "super" and r["role"] == "admin"
    # ... whom the other IT admin can no longer edit
    assert c.get(f"/setup/users/{mgr}").status_code == 302


def q_move(app, inv_id):
    with app.app_context():
        return q("SELECT move_id FROM invoices WHERE id = ?", (inv_id,), one=True)[0]


def test_trading_tools():
    """Batches with FEFO, write-offs, transfers keeping batches, reorder to draft POs, gross margin, cash forecast,
    supplier performance, labels and the app overviews."""
    from datetime import date, timedelta
    from vertex.db import commit, x
    from vertex.ledger import post_invoice
    from vertex.stock import create_invoice_picking, create_picking, quant, validate_picking
    app = make_app()
    c = app.test_client()
    login(c)
    today = date.today()
    ti = today.isoformat()
    with app.app_context():
        x("UPDATE company SET lock_date = NULL")
        acc = {r["code"]: r["id"] for r in q("SELECT id, code FROM accounts")}
        assert "5250" in acc and q("SELECT scrap_account_id FROM company", one=True)[0] == acc["5250"]
        b = q("SELECT * FROM branches WHERE code='RUH'", one=True)
        wh = q("SELECT id FROM warehouses WHERE branch_id=? ORDER BY id LIMIT 1", (b["id"],), one=True)["id"]
        show = q("SELECT id FROM warehouses WHERE code = 'SHW-RUH'", one=True)["id"]
        sj = q("SELECT id FROM journals WHERE branch_id=? AND code='INV'", (b["id"],), one=True)["id"]
        cust = x("INSERT INTO partners (name_en, is_customer) VALUES ('Fresh Mart', 1)")
        vend = x("INSERT INTO partners (name_en, is_vendor) VALUES ('Dairy Farm', 1)")
        prod = x("INSERT INTO products (code, name_en, type, sale_price, purchase_price, income_account_id, "
                 "expense_account_id, tracking, shelf_life_days) VALUES ('MLK', 'Milk 1L', 'stock', 20000, 10000, ?, ?, "
                 "'lot', 30)", (acc["4100"], acc["5100"]))
        pk1 = create_picking("in", b["id"], wh, ti, [{"product_id": prod, "quantity": 10, "unit_cost": 10000}], vend)
        pk2 = create_picking("in", b["id"], wh, ti, [{"product_id": prod, "quantity": 5, "unit_cost": 10000}], vend)
        l1 = q("SELECT id FROM picking_lines WHERE picking_id = ?", (pk1,), one=True)[0]
        l2 = q("SELECT id FROM picking_lines WHERE picking_id = ?", (pk2,), one=True)[0]
        commit()
        start = {k: balance(k) for k in ("1200", "5100", "5250")}
    # the receipt asks for the batch, and offers the expiry from the shelf life
    page = c.get(f"/inventory/transfers/{pk1}").get_data(as_text=True)
    assert f'name="lot_{l1}"' in page and (today + timedelta(days=30)).isoformat() in page
    c.post(f"/inventory/transfers/{pk1}", data={"action": "validate", f"qty_{l1}": "10", "date": ti})
    with app.app_context():
        assert q("SELECT state FROM pickings WHERE id = ?", (pk1,), one=True)[0] == "ready"
    r = c.post(f"/inventory/transfers/{pk1}", data={"action": "validate", f"qty_{l1}": "10", "date": ti,
                                                   f"lot_{l1}": "B1", f"exp_{l1}": (today + timedelta(days=10)).isoformat()})
    assert r.status_code == 302
    c.post(f"/inventory/transfers/{pk2}", data={"action": "validate", f"qty_{l2}": "5", "date": ti,
                                               f"lot_{l2}": "B2", f"exp_{l2}": (today + timedelta(days=60)).isoformat()})
    with app.app_context():
        lots = {r["number"]: r["id"] for r in q("SELECT id, number FROM lots WHERE product_id = ?", (prod,))}
        assert set(lots) == {"B1", "B2"}

        def lq(lot, w=wh):
            r = q("SELECT qty FROM lot_quants WHERE lot_id = ? AND warehouse_id = ?", (lots[lot], w), one=True)
            return r["qty"] if r else 0
        assert (lq("B1"), lq("B2")) == (10, 5)
        # sell 12: the earliest expiry (B1) goes first, then 2 from B2
        iid = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, state, amount_untaxed, "
                "amount_tax, amount_total) VALUES (?,?,'out_invoice',?,?,?,'draft',240000,0,240000)",
                (b["id"], sj, cust, ti, (today + timedelta(days=20)).isoformat()))
        x("INSERT INTO invoice_lines (invoice_id, product_id, description, account_id, quantity, price_unit, subtotal, "
          "tax_amount) VALUES (?,?,'Milk',?,12,20000,240000,0)", (iid, prod, acc["4100"]))
        post_invoice(iid, 1)
        dl = create_invoice_picking(iid, 1)
        validate_picking(dl, d=ti, user_id=1)
        assert (lq("B1"), lq("B2")) == (0, 3)
        assert balance("5100") - start["5100"] == 120000
        commit()
    page = c.get(f"/inventory/transfers/{dl}").get_data(as_text=True)
    assert "B1" in page and "B2" in page
    # B2 expires; write the rest off: Dr Inventory Write-offs, Cr Inventory, at average cost
    with app.app_context():
        x("UPDATE lots SET expiry_date = ? WHERE id = ?", ((today - timedelta(days=1)).isoformat(), lots["B2"]))
        commit()
    page = c.get("/inventory/batches?status=expired").get_data(as_text=True)
    assert "B2" in page and "B1" not in page
    assert c.get(f"/inventory/scrap?warehouse_id={wh}&product_id={prod}&lot_id={lots['B2']}&qty=3&reason=expired").status_code == 200
    r = c.post("/inventory/scrap", data={"warehouse_id": wh, "date": ti, "reason": "expired", "product_id": ["", str(prod)],
                                         "lot_id": ["", str(lots["B2"])], "qty": ["", "3"], "notes": "expired"})
    assert r.status_code == 302, r.get_data(as_text=True)[:400]
    with app.app_context():
        assert lq("B2") == 0 and quant(prod, wh) == (0, 0)
        assert balance("5250") - start["5250"] == 30000
        assert balance("1200") - start["1200"] == 0  # 150.00 in, 120.00 sold, 30.00 written off
        sc = q("SELECT * FROM pickings WHERE kind = 'scrap' ORDER BY id DESC LIMIT 1", one=True)
        assert sc["reason"] == "expired" and sc["number"].startswith("SCR/")
        # a batch survives a transfer to another warehouse
        pk3 = create_picking("in", b["id"], wh, ti, [{"product_id": prod, "quantity": 4, "unit_cost": 10000}], vend)
        l3 = q("SELECT id FROM picking_lines WHERE picking_id = ?", (pk3,), one=True)[0]
        validate_picking(pk3, d=ti, user_id=1, lots={l3: ("B3", (today + timedelta(days=90)).isoformat())})
        from vertex import wtransfers
        tid = wtransfers.create(wh, show, ti, [{"product_id": prod, "quantity": 4}], user_id=1)
        wtransfers.send(tid, ti, 1)
        lots = {r["number"]: r["id"] for r in q("SELECT id, number FROM lots WHERE product_id = ?", (prod,))}
        assert lq("B3") == 0 and lq("B3", show) == 4
        # a receipt without a batch typed (e.g. from a script) still gets a traceable batch named after it
        pk4 = create_picking("in", b["id"], wh, ti, [{"product_id": prod, "quantity": 1, "unit_cost": 10000}], vend)
        validate_picking(pk4, d=ti, user_id=1)
        num = q("SELECT number FROM pickings WHERE id = ?", (pk4,), one=True)[0]
        assert q("SELECT expiry_date FROM lots WHERE product_id = ? AND number = ?", (prod, num), one=True)[0] == \
            (today + timedelta(days=30)).isoformat()
        x("UPDATE products SET min_qty = 5 WHERE id = ?", (prod,))
        commit()
    page = c.get(f"/inventory/batches/{lots['B1']}").get_data(as_text=True)
    assert "Fresh Mart" in page  # who received the batch, for a recall
    # reorder: 12 sold in 90 days, 1 on hand, reorder level 5 -> suggested, then a draft PO for the vendor
    with app.app_context():
        from vertex.insights import reorder_suggestions
        row = [r for r in reorder_suggestions([b["id"]], wh) if r["product"]["id"] == prod][0]
        assert row["vendor_id"] == vend and row["qty"] >= 9 and row["forecast"] == 1, {k: v for k, v in row.items() if k not in ("product", "warehouse", "vendor")}
    key = f"{prod}-{wh}"
    r = c.post("/inventory/reorder", data={"pick": key, f"vendor_{key}": vend, f"qty_{key}": "20"})
    assert r.status_code == 302
    with app.app_context():
        po = q("SELECT * FROM orders WHERE kind = 'purchase' AND partner_id = ? ORDER BY id DESC LIMIT 1", (vend,), one=True)
        assert po["state"] == "draft" and po["warehouse_id"] == wh
        assert q("SELECT quantity, price_unit FROM order_lines WHERE order_id = ?", (po["id"],), one=True)[:] == (20, 10000)
        row = [r for r in reorder_suggestions([b["id"]], wh, True) if r["product"]["id"] == prod][0]
        assert row["draft"] == 20 and row["qty"] == 0  # not suggested twice
        # gross margin: 240.00 sales, 120.00 cost
        from vertex.insights import cash_forecast, gross_margin, supplier_performance
        rows, total, _ = gross_margin([b], ti, ti, "product")
        m = [r for r in rows if r["key"] == prod][0]
        assert (m["revenue"], m["cost"], m["margin"], round(m["pct"])) == (240000, 120000, 120000, 50)
        rows, _, _ = gross_margin([b], ti, ti, "customer")
        assert [r for r in rows if r["key"] == cust][0]["margin"] == 120000
        # the reconciliation ends on the Profit & Loss gross profit
        from vertex.insights import ledger_gross
        _, gl = ledger_gross([b], ti[:4] + "-01-01", ti)
        pl = profit_loss([b["id"]], ti[:4] + "-01-01", ti)
        assert [r for r in pl.rows if r.get("label") == "Gross Profit"][0]["total"] == gl["margin"]
        # cash forecast: the open invoice comes in during the week it is due
        fc = cash_forecast([b])
        week = ((today + timedelta(days=20)) - fc["weeks"][0]).days // 7
        inv_line = dict(fc["inflow"])["Customer invoices"]
        assert inv_line[week] >= 240000
        assert fc["closing"][-1] == fc["opening"] + sum(fc["total_in"]) - sum(fc["total_out"])
        assert isinstance(supplier_performance([b["id"]], "2000-01-01", ti), list)
    # barcodes and labels
    r = c.post("/inventory/barcodes/generate")
    assert r.status_code == 302
    with app.app_context():
        bc = q("SELECT barcode FROM products WHERE id = ?", (prod,), one=True)[0]
        from vertex.barcode import ean13_check
        assert len(bc) == 13 and bc[0] == "2" and ean13_check(bc[:12]) == bc[-1]
    page = c.get(f"/inventory/labels?product_id={prod}&copies=3").get_data(as_text=True)
    assert page.count('class="label"') == 3 and "<svg" in page
    assert c.get(f"/inventory/labels?picking_id={pk1}").get_data(as_text=True).count('class="label"') == 10
    for url in ("/insights/sales", "/insights/purchases", "/insights/inventory", "/insights/gross-margin",
                "/insights/gross-margin?by=customer", "/insights/gross-margin?by=category", "/insights/gross-margin?by=month",
                "/insights/cash-forecast", "/insights/cash-forecast?orders=0", "/insights/suppliers", "/inventory/reorder",
                "/inventory/batches", "/inventory/scrap", "/inventory/labels", "/inventory/transfers?kind=scrap",
                f"/setup/products/{prod}"):
        for lang in ("en", "ar"):
            c.get(f"/lang/{lang}")
            assert c.get(url).status_code == 200, (url, lang)
    c.get("/lang/en")
    for url in ("/insights/gross-margin?export=xlsx", "/insights/cash-forecast?export=pdf", "/insights/suppliers?export=csv"):
        assert c.get(url).status_code == 200, url
    with app.app_context():
        bad = q("SELECT move_id FROM move_lines ml JOIN moves m ON m.id = ml.move_id WHERE m.state='posted' "
                "GROUP BY move_id HAVING SUM(debit) != SUM(credit)")
        assert not bad


def test_archive_recent_columns():
    """Archive / restore master data (not while stock is on hand), recently viewed records, column chooser hooks."""
    from vertex.db import commit, x
    app = make_app()
    c = app.test_client()
    login(c)
    with app.app_context():
        stocked = q("SELECT product_id FROM stock_quants WHERE qty > 0 LIMIT 1", one=True)[0]
        free = x("INSERT INTO products (code, name_en, type, income_account_id, expense_account_id) "
                 "VALUES ('OLD', 'Old item', 'consumable', ?, ?)", (acc_id(app, "4100"), acc_id(app, "5100")))
        vend = x("INSERT INTO partners (name_en, is_vendor) VALUES ('Gone Trading', 1)")
        commit()
    page = c.get("/setup/products").get_data(as_text=True)
    assert "seg-tabs" in page and "Old item" in page and 'data-bulk="' in page
    # archive one from its form, and one with stock is refused
    assert c.post("/setup/products/archive", data={"key": free, "action": "archive"}).status_code == 302
    r = c.post("/setup/products/archive", data={"ids": [str(stocked)], "action": "archive"}, follow_redirects=True)
    assert "still has stock" in r.get_data(as_text=True)
    r = c.post(f"/setup/products/{stocked}", data={"code": "X", "name_en": "X", "type": "stock",
                                                    "income_account_id": acc_id(app, "4100"),
                                                    "expense_account_id": acc_id(app, "5100"), "min_qty": "0"})
    assert r.status_code == 200 and "still has stock" in r.get_data(as_text=True)
    with app.app_context():
        assert q("SELECT active FROM products WHERE id = ?", (free,), one=True)[0] == 0
        assert q("SELECT active FROM products WHERE id = ?", (stocked,), one=True)[0] == 1
        assert q("SELECT 1 FROM audit_log WHERE action = 'archive' AND record_id = ?", (free,), one=True)
    assert "Old item" not in c.get("/setup/products").get_data(as_text=True)
    page = c.get("/setup/products?archived=1").get_data(as_text=True)
    assert "Old item" in page and "Restore" in page
    assert "Archived" in c.get(f"/setup/products/{free}").get_data(as_text=True)
    # bulk on partners, then restore
    c.post("/setup/partners/archive", data={"ids": [str(vend)], "action": "archive"})
    assert "Gone Trading" not in c.get("/setup/partners?type=vendor").get_data(as_text=True)
    c.post("/setup/partners/archive", data={"ids": [str(vend)], "action": "restore"})
    assert "Gone Trading" in c.get("/setup/partners?type=vendor").get_data(as_text=True)
    # recently viewed: record pages are remembered per user, newest first, lists are not
    with app.app_context():
        inv = q("SELECT id, number FROM invoices WHERE state = 'posted' ORDER BY id LIMIT 1", one=True)
    c.get(f"/invoices/view/{inv['id']}")
    c.get(f"/setup/products/{stocked}")
    c.get("/setup/products")
    with app.app_context():
        rv = q("SELECT url, title, app FROM recent_views ORDER BY at DESC, rowid DESC")
        urls = [r["url"] for r in rv]
        assert f"/setup/products/{stocked}" in urls and f"/invoices/view/{inv['id']}" in urls and "/setup/products" not in urls
        assert [r for r in rv if r["url"] == f"/invoices/view/{inv['id']}"][0]["title"] == inv["number"]
    page = c.get("/dashboard").get_data(as_text=True)
    assert "recent-menu" in page and f"/invoices/view/{inv['id']}" in page
    # the shared script carries the column chooser
    js = c.get("/static/app.js").get_data(as_text=True)
    assert "colChooser" in js and "vx-cols:" in js
    for lang in ("ar", "en"):
        c.get(f"/lang/{lang}")
        for url in ("/setup/products", "/setup/products?archived=1", "/setup/products?archived=all", "/setup/warehouses",
                    "/setup/partners?type=customer", "/dashboard"):
            assert c.get(url).status_code == 200, (url, lang)


def acc_id(app, code):
    with app.app_context():
        return q("SELECT id FROM accounts WHERE code = ?", (code,), one=True)[0]


def q_pickings(app, oid):
    with app.app_context():
        return q("SELECT id FROM pickings WHERE order_id = ? AND kind = 'in' AND state = 'done'", (oid,), one=True)["id"]


def q_partner(app, lid):
    with app.app_context():
        return q("SELECT partner_id FROM leads WHERE id=?", (lid,), one=True)[0]


if __name__ == "__main__":
    for fn in (test_invariants, test_pages, test_workflow, test_procurement_assets_prepaid, test_license_modules,
               test_crm_pos_bank, test_imports, test_costing, test_attachments, test_statements_email, test_recurring, test_credit_control, test_year_end,
               test_cost_centres, test_budgets, test_coa_hierarchy, test_hr_payroll, test_foreign_currency, test_cheques, test_petty_cash, test_stock_transfers, test_exports, test_workflow_engine, test_projects, test_procurement_upgrade, test_supplier_onboarding, test_vendor_portal, test_invoice_types, test_security, test_partner_ledger_and_notes, test_partner_accounts, test_pos_with_csrf, test_code_editor, test_cash_flow, test_duplicate_and_pager, test_vendor_numbers_and_search, test_numbers_bulk_notes_export_favorites, test_bill_creates_receipt, test_business_types, test_levels, test_trading_tools, test_archive_recent_columns):
        fn()
        print("ok", fn.__name__)
