from collections import defaultdict
from datetime import date

from flask import Blueprint, abort, flash, redirect, render_template, request, url_for

from .. import check_branch, context_branch_ids, login_required, module_required, write_required
from ..db import commit, get_db, placeholders, q, x
from ..i18n import nm, t
from ..ledger import AccountingError, audit, company
from ..money import to_int
from ..stock import EPS, SCRAP_REASONS, adjust_stock, scrap_stock, create_invoice_picking, inventory_account_ids, next_number, \
    stock_value_by_branch, validate_landed_cost, validate_picking
from ..attachments import remove_all as remove_attachments
from .common import apply_search, partner_search, product_search, accounts_list, branch_map, default_branch, list_filters, user_id
from .orders import products_list, warehouses_for

bp = Blueprint("inventory", __name__, url_prefix="/inventory")
bp.before_request(module_required("inventory"))

PICKING_KINDS = {"in": "Receipts", "out": "Deliveries", "return_in": "Customer Returns", "return_out": "Vendor Returns",
                 "adjust": "Adjustments", "pos": "POS Sales", "transfer_out": "Transfers Out", "transfer_in": "Transfers In",
                 "scrap": "Write-offs"}
# one document of each kind
PICKING_TYPES = {"in": "Receipt", "out": "Delivery", "return_in": "Customer Return", "return_out": "Return to Vendor",
                 "adjust": "Adjustment", "pos": "POS Sales", "transfer_out": "Transfer Out", "transfer_in": "Transfer In",
                 "scrap": "Write-off"}


@bp.route("/transfers")
@login_required
def pickings():
    kind = request.args.get("kind", "")
    f = list_filters()
    rows = []
    if f["branch_ids"]:
        sql = (f"SELECT pk.*, p.name_en AS pname_en, p.name_ar AS pname_ar, w.code AS wcode, o.number AS onumber, "
               f"i.number AS inumber FROM pickings pk LEFT JOIN partners p ON p.id = pk.partner_id "
               f"JOIN warehouses w ON w.id = pk.warehouse_id LEFT JOIN orders o ON o.id = pk.order_id "
               f"LEFT JOIN invoices i ON i.id = pk.invoice_id WHERE pk.branch_id IN ({placeholders(f['branch_ids'])})")
        args = list(f["branch_ids"])
        if kind in PICKING_KINDS:
            sql += " AND pk.kind = ?"
            args.append(kind)
        if f["state"] in ("ready", "done", "cancelled"):
            sql += " AND pk.state = ?"
            args.append(f["state"])
        if f["date_from"]:
            sql += " AND pk.date >= ?"
            args.append(f["date_from"])
        if f["date_to"]:
            sql += " AND pk.date <= ?"
            args.append(f["date_to"])
        sql, args = apply_search(f, sql, args, [
            ("number", "Number", "pk.number LIKE ?", "text"),
            ("partner", "Partner", partner_search("p"), "text"),
            ("source", "Source document", "o.number LIKE ? OR i.number LIKE ? OR i.ref LIKE ?", "text"),
            ("product", "Product", product_search("picking_lines", "picking_id", "pk.id"), "text")])
        rows = q(sql + " ORDER BY pk.state = 'ready' DESC, pk.date DESC, pk.id DESC LIMIT 1000", args)
    return render_template("inventory/pickings.html", rows=rows, kind=kind, kinds=PICKING_KINDS, types=PICKING_TYPES,
                           f=f, branches=branch_map())


def get_picking(picking_id):
    p = q("SELECT pk.*, pa.name_en AS pname_en, pa.name_ar AS pname_ar, w.code AS wcode, w.name_en AS wname_en, "
          "w.name_ar AS wname_ar, o.number AS onumber, i.number AS inumber FROM pickings pk "
          "LEFT JOIN partners pa ON pa.id = pk.partner_id JOIN warehouses w ON w.id = pk.warehouse_id "
          "LEFT JOIN orders o ON o.id = pk.order_id LEFT JOIN invoices i ON i.id = pk.invoice_id WHERE pk.id = ?",
          (picking_id,), one=True)
    if not p:
        abort(404)
    check_branch(p["branch_id"])
    return p


@bp.route("/transfers/<int:picking_id>", methods=["GET", "POST"])
@login_required
def picking(picking_id):
    p = get_picking(picking_id)
    if request.method == "POST":
        if not g_can_write():
            abort(403)
        try:
            if request.form.get("action") == "cancel":
                if p["state"] != "ready":
                    raise AccountingError(t("This action is not allowed in the current state."))
                x("UPDATE pickings SET state = 'cancelled' WHERE id = ?", (picking_id,))
                audit(user_id(), "cancel", "picking", picking_id, p["number"])
            else:
                done, given = {}, {}
                for key, val in request.form.items():
                    if key.startswith("qty_"):
                        done[int(key[4:])] = float(val or 0)
                    elif key.startswith("lot_") and val.strip():
                        given[int(key[4:])] = (val.strip(), request.form.get("exp_" + key[4:]) or None)
                if p["kind"] == "in":
                    for ln in q("SELECT pl.id, pr.name_en, pr.name_ar FROM picking_lines pl JOIN products pr "
                                "ON pr.id = pl.product_id WHERE pl.picking_id = ? AND pr.tracking = 'lot'", (picking_id,)):
                        if ln["id"] not in given and done.get(ln["id"], 1) > EPS:
                            raise AccountingError(f"{t('Enter the batch number for')} {nm(ln)}")
                validate_picking(picking_id, done, request.form.get("date") or date.today().isoformat(), user_id(),
                                 lots=given)
            commit()
            flash(t("Done."), "success")
        except (AccountingError, ValueError) as e:
            get_db().rollback()
            flash(str(e), "error")
        return redirect(url_for("inventory.picking", picking_id=picking_id))
    lines = [dict(r) for r in q(
        "SELECT pl.*, pr.code, pr.name_en, pr.name_ar, pr.uom, pr.tracking, pr.shelf_life_days, "
        "COALESCE((SELECT qty FROM stock_quants WHERE product_id = pl.product_id AND warehouse_id = ?), 0) AS on_hand "
        "FROM picking_lines pl JOIN products pr ON pr.id = pl.product_id WHERE pl.picking_id = ? ORDER BY pl.id",
        (p["warehouse_id"], picking_id))]
    for ln in lines:
        if ln["shelf_life_days"]:
            from datetime import timedelta
            ln["default_expiry"] = (date.today() + timedelta(days=int(ln["shelf_life_days"]))).isoformat()
    from ..lots import line_lots
    batches = line_lots([ln["id"] for ln in lines])
    branch = q("SELECT * FROM branches WHERE id = ?", (p["branch_id"],), one=True)
    if request.args.get("export"):
        from ..exports import Report, respond
        title = {"out": "Delivery Note", "in": "Goods Receipt Note", "return_in": "Customer Return Note",
                 "return_out": "Return to Vendor Note", "transfer_out": "Transfer Note", "transfer_in": "Transfer Receipt"
                 }.get(p["kind"], "Stock Document")
        rep = Report(title, signatures=True, subtitle=nm({"name_en": p["pname_en"], "name_ar": p["pname_ar"]})
                     if p["pname_en"] else None)
        rep.details = [("Number", p["number"]), ("Date", p["date"]), ("Warehouse", f"{p['wcode']} — {nm({'name_en': p['wname_en'], 'name_ar': p['wname_ar']})}"),
                       ("Order", p["onumber"] or "—"), ("Invoice", p["inumber"] or "—"),
                       ("Status", t({"ready": "Ready", "done": "Done", "cancelled": "Cancelled"}.get(p["state"], p["state"])))]
        rep.column(t("#"), width=0.35).column(t("Code"), width=0.9).column(t("Product"), width=3.2) \
            .column(t("Unit"), width=0.7).column(t("Quantity"), "qty", 0.9)
        for i, ln in enumerate(lines, 1):
            lots_txt = ", ".join(f"{b['number']}" + (f" ({t('Exp.')} {b['expiry_date']})" if b["expiry_date"] else "")
                                 for b in batches.get(ln["id"], []))
            rep.row([i, ln["code"] or "", nm(ln) + (f" — {t('Batch')} {lots_txt}" if lots_txt else ""),
                     t(ln["uom"] or ""), ln["quantity"]])
        if p["notes"]:
            rep.notes.append(p["notes"])
        rep.notes.append(t("Received in good condition. Any shortage or damage must be written on this note."))
        out = respond(rep, (p["number"] or "stock").replace("/", "-"))
        if out:
            return out
    return render_template("inventory/picking.html", p=p, lines=lines, branch=branch, kinds=PICKING_KINDS,
                           types=PICKING_TYPES, today=date.today().isoformat(), line_lots=batches,
                           reasons=SCRAP_REASONS)


def g_can_write():
    from flask import g
    return g.user["role"] in ("admin", "accountant")


@bp.route("/adjust", methods=["GET", "POST"])
@write_required
def adjust():
    branches = branch_map()
    warehouses = warehouses_for(list(branches))
    if request.method == "POST":
        fm = request.form
        try:
            wh = q("SELECT * FROM warehouses WHERE id = ?", (fm.get("warehouse_id") or 0,), one=True)
            if not wh:
                raise AccountingError(t("Choose a warehouse."))
            check_branch(wh["branch_id"])
            counts = []
            for pid, cnt in zip(fm.getlist("product_id"), fm.getlist("counted")):
                if pid and cnt.strip() != "":
                    counts.append((int(pid), float(cnt)))
            if not counts:
                raise AccountingError(t("Add at least one line."))
            pk = adjust_stock(wh["branch_id"], wh["id"], fm.get("date") or date.today().isoformat(), counts,
                              fm.get("notes"), user_id())
            commit()
            flash(t("Stock adjusted."), "success")
            return redirect(url_for("inventory.picking", picking_id=pk))
        except (AccountingError, ValueError) as e:
            get_db().rollback()
            flash(str(e), "error")
    on_hand = defaultdict(dict)
    if warehouses:
        for r in q(f"SELECT * FROM stock_quants WHERE warehouse_id IN ({placeholders(warehouses)})",
                   [w["id"] for w in warehouses]):
            on_hand[r["warehouse_id"]][r["product_id"]] = r["qty"]
    return render_template("inventory/adjust.html", warehouses=warehouses, branches=branches, on_hand=on_hand,
                           products=[p for p in products_list() if p["type"] == "stock"],
                           today=date.today().isoformat())


@bp.route("/stock")
@login_required
def stock():
    ids = context_branch_ids()
    warehouses = warehouses_for(ids)
    wid = request.args.get("warehouse_id", type=int)
    search = request.args.get("q", "").strip()
    low = request.args.get("low")
    rows = []
    if warehouses:
        wids = [w["id"] for w in warehouses if not wid or w["id"] == wid]
        sql = (f"SELECT pr.*, w.id AS wid, w.code AS wcode, w.branch_id, COALESCE(sq.qty, 0) AS qty, "
               f"COALESCE(sq.value, 0) AS value FROM products pr CROSS JOIN warehouses w "
               f"LEFT JOIN stock_quants sq ON sq.product_id = pr.id AND sq.warehouse_id = w.id "
               f"WHERE pr.type = 'stock' AND pr.active = 1 AND w.id IN ({placeholders(wids)})")
        args = list(wids)
        if search:
            sql += " AND (pr.code LIKE ? OR pr.name_en LIKE ? OR pr.name_ar LIKE ? OR pr.barcode LIKE ?)"
            args += [f"%{search}%"] * 4
        rows = [dict(r) for r in q(sql + " ORDER BY pr.code, pr.name_en, w.code", args)]
        incoming = {(r["product_id"], r["warehouse_id"]): r for r in q(
            f"SELECT pl.product_id, pk.warehouse_id, "
            f"SUM(CASE WHEN pk.kind IN ('in', 'return_in') THEN pl.quantity ELSE 0 END) AS inc, "
            f"SUM(CASE WHEN pk.kind IN ('out', 'return_out') THEN pl.quantity ELSE 0 END) AS outg FROM picking_lines pl "
            f"JOIN pickings pk ON pk.id = pl.picking_id WHERE pk.state = 'ready' AND pk.warehouse_id IN "
            f"({placeholders(wids)}) GROUP BY pl.product_id, pk.warehouse_id", wids)}
        for r in rows:
            m = incoming.get((r["id"], r["wid"]))
            r["incoming"] = m["inc"] if m else 0
            r["outgoing"] = m["outg"] if m else 0
            r["forecast"] = r["qty"] + r["incoming"] - r["outgoing"]
            r["avg"] = int(round(r["value"] / r["qty"])) if r["qty"] > EPS else r["purchase_price"]
            r["low"] = r["min_qty"] > 0 and r["forecast"] < r["min_qty"] - EPS
        if low:
            rows = [r for r in rows if r["low"]]
    # reconcile stock valuation with the inventory account in the general ledger
    recon = []
    inv_accs = inventory_account_ids()
    values = stock_value_by_branch(ids)
    for b in branch_map().values():
        if b["id"] not in ids or not inv_accs:
            continue
        gl = q(f"SELECT COALESCE(SUM(ml.debit - ml.credit), 0) FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
               f"WHERE m.state = 'posted' AND ml.account_id IN ({placeholders(inv_accs)}) AND ml.branch_id = ?",
               list(inv_accs) + [b["id"]], one=True)[0]
        recon.append({"branch": b, "stock": values.get(b["id"], 0), "gl": gl})
    return render_template("inventory/stock.html", rows=rows, warehouses=warehouses, wid=wid, search=search, low=low,
                           branches=branch_map(), recon=recon)


@bp.route("/moves")
@login_required
def moves():
    f = list_filters()
    product_id = request.args.get("product_id", type=int)
    rows = []
    if f["branch_ids"]:
        sql = (f"SELECT pl.*, pk.number, pk.kind, pk.date, pk.id AS pk_id, pk.branch_id, w.code AS wcode, "
               f"pr.code, pr.name_en, pr.name_ar, pa.name_en AS pname_en, pa.name_ar AS pname_ar "
               f"FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id JOIN warehouses w ON w.id = pk.warehouse_id "
               f"JOIN products pr ON pr.id = pl.product_id LEFT JOIN partners pa ON pa.id = pk.partner_id "
               f"WHERE pk.state = 'done' AND pl.qty_signed != 0 AND pk.branch_id IN ({placeholders(f['branch_ids'])})")
        args = list(f["branch_ids"])
        if product_id:
            sql += " AND pl.product_id = ?"
            args.append(product_id)
        if f["date_from"]:
            sql += " AND pk.date >= ?"
            args.append(f["date_from"])
        if f["date_to"]:
            sql += " AND pk.date <= ?"
            args.append(f["date_to"])
        rows = q(sql + " ORDER BY pk.date DESC, pl.id DESC LIMIT 2000", args)
    return render_template("inventory/moves.html", rows=rows, f=f, branches=branch_map(), products=products_list(),
                           product_id=product_id)


@bp.route("/from-invoice/<int:inv_id>", methods=["POST"])
@write_required
def from_invoice(inv_id):
    inv = q("SELECT * FROM invoices WHERE id = ?", (inv_id,), one=True)
    if not inv:
        abort(404)
    check_branch(inv["branch_id"])
    try:
        pk = create_invoice_picking(inv_id, user_id())
        commit()
        return redirect(url_for("inventory.picking", picking_id=pk))
    except AccountingError as e:
        get_db().rollback()
        flash(str(e), "error")
        return redirect(url_for("invoices.view", inv_id=inv_id))


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

def get_landed(lc_id):
    lc = q("SELECT * FROM landed_costs WHERE id = ?", (lc_id,), one=True)
    if not lc:
        abort(404)
    check_branch(lc["branch_id"])
    return lc


def clearing_balances(ids):
    acc = company()["landed_cost_account_id"]
    if not acc or not ids:
        return {}
    return {r["branch_id"]: r["bal"] 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"WHERE m.state = 'posted' AND ml.account_id = ? AND ml.branch_id IN ({placeholders(ids)}) "
        f"GROUP BY ml.branch_id", [acc] + list(ids)) if r["bal"]}


@bp.route("/landed-costs")
@login_required
def landed_costs():
    f = list_filters()
    rows = []
    if f["branch_ids"]:
        sql = (f"SELECT lc.*, (SELECT GROUP_CONCAT(pk.number, ', ') FROM landed_cost_pickings lp JOIN pickings pk "
               f"ON pk.id = lp.picking_id WHERE lp.landed_cost_id = lc.id) AS receipts FROM landed_costs lc "
               f"WHERE lc.branch_id IN ({placeholders(f['branch_ids'])})")
        args = list(f["branch_ids"])
        if f["state"] in ("draft", "done"):
            sql += " AND lc.state = ?"
            args.append(f["state"])
        if f["date_from"]:
            sql += " AND lc.date >= ?"
            args.append(f["date_from"])
        if f["date_to"]:
            sql += " AND lc.date <= ?"
            args.append(f["date_to"])
        sql, args = apply_search(f, sql, args, [
            ("number", "Number", "lc.number LIKE ?", "text"),
            ("receipt", "Receipt", "EXISTS (SELECT 1 FROM landed_cost_pickings lp JOIN pickings pk ON pk.id = lp.picking_id "
                                   "WHERE lp.landed_cost_id = lc.id AND pk.number LIKE ?)", "text"),
            ("notes", "Notes", "lc.notes LIKE ?", "text"),
            ("amount", "Amount", "lc.amount = ?", "amount")])
        rows = q(sql + " ORDER BY lc.state = 'draft' DESC, lc.date DESC, lc.id DESC LIMIT 1000", args)
    acc = q("SELECT * FROM accounts WHERE id = ?", (company()["landed_cost_account_id"] or 0,), one=True)
    return render_template("inventory/landed_list.html", rows=rows, f=f, branches=branch_map(),
                           waiting=clearing_balances(f["branch_ids"]), clearing=acc)


@bp.route("/landed-costs/new", methods=["GET", "POST"])
@bp.route("/landed-costs/<int:lc_id>/edit", methods=["GET", "POST"])
@write_required
def landed_form(lc_id=None):
    lc = get_landed(lc_id) if lc_id else None
    if lc and lc["state"] != "draft":
        return redirect(url_for("inventory.landed_view", lc_id=lc_id))
    branches = branch_map()
    charges = [dict(r) for r in q("SELECT * FROM landed_cost_charges WHERE landed_cost_id = ? ORDER BY id",
                                  (lc_id,))] if lc else []
    chosen = {r[0] for r in q("SELECT picking_id FROM landed_cost_pickings WHERE landed_cost_id = ?",
                              (lc_id,))} if lc else set()
    if request.method == "POST":
        fm = request.form
        try:
            branch_id = check_branch(fm["branch_id"])
            d = fm.get("date") or date.today().isoformat()
            charges = []
            for desc, acc, amt in zip(fm.getlist("description"), fm.getlist("account_id"), fm.getlist("amount")):
                if not (desc or amt):
                    continue
                if not acc:
                    raise AccountingError(t("Every line needs an account."))
                amount = to_int(amt or 0)
                if amount <= 0:
                    raise AccountingError(t("The amount must be greater than zero."))
                charges.append({"description": desc, "account_id": int(acc), "amount": amount})
            if not charges:
                raise AccountingError(t("Add at least one line."))
            chosen = {int(v) for v in fm.getlist("picking_id")}
            if not chosen:
                raise AccountingError(t("Choose at least one validated receipt."))
            if q(f"SELECT 1 FROM pickings WHERE id IN ({placeholders(chosen)}) AND (branch_id != ? OR kind != 'in' "
                 f"OR state != 'done')", list(chosen) + [branch_id]):
                raise AccountingError(t("The receipts must be in the same branch as the landed cost."))
            method = fm.get("method") if fm.get("method") in ("value", "quantity") else "value"
            total = sum(c["amount"] for c in charges)
            if lc and (lc["branch_id"] != branch_id or str(lc["date"])[:4] != d[:4]):
                x("UPDATE landed_costs SET number = ? WHERE id = ?",
                  (next_number("landed_costs", "number", "LC", branch_id, d), lc_id))
            if lc:
                x("UPDATE landed_costs SET branch_id = ?, date = ?, method = ?, amount = ?, notes = ? WHERE id = ?",
                  (branch_id, d, method, total, fm.get("notes"), lc_id))
                x("DELETE FROM landed_cost_charges WHERE landed_cost_id = ?", (lc_id,))
                x("DELETE FROM landed_cost_pickings WHERE landed_cost_id = ?", (lc_id,))
            else:
                lc_id = x("INSERT INTO landed_costs (number, branch_id, date, method, amount, notes, created_by, "
                          "created_at) VALUES (?,?,?,?,?,?,?,datetime('now'))",
                          (next_number("landed_costs", "number", "LC", branch_id, d), branch_id, d, method, total,
                           fm.get("notes"), user_id()))
            for c in charges:
                x("INSERT INTO landed_cost_charges (landed_cost_id, description, account_id, amount) VALUES (?,?,?,?)",
                  (lc_id, c["description"], c["account_id"], c["amount"]))
            for pid in chosen:
                x("INSERT INTO landed_cost_pickings (landed_cost_id, picking_id) VALUES (?,?)", (lc_id, pid))
            audit(user_id(), "save", "landed_cost", lc_id)
            if fm.get("action") == "validate":
                validate_landed_cost(lc_id, user_id())
            commit()
            flash(t("Saved."), "success")
            return redirect(url_for("inventory.landed_view", lc_id=lc_id))
        except (AccountingError, ValueError) as e:
            get_db().rollback()
            flash(str(e), "error")
            lc = dict(fm.items())
            lc["id"] = lc_id
    receipts = []
    if branches:
        receipts = q(f"SELECT pk.*, p.name_en AS pname_en, p.name_ar AS pname_ar, o.number AS onumber, "
                     f"(SELECT SUM(value_signed) FROM picking_lines WHERE picking_id = pk.id) AS value "
                     f"FROM pickings pk LEFT JOIN partners p ON p.id = pk.partner_id "
                     f"LEFT JOIN orders o ON o.id = pk.order_id WHERE pk.kind = 'in' AND pk.state = 'done' "
                     f"AND pk.branch_id IN ({placeholders(branches)}) ORDER BY pk.date DESC, pk.id DESC LIMIT 300",
                     list(branches))
    return render_template("inventory/landed_form.html", lc=lc, charges=charges, chosen=chosen, receipts=receipts,
                           branches=branches, accounts=accounts_list(), default_branch=default_branch(),
                           clearing=company()["landed_cost_account_id"], today=date.today().isoformat())


@bp.route("/landed-costs/<int:lc_id>", methods=["GET", "POST"])
@login_required
def landed_view(lc_id):
    lc = get_landed(lc_id)
    if request.method == "POST":
        if not g_can_write():
            abort(403)
        try:
            if request.form.get("action") == "validate":
                validate_landed_cost(lc_id, user_id())
                flash(t("Done."), "success")
            elif request.form.get("action") == "delete" and lc["state"] == "draft":
                x("DELETE FROM landed_costs WHERE id = ?", (lc_id,))
                remove_attachments("landed_cost", lc_id)
                audit(user_id(), "delete", "landed_cost", lc_id, lc["number"])
                commit()
                flash(t("Deleted."), "success")
                return redirect(url_for("inventory.landed_costs"))
            commit()
        except AccountingError as e:
            get_db().rollback()
            flash(str(e), "error")
        return redirect(url_for("inventory.landed_view", lc_id=lc_id))
    charges = q("SELECT c.*, a.code, a.name_en, a.name_ar FROM landed_cost_charges c JOIN accounts a "
                "ON a.id = c.account_id WHERE c.landed_cost_id = ? ORDER BY c.id", (lc_id,))
    receipts = q("SELECT pk.*, p.name_en AS pname_en, p.name_ar AS pname_ar FROM landed_cost_pickings lp "
                 "JOIN pickings pk ON pk.id = lp.picking_id LEFT JOIN partners p ON p.id = pk.partner_id "
                 "WHERE lp.landed_cost_id = ? ORDER BY pk.date, pk.id", (lc_id,))
    lines = q("SELECT l.*, pr.code, pr.name_en, pr.name_ar, pr.uom, pk.number FROM landed_cost_lines l "
              "JOIN products pr ON pr.id = l.product_id JOIN picking_lines pl ON pl.id = l.picking_line_id "
              "JOIN pickings pk ON pk.id = pl.picking_id WHERE l.landed_cost_id = ? ORDER BY l.id", (lc_id,))
    branch = q("SELECT * FROM branches WHERE id = ?", (lc["branch_id"],), one=True)
    return render_template("inventory/landed_view.html", lc=lc, charges=charges, receipts=receipts, lines=lines,
                           branch=branch)


# ---------------------------------------------------------------- write-offs (damaged / expired / lost)

@bp.route("/scrap", methods=["GET", "POST"])
@write_required
def scrap():
    from ..costcentres import options as cost_centre_options
    branches = branch_map()
    warehouses = warehouses_for(list(branches))
    fm = request.form
    if request.method == "POST":
        try:
            wh = q("SELECT * FROM warehouses WHERE id = ?", (fm.get("warehouse_id") or 0,), one=True)
            if not wh:
                raise AccountingError(t("Choose a warehouse."))
            check_branch(wh["branch_id"])
            lines = []
            for pid, qty, lot in zip(fm.getlist("product_id"), fm.getlist("qty"), fm.getlist("lot_id")):
                if pid and (qty or "").strip():
                    if float(qty) <= 0:
                        raise AccountingError(t("The quantity must be greater than zero."))
                    lines.append((int(pid), float(qty), int(lot) if lot else None))
            if not lines:
                raise AccountingError(t("Add at least one line."))
            pk = scrap_stock(wh["branch_id"], wh["id"], fm.get("date") or date.today().isoformat(), lines,
                             fm.get("reason"), fm.get("notes"), user_id(), int(fm["cost_centre_id"])
                             if (fm.get("cost_centre_id") or "").isdigit() else None)
            commit()
            flash(t("Stock written off."), "success")
            return redirect(url_for("inventory.picking", picking_id=pk))
        except (AccountingError, ValueError) as e:
            get_db().rollback()
            flash(str(e), "error")
    on_hand = defaultdict(dict)
    lots = defaultdict(lambda: defaultdict(list))
    if warehouses:
        wids = [w["id"] for w in warehouses]
        for r in q(f"SELECT * FROM stock_quants WHERE warehouse_id IN ({placeholders(wids)})", wids):
            on_hand[r["warehouse_id"]][r["product_id"]] = r["qty"]
        for r in q(f"SELECT l.id, l.product_id, l.number, l.expiry_date, lq.warehouse_id, lq.qty FROM lot_quants lq "
                   f"JOIN lots l ON l.id = lq.lot_id WHERE lq.qty > 0.000001 AND lq.warehouse_id IN ({placeholders(wids)}) "
                   f"ORDER BY l.expiry_date IS NULL, l.expiry_date", wids):
            lots[r["warehouse_id"]][r["product_id"]].append(
                {"id": r["id"], "label": r["number"] + (f" · {t('Exp.')} {r['expiry_date']}" if r["expiry_date"] else "")
                 + f" ({r['qty']:g})"})
    pre = {"warehouse_id": request.args.get("warehouse_id", type=int), "product_id": request.args.get("product_id", type=int),
           "lot_id": request.args.get("lot_id", type=int), "qty": request.args.get("qty", ""),
           "reason": request.args.get("reason", "damaged")}
    return render_template("inventory/scrap.html", warehouses=warehouses, branches=branches, on_hand=on_hand,
                           lots=lots, products=[p for p in products_list() if p["type"] == "stock"],
                           reasons=SCRAP_REASONS, today=date.today().isoformat(), pre=pre,
                           cost_centres=cost_centre_options(None))


# ---------------------------------------------------------------- batches and expiry

@bp.route("/batches")
@login_required
def batches():
    ids = context_branch_ids()
    warehouses = warehouses_for(ids)
    status = request.args.get("status", "stock")
    wid = request.args.get("warehouse_id", type=int)
    search = request.args.get("q", "").strip()
    rows = []
    today = date.today().isoformat()
    if warehouses:
        wids = [w["id"] for w in warehouses if not wid or w["id"] == wid]
        sql = (f"SELECT l.*, lq.qty, lq.warehouse_id, w.code AS wcode, w.branch_id, p.code, p.name_en, p.name_ar, p.uom, "
               f"CAST(julianday(l.expiry_date) - julianday(?) AS INTEGER) AS days, "
               f"COALESCE((SELECT value / NULLIF(qty, 0) FROM stock_quants WHERE product_id = l.product_id "
               f"AND warehouse_id = lq.warehouse_id), p.purchase_price) AS unit_cost "
               f"FROM lots l JOIN lot_quants lq ON lq.lot_id = l.id JOIN warehouses w ON w.id = lq.warehouse_id "
               f"JOIN products p ON p.id = l.product_id WHERE lq.qty > 0.000001 AND lq.warehouse_id IN ({placeholders(wids)})")
        args = [today] + wids
        if status == "expired":
            sql += " AND l.expiry_date < ?"
            args.append(today)
        elif status in ("30", "90"):
            sql += f" AND l.expiry_date IS NOT NULL AND l.expiry_date <= date(?, '+{int(status)} days')"
            args.append(today)
        if search:
            sql += " AND (l.number LIKE ? OR p.code LIKE ? OR p.name_en LIKE ? OR p.name_ar LIKE ?)"
            args += [f"%{search}%"] * 4
        rows = q(sql + " ORDER BY l.expiry_date IS NULL, l.expiry_date, p.code, l.number", args)
    return render_template("inventory/batches.html", rows=rows, warehouses=warehouses, wid=wid, status=status,
                           search=search, branches=branch_map())


@bp.route("/batches/<int:lot_id>")
@login_required
def batch(lot_id):
    lot = q("SELECT l.*, p.code, p.name_en, p.name_ar, p.uom FROM lots l JOIN products p ON p.id = l.product_id "
            "WHERE l.id = ?", (lot_id,), one=True) or abort(404)
    ids = context_branch_ids()
    moves = q(f"SELECT pll.qty, pk.id AS pk_id, pk.number, pk.kind, pk.date, pk.branch_id, w.code AS wcode, "
              f"pa.name_en AS pname_en, pa.name_ar AS pname_ar, pk.partner_id FROM picking_line_lots pll "
              f"JOIN picking_lines pl ON pl.id = pll.picking_line_id JOIN pickings pk ON pk.id = pl.picking_id "
              f"JOIN warehouses w ON w.id = pk.warehouse_id LEFT JOIN partners pa ON pa.id = pk.partner_id "
              f"WHERE pll.lot_id = ? AND pk.branch_id IN ({placeholders(ids) if ids else 'NULL'}) ORDER BY pk.date, pll.id",
              [lot_id] + list(ids))
    stock = q("SELECT lq.qty, w.code, w.name_en, w.name_ar FROM lot_quants lq JOIN warehouses w ON w.id = lq.warehouse_id "
              "WHERE lq.lot_id = ? AND lq.qty > 0.000001", (lot_id,))
    customers = {}
    for m in moves:
        if m["kind"] in ("out", "pos") and m["partner_id"]:
            c = customers.setdefault(m["partner_id"], {"name": nm({"name_en": m["pname_en"], "name_ar": m["pname_ar"]}),
                                                       "qty": 0})
            c["qty"] += -m["qty"]
    return render_template("inventory/batch.html", lot=lot, moves=moves, stock=stock, customers=list(customers.values()),
                           kinds=PICKING_TYPES, today=date.today().isoformat())


# ---------------------------------------------------------------- barcodes and labels

@bp.route("/labels")
@login_required
def labels():
    """Product labels with barcode: chosen products, or one label per unit received on a receipt."""
    items = []
    picking_id = request.args.get("picking_id", type=int)
    if picking_id:
        get_picking(picking_id)
        from ..lots import line_lots
        pls = q("SELECT pl.id, pl.product_id, pl.qty_signed FROM picking_lines pl WHERE pl.picking_id = ? ORDER BY pl.id",
                (picking_id,))
        lots_by = line_lots([p["id"] for p in pls])
        for pl in pls:
            prod = q("SELECT * FROM products WHERE id = ?", (pl["product_id"],), one=True)
            batches_ = lots_by.get(pl["id"]) or [None]
            for b in batches_:
                n = int(round(abs(b["qty"]) if b else abs(pl["qty_signed"])))
                items.append({"p": prod, "lot": b, "copies": max(min(n, 500), 0)})
    else:
        for pid, n in zip(request.args.getlist("product_id"), request.args.getlist("copies")):
            if pid.isdigit():
                prod = q("SELECT * FROM products WHERE id = ?", (int(pid),), one=True)
                if prod:
                    items.append({"p": prod, "lot": None, "copies": max(min(int(n or 1), 500), 1)})
    size = request.args.get("size", "a4")
    show_price = request.args.get("price", "1") == "1"
    from ..barcode import svg
    return render_template("inventory/labels.html", items=items, size=size, show_price=show_price, svg=svg,
                           products=products_list(), picking_id=picking_id,
                           currency=company()["currency"])


@bp.route("/barcodes/generate", methods=["POST"])
@write_required
def generate_barcodes():
    """Give every active product without a barcode an in-store EAN-13 (starting with 2)."""
    from ..barcode import in_store_ean
    n = 0
    for p in q("SELECT id FROM products WHERE active = 1 AND type != 'service' AND (barcode IS NULL OR barcode = '')"):
        code = in_store_ean(p["id"])
        if not q("SELECT 1 FROM products WHERE barcode = ?", (code,)):
            x("UPDATE products SET barcode = ? WHERE id = ?", (code, p["id"]))
            n += 1
    audit(user_id(), "update", "product", None, f"barcodes generated: {n}")
    commit()
    flash(f"{t('Barcodes created')}: {n}", "success")
    return redirect(request.referrer or url_for("inventory.labels"))


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

@bp.route("/reorder", methods=["GET", "POST"])
@login_required
def reorder():
    from ..insights import COVER_DAYS, USAGE_DAYS, create_reorder_pos, reorder_suggestions
    ids = context_branch_ids()
    if request.method == "POST":
        if not g_can_write():
            abort(403)
        fm = request.form
        try:
            picks = []
            for key in fm.getlist("pick"):
                pid, wid = (int(v) for v in key.split("-"))
                wh = q("SELECT branch_id FROM warehouses WHERE id = ?", (wid,), one=True)
                check_branch(wh["branch_id"])
                vid = fm.get(f"vendor_{key}")
                picks.append((pid, wid, int(vid) if vid else None, float(fm.get(f"qty_{key}") or 0)))
            made = create_reorder_pos(picks, date.today().isoformat(), user_id())
            commit()
            flash(f"{t('Draft purchase orders created')}: {len(made)}", "success")
            if len(made) == 1:
                return redirect(url_for("orders.view", order_id=made[0]))
            return redirect(url_for("orders.index", kind="purchase", state="draft"))
        except (AccountingError, ValueError) as e:
            get_db().rollback()
            flash(str(e), "error")
    wid = request.args.get("warehouse_id", type=int)
    show_all = bool(request.args.get("all"))
    rows = reorder_suggestions(ids, wid, show_all)
    from ..procurement import approved_vendors
    return render_template("inventory/reorder.html", rows=rows, warehouses=warehouses_for(ids), wid=wid,
                           show_all=show_all, vendors=approved_vendors(), usage_days=USAGE_DAYS, cover_days=COVER_DAYS,
                           branches=branch_map())
