platform/modules/spend.py

"""Spend & Payables — the expense-waste module (cost-waste brief recs 1/3/4): the first
systematic analysis of vendor bills on this platform.

  1. TERMS CAPTURE  — per supplier: effective terms (per-bill due-date gap — partner-level terms
     are sparsely populated) vs actual bill→settlement days (payable full-reconcile counterpart
     date). Paying EARLY = free cash surrendered (the mirror of the receivables free-credit
     finding). China/import suppliers are deposit-based — DPO stretch is structurally capped, so
     the book is SPLIT by supplier country; the verb is pay-to-TERMS, never pay-late.
  2. DUPLICATE BILLS — same supplier + same amount (±$1) within 45 days, or same non-empty
     vendor ref. Classic AP recovery: 0.1-0.5% of spend.
  3. SPEND CUBE     — NON-product bill lines (Stock Interim / inventory accounts excluded) by
     GL account × supplier × month: category fragmentation (consolidate + rebid) and
     MAVERICK % = lines that never touched a PO (no purchase_line_id).
  4. FREIGHT RECOVERY — outbound freight PAID (the 'Freight Out' expense account) vs freight
     BILLED to customers (freight/shipping lines on customer invoices). NAW: distributors
     recapture only ~70%.

READ-ONLY. Supplier names normalized with a dependency-free suffix-strip (upgrade path:
RapidFuzz+cleanco per the brief).
"""
import re
import datetime as dt

import core.odoo as O
import core.periods as P

DUP_WINDOW_DAYS = 45
EARLY_GRACE_DAYS = 5           # paying ≤5d early is noise, not a finding
FREIGHT_OUT_PAT = re.compile(r'freight out|freight-out', re.I)
FREIGHT_BILL_PAT = re.compile(r'freight|shipping|delivery', re.I)
# product-side interim/inventory accounts excluded from the OPEX cube
PRODUCT_ACC_PAT = re.compile(r'stock interim|inventory|goods received', re.I)

_SUFFIX = re.compile(r'\b(inc|llc|ltd|corp|corporation|co|company|s\.?a\.?|gmbh)\b\.?', re.I)


def _norm_supplier(name):
    s = _SUFFIX.sub('', str(name or '').upper())
    return re.sub(r'[^A-Z0-9 ]', '', s).strip() or str(name or '')


def _chunk(ids, n=2000):
    ids = list(ids)
    for i in range(0, len(ids), n):
        yield ids[i:i + n]


def _bills(lf, lt):
    return O.search_read('account.move',
                         [('move_type', '=', 'in_invoice'), ('state', '=', 'posted'),
                          ('invoice_date', '>=', lf), ('invoice_date', '<=', lt)],
                         ['partner_id', 'invoice_date', 'invoice_date_due', 'amount_total',
                          'amount_untaxed', 'ref', 'payment_state', 'name'])


def terms_capture(t=None):
    """Per-supplier payment discipline over the LTM + the duplicate-bill worklist."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    bills = _bills(lf, lt)

    # settlement date per bill: payable line's full-reconcile group, max counterpart date
    move_ids = [b['id'] for b in bills]
    pay_lines = []
    for ch in _chunk(move_ids):
        pay_lines += O.search_read('account.move.line',
                                   [('move_id', 'in', ch),
                                    ('account_id.account_type', '=', 'liability_payable'),
                                    ('full_reconcile_id', '!=', False)],
                                   ['move_id', 'full_reconcile_id'])
    fr_by_move = {O.m2o_id(l['move_id']): O.m2o_id(l['full_reconcile_id']) for l in pay_lines}
    settle = {}
    for ch in _chunk(list(set(fr_by_move.values())), 4000):
        for l in O.search_read('account.move.line', [('full_reconcile_id', 'in', ch)],
                               ['full_reconcile_id', 'date']):
            k = O.m2o_id(l['full_reconcile_id'])
            d = str(l.get('date') or '')[:10]
            if k and d:
                settle[k] = max(settle.get(k, ''), d)

    # supplier country for the import/domestic split
    pids = list({O.m2o_id(b['partner_id']) for b in bills})
    country = {}
    for ch in _chunk(pids):
        for r in O.search_read('res.partner', [('id', 'in', ch)], ['country_id']):
            country[r['id']] = O.m2o_name(r.get('country_id')) or '(none)'

    per = {}
    for b in bills:
        pid = O.m2o_id(b['partner_id'])
        inv_d = str(b.get('invoice_date') or '')[:10]
        due_d = str(b.get('invoice_date_due') or '')[:10]
        if not pid or not inv_d:
            continue
        e = per.setdefault(pid, {'pid': pid, 'supplier': O.m2o_name(b['partner_id']),
                                 'country': country.get(pid, '(none)'), 'bills': 0,
                                 'spend': 0.0, 'terms': [], 'actual': [],
                                 'early_cash': 0.0, 'early_bills': 0})
        e['bills'] += 1
        e['spend'] += b.get('amount_total') or 0.0
        terms_days = None
        if due_d and due_d >= inv_d:
            terms_days = (dt.date.fromisoformat(due_d) - dt.date.fromisoformat(inv_d)).days
            e['terms'].append(terms_days)
        fr = fr_by_move.get(b['id'])
        pay_d = settle.get(fr, '') if fr else ''
        if pay_d and pay_d >= inv_d:
            actual = (dt.date.fromisoformat(pay_d) - dt.date.fromisoformat(inv_d)).days
            e['actual'].append(actual)
            if terms_days is not None and actual < terms_days - EARLY_GRACE_DAYS:
                early = terms_days - actual
                e['early_cash'] += early / 365.0 * (b.get('amount_total') or 0.0)
                e['early_bills'] += 1

    def _med(v):
        v = sorted(v)
        return v[len(v) // 2] if v else None
    rows = []
    for e in per.values():
        seg = ('(country unset)' if e['country'] == '(none)'
               else 'import' if e['country'] not in ('United States', 'Canada')
               else 'domestic')
        rows.append({'pid': e['pid'], 'supplier': e['supplier'], 'country': e['country'],
                     'segment': seg,
                     'bills': e['bills'], 'spend': e['spend'],
                     'terms_days': _med(e['terms']), 'actual_days': _med(e['actual']),
                     'early_bills': e['early_bills'], 'early_cash': e['early_cash']})
    rows.sort(key=lambda x: -x['early_cash'])

    # duplicate-bill candidates
    by_partner = {}
    for b in bills:
        by_partner.setdefault(O.m2o_id(b['partner_id']), []).append(b)
    dups = []
    for pid, bs in by_partner.items():
        bs = sorted(bs, key=lambda x: str(x.get('invoice_date') or ''))
        for i, a in enumerate(bs):
            for c in bs[i + 1:]:
                da = str(a.get('invoice_date') or '')[:10]
                dc = str(c.get('invoice_date') or '')[:10]
                if not da or not dc:
                    continue
                gap = (dt.date.fromisoformat(dc) - dt.date.fromisoformat(da)).days
                if gap > DUP_WINDOW_DAYS:
                    break
                amt_a, amt_c = a.get('amount_total') or 0.0, c.get('amount_total') or 0.0
                same_ref = (a.get('ref') and a.get('ref') == c.get('ref'))
                # same vendor ref = strong signal at any gap; without a ref, only a same-amount
                # bill within 7 days counts (monthly recurring bills — rent, loans, standing
                # POs — land ~28-31 days apart and are NOT duplicates)
                if abs(amt_a - amt_c) <= 1.0 and amt_a > 100 and (same_ref or gap <= 7):
                    dups.append({'supplier': O.m2o_name(a['partner_id']),
                                 'bill_a': a['name'], 'bill_b': c['name'],
                                 'date_a': da, 'date_b': dc, 'amount': amt_a,
                                 'same_ref': 'yes' if same_ref else '',
                                 'ref': str(a.get('ref') or '')[:24]})
    dups.sort(key=lambda x: -x['amount'])
    return {'rows': rows, 'dups': dups,
            'n_bills': len(bills), 'spend_total': sum(b.get('amount_total') or 0 for b in bills),
            'early_cash_total': sum(r['early_cash'] for r in rows),
            'dup_exposure': sum(d['amount'] for d in dups),
            'window': (lf, lt)}


def _bill_lines(lf, lt):
    lines = []
    # account.move.line.display_type is a SELECTION ('product'/'tax'/'payment_term'/…),
    # not a boolean — '= False' matches nothing (found live 2026-07-05)
    got = O.search_read('account.move.line',
                        [('move_id.move_type', '=', 'in_invoice'),
                         ('parent_state', '=', 'posted'),
                         ('move_id.invoice_date', '>=', lf),
                         ('move_id.invoice_date', '<=', lt),
                         ('display_type', '=', 'product'),
                         ('price_subtotal', '!=', 0)],
                        ['partner_id', 'account_id', 'price_subtotal', 'purchase_line_id',
                         'product_id', 'date'])
    acc_ids = list({O.m2o_id(l['account_id']) for l in got if l.get('account_id')})
    atype = {}
    for ch in _chunk(acc_ids):
        for a in O.search_read('account.account', [('id', 'in', ch)], ['account_type']):
            atype[a['id']] = a.get('account_type') or ''
    for l in got:
        l['account'] = O.m2o_name(l.get('account_id'))
        l['atype'] = atype.get(O.m2o_id(l.get('account_id')), '')
        lines.append(l)
    return lines


def spend_cube(t=None):
    """OPEX spend by GL category × supplier(normalized), with fragmentation and no-PO %.
    Scope = expense-type accounts ONLY (account_type 'expense'/'expense_depreciation'):
    inventory-interim (asset) and loan/advance (liability) bill lines are excluded by TYPE,
    and COGS (expense_direct_cost) belongs to the product side. Note: this company runs no
    PO process for indirect spend, so no-PO% ≈ 100% by construction — it becomes a signal
    only if POs are ever adopted for indirect purchases."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    lines = [l for l in _bill_lines(lf, lt)
             if l.get('atype') in ('expense', 'expense_depreciation')]
    cats, sup_cat = {}, {}
    for l in lines:
        acc = l['account']
        amt = l.get('price_subtotal') or 0.0
        sup = _norm_supplier(O.m2o_name(l.get('partner_id')))
        c = cats.setdefault(acc, {'category': acc, 'spend': 0.0, 'lines': 0,
                                  'maverick': 0.0, 'suppliers': set()})
        c['spend'] += amt
        c['lines'] += 1
        c['suppliers'].add(sup)
        if not l.get('purchase_line_id'):
            c['maverick'] += amt
        k = (acc, sup)
        sup_cat[k] = sup_cat.get(k, 0.0) + amt
    rows = []
    for c in cats.values():
        n_sup = len(c['suppliers'])
        top = sorted(((s, v) for (a, s), v in sup_cat.items() if a == c['category']),
                     key=lambda kv: -kv[1])
        rows.append({'category': c['category'], 'spend': c['spend'], 'lines': c['lines'],
                     'n_suppliers': n_sup,
                     'maverick_pct': (c['maverick'] / c['spend'] * 100) if c['spend'] else 0.0,
                     'top_supplier': top[0][0][:30] if top else '',
                     'top_share_pct': (top[0][1] / c['spend'] * 100) if (top and c['spend']) else 0.0,
                     'consolidate': ('yes' if n_sup >= 3 and c['spend'] > 10000 else '')})
    rows.sort(key=lambda x: -x['spend'])
    return {'rows': rows,
            'spend_total': sum(r['spend'] for r in rows),
            'maverick_total': sum(r['spend'] * r['maverick_pct'] / 100 for r in rows),
            'frag_categories': [r for r in rows if r['consolidate']],
            'window': (lf, lt)}


def freight_recovery(t=None):
    """Outbound freight paid (the 'Freight Out' expense account) vs freight billed to
    customers (freight/shipping/delivery lines on out_invoices), monthly + the headline
    recovery %. Company-level."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    paid_lines = [l for l in _bill_lines(lf, lt)
                  if l['account'] and FREIGHT_OUT_PAT.search(l['account'])]
    paid_m = {}
    for l in paid_lines:
        m = str(l.get('date') or '')[:7]
        paid_m[m] = paid_m.get(m, 0.0) + (l.get('price_subtotal') or 0.0)

    billed = O.search_read('account.move.line',
                           [('move_id.move_type', '=', 'out_invoice'),
                            ('parent_state', '=', 'posted'),
                            ('move_id.invoice_date', '>=', lf),
                            ('move_id.invoice_date', '<=', lt),
                            ('display_type', '=', 'product')],
                           ['account_id', 'product_id', 'price_subtotal', 'date',
                            'partner_id'])
    billed_lines = [l for l in billed
                    if FREIGHT_BILL_PAT.search(O.m2o_name(l.get('product_id')) or '')
                    or FREIGHT_BILL_PAT.search(O.m2o_name(l.get('account_id')) or '')]
    billed_m, billed_cust = {}, {}
    for l in billed_lines:
        m = str(l.get('date') or '')[:7]
        amt = l.get('price_subtotal') or 0.0
        billed_m[m] = billed_m.get(m, 0.0) + amt
        pid = O.m2o_id(l.get('partner_id'))
        if pid:
            billed_cust[pid] = billed_cust.get(pid, 0.0) + amt
    months = sorted(set(paid_m) | set(billed_m))
    monthly = [{'month': m, 'paid': paid_m.get(m, 0.0), 'billed': billed_m.get(m, 0.0)}
               for m in months]
    paid_total = sum(paid_m.values())
    billed_total = sum(billed_m.values())
    return {'monthly': monthly, 'paid_total': paid_total, 'billed_total': billed_total,
            'recovery_pct': (billed_total / paid_total * 100) if paid_total else None,
            'n_billed_customers': len(billed_cust),
            'window': (lf, lt)}


def validate(t=None, team_id=None, pre=None):
    """(1) bill headline ties the server sum over the same domain; (2) the cube total +
    excluded product-account lines re-adds to the full bill-line total (nothing dropped
    silently)."""
    t = t or P.today()
    tc = pre or terms_capture(t)
    lf, lt = tc['window']
    srv = O.sum_field('account.move',
                      [('move_type', '=', 'in_invoice'), ('state', '=', 'posted'),
                       ('invoice_date', '>=', lf), ('invoice_date', '<=', lt)],
                      'amount_total')
    a = tc['spend_total']
    checks = [{'check': 'vendor-bill spend — client Σ == server Σ',
               'a': round(a, 2), 'b': round(srv, 2), 'gap': round(a - srv, 2),
               'ok': abs(a - srv) <= max(1.0, srv * 0.001)}]
    all_lines = _bill_lines(lf, lt)
    full = sum(l.get('price_subtotal') or 0 for l in all_lines)
    cube = spend_cube(t)
    excluded = sum(l.get('price_subtotal') or 0 for l in all_lines
                   if l.get('atype') not in ('expense', 'expense_depreciation'))
    lhs = cube['spend_total'] + excluded
    checks.append({'check': 'opex cube + non-expense-type lines == all bill lines',
                   'a': round(lhs, 2), 'b': round(full, 2), 'gap': round(lhs - full, 2),
                   'ok': abs(lhs - full) < 1.0})
    return checks