"""Approval workflow.

A document that needs approval (material request, requisition, purchase order, RFQ award, vendor registration...)
is submitted with its amount. The first active rule for its type whose conditions match (branch, department,
project, amount band; lowest priority number wins) decides the steps. No matching rule means no approval is needed.

Each step creates a task for every eligible approver: the members of the step's role and the users named on the
step, except the person who submitted the document (separation of duties) and inactive users. An approver who has
delegated (out of office) passes the task to the delegate for those dates. A step in "any" mode is complete when
`required` approvers have approved; in "all" mode when everybody has. Then the next step starts, and after the last
the document's on_approved handler runs. A rejection (with a reason) ends the request at once and runs on_rejected,
which returns the document to its creator. An administrator may approve in place of the approvers (for example
their own document) only with a reason; that override is recorded.
"""
from datetime import date, datetime, timedelta

from flask import current_app, url_for

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

# document type -> label, the page that shows it, the licensed module it belongs to (None = core)
DOC_TYPES = {
    "mr": {"label": "Material Request", "endpoint": "procurement.mr_view", "arg": "doc_id", "module": "procurement"},
    "pr": {"label": "Purchase Requisition", "endpoint": "procurement.pr_view", "arg": "doc_id", "module": "procurement"},
    "po": {"label": "Purchase Order", "endpoint": "orders.view", "arg": "order_id", "module": None},
    "rfq_award": {"label": "RFQ Award", "endpoint": "procurement.rfq_view", "arg": "rfq_id", "module": "procurement"},
    "bank_change": {"label": "Bank Details Change", "endpoint": "vendors.bank_change", "arg": "cid", "module": "vendors"},
    "vendor_registration": {"label": "Supplier Registration", "endpoint": "vendors.registration", "arg": "rid",
                            "module": "portal"},
}
HANDLERS = {}  # doc type -> {"approved": fn(doc_id, user_id), "rejected": fn(doc_id, user_id, reason)}


def handler(doc_type, event):
    def deco(fn):
        HANDLERS.setdefault(doc_type, {})[event] = fn
        return fn
    return deco


def doc_types():
    """Document types available in this licence."""
    from . import has
    return {k: v for k, v in DOC_TYPES.items() if not v["module"] or has(v["module"])}


def to_company(amount, currency, d=None):
    from .ledger import rate_on
    from .money import translate
    if not currency or currency == company()["currency"]:
        return amount
    return translate(amount, rate_on(currency, d or date.today().isoformat()))


def find_rule(doc_type, amount, branch_id=None, department=None, project_id=None):
    for r in q("SELECT * FROM approval_rules WHERE doc_type = ? AND active = 1 ORDER BY priority, min_amount DESC, id",
               (doc_type,)):
        if r["branch_id"] and r["branch_id"] != branch_id:
            continue
        if r["department"] and (r["department"] or "").strip().lower() != (department or "").strip().lower():
            continue
        if r["project_id"] and r["project_id"] != project_id:
            continue
        if amount < r["min_amount"] or (r["max_amount"] is not None and amount > r["max_amount"]):
            continue
        if q("SELECT 1 FROM approval_steps WHERE rule_id = ?", (r["id"],), one=True):
            return r
    return None


DYNAMIC = {"project_manager": "Project manager"}  # approvers found from the document itself


def step_approvers(step, requester=None, project_id=None):
    """Active users who may act on a step, without the requester."""
    ids = {r[0] for r in q("SELECT user_id FROM approval_step_users WHERE step_id = ?", (step["id"],))}
    if step["dynamic"] == "project_manager" and project_id:
        pm = q("SELECT manager_id FROM projects WHERE id = ?", (project_id,), one=True)
        if pm and pm["manager_id"]:
            ids.add(pm["manager_id"])
    if step["role_id"]:
        ids |= {r[0] for r in q("SELECT user_id FROM approval_role_users WHERE role_id = ?", (step["role_id"],))}
    if not ids:
        return []
    users = q(f"SELECT id FROM users WHERE active = 1 AND id IN ({placeholders(ids)})", list(ids))
    return [u["id"] for u in users if u["id"] != requester]


def delegate_of(user_id, d=None):
    d = d or date.today().isoformat()
    r = q("SELECT delegate_id FROM approval_delegations WHERE user_id = ? AND date_from <= ? AND date_to >= ? "
          "ORDER BY id DESC LIMIT 1", (user_id, d, d), one=True)
    return r["delegate_id"] if r else None


def event(request_id, action, user_id=None, step_no=None, comment=None):
    x("INSERT INTO approval_events (request_id, step_no, user_id, action, comment, at) VALUES (?,?,?,?,?,?)",
      (request_id, step_no, user_id, action, comment, now()))


def current_request(doc_type, doc_id):
    return q("SELECT * FROM approval_requests WHERE doc_type = ? AND doc_id = ? ORDER BY id DESC LIMIT 1",
             (doc_type, doc_id), one=True)


def submit(doc_type, doc_id, amount, title, user_id, branch_id=None, currency=None, department=None,
           project_id=None):
    """Start the approval of a document. Returns the request id, or None when no rule applies (approved at once)."""
    open_req = q("SELECT id FROM approval_requests WHERE doc_type = ? AND doc_id = ? AND state = 'pending'",
                 (doc_type, doc_id), one=True)
    if open_req:
        raise AccountingError(t("This document is already waiting for approval."))
    value = to_company(amount, currency)
    rule = find_rule(doc_type, value, branch_id, department, project_id)
    if not rule:
        return None
    rid = x("INSERT INTO approval_requests (doc_type, doc_id, rule_id, title, amount, currency, branch_id, state, "
            "current_step, requested_by, requested_at, project_id) VALUES (?,?,?,?,?,?,?, 'pending', 0, ?, ?, ?)",
            (doc_type, doc_id, rule["id"], title, amount, currency, branch_id, user_id, now(), project_id))
    event(rid, "submitted", user_id, comment=rule["name"])
    _start_step(rid, 1)
    return rid


def _steps(rule_id):
    return q("SELECT * FROM approval_steps WHERE rule_id = ? ORDER BY step_no", (rule_id,))


def _start_step(request_id, step_no):
    req = q("SELECT * FROM approval_requests WHERE id = ?", (request_id,), one=True)
    steps = [s for s in _steps(req["rule_id"]) if s["step_no"] >= step_no]
    if not steps:
        return _finish(request_id, "approved")
    step = steps[0]
    users = step_approvers(step, req["requested_by"], req["project_id"])
    if not users and step["dynamic"] and not step["role_id"] and not _named(step):
        # e.g. a project-manager step on a document without a project: the step does not apply. When the project
        # manager is the requester, a later step must still approve (separation of duties).
        pm = q("SELECT manager_id FROM projects WHERE id = ?", (req["project_id"] or 0,), one=True)
        if not (pm and pm["manager_id"]) or len(steps) > 1:
            event(request_id, "skipped", None, step["step_no"], t(DYNAMIC[step["dynamic"]]) + ": " + t("not applicable"))
            return _start_step(request_id, step["step_no"] + 1)
    if not users:
        raise AccountingError(f"{t('Nobody can approve step')} {step['step_no']} ({step['name'] or ''}) "
                              f"{t('of the approval rule')} \"{_rule_name(req['rule_id'])}\". "
                              f"{t('Add approvers in Settings → Approval Workflow.')}")
    x("UPDATE approval_requests SET current_step = ? WHERE id = ?", (step["step_no"], request_id))
    for uid in users:
        x("INSERT INTO approval_tasks (request_id, step_no, user_id, state, created_at) VALUES (?,?,?, 'pending', ?)",
          (request_id, step["step_no"], uid, now()))
        notify_task(req, uid, step)
    return None


def _named(step):
    return bool(q("SELECT 1 FROM approval_step_users WHERE step_id = ? LIMIT 1", (step["id"],), one=True))


def _rule_name(rule_id):
    r = q("SELECT name FROM approval_rules WHERE id = ?", (rule_id,), one=True)
    return r["name"] if r else ""


def doc_url(doc_type, doc_id, external=False):
    from flask import has_request_context, request
    info = DOC_TYPES[doc_type]
    if has_request_context():
        path = url_for(info["endpoint"], **{info["arg"]: doc_id})
    else:  # background work and the demo seed: build the path without a request
        with current_app.test_request_context():
            path = url_for(info["endpoint"], **{info["arg"]: doc_id})
    if external:
        base = (company()["public_url"] or "").rstrip("/")
        if not base and has_request_context():
            base = request.host_url.rstrip("/")
        return base + path
    return path


def notify(user_id, title, body="", url=None, email=True):
    """In-app notification, plus an email when the user has an address and email is set up."""
    if not user_id:  # e.g. a supplier registration submitted from the public form
        return
    x("INSERT INTO notifications (user_id, title, body, url, created_at) VALUES (?,?,?,?,?)",
      (user_id, title, body, url, now()))
    if not email:
        return
    from . import mailer
    u = q("SELECT email, full_name, username FROM users WHERE id = ?", (user_id,), one=True)
    if u and u["email"] and mailer.configured():
        try:
            mailer.send(u["email"], title, f"{body}\n\n{url or ''}".strip(), kind="workflow", user_id=None)
        except AccountingError:
            pass  # logged in the email log; the in-app notification is there anyway


def notify_task(req, user_id, step):
    who = q("SELECT full_name, username FROM users WHERE id = ?", (req["requested_by"],), one=True)
    label = t(DOC_TYPES[req["doc_type"]]["label"])
    notify(user_id, f"{t('Approval needed')}: {label} {req['title'] or ''}".strip(),
           f"{t('Requested by')} {(who['full_name'] or who['username']) if who else ''} · "
           f"{t('Step')} {step['step_no']}{' — ' + step['name'] if step['name'] else ''}",
           doc_url(req["doc_type"], req["doc_id"], external=True))


def _finish(request_id, state, user_id=None, reason=None):
    req = q("SELECT * FROM approval_requests WHERE id = ?", (request_id,), one=True)
    x("UPDATE approval_requests SET state = ?, decided_at = ? WHERE id = ?", (state, now(), request_id))
    x("UPDATE approval_tasks SET state = 'cancelled' WHERE request_id = ? AND state = 'pending'", (request_id,))
    event(request_id, state, user_id, comment=reason)
    fn = HANDLERS.get(req["doc_type"], {}).get(state)
    if fn:
        if state == "rejected":
            fn(req["doc_id"], user_id, reason)
        else:
            fn(req["doc_id"], user_id)
    label = t(DOC_TYPES[req["doc_type"]]["label"])
    notify(req["requested_by"], f"{label} {req['title'] or ''} {t('approved') if state == 'approved' else t('rejected')}",
           reason or "", doc_url(req["doc_type"], req["doc_id"], external=True))
    return state


def open_task(request_id, user_id):
    """The pending task of the current step this user may act on (their own, or one delegated to them)."""
    req = q("SELECT * FROM approval_requests WHERE id = ?", (request_id,), one=True)
    if not req or req["state"] != "pending":
        return None
    tasks = q("SELECT * FROM approval_tasks WHERE request_id = ? AND step_no = ? AND state = 'pending'",
              (request_id, req["current_step"]))
    for tk in tasks:
        if tk["user_id"] == user_id:
            return tk
    for tk in tasks:
        if delegate_of(tk["user_id"]) == user_id and user_id != req["requested_by"]:
            return tk
    return None


def act(request_id, user_id, action, comment=None, override=False):
    """approve | reject. override: an administrator acting for the approvers (a reason is required)."""
    req = q("SELECT * FROM approval_requests WHERE id = ?", (request_id,), one=True)
    if not req or req["state"] != "pending":
        raise AccountingError(t("This approval is no longer open."))
    if action == "reject" and not (comment or "").strip():
        raise AccountingError(t("Give a reason for the rejection."))
    task = open_task(request_id, user_id)
    if not task:
        u = q("SELECT role FROM users WHERE id = ?", (user_id,), one=True)
        if not (override and u and u["role"] == "admin"):
            if user_id == req["requested_by"]:
                raise AccountingError(t("You cannot approve a document you submitted."))
            raise AccountingError(t("You are not an approver for this step."))
        if not (comment or "").strip():
            raise AccountingError(t("Give the reason for approving in place of the approvers."))
        event(request_id, f"override_{action}", user_id, req["current_step"], comment)
        audit(user_id, f"override {action}", req["doc_type"], req["doc_id"], comment)
        if action == "reject":
            return _finish(request_id, "rejected", user_id, comment)
        x("UPDATE approval_tasks SET state = 'skipped' WHERE request_id = ? AND step_no = ? AND state = 'pending'",
          (request_id, req["current_step"]))
        return _advance(request_id)
    x("UPDATE approval_tasks SET state = ?, acted_by = ?, acted_at = ?, comment = ? WHERE id = ?",
      ("approved" if action == "approve" else "rejected", user_id, now(), comment, task["id"]))
    on_behalf = f" ({t('on behalf of')} {_user_name(task['user_id'])})" if task["user_id"] != user_id else ""
    event(request_id, "approved_step" if action == "approve" else "rejected", user_id, req["current_step"],
          (comment or "") + on_behalf)
    if action == "reject":
        return _finish(request_id, "rejected", user_id, comment)
    step = q("SELECT * FROM approval_steps WHERE rule_id = ? AND step_no = ?", (req["rule_id"], req["current_step"]),
             one=True)
    done = q("SELECT COUNT(*) FROM approval_tasks WHERE request_id = ? AND step_no = ? AND state = 'approved'",
             (request_id, req["current_step"]), one=True)[0]
    left = q("SELECT COUNT(*) FROM approval_tasks WHERE request_id = ? AND step_no = ? AND state = 'pending'",
             (request_id, req["current_step"]), one=True)[0]
    complete = (left == 0) if step["mode"] == "all" else (done >= (step["required"] or 1) or left == 0)
    if not complete:
        return "pending"
    x("UPDATE approval_tasks SET state = 'skipped' WHERE request_id = ? AND step_no = ? AND state = 'pending'",
      (request_id, req["current_step"]))
    return _advance(request_id)


def _advance(request_id):
    req = q("SELECT * FROM approval_requests WHERE id = ?", (request_id,), one=True)
    nxt = q("SELECT MIN(step_no) FROM approval_steps WHERE rule_id = ? AND step_no > ?",
            (req["rule_id"], req["current_step"]), one=True)[0]
    if nxt is None:
        return _finish(request_id, "approved")
    _start_step(request_id, nxt)
    return "pending"


def cancel(doc_type, doc_id, user_id=None, reason=None):
    """The document was withdrawn or cancelled: close its open request without running the handlers."""
    req = q("SELECT * FROM approval_requests WHERE doc_type = ? AND doc_id = ? AND state = 'pending'",
            (doc_type, doc_id), one=True)
    if req:
        x("UPDATE approval_requests SET state = 'cancelled', decided_at = ? WHERE id = ?", (now(), req["id"]))
        x("UPDATE approval_tasks SET state = 'cancelled' WHERE request_id = ? AND state = 'pending'", (req["id"],))
        event(req["id"], "cancelled", user_id, comment=reason)


def act_on_doc(doc_type, doc_id, user_id, action, comment=None, override=False):
    req = q("SELECT * FROM approval_requests WHERE doc_type = ? AND doc_id = ? AND state = 'pending'",
            (doc_type, doc_id), one=True)
    if not req:
        raise AccountingError(t("This document is not waiting for approval."))
    return act(req["id"], user_id, action, comment, override)


def _user_name(uid):
    u = q("SELECT full_name, username FROM users WHERE id = ?", (uid,), one=True)
    return (u["full_name"] or u["username"]) if u else ""


def status(doc_type, doc_id, user_id=None):
    """For the approval panel on a document: the latest request, its steps with who acted, the history, and
    whether this user can act now."""
    req = current_request(doc_type, doc_id)
    if not req:
        return None
    steps = []
    for s in _steps(req["rule_id"]) if req["rule_id"] else []:
        tasks = q("SELECT tk.*, u.full_name, u.username FROM approval_tasks tk JOIN users u ON u.id = tk.user_id "
                  "WHERE tk.request_id = ? AND tk.step_no = ? ORDER BY tk.id", (req["id"], s["step_no"]))
        role = q("SELECT * FROM approval_roles WHERE id = ?", (s["role_id"],), one=True) if s["role_id"] else None
        state = "done" if req["state"] == "approved" or s["step_no"] < req["current_step"] else \
            "current" if s["step_no"] == req["current_step"] and req["state"] == "pending" else \
            "rejected" if req["state"] == "rejected" and s["step_no"] == req["current_step"] else "waiting"
        if s["dynamic"] and not tasks and (not req["project_id"] or s["step_no"] < req["current_step"]
                                           or req["state"] != "pending"):
            continue  # a step that does not apply to this document (e.g. project manager, no project)
        steps.append({"step": s, "role": role, "tasks": tasks, "state": state})
    history = q("SELECT e.*, u.full_name, u.username FROM approval_events e LEFT JOIN users u ON u.id = e.user_id "
                "WHERE e.request_id = ? AND e.action != 'skipped' ORDER BY e.id", (req["id"],))
    me = q("SELECT role FROM users WHERE id = ?", (user_id,), one=True) if user_id else None
    can_act = bool(user_id and open_task(req["id"], user_id))
    return {"req": req, "steps": steps, "history": history, "can_act": can_act,
            "can_override": bool(req["state"] == "pending" and me and me["role"] == "admin" and not can_act),
            "rule": q("SELECT * FROM approval_rules WHERE id = ?", (req["rule_id"],), one=True)}


def inbox(user_id):
    """Open tasks for this user, including those delegated to them today."""
    today = date.today().isoformat()
    deleg = [r["user_id"] for r in q("SELECT user_id FROM approval_delegations WHERE delegate_id = ? AND date_from <= ? "
                                     "AND date_to >= ?", (user_id, today, today))]
    ids = [user_id] + deleg
    rows = q(f"SELECT tk.*, r.doc_type, r.doc_id, r.title, r.amount, r.currency, r.requested_by, r.requested_at, "
             f"r.branch_id, s.name AS step_name, u.full_name AS req_name, u.username AS req_user "
             f"FROM approval_tasks tk JOIN approval_requests r ON r.id = tk.request_id "
             f"JOIN approval_steps s ON s.rule_id = r.rule_id AND s.step_no = tk.step_no "
             f"LEFT JOIN users u ON u.id = r.requested_by "
             f"WHERE tk.state = 'pending' AND r.state = 'pending' AND tk.step_no = r.current_step "
             f"AND tk.user_id IN ({placeholders(ids)}) AND r.requested_by != ? ORDER BY r.requested_at",
             ids + [user_id])
    seen, out = set(), []
    for r in rows:
        if r["request_id"] in seen or r["doc_type"] not in doc_types():
            continue
        seen.add(r["request_id"])
        out.append(dict(r, delegated=r["user_id"] != user_id, url=doc_url(r["doc_type"], r["doc_id"])))
    return out


def unread_count(user_id):
    return q("SELECT COUNT(*) FROM notifications WHERE user_id = ? AND read_at IS NULL", (user_id,), one=True)[0]


def send_reminders():
    """Remind approvers of tasks waiting longer than their rule's reminder days (once a day per task)."""
    rows = q("SELECT tk.*, r.doc_type, r.doc_id, r.title, r.requested_by, ru.reminder_days, s.step_no AS sno, s.name "
             "FROM approval_tasks tk JOIN approval_requests r ON r.id = tk.request_id "
             "JOIN approval_rules ru ON ru.id = r.rule_id "
             "JOIN approval_steps s ON s.rule_id = r.rule_id AND s.step_no = tk.step_no "
             "WHERE tk.state = 'pending' AND r.state = 'pending'")
    n = 0
    for tk in rows:
        created = datetime.fromisoformat(tk["created_at"][:19])
        last = datetime.fromisoformat(tk["reminded_at"][:19]) if tk["reminded_at"] else created
        if datetime.now() - created >= timedelta(days=tk["reminder_days"] or 2) and \
                datetime.now() - last >= timedelta(hours=23):
            label = t(DOC_TYPES.get(tk["doc_type"], {}).get("label", tk["doc_type"]))
            notify(tk["user_id"], f"{t('Reminder')}: {t('approval waiting')} — {label} {tk['title'] or ''}",
                   f"{t('Waiting since')} {tk['created_at'][:10]}", doc_url(tk["doc_type"], tk["doc_id"], external=True))
            x("UPDATE approval_tasks SET reminded_at = ? WHERE id = ?", (now(), tk["id"]))
            n += 1
    return n


# ---------------------------------------------------------------- terms and conditions

TERMS_TYPES = {"rfq": "Request for Quotation", "po": "Purchase Order", "quotation": "Sales Quotation / Order",
               "invoice": "Customer Invoice", "bill": "Vendor Bill"}


def terms_options(doc_type):
    return q("SELECT * FROM terms_templates WHERE doc_type = ? AND active = 1 ORDER BY is_default DESC, name",
             (doc_type,))


def default_terms(doc_type, lang="en"):
    r = q("SELECT * FROM terms_templates WHERE doc_type = ? AND active = 1 AND is_default = 1 LIMIT 1", (doc_type,),
          one=True)
    if not r:
        return ""
    return (r["body_ar"] if lang == "ar" and r["body_ar"] else r["body_en"]) or ""
