"""Projects (licensed module "projects").

A project is a job for a customer, run from one branch, with a project manager and a team. Every project gets its
own cost centre, so whatever is posted for it (bills, stock issued on its material requests, petty cash, payroll
journals...) is its actual cost, and the invoices raised for it are its revenue.

Budget: lines by expense account; a line on a group account covers every account under it.
  Committed = confirmed purchase orders of the project not yet billed (value before VAT).
  Actual    = posted expense on the project's cost centre (debit - credit).
  Remaining = Budget - Committed - Actual.
Budget control (warn / block) runs when a material request is submitted and when a purchase order is confirmed.

Progress billing: each milestone is billed with a customer invoice for its amount. When the contract holds back a
retention, the invoice carries a deduction line:
  Dr Accounts Receivable (net)  Dr Retention Receivable (retention)  Cr Revenue (milestone)  Cr Output VAT (on the
  full milestone amount). Releasing the retention later invoices Dr Accounts Receivable, Cr Retention Receivable.
"""
from collections import defaultdict

from .db import placeholders, q, x
from .i18n import nm, t
from .ledger import ROOT, AccountingError, audit, company, compute_invoice_line, now

STATES = ("draft", "active", "on_hold", "closed")
CONTROLS = ("none", "warn", "block")


def enabled():
    from . import has
    return has("projects")


def get(project_id):
    return q("SELECT * FROM projects WHERE id = ?", (project_id,), one=True) if project_id else None


def options(include_id=None):
    """Projects new documents can be raised for (plus the one already on the record)."""
    if not enabled():
        return []
    return q("SELECT p.*, b.code AS bcode FROM projects p JOIN branches b ON b.id = p.branch_id "
             "WHERE p.state = 'active' OR p.id = ? ORDER BY p.code", (include_id or -1,))


def parse(value, branch_id=None, current=None):
    """Project id from a form (None when the module is not licensed or nothing was chosen)."""
    if not enabled() or not value:
        return None
    p = get(int(value))
    if not p:
        raise AccountingError(t("Unknown project."))
    if p["state"] != "active" and p["id"] != current:
        raise AccountingError(f"{p['code']}: {t('the project is not active.')}")
    if branch_id is not None and p["branch_id"] != int(branch_id):
        raise AccountingError(f"{p['code']}: {t('the project belongs to another branch.')}")
    return p["id"]


def cost_centre(project_id):
    p = get(project_id)
    return p["cost_centre_id"] if p else None


def ensure_cost_centre(project_id):
    """The project's own cost centre, code = project code."""
    p = get(project_id)
    if p["cost_centre_id"]:
        x("UPDATE cost_centres SET name_en = ?, name_ar = ?, active = ? WHERE id = ?",
          (p["name_en"], p["name_ar"], 0 if p["state"] == "closed" else 1, p["cost_centre_id"]))
        return p["cost_centre_id"]
    cc = q("SELECT id FROM cost_centres WHERE code = ?", (p["code"],), one=True)
    if cc:
        if q("SELECT 1 FROM projects WHERE cost_centre_id = ? AND id != ?", (cc["id"], project_id), one=True):
            raise AccountingError(f"{t('Cost centre')} {p['code']} {t('already belongs to another project.')}")
        cc_id = cc["id"]
    else:
        cc_id = x("INSERT INTO cost_centres (code, name_en, name_ar, active) VALUES (?,?,?,1)",
                  (p["code"], p["name_en"], p["name_ar"]))
    x("UPDATE projects SET cost_centre_id = ? WHERE id = ?", (cc_id, project_id))
    return cc_id


def save_project(project_id, vals, members, budget, milestones, user_id=None):
    """vals: header columns; members: [(user_id, role)]; budget: [(account_id, description, amount)];
    milestones: [(id or None, name, amount, due_date)]."""
    if not (vals.get("code") or "").strip() or not (vals.get("name_en") or "").strip():
        raise AccountingError(t("Code and name are required."))
    if vals.get("state") not in STATES:
        vals["state"] = "active"
    if vals.get("budget_control") not in CONTROLS:
        vals["budget_control"] = "warn"
    if not 0 <= (vals.get("retention_pct") or 0) < 100:
        raise AccountingError(t("Retention must be between 0 and 100%."))
    dup = q("SELECT id FROM projects WHERE code = ? AND id != ?", (vals["code"], project_id or 0), one=True)
    if dup:
        raise AccountingError(t("Another project has this code."))
    for acc_id, _, _ in budget:
        acc = q("SELECT kind FROM accounts WHERE id = ?", (acc_id,), one=True)
        if not acc or ROOT[acc["kind"]] != "expense":
            raise AccountingError(t("Budget lines must be on expense accounts."))
    cols = list(vals)
    if project_id:
        old = get(project_id)
        if old["branch_id"] != vals["branch_id"] and _has_documents(project_id):
            raise AccountingError(t("The project already has documents; its branch cannot change."))
        x(f"UPDATE projects SET {', '.join(c + ' = ?' for c in cols)} WHERE id = ?", [vals[c] for c in cols] + [project_id])
    else:
        vals = {**vals, "created_by": user_id, "created_at": now()}
        cols = list(vals)
        project_id = x(f"INSERT INTO projects ({', '.join(cols)}) VALUES ({', '.join('?' * len(cols))})",
                       [vals[c] for c in cols])
    x("DELETE FROM project_members WHERE project_id = ?", (project_id,))
    for uid, role in dict(members).items():
        x("INSERT INTO project_members (project_id, user_id, role) VALUES (?,?,?)", (project_id, uid, role))
    x("DELETE FROM project_budget WHERE project_id = ?", (project_id,))
    for acc_id, desc, amount in budget:
        x("INSERT INTO project_budget (project_id, account_id, description, amount) VALUES (?,?,?,?)",
          (project_id, acc_id, desc, amount))
    keep = set()
    for seq, (mid, name, amount, due) in enumerate(milestones):
        old = q("SELECT * FROM project_milestones WHERE id = ? AND project_id = ?", (mid, project_id), one=True) \
            if mid else None
        if old and milestone_billed(old):
            keep.add(old["id"])
            x("UPDATE project_milestones SET sequence = ?, due_date = ? WHERE id = ?", (seq, due, old["id"]))
            continue
        if old:
            x("UPDATE project_milestones SET sequence = ?, name = ?, amount = ?, due_date = ? WHERE id = ?",
              (seq, name, amount, due, old["id"]))
            keep.add(old["id"])
        else:
            keep.add(x("INSERT INTO project_milestones (project_id, sequence, name, amount, due_date) VALUES (?,?,?,?,?)",
                       (project_id, seq, name, amount, due)))
    for m in q("SELECT * FROM project_milestones WHERE project_id = ?", (project_id,)):
        if m["id"] not in keep:
            if milestone_billed(m):
                raise AccountingError(f"{m['name']}: {t('this milestone is invoiced and cannot be removed.')}")
            x("DELETE FROM project_milestones WHERE id = ?", (m["id"],))
    p = get(project_id)
    if p["contract_value"] and sum(m["amount"] for m in q("SELECT amount FROM project_milestones WHERE project_id = ?",
                                                          (project_id,))) > p["contract_value"]:
        raise AccountingError(t("The milestones add up to more than the contract value."))
    ensure_cost_centre(project_id)
    audit(user_id, "save", "project", project_id, vals["code"])
    return project_id


def _has_documents(project_id):
    return any(q(f"SELECT 1 FROM {tbl} WHERE project_id = ? LIMIT 1", (project_id,), one=True)
               for tbl in ("material_requests", "purchase_requisitions", "orders", "invoices"))


def members(project_id):
    return q("SELECT pm.*, u.full_name, u.username FROM project_members pm JOIN users u ON u.id = pm.user_id "
             "WHERE pm.project_id = ? ORDER BY u.full_name", (project_id,))


# ---------------------------------------------------------------- budget, committed, actual

def product_cost_account(prod):
    """The expense account a product's cost reaches the project on (stock: when issued; others: on the bill)."""
    from .stock import cogs_account
    if not prod:
        return None
    if prod["type"] == "stock":
        return cogs_account(prod)
    return prod["expense_account_id"] or q("SELECT default_account_id FROM journals WHERE type = 'purchase' "
                                           "ORDER BY id LIMIT 1", one=True)[0]


def _ancestors(account_id, accs):
    while account_id:
        yield account_id
        account_id = accs[account_id]["parent_id"] if account_id in accs else None


def _budget_key(account_id, budget_accounts, accs):
    """The budget line an account's cost falls under: the account itself or its nearest group with a budget."""
    for a in _ancestors(account_id, accs):
        if a in budget_accounts:
            return a
    return None


def actual_by_account(project_id, date_to=None):
    cc = cost_centre(project_id)
    if not cc:
        return {}
    rows = q("SELECT ml.account_id, a.kind, SUM(ml.debit - ml.credit) AS amt 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.cost_centre_id = ?"
             + (" AND m.date <= ?" if date_to else "") + " GROUP BY ml.account_id",
             [cc] + ([date_to] if date_to else []))
    return {r["account_id"]: r["amt"] for r in rows if ROOT[r["kind"]] == "expense" and r["amt"]}


def revenue(project_id):
    cc = cost_centre(project_id)
    if not cc:
        return 0
    rows = q("SELECT a.kind, SUM(ml.credit - ml.debit) AS amt 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.cost_centre_id = ? GROUP BY a.kind", (cc,))
    return sum(r["amt"] for r in rows if ROOT[r["kind"]] == "income")


def committed_by_account(project_id, exclude_order=None):
    """Confirmed purchase orders of the project, less what has been billed (before VAT)."""
    from .stock import product
    out = defaultdict(int)
    for ln in q("SELECT ol.* FROM order_lines ol JOIN orders o ON o.id = ol.order_id WHERE o.project_id = ? "
                "AND o.kind = 'purchase' AND o.state = 'confirmed' AND o.id != ?", (project_id, exclude_order or 0)):
        billed = q("SELECT COALESCE(SUM(CASE WHEN i.kind = 'in_invoice' THEN il.subtotal ELSE -il.subtotal END), 0) "
                   "FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id WHERE il.order_line_id = ? "
                   "AND i.state = 'posted'", (ln["id"],), one=True)[0]
        rest = ln["subtotal"] - billed
        if rest > 0:
            acc = product_cost_account(product(ln["product_id"]) if ln["product_id"] else None)
            out[acc] += rest
    return dict(out)


def budget_report(project_id, extra=None):
    """Rows per budget line: budget, committed, actual, remaining. extra = {account: amount} of a document being
    checked (shown as 'this document')."""
    accs = {r["id"]: r for r in q("SELECT * FROM accounts")}
    lines = q("SELECT pb.*, a.code, a.name_en, a.name_ar FROM project_budget pb JOIN accounts a ON a.id = pb.account_id "
              "WHERE pb.project_id = ? ORDER BY a.code, pb.id", (project_id,))
    rows, by_acc = [], {}
    for ln in lines:
        if ln["account_id"] in by_acc:
            r = by_acc[ln["account_id"]]
            r["budget"] += ln["amount"]
            if ln["description"]:
                r["description"] = ", ".join(filter(None, [r["description"], ln["description"]]))
            continue
        r = {"account_id": ln["account_id"], "code": ln["code"], "name_en": ln["name_en"], "name_ar": ln["name_ar"],
             "description": ln["description"], "budget": ln["amount"], "committed": 0, "actual": 0, "extra": 0}
        by_acc[ln["account_id"]] = r
        rows.append(r)
    unbudgeted = {"account_id": None, "code": "", "name_en": "Not in the budget", "name_ar": "خارج الموازنة",
                  "description": None, "budget": 0, "committed": 0, "actual": 0, "extra": 0, "unbudgeted": True}
    for field, data in (("committed", committed_by_account(project_id)), ("actual", actual_by_account(project_id)),
                        ("extra", extra or {})):
        for acc_id, amt in data.items():
            key = _budget_key(acc_id, by_acc, accs) if acc_id else None
            (by_acc[key] if key else unbudgeted)[field] += amt
    if any(unbudgeted[f] for f in ("committed", "actual", "extra")):
        rows.append(unbudgeted)
    for r in rows:
        r["remaining"] = r["budget"] - r["committed"] - r["actual"]
        r["used_pct"] = round((r["committed"] + r["actual"]) * 100 / r["budget"], 1) if r["budget"] else None
        r["over"] = r["remaining"] - r["extra"] < 0
    total = {f: sum(r[f] for r in rows) for f in ("budget", "committed", "actual", "extra", "remaining")}
    total["used_pct"] = round((total["committed"] + total["actual"]) * 100 / total["budget"], 1) \
        if total["budget"] else None
    return {"rows": rows, "total": total}


def check_budget(project_id, amounts, label):
    """Budget control for a new commitment ({account: amount}). Block: raise. Warn: return the messages."""
    p = get(project_id)
    if not p or p["budget_control"] == "none" or not enabled():
        return []
    if not q("SELECT 1 FROM project_budget WHERE project_id = ? LIMIT 1", (project_id,), one=True):
        return []
    rep = budget_report(project_id, amounts)
    cur = q("SELECT currency FROM branches WHERE id = ?", (p["branch_id"],), one=True)["currency"]
    from . import currency_decimals
    from .money import fmt
    dec = currency_decimals(cur)
    msgs = []
    for r in rep["rows"]:
        if r["extra"] and r["remaining"] - r["extra"] < 0:
            name = t(r["name_en"]) if r.get("unbudgeted") else f"{r['code']} {nm(r)}"
            msgs.append(f"{name}: {t('over budget by')} {fmt(r['extra'] - r['remaining'], dec)} {cur}")
    tot = rep["total"]
    if tot["extra"] and tot["remaining"] - tot["extra"] < 0:
        msgs.append(f"{t('Project total')}: {t('over budget by')} {fmt(tot['extra'] - tot['remaining'], dec)} {cur}")
    if msgs and p["budget_control"] == "block":
        raise AccountingError(f"{label} — {p['code']} {t('budget exceeded')}: " + "; ".join(msgs))
    return [f"{p['code']} {t('budget warning')}: " + "; ".join(msgs)] if msgs else []


def warn(messages):
    from flask import flash, has_request_context
    if has_request_context():
        for m in messages:
            flash(m, "error")


def mr_amounts(kind, doc_id):
    """{account: estimated value} of a material request / requisition."""
    from .stock import product
    out = defaultdict(int)
    if kind == "pr":
        rows = q("SELECT product_id, quantity, est_price AS price FROM pr_lines WHERE pr_id = ?", (doc_id,))
    else:
        rows = q("SELECT l.product_id, l.quantity, COALESCE(p.purchase_price, 0) AS price FROM mr_lines l "
                 "LEFT JOIN products p ON p.id = l.product_id WHERE l.mr_id = ?", (doc_id,))
    for r in rows:
        acc = product_cost_account(product(r["product_id"]) if r["product_id"] else None)
        out[acc] += int(round(r["quantity"] * r["price"]))
    return {k: v for k, v in out.items() if v}


def order_amounts(order_id):
    from .stock import product
    out = defaultdict(int)
    for ln in q("SELECT * FROM order_lines WHERE order_id = ?", (order_id,)):
        out[product_cost_account(product(ln["product_id"]) if ln["product_id"] else None)] += ln["subtotal"]
    return {k: v for k, v in out.items() if v}


# ---------------------------------------------------------------- billing and retention

def retention_account():
    acc = company()["retention_account_id"]
    if not acc:
        raise AccountingError(t("Set the retention receivable account in Company settings."))
    return acc


def milestone_billed(m):
    """A milestone counts as invoiced while its invoice exists and is not cancelled."""
    if not m["invoice_id"]:
        return False
    inv = q("SELECT state FROM invoices WHERE id = ?", (m["invoice_id"],), one=True)
    return bool(inv) and inv["state"] != "cancelled"


def billing(project_id):
    """Invoiced to date (before VAT, without retention lines), retention held and released, from posted documents."""
    ret = company()["retention_account_id"]
    billed = q("SELECT COALESCE(SUM(CASE WHEN i.kind = 'out_invoice' THEN il.subtotal ELSE -il.subtotal END), 0) "
               "FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id WHERE i.project_id = ? "
               "AND i.state = 'posted' AND i.kind IN ('out_invoice', 'out_refund') AND il.account_id != ?",
               (project_id, ret or 0), one=True)[0]
    held = released = 0
    if ret:
        r = q("SELECT COALESCE(SUM(ml.debit), 0) AS dr, COALESCE(SUM(ml.credit), 0) AS cr FROM move_lines ml "
              "JOIN invoices i ON i.move_id = ml.move_id WHERE i.project_id = ? AND i.state = 'posted' "
              "AND ml.account_id = ?", (project_id, ret), one=True)
        held, released = r["dr"], r["cr"]
    pending = q("SELECT COALESCE(SUM(il.subtotal), 0) FROM invoice_lines il JOIN invoices i ON i.id = il.invoice_id "
                "WHERE i.project_id = ? AND i.state = 'draft' AND i.project_billing = 'retention_release' "
                "AND il.account_id = ?", (project_id, ret or 0), one=True)[0]
    return {"billed": billed, "retention_held": held, "retention_released": released,
            "retention_balance": held - released, "release_pending": pending}


def _sale_journal(branch_id):
    j = q("SELECT * FROM journals WHERE branch_id = ? AND type = 'sale' AND active = 1 ORDER BY id LIMIT 1",
          (branch_id,), one=True)
    if not j:
        raise AccountingError(t("The branch has no sales/purchase journal."))
    return j


def _new_invoice(p, d, lines, billing_kind, ref, user_id):
    if not p["partner_id"]:
        raise AccountingError(t("Set the project's customer first."))
    j = _sale_journal(p["branch_id"])
    from . import currency_decimals
    cur = q("SELECT currency FROM branches WHERE id = ?", (p["branch_id"],), one=True)["currency"]
    dec = currency_decimals(cur)
    out = []
    for desc, acc, price, tax_id, cc in lines:
        rate = q("SELECT rate FROM taxes WHERE id = ?", (tax_id,), one=True)["rate"] if tax_id else 0
        sub, tax = compute_invoice_line(1, price, rate, dec)
        out.append((desc, acc, price, tax_id, sub, tax, cc))
    untaxed, tax = sum(o[4] for o in out), sum(o[5] for o in out)
    inv_id = x("INSERT INTO invoices (branch_id, journal_id, kind, partner_id, date, due_date, ref, state, amount_untaxed, "
               "amount_tax, amount_total, project_id, project_billing, invoice_type, created_by, created_at) "
               "VALUES (?,?,'out_invoice',?,?,date(?, '+30 days'),?,'draft',?,?,?,?,?,?,?,?)",
               (p["branch_id"], j["id"], p["partner_id"], d, d, ref, untaxed, tax, untaxed + tax, p["id"],
                billing_kind, billing_kind, user_id, now()))
    for i, (desc, acc, price, tax_id, sub, tx, cc) in enumerate(out):
        x("INSERT INTO invoice_lines (invoice_id, sequence, description, account_id, quantity, price_unit, tax_id, "
          "subtotal, tax_amount, cost_centre_id) VALUES (?,?,?,?,1,?,?,?,?,?)",
          (inv_id, i, desc, acc, price, tax_id, sub, tx, cc))
    return inv_id


def revenue_account(p):
    acc = p["revenue_account_id"] or company()["income_account_id"] or _sale_journal(p["branch_id"])["default_account_id"]
    if not acc:
        raise AccountingError(t("Choose the project's revenue account."))
    return acc


def sale_tax(p):
    if p["tax_id"]:
        return p["tax_id"]
    b = q("SELECT sale_tax_id FROM branches WHERE id = ?", (p["branch_id"],), one=True)
    if b and b["sale_tax_id"]:
        return b["sale_tax_id"]
    r = q("SELECT id FROM taxes WHERE scope = 'sale' AND rate > 0 AND active = 1 ORDER BY id LIMIT 1", one=True)
    return r["id"] if r else None


def bill_milestone(milestone_id, d, user_id=None):
    """Draft progress invoice for a milestone, with the retention deduction."""
    m = q("SELECT * FROM project_milestones WHERE id = ?", (milestone_id,), one=True)
    if not m:
        raise AccountingError(t("Unknown milestone."))
    if milestone_billed(m):
        raise AccountingError(t("This milestone is already invoiced."))
    p = get(m["project_id"])
    if p["state"] == "closed":
        raise AccountingError(t("The project is closed."))
    if m["amount"] <= 0:
        raise AccountingError(t("The milestone has no amount."))
    cc = ensure_cost_centre(p["id"])
    lines = [(f"{p['code']} — {m['name']}", revenue_account(p), m["amount"], sale_tax(p), cc)]
    retention = int(round(m["amount"] * (p["retention_pct"] or 0) / 100))
    if retention:
        lines.append((f"{t('Retention')} {p['retention_pct']:g}% {t('held')}", retention_account(), -retention, None,
                      None))
    inv_id = _new_invoice(p, d, lines, "progress", f"{p['code']} {m['name']}", user_id)
    x("UPDATE project_milestones SET state = 'invoiced', invoice_id = ?, completed_at = COALESCE(completed_at, ?) "
      "WHERE id = ?", (inv_id, now(), milestone_id))
    audit(user_id, "bill_milestone", "project", p["id"], m["name"])
    return inv_id


def release_retention(project_id, d, amount=None, user_id=None):
    """Draft invoice releasing retention held (all of it unless an amount is given)."""
    p = get(project_id)
    b = billing(project_id)
    available = b["retention_balance"] - b["release_pending"]
    amount = available if amount is None else amount
    if amount <= 0 or available <= 0:
        raise AccountingError(t("There is no retention to release."))
    if amount > available:
        raise AccountingError(t("The amount is more than the retention held."))
    inv_id = _new_invoice(p, d, [(f"{p['code']} — {t('Retention release')}", retention_account(), amount, None, None)],
                          "retention_release", f"{p['code']} {t('Retention release')}", user_id)
    audit(user_id, "release_retention", "project", project_id)
    return inv_id


def summary(project_id):
    p = get(project_id)
    rep = budget_report(project_id)
    b = billing(project_id)
    rev = revenue(project_id)
    cost = rep["total"]["actual"]
    ms = q("SELECT * FROM project_milestones WHERE project_id = ? ORDER BY sequence, id", (project_id,))
    billed_ms = sum(m["amount"] for m in ms if milestone_billed(m))
    return {"budget": rep["total"]["budget"], "committed": rep["total"]["committed"], "actual": cost,
            "remaining": rep["total"]["remaining"], "used_pct": rep["total"]["used_pct"], "revenue": rev,
            "margin": rev - cost, "margin_pct": round((rev - cost) * 100 / rev, 1) if rev else None,
            "progress_pct": round(billed_ms * 100 / p["contract_value"], 1) if p["contract_value"] else None,
            "to_bill": max(p["contract_value"] - b["billed"], 0), **b}


def list_rows(branch_ids, state=None, search=None, user_id=None):
    if not branch_ids:
        return []
    sql = (f"SELECT p.*, b.code AS bcode, b.currency, c.name_en AS cust_en, c.name_ar AS cust_ar, "
           f"u.full_name AS manager FROM projects p JOIN branches b ON b.id = p.branch_id "
           f"LEFT JOIN partners c ON c.id = p.partner_id LEFT JOIN users u ON u.id = p.manager_id "
           f"WHERE p.branch_id IN ({placeholders(branch_ids)})")
    args = list(branch_ids)
    if state:
        sql += " AND p.state = ?"
        args.append(state)
    if search:
        sql += " AND (p.code LIKE ? OR p.name_en LIKE ? OR p.name_ar LIKE ?)"
        args += [f"%{search}%"] * 3
    if user_id:
        sql += " AND (p.manager_id = ? OR p.id IN (SELECT project_id FROM project_members WHERE user_id = ?))"
        args += [user_id, user_id]
    out = []
    for r in q(sql + " ORDER BY p.code", args):
        s = summary(r["id"])
        out.append({**dict(r), **s})
    return out
