platform/modules/reprice.py

"""Reprice lifecycle — the price-side discipline the pricing brief found missing
(.claude/wiki/research/pricing.md): rule hygiene BEFORE the reprice, realization tracking
AFTER it, and the between-waves views (pocket waterfall, peer price corridor).

Everything reads sale.order.line + product.pricelist.item + product masters, read-only.
NO elasticity claims — rejected at wholesale data density (see the brief's anti-patterns).

Windows: LTM for corridor/waterfall (matches pricecomp), all-channel sales for the DEAD-rule
flag (the Amazon channel-scope trap: a rule serving Amazon sales is not dead).
"""
import datetime as dt

import core.odoo as O
import core.periods as P
import modules.pricecomp as pcomp
import modules.customers as cust_mod

STALE_MONTHS = 24
MIN_CORRIDOR_BUYERS = 8
CORRIDOR_FLOOR_PCTL = 25          # floor = p25 of %-of-tier among the SKU's buyers
REALIZED_TARGET_PCT = 80.0        # an increase that landed <80% = worklist


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


def _all_channel_sold(lf, lt):
    """{product_id: qty} confirmed sales, ALL channels (never flag an Amazon seller as dead)."""
    out = {}
    for g in O.read_group('sale.order.line',
                          [('state', 'in', ['sale', 'done']), ('product_id', '!=', False),
                           ('order_id.date_order', '>=', f'{lf} 00:00:00'),
                           ('order_id.date_order', '<=', f'{lt} 23:59:59')],
                          ['product_uom_qty:sum'], ['product_id'], lazy=False):
        pid = O.m2o_id(g.get('product_id'))
        if pid:
            out[pid] = g.get('product_uom_qty') or 0.0
    return out


# ================================================================ 1. RULE HYGIENE
def rule_health(t=None):
    """Every fixed pricelist rule flagged for the pre-reprice cleanup. One verb per flag:
    STALE → reprice it · DEAD → archive it · BELOW-COST → raise it NOW (before the next
    order books — the realized below-cost view only catches it after) · OVERLAP → resolve
    precedence."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    stale_cut = (t - dt.timedelta(days=STALE_MONTHS * 30)).isoformat()
    today = t.isoformat()

    rules = O.search_read(
        'product.pricelist.item',
        [('compute_price', '=', 'fixed'),
         ('applied_on', 'in', ['0_product_variant', '1_product'])],
        ['pricelist_id', 'product_id', 'product_tmpl_id', 'applied_on', 'fixed_price',
         'min_quantity', 'date_start', 'date_end', 'write_date'])

    # resolve template rules to variants; pull cost + active per involved variant
    tmpl_ids = {O.m2o_id(r['product_tmpl_id']) for r in rules
                if r['applied_on'] == '1_product' and r.get('product_tmpl_id')}
    var_ids = {O.m2o_id(r['product_id']) for r in rules
               if r['applied_on'] == '0_product_variant' and r.get('product_id')}
    by_tmpl, vinfo = {}, {}
    for ch in _chunk(list(tmpl_ids) | set() if not tmpl_ids else list(tmpl_ids), 2000):
        for p in O.search_read('product.product',
                               [('product_tmpl_id', 'in', ch), ('active', 'in', [True, False])],
                               ['product_tmpl_id', 'standard_price', 'active',
                                'default_code']):
            by_tmpl.setdefault(O.m2o_id(p['product_tmpl_id']), []).append(p)
            vinfo[p['id']] = p
    for ch in _chunk([v for v in var_ids if v not in vinfo], 2000):
        for p in O.search_read('product.product',
                               [('id', 'in', ch), ('active', 'in', [True, False])],
                               ['product_tmpl_id', 'standard_price', 'active',
                                'default_code']):
            vinfo[p['id']] = p

    sold = _all_channel_sold(lf, lt)

    def _variants_of(r):
        if r['applied_on'] == '0_product_variant':
            v = vinfo.get(O.m2o_id(r.get('product_id')))
            return [v] if v else []
        return by_tmpl.get(O.m2o_id(r.get('product_tmpl_id')), [])

    # overlap detection: same (pricelist, target, qty break) with overlapping date windows
    def _target_key(r):
        return (O.m2o_id(r['pricelist_id']), r['applied_on'],
                O.m2o_id(r.get('product_id')) or O.m2o_id(r.get('product_tmpl_id')),
                r.get('min_quantity') or 0)
    seen_keys = {}
    for r in rules:
        seen_keys.setdefault(_target_key(r), []).append(r)

    def _windows_overlap(a, b):
        a0 = str(a.get('date_start') or '')[:10] or '0000'
        a1 = str(a.get('date_end') or '')[:10] or '9999'
        b0 = str(b.get('date_start') or '')[:10] or '0000'
        b1 = str(b.get('date_end') or '')[:10] or '9999'
        return a0 <= b1 and b0 <= a1

    rows = []
    counts = {'stale': 0, 'dead': 0, 'below_cost': 0, 'overlap': 0}
    below_cost_gap = 0.0
    for r in rules:
        de = str(r.get('date_end') or '')[:10]
        if de and de < today:
            continue                          # already expired — not operating
        variants = _variants_of(r)
        v_sold = sum(sold.get(v['id'], 0.0) for v in variants)
        any_active = any(v.get('active') for v in variants)
        # cost basis: the CHEAPEST variant — flags only when price is below even that
        costs = [v.get('standard_price') or 0.0 for v in variants
                 if (v.get('standard_price') or 0.0) > 0]
        min_cost = min(costs) if costs else None
        price = r.get('fixed_price') or 0.0
        code = next(((v.get('default_code') or '').strip() for v in variants
                     if (v.get('default_code') or '').strip()), '')

        flags = []
        if not variants or not any_active or (any_active and v_sold <= 0):
            if not variants or not any_active:
                flags.append(('dead', 'Archive rule — product archived/missing'))
            elif v_sold <= 0:
                flags.append(('dead', 'Archive rule — no sales in 12m (all channels)'))
        if str(r.get('write_date') or '')[:10] < stale_cut and v_sold > 0:
            flags.append(('stale', f'Reprice — untouched >{STALE_MONTHS}m, still selling'))
        if min_cost is not None and price > 0 and price < min_cost and v_sold > 0:
            flags.append(('below_cost', 'Raise NOW — tier below current unit cost'))
            below_cost_gap += (min_cost - price) * v_sold
        siblings = seen_keys.get(_target_key(r), [])
        if len(siblings) > 1 and any(s is not r and _windows_overlap(r, s)
                                     for s in siblings):
            flags.append(('overlap', 'Resolve precedence — duplicate qty-break rule'))
        if not flags:
            continue
        for kind, _v in flags:
            counts[kind] += 1
        rows.append({
            'rule_id': r['id'], 'pricelist': O.m2o_name(r['pricelist_id']),
            'sku': code,
            'product': O.m2o_name(r.get('product_id')) or O.m2o_name(r.get('product_tmpl_id')),
            'qty_break': r.get('min_quantity') or 0, 'price': price,
            'unit_cost': min_cost if min_cost is not None else '',
            'sold_12m': v_sold, 'last_touched': str(r.get('write_date') or '')[:10],
            'flags': ', '.join(k for k, _ in flags),
            'action': flags[0][1],
        })
    rows.sort(key=lambda x: (0 if 'below_cost' in x['flags'] else 1, -(x['sold_12m'] or 0)))
    return {'rows': rows, 'n_rules': len(rules), 'counts': counts,
            'below_cost_gap': below_cost_gap, 'window': (lf, lt)}


# ================================================================ 2. REALIZATION TRACKER
def realization_events(t=None, months_back=15, min_rules=15):
    """Candidate reprice events = days on which many fixed rules were (re)written. Odoo keeps
    no price history, so the event DATE is the anchor and the CURRENT rule price is the target."""
    t = t or P.today()
    since = (t - dt.timedelta(days=months_back * 30)).isoformat()
    rules = O.search_read('product.pricelist.item',
                          [('compute_price', '=', 'fixed'),
                           ('applied_on', 'in', ['0_product_variant', '1_product']),
                           ('write_date', '>=', since)],
                          ['write_date', 'product_id', 'product_tmpl_id'])
    per_day = {}
    for r in rules:
        d = str(r.get('write_date') or '')[:10]
        if d:
            e = per_day.setdefault(d, {'n_rules': 0, 'targets': set()})
            e['n_rules'] += 1
            e['targets'].add(O.m2o_id(r.get('product_id'))
                             or O.m2o_id(r.get('product_tmpl_id')))
    out = [{'date': d, 'n_rules': v['n_rules'], 'n_products': len(v['targets'])}
           for d, v in per_day.items() if v['n_rules'] >= min_rules]
    out.sort(key=lambda x: x['date'], reverse=True)
    return out


def _avg_price_by_product(prod_ids, a, b, team_id=None):
    """{product_id: (avg unit price, qty)} over a window — chunked (large in-domains make
    grouped reads echo the domain per group and MemoryError server-side)."""
    out = {}
    for ch in _chunk(prod_ids):
        dom = O.sale_line_domain(a, b, team_id, extra=[('product_id', 'in', ch)])
        for g in O.read_group('sale.order.line', dom,
                              ['price_subtotal:sum', 'product_uom_qty:sum'],
                              ['product_id'], lazy=False):
            pid = O.m2o_id(g.get('product_id'))
            qty = g.get('product_uom_qty') or 0.0
            rev = g.get('price_subtotal') or 0.0
            if pid and qty > 0:
                out[pid] = (rev / qty, qty)
    return out


def realization(event_date, t=None, team_id=None, before_days=180, after_min_days=30):
    """Did the increase STICK? For every product whose rule was written on `event_date`:
    realized avg before (180d) vs after (event → today) vs the CURRENT rule target.
    realization % = (after − before) / (target − before). <80% at scale = the worklist."""
    t = t or P.today()
    ev = dt.date.fromisoformat(event_date)
    if (t - ev).days < after_min_days:
        return {'rows': [], 'note': f'event only {(t - ev).days}d old — too early to score',
                'event': event_date}
    rules = O.search_read('product.pricelist.item',
                          [('compute_price', '=', 'fixed'),
                           ('applied_on', 'in', ['0_product_variant', '1_product']),
                           ('write_date', '>=', f'{event_date} 00:00:00'),
                           ('write_date', '<=', f'{event_date} 23:59:59')],
                          ['product_id', 'product_tmpl_id', 'applied_on', 'fixed_price',
                           'min_quantity'])
    # base tier target per product (lowest qty break); template rules → variants
    tmpl_ids = [O.m2o_id(r['product_tmpl_id']) for r in rules
                if r['applied_on'] == '1_product' and r.get('product_tmpl_id')]
    t2v = {}
    for ch in _chunk(tmpl_ids, 2000):
        for p in O.search_read('product.product',
                               [('product_tmpl_id', 'in', ch), ('active', 'in', [True, False])],
                               ['product_tmpl_id', 'default_code']):
            t2v.setdefault(O.m2o_id(p['product_tmpl_id']), []).append(p)
    target, codes = {}, {}
    for r in sorted(rules, key=lambda x: x.get('min_quantity') or 0, reverse=True):
        price = r.get('fixed_price') or 0.0
        if price <= 0:
            continue
        if r['applied_on'] == '0_product_variant' and r.get('product_id'):
            target[O.m2o_id(r['product_id'])] = price   # lowest break wins (reverse sort)
        else:
            for p in t2v.get(O.m2o_id(r.get('product_tmpl_id')), []):
                target[p['id']] = price
                codes[p['id']] = (p.get('default_code') or '').strip()
    prods = list(target)
    if not prods:
        return {'rows': [], 'note': 'no fixed rules found on that date', 'event': event_date}
    for ch in _chunk([p for p in prods if p not in codes], 2000):
        for p in O.search_read('product.product',
                               [('id', 'in', ch), ('active', 'in', [True, False])],
                               ['default_code', 'name']):
            codes[p['id']] = (p.get('default_code') or '').strip()

    b0 = (ev - dt.timedelta(days=before_days)).isoformat()
    b1 = (ev - dt.timedelta(days=1)).isoformat()
    before = _avg_price_by_product(prods, b0, b1, team_id)
    after = _avg_price_by_product(prods, event_date, t.isoformat(), team_id)

    rows = []
    for pid in prods:
        if pid not in before or pid not in after:
            continue
        p0, _q0 = before[pid]
        p1, q1 = after[pid]
        tgt = target[pid]
        if tgt <= p0 * 1.005:
            continue                          # not an increase vs realized base — skip
        realized_pct = (p1 - p0) / (tgt - p0) * 100.0
        days_since = max((t - ev).days, 1)
        run_rate = q1 / days_since * 365.0
        rows.append({'prod': pid, 'sku': codes.get(pid, f'#{pid}'),
                     'before': p0, 'after': p1, 'target': tgt,
                     'realized_pct': realized_pct, 'qty_after': q1,
                     'missing': max(0.0, (tgt - p1)) * run_rate})
    rows.sort(key=lambda x: -x['missing'])
    n = len(rows)
    landed = sum(1 for r in rows if r['realized_pct'] >= REALIZED_TARGET_PCT)
    return {'rows': rows, 'event': event_date, 'n_scored': n,
            'n_landed': landed,
            'landed_pct': (landed / n * 100.0) if n else None,
            'missing_total': sum(r['missing'] for r in rows),
            'note': None}


# ================================================================ 3. CORRIDOR + WATERFALL
def corridor(team_id=None, t=None, pre=None):
    """Peer price corridor: within each SKU (≥MIN_CORRIDOR_BUYERS tier-matched buyers), the
    floor = p{CORRIDOR_FLOOR_PCTL} of realized-%-of-tier. A customer below the floor AND below
    95% of tier is off-corridor even if 'on' their assigned tier — the tier ASSIGNMENT review.
    Uplift = (floor − actual) × tier × qty."""
    mp = pre or pcomp.matched_pairs(team_id, t)
    by_prod = {}
    for p in mp['pairs']:
        if p.get('tier'):
            by_prod.setdefault(p['prod'], []).append(p)
    flags, n_skus = [], 0
    for prod, plist in by_prod.items():
        if len(plist) < MIN_CORRIDOR_BUYERS:
            continue
        n_skus += 1
        ratios = sorted(x['unit'] / x['tier'] for x in plist)
        floor = ratios[max(0, int(len(ratios) * CORRIDOR_FLOOR_PCTL / 100) - 1)]
        for x in plist:
            ratio = x['unit'] / x['tier']
            if ratio < floor and ratio < 0.95:
                flags.append({'pid': x['pid'], 'customer': x['customer'],
                              'sku': x['sku'], 'product': x['product'],
                              'pct_of_tier': ratio * 100.0, 'floor_pct': floor * 100.0,
                              'qty_ltm': x['qty'],
                              'uplift': (floor - ratio) * x['tier'] * x['qty']})
    flags.sort(key=lambda x: -x['uplift'])
    per_cust = {}
    for f in flags:
        e = per_cust.setdefault(f['pid'], {'pid': f['pid'], 'customer': f['customer'],
                                           'skus': 0, 'uplift': 0.0})
        e['skus'] += 1
        e['uplift'] += f['uplift']
    by_customer = sorted(per_cust.values(), key=lambda x: -x['uplift'])
    return {'flags': flags, 'by_customer': by_customer, 'n_skus_scored': n_skus,
            'uplift_total': sum(f['uplift'] for f in flags), 'window': mp['window']}


def waterfall(team_id=None, t=None, pre=None):
    """Honest pocket-price waterfall, LTM, tier-matched pairs only (coverage stated):
    tier list value → − below-tier selling → + above-tier selling → invoice value
    → − credit notes (company-level, not BU-taggable) → pocket. Stages with no data are
    DROPPED, never imputed."""
    mp = pre or pcomp.matched_pairs(team_id, t)
    lf, lt = mp['window']
    matched = [p for p in mp['pairs'] if p.get('tier')]
    tier_value = sum(p['tier'] * p['qty'] for p in matched)
    invoice_value = sum(p['rev'] for p in matched)
    below = sum(max(0.0, (p['tier'] - p['unit'])) * p['qty'] for p in matched)
    above = sum(max(0.0, (p['unit'] - p['tier'])) * p['qty'] for p in matched)
    matched_rev_share = (invoice_value /
                         sum(p['rev'] for p in mp['pairs']) * 100.0) if mp['pairs'] else 0.0
    # credit notes, LTM, product lines (company-level — honesty note in the UI)
    refunds = O.sum_field('account.move.line',
                          [('move_id.move_type', '=', 'out_refund'),
                           ('parent_state', '=', 'posted'),
                           ('move_id.invoice_date', '>=', lf),
                           ('move_id.invoice_date', '<=', lt),
                           ('product_id', '!=', False)], 'price_subtotal')
    stages = [{'stage': 'Tier list value', 'amount': tier_value, 'kind': 'base'}]
    if below > 0:
        stages.append({'stage': 'Below-tier selling', 'amount': -below, 'kind': 'leak'})
    if above > 0:
        stages.append({'stage': 'Above-tier selling', 'amount': above, 'kind': 'gain'})
    stages.append({'stage': 'Invoice value', 'amount': invoice_value, 'kind': 'subtotal'})
    if refunds:
        stages.append({'stage': 'Credit notes (company)', 'amount': -refunds, 'kind': 'leak'})
    stages.append({'stage': 'Pocket value', 'amount': invoice_value - (refunds or 0.0),
                   'kind': 'total'})
    return {'stages': stages, 'tier_value': tier_value, 'invoice_value': invoice_value,
            'below': below, 'above': above, 'refunds': refunds or 0.0,
            'matched_rev_share': matched_rev_share, 'window': (lf, lt)}


def below_tier_detail(team_id=None, t=None, pre=None):
    """The waterfall's below-tier bar, drilled: every pair sold under its tier (the FULL gap,
    not just the <97% compliance threshold), rolled up by customer / SKU / agent. Each rollup's
    total reproduces the waterfall stage to the cent (rule 8b — no unverifiable aggregates)."""
    mp = pre or pcomp.matched_pairs(team_id, t)
    below = [p for p in mp['pairs'] if p.get('tier') and p['unit'] < p['tier']]
    attrs = cust_mod._partner_attrs(list({p['pid'] for p in below}))
    by_cust, by_sku, by_agent = {}, {}, {}
    for p in below:
        gap = (p['tier'] - p['unit']) * p['qty']
        c = by_cust.setdefault(p['pid'], {'pid': p['pid'], 'customer': p['customer'],
                                          'skus': 0, 'gap': 0.0, 'rev': 0.0})
        c['skus'] += 1
        c['gap'] += gap
        c['rev'] += p['rev']
        s = by_sku.setdefault(p['prod'], {'code': p['sku'] or f"#{p['prod']}",
                                          'product': p['product'], 'customers': 0,
                                          'gap': 0.0, 'rev': 0.0})
        s['customers'] += 1
        s['gap'] += gap
        s['rev'] += p['rev']
        a_name = (attrs.get(p['pid']) or {}).get('agent') or '(none)'
        a = by_agent.setdefault(a_name, {'agent': a_name, 'pairs': 0, 'gap': 0.0})
        a['pairs'] += 1
        a['gap'] += gap
    out = {
        'by_customer': sorted(by_cust.values(), key=lambda x: -x['gap']),
        'by_sku': sorted(by_sku.values(), key=lambda x: -x['gap']),
        'by_agent': sorted(by_agent.values(), key=lambda x: -x['gap']),
        'total': sum((p['tier'] - p['unit']) * p['qty'] for p in below),
        'n_pairs': len(below),
    }
    return out


# ================================================================ VALIDATION
def validate(t=None, team_id=None, pre_mp=None):
    """(1) rule universe ties search_count; (2) waterfall arithmetic closes exactly:
    tier − below + above == invoice (same pairs, two computation paths)."""
    n = O.get_odoo().search_count('product.pricelist.item',
                                  [('compute_price', '=', 'fixed'),
                                   ('applied_on', 'in', ['0_product_variant', '1_product'])])
    rh = rule_health(t)
    checks = [{'check': 'pricelist rules — pull complete',
               'a': rh['n_rules'], 'b': n, 'gap': rh['n_rules'] - n,
               'ok': rh['n_rules'] == n}]
    wf = waterfall(team_id, t, pre=pre_mp)
    lhs = wf['tier_value'] - wf['below'] + wf['above']
    checks.append({'check': 'waterfall closes: tier − below + above == invoice',
                   'a': round(lhs, 2), 'b': round(wf['invoice_value'], 2),
                   'gap': round(lhs - wf['invoice_value'], 2),
                   'ok': abs(lhs - wf['invoice_value']) < 1.0})
    bd = below_tier_detail(team_id, t, pre=pre_mp)
    for key in ('by_customer', 'by_sku', 'by_agent'):
        s = sum(r['gap'] for r in bd[key])
        checks.append({'check': f'below-tier drill {key} == the waterfall bar',
                       'a': round(s, 2), 'b': round(wf['below'], 2),
                       'gap': round(s - wf['below'], 2),
                       'ok': abs(s - wf['below']) < 1.0})
    return checks