from collections import defaultdict
from datetime import date

from flask import Blueprint, redirect, render_template, url_for

from .. import can_approve, context_branch_ids, has, login_required
from ..db import placeholders, q
from ..ledger import ROOT, AccountingError, branch_rows, fy_start, invoice_residuals, payment_state, rate_on, \
    report_currency
from ..money import translate

bp = Blueprint("dashboard", __name__)


def last_months(n=12):
    d = date.today().replace(day=1)
    out = []
    y, m = d.year, d.month
    for _ in range(n):
        out.append(f"{y:04d}-{m:02d}")
        m -= 1
        if m == 0:
            y, m = y - 1, 12
    return list(reversed(out))


ACTION_ICONS = {
    "Material requests waiting approval": ("clipboard", "amber"), "Requisitions waiting approval": ("list", "amber"),
    "Purchase orders waiting approval": ("cart", "indigo"), "Open RFQs": ("mail", "blue"),
    "Receipts to process": ("down", "green"), "Deliveries to process": ("truck", "blue"),
    "Products below reorder level": ("box", "red"), "Batches expired or expiring within 30 days": ("clock", "red"), "Vendor documents expiring within 30 days": ("shield", "red"),
    "CRM activities due today or overdue": ("check", "indigo"), "POS sessions still open from previous days": ("monitor", "amber"),
    "Bank statement lines to reconcile": ("bank", "blue"), "Month-end entries to post": ("calendar", "green"),
}


def greeting():
    from datetime import datetime
    h = datetime.now().hour
    return "Good morning" if h < 12 else "Good afternoon" if h < 18 else "Good evening"


def shift_year(d, years=-1):
    y, rest = int(d[:4]) + years, d[4:]
    return f"{y:04d}-02-28" if rest == "-02-29" else f"{y:04d}{rest}"


def nice_ceiling(v):
    """Round a chart maximum up to 1, 2, 2.5 or 5 times a power of ten so the scale reads well."""
    mag = 10 ** (len(str(int(v))) - 1)
    for step in (1, 2, 2.5, 5, 10):
        if v <= step * mag:
            return int(step * mag)
    return int(10 * mag)


def cash_accounts(ids):
    return q(f"SELECT COUNT(*) FROM journals WHERE type IN ('bank', 'cash') AND active = 1 "
             f"AND branch_id IN ({placeholders(ids)})", ids, one=True)[0]


@bp.route("/dashboard")
@login_required
def index():
    ids = context_branch_ids()
    branches = branch_rows(ids)
    if not branches:
        return render_template("dashboard.html", empty=True)
    currency, translated = report_currency(branches)
    today = date.today().isoformat()
    fys = fy_start(today)
    error = None
    try:
        rates = {b["id"]: rate_on(b["currency"], today) if translated else 1.0 for b in branches}
    except AccountingError as e:
        error, rates = str(e), {b["id"]: 1.0 for b in branches}
    kinds = {a["id"]: a["kind"] for a in q("SELECT id, kind FROM accounts")}
    ph = placeholders(ids)
    stats = {b["id"]: defaultdict(int) for b in branches}
    for r in q(f"SELECT ml.branch_id, ml.account_id, "
               f"SUM(ml.debit - ml.credit) AS bal, "
               f"SUM(CASE WHEN m.date >= ? AND m.move_type != 'closing' THEN ml.debit - ml.credit ELSE 0 END) AS ytd "
               f"FROM move_lines ml JOIN moves m ON m.id = ml.move_id "
               f"WHERE m.state = 'posted' AND m.date <= ? AND ml.branch_id IN ({ph}) "
               f"GROUP BY ml.branch_id, ml.account_id", [fys, today] + ids):
        k = kinds[r["account_id"]]
        s = stats[r["branch_id"]]
        rate = rates[r["branch_id"]]
        if ROOT[k] == "income":
            s["revenue"] += translate(-r["ytd"], rate)
        elif ROOT[k] == "expense":
            s["expenses"] += translate(r["ytd"], rate)
        elif k == "asset_cash":
            s["cash"] += translate(r["bal"], rate)
        elif k == "asset_receivable":
            s["receivable"] += translate(r["bal"], rate)
        elif k == "liability_payable":
            s["payable"] += translate(-r["bal"], rate)
    # same period last year, for the change shown on each figure
    last_year = {"revenue": 0, "expenses": 0}
    for r in q(f"SELECT ml.branch_id, a.kind, SUM(ml.debit - ml.credit) AS bal FROM move_lines ml "
               f"JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id "
               f"WHERE m.state = 'posted' AND m.date BETWEEN ? AND ? AND m.move_type != 'closing' "
               f"AND ml.branch_id IN ({ph}) GROUP BY ml.branch_id, a.kind", [shift_year(fys), shift_year(today)] + ids):
        if ROOT[r["kind"]] == "income":
            last_year["revenue"] += translate(-r["bal"], rates[r["branch_id"]])
        elif ROOT[r["kind"]] == "expense":
            last_year["expenses"] += translate(r["bal"], rates[r["branch_id"]])
    for s in stats.values():
        s["profit"] = s["revenue"] - s["expenses"]
    totals = defaultdict(int)
    for s in stats.values():
        for k, v in s.items():
            totals[k] += v

    months = last_months()
    series = {m: [0, 0] for m in months}
    for r in q(f"SELECT ml.branch_id, substr(m.date, 1, 7) AS ym, a.kind, SUM(ml.debit - ml.credit) AS bal "
               f"FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id "
               f"WHERE m.state = 'posted' AND m.date >= ? AND m.move_type != 'closing' AND ml.branch_id IN ({ph}) "
               f"GROUP BY ml.branch_id, ym, a.kind", [months[0] + "-01"] + ids):
        if r["ym"] in series and ROOT[r["kind"]] in ("income", "expense"):
            v = translate(r["bal"], rates[r["branch_id"]])
            if ROOT[r["kind"]] == "income":
                series[r["ym"]][0] -= v
            else:
                series[r["ym"]][1] += v
    peak = nice_ceiling(max([max(v) for v in series.values()] + [1]))

    overdue = q(f"SELECT i.id, i.branch_id, i.amount_total FROM invoices i WHERE i.state = 'posted' "
                f"AND i.kind = 'out_invoice' AND COALESCE(i.due_date, i.date) < ? AND i.branch_id IN ({ph})",
                [today] + ids)
    res = invoice_residuals([o["id"] for o in overdue], base=True)
    overdue_amt = sum(translate(res.get(o["id"], 0), rates[o["branch_id"]]) for o in overdue)
    overdue_n = sum(1 for o in overdue if res.get(o["id"], 0) > 0)

    recent = q(f"SELECT i.*, p.name_en AS pname_en, p.name_ar AS pname_ar FROM invoices i "
               f"JOIN partners p ON p.id = i.partner_id WHERE i.kind IN ('out_invoice', 'out_refund') AND i.branch_id IN ({ph}) "
               f"ORDER BY i.date DESC, i.id DESC LIMIT 6", ids)
    recent_res = invoice_residuals([i["id"] for i in recent if i["state"] == "posted"])
    recent = [dict(i, pay=payment_state(i, recent_res.get(i["id"], 0)), overdue=i["state"] == "posted" and
                   recent_res.get(i["id"], 0) > 0 and (i["due_date"] or i["date"]) < today) for i in recent]
    open_bills = q(f"SELECT id FROM invoices WHERE state = 'posted' AND kind = 'in_invoice' AND branch_id IN ({ph})", ids)
    bills_res = invoice_residuals([b["id"] for b in open_bills], base=True)
    open_bills_n = sum(1 for v in bills_res.values() if v > 0)
    open_inv = q(f"SELECT id FROM invoices WHERE state = 'posted' AND kind = 'out_invoice' AND branch_id IN ({ph})", ids)
    open_inv_n = sum(1 for v in invoice_residuals([i["id"] for i in open_inv]).values() if v > 0)
    drafts = q(f"SELECT COUNT(*) FROM moves WHERE state = 'draft' AND branch_id IN ({ph})", ids, one=True)[0]
    drafts += q(f"SELECT COUNT(*) FROM invoices WHERE state = 'draft' AND branch_id IN ({ph})", ids, one=True)[0]
    actions = []
    if has("procurement"):
        for tbl, ep, label in (("material_requests", "procurement.mr_list", "Material requests waiting approval"),
                               ("purchase_requisitions", "procurement.pr_list", "Requisitions waiting approval")):
            n = q(f"SELECT COUNT(*) FROM {tbl} WHERE state = 'submitted' AND branch_id IN ({ph})", ids, one=True)[0]
            if n and can_approve():
                actions.append((label, n, url_for(ep, todo="approve")))
        n = q(f"SELECT COUNT(*) FROM orders WHERE kind = 'purchase' AND state = 'to_approve' AND branch_id IN ({ph})",
              ids, one=True)[0]
        if n and can_approve():
            actions.append(("Purchase orders waiting approval", n, url_for("orders.index", kind="purchase", state="to_approve")))
        n = q(f"SELECT COUNT(*) FROM rfqs WHERE state IN ('draft', 'sent') AND branch_id IN ({ph})", ids, one=True)[0]
        if n:
            actions.append(("Open RFQs", n, url_for("procurement.rfq_list", state="sent")))
    if has("inventory"):
        for kind, label in (("in", "Receipts to process"), ("out", "Deliveries to process")):
            n = q(f"SELECT COUNT(*) FROM pickings WHERE kind = ? AND state = 'ready' AND branch_id IN ({ph})",
                  [kind] + ids, one=True)[0]
            if n:
                actions.append((label, n, url_for("inventory.pickings", kind=kind, state="ready")))
        n = q(f"SELECT COUNT(*) FROM products p WHERE p.type = 'stock' AND p.active = 1 AND p.min_qty > 0 AND "
              f"COALESCE((SELECT SUM(sq.qty) FROM stock_quants sq JOIN warehouses w ON w.id = sq.warehouse_id "
              f"WHERE sq.product_id = p.id AND w.branch_id IN ({ph})), 0) < p.min_qty", ids, one=True)[0]
        if n:
            actions.append(("Products below reorder level", n, url_for("inventory.reorder")))
        n = q(f"SELECT COUNT(DISTINCT l.id) FROM lots l JOIN lot_quants lq ON lq.lot_id = l.id JOIN warehouses w "
              f"ON w.id = lq.warehouse_id WHERE lq.qty > 0.000001 AND l.expiry_date <= date(?, '+30 days') "
              f"AND w.branch_id IN ({ph})", [today] + ids, one=True)[0]
        if n:
            actions.append(("Batches expired or expiring within 30 days", n, url_for("inventory.batches", status="30")))
    if has("vendors"):
        n = q("SELECT COUNT(*) FROM vendor_documents WHERE expiry_date IS NOT NULL AND expiry_date <= date('now', '+30 days')",
              one=True)[0]
        if n:
            actions.append(("Vendor documents expiring within 30 days", n, url_for("vendors.expiring")))
    if has("crm"):
        from flask import g
        n = q(f"SELECT COUNT(*) FROM crm_activities a JOIN leads l ON l.id = a.lead_id WHERE a.done = 0 AND a.user_id = ? "
              f"AND a.due_date <= ? AND l.branch_id IN ({ph})", [g.user["id"], today] + ids, one=True)[0]
        if n:
            actions.append(("CRM activities due today or overdue", n, url_for("crm.activities")))
    if has("pos"):
        n = q(f"SELECT COUNT(*) FROM pos_sessions WHERE state = 'open' AND opened_at < ? AND branch_id IN ({ph})",
              [today] + ids, one=True)[0]
        if n:
            actions.append(("POS sessions still open from previous days", n, url_for("pos.index")))
    if has("bank"):
        n = q(f"SELECT COUNT(*) FROM bank_statement_lines l JOIN bank_statements s ON s.id = l.statement_id "
              f"WHERE l.state = 'unmatched' AND s.branch_id IN ({ph})", ids, one=True)[0]
        if n:
            actions.append(("Bank statement lines to reconcile", n, url_for("bank.index")))
    if has("assets") or has("prepaid"):
        from .closing import last_month_end
        from ..schedules import due_depreciation, due_recognitions
        n = (len(due_depreciation(last_month_end(), ids)) if has("assets") else 0) + \
            (sum(len(x) for x in due_recognitions(last_month_end(), ids)) if has("prepaid") else 0)
        if n:
            actions.append(("Month-end entries to post", n, url_for("closing.index")))
    return render_template("dashboard.html", empty=False, actions=actions, branches=branches, stats=stats, totals=totals,
                           currency=currency, translated=translated, series=series, peak=peak, months=months,
                           overdue_amt=overdue_amt, overdue_n=overdue_n, recent=recent, drafts=drafts,
                           fys=fys, error=error, last_year=last_year, open_bills_n=open_bills_n,
                           open_inv_n=open_inv_n, cash_accounts=cash_accounts(ids), action_icons=ACTION_ICONS,
                           fy_month=fys[:7], greeting=greeting(), scale=[peak * i // 4 for i in range(4, -1, -1)])


@bp.route("/home")
def home():
    return redirect(url_for("home.index"))
