"""Statement of account of a customer or vendor, straight from the general ledger.

A statement shows every posted line on the partner's receivable (customer) or payable (vendor) accounts for the
period, with the opening balance, a running balance and an ageing of the open invoices. Branches with another
currency get their own section, since amounts in SAR and AED cannot be added.
"""
from collections import OrderedDict
from datetime import date

from .db import placeholders, q
from .ledger import INVOICE_KINDS, invoice_residuals

SIDES = {"customer": {"kind": "asset_receivable", "sign": 1, "invoices": ("out_invoice", "out_refund")},
         "vendor": {"kind": "liability_payable", "sign": -1, "invoices": ("in_invoice", "in_refund")}}
SHORT_LABEL = {"out_invoice": "Invoice", "out_refund": "Credit Note", "in_invoice": "Bill", "in_refund": "Refund"}
BUCKETS = [("current", "Not due"), ("b30", "1-30"), ("b60", "31-60"), ("b90", "61-90"), ("older", "90+")]


def bucket(days):
    return "current" if days <= 0 else "b30" if days <= 30 else "b60" if days <= 60 else "b90" if days <= 90 else "older"


def statement(partner_id, side, branch_ids, date_from, date_to):
    """[{currency, opening, lines, debit, credit, closing, ageing, unapplied}] — balances are what the customer owes
    us (customer) or what we owe the vendor (vendor)."""
    s = SIDES[side]
    if not branch_ids:
        return []
    ph = placeholders(branch_ids)
    base = (f"FROM move_lines ml JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id "
            f"JOIN branches b ON b.id = ml.branch_id WHERE m.state = 'posted' AND a.kind = ? AND ml.partner_id = ? "
            f"AND ml.branch_id IN ({ph})")
    args = [s["kind"], partner_id] + list(branch_ids)
    sections = OrderedDict()

    def section(cur):
        return sections.setdefault(cur, {"currency": cur, "opening": 0, "lines": [], "debit": 0, "credit": 0,
                                         "ageing": {k: 0 for k, _ in BUCKETS}, "branches": set()})

    for r in q(f"SELECT b.currency, SUM(ml.debit - ml.credit) AS bal {base} AND m.date < ? GROUP BY b.currency",
               args + [date_from]):
        section(r["currency"])["opening"] = r["bal"] * s["sign"]
    for r in _lines(base, args, date_from, date_to):
        sec = section(r["currency"])
        sec["branches"].add(r["bcode"])
        sec["lines"].append(r)
        sec["debit"] += r["debit"]
        sec["credit"] += r["credit"]
    for sec in sections.values():
        bal = sec["opening"]
        for r in sec["lines"]:
            bal += (r["debit"] - r["credit"]) * s["sign"]
            r["balance"] = bal
        sec["closing"] = bal
    # ageing of the invoices still open at the end of the period
    invs = q(f"SELECT i.*, b.currency AS branch_currency FROM invoices i JOIN branches b ON b.id = i.branch_id WHERE i.partner_id = ? "
             f"AND i.state = 'posted' AND i.date <= ? AND i.kind IN (?, ?) AND i.branch_id IN ({ph})",
             [partner_id, date_to, *s["invoices"]] + list(branch_ids))
    res = invoice_residuals([i["id"] for i in invs], as_of=date_to, base=True)
    end = date.fromisoformat(date_to)
    open_items = []
    for inv in invs:
        r = res.get(inv["id"], 0)
        if not r:
            continue
        amt = r * INVOICE_KINDS[inv["kind"]]["sign"]
        days = (end - date.fromisoformat(inv["due_date"] or inv["date"])).days
        section(inv["branch_currency"])["ageing"][bucket(days)] += amt
        open_items.append({**dict(inv), "residual": amt, "days": days, "doc_currency": inv["currency"],
                           "currency": inv["branch_currency"]})
    for sec in sections.values():
        # payments not matched to an invoice, opening balances, journal entries...
        sec["unapplied"] = sec["closing"] - sum(sec["ageing"].values())
        sec["overdue"] = sum(v for k, v in sec["ageing"].items() if k != "current")
        sec["open_items"] = sorted([o for o in open_items if o["currency"] == sec["currency"]],
                                   key=lambda o: o["due_date"] or o["date"])
        sec["branches"] = sorted(sec["branches"])
    return list(sections.values())


def _lines(base, args, date_from, date_to):
    sql = base.replace("WHERE", "LEFT JOIN invoices i ON i.move_id = m.id LEFT JOIN payments p ON p.move_id = m.id WHERE",
                       1)
    rows = q(f"""SELECT ml.*, m.date, m.name AS move_name, m.ref, m.narration, m.move_type, b.currency AS branch_currency,
                        b.code AS bcode, i.id AS invoice_id, i.number AS inv_number, i.kind AS inv_kind, i.due_date,
                        i.ref AS inv_ref, p.id AS payment_id, p.name AS pay_name, p.ref AS pay_ref
                 {sql} AND m.date BETWEEN ? AND ? ORDER BY m.date, m.id, ml.id""", args + [date_from, date_to])
    out = []
    for r in rows:
        r = dict(r)
        r["doc_currency"], r["currency"] = r["currency"], r["branch_currency"]
        if r["invoice_id"]:
            r["number"], r["doc"] = r["inv_number"], SHORT_LABEL[r["inv_kind"]]
            r["detail"] = r["inv_ref"] or ""
        elif r["payment_id"]:
            r["number"], r["doc"] = r["pay_name"], "Payment"
            r["detail"] = r["pay_ref"] or ""
        else:
            r["number"], r["doc"] = r["move_name"], "Journal Entry"
            r["detail"] = r["label"] or r["ref"] or r["narration"] or ""
        out.append(r)
    return out


def balances(side, branch_ids, as_of, partner_ids=None):
    """{partner_id: {currency: balance}} at a date, for the statements list."""
    s = SIDES[side]
    if not branch_ids:
        return {}
    sql = (f"SELECT ml.partner_id, b.currency, SUM(ml.debit - ml.credit) AS bal FROM move_lines ml "
           f"JOIN moves m ON m.id = ml.move_id JOIN accounts a ON a.id = ml.account_id JOIN branches b ON b.id = ml.branch_id "
           f"WHERE m.state = 'posted' AND a.kind = ? AND ml.partner_id IS NOT NULL AND m.date <= ? "
           f"AND ml.branch_id IN ({placeholders(branch_ids)})")
    args = [s["kind"], as_of] + list(branch_ids)
    if partner_ids:
        sql += f" AND ml.partner_id IN ({placeholders(partner_ids)})"
        args += list(partner_ids)
    out = {}
    for r in q(sql + " GROUP BY ml.partner_id, b.currency", args):
        if r["bal"]:
            out.setdefault(r["partner_id"], {})[r["currency"]] = r["bal"] * s["sign"]
    return out
