"""Credit control: customers with limits, holds or overdue invoices, and what they owe."""
from datetime import date

from flask import Blueprint, render_template, request

from .. import login_required
from ..credit import exposure
from ..db import placeholders, q
from ..ledger import company
from ..mailer import configured

bp = Blueprint("credit", __name__, url_prefix="/credit")


@bp.route("/")
@login_required
def index():
    show = request.args.get("show", "attention")
    today = date.today().isoformat()
    sql = "SELECT * FROM partners WHERE is_customer = 1 AND active = 1"
    if show != "all":
        # customers with a limit or hold, and any with an invoice past due
        sql += (" AND (credit_hold = 1 OR COALESCE(credit_limit, 0) > 0 OR id IN (SELECT partner_id FROM invoices "
                "WHERE kind = 'out_invoice' AND state = 'posted' AND COALESCE(due_date, date) < ?))")
    partners = q(sql + " ORDER BY name_en", [today] if show != "all" else [])
    c = company()
    rows = []
    for p in partners:
        ex = exposure(p["id"], today)
        limit = p["credit_limit"] or 0
        status = "hold" if p["credit_hold"] else "over" if limit and ex["total"] > limit else \
            "late" if c["credit_overdue_days"] and ex["oldest_days"] > c["credit_overdue_days"] else \
            "overdue" if ex["overdue"] > 0 else "ok"
        if show == "attention" and status == "ok" and not limit:
            continue
        rows.append({"p": p, "ex": ex, "limit": limit, "available": limit - ex["total"] if limit else None,
                     "used": min(ex["total"] * 100 / limit, 100) if limit and ex["total"] > 0 else 0, "status": status})
    order = {"hold": 0, "over": 1, "late": 2, "overdue": 3, "ok": 4}
    rows.sort(key=lambda r: (order[r["status"]], -r["ex"]["overdue"], -r["ex"]["total"]))
    ids = [r["p"]["id"] for r in rows]
    last = {r["partner_id"]: r["sent_at"] for r in q(
        f"SELECT partner_id, MAX(sent_at) AS sent_at FROM email_log WHERE state = 'sent' AND kind = 'reminder' "
        f"AND partner_id IN ({placeholders(ids) if ids else 'NULL'}) GROUP BY partner_id", ids)}
    totals = {k: sum(r["ex"][k] for r in rows) for k in ("balance", "orders", "total", "overdue")}
    return render_template("credit/index.html", rows=rows, show=show, last=last, totals=totals, cur=c["currency"],
                           policy=c["credit_policy"], overdue_days=c["credit_overdue_days"], mail_ready=configured(),
                           today=today)
