import csv
import io
from collections import defaultdict
from datetime import date

from flask import Blueprint, Response, render_template, request

from .. import allowed_branch_ids, context_branch_ids, currency_decimals, has, login_required
from ..db import placeholders, q
from ..i18n import nm, t
from ..ledger import AccountingError, INVOICE_KINDS, balance_sheet, branch_rows, company, fy_start, \
    invoice_residuals, cash_flow, profit_loss, rate_on, report_currency, trial_balance
from ..money import fmt, translate
from ..coa import max_depth, nest, parse_level
from ..costcentres import NONE as NO_COST_CENTRE, options as cost_centre_options, profit_loss_by_cost_centre
from ..exports import Report, respond, statement_report
from .common import accounts_list, partners_list

bp = Blueprint("reports", __name__, url_prefix="/reports")


def filters(need_from=True):
    allowed = allowed_branch_ids()
    picked = [int(b) for b in request.args.getlist("b") if b.isdigit() and int(b) in allowed]
    ids = picked or context_branch_ids()
    today = date.today().isoformat()
    date_to = request.args.get("date_to") or today
    date_from = request.args.get("date_from") or fy_start(date_to)
    return {"branch_ids": ids, "date_from": date_from, "date_to": date_to, "need_from": need_from,
            "group": bool(request.args.get("group")), "branches": branch_rows(ids),
            "level": parse_level(request.args.get("level")), "levels": max_depth()}


STYLE = {"group": "group", "subtotal": "subtotal", "total": "total", "grand": "grand"}


def meta(f, currency, *extra):
    """The line under a report title: period, branches, currency and any filters."""
    period = f"{f['date_from']} → {f['date_to']}" if f["need_from"] else f"{t('As of')} {f['date_to']}"
    return [period, ", ".join(f"{b['code']} {nm(b)}" for b in f["branches"]), f"{t('Amounts in')} {currency}"] + \
        [e for e in extra if e]


def cost_centre_filter():
    """?cc=<id> one cost centre, ?cc=none lines without one, nothing = all lines."""
    v = request.args.get("cc", "")
    return NO_COST_CENTRE if v == "none" else int(v) if v.isdigit() else None


def csv_response(name, header, rows):
    buf = io.StringIO()
    buf.write("﻿")  # BOM so Excel opens Arabic text correctly
    w = csv.writer(buf)
    w.writerow(header)
    w.writerows(rows)
    return Response(buf.getvalue(), mimetype="text/csv",
                    headers={"Content-Disposition": f"attachment; filename={name}.csv"})


def render_statement(st, title, f, template="reports/statement.html", extra_meta=(), **ctx):
    st.rows = nest(st.rows, ("vals", "elim", "total"), f["level"])
    out = respond(statement_report(st, title, f, extra_meta), title.replace(" & ", "_").replace(" ", "_"))
    return out or render_template(template, st=st, title=title, f=f, **ctx)


@bp.route("/profit-loss")
@login_required
def pl():
    f = filters()
    if not f["branches"]:
        return render_template("reports/empty.html")
    cc = cost_centre_filter()
    try:
        st = profit_loss(f["branch_ids"], f["date_from"], f["date_to"], f["group"], cost_centre=cc)
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    ccs = cost_centre_options(cc)
    label = next((f"{c['code']} {nm(c)}" for c in ccs if c["id"] == cc), None)
    return render_statement(st, "Profit & Loss", f, cost_centres=ccs, cc=cc, cc_label=label,
                            extra_meta=[f"{t('Cost Centre')}: {t('No cost centre') if cc == 0 else label}"] if cc is not None else ())


@bp.route("/profit-loss/cost-centres")
@login_required
def pl_cost_centre():
    f = filters()
    if not f["branches"]:
        return render_template("reports/empty.html")
    try:
        data = profit_loss_by_cost_centre(f["branch_ids"], f["date_from"], f["date_to"], f["group"])
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    data["rows"] = nest(data["rows"], ("vals", "total"), f["level"])
    rep = Report("P&L by Cost Centre", meta(f, data["currency"]), currency_decimals(data["currency"]))
    rep.column(t("Account"), width=3)
    for c in data["cols"]:
        rep.column(t("No cost centre") if c.get("none") else c["code"], "num")
    rep.column(t("Total"), "num", 1.15)
    for r in data["rows"]:
        if r["kind"] == "section":
            rep.section(r["label"])
            continue
        lbl = f"{r['account']['code']}  {nm(r['account'])}" if r.get("account") else r["label"]
        rep.row([lbl] + list(r["vals"]) + [r["total"]], STYLE.get(r["kind"], "normal"), max((r.get("depth") or 1) - 1, 0))
    return respond(rep, "PL_by_Cost_Centre") or render_template("reports/cost_centre.html", data=data, f=f)


@bp.route("/balance-sheet")
@login_required
def bs():
    f = filters(need_from=False)
    if not f["branches"]:
        return render_template("reports/empty.html")
    try:
        st = balance_sheet(f["branch_ids"], f["date_to"], f["group"])
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    return render_statement(st, "Balance Sheet", f)


@bp.route("/cash-flow")
@login_required
def cf():
    f = filters()
    if not f["branches"]:
        return render_template("reports/empty.html")
    try:
        st = cash_flow(f["branch_ids"], f["date_from"], f["date_to"], f["group"])
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    return render_statement(st, "Cash Flow Statement", f)


@bp.route("/trial-balance")
@login_required
def tb():
    f = filters()
    if not f["branches"]:
        return render_template("reports/empty.html")
    try:
        data = trial_balance(f["branch_ids"], f["date_from"], f["date_to"], f["group"],
                             exclude_closing=bool(request.args.get("preclosing")))
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    totals = [sum(r[k] for r in data["rows"]) for k in ("opening", "debit", "credit", "closing")]
    data["rows"] = nest(data["rows"], ("opening", "debit", "credit", "closing"), f["level"])
    rep = Report("Trial Balance", meta(f, data["currency"], t("Before year-end closing") if request.args.get("preclosing") else ""),
                 currency_decimals(data["currency"]))
    rep.column(t("Code"), width=0.7).column(t("Account"), width=2.8)
    for lbl in ("Opening", "Debit", "Credit", "Closing"):
        rep.column(t(lbl), "num")
    for r in data["rows"]:
        a = r["account"] or r["group"]
        rep.row([a["code"], nm(a)] + [r[k] for k in ("opening", "debit", "credit", "closing")],
                "group" if r.get("kind") == "group" else "normal", max((r.get("depth") or 1) - 1, 0))
    rep.row(["", t("Total")] + totals, "grand")
    return respond(rep, "Trial_Balance") or render_template("reports/trial_balance.html", data=data, totals=totals, f=f)


@bp.route("/general-ledger")
@login_required
def gl():
    f = filters()
    account_id = request.args.get("account_id", type=int)
    partner_id = request.args.get("partner_id", type=int)
    cc = cost_centre_filter()
    groups = []
    if f["branches"]:
        ph = placeholders(f["branch_ids"])
        cond = f"ml.branch_id IN ({ph}) AND m.state = 'posted'"
        args = list(f["branch_ids"])
        if account_id:
            cond += " AND ml.account_id = ?"
            args.append(account_id)
        if partner_id:
            cond += " AND ml.partner_id = ?"
            args.append(partner_id)
        if cc is not None:
            cond += " AND COALESCE(ml.cost_centre_id, 0) = ?"
            args.append(cc)
        opening = {(r["account_id"], r["branch_id"]): r["bal"] for r in q(
            f"SELECT ml.account_id, ml.branch_id, SUM(ml.debit - ml.credit) AS bal FROM move_lines ml "
            f"JOIN moves m ON m.id = ml.move_id WHERE {cond} AND m.date < ? GROUP BY ml.account_id, ml.branch_id",
            args + [f["date_from"]])}
        lines = q(f"SELECT ml.*, m.date, m.name AS move_name, m.ref, m.id AS move_id, p.name_en AS pname_en, "
                  f"p.name_ar AS pname_ar, cc.code AS cc_code FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
                  f"LEFT JOIN partners p ON p.id = ml.partner_id LEFT JOIN cost_centres cc ON cc.id = ml.cost_centre_id "
                  f"WHERE {cond} AND m.date BETWEEN ? AND ? ORDER BY m.date, m.id, ml.id",
                  args + [f["date_from"], f["date_to"]])
        accs = {a["id"]: a for a in q("SELECT * FROM accounts")}
        bmap = {b["id"]: b for b in f["branches"]}
        by_key = defaultdict(list)
        for ln in lines:
            by_key[(ln["account_id"], ln["branch_id"])].append(ln)
        keys = set(by_key) | {k for k, v in opening.items() if v}
        for key in sorted(keys, key=lambda k: (accs[k[0]]["code"], bmap[k[1]]["code"])):
            bal = opening.get(key, 0)
            rows = []
            for ln in by_key.get(key, []):
                bal += ln["debit"] - ln["credit"]
                rows.append({**dict(ln), "balance": bal})
            groups.append({"account": accs[key[0]], "branch": bmap[key[1]], "opening": opening.get(key, 0),
                           "rows": rows, "closing": bal,
                           "debit": sum(r["debit"] for r in rows), "credit": sum(r["credit"] for r in rows)})
    curs = {gr["branch"]["currency"] for gr in groups} or {f["branches"][0]["currency"] if f["branches"] else ""}
    extra = []
    if account_id:
        a = q("SELECT code, name_en, name_ar FROM accounts WHERE id = ?", (account_id,), one=True)
        extra.append(f"{t('Account')}: {a['code']} {nm(a)}" if a else "")
    if partner_id:
        pa = q("SELECT name_en, name_ar FROM partners WHERE id = ?", (partner_id,), one=True)
        extra.append(f"{t('Partner')}: {nm(pa)}" if pa else "")
    rep = Report("General Ledger", meta(f, ", ".join(sorted(curs)), *extra),
                 currency_decimals(sorted(curs)[0]) if curs else 2, landscape=True)
    rep.column(t("Date"), width=0.8).column(t("Entry"), width=1.1).column(t("Partner"), width=1.5) \
        .column(t("Label"), width=2.2).column(t("Debit"), "num").column(t("Credit"), "num").column(t("Balance"), "num")
    for gr in groups:
        rep.section(f"{gr['account']['code']}  {nm(gr['account'])}  ·  {gr['branch']['code']} ({gr['branch']['currency']})")
        rep.row(["", "", "", t("Opening balance"), None, None, gr["opening"]], "normal")
        for r in gr["rows"]:
            rep.row([r["date"], r["move_name"] or "", nm({"name_en": r["pname_en"], "name_ar": r["pname_ar"]}) if r["pname_en"] else "",
                     r["label"] or r["ref"] or "", r["debit"] or None, r["credit"] or None, r["balance"]])
        rep.row(["", "", "", t("Closing balance"), gr["debit"], gr["credit"], gr["closing"]], "subtotal")
    out = respond(rep, "General_Ledger")
    if out:
        return out
    return render_template("reports/general_ledger.html", groups=groups, f=f, accounts=accounts_list(),
                           partners=partners_list(), account_id=account_id, partner_id=partner_id,
                           cost_centres=cost_centre_options(cc), cc=cc)


BUCKETS = [("current", "Not due"), ("b30", "1-30"), ("b60", "31-60"), ("b90", "61-90"), ("older", "90+")]


@bp.route("/aged/<side>")
@login_required
def aged(side):
    customer = side == "receivable"
    f = filters(need_from=False)
    if not f["branches"]:
        return render_template("reports/empty.html")
    currency, translated = report_currency(f["branches"], f["group"])
    kinds = ("out_invoice", "out_refund") if customer else ("in_invoice", "in_refund")
    invs = q(f"SELECT i.*, p.name_en AS pname_en, p.name_ar AS pname_ar FROM invoices i "
             f"JOIN partners p ON p.id = i.partner_id WHERE i.state = 'posted' AND i.date <= ? "
             f"AND i.kind IN (?, ?) AND i.branch_id IN ({placeholders(f['branch_ids'])})",
             [f["date_to"], *kinds] + list(f["branch_ids"]))
    res = invoice_residuals([i["id"] for i in invs], as_of=f["date_to"], base=True)
    try:
        rates = {b["id"]: (rate_on(b["currency"], f["date_to"]) if translated else 1.0) for b in f["branches"]}
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    as_of = date.fromisoformat(f["date_to"])
    partners = {}
    detail = request.args.get("detail")
    for inv in invs:
        r = res.get(inv["id"], 0)
        if not r:
            continue
        amt = translate(r * INVOICE_KINDS[inv["kind"]]["sign"], rates[inv["branch_id"]])
        days = (as_of - date.fromisoformat(inv["due_date"] or inv["date"])).days
        b = "current" if days <= 0 else "b30" if days <= 30 else "b60" if days <= 60 else "b90" if days <= 90 else "older"
        p = partners.setdefault(inv["partner_id"], {"name": nm({"name_en": inv["pname_en"], "name_ar": inv["pname_ar"]}),
                                                    "id": inv["partner_id"], "total": 0, "invoices": [],
                                                    **{k: 0 for k, _ in BUCKETS}})
        p[b] += amt
        p["total"] += amt
        if detail:
            p["invoices"].append({"inv": inv, "amount": amt, "bucket": b, "days": days})
    rows = sorted(partners.values(), key=lambda p: -p["total"])
    totals = {k: sum(p[k] for p in rows) for k, _ in BUCKETS + [("total", "")]}
    rep = Report("Aged Receivables" if customer else "Aged Payables", meta(f, currency), currency_decimals(currency))
    rep.column(t("Customer") if customer else t("Vendor"), width=2.6)
    for _, lbl in BUCKETS:
        rep.column(t(lbl), "num")
    rep.column(t("Total"), "num", 1.15)
    for p in rows:
        rep.row([p["name"]] + [p[k] for k, _ in BUCKETS] + [p["total"]], "group" if detail else "normal")
        for d in p["invoices"] if detail else []:
            rep.row([f"{d['inv']['number']}  ·  {d['inv']['due_date'] or d['inv']['date']}  ·  {d['days']} {t('days')}"] +
                    [d["amount"] if k == d["bucket"] else None for k, _ in BUCKETS] + [d["amount"]], indent=1)
    rep.row([t("Total")] + [totals[k] for k, _ in BUCKETS] + [totals["total"]], "grand")
    out = respond(rep, f"Aged_{side}")
    if out:
        return out
    return render_template("reports/aged.html", rows=rows, totals=totals, buckets=BUCKETS, f=f, side=side,
                           currency=currency, detail=detail)


@bp.route("/vat")
@login_required
def vat():
    f = filters()
    sections = []
    for b in f["branches"]:
        rows = q("""
            SELECT i.kind, tx.id AS tax_id, tx.name_en, tx.name_ar, tx.rate,
                   SUM(il.subtotal) AS base, SUM(il.tax_amount) AS tax, COUNT(DISTINCT i.id) AS docs
            FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id LEFT JOIN taxes tx ON tx.id = il.tax_id
            WHERE i.state = 'posted' AND i.branch_id = ? AND i.date BETWEEN ? AND ?
            GROUP BY i.kind, tx.id ORDER BY tx.rate DESC""", (b["id"], f["date_from"], f["date_to"]))
        sales, purchases = defaultdict(lambda: [0, 0, None]), defaultdict(lambda: [0, 0, None])
        for r in rows:
            target = sales if r["kind"].startswith("out_") else purchases
            sign = INVOICE_KINDS[r["kind"]]["sign"]
            entry = target[r["tax_id"]]
            entry[0] += sign * r["base"]
            entry[1] += sign * r["tax"]
            entry[2] = r if r["tax_id"] else {"name_en": "No tax", "name_ar": "بدون ضريبة", "rate": 0}
        from ..pos import pos_tax_totals
        for tax_id, (base, tx) in pos_tax_totals(b["id"], f["date_from"], f["date_to"]).items():
            entry = sales[tax_id]
            entry[0] += base
            entry[1] += tx
            if entry[2] is None:
                entry[2] = q("SELECT * FROM taxes WHERE id = ?", (tax_id,), one=True) if tax_id else \
                    {"name_en": "No tax", "name_ar": "بدون ضريبة", "rate": 0}
        out_tax = sum(v[1] for v in sales.values())
        in_tax = sum(v[1] for v in purchases.values())
        sections.append({"branch": b, "sales": list(sales.values()), "purchases": list(purchases.values()),
                         "out_tax": out_tax, "in_tax": in_tax, "net": out_tax - in_tax})
    if f["branches"]:
        cur = ", ".join(sorted({b["currency"] for b in f["branches"]}))
        rep = Report("VAT Report", meta(f, cur, f"{t('VAT Number')}: {company()['vat_no']}" if company()["vat_no"] else ""),
                     currency_decimals(f["branches"][0]["currency"]))
        rep.column(t("Tax"), width=2.6).column(t("Rate"), "qty", 0.6).column(t("Taxable amount"), "num").column(t("VAT"), "num")
        for sec in sections:
            b = sec["branch"]
            rep.section(f"{b['code']} — {nm(b)}{'  ·  ' + t('VAT') + ' ' + b['vat_no'] if b['vat_no'] else ''}  ({b['currency']})")
            for title, key in (("Sales (output VAT)", "sales"), ("Purchases (input VAT)", "purchases")):
                rep.row([t(title), None, None, None], "group")
                for base, tax, tx in sec[key]:
                    rep.row([nm(tx), tx["rate"], base, tax], indent=1)
            rep.row([t("Output VAT"), None, None, sec["out_tax"]], "subtotal")
            rep.row([t("Input VAT"), None, None, sec["in_tax"]], "subtotal")
            rep.row([t("Net VAT payable") if sec["net"] >= 0 else t("Net VAT refundable"), None, None, sec["net"]], "grand")
        out = respond(rep, "VAT_Report")
        if out:
            return out
    return render_template("reports/vat.html", sections=sections, f=f, company=company())


@bp.route("/sales")
@login_required
def sales():
    f = filters()
    group = request.args.get("by", "product")
    if group not in ("product", "customer", "branch", "month"):
        group = "product"
    if not f["branches"]:
        return render_template("reports/empty.html")
    currency, translated = report_currency(f["branches"], f["group"])
    try:
        from ..ledger import avg_rate
        rates = {b["id"]: (avg_rate(b["currency"], f["date_from"], f["date_to"]) if translated else 1.0)
                 for b in f["branches"]}
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    key = {"product": "COALESCE(il.product_id, -1)", "customer": "i.partner_id", "branch": "i.branch_id",
           "month": "substr(i.date, 1, 7)"}[group]
    rows = q(f"""
        SELECT {key} AS k, i.branch_id, MAX(pr.code) AS pcode, MAX(pr.name_en) AS pname_en, MAX(pr.name_ar) AS pname_ar,
               MAX(pa.name_en) AS cname_en, MAX(pa.name_ar) AS cname_ar,
               SUM(il.quantity * CASE WHEN i.kind = 'out_refund' THEN -1 ELSE 1 END) AS qty,
               SUM(il.subtotal * CASE WHEN i.kind = 'out_refund' THEN -1 ELSE 1 END) AS amount,
               COUNT(DISTINCT i.id) AS docs
        FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id JOIN partners pa ON pa.id = i.partner_id
        LEFT JOIN products pr ON pr.id = il.product_id
        WHERE i.state = 'posted' AND i.kind IN ('out_invoice', 'out_refund') AND i.date BETWEEN ? AND ?
          AND i.branch_id IN ({placeholders(f['branch_ids'])})
        GROUP BY k, i.branch_id""", [f["date_from"], f["date_to"]] + list(f["branch_ids"]))
    pos_key = {"product": "l.product_id", "customer": "o.partner_id", "branch": "s.branch_id",
               "month": "substr(s.closed_at, 1, 7)"}[group]
    rows = list(rows) + list(q(f"""
        SELECT {pos_key} AS k, s.branch_id, MAX(pr.code) AS pcode, MAX(pr.name_en) AS pname_en, MAX(pr.name_ar) AS pname_ar,
               MAX(pa.name_en) AS cname_en, MAX(pa.name_ar) AS cname_ar, SUM(l.quantity) AS qty, SUM(l.subtotal) AS amount,
               COUNT(DISTINCT o.id) AS docs
        FROM pos_order_lines l JOIN pos_orders o ON o.id = l.order_id JOIN pos_sessions s ON s.id = o.session_id
        LEFT JOIN partners pa ON pa.id = o.partner_id LEFT JOIN products pr ON pr.id = l.product_id
        WHERE s.state = 'closed' AND substr(s.closed_at, 1, 10) BETWEEN ? AND ? AND s.branch_id IN ({placeholders(f['branch_ids'])})
        GROUP BY k, s.branch_id""", [f["date_from"], f["date_to"]] + list(f["branch_ids"])))
    bmap = {b["id"]: b for b in f["branches"]}
    agg = {}
    for r in rows:
        if group == "product":
            label = f"{r['pcode'] or ''} {nm({'name_en': r['pname_en'], 'name_ar': r['pname_ar']})}".strip() \
                if r["k"] != -1 else t("Without product")
        elif group == "customer":
            label = nm({"name_en": r["cname_en"], "name_ar": r["cname_ar"]})
        elif group == "branch":
            label = f"{bmap[r['k']]['code']} — {nm(bmap[r['k']])}"
        else:
            label = r["k"]
        a = agg.setdefault(r["k"], {"label": label, "qty": 0, "amount": 0, "docs": 0})
        a["qty"] += r["qty"] or 0
        a["amount"] += translate(r["amount"] or 0, rates[r["branch_id"]])
        a["docs"] += r["docs"]
    out = sorted(agg.values(), key=(lambda a: a["label"]) if group == "month" else (lambda a: -a["amount"]))
    total = sum(a["amount"] for a in out)
    rep = Report("Sales Analysis", meta(f, currency, f"{t('By')} {t(group.capitalize())}"), currency_decimals(currency))
    rep.column(t(group.capitalize()), width=3).column(t("Quantity"), "qty", 0.8).column(t("Documents"), "qty", 0.8) \
        .column(t("Amount"), "num").column(t("Share"), "text", 0.6)
    for a in out:
        rep.row([a["label"], a["qty"], a["docs"], a["amount"], f"{a['amount'] * 100 / total:.1f}%" if total else ""])
    rep.row([t("Total"), None, sum(a["docs"] for a in out), total, "100%"], "grand")
    resp = respond(rep, "Sales_Analysis")
    if resp:
        return resp
    return render_template("reports/sales.html", rows=out, total=total, f=f, group=group, currency=currency)


@bp.route("/stock-matching")
@login_required
def stock_matching():
    """Month-end check: goods invoiced but not delivered (revenue without COGS) and the other mismatches."""
    from ..stock import MATCH_SECTIONS, stock_matching as matching
    f = filters(need_from=False)
    if not has("inventory") or not f["branches"]:
        return render_template("reports/empty.html")
    data = matching(f["branch_ids"], f["date_to"])
    bmap = {b["id"]: b for b in f["branches"]}
    rep = Report("Stock vs Invoices", meta(f, ", ".join(sorted({b["currency"] for b in f["branches"]}))),
                 currency_decimals(f["branches"][0]["currency"]), landscape=True)
    rep.column(t("Branch"), width=0.6).column(t("Document"), width=1.1).column(t("Date"), width=0.8) \
        .column(t("Partner"), width=1.6).column(t("Product"), width=2).column(t("Quantity"), "qty", 0.7) \
        .column(t("Value"), "num").column(t("Estimated cost"), "num")
    for key, label, _ in MATCH_SECTIONS:
        if data[key]:
            rep.section(t(label))
            for r in data[key]:
                rep.row([bmap[r["branch_id"]]["code"], r["doc"] or "", r["date"],
                         nm({"name_en": r["pname_en"], "name_ar": r["pname_ar"]}),
                         f"{r['pcode'] or ''} {nm({'name_en': r['prname_en'], 'name_ar': r['prname_ar']})}".strip(),
                         r["qty"], r["value"], r["cost"]])
    resp = respond(rep, "Stock_vs_Invoices")
    if resp:
        return resp
    return render_template("reports/stock_matching.html", f=f, data=data, sections=MATCH_SECTIONS, bmap=bmap)


@bp.route("/partner-ledger")
@login_required
def partner_ledger():
    """Receivable and payable movements per partner: opening, debit, credit and closing for the period, with every
    entry (and a running balance) when one partner is chosen. Amounts in each branch's currency."""
    f = filters()
    side = request.args.get("side", "customer")
    if side not in ("customer", "vendor", "all"):
        side = "customer"
    partner_id = request.args.get("partner_id", type=int)
    kinds = {"customer": ("asset_receivable",), "vendor": ("liability_payable",),
             "all": ("asset_receivable", "liability_payable")}[side]
    summary, detail = [], []
    if f["branches"]:
        cond = (f"m.state = 'posted' AND ml.partner_id IS NOT NULL AND ml.branch_id IN ({placeholders(f['branch_ids'])}) "
                f"AND a.kind IN ({placeholders(kinds)})")
        args = list(f["branch_ids"]) + list(kinds)
        if partner_id:
            cond += " AND ml.partner_id = ?"
            args.append(partner_id)
        base = "FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id " \
               "JOIN branches b ON b.id = ml.branch_id JOIN partners p ON p.id = ml.partner_id"
        rows = q(f"SELECT ml.partner_id, p.name_en, p.name_ar, b.currency, "
                 f"SUM(CASE WHEN m.date < ? THEN ml.debit - ml.credit ELSE 0 END) AS opening, "
                 f"SUM(CASE WHEN m.date BETWEEN ? AND ? THEN ml.debit ELSE 0 END) AS debit, "
                 f"SUM(CASE WHEN m.date BETWEEN ? AND ? THEN ml.credit ELSE 0 END) AS credit "
                 f"{base} WHERE {cond} AND m.date <= ? GROUP BY ml.partner_id, b.currency ORDER BY p.name_en, b.currency",
                 [f["date_from"], f["date_from"], f["date_to"], f["date_from"], f["date_to"]] + args + [f["date_to"]])
        for r in rows:
            d = dict(r)
            d["closing"] = d["opening"] + d["debit"] - d["credit"]
            if d["opening"] or d["debit"] or d["credit"]:
                summary.append(d)
        if partner_id:
            for cur in sorted({r["currency"] for r in summary}):
                op = sum(r["opening"] for r in summary if r["currency"] == cur)
                bal, lines = op, []
                for ln in q(f"SELECT ml.*, m.date, m.name AS move_name, m.ref, m.id AS mid, b.code AS bcode, a.code AS acode "
                            f"{base} WHERE {cond} AND b.currency = ? AND m.date BETWEEN ? AND ? ORDER BY m.date, m.id, ml.id",
                            args + [cur, f["date_from"], f["date_to"]]):
                    bal += ln["debit"] - ln["credit"]
                    lines.append({**dict(ln), "balance": bal})
                detail.append({"currency": cur, "opening": op, "rows": lines, "closing": bal,
                               "debit": sum(x["debit"] for x in lines), "credit": sum(x["credit"] for x in lines)})
    title = {"customer": "Customer Ledger", "vendor": "Vendor Ledger", "all": "Partner Ledger"}[side]
    pa = q("SELECT * FROM partners WHERE id = ?", (partner_id,), one=True) if partner_id else None
    curs = sorted({r["currency"] for r in summary}) or ([f["branches"][0]["currency"]] if f["branches"] else [])
    rep = Report(title, meta(f, ", ".join(curs), f"{t('Partner')}: {nm(pa)}" if pa else ""),
                 currency_decimals(curs[0]) if curs else 2, landscape=bool(partner_id), subtitle=nm(pa) if pa else None)
    if partner_id:
        rep.column(t("Date"), width=0.8).column(t("Entry"), width=1.2).column(t("Branch"), width=0.6) \
            .column(t("Label"), width=2.4).column(t("Debit"), "num").column(t("Credit"), "num").column(t("Balance"), "num")
        for g in detail:
            rep.section(g["currency"])
            rep.row(["", "", "", t("Opening balance"), None, None, g["opening"]])
            for r in g["rows"]:
                rep.row([r["date"], r["move_name"] or "", r["bcode"], r["label"] or r["ref"] or "", r["debit"] or None,
                         r["credit"] or None, r["balance"]])
            rep.row(["", "", "", t("Closing balance"), g["debit"], g["credit"], g["closing"]], "subtotal")
    else:
        rep.column(t("Partner"), width=2.6).column(t("Currency"), width=0.6).column(t("Opening balance"), "num") \
            .column(t("Debit"), "num").column(t("Credit"), "num").column(t("Closing balance"), "num")
        for r in summary:
            rep.row([nm(r), r["currency"], r["opening"], r["debit"], r["credit"], r["closing"]])
        for cur in curs:
            sub = [r for r in summary if r["currency"] == cur]
            rep.row([f"{t('Total')} {cur}", "", sum(r["opening"] for r in sub), sum(r["debit"] for r in sub),
                     sum(r["credit"] for r in sub), sum(r["closing"] for r in sub)], "grand")
    out = respond(rep, title.replace(" ", "_"))
    if out:
        return out
    partners = partners_list(side if side != "all" else None)
    return render_template("reports/partner_ledger.html", f=f, side=side, partner_id=partner_id, pa=pa,
                           summary=summary, detail=detail, partners=partners, title=title, curs=curs)
