"""Procurement workflow: Material Request -> Purchase Requisition -> RFQ -> Purchase Order.

Material requests and requisitions go draft -> submitted -> approved / rejected. An approved material request
is fulfilled by issuing stock from the warehouse or by raising a purchase requisition. An approved requisition
becomes an RFQ sent to several vendors (quotes are compared and one vendor is awarded) or a direct PO.
"""
from collections import defaultdict

from .db import q, x
from .i18n import t
from .ledger import AccountingError, audit, compute_invoice_line, now
from .stock import EPS, ORDER_PREFIX, create_picking, next_number, validate_picking

DOCS = {
    "mr": {"table": "material_requests", "lines": "mr_lines", "fk": "mr_id", "prefix": "MR"},
    "pr": {"table": "purchase_requisitions", "lines": "pr_lines", "fk": "pr_id", "prefix": "PR"},
}


def get_doc(kind, doc_id):
    return q(f"SELECT * FROM {DOCS[kind]['table']} WHERE id = ?", (doc_id,), one=True)


def doc_amount(kind, doc_id):
    """Estimated value of a material request (purchase prices) or a requisition (estimated prices)."""
    if kind == "pr":
        return int(q("SELECT COALESCE(SUM(quantity * est_price), 0) FROM pr_lines WHERE pr_id = ?", (doc_id,), one=True)[0])
    return int(q("SELECT COALESCE(SUM(l.quantity * COALESCE(p.purchase_price, 0)), 0) FROM mr_lines l "
                 "LEFT JOIN products p ON p.id = l.product_id WHERE l.mr_id = ?", (doc_id,), one=True)[0])


def transition(kind, doc_id, action, user_id, approver=False, reason=None, override=False):
    """Workflow actions shared by material requests and purchase requisitions. Submitting starts the approval
    workflow (no matching rule: approved at once); approve / reject act on the open approval."""
    from . import workflow
    d = DOCS[kind]
    doc = get_doc(kind, doc_id)
    state = doc["state"]
    if action == "submit" and state == "draft":
        if not q(f"SELECT 1 FROM {d['lines']} WHERE {d['fk']} = ?", (doc_id,)):
            raise AccountingError(t("Add at least one line."))
        project_id = doc["project_id"]
        if project_id:
            from . import projects
            projects.parse(project_id, doc["branch_id"])
            projects.warn(projects.check_budget(project_id, projects.mr_amounts(kind, doc_id),
                                                f"{t(workflow.DOC_TYPES[kind]['label'])} {doc['number']}"))
        x(f"UPDATE {d['table']} SET state = 'submitted' WHERE id = ?", (doc_id,))
        cur = q("SELECT currency FROM branches WHERE id = ?", (doc["branch_id"],), one=True)["currency"]
        rid = workflow.submit(kind, doc_id, doc_amount(kind, doc_id), doc["number"], user_id, doc["branch_id"], cur,
                              doc["department"], project_id)
        if rid is None:
            _set_approved(kind, doc_id, user_id)
        audit(user_id, action, kind, doc_id, doc["number"])
        return
    if action in ("approve", "reject") and state == "submitted":
        workflow.act_on_doc(kind, doc_id, user_id, action, reason, override)
        return
    if action == "draft" and state in ("submitted", "rejected", "cancelled"):
        new = "draft"
        workflow.cancel(kind, doc_id, user_id, t("Returned to draft"))
    elif action == "cancel" and state in ("draft", "submitted", "approved"):
        new = "cancelled"
        workflow.cancel(kind, doc_id, user_id, t("Cancelled"))
    elif action == "done" and state == "approved":
        new = "done"
    else:
        raise AccountingError(t("This action is not allowed in the current state."))
    x(f"UPDATE {d['table']} SET state = ? WHERE id = ?", (new, doc_id))
    audit(user_id, action, kind, doc_id, doc["number"])


def _set_approved(kind, doc_id, user_id):
    x(f"UPDATE {DOCS[kind]['table']} SET state = 'approved', approved_by = ?, approved_at = ?, reject_reason = NULL "
      f"WHERE id = ?", (user_id, now(), doc_id))


def _set_rejected(kind, doc_id, user_id, reason):
    x(f"UPDATE {DOCS[kind]['table']} SET state = 'rejected', approved_by = ?, approved_at = ?, reject_reason = ? "
      f"WHERE id = ?", (user_id, now(), reason, doc_id))


def _register_handlers():
    from . import workflow
    for kind in ("mr", "pr"):
        workflow.handler(kind, "approved")(lambda doc_id, user_id, k=kind: _set_approved(k, doc_id, user_id))
        workflow.handler(kind, "rejected")(lambda doc_id, user_id, reason, k=kind: _set_rejected(k, doc_id, user_id, reason))


_register_handlers()


def save_doc(kind, doc_id, header, lines, user_id):
    d = DOCS[kind]
    if not lines:
        raise AccountingError(t("Add at least one line."))
    cols = list(header)
    if doc_id:
        x(f"UPDATE {d['table']} SET {', '.join(c + ' = ?' for c in cols)} WHERE id = ?",
          [header[c] for c in cols] + [doc_id])
        x(f"DELETE FROM {d['lines']} WHERE {d['fk']} = ?", (doc_id,))
    else:
        header = {**header, "number": next_number(d["table"], "number", d["prefix"], header["branch_id"], header["date"]),
                  "requested_by": user_id, "state": "draft", "created_at": now()}
        cols = list(header)
        doc_id = x(f"INSERT INTO {d['table']} ({', '.join(cols)}) VALUES ({', '.join('?' * len(cols))})",
                   [header[c] for c in cols])
    for ln in lines:
        lc = list(ln)
        x(f"INSERT INTO {d['lines']} ({d['fk']}, {', '.join(lc)}) VALUES (?, {', '.join('?' * len(lc))})",
          [doc_id] + [ln[c] for c in lc])
    audit(user_id, "save", kind, doc_id)
    return doc_id


# ---------------------------------------------------------------- material requests

def issue_material_request(mr_id, d, user_id=None):
    """Issue the stock items of an approved material request (charged to each product's expense account)."""
    mr = get_doc("mr", mr_id)
    if mr["state"] != "approved":
        raise AccountingError(t("Approve the request first."))
    if not mr["warehouse_id"]:
        raise AccountingError(t("Choose a warehouse."))
    lines = [{"product_id": ln["product_id"], "quantity": ln["quantity"]} for ln in q(
        "SELECT ml.* FROM mr_lines ml JOIN products p ON p.id = ml.product_id WHERE ml.mr_id = ? AND p.type = 'stock'",
        (mr_id,))]
    if not lines:
        raise AccountingError(t("Nothing to move."))
    pid = create_picking("out", mr["branch_id"], mr["warehouse_id"], d, lines,
                         notes=f"{t('Material Request')} {mr['number']}", user_id=user_id)
    from .projects import cost_centre
    x("UPDATE pickings SET mr_id = ?, cost_centre_id = ? WHERE id = ?", (mr_id, cost_centre(mr["project_id"]), pid))
    validate_picking(pid, d=d, user_id=user_id)
    transition("mr", mr_id, "done", user_id)
    return pid


def requisition_from_mr(mr_id, d, user_id=None):
    mr = get_doc("mr", mr_id)
    if mr["state"] != "approved":
        raise AccountingError(t("Approve the request first."))
    lines = []
    for ln in q("SELECT ml.*, p.purchase_price FROM mr_lines ml LEFT JOIN products p ON p.id = ml.product_id "
                "WHERE ml.mr_id = ?", (mr_id,)):
        lines.append({"product_id": ln["product_id"], "description": ln["description"], "quantity": ln["quantity"],
                      "est_price": ln["purchase_price"] or 0, "vendor_id": None})
    pr_id = save_doc("pr", None, {"branch_id": mr["branch_id"], "mr_id": mr_id, "department": mr["department"],
                                  "project_id": mr["project_id"], "date": d, "required_date": mr["required_date"],
                                  "justification": f"{t('Material Request')} {mr['number']}: {mr['purpose'] or ''}"},
                     lines, user_id)
    transition("mr", mr_id, "done", user_id)
    return pr_id


# ---------------------------------------------------------------- RFQs

def check_vendor(partner_id, for_po=False):
    from . import has
    from .ledger import company
    p = q("SELECT * FROM partners WHERE id = ?", (partner_id,), one=True)
    if not p:
        raise AccountingError(t("Choose a partner."))
    if has("vendors"):
        if p["vendor_status"] == "blocked":
            raise AccountingError(f"{p['name_en']}: {t('this vendor is blocked.')}")
        if for_po and company()["require_approved_vendors"] and p["vendor_status"] != "approved":
            raise AccountingError(f"{p['name_en']}: {t('purchase orders need an approved vendor.')}")
    return p


RFQ_HEADER = ("closing_at", "validity_days", "payment_terms", "incoterms", "delivery_place", "sealed", "terms")
RFQ_LINE = ("product_id", "item_code", "description", "specification", "manufacturer", "part_number", "uom",
            "quantity", "required_date", "allow_alternative", "pr_line_id", "mr_line_id")
INCOTERMS = ("EXW", "FCA", "FAS", "FOB", "CFR", "CIF", "CPT", "CIP", "DAP", "DPU", "DDP")


def _product_defaults(ln):
    """Item code and unit from the product when the line does not give them."""
    if ln.get("product_id"):
        p = q("SELECT code, uom, name_en FROM products WHERE id = ?", (ln["product_id"],), one=True)
        if p:
            ln.setdefault("item_code", p["code"])
            ln["item_code"] = ln.get("item_code") or p["code"]
            ln["uom"] = ln.get("uom") or p["uom"]
            ln["description"] = ln.get("description") or p["name_en"]
    return ln


def _write_rfq_lines(rfq_id, lines):
    x("DELETE FROM rfq_lines WHERE rfq_id = ?", (rfq_id,))
    for ln in lines:
        ln = _product_defaults(dict(ln))
        if ln["quantity"] <= 0:
            raise AccountingError(t("Quantities must be greater than zero."))
        x(f"INSERT INTO rfq_lines (rfq_id, {', '.join(RFQ_LINE)}) VALUES (?, {', '.join('?' * len(RFQ_LINE))})",
          [rfq_id] + [ln.get(c) if c != "allow_alternative" else (1 if ln.get(c) else 0) for c in RFQ_LINE])


def create_rfq(branch_id, d, deadline, lines, vendor_ids, pr_id=None, notes=None, user_id=None, project_id=None,
               header=None, mr_id=None):
    """A draft RFQ. Vendors can be added later; at least one is needed to send it."""
    if not lines:
        raise AccountingError(t("Add at least one line."))
    for v in vendor_ids:
        check_vendor(v)
    header = {k: v for k, v in (header or {}).items() if k in RFQ_HEADER}
    if deadline and not header.get("closing_at"):
        header["closing_at"] = f"{deadline}T17:00"
    cols = ["number", "branch_id", "pr_id", "mr_id", "date", "deadline", "notes", "state", "created_by", "created_at",
            "project_id"] + list(header)
    vals = [next_number("rfqs", "number", "RFQ", branch_id, d), branch_id, pr_id, mr_id, d,
            (header.get("closing_at") or "")[:10] or deadline, notes, "draft", user_id, now(), project_id] + list(header.values())
    rfq_id = x(f"INSERT INTO rfqs ({', '.join(cols)}) VALUES ({', '.join('?' * len(cols))})", vals)
    _write_rfq_lines(rfq_id, lines)
    for v in dict.fromkeys(vendor_ids):
        x("INSERT INTO rfq_vendors (rfq_id, partner_id) VALUES (?,?)", (rfq_id, v))
    audit(user_id, "create", "rfq", rfq_id)
    return rfq_id


def update_rfq(rfq_id, header, lines, vendor_ids, notes=None, project_id=None, user_id=None):
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    if rfq["state"] != "draft":
        raise AccountingError(t("Only draft documents can be edited."))
    if not lines:
        raise AccountingError(t("Add at least one line."))
    header = {k: v for k, v in header.items() if k in RFQ_HEADER}
    header["deadline"] = (header.get("closing_at") or "")[:10] or None
    header["notes"], header["project_id"] = notes, project_id
    x(f"UPDATE rfqs SET {', '.join(k + ' = ?' for k in header)} WHERE id = ?", list(header.values()) + [rfq_id])
    _write_rfq_lines(rfq_id, lines)
    keep = set(vendor_ids)
    for v in keep:
        check_vendor(v)
        x("INSERT OR IGNORE INTO rfq_vendors (rfq_id, partner_id) VALUES (?,?)", (rfq_id, v))
    for rv in q("SELECT * FROM rfq_vendors WHERE rfq_id = ?", (rfq_id,)):
        if rv["partner_id"] not in keep and rv["state"] == "invited":
            x("DELETE FROM rfq_vendors WHERE id = ?", (rv["id"],))
    audit(user_id, "save", "rfq", rfq_id)


def rfq_from_pr(pr_id, d, deadline, vendor_ids, user_id=None):
    pr = get_doc("pr", pr_id)
    if pr["state"] != "approved":
        raise AccountingError(t("Approve the requisition first."))
    lines = [{"product_id": ln["product_id"], "description": ln["description"], "quantity": ln["quantity"],
              "pr_line_id": ln["id"], "required_date": pr["required_date"]}
             for ln in q("SELECT * FROM pr_lines WHERE pr_id = ?", (pr_id,))]
    suggested = [r["vendor_id"] for r in q("SELECT DISTINCT vendor_id FROM pr_lines WHERE pr_id = ? AND vendor_id "
                                           "IS NOT NULL", (pr_id,))]
    rfq_id = create_rfq(pr["branch_id"], d, deadline, lines, list(vendor_ids) or suggested, pr_id, user_id=user_id,
                        project_id=pr["project_id"], mr_id=pr["mr_id"],
                        header={"terms": _default_terms("rfq")})
    transition("pr", pr_id, "done", user_id)
    return rfq_id


def closed_for_quotes(rfq):
    """Past the closing time (quotations are no longer accepted)."""
    from datetime import datetime
    return bool(rfq["closing_at"]) and datetime.now().strftime("%Y-%m-%dT%H:%M") > rfq["closing_at"]


def is_sealed(rfq):
    """Sealed and not yet opened: nobody sees the prices."""
    return bool(rfq["sealed"]) and not rfq["opened_at"]


def save_quote(rfq_vendor_id, prices, taxes, quote_ref=None, quote_date=None, lead_days=None, notes=None,
               declined=False, user_id=None, offered=None, alternatives=None, valid_until=None, payment_terms=None,
               late_ok=False):
    rv = q("SELECT rv.*, r.state AS rstate FROM rfq_vendors rv JOIN rfqs r ON r.id = rv.rfq_id WHERE rv.id = ?",
           (rfq_vendor_id,), one=True)
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rv["rfq_id"],), one=True)
    if rv["rstate"] not in ("draft", "sent"):
        raise AccountingError(t("This action is not allowed in the current state."))
    if closed_for_quotes(rfq) and not late_ok:
        raise AccountingError(t("The RFQ is closed; quotations are no longer accepted. Extend the closing time first."))
    offered, alternatives = offered or {}, alternatives or {}
    allowed = {r["id"]: r["allow_alternative"] for r in q("SELECT id, allow_alternative FROM rfq_lines WHERE rfq_id = ?",
                                                           (rv["rfq_id"],))}
    x("DELETE FROM rfq_quotes WHERE rfq_vendor_id = ?", (rfq_vendor_id,))
    if not declined:
        for line_id, price in prices.items():
            if price is not None:
                if price < 0:
                    raise AccountingError(t("Prices cannot be negative."))
                alt = 1 if alternatives.get(line_id) else 0
                if alt and not allowed.get(line_id):
                    raise AccountingError(t("This line does not accept alternatives."))
                x("INSERT INTO rfq_quotes (rfq_vendor_id, rfq_line_id, price, tax_id, offered, is_alternative) "
                  "VALUES (?,?,?,?,?,?)", (rfq_vendor_id, line_id, price, taxes.get(line_id), offered.get(line_id), alt))
    x("UPDATE rfq_vendors SET state = ?, quote_ref = ?, quote_date = ?, lead_days = ?, notes = ?, valid_until = ?, "
      "payment_terms = ?, received_at = ?, entered_by = ? WHERE id = ?",
      ("declined" if declined else "quoted", quote_ref, quote_date, lead_days, notes, valid_until, payment_terms, now(),
       user_id, rfq_vendor_id))
    x("UPDATE rfqs SET state = 'sent' WHERE id = ? AND state = 'draft'", (rv["rfq_id"],))
    audit(user_id, "quote", "rfq", rv["rfq_id"])


def rfq_comparison(rfq_id):
    lines = q("SELECT rl.*, p.code, p.name_en, p.name_ar, p.uom FROM rfq_lines rl LEFT JOIN products p "
              "ON p.id = rl.product_id WHERE rl.rfq_id = ? ORDER BY rl.id", (rfq_id,))
    vendors = q("SELECT rv.*, pa.name_en, pa.name_ar, pa.vendor_status FROM rfq_vendors rv JOIN partners pa "
                "ON pa.id = rv.partner_id WHERE rv.rfq_id = ? ORDER BY rv.id", (rfq_id,))
    quotes = {(r["rfq_vendor_id"], r["rfq_line_id"]): r for r in q(
        "SELECT rq.*, tx.rate FROM rfq_quotes rq JOIN rfq_vendors rv ON rv.id = rq.rfq_vendor_id "
        "LEFT JOIN taxes tx ON tx.id = rq.tax_id WHERE rv.rfq_id = ?", (rfq_id,))}
    best = {}
    for ln in lines:
        prices = [(quotes[(v["id"], ln["id"])]["price"], v["id"]) for v in vendors if (v["id"], ln["id"]) in quotes]
        if prices:
            best[ln["id"]] = min(prices)[1]
    totals = defaultdict(int)
    complete = {}
    for v in vendors:
        complete[v["id"]] = v["state"] in ("quoted", "awarded") and all((v["id"], ln["id"]) in quotes for ln in lines)
        for ln in lines:
            qt = quotes.get((v["id"], ln["id"]))
            if qt:
                totals[v["id"]] += int(round(qt["price"] * ln["quantity"]))
    ranked = sorted((tot, vid) for vid, tot in totals.items() if complete[vid])
    return {"lines": lines, "vendors": vendors, "quotes": quotes, "best": best, "totals": totals,
            "complete": complete, "best_vendor": ranked[0][1] if ranked else None}


def award_rfq(rfq_id, rfq_vendor_id, warehouse_id, d, user_id=None):
    """Create a draft purchase order for the awarded vendor at the quoted prices."""
    from .money import round_cur
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    if rfq["state"] not in ("draft", "sent", "award_pending"):
        raise AccountingError(t("This action is not allowed in the current state."))
    if is_sealed(rfq):
        raise AccountingError(t("Open the sealed bids first."))
    rv = q("SELECT * FROM rfq_vendors WHERE id = ? AND rfq_id = ?", (rfq_vendor_id, rfq_id), one=True)
    if not rv or rv["state"] != "quoted":
        raise AccountingError(t("The awarded vendor must have a quotation."))
    check_vendor(rv["partner_id"], for_po=True)
    dec = q("SELECT c.decimals FROM branches b JOIN currencies c ON c.code = b.currency WHERE b.id = ?",
            (rfq["branch_id"],), one=True)["decimals"]
    order_lines = []
    for ln in q("SELECT * FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (rfq_id,)):
        qt = q("SELECT rq.*, tx.rate FROM rfq_quotes rq LEFT JOIN taxes tx ON tx.id = rq.tax_id "
               "WHERE rq.rfq_vendor_id = ? AND rq.rfq_line_id = ?", (rfq_vendor_id, ln["id"]), one=True)
        if not qt:
            raise AccountingError(t("The awarded vendor has not quoted every line."))
        sub, tax = compute_invoice_line(ln["quantity"], qt["price"], qt["rate"] or 0, dec)
        order_lines.append((ln, qt, sub, tax))
    untaxed = sum(o[2] for o in order_lines)
    tax = sum(o[3] for o in order_lines)
    from .projects import cost_centre
    po_id = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, expected_date, ref, notes, "
              "state, amount_untaxed, amount_tax, amount_total, pr_id, rfq_id, created_by, created_at, project_id, "
              "cost_centre_id, mr_id, terms) VALUES ('purchase',?,?,?,?,?,date(?, ?),?,?,'draft',?,?,?,?,?,?,?,?,?,?,?)",
              (next_number("orders", "number", ORDER_PREFIX["purchase"], rfq["branch_id"], d), rfq["branch_id"],
               warehouse_id, rv["partner_id"], d, d, f"+{rv['lead_days'] or 0} days", rv["quote_ref"],
               f"{t('From')} {rfq['number']}", untaxed, tax, untaxed + tax, rfq["pr_id"], rfq_id, user_id, now(),
               rfq["project_id"], cost_centre(rfq["project_id"]), rfq["mr_id"], _default_terms("po")))
    for i, (ln, qt, sub, tx) in enumerate(order_lines):
        x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, "
          "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)",
          (po_id, i, ln["product_id"], ln["description"], ln["quantity"], qt["price"], qt["tax_id"], sub, tx))
    x("UPDATE rfq_vendors SET state = 'awarded' WHERE id = ?", (rfq_vendor_id,))
    x("UPDATE rfqs SET state = 'awarded', awarded_vendor_id = ? WHERE id = ?", (rv["partner_id"], rfq_id))
    audit(user_id, "award", "rfq", rfq_id, f"PO {po_id}")
    return po_id


def po_from_pr(pr_id, vendor_id, warehouse_id, d, user_id=None):
    pr = get_doc("pr", pr_id)
    if pr["state"] != "approved":
        raise AccountingError(t("Approve the requisition first."))
    check_vendor(vendor_id, for_po=True)
    dec = q("SELECT c.decimals FROM branches b JOIN currencies c ON c.code = b.currency WHERE b.id = ?",
            (pr["branch_id"],), one=True)["decimals"]
    from .ledger import effective_tax
    lines = []
    for ln in q("SELECT pl.*, p.purchase_tax_id FROM pr_lines pl LEFT JOIN products p ON p.id = pl.product_id "
                "WHERE pl.pr_id = ?", (pr_id,)):
        tax_id = effective_tax(pr["branch_id"], ln["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(ln["quantity"], ln["est_price"], rate, dec)
        lines.append(({**dict(ln), "tax_id": tax_id}, sub, tax))
    untaxed = sum(o[1] for o in lines)
    tax = sum(o[2] for o in lines)
    from .projects import cost_centre
    po_id = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, expected_date, notes, state, "
              "amount_untaxed, amount_tax, amount_total, pr_id, created_by, created_at, project_id, cost_centre_id) "
              "VALUES ('purchase',?,?,?,?,?,?,?,'draft',?,?,?,?,?,?,?,?)",
              (next_number("orders", "number", ORDER_PREFIX["purchase"], pr["branch_id"], d), pr["branch_id"],
               warehouse_id, vendor_id, d, pr["required_date"], f"{t('From')} {pr['number']}", untaxed, tax,
               untaxed + tax, pr_id, user_id, now(), pr["project_id"], cost_centre(pr["project_id"])))
    x("UPDATE orders SET mr_id = ?, terms = ? WHERE id = ?", (pr["mr_id"], _default_terms("po"), po_id))
    for i, (ln, sub, tx) in enumerate(lines):
        x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, "
          "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)",
          (po_id, i, ln["product_id"], ln["description"], ln["quantity"], ln["est_price"], ln["tax_id"], sub, tx))
    transition("pr", pr_id, "done", user_id)
    return po_id


# ---------------------------------------------------------------- Phase 3: MR -> RFQ / PO, sending, sealed bids, award

def _default_terms(doc_type):
    from .workflow import default_terms
    return default_terms(doc_type) or None


def rfq_from_mr(mr_id, d, user_id=None):
    """An approved material request straight to a draft RFQ (no purchase requisition needed)."""
    mr = get_doc("mr", mr_id)
    if mr["state"] != "approved":
        raise AccountingError(t("Approve the request first."))
    lines = [{"product_id": ln["product_id"], "description": ln["description"], "quantity": ln["quantity"],
              "mr_line_id": ln["id"], "required_date": mr["required_date"]}
             for ln in q("SELECT * FROM mr_lines WHERE mr_id = ? ORDER BY id", (mr_id,))]
    rfq_id = create_rfq(mr["branch_id"], d, None, lines, [], notes=mr["purpose"], user_id=user_id,
                        project_id=mr["project_id"], mr_id=mr_id,
                        header={"terms": _default_terms("rfq")})
    transition("mr", mr_id, "done", user_id)
    return rfq_id


def po_from_mr(mr_id, vendor_id, warehouse_id, d, user_id=None):
    """An approved material request straight to a draft purchase order at the products' purchase prices."""
    from .ledger import effective_tax
    from .projects import cost_centre
    mr = get_doc("mr", mr_id)
    if mr["state"] != "approved":
        raise AccountingError(t("Approve the request first."))
    check_vendor(vendor_id, for_po=True)
    dec = q("SELECT c.decimals FROM branches b JOIN currencies c ON c.code = b.currency WHERE b.id = ?",
            (mr["branch_id"],), one=True)["decimals"]
    lines = []
    for ln in q("SELECT ml.*, p.purchase_price, p.purchase_tax_id FROM mr_lines ml LEFT JOIN products p "
                "ON p.id = ml.product_id WHERE ml.mr_id = ? ORDER BY ml.id", (mr_id,)):
        tax_id = effective_tax(mr["branch_id"], ln["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(ln["quantity"], ln["purchase_price"] or 0, rate, dec)
        lines.append((ln, tax_id, sub, tax))
    untaxed, tax = sum(o[2] for o in lines), sum(o[3] for o in lines)
    po_id = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, expected_date, notes, state, "
              "amount_untaxed, amount_tax, amount_total, mr_id, project_id, cost_centre_id, terms, created_by, created_at) "
              "VALUES ('purchase',?,?,?,?,?,?,?,'draft',?,?,?,?,?,?,?,?,?)",
              (next_number("orders", "number", ORDER_PREFIX["purchase"], mr["branch_id"], d), mr["branch_id"],
               warehouse_id or mr["warehouse_id"], vendor_id, d, mr["required_date"], f"{t('From')} {mr['number']}",
               untaxed, tax, untaxed + tax, mr_id, mr["project_id"], cost_centre(mr["project_id"]),
               _default_terms("po"), user_id, now()))
    for i, (ln, tax_id, sub, tx) in enumerate(lines):
        x("INSERT INTO order_lines (order_id, sequence, product_id, description, quantity, price_unit, tax_id, "
          "subtotal, tax_amount) VALUES (?,?,?,?,?,?,?,?,?)",
          (po_id, i, ln["product_id"], ln["description"], ln["quantity"], ln["purchase_price"] or 0, tax_id, sub, tx))
    transition("mr", mr_id, "done", user_id)
    return po_id


def approved_vendors(category=None):
    from . import has
    sql = "SELECT * FROM partners WHERE is_vendor = 1 AND active = 1"
    args = []
    if has("vendors"):
        sql += " AND vendor_status = 'approved'"
    if category:
        sql += " AND vendor_category = ?"
        args.append(category)
    return q(sql + " ORDER BY name_en", args)


def send_rfq(rfq_id, user_id=None, email=False):
    """Mark the RFQ as sent (and email it to every invited vendor with an address). Returns (sent, skipped names)."""
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    if rfq["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    vendors = q("SELECT rv.*, p.name_en, p.email FROM rfq_vendors rv JOIN partners p ON p.id = rv.partner_id "
                "WHERE rv.rfq_id = ?", (rfq_id,))
    if not vendors:
        raise AccountingError(t("Invite at least one vendor."))
    if not rfq["closing_at"]:
        raise AccountingError(t("Set the closing date and time."))
    if closed_for_quotes(rfq):
        raise AccountingError(t("The closing time is already past."))
    sent, skipped = 0, []
    if email:
        from . import mailer
        from .exports import to_pdf
        if not mailer.configured():
            raise AccountingError(t("Set up email in Company settings first, or mark the RFQ as sent."))
        for v in vendors:
            if not v["email"]:
                skipped.append(v["name_en"])
                continue
            pdf = to_pdf(rfq_report(rfq_id, v["id"]))
            mailer.send(v["email"], f"{t('Request for Quotation')} {rfq['number']}",
                        f"{t('Please find attached our request for quotation')} {rfq['number']}. "
                        f"{t('Quotations close on')} {rfq['closing_at'].replace('T', ' ')}." + _portal_line(v["partner_id"], "supplier.rfq", rfq_id=rfq_id),
                        [(f"{rfq['number'].replace('/', '-')}.pdf", pdf, "application/pdf")], kind="rfq",
                        model="rfq", record_id=rfq_id, partner_id=v["partner_id"], user_id=user_id)
            sent += 1
    x("UPDATE rfqs SET state = 'sent' WHERE id = ?", (rfq_id,))
    audit(user_id, "send", "rfq", rfq_id, rfq["number"])
    return sent, skipped


def open_bids(rfq_id, user_id=None):
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    if not is_sealed(rfq):
        raise AccountingError(t("The bids are not sealed."))
    if not closed_for_quotes(rfq):
        raise AccountingError(t("Sealed bids can be opened only after the closing time."))
    x("UPDATE rfqs SET opened_at = ?, opened_by = ? WHERE id = ?", (now(), user_id, rfq_id))
    audit(user_id, "open_bids", "rfq", rfq_id, rfq["number"])


def extend_closing(rfq_id, closing_at, user_id=None):
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    if rfq["state"] not in ("draft", "sent") or rfq["opened_at"]:
        raise AccountingError(t("This action is not allowed in the current state."))
    if not closing_at or (rfq["closing_at"] and closing_at <= rfq["closing_at"]):
        raise AccountingError(t("The new closing time must be later than the current one."))
    x("UPDATE rfqs SET closing_at = ?, deadline = ? WHERE id = ?", (closing_at, closing_at[:10], rfq_id))
    audit(user_id, "extend_closing", "rfq", rfq_id, f"{rfq['closing_at']} -> {closing_at}")


def vendor_total(rfq_id, rfq_vendor_id):
    """Quoted total of one vendor incl. VAT (for the award approval amount)."""
    total = 0
    for ln in q("SELECT * FROM rfq_lines WHERE rfq_id = ?", (rfq_id,)):
        qt = q("SELECT rq.price, tx.rate FROM rfq_quotes rq LEFT JOIN taxes tx ON tx.id = rq.tax_id "
               "WHERE rq.rfq_vendor_id = ? AND rq.rfq_line_id = ?", (rfq_vendor_id, ln["id"]), one=True)
        if qt:
            sub = int(round(qt["price"] * ln["quantity"]))
            total += sub + int(round(sub * (qt["rate"] or 0) / 100))
    return total


def recommend_award(rfq_id, rfq_vendor_id, warehouse_id, reason, user_id=None):
    """Send the award for approval (rule type 'RFQ Award'). Once approved the PO is created and confirmed.
    A vendor other than the lowest complete quotation needs a reason."""
    from . import workflow
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    if rfq["state"] not in ("draft", "sent"):
        raise AccountingError(t("This action is not allowed in the current state."))
    if is_sealed(rfq):
        raise AccountingError(t("Open the sealed bids first."))
    rv = q("SELECT * FROM rfq_vendors WHERE id = ? AND rfq_id = ?", (rfq_vendor_id, rfq_id), one=True)
    if not rv or rv["state"] != "quoted":
        raise AccountingError(t("The awarded vendor must have a quotation."))
    c = rfq_comparison(rfq_id)
    if not c["complete"].get(rfq_vendor_id):
        raise AccountingError(t("The awarded vendor has not quoted every line."))
    check_vendor(rv["partner_id"], for_po=True)
    if c["best_vendor"] and c["best_vendor"] != rfq_vendor_id and not (reason or "").strip():
        raise AccountingError(t("This is not the lowest quotation. Give the reason for the choice."))
    x("UPDATE rfqs SET state = 'award_pending', award_rv_id = ?, award_reason = ?, award_warehouse_id = ?, award_by = ? "
      "WHERE id = ?", (rfq_vendor_id, (reason or "").strip() or None, warehouse_id, user_id, rfq_id))
    cur = q("SELECT currency FROM branches WHERE id = ?", (rfq["branch_id"],), one=True)["currency"]
    vendor = q("SELECT name_en FROM partners WHERE id = ?", (rv["partner_id"],), one=True)["name_en"]
    rid = workflow.submit("rfq_award", rfq_id, vendor_total(rfq_id, rfq_vendor_id), f"{rfq['number']} → {vendor}",
                          user_id, rfq["branch_id"], cur, project_id=rfq["project_id"])
    audit(user_id, "recommend_award", "rfq", rfq_id, vendor)
    if rid is None:
        return _award_approved(rfq_id, user_id)
    return None


def _award_approved(rfq_id, user_id):
    """Award approved: draft PO at the quoted prices, confirmed at once (the award approval is the PO approval)."""
    from datetime import date
    from .stock import confirm_order
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    po_id = award_rfq(rfq_id, rfq["award_rv_id"], rfq["award_warehouse_id"], date.today().isoformat(),
                      rfq["award_by"] or user_id)
    if rfq["award_reason"]:
        x("UPDATE orders SET notes = notes || ? WHERE id = ?", (f" · {t('Award reason')}: {rfq['award_reason']}", po_id))
    confirm_order(po_id, user_id, approver=True)
    return po_id


def _award_rejected(rfq_id, user_id, reason):
    x("UPDATE rfqs SET state = 'sent', award_rv_id = NULL, award_warehouse_id = NULL WHERE id = ?", (rfq_id,))


def _register_award_handlers():
    from . import workflow
    workflow.handler("rfq_award", "approved")(_award_approved)
    workflow.handler("rfq_award", "rejected")(_award_rejected)


_register_award_handlers()


# ---------------------------------------------------------------- printable documents

def rfq_report(rfq_id, rfq_vendor_id=None):
    """The RFQ sent to a vendor (PDF)."""
    from .exports import Report
    rfq = q("SELECT * FROM rfqs WHERE id = ?", (rfq_id,), one=True)
    v = q("SELECT p.* FROM rfq_vendors rv JOIN partners p ON p.id = rv.partner_id WHERE rv.id = ?", (rfq_vendor_id,),
          one=True) if rfq_vendor_id else None
    rep = Report("Request for Quotation", subtitle=v["name_en"] if v else None, signatures=False)
    rep.details = [("Number", rfq["number"]), ("Date", rfq["date"]),
                   ("Closing", (rfq["closing_at"] or "—").replace("T", " ")),
                   ("Quotation validity", f"{rfq['validity_days']} {t('days')}" if rfq["validity_days"] else "—"),
                   ("Payment terms", rfq["payment_terms"] or "—"),
                   ("Incoterms", f"{rfq['incoterms'] or '—'} {rfq['delivery_place'] or ''}".strip()),
                   ("Bids", t("Sealed") if rfq["sealed"] else t("Open"))]
    rep.column(t("#"), width=0.35).column(t("Item code"), width=0.9).column(t("Description"), width=2.6) \
        .column(t("Manufacturer / part no."), width=1.5).column(t("Unit"), width=0.6).column(t("Qty"), "qty", 0.6) \
        .column(t("Required by"), width=0.9).column(t("Alt."), width=0.45)
    for i, ln in enumerate(q("SELECT * FROM rfq_lines WHERE rfq_id = ? ORDER BY id", (rfq_id,)), 1):
        desc = ln["description"] or ""
        if ln["specification"]:
            desc += f" — {ln['specification']}"
        rep.row([i, ln["item_code"] or "", desc, " / ".join(filter(None, [ln["manufacturer"], ln["part_number"]])),
                 ln["uom"] or "", ln["quantity"], ln["required_date"] or "", t("Yes") if ln["allow_alternative"] else ""])
    rep.notes.append(t("Quote your unit price excluding VAT for each line, the VAT rate, delivery lead time and the "
                       "validity of your offer. Alternatives are accepted only where the line says so."))
    if rfq["terms"]:
        rep.notes.append(f"{t('Terms and Conditions')}: {rfq['terms']}")
    return rep


def po_report(order_id):
    """Purchase order PDF for the vendor, with terms and signature boxes."""
    from . import currency_decimals
    from .exports import Report
    o = q("SELECT o.*, p.name_en, p.vat_no, p.address FROM orders o JOIN partners p ON p.id = o.partner_id "
          "WHERE o.id = ?", (order_id,), one=True)
    cur = q("SELECT currency FROM branches WHERE id = ?", (o["branch_id"],), one=True)["currency"]
    rep = Report("Purchase Order", subtitle=o["name_en"], decimals=currency_decimals(cur), signatures=True)
    rep.details = [("Number", o["number"]), ("Date", o["date"]), ("Expected Arrival", o["expected_date"] or "—"),
                   ("Vendor VAT no.", o["vat_no"] or "—"), ("Reference", o["ref"] or "—"),
                   ("Type", t(po_type(o)))]
    rep.column(t("#"), width=0.35).column(t("Description"), width=3).column(t("Qty"), "qty", 0.6) \
        .column(t("Unit Price"), "num").column(t("VAT"), "num").column(t("Amount"), "num")
    for i, ln in enumerate(q("SELECT ol.*, pr.code FROM order_lines ol LEFT JOIN products pr ON pr.id = ol.product_id "
                             "WHERE ol.order_id = ? ORDER BY ol.sequence, ol.id", (order_id,)), 1):
        rep.row([i, f"{ln['code'] + ' ' if ln['code'] else ''}{ln['description'] or ''}", ln["quantity"],
                 ln["price_unit"], ln["tax_amount"], ln["subtotal"]])
    rep.totals = [(t("Subtotal"), o["amount_untaxed"], False), (t("VAT"), o["amount_tax"], False),
                  (f"{t('Total')} ({cur})", o["amount_total"], True)]
    rep.notes.append(t("Quote this PO number on the delivery note and the invoice."))
    if o["terms"]:
        rep.notes.append(f"{t('Terms and Conditions')}: {o['terms']}")
    return rep


# ---------------------------------------------------------------- purchase orders: type, sending, acknowledgement

def _portal_line(partner_id, endpoint, **kw):
    """'Reply on the supplier portal: <link>' when the vendor has an active portal account."""
    from . import has
    if not has("portal") or not q("SELECT 1 FROM portal_users WHERE partner_id = ? AND active = 1 AND password_hash "
                                   "IS NOT NULL", (partner_id,), one=True):
        return ""
    from .portal import public_url
    return f"\n\n{t('You can reply on the supplier portal')}: {public_url(endpoint, **kw)}"


def po_type(order):
    """Requisition PO (raised from a material request, requisition or RFQ) or a direct PO."""
    return "Requisition PO" if order["mr_id"] or order["pr_id"] or order["rfq_id"] else "Direct PO"


def send_po(order_id, user_id=None, email=True, cc=None):
    o = q("SELECT o.*, p.email, p.name_en FROM orders o JOIN partners p ON p.id = o.partner_id WHERE o.id = ?",
          (order_id,), one=True)
    if o["kind"] != "purchase" or o["state"] != "confirmed":
        raise AccountingError(t("Confirm the order first."))
    if email:
        from . import mailer
        from .exports import to_pdf
        if not mailer.configured():
            raise AccountingError(t("Set up email in Company settings first, or mark the order as sent."))
        if not o["email"]:
            raise AccountingError(t("The vendor has no email address."))
        mailer.send(o["email"], f"{t('Purchase Order')} {o['number']}",
                    f"{t('Please find attached our purchase order')} {o['number']}. "
                    f"{t('Please confirm the order and the delivery date.')}" + _portal_line(o["partner_id"], "supplier.order", order_id=order_id),
                    [(f"{o['number'].replace('/', '-')}.pdf", to_pdf(po_report(order_id)), "application/pdf")],
                    cc=cc or "", kind="po", model="order", record_id=order_id, partner_id=o["partner_id"],
                    user_id=user_id)
    x("UPDATE orders SET sent_at = ?, ack_state = COALESCE(ack_state, 'awaiting') WHERE id = ?", (now(), order_id))
    audit(user_id, "send", "order", order_id, o["number"])


def acknowledge_po(order_id, state, ref=None, promised=None, note=None, user_id=None):
    o = q("SELECT * FROM orders WHERE id = ?", (order_id,), one=True)
    if o["kind"] != "purchase" or o["state"] != "confirmed":
        raise AccountingError(t("Confirm the order first."))
    if state not in ("accepted", "rejected"):
        raise AccountingError(t("Choose accepted or rejected."))
    if state == "rejected" and not (note or "").strip():
        raise AccountingError(t("Give the vendor's reason."))
    x("UPDATE orders SET ack_state = ?, ack_at = ?, ack_ref = ?, ack_date = ?, ack_note = ? WHERE id = ?",
      (state, now(), ref, promised, note, order_id))
    if state == "accepted" and promised:
        x("UPDATE orders SET expected_date = ? WHERE id = ?", (promised, order_id))
    audit(user_id, f"ack_{state}", "order", order_id, o["number"])


# ---------------------------------------------------------------- GRN for site deliveries and services

def needs_grn(product_row):
    """Lines that are received with a GRN instead of a warehouse receipt."""
    from .stock import inventory_enabled
    return not product_row or product_row["type"] != "stock" or not inventory_enabled()


def requester_of(order):
    """The person who asked for the goods (material request), if any."""
    mr_id = order["mr_id"]
    if not mr_id and order["pr_id"]:
        pr = q("SELECT mr_id FROM purchase_requisitions WHERE id = ?", (order["pr_id"],), one=True)
        mr_id = pr and pr["mr_id"]
    if not mr_id and order["rfq_id"]:
        r = q("SELECT mr_id, pr_id FROM rfqs WHERE id = ?", (order["rfq_id"],), one=True)
        mr_id = r and r["mr_id"]
    mr = q("SELECT requested_by FROM material_requests WHERE id = ?", (mr_id,), one=True) if mr_id else None
    return mr["requested_by"] if mr else None


def can_receive(order, user):
    """Site / service lines are received by the requester, the project manager or team, or an administrator."""
    if user["role"] == "admin":
        return True
    if user["role"] not in ("admin", "accountant"):
        return False
    req = requester_of(order)
    allowed = {req} if req else set()
    if order["project_id"]:
        p = q("SELECT manager_id FROM projects WHERE id = ?", (order["project_id"],), one=True)
        if p and p["manager_id"]:
            allowed.add(p["manager_id"])
        allowed |= {r[0] for r in q("SELECT user_id FROM project_members WHERE project_id = ?", (order["project_id"],))}
    return not allowed or user["id"] in allowed


def grn_lines_open(order_id):
    from .stock import order_progress, product
    prog = order_progress(order_id)
    out = []
    for ln in q("SELECT * FROM order_lines WHERE order_id = ? ORDER BY sequence, id", (order_id,)):
        prod = product(ln["product_id"]) if ln["product_id"] else None
        if needs_grn(prod):
            out.append({**dict(ln), "received": prog[ln["id"]]["done"],
                        "open": max(ln["quantity"] - prog[ln["id"]]["done"], 0), "uom": prod["uom"] if prod else ""})
    return out


def create_grn(order_id, d, quantities, delivery_note=None, notes=None, user_id=None):
    """quantities: {order_line_id: qty}. No accounting entry: the cost is booked by the vendor bill."""
    from .ledger import check_lock
    o = q("SELECT * FROM orders WHERE id = ?", (order_id,), one=True)
    if o["kind"] != "purchase" or o["state"] != "confirmed":
        raise AccountingError(t("Confirm the order first."))
    check_lock(d)
    open_lines = {ln["id"]: ln for ln in grn_lines_open(order_id)}
    rows = [(lid, float(qty)) for lid, qty in quantities.items() if qty and float(qty) > EPS]
    if not rows:
        raise AccountingError(t("Enter the quantity received."))
    for lid, qty in rows:
        if lid not in open_lines:
            raise AccountingError(t("This line is received through the warehouse."))
        if qty > open_lines[lid]["open"] + EPS:
            raise AccountingError(f"{open_lines[lid]['description'] or ''}: {t('more than the quantity still to receive.')}")
    gid = x("INSERT INTO grns (number, branch_id, order_id, date, received_by, delivery_note, notes, created_at) "
            "VALUES (?,?,?,?,?,?,?,?)", (next_number("grns", "number", "GRN", o["branch_id"], d), o["branch_id"],
                                         order_id, d, user_id, delivery_note, notes, now()))
    for lid, qty in rows:
        x("INSERT INTO grn_lines (grn_id, order_line_id, quantity) VALUES (?,?,?)", (gid, lid, qty))
    audit(user_id, "create", "grn", gid, o["number"])
    return gid


def cancel_grn(grn_id, user_id=None):
    g = q("SELECT * FROM grns WHERE id = ?", (grn_id,), one=True)
    if g["state"] != "done":
        raise AccountingError(t("This action is not allowed in the current state."))
    billed = q("SELECT 1 FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id JOIN grn_lines gl "
               "ON gl.order_line_id = il.order_line_id WHERE gl.grn_id = ? AND i.state = 'posted' LIMIT 1",
               (grn_id,), one=True)
    if billed:
        from .stock import order_progress
        prog = order_progress(g["order_id"])
        for gl in q("SELECT * FROM grn_lines WHERE grn_id = ?", (grn_id,)):
            if prog[gl["order_line_id"]]["done"] - gl["quantity"] < prog[gl["order_line_id"]]["invoiced"] - EPS:
                raise AccountingError(t("The goods on this GRN are already billed."))
    x("UPDATE grns SET state = 'cancelled' WHERE id = ?", (grn_id,))
    audit(user_id, "cancel", "grn", grn_id, g["number"])


def grn_report(grn_id):
    from .exports import Report
    g = q("SELECT g.*, o.number AS po, p.name_en, u.full_name FROM grns g JOIN orders o ON o.id = g.order_id "
          "JOIN partners p ON p.id = o.partner_id LEFT JOIN users u ON u.id = g.received_by WHERE g.id = ?",
          (grn_id,), one=True)
    rep = Report("Goods Received Note", subtitle=g["name_en"], signatures=True)
    rep.details = [("Number", g["number"]), ("Date", g["date"]), ("Purchase Order", g["po"]),
                   ("Delivery note", g["delivery_note"] or "—"), ("Received by", g["full_name"] or "—"),
                   ("Status", t("Cancelled") if g["state"] == "cancelled" else t("Done"))]
    rep.column(t("#"), width=0.35).column(t("Description"), width=3.4).column(t("Ordered"), "qty", 0.8) \
        .column(t("Received"), "qty", 0.8)
    for i, ln in enumerate(q("SELECT gl.*, ol.description, ol.quantity AS ordered FROM grn_lines gl JOIN order_lines ol "
                             "ON ol.id = gl.order_line_id WHERE gl.grn_id = ? ORDER BY gl.id", (grn_id,)), 1):
        rep.row([i, ln["description"] or "", ln["ordered"], ln["quantity"]])
    if g["notes"]:
        rep.notes.append(g["notes"])
    return rep


# ---------------------------------------------------------------- three-way match (PO - receipt - bill)

def match_rows(invoice_id):
    """Per bill line linked to a PO line: ordered, received, billed to date (incl. this bill), prices, issues."""
    from .stock import order_progress
    c = q("SELECT match_qty_tolerance, match_price_tolerance FROM company", one=True)
    qtol, ptol = (c["match_qty_tolerance"] or 0) / 100, (c["match_price_tolerance"] or 0) / 100
    inv = q("SELECT * FROM invoices WHERE id = ?", (invoice_id,), one=True)
    rows = []
    progs = {}
    for il in q("SELECT il.*, ol.order_id, ol.quantity AS ordered, ol.price_unit AS po_price, ol.description AS po_desc "
                "FROM invoice_lines il JOIN order_lines ol ON ol.id = il.order_line_id WHERE il.invoice_id = ? "
                "ORDER BY il.sequence, il.id", (invoice_id,)):
        prog = progs.setdefault(il["order_id"], order_progress(il["order_id"]))[il["order_line_id"]]
        billed = q("SELECT COALESCE(SUM(il2.quantity * CASE WHEN i.kind = 'in_refund' THEN -1 ELSE 1 END), 0) "
                   "FROM invoice_lines il2 JOIN invoices i ON i.id = il2.invoice_id WHERE il2.order_line_id = ? "
                   "AND (i.state = 'posted' OR i.id = ?)", (il["order_line_id"], invoice_id), one=True)[0]
        issues = []
        if inv["kind"] == "in_invoice":
            if billed > prog["done"] * (1 + qtol) + EPS:
                issues.append(f"{t('billed')} {billed:g} > {t('received')} {prog['done']:g}")
            if il["po_price"] and il["price_unit"] > il["po_price"] * (1 + ptol) + 0.5:
                issues.append(t("price above the PO"))
        rows.append({"description": il["description"] or il["po_desc"], "ordered": il["ordered"],
                     "received": prog["done"], "billed": billed, "po_price": il["po_price"], "price": il["price_unit"],
                     "issues": issues})
    return rows


def match_issues(invoice_id):
    return [f"{r['description']}: {', '.join(r['issues'])}" for r in match_rows(invoice_id) if r["issues"]]


def check_match(invoice_id):
    """Run on posting a vendor bill. Block: raise. Warn: show the differences."""
    from . import has
    from .ledger import company
    inv = q("SELECT kind FROM invoices WHERE id = ?", (invoice_id,), one=True)
    policy = company()["match_policy"] or "warn"
    if inv["kind"] != "in_invoice" or policy == "none" or not has("procurement"):
        return []
    issues = match_issues(invoice_id)
    if issues and policy == "block":
        raise AccountingError(t("Three-way match failed") + ": " + "; ".join(issues))
    if issues:
        from .projects import warn
        warn([t("Three-way match") + ": " + "; ".join(issues)])
    return issues
