"""Initial setup plus a year of demo transactions for three branches (SAR, SAR, AED)."""
import random
from datetime import date, timedelta

from werkzeug.security import generate_password_hash

from .coa import auto_group
from .db import commit, get_db, q, x
from .migrations import ensure_branch_defaults, ensure_categories, ensure_costing_accounts, ensure_inventory_accounts, \
    ensure_module_accounts, ensure_year_end_accounts, ensure_hr_accounts, ensure_fx_accounts, ensure_cheque_accounts, ensure_transit_account, \
    ensure_retention_account, ensure_advance_account
from .ledger import compute_invoice_line, create_move, create_payment, create_transfer, now, post_invoice, rate_on
from .money import to_int, translate
from .stock import next_number

ACCOUNTS = [
    ("1010", "Cash on Hand", "النقدية في الصندوق", "asset_cash", 0),
    ("1020", "Bank Accounts", "الحسابات البنكية", "asset_cash", 0),
    ("1100", "Accounts Receivable", "الذمم المدينة - العملاء", "asset_receivable", 0),
    ("1200", "Inventory", "المخزون", "asset_current", 0),
    ("1205", "Inventory - Spare Parts", "المخزون - قطع الغيار", "asset_current", 0),
    ("1300", "Prepaid Expenses", "مصروفات مدفوعة مقدماً", "asset_prepayments", 0),
    ("1400", "Input VAT", "ضريبة القيمة المضافة - المدخلات", "asset_current", 0),
    ("1500", "Inter-branch Current Account", "الحساب الجاري بين الفروع", "asset_current", 1),
    ("1600", "Property, Plant & Equipment", "الممتلكات والآلات والمعدات", "asset_fixed", 0),
    ("1610", "Accumulated Depreciation", "مجمع الإهلاك", "asset_fixed", 0),
    ("1620", "Vehicles", "السيارات", "asset_fixed", 0),
    ("1625", "Accumulated Depreciation — Vehicles", "مجمع إهلاك السيارات", "asset_fixed", 0),
    ("1630", "Computers & IT Equipment", "أجهزة الحاسب وتقنية المعلومات", "asset_fixed", 0),
    ("1635", "Accumulated Depreciation — IT", "مجمع إهلاك أجهزة الحاسب", "asset_fixed", 0),
    ("2100", "Accounts Payable", "الذمم الدائنة - الموردون", "liability_payable", 0),
    ("2150", "Goods Received Not Invoiced", "بضاعة مستلمة لم تصل فواتيرها", "liability_current", 0),
    ("2200", "Output VAT", "ضريبة القيمة المضافة - المخرجات", "liability_current", 0),
    ("2300", "Accrued Expenses", "مصروفات مستحقة", "liability_current", 0),
    ("2400", "Salaries Payable", "رواتب مستحقة", "liability_current", 0),
    ("2600", "Deferred Revenue", "إيرادات مؤجلة", "liability_current", 0),
    ("2500", "End of Service Provision", "مخصص مكافأة نهاية الخدمة", "liability_non_current", 0),
    ("3100", "Share Capital", "رأس المال", "equity", 0),
    ("3200", "Statutory Reserve", "الاحتياطي النظامي", "equity", 0),
    ("3990", "Opening Balance Clearing", "حساب وسيط للأرصدة الافتتاحية", "equity", 0),
    ("4100", "Sales Revenue", "إيرادات المبيعات", "income", 0),
    ("4200", "Service Revenue", "إيرادات الخدمات", "income", 0),
    ("4900", "Other Income", "إيرادات أخرى", "income_other", 0),
    ("4910", "Gain on Disposal of Assets", "أرباح بيع الأصول", "income_other", 0),
    ("5100", "Cost of Goods Sold", "تكلفة البضاعة المباعة", "expense_direct_cost", 0),
    ("5200", "Inventory Adjustments", "تسويات المخزون", "expense_direct_cost", 0),
    ("6100", "Salaries & Wages", "الرواتب والأجور", "expense", 0),
    ("6200", "Rent", "الإيجار", "expense", 0),
    ("6300", "Utilities", "المرافق", "expense", 0),
    ("6400", "Marketing", "التسويق والإعلان", "expense", 0),
    ("6500", "Bank Charges", "الرسوم البنكية", "expense", 0),
    ("6600", "Office Supplies", "اللوازم المكتبية", "expense", 0),
    ("6700", "Depreciation Expense", "مصروف الإهلاك", "expense_depreciation", 0),
    ("6800", "Government Fees", "الرسوم الحكومية", "expense", 0),
    ("6850", "Insurance", "التأمين", "expense", 0),
    ("6910", "Loss on Disposal of Assets", "خسائر بيع الأصول", "expense", 0),
    ("6950", "Cash Over / Short", "فروقات النقدية", "expense", 0),
]

CURRENCIES = [
    ("SAR", "Saudi Riyal", "ريال سعودي", "ر.س", 2),
    ("AED", "UAE Dirham", "درهم إماراتي", "د.إ", 2),
    ("USD", "US Dollar", "دولار أمريكي", "$", 2),
    ("KWD", "Kuwaiti Dinar", "دينار كويتي", "د.ك", 3),
    ("BHD", "Bahraini Dinar", "دينار بحريني", "د.ب", 3),
]

BRANCHES = [
    ("RUH", "Riyadh — Head Office", "الرياض — المركز الرئيسي", "SAR", "300000000000003",
     "King Fahd Road, Riyadh", "طريق الملك فهد، الرياض"),
    ("JED", "Jeddah", "جدة", "SAR", "300000000000003", "Tahlia Street, Jeddah", "شارع التحلية، جدة"),
    ("DXB", "Dubai", "دبي", "AED", "100000000000003", "Business Bay, Dubai", "الخليج التجاري، دبي"),
]

PARTNERS = [
    ("Al Noor Trading Est.", "مؤسسة النور التجارية", 1, 0, "310000000000001"),
    ("Gulf Horizon Contracting", "شركة أفق الخليج للمقاولات", 1, 0, "310000000000002"),
    ("Desert Rose Hotels", "فنادق وردة الصحراء", 1, 0, "310000000000003"),
    ("Saudi Modern Clinics", "العيادات السعودية الحديثة", 1, 0, "310000000000004"),
    ("Emirates Retail Group", "مجموعة الإمارات للتجزئة", 1, 0, "100000000000011"),
    ("Marina Facilities LLC", "مارينا لإدارة المرافق ذ.م.م", 1, 0, "100000000000012"),
    ("Riyadh Office Supplies", "مكتبة الرياض للوازم المكتبية", 0, 1, "310000000000021"),
    ("National Logistics Co.", "الشركة الوطنية للخدمات اللوجستية", 0, 1, "310000000000022"),
    ("Al Waha Properties", "الواحة العقارية", 0, 1, "310000000000023"),
    ("TechSource Systems", "تك سورس للأنظمة", 1, 1, "310000000000024"),
    ("Dubai Properties LLC", "دبي للعقارات ذ.م.م", 0, 1, "100000000000025"),
]


PRODUCTS = [
    # code, en, ar, type, category, sale, cost, sale VAT, purchase VAT, income acc, expense acc, reorder level
    ("SAF-001", "Safety Helmet", "خوذة سلامة", "stock", "Safety", 85, 42, 15, 15, "4100", "5100", 40),
    ("SAF-002", "Safety Gloves (pair)", "قفازات سلامة (زوج)", "stock", "Safety", 25, 11, 15, 15, "4100", "5100", 100),
    ("SAF-003", "High-visibility Vest", "سترة عاكسة", "stock", "Safety", 45, 19, 15, 15, "4100", "5100", 50),
    ("IND-010", "Industrial Drill 750W", "مثقاب صناعي 750 واط", "stock", "Tools", 640, 410, 15, 15, "4100", "5100", 5),
    ("IND-011", "Angle Grinder", "صاروخ تجليخ", "stock", "Tools", 380, 230, 15, 15, "4100", "5100", 5),
    ("SPR-100", "Hydraulic Pump Seal Kit", "طقم موانع تسرب مضخة هيدروليكية", "stock", "Spare parts", 220, 120, 15, 15, "4100", "5100", 10),
    ("SRV-001", "Installation Service", "خدمة التركيب", "service", "Services", 150, 0, 15, 15, "4200", "6100", 0),
    ("SRV-002", "Annual Maintenance Contract", "عقد صيانة سنوي", "service", "Services", 12000, 0, 15, 15, "4200", "6100", 0),
    ("CON-001", "Printer Paper A4 (box)", "ورق طباعة A4 (كرتون)", "consumable", "Office", 0, 95, 15, 15, "4100", "6600", 0),
]


def acc(code):
    return q("SELECT id FROM accounts WHERE code = ?", (code,), one=True)["id"]


def setup_base():
    for c in CURRENCIES:
        x("INSERT INTO currencies (code, name_en, name_ar, symbol, decimals) VALUES (?,?,?,?,?)", c)
    for code, en, ar, kind, ic in ACCOUNTS:
        x("INSERT INTO accounts (code, name_en, name_ar, kind, intercompany) VALUES (?,?,?,?,?)", (code, en, ar, kind, ic))
    x("INSERT INTO company (id, name_en, name_ar, vat_no, cr_no, currency, address_en, address_ar, phone, email, "
      "fy_start_month, receivable_account_id, payable_account_id) VALUES (1,?,?,?,?,?,?,?,?,?,1,?,?)",
      ("Vertex Trading Co.", "شركة فيرتكس التجارية", "300000000000003", "1010000000", "SAR",
       "King Fahd Road, Riyadh, Saudi Arabia", "طريق الملك فهد، الرياض، المملكة العربية السعودية",
       "+966 11 000 0000", "accounts@vertex.example", acc("1100"), acc("2100")))
    for code, en, ar, cur, vat, aen, aar in BRANCHES:
        bid = x("INSERT INTO branches (code, name_en, name_ar, currency, vat_no, address_en, address_ar) "
                "VALUES (?,?,?,?,?,?,?)", (code, en, ar, cur, vat, aen, aar))
        ensure_branch_defaults(get_db(), bid)
    for en, ar, rate, scope, code in [
        ("VAT 15% (Sales)", "ضريبة 15% (مبيعات)", 15, "sale", "2200"),
        ("VAT 5% (Sales)", "ضريبة 5% (مبيعات)", 5, "sale", "2200"),
        ("Zero-rated (Sales)", "خاضع لنسبة صفر (مبيعات)", 0, "sale", "2200"),
        ("VAT 15% (Purchases)", "ضريبة 15% (مشتريات)", 15, "purchase", "1400"),
        ("VAT 5% (Purchases)", "ضريبة 5% (مشتريات)", 5, "purchase", "1400"),
    ]:
        x("INSERT INTO taxes (name_en, name_ar, rate, scope, account_id) VALUES (?,?,?,?,?)", (en, ar, rate, scope, acc(code)))
    for en, ar, cust, vend, vat in PARTNERS:
        x("INSERT INTO partners (name_en, name_ar, is_customer, is_vendor, vat_no) VALUES (?,?,?,?,?)",
          (en, ar, cust, vend, vat))
    ensure_inventory_accounts(get_db())
    ensure_module_accounts(get_db())
    ensure_costing_accounts(get_db())
    ensure_year_end_accounts(get_db())
    ensure_hr_accounts(get_db())
    ensure_fx_accounts(get_db())
    ensure_cheque_accounts(get_db())
    ensure_transit_account(get_db())
    ensure_retention_account(get_db())
    ensure_advance_account(get_db())
    x("UPDATE branches SET sale_tax_id = (SELECT id FROM taxes WHERE name_en = 'VAT 5% (Sales)'), "
      "purchase_tax_id = (SELECT id FROM taxes WHERE name_en = 'VAT 5% (Purchases)') WHERE code = 'DXB'")
    for en, ar, a1, a2, a3, method, life in [
        ("Furniture & Office Equipment", "الأثاث والمعدات المكتبية", "1600", "1610", "6700", "linear", 60),
        ("Vehicles", "السيارات", "1620", "1625", "6700", "linear", 48),
        ("Computers & IT", "أجهزة الحاسب وتقنية المعلومات", "1630", "1635", "6700", "declining", 36),
    ]:
        x("INSERT INTO asset_categories (name_en, name_ar, asset_account_id, depreciation_account_id, expense_account_id, "
          "method, life_months) VALUES (?,?,?,?,?,?,?)", (en, ar, acc(a1), acc(a2), acc(a3), method, life))
    x("DELETE FROM asset_categories WHERE name_en = 'General Equipment'")
    x("UPDATE company SET po_approval_limit = ?, require_approved_vendors = 1", (to_int(50000),))
    for code, en, ar, ptype, cat, sale, cost, rate_s, rate_p, inc, exp, minq in PRODUCTS:
        x("INSERT INTO products (code, name_en, name_ar, type, category, uom, sale_price, purchase_price, sale_tax_id, "
          "purchase_tax_id, income_account_id, expense_account_id, min_qty) VALUES (?,?,?,?,?,?,?,?,"
          "(SELECT id FROM taxes WHERE scope = 'sale' AND rate = ?), (SELECT id FROM taxes WHERE scope = 'purchase' AND rate = ?),?,?,?)",
          (code, en, ar, ptype, cat, "Unit" if ptype != "service" else "Hour", to_int(sale), to_int(cost), rate_s, rate_p,
           acc(inc), acc(exp), minq))
    ensure_categories(get_db())
    for en, ar in (("Safety", "معدات السلامة"), ("Tools", "العدد والأدوات"), ("Spare parts", "قطع الغيار"),
                   ("Services", "الخدمات"), ("Office", "مستلزمات مكتبية")):
        x("UPDATE product_categories SET name_ar = ? WHERE name_en = ?", (ar, en))
    # spare parts are carried on their own inventory account
    x("UPDATE product_categories SET inventory_account_id = ? WHERE name_en = 'Spare parts'", (acc("1205"),))
    rng = random.Random(7)
    for m in range(1, 13):
        x("INSERT INTO exchange_rates (currency, date, rate) VALUES ('AED', ?, ?)",
          (f"2026-{m:02d}-01", round(1.0211 + rng.uniform(-0.0015, 0.0015), 6)))
        x("INSERT INTO exchange_rates (currency, date, rate) VALUES ('USD', ?, 3.75)", (f"2026-{m:02d}-01",))
    x("INSERT INTO exchange_rates (currency, date, rate) VALUES ('KWD', '2026-01-01', 12.21)")
    x("INSERT INTO exchange_rates (currency, date, rate) VALUES ('BHD', '2026-01-01', 9.95)")


def setup_users():
    branches = {r["code"]: r["id"] for r in q("SELECT id, code FROM branches")}
    for username, pwd, name, role, lang, allb, codes, approver in [
        ("admin", "admin123", "System Administrator", "admin", "en", 1, [], 1),
        ("manager", "manager123", "Operations Manager (approver)", "accountant", "en", 1, [], 1),
        ("accountant", "account123", "Saudi Accountant", "accountant", "en", 0, ["RUH", "JED"], 0),
        ("dubai", "dubai123", "محاسب فرع دبي", "accountant", "ar", 0, ["DXB"], 0),
        ("viewer", "viewer123", "Group CFO (read only)", "viewer", "en", 1, [], 0),
    ]:
        uid = x("INSERT INTO users (username, password_hash, full_name, role, lang, all_branches, can_approve) "
                "VALUES (?,?,?,?,?,?,?)",
                (username, generate_password_hash(pwd, method="pbkdf2:sha256"), name, role, lang, allb, approver))
        for c in codes:
            x("INSERT INTO user_branches (user_id, branch_id) VALUES (?,?)", (uid, branches[c]))
        if username == "admin":  # the customer's system administrator (IT): manages users
            x("UPDATE users SET level = 'super' WHERE id = ?", (uid,))


def setup_workflow():
    """Approval roles, rules and standard terms for the demo company."""
    users = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
    for uname, mail in (("admin", "admin@vertex.example"), ("manager", "operations@vertex.example"),
                        ("accountant", "accounts@vertex.example"), ("dubai", "dubai@vertex.example")):
        x("UPDATE users SET email = ? WHERE username = ?", (mail, uname))
    roles = {}
    for key, en, ar, members in (("approvers", "Approvers", "المعتمدون", ["admin", "manager"]),
                                 ("procurement", "Procurement Manager", "مدير المشتريات", ["manager"]),
                                 ("finance", "Finance Manager", "المدير المالي", ["admin"]),
                                 ("gm", "General Manager", "المدير العام", ["admin"])):
        roles[key] = x("INSERT INTO approval_roles (name_en, name_ar) VALUES (?,?)", (en, ar))
        for m in members:
            x("INSERT INTO approval_role_users (role_id, user_id) VALUES (?,?)", (roles[key], users[m]))

    def rule(doc_type, name, steps, minimum=0, maximum=None, priority=10):
        rid = x("INSERT INTO approval_rules (doc_type, name, min_amount, max_amount, priority) VALUES (?,?,?,?,?)",
                (doc_type, name, to_int(minimum), to_int(maximum) if maximum is not None else None, priority))
        for n, (sname, role, mode, req) in enumerate(steps, 1):
            x("INSERT INTO approval_steps (rule_id, step_no, name, mode, required, role_id, dynamic) VALUES (?,?,?,?,?,?,?)",
              (rid, n, sname, mode, req, roles.get(role), "project_manager" if role == "pm" else None))
    rule("mr", "Material requests", [("Project manager", "pm", "any", 1),
                                     ("Department approval", "approvers", "any", 1)])
    rule("pr", "Purchase requisitions", [("Procurement approval", "approvers", "any", 1)])
    rule("po", "Purchase orders 50,000 – 200,000", [("Procurement Manager", "procurement", "any", 1)], 50000.01, 200000)
    rule("po", "Purchase orders above 200,000", [("Procurement Manager", "procurement", "any", 1),
                                                 ("Finance Manager", "finance", "any", 1)], 200000.01)
    rule("bank_change", "Vendor bank detail changes", [("Finance Manager", "finance", "any", 1)])
    rule("vendor_registration", "Supplier registrations", [("Procurement review", "procurement", "any", 1),
                                                           ("Finance check of bank details", "finance", "any", 1)])
    rule("rfq_award", "RFQ awards up to 100,000", [("Procurement Manager", "procurement", "any", 1)], 0, 100000)
    rule("rfq_award", "RFQ awards above 100,000", [("Procurement Manager", "procurement", "any", 1),
                                                   ("Finance Manager", "finance", "any", 1)], 100000.01)
    for name, doc_type, en, ar in (
            ("Standard purchase terms", "po",
             "1. Delivery: to the warehouse stated on this order, by the expected date.\n"
             "2. Payment: 30 days from the date of a correct tax invoice and delivery.\n"
             "3. Quote this PO number on every delivery note and invoice.\n"
             "4. Goods that do not meet the specification will be returned at the vendor's cost.\n"
             "5. Warranty: at least 12 months from delivery unless agreed otherwise.",
             "1. التسليم: إلى المستودع المذكور في هذا الأمر وفي التاريخ المتوقع.\n"
             "2. الدفع: خلال 30 يوماً من تاريخ الفاتورة الضريبية الصحيحة والتسليم.\n"
             "3. يذكر رقم أمر الشراء في كل مذكرة تسليم وفاتورة.\n"
             "4. تعاد البضائع غير المطابقة للمواصفات على حساب المورد.\n"
             "5. الضمان: 12 شهراً على الأقل من التسليم ما لم يتفق على غير ذلك."),
            ("Standard quotation terms", "quotation",
             "1. Prices are in the currency shown and exclude VAT unless stated.\n"
             "2. This quotation is valid for 30 days.\n3. Delivery: within 2 weeks of the purchase order.\n"
             "4. Payment: 50% advance, balance on delivery.",
             "1. الأسعار بالعملة المذكورة ولا تشمل الضريبة ما لم يذكر خلاف ذلك.\n2. هذا العرض صالح لمدة 30 يوماً.\n"
             "3. التسليم: خلال أسبوعين من أمر الشراء.\n4. الدفع: 50% مقدماً والباقي عند التسليم."),
            ("Standard RFQ terms", "rfq",
             "1. Quote unit prices excluding VAT, in the currency of the request, for the exact item or a stated alternative.\n"
             "2. State the delivery lead time, warranty and the validity of the offer (at least the days requested).\n"
             "3. Quotations received after the closing time are not considered.\n"
             "4. The company may award all, part or none of the items, and is not bound to accept the lowest offer.",
             "1. تقدم الأسعار للوحدة بدون ضريبة وبعملة الطلب، للصنف المطلوب أو بديل مذكور.\n"
             "2. يذكر مدة التوريد والضمان وصلاحية العرض (لا تقل عن المدة المطلوبة).\n"
             "3. لا ينظر في العروض الواردة بعد موعد الإغلاق.\n"
             "4. يحق للشركة الترسية على كل الأصناف أو بعضها أو عدم الترسية، ولا تلتزم بأقل الأسعار."),
            ("Payment terms", "invoice",
             "Payment due within 30 days by bank transfer to the account shown. Please quote the invoice number.",
             "يستحق الدفع خلال 30 يوماً بالتحويل البنكي إلى الحساب المذكور مع ذكر رقم الفاتورة.")):
        x("INSERT INTO terms_templates (name, doc_type, body_en, body_ar, is_default) VALUES (?,?,?,?,1)",
          (name, doc_type, en, ar))


def journal(branch_id, code):
    return q("SELECT * FROM journals WHERE branch_id = ? AND code = ?", (branch_id, code), one=True)


def partner(name):
    return q("SELECT id FROM partners WHERE name_en = ?", (name,), one=True)["id"]


def tax(name):
    return q("SELECT * FROM taxes WHERE name_en = ?", (name,), one=True)


def make_invoice(kind, branch, partner_id, d, lines, due_days=30, ref=None, currency=None, rate=1.0):
    j = journal(branch["id"], "INV" if kind.startswith("out_") else "BILL")
    computed = []
    for desc, code, qty, price, tx in lines:
        sub, tamt = compute_invoice_line(qty, to_int(price), tx["rate"] if tx else 0, 2)
        computed.append((desc, acc(code), qty, to_int(price), tx["id"] if tx else None, sub, tamt))
    untaxed = sum(c[5] for c in computed)
    taxed = sum(c[6] for c in computed)
    due = (date.fromisoformat(d) + timedelta(days=due_days)).isoformat()
    inv_id = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, ref, state, "
               "amount_untaxed, amount_tax, amount_total, created_by, created_at, currency, rate) "
               "VALUES (?,?,?,?,?,?,?,'draft',?,?,?,1,?,?,?)",
               (branch["id"], j["id"], kind, partner_id, d, due, ref, untaxed, taxed, untaxed + taxed, d + "T09:00:00",
                currency, rate))
    for i, c in enumerate(computed):
        x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, "
          "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)", (inv_id, i) + c)
    post_invoice(inv_id, 1)
    return q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)


def pay(inv, d, amount=None, journal_code="BNK", rate=None):
    customer = inv["kind"].startswith("out_")
    create_payment({
        "branch_id": inv["branch_id"], "journal_id": journal(inv["branch_id"], journal_code)["id"],
        "partner_id": inv["partner_id"], "partner_type": "customer" if customer else "vendor",
        "payment_type": "inbound" if customer else "outbound", "date": d,
        "amount": amount or inv["amount_total"], "ref": inv["number"], "invoice_id": inv["id"],
        "currency": inv["currency"], "rate": rate or inv["rate"],
    }, 1)


def entry(branch, d, lines, ref, jcode="MISC"):
    create_move(branch["id"], journal(branch["id"], jcode)["id"], d,
                [{"account_id": acc(a), "label": lbl, "debit": to_int(dr), "credit": to_int(cr)} for a, lbl, dr, cr in lines],
                user_id=1, ref=ref, post=True)


def demo_transactions():
    rng = random.Random(42)
    today = date.today()
    branches = {r["code"]: r for r in q("SELECT * FROM branches")}
    ruh, jed, dxb = branches["RUH"], branches["JED"], branches["DXB"]
    year = today.year
    start = date(year, 1, 1)

    # opening capital
    for b, amount in ((ruh, 600000), (jed, 250000), (dxb, 180000)):
        entry(b, start.isoformat(), [("1020", "Capital injection", amount, 0), ("3100", "Capital injection", 0, amount)],
              "Opening capital", "BNK")
    entry(ruh, start.isoformat(), [("1600", "Office fit-out & equipment", 240000, 0),
                                   ("1020", "Office fit-out & equipment", 0, 240000)], "Fixed assets", "BNK")

    sa_customers = [partner(n) for n in ("Al Noor Trading Est.", "Gulf Horizon Contracting", "Desert Rose Hotels",
                                         "Saudi Modern Clinics", "TechSource Systems")]
    ae_customers = [partner(n) for n in ("Emirates Retail Group", "Marina Facilities LLC")]
    vat15s, vat5s = tax("VAT 15% (Sales)"), tax("VAT 5% (Sales)")
    vat15p, vat5p = tax("VAT 15% (Purchases)"), tax("VAT 5% (Purchases)")
    products = [("Industrial supplies", "4100"), ("Safety equipment", "4100"), ("Maintenance contract", "4200"),
                ("Installation services", "4200"), ("Spare parts", "4100")]

    month = start
    while month <= today:
        m_end = (month.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)
        last = min(m_end, today)
        for b, customers, vs, vp, scale, rent_vendor in (
                (ruh, sa_customers, vat15s, vat15p, 1.0, "Al Waha Properties"),
                (jed, sa_customers, vat15s, vat15p, 0.55, "Al Waha Properties"),
                (dxb, ae_customers, vat5s, vat5p, 0.7, "Dubai Properties LLC")):
            # sales
            for _ in range(rng.randint(3, 5)):
                d = month + timedelta(days=rng.randint(0, max(0, (last - month).days)))
                lines = []
                for _ in range(rng.randint(1, 3)):
                    desc, code = rng.choice(products)
                    lines.append((desc, code, rng.randint(1, 10), round(rng.uniform(800, 6000) * scale, 2), vs))
                inv = make_invoice("out_invoice", b, rng.choice(customers), d.isoformat(), lines)
                age = (today - d).days
                roll = rng.random()
                if age > 45 and roll < 0.85 or age > 20 and roll < 0.5:
                    pd = min(today, d + timedelta(days=rng.randint(10, 40))).isoformat()
                    if roll < 0.12:
                        pay(inv, pd, amount=inv["amount_total"] // 2 // 10 * 10)
                    else:
                        pay(inv, pd)
            # cost of goods & purchases
            d = month + timedelta(days=min(5, (last - month).days))
            bill = make_invoice("in_invoice", b, partner("National Logistics Co."), d.isoformat(),
                                [("Goods purchased for resale", "5100", 1, round(rng.uniform(18000, 32000) * scale, 2), vp),
                                 ("Freight", "5100", 1, round(rng.uniform(800, 2000) * scale, 2), vp)],
                                ref=f"NL-{rng.randint(1000, 9999)}")
            if (today - d).days > 25:
                pay(bill, min(today, d + timedelta(days=28)).isoformat())
            # rent
            bill = make_invoice("in_invoice", b, partner(rent_vendor), month.isoformat(),
                                [("Office rent", "6200", 1, round(15000 * scale, 2), vp)], due_days=5)
            pay(bill, min(today, month + timedelta(days=3)).isoformat())
            # supplies
            if rng.random() < 0.6 and b is not dxb:
                bill = make_invoice("in_invoice", b, partner("Riyadh Office Supplies"), last.isoformat(),
                                    [("Stationery and printer supplies", "6600", 1, round(rng.uniform(600, 2400), 2), vp)])
            # salaries, utilities, bank charges
            if last == m_end:
                util = round(rng.uniform(1500, 3500) * scale, 2)
                entry(b, last.isoformat(), [("6300", "Electricity & water", util, 0), ("1020", "Electricity & water", 0, util)],
                      f"Utilities {month:%Y-%m}", "BNK")
                entry(b, last.isoformat(), [("6500", "Bank charges", 85, 0), ("1020", "Bank charges", 0, 85)],
                      f"Bank charges {month:%Y-%m}", "BNK")
        if last == m_end:
            entry(ruh, last.isoformat(), [("6700", "Monthly depreciation", 4000, 0),
                                          ("1610", "Monthly depreciation", 0, 4000)], f"Depreciation {month:%Y-%m}")
        month = m_end + timedelta(days=1)

    # a customer credit note
    cn = make_invoice("out_refund", ruh, sa_customers[0], date(year, 3, 18).isoformat(),
                      [("Returned safety equipment", "4100", 2, 1250, vat15s)])
    # inter-branch funding
    for d, frm, to, amt in ((date(year, 2, 10), ruh, jed, 80000), (date(year, 4, 15), ruh, dxb, 60000),
                            (date(year, 8, 3), jed, ruh, 25000)):
        if d > today:
            continue
        amount_from = to_int(amt)
        rf, rt = rate_on(frm["currency"], d.isoformat()), rate_on(to["currency"], d.isoformat())
        amount_to = translate(amount_from, rf / rt) // 10 * 10
        create_transfer({"date": d.isoformat(), "ref": "Branch funding",
                         "from_branch": frm["id"], "from_journal": journal(frm["id"], "BNK")["id"],
                         "to_branch": to["id"], "to_journal": journal(to["id"], "BNK")["id"],
                         "amount_from": amount_from, "amount_to": amount_to}, 1)
    # a draft entry waiting for review
    create_move(jed["id"], journal(jed["id"], "MISC")["id"], today.isoformat(),
                [{"account_id": acc("6400"), "label": "Marketing campaign accrual", "debit": to_int(12000), "credit": 0},
                 {"account_id": acc("2300"), "label": "Marketing campaign accrual", "debit": 0, "credit": to_int(12000)}],
                user_id=1, ref="Accrual — review", post=False)
    return cn


def demo_modules():
    """Procurement, inventory, sales orders, vendor management, assets and prepaid demo data."""
    from .procurement import (award_rfq, issue_material_request, rfq_from_pr, save_doc, save_quote, transition,
                              requisition_from_mr)
    from .schedules import confirm_asset, confirm_schedule, dispose_asset, post_depreciation, post_recognitions
    from .stock import adjust_stock, confirm_order, create_order_invoice, validate_picking
    today = date.today()
    year = today.year
    branches = {r["code"]: r for r in q("SELECT * FROM branches")}
    ruh, jed, dxb = branches["RUH"], branches["JED"], branches["DXB"]
    wh = {code: q("SELECT id FROM warehouses WHERE branch_id = ?", (b["id"],), one=True)["id"] for code, b in branches.items()}
    prod = {r["code"]: r for r in q("SELECT * FROM products")}
    vat15p, vat5p = tax("VAT 15% (Purchases)"), tax("VAT 5% (Purchases)")
    d = lambda m, day: min(date(year, m, day), today).isoformat()  # noqa: E731

    # vendor management
    x("UPDATE partners SET vendor_category = 'Logistics', contact_person = 'Faisal Al-Harbi', payment_terms = 30 "
      "WHERE name_en = 'National Logistics Co.'")
    x("UPDATE partners SET vendor_category = 'Industrial supplies', contact_person = 'Omar Saleh' WHERE name_en = 'TechSource Systems'")
    x("UPDATE partners SET vendor_category = 'Office supplies' WHERE name_en = 'Riyadh Office Supplies'")
    x("UPDATE partners SET vendor_category = 'Real estate' WHERE name_en IN ('Al Waha Properties', 'Dubai Properties LLC')")
    gulf = x("INSERT INTO partners (name_en, name_ar, is_vendor, vat_no, vendor_status, vendor_category, contact_person, "
             "email, phone) VALUES (?,?,1,?,?,?,?,?,?)",
             ("Gulf Safety Equipment Co.", "شركة الخليج لمعدات السلامة", "310000000000031", "approved", "Industrial supplies",
              "Ahmed Nasser", "sales@gulfsafety.example", "+966 12 555 0101"))
    arabian = x("INSERT INTO partners (name_en, name_ar, is_vendor, vat_no, vendor_status, vendor_category, contact_person) "
                "VALUES (?,?,1,?,?,?,?)", ("Arabian Tools Trading", "العربية لتجارة العدد", "310000000000032",
                                           "approved", "Industrial supplies", "Khalid Rahman"))
    x("INSERT INTO partners (name_en, name_ar, is_vendor, vat_no, vendor_status, vendor_category) VALUES (?,?,1,?,?,?)",
      ("Eastern Industrial Supply", "الشرقية للتوريدات الصناعية", "310000000000033", "prospect", "Industrial supplies"))
    for pid, doc, num, exp in [(gulf, "Commercial Registration", "4030111111", (today + timedelta(days=20)).isoformat()),
                               (gulf, "VAT Certificate", "310000000000031", None),
                               (arabian, "Commercial Registration", "1010222222", (today + timedelta(days=400)).isoformat()),
                               (arabian, "ISO Certificate", "ISO-9001-7781", (today - timedelta(days=10)).isoformat())]:
        x("INSERT INTO vendor_documents (partner_id, doc_type, number, issue_date, expiry_date, uploaded_at) VALUES (?,?,?,?,?,?)",
          (pid, doc, num, f"{year - 1}-01-15", exp, today.isoformat()))
    for pid, scores, period in [(gulf, (5, 4, 4, 5), "H1"), (arabian, (4, 3, 5, 4), "H1"),
                                (partner("National Logistics Co."), (4, 4, 3, 4), "H1")]:
        x("INSERT INTO vendor_evaluations (partner_id, date, period, quality, delivery, price, service, comments, evaluator_id) "
          "VALUES (?,?,?,?,?,?,?,?,1)", (pid, d(7, 1), f"{period} {year}", *scores, "Half-year review"))

    # procurement: MR -> PR -> RFQ -> PO -> receipt -> bill
    mr = save_doc("mr", None, {"branch_id": ruh["id"], "warehouse_id": wh["RUH"], "department": "Operations",
                               "date": d(2, 2), "required_date": d(2, 15), "purpose": "Stock for new safety contracts"},
                  [{"product_id": prod["SAF-001"]["id"], "description": "Safety Helmet", "quantity": 200},
                   {"product_id": prod["SAF-002"]["id"], "description": "Safety Gloves", "quantity": 500},
                   {"product_id": prod["SAF-003"]["id"], "description": "High-visibility Vest", "quantity": 250}], 1)
    transition("mr", mr, "submit", 3)
    transition("mr", mr, "approve", 2, approver=True)
    pr = requisition_from_mr(mr, d(2, 3), 1)
    transition("pr", pr, "submit", 3)
    transition("pr", pr, "approve", 2, approver=True)
    rfq = rfq_from_pr(pr, d(2, 4), d(2, 9), [gulf, arabian, partner("TechSource Systems")], 1)
    vendors = q("SELECT * FROM rfq_vendors WHERE rfq_id = ? ORDER BY id", (rfq,))
    lines = q("SELECT * FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (rfq,))
    for rv, prices, lead in [(vendors[0], (40, 10.5, 18), 5), (vendors[1], (43, 10, 19.5), 7)]:
        save_quote(rv["id"], {ln["id"]: to_int(pr_) for ln, pr_ in zip(lines, prices)},
                   {ln["id"]: vat15p["id"] for ln in lines}, f"Q-{rv['id']:04d}", d(2, 7), lead, None, user_id=1,
                   late_ok=True)  # history: entered as it happened
    save_quote(vendors[2]["id"], {}, {}, declined=True, user_id=1, late_ok=True)
    po = award_rfq(rfq, vendors[0]["id"], wh["RUH"], d(2, 10), 1)
    confirm_order(po, 1)
    pk = q("SELECT id FROM pickings WHERE order_id = ? AND state = 'ready'", (po,), one=True)["id"]
    validate_picking(pk, d=d(2, 14), user_id=1)
    inv = create_order_invoice(po, d(2, 15), 1)
    x("UPDATE invoices SET ref = 'GSE-55120' WHERE id = ?", (inv,))
    post_invoice(inv, 1)

    # tools and spare parts bought directly for each branch
    for b, code, vend, items, vp in [
            (ruh, "RUH", arabian, [("IND-010", 20), ("IND-011", 25), ("SPR-100", 40)], vat15p),
            (jed, "JED", arabian, [("IND-010", 10), ("IND-011", 10), ("SAF-001", 80), ("SAF-002", 150)], vat15p),
            (dxb, "DXB", gulf, [("SAF-001", 120), ("SAF-003", 100), ("SPR-100", 30)], vat5p)]:
        number = q("SELECT COUNT(*) FROM orders", one=True)[0] + 1
        oid = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, expected_date, state, "
                "created_by, created_at) VALUES ('purchase', ?, ?, ?, ?, ?, ?, 'draft', 1, ?)",
                (f"PO/{code}/{year}/{number:04d}", b["id"], wh[code], vend, d(3, 1), d(3, 8), d(3, 1)))
        unt = tx_total = 0
        for i, (pc, qty) in enumerate(items):
            p = prod[pc]
            price = p["purchase_price"] if code != "DXB" else int(p["purchase_price"] * 0.98)
            sub, tx = compute_invoice_line(qty, price, vp["rate"], 2)
            unt += sub
            tx_total += tx
            x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, "
              "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)", (oid, i, p["id"], p["name_en"], qty, price, vp["id"], sub, tx))
        x("UPDATE orders SET amount_untaxed = ?, amount_tax = ?, amount_total = ? WHERE id = ?", (unt, tx_total, unt + tx_total, oid))
        confirm_order(oid, 1)
        pk = q("SELECT id FROM pickings WHERE order_id = ? AND state = 'ready'", (oid,), one=True)["id"]
        validate_picking(pk, d=d(3, 9 if code != "JED" else 12), user_id=1)
        bill = create_order_invoice(oid, d(3, 12), 1)
        post_invoice(bill, 1)
        pay(q("SELECT * FROM invoices WHERE id = ?", (bill,), one=True), d(4, 5))

    # sales orders delivered from stock
    sa = [partner(n) for n in ("Gulf Horizon Contracting", "Al Noor Trading Est.", "Saudi Modern Clinics")]
    for i, (b, code, cust, items) in enumerate([
            (ruh, "RUH", sa[0], [("SAF-001", 60), ("SAF-002", 150), ("SRV-001", 16)]),
            (ruh, "RUH", sa[1], [("IND-010", 6), ("IND-011", 8), ("SPR-100", 12)]),
            (jed, "JED", sa[2], [("SAF-001", 40), ("IND-010", 4)]),
            (dxb, "DXB", partner("Emirates Retail Group"), [("SAF-001", 70), ("SAF-003", 60)]),
            (ruh, "RUH", sa[0], [("SAF-003", 120), ("SAF-001", 50)])]):
        number = q("SELECT COUNT(*) FROM orders WHERE kind = 'sale'", one=True)[0] + 1
        sdate = d(4 + i, 3)
        oid = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, expected_date, state, "
                "created_by, created_at) VALUES ('sale', ?, ?, ?, ?, ?, ?, 'draft', 1, ?)",
                (f"SO/{code}/{year}/{number:04d}", b["id"], wh[code], cust, sdate, sdate, sdate))
        vs = tax("VAT 5% (Sales)") if code == "DXB" else tax("VAT 15% (Sales)")
        unt = tx_total = 0
        for j, (pc, qty) in enumerate(items):
            p = prod[pc]
            sub, tx = compute_invoice_line(qty, p["sale_price"], vs["rate"], 2)
            unt += sub
            tx_total += tx
            x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, "
              "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)", (oid, j, p["id"], p["name_en"], qty, p["sale_price"],
                                                                   vs["id"], sub, tx))
        x("UPDATE orders SET amount_untaxed = ?, amount_tax = ?, amount_total = ? WHERE id = ?", (unt, tx_total, unt + tx_total, oid))
        confirm_order(oid, 1)
        if i < 4:
            pk = q("SELECT id FROM pickings WHERE order_id = ? AND state = 'ready'", (oid,), one=True)
            if pk:
                validate_picking(pk["id"], d=d(4 + i, 6), user_id=1)
            inv = create_order_invoice(oid, d(4 + i, 7), 1)
            post_invoice(inv, 1)
            if i < 2:
                pay(q("SELECT * FROM invoices WHERE id = ?", (inv,), one=True), d(4 + i, 28))
    # one quotation still open and a material issue for internal use
    mr2 = save_doc("mr", None, {"branch_id": jed["id"], "warehouse_id": wh["JED"], "department": "Maintenance",
                                "date": d(9, 2), "required_date": d(9, 5), "purpose": "Workshop consumption"},
                   [{"product_id": prod["SAF-002"]["id"], "description": "Gloves for workshop", "quantity": 20}], 1)
    transition("mr", mr2, "submit", 3)
    transition("mr", mr2, "approve", 2, approver=True)
    issue_material_request(mr2, d(9, 3), 1)
    mr3 = save_doc("mr", None, {"branch_id": ruh["id"], "warehouse_id": wh["RUH"], "department": "Projects",
                                "date": today.isoformat(), "required_date": (today + timedelta(days=10)).isoformat(),
                                "purpose": "Site kit for new project"},
                   [{"product_id": prod["IND-010"]["id"], "description": "Drills", "quantity": 15}], 3)
    transition("mr", mr3, "submit", 3)
    adjust_stock(ruh["id"], wh["RUH"], d(6, 30), [(prod["SAF-002"]["id"],
                 q("SELECT qty FROM stock_quants WHERE product_id = ? AND warehouse_id = ?",
                   (prod["SAF-002"]["id"], wh["RUH"]), one=True)["qty"] - 5)], "Half-year stock count", 1)

    # fixed assets
    cats = {r["name_en"]: r["id"] for r in q("SELECT * FROM asset_categories")}
    for code, en, ar, cat, b, acq, cost, salvage, credit in [
            ("AST-00001", "Office Furniture — Riyadh HQ", "أثاث مكتب الرياض", "Furniture & Office Equipment", ruh, f"{year}-01-01", 240000, 0, None),
            ("AST-00002", "Toyota Hilux Delivery Truck", "شاحنة توصيل تويوتا هايلكس", "Vehicles", jed, f"{year}-02-01", 128000, 20000, "1020"),
            ("AST-00003", "Laptops (12 units)", "أجهزة حاسب محمولة (12)", "Computers & IT", dxb, f"{year}-03-15", 54000, 0, "1020"),
            ("AST-00004", "Forklift", "رافعة شوكية", "Vehicles", ruh, f"{year}-01-10", 85000, 5000, "1020")]:
        c = q("SELECT * FROM asset_categories WHERE id = ?", (cats[cat],), one=True)
        aid = x("INSERT INTO assets (code, name_en, name_ar, category_id, branch_id, acquisition_date, start_date, cost, salvage, "
                "method, life_months, rate, state, created_by, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,0,'draft',1,?)",
                (code, en, ar, c["id"], b["id"], acq, acq, to_int(cost), to_int(salvage), c["method"], c["life_months"], acq))
        confirm_asset(aid, 1, acc(credit) if credit else None)
    # the opening furniture was booked in 1600 already; its old 4000/month entries are replaced by the asset schedule
    x("UPDATE moves SET state = 'cancelled' WHERE ref LIKE 'Depreciation %'")
    from .views.closing import last_month_end
    post_depreciation(last_month_end(), [b["id"] for b in branches.values()], 1)
    forklift = q("SELECT id FROM assets WHERE code = 'AST-00004'", one=True)["id"]
    dispose_asset(forklift, d(8, 31), to_int(60000), acc("1020"), 1)

    # prepaid rent, insurance, deferred maintenance revenue and a monthly bonus accrual
    for kind, name, b, bal, pl, total, start, months, rev in [
            ("prepaid", "Annual insurance policy", ruh, "1300", "6850", 36000, f"{year}-01-01", 12, 0),
            ("prepaid", "Warehouse rent (paid yearly)", jed, "1300", "6200", 96000, f"{year}-04-01", 12, 0),
            ("deferred", "Annual maintenance contract — Desert Rose Hotels", ruh, "2600", "4200", 12000, f"{year}-03-01", 12, 0),
            ("accrual", "Staff bonus provision", ruh, "2300", "6100", 60000, f"{year}-01-01", 12, 0),
            ("accrual", "Utilities not yet billed", dxb, "2300", "6300", 1800, f"{year}-06-01", 6, 1)]:
        prefix = {"prepaid": "PPD", "deferred": "DEF", "accrual": "ACR"}[kind]
        n = q("SELECT COUNT(*) FROM prepaid_schedules", one=True)[0] + 1
        sid = x("INSERT INTO prepaid_schedules (number, kind, name, branch_id, balance_account_id, pl_account_id, total, "
                "start_date, months, auto_reverse, state, created_by, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,'draft',1,?)",
                (f"{prefix}/{b['code']}/{year}/{n:04d}", kind, name, b["id"], acc(bal), acc(pl), to_int(total), start, months,
                 rev, start))
        confirm_schedule(sid, 1)
    # the prepaid / deferred amounts were paid or received up front
    entry(ruh, f"{year}-01-01", [("1300", "Insurance paid", 36000, 0), ("1020", "Insurance paid", 0, 36000)], "Insurance", "BNK")
    entry(jed, f"{year}-04-01", [("1300", "Rent paid", 96000, 0), ("1020", "Rent paid", 0, 96000)], "Rent", "BNK")
    entry(ruh, f"{year}-03-01", [("1020", "AMC received", 12000, 0), ("2600", "AMC received", 0, 12000)], "AMC", "BNK")
    post_recognitions(last_month_end(), [b["id"] for b in branches.values()], 1)


def demo_crm_pos_bank():
    from .bankrec import auto_match, create_statement, add_line
    from .pos import close_session, create_order, open_session, refund_order
    from .stock import confirm_order
    today = date.today()
    branches = {r["code"]: r for r in q("SELECT * FROM branches")}
    ruh, jed, dxb = branches["RUH"], branches["JED"], branches["DXB"]
    prod = {r["code"]: r for r in q("SELECT * FROM products")}
    users = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
    ago = lambda n: (today - timedelta(days=n)).isoformat()  # noqa: E731

    # CRM pipeline
    leads = [
        ("Safety kit for new metro site", "Riyadh Metro Contractors JV", "Saad Al-Qahtani", "Exhibition", "negotiation", 185000, 25, 2, ruh),
        ("Annual PPE supply 2027", "Al Noor Trading Est.", "Mona Hassan", "Existing customer", "proposal", 96000, 40, 1, ruh),
        ("Workshop tools upgrade", "Red Sea Shipyard", "Yousef Bakr", "Website", "qualified", 54000, 60, 0, jed),
        ("Hotel maintenance contract", "Desert Rose Hotels", "Lina Farouk", "Referral", "proposal", 36000, 20, 1, ruh),
        ("Warehouse safety audit kit", "Gulf Cold Stores", "Hamad Al-Mutairi", "Phone", "new", 18000, 30, 0, ruh),
        ("Hi-vis vests for logistics fleet", "Fast Lane Logistics", "Ravi Menon", "Email campaign", "new", 22000, 45, 0, dxb),
        ("Retail store fit-out tools", "Emirates Retail Group", "Sara Al-Marzouqi", "Existing customer", "qualified", 41000, 50, 1, dxb),
        ("Clinic equipment spares", "Saudi Modern Clinics", "Dr. Nabil Saeed", "Existing customer", "negotiation", 28000, 15, 0, jed),
        ("Stadium renovation PPE", "Al Khobar Sports Co.", "Turki Al-Shehri", "Walk-in", "lost", 120000, 0, 0, ruh),
        ("Port expansion tools", "Jeddah Port Services", "Adel Omar", "Website", "lost", 64000, 0, 0, jed),
    ]
    owner_cycle = [users["admin"], users["manager"], users["accountant"]]
    for i, (title, company, contact, source, stage, rev, due_in, prio, b) in enumerate(leads):
        from .views.crm import STAGE_PROB
        lid = x("INSERT INTO leads (number, title, company_name, contact_name, email, phone, source, stage, expected_revenue, "
                "probability, expected_close, priority, owner_id, branch_id, created_at, closed_at, lost_reason) "
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                (f"LEAD/{b['code']}/{today.year}/{i + 1:04d}", title, company, contact,
                 contact.split()[0].lower().replace(".", "") + "@example.com", f"+966 5{i}0 000 00{i}", source, stage,
                 to_int(rev), STAGE_PROB[stage], (today + timedelta(days=due_in * 2)).isoformat(), prio,
                 owner_cycle[i % 3], b["id"], ago(60 - i * 4) + "T10:00:00",
                 ago(10) + "T10:00:00" if stage == "lost" else None,
                 {8: "Price too high", 9: "Chose a competitor with local stock"}.get(i)))
        for kind, summary, due in [("call", "Follow-up call", today + timedelta(days=due_in - 3)),
                                   ("meeting", "Site visit and demo", today + timedelta(days=due_in))][: 1 if i % 2 else 2]:
            if stage not in ("won", "lost"):
                x("INSERT INTO crm_activities (lead_id, kind, summary, due_date, user_id, created_at) VALUES (?,?,?,?,?,?)",
                  (lid, kind, summary, due.isoformat(), owner_cycle[i % 3], now()))
        x("INSERT INTO crm_activities (lead_id, kind, summary, due_date, done, done_at, user_id, created_at) "
          "VALUES (?, 'email', 'Sent company profile and catalogue', ?, 1, ?, ?, ?)", (lid, ago(20), now(), owner_cycle[i % 3], now()))
    # one opportunity won through a quotation
    won = x("INSERT INTO leads (number, title, company_name, contact_name, source, stage, expected_revenue, probability, "
            "priority, owner_id, branch_id, partner_id, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
            (f"LEAD/RUH/{today.year}/0011", "Drills for maintenance team", "Gulf Horizon Contracting", "Faisal",
             "Existing customer", "proposal", to_int(4800), 50, 1, users["manager"], ruh["id"],
             partner("Gulf Horizon Contracting"), ago(30) + "T09:00:00"))
    wh_ruh = q("SELECT id FROM warehouses WHERE branch_id = ?", (ruh["id"],), one=True)["id"]
    vs = tax("VAT 15% (Sales)")
    sub, tx = compute_invoice_line(6, prod["IND-010"]["sale_price"], 15, 2)
    oid = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, state, amount_untaxed, amount_tax, "
            "amount_total, lead_id, created_by, created_at) VALUES ('sale', ?, ?, ?, ?, ?, 'draft', ?, ?, ?, ?, 1, ?)",
            (f"SO/RUH/{today.year}/0101", ruh["id"], wh_ruh, partner("Gulf Horizon Contracting"), ago(5), sub, tx, sub + tx,
             won, ago(5)))
    x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, subtotal, tax_amount) "
      "VALUES (?, 0, ?, ?, 6, ?, ?, ?, ?)", (oid, prod["IND-010"]["id"], prod["IND-010"]["name_en"], prod["IND-010"]["sale_price"],
                                             vs["id"], sub, tx))
    confirm_order(oid, users["manager"])

    # POS: a register in Riyadh and Dubai; a closed session and an open one
    walk_in = x("INSERT INTO partners (name_en, name_ar, is_customer) VALUES ('Walk-in Customer', 'عميل نقدي', 1)")
    regs = {}
    for b, name in ((ruh, "Riyadh Showroom"), (dxb, "Dubai Counter")):
        cash_j = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'CSH'", (b["id"],), one=True)["id"]
        bank_j = q("SELECT id FROM journals WHERE branch_id = ? AND code = 'BNK'", (b["id"],), one=True)["id"]
        wh = q("SELECT id FROM warehouses WHERE branch_id = ?", (b["id"],), one=True)["id"]
        regs[b["code"]] = x("INSERT INTO pos_registers (name, branch_id, warehouse_id, cash_journal_id, card_journal_id, partner_id) "
                            "VALUES (?,?,?,?,?,?)", (name, b["id"], wh, cash_j, bank_j, walk_in))
    rng = random.Random(11)
    sid = open_session(regs["RUH"], to_int(500), users["accountant"])
    first = None
    for i in range(14):
        items = [{"product_id": prod[c]["id"], "qty": rng.randint(1, 3), "discount": 10 if i == 3 else 0}
                 for c in rng.sample(["SAF-001", "SAF-002", "SAF-003", "SRV-001"], rng.randint(1, 3))]
        card = i % 3 == 0
        o = create_order(sid, items, None, 10 ** 12 if card else 0, None, users["accountant"])
        first = first or o
    refund_order(first, users["accountant"])
    expected = q("SELECT opening_cash + (SELECT COALESCE(SUM(paid_cash), 0) FROM pos_orders WHERE session_id = ?) "
                 "FROM pos_sessions WHERE id = ?", (sid, sid), one=True)[0]
    close_session(sid, expected - to_int(5), users["accountant"])
    x("UPDATE pos_sessions SET opened_at = ? WHERE id = ?", (ago(1) + "T09:00:00", sid))
    sid2 = open_session(regs["DXB"], to_int(300), users["dubai"])
    for items in ([{"product_id": prod["SAF-001"]["id"], "qty": 2}], [{"product_id": prod["SAF-003"]["id"], "qty": 3}]):
        create_order(sid2, items, None, 0, None, users["dubai"])

    # bank statement for Riyadh's bank account this month
    bnk = q("SELECT * FROM journals WHERE branch_id = ? AND code = 'BNK'", (ruh["id"],), one=True)
    month_start = today.replace(day=1).isoformat()
    start_bal = q("SELECT COALESCE(SUM(ml.debit - ml.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 = ? AND m.date < ?",
                  (bnk["default_account_id"], ruh["id"], month_start), one=True)[0]
    items = q("SELECT ml.*, m.date, m.ref, m.name 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 = ? AND m.date >= ? ORDER BY m.date, ml.id",
              (bnk["default_account_id"], ruh["id"], month_start))
    st = create_statement(bnk["id"], today.isoformat(), f"{today:%B %Y}", start_bal, start_bal, 1)
    total = 0
    for k, it in enumerate(items):
        if k == len(items) - 1 and len(items) > 2:
            continue  # last payment not yet cleared by the bank
        amt = it["debit"] - it["credit"]
        add_line(st, min(today, date.fromisoformat(it["date"]) + timedelta(days=1)).isoformat(), amt,
                 it["label"] or it["ref"] or "", it["ref"] or "")
        total += amt
    add_line(st, today.isoformat(), -to_int(57.5), "Bank charges — international transfer", "CHG-2291")
    total -= to_int(57.5)
    open_inv = q("SELECT i.* FROM invoices i WHERE i.branch_id = ? AND i.kind = 'out_invoice' AND i.state = 'posted' "
                 "ORDER BY i.date LIMIT 40", (ruh["id"],))
    from .ledger import invoice_residuals
    res = invoice_residuals([i["id"] for i in open_inv])
    target = next((i for i in open_inv if res.get(i["id"]) == i["amount_total"]), None)
    if target:
        add_line(st, today.isoformat(), target["amount_total"], "Incoming transfer", target["number"],
                 q("SELECT name_en FROM partners WHERE id = ?", (target["partner_id"],), one=True)[0])
        total += target["amount_total"]
    x("UPDATE bank_statements SET balance_end = ? WHERE id = ?", (start_bal + total, st))
    auto_match(st, 1)


EMPLOYEES = [
    # code, English, Arabic, branch, nationality, national, job, department, joined, basic, housing, transport, other
    ("E001", "Khalid Al-Otaibi", "خالد العتيبي", "RUH", "Saudi", 1, "General Manager", "Management", "2016-03-01", 12000, 3000, 1000, 0),
    ("E002", "Rajesh Kumar", "راجيش كومار", "RUH", "Indian", 0, "Chief Accountant", "Finance", "2019-07-15", 8000, 2000, 600, 0),
    ("E003", "Noura Al-Qahtani", "نورة القحطاني", "RUH", "Saudi", 1, "HR Officer", "Administration", "2023-02-01", 6000, 1500, 500, 0),
    ("E004", "Mohammed Farouk", "محمد فاروق", "RUH", "Egyptian", 0, "Sales Engineer", "Sales", "2021-09-01", 6500, 1625, 500, 375),
    ("E005", "Ali Hassan", "علي حسن", "RUH", "Pakistani", 0, "Storekeeper", "Warehouse", "2025-04-01", 3200, 800, 400, 0),
    ("E006", "Faisal Al-Ghamdi", "فيصل الغامدي", "JED", "Saudi", 1, "Branch Manager", "Management", "2020-01-12", 9000, 2250, 750, 0),
    ("E007", "John Mathew", "جون ماثيو", "JED", "Indian", 0, "Accountant", "Finance", "2022-05-01", 5000, 1250, 500, 0),
    ("E008", "Abdul Rahman", "عبد الرحمن", "JED", "Bangladeshi", 0, "Driver", "Logistics", "2018-11-01", 2600, 650, 300, 0),
    ("E009", "Grace Santos", "جريس سانتوس", "JED", "Filipino", 0, "Sales Coordinator", "Sales", "2024-08-01", 3200, 800, 300, 0),
    ("E010", "Omar Al Mansoori", "عمر المنصوري", "DXB", "Emirati", 1, "Branch Manager", "Management", "2019-04-01", 15000, 5000, 1000, 0),
    ("E011", "Priya Nair", "بريا ناير", "DXB", "Indian", 0, "Office Manager", "Administration", "2021-01-10", 5000, 2500, 500, 0),
    ("E012", "Ahmed Khan", "أحمد خان", "DXB", "Pakistani", 0, "Sales Executive", "Sales", "2025-02-01", 3000, 1200, 400, 0),
]


def demo_hr():
    """Employees with opening end-of-service and leave provisions, leave, loans and a paid payroll every month."""
    from .payroll import calculate_run, create_run, disburse_loan, eosb, leave_balance, leave_daily, pay, post_run
    from .money import round_cur
    today = date.today()
    start = date(today.year, 1, 1)
    branches = {r["code"]: r for r in q("SELECT * FROM branches")}
    banks = ("Al Rajhi Bank", "Saudi National Bank", "Riyad Bank", "Emirates NBD")
    for i, (code, en, ar, bc, nat, national, job, dept, joined, basic, housing, transport, other) in enumerate(EMPLOYEES):
        b = branches[bc]
        uae = b["currency"] == "AED"
        x("INSERT INTO employees (code, name_en, name_ar, branch_id, law, is_national, nationality, job_title, department, "
          "join_date, basic, housing, transport, other_allowance, id_number, id_expiry, passport_no, passport_expiry, "
          "bank_name, iban, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
          (code, en, ar, b["id"], "uae" if uae else "ksa", national, nat, job, dept, joined,
           to_int(basic), to_int(housing), to_int(transport), to_int(other),
           f"{'784' if uae else ('1' if national else '2')}{4172000 + i * 7919:09d}",
           (today + timedelta(days={"E005": 20, "E002": -5}.get(code, 200 + 37 * i))).isoformat(),
           None if national else f"P{8800000 + i * 131}",
           None if national else (today + timedelta(days=45 if code == "E009" else 500 + 41 * i)).isoformat(),
           banks[3] if uae else banks[i % 3],
           (f"AE{44 + i:02d}026{1000000000000000 + i * 104729:016d}" if uae else f"SA{44 + i:02d}80{1000000000000000 + i * 104729:018d}"),
           now()))
    # opening provisions brought in at the start of the year: Dr Retained Earnings / Cr the provisions
    opening = start - timedelta(days=1)
    st = q("SELECT * FROM hr_settings WHERE id = 1", one=True)
    for b in branches.values():
        dec = q("SELECT decimals FROM currencies WHERE code = ?", (b["currency"],), one=True)[0]
        total_e = total_l = 0
        for e in q("SELECT * FROM employees WHERE branch_id = ?", (b["id"],)):
            ev = round_cur(int(eosb(e, opening, "termination", st)[2]), dec)
            unused = float(4 + (e["id"] * 7) % 19)  # unused leave days brought forward
            lv = round_cur(int(unused * leave_daily(e)), dec)
            x("UPDATE employees SET eosb_booked = ?, leave_booked = ?, leave_opening = ? WHERE id = ?",
              (ev, lv, round(unused - leave_balance(e, opening), 2), e["id"]))
            total_e += ev
            total_l += lv
        if total_e or total_l:
            create_move(b["id"], journal(b["id"], "MISC")["id"], start.isoformat(),
                        [{"account_id": acc("3300"), "label": "Opening HR provisions", "debit": total_e + total_l, "credit": 0},
                         {"account_id": acc("2500"), "label": "Opening end-of-service provision", "debit": 0, "credit": total_e},
                         {"account_id": acc("2420"), "label": "Opening vacation provision", "debit": 0, "credit": total_l}],
                        user_id=1, ref="HR opening balances", post=True)
    emp = {r["code"]: r for r in q("SELECT * FROM employees")}
    # leave taken this year, a loan repaid and one still running
    for code, kind, d1, d2 in (("E002", "annual", f"{today.year}-08-03", f"{today.year}-08-16"),
                               ("E006", "annual", f"{today.year}-06-01", f"{today.year}-06-21"),
                               ("E011", "annual", f"{today.year}-07-06", f"{today.year}-07-25"),
                               ("E005", "unpaid", f"{today.year}-09-14", f"{today.year}-09-15"),
                               ("E008", "sick", f"{today.year}-05-11", f"{today.year}-05-13")):
        if d1 < today.isoformat():
            days = (date.fromisoformat(d2) - date.fromisoformat(d1)).days + 1
            x("INSERT INTO leave_requests (number, employee_id, kind, date_from, date_to, days, state, approved_by, "
              "created_by, created_at) VALUES (?,?,?,?,?,?, 'approved', 1, 1, ?)",
              (next_number("leave_requests", "number", "LV", emp[code]["branch_id"], d1), emp[code]["id"], kind, d1, d2,
               days, now()))
    nxt = (today.replace(day=1) + timedelta(days=40)).replace(day=1)
    x("INSERT INTO leave_requests (number, employee_id, kind, date_from, date_to, days, state, created_by, created_at) "
      "VALUES (?,?, 'annual', ?, ?, 10, 'draft', 1, ?)",
      (next_number("leave_requests", "number", "LV", emp["E004"]["branch_id"], nxt.isoformat()), emp["E004"]["id"],
       nxt.isoformat(), (nxt + timedelta(days=9)).isoformat(), now()))
    for code, d, amount, inst in (("E004", f"{today.year}-03-01", 6000, 1000), ("E007", None, 5000, 500)):
        d = d or (today.replace(day=1) - timedelta(days=50)).replace(day=1).isoformat()
        if d >= today.isoformat():
            continue
        start_p = (date.fromisoformat(d) + timedelta(days=32)).isoformat()[:7]
        lid = x("INSERT INTO employee_loans (number, employee_id, branch_id, date, amount, installment, start_period, "
                "state, created_by, created_at) VALUES (?,?,?,?,?,?,?, 'draft', 1, ?)",
                (next_number("employee_loans", "number", "LOAN", emp[code]["branch_id"], d), emp[code]["id"],
                 emp[code]["branch_id"], d, to_int(amount), to_int(inst), start_p, now()))
        disburse_loan(lid, journal(emp[code]["branch_id"], "BNK")["id"], 1)
    # a payroll for every finished month, posted and paid on the last day
    month = start
    while True:
        m_end = (month.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)
        if m_end >= today:
            break
        for b in branches.values():
            rid = create_run(b["id"], month.isoformat()[:7], 1)
            if m_end.month == today.month - 1 or (today.month == 1 and m_end.month == 12):
                x("UPDATE payroll_lines SET ot_hours = 12 WHERE run_id = ? AND employee_id IN "
                  "(SELECT id FROM employees WHERE code IN ('E005', 'E008', 'E012'))", (rid,))
                x("UPDATE payroll_lines SET additions = ?, note = 'Sales bonus' WHERE run_id = ? AND employee_id IN "
                  "(SELECT id FROM employees WHERE code IN ('E004', 'E009'))", (to_int(1500), rid))
                calculate_run(rid)
            post_run(rid, 1)
            pay("payroll_runs", rid, journal(b["id"], "BNK")["id"], m_end.isoformat(), 1)
        month = m_end + timedelta(days=1)


# EUR against SAR on the 1st of each month (the euro strengthens through the year)
EUR_RATES = [4.02, 4.05, 4.09, 4.06, 4.11, 4.15, 4.18, 4.14, 4.20, 4.24, 4.27, 4.22]


def demo_fx():
    """A German vendor billing in EUR and a US customer invoiced in USD, some paid at a later rate, and the
    revaluation of what is open at the end of last month."""
    from .fx import post_revaluation
    from .ledger import cross_rate
    today = date.today()
    if not q("SELECT 1 FROM currencies WHERE code = 'EUR'", one=True):
        x("INSERT INTO currencies (code, name_en, name_ar, symbol, decimals) VALUES ('EUR', 'Euro', 'يورو', '€', 2)")
    for m, r in enumerate(EUR_RATES, 1):
        x("INSERT OR IGNORE INTO exchange_rates (currency, date, rate) VALUES ('EUR', ?, ?)", (f"{today.year}-{m:02d}-01", r))
    ruh = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
    vend = x("INSERT INTO partners (name_en, name_ar, is_vendor, currency, email) VALUES (?,?,1,'EUR',?)",
             ("Rhein Industrietechnik GmbH", "راين للتقنيات الصناعية", "orders@rhein-tech.example"))
    cust = x("INSERT INTO partners (name_en, name_ar, is_customer, currency, email) VALUES (?,?,1,'USD',?)",
             ("Atlas Energy Inc.", "أطلس للطاقة", "ap@atlas-energy.example"))
    for m, amount, paid_month in ((3, 18500, 5), (6, 24200, 8), (8, 9800, None), (10, 31600, None)):
        d = date(today.year, m, 12)
        if d >= today:
            continue
        rate = cross_rate("EUR", "SAR", d.isoformat())
        bill = make_invoice("in_invoice", ruh, vend, d.isoformat(),
                            [("Hydraulic pumps and spare parts (import)", "5100", 1, amount, None)], due_days=60,
                            ref=f"RT-{2600 + m}", currency="EUR", rate=rate)
        if paid_month and date(today.year, paid_month, 10) < today:
            pd = date(today.year, paid_month, 10).isoformat()
            pay(bill, pd, rate=cross_rate("EUR", "SAR", pd))
    for m, amount, paid in ((4, 42000, True), (7, 27500, False), (9, 36800, False)):
        d = date(today.year, m, 20)
        if d >= today:
            continue
        inv = make_invoice("out_invoice", ruh, cust, d.isoformat(),
                           [("Engineering services — offshore project", "4200", 1, amount, None)], due_days=45,
                           currency="USD", rate=3.75)
        if paid:
            pay(inv, (d + timedelta(days=40)).isoformat(), rate=3.75)
    last_end = today.replace(day=1) - timedelta(days=1)
    try:
        post_revaluation(ruh["id"], last_end.isoformat(), 1)
    except Exception:  # nothing open on that date
        pass


def demo_cheques():
    """Customer cheques (one cleared, one bounced, one due, two post-dated) and post-dated cheques to vendors."""
    from .cheques import bounce, clear, register
    from .ledger import invoice_residuals
    today = date.today()
    ruh = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
    bank = journal(ruh["id"], "BNK")["id"]
    for kind, direction, plan in (
            ("out_invoice", "received", [(-40, -35, "clear"), (-20, -18, "bounce"), (-12, -3, None), (-6, 25, None),
                                         (-2, 55, None)]),
            ("in_invoice", "issued", [(-30, -25, "clear"), (-8, 20, None), (-4, 50, None)])):
        invs = q("SELECT * FROM invoices WHERE branch_id = ? AND kind = ? AND state = 'posted' AND currency IS NULL "
                 "AND date <= ? ORDER BY date LIMIT 60", (ruh["id"], kind, (today - timedelta(days=45)).isoformat()))
        res = invoice_residuals([i["id"] for i in invs])
        open_invs = [i for i in invs if res.get(i["id"], 0) > 0]
        for n, (got, dated, then) in enumerate(plan):
            if n >= len(open_invs):
                break
            inv = open_invs[n]
            issue = max(date.fromisoformat(inv["date"]), today + timedelta(days=got))
            cheque_date = max(issue, today + timedelta(days=dated))
            cid = register({"direction": direction, "branch_id": ruh["id"], "partner_id": inv["partner_id"],
                            "invoice_id": inv["id"], "cheque_no": f"{(415200 if direction == 'received' else 880100) + n * 37}",
                            "bank_name": ("Riyad Bank", "Saudi National Bank", "Al Rajhi Bank", "Banque Saudi Fransi",
                                          "Arab National Bank")[n % 5] if direction == "received" else "Al Rajhi Bank",
                            "issue_date": issue.isoformat(), "cheque_date": cheque_date.isoformat(),
                            "amount": res[inv["id"]], "bank_journal_id": bank, "notes": None}, 1)
            if then == "clear":
                clear(cid, min(today, cheque_date + timedelta(days=2)).isoformat(), bank, 1)
            elif then == "bounce":
                bounce(cid, min(today, cheque_date + timedelta(days=3)).isoformat(), "Insufficient funds", 1)


def demo_petty_cash():
    """An office petty cash fund in Riyadh and one in Jeddah, with a month of small expenses and top-ups."""
    from .pettycash import create_fund, post_voucher, top_up, voucher_totals, next_voucher_number
    today = date.today()
    vat = tax("VAT 15% (Purchases)")
    spend = [("6600", "Printer paper and toner", "Jarir Bookstore", 245, True), ("6300", "Water dispenser refill", "Nova Water", 90, True),
             ("6850", "Car wash for delivery van", "Petromin Express", 60, False), ("6600", "Courier to customer", "SMSA Express", 38, True),
             ("6200", "Parking fees", "Riyadh Municipality", 25, False), ("6600", "Tea, coffee and sugar", "Panda", 312, True),
             ("6800", "Document attestation", "Chamber of Commerce", 150, False)]
    for code, name, ar, custodian, flt in (("RUH", "Office", "المكتب", "Noura Al-Qahtani", 5000),
                                           ("JED", "Jeddah office", "مكتب جدة", "John Mathew", 3000)):
        b = q("SELECT * FROM branches WHERE code = ?", (code,), one=True)
        fid = create_fund(b["id"], name, ar, custodian, to_int(flt))
        fund = q("SELECT * FROM petty_cash_funds WHERE id = ?", (fid,), one=True)
        start = today - timedelta(days=50)
        top_up(fid, journal(b["id"], "BNK")["id"], to_int(flt), start.isoformat(), 1)
        for n in range(9 if code == "RUH" else 5):
            d = (start + timedelta(days=3 + n * 5)).isoformat()
            if d > today.isoformat():
                break
            acc_code, desc, payee, amt, with_vat = spend[n % len(spend)]
            lines = voucher_totals([{"account_id": acc(acc_code), "description": desc, "amount": to_int(amt),
                                     "tax_id": vat["id"] if with_vat else None, "cost_centre_id": None,
                                     "receipt_ref": f"{code[0]}{1200 + n}"}], 2)
            vid = x("INSERT INTO petty_cash_vouchers (number, fund_id, branch_id, date, payee, total, state, created_by, "
                    "created_at) VALUES (?,?,?,?,?,?, 'draft', 1, ?)",
                    (next_voucher_number(fund, d), fid, b["id"], d, payee, lines[0]["amount"] + lines[0]["tax_amount"], now()))
            for ln in lines:
                x("INSERT INTO petty_cash_lines (voucher_id, account_id, description, amount, tax_id, tax_amount, receipt_ref) "
                  "VALUES (?,?,?,?,?,?,?)", (vid, ln["account_id"], ln["description"], ln["amount"], ln["tax_id"],
                                             ln["tax_amount"], ln["receipt_ref"]))
            if n < 7:
                post_voucher(vid, 1)
            if n == 4:
                from .pettycash import fund_balance
                top_up(fid, journal(b["id"], "BNK")["id"], fund["float_amount"] - fund_balance(fund, d), d, 1)


def demo_stock_transfers():
    """A second warehouse in Riyadh (showroom) stocked from the main one, and goods sent to Jeddah (one in transit)."""
    from .wtransfers import create, receive, send
    today = date.today()
    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)
    jwh = q("SELECT * FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (jed["id"],), one=True)
    if not main or not jwh:
        return
    show = x("INSERT INTO warehouses (branch_id, code, name_en, name_ar) VALUES (?, 'SHW-RUH', 'Riyadh Showroom', "
             "'معرض الرياض')", (ruh["id"],))
    have = q("SELECT product_id, qty FROM stock_quants WHERE warehouse_id = ? AND qty >= 10 ORDER BY qty DESC LIMIT 4",
             (main["id"],))
    if len(have) < 2:
        return
    d1 = (today - timedelta(days=20)).isoformat()
    t1 = create(main["id"], show, d1, [{"product_id": h["product_id"], "quantity": max(int(h["qty"] // 5), 1)} for h in have[:2]],
                "Showroom display stock", 1)
    send(t1, d1, 1)
    d2 = (today - timedelta(days=12)).isoformat()
    t2 = create(main["id"], jwh["id"], d2, [{"product_id": h["product_id"], "quantity": max(int(h["qty"] // 6), 1)} for h in have[1:3]],
                "Replenish Jeddah", 1)
    send(t2, d2, 1)
    receive(t2, (today - timedelta(days=9)).isoformat(), 1)
    d3 = (today - timedelta(days=2)).isoformat()
    t3 = create(main["id"], jwh["id"], d3, [{"product_id": have[-1]["product_id"], "quantity": 2}], "On the truck", 1)
    send(t3, d3, 1)


def demo_projects():
    """Two customer projects: budgets, team, milestones billed with retention, a project material request issued
    from stock, a subcontractor bill, an equipment-hire PO partly billed, and a request waiting for the PM."""
    from .procurement import issue_material_request, rfq_comparison, save_doc, transition
    from .projects import bill_milestone, save_project
    from .stock import confirm_order, create_order_invoice
    today = date.today()
    year = today.year
    d = lambda m, day: min(date(year, m, day), today).isoformat()  # noqa: E731
    branches = {r["code"]: r for r in q("SELECT * FROM branches")}
    ruh, jed = branches["RUH"], branches["JED"]
    users = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
    wh = q("SELECT id FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (ruh["id"],), one=True)["id"]
    vat15s, vat15p = tax("VAT 15% (Sales)"), tax("VAT 15% (Purchases)")
    for code, en, ar in (("5300", "Subcontract Costs", "تكاليف مقاولي الباطن"),
                         ("5400", "Equipment Hire", "استئجار المعدات")):
        if not q("SELECT 1 FROM accounts WHERE code = ?", (code,), one=True):
            x("INSERT INTO accounts (code, name_en, name_ar, kind) VALUES (?,?,?, 'expense_direct_cost')", (code, en, ar))
    prods = {}
    for code, en, ar, acc_code in (("SUB-001", "Subcontract Works", "أعمال مقاولي الباطن", "5300"),
                                   ("EQH-001", "Equipment Hire (month)", "استئجار معدات (شهر)", "5400")):
        prods[code] = x("INSERT INTO products (code, name_en, name_ar, type, purchase_tax_id, expense_account_id, uom) "
                        "VALUES (?,?,?, 'service', ?, ?, 'Unit')", (code, en, ar, vat15p["id"], acc(acc_code)))
    sub = x("INSERT INTO partners (name_en, name_ar, is_vendor, vat_no, vendor_status, vendor_category, contact_person) "
            "VALUES (?,?,1,?,?,?,?)", ("Al Bina Electromechanical Contracting", "البناء للمقاولات الكهروميكانيكية",
                                       "310000000000041", "approved", "Subcontractors", "Hassan Al-Qahtani"))
    lift = x("INSERT INTO partners (name_en, name_ar, is_vendor, vat_no, vendor_status, vendor_category) "
             "VALUES (?,?,1,?,?,?)", ("Kingdom Equipment Rental", "المملكة لتأجير المعدات", "310000000000042",
                                      "approved", "Equipment hire"))

    p1 = save_project(None, {
        "code": "PRJ-001", "name_en": "Gulf Horizon HQ — Safety & MEP Fit-out",
        "name_ar": "مقر الخليج هورايزن — أعمال السلامة والكهروميكانيك", "branch_id": ruh["id"],
        "partner_id": partner("Gulf Horizon Contracting"), "manager_id": users["manager"],
        "start_date": d(6, 1), "end_date": f"{year + 1}-03-31", "site_address": "King Fahd Road, Al Olaya, Riyadh",
        "contract_value": to_int(850000), "retention_pct": 10, "budget_control": "warn", "state": "active",
        "tax_id": vat15s["id"]},
        [(users["accountant"], "Quantity surveyor"), (users["admin"], "Commercial manager")],
        [(acc("5100"), "Materials from stock", to_int(180000)), (acc("5300"), "Subcontract works", to_int(320000)),
         (acc("5400"), "Equipment hire", to_int(60000)), (acc("6600"), "Site consumables", to_int(15000))],
        [(None, "Mobilisation (15%)", to_int(127500), d(6, 30)), (None, "Rough-in works (35%)", to_int(297500), d(9, 30)),
         (None, "Second fix (30%)", to_int(255000), d(12, 15)),
         (None, "Testing & handover (20%)", to_int(170000), f"{year + 1}-03-31")], 1)
    p2 = save_project(None, {
        "code": "PRJ-002", "name_en": "Desert Rose Hotel — Fire Safety Upgrade",
        "name_ar": "فندق وردة الصحراء — تحديث أنظمة السلامة من الحريق", "branch_id": jed["id"],
        "partner_id": partner("Desert Rose Hotels"), "manager_id": users["admin"], "start_date": d(9, 1),
        "end_date": f"{year + 1}-01-31", "contract_value": to_int(240000), "retention_pct": 5,
        "budget_control": "block", "state": "active", "tax_id": vat15s["id"]},
        [(users["manager"], "Site engineer")],
        [(acc("5100"), "Materials", to_int(90000)), (acc("5300"), "Installation subcontract", to_int(60000))],
        [(None, "Supply of equipment (50%)", to_int(120000), d(11, 30)),
         (None, "Installation & commissioning (50%)", to_int(120000), f"{year + 1}-01-31")], 1)
    cc1 = q("SELECT cost_centre_id FROM projects WHERE id = ?", (p1,), one=True)[0]

    # progress invoices: mobilisation (posted and paid), rough-in (posted)
    for n, (mname, day) in enumerate((("Mobilisation (15%)", d(7, 2)), ("Rough-in works (35%)", d(10, 3)))):
        mid = q("SELECT id FROM project_milestones WHERE project_id = ? AND name = ?", (p1, mname), one=True)[0]
        inv_id = bill_milestone(mid, day, 1)
        post_invoice(inv_id, 1)
        if n == 0:
            pay(q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True), d(7, 28))

    # material request for the project: the PM approves first, then the department; issued from stock
    mr = save_doc("mr", None, {"branch_id": ruh["id"], "warehouse_id": wh, "department": "Projects",
                               "project_id": p1, "date": d(6, 10), "required_date": d(6, 15),
                               "purpose": "Safety kit and tools for the site team"},
                  [{"product_id": q("SELECT id FROM products WHERE code = ?", (c,), one=True)[0], "description": dsc,
                    "quantity": qty} for c, dsc, qty in (("SAF-001", "Safety Helmet", 30), ("SAF-003", "Hi-vis Vest", 30),
                                                         ("IND-010", "Industrial Drill", 2))], 3)
    transition("mr", mr, "submit", 3)
    transition("mr", mr, "approve", users["manager"], approver=True)
    transition("mr", mr, "approve", users["admin"], approver=True)
    issue_material_request(mr, d(6, 12), 1)

    # subcontractor bill on the project
    j = journal(ruh["id"], "BILL")
    for desc, amt, day, ref in (("Electrical first fix — progress 1", 95000, d(8, 25), "BINA-2201"),
                                ("Electrical first fix — progress 2", 48000, d(10, 20), "BINA-2245")):
        sub_t, tax_t = compute_invoice_line(1, to_int(amt), vat15p["rate"], 2)
        bid = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, ref, state, amount_untaxed, "
                "amount_tax, amount_total, project_id, created_by, created_at) VALUES (?,?,'in_invoice',?,?,date(?, '+30 days'),"
                "?,'draft',?,?,?,?,1,?)", (ruh["id"], j["id"], sub, day, day, ref, sub_t, tax_t, sub_t + tax_t, p1, day))
        x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, "
          "subtotal, tax_amount, product_id, cost_centre_id) VALUES (?,0,?,?,1,?,?,?,?,?,?)",
          (bid, desc, acc("5300"), to_int(amt), vat15p["id"], sub_t, tax_t, prods["SUB-001"], cc1))
        post_invoice(bid, 1)

    # equipment hire PO for the project (committed), the first month billed (actual)
    price = to_int(12500)
    sub_t, tax_t = compute_invoice_line(3, price, vat15p["rate"], 2)
    po = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, expected_date, state, "
           "amount_untaxed, amount_tax, amount_total, project_id, cost_centre_id, created_by, created_at) "
           "VALUES ('purchase', 'PO/RUH/PRJ1', ?, ?, ?, ?, ?, 'draft', ?, ?, ?, ?, ?, 1, ?)",
           (ruh["id"], wh, lift, d(8, 1), d(8, 3), sub_t, tax_t, sub_t + tax_t, p1, cc1, d(8, 1)))
    x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, subtotal, "
      "tax_amount) VALUES (?,0,?,?,3,?,?,?,?)", (po, prods["EQH-001"], "Scissor lift 12 m — monthly hire", price,
                                                 vat15p["id"], sub_t, tax_t))
    from .stock import next_number, ORDER_PREFIX
    x("UPDATE orders SET number = ? WHERE id = ?", (next_number("orders", "number", ORDER_PREFIX["purchase"], ruh["id"],
                                                                d(8, 1)), po))
    confirm_order(po, 1)
    from .procurement import create_grn, send_po, acknowledge_po
    send_po(po, 1, email=False)
    acknowledge_po(po, "accepted", "KER-SO-7781", d(8, 3), None, 1)
    hire_line = q("SELECT id FROM order_lines WHERE order_id = ?", (po,), one=True)[0]
    create_grn(po, d(8, 31), {hire_line: 1}, "KER-DN-0412", "First month of hire confirmed by site", users["manager"])
    create_grn(po, d(9, 30), {hire_line: 1}, "KER-DN-0498", None, users["manager"])
    bill = create_order_invoice(po, d(9, 2), 1)
    x("UPDATE invoice_lines SET quantity = 1, subtotal = ?, tax_amount = ? WHERE invoice_id = ?",
      (price, compute_invoice_line(1, price, vat15p["rate"], 2)[1], bill))
    t1 = compute_invoice_line(1, price, vat15p["rate"], 2)
    x("UPDATE invoices SET ref = 'KER-0931', amount_untaxed = ?, amount_tax = ?, amount_total = ? WHERE id = ?",
      (t1[0], t1[1], t1[0] + t1[1], bill))
    post_invoice(bill, 1)

    # a request waiting for the project manager's approval
    mr2 = save_doc("mr", None, {"branch_id": ruh["id"], "warehouse_id": wh, "department": "Projects", "project_id": p1,
                                "date": today.isoformat(), "required_date": (today + timedelta(days=7)).isoformat(),
                                "purpose": "Second-fix tools"},
                   [{"product_id": q("SELECT id FROM products WHERE code = 'IND-011'", one=True)[0],
                     "description": "Angle Grinder", "quantity": 4}], 3)
    transition("mr", mr2, "submit", 3)

    demo_rfqs()
    return p1, p2


def demo_rfqs(trading=False):
    """RFQs straight from approved MRs (project site materials, or warehouse restocking for a trading company):
    one sealed and still open, one opened with its award waiting."""
    from datetime import datetime
    from .procurement import open_bids, recommend_award, rfq_comparison, rfq_from_mr, save_doc, save_quote, send_rfq, \
        transition
    today = date.today()
    year = today.year
    d = lambda m, day: min(date(year, m, day), today).isoformat()  # noqa: E731
    ruh = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
    users = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
    wh = q("SELECT id FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (ruh["id"],), one=True)["id"]
    vat15p = tax("VAT 15% (Purchases)")
    p1 = None if trading else q("SELECT id FROM projects WHERE code = 'PRJ-001'", one=True)["id"]
    vendors = [partner(n) for n in ("Gulf Safety Equipment Co.", "Arabian Tools Trading", "TechSource Systems")]
    vat = vat15p["id"]
    stories = ((
            ("Restock safety gloves and goggles for Q1",
             [("Cut-resistant gloves, level 5", "EN 388 4X43F, sizes 8-10", "Ansell / HyFlex", "11-627", "Pair", 600),
              ("Safety goggles, anti-fog", "EN 166, clear lens", "3M", "2890", "Unit", 400)],
             (today + timedelta(days=3)).isoformat() + "T14:00", [(14, 9), (13, 10)]),
            ("Hand tools for the spring promotion",
             [("Socket set 1/2\" drive, 24 pcs", "Chrome vanadium, metric", "Stanley", "STMT74183", "Set", 80)],
             None, [(165,), (149,), (158,)]))
        if trading else (
            ("Cable trays and fittings for level 3",
             [("Cable tray 300 mm, hot-dip galvanised", "IEC 61537, 3 m lengths", "Legrand / Unitrunk", "CT-300-HDG", "Length", 120),
              ("Tray bends 90°, 300 mm", "Matching the trays", "Legrand / Unitrunk", "CTB-300-90", "Unit", 40)],
             (today + timedelta(days=3)).isoformat() + "T14:00", [(820, 95), (790, 102)]),
            ("Fire-rated sealant for penetrations",
             [("Intumescent sealant 310 ml", "2-hour fire rating, UL listed", "Hilti", "CFS-S ACR", "Cartridge", 300)],
             None, [(88,), (79,), (84,)])))
    for n, (purpose, items, closing, quotes) in enumerate(stories):
        mr_x = save_doc("mr", None, {"branch_id": ruh["id"], "warehouse_id": wh, "department": "Warehouse" if trading else "Projects",
                                     "project_id": p1, "date": d(10, 20 + n), "required_date": d(11, 30),
                                     "purpose": purpose},
                        [{"product_id": None, "description": it[0], "quantity": it[5]} for it in items], 3)
        transition("mr", mr_x, "submit", 3)
        for approver in ("manager", "admin"):  # project MRs also need the project manager's step
            if q("SELECT state FROM material_requests WHERE id = ?", (mr_x,), one=True)["state"] == "submitted":
                transition("mr", mr_x, "approve", users[approver], approver=True)
        rfq = rfq_from_mr(mr_x, d(10, 22 + n), 3)
        for ln, it in zip(q("SELECT id FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (rfq,)), items):
            x("UPDATE rfq_lines SET specification = ?, manufacturer = ?, part_number = ?, uom = ?, allow_alternative = 1 "
              "WHERE id = ?", (it[1], it[2].split(" / ")[0], it[3], it[4], ln["id"]))
        x("UPDATE rfqs SET sealed = 1, closing_at = ?, validity_days = 30, payment_terms = '30 days from invoice', "
          "incoterms = 'DAP', delivery_place = ? WHERE id = ?",
          (closing or (today + timedelta(days=5)).isoformat() + "T17:00",
           "Main warehouse WH-RUH, Riyadh" if trading else "Gulf Horizon HQ site, Riyadh", rfq))
        for v in vendors:
            x("INSERT INTO rfq_vendors (rfq_id, partner_id) VALUES (?,?)", (rfq, v))
        send_rfq(rfq, 3)
        rvs = q("SELECT id FROM rfq_vendors WHERE rfq_id = ? ORDER BY id", (rfq,))
        rls = q("SELECT id FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (rfq,))
        for rv, prices in zip(rvs, quotes):
            save_quote(rv["id"], {ln["id"]: to_int(pr_) for ln, pr_ in zip(rls, prices)}, {ln["id"]: vat for ln in rls},
                       f"Q-{rfq}-{rv['id']}", d(10, 25), 10 + rv["id"] % 7, None, user_id=3,
                       valid_until=(today + timedelta(days=30)).isoformat(), payment_terms="30 days")
        if closing is None:  # the second RFQ has closed: open the bids and recommend the lowest
            x("UPDATE rfqs SET closing_at = ? WHERE id = ?", (datetime.now().strftime("%Y-%m-%dT%H:%M")[:10] + "T00:00", rfq))
            open_bids(rfq, 3)
            best = rfq_comparison(rfq)["best_vendor"]
            recommend_award(rfq, best, wh, None, 3)


def demo_registrations():
    """Supplier registrations from the public link: one waiting for review, one approved (vendor + portal user)."""
    import os
    import uuid
    from .portal import _approved, _start_review, folder, next_reg_number
    pdf = b"%PDF-1.4\n1 0 obj<<>>endobj\ntrailer<<>>\n%%EOF\n"
    users = {r["username"]: r["id"] for r in q("SELECT id, username FROM users")}
    today = date.today()
    for n, (en, ar, cr, vat, cat, person, email, bank, iban) in enumerate((
            ("Najd Electrical Supplies Co.", "شركة نجد للتوريدات الكهربائية", "1010778899", "310778899000003",
             "Electrical supplies", "Sami Al-Otaibi", "sales@najd-electric.example", "Al Rajhi Bank",
             "SA0380000000608010167519"),
            ("Red Sea Fire Protection LLC", "البحر الأحمر لأنظمة الحماية من الحريق", "4030556677", "310556677000003",
             "Fire protection", "Yousef Hamdan", "info@redsea-fire.example", "Saudi National Bank",
             "SA4420000001234567891234"))):
        rid = x("INSERT INTO vendor_registrations (number, state, company_en, company_ar, cr_no, vat_no, category, "
                "contact_person, email, phone, address, city, country, bank_name, iban, account_name, currency, "
                "declaration, ip, submitted_at, created_at) VALUES (?, 'submitted', ?,?,?,?,?,?,?,?,?,?,?,?,?,?, 'SAR', 1,"
                " '127.0.0.1', ?, ?)",
                (next_reg_number(), en, ar, cr, vat, cat, person, email, f"+966 5{n}0 000 00{n}", "Industrial Area",
                 "Riyadh" if n == 0 else "Jeddah", "Saudi Arabia", bank, iban, en,
                 (today - timedelta(days=3 - n)).isoformat() + "T10:30:00", (today - timedelta(days=3 - n)).isoformat()))
        for doc, num, exp in (("Commercial Registration", cr, (today + timedelta(days=300)).isoformat()),
                              ("VAT Certificate", vat, None), ("Bank Letter", None, None)):
            stored = f"reg-{uuid.uuid4().hex}.pdf"
            with open(os.path.join(folder(), stored), "wb") as f:
                f.write(pdf)
            x("INSERT INTO vendor_registration_docs (registration_id, doc_type, number, expiry_date, filename, "
              "original_name, uploaded_at) VALUES (?,?,?,?,?,?,?)",
              (rid, doc, num, exp, stored, f"{doc.replace(' ', '_')}.pdf", now()))
        _start_review(rid)
        if n == 1:  # reviewed by procurement and finance: the vendor and its portal user are created
            from .workflow import act_on_doc
            act_on_doc("vendor_registration", rid, users["manager"], "approve", "Documents in order")
            act_on_doc("vendor_registration", rid, users["admin"], "approve", "IBAN matches the bank letter")


def demo_vendor_portal():
    """Gulf Safety on the portal (demo sign-in sales@gulfsafety.example / Supplier2026): its POs sent, an invoice in
    the AP inbox, a renewed CR and a bank account change waiting for review."""
    import io
    from werkzeug.datastructures import FileStorage
    from werkzeug.security import generate_password_hash
    from .vendorportal import request_bank_change, submit_doc, submit_invoice
    gulf = partner("Gulf Safety Equipment Co.")
    today = date.today()
    uid = x("INSERT INTO portal_users (partner_id, email, full_name, password_hash, activated_at, created_at) "
            "VALUES (?, 'sales@gulfsafety.example', 'Ahmed Nasser', ?, ?, ?)",
            (gulf, generate_password_hash("Supplier2026", method="pbkdf2:sha256"), now(), now()))
    x("UPDATE orders SET sent_at = date || 'T12:00:00' WHERE partner_id = ? AND kind = 'purchase' AND state = 'confirmed'",
      (gulf,))

    def pdf(name):
        return FileStorage(io.BytesIO(b"%PDF-1.4\n%%EOF\n"), filename=name)
    from .stock import order_status
    po = None
    for o in q("SELECT * FROM orders WHERE partner_id = ? AND kind = 'purchase' AND state = 'confirmed' ORDER BY id DESC",
               (gulf,)):
        if order_status(o)[1] != "full":
            po = o
            break
    submit_invoice(gulf, uid, {"invoice_no": "GSE-INV-7781", "invoice_date": today.isoformat(),
                               "order_id": po["id"] if po else None,
                               "amount_untaxed": po["amount_untaxed"] if po else to_int(4200),
                               "amount_tax": po["amount_tax"] if po else to_int(630),
                               "amount_total": po["amount_total"] if po else to_int(4830), "currency": "SAR",
                               "notes": "Delivered in full"}, pdf("GSE-INV-7781.pdf"))
    submit_doc(gulf, uid, "Commercial Registration", "4030111111", (today + timedelta(days=385)).isoformat(),
               pdf("CR-renewed.pdf"))
    request_bank_change(gulf, {"bank_name": "Riyad Bank", "iban": "SA4420000001234567891234",
                               "account_name": "Gulf Safety Equipment Co.", "swift": "RIBLSARI"},
                        pdf("bank-letter.pdf"), portal_user_id=uid)


def demo_invoice_types():
    """An advance invoice (paid) deducted on its final invoice, a proforma waiting, a debit note and a simplified
    invoice for a walk-in customer."""
    from . import invoicetypes as it
    today = date.today()
    ruh = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
    vat = tax("VAT 15% (Sales)")
    cust = partner("Saudi Modern Clinics")

    def draft(itype, lines, d, partner_id=cust, ref=None):
        j = journal(ruh["id"], "INV")
        comp = []
        for desc, acc_id, qty, price in lines:
            sub, tx = compute_invoice_line(qty, to_int(price), vat["rate"], 2)
            comp.append((desc, acc_id, qty, to_int(price), vat["id"], sub, tx))
        un, tx = sum(c[5] for c in comp), sum(c[6] for c in comp)
        iid = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, ref, state, amount_untaxed, "
                "amount_tax, amount_total, invoice_type, created_by, created_at) VALUES (?,?,'out_invoice',?,?,date(?, '+30 days'),"
                "?,'draft',?,?,?,?,1,?)", (ruh["id"], j["id"], partner_id, d, d, ref, un, tx, un + tx, itype, d))
        for i, c in enumerate(comp):
            x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, "
              "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)", (iid, i) + c)
        return iid
    d1, d2 = (today - timedelta(days=40)).isoformat(), (today - timedelta(days=5)).isoformat()
    adv = draft("advance", [("Advance 30% — clinic safety equipment package", it.advance_account(), 1, 36000)], d1,
                ref="SO advance")
    post_invoice(adv, 1)
    pay(q("SELECT * FROM invoices WHERE id = ?", (adv,), one=True), (today - timedelta(days=35)).isoformat())
    final = draft("standard", [("Clinic safety equipment package — supplied and installed", acc("4100"), 1, 120000)], d2)
    it.deduct_advance(final, adv, to_int(36000), 1)
    post_invoice(final, 1)
    pro = draft("proforma", [("Annual maintenance contract 2027 (proforma)", acc("4200"), 1, 48000)], today.isoformat(),
                partner_id=partner("Desert Rose Hotels"))
    it.issue_proforma(pro, 1)
    dn = it.create_debit_note(final, 1)
    x("UPDATE invoice_lines SET description = 'Additional installation at the second floor', quantity = 1, price_unit = ?, "
      "subtotal = ?, tax_amount = ? WHERE invoice_id = ?", (to_int(4000), to_int(4000), to_int(600), dn))
    x("UPDATE invoices SET amount_untaxed = ?, amount_tax = ?, amount_total = ?, note_reason = ? WHERE id = ?",
      (to_int(4000), to_int(600), to_int(4600), "Extra work agreed on site", dn))
    post_invoice(dn, 1)
    simp = draft("simplified", [("Safety helmets — counter sale", acc("4100"), 5, 60)], today.isoformat(),
                 partner_id=partner("Walk-in Customer"))
    post_invoice(simp, 1)


def demo_trading_tools():
    """Seal kits tracked by batch (one batch expiring soon), reorder settings, and a write-off of damaged gloves."""
    from datetime import date, timedelta
    from .stock import create_picking, scrap_stock, validate_picking
    kit = q("SELECT * FROM products WHERE code = 'SPR-100'", one=True)
    gloves = q("SELECT * FROM products WHERE code = 'SAF-002'", one=True)
    b = q("SELECT * FROM branches WHERE code = 'RUH'", one=True)
    if not (kit and gloves and b):
        return
    wh = q("SELECT id FROM warehouses WHERE branch_id = ? ORDER BY id LIMIT 1", (b["id"],), one=True)["id"]
    vend = q("SELECT partner_id FROM orders WHERE kind = 'purchase' AND state = 'confirmed' ORDER BY id LIMIT 1", one=True)
    today = date.today()
    x("UPDATE products SET tracking = 'lot', shelf_life_days = 730, lead_days = 14, max_qty = 60 WHERE id = ?", (kit["id"],))
    x("UPDATE products SET max_qty = 400 WHERE id = ?", (gloves["id"],))
    d = (today - timedelta(days=6)).isoformat()
    for qty, lot, days in ((12, "SK-2411", 21), (8, "SK-2605", 540)):
        pk = create_picking("in", b["id"], wh, d, [{"product_id": kit["id"], "quantity": qty,
                                                    "unit_cost": kit["purchase_price"]}], vend[0] if vend else None)
        line = q("SELECT id FROM picking_lines WHERE picking_id = ?", (pk,), one=True)[0]
        validate_picking(pk, d=d, user_id=1, lots={line: (lot, (today + timedelta(days=days)).isoformat())})
    scrap_stock(b["id"], wh, (today - timedelta(days=2)).isoformat(), [(gloves["id"], 6, None)], "damaged",
                "Torn in the carton — found at shelf count", 1)


def seed(demo=True, business="custom"):
    """business: trading | contracting | services | custom (every module). The demo data tells that business's story,
    and only its modules are switched on."""
    from flask import current_app
    current_app.config["ALL_MODULES"] = True
    try:
        _seed(demo, business)
    finally:
        current_app.config["ALL_MODULES"] = False


def _seed(demo, business="custom"):
    from .modules import BUSINESS_TYPES
    setup_base()
    setup_users()
    setup_workflow()
    mods = BUSINESS_TYPES.get(business, BUSINESS_TYPES["custom"])["modules"]
    on = (lambda m: True) if mods is None else (lambda m: m in mods)  # noqa: E731
    if business == "contracting":
        x("UPDATE company SET name_en = 'Vertex Contracting Co.', name_ar = 'شركة فيرتكس للمقاولات' WHERE id = 1")
    if demo:
        demo_transactions()
        demo_modules()
        demo_crm_pos_bank()
        if on("hr"):
            demo_hr()
        demo_fx()
        demo_cheques()
        demo_petty_cash()
        demo_stock_transfers()
        if on("projects"):
            demo_projects()          # also runs the project RFQs
        else:
            demo_rfqs(trading=True)  # restocking RFQs from the warehouse
        demo_registrations()
        demo_vendor_portal()
        demo_invoice_types()
        if on("inventory"):
            demo_trading_tools()
        if not on("projects"):
            x("UPDATE invoices SET invoice_type = 'standard' WHERE invoice_type = 'progress'")
    x("UPDATE company SET business_type = ?, enabled_modules = ? WHERE id = 1",
      (business if business in BUSINESS_TYPES else "custom", ",".join(mods) if mods else None))
    from .modules import sync_module_accounts
    sync_module_accounts(set(mods) if mods else None)
    auto_group()  # a new company starts with the standard grouped chart
    commit()
