"""Cost centres: one analysis dimension (department, project, site, vehicle...) on income and expense lines.

A cost centre is entered on invoice, bill and journal lines and travels to the ledger line it creates. Cost of
goods sold on a delivery takes the cost centre of the invoice line (or the sales order) it belongs to; depreciation
and prepaid/accrual entries take the one set on the asset or schedule. Lines without one are reported as
"No cost centre", so the report always adds up to the ordinary profit and loss.
"""
from collections import defaultdict

from .db import placeholders, q
from .i18n import nm, t
from .ledger import PL_ROOTS, ROOT, AccountingError, accounts_by_id, avg_rate, branch_rows, company, \
    report_currency
from .money import translate

NONE = 0  # report column / filter value for lines without a cost centre


def options(include_id=None):
    """Active cost centres (plus an inactive one already in use on the record being edited)."""
    return q("SELECT * FROM cost_centres WHERE active = 1 OR id = ? ORDER BY code", (include_id or -1,))


def in_use():
    return bool(q("SELECT 1 FROM cost_centres WHERE active = 1 LIMIT 1", one=True))


def parse(value):
    if not value:
        return None
    cc = q("SELECT id FROM cost_centres WHERE id = ?", (int(value),), one=True)
    if not cc:
        raise AccountingError(t("Unknown cost centre."))
    return cc["id"]


def is_pl(account_id):
    acc = q("SELECT kind FROM accounts WHERE id = ?", (account_id,), one=True)
    return bool(acc) and ROOT[acc["kind"]] in PL_ROOTS


def needs_cost_centre(account_id):
    """Lines a cost centre belongs on: income and expense, or expenses only (company setting)."""
    acc = q("SELECT kind FROM accounts WHERE id = ?", (account_id,), one=True)
    if not acc:
        return False
    if (company()["cost_centre_scope"] or "pl") == "expense":
        return ROOT[acc["kind"]] == "expense"
    return ROOT[acc["kind"]] in PL_ROOTS


def check_lines(lines):
    """When the company requires it, every income and expense line (or every expense line) needs a cost centre."""
    if not company()["cost_centre_required"] or not in_use():
        return
    for ln in lines:
        if ln.get("account_id") and not ln.get("cost_centre_id") and needs_cost_centre(ln["account_id"]):
            acc = q("SELECT code, name_en, name_ar FROM accounts WHERE id = ?", (ln["account_id"],), one=True)
            raise AccountingError(f"{t('Choose a cost centre for the income and expense lines')}: "
                                  f"{acc['code']} {nm(acc)}")


def profit_loss_by_cost_centre(branch_ids, date_from, date_to, force_group=False):
    """Profit and loss with one column per cost centre (and one for lines without). Year-end closing entries are
    left out, as in the ordinary profit and loss."""
    branches = branch_rows(branch_ids)
    currency, translated = report_currency(branches, force_group)
    accs = accounts_by_id()
    rows = q(f"""
        SELECT ml.branch_id, ml.account_id, COALESCE(ml.cost_centre_id, 0) AS cc, SUM(ml.credit - ml.debit) AS amount
        FROM move_lines ml JOIN moves m ON m.id = ml.move_id
        WHERE m.state = 'posted' AND m.move_type != 'closing' AND m.date BETWEEN ? AND ?
          AND ml.branch_id IN ({placeholders(branch_ids)})
        GROUP BY ml.branch_id, ml.account_id, cc""", [date_from, date_to] + list(branch_ids))
    rates, notes = {}, []
    for b in branches:
        rates[b["id"]] = avg_rate(b["currency"], date_from, date_to) if translated else 1.0
        if translated and b["currency"] != currency:
            notes.append(f"{b['code']}: 1 {b['currency']} = {rates[b['id']]:.6f} {currency} ({t('average rate')})")
    data = defaultdict(lambda: defaultdict(int))  # account -> cost centre -> income-positive amount
    for r in rows:
        if ROOT[accs[r["account_id"]]["kind"]] in PL_ROOTS and r["amount"]:
            data[r["account_id"]][r["cc"]] += translate(r["amount"], rates[r["branch_id"]])
    used = {cc for per in data.values() for cc, v in per.items() if v}
    cols = [dict(c) for c in q("SELECT * FROM cost_centres ORDER BY code") if c["active"] or c["id"] in used]
    if NONE in used or not cols:
        cols.append({"id": NONE, "code": "—", "name_en": "No cost centre", "name_ar": None, "none": True})
    ids = [c["id"] for c in cols]

    out = []

    def line(label, vals, kind, account=None):
        r = {"label": label, "vals": vals, "total": sum(vals), "kind": kind, "account": account}
        out.append(r)
        return r

    def block(kinds, title, sign):
        out.append({"label": title, "kind": "section"})
        members = []
        for acc_id in sorted(data, key=lambda a: accs[a]["code"]):
            acc = accs[acc_id]
            if acc["kind"] in kinds and any(data[acc_id].values()):
                members.append(line(None, [data[acc_id].get(i, 0) * sign for i in ids], "account", acc))
        return line(t("Total") + " " + title, [sum(m["vals"][k] for m in members) for k in range(len(ids))],
                    "subtotal")

    rev = block(("income",), t("Revenue"), 1)
    cogs = block(("expense_direct_cost",), t("Cost of Revenue"), -1)
    gp = line(t("Gross Profit"), [a - b for a, b in zip(rev["vals"], cogs["vals"])], "total")
    other = block(("income_other",), t("Other Income"), 1)
    opex = block(("expense", "expense_depreciation"), t("Operating Expenses"), -1)
    line(t("Net Profit"), [g + o - e for g, o, e in zip(gp["vals"], other["vals"], opex["vals"])], "grand")
    return {"cols": cols, "rows": out, "currency": currency, "translated": translated, "notes": notes,
            "branches": branches}
