platform/modules/collections_exec.py

"""Collections executive module — the LIVE Biweekly Executive Collection Report.

Replicates the Excel "Royal Imports - Collection Report - Biweekly Executive" (Summary tab time-series
+ Overdue (FFS)/(RI) lists) but computed live from Odoo (read-only).

AR as-of any date D = the receivable-account LEDGER balance: Σ balance of posted receivable move lines
dated <= D (customer payments post a credit to the receivable account on the payment date, so the
running account balance IS the AR). Validated to ~1% of the hardcoded report; today's value ties to
Σ amount_residual exactly. BU split is by the CUSTOMER (res.partner.team_id 5=Fisch/FFS, 6=Royal/RI),
NOT the invoice team (most invoices carry a generic team). Current aging uses exact per-invoice
amount_residual; historical aging is a FIFO approximation conserved toward the netting total (the
report's exact buckets come from Odoo's specific reconciliation, which the API cannot reconstruct —
account.partial.reconcile.max_date errors server-side). READ-ONLY.
"""
import sys
import datetime as dt
from collections import defaultdict
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
import core.odoo as O
import core.periods as P

FFS, RI = 5, 6
BU_OF = {FFS: 'FFS', RI: 'RI'}
AGE = ['At Date', '01-30', '31-60', '61-90', '91-120', '>120']
# Overdue-list aging (the report folds not-due + 0-30 days into "At Date"; overdue starts at 31 days).
AGE_LIST = ['At Date', '31-60', '61-90', '91-120', '>120']

# Cash doesn't settle same-day, so the report's "as of" / ending period lags real-time by this many
# days: the last few days of receipts haven't cleared in the bank yet and would understate collections
# and overstate receivables. The ending period is therefore today - SETTLE_OFFSET_DAYS (real-time).
SETTLE_OFFSET_DAYS = 7

# Targets from the report (col U). (goal_value, 'lower'|'higher' is-better)
GOALS = {
    'recv_FFS': (427708.0, 'lower'), 'recv_RI': (150789.0, 'lower'),
    'dso': (30.0, 'lower'), 'dso_FFS': (30.0, 'lower'), 'dso_RI': (30.0, 'lower'),
    'odpct_FFS': (10.0, 'lower'), 'odpct_RI': (10.0, 'lower'), 'odpct_total': (10.0, 'lower'),
}


def _d(s):
    try:
        return dt.date.fromisoformat(str(s)[:10])
    except Exception:
        return None


def _as_of(t=None):
    """The report's as-of / ending-period date: real-time minus the settlement lag (default), or an
    explicit override if a caller passes one. Using plain 'today' would understate the most recent
    receipts (cash takes ~SETTLE_OFFSET_DAYS to clear), so the ending period is offset back by it."""
    return t or (P.today() - dt.timedelta(days=SETTLE_OFFSET_DAYS))


def _bucket(days):
    if days <= 0:
        return 'At Date'
    if days <= 30:
        return '01-30'
    if days <= 60:
        return '31-60'
    if days <= 90:
        return '61-90'
    if days <= 120:
        return '91-120'
    return '>120'


def _lbucket(days):
    """Overdue-list bucket — not-due or within 30 days is 'At Date'; overdue starts at 31."""
    if days <= 30:
        return 'At Date'
    if days <= 60:
        return '31-60'
    if days <= 90:
        return '61-90'
    if days <= 120:
        return '91-120'
    return '>120'


def periods(t=None, n=14, step=14):
    """n biweekly periods ending at t (latest last). Each {label, end(date), beg(date), year, month}."""
    t = _as_of(t)
    out = []
    for i in range(n):
        end = t - dt.timedelta(days=step * i)
        out.append({'label': end.strftime('%m/%d'), 'end': end, 'beg': end - dt.timedelta(days=step - 1),
                    'year': end.year, 'month': end.month})
    return list(reversed(out))


def pull(t=None):
    """The heavy one-time read: receivable ledger (invoice lines + reduction lines) + partner master.
    Records are normalized to plain dicts with parsed date strings + the customer's BU."""
    rec = [('account_id.account_type', '=', 'asset_receivable'), ('parent_state', '=', 'posted')]
    inv = O.search_read('account.move.line',
                        rec + [('move_id.move_type', 'in', ['out_invoice', 'out_refund'])],
                        ['date', 'date_maturity', 'balance', 'amount_residual', 'partner_id'], limit=300000)
    red = O.search_read('account.move.line',
                        rec + [('move_id.move_type', 'not in', ['out_invoice', 'out_refund'])],
                        ['date', 'balance', 'partner_id'], limit=300000)
    ex = set(O.excluded_partner_ids())
    pids = sorted({O.m2o_id(r['partner_id']) for r in inv + red
                   if r.get('partner_id') and O.m2o_id(r['partner_id']) not in ex})
    parts = {p['id']: p for p in O.search_read('res.partner', [('id', 'in', pids)],
             ['name', 'team_id', 'user_id', 'property_payment_term_id'])}

    def bu(pid):
        return BU_OF.get(O.m2o_id(parts.get(pid, {}).get('team_id')))
    I, R = [], []
    for r in inv:
        pid = O.m2o_id(r['partner_id'])
        if pid in ex or pid not in parts:
            continue
        I.append({'pid': pid, 'bu': bu(pid), 'date': str(r.get('date') or '')[:10],
                  'due': str(r.get('date_maturity') or r.get('date') or '')[:10],
                  'bal': r.get('balance') or 0.0, 'resid': r.get('amount_residual') or 0.0})
    for r in red:
        pid = O.m2o_id(r['partner_id'])
        if pid in ex or pid not in parts:
            continue
        R.append({'pid': pid, 'bu': bu(pid), 'date': str(r.get('date') or '')[:10],
                  'bal': r.get('balance') or 0.0})
    return {'inv': I, 'red': R, 'parts': parts,
            'pulled_at': dt.datetime.now().strftime('%Y-%m-%d %H:%M')}


# ---------------------------------------------------------------- as-of-date primitives
def _recv_by_partner(data, D):
    """AR netting as-of D, per partner = Σ(invoice + reduction balances dated <= D)."""
    bal = defaultdict(float)
    for r in data['inv']:
        if r['date'] <= D:
            bal[r['pid']] += r['bal']
    for r in data['red']:
        if r['date'] <= D:
            bal[r['pid']] += r['bal']
    return bal


def _agg_bu(by_partner, data):
    out = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0}
    for pid, v in by_partner.items():
        bu = BU_OF.get(O.m2o_id(data['parts'].get(pid, {}).get('team_id')))
        if bu:
            out[bu] += v
        out['total'] += v
    return out


def _aging(data, D, recv=None, exact=False):
    """Aging buckets ($) by BU as-of D, + overdue total by BU. exact=True uses live per-invoice
    amount_residual (current period); else FIFO (payments applied oldest-due-first). When `recv`
    (netting by BU) is given, the buckets are conserved to it — the difference (unapplied payments /
    credits sitting in the receivable account, not tied to an invoice) lands in 'At Date'."""
    Dd = _d(D)
    inv_by = defaultdict(list)
    red_by = defaultdict(float)
    for r in data['inv']:
        if r['date'] <= D:
            inv_by[r['pid']].append(r)
    if not exact:
        for r in data['red']:
            if r['date'] <= D:
                red_by[r['pid']] += r['bal']
    out = {'FFS': {b: 0.0 for b in AGE}, 'RI': {b: 0.0 for b in AGE}}
    overdue = {'FFS': 0.0, 'RI': 0.0}
    for pid, invs in inv_by.items():
        bu = invs[0]['bu']
        if bu not in out:
            continue
        if exact:
            for r in invs:
                amt = r['resid']
                if abs(amt) < 1e-6:
                    continue
                days = (Dd - (_d(r['due']) or Dd)).days
                out[bu][_bucket(days)] += amt
                if days > 30 and amt > 0:
                    overdue[bu] += amt
        else:
            pay = -red_by.get(pid, 0.0)
            pos = []
            for r in invs:
                if r['bal'] >= 0:
                    pos.append([r['due'], r['bal']])
                else:
                    pay += -r['bal']          # credit notes act like reductions
            pos.sort(key=lambda x: x[0])      # oldest due first
            for it in pos:
                if pay <= 0:
                    break
                take = min(pay, it[1])
                it[1] -= take
                pay -= take
            for due, amt in pos:
                if amt < 1e-6:
                    continue
                days = (Dd - (_d(due) or Dd)).days
                out[bu][_bucket(days)] += amt
                if days > 30 and amt > 0:
                    overdue[bu] += amt
    if recv is not None:
        for bu in ('FFS', 'RI'):
            out[bu]['At Date'] += recv.get(bu, 0.0) - sum(out[bu].values())
    return out, overdue


def _ltm_invoiced(data, D):
    """Net invoiced (out_invoice − out_refund) in the trailing 365 days ending D, by BU — for DSO."""
    lo = (_d(D) - dt.timedelta(days=365)).isoformat()
    out = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0}
    for r in data['inv']:
        if lo < r['date'] <= D:
            if r['bu']:
                out[r['bu']] += r['bal']
            out['total'] += r['bal']
    return out


def _flows(data, beg, end):
    """New sales (net invoiced) + new collection (payments) in [beg, end], by BU."""
    b, e = beg.isoformat(), end.isoformat()
    sales = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0}
    coll = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0}
    for r in data['inv']:
        if b <= r['date'] <= e and r['bu']:
            sales[r['bu']] += r['bal']
            sales['total'] += r['bal']
    for r in data['red']:
        if b <= r['date'] <= e and r['bu']:
            coll[r['bu']] += -r['bal']
            coll['total'] += -r['bal']
    return sales, coll


def _last_payment(data):
    lp = {}
    for r in data['red']:
        if r['bal'] < 0 and r['date']:
            if r['pid'] not in lp or r['date'] > lp[r['pid']]:
                lp[r['pid']] = r['date']
    return lp


def _idle(data, D, lp, recv_pp):
    Dd = _d(D)
    out = {'FFS': {'IDLE3': 0, 'IDLE6': 0, 'IDLE12': 0, 'total': 0},
           'RI': {'IDLE3': 0, 'IDLE6': 0, 'IDLE12': 0, 'total': 0}}
    for pid, bal in recv_pp.items():
        if bal <= 1:
            continue
        bu = BU_OF.get(O.m2o_id(data['parts'].get(pid, {}).get('team_id')))
        if bu not in out:
            continue
        last = lp.get(pid)
        months = ((Dd - _d(last)).days / 30.0) if last else 999
        tier = 'IDLE12' if months > 12 else 'IDLE6' if months > 6 else 'IDLE3' if months > 3 else None
        if tier:
            out[bu][tier] += 1
            out[bu]['total'] += 1
    return out


def _is_cod(term_name):
    return any(k in (term_name or '').lower() for k in ('immediate', 'cod', 'cash'))


def _credit_terms(data, recv_pp):
    out = {'FFS': {'COD': 0, 'TOP': 0, 'total': 0}, 'RI': {'COD': 0, 'TOP': 0, 'total': 0}}
    for pid, bal in recv_pp.items():
        if bal <= 1:
            continue
        p = data['parts'].get(pid, {})
        bu = BU_OF.get(O.m2o_id(p.get('team_id')))
        if bu not in out:
            continue
        out[bu]['COD' if _is_cod(O.m2o_name(p.get('property_payment_term_id'))) else 'TOP'] += 1
        out[bu]['total'] += 1
    return out


# ---------------------------------------------------------------- assembly
def summary(data, t=None):
    """The biweekly time-series — a list of per-period metric dicts (latest last)."""
    t = _as_of(t)
    lp = _last_payment(data)
    pers = periods(t)
    rows = []
    for i, pr in enumerate(pers):
        D = pr['end'].isoformat()
        recv_pp = _recv_by_partner(data, D)
        recv = _agg_bu(recv_pp, data)
        aging, overdue = _aging(data, D, recv, exact=(i == len(pers) - 1))
        ltm = _ltm_invoiced(data, D)
        sales, coll = _flows(data, pr['beg'], pr['end'])
        dso = {bu: (recv[bu] / (ltm[bu] / 365.0)) if ltm.get(bu) else None for bu in ('FFS', 'RI', 'total')}
        rows.append({'period': pr, 'recv': recv, 'overdue': overdue, 'aging': aging, 'ltm': ltm,
                     'dso': dso, 'sales': sales, 'coll': coll,
                     'idle': _idle(data, D, lp, recv_pp), 'terms': _credit_terms(data, recv_pp)})
    for i, r in enumerate(rows):                       # change in receivable vs prior period end
        prev = rows[i - 1]['recv'] if i else None
        r['change'] = {bu: (r['recv'][bu] - prev[bu]) if prev else None for bu in ('FFS', 'RI', 'total')}
    return rows


def overdue_list(data, team, t=None):
    """Current (as-of t) ranked overdue customers for a BU (5=FFS / 6=RI), exact per-invoice."""
    t = _as_of(t)
    Dd = t
    bu = BU_OF.get(team)
    lp = _last_payment(data)
    by = {}
    for r in data['inv']:
        if r['bu'] != bu:
            continue
        amt = r['resid']
        if abs(amt) < 1e-6:
            continue
        e = by.setdefault(r['pid'], dict({'pid': r['pid'], 'total': 0.0, 'overdue': 0.0}, **{b: 0.0 for b in AGE_LIST}))
        e['total'] += amt
        days = (Dd - (_d(r['due']) or Dd)).days
        e[_lbucket(days)] += amt
        if days > 30 and amt > 0:
            e['overdue'] += amt
    tot_od = sum(e['overdue'] for e in by.values() if e['overdue'] > 0) or 1.0
    rows = []
    for pid, e in by.items():
        if e['overdue'] <= 0:
            continue
        p = data['parts'].get(pid, {})
        last = lp.get(pid)
        months = ((Dd - _d(last)).days / 30.0) if last else 999
        idle = 'IDLE12' if months > 12 else 'IDLE6' if months > 6 else 'IDLE3' if months > 3 else 'Active'
        term = O.m2o_name(p.get('property_payment_term_id'))
        rows.append(dict({'customer': p.get('name') or '(unknown)', 'odoo_id': pid,
                          'sales_rep': O.m2o_name(p.get('user_id')) or '',
                          'top': 'COD' if _is_cod(term) else (term or 'TOP'),
                          'overdue': e['overdue'], 'overdue_pct': e['overdue'] / tot_od * 100,
                          'idle': idle, 'total_recv': e['total']}, **{b: e[b] for b in AGE_LIST}))
    rows.sort(key=lambda x: -x['overdue'])
    for i, r in enumerate(rows, 1):
        r['no'] = i
    return rows


def goal_status(key, value):
    """('goal', 'Achieved'|'Unmet') for a metric vs the report's hardcoded target, or (None, None)."""
    g = GOALS.get(key)
    if not g or value is None:
        return None, None
    target, better = g
    ok = (value <= target) if better == 'lower' else (value >= target)
    return target, ('Achieved' if ok else 'Unmet')


def validate(data, t=None):
    """Aging buckets (conserved) tie to BU receivable; the Overdue list total ties to the summary's
    current overdue (the two independent code paths agree)."""
    t = _as_of(t)
    D = t.isoformat()
    recv = _agg_bu(_recv_by_partner(data, D), data)
    aging, overdue = _aging(data, D, recv, exact=True)
    ag_total = sum(aging['FFS'].values()) + sum(aging['RI'].values())
    checks = [{'check': 'Aging buckets (FFS+RI) == receivable (FFS+RI)',
               'a': round(ag_total, 2), 'b': round(recv['FFS'] + recv['RI'], 2),
               'gap': round(ag_total - (recv['FFS'] + recv['RI']), 2),
               'ok': abs(ag_total - (recv['FFS'] + recv['RI'])) <= 1.0}]
    od_list = sum(r['overdue'] for r in overdue_list(data, FFS, t)) + sum(r['overdue'] for r in overdue_list(data, RI, t))
    od_sum = overdue['FFS'] + overdue['RI']
    checks.append({'check': 'Overdue list total == summary current overdue',
                   'a': round(od_list, 2), 'b': round(od_sum, 2), 'gap': round(od_list - od_sum, 2),
                   'ok': abs(od_list - od_sum) <= 1.0})
    return checks