"""Inventory (average cost, perpetual valuation) and sales/purchase order logic.

Accounting:
  receipt          Dr Inventory           Cr Goods Received Not Invoiced
  vendor bill      Dr GRNI (stock lines)  Cr Payable          -> net effect Dr Inventory / Cr Payable
  price difference Dr/Cr Inventory (still in stock) or COGS (sold)   Cr/Dr GRNI   (bill price vs receipt cost)
  delivery         Dr Cost of goods sold  Cr Inventory        (at average cost)
  customer return  Dr Inventory           Cr Cost of goods sold   (at the cost the goods left with)
  vendor return    Dr GRNI (refund price) Cr Inventory (average cost), the gap to COGS
  landed cost      Dr Inventory (still in stock) or COGS (sold)   Cr the account the charges were booked to
  adjustment       Dr/Cr Inventory        Cr/Dr Inventory Adjustments
  write-off        Dr Inventory Write-offs (damaged / expired)   Cr Inventory   (at average cost)
"""
from collections import defaultdict
from datetime import date

from .db import placeholders, q, x
from .i18n import nm, t
from .ledger import AccountingError, audit, check_lock, company, compute_invoice_line, create_move, now

EPS = 1e-9
PICKING_PREFIX = {"in": "REC", "out": "DEL", "adjust": "ADJ", "pos": "PSTK", "return_in": "RET", "return_out": "RTV",
                  "transfer_out": "TRO", "transfer_in": "TRI", "scrap": "SCR"}
SCRAP_REASONS = {"damaged": "Damaged", "expired": "Expired", "lost": "Lost / missing", "sample": "Free sample",
                 "other": "Other"}
INVOICE_PICKING = {"out_invoice": "out", "in_invoice": "in", "out_refund": "return_in", "in_refund": "return_out"}
ORDER_PREFIX = {"sale": "SO", "purchase": "PO"}


def next_number(table, col, prefix, branch_id, d):
    code = q("SELECT code FROM branches WHERE id = ?", (branch_id,), one=True)["code"]
    pfx = f"{prefix}/{code}/{d[:4]}/"
    last = 0
    for r in q(f"SELECT {col} FROM {table} WHERE {col} LIKE ?", (pfx + "%",)):
        try:
            last = max(last, int(r[0].rsplit("/", 1)[1]))
        except (ValueError, IndexError):
            pass
    return f"{pfx}{last + 1:04d}"


def stock_journal(branch_id):
    j = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'general' AND active = 1 "
          "ORDER BY code = 'STK' DESC, id LIMIT 1", (branch_id,), one=True)
    if not j:
        raise AccountingError(t("The branch needs a miscellaneous (general) journal for stock entries."))
    return j["id"]


def stock_accounts():
    c = company()
    if not (c["inventory_account_id"] and c["grni_account_id"] and c["stock_adjust_account_id"]):
        raise AccountingError(t("Set the inventory accounts in Company settings."))
    return c["inventory_account_id"], c["grni_account_id"], c["stock_adjust_account_id"]


def category(prod):
    if not prod["category_id"]:
        return None
    return q("SELECT * FROM product_categories WHERE id = ?", (prod["category_id"],), one=True)


def cogs_account(prod):
    """The product's own COGS account, else its category's, else the company default."""
    cat = category(prod)
    acc = prod["expense_account_id"] or (cat and cat["cogs_account_id"]) or company()["cogs_account_id"]
    if not acc:
        raise AccountingError(t("Set the cost of goods sold account in Company settings."))
    return acc


def inventory_account(prod):
    """Inventory is carried on the product category's account, else the company inventory account."""
    cat = category(prod)
    acc = (cat and cat["inventory_account_id"]) or company()["inventory_account_id"]
    if not acc:
        raise AccountingError(t("Set the inventory accounts in Company settings."))
    return acc


def inventory_account_ids():
    """Every account stock can be carried on (for reconciling stock valuation with the ledger)."""
    ids = {r[0] for r in q("SELECT inventory_account_id FROM product_categories WHERE inventory_account_id IS NOT NULL")}
    if company()["inventory_account_id"]:
        ids.add(company()["inventory_account_id"])
    return sorted(ids)


def inventory_account_map():
    """{product_id: inventory account} for stockable products."""
    default = company()["inventory_account_id"]
    return {r["id"]: r["acc"] or default for r in q(
        "SELECT p.id, c.inventory_account_id AS acc FROM products p "
        "LEFT JOIN product_categories c ON c.id = p.category_id WHERE p.type = 'stock'")}


def reclass_inventory(before, user_id=None, d=None):
    """After a product changed category or a category / the company changed its inventory account, move the value
    of the stock on hand from the old account to the new one, per branch. before: inventory_account_map() taken
    before the change."""
    after = inventory_account_map()
    entry = defaultdict(lambda: defaultdict(int))  # branch -> account -> debit-positive amount
    for pid, old in before.items():
        new = after.get(pid)
        if not old or not new or old == new:
            continue
        for r in q("SELECT w.branch_id, SUM(sq.value) AS value FROM stock_quants sq JOIN warehouses w "
                   "ON w.id = sq.warehouse_id WHERE sq.product_id = ? GROUP BY w.branch_id", (pid,)):
            if r["value"]:
                entry[r["branch_id"]][new] += r["value"]
                entry[r["branch_id"]][old] -= r["value"]
    d = d or date.today().isoformat()
    for branch_id, accs in entry.items():
        lines = [{"account_id": a, "label": t("Inventory account change"), "debit": max(v, 0), "credit": max(-v, 0)}
                 for a, v in accs.items() if v]
        if lines:
            create_move(branch_id, stock_journal(branch_id), d, lines, user_id=user_id,
                        ref=t("Inventory account change"), move_type="stock", post=True)


def product(pid):
    return q("SELECT * FROM products WHERE id = ?", (pid,), one=True)


def quant(pid, wid):
    r = q("SELECT qty, value FROM stock_quants WHERE product_id = ? AND warehouse_id = ?", (pid, wid), one=True)
    return (r["qty"], r["value"]) if r else (0.0, 0)


def _bump(pid, wid, dq, dv):
    x("INSERT INTO stock_quants (product_id, warehouse_id, qty, value) VALUES (?,?,?,?) "
      "ON CONFLICT (product_id, warehouse_id) DO UPDATE SET qty = qty + excluded.qty, value = value + excluded.value",
      (pid, wid, dq, dv))


def avg_cost(pid, wid):
    qty, value = quant(pid, wid)
    if qty > EPS:
        return int(round(value / qty))
    return product(pid)["purchase_price"]


def _out_value(pid, wid, qty_out):
    qty, value = quant(pid, wid)
    if qty_out > qty + EPS:
        p = product(pid)
        raise AccountingError(f"{t('Not enough stock for')} {nm(p)}: {t('available')} {qty:g}, {t('needed')} {qty_out:g}")
    if abs(qty_out - qty) < EPS:
        return value
    return int(round(value * qty_out / qty))


def warehouse(wid):
    return q("SELECT * FROM warehouses WHERE id = ?", (wid,), one=True)


# ---------------------------------------------------------------- pickings

def create_picking(kind, branch_id, warehouse_id, d, lines, partner_id=None, order_id=None, invoice_id=None,
                   notes=None, user_id=None):
    """lines: dicts with product_id, quantity and optional order_line_id, invoice_line_id, unit_cost."""
    wh = warehouse(warehouse_id)
    if not wh or wh["branch_id"] != int(branch_id):
        raise AccountingError(t("The warehouse does not belong to the selected branch."))
    lines = [ln for ln in lines if ln["quantity"] > EPS or kind == "adjust"]
    if not lines:
        raise AccountingError(t("Nothing to move."))
    pid = x("INSERT INTO pickings (number, kind, branch_id, warehouse_id, partner_id, order_id, invoice_id, date, "
            "state, notes, created_by, created_at) VALUES (?,?,?,?,?,?,?,?,'ready',?,?,?)",
            (next_number("pickings", "number", PICKING_PREFIX[kind], branch_id, d), kind, branch_id, warehouse_id,
             partner_id, order_id, invoice_id, d, notes, user_id, now()))
    for ln in lines:
        x("INSERT INTO picking_lines (picking_id, product_id, order_line_id, invoice_line_id, quantity, unit_cost) "
          "VALUES (?,?,?,?,?,?)", (pid, ln["product_id"], ln.get("order_line_id"), ln.get("invoice_line_id"),
                                   ln["quantity"], ln.get("unit_cost") or 0))
    audit(user_id, "create", "picking", pid)
    return pid


def line_cost_centre(picking_line, order_cc=None):
    """Cost centre for the COGS of a picking line: its invoice line's, else the order's."""
    if picking_line["invoice_line_id"]:
        il = q("SELECT cost_centre_id FROM invoice_lines WHERE id = ?", (picking_line["invoice_line_id"],), one=True)
        if il and il["cost_centre_id"]:
            return il["cost_centre_id"]
    return order_cc["cost_centre_id"] if order_cc else None


def scrap_account():
    c = company()
    acc = c["scrap_account_id"] if "scrap_account_id" in c.keys() else None
    acc = acc or c["stock_adjust_account_id"]
    if not acc:
        raise AccountingError(t("Set the inventory write-off account in Company settings."))
    return acc


def validate_picking(picking_id, done=None, d=None, user_id=None, counter_account=None, lots=None):
    """done: {picking_line_id: qty}; quantities below plan create a backorder for the rest.
    counter_account replaces GRNI on receipts (opening stock is credited to the opening balance clearing account).
    lots: batches for tracked products, see lots.record."""
    p = q("SELECT * FROM pickings WHERE id = ?", (picking_id,), one=True)
    if p["state"] != "ready":
        raise AccountingError(t("This action is not allowed in the current state."))
    d = d or p["date"]
    check_lock(d)
    _, grni_acc, adj_acc = stock_accounts()
    lines = q("SELECT * FROM picking_lines WHERE picking_id = ? ORDER BY id", (picking_id,))
    entry = defaultdict(lambda: [0, 0])  # (account, cost centre) -> [debit, credit]
    order_cc = q("SELECT cost_centre_id FROM orders WHERE id = ?", (p["order_id"],), one=True) if p["order_id"] else None
    remaining = []
    moved = False
    for ln in lines:
        prod = product(ln["product_id"])
        if prod["type"] != "stock":
            continue
        inv_acc = inventory_account(prod)
        cc = line_cost_centre(ln, order_cc) or p["cost_centre_id"]
        if p["kind"] == "adjust":
            have, _ = quant(prod["id"], p["warehouse_id"])
            dq = ln["quantity"] - have
            if abs(dq) < EPS:
                continue
            if dq > 0:
                dv = int(round(dq * avg_cost(prod["id"], p["warehouse_id"])))
                entry[inv_acc, None][0] += dv
                entry[adj_acc, None][1] += dv
            else:
                dv = -_out_value(prod["id"], p["warehouse_id"], -dq)
                entry[adj_acc, None][0] -= dv
                entry[inv_acc, None][1] -= dv
        else:
            qty = ln["quantity"] if done is None else float(done.get(ln["id"], ln["quantity"]) or 0)
            if qty < -EPS or qty > ln["quantity"] + EPS:
                raise AccountingError(t("Done quantity must be between zero and the planned quantity."))
            if ln["quantity"] - qty > EPS:
                remaining.append({**dict(ln), "quantity": ln["quantity"] - qty})
            if qty < EPS:
                x("UPDATE picking_lines SET quantity = 0 WHERE id = ?", (ln["id"],))
                continue
            if p["kind"] in ("in", "return_in"):
                cost = ln["unit_cost"] or (avg_cost(prod["id"], p["warehouse_id"]) if p["kind"] == "return_in"
                                           else prod["purchase_price"])
                dq, dv = qty, int(round(qty * cost))
                entry[inv_acc, None][0] += dv
                if p["kind"] == "in":
                    entry[counter_account or grni_acc, None][1] += dv
                else:
                    entry[cogs_account(prod), cc][1] += dv
            else:
                dq, dv = -qty, -_out_value(prod["id"], p["warehouse_id"], qty)
                entry[inv_acc, None][1] -= dv
                if p["kind"] == "out":
                    entry[cogs_account(prod), cc][0] -= dv
                elif p["kind"] == "scrap":
                    entry[scrap_account(), cc][0] -= dv
                else:
                    # the vendor refund credited GRNI at the refund price; clear exactly that
                    refund = int(round(qty * ln["unit_cost"])) if ln["unit_cost"] else -dv
                    entry[grni_acc, None][0] += refund
                    entry[cogs_account(prod), cc][1] += refund + dv
            x("UPDATE picking_lines SET quantity = ? WHERE id = ?", (qty, ln["id"]))
        _bump(prod["id"], p["warehouse_id"], dq, dv)
        x("UPDATE picking_lines SET qty_signed = ?, value_signed = ? WHERE id = ?", (dq, dv, ln["id"]))
        moved = True
    if not moved:
        raise AccountingError(t("Nothing to move."))
    move_id = None
    move_lines = [{"account_id": a, "partner_id": p["partner_id"], "label": p["number"], "cost_centre_id": cc,
                   "debit": max(dr - cr, 0), "credit": max(cr - dr, 0)} for (a, cc), (dr, cr) in entry.items() if dr != cr]
    if move_lines:
        move_id = create_move(p["branch_id"], stock_journal(p["branch_id"]), d, move_lines, user_id=user_id,
                              ref=p["number"], move_type="stock", partner_id=p["partner_id"], post=True)
    x("UPDATE pickings SET state = 'done', date = ?, move_id = ? WHERE id = ?", (d, move_id, picking_id))
    from . import lots as lot_mod
    lot_mod.record(picking_id, lots)
    if p["kind"] == "in":
        settle_price_differences({ln["order_line_id"] for ln in lines if ln["order_line_id"]}, d, p["number"], user_id)
    if remaining:
        create_picking(p["kind"], p["branch_id"], p["warehouse_id"], d, remaining, p["partner_id"], p["order_id"],
                       p["invoice_id"], t("Backorder of") + " " + p["number"], user_id)
    audit(user_id, "validate", "picking", picking_id, p["number"])


def adjust_stock(branch_id, warehouse_id, d, counts, notes=None, user_id=None):
    """counts: [(product_id, counted_qty)]."""
    pid = create_picking("adjust", branch_id, warehouse_id, d,
                         [{"product_id": p, "quantity": c} for p, c in counts], notes=notes, user_id=user_id)
    validate_picking(pid, d=d, user_id=user_id)
    return pid


def scrap_stock(branch_id, warehouse_id, d, lines, reason, notes=None, user_id=None, cost_centre_id=None):
    """Write off damaged, expired or lost goods. lines: [(product_id, qty, lot_id or None)]."""
    if reason not in SCRAP_REASONS:
        raise AccountingError(t("Choose a reason."))
    for pid_, qty, _ in lines:
        if product(pid_)["type"] != "stock":
            raise AccountingError(t("Only stock products can be written off."))
    pk = create_picking("scrap", branch_id, warehouse_id, d,
                        [{"product_id": p_, "quantity": qty} for p_, qty, _ in lines], notes=notes, user_id=user_id)
    x("UPDATE pickings SET reason = ?, cost_centre_id = ? WHERE id = ?", (reason, cost_centre_id, pk))
    ids = [r[0] for r in q("SELECT id FROM picking_lines WHERE picking_id = ? ORDER BY id", (pk,))]
    chosen = {lid: lot for lid, (_, _, lot) in zip(ids, [ln for ln in lines if ln[1] > EPS]) if lot}
    validate_picking(pk, d=d, user_id=user_id, lots=chosen)
    return pk


# ---------------------------------------------------------------- orders

def order_progress(order_id):
    """{order_line_id: {'done': delivered/received, 'pending': in open pickings, 'invoiced': qty}}."""
    out = defaultdict(lambda: {"done": 0.0, "pending": 0.0, "invoiced": 0.0})
    for r in q("SELECT pl.order_line_id, p.state, SUM(pl.quantity) AS qty FROM picking_lines pl "
               "JOIN pickings p ON p.id = pl.picking_id JOIN order_lines ol ON ol.id = pl.order_line_id "
               "WHERE ol.order_id = ? AND p.state IN ('done', 'ready') GROUP BY pl.order_line_id, p.state", (order_id,)):
        out[r["order_line_id"]]["done" if r["state"] == "done" else "pending"] += r["qty"]
    for r in q("SELECT il.order_line_id, SUM(il.quantity * CASE WHEN i.kind LIKE '%refund' THEN -1 ELSE 1 END) AS qty "
               "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.state != 'cancelled' GROUP BY il.order_line_id", (order_id,)):
        out[r["order_line_id"]]["invoiced"] += r["qty"]
    for r in q("SELECT gl.order_line_id, SUM(gl.quantity) AS qty FROM grn_lines gl JOIN grns g ON g.id = gl.grn_id "
               "WHERE g.order_id = ? AND g.state = 'done' GROUP BY gl.order_line_id", (order_id,)):
        out[r["order_line_id"]]["done"] += r["qty"]  # site deliveries and services received with a GRN
    return out


def _grn_enabled():
    from . import has
    return has("procurement")


def order_status(order):
    """(delivery_status, invoice_status) for a confirmed order: 'no', 'partial', 'full' or None."""
    if order["state"] != "confirmed":
        return None, None
    lines = q("SELECT ol.*, p.type FROM order_lines ol LEFT JOIN products p ON p.id = ol.product_id "
              "WHERE ol.order_id = ?", (order["id"],))
    prog = order_progress(order["id"])

    def status(items):
        if not items:
            return None
        if all(done >= want - EPS for want, done in items):
            return "full"
        if any(done > EPS for _, done in items):
            return "partial"
        return "no"
    grn = order["kind"] == "purchase" and _grn_enabled()
    deliv = status([(ln["quantity"], prog[ln["id"]]["done"]) for ln in lines
                    if (ln["type"] == "stock" and inventory_enabled()) or grn]) \
        if inventory_enabled() or grn else None
    inv = status([(ln["quantity"], prog[ln["id"]]["invoiced"]) for ln in lines])
    return deliv, inv


def inventory_enabled():
    from flask import has_app_context
    if not has_app_context():
        return True
    from . import has
    return has("inventory")


def confirm_order(order_id, user_id=None, approver=True):
    """Confirm a quotation / draft PO. Purchase orders above the approval limit wait for an approver."""
    o = q("SELECT * FROM orders WHERE id = ?", (order_id,), one=True)
    if o["state"] not in ("draft", "to_approve"):
        raise AccountingError(t("This action is not allowed in the current state."))
    if not q("SELECT 1 FROM order_lines WHERE order_id = ?", (order_id,)):
        raise AccountingError(t("Add at least one line."))
    if o["kind"] == "sale":
        from .credit import check
        cur = q("SELECT currency FROM branches WHERE id = ?", (o["branch_id"],), one=True)["currency"]
        check(o["partner_id"], o["amount_total"], cur, o["date"], "Sales Order")
    if o["kind"] == "purchase":
        from .procurement import check_vendor
        check_vendor(o["partner_id"], for_po=True)
        if o["state"] == "draft" and o["project_id"]:
            from . import projects
            projects.parse(o["project_id"], o["branch_id"])
            projects.warn(projects.check_budget(o["project_id"], projects.order_amounts(order_id),
                                                f"{t('Purchase Order')} {o['number'] or ''}"))
        if o["state"] == "draft" and not approver:
            # the approval workflow decides; no matching rule means the order is confirmed now
            from . import workflow
            cur = q("SELECT currency FROM branches WHERE id = ?", (o["branch_id"],), one=True)["currency"]
            if workflow.submit("po", order_id, o["amount_total"], o["number"] or "", user_id, o["branch_id"], cur,
                               project_id=o["project_id"]):
                x("UPDATE orders SET state = 'to_approve' WHERE id = ?", (order_id,))
                audit(user_id, "to_approve", "order", order_id, o["number"])
                return "to_approve"
        elif o["state"] == "to_approve" and not approver:
            raise AccountingError(t("This order is waiting for approval."))
        x("UPDATE orders SET approved_by = ?, approved_at = ? WHERE id = ?", (user_id, now(), order_id))
    x("UPDATE orders SET state = 'confirmed' WHERE id = ?", (order_id,))
    if o["kind"] == "sale" and o["lead_id"]:
        x("UPDATE leads SET stage = 'won', probability = 100, closed_at = ? WHERE id = ? AND stage != 'won'",
          (now(), o["lead_id"]))
    if inventory_enabled() and o["warehouse_id"] and q(
            "SELECT 1 FROM order_lines ol JOIN products p ON p.id = ol.product_id "
            "WHERE ol.order_id = ? AND p.type = 'stock'", (order_id,)):
        create_order_picking(order_id, user_id)
    audit(user_id, "confirm", "order", order_id, o["number"])
    return "confirmed"


def _po_handlers():
    from . import workflow

    @workflow.handler("po", "approved")
    def _approved(order_id, user_id):
        confirm_order(order_id, user_id, approver=True)

    @workflow.handler("po", "rejected")
    def _rejected(order_id, user_id, reason):
        x("UPDATE orders SET state = 'draft' WHERE id = ?", (order_id,))
        audit(user_id, "reject", "order", order_id, reason)


_po_handlers()


def create_order_picking(order_id, user_id=None):
    o = q("SELECT * FROM orders WHERE id = ?", (order_id,), one=True)
    if not o["warehouse_id"]:
        raise AccountingError(t("Choose a warehouse."))
    prog = order_progress(order_id)
    lines = []
    for ln in q("SELECT ol.* FROM order_lines ol JOIN products p ON p.id = ol.product_id "
                "WHERE ol.order_id = ? AND p.type = 'stock'", (order_id,)):
        rest = ln["quantity"] - prog[ln["id"]]["done"] - prog[ln["id"]]["pending"]
        if rest > EPS:
            lines.append({"product_id": ln["product_id"], "quantity": rest, "order_line_id": ln["id"],
                          "unit_cost": ln["price_unit"] if o["kind"] == "purchase" else 0})
    if not lines:
        raise AccountingError(t("Nothing to move."))
    return create_picking("out" if o["kind"] == "sale" else "in", o["branch_id"], o["warehouse_id"],
                          o["expected_date"] or o["date"], lines, o["partner_id"], order_id=order_id, user_id=user_id)


def line_account(kind, prod, journal):
    """Default account for an invoice line (kind = invoice kind)."""
    if kind.startswith("out_"):
        cat = category(prod) if prod else None
        return (prod and prod["income_account_id"]) or (cat and cat["income_account_id"]) or \
            journal["default_account_id"]
    if prod and prod["type"] == "stock" and inventory_enabled():
        return company()["grni_account_id"]
    return (prod and prod["expense_account_id"]) or journal["default_account_id"]


def create_order_invoice(order_id, d, user_id=None):
    from .money import round_cur
    o = q("SELECT * FROM orders WHERE id = ?", (order_id,), one=True)
    if o["state"] != "confirmed":
        raise AccountingError(t("Confirm the order first."))
    kind = "out_invoice" if o["kind"] == "sale" else "in_invoice"
    j = q("SELECT * FROM journals WHERE branch_id = ? AND type = ? AND active = 1 ORDER BY id LIMIT 1",
          (o["branch_id"], "sale" if o["kind"] == "sale" else "purchase"), one=True)
    if not j:
        raise AccountingError(t("The branch has no sales/purchase journal."))
    dec = q("SELECT c.decimals FROM branches b JOIN currencies c ON c.code = b.currency WHERE b.id = ?",
            (o["branch_id"],), one=True)["decimals"]
    prog = order_progress(order_id)
    new_lines = []
    for ln in q("SELECT * FROM order_lines WHERE order_id = ? ORDER BY sequence, id", (order_id,)):
        rest = ln["quantity"] - prog[ln["id"]]["invoiced"]
        if rest <= EPS:
            continue
        prod = product(ln["product_id"]) if ln["product_id"] else None
        acc = line_account(kind, prod, j)
        if not acc:
            raise AccountingError(t("Every line needs an account."))
        rate = q("SELECT rate FROM taxes WHERE id = ?", (ln["tax_id"],), one=True)["rate"] if ln["tax_id"] else 0
        sub, tax = compute_invoice_line(rest, ln["price_unit"], rate, dec)
        new_lines.append((ln, rest, acc, sub, tax))
    if not new_lines:
        raise AccountingError(t("Everything on this order is already invoiced."))
    untaxed = sum(n[3] for n in new_lines)
    tax = sum(n[4] for n in new_lines)
    terms = 30
    inv_id = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, ref, state, "
               "amount_untaxed, amount_tax, amount_total, order_id, created_by, created_at, project_id) "
               "VALUES (?,?,?,?,?,date(?, ?),?,'draft',?,?,?,?,?,?,?)",
               (o["branch_id"], j["id"], kind, o["partner_id"], d, d, f"+{terms} days", o["ref"] or o["number"],
                untaxed, tax, untaxed + tax, order_id, user_id, now(), o["project_id"]))
    for i, (ln, qty, acc, sub, tx) in enumerate(new_lines):
        x("INSERT INTO invoice_lines (invoice_id, sequence, product_id, order_line_id, description, account_id, quantity, "
          "price_unit, tax_id, subtotal, tax_amount, cost_centre_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
          (inv_id, i, ln["product_id"], ln["id"], ln["description"], acc, qty, ln["price_unit"], ln["tax_id"], sub, tx,
           o["cost_centre_id"]))
    audit(user_id, "create", "invoice", inv_id, f"from {o['number']}")
    return inv_id


def create_invoice_picking(invoice_id, user_id=None):
    """Delivery/receipt for stock products on an invoice or bill entered without an order, or the return of goods
    on a credit note / vendor refund."""
    inv = q("SELECT * FROM invoices WHERE id = ?", (invoice_id,), one=True)
    if inv["kind"] not in INVOICE_PICKING or inv["state"] != "posted":
        raise AccountingError(t("This action is not allowed in the current state."))
    kind = INVOICE_PICKING[inv["kind"]]
    wh = q("SELECT id FROM warehouses WHERE branch_id = ? AND active = 1 ORDER BY id LIMIT 1", (inv["branch_id"],), one=True)
    if not wh:
        raise AccountingError(t("Choose a warehouse."))
    moved = defaultdict(float)
    for r in q("SELECT pl.invoice_line_id, SUM(pl.quantity) AS qty FROM picking_lines pl JOIN pickings p "
               "ON p.id = pl.picking_id WHERE p.invoice_id = ? AND p.state != 'cancelled' GROUP BY pl.invoice_line_id",
               (invoice_id,)):
        moved[r["invoice_line_id"]] = r["qty"]
    lines = []
    for ln in q("SELECT il.* FROM invoice_lines il JOIN products p ON p.id = il.product_id "
                "WHERE il.invoice_id = ? AND p.type = 'stock' AND il.order_line_id IS NULL", (invoice_id,)):
        rest = ln["quantity"] - moved[ln["id"]]
        if rest > EPS:
            cost = int(round(ln["price_unit"] * inv["rate"])) if kind in ("in", "return_out") else \
                return_unit_cost(inv, ln, wh["id"]) if kind == "return_in" else 0
            lines.append({"product_id": ln["product_id"], "quantity": rest, "invoice_line_id": ln["id"],
                          "unit_cost": cost})
    return create_picking(kind, inv["branch_id"], wh["id"], inv["date"], lines, inv["partner_id"],
                          invoice_id=invoice_id, user_id=user_id)


def return_unit_cost(inv, ln, warehouse_id):
    """Cost of goods a customer sends back: what they cost when delivered against the original invoice, else the
    last delivery of the product to this customer, else today's average cost."""
    sql = ("SELECT SUM(pl.qty_signed) AS qty, SUM(pl.value_signed) AS value FROM picking_lines pl "
           "JOIN pickings pk ON pk.id = pl.picking_id WHERE pk.state = 'done' AND pk.kind = 'out' AND pl.product_id = ? ")
    if inv["origin_id"]:
        r = q(sql + "AND (pl.invoice_line_id IN (SELECT id FROM invoice_lines WHERE invoice_id = ?) "
                    "OR pl.order_line_id IN (SELECT order_line_id FROM invoice_lines WHERE invoice_id = ?))",
              (ln["product_id"], inv["origin_id"], inv["origin_id"]), one=True)
        if r["qty"] and r["qty"] < -EPS:
            return int(round(r["value"] / r["qty"]))
    r = q("SELECT pl.qty_signed AS qty, pl.value_signed AS value FROM picking_lines pl JOIN pickings pk "
          "ON pk.id = pl.picking_id WHERE pk.state = 'done' AND pk.kind = 'out' AND pl.product_id = ? "
          "AND pk.partner_id = ? AND pl.qty_signed < 0 ORDER BY pk.date DESC, pl.id DESC LIMIT 1",
          (ln["product_id"], inv["partner_id"]), one=True)
    if r:
        return int(round(r["value"] / r["qty"]))
    return avg_cost(ln["product_id"], warehouse_id)


# ---------------------------------------------------------------- purchase price differences

def settle_price_differences(order_line_ids, d, ref, user_id=None):
    """Receipts are valued at the PO price and bills clear GRNI at the bill price. Once a PO line is both received
    and billed, the difference on the matched quantity moves out of GRNI: into the inventory value for the part
    still in stock, into COGS for the part already sold. It is recomputed from the documents each time, so posting,
    resetting or cancelling bills and receipts in any order leaves exactly the right amount booked."""
    for olid in sorted(order_line_ids):
        ol = q("SELECT ol.*, o.kind, o.branch_id, o.warehouse_id, o.partner_id FROM order_lines ol "
               "JOIN orders o ON o.id = ol.order_id WHERE ol.id = ?", (olid,), one=True)
        if not ol or ol["kind"] != "purchase" or not ol["product_id"]:
            continue
        prod = product(ol["product_id"])
        if prod["type"] != "stock":
            continue
        rec = q("SELECT COALESCE(SUM(pl.qty_signed), 0) AS qty, COALESCE(SUM(pl.value_signed), 0) AS value, "
                "MAX(pk.warehouse_id) AS wid FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id "
                "WHERE pl.order_line_id = ? AND pk.kind = 'in' AND pk.state = 'done'", (olid,), one=True)
        bill = q("SELECT COALESCE(SUM(il.quantity), 0) AS qty, COALESCE(SUM(ROUND(il.subtotal * i.rate)), 0) AS value "
                 "FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id "
                 "WHERE il.order_line_id = ? AND i.kind = 'in_invoice' AND i.state = 'posted'", (olid,), one=True)
        matched = min(rec["qty"], bill["qty"])
        target = 0
        if matched > EPS:
            target = int(round(matched * bill["value"] / bill["qty"])) - int(round(matched * rec["value"] / rec["qty"]))
        booked = q("SELECT COALESCE(SUM(amount), 0) AS amount, COALESCE(MAX(qty), 0) AS qty FROM price_diffs "
                   "WHERE order_line_id = ?", (olid,), one=True)
        delta = target - booked["amount"]
        if not delta:
            continue
        _, grni_acc, _ = stock_accounts()
        inv_acc = inventory_account(prod)
        wid = rec["wid"] or ol["warehouse_id"]
        basis = max(matched, booked["qty"])
        on_hand, value = quant(prod["id"], wid) if wid else (0.0, 0)
        to_stock = int(round(delta * min(on_hand, basis) / basis)) if basis > EPS and on_hand > EPS else 0
        to_stock = max(to_stock, -value)
        entry = defaultdict(int)  # account -> debit-positive amount
        entry[inv_acc] += to_stock
        entry[cogs_account(prod)] += delta - to_stock
        entry[grni_acc] -= delta
        label = f"{t('Price difference')} {ref or ''}".strip()
        move_id = create_move(ol["branch_id"], stock_journal(ol["branch_id"]), d,
                              [{"account_id": a, "partner_id": ol["partner_id"], "label": label,
                                "debit": max(v, 0), "credit": max(-v, 0)} for a, v in entry.items() if v],
                              user_id=user_id, ref=ref, move_type="stock", partner_id=ol["partner_id"], post=True)
        if to_stock:
            _bump(prod["id"], wid, 0, to_stock)
        x("INSERT INTO price_diffs (order_line_id, date, qty, amount, to_stock, move_id, ref, created_at) "
          "VALUES (?,?,?,?,?,?,?,?)", (olid, d, matched, delta, to_stock, move_id, ref, now()))


# ---------------------------------------------------------------- landed costs

def validate_landed_cost(lc_id, user_id=None):
    """Share the charges over the selected receipts (by value or quantity) and add them to the cost of the goods."""
    lc = q("SELECT * FROM landed_costs WHERE id = ?", (lc_id,), one=True)
    if lc["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    check_lock(lc["date"])
    charges = q("SELECT * FROM landed_cost_charges WHERE landed_cost_id = ? ORDER BY id", (lc_id,))
    total = sum(c["amount"] for c in charges)
    if total <= 0:
        raise AccountingError(t("The amount must be greater than zero."))
    lines = q("SELECT pl.*, pk.warehouse_id, pk.branch_id FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id "
              "JOIN landed_cost_pickings lp ON lp.picking_id = pk.id WHERE lp.landed_cost_id = ? AND pk.kind = 'in' "
              "AND pk.state = 'done' AND pl.qty_signed > 0 ORDER BY pl.id", (lc_id,))
    if not lines:
        raise AccountingError(t("Choose at least one validated receipt."))
    if any(ln["branch_id"] != lc["branch_id"] for ln in lines):
        raise AccountingError(t("The receipts must be in the same branch as the landed cost."))
    basis = [ln["value_signed"] if lc["method"] == "value" else ln["qty_signed"] for ln in lines]
    if sum(basis) <= 0:
        raise AccountingError(t("The selected receipts have no value to share the charges over."))
    amounts = [int(total * b // sum(basis)) for b in basis]
    amounts[basis.index(max(basis))] += total - sum(amounts)
    # goods of a receipt that are no longer in stock carry their share into COGS
    received = defaultdict(float)
    for ln in lines:
        received[(ln["product_id"], ln["warehouse_id"])] += ln["qty_signed"]
    share = {k: min(max(quant(*k)[0], 0), qty) / qty for k, qty in received.items()}
    entry = defaultdict(int)  # account -> debit-positive amount
    for ln, amt in zip(lines, amounts):
        key = (ln["product_id"], ln["warehouse_id"])
        prod = product(ln["product_id"])
        to_stock = int(round(amt * share[key]))
        entry[inventory_account(prod)] += to_stock
        entry[cogs_account(prod)] += amt - to_stock
        if to_stock:
            _bump(ln["product_id"], ln["warehouse_id"], 0, to_stock)
        x("INSERT INTO landed_cost_lines (landed_cost_id, picking_line_id, product_id, warehouse_id, quantity, "
          "base_value, amount, to_stock) VALUES (?,?,?,?,?,?,?,?)",
          (lc_id, ln["id"], ln["product_id"], ln["warehouse_id"], ln["qty_signed"], ln["value_signed"], amt, to_stock))
    for c in charges:
        entry[c["account_id"]] -= c["amount"]
    move_id = create_move(lc["branch_id"], stock_journal(lc["branch_id"]), lc["date"],
                          [{"account_id": a, "label": f"{t('Landed Costs')} {lc['number']}",
                            "debit": max(v, 0), "credit": max(-v, 0)} for a, v in entry.items() if v],
                          user_id=user_id, ref=lc["number"], move_type="stock", post=True)
    x("UPDATE landed_costs SET state = 'done', amount = ?, move_id = ? WHERE id = ?", (total, move_id, lc_id))
    audit(user_id, "validate", "landed_cost", lc_id, lc["number"])


def invoice_needs_picking(invoice_id):
    r = q("SELECT COALESCE(SUM(il.quantity), 0) - COALESCE((SELECT SUM(pl.quantity) FROM picking_lines pl "
          "JOIN pickings p ON p.id = pl.picking_id WHERE p.invoice_id = ? AND p.state != 'cancelled'), 0) AS rest "
          "FROM invoice_lines il JOIN products p ON p.id = il.product_id "
          "WHERE il.invoice_id = ? AND p.type = 'stock' AND il.order_line_id IS NULL", (invoice_id, invoice_id), one=True)
    return r["rest"] > EPS


# ---------------------------------------------------------------- reporting helpers

def stock_value_by_branch(branch_ids):
    if not branch_ids:
        return {}
    return {r["branch_id"]: r["value"] for r in q(
        f"SELECT w.branch_id, SUM(sq.value) AS value FROM stock_quants sq JOIN warehouses w ON w.id = sq.warehouse_id "
        f"WHERE w.branch_id IN ({placeholders(branch_ids)}) GROUP BY w.branch_id", list(branch_ids))}


# ---------------------------------------------------------------- invoices vs goods movements (month-end check)

MATCH_SECTIONS = [
    ("inv_not_delivered", "Invoiced, not delivered", "Revenue is booked but the cost of goods sold is not."),
    ("delivered_not_inv", "Delivered, not invoiced", "Cost of goods sold is booked but the revenue is not."),
    ("credit_not_returned", "Credit notes, goods not received back",
     "Revenue is reversed but the goods are not back in stock and COGS is not reversed."),
    ("received_not_billed", "Received, not billed", "Sits in goods received not invoiced as a liability."),
    ("billed_not_received", "Billed, not received", "Sits in goods received not invoiced as a debit balance."),
    ("refund_not_returned", "Vendor refunds, goods not returned",
     "The refund is booked but the goods are still in stock."),
]


def stock_matching(branch_ids, as_of):
    """Stock lines whose invoiced quantity and moved quantity differ as of a date, grouped by MATCH_SECTIONS."""
    out = {k: [] for k, _, _ in MATCH_SECTIONS}
    if not branch_ids:
        return out
    ph = placeholders(branch_ids)
    avg = {}
    for r in q(f"SELECT sq.product_id, w.branch_id, SUM(sq.qty) AS qty, SUM(sq.value) AS value FROM stock_quants sq "
               f"JOIN warehouses w ON w.id = sq.warehouse_id WHERE w.branch_id IN ({ph}) "
               f"GROUP BY sq.product_id, w.branch_id", list(branch_ids)):
        if r["qty"] > EPS:
            avg[(r["product_id"], r["branch_id"])] = r["value"] / r["qty"]
    names = ("pa.name_en AS pname_en, pa.name_ar AS pname_ar, p.code AS pcode, p.name_en AS prname_en, "
             "p.name_ar AS prname_ar, p.uom, p.purchase_price")
    rows = []
    for kind in ("sale", "purchase"):
        for r in q(f"""
            SELECT ol.id, o.branch_id, o.number AS doc, o.date, o.id AS order_id, NULL AS invoice_id, ? AS kind,
                   ol.product_id, ol.price_unit, {names},
                   COALESCE((SELECT SUM(il.quantity * CASE WHEN i.kind LIKE '%refund' THEN -1 ELSE 1 END)
                             FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id
                             WHERE il.order_line_id = ol.id AND i.state = 'posted' AND i.date <= ?), 0) AS billed,
                   COALESCE((SELECT SUM(ABS(pl.qty_signed)) FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id
                             WHERE pl.order_line_id = ol.id AND pk.state = 'done' AND pk.kind IN ('in', 'out')
                             AND pk.date <= ?), 0) AS moved
            FROM order_lines ol JOIN orders o ON o.id = ol.order_id JOIN products p ON p.id = ol.product_id
            JOIN partners pa ON pa.id = o.partner_id
            WHERE o.kind = ? AND o.state = 'confirmed' AND p.type = 'stock' AND o.date <= ? AND o.branch_id IN ({ph})""",
                   [kind, as_of, as_of, kind, as_of] + list(branch_ids)):
            rows.append(dict(r))
    for r in q(f"""
        SELECT il.id, i.branch_id, i.number AS doc, i.date, NULL AS order_id, i.id AS invoice_id, i.kind,
               il.product_id, il.price_unit * i.rate AS price_unit, {names}, il.quantity AS billed,
               COALESCE((SELECT SUM(ABS(pl.qty_signed)) FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id
                         WHERE pl.invoice_line_id = il.id AND pk.state = 'done' AND pk.date <= ?), 0) AS moved
        FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id JOIN products p ON p.id = il.product_id
        JOIN partners pa ON pa.id = i.partner_id
        WHERE i.state = 'posted' AND i.date <= ? AND il.order_line_id IS NULL AND p.type = 'stock'
          AND i.branch_id IN ({ph})""", [as_of, as_of] + list(branch_ids)):
        rows.append(dict(r))
    for r in rows:
        diff = r["billed"] - r["moved"]
        if abs(diff) < EPS:
            continue
        if r["kind"] in ("sale", "out_invoice"):
            section = "inv_not_delivered" if diff > 0 else "delivered_not_inv"
        elif r["kind"] in ("purchase", "in_invoice"):
            section = "billed_not_received" if diff > 0 else "received_not_billed"
        elif r["kind"] == "out_refund" and diff > 0:
            section = "credit_not_returned"
        elif r["kind"] == "in_refund" and diff > 0:
            section = "refund_not_returned"
        else:
            continue
        r["qty"] = abs(diff)
        r["value"] = int(round(r["qty"] * r["price_unit"]))
        r["cost"] = int(round(r["qty"] * avg.get((r["product_id"], r["branch_id"]), r["purchase_price"])))
        out[section].append(r)
    for rs in out.values():
        rs.sort(key=lambda r: (r["date"], r["doc"] or ""))
    return out
