platform/modules/o2c.py

"""Order-to-Cash timing — where the days hide, by stage, each with its owner:

  order → ship      (warehouse)   sale.order.date_order → effective_date (delivery complete)
  ship → invoice    (admin — a FREE DSO leak; links to the DNI list in Data Health)
  invoice → paid    (collections) invoice date → full-reconcile settlement date
                                  (same OCA partner_time_to_pay semantics as ar.days_to_pay)

Plus the terms-gap rollup: per payment term, contractual days vs $-weighted actual days —
"are the TERMS wrong?" — with the stranded free-credit $ it represents (reuses the validated
per-customer machinery in modules/ar.days_to_pay: dominant term, excess days, free credit).
Medians (not means) per stage — one whale order must not move the story.
"""
import datetime as dt

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


def _median(v):
    v = sorted(v)
    n = len(v)
    if not n:
        return None
    return v[n // 2] if n % 2 else (v[n // 2 - 1] + v[n // 2]) / 2.0


def _d(s):
    s = str(s or '')[:10]
    try:
        return dt.date.fromisoformat(s)
    except ValueError:
        return None


def stages(team_id=None, t=None):
    """Per-BU stage medians + monthly trend over the LTM. Coverage counts are honest:
    each stage only scores orders where both endpoints exist."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    ex = O.excluded_partner_ids()

    dom = [('state', 'in', ['sale', 'done']),
           ('date_order', '>=', f'{lf} 00:00:00'), ('date_order', '<=', f'{lt} 23:59:59')]
    dom.append(('team_id', '=', team_id) if team_id is not None
               else ('team_id', 'in', O.TEAM_IDS))
    if ex:
        dom.append(('partner_id', 'not in', list(ex)))
    orders = O.search_read('sale.order', dom,
                           ['name', 'date_order', 'effective_date', 'team_id'])
    by_name = {o['name']: o for o in orders}

    # invoices LTM joined by exact invoice_origin (multi-origin merges skipped — counted)
    inv_dom = [('move_type', '=', 'out_invoice'), ('state', '=', 'posted'),
               ('invoice_date', '>=', lf)]
    if ex:
        inv_dom.append(('partner_id', 'not in', list(ex)))
    invs = O.search_read('account.move', inv_dom,
                         ['invoice_origin', 'invoice_date', 'name'])
    first_inv = {}
    multi_origin = 0
    for iv in invs:
        og = (iv.get('invoice_origin') or '').strip()
        if not og:
            continue
        if ',' in og:
            multi_origin += 1
            continue
        d = str(iv.get('invoice_date') or '')[:10]
        if og in by_name and d and (og not in first_inv or d < first_inv[og]):
            first_inv[og] = d

    # invoice → settled (receivable full-reconcile max counterpart date), LTM invoices
    pay_dom = [('move_id.move_type', '=', 'out_invoice'), ('move_id.state', '=', 'posted'),
               ('account_id.account_type', '=', 'asset_receivable'),
               ('full_reconcile_id', '!=', False), ('date', '>=', lf)]
    if ex:
        pay_dom.append(('partner_id', 'not in', list(ex)))
    rec_lines = O.search_read('account.move.line', pay_dom,
                              ['date', 'full_reconcile_id'])
    fr_ids = list({O.m2o_id(l['full_reconcile_id']) for l in rec_lines})
    settle = {}
    # raw lines + local max — a grouped read here makes the server echo the full id-list
    # domain back per group (__domain) and dies with a MemoryError at 5k-id chunks
    for i in range(0, len(fr_ids), 5000):
        for l in O.search_read('account.move.line',
                               [('full_reconcile_id', 'in', fr_ids[i:i + 5000])],
                               ['full_reconcile_id', 'date']):
            k = O.m2o_id(l.get('full_reconcile_id'))
            d = str(l.get('date') or '')[:10]
            if k and d:
                settle[k] = max(settle.get(k, ''), d)
    # survivorship guard: a recent invoice only appears here if it is ALREADY reconciled,
    # so the newest cohort is all fast payers. Score only invoices ≥120d old (they've had
    # time to be slow); the honest label carries into the UI.
    pay_cutoff = (t - dt.timedelta(days=120)).isoformat()
    pay_days = []
    for l in rec_lines:
        i_d, s_d = _d(l.get('date')), _d(settle.get(O.m2o_id(l['full_reconcile_id'])))
        if i_d and s_d and s_d >= i_d and i_d.isoformat() <= pay_cutoff:
            pay_days.append(((s_d - i_d).days, i_d))

    # assemble per-order stage observations
    obs = []          # {bu, month, ship_days?, inv_days?}
    for o in orders:
        od, sd = _d(o.get('date_order')), _d(o.get('effective_date'))
        bu = O.TEAM_NAMES.get(O.m2o_id(o.get('team_id')), '?')
        month = str(o.get('date_order') or '')[:7]
        ship = (sd - od).days if od and sd and sd >= od else None
        iv = _d(first_inv.get(o['name']))
        invd = (iv - sd).days if sd and iv and iv >= sd else None
        obs.append({'bu': bu, 'month': month, 'ship': ship, 'inv': invd})

    def _roll(rows, key):
        vals = [r[key] for r in rows if r.get(key) is not None]
        return _median(vals), len(vals)

    by_bu = []
    for bu in sorted({r['bu'] for r in obs}):
        sub = [r for r in obs if r['bu'] == bu]
        m_ship, n_ship = _roll(sub, 'ship')
        m_inv, n_inv = _roll(sub, 'inv')
        by_bu.append({'bu': bu, 'order_to_ship': m_ship, 'n_ship': n_ship,
                      'ship_to_invoice': m_inv, 'n_inv': n_inv})
    m_pay = _median([d for d, _ in pay_days])

    trend = []
    months = sorted({r['month'] for r in obs})
    for m in months:
        sub = [r for r in obs if r['month'] == m]
        s, ns = _roll(sub, 'ship')
        v, nv = _roll(sub, 'inv')
        p_vals = [d for d, i_d in pay_days if i_d.isoformat()[:7] == m]
        if ns >= 10 and s is not None:
            trend.append({'month': m, 'stage': 'order-to-ship', 'days': s})
        if nv >= 10 and v is not None:
            trend.append({'month': m, 'stage': 'ship-to-invoice', 'days': v})
        if len(p_vals) >= 10:
            trend.append({'month': m, 'stage': 'invoice-to-paid', 'days': _median(p_vals)})

    ship_all, n_ship_all = _roll(obs, 'ship')
    inv_all, n_inv_all = _roll(obs, 'inv')
    return {
        'by_bu': by_bu, 'pay_median': m_pay, 'n_pay': len(pay_days), 'trend': trend,
        'ship_median': ship_all, 'n_ship': n_ship_all,
        'inv_median': inv_all, 'n_inv': n_inv_all,
        'n_orders': len(orders), 'n_invoices': len(invs), 'multi_origin': multi_origin,
        'window': (lf, lt),
    }


def terms_gap(t=None):
    """Per payment term: contractual days vs invoiced-$-weighted actual days-to-pay and the
    stranded free-credit $ — rolled up from ar.days_to_pay's validated per-customer rows
    (dominant term per customer)."""
    dtp = ar.days_to_pay(t, limit=100000)
    rows = dtp['rows'] if isinstance(dtp, dict) else dtp
    per = {}
    for r in rows:
        term = r.get('terms') or '(none)'
        eff = r.get('avg_days_ytd') if r.get('avg_days_ytd') is not None else r.get('avg_days')
        if eff is None:
            continue
        e = per.setdefault(term, {'term': term, 'term_days': r.get('term_days') or 0,
                                  'w': 0.0, 'wd': 0.0, 'customers': 0, 'free_credit': 0.0,
                                  'open': 0.0})
        inv_w = max(r.get('open') or 0.0, 0.0) + 1.0   # weight by open AR (+1 floor so
                                                       # zero-AR customers still count)
        e['w'] += inv_w
        e['wd'] += eff * inv_w
        e['customers'] += 1
        e['free_credit'] += r.get('free_credit') or 0.0
        e['open'] += r.get('open') or 0.0
    out = []
    for e in per.values():
        actual = e['wd'] / e['w'] if e['w'] else None
        out.append({'term': e['term'], 'term_days': e['term_days'],
                    'actual_days': actual,
                    'gap_days': (actual - e['term_days']) if actual is not None else None,
                    'customers': e['customers'], 'open_ar': e['open'],
                    'free_credit': e['free_credit']})
    out.sort(key=lambda x: -(x['free_credit'] or 0))
    return out


def validate(t=None, team_id=None):
    """Pull-completeness both sides: LTM order count and posted-invoice count vs
    independent search_count on the same domains."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    ex = O.excluded_partner_ids()
    dom = [('state', 'in', ['sale', 'done']),
           ('date_order', '>=', f'{lf} 00:00:00'), ('date_order', '<=', f'{lt} 23:59:59')]
    dom.append(('team_id', '=', team_id) if team_id is not None
               else ('team_id', 'in', O.TEAM_IDS))
    if ex:
        dom.append(('partner_id', 'not in', list(ex)))
    b = stages(team_id, t)
    n_orders = O.get_odoo().search_count('sale.order', dom)
    inv_dom = [('move_type', '=', 'out_invoice'), ('state', '=', 'posted'),
               ('invoice_date', '>=', lf)]
    if ex:
        inv_dom.append(('partner_id', 'not in', list(ex)))
    n_inv = O.get_odoo().search_count('account.move', inv_dom)
    return [
        {'check': 'LTM orders — pull complete', 'a': b['n_orders'], 'b': n_orders,
         'gap': b['n_orders'] - n_orders, 'ok': b['n_orders'] == n_orders},
        {'check': 'LTM posted invoices — pull complete', 'a': b['n_invoices'], 'b': n_inv,
         'gap': b['n_invoices'] - n_inv, 'ok': b['n_invoices'] == n_inv},
    ]