"""Overview dashboards for the Sales, Purchases and Inventory apps, and the trading reports: gross margin, cash
forecast and supplier performance."""
from collections import defaultdict
from datetime import date, timedelta

from flask import Blueprint, render_template, request

from .. import context_branch_ids, has, login_required
from ..db import placeholders, q
from ..exports import Report, respond
from ..i18n import nm, t
from ..insights import MARGIN_GROUPS, cash_forecast, gross_margin, ledger_gross, rates_for, reorder_suggestions, \
    supplier_performance
from ..ledger import AccountingError, branch_rows, fy_start, invoice_residuals
from ..money import translate
from .dashboard import last_months, nice_ceiling
from .reports import filters, meta

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


def _scale(peak):
    return [peak * i // 4 for i in range(4, -1, -1)]


def _monthly(by_month):
    months = last_months()
    series = {m: [by_month.get(m, [0, 0])[0], by_month.get(m, [0, 0])[0] - by_month.get(m, [0, 0])[1]] for m in months}
    return months, series


def _open_residual(kinds, ids, rates, today, overdue_only=False, due_within=None):
    rows = q(f"SELECT id, branch_id, COALESCE(due_date, date) AS due FROM invoices WHERE state = 'posted' "
             f"AND kind IN ({placeholders(kinds)}) AND branch_id IN ({placeholders(ids)})", list(kinds) + ids)
    res = invoice_residuals([r["id"] for r in rows], base=True)
    total = n = 0
    for r in rows:
        v = res.get(r["id"], 0)
        if v <= 0:
            continue
        if overdue_only and r["due"] >= today:
            continue
        if due_within is not None and not (today <= r["due"] <= due_within):
            continue
        total += translate(v, rates[r["branch_id"]])
        n += 1
    return total, n


@bp.route("/sales")
@login_required
def sales():
    ids = context_branch_ids()
    branches = branch_rows(ids)
    if not branches:
        return render_template("reports/empty.html")
    today = date.today()
    ti = today.isoformat()
    fys = fy_start(ti)
    months = last_months()
    try:
        by_month, _ = ledger_gross(branches, months[0] + "-01", ti)
        _, ytd = ledger_gross(branches, fys, ti)
        ytd_rows, _, currency = gross_margin(branches, fys, ti, "customer")
        prod_rows, _, _ = gross_margin(branches, fys, ti, "product")
        _, rates = rates_for(branches, ti, ti, average=False)
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    months, series = _monthly(by_month)
    this_m, last_m = series[months[-1]][0], series[months[-2]][0]
    peak = nice_ceiling(max([max(v[0], 0) for v in series.values()] + [1]))
    ph = placeholders(ids)
    quotes = q(f"SELECT COUNT(*) AS n, COALESCE(SUM(amount_untaxed), 0) AS v, branch_id FROM orders WHERE kind = 'sale' "
               f"AND state = 'draft' AND branch_id IN ({ph}) GROUP BY branch_id", ids)
    quote_n = sum(r["n"] for r in quotes)
    quote_v = sum(translate(r["v"], rates[r["branch_id"]]) for r in quotes)
    from ..stock import order_status
    to_invoice = sum(1 for o in q(f"SELECT * FROM orders WHERE kind = 'sale' AND state = 'confirmed' AND branch_id IN ({ph})", ids)
                     if order_status(o)[1] in ("no", "partial"))
    recv, recv_n = _open_residual(["out_invoice"], ids, rates, ti)
    over, over_n = _open_residual(["out_invoice"], ids, rates, ti, overdue_only=True)
    return render_template("insights/sales.html", currency=currency, months=months, series=series, peak=peak,
                           scale=_scale(peak), this_m=this_m, last_m=last_m, ytd=ytd, customers=ytd_rows[:6],
                           products=sorted(prod_rows, key=lambda r: -r["revenue"])[:6], quote_n=quote_n,
                           quote_v=quote_v, to_invoice=to_invoice, recv=recv, recv_n=recv_n, over=over,
                           over_n=over_n, fys=fys, branches=branches)


@bp.route("/purchases")
@login_required
def purchases():
    ids = context_branch_ids()
    branches = branch_rows(ids)
    if not branches:
        return render_template("reports/empty.html")
    today = date.today()
    ti = today.isoformat()
    fys = fy_start(ti)
    months = last_months()
    try:
        currency, rates = rates_for(branches, ti, ti, average=False)
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    ph = placeholders(ids)
    series = {m: [0, 0] for m in months}
    vendors = defaultdict(int)
    for r in q(f"SELECT substr(i.date, 1, 7) AS ym, i.partner_id, i.branch_id, i.date, "
               f"SUM(ROUND(i.amount_untaxed * i.rate) * CASE WHEN i.kind = 'in_refund' THEN -1 ELSE 1 END) AS v "
               f"FROM invoices i WHERE i.state = 'posted' AND i.kind IN ('in_invoice', 'in_refund') AND i.date >= ? "
               f"AND i.branch_id IN ({ph}) GROUP BY ym, i.partner_id, i.branch_id", [min(months[0] + "-01", fys)] + ids):
        v = translate(r["v"] or 0, rates[r["branch_id"]])
        if r["ym"] in series:
            series[r["ym"]][0] += v
        if r["ym"] + "-31" >= fys:
            vendors[r["partner_id"]] += v
    peak = nice_ceiling(max([max(v[0], 0) for v in series.values()] + [1]))
    ytd = sum(vendors.values())
    names = {r["id"]: r for r in q(f"SELECT id, name_en, name_ar FROM partners WHERE id IN ({placeholders(vendors) if vendors else 'NULL'})",
                                   list(vendors))}
    top = sorted(({"label": nm(names[k]) if k in names else "—", "value": v, "id": k} for k, v in vendors.items()),
                 key=lambda r: -r["value"])[:6]
    po_open = q(f"SELECT COUNT(*) FROM orders WHERE kind = 'purchase' AND state = 'to_approve' AND branch_id IN ({ph})",
                ids, one=True)[0]
    to_receive = q(f"SELECT COUNT(*) FROM pickings WHERE kind = 'in' AND state = 'ready' AND branch_id IN ({ph})",
                   ids, one=True)[0]
    pay, pay_n = _open_residual(["in_invoice"], ids, rates, ti)
    due7, due7_n = _open_residual(["in_invoice"], ids, rates, ti, due_within=(today + timedelta(days=7)).isoformat())
    over, over_n = _open_residual(["in_invoice"], ids, rates, ti, overdue_only=True)
    perf = supplier_performance(ids, (today - timedelta(days=365)).isoformat(), ti)
    return render_template("insights/purchases.html", currency=currency, months=months, series=series, peak=peak,
                           scale=_scale(peak), this_m=series[months[-1]][0], last_m=series[months[-2]][0], ytd=ytd,
                           top=top, po_open=po_open, to_receive=to_receive, pay=pay, pay_n=pay_n, due7=due7,
                           due7_n=due7_n, over=over, over_n=over_n, perf=perf[:6], fys=fys)


@bp.route("/inventory")
@login_required
def inventory():
    ids = context_branch_ids()
    branches = branch_rows(ids)
    if not branches or not has("inventory"):
        return render_template("reports/empty.html")
    today = date.today()
    ti = today.isoformat()
    fys = fy_start(ti)
    try:
        currency, rates = rates_for(branches, ti, ti, average=False)
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    ph = placeholders(ids)
    quants = q(f"SELECT sq.*, w.branch_id, p.code, p.name_en, p.name_ar, p.category_id, p.uom FROM stock_quants sq "
               f"JOIN warehouses w ON w.id = sq.warehouse_id JOIN products p ON p.id = sq.product_id "
               f"WHERE w.branch_id IN ({ph}) AND (sq.qty != 0 OR sq.value != 0)", ids)
    value = sum(translate(r["value"], rates[r["branch_id"]]) for r in quants)
    by_product = defaultdict(lambda: {"value": 0, "qty": 0.0})
    by_cat = defaultdict(int)
    for r in quants:
        v = translate(r["value"], rates[r["branch_id"]])
        bp_ = by_product[r["product_id"]]
        bp_["value"] += v
        bp_["qty"] += r["qty"]
        bp_["row"] = r
        by_cat[r["category_id"] or 0] += v
    cats = {c["id"]: nm(c) for c in q("SELECT * FROM product_categories")}
    cat_rows = sorted(({"label": cats.get(k, t("Without category")), "value": v} for k, v in by_cat.items() if v),
                      key=lambda r: -r["value"])
    top = sorted(({"label": f"{d['row']['code'] or ''} {nm(d['row'])}".strip(), "value": d["value"], "qty": d["qty"],
                   "uom": d["row"]["uom"], "id": k} for k, d in by_product.items()), key=lambda r: -r["value"])[:8]
    moved = {r[0] for r in q(f"SELECT DISTINCT pl.product_id FROM picking_lines pl JOIN pickings pk ON pk.id = pl.picking_id "
                             f"WHERE pk.state = 'done' AND pl.qty_signed < 0 AND pk.kind IN ('out', 'pos', 'transfer_out') "
                             f"AND pk.date >= ? AND pk.branch_id IN ({ph})", [(today - timedelta(days=90)).isoformat()] + ids)}
    slow = sum(d["value"] for k, d in by_product.items() if k not in moved and d["qty"] > 0)
    slow_n = sum(1 for k, d in by_product.items() if k not in moved and d["qty"] > 0)
    reorder_n = len(reorder_suggestions(ids))
    soon = (today + timedelta(days=30)).isoformat()
    exp = q(f"SELECT COUNT(DISTINCT l.id) AS n, SUM(CASE WHEN l.expiry_date < ? THEN 1 ELSE 0 END) AS expired "
            f"FROM lots l JOIN lot_quants lq ON lq.lot_id = l.id JOIN warehouses w ON w.id = lq.warehouse_id "
            f"WHERE lq.qty > 0.000001 AND l.expiry_date IS NOT NULL AND l.expiry_date <= ? AND w.branch_id IN ({ph})",
            [ti, soon] + ids, one=True)
    scrap = sum(translate(-(r["v"] or 0), rates[r["branch_id"]]) for r in q(
        f"SELECT pk.branch_id, SUM(pl.value_signed) AS v FROM pickings pk JOIN picking_lines pl ON pl.picking_id = pk.id "
        f"WHERE pk.kind = 'scrap' AND pk.state = 'done' AND pk.date >= ? AND pk.branch_id IN ({ph}) GROUP BY pk.branch_id",
        [fys] + ids))
    todo = {k: q(f"SELECT COUNT(*) FROM pickings WHERE kind = ? AND state = 'ready' AND branch_id IN ({ph})",
                 [k] + ids, one=True)[0] for k in ("in", "out")}
    products_n = q("SELECT COUNT(*) FROM products WHERE type = 'stock' AND active = 1", one=True)[0]
    cat_peak = max([r["value"] for r in cat_rows] + [1])
    return render_template("insights/inventory.html", currency=currency, value=value, cat_rows=cat_rows,
                           cat_peak=cat_peak, top=top, slow=slow, slow_n=slow_n, reorder_n=reorder_n,
                           exp_n=exp["n"] or 0, expired_n=exp["expired"] or 0, scrap=scrap, todo=todo,
                           products_n=products_n, fys=fys)


# ---------------------------------------------------------------- reports

@bp.route("/gross-margin")
@login_required
def margin():
    f = filters()
    group = request.args.get("by", "product")
    if group not in MARGIN_GROUPS:
        group = "product"
    if not f["branches"]:
        return render_template("reports/empty.html")
    try:
        rows, total, currency = gross_margin(f["branches"], f["date_from"], f["date_to"], group)
        _, gl = ledger_gross(f["branches"], f["date_from"], f["date_to"])
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    rep = Report("Gross Margin", meta(f, currency, f"{t('By')} {t(group.capitalize())}"))
    rep.column(t(group.capitalize()), width=3)
    if group == "product":
        rep.column(t("Quantity"), "qty", 0.8)
    rep.column(t("Revenue"), "num").column(t("Cost of Goods Sold"), "num").column(t("Gross Margin"), "num") \
        .column(t("Margin %"), "text", 0.7)
    for r in rows:
        rep.row([r["label"]] + ([r["qty"]] if group == "product" else []) +
                [r["revenue"], r["cost"], r["margin"], f"{r['pct']:.1f}%" if r["pct"] is not None else "—"])
    rep.row([t("Total")] + ([None] if group == "product" else []) +
            [total["revenue"], total["cost"], total["margin"], f"{total['pct']:.1f}%" if total["pct"] is not None else "—"],
            "grand")
    pad = [None] if group == "product" else []
    rep.row([t("Revenue in the ledger not on invoice lines or POS")] + pad + [gl["revenue"] - total["revenue"], None, None, ""])
    rep.row([t("Cost of revenue in the ledger not from goods movements")] + pad + [None, gl["cost"] - total["cost"], None, ""])
    rep.row([t("Gross profit as in the Profit & Loss")] + pad + [gl["revenue"], gl["cost"], gl["margin"],
                                                                f"{gl['pct']:.1f}%" if gl["pct"] is not None else "—"], "total")
    out = respond(rep, "Gross_Margin")
    if out:
        return out
    return render_template("insights/margin.html", rows=rows, total=total, f=f, group=group, currency=currency, gl=gl)


@bp.route("/cash-forecast")
@login_required
def forecast():
    ids = context_branch_ids()
    branches = branch_rows(ids)
    if not branches:
        return render_template("reports/empty.html")
    orders = request.args.get("orders", "1") == "1"
    try:
        fc = cash_forecast(branches, include_orders=orders)
    except AccountingError as e:
        return render_template("reports/empty.html", error=str(e))
    rep = Report("13-Week Cash Forecast", [f"{t('Amounts in')} {fc['currency']}",
                                          ", ".join(f"{b['code']} {nm(b)}" for b in branches)], landscape=True)
    rep.column(t("Week of"), width=2.2)
    for w in fc["weeks"]:
        rep.column(w.strftime("%d %b"), "num", 0.75)
    rep.row([t("Opening cash")] + [fc["opening"]] + [None] * 12, "group")
    for lbl, vals in fc["inflow"]:
        rep.row([t(lbl)] + vals)
    rep.row([t("Total cash in")] + fc["total_in"], "subtotal")
    for lbl, vals in fc["outflow"]:
        rep.row([t(lbl)] + vals)
    rep.row([t("Total cash out")] + fc["total_out"], "subtotal")
    rep.row([t("Net cash flow")] + fc["net"], "total")
    rep.row([t("Closing cash")] + fc["closing"], "grand")
    out = respond(rep, "Cash_Forecast")
    if out:
        return out
    lo = min(fc["closing"] + [fc["opening"], 0])
    hi = max(fc["closing"] + [fc["opening"], 1])
    return render_template("insights/forecast.html", fc=fc, orders=orders, lo=lo, hi=hi)


@bp.route("/suppliers")
@login_required
def suppliers():
    f = filters()
    if not f["branches"]:
        return render_template("reports/empty.html")
    rows = supplier_performance(f["branch_ids"], f["date_from"], f["date_to"])
    rep = Report("Supplier Performance", meta(f, f["branches"][0]["currency"]))
    rep.column(t("Vendor"), width=2.6).column(t("Orders"), "qty", 0.6).column(t("Receipts"), "qty", 0.7) \
        .column(t("On time"), "text", 0.7).column(t("Fill rate"), "text", 0.7).column(t("Lead days"), "text", 0.7) \
        .column(t("Returns"), "text", 0.7).column(t("Price vs PO"), "text", 0.8).column(t("Score"), "text", 0.6)

    def pct(v, sign=False):
        return "—" if v is None else (f"{v:+.1f}%" if sign else f"{v:.0f}%")
    for r in rows:
        rep.row([nm(r["vendor"]) if r["vendor"] else "—", r["orders"], r["receipts"], pct(r["on_time"]), pct(r["fill"]),
                 "—" if r["lead"] is None else f"{r['lead']:.1f}", pct(r["returns"]), pct(r["price"], True),
                 "—" if r["score"] is None else f"{r['score']:.0f}"])
    out = respond(rep, "Supplier_Performance")
    if out:
        return out
    return render_template("insights/suppliers.html", rows=rows, f=f)
