"""Trading analysis: gross margin, reorder suggestions, 13-week cash forecast and supplier performance.

All amounts are in the branch currency (one branch) or translated to the group currency (several branches), like the
other reports. Figures come from posted documents and done stock moves, never from estimates typed by the user.
"""
import math
from collections import defaultdict
from datetime import date, timedelta

from .db import placeholders, q
from .i18n import nm, t
from .ledger import avg_rate, invoice_residuals, rate_on, report_currency
from .money import translate

EPS = 1e-9


def rates_for(branches, d_from, d_to, average=True):
    currency, translated = report_currency(branches)
    rates = {b["id"]: ((avg_rate(b["currency"], d_from, d_to) if average else rate_on(b["currency"], d_to))
                       if translated else 1.0) for b in branches}
    return currency, rates


# ---------------------------------------------------------------- gross margin

MARGIN_GROUPS = ("product", "customer", "category", "month", "branch")


def gross_margin(branches, d_from, d_to, group="product"):
    """[{key, label, qty, revenue, cost, margin, pct}] and totals.
    Revenue: invoice lines on revenue accounts (posted invoices less credit notes) plus closed POS sessions, without
    VAT; advances and other balance-sheet lines are left out.
    Cost: the cost of goods sold booked when the goods left (deliveries and POS) less goods returned by customers,
    at average cost. Services have no cost of goods, so their margin is 100%."""
    ids = [b["id"] for b in branches]
    ph = placeholders(ids)
    currency, rates = rates_for(branches, d_from, d_to)
    key_inv = {"product": "COALESCE(il.product_id, 0)", "customer": "i.partner_id",
               "category": "COALESCE(pr.category_id, 0)", "month": "substr(i.date, 1, 7)", "branch": "i.branch_id"}[group]
    key_pos = {"product": "l.product_id", "customer": "COALESCE(o.partner_id, 0)", "category": "COALESCE(pr.category_id, 0)",
               "month": "substr(s.closed_at, 1, 7)", "branch": "s.branch_id"}[group]
    key_pk = {"product": "pl.product_id", "customer": "COALESCE(pk.partner_id, 0)",
              "category": "COALESCE(pr.category_id, 0)", "month": "substr(pk.date, 1, 7)", "branch": "pk.branch_id"}[group]
    agg = defaultdict(lambda: {"qty": 0.0, "revenue": 0, "cost": 0})
    for r in q(f"SELECT {key_inv} AS k, i.branch_id, SUM(il.quantity * CASE WHEN i.kind = 'out_refund' THEN -1 ELSE 1 END) AS qty, "
               f"SUM(ROUND(il.subtotal * i.rate) * CASE WHEN i.kind = 'out_refund' THEN -1 ELSE 1 END) AS amt "
               f"FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id LEFT JOIN products pr ON pr.id = il.product_id "
               f"JOIN accounts a ON a.id = il.account_id "
               f"WHERE i.state = 'posted' AND i.kind IN ('out_invoice', 'out_refund') AND i.date BETWEEN ? AND ? "
               f"AND a.kind = 'income' AND i.branch_id IN ({ph}) GROUP BY k, i.branch_id",
               [d_from, d_to] + ids):
        a = agg[r["k"]]
        a["qty"] += r["qty"] or 0
        a["revenue"] += translate(r["amt"] or 0, rates[r["branch_id"]])
    for r in q(f"SELECT {key_pos} AS k, s.branch_id, SUM(l.quantity) AS qty, SUM(l.subtotal) AS amt "
               f"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 "
               f"LEFT JOIN products pr ON pr.id = l.product_id WHERE s.state = 'closed' "
               f"AND substr(s.closed_at, 1, 10) BETWEEN ? AND ? AND s.branch_id IN ({ph}) GROUP BY k, s.branch_id",
               [d_from, d_to] + ids):
        a = agg[r["k"]]
        a["qty"] += r["qty"] or 0
        a["revenue"] += translate(r["amt"] or 0, rates[r["branch_id"]])
    for r in q(f"SELECT {key_pk} AS k, pk.branch_id, -SUM(pl.value_signed) AS cost FROM picking_lines pl "
               f"JOIN pickings pk ON pk.id = pl.picking_id JOIN products pr ON pr.id = pl.product_id "
               f"WHERE pk.state = 'done' AND pk.kind IN ('out', 'pos', 'return_in') AND pk.date BETWEEN ? AND ? "
               f"AND pk.branch_id IN ({ph}) GROUP BY k, pk.branch_id", [d_from, d_to] + ids):
        agg[r["k"]]["cost"] += translate(r["cost"] or 0, rates[r["branch_id"]])
    labels = _labels(group, list(agg), branches)
    rows = []
    for k, a in agg.items():
        if not (a["revenue"] or a["cost"]):
            continue
        m = a["revenue"] - a["cost"]
        rows.append({"key": k, "label": labels.get(k, str(k)), "qty": a["qty"], "revenue": a["revenue"],
                     "cost": a["cost"], "margin": m, "pct": (m * 100.0 / a["revenue"]) if a["revenue"] else None})
    rows.sort(key=(lambda r: r["label"]) if group == "month" else (lambda r: -r["margin"]))
    rev, cost = sum(r["revenue"] for r in rows), sum(r["cost"] for r in rows)
    total = {"revenue": rev, "cost": cost, "margin": rev - cost, "pct": ((rev - cost) * 100.0 / rev) if rev else None}
    return rows, total, currency


def ledger_gross(branches, d_from, d_to, rates=None):
    """Revenue (income accounts) and cost of revenue (direct cost accounts) as posted in the ledger, by month:
    {'YYYY-MM': [revenue, cost]} and the totals. These are the Profit & Loss figures."""
    ids = [b["id"] for b in branches]
    if rates is None:
        _, rates = rates_for(branches, d_from, d_to)
    months = defaultdict(lambda: [0, 0])
    for r in q(f"SELECT ml.branch_id, substr(m.date, 1, 7) AS ym, a.kind, SUM(ml.debit - ml.credit) AS bal "
               f"FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id "
               f"WHERE m.state = 'posted' AND m.move_type != 'closing' AND m.date BETWEEN ? AND ? "
               f"AND a.kind IN ('income', 'expense_direct_cost') AND ml.branch_id IN ({placeholders(ids)}) "
               f"GROUP BY ml.branch_id, ym, a.kind", [d_from, d_to] + ids):
        v = translate(r["bal"] or 0, rates[r["branch_id"]])
        if r["kind"] == "income":
            months[r["ym"]][0] -= v
        else:
            months[r["ym"]][1] += v
    rev = sum(v[0] for v in months.values())
    cost = sum(v[1] for v in months.values())
    return dict(months), {"revenue": rev, "cost": cost, "margin": rev - cost,
                          "pct": ((rev - cost) * 100.0 / rev) if rev else None}


def _labels(group, keys, branches):
    keys = [k for k in keys if k not in (None, 0)]
    out = {0: t({"product": "Without product", "customer": "Walk-in customers (POS)",
                 "category": "Without category"}.get(group, "—"))}
    if not keys:
        return out
    if group == "product":
        for r in q(f"SELECT id, code, name_en, name_ar FROM products WHERE id IN ({placeholders(keys)})", keys):
            out[r["id"]] = f"{r['code'] or ''} {nm(r)}".strip()
    elif group == "customer":
        for r in q(f"SELECT id, name_en, name_ar FROM partners WHERE id IN ({placeholders(keys)})", keys):
            out[r["id"]] = nm(r)
    elif group == "category":
        for r in q(f"SELECT id, name_en, name_ar FROM product_categories WHERE id IN ({placeholders(keys)})", keys):
            out[r["id"]] = nm(r)
    elif group == "branch":
        for b in branches:
            out[b["id"]] = f"{b['code']} — {nm(b)}"
    else:
        out.update({k: k for k in keys})
    return out


# ---------------------------------------------------------------- reorder suggestions

USAGE_DAYS = 90
DEFAULT_LEAD = 7
COVER_DAYS = 30


def last_vendor(product_ids):
    """{product_id: (vendor id, last unit price in branch currency, date)} from purchase orders, receipts and vendor
    bills; the latest wins."""
    out = {}
    if not product_ids:
        return out
    ph = placeholders(product_ids)
    rows = list(q(f"SELECT ol.product_id, o.partner_id, ol.price_unit AS price, o.date FROM order_lines ol "
                  f"JOIN orders o ON o.id = ol.order_id WHERE o.kind = 'purchase' AND o.state = 'confirmed' "
                  f"AND ol.product_id IN ({ph})", list(product_ids)))
    rows += list(q(f"SELECT pl.product_id, pk.partner_id, pl.unit_cost AS price, pk.date FROM picking_lines pl "
                   f"JOIN pickings pk ON pk.id = pl.picking_id JOIN partners pa ON pa.id = pk.partner_id "
                   f"WHERE pk.kind = 'in' AND pk.state = 'done' AND pa.is_vendor = 1 AND pl.product_id IN ({ph})",
                   list(product_ids)))
    rows += list(q(f"SELECT il.product_id, i.partner_id, ROUND(il.price_unit * i.rate) AS price, i.date "
                   f"FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id WHERE i.kind = 'in_invoice' "
                   f"AND i.state = 'posted' AND il.product_id IN ({ph})", list(product_ids)))
    for r in sorted(rows, key=lambda r: r["date"]):
        out[r["product_id"]] = (r["partner_id"], int(r["price"] or 0), r["date"])
    return out


def vendor_lead_days():
    """{vendor id: average days from purchase order to receipt} over the last year."""
    since = (date.today() - timedelta(days=365)).isoformat()
    out = {}
    for r in q("SELECT o.partner_id, AVG(julianday(pk.date) - julianday(o.date)) AS d FROM pickings pk "
               "JOIN orders o ON o.id = pk.order_id WHERE pk.kind = 'in' AND pk.state = 'done' AND pk.date >= ? "
               "GROUP BY o.partner_id", (since,)):
        out[r["partner_id"]] = max(int(round(r["d"] or 0)), 1)
    return out


def reorder_suggestions(branch_ids, warehouse_id=None, show_all=False):
    """One row per stockable product and warehouse that should be ordered now.
    usage per day = units delivered or sold (less customer returns) in the last 90 days / 90
    reorder point  = the larger of the reorder level and the usage expected during the lead time
    order up to    = 'Reorder up to' on the product, else reorder point + 30 days of usage
    suggested qty  = order up to − (on hand + incoming − outgoing + already on draft purchase orders)"""
    if not branch_ids:
        return []
    ph = placeholders(branch_ids)
    whs = q(f"SELECT * FROM warehouses WHERE active = 1 AND branch_id IN ({ph})" +
            (" AND id = ?" if warehouse_id else "") + " ORDER BY code",
            list(branch_ids) + ([warehouse_id] if warehouse_id else []))
    if not whs:
        return []
    wids = [w["id"] for w in whs]
    wph = placeholders(wids)
    prods = {p["id"]: p for p in q("SELECT * FROM products WHERE type = 'stock' AND active = 1")}
    onhand = {(r["product_id"], r["warehouse_id"]): r["qty"] for r in q(
        f"SELECT product_id, warehouse_id, qty FROM stock_quants WHERE warehouse_id IN ({wph})", wids)}
    open_ = defaultdict(float)
    for r in q(f"SELECT pl.product_id, pk.warehouse_id, SUM(CASE WHEN pk.kind IN ('in', 'return_in') THEN pl.quantity "
               f"ELSE -pl.quantity END) AS q FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id "
               f"WHERE pk.state = 'ready' AND pk.kind IN ('in', 'return_in', 'out', 'return_out') AND pk.warehouse_id IN ({wph}) "
               f"GROUP BY pl.product_id, pk.warehouse_id", wids):
        open_[(r["product_id"], r["warehouse_id"])] += r["q"] or 0
    draft = defaultdict(float)
    for r in q(f"SELECT ol.product_id, o.warehouse_id, SUM(ol.quantity) AS q FROM order_lines ol JOIN orders o "
               f"ON o.id = ol.order_id WHERE o.kind = 'purchase' AND o.state IN ('draft', 'to_approve') "
               f"AND o.warehouse_id IN ({wph}) GROUP BY ol.product_id, o.warehouse_id", wids):
        draft[(r["product_id"], r["warehouse_id"])] += r["q"] or 0
    since = (date.today() - timedelta(days=USAGE_DAYS)).isoformat()
    usage = defaultdict(float)
    for r in q(f"SELECT pl.product_id, pk.warehouse_id, -SUM(pl.qty_signed) AS q FROM picking_lines pl "
               f"JOIN pickings pk ON pk.id = pl.picking_id WHERE pk.state = 'done' AND pk.kind IN ('out', 'pos', 'return_in') "
               f"AND pk.date >= ? AND pk.warehouse_id IN ({wph}) GROUP BY pl.product_id, pk.warehouse_id", [since] + wids):
        usage[(r["product_id"], r["warehouse_id"])] = max(r["q"] or 0, 0)
    vendors = last_vendor(list(prods))
    leads = vendor_lead_days()
    vnames = {r["id"]: r for r in q("SELECT id, name_en, name_ar FROM partners WHERE is_vendor = 1")}
    rows = []
    for w in whs:
        for pid, p in prods.items():
            k = (pid, w["id"])
            daily = usage[k] / USAGE_DAYS
            have = onhand.get(k, 0.0)
            forecast = have + open_[k]
            if not (p["min_qty"] or p["max_qty"] or daily > EPS or have > EPS or show_all):
                continue
            v = vendors.get(pid)
            lead = p["lead_days"] or (leads.get(v[0]) if v else None) or DEFAULT_LEAD
            point = max(p["min_qty"] or 0, daily * lead)
            target = p["max_qty"] or (point + daily * COVER_DAYS)
            if p["min_qty"] and not p["max_qty"]:
                target = max(target, (p["min_qty"] or 0) * 2)
            position = forecast + draft[k]
            need = forecast <= point + EPS and (point > EPS)
            qty = math.ceil(max(target - position, 0)) if need else 0
            if not (qty > 0 or show_all):
                continue
            rows.append({"product": p, "warehouse": w, "on_hand": have, "open": open_[k], "forecast": forecast,
                         "draft": draft[k], "daily": daily, "lead": lead, "point": point, "target": target,
                         "qty": qty, "cover": (forecast / daily) if daily > EPS else None,
                         "vendor_id": v[0] if v else None, "vendor": vnames.get(v[0]) if v else None,
                         "price": v[1] if v else p["purchase_price"]})
    rows.sort(key=lambda r: (r["cover"] if r["cover"] is not None else 9e9, r["product"]["code"] or ""))
    return rows


def create_reorder_pos(picks, d, user_id=None):
    """picks: [(product_id, warehouse_id, vendor_id, qty)] -> draft purchase orders, one per vendor and warehouse."""
    from .ledger import AccountingError, audit, compute_invoice_line, effective_tax, now
    from .db import x
    from .procurement import _default_terms, check_vendor
    from .stock import ORDER_PREFIX, next_number
    groups = defaultdict(list)
    for pid, wid, vid, qty in picks:
        if qty > EPS:
            if not vid:
                raise AccountingError(t("Choose a vendor for every line."))
            groups[(vid, wid)].append((pid, qty))
    if not groups:
        raise AccountingError(t("Nothing to order."))
    made = []
    prices = last_vendor([p for (_, _), ls in groups.items() for p, _ in ls])
    for (vid, wid), lines in groups.items():
        check_vendor(vid, for_po=True)
        wh = q("SELECT * FROM warehouses WHERE id = ?", (wid,), one=True)
        dec = q("SELECT c.decimals FROM branches b JOIN currencies c ON c.code = b.currency WHERE b.id = ?",
                (wh["branch_id"],), one=True)["decimals"]
        out = []
        for pid, qty in lines:
            p = q("SELECT * FROM products WHERE id = ?", (pid,), one=True)
            price = prices[pid][1] if pid in prices and prices[pid][0] == vid else p["purchase_price"]
            tax_id = effective_tax(wh["branch_id"], p["purchase_tax_id"], "purchase")
            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)
            out.append((p, qty, price, tax_id, sub, tax))
        untaxed, tax = sum(o[4] for o in out), sum(o[5] for o in out)
        lead = max((o[0]["lead_days"] or 0) for o in out) or vendor_lead_days().get(vid) or DEFAULT_LEAD
        expected = (date.fromisoformat(d) + timedelta(days=lead)).isoformat()
        po = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, expected_date, notes, state, "
               "amount_untaxed, amount_tax, amount_total, terms, created_by, created_at) "
               "VALUES ('purchase',?,?,?,?,?,?,?,'draft',?,?,?,?,?,?)",
               (next_number("orders", "number", ORDER_PREFIX["purchase"], wh["branch_id"], d), wh["branch_id"], wid,
                vid, d, expected, t("From reorder suggestions"), untaxed, tax, untaxed + tax, _default_terms("po"),
                user_id, now()))
        for i, (p, qty, price, tax_id, sub, tx) in enumerate(out):
            x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, "
              "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)", (po, i, p["id"], nm(p), qty, price, tax_id, sub, tx))
        audit(user_id, "create", "order", po, "reorder")
        made.append(po)
    return made


# ---------------------------------------------------------------- 13-week cash forecast

def week_starts(today=None, n=13):
    today = today or date.today()
    monday = today - timedelta(days=today.weekday())
    return [monday + timedelta(weeks=i) for i in range(n)]


def cash_forecast(branches, weeks=13, include_orders=True):
    """Weekly cash in and out from what is already known: open invoices and bills by due date (overdue ones in the
    first week), post-dated cheques by cheque date, recurring invoices and bills, the last payroll repeated at each
    month end, and (optionally) confirmed orders not yet invoiced at their expected date plus the partner's terms."""
    from . import has
    from .recurring import amount as rec_amount, finished, occurrence
    ids = [b["id"] for b in branches]
    ph = placeholders(ids)
    today = date.today()
    starts = week_starts(today, weeks)
    end = starts[-1] + timedelta(days=6)
    currency, rates = rates_for(branches, today.isoformat(), today.isoformat(), average=False)

    def week(d):
        d = date.fromisoformat(d[:10]) if isinstance(d, str) else d
        if d < starts[0]:
            return 0
        i = (d - starts[0]).days // 7
        return i if i < weeks else None

    lines = {}  # (direction, label) -> [amount per week]
    overdue = defaultdict(int)

    def add(direction, label, d, amount, branch_id):
        i = week(d)
        if i is None or not amount:
            return
        v = translate(amount, rates[branch_id])
        lines.setdefault((direction, label), [0] * weeks)[i] += v
        if (date.fromisoformat(d[:10]) if isinstance(d, str) else d) < today:
            overdue[direction] += v

    opening = 0
    for r in q(f"SELECT ml.branch_id, SUM(ml.debit - ml.credit) AS bal FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
               f"JOIN accounts a ON a.id = ml.account_id WHERE m.state = 'posted' AND a.kind = 'asset_cash' "
               f"AND ml.branch_id IN ({ph}) GROUP BY ml.branch_id", ids):
        opening += translate(r["bal"] or 0, rates[r["branch_id"]])
    invs = q(f"SELECT id, kind, branch_id, COALESCE(due_date, date) AS due FROM invoices WHERE state = 'posted' "
             f"AND kind IN ('out_invoice', 'in_invoice', 'out_refund', 'in_refund') AND branch_id IN ({ph})", ids)
    res = invoice_residuals([i["id"] for i in invs], base=True)
    for i in invs:
        r = res.get(i["id"], 0)
        if r <= 0:
            continue
        if i["kind"] == "out_invoice":
            add("in", "Customer invoices", i["due"], r, i["branch_id"])
        elif i["kind"] == "in_invoice":
            add("out", "Vendor bills", i["due"], r, i["branch_id"])
        elif i["kind"] == "out_refund":
            add("out", "Credit notes to refund", i["due"], r, i["branch_id"])
        else:
            add("in", "Vendor refunds", i["due"], r, i["branch_id"])
    for c in q(f"SELECT direction, branch_id, cheque_date, amount FROM cheques WHERE state IN ('registered', 'deposited') "
               f"AND branch_id IN ({ph})", ids):
        if c["direction"] == "received":
            add("in", "Post-dated cheques received", c["cheque_date"], c["amount"], c["branch_id"])
        else:
            add("out", "Post-dated cheques issued", c["cheque_date"], c["amount"], c["branch_id"])
    for rec in q(f"SELECT * FROM recurring WHERE state = 'active' AND kind IN ('out_invoice', 'in_invoice') "
                 f"AND branch_id IN ({ph})", ids):
        amt = rec_amount(rec)
        n = rec["done_count"]
        while not finished(rec, n):
            d = occurrence(rec, n) + timedelta(days=int(rec["due_days"] or 0))
            if d > end:
                break
            if d >= today:
                add("in" if rec["kind"] == "out_invoice" else "out",
                    "Recurring invoices" if rec["kind"] == "out_invoice" else "Recurring bills", d, amt, rec["branch_id"])
            n += 1
            if n > 400:
                break
    if include_orders:
        from .stock import order_progress
        for o in q(f"SELECT o.*, p.payment_terms FROM orders o JOIN partners p ON p.id = o.partner_id "
                   f"WHERE o.state = 'confirmed' AND o.branch_id IN ({ph})", ids):
            prog = order_progress(o["id"])
            rest = 0
            for ln in q("SELECT * FROM order_lines WHERE order_id = ?", (o["id"],)):
                left = ln["quantity"] - prog[ln["id"]]["invoiced"]
                if left > EPS and ln["quantity"]:
                    rest += int(round((ln["subtotal"] + ln["tax_amount"]) * left / ln["quantity"]))
            if rest > 0:
                d = date.fromisoformat(o["expected_date"] or o["date"])
                d = max(d, today) + timedelta(days=int(o["payment_terms"] or 30))
                add("in" if o["kind"] == "sale" else "out",
                    "Sales orders not yet invoiced" if o["kind"] == "sale" else "Purchase orders not yet billed",
                    d, rest, o["branch_id"])
    if has("hr"):
        for b in ids:
            last = q("SELECT r.id FROM payroll_runs r WHERE r.branch_id = ? AND r.state IN ('posted', 'paid') "
                     "ORDER BY r.period DESC LIMIT 1", (b,), one=True)
            if not last:
                continue
            net = q("SELECT COALESCE(SUM(net), 0) FROM payroll_lines WHERE run_id = ?", (last["id"],), one=True)[0]
            m = date(today.year, today.month, 1)
            from .recurring import add_months
            import calendar
            for k in range(5):
                mm = add_months(m, k)
                d = mm.replace(day=calendar.monthrange(mm.year, mm.month)[1])
                paid = q("SELECT 1 FROM payroll_runs WHERE branch_id = ? AND period = ? AND state = 'paid'",
                         (b, d.strftime("%Y-%m")), one=True)
                if d >= today and not paid:
                    add("out", "Salaries (last payroll repeated)", d, net, b)
    order = {"in": ["Customer invoices", "Post-dated cheques received", "Recurring invoices",
                    "Sales orders not yet invoiced", "Vendor refunds"],
             "out": ["Vendor bills", "Post-dated cheques issued", "Recurring bills", "Purchase orders not yet billed",
                     "Salaries (last payroll repeated)", "Credit notes to refund"]}
    inflow = [(lbl, lines[("in", lbl)]) for lbl in order["in"] if ("in", lbl) in lines]
    outflow = [(lbl, lines[("out", lbl)]) for lbl in order["out"] if ("out", lbl) in lines]
    tin = [sum(v[i] for _, v in inflow) for i in range(weeks)]
    tout = [sum(v[i] for _, v in outflow) for i in range(weeks)]
    closing, bal = [], opening
    for i in range(weeks):
        bal += tin[i] - tout[i]
        closing.append(bal)
    return {"weeks": starts, "opening": opening, "inflow": inflow, "outflow": outflow, "total_in": tin,
            "total_out": tout, "net": [a - b for a, b in zip(tin, tout)], "closing": closing,
            "currency": currency, "overdue": overdue, "lowest": min(closing) if closing else opening}


# ---------------------------------------------------------------- supplier performance

def supplier_performance(branch_ids, d_from, d_to):
    """Per vendor, over purchase orders dated in the period:
    on time  = receipts done on or before the order's expected date / all receipts against those orders
    fill     = quantity received / quantity ordered, for orders whose expected date has passed
    lead     = average days from order to receipt
    returns  = value returned to the vendor / value received
    price    = billed price vs ordered price on billed lines (+ = billed above the order)
    score    = 40% on time + 30% fill + 15% returns + 15% price, out of 100"""
    if not branch_ids:
        return []
    ph = placeholders(branch_ids)
    today = date.today().isoformat()
    stats = defaultdict(lambda: defaultdict(float))
    for o in q(f"SELECT o.id, o.partner_id, o.date, COALESCE(o.expected_date, o.date) AS exp, o.amount_untaxed "
               f"FROM orders o WHERE o.kind = 'purchase' AND o.state = 'confirmed' AND o.date BETWEEN ? AND ? "
               f"AND o.branch_id IN ({ph})", [d_from, d_to] + list(branch_ids)):
        s = stats[o["partner_id"]]
        s["orders"] += 1
        s["value"] += o["amount_untaxed"]
        for r in q("SELECT pk.date, SUM(pl.qty_signed) AS qty, SUM(pl.value_signed) AS value FROM pickings pk "
                   "JOIN picking_lines pl ON pl.picking_id = pk.id WHERE pk.order_id = ? AND pk.kind = 'in' "
                   "AND pk.state = 'done' GROUP BY pk.id", (o["id"],)):
            s["receipts"] += 1
            s["on_time"] += 1 if r["date"] <= o["exp"] else 0
            s["lead"] += (date.fromisoformat(r["date"]) - date.fromisoformat(o["date"])).days
            s["recv_value"] += r["value"] or 0
        if o["exp"] < today:
            ordered = q("SELECT COALESCE(SUM(ol.quantity), 0) FROM order_lines ol JOIN products p ON p.id = ol.product_id "
                        "WHERE ol.order_id = ? AND p.type = 'stock'", (o["id"],), one=True)[0]
            got = q("SELECT COALESCE(SUM(pl.qty_signed), 0) FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id "
                    "WHERE pk.order_id = ? AND pk.kind = 'in' AND pk.state = 'done'", (o["id"],), one=True)[0]
            if ordered:
                s["ordered"] += ordered
                s["received"] += min(got, ordered)
        for r in q("SELECT SUM(il.quantity * ol.price_unit) AS po, SUM(il.subtotal) AS bill FROM invoice_lines il "
                   "JOIN invoices i ON i.id = il.invoice_id JOIN order_lines ol ON ol.id = il.order_line_id "
                   "WHERE ol.order_id = ? AND i.kind = 'in_invoice' AND i.state = 'posted'", (o["id"],)):
            s["po_price"] += r["po"] or 0
            s["bill_price"] += r["bill"] or 0
    for r in q(f"SELECT pk.partner_id, -SUM(pl.value_signed) AS v FROM pickings pk JOIN picking_lines pl "
               f"ON pl.picking_id = pk.id WHERE pk.kind = 'return_out' AND pk.state = 'done' AND pk.date BETWEEN ? AND ? "
               f"AND pk.branch_id IN ({ph}) GROUP BY pk.partner_id", [d_from, d_to] + list(branch_ids)):
        if r["partner_id"] in stats:
            stats[r["partner_id"]]["returned"] += r["v"] or 0
    if not stats:
        return []
    names = {r["id"]: r for r in q(f"SELECT * FROM partners WHERE id IN ({placeholders(stats)})", list(stats))}
    rows = []
    for vid, s in stats.items():
        on_time = s["on_time"] * 100 / s["receipts"] if s["receipts"] else None
        fill = s["received"] * 100 / s["ordered"] if s["ordered"] else None
        returns = s["returned"] * 100 / s["recv_value"] if s["recv_value"] else (0.0 if s["receipts"] else None)
        price = (s["bill_price"] - s["po_price"]) * 100 / s["po_price"] if s["po_price"] else None
        parts = [(on_time, 40), (fill, 30), (None if returns is None else max(0, 100 - returns * 10), 15),
                 (None if price is None else max(0, 100 - max(price, 0) * 10), 15)]
        known = [(v, w) for v, w in parts if v is not None]
        score = sum(v * w for v, w in known) / sum(w for _, w in known) if known else None
        rows.append({"vendor": names.get(vid), "vendor_id": vid, "orders": int(s["orders"]), "value": s["value"],
                     "receipts": int(s["receipts"]), "on_time": on_time, "fill": fill,
                     "lead": s["lead"] / s["receipts"] if s["receipts"] else None, "returns": returns,
                     "price": price, "score": score})
    rows.sort(key=lambda r: -(r["score"] if r["score"] is not None else -1))
    return rows
