platform/modules/returns.py

"""Returns module — the credit-note lens nobody reads: 14% of billing documents are refunds.

Governing question: WHERE DO RETURNS CONCENTRATE — WHICH SKUs (quality signal), WHICH
CUSTOMERS (behavior signal), WHICH CATEGORIES (product-line signal), WHICH SUPPLIERS
(sourcing-quality signal) AND WHICH AGENTS (book-behavior signal) — AND IS THE RATE MOVING?
Odoo has no return-reason field, so the concentration IS the diagnostic. Rates are always shown
next to raw $ — a big seller with average rate is noise; a small line with 4x the company rate
is the finding.

Supplier attribution: curated procurement map (default_code) first, else the DOMINANT vendor
from confirmed PO history (most units bought), else '(no supplier data)'. Agent attribution:
the customer's res.partner agent — credit-note team_id is NOT trustworthy.

Company-level (credit notes carry team_id=1 for everything). READ-ONLY.
"""
import sys
import datetime as dt
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
import core.odoo as O
import core.periods as P
import modules.customers as cust_mod
import modules.procurement as proc_mod

_EX_MOVE = []            # posted customer docs only


def _mdom(move_type, a, b):
    return [('move_type', '=', move_type), ('state', '=', 'posted'),
            ('invoice_date', '>=', a), ('invoice_date', '<=', b)]


def _ldom(move_type, a, b):
    return [('move_id.move_type', '=', move_type), ('parent_state', '=', 'posted'),
            ('move_id.invoice_date', '>=', a), ('move_id.invoice_date', '<=', b),
            ('product_id', '!=', False)]


def build(t=None):
    t = t or P.today()
    d12 = (t - dt.timedelta(days=365)).isoformat()
    d13m = (t.replace(day=1) - dt.timedelta(days=380)).replace(day=1).isoformat()
    ti = t.isoformat()

    # ---- headline + monthly (from the MOVES — count + untaxed value) -----------------------
    def _monthly(mt):
        out = {}
        for g in O.read_group('account.move', _mdom(mt, d13m, ti),
                              ['amount_untaxed:sum'], ['invoice_date:month'], lazy=False):
            k = g.get('invoice_date:month')
            out[str(k)] = {'value': g.get('amount_untaxed') or 0.0, 'n': g.get('__count') or 0}
        return out
    ref_m, inv_m = _monthly('out_refund'), _monthly('out_invoice')
    months = sorted(set(ref_m) | set(inv_m),
                    key=lambda m: dt.datetime.strptime(m, '%B %Y'))
    monthly = []
    for m in months:
        r, i = ref_m.get(m, {'value': 0, 'n': 0}), inv_m.get(m, {'value': 0, 'n': 0})
        monthly.append({'month': m, 'refund_value': r['value'], 'n_refunds': r['n'],
                        'invoice_value': i['value'], 'n_invoices': i['n'],
                        'rate_pct': (r['value'] / i['value'] * 100) if i['value'] else None})

    # ---- by customer (moves, 12m) ----------------------------------------------------------
    def _by_partner(mt):
        out = {}
        for g in O.read_group('account.move', _mdom(mt, d12, ti),
                              ['amount_untaxed:sum'], ['partner_id'], lazy=False):
            pid = O.m2o_id(g.get('partner_id'))
            if pid:
                out[pid] = {'name': O.m2o_name(g.get('partner_id')),
                            'value': g.get('amount_untaxed') or 0.0, 'n': g.get('__count') or 0}
        return out
    ref_c, inv_c = _by_partner('out_refund'), _by_partner('out_invoice')
    by_customer = []
    for pid, r in ref_c.items():
        inv = inv_c.get(pid, {'value': 0.0, 'n': 0})
        by_customer.append({'pid': pid, 'customer': r['name'], 'ret_value': r['value'],
                            'n_refunds': r['n'], 'invoiced': inv['value'],
                            'rate_pct': (r['value'] / inv['value'] * 100) if inv['value'] else None})
    by_customer.sort(key=lambda x: -x['ret_value'])

    # ---- by SKU (refund LINES vs invoice LINES, 12m) ----------------------------------------
    def _by_product(mt):
        out = {}
        for g in O.read_group('account.move.line', _ldom(mt, d12, ti),
                              ['price_subtotal:sum', 'quantity:sum'], ['product_id'], lazy=False):
            pid = O.m2o_id(g.get('product_id'))
            if pid:
                out[pid] = {'value': g.get('price_subtotal') or 0.0,
                            'qty': g.get('quantity') or 0.0}
        return out
    ref_p, inv_p = _by_product('out_refund'), _by_product('out_invoice')
    # meta for the UNION of refunded + sold products (category/supplier denominators need the
    # invoice side too)
    all_pids = list(set(ref_p) | set(inv_p))
    meta = {}
    for i in range(0, len(all_pids), 5000):
        for p in O.search_read('product.product',
                               [('id', 'in', all_pids[i:i + 5000]),
                                ('active', 'in', [True, False])],
                               ['default_code', 'name', 'categ_id']):
            meta[p['id']] = p
    by_sku = []
    for pid, r in ref_p.items():
        inv = inv_p.get(pid, {'value': 0.0, 'qty': 0.0})
        m = meta.get(pid, {})
        by_sku.append({'pid': pid, 'code': (m.get('default_code') or '').strip() or f'#{pid}',
                       'product': m.get('name') or '',
                       'ret_value': r['value'], 'ret_units': r['qty'],
                       'sold_value': inv['value'], 'sold_units': inv['qty'],
                       'rate_pct': (r['value'] / inv['value'] * 100) if inv['value'] else None})
    by_sku.sort(key=lambda x: -x['ret_value'])

    # ---- by CATEGORY (product-line signal: which kinds of products come back) ---------------
    def _cat_of(pid):
        return O.m2o_name((meta.get(pid) or {}).get('categ_id')) or '(none)'
    cat = {}
    for pid, r in ref_p.items():
        e = cat.setdefault(_cat_of(pid), {'ret_value': 0.0, 'ret_units': 0.0,
                                          'sold_value': 0.0, 'n_skus': 0})
        e['ret_value'] += r['value']
        e['ret_units'] += r['qty']
        e['n_skus'] += 1
    for pid, r in inv_p.items():
        e = cat.setdefault(_cat_of(pid), {'ret_value': 0.0, 'ret_units': 0.0,
                                          'sold_value': 0.0, 'n_skus': 0})
        e['sold_value'] += r['value']
    by_category = [{'category': k, **v,
                    'rate_pct': (v['ret_value'] / v['sold_value'] * 100)
                                if v['sold_value'] else None}
                   for k, v in cat.items() if v['ret_value'] > 0]
    by_category.sort(key=lambda x: -x['ret_value'])

    # ---- by SUPPLIER (sourcing-quality signal) ----------------------------------------------
    # dominant vendor per product from confirmed PO history (most units bought), one grouped read
    po_vendor, _best_q = {}, {}
    try:
        for g in O.read_group('purchase.order.line',
                              [('order_id.state', 'in', ('purchase', 'done')),
                               ('product_id', '!=', False)],
                              ['product_qty:sum'], ['product_id', 'partner_id'], lazy=False):
            pid = O.m2o_id(g.get('product_id'))
            q = g.get('product_qty') or 0.0
            v = O.m2o_name(g.get('partner_id'))
            if pid and v and q > _best_q.get(pid, 0.0):
                _best_q[pid] = q
                po_vendor[pid] = v
    except Exception:
        pass
    sup_map = proc_mod.suppliers()

    def _vendor_of(pid):
        code = ((meta.get(pid) or {}).get('default_code') or '').strip()
        cur = sup_map.get(code) or {}
        return (cur.get('vendor') or '').strip() or po_vendor.get(pid) or '(no supplier data)'
    sup = {}
    for pid, r in ref_p.items():
        e = sup.setdefault(_vendor_of(pid), {'ret_value': 0.0, 'ret_units': 0.0,
                                             'sold_value': 0.0, 'n_skus': 0})
        e['ret_value'] += r['value']
        e['ret_units'] += r['qty']
        e['n_skus'] += 1
    for pid, r in inv_p.items():
        e = sup.setdefault(_vendor_of(pid), {'ret_value': 0.0, 'ret_units': 0.0,
                                             'sold_value': 0.0, 'n_skus': 0})
        e['sold_value'] += r['value']
    by_supplier = [{'supplier': k, **v,
                    'rate_pct': (v['ret_value'] / v['sold_value'] * 100)
                                if v['sold_value'] else None}
                   for k, v in sup.items() if v['ret_value'] > 0]
    by_supplier.sort(key=lambda x: -x['ret_value'])

    # ---- by AGENT (book-behavior signal; agent = the customer's res.partner agent, NOT the
    # credit-note team_id). Denominator = the agent's WHOLE invoiced book, not just refunders.
    attrs = cust_mod._partner_attrs(list(set(ref_c) | set(inv_c)))

    def _agent_of(pid):
        return (attrs.get(pid) or {}).get('agent') or '(none)'
    ag = {}
    for pid, r in ref_c.items():
        e = ag.setdefault(_agent_of(pid), {'ret_value': 0.0, 'n_refunds': 0, 'invoiced': 0.0,
                                           'top_customer': '', 'top_value': 0.0, 'top_pid': None})
        e['ret_value'] += r['value']
        e['n_refunds'] += r['n']
        if r['value'] > e['top_value']:
            e['top_value'] = r['value']
            e['top_customer'] = r['name']
            e['top_pid'] = pid
    for pid, r in inv_c.items():
        e = ag.setdefault(_agent_of(pid), {'ret_value': 0.0, 'n_refunds': 0, 'invoiced': 0.0,
                                           'top_customer': '', 'top_value': 0.0, 'top_pid': None})
        e['invoiced'] += r['value']
    by_agent = [{'agent': k, **v,
                 'rate_pct': (v['ret_value'] / v['invoiced'] * 100) if v['invoiced'] else None}
                for k, v in ag.items() if v['ret_value'] > 0]
    by_agent.sort(key=lambda x: -x['ret_value'])

    # ---- headline ---------------------------------------------------------------------------
    n_ref = sum(r['n'] for r in ref_c.values())
    ref_val = sum(r['value'] for r in ref_c.values())
    n_inv = sum(r['n'] for r in inv_c.values())
    inv_val = sum(r['value'] for r in inv_c.values())
    top10 = sum(r['ret_value'] for r in by_sku[:10])
    return {'monthly': monthly, 'by_customer': by_customer, 'by_sku': by_sku,
            'by_category': by_category, 'by_supplier': by_supplier, 'by_agent': by_agent,
            'n_refunds': n_ref, 'refund_value': ref_val,
            'n_invoices': n_inv, 'invoice_value': inv_val,
            'doc_rate_pct': (n_ref / n_inv * 100) if n_inv else None,
            'value_rate_pct': (ref_val / inv_val * 100) if inv_val else None,
            'top10_share_pct': (top10 / ref_val * 100) if ref_val else None,
            'window': (d12, ti)}


def validate(t=None, team_id=None, pre=None):
    t = t or P.today()
    b = pre or build(t)
    d12, ti = b['window']
    checks = []
    srv_val = O.sum_field('account.move', _mdom('out_refund', d12, ti), 'amount_untaxed')
    a = sum(r['ret_value'] for r in b['by_customer'])
    checks.append({'check': 'Returns: Σ(per-customer refunds) == server Σ(credit-note untaxed)',
                   'a': round(a, 2), 'b': round(srv_val, 2), 'gap': round(a - srv_val, 2),
                   'ok': abs(a - srv_val) <= max(1.0, abs(srv_val) * 0.001)})
    srv_line = O.sum_field('account.move.line', _ldom('out_refund', d12, ti), 'price_subtotal')
    a2 = sum(r['ret_value'] for r in b['by_sku'])
    checks.append({'check': 'Returns: Σ(per-SKU refund lines) == server Σ(refund product lines)',
                   'a': round(a2, 2), 'b': round(srv_line, 2), 'gap': round(a2 - srv_line, 2),
                   'ok': abs(a2 - srv_line) <= max(1.0, abs(srv_line) * 0.001)})
    # the three new rollups are re-groupings of the SAME universes — they must tie exactly
    for key, base, label in (('by_category', a2, 'per-SKU lines'),
                             ('by_supplier', a2, 'per-SKU lines'),
                             ('by_agent', sum(r['ret_value'] for r in b['by_customer']),
                              'per-customer refunds')):
        s = sum(r['ret_value'] for r in b[key])
        checks.append({'check': f'Returns: Σ({key}) == Σ({label})',
                       'a': round(s, 2), 'b': round(base, 2), 'gap': round(s - base, 2),
                       'ok': abs(s - base) <= 1.0})
    return checks