platform/modules/inventory.py

"""Inventory module — working capital tied up, dead stock, stockouts blocking revenue,
ABC, slow movers, turnover.

Valuation: on-hand qty (stock.quant, internal locations) × standard cost. (stock.quant.value
is a non-stored computed field Odoo refuses to aggregate, so standard-cost valuation is used
and is independently cross-checked.) Sales velocity from sale.order.line over LTM (RI+FFS).
"""
import sys
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
import pandas as pd
import core.odoo as O
import core.periods as P


def _cat_main_map():
    cats = O.search_read('product.category', [], ['id', 'complete_name'])
    out = {}
    for c in cats:
        parts = [x.strip() for x in (c['complete_name'] or '').split('/')]
        out[c['id']] = parts[1] if len(parts) >= 2 else (parts[0] if parts else None)
    return out


def _build(t=None):
    """One master frame: product × on-hand × cost × category × LTM sales."""
    t = t or P.today()
    o = O.get_odoo()

    # On-hand qty per product (internal locations only)
    q = o.read_group('stock.quant', [('location_id.usage', '=', 'internal')],
                     ['product_id', 'quantity:sum'], ['product_id'], lazy=False)
    onhand = {O.m2o_id(r['product_id']): (r.get('quantity') or 0.0)
              for r in q if r.get('product_id')}

    # Products (coded). 'type' lets us separate storable goods from services
    # (delivery charges etc. are services — they can't "stock out").
    fields = ['id', 'default_code', 'name', 'categ_id', 'standard_price', 'sale_ok', 'type']
    prods = o.search_read('product.product', [('default_code', '!=', False)], fields)
    catmap = _cat_main_map()

    # LTM sales per product (line level, RI+FFS scope)
    lf, lt = P.ltm(t)
    s = o.read_group('sale.order.line', O.sale_line_domain(lf, lt),
                     ['product_id', 'price_subtotal:sum', 'product_uom_qty:sum'],
                     ['product_id'], lazy=False)
    rev = {O.m2o_id(r['product_id']): (r.get('price_subtotal') or 0.0) for r in s if r.get('product_id')}
    qty = {O.m2o_id(r['product_id']): (r.get('product_uom_qty') or 0.0) for r in s if r.get('product_id')}

    rows = []
    for p in prods:
        pid = p['id']
        oh = onhand.get(pid, 0.0)
        cost = p.get('standard_price') or 0.0
        rows.append({
            'product_id': pid,
            'sku': str(p['default_code']).strip(),
            'name': p['name'],
            'type': p.get('type') or '',
            'storable': (p.get('type') == 'product'),
            'category': catmap.get(O.m2o_id(p.get('categ_id'))) or '(uncategorized)',
            'cost': cost,
            'on_hand': oh,
            'inv_value': oh * cost,
            'rev_ltm': rev.get(pid, 0.0),
            'qty_ltm': qty.get(pid, 0.0),
        })
    return pd.DataFrame(rows), (lf, lt)


def summary(t=None):
    df, (lf, lt) = _build(t)
    in_stock = df[df['on_hand'] > 0]
    dead = df[(df['inv_value'] >= 1000) & (df['rev_ltm'] < 100)]
    # stockouts only make sense for STORABLE goods (services like delivery never stock)
    stockout = df[(df['storable']) & (df['rev_ltm'] >= 5000) & (df['on_hand'] <= 0)]
    uncosted = in_stock[in_stock['cost'] <= 0]
    return {
        'ltm_window': f'{lf} → {lt}',
        'total_inv_value': float(df['inv_value'].sum()),
        'total_on_hand_units': float(df['on_hand'].sum()),
        'skus_in_stock': int((df['on_hand'] > 0).sum()),
        'dead_skus': int(len(dead)),
        'dead_value': float(dead['inv_value'].sum()),
        'stockout_skus': int(len(stockout)),
        'stockout_rev_ltm': float(stockout['rev_ltm'].sum()),
        'uncosted_in_stock_skus': int(len(uncosted)),
    }


def by_category(t=None):
    df, _ = _build(t)
    g = df.groupby('category').agg(
        inv_value=('inv_value', 'sum'),
        rev_ltm=('rev_ltm', 'sum'),
        skus=('product_id', 'count'),
        in_stock=('on_hand', lambda s: int((s > 0).sum())),
    ).reset_index()
    # crude turnover proxy: LTM revenue / on-hand value (higher = faster)
    g['turnover_x'] = g.apply(lambda r: (r['rev_ltm'] / r['inv_value']) if r['inv_value'] > 0 else None, axis=1)
    return g.sort_values('inv_value', ascending=False).to_dict('records')


def dead_stock(t=None, limit=50):
    df, _ = _build(t)
    d = df[(df['inv_value'] >= 1000) & (df['rev_ltm'] < 100)]
    return d.sort_values('inv_value', ascending=False).head(limit)[
        ['sku', 'name', 'category', 'on_hand', 'cost', 'inv_value', 'rev_ltm']].to_dict('records')


def stockouts(t=None, limit=50):
    df, _ = _build(t)
    s = df[(df['storable']) & (df['rev_ltm'] >= 5000) & (df['on_hand'] <= 0)]
    return s.sort_values('rev_ltm', ascending=False).head(limit)[
        ['sku', 'name', 'category', 'on_hand', 'rev_ltm', 'qty_ltm']].to_dict('records')


def abc(t=None):
    """ABC by LTM revenue contribution. A=top 80%, B=next 15%, C=last 5%."""
    df, _ = _build(t)
    d = df[df['rev_ltm'] > 0].sort_values('rev_ltm', ascending=False).copy()
    total = d['rev_ltm'].sum() or 1.0
    d['cum'] = d['rev_ltm'].cumsum() / total
    def cls(c):
        return 'A' if c <= 0.80 else ('B' if c <= 0.95 else 'C')
    d['abc'] = d['cum'].apply(cls)
    out = d.groupby('abc').agg(skus=('product_id', 'count'),
                               rev_ltm=('rev_ltm', 'sum'),
                               inv_value=('inv_value', 'sum')).reset_index()
    return out.to_dict('records')


# ----------------------------------------------------------- deeper analytics
_CARRY_PCT = 25.0   # default annual carrying cost as a % of inventory value (capital + storage + obsolescence)
_COVERAGE_BUCKETS = [('Critical (<15d)', 0, 15), ('Lean (15–45d)', 15, 45),
                     ('Healthy (45–120d)', 45, 120), ('Ample (120–270d)', 120, 270),
                     ('Overstock (270–540d)', 270, 540), ('Excess (>540d)', 540, 1e15)]


def _enrich(df, target_days):
    """Add daily run-rate, days-of-supply, COGS, and excess-vs-target columns."""
    df = df.copy()
    daily = df['qty_ltm'] / 365.0
    df['daily_rate'] = daily
    df['cogs_ltm'] = df['qty_ltm'] * df['cost']

    def _dos(r):
        if r['daily_rate'] > 0:
            return r['on_hand'] / r['daily_rate']
        return float('inf') if r['on_hand'] > 0 else 0.0
    df['dos'] = df.apply(_dos, axis=1)
    df['target_units'] = daily * target_days
    df['excess_units'] = (df['on_hand'] - df['target_units']).clip(lower=0)
    df['excess_value'] = df['excess_units'] * df['cost']
    return df


def analytics(t=None, target_days=180, carry_pct=_CARRY_PCT):
    """Inventory health analytics from ONE build: overstock (excess vs a target days-of-supply), the
    coverage-profile distribution, turnover, average days-of-supply, and the carrying cost of excess."""
    df, (lf, lt) = _build(t)
    df = _enrich(df, target_days)
    instock = df[(df['storable']) & (df['on_hand'] > 0)]
    total_inv = float(df['inv_value'].sum())
    total_cogs = float(df['cogs_ltm'].sum())

    # overstock = sells, but holds materially more than the target coverage
    over = df[(df['storable']) & (df['on_hand'] > 0) & (df['qty_ltm'] > 0)
              & (df['dos'] > target_days) & (df['excess_value'] > 0)]
    over_value = float(over['excess_value'].sum())
    overrows = [{'sku': r['sku'], 'code': r['sku'], 'product': r['name'], 'category': r['category'],
                 'on_hand': float(r['on_hand']),
                 'dos': (None if r['dos'] == float('inf') else round(float(r['dos']), 0)),
                 'excess_units': float(r['excess_units']), 'excess_value': float(r['excess_value']),
                 'carry_cost': float(r['excess_value']) * (carry_pct / 100.0), 'rev_ltm': float(r['rev_ltm'])}
                for _, r in over.sort_values('excess_value', ascending=False).head(80).iterrows()]

    dead = df[(df['inv_value'] >= 1000) & (df['rev_ltm'] < 100)]

    # coverage profile (in-stock storable, by days-of-supply)
    prof = []
    for nm, lo, hi in _COVERAGE_BUCKETS:
        sub = instock[(instock['qty_ltm'] > 0) & (instock['dos'] >= lo) & (instock['dos'] < hi)]
        prof.append({'bucket': nm, 'skus': int(len(sub)), 'inv_value': float(sub['inv_value'].sum()),
                     'units': float(sub['on_hand'].sum())})
    nosale = instock[instock['qty_ltm'] <= 0]
    prof.append({'bucket': 'No recent sales', 'skus': int(len(nosale)),
                 'inv_value': float(nosale['inv_value'].sum()), 'units': float(nosale['on_hand'].sum())})

    healthy_val = sum(p['inv_value'] for p in prof if p['bucket'].startswith(('Healthy', 'Ample')))
    excess_val = sum(p['inv_value'] for p in prof if p['bucket'].startswith(('Overstock', 'Excess', 'No recent')))
    return {
        'window': f'{lf} → {lt}', 'target_days': target_days, 'carry_pct': carry_pct,
        'total_inv_value': total_inv,
        'turnover_x': (total_cogs / total_inv) if total_inv else None,
        'avg_dos': (total_inv / (total_cogs / 365.0)) if total_cogs else None,
        'overstock': overrows, 'overstock_value': over_value, 'overstock_skus': int(len(over)),
        'carrying_cost': over_value * (carry_pct / 100.0),
        'coverage': prof, 'healthy_value': healthy_val, 'excess_value_total': excess_val,
        'dead_value': float(dead['inv_value'].sum()), 'dead_skus': int(len(dead)),
    }


def _bucket(dos_raw, on_hand, qty_ltm):
    """Coverage-bucket label for one SKU from its days-of-supply (matches the coverage profile)."""
    if on_hand <= 0:
        return 'Out of stock'
    if qty_ltm <= 0:
        return 'No recent sales'
    for nm, lo, hi in _COVERAGE_BUCKETS:
        if lo <= dos_raw < hi:
            return nm
    return _COVERAGE_BUCKETS[-1][0]


# the coverage facet, worst→best, for the SKU directory filter
COVERAGE_LABELS = ['Out of stock'] + [b[0] for b in _COVERAGE_BUCKETS] + ['No recent sales']


def sku_inventory(target_days=180, carry_pct=_CARRY_PCT, t=None):
    """Per-SKU-code inventory position for the SKU module — keyed by SKU code (variants merged the
    same way the SKU module merges them). CONSOLIDATED: on-hand stock is one physical warehouse, not
    brand-tagged, so this is brand-independent. Returns {code: {name, category, storable, on_hand,
    inv_value, unit_cost, qty_ltm, dos (None=never sells through), bucket, excess_units, excess_value,
    carry_cost, turns, target_units, target_days}}."""
    df, _ = _build(t)
    df = _enrich(df, target_days)
    g = df.groupby('sku').agg(
        name=('name', 'first'), category=('category', 'first'), storable=('storable', 'max'),
        on_hand=('on_hand', 'sum'), inv_value=('inv_value', 'sum'),
        cogs_ltm=('cogs_ltm', 'sum'), qty_ltm=('qty_ltm', 'sum'), rev_ltm=('rev_ltm', 'sum'),
    ).reset_index()
    out = {}
    for r in g.itertuples(index=False):
        daily = r.qty_ltm / 365.0
        dos_raw = (r.on_hand / daily) if daily > 0 else (float('inf') if r.on_hand > 0 else 0.0)
        target_units = daily * target_days
        excess_units = max(r.on_hand - target_units, 0.0)
        # code-level unit cost: on-hand-weighted where in stock, else the LTM COGS unit cost
        unit_cost = (r.inv_value / r.on_hand) if r.on_hand > 0 else (
            (r.cogs_ltm / r.qty_ltm) if r.qty_ltm > 0 else 0.0)
        excess_value = excess_units * unit_cost
        out[r.sku] = {
            'name': r.name, 'category': r.category, 'storable': bool(r.storable),
            'on_hand': float(r.on_hand), 'inv_value': float(r.inv_value),
            'unit_cost': float(unit_cost), 'qty_ltm': float(r.qty_ltm),
            'dos': (None if dos_raw == float('inf') else round(float(dos_raw), 0)),
            'bucket': _bucket(dos_raw, r.on_hand, r.qty_ltm),
            'excess_units': float(excess_units), 'excess_value': float(excess_value),
            'carry_cost': float(excess_value) * (carry_pct / 100.0),
            'turns': (float(r.cogs_ltm) / float(r.inv_value)) if r.inv_value > 0 else None,
            'target_units': float(target_units), 'target_days': target_days,
        }
    return out


def validate(t=None):
    df, _ = _build(t)
    o = O.get_odoo()
    checks = []

    # 1. On-hand units: Σ(per-product) == ungrouped Odoo total
    g = o.read_group('stock.quant', [('location_id.usage', '=', 'internal')],
                     ['quantity:sum'], [], lazy=False)
    odoo_total = (g[0].get('quantity') or 0.0) if g else 0.0
    # our df only covers coded products; compare coded-product on-hand to a coded-only Odoo total
    coded_ids = set(df['product_id'])
    ours = float(df['on_hand'].sum())
    checks.append({
        'check': 'On-hand units (coded SKUs) ≤ Odoo total internal qty',
        'a': round(ours, 0), 'b': round(odoo_total, 0),
        'gap': round(odoo_total - ours, 0),
        'ok': ours <= odoo_total + 1})  # coded ≤ all (uncoded/raw exist)

    # 2. Σ(category inv_value) == total inv_value
    cat_sum = sum(c['inv_value'] for c in by_category(t))
    tot = float(df['inv_value'].sum())
    checks.append({
        'check': 'Inv value: Σ(category) == total',
        'a': round(cat_sum, 2), 'b': round(tot, 2),
        'gap': round(cat_sum - tot, 2),
        'ok': abs(cat_sum - tot) <= 1.0})

    # 3. inv_value definition holds for a sampled SKU (qty×cost)
    smp = df[df['on_hand'] > 0].head(1)
    if len(smp):
        r = smp.iloc[0]
        recomputed = r['on_hand'] * r['cost']
        checks.append({
            'check': f"inv_value == on_hand×cost (sample {r['sku']})",
            'a': round(float(r['inv_value']), 2), 'b': round(float(recomputed), 2),
            'gap': round(float(r['inv_value'] - recomputed), 2),
            'ok': abs(r['inv_value'] - recomputed) <= 0.01})
    return checks