"""Budgets: expected income and expenses per account and month, compared with the actual ledger.

Amounts are kept in their natural sign (income and expenses both positive), in the branch currency. A budget can be
for a whole branch or for one cost centre of it; the actuals are read the same way (year-end closing entries left
out, as in the profit and loss). Variance is actual minus budget; it is favourable when income is above budget or
expenses are below it.
"""
from collections import defaultdict

from .db import q, x
from .i18n import t
from .ledger import PL_ROOTS, ROOT, AccountingError, accounts_by_id
from .recurring import add_months

SECTIONS = [  # (account kinds, title, income-like)
    (("income",), "Revenue", True),
    (("expense_direct_cost",), "Cost of Revenue", False),
    (("income_other",), "Other Income", True),
    (("expense", "expense_depreciation"), "Operating Expenses", False),
]


def periods(fy_start):
    """The twelve months of the financial year as YYYY-MM."""
    from datetime import date
    d = date.fromisoformat(fy_start)
    return [add_months(d, i).strftime("%Y-%m") for i in range(12)]


def month_end(period):
    import calendar
    y, m = int(period[:4]), int(period[5:7])
    return f"{period}-{calendar.monthrange(y, m)[1]:02d}"


def pl_accounts():
    return [a for a in q("SELECT * FROM accounts WHERE active = 1 AND is_group = 0 ORDER BY code") if ROOT[a["kind"]] in PL_ROOTS]


def is_income(kind):
    return ROOT[kind] == "income"


def grid(budget_id):
    """{account_id: {period: amount}}"""
    out = defaultdict(dict)
    for r in q("SELECT account_id, period, amount FROM budget_lines WHERE budget_id = ?", (budget_id,)):
        out[r["account_id"]][r["period"]] = r["amount"]
    return out


def save_grid(budget_id, data):
    """data: {account_id: {period: amount}}; zero amounts are not stored."""
    x("DELETE FROM budget_lines WHERE budget_id = ?", (budget_id,))
    for acc, per in data.items():
        for period, amt in per.items():
            if amt:
                x("INSERT INTO budget_lines (budget_id, account_id, period, amount) VALUES (?,?,?,?)",
                  (budget_id, acc, period, amt))


def actuals(branch_id, cost_centre_id, date_from, date_to):
    """{account_id: {period: natural amount}} from posted entries."""
    sql = ("SELECT ml.account_id, a.kind, substr(m.date, 1, 7) AS period, SUM(ml.debit - ml.credit) AS bal "
           "FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id "
           "WHERE m.state = 'posted' AND m.move_type != 'closing' AND ml.branch_id = ? AND m.date BETWEEN ? AND ?")
    args = [branch_id, date_from, date_to]
    if cost_centre_id:
        sql += " AND ml.cost_centre_id = ?"
        args.append(cost_centre_id)
    out = defaultdict(dict)
    for r in q(sql + " GROUP BY ml.account_id, a.kind, period", args):
        if ROOT[r["kind"]] in PL_ROOTS and r["bal"]:
            out[r["account_id"]][r["period"]] = -r["bal"] if is_income(r["kind"]) else r["bal"]
    return out


def from_actuals(branch_id, cost_centre_id, fy_start, pct=0.0, decimals=2):
    """A grid copied from the actuals of the year before fy_start, raised (or lowered) by pct %."""
    from datetime import date
    from .money import round_cur
    prev = add_months(date.fromisoformat(fy_start), -12).isoformat()
    prev_periods, new_periods = periods(prev), periods(fy_start)
    act = actuals(branch_id, cost_centre_id, prev, month_end(prev_periods[-1]))
    out = {}
    for acc, per in act.items():
        out[acc] = {new: round_cur(int(round(per.get(old, 0) * (1 + pct / 100))), decimals)
                    for old, new in zip(prev_periods, new_periods) if per.get(old)}
    return out


def compare(budget, upto, scope="ytd"):
    """Budget against actual. scope: 'month' (the month `upto`), 'ytd' (year start to `upto`) or 'year'.
    Returns rows like the profit and loss: sections, accounts, subtotals, gross profit and net profit."""
    pers = periods(budget["fy_start"])
    if upto not in pers:
        raise AccountingError(t("Choose a month of the budget's financial year."))
    chosen = [upto] if scope == "month" else pers if scope == "year" else pers[:pers.index(upto) + 1]
    accs = accounts_by_id()
    bud, act = grid(budget["id"]), actuals(budget["branch_id"], budget["cost_centre_id"], chosen[0] + "-01",
                                           month_end(chosen[-1]))
    rows, totals = [], {}

    def row(label, b, a, income, kind, account=None):
        var = a - b
        fav = var >= 0 if income else var <= 0
        r = {"label": label, "budget": b, "actual": a, "variance": var, "favourable": fav, "kind": kind,
             "account": account, "pct": (a * 100 / b) if b else None}
        rows.append(r)
        return r

    for kinds, title, income in SECTIONS:
        rows.append({"label": t(title), "kind": "section"})
        sb = sa = 0
        for acc_id in sorted(set(bud) | set(act), key=lambda i: accs[i]["code"]):
            acc = accs[acc_id]
            if acc["kind"] not in kinds:
                continue
            b = sum(bud.get(acc_id, {}).get(p, 0) for p in chosen)
            a = sum(act.get(acc_id, {}).get(p, 0) for p in chosen)
            if b or a:
                row(None, b, a, income, "account", acc)
                sb, sa = sb + b, sa + a
        totals[title] = row(t("Total") + " " + t(title), sb, sa, income, "subtotal")
        if title == "Cost of Revenue":
            rev, cogs = totals["Revenue"], totals["Cost of Revenue"]
            row(t("Gross Profit"), rev["budget"] - cogs["budget"], rev["actual"] - cogs["actual"], True, "total")
    gp = [r for r in rows if r.get("kind") == "total"][0]
    oi, ox = totals["Other Income"], totals["Operating Expenses"]
    row(t("Net Profit"), gp["budget"] + oi["budget"] - ox["budget"], gp["actual"] + oi["actual"] - ox["actual"],
        True, "grand")
    return {"rows": rows, "periods": chosen}
