from collections import defaultdict
from datetime import date, timedelta

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

from .. import check_branch, context_branch_ids, has, login_required, module_required, write_required
from ..db import commit, get_db, placeholders, q, x
from ..i18n import t
from ..ledger import AccountingError, audit, now
from ..money import to_int
from ..stock import next_number
from .common import branch_map, default_branch, partners_list, user_id

bp = Blueprint("crm", __name__, url_prefix="/crm")
bp.before_request(module_required("crm"))

STAGES = [("new", "New", 10), ("qualified", "Qualified", 30), ("proposal", "Proposal", 50),
          ("negotiation", "Negotiation", 75), ("won", "Won", 100), ("lost", "Lost", 0)]
STAGE_PROB = {k: p for k, _, p in STAGES}
SOURCES = ["Website", "Referral", "Walk-in", "Phone", "Social media", "Exhibition", "Email campaign", "Existing customer"]
ACTIVITY_KINDS = [("call", "Call"), ("meeting", "Meeting"), ("email", "Email"), ("task", "Task")]


def lead_or_404(lead_id):
    ld = q("SELECT l.*, u.full_name AS owner_name, u.username AS owner_user, p.name_en AS pname_en, p.name_ar AS pname_ar "
           "FROM leads l LEFT JOIN users u ON u.id = l.owner_id LEFT JOIN partners p ON p.id = l.partner_id "
           "WHERE l.id = ?", (lead_id,), one=True)
    if not ld:
        abort(404)
    check_branch(ld["branch_id"])
    return ld


def lead_rows(extra="", args=()):
    ids = context_branch_ids()
    if not ids:
        return []
    sql = (f"SELECT l.*, u.full_name AS owner_name, u.username AS owner_user, "
           f"(SELECT MIN(due_date) FROM crm_activities a WHERE a.lead_id = l.id AND a.done = 0) AS next_activity "
           f"FROM leads l LEFT JOIN users u ON u.id = l.owner_id WHERE l.branch_id IN ({placeholders(ids)})")
    params = list(ids)
    owner = request.args.get("owner")
    if owner == "me":
        sql += " AND l.owner_id = ?"
        params.append(g.user["id"])
    search = request.args.get("q", "").strip()
    if search:
        sql += " AND (l.title LIKE ? OR l.company_name LIKE ? OR l.contact_name LIKE ? OR l.email LIKE ? OR l.phone LIKE ?)"
        params += [f"%{search}%"] * 5
    return q(sql + extra + " ORDER BY l.priority DESC, l.expected_close IS NULL, l.expected_close, l.id DESC",
             params + list(args))


@bp.route("/")
@login_required
def pipeline():
    rows = lead_rows(" AND (l.stage NOT IN ('won', 'lost') OR l.closed_at >= ?)",
                     ((date.today() - timedelta(days=30)).isoformat(),))
    columns = {k: [] for k, _, _ in STAGES}
    for r in rows:
        columns[r["stage"]].append(r)
    totals = {k: sum(r["expected_revenue"] for r in v) for k, v in columns.items()}
    return render_template("crm/pipeline.html", stages=STAGES, columns=columns, totals=totals,
                           today=date.today().isoformat(), owner=request.args.get("owner", ""),
                           search=request.args.get("q", ""))


@bp.route("/list")
@login_required
def leads():
    stage = request.args.get("stage", "")
    rows = lead_rows(" AND l.stage = ?", (stage,)) if stage else lead_rows()
    return render_template("crm/list.html", rows=rows, stages=STAGES, stage=stage, branches=branch_map(),
                           today=date.today().isoformat(), owner=request.args.get("owner", ""),
                           search=request.args.get("q", ""))


@bp.route("/new", methods=["GET", "POST"])
@bp.route("/<int:lead_id>", methods=["GET", "POST"])
@login_required
def lead(lead_id=None):
    ld = lead_or_404(lead_id) if lead_id else None
    if request.method == "POST":
        if g.user["role"] not in ("admin", "accountant"):
            abort(403)
        f = request.form
        try:
            branch_id = check_branch(f["branch_id"])
            stage = f.get("stage") if f.get("stage") in STAGE_PROB else "new"
            vals = {"title": f["title"].strip(), "contact_name": f.get("contact_name"), "company_name": f.get("company_name"),
                    "email": f.get("email"), "phone": f.get("phone"),
                    "partner_id": int(f["partner_id"]) if f.get("partner_id") else None,
                    "source": f.get("source"), "stage": stage, "expected_revenue": to_int(f.get("expected_revenue") or 0),
                    "probability": int(f.get("probability") or STAGE_PROB[stage]),
                    "expected_close": f.get("expected_close") or None, "priority": int(f.get("priority") or 0),
                    "owner_id": int(f["owner_id"]) if f.get("owner_id") else user_id(), "branch_id": branch_id,
                    "notes": f.get("notes")}
            if not vals["title"]:
                raise AccountingError(t("Opportunity") + ": " + t("is required"))
            if lead_id:
                x(f"UPDATE leads SET {', '.join(k + ' = ?' for k in vals)} WHERE id = ?", list(vals.values()) + [lead_id])
            else:
                vals.update(number=next_number("leads", "number", "LEAD", branch_id, date.today().isoformat()),
                            created_at=now())
                lead_id = x(f"INSERT INTO leads ({', '.join(vals)}) VALUES ({', '.join('?' * len(vals))})",
                            list(vals.values()))
            audit(user_id(), "save", "lead", lead_id, vals["title"])
            commit()
            flash(t("Saved."), "success")
            return redirect(url_for("crm.lead", lead_id=lead_id))
        except (AccountingError, ValueError, KeyError) as e:
            get_db().rollback()
            flash(str(e) or t("invalid number"), "error")
    activities = q("SELECT a.*, u.full_name, u.username FROM crm_activities a LEFT JOIN users u ON u.id = a.user_id "
                   "WHERE a.lead_id = ? ORDER BY a.done, a.due_date", (lead_id,)) if lead_id else []
    orders = q("SELECT * FROM orders WHERE lead_id = ? ORDER BY id", (lead_id,)) if lead_id else []
    users = q("SELECT id, full_name, username FROM users WHERE active = 1 ORDER BY full_name")
    return render_template("crm/lead.html", ld=ld, stages=STAGES, sources=SOURCES, kinds=ACTIVITY_KINDS,
                           activities=activities, orders=orders, users=users, branches=branch_map(),
                           customers=partners_list("customer"), default_branch=default_branch(),
                           today=date.today().isoformat())


@bp.route("/<int:lead_id>/stage", methods=["POST"])
@write_required
def set_stage(lead_id):
    ld = lead_or_404(lead_id)
    stage = request.form.get("stage")
    if stage not in STAGE_PROB:
        abort(400)
    closed = now() if stage in ("won", "lost") else None
    x("UPDATE leads SET stage = ?, probability = ?, closed_at = ?, lost_reason = ? WHERE id = ?",
      (stage, STAGE_PROB[stage], closed, request.form.get("reason") if stage == "lost" else None, lead_id))
    audit(user_id(), f"stage:{stage}", "lead", lead_id, ld["title"])
    commit()
    if request.headers.get("X-Requested-With") == "fetch":
        return jsonify(ok=True)
    return redirect(request.referrer or url_for("crm.lead", lead_id=lead_id))


@bp.route("/<int:lead_id>/activity", methods=["POST"])
@write_required
def add_activity(lead_id):
    lead_or_404(lead_id)
    f = request.form
    if not f.get("summary", "").strip():
        flash(t("Description") + ": " + t("is required"), "error")
        return redirect(url_for("crm.lead", lead_id=lead_id))
    x("INSERT INTO crm_activities (lead_id, kind, summary, due_date, notes, user_id, created_at) VALUES (?,?,?,?,?,?,?)",
      (lead_id, f.get("kind") or "call", f["summary"].strip(), f.get("due_date") or None, f.get("notes"),
       int(f["user_id"]) if f.get("user_id") else user_id(), now()))
    commit()
    return redirect(url_for("crm.lead", lead_id=lead_id) + "#activities")


@bp.route("/activity/<int:act_id>/done", methods=["POST"])
@write_required
def activity_done(act_id):
    a = q("SELECT * FROM crm_activities WHERE id = ?", (act_id,), one=True)
    if not a:
        abort(404)
    lead_or_404(a["lead_id"])
    x("UPDATE crm_activities SET done = 1, done_at = ?, notes = COALESCE(?, notes) WHERE id = ?",
      (now(), request.form.get("result") or None, act_id))
    commit()
    return redirect(request.referrer or url_for("crm.activities"))


@bp.route("/activities")
@login_required
def activities():
    ids = context_branch_ids()
    rows = []
    if ids:
        sql = (f"SELECT a.*, l.title, l.number, l.company_name, u.full_name, u.username FROM crm_activities a "
               f"JOIN leads l ON l.id = a.lead_id LEFT JOIN users u ON u.id = a.user_id "
               f"WHERE a.done = 0 AND l.branch_id IN ({placeholders(ids)})")
        args = list(ids)
        if request.args.get("owner") != "all":
            sql += " AND a.user_id = ?"
            args.append(g.user["id"])
        rows = q(sql + " ORDER BY a.due_date IS NULL, a.due_date", args)
    return render_template("crm/activities.html", rows=rows, today=date.today().isoformat(), kinds=dict(ACTIVITY_KINDS),
                           owner=request.args.get("owner", ""))


@bp.route("/<int:lead_id>/quotation", methods=["POST"])
@write_required
def quotation(lead_id):
    ld = lead_or_404(lead_id)
    if not has("sales"):
        flash(t("This module is not included in your license."), "error")
        return redirect(url_for("crm.lead", lead_id=lead_id))
    partner_id = ld["partner_id"]
    if not partner_id:
        name = ld["company_name"] or ld["contact_name"] or ld["title"]
        partner_id = x("INSERT INTO partners (name_en, is_customer, email, phone) VALUES (?, 1, ?, ?)",
                       (name, ld["email"], ld["phone"]))
        x("UPDATE leads SET partner_id = ? WHERE id = ?", (partner_id, lead_id))
    wh = q("SELECT id FROM warehouses WHERE branch_id = ? AND active = 1 ORDER BY id LIMIT 1", (ld["branch_id"],), one=True)
    d = date.today().isoformat()
    oid = x("INSERT INTO orders (kind, number, branch_id, warehouse_id, partner_id, date, ref, notes, state, lead_id, "
            "created_by, created_at) VALUES ('sale', ?, ?, ?, ?, ?, ?, ?, 'draft', ?, ?, ?)",
            (next_number("orders", "number", "SO", ld["branch_id"], d), ld["branch_id"], wh["id"] if wh else None,
             partner_id, d, ld["number"], ld["title"], lead_id, user_id(), now()))
    if ld["stage"] in ("new", "qualified"):
        x("UPDATE leads SET stage = 'proposal', probability = ? WHERE id = ?", (STAGE_PROB["proposal"], lead_id))
    audit(user_id(), "quotation", "lead", lead_id, f"order {oid}")
    commit()
    flash(t("Quotation created. Add the products and confirm it."), "success")
    return redirect(url_for("orders.form", kind="sale", order_id=oid))


@bp.route("/report")
@login_required
def report():
    rows = lead_rows()
    since = request.args.get("since") or (date.today() - timedelta(days=365)).isoformat()
    by_stage = {k: {"n": 0, "amount": 0, "weighted": 0} for k, _, _ in STAGES}
    by_source = defaultdict(lambda: {"n": 0, "won": 0, "lost": 0, "amount_won": 0})
    by_owner = defaultdict(lambda: {"n": 0, "won": 0, "lost": 0, "amount_won": 0, "open": 0})
    for r in rows:
        st = by_stage[r["stage"]]
        st["n"] += 1
        st["amount"] += r["expected_revenue"]
        st["weighted"] += int(r["expected_revenue"] * r["probability"] / 100)
        if r["created_at"] and r["created_at"][:10] < since:
            continue
        for bucket in (by_source[r["source"] or "—"], by_owner[r["owner_name"] or r["owner_user"] or "—"]):
            bucket["n"] += 1
            if r["stage"] == "won":
                bucket["won"] += 1
                bucket["amount_won"] += r["expected_revenue"]
            elif r["stage"] == "lost":
                bucket["lost"] += 1
            elif "open" in bucket:
                bucket["open"] += 1
    ids = context_branch_ids() or [0]
    lost = q(f"SELECT lost_reason, COUNT(*) AS n FROM leads WHERE stage = 'lost' AND lost_reason IS NOT NULL "
             f"AND lost_reason != '' AND branch_id IN ({placeholders(ids)}) GROUP BY lost_reason ORDER BY n DESC LIMIT 10",
             ids)
    open_total = sum(v["amount"] for k, v in by_stage.items() if k not in ("won", "lost"))
    weighted = sum(v["weighted"] for k, v in by_stage.items() if k not in ("won", "lost"))
    won, lostn = by_stage["won"]["n"], by_stage["lost"]["n"]
    return render_template("crm/report.html", stages=STAGES, by_stage=by_stage, by_source=dict(by_source),
                           by_owner=dict(by_owner), lost=lost, open_total=open_total, weighted=weighted,
                           win_rate=(100.0 * won / (won + lostn)) if won + lostn else None, since=since)
