"""Import data from an old system: CSV or Excel files, mapped column by column.

Each import type lists its fields. The file is read into rows, the user's column mapping turns each row into a
dict of field values, and the type's handler writes them through the normal posting engine. One import is one
transaction: a preview runs the whole import and rolls it back, so it checks exactly what the real run does.
Every row (or document, for multi-row types) runs in a savepoint, so all errors are collected in one pass.

Subledger items (open invoices, opening stock, fixed assets) post against the Opening Balance Clearing account
(3990). The opening trial balance moves the matching control accounts (receivable, payable, inventory, fixed
assets) to the same account, so once everything is imported the clearing account is zero.
"""
import csv
import io
import os
import re
from collections import OrderedDict
from datetime import date, datetime, timedelta

from .db import get_db, placeholders, q, x
from .i18n import nm, t
from .ledger import ACCOUNT_KINDS, AccountingError, INVOICE_KINDS, audit, check_lock, company, \
    compute_invoice_line, create_move, create_payment, invoice_residuals, now, post_invoice
from .money import to_int
from .schedules import branch_decimals, depreciation_schedule
from .stock import EPS, create_picking, validate_picking

DATE_FORMATS = {"DD/MM/YYYY": "%d/%m/%Y", "MM/DD/YYYY": "%m/%d/%Y", "YYYY-MM-DD": "%Y-%m-%d",
                "DD-MM-YYYY": "%d-%m-%Y", "DD.MM.YYYY": "%d.%m.%Y", "DD-Mon-YYYY": "%d-%b-%Y"}
DIGITS = str.maketrans("٠١٢٣٤٥٦٧٨٩٫٬۰۱۲۳۴۵۶۷۸۹", "0123456789.,0123456789")
CLEARING_CODE = "3990"
MAX_ERRORS = 300


class RowError(Exception):
    pass


def F(key, label, required=False, aliases=(), help=None):
    return {"key": key, "label": label, "required": required, "aliases": aliases, "help": help}


BRANCH = F("branch", "Branch", aliases=("branch code", "location", "الفرع"),
           help="Branch code or name. Leave out to use the default branch chosen below.")
PARTNER_ALIASES = ("customer", "vendor", "supplier", "partner", "party", "customer name", "vendor name",
                   "supplier name", "account name", "العميل", "المورد", "اسم العميل", "اسم المورد")
AMOUNT_HELP = "Use Debit and Credit columns, or one Amount column (positive = debit, negative = credit)."

TYPES = OrderedDict([
    ("accounts", {
        "title": "Chart of Accounts", "group": "master",
        "desc": "Account codes, names in English and Arabic, and account types. Existing codes are updated.",
        "fields": [F("code", "Account Code", True, ("account code", "account no", "account number", "acc code",
                                                    "رقم الحساب", "رمز الحساب")),
                   F("name_en", "Name (English)", True, ("account name", "name", "description", "اسم الحساب")),
                   F("name_ar", "Name (Arabic)", aliases=("arabic name", "الاسم بالعربي", "الاسم العربي")),
                   F("kind", "Type", aliases=("account type", "category", "group", "نوع الحساب"),
                     help="Bank and Cash, Receivable, Current Assets, Fixed Assets, Payable, Current Liabilities, "
                          "Equity, Income, Cost of Revenue, Expenses … If empty, the type is guessed from the first "
                          "digit of the code (1 assets, 2 liabilities, 3 equity, 4 income, 5 cost of revenue, "
                          "6–9 expenses)."),
                   F("intercompany", "Inter-branch account", aliases=("intercompany",))],
    }),
    ("partners", {
        "title": "Customers & Vendors", "group": "master",
        "desc": "Names, VAT and CR numbers, contacts, payment terms and bank details. Matched by VAT number or name.",
        "fields": [F("name_en", "Name (English)", True, PARTNER_ALIASES + ("name", "company", "الاسم")),
                   F("name_ar", "Name (Arabic)", aliases=("arabic name", "الاسم بالعربي", "الاسم العربي")),
                   F("role", "Customer / Vendor", aliases=("type", "partner type", "النوع"),
                     help="customer, vendor or both. Leave out to use the choice below."),
                   F("vat_no", "VAT Number", aliases=("vat", "trn", "tax number", "vat no", "الرقم الضريبي")),
                   F("cr_no", "CR Number", aliases=("cr", "commercial registration", "السجل التجاري")),
                   F("phone", "Phone", aliases=("mobile", "telephone", "tel", "الجوال", "الهاتف")),
                   F("email", "Email", aliases=("e-mail", "البريد الإلكتروني")),
                   F("address", "Address", aliases=("العنوان",)),
                   F("contact_person", "Contact person", aliases=("contact", "attention", "جهة الاتصال")),
                   F("payment_terms", "Payment terms (days)", aliases=("terms", "credit days", "credit period",
                                                                       "مدة السداد")),
                   F("bank_name", "Bank name", aliases=("bank", "اسم البنك")),
                   F("iban", "IBAN", aliases=("iban no", "account iban", "الآيبان"))],
    }),
    ("products", {
        "title": "Products & Services", "group": "master", "modules": ("sales", "procurement", "inventory", "pos"),
        "desc": "Item codes, names, type, prices, VAT and accounts. Existing codes are updated.",
        "fields": [F("code", "Internal Reference", aliases=("item code", "product code", "sku", "code", "رمز الصنف",
                                                            "كود الصنف")),
                   F("name_en", "Name (English)", True, ("item name", "product name", "product", "item", "name",
                                                         "description", "اسم الصنف")),
                   F("name_ar", "Name (Arabic)", aliases=("arabic name", "الاسم بالعربي", "الاسم العربي")),
                   F("type", "Product Type", aliases=("item type", "type"),
                     help="stock (tracked in the warehouse), service or consumable. Default: stock."),
                   F("category", "Category", aliases=("item group", "group", "الفئة", "المجموعة")),
                   F("uom", "Unit of Measure", aliases=("unit", "uom", "الوحدة")),
                   F("barcode", "Barcode", aliases=("ean", "upc", "الباركود")),
                   F("sale_price", "Sales Price", aliases=("price", "selling price", "sale price", "سعر البيع")),
                   F("cost", "Cost", aliases=("purchase price", "cost price", "unit cost", "التكلفة", "سعر الشراء")),
                   F("sale_tax", "Sales Tax", aliases=("vat", "vat %", "tax", "vat rate"),
                     help="VAT rate (15, 5, 0) or tax name. Leave the column out to use the standard rate."),
                   F("purchase_tax", "Purchase Tax", aliases=("purchase vat",)),
                   F("income_account", "Income Account", aliases=("sales account", "revenue account")),
                   F("expense_account", "Expense / COGS Account", aliases=("cogs account", "expense account"))],
    }),
    ("opening", {
        "title": "Opening Balances", "group": "fresh",
        "desc": "The trial balance of the old system on the cut-off date, posted as one opening entry per branch.",
        "fields": [BRANCH,
                   F("account", "Account", True, ("account code", "account no", "code", "acc code", "رقم الحساب")),
                   F("partner", "Partner", aliases=PARTNER_ALIASES),
                   F("label", "Label", aliases=("description", "narration", "account name", "البيان")),
                   F("debit", "Debit", aliases=("dr", "debit balance", "مدين"), help=AMOUNT_HELP),
                   F("credit", "Credit", aliases=("cr", "credit balance", "دائن")),
                   F("amount", "Amount", aliases=("balance", "net", "closing balance", "الرصيد"))],
    }),
    ("open_items", {
        "title": "Open Invoices & Bills", "group": "fresh",
        "desc": "Unpaid customer invoices and vendor bills on the cut-off date, so aging and payments work.",
        "fields": [F("type", "Document type", aliases=("doc type", "type", "kind", "نوع المستند"),
                     help="invoice, bill, credit note or vendor refund. Leave out to use the choice below."),
                   BRANCH,
                   F("number", "Number", True, ("invoice no", "invoice number", "bill no", "document no", "doc no",
                                                "رقم الفاتورة")),
                   F("date", "Date", True, ("invoice date", "doc date", "التاريخ", "تاريخ الفاتورة")),
                   F("due_date", "Due Date", aliases=("due", "تاريخ الاستحقاق")),
                   F("partner", "Partner", True, PARTNER_ALIASES),
                   F("amount", "Amount due", True, ("balance", "open amount", "outstanding", "residual",
                                                     "amount due", "المبلغ المستحق", "الرصيد"),
                     help="The unpaid amount including VAT. A negative amount is imported as a credit note."),
                   F("total", "Original total", aliases=("invoice amount", "total", "الإجمالي")),
                   F("ref", "Reference", aliases=("reference", "po number", "المرجع"))],
    }),
    ("entries", {
        "title": "Journal Entries", "group": "history",
        "desc": "Past journal entries line by line. Rows with the same entry number form one entry, which must balance.",
        "fields": [F("entry", "Entry number", True, ("entry no", "voucher", "voucher no", "jv no", "jv",
                                                     "document no", "رقم القيد", "رقم السند")),
                   F("date", "Date", True, ("entry date", "voucher date", "posting date", "التاريخ")),
                   BRANCH,
                   F("journal", "Journal", aliases=("journal code", "دفتر اليومية"),
                     help="Journal code in the branch (MISC, BNK, INV …). Default: MISC."),
                   F("account", "Account", True, ("account code", "account no", "code", "رقم الحساب")),
                   F("partner", "Partner", aliases=PARTNER_ALIASES),
                   F("label", "Label", aliases=("description", "narration", "memo", "البيان")),
                   F("debit", "Debit", aliases=("dr", "مدين"), help=AMOUNT_HELP),
                   F("credit", "Credit", aliases=("cr", "دائن")),
                   F("amount", "Amount", aliases=("المبلغ",)),
                   F("ref", "Reference", aliases=("reference", "المرجع"))],
    }),
    ("invoices", {
        "title": "Invoices & Bills with lines", "group": "history",
        "desc": "Past invoices and bills with their lines and VAT. Rows with the same number form one document.",
        "fields": [F("type", "Document type", aliases=("doc type", "kind", "نوع المستند")),
                   BRANCH,
                   F("number", "Number", True, ("invoice no", "invoice number", "bill no", "document no",
                                                "رقم الفاتورة")),
                   F("date", "Date", True, ("invoice date", "التاريخ", "تاريخ الفاتورة")),
                   F("due_date", "Due Date", aliases=("due", "تاريخ الاستحقاق")),
                   F("partner", "Partner", True, PARTNER_ALIASES),
                   F("ref", "Reference", aliases=("reference", "المرجع")),
                   F("product", "Product", aliases=("item", "item code", "product code", "sku", "الصنف")),
                   F("description", "Description", aliases=("line description", "details", "البيان", "الوصف")),
                   F("account", "Account", aliases=("account code", "gl account", "رقم الحساب"),
                     help="Income or expense account. Default: the product's account, then the journal's."),
                   F("quantity", "Quantity", aliases=("qty", "الكمية")),
                   F("price", "Unit Price", True, ("price", "rate", "unit price", "سعر الوحدة", "السعر"),
                     help="Price before VAT."),
                   F("tax", "Tax", aliases=("vat", "vat %", "vat rate", "الضريبة")),
                   F("total", "Total", aliases=("invoice total", "grand total", "الإجمالي"),
                     help="Optional check: the document is refused when its computed total differs."),
                   ],
    }),
    ("payments", {
        "title": "Payments", "group": "history",
        "desc": "Customer receipts and vendor payments, linked to invoices by number when given.",
        "fields": [F("date", "Date", True, ("payment date", "التاريخ")),
                   BRANCH,
                   F("type", "Payment type", aliases=("type", "direction", "نوع الدفعة"),
                     help="receipt (from a customer), payment (to a vendor), customer refund or vendor refund. "
                          "Not needed when the invoice is given."),
                   F("partner", "Partner", True, PARTNER_ALIASES),
                   F("journal", "Journal", aliases=("bank", "cash account", "journal code", "البنك", "الصندوق"),
                     help="Bank or cash journal code (BNK, CSH …). Default: BNK."),
                   F("amount", "Amount", True, ("paid", "amount paid", "المبلغ")),
                   F("invoice", "Invoice number", aliases=("invoice", "invoice no", "bill no", "رقم الفاتورة")),
                   F("ref", "Reference", aliases=("reference", "cheque no", "المرجع"))],
    }),
    ("stock", {
        "title": "Opening Stock", "group": "fresh", "modules": ("inventory",),
        "desc": "Quantities and unit costs per warehouse on the cut-off date.",
        "fields": [F("warehouse", "Warehouse", aliases=("store", "location", "warehouse code", "المستودع"),
                     help="Warehouse code or name. Default: the branch's first warehouse."),
                   BRANCH,
                   F("product", "Product", True, ("item", "item code", "product code", "sku", "code", "الصنف")),
                   F("quantity", "Quantity", True, ("qty", "on hand", "qty on hand", "الكمية")),
                   F("cost", "Unit Cost", aliases=("cost", "unit cost", "average cost", "التكلفة"),
                     help="Default: the product's cost.")],
    }),
    ("assets", {
        "title": "Fixed Assets", "group": "fresh", "modules": ("assets",),
        "desc": "The asset register with cost and depreciation to date; depreciation continues for the remaining life.",
        "fields": [F("code", "Code", aliases=("asset code", "asset no", "tag", "رقم الأصل")),
                   F("name_en", "Name (English)", True, ("asset name", "name", "description", "اسم الأصل")),
                   F("name_ar", "Name (Arabic)", aliases=("arabic name", "الاسم بالعربي")),
                   F("category", "Category", True, ("asset category", "class", "فئة الأصل")),
                   BRANCH,
                   F("acquisition_date", "Acquisition date", True, ("purchase date", "date", "تاريخ الشراء")),
                   F("start_date", "Depreciation start", aliases=("depreciation start", "in service date"),
                     help="Default: the acquisition date."),
                   F("cost", "Cost", True, ("purchase cost", "original cost", "التكلفة")),
                   F("accumulated", "Accumulated depreciation", aliases=("accumulated depreciation", "acc dep",
                                                                        "مجمع الإهلاك")),
                   F("salvage", "Salvage value", aliases=("residual value", "scrap value", "القيمة المتبقية")),
                   F("life_months", "Useful life (months)", aliases=("life", "useful life", "life months",
                                                                      "العمر الإنتاجي")),
                   F("method", "Method", aliases=("depreciation method",)),
                   F("serial_no", "Serial number", aliases=("serial", "الرقم التسلسلي")),
                   F("location", "Location", aliases=("الموقع",)),
                   F("custodian", "Custodian", aliases=("employee", "assigned to", "العهدة"))],
    }),
])

GROUPS = [("master", "Master data"), ("fresh", "Start from a cut-off date"), ("history", "Full history")]


def available_types(has):
    return OrderedDict((k, v) for k, v in TYPES.items() if not v.get("modules") or any(has(m) for m in v["modules"]))


# ---------------------------------------------------------------- reading files

def norm(text):
    text = str(text or "").translate(DIGITS).strip().lower().replace("_", " ").replace("*", "")
    return re.sub(r"\s+", " ", text).strip(" .:#")


def _cell(v):
    if v is None:
        return ""
    if isinstance(v, datetime):
        return v.date().isoformat() if not (v.hour or v.minute or v.second) else v.isoformat(sep=" ")
    if isinstance(v, date):
        return v.isoformat()
    if isinstance(v, float) and v.is_integer() and abs(v) < 1e15:
        return str(int(v))
    return str(v).strip()


def decode(raw):
    for enc in ("utf-8-sig", "cp1256", "latin-1"):
        try:
            return raw.decode(enc)
        except UnicodeDecodeError:
            continue
    return raw.decode("utf-8", "replace")


class Row(list):
    """Cell values of one data row; .line is its row number in the file."""
    def __init__(self, cells, line):
        super().__init__(cells)
        self.line = line


def read_file(path, header_row=1, sheet=None):
    """(header, rows, sheet names): rows are Row lists of text values padded to the header length."""
    if path.lower().endswith((".xlsx", ".xlsm")):
        import openpyxl
        try:
            wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
        except Exception:
            raise AccountingError(t("The file could not be read as an Excel workbook."))
        sheets = wb.sheetnames
        ws = wb[sheet] if sheet in sheets else wb.worksheets[0]
        raw_rows = [[_cell(c) for c in r] for r in ws.iter_rows(values_only=True)]
        wb.close()
    else:
        with open(path, "rb") as fh:
            text = decode(fh.read())
        sheets = []
        try:
            dialect = csv.Sniffer().sniff(text[:8192], delimiters=",;\t|")
        except csv.Error:
            dialect = csv.excel
        raw_rows = [[c.strip() for c in r] for r in csv.reader(io.StringIO(text), dialect)]
    numbered = list(enumerate(raw_rows, 1))[max(int(header_row or 1), 1) - 1:]
    while numbered and not any(numbered[0][1]):
        numbered.pop(0)
    if not numbered:
        raise AccountingError(t("The file is empty."))
    header = numbered[0][1]
    while header and not header[-1]:
        header = header[:-1]
    header = [h or f"{t('Column')} {i + 1}" for i, h in enumerate(header)]
    n = len(header)
    rows = []
    for line, r in numbered[1:]:
        r = (r + [""] * n)[:n]
        if any(c for c in r):
            rows.append(Row(r, line))
    return header, rows, sheets


def auto_map(kind, header):
    """{field key: column index} guessed from the column names (English or Arabic)."""
    names = [norm(h) for h in header]
    out = {}
    used = set()
    for f in TYPES[kind]["fields"]:
        candidates = [norm(f["key"]), norm(f["label"]), norm(t(f["label"]))] + [norm(a) for a in f["aliases"]]
        for c in candidates:
            if c in names and names.index(c) not in used:
                out[f["key"]] = names.index(c)
                used.add(out[f["key"]])
                break
    return out


def mapped_rows(kind, rows, mapping):
    """[(row number in the file, {field: text})]."""
    return [(getattr(r, "line", i + 2), {k: r[c] if c < len(r) else "" for k, c in mapping.items()})
            for i, r in enumerate(rows)]


# ---------------------------------------------------------------- values

def parse_amount(text):
    """Mils from text like '1,234.50', '(1,234.50)', '1234.50-', 'SAR 1,234.50', '١٬٢٣٤٫٥٠'."""
    s = str(text or "").translate(DIGITS).strip()
    if not s:
        return 0
    neg = False
    if s.startswith("(") and s.endswith(")"):
        neg, s = True, s[1:-1]
    if s.endswith("-"):
        neg, s = True, s[:-1]
    upper = s.upper()
    if upper.endswith(" CR"):
        neg, s = not neg, s[:-3]
    elif upper.endswith(" DR"):
        s = s[:-3]
    s = re.sub(r"[^\d.,\-eE]", "", s)
    if s.count(",") and not s.count(".") and re.fullmatch(r"-?\d+,\d{1,3}", s) and len(s.split(",")[1]) != 3:
        s = s.replace(",", ".")  # decimal comma: 12,5
    try:
        v = to_int(s.replace(",", ""))
    except ValueError:
        raise RowError(t("Invalid amount") + f": {text}")
    return -v if neg else v


def parse_number(text, default=None):
    s = str(text or "").translate(DIGITS).strip().replace(",", "")
    if not s:
        return default
    try:
        return float(s)
    except ValueError:
        raise RowError(t("Invalid number") + f": {text}")


def parse_date(text, fmt="DD/MM/YYYY"):
    s = str(text or "").translate(DIGITS).strip()
    if not s:
        return None
    if re.fullmatch(r"\d{4,5}(\.0+)?", s) and 20000 < float(s) < 80000:  # Excel serial day number
        return (date(1899, 12, 30) + timedelta(days=int(float(s)))).isoformat()
    s = s.split(" ")[0].split("T")[0]
    for f in [DATE_FORMATS.get(fmt, "%d/%m/%Y"), "%Y-%m-%d", "%Y/%m/%d"] + list(DATE_FORMATS.values()):
        try:
            return datetime.strptime(s, f).date().isoformat()
        except ValueError:
            continue
    raise RowError(t("Invalid date") + f": {text} ({fmt})")


def parse_bool(text):
    return norm(text) in ("1", "yes", "y", "true", "x", "نعم", "✓")


KIND_WORDS = {
    "bank": "asset_cash", "cash": "asset_cash", "bank and cash": "asset_cash", "cash and bank": "asset_cash",
    "receivable": "asset_receivable", "receivables": "asset_receivable", "accounts receivable": "asset_receivable",
    "debtors": "asset_receivable", "asset": "asset_current", "assets": "asset_current",
    "current asset": "asset_current", "current assets": "asset_current", "inventory": "asset_current",
    "prepayment": "asset_prepayments", "prepayments": "asset_prepayments", "prepaid": "asset_prepayments",
    "fixed asset": "asset_fixed", "fixed assets": "asset_fixed", "property plant and equipment": "asset_fixed",
    "non current asset": "asset_non_current", "non-current asset": "asset_non_current",
    "non-current assets": "asset_non_current", "payable": "liability_payable", "payables": "liability_payable",
    "accounts payable": "liability_payable", "creditors": "liability_payable", "liability": "liability_current",
    "liabilities": "liability_current", "current liability": "liability_current",
    "current liabilities": "liability_current", "non current liability": "liability_non_current",
    "non-current liability": "liability_non_current", "non-current liabilities": "liability_non_current",
    "long term liability": "liability_non_current", "equity": "equity", "capital": "equity",
    "income": "income", "revenue": "income", "sales": "income", "other income": "income_other",
    "cost of sales": "expense_direct_cost", "cost of goods sold": "expense_direct_cost", "cogs": "expense_direct_cost",
    "direct cost": "expense_direct_cost", "cost of revenue": "expense_direct_cost", "expense": "expense",
    "expenses": "expense", "depreciation": "expense_depreciation",
    "أصول": "asset_current", "الأصول": "asset_current", "بنك": "asset_cash", "نقدية": "asset_cash",
    "ذمم مدينة": "asset_receivable", "مدينون": "asset_receivable", "أصول ثابتة": "asset_fixed",
    "خصوم": "liability_current", "التزامات": "liability_current", "ذمم دائنة": "liability_payable",
    "دائنون": "liability_payable", "حقوق الملكية": "equity", "رأس المال": "equity", "إيرادات": "income",
    "مبيعات": "income", "إيرادات أخرى": "income_other", "تكلفة المبيعات": "expense_direct_cost",
    "مصروفات": "expense", "مصاريف": "expense", "إهلاك": "expense_depreciation",
}
for _k, _label, _root in ACCOUNT_KINDS:
    KIND_WORDS.setdefault(_k.replace("_", " "), _k)
    KIND_WORDS.setdefault(norm(_label), _k)
CODE_KINDS = {"1": "asset_current", "2": "liability_current", "3": "equity", "4": "income",
              "5": "expense_direct_cost", "6": "expense", "7": "expense", "8": "expense", "9": "expense"}

INVOICE_WORDS = {
    "out_invoice": ("invoice", "customer invoice", "sales invoice", "sale", "sales", "tax invoice", "inv",
                    "فاتورة", "فاتورة مبيعات", "فاتورة عميل"),
    "in_invoice": ("bill", "vendor bill", "supplier invoice", "purchase invoice", "purchase", "purchases",
                   "فاتورة مورد", "فاتورة مشتريات"),
    "out_refund": ("credit note", "customer credit note", "sales return", "return", "إشعار دائن", "مرتجع مبيعات"),
    "in_refund": ("vendor refund", "debit note", "supplier credit note", "purchase return", "إشعار مدين",
                  "مرتجع مشتريات"),
}
PAYMENT_WORDS = {
    ("customer", "inbound"): ("receipt", "customer receipt", "received", "collection", "in", "inbound",
                              "سند قبض", "قبض"),
    ("vendor", "outbound"): ("payment", "vendor payment", "paid", "out", "outbound", "سند صرف", "صرف"),
    ("customer", "outbound"): ("customer refund", "refund to customer", "استرداد عميل"),
    ("vendor", "inbound"): ("vendor refund", "refund from vendor", "استرداد مورد"),
}


# ---------------------------------------------------------------- context

class Ctx:
    def __init__(self, kind, options, user_id):
        self.kind = kind
        self.opt = options
        self.user_id = user_id
        self.date_format = options.get("date_format") or "DD/MM/YYYY"
        self.errors = []
        self.notes = {}
        self.created = 0
        self.updated = 0
        self.skipped = 0
        self.records = []
        self.preview = []
        self.cache = {}
        self.clearing = (q("SELECT id FROM accounts WHERE code = ?", (CLEARING_CODE,), one=True) or {"id": None})["id"]

    def note(self, text, n=1):
        self.notes[text] = self.notes.get(text, 0) + n

    def record(self, model, rid, ext_key=None):
        self.records.append((model, rid, ext_key))

    def show(self, *cols):
        if len(self.preview) < 15:
            self.preview.append(cols)

    def date(self, text, required=True, what="Date"):
        d = parse_date(text, self.date_format)
        if required and not d:
            raise RowError(t(what) + ": " + t("is required"))
        return d

    def cutoff(self):
        d = self.opt.get("cutoff")
        if not d:
            raise AccountingError(t("Choose the cut-off date."))
        return d

    # lookups, cached per import ------------------------------------------------
    def _cached(self, key, fn):
        if key not in self.cache:
            self.cache[key] = fn()
        return self.cache[key]

    def branch(self, text):
        text = (text or "").strip()
        if not text:
            bid = self.opt.get("branch_id")
            if not bid:
                raise RowError(t("Branch") + ": " + t("is required"))
            return int(bid)

        def find():
            n = norm(text)
            for b in q("SELECT * FROM branches"):
                if n in (norm(b["code"]), norm(b["name_en"]), norm(b["name_ar"])):
                    return b["id"]
            return None
        bid = self._cached(("branch", norm(text)), find)
        if not bid:
            raise RowError(t("Unknown branch") + f": {text}")
        allowed = self.opt.get("allowed_branches")
        if allowed is not None and bid not in allowed:
            raise RowError(t("You do not have access to this branch.") + f" ({text})")
        return bid

    def account(self, text, required=True):
        text = (text or "").strip()
        if not text:
            if required:
                raise RowError(t("Account") + ": " + t("is required"))
            return None

        def find():
            r = q("SELECT id FROM accounts WHERE code = ?", (text,), one=True)
            if not r and " " in text:  # "1010 Cash on hand" or "1010 - Cash"
                r = q("SELECT id FROM accounts WHERE code = ?", (text.split(" ")[0].strip(" -"),), one=True)
            if not r:
                r = q("SELECT id FROM accounts WHERE lower(name_en) = ? OR name_ar = ?", (text.lower(), text), one=True)
            return r["id"] if r else None
        aid = self._cached(("account", text), find)
        if not aid:
            raise RowError(t("Unknown account") + f": {text}")
        return aid

    def partner(self, text, role=None, row=None, required=True):
        text = (text or "").strip()
        if not text:
            if required:
                raise RowError(t("Partner") + ": " + t("is required"))
            return None
        key = ("partner", text.lower())
        if key not in self.cache:
            r = q("SELECT id FROM partners WHERE lower(trim(name_en)) = ? OR trim(name_ar) = ? OR vat_no = ? "
                  "ORDER BY active DESC, id LIMIT 1", (text.lower(), text, text), one=True)
            self.cache[key] = r["id"] if r else None
        pid = self.cache[key]
        if not pid:
            if not self.opt.get("create_partners"):
                raise RowError(t("Unknown partner") + f": {text}")
            arabic = bool(re.search(r"[؀-ۿ]", text))
            pid = x("INSERT INTO partners (name_en, name_ar, is_customer, is_vendor) VALUES (?,?,?,?)",
                    (text, text if arabic else None, 1 if role == "customer" else 0, 1 if role == "vendor" else 0))
            self.cache[key] = pid
            self.record("partner", pid)
            self.note("New partners created", 1)
        elif role:
            x(f"UPDATE partners SET is_{role} = 1 WHERE id = ? AND is_{role} = 0", (pid,))
        return pid

    def product(self, text, required=True):
        text = (text or "").strip()
        if not text:
            if required:
                raise RowError(t("Product") + ": " + t("is required"))
            return None

        def find():
            r = q("SELECT id FROM products WHERE code = ? OR barcode = ? OR lower(name_en) = ? OR name_ar = ? "
                  "ORDER BY code = ? DESC LIMIT 1", (text, text, text.lower(), text, text), one=True)
            return r["id"] if r else None
        pid = self._cached(("product", text), find)
        if not pid:
            raise RowError(t("Unknown product") + f": {text}")
        return q("SELECT * FROM products WHERE id = ?", (pid,), one=True)

    def tax(self, text, scope):
        text = (text or "").strip()
        if not text:
            return None

        def find():
            s = text.translate(DIGITS).replace("%", "").strip()
            try:
                rate = float(s)
                if 0 < rate < 1:
                    rate *= 100
                r = q("SELECT id FROM taxes WHERE scope = ? AND abs(rate - ?) < 0.001 AND active = 1 "
                      "ORDER BY id LIMIT 1", (scope, rate), one=True)
            except ValueError:
                r = q("SELECT id FROM taxes WHERE scope = ? AND (lower(name_en) = ? OR name_ar = ?) LIMIT 1",
                      (scope, text.lower(), text), one=True)
            return r["id"] if r else None
        tid = self._cached(("tax", scope, text), find)
        if not tid:
            raise RowError(t("Unknown tax") + f": {text}")
        return tid

    def journal(self, branch_id, text, types, default_codes):
        text = (text or "").strip()

        def find():
            if text:
                r = q(f"SELECT id FROM journals WHERE branch_id = ? AND (upper(code) = ? OR lower(name_en) = ? "
                      f"OR name_ar = ?) AND type IN ({placeholders(types)})",
                      [branch_id, text.upper(), text.lower(), text] + list(types), one=True)
            else:
                r = q(f"SELECT id FROM journals WHERE branch_id = ? AND active = 1 AND type IN ({placeholders(types)}) "
                      f"ORDER BY code IN ({placeholders(default_codes)}) DESC, id LIMIT 1",
                      [branch_id] + list(types) + list(default_codes), one=True)
            return r["id"] if r else None
        jid = self._cached(("journal", branch_id, text, tuple(types)), find)
        if not jid:
            raise RowError(t("Unknown journal") + f": {text or '/'.join(default_codes)}")
        return jid

    def imported(self, model, ext_key):
        """Batch number if a document with this key was imported before (and not undone)."""
        r = q("SELECT r.batch_id FROM import_records r JOIN import_batches b ON b.id = r.batch_id "
              "WHERE r.model = ? AND r.ext_key = ? AND b.state = 'done' LIMIT 1", (model, ext_key), one=True)
        return r["batch_id"] if r else None

    def skip_if_imported(self, model, ext_key, label):
        batch = self.imported(model, ext_key)
        if batch:
            if self.opt.get("skip_existing"):
                self.skipped += 1
                return True
            raise RowError(f"{label}: " + t("already imported in import") + f" #{batch}")
        return False


def lines_amount(v, row_label=""):
    """Debit/credit mils from a row with debit, credit or a signed amount."""
    debit, credit = parse_amount(v.get("debit")), parse_amount(v.get("credit"))
    if not debit and not credit and v.get("amount"):
        a = parse_amount(v.get("amount"))
        debit, credit = (a, 0) if a > 0 else (0, -a)
    if debit < 0:
        debit, credit = 0, credit - debit
    if credit < 0:
        debit, credit = debit - credit, 0
    net = debit - credit
    return (net, 0) if net >= 0 else (0, -net)


# ---------------------------------------------------------------- handlers

def h_accounts(ctx, row_no, v):
    code = v["code"].strip()
    if not code or not v.get("name_en", "").strip():
        raise RowError(t("Code and English name are required."))
    kind = None
    if v.get("kind", "").strip():
        kind = KIND_WORDS.get(norm(v["kind"]))
        if not kind:
            raise RowError(t("Unknown account type") + f": {v['kind']}")
    existing = q("SELECT * FROM accounts WHERE code = ?", (code,), one=True)
    if existing:
        if not ctx.opt.get("update_existing"):
            ctx.skipped += 1
            return
        x("UPDATE accounts SET name_en = ?, name_ar = COALESCE(?, name_ar), kind = COALESCE(?, kind), "
          "intercompany = CASE WHEN ? = '' THEN intercompany ELSE ? END WHERE id = ?",
          (v["name_en"].strip(), v.get("name_ar", "").strip() or None, kind, v.get("intercompany", "").strip(),
           1 if parse_bool(v.get("intercompany")) else 0, existing["id"]))
        ctx.updated += 1
        ctx.show(code, v["name_en"], t("Update"))
        return
    if not kind:
        kind = CODE_KINDS.get(code[:1])
        if not kind:
            raise RowError(t("Type") + ": " + t("is required"))
        ctx.note("Account types guessed from the code")
    aid = x("INSERT INTO accounts (code, name_en, name_ar, kind, intercompany) VALUES (?,?,?,?,?)",
            (code, v["name_en"].strip(), v.get("name_ar", "").strip() or None, kind,
             1 if parse_bool(v.get("intercompany")) else 0))
    ctx.record("account", aid)
    ctx.created += 1
    ctx.show(code, v["name_en"], t(dict((k, lb) for k, lb, _ in ACCOUNT_KINDS)[kind]))


def _role(text, default):
    n = norm(text)
    if not n:
        return default
    if n in ("both", "customer and vendor", "customer/vendor", "كلاهما"):
        return "both"
    if n in ("customer", "client", "debtor", "عميل"):
        return "customer"
    if n in ("vendor", "supplier", "creditor", "مورد"):
        return "vendor"
    raise RowError(t("Unknown partner type") + f": {text}")


def h_partners(ctx, row_no, v):
    name = v["name_en"].strip()
    if not name:
        raise RowError(t("Name (English)") + ": " + t("is required"))
    role = _role(v.get("role"), ctx.opt.get("role") or "customer")
    flags = {"is_customer": 1 if role in ("customer", "both") else 0, "is_vendor": 1 if role in ("vendor", "both") else 0}
    vals = {}
    for k in ("name_ar", "vat_no", "cr_no", "phone", "email", "address", "contact_person", "bank_name", "iban"):
        if (v.get(k) or "").strip():
            vals[k] = v[k].strip()
    if (v.get("payment_terms") or "").strip():
        vals["payment_terms"] = int(parse_number(v["payment_terms"], 0))
    existing = None
    if vals.get("vat_no"):
        existing = q("SELECT * FROM partners WHERE vat_no = ?", (vals["vat_no"],), one=True)
    existing = existing or q("SELECT * FROM partners WHERE lower(trim(name_en)) = ?", (name.lower(),), one=True)
    if existing:
        if not ctx.opt.get("update_existing"):
            ctx.skipped += 1
            return
        vals.update({k: 1 for k, f in flags.items() if f})
        vals["name_en"] = name
        x(f"UPDATE partners SET {', '.join(k + ' = ?' for k in vals)} WHERE id = ?", list(vals.values()) + [existing["id"]])
        ctx.updated += 1
        ctx.show(name, t("Update"))
        return
    vals.update(flags, name_en=name)
    pid = x(f"INSERT INTO partners ({', '.join(vals)}) VALUES ({placeholders(vals)})", list(vals.values()))
    ctx.record("partner", pid)
    ctx.created += 1
    ctx.show(name, vals.get("vat_no", ""), t({"both": "Customer and vendor", "customer": "Customer",
                                              "vendor": "Vendor"}[role]))


PRODUCT_TYPES = {"stock": "stock", "stockable": "stock", "storable": "stock", "inventory": "stock", "goods": "stock",
                 "item": "stock", "product": "stock", "مخزني": "stock", "service": "service", "services": "service",
                 "خدمة": "service", "consumable": "consumable", "non-stock": "consumable", "non stock": "consumable",
                 "مستهلك": "consumable"}


def h_products(ctx, row_no, v):
    name = v["name_en"].strip()
    if not name:
        raise RowError(t("Name (English)") + ": " + t("is required"))
    vals = {"name_en": name}
    if v.get("type", "").strip():
        ptype = PRODUCT_TYPES.get(norm(v["type"]))
        if not ptype:
            raise RowError(t("Unknown product type") + f": {v['type']}")
        vals["type"] = ptype
    for k in ("code", "name_ar", "category", "uom", "barcode"):
        if (v.get(k) or "").strip():
            vals[k] = v[k].strip()
    if (v.get("sale_price") or "").strip():
        vals["sale_price"] = parse_amount(v["sale_price"])
    if (v.get("cost") or "").strip():
        vals["purchase_price"] = parse_amount(v["cost"])
    for field, col, scope in (("sale_tax", "sale_tax_id", "sale"), ("purchase_tax", "purchase_tax_id", "purchase")):
        if field in ctx.opt["mapped"]:
            vals[col] = ctx.tax(v.get(field), scope)
    for field, col in (("income_account", "income_account_id"), ("expense_account", "expense_account_id")):
        if (v.get(field) or "").strip():
            vals[col] = ctx.account(v[field])
    cat = None
    if vals.get("category"):
        cat = q("SELECT * FROM product_categories WHERE lower(name_en) = ? OR name_ar = ?",
                (vals["category"].lower(), vals["category"]), one=True)
        if not cat:
            cid = x("INSERT INTO product_categories (name_en) VALUES (?)", (vals["category"],))
            cat = q("SELECT * FROM product_categories WHERE id = ?", (cid,), one=True)
        vals["category_id"] = cat["id"]
        vals["category"] = cat["name_en"]
    existing = None
    if vals.get("code"):
        existing = q("SELECT * FROM products WHERE code = ?", (vals["code"],), one=True)
    else:
        existing = q("SELECT * FROM products WHERE lower(name_en) = ?", (name.lower(),), one=True)
    if existing:
        if not ctx.opt.get("update_existing"):
            ctx.skipped += 1
            return
        from .stock import inventory_account_map, reclass_inventory
        before = inventory_account_map()
        x(f"UPDATE products SET {', '.join(k + ' = ?' for k in vals)} WHERE id = ?", list(vals.values()) + [existing["id"]])
        reclass_inventory(before, ctx.user_id)
        ctx.updated += 1
        ctx.show(vals.get("code", ""), name, t("Update"))
        return
    vals.setdefault("type", "stock")
    if cat:
        vals.setdefault("income_account_id", cat["income_account_id"])
        vals.setdefault("expense_account_id", cat["cogs_account_id"])
    for col, scope in (("sale_tax_id", "sale"), ("purchase_tax_id", "purchase")):
        if col not in vals:
            std = q("SELECT id FROM taxes WHERE scope = ? AND active = 1 ORDER BY rate DESC LIMIT 1", (scope,), one=True)
            vals[col] = std["id"] if std else None
    pid = x(f"INSERT INTO products ({', '.join(vals)}) VALUES ({placeholders(vals)})", list(vals.values()))
    ctx.record("product", pid)
    ctx.created += 1
    ctx.show(vals.get("code", ""), name, vals["type"])


def redirect_accounts(ctx):
    """Control accounts whose opening balance goes to the clearing account (their detail is imported separately)."""
    c = company()
    out = {}
    if ctx.opt.get("redirect_ar"):
        accs = {c["receivable_account_id"], c["payable_account_id"]}
        for r in q("SELECT receivable_account_id, payable_account_id FROM partners"):
            accs.update(r)
        for a in accs:
            if a:
                out[a] = "Receivable and payable"
    if ctx.opt.get("redirect_stock"):
        from .stock import inventory_account_ids
        for a in inventory_account_ids():
            out[a] = "Inventory"
    if ctx.opt.get("redirect_assets"):
        for r in q("SELECT asset_account_id, depreciation_account_id FROM asset_categories"):
            for a in r:
                if a:
                    out[a] = "Fixed assets"
    return out


def g_opening(ctx, branch_id, rows):
    d = ctx.cutoff()
    b = q("SELECT * FROM branches WHERE id = ?", (branch_id,), one=True)
    batch = ctx.imported("opening", str(branch_id))
    if batch:
        raise RowError(f"{b['code']}: " + t("opening balances were already imported in import") + f" #{batch}. "
                       + t("Undo that import first."))
    redirect = redirect_accounts(ctx)
    if redirect and not ctx.clearing:
        raise RowError(t("Missing account") + f" {CLEARING_CODE}")
    lines = []
    errors = []
    for row_no, v in rows:
        try:
            debit, credit = lines_amount(v)
            if not debit and not credit:
                continue
            acc = ctx.account(v.get("account"))
            partner = ctx.partner(v.get("partner"), required=False) if (v.get("partner") or "").strip() else None
            label = (v.get("label") or "").strip() or t("Opening balance")
            if acc in redirect:
                code = q("SELECT code FROM accounts WHERE id = ?", (acc,), one=True)["code"]
                ctx.note(f"{t(redirect[acc])}: " + t("moved to the opening balance clearing account"))
                label = f"{code} · {label}"
                acc = ctx.clearing
            lines.append({"account_id": acc, "partner_id": partner, "label": label, "debit": debit, "credit": credit})
        except (RowError, AccountingError) as e:
            errors.append((row_no, str(e)))
    if errors:
        raise GroupErrors(errors)
    diff = sum(ln["debit"] - ln["credit"] for ln in lines)
    if diff:
        plug = ctx.opt.get("difference_account")
        if not plug:
            raise RowError(f"{b['code']}: " + t("The entry is not balanced.") + f" ({t('difference')} {diff / 1000:,.3f}). "
                           + t("Choose an account for the difference, or correct the file."))
        lines.append({"account_id": int(plug), "label": t("Opening balance difference"),
                      "debit": max(-diff, 0), "credit": max(diff, 0)})
        ctx.note(f"{b['code']}: " + t("difference posted") + f" {diff / 1000:,.3f}")
    if not lines:
        return
    jid = ctx.journal(branch_id, ctx.opt.get("journal"), ("general",), ("MISC",))
    mid = create_move(branch_id, jid, d, lines, user_id=ctx.user_id, ref=t("Opening balances"),
                      narration=t("Imported opening balances"), move_type="entry", post=True)
    ctx.record("move", mid)
    ctx.record("opening", mid, str(branch_id))
    ctx.created += 1
    ctx.show(b["code"], d, len(lines), sum(ln["debit"] for ln in lines) / 1000.0)


class GroupErrors(Exception):
    def __init__(self, errors):
        super().__init__("")
        self.errors = errors


def _invoice_kind(ctx, text, amount_sign=1):
    n = norm(text)
    kind = None
    if n:
        for k, words in INVOICE_WORDS.items():
            if n in words or n == k or n == norm(t(INVOICE_KINDS[k]["label"])):
                kind = k
                break
        if not kind:
            raise RowError(t("Unknown document type") + f": {text}")
    kind = kind or ctx.opt.get("doc_kind") or "out_invoice"
    if amount_sign < 0:
        kind = {"out_invoice": "out_refund", "in_invoice": "in_refund", "out_refund": "out_invoice",
                "in_refund": "in_invoice"}[kind]
    return kind


def _doc_journal(ctx, branch_id, kind):
    jtype = "sale" if kind.startswith("out") else "purchase"
    return ctx.journal(branch_id, "", (jtype,), ("INV",) if jtype == "sale" else ("BILL",))


def _number_taken(branch_id, number):
    return q("SELECT id FROM invoices WHERE branch_id = ? AND number = ?", (branch_id, number), one=True)


def h_open_items(ctx, row_no, v):
    number = v["number"].strip()
    if not number:
        raise RowError(t("Number") + ": " + t("is required"))
    branch_id = ctx.branch(v.get("branch"))
    amount = parse_amount(v["amount"])
    if not amount:
        ctx.skipped += 1
        return
    kind = _invoice_kind(ctx, v.get("type"), 1 if amount > 0 else -1)
    amount = abs(amount)
    key = f"{branch_id}:{number}"
    if ctx.skip_if_imported("invoice", key, number):
        return
    if _number_taken(branch_id, number):
        raise RowError(f"{number}: " + t("a document with this number already exists in the branch"))
    if not ctx.clearing:
        raise RowError(t("Missing account") + f" {CLEARING_CODE}")
    role = INVOICE_KINDS[kind]["partner_type"]
    partner = ctx.partner(v.get("partner"), role)
    d = ctx.date(v.get("date"))
    due = ctx.date(v.get("due_date"), required=False) or d
    jid = _doc_journal(ctx, branch_id, kind)
    total = (v.get("total") or "").strip()
    notes = t("Imported open balance") + (f" — {t('original total')} {total}" if total else "")
    _check_ref(partner, kind, v.get("ref"), number)
    iid = x("INSERT INTO invoices (branch_id, journal_id, kind, number, partner_id, date, due_date, ref, notes, state, "
            "amount_untaxed, amount_tax, amount_total, created_by, created_at) VALUES (?,?,?,?,?,?,?,?,?,'draft',?,0,?,?,?)",
            (branch_id, jid, kind, number, partner, d, due, (v.get("ref") or "").strip() or None, notes, amount,
             amount, ctx.user_id, now()))
    x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, subtotal, "
      "tax_amount) VALUES (?,?,?,?,1,?,?,0)", (iid, 1, f"{t('Opening balance')} {number}", ctx.clearing, amount, amount))
    post_invoice(iid, ctx.user_id, credit_check=False)
    ctx.record("invoice", iid, key)
    ctx.created += 1
    ctx.show(number, t(INVOICE_KINDS[kind]["label"]), d, v.get("partner"), amount / 1000.0)


def g_entries(ctx, key, rows):
    first = rows[0][1]
    branch_id = ctx.branch(first.get("branch"))
    ext = f"{branch_id}:{key}"
    if ctx.skip_if_imported("entry", ext, key):
        return
    d = ctx.date(first.get("date"))
    jid = ctx.journal(branch_id, first.get("journal"), ("general", "bank", "cash", "sale", "purchase"), ("MISC",))
    lines, errors = [], []
    for row_no, v in rows:
        try:
            if ctx.date(v.get("date")) != d:
                raise RowError(t("All lines of an entry need the same date."))
            if (v.get("branch") or "").strip() and ctx.branch(v.get("branch")) != branch_id:
                raise RowError(t("All lines of an entry need the same branch."))
            debit, credit = lines_amount(v)
            if not debit and not credit:
                continue
            partner = ctx.partner(v.get("partner"), required=False) if (v.get("partner") or "").strip() else None
            lines.append({"account_id": ctx.account(v.get("account")), "partner_id": partner,
                          "label": (v.get("label") or "").strip(), "debit": debit, "credit": credit})
        except (RowError, AccountingError) as e:
            errors.append((row_no, str(e)))
    if errors:
        raise GroupErrors(errors)
    if not lines:
        ctx.skipped += 1
        return
    try:
        mid = create_move(branch_id, jid, d, lines, user_id=ctx.user_id, ref=(first.get("ref") or "").strip() or key,
                          narration=t("Imported entry") + f" {key}", move_type="entry", post=True)
    except AccountingError as e:
        raise RowError(f"{t('Entry')} {key}: {e}")
    ctx.record("move", mid, ext)
    ctx.created += 1
    ctx.show(key, d, len(lines), sum(ln["debit"] for ln in lines) / 1000.0)


def g_invoices(ctx, number, rows):
    first = rows[0][1]
    branch_id = ctx.branch(first.get("branch"))
    kind = _invoice_kind(ctx, first.get("type"))
    ext = f"{branch_id}:{number}"
    if ctx.skip_if_imported("invoice", ext, number):
        return
    if _number_taken(branch_id, number):
        raise RowError(f"{number}: " + t("a document with this number already exists in the branch"))
    role = INVOICE_KINDS[kind]["partner_type"]
    scope = "sale" if role == "customer" else "purchase"
    partner = ctx.partner(first.get("partner"), role)
    d = ctx.date(first.get("date"))
    due = ctx.date(first.get("due_date"), required=False) or d
    jid = _doc_journal(ctx, branch_id, kind)
    journal_acc = q("SELECT default_account_id FROM journals WHERE id = ?", (jid,), one=True)[0]
    dec = branch_decimals(branch_id)
    lines, errors = [], []
    for row_no, v in rows:
        try:
            prod = ctx.product(v.get("product"), required=False) if (v.get("product") or "").strip() else None
            acc = ctx.account(v.get("account"), required=False)
            if not acc and prod:
                acc = prod["income_account_id"] if scope == "sale" else prod["expense_account_id"]
            acc = acc or journal_acc
            if not acc:
                raise RowError(t("Account") + ": " + t("is required"))
            qty = parse_number(v.get("quantity"), 1.0)
            price = parse_amount(v.get("price"))
            if "tax" in ctx.opt["mapped"]:
                tax_id = ctx.tax(v.get("tax"), scope)
            else:
                tax_id = prod[f"{scope}_tax_id"] if prod else None
            rate = q("SELECT rate FROM taxes WHERE id = ?", (tax_id,), one=True)["rate"] if tax_id else 0
            sub, tax = compute_invoice_line(qty, price, rate, dec)
            desc = (v.get("description") or "").strip() or (nm(prod) if prod else number)
            lines.append((desc, acc, qty, price, tax_id, sub, tax, prod["id"] if prod else None))
        except (RowError, AccountingError) as e:
            errors.append((row_no, str(e)))
    if errors:
        raise GroupErrors(errors)
    untaxed, taxed = sum(ln[5] for ln in lines), sum(ln[6] for ln in lines)
    total = untaxed + taxed
    if total < 0:
        raise RowError(f"{number}: " + t("The document total must be greater than zero."))
    expected = (first.get("total") or "").strip()
    if expected and abs(parse_amount(expected) - total) > 10 ** (3 - dec):
        raise RowError(f"{number}: " + t("computed total") + f" {total / 1000:,.{dec}f} ≠ {t('file total')} {expected}")
    _check_ref(partner, kind, first.get("ref"), number)
    iid = x("INSERT INTO invoices (branch_id, journal_id, kind, number, partner_id, date, due_date, ref, notes, state, "
            "amount_untaxed, amount_tax, amount_total, created_by, created_at) VALUES (?,?,?,?,?,?,?,?,?,'draft',?,?,?,?,?)",
            (branch_id, jid, kind, number, partner, d, due, (first.get("ref") or "").strip() or None,
             t("Imported"), untaxed, taxed, total, ctx.user_id, now()))
    for i, (desc, acc, qty, price, tax_id, sub, tax, prod_id) in enumerate(lines):
        x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, "
          "subtotal, tax_amount, product_id) VALUES (?,?,?,?,?,?,?,?,?,?)",
          (iid, i, desc, acc, qty, price, tax_id, sub, tax, prod_id))
    try:
        post_invoice(iid, ctx.user_id, credit_check=False)
    except AccountingError as e:
        raise RowError(f"{number}: {e}")
    ctx.record("invoice", iid, ext)
    ctx.created += 1
    ctx.show(number, t(INVOICE_KINDS[kind]["label"]), d, first.get("partner"), total / 1000.0)


def h_payments(ctx, row_no, v):
    branch_id = ctx.branch(v.get("branch"))
    d = ctx.date(v.get("date"))
    amount = parse_amount(v.get("amount"))
    if amount < 0:
        amount = -amount
    if not amount:
        raise RowError(t("The amount must be greater than zero."))
    inv = None
    number = (v.get("invoice") or "").strip()
    if number:
        inv = q("SELECT * FROM invoices WHERE branch_id = ? AND number = ? AND state = 'posted'", (branch_id, number),
                one=True)
        if not inv:
            raise RowError(t("Unknown invoice") + f": {number}")
    n = norm(v.get("type"))
    direction = None
    if n:
        for k, words in PAYMENT_WORDS.items():
            if n in words:
                direction = k
        if not direction:
            raise RowError(t("Unknown payment type") + f": {v.get('type')}")
    if inv:
        ptype = INVOICE_KINDS[inv["kind"]]["partner_type"]
        inbound = (ptype == "customer") == (INVOICE_KINDS[inv["kind"]]["sign"] > 0)
        direction = (ptype, "inbound" if inbound else "outbound")
    direction = direction or ((ctx.opt.get("role") or "customer"),
                              "inbound" if (ctx.opt.get("role") or "customer") == "customer" else "outbound")
    partner = ctx.partner(v.get("partner"), direction[0])
    if inv and inv["partner_id"] != partner:
        raise RowError(f"{number}: " + t("the invoice belongs to another partner"))
    ref = (v.get("ref") or "").strip()
    ext = f"{branch_id}:{d}:{partner}:{amount}:{number}:{ref}"
    if ctx.skip_if_imported("payment", ext, ref or number or d):
        return
    jid = ctx.journal(branch_id, v.get("journal"), ("bank", "cash"), ("BNK",))
    pid = create_payment({"branch_id": branch_id, "journal_id": jid, "partner_id": partner, "partner_type": direction[0],
                          "payment_type": direction[1], "date": d, "amount": amount, "ref": ref or number,
                          "invoice_id": inv["id"] if inv else None}, ctx.user_id)
    ctx.record("payment", pid, ext)
    ctx.created += 1
    ctx.show(d, v.get("partner"), t("Receipt") if direction[1] == "inbound" else t("Payment"), number, amount / 1000.0)


def _warehouse(ctx, text, branch_text):
    text = (text or "").strip()
    if text:
        def find():
            n = norm(text)
            for w in q("SELECT * FROM warehouses"):
                if n in (norm(w["code"]), norm(w["name_en"]), norm(w["name_ar"])):
                    return w["id"]
            return None
        wid = ctx._cached(("warehouse", norm(text)), find)
        if not wid:
            raise RowError(t("Unknown warehouse") + f": {text}")
        w = q("SELECT * FROM warehouses WHERE id = ?", (wid,), one=True)
        allowed = ctx.opt.get("allowed_branches")
        if allowed is not None and w["branch_id"] not in allowed:
            raise RowError(t("You do not have access to this branch.") + f" ({text})")
        return w
    branch_id = ctx.branch(branch_text)
    w = q("SELECT * FROM warehouses WHERE branch_id = ? AND active = 1 ORDER BY id LIMIT 1", (branch_id,), one=True)
    if not w:
        raise RowError(t("The branch has no warehouse."))
    return w


def g_stock(ctx, wid, rows):
    d = ctx.cutoff()
    w = q("SELECT * FROM warehouses WHERE id = ?", (wid,), one=True)
    batch = ctx.imported("stock", str(wid))
    if batch:
        raise RowError(f"{w['code']}: " + t("opening stock was already imported in import") + f" #{batch}. "
                       + t("Undo that import first."))
    if not ctx.clearing:
        raise RowError(t("Missing account") + f" {CLEARING_CODE}")
    lines, errors = {}, []
    for row_no, v in rows:
        try:
            prod = ctx.product(v.get("product"))
            if prod["type"] != "stock":
                raise RowError(f"{nm(prod)}: " + t("only stockable products have stock"))
            qty = parse_number(v.get("quantity"), 0.0)
            if qty < 0:
                raise RowError(t("Quantity cannot be negative."))
            if qty < EPS:
                continue
            cost = parse_amount(v.get("cost")) if (v.get("cost") or "").strip() else prod["purchase_price"]
            if prod["id"] in lines:  # same product twice: add up at a weighted cost
                q0, c0 = lines[prod["id"]]
                cost = int(round((q0 * c0 + qty * cost) / (q0 + qty)))
                qty += q0
            lines[prod["id"]] = (qty, cost)
        except (RowError, AccountingError) as e:
            errors.append((row_no, str(e)))
    if errors:
        raise GroupErrors(errors)
    if not lines:
        return
    pid = create_picking("in", w["branch_id"], wid, d,
                         [{"product_id": p, "quantity": qv, "unit_cost": c} for p, (qv, c) in lines.items()],
                         notes=t("Opening stock"), user_id=ctx.user_id)
    validate_picking(pid, d=d, user_id=ctx.user_id, counter_account=ctx.clearing)
    ctx.record("picking", pid, None)
    ctx.record("stock", pid, str(wid))
    ctx.created += 1
    value = q("SELECT COALESCE(SUM(value_signed), 0) FROM picking_lines WHERE picking_id = ?", (pid,), one=True)[0]
    ctx.show(w["code"], d, len(lines), value / 1000.0)


METHODS = {"linear": "linear", "straight line": "linear", "straight-line": "linear", "sl": "linear",
           "قسط ثابت": "linear", "declining": "declining", "declining balance": "declining", "reducing balance": "declining",
           "db": "declining", "قسط متناقص": "declining"}


def _months_through(start, end):
    """Whole months from the month of start to the month of end, both included."""
    s, e = date.fromisoformat(start), date.fromisoformat(end)
    return (e.year - s.year) * 12 + e.month - s.month + 1 if s <= e else 0


def h_assets(ctx, row_no, v):
    d = ctx.cutoff()
    name = v["name_en"].strip()
    if not name:
        raise RowError(t("Name (English)") + ": " + t("is required"))
    cat_text = (v.get("category") or "").strip()
    cat = q("SELECT * FROM asset_categories WHERE lower(name_en) = ? OR name_ar = ?", (cat_text.lower(), cat_text),
            one=True)
    if not cat:
        raise RowError(t("Unknown asset category") + f": {cat_text}")
    if not (cat["asset_account_id"] and cat["depreciation_account_id"] and cat["expense_account_id"]):
        raise RowError(t("Set the accounts on the asset category."))
    branch_id = ctx.branch(v.get("branch"))
    code = (v.get("code") or "").strip()
    if code and ctx.skip_if_imported("asset", code, code):
        return
    if code and q("SELECT 1 FROM assets WHERE code = ?", (code,), one=True):
        raise RowError(f"{code}: " + t("an asset with this code already exists"))
    acq = ctx.date(v.get("acquisition_date"), what="Acquisition date")
    if acq > d:
        raise RowError(t("Acquired after the cut-off date: enter it as a new asset."))
    start = ctx.date(v.get("start_date"), required=False) or acq
    cost = parse_amount(v.get("cost"))
    acc_dep = parse_amount(v.get("accumulated"))
    salvage = parse_amount(v.get("salvage"))
    life = int(parse_number(v.get("life_months"), cat["life_months"]) or 0)
    method = METHODS.get(norm(v.get("method")), cat["method"]) if (v.get("method") or "").strip() else cat["method"]
    if cost <= 0 or life <= 0:
        raise RowError(t("Cost and useful life must be greater than zero."))
    if acc_dep < 0 or acc_dep + salvage > cost:
        raise RowError(t("Accumulated depreciation plus salvage value cannot exceed the cost."))
    if not code:
        n = q("SELECT COUNT(*) FROM assets", one=True)[0] + 1
        code = f"AST-{n:05d}"
        while q("SELECT 1 FROM assets WHERE code = ?", (code,), one=True):
            n += 1
            code = f"AST-{n:05d}"
    remaining = life - _months_through(start, d)
    left = cost - salvage - acc_dep
    if left > 0 and remaining <= 0:
        remaining = 1
        ctx.note("Assets past their useful life: the remaining value is depreciated next month")
    aid = x("INSERT INTO assets (code, name_en, name_ar, category_id, branch_id, serial_no, location, custodian, "
            "acquisition_date, start_date, cost, salvage, method, life_months, rate, state, notes, created_by, created_at) "
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'running',?,?,?)",
            (code, name, (v.get("name_ar") or "").strip() or None, cat["id"], branch_id,
             (v.get("serial_no") or "").strip() or None, (v.get("location") or "").strip() or None,
             (v.get("custodian") or "").strip() or None, acq, start, cost, salvage, method, life, cat["rate"] or 0,
             t("Imported with depreciation to") + f" {d}", ctx.user_id, now()))
    jid = ctx.journal(branch_id, "", ("general",), ("MISC",))
    lines = [{"account_id": cat["asset_account_id"], "label": f"{code} {name}", "debit": cost, "credit": 0}]
    if acc_dep:
        lines.append({"account_id": cat["depreciation_account_id"], "label": f"{code} {name}", "debit": 0, "credit": acc_dep})
    lines.append({"account_id": ctx.clearing, "label": f"{code} {name}", "debit": 0, "credit": cost - acc_dep})
    mid = create_move(branch_id, jid, d, lines, user_id=ctx.user_id, ref=code, narration=t("Imported fixed asset"),
                      move_type="asset", post=True)
    x("UPDATE assets SET acquisition_move_id = ? WHERE id = ?", (mid, aid))
    if acc_dep:
        x("INSERT INTO asset_lines (asset_id, date, amount, accumulated, book_value, move_id) VALUES (?,?,?,?,?,?)",
          (aid, d, acc_dep, acc_dep, cost - acc_dep, mid))
    if left > 0:
        nd = date.fromisoformat(d)
        nxt = (date(nd.year + (nd.month == 12), nd.month % 12 + 1, 1)).isoformat()
        for ld, amt, acc, _bv in depreciation_schedule(cost - acc_dep, salvage, nxt, remaining, method,
                                                       cat["rate"] or 0, branch_decimals(branch_id)):
            x("INSERT INTO asset_lines (asset_id, date, amount, accumulated, book_value) VALUES (?,?,?,?,?)",
              (aid, ld, amt, acc + acc_dep, cost - acc - acc_dep))
    else:
        x("UPDATE assets SET state = 'closed' WHERE id = ?", (aid,))
    ctx.record("asset", aid, code)
    ctx.created += 1
    ctx.show(code, name, (cost - acc_dep) / 1000.0, max(remaining, 0))


HANDLERS = {"accounts": h_accounts, "partners": h_partners, "products": h_products, "open_items": h_open_items,
            "payments": h_payments, "assets": h_assets}
GROUPED = {
    "opening": (g_opening, lambda ctx, v: ctx.branch(v.get("branch"))),
    "entries": (g_entries, lambda ctx, v: ((v.get("branch") or "").strip().upper(), (v.get("entry") or "").strip())),
    "invoices": (g_invoices, lambda ctx, v: ((v.get("branch") or "").strip().upper(), (v.get("number") or "").strip())),
    "stock": (g_stock, lambda ctx, v: _warehouse(ctx, v.get("warehouse"), v.get("branch"))["id"]),
}
PREVIEW_COLS = {
    "accounts": ("Code", "Name", "Type"), "partners": ("Name", "VAT Number", "Type"),
    "products": ("Code", "Name", "Type"), "opening": ("Branch", "Date", "Lines", "Total debit"),
    "open_items": ("Number", "Type", "Date", "Partner", "Amount"), "entries": ("Entry", "Date", "Lines", "Total debit"),
    "invoices": ("Number", "Type", "Date", "Partner", "Total"), "payments": ("Date", "Partner", "Type", "Invoice", "Amount"),
    "stock": ("Warehouse", "Date", "Products", "Value"), "assets": ("Code", "Name", "Net book value", "Months left"),
}


# ---------------------------------------------------------------- run / undo

def _savepoint(ctx, fn, row_label, *args):
    """Run one row or document; on failure undo its writes and anything it added to the context."""
    state = (dict(ctx.notes), ctx.created, ctx.updated, ctx.skipped, len(ctx.records), len(ctx.preview), dict(ctx.cache))
    x("SAVEPOINT imp")
    try:
        fn(ctx, *args)
        x("RELEASE imp")
        return
    except GroupErrors as e:
        errors = e.errors
    except (RowError, AccountingError, ValueError, KeyError) as e:
        errors = [(row_label, str(e) or e.__class__.__name__)]
    x("ROLLBACK TO imp")
    x("RELEASE imp")
    ctx.notes, ctx.created, ctx.updated, ctx.skipped, n_rec, n_prev, ctx.cache = state
    del ctx.records[n_rec:]
    del ctx.preview[n_prev:]
    ctx.errors.extend(errors)


def run(kind, rows, mapping, options, user_id, filename="", commit_changes=False):
    """Import mapped rows. Returns the context with counts, notes, errors and preview rows.
    Nothing is kept unless commit_changes is set and there are no errors."""
    db = get_db()
    if db.in_transaction:
        db.commit()
    db.execute("BEGIN")
    options = dict(options, mapped=set(mapping))
    ctx = Ctx(kind, options, user_id)
    try:
        missing = [t(f["label"]) for f in TYPES[kind]["fields"] if f["required"] and f["key"] not in mapping]
        if kind in ("opening", "entries") and not ({"debit", "credit"} & set(mapping) or "amount" in mapping):
            missing.append(t("Debit") + " / " + t("Credit") + " / " + t("Amount"))
        if missing:
            ctx.errors.append(("", t("Map these columns") + ": " + ", ".join(missing)))
            return ctx
        if kind in ("opening", "stock", "assets"):
            check_lock(ctx.cutoff())
        data = mapped_rows(kind, rows, mapping)
        if kind in GROUPED:
            fn, keyfn = GROUPED[kind]
            groups = OrderedDict()
            for row_no, v in data:
                try:
                    k = keyfn(ctx, v)
                except (RowError, AccountingError) as e:
                    ctx.errors.append((row_no, str(e)))
                    continue
                if kind in ("entries", "invoices") and not k[1]:
                    ctx.errors.append((row_no, t("Entry number") if kind == "entries" else t("Number")) + ": "
                                      + t("is required"))
                    continue
                groups.setdefault(k, []).append((row_no, v))
            for k, grp in groups.items():
                arg = k[1] if kind in ("entries", "invoices") else k
                _savepoint(ctx, fn, grp[0][0], arg, grp)
                if len(ctx.errors) >= MAX_ERRORS:
                    break
        else:
            for row_no, v in data:
                _savepoint(ctx, HANDLERS[kind], row_no, row_no, v)
                if len(ctx.errors) >= MAX_ERRORS:
                    break
        if commit_changes and not ctx.errors and (ctx.created or ctx.updated):
            summary = "; ".join(f"{t(k)}: {n}" if n > 1 else t(k) for k, n in ctx.notes.items())
            bid = x("INSERT INTO import_batches (kind, filename, rows, created, updated, summary, state, created_by, "
                    "created_at) VALUES (?,?,?,?,?,?,'done',?,?)",
                    (kind, filename, len(rows), ctx.created, ctx.updated, summary, user_id, now()))
            for model, rid, ext in ctx.records:
                x("INSERT INTO import_records (batch_id, model, record_id, ext_key) VALUES (?,?,?,?)",
                  (bid, model, rid, ext))
            audit(user_id, "import", kind, bid, f"{filename}: {ctx.created} created, {ctx.updated} updated")
            ctx.batch_id = bid
            db.commit()
        else:
            db.rollback()
    except Exception:
        db.rollback()
        raise
    return ctx


def undo(batch_id, user_id):
    """Remove what an import created. Updates to existing records stay."""
    b = q("SELECT * FROM import_batches WHERE id = ?", (batch_id,), one=True)
    if not b or b["state"] != "done":
        raise AccountingError(t("This action is not allowed in the current state."))
    records = q("SELECT * FROM import_records WHERE batch_id = ? ORDER BY id DESC", (batch_id,))
    lock = company()["lock_date"]

    def drop_move(mid):
        if not mid:
            return
        m = q("SELECT * FROM moves WHERE id = ?", (mid,), one=True)
        if not m:
            return
        if lock and m["date"] <= lock:
            raise AccountingError(t("The date is on or before the lock date") + f" ({lock}): {m['name']}")
        if q("SELECT 1 FROM move_lines WHERE move_id = ? AND statement_line_id IS NOT NULL", (mid,), one=True):
            raise AccountingError(f"{m['name']}: " + t("reconciled with a bank statement; unmatch it first."))
        x("DELETE FROM moves WHERE id = ?", (mid,))

    import sqlite3
    try:
        for r in records:
            rid = r["record_id"]
            if r["model"] == "move":
                drop_move(rid)
            elif r["model"] == "invoice":
                inv = q("SELECT * FROM invoices WHERE id = ?", (rid,), one=True)
                if not inv:
                    continue
                if q("SELECT 1 FROM payments WHERE invoice_id = ? AND state = 'posted'", (rid,), one=True):
                    raise AccountingError(f"{inv['number']}: " + t("Cancel the payments of this document first."))
                x("DELETE FROM invoices WHERE id = ?", (rid,))
                drop_move(inv["move_id"])
            elif r["model"] == "payment":
                p = q("SELECT * FROM payments WHERE id = ?", (rid,), one=True)
                if p:
                    x("DELETE FROM payments WHERE id = ?", (rid,))
                    drop_move(p["move_id"])
            elif r["model"] == "picking":
                p = q("SELECT * FROM pickings WHERE id = ?", (rid,), one=True)
                if not p:
                    continue
                for ln in q("SELECT * FROM picking_lines WHERE picking_id = ?", (rid,)):
                    have = q("SELECT qty, value FROM stock_quants WHERE product_id = ? AND warehouse_id = ?",
                             (ln["product_id"], p["warehouse_id"]), one=True)
                    if not have or have["qty"] - ln["qty_signed"] < -EPS:
                        prod = q("SELECT * FROM products WHERE id = ?", (ln["product_id"],), one=True)
                        raise AccountingError(f"{nm(prod)}: " + t("the stock has been used since the import."))
                    x("UPDATE stock_quants SET qty = qty - ?, value = value - ? WHERE product_id = ? AND warehouse_id = ?",
                      (ln["qty_signed"], ln["value_signed"], ln["product_id"], p["warehouse_id"]))
                x("DELETE FROM picking_lines WHERE picking_id = ?", (rid,))
                x("DELETE FROM pickings WHERE id = ?", (rid,))
                drop_move(p["move_id"])
            elif r["model"] == "asset":
                a = q("SELECT * FROM assets WHERE id = ?", (rid,), one=True)
                if not a:
                    continue
                if a["state"] == "disposed" or q("SELECT 1 FROM asset_lines WHERE asset_id = ? AND move_id IS NOT NULL "
                                                 "AND move_id != ?", (rid, a["acquisition_move_id"] or 0), one=True):
                    raise AccountingError(f"{a['code']}: " + t("depreciation has been posted since the import."))
                x("DELETE FROM asset_lines WHERE asset_id = ?", (rid,))
                x("DELETE FROM assets WHERE id = ?", (rid,))
                drop_move(a["acquisition_move_id"])
            elif r["model"] in ("partner", "account", "product"):
                table = {"partner": "partners", "account": "accounts", "product": "products"}[r["model"]]
                try:
                    x("SAVEPOINT u")
                    x(f"DELETE FROM {table} WHERE id = ?", (rid,))
                    x("RELEASE u")
                except sqlite3.IntegrityError:
                    x("ROLLBACK TO u")
                    x("RELEASE u")
                    row = q(f"SELECT * FROM {table} WHERE id = ?", (rid,), one=True)
                    raise AccountingError(f"{row['code'] if 'code' in row.keys() and row['code'] else nm(row)}: "
                                          + t("used by other records since the import."))
        x("UPDATE import_batches SET state = 'undone', undone_at = ? WHERE id = ?", (now(), batch_id))
        audit(user_id, "undo import", b["kind"], batch_id, b["filename"] or "")
    except Exception:
        get_db().rollback()
        raise


# ---------------------------------------------------------------- templates

EXAMPLES = {
    "accounts": [["1010", "Cash on Hand", "النقدية في الصندوق", "Bank and Cash", ""],
                 ["4100", "Sales Revenue", "إيرادات المبيعات", "Income", ""]],
    "partners": [["Al Noor Trading Est.", "مؤسسة النور التجارية", "customer", "310000000000001", "1010123456",
                  "+966 11 000 0000", "info@alnoor.example", "Riyadh", "Ahmed", "30", "Al Rajhi Bank",
                  "SA0000000000000000000000"]],
    "products": [["P-1001", "Office Chair", "كرسي مكتب", "stock", "Furniture", "Unit", "6281000000001", "450.00",
                  "300.00", "15", "15", "", ""]],
    "opening": [["RUH", "1020", "", "Bank balance", "125000.00", "", ""],
                ["RUH", "3100", "", "Share capital", "", "125000.00", ""]],
    "open_items": [["invoice", "RUH", "INV-2026-0451", "15/09/2026", "15/10/2026", "Al Noor Trading Est.", "11500.00",
                    "23000.00", ""]],
    "entries": [["JV-0001", "31/01/2026", "RUH", "MISC", "6100", "", "January rent", "5000.00", "", "", ""],
                ["JV-0001", "31/01/2026", "RUH", "MISC", "1020", "", "January rent", "", "5000.00", "", ""]],
    "invoices": [["invoice", "RUH", "INV-2026-0001", "05/01/2026", "04/02/2026", "Al Noor Trading Est.", "", "P-1001",
                  "Office Chair", "", "2", "450.00", "15", "1035.00"]],
    "payments": [["20/01/2026", "RUH", "receipt", "Al Noor Trading Est.", "BNK", "1035.00", "INV-2026-0001", "TT-5531"]],
    "stock": [["WH-RUH", "", "P-1001", "12", "300.00"]],
    "assets": [["FA-0001", "Delivery Van", "شاحنة توصيل", "General Equipment", "RUH", "01/03/2024", "", "90000.00",
                "40500.00", "0", "60", "linear", "", "", ""]],
}


def template_rows(kind):
    header = [t(f["label"]) for f in TYPES[kind]["fields"]]
    return header, EXAMPLES.get(kind, [])


def template_csv(kind):
    header, rows = template_rows(kind)
    out = io.StringIO()
    w = csv.writer(out)
    w.writerow(header)
    w.writerows(rows)
    return "﻿" + out.getvalue()


def template_xlsx(kind):
    import openpyxl
    from openpyxl.styles import Font, PatternFill
    header, rows = template_rows(kind)
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = t(TYPES[kind]["title"])[:31]
    ws.append(header)
    for r in rows:
        ws.append(r)
    bold = Font(bold=True)
    fill = PatternFill("solid", fgColor="E8EEF7")
    for i, f in enumerate(TYPES[kind]["fields"]):
        cell = ws.cell(row=1, column=i + 1)
        cell.font = bold
        cell.fill = fill
        ws.column_dimensions[cell.column_letter].width = max(14, len(header[i]) + 4)
    ws.freeze_panes = "A2"
    help_ws = wb.create_sheet(t("Help"))
    help_ws.append([t("Column"), t("Required"), t("Notes")])
    for c in help_ws[1]:
        c.font = bold
    for f in TYPES[kind]["fields"]:
        help_ws.append([t(f["label"]), t("Yes") if f["required"] else "", t(f["help"]) if f["help"] else ""])
    help_ws.column_dimensions["A"].width = 28
    help_ws.column_dimensions["C"].width = 100
    if kind in ("open_items", "entries", "invoices", "payments", "assets"):
        help_ws.append([])
        help_ws.append([t("Dates"), "", t("Choose the date format of the file when you upload it (e.g. DD/MM/YYYY).")])
    if kind == "accounts":
        help_ws.append([])
        help_ws.append([t("Types"), "", ", ".join(t(label) for _k, label, _r in ACCOUNT_KINDS)])
    out = io.BytesIO()
    wb.save(out)
    return out.getvalue()


def clearing_balances(branch_ids):
    """Balance of the opening balance clearing account per branch (zero once all opening data is imported)."""
    acc = q("SELECT id FROM accounts WHERE code = ?", (CLEARING_CODE,), one=True)
    if not acc or not branch_ids:
        return []
    return q(f"SELECT b.id, b.code, b.currency, COALESCE(SUM(ml.debit - ml.credit), 0) AS balance FROM branches b "
             f"LEFT JOIN move_lines ml ON ml.branch_id = b.id AND ml.account_id = ? AND ml.move_id IN "
             f"(SELECT id FROM moves WHERE state = 'posted') WHERE b.id IN ({placeholders(branch_ids)}) "
             f"GROUP BY b.id ORDER BY b.code", [acc["id"]] + list(branch_ids))


def upload_dir(database_path):
    path = os.path.join(os.path.dirname(database_path), "imports")
    os.makedirs(path, exist_ok=True)
    return path


def _check_ref(partner_id, kind, ref, number):
    """Imported bills follow the same rule as typed ones: a vendor invoice number is booked only once."""
    from .ledger import check_vendor_ref
    try:
        check_vendor_ref(partner_id, kind, ref)
    except AccountingError as e:
        raise RowError(f"{number}: {e}")
