"""Budgets: entry grid (accounts x months) and budget against actual."""
from datetime import date

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

from .. import check_branch, context_branch_ids, currency_decimals, login_required, write_required
from ..coa import max_depth, nest, parse_level
from ..budgets import compare, from_actuals, grid, is_income, month_end, periods, pl_accounts, save_grid
from ..costcentres import options as cost_centre_options, parse as parse_cost_centre
from ..db import commit, get_db, placeholders, q, x
from ..i18n import nm, t
from ..ledger import AccountingError, audit, fy_start, now
from ..money import fmt, to_int
from ..recurring import add_months
from .common import branch_map, default_branch, user_id

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


def get_budget(bid):
    b = q("SELECT bu.*, br.code AS bcode, br.currency, cc.code AS cc_code, cc.name_en AS cc_name_en, "
          "cc.name_ar AS cc_name_ar FROM budgets bu JOIN branches br ON br.id = bu.branch_id "
          "LEFT JOIN cost_centres cc ON cc.id = bu.cost_centre_id WHERE bu.id = ?", (bid,), one=True)
    if not b:
        abort(404)
    check_branch(b["branch_id"])
    return b


def fy_options():
    """Last, this and next financial year: [(start, label)]."""
    cur = date.fromisoformat(fy_start(date.today().isoformat()))
    out = []
    for n in (-12, 0, 12):
        s = add_months(cur, n)
        e = add_months(s, 11)
        label = str(s.year) if s.month == 1 else f"{s.strftime('%Y-%m')} – {e.strftime('%Y-%m')}"
        out.append((s.isoformat(), label))
    return out


def fy_label(start):
    s = date.fromisoformat(start)
    return str(s.year) if s.month == 1 else f"{s.strftime('%Y-%m')} – {add_months(s, 11).strftime('%Y-%m')}"


def totals(bid):
    """(income, expenses) of a budget for the whole year."""
    inc = exp = 0
    for r in q("SELECT a.kind, SUM(bl.amount) AS amt FROM budget_lines bl JOIN accounts a ON a.id = bl.account_id "
               "WHERE bl.budget_id = ? GROUP BY a.kind", (bid,)):
        if is_income(r["kind"]):
            inc += r["amt"]
        else:
            exp += r["amt"]
    return inc, exp


@bp.route("/")
@login_required
def index():
    ids = context_branch_ids()
    rows = []
    if ids:
        for b in q(f"SELECT bu.*, br.code AS bcode, br.currency, cc.code AS cc_code FROM budgets bu "
                   f"JOIN branches br ON br.id = bu.branch_id LEFT JOIN cost_centres cc ON cc.id = bu.cost_centre_id "
                   f"WHERE bu.branch_id IN ({placeholders(ids)}) ORDER BY bu.fy_start DESC, br.code, bu.name", ids):
            inc, exp = totals(b["id"])
            rows.append({"b": b, "income": inc, "expense": exp, "net": inc - exp, "fy": fy_label(b["fy_start"])})
    return render_template("budgets/list.html", rows=rows)


@bp.route("/new", methods=["GET", "POST"])
@write_required
def new():
    if request.method == "POST":
        f = request.form
        try:
            branch_id = check_branch(f["branch_id"])
            if not f.get("name", "").strip():
                raise AccountingError(f"{t('Name')}: {t('is required')}")
            fy = f["fy_start"]
            if fy not in dict(fy_options()):
                raise AccountingError(t("Choose a financial year."))
            cc = parse_cost_centre(f.get("cost_centre_id"))
            bid = x("INSERT INTO budgets (name, branch_id, cost_centre_id, fy_start, notes, created_by, created_at) "
                    "VALUES (?,?,?,?,?,?,?)", (f["name"].strip(), branch_id, cc, fy, f.get("notes"), user_id(), now()))
            if f.get("start") == "actuals":
                cur = q("SELECT currency FROM branches WHERE id = ?", (branch_id,), one=True)["currency"]
                pct = float(f.get("pct") or 0)
                save_grid(bid, from_actuals(branch_id, cc, fy, pct, currency_decimals(cur)))
            elif f.get("start") == "copy" and f.get("copy_id"):
                src = get_budget(int(f["copy_id"]))
                shift = {o: n for o, n in zip(periods(src["fy_start"]), periods(fy))}
                save_grid(bid, {a: {shift[p]: v for p, v in per.items()} for a, per in grid(src["id"]).items()})
            audit(user_id(), "create", "budget", bid, f["name"])
            commit()
            return redirect(url_for("budgets.edit", bid=bid))
        except (AccountingError, ValueError) as e:
            get_db().rollback()
            flash(str(e), "error")
    ids = context_branch_ids()
    others = q(f"SELECT bu.id, bu.name, bu.fy_start, br.code AS bcode FROM budgets bu JOIN branches br "
               f"ON br.id = bu.branch_id WHERE bu.branch_id IN ({placeholders(ids)}) ORDER BY bu.fy_start DESC",
               ids) if ids else []
    fys = fy_options()
    return render_template("budgets/new.html", branches=branch_map(), default_branch=default_branch(),
                           cost_centres=cost_centre_options(), fys=fys, this_fy=fys[1][0], others=others,
                           copy_id=request.args.get("copy", type=int), fy_label=fy_label)


@bp.route("/<int:bid>/edit", methods=["GET", "POST"])
@write_required
def edit(bid):
    b = get_budget(bid)
    pers = periods(b["fy_start"])
    accounts = pl_accounts()
    if request.method == "POST":
        f = request.form
        try:
            data = {}
            for a in accounts:
                per = {}
                for i, p in enumerate(pers):
                    v = f.get(f"a{a['id']}_{i}", "").strip()
                    if v:
                        per[p] = to_int(v)
                if per:
                    data[a["id"]] = per
            name = f.get("name", "").strip() or b["name"]
            x("UPDATE budgets SET name = ?, notes = ? WHERE id = ?", (name, f.get("notes"), bid))
            save_grid(bid, data)
            audit(user_id(), "save", "budget", bid, name)
            commit()
            flash(t("Saved."), "success")
            return redirect(url_for("budgets.view", bid=bid))
        except ValueError as e:
            get_db().rollback()
            flash(str(e), "error")
    g = grid(bid)
    used = set(g)
    return render_template("budgets/edit.html", b=b, pers=pers, accounts=accounts, grid=g, used=used,
                           dec=currency_decimals(b["currency"]), fy=fy_label(b["fy_start"]))


@bp.route("/<int:bid>")
@login_required
def view(bid):
    b = get_budget(bid)
    pers = periods(b["fy_start"])
    this_month = date.today().strftime("%Y-%m")
    upto = request.args.get("upto") or (this_month if this_month in pers else pers[-1] if this_month > pers[-1]
                                        else pers[0])
    scope = request.args.get("scope") if request.args.get("scope") in ("month", "ytd", "year") else "ytd"
    try:
        data = compare(b, upto, scope)
    except AccountingError as e:
        flash(str(e), "error")
        return redirect(url_for("budgets.view", bid=bid))
    level = parse_level(request.args.get("level"))
    data["rows"] = nest(data["rows"], ("budget", "actual", "variance"), level, finish=group_figures)
    cur = b["currency"]
    from ..exports import Report, respond
    scope_lbl = {"month": "Month", "ytd": "Year to date", "year": "Full year"}[scope]
    branch = q("SELECT code, name_en, name_ar FROM branches WHERE id = ?", (b["branch_id"],), one=True)
    rep = Report("Budget vs Actual", [f"{b['name']} · {fy_label(b['fy_start'])}", f"{t(scope_lbl)}: {data['periods'][0]} → {data['periods'][-1]}",
                                      f"{branch['code']} {nm(branch)}", f"{t('Amounts in')} {cur}"], currency_decimals(cur))
    rep.column(t("Account"), width=3).column(t("Budget"), "num").column(t("Actual"), "num") \
        .column(t("Variance"), "num").column(t("% of budget"), "text", 0.8)
    for r in data["rows"]:
        if r["kind"] == "section":
            rep.section(r["label"])
            continue
        lbl = f"{r['account']['code']}  {nm(r['account'])}" if r.get("account") else r["label"]
        rep.row([lbl, r["budget"], r["actual"], r["variance"], f"{r['pct']:.0f}%" if r.get("pct") is not None else ""],
                {"group": "group", "subtotal": "subtotal", "total": "total", "grand": "grand"}.get(r["kind"], "normal"),
                max((r.get("depth") or 1) - 1, 0))
    out = respond(rep, "Budget_vs_Actual")
    if out:
        return out
    return render_template("budgets/view.html", b=b, data=data, pers=pers, upto=upto, scope=scope, cur=cur,
                           fy=fy_label(b["fy_start"]), d_from=data["periods"][0] + "-01",
                           d_to=month_end(data["periods"][-1]), level=level, levels=max_depth())


def group_figures(r):
    """Favourable and % of budget on a group line of the chart of accounts."""
    r["favourable"] = r["variance"] >= 0 if is_income(r["group"]["kind"]) else r["variance"] <= 0
    r["pct"] = r["actual"] * 100 / r["budget"] if r["budget"] else None


@bp.route("/<int:bid>/delete", methods=["POST"])
@write_required
def delete(bid):
    b = get_budget(bid)
    x("DELETE FROM budgets WHERE id = ?", (bid,))
    audit(user_id(), "delete", "budget", bid, b["name"])
    commit()
    flash(t("Deleted."), "success")
    return redirect(url_for("budgets.index"))


@bp.route("/report")
@login_required
def report():
    """Budget vs Actual from the menu: the latest budget of the branches in view."""
    ids = context_branch_ids()
    b = q(f"SELECT id FROM budgets WHERE branch_id IN ({placeholders(ids)}) ORDER BY fy_start DESC, id DESC LIMIT 1",
          ids, one=True) if ids else None
    if not b:
        flash(t("Create a budget first."), "error")
        return redirect(url_for("budgets.index"))
    return redirect(url_for("budgets.view", bid=b["id"]))
