"""Double-entry posting engine, balances, currency translation and consolidation."""
import uuid
from collections import defaultdict
from datetime import date, datetime

from .db import placeholders, q, x
from .i18n import nm, t
from .money import translate

# (kind, English label, root type)
ACCOUNT_KINDS = [
    ("asset_cash", "Bank and Cash", "asset"),
    ("asset_receivable", "Receivable", "asset"),
    ("asset_current", "Current Assets", "asset"),
    ("asset_prepayments", "Prepayments", "asset"),
    ("asset_non_current", "Non-current Assets", "asset"),
    ("asset_fixed", "Fixed Assets", "asset"),
    ("liability_payable", "Payable", "liability"),
    ("liability_current", "Current Liabilities", "liability"),
    ("liability_non_current", "Non-current Liabilities", "liability"),
    ("equity", "Equity", "equity"),
    ("income", "Income", "income"),
    ("income_other", "Other Income", "income"),
    ("expense_direct_cost", "Cost of Revenue", "expense"),
    ("expense", "Expenses", "expense"),
    ("expense_depreciation", "Depreciation", "expense"),
]
ROOT = {k: r for k, _, r in ACCOUNT_KINDS}
KIND_LABEL = {k: label for k, label, _ in ACCOUNT_KINDS}
PL_ROOTS = ("income", "expense")

INVOICE_KINDS = {
    "out_invoice": {"label": "Customer Invoice", "partner_type": "customer", "sign": 1},
    "out_refund": {"label": "Customer Credit Note", "partner_type": "customer", "sign": -1},
    "in_invoice": {"label": "Vendor Bill", "partner_type": "vendor", "sign": 1},
    "in_refund": {"label": "Vendor Refund", "partner_type": "vendor", "sign": -1},
}


class AccountingError(Exception):
    pass


def now():
    return datetime.now().isoformat(timespec="seconds")


def today():
    return date.today().isoformat()


def company():
    return q("SELECT * FROM company WHERE id = 1", one=True)


def fy_start(d):
    month = company()["fy_start_month"] or 1
    y, m = int(d[:4]), int(d[5:7])
    if m < month:
        y -= 1
    return f"{y:04d}-{month:02d}-01"


def check_lock(d):
    lock = company()["lock_date"]
    if lock and d <= lock:
        raise AccountingError(t("The date is on or before the lock date") + f" ({lock}).")


def audit(user_id, action, model=None, record_id=None, details=""):
    x("INSERT INTO audit_log (ts, user_id, action, model, record_id, details) VALUES (?,?,?,?,?,?)",
      (now(), user_id, action, model, record_id, details))


# ---------------------------------------------------------------- sequences

def next_sequence(journal_id, d):
    j = q("SELECT j.code, b.code AS bcode FROM journals j JOIN branches b ON b.id = j.branch_id "
          "WHERE j.id = ?", (journal_id,), one=True)
    prefix = f"{j['code']}/{j['bcode']}/{d[:4]}/"
    rows = q("SELECT name FROM moves WHERE name LIKE ? UNION ALL "
             "SELECT number FROM invoices WHERE number LIKE ?", (prefix + "%", prefix + "%"))
    last = 0
    for r in rows:
        try:
            last = max(last, int(r[0].rsplit("/", 1)[1]))
        except (ValueError, IndexError):
            pass
    return f"{prefix}{last + 1:04d}"


def check_number_kept(number, old_branch, new_branch, old_journal, new_journal, old_date, new_date):
    """An issued document number (INV/DXB/2026/0007) belongs to its branch, journal and year for life: the
    numbering of each branch must stay continuous for VAT and audit. A document that was posted once and reset to
    draft therefore cannot move to another branch, journal or year."""
    if not number:
        return
    if int(old_branch) != int(new_branch) or int(old_journal) != int(new_journal) or str(old_date)[:4] != str(new_date)[:4]:
        b = q("SELECT code FROM branches WHERE id = ?", (old_branch,), one=True)
        raise AccountingError(f"{number} " + t("was already issued in") + f" {b['code'] if b else ''} {str(old_date)[:4]}. " +
                              t("An issued number cannot move to another branch, journal or year. Keep the branch, "
                                "journal and year, or cancel this document and use Duplicate to create it in the "
                                "right branch."))


# ---------------------------------------------------------------- moves

def validate_lines(lines):
    if len(lines) < 2:
        raise AccountingError(t("An entry needs at least two lines."))
    debit = credit = 0
    for ln in lines:
        if ln["debit"] < 0 or ln["credit"] < 0:
            raise AccountingError(t("Debit and credit cannot be negative."))
        if ln["debit"] and ln["credit"]:
            raise AccountingError(t("A line cannot have both a debit and a credit."))
        acc = q("SELECT code, name_en, name_ar, is_group FROM accounts WHERE id = ?", (ln["account_id"],), one=True)
        if not acc:
            raise AccountingError(t("Every line needs an account."))
        if acc["is_group"]:
            raise AccountingError(f"{acc['code']} {nm(acc)}: " + t("is a group account; choose an account under it."))
        debit += ln["debit"]
        credit += ln["credit"]
    if debit != credit:
        raise AccountingError(t("The entry is not balanced.") + f" ({debit / 1000:.3f} ≠ {credit / 1000:.3f})")
    if debit == 0:
        raise AccountingError(t("The entry has no amounts."))


def write_lines(move_id, branch_id, lines):
    x("DELETE FROM move_lines WHERE move_id = ?", (move_id,))
    for ln in lines:
        x("INSERT INTO move_lines (move_id, branch_id, account_id, partner_id, label, debit, credit, tax_id, "
          "cost_centre_id, currency, amount_currency) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
          (move_id, branch_id, ln["account_id"], ln.get("partner_id"), ln.get("label") or "",
           ln["debit"], ln["credit"], ln.get("tax_id"), ln.get("cost_centre_id"), ln.get("currency"),
           ln.get("amount_currency")))


def check_journal(branch_id, journal_id):
    j = q("SELECT * FROM journals WHERE id = ?", (journal_id,), one=True)
    if not j or j["branch_id"] != int(branch_id):
        raise AccountingError(t("The journal does not belong to the selected branch."))
    return j


def create_move(branch_id, journal_id, d, lines, user_id=None, ref="", narration="",
                move_type="entry", partner_id=None, post=False, name=None, transfer_ref=None):
    check_journal(branch_id, journal_id)
    if post:
        validate_lines(lines)
        check_lock(d)
    move_id = x("INSERT INTO moves (branch_id, journal_id, date, ref, narration, state, move_type, partner_id, "
                "transfer_ref, created_by, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                (branch_id, journal_id, d, ref, narration, "draft", move_type, partner_id,
                 transfer_ref, user_id, now()))
    write_lines(move_id, branch_id, lines)
    if post:
        post_move(move_id, user_id, name=name)
    return move_id


def move_lines(move_id):
    return [dict(r) for r in q("SELECT * FROM move_lines WHERE move_id = ? ORDER BY id", (move_id,))]


def post_move(move_id, user_id=None, name=None):
    m = q("SELECT * FROM moves WHERE id = ?", (move_id,), one=True)
    if m["state"] == "posted":
        return
    validate_lines(move_lines(move_id))
    check_lock(m["date"])
    name = name or m["name"] or next_sequence(m["journal_id"], m["date"])
    x("UPDATE moves SET state = 'posted', name = ?, posted_by = ?, posted_at = ? WHERE id = ?",
      (name, user_id, now(), move_id))
    audit(user_id, "post", "move", move_id, name)


def set_move_state(move_id, state, user_id=None):
    m = q("SELECT * FROM moves WHERE id = ?", (move_id,), one=True)
    check_lock(m["date"])
    x("UPDATE moves SET state = ? WHERE id = ?", (state, move_id))
    audit(user_id, state, "move", move_id, m["name"] or "")


# ---------------------------------------------------------------- invoices

def compute_invoice_line(qty, price_unit, tax_rate, decimals):
    from .money import round_cur
    subtotal = round_cur(int(round(qty * price_unit)), decimals)
    tax = round_cur(int(round(subtotal * (tax_rate or 0) / 100)), decimals)
    return subtotal, tax


def effective_tax(branch_id, tax_id, scope):
    """A branch's default VAT replaces the product's taxable VAT (e.g. 5% in a UAE branch); 0% and exempt stay."""
    if not tax_id:
        return None
    b = q("SELECT sale_tax_id, purchase_tax_id FROM branches WHERE id = ?", (branch_id,), one=True)
    override = b and b["sale_tax_id" if scope == "sale" else "purchase_tax_id"]
    if not override:
        return tax_id
    rate = q("SELECT rate FROM taxes WHERE id = ?", (tax_id,), one=True)
    return override if rate and rate["rate"] > 0 else tax_id


def invoice_residuals(invoice_ids=None, as_of=None, base=False):
    """Amount still open on each posted invoice (positive): in the invoice currency, or with base=True in the branch
    currency at the invoice's own rate (what the receivable / payable account carries for it)."""
    total, paid = ("COALESCE(i.amount_total_base, i.amount_total)", "COALESCE(p.settled_base, p.amount)") if base \
        else ("i.amount_total", "p.amount")
    sql = (f"SELECT i.id, {total} - COALESCE((SELECT SUM({paid}) FROM payments p "
           "WHERE p.invoice_id = i.id AND p.state = 'posted'" + (" AND p.date <= ?" if as_of else "") +
           "), 0) AS residual FROM invoices i WHERE i.state = 'posted'")
    args = [as_of] if as_of else []
    if invoice_ids is not None:
        if not invoice_ids:
            return {}
        sql += f" AND i.id IN ({placeholders(invoice_ids)})"
        args += list(invoice_ids)
    return {r["id"]: r["residual"] for r in q(sql, args)}


def payment_state(inv, residual):
    if inv["state"] != "posted":
        return None
    if residual <= 0:
        return "paid"
    if residual < inv["amount_total"]:
        return "partial"
    return "not_paid"


def partner_account(partner_id, customer):
    """Receivable (customer) or payable (vendor) account of a partner, else the company default."""
    col = "receivable_account_id" if customer else "payable_account_id"
    if partner_id:
        from . import partneraccts
        if partneraccts.enabled():
            acc = partneraccts.own_account(partner_id, customer) or partneraccts.ensure_account(partner_id, customer)
            if acc:
                return acc
    p = q(f"SELECT {col} FROM partners WHERE id = ?", (partner_id,), one=True) if partner_id else None
    return (p and p[0]) or company()[col]


def invoice_move_lines(inv, lines):
    c = company()
    kind = INVOICE_KINDS[inv["kind"]]
    customer = kind["partner_type"] == "customer"
    # Sales credit income and debit receivable; purchases the opposite. Refunds flip it again.
    natural_credit = customer if kind["sign"] > 0 else not customer
    partner_acc = partner_account(inv["partner_id"], customer)
    if not partner_acc:
        raise AccountingError(t("Set the default receivable and payable accounts in Company settings."))

    def side(amount, credit):
        if amount < 0:  # a deduction line (retention held back, advance recovered) goes to the other side
            amount, credit = -amount, not credit
        return {"debit": 0, "credit": amount} if credit else {"debit": amount, "credit": 0}

    out = []
    tax_totals = defaultdict(int)
    for ln in lines:
        if ln["subtotal"]:
            out.append({"account_id": ln["account_id"], "partner_id": inv["partner_id"],
                        "label": ln["description"], "tax_id": ln["tax_id"], "cost_centre_id": ln["cost_centre_id"],
                        **side(ln["subtotal"], natural_credit)})
        if ln["tax_id"] and ln["tax_amount"]:
            tax_totals[ln["tax_id"]] += ln["tax_amount"]
    for tax_id, amount in tax_totals.items():
        tax = q("SELECT * FROM taxes WHERE id = ?", (tax_id,), one=True)
        if not tax["account_id"]:
            raise AccountingError(t("The tax has no account") + f": {tax['name_en']}")
        out.append({"account_id": tax["account_id"], "partner_id": inv["partner_id"],
                    "label": tax["name_en"], "tax_id": tax_id, **side(amount, natural_credit)})
    out.append({"account_id": partner_acc, "partner_id": inv["partner_id"], "label": inv["number"] or "",
                **side(inv["amount_total"], not natural_credit)})
    return out


VENDOR_REF_LABEL = {"in_invoice": "Vendor invoice no.", "in_refund": "Vendor credit note no."}


def check_vendor_ref(partner_id, kind, ref, exclude_id=None, posting=False):
    """A vendor's invoice (or credit note) number can be booked only once. Returns the cleaned number.
    With Company > "Vendor invoice number required", a bill or vendor refund cannot be posted without it."""
    if kind not in VENDOR_REF_LABEL:
        return ref
    ref = (ref or "").strip() or None
    if not ref:
        if posting and company()["require_vendor_ref"]:
            raise AccountingError(t("Enter the") + " " + t(VENDOR_REF_LABEL[kind]) + ".")
        return None
    dup = q("SELECT id, number, date, state FROM invoices WHERE partner_id = ? AND kind = ? AND state != 'cancelled' "
            "AND lower(trim(ref)) = lower(?) AND id != ? ORDER BY id LIMIT 1",
            (partner_id, kind, ref, exclude_id or 0), one=True)
    if dup:
        raise AccountingError(f"{t(VENDOR_REF_LABEL[kind])} {ref} " + t("is already booked for this vendor") +
                              f": {dup['number'] or t('Draft')} ({dup['date']}). " +
                              t("The same vendor invoice cannot be entered twice."))
    return ref


def post_invoice(invoice_id, user_id=None, credit_check=True):
    """credit_check=False for documents that already happened (imports of history)."""
    inv = q("SELECT * FROM invoices WHERE id = ?", (invoice_id,), one=True)
    if inv["state"] != "draft":
        raise AccountingError(t("Only draft documents can be posted."))
    lines = q("SELECT * FROM invoice_lines WHERE invoice_id = ? ORDER BY sequence, id", (invoice_id,))
    if not lines or inv["amount_total"] <= 0:
        raise AccountingError(t("The document total must be greater than zero."))
    check_lock(inv["date"])
    branch_cur = q("SELECT currency FROM branches WHERE id = ?", (inv["branch_id"],), one=True)["currency"]
    foreign = bool(inv["currency"]) and inv["currency"] != branch_cur
    if foreign and inv["rate"] <= 0:
        raise AccountingError(t("Enter the exchange rate."))
    if credit_check and inv["kind"] == "out_invoice":
        from .credit import check
        check(inv["partner_id"], to_base(inv["amount_total"], inv["rate"] if foreign else 1, branch_cur), branch_cur,
              inv["date"], "Customer Invoice")
    if inv["kind"] == "out_invoice":
        from .invoicetypes import check_post
        check_post(inv)  # proforma never posts; advance, debit note and advance deductions are checked
    if inv["kind"] == "in_invoice":
        from .procurement import check_match
        check_match(invoice_id)  # three-way match: PO - receipt - bill
    number = inv["number"] or next_sequence(inv["journal_id"], inv["date"])
    x("UPDATE invoices SET number = ? WHERE id = ?", (number, invoice_id))
    inv = q("SELECT * FROM invoices WHERE id = ?", (invoice_id,), one=True)
    mlines = invoice_move_lines(inv, lines)
    if foreign:
        mlines = convert_lines(mlines, inv["currency"], inv["rate"], branch_cur)
    else:
        x("UPDATE invoices SET currency = NULL, rate = 1 WHERE id = ?", (invoice_id,))
    x("UPDATE invoices SET amount_total_base = ? WHERE id = ?", (mlines[-1]["debit"] + mlines[-1]["credit"], invoice_id))
    move_id = create_move(inv["branch_id"], inv["journal_id"], inv["date"], mlines,
                          user_id=user_id, ref=inv["ref"], move_type=inv["kind"], partner_id=inv["partner_id"],
                          post=True, name=number)
    x("UPDATE invoices SET state = 'posted', move_id = ? WHERE id = ?", (move_id, invoice_id))
    settle_bill(inv, user_id)
    audit(user_id, "post", "invoice", invoice_id, number)


def to_base(amount, rate, branch_currency):
    """An amount in a document currency in the branch currency, rounded to the branch currency's decimals."""
    from . import currency_decimals
    from .money import round_cur
    return round_cur(translate(amount, rate), currency_decimals(branch_currency))


def cross_rate(currency, branch_currency, d):
    """Branch currency per unit of `currency` on date d (rates are kept against the group currency)."""
    if not currency or currency == branch_currency:
        return 1.0
    return round(rate_on(currency, d) / rate_on(branch_currency, d), 6)


def convert_lines(lines, currency, rate, branch_currency):
    """Entry lines in a document currency -> branch currency, keeping the document amount on each line. The last
    line (the receivable / payable) takes the rounding so the entry balances."""
    out, total = [], 0
    for ln in lines[:-1]:
        amt = to_base(ln["debit"] or ln["credit"], rate, branch_currency)
        signed = amt if ln["debit"] else -amt
        total += signed
        out.append({**ln, "debit": max(signed, 0), "credit": max(-signed, 0), "currency": currency,
                    "amount_currency": ln["debit"] - ln["credit"]})
    last = lines[-1]
    out.append({**last, "debit": max(-total, 0), "credit": max(total, 0), "currency": currency,
                "amount_currency": last["debit"] - last["credit"]})
    return out


def settle_bill(inv, user_id=None):
    """Book (or take back) the bill-vs-receipt price difference on the purchase order lines of a vendor bill."""
    if inv["kind"] != "in_invoice":
        return
    ols = {r[0] for r in q("SELECT order_line_id FROM invoice_lines WHERE invoice_id = ? AND order_line_id IS NOT NULL",
                           (inv["id"],))}
    if ols:
        from .stock import settle_price_differences
        settle_price_differences(ols, inv["date"], inv["number"], user_id)


def create_refund(invoice_id, user_id=None):
    """Draft credit note / vendor refund copying a posted invoice or bill; its stock return is valued at the
    original cost."""
    inv = q("SELECT * FROM invoices WHERE id = ?", (invoice_id,), one=True)
    kind = {"out_invoice": "out_refund", "in_invoice": "in_refund"}.get(inv["kind"])
    if not kind or inv["state"] != "posted":
        raise AccountingError(t("This action is not allowed in the current state."))
    j = q("SELECT id FROM journals WHERE id = ? AND active = 1", (inv["journal_id"],), one=True)
    d = date.today().isoformat()
    new_id = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, ref, state, amount_untaxed, "
               "amount_tax, amount_total, origin_id, created_by, created_at, currency, rate) "
               "VALUES (?,?,?,?,?,?,?,'draft',?,?,?,?,?,?,?,?)",
               (inv["branch_id"], j["id"] if j else inv["journal_id"], kind, inv["partner_id"], d, d,
                inv["number"], inv["amount_untaxed"], inv["amount_tax"], inv["amount_total"], invoice_id, user_id, now(),
                inv["currency"], inv["rate"]))
    for ln in q("SELECT * FROM invoice_lines WHERE invoice_id = ? ORDER BY sequence, id", (invoice_id,)):
        x("INSERT INTO invoice_lines (invoice_id, sequence, product_id, description, account_id, quantity, price_unit, "
          "tax_id, subtotal, tax_amount, cost_centre_id) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
          (new_id, ln["sequence"], ln["product_id"], ln["description"], ln["account_id"], ln["quantity"],
           ln["price_unit"], ln["tax_id"], ln["subtotal"], ln["tax_amount"], ln["cost_centre_id"]))
    audit(user_id, "create", "invoice", new_id, f"refund of {inv['number']}")
    return new_id


def unpost_invoice(invoice_id, new_state, user_id=None):
    """new_state is 'draft' (reset) or 'cancelled'."""
    inv = q("SELECT * FROM invoices WHERE id = ?", (invoice_id,), one=True)
    if inv["state"] == "posted":
        from .invoicetypes import check_unpost
        check_unpost(inv)
    paid = q("SELECT COUNT(*) FROM payments WHERE invoice_id = ? AND state = 'posted'", (invoice_id,), one=True)[0]
    if paid:
        raise AccountingError(t("Cancel the payments of this document first."))
    check_lock(inv["date"])
    if inv["move_id"]:
        x("UPDATE invoices SET move_id = NULL WHERE id = ?", (invoice_id,))
        x("DELETE FROM moves WHERE id = ?", (inv["move_id"],))
    x("UPDATE invoices SET state = ? WHERE id = ?", (new_state, invoice_id))
    if inv["state"] == "posted":
        settle_bill(inv, user_id)
    audit(user_id, new_state, "invoice", invoice_id, inv["number"] or "")


# ---------------------------------------------------------------- payments

def create_payment(data, user_id=None):
    c = company()
    j = check_journal(data["branch_id"], data["journal_id"])
    if j["type"] not in ("bank", "cash", "cheque") or not j["default_account_id"]:
        raise AccountingError(t("Payments need a bank or cash journal with an account."))
    if data["amount"] <= 0:
        raise AccountingError(t("The amount must be greater than zero."))
    partner_acc = partner_account(data["partner_id"], data["partner_type"] == "customer")
    if not partner_acc:
        raise AccountingError(t("Set the default receivable and payable accounts in Company settings."))
    bank = {"account_id": j["default_account_id"], "partner_id": data["partner_id"], "label": data.get("ref") or ""}
    other = {"account_id": partner_acc, "partner_id": data["partner_id"], "label": data.get("ref") or ""}
    amt = data["amount"]
    branch_cur = q("SELECT currency FROM branches WHERE id = ?", (data["branch_id"],), one=True)["currency"]
    currency = data.get("currency") or branch_cur
    inv = q("SELECT * FROM invoices WHERE id = ?", (data["invoice_id"],), one=True) if data.get("invoice_id") else None
    if inv:
        if inv["branch_id"] != int(data["branch_id"]):
            raise AccountingError(t("The payment must be in the same branch as the document."))
        if currency != (inv["currency"] or branch_cur):
            raise AccountingError(t("Pay the document in its own currency") + f" ({inv['currency'] or branch_cur}).")
        residual = invoice_residuals([inv["id"]])[inv["id"]]
        if amt > residual:
            raise AccountingError(t("The amount is greater than the amount due."))
    foreign = currency != branch_cur
    rate = float(data.get("rate") or 0) if foreign else 1.0
    if foreign and rate <= 0:
        raise AccountingError(t("Enter the exchange rate."))
    base = to_base(amt, rate, branch_cur)          # what goes through the bank
    settled = base                                 # what the receivable / payable is cleared by
    if inv and foreign:
        # cleared at the invoice's own rate; the last payment clears whatever is left
        open_base = invoice_residuals([inv["id"]], base=True)[inv["id"]]
        settled = open_base if amt == residual else min(to_base(amt, inv["rate"], branch_cur), open_base)
    inbound = data["payment_type"] == "inbound"
    sign = 1 if inbound else -1                    # bank side, debit-positive

    def fx(signed):
        return {"currency": currency, "amount_currency": signed} if foreign else {}
    lines = [{**bank, **fx(sign * amt), "debit": base if inbound else 0, "credit": 0 if inbound else base},
             {**other, **fx(-sign * amt), "debit": 0 if inbound else settled, "credit": settled if inbound else 0}]
    diff = (base - settled) if inbound else (settled - base)  # positive = gain
    if diff:
        c = company()
        acc = c["fx_gain_account_id"] if diff > 0 else c["fx_loss_account_id"]
        if not acc:
            raise AccountingError(t("Set the exchange gain and loss accounts in Company settings."))
        lines.append({"account_id": acc, "partner_id": data["partner_id"],
                      "label": f"{t('Exchange difference')} {inv['number'] if inv else ''}".strip(),
                      "debit": max(-diff, 0), "credit": max(diff, 0)})
    move_id = create_move(data["branch_id"], data["journal_id"], data["date"], lines, user_id=user_id,
                          ref=data.get("ref"), move_type="payment", partner_id=data["partner_id"], post=True)
    name = q("SELECT name FROM moves WHERE id = ?", (move_id,), one=True)["name"]
    pid = x("INSERT INTO payments (name, branch_id, journal_id, partner_id, partner_type, payment_type, date, "
            "amount, ref, invoice_id, state, move_id, created_by, created_at, currency, rate, settled_base) "
            "VALUES (?,?,?,?,?,?,?,?,?,?,'posted',?,?,?,?,?,?)",
            (name, data["branch_id"], data["journal_id"], data["partner_id"], data["partner_type"],
             data["payment_type"], data["date"], amt, data.get("ref"), data.get("invoice_id"), move_id,
             user_id, now(), currency if foreign else None, rate, settled))
    audit(user_id, "post", "payment", pid, name)
    return pid


# ---------------------------------------------------------------- transfers

def create_transfer(data, user_id=None):
    if int(data["from_branch"]) == int(data["to_branch"]):
        raise AccountingError(t("Choose two different branches."))
    ic = q("SELECT id FROM accounts WHERE intercompany = 1 AND kind IN "
           "('asset_current','liability_current') AND active = 1 ORDER BY code LIMIT 1", one=True)
    if not ic:
        raise AccountingError(t("Create an inter-branch account (flagged inter-branch) first."))
    ja = check_journal(data["from_branch"], data["from_journal"])
    jb = check_journal(data["to_branch"], data["to_journal"])
    if not ja["default_account_id"] or not jb["default_account_id"]:
        raise AccountingError(t("Payments need a bank or cash journal with an account."))
    if data["amount_from"] <= 0 or data["amount_to"] <= 0:
        raise AccountingError(t("The amount must be greater than zero."))
    ref = data.get("ref") or ""
    tref = f"TRF-{uuid.uuid4().hex[:12]}"
    b_from = q("SELECT code FROM branches WHERE id = ?", (data["from_branch"],), one=True)["code"]
    b_to = q("SELECT code FROM branches WHERE id = ?", (data["to_branch"],), one=True)["code"]
    a = create_move(data["from_branch"], data["from_journal"], data["date"], [
        {"account_id": ic["id"], "label": f"→ {b_to} {ref}", "debit": data["amount_from"], "credit": 0},
        {"account_id": ja["default_account_id"], "label": f"→ {b_to} {ref}", "debit": 0, "credit": data["amount_from"]},
    ], user_id=user_id, ref=ref, move_type="transfer", post=True, transfer_ref=tref)
    create_move(data["to_branch"], data["to_journal"], data["date"], [
        {"account_id": jb["default_account_id"], "label": f"← {b_from} {ref}", "debit": data["amount_to"], "credit": 0},
        {"account_id": ic["id"], "label": f"← {b_from} {ref}", "debit": 0, "credit": data["amount_to"]},
    ], user_id=user_id, ref=ref, move_type="transfer", post=True, transfer_ref=tref)
    audit(user_id, "transfer", "move", a, tref)
    return tref


# ---------------------------------------------------------------- rates

def rate_on(currency, d):
    """Value of one unit of `currency` in the group currency on date d."""
    if currency == company()["currency"]:
        return 1.0
    r = q("SELECT rate FROM exchange_rates WHERE currency = ? AND date <= ? ORDER BY date DESC LIMIT 1",
          (currency, d), one=True)
    r = r or q("SELECT rate FROM exchange_rates WHERE currency = ? ORDER BY date LIMIT 1", (currency,), one=True)
    if not r:
        raise AccountingError(t("No exchange rate for") + f" {currency}.")
    return r["rate"]


def avg_rate(currency, d1, d2):
    if currency == company()["currency"]:
        return 1.0
    r = q("SELECT AVG(rate) FROM exchange_rates WHERE currency = ? AND date BETWEEN ? AND ?",
          (currency, d1, d2), one=True)
    return r[0] if r and r[0] else rate_on(currency, d2)


# ---------------------------------------------------------------- balances

def balances(branch_ids, date_to, date_from="0000-01-01", exclude_closing=False, cost_centre=None):
    """{branch_id: {account_id: [opening, debit, credit]}} from posted entries (debit-positive).
    exclude_closing leaves out year-end closing entries (profit and loss, pre-closing trial balance).
    cost_centre limits to one cost centre's lines (0 = lines without a cost centre)."""
    out = defaultdict(lambda: defaultdict(lambda: [0, 0, 0]))
    if not branch_ids:
        return out
    rows = q(f"""
        SELECT ml.branch_id, ml.account_id,
               SUM(CASE WHEN m.date < ? THEN ml.debit - ml.credit ELSE 0 END) AS opening,
               SUM(CASE WHEN m.date >= ? THEN ml.debit ELSE 0 END) AS debit,
               SUM(CASE WHEN m.date >= ? THEN ml.credit ELSE 0 END) AS credit
        FROM move_lines ml JOIN moves m ON m.id = ml.move_id
        WHERE m.state = 'posted' AND m.date <= ? AND ml.branch_id IN ({placeholders(branch_ids)})
          {"AND m.move_type != 'closing'" if exclude_closing else ""}
          {"" if cost_centre is None else "AND COALESCE(ml.cost_centre_id, 0) = ?"}
        GROUP BY ml.branch_id, ml.account_id""",
             [date_from, date_from, date_from, date_to] + list(branch_ids) + ([] if cost_centre is None else [cost_centre]))
    for r in rows:
        out[r["branch_id"]][r["account_id"]] = [r["opening"], r["debit"], r["credit"]]
    return out


def accounts_by_id():
    return {a["id"]: a for a in q("SELECT * FROM accounts ORDER BY code")}


def branch_rows(branch_ids):
    if not branch_ids:
        return []
    return q(f"SELECT * FROM branches WHERE id IN ({placeholders(branch_ids)}) ORDER BY code", list(branch_ids))


def report_currency(branches, force_group=False):
    if len(branches) == 1 and not force_group:
        return branches[0]["currency"], False
    return company()["currency"], True


# ---------------------------------------------------------------- statements

class Statement:
    """Rows with one value column per branch plus eliminations and a consolidated total."""

    def __init__(self, branches, translated, currency):
        self.branches = branches
        self.n = len(branches)
        self.translated = translated
        self.currency = currency
        self.multi = self.n > 1
        self.rows = []
        self.notes = []

    def row(self, label, vals, elim=0, kind="account", account=None, level=1):
        r = {"label": label, "vals": vals, "elim": elim, "total": sum(vals) + elim,
             "kind": kind, "account": account, "level": level}
        self.rows.append(r)
        return r

    def section(self, label):
        self.rows.append({"label": label, "kind": "section"})

    @staticmethod
    def add(rows):
        n = len(rows[0]["vals"]) if rows else 0
        vals = [sum(r["vals"][i] for r in rows) for i in range(n)]
        elim = sum(r["elim"] for r in rows)
        return vals, elim


def _translated_balances(branches, translated, date_to, date_from, pl_rate_from, exclude_closing=False,
                         cost_centre=None):
    """Per branch: ({account_id: signed balance in report currency}, rate info)."""
    accs = accounts_by_id()
    raw = balances([b["id"] for b in branches], date_to, date_from, exclude_closing, cost_centre)
    out = []
    for b in branches:
        rc = rate_on(b["currency"], date_to) if translated else 1.0
        ra = avg_rate(b["currency"], pl_rate_from, date_to) if translated else 1.0
        bal = {}
        for acc_id, (op, dr, cr) in raw[b["id"]].items():
            out_bal = op + dr - cr
            bal[acc_id] = (out_bal, op, dr - cr)
        out.append((b, bal, rc, ra))
    return accs, out


def _elim(stmt, acc, vals):
    if stmt.multi and acc["intercompany"]:
        return -sum(vals)
    return 0


def profit_loss(branch_ids, date_from, date_to, force_group=False, cost_centre=None):
    branches = branch_rows(branch_ids)
    currency, translated = report_currency(branches, force_group)
    st = Statement(branches, translated, currency)
    # the year-end closing entry moves the result to retained earnings; it is not part of the year's result
    accs, data = _translated_balances(branches, translated, date_to, date_from, date_from, exclude_closing=True,
                                      cost_centre=cost_centre)
    # period movement translated at the average rate; display sign: income positive
    per_acc = defaultdict(lambda: [0] * st.n)
    for i, (b, bal, rc, ra) in enumerate(data):
        for acc_id, (_close, _op, move) in bal.items():
            if ROOT[accs[acc_id]["kind"]] in PL_ROOTS:
                per_acc[acc_id][i] = translate(-move, ra)
        if translated and b["currency"] != currency:
            st.notes.append(f"{b['code']}: 1 {b['currency']} = {ra:.6f} {currency} ({t('average rate')})")

    def block(kinds, title, sign):
        st.section(title)
        rows = []
        for acc_id in sorted(per_acc, key=lambda a: accs[a]["code"]):
            acc = accs[acc_id]
            if acc["kind"] in kinds and any(per_acc[acc_id]):
                vals = [v * sign for v in per_acc[acc_id]]
                rows.append(st.row(acc, vals, _elim(st, acc, vals), account=acc))
        vals, elim = Statement.add(rows) if rows else ([0] * st.n, 0)
        return st.row(t("Total") + " " + title, vals, elim, kind="subtotal")

    rev = block(("income",), t("Revenue"), 1)
    cogs = block(("expense_direct_cost",), t("Cost of Revenue"), -1)
    gp = st.row(t("Gross Profit"), [a - b for a, b in zip(rev["vals"], cogs["vals"])],
                rev["elim"] - cogs["elim"], kind="total")
    other = block(("income_other",), t("Other Income"), 1)
    opex = block(("expense", "expense_depreciation"), t("Operating Expenses"), -1)
    net_vals = [g + o - e for g, o, e in zip(gp["vals"], other["vals"], opex["vals"])]
    net_elim = gp["elim"] + other["elim"] - opex["elim"]
    if net_elim:
        # unmatched inter-branch income/expense: keep consolidated profit equal to the branches' sum
        st.row(t("Unmatched inter-branch difference"), [0] * st.n, -net_elim, kind="account")
        net_elim = 0
    st.row(t("Net Profit"), net_vals, net_elim, kind="grand")
    return st


def balance_sheet(branch_ids, date_to, force_group=False):
    branches = branch_rows(branch_ids)
    currency, translated = report_currency(branches, force_group)
    st = Statement(branches, translated, currency)
    fys = fy_start(date_to)
    accs, data = _translated_balances(branches, translated, date_to, fys, fys)
    per_acc = defaultdict(lambda: [0] * st.n)
    prior, current, cta = [0] * st.n, [0] * st.n, [0] * st.n
    for i, (b, bal, rc, ra) in enumerate(data):
        total = 0
        for acc_id, (close, opening, move) in bal.items():
            if ROOT[accs[acc_id]["kind"]] in PL_ROOTS:
                p, c = translate(opening, rc), translate(move, ra)
                prior[i] += p
                current[i] += c
                total += p + c
            else:
                v = translate(close, rc)
                per_acc[acc_id][i] = v
                total += v
        cta[i] = -total  # translation difference keeps each translated branch balanced
        if translated and b["currency"] != currency:
            st.notes.append(f"{b['code']}: 1 {b['currency']} = {rc:.6f} {currency} ({t('closing rate')}), "
                            f"{ra:.6f} ({t('average rate')})")

    def block(roots_kinds, title, sign):
        rows = []
        for acc_id in sorted(per_acc, key=lambda a: accs[a]["code"]):
            acc = accs[acc_id]
            if acc["kind"] in roots_kinds and any(per_acc[acc_id]):
                vals = [v * sign for v in per_acc[acc_id]]
                rows.append(st.row(acc, vals, _elim(st, acc, vals), account=acc, level=2))
        return rows

    def subtotal(label, rows, kind="subtotal"):
        vals, elim = Statement.add(rows) if rows else ([0] * st.n, 0)
        return st.row(label, vals, elim, kind=kind)

    st.section(t("Assets"))
    st.section(t("Current Assets"))
    ca = block(("asset_cash", "asset_receivable", "asset_current", "asset_prepayments"), "", 1)
    tca = subtotal(t("Total Current Assets"), ca)
    st.section(t("Non-current Assets"))
    nca = block(("asset_non_current", "asset_fixed"), "", 1)
    tnca = subtotal(t("Total Non-current Assets"), nca)
    ta = subtotal(t("Total Assets"), [tca, tnca], kind="grand")

    st.section(t("Liabilities"))
    st.section(t("Current Liabilities"))
    cl = block(("liability_payable", "liability_current"), "", -1)
    tcl = subtotal(t("Total Current Liabilities"), cl)
    st.section(t("Non-current Liabilities"))
    ncl = block(("liability_non_current",), "", -1)
    tncl = subtotal(t("Total Non-current Liabilities"), ncl)
    tl = subtotal(t("Total Liabilities"), [tcl, tncl], kind="total")

    st.section(t("Equity"))
    eq = block(("equity",), "", -1)
    if any(prior):  # results of earlier years not yet closed to retained earnings
        eq.append(st.row(t("Prior years' earnings (not closed)"), [-v for v in prior], level=2))
    eq.append(st.row(t("Current Year Earnings"), [-v for v in current], level=2))
    if any(cta):
        eq.append(st.row(t("Currency Translation Reserve"), [-v for v in cta], level=2))
    diff = ta["elim"] - sum(r["elim"] for r in [tl] + eq)
    if diff:
        eq.append(st.row(t("Unmatched inter-branch difference"), [0] * st.n, diff, level=2))
    te = subtotal(t("Total Equity"), eq, kind="total")
    subtotal(t("Total Liabilities and Equity"), [tl, te], kind="grand")
    return st


CF_SECTION = {"asset_receivable": "op", "asset_current": "op", "asset_prepayments": "op", "liability_payable": "op",
              "liability_current": "op", "asset_fixed": "inv", "asset_non_current": "inv",
              "liability_non_current": "fin", "equity": "fin"}


def cash_flow(branch_ids, date_from, date_to, force_group=False):
    """Statement of cash flows, indirect method (IAS 7).

    Cash = Bank and Cash accounts. Every line is the cash effect of a group's movement in the period: an increase in
    an asset uses cash (negative), an increase in a liability or equity provides cash (positive).
    * Year-end closing entries are left out (they move profit to retained earnings).
    * Entries with no cash and no profit-and-loss line that move value between operating, investing and financing
      (e.g. opening balances, an asset bought on credit, a loan converted to capital) are non-cash transactions:
      they are left out and counted in a note, as IAS 7 requires.
    * Depreciation (the credit to fixed-asset accounts in depreciation entries) is added back; employee provisions
      (end of service, leave) are shown under operating activities."""
    from datetime import date as _d, timedelta
    branches = branch_rows(branch_ids)
    currency, translated = report_currency(branches, force_group)
    st = Statement(branches, translated, currency)
    n = st.n
    accs = accounts_by_id()
    idx = {b["id"]: i for i, b in enumerate(branches)}
    hr = q("SELECT eosb_provision_id, leave_provision_id FROM hr_settings LIMIT 1", one=True)
    provisions = {v for v in (tuple(hr) if hr else ()) if v}

    def section(acc_id):
        kind = accs[acc_id]["kind"]
        if kind == "asset_cash":
            return "cash"
        if ROOT[kind] in PL_ROOTS:
            return "pl"
        if acc_id in provisions:
            return "op"
        return CF_SECTION.get(kind, "op")

    rows = q(f"""SELECT ml.move_id, ml.branch_id, ml.account_id, ml.debit - ml.credit AS amt
                 FROM move_lines ml JOIN moves m ON m.id = ml.move_id
                 WHERE m.state = 'posted' AND m.move_type != 'closing' AND m.date BETWEEN ? AND ?
                   AND ml.branch_id IN ({placeholders(branch_ids)})""", [date_from, date_to] + list(branch_ids))
    moves = defaultdict(list)
    for r in rows:
        moves[(r["branch_id"], r["move_id"])].append(r)
    rate_avg, rate_open, rate_close = [1.0] * n, [1.0] * n, [1.0] * n
    day_before = (_d.fromisoformat(date_from) - timedelta(days=1)).isoformat()
    for i, b in enumerate(branches):
        if translated:
            rate_avg[i] = avg_rate(b["currency"], date_from, date_to)
            rate_open[i] = rate_on(b["currency"], day_before)
            rate_close[i] = rate_on(b["currency"], date_to)
            if b["currency"] != currency:
                st.notes.append(f"{b['code']}: 1 {b['currency']} = {rate_avg[i]:.6f} {currency} ({t('average rate')}), "
                                f"{rate_close[i]:.6f} ({t('closing rate')})")
    raw = defaultdict(lambda: [0] * n)   # key -> per-branch cash effect in branch currency
    cash_move = [0] * n
    non_cash = 0
    for (branch_id, _mid), lines in moves.items():
        i = idx[branch_id]
        secs = {section(ln["account_id"]) for ln in lines}
        if "cash" not in secs and "pl" not in secs and len(secs) > 1:
            non_cash += 1
            continue
        is_dep = any(accs[ln["account_id"]]["kind"] == "expense_depreciation" for ln in lines)
        if not is_dep and "pl" in secs and any(accs[ln["account_id"]]["kind"] == "asset_fixed" for ln in lines):
            # disposal: the gain or loss is not operating cash; it belongs with the proceeds in investing
            pl = sum(-ln["amt"] for ln in lines if section(ln["account_id"]) == "pl")
            raw["disposal"][i] -= pl
            raw["fixed"][i] += pl
        for ln in lines:
            acc_id, amt = ln["account_id"], ln["amt"]
            sec, kind = section(acc_id), accs[acc_id]["kind"]
            if sec == "cash":
                cash_move[i] += amt
            elif sec == "pl":
                raw["profit"][i] += -amt
            elif kind == "asset_fixed" and is_dep:
                raw["depreciation"][i] += -amt        # credit to accumulated depreciation, added back
            elif acc_id in provisions:
                raw["provisions"][i] += -amt
            elif sec == "inv":
                raw["fixed" if kind == "asset_fixed" else "asset_non_current"][i] += -amt
            else:
                raw[kind][i] += -amt
    opening = balances(list(branch_ids), day_before)
    open_cash, close_cash = [0] * n, [0] * n
    for i, b in enumerate(branches):
        oc = sum(v[0] + v[1] - v[2] for a, v in opening[b["id"]].items() if accs[a]["kind"] == "asset_cash")
        open_cash[i] = translate(oc, rate_open[i])
        close_cash[i] = translate(oc + cash_move[i], rate_close[i])
    lines = defaultdict(lambda: [0] * n)
    for key, vals in raw.items():
        lines[key] = [translate(v, rate_avg[i]) for i, v in enumerate(vals)]

    def row(label, key):
        return st.row(label, list(lines[key]), kind="account", level=2)

    def subtotal(label, rows, kind="subtotal"):
        vals, elim = Statement.add(rows) if rows else ([0] * n, 0)
        return st.row(label, vals, elim, kind=kind)

    st.section(t("Cash flows from operating activities"))
    op = [row(t("Net Profit"), "profit")]
    st.section(t("Adjustments for non-cash items"))
    op.append(row(t("Depreciation"), "depreciation"))
    op.append(row(t("Employee provisions (end of service, leave)"), "provisions"))
    op.append(row(t("(Gain) / loss on disposal of fixed assets"), "disposal"))
    st.section(t("Changes in working capital"))
    for key, label in (("asset_receivable", "(Increase) / decrease in receivables"),
                       ("asset_current", "(Increase) / decrease in inventory and other current assets"),
                       ("asset_prepayments", "(Increase) / decrease in prepayments"),
                       ("liability_payable", "Increase / (decrease) in payables"),
                       ("liability_current", "Increase / (decrease) in other current liabilities (incl. VAT)")):
        op.append(row(t(label), key))
    net_op = subtotal(t("Net cash from operating activities"), op, "total")
    st.section(t("Cash flows from investing activities"))
    inv = [row(t("Purchase of fixed assets, net of disposal proceeds"), "fixed"),
           row(t("Other non-current assets"), "asset_non_current")]
    net_inv = subtotal(t("Net cash from investing activities"), inv, "total")
    st.section(t("Cash flows from financing activities"))
    fin = [row(t("Loans and other non-current liabilities"), "liability_non_current"),
           row(t("Capital, drawings and other equity movements"), "equity")]
    net_fin = subtotal(t("Net cash from financing activities"), fin, "total")
    net = subtotal(t("Net increase / (decrease) in cash"), [net_op, net_inv, net_fin], "grand")
    st.row(t("Cash and bank at the beginning of the period"), list(open_cash), kind="account", level=1)
    fx = [c - o - v for c, o, v in zip(close_cash, open_cash, net["vals"])]
    if any(fx):
        st.row(t("Effect of exchange rate changes on cash"), fx, kind="account", level=1)
    st.row(t("Cash and bank at the end of the period"), list(close_cash), kind="grand")
    if non_cash:
        st.notes.append(f"{non_cash} " + t("non-cash entries (no cash and no profit or loss, e.g. opening balances "
                                           "or assets bought on credit) are left out, as IAS 7 requires."))
    return st

def trial_balance(branch_ids, date_from, date_to, force_group=False, exclude_closing=False):
    branches = branch_rows(branch_ids)
    currency, translated = report_currency(branches, force_group)
    accs = accounts_by_id()
    raw = balances([b["id"] for b in branches], date_to, date_from, exclude_closing)
    rows = defaultdict(lambda: [0, 0, 0, 0])
    notes = []
    for b in branches:
        rate = rate_on(b["currency"], date_to) if translated else 1.0
        if translated and b["currency"] != currency:
            notes.append(f"{b['code']}: 1 {b['currency']} = {rate:.6f} {currency} ({t('closing rate')})")
        for acc_id, (op, dr, cr) in raw[b["id"]].items():
            r = rows[acc_id]
            r[0] += translate(op, rate)
            r[1] += translate(dr, rate)
            r[2] += translate(cr, rate)
    out = []
    for acc_id in sorted(rows, key=lambda a: accs[a]["code"]):
        op, dr, cr, _ = rows[acc_id]
        if op or dr or cr:
            out.append({"account": accs[acc_id], "opening": op, "debit": dr, "credit": cr, "closing": op + dr - cr})
    return {"rows": out, "currency": currency, "notes": notes, "branches": branches}
