"""Stage 5: warehouse inventory by lot (F16), demand plan (F18), e-commerce listings (F15)."""
import numpy as np, pandas as pd
from common import *
import warnings; warnings.filterwarnings('ignore')

r = rng(5)
FI = load('fact_sell_in'); FI['doc_date'] = pd.to_datetime(FI.doc_date)
prod = load('dim_product_ext').set_index('sku'); F = load('fact_order_fulfilment')
LT = {'Unilever': 4, 'Non-Food': 5, 'Drinks': 2, 'Food': 3, 'Chilled': 1, 'Ice Cream': 2}   # supplier lead time, weeks (assumption)

# ---------------- F16 inventory: weekly snapshot by warehouse × SKU × lot
wh = F[['order_id', 'warehouse_id']].drop_duplicates('order_id').set_index('order_id').warehouse_id
FI['warehouse_id'] = np.where(FI.channel_id == 'GR', 'RYD-DC', FI.doc_no.map(wh).fillna('RYD-DC'))
FI['week'] = FI.doc_date.dt.to_period('W-TUE').dt.end_time.dt.normalize()
dem = FI.groupby(['warehouse_id', 'sku', 'week']).qty_base_ea.sum()
weeks = pd.date_range(FI.week.min(), FI.week.max(), freq='7D')
rows = []
for (w, s), d in dem.groupby(level=[0, 1]):
    v = d.droplevel([0, 1]).reindex(weeks, fill_value=0).values
    if (v > 0).sum() < 4: continue
    div = prod.division.get(s, 'Food'); lt = LT[div]; life = int(prod.shelf_life_days.get(s, 365)); case = int(prod.case_size.get(s, 12))
    mu, sd = v.mean(), v.std()
    ss = 1.65 * sd * np.sqrt(lt); rop = mu * lt + ss; out_to = rop + mu * r.uniform(3, 5)
    lots = [[weeks[0] - pd.Timedelta(days=14), weeks[0] + pd.Timedelta(days=int(life * r.uniform(0.5, 0.85))), out_to]]   # [received, expiry, qty]
    pipeline = []   # [arrival_week_index, qty]
    lot_n = 1
    for k, wk in enumerate(weeks):
        for p in [p for p in pipeline if p[0] == k]:
            lot_n += 1; lots.append([wk, wk + pd.Timedelta(days=int(life * r.uniform(0.6, 0.9))), p[1]])
        pipeline = [p for p in pipeline if p[0] > k]
        # demand consumes stock first-expiry-first-out; expired stock is written off
        need = v[k]; shipped = 0
        lots.sort(key=lambda x: x[1])
        for L in lots:
            if L[1] < wk: L[2] = 0; continue
            take = min(L[2], need - shipped); L[2] -= take; shipped += take
            if shipped >= need: break
        lots = [L for L in lots if L[2] > 0]
        on = sum(L[2] for L in lots); transit = sum(p[1] for p in pipeline)
        if on + transit < rop:
            q = np.ceil((out_to - on - transit) / case) * case
            delay = lt + (1 if r.random() < 0.12 else 0)          # 12% of supplier deliveries slip a week
            pipeline.append([k + delay, q])
        alloc = round(v[k + 1] * 0.3) if k + 1 < len(v) else 0
        for j, L in enumerate(lots):
            rows.append((wk.date(), w, s, f'{s}-L{lot_n:03d}-{j}', round(L[2]), alloc if j == 0 else 0, round(transit) if j == 0 else 0,
                         L[0].date(), L[1].date(), round(ss), round(rop), lt * 7, round(on / (mu / 7), 1) if mu else None,
                         round(need - shipped)))
        if not lots:
            rows.append((wk.date(), w, s, None, 0, alloc, round(transit), None, None, round(ss), round(rop), lt * 7, 0.0, round(need - shipped)))
INV = pd.DataFrame(rows, columns=['snapshot_week_end', 'warehouse_id', 'sku', 'lot_id', 'on_hand_units', 'allocated_units', 'in_transit_units',
                                  'received_date', 'expiry_date', 'safety_stock_units', 'reorder_point_units', 'lead_time_days',
                                  'stock_cover_days_sku', 'unfilled_demand_units'])
INV['days_to_expiry'] = (pd.to_datetime(INV.expiry_date) - pd.to_datetime(INV.snapshot_week_end)).dt.days
save(INV, 'fact_inventory_weekly')
sku_wk = INV.groupby(['warehouse_id', 'sku', 'snapshot_week_end']).agg(on=('on_hand_units', 'sum'), un=('unfilled_demand_units', 'max'))
print('  SKU-weeks with unfilled demand', round((sku_wk.un > 0).mean(), 3), '| near expiry (<30 d) share of units',
      round(INV.loc[INV.days_to_expiry < 30, 'on_hand_units'].sum() / INV.on_hand_units.sum(), 3))

# ---------------- F18 demand plan: monthly S&OP cycles (Apr–Oct) × top-300 SKUs × location × 1–3 months ahead
FI['month'] = FI.doc_date.dt.to_period('M')
mon = FI.groupby(['warehouse_id', 'sku', 'month']).qty_base_ea.sum()
top = FI.groupby('sku').net_amt.sum().nlargest(300).index
mon = mon[mon.index.get_level_values(1).isin(top)]
act = mon.unstack('month').fillna(0)
months = act.columns
pl = []
for ci, cyc in enumerate(months[1:], 1):
    hist = act[months[:ci]].mean(axis=1)
    for h in (1, 2, 3):
        tm = cyc + h - 1
        if tm > months[-1] + 2: continue
        a = act[tm] if tm in act.columns else pd.Series(np.nan, index=act.index)
        truth = a.where(a.notna(), hist)
        stat = (truth * r.lognormal(0, 0.28 + 0.06 * h, len(truth))).round()
        bias = r.uniform(0.05, 0.15, len(truth))
        sales_in = (stat * (1 + bias)).round()
        consensus = ((stat + sales_in) / 2).round()
        supply = np.minimum(consensus, consensus * r.uniform(0.92, 1.0, len(truth))).round()
        for (w, s), sv, si_, co, su, av in zip(act.index, stat, sales_in, consensus, supply, a):
            pl.append((cyc.to_timestamp().date(), s, w, tm.to_timestamp().date(), h, sv, si_, co, co, su, None if pd.isna(av) else av))
DP = pd.DataFrame(pl, columns=['cycle_month', 'sku', 'location', 'target_month', 'horizon_months', 'stat_forecast_units', 'sales_input_units',
                               'consensus_units', 'final_plan_units', 'supply_plan_units', 'actual_units'])
save(DP, 'fact_demand_plan')
k = DP.dropna(subset=['actual_units']); k = k[k.horizon_months == 1]
print('  monthly accuracy 1 − WAPE: stat', round(1 - (k.stat_forecast_units - k.actual_units).abs().sum() / k.actual_units.sum(), 3),
      '| final', round(1 - (k.final_plan_units - k.actual_units).abs().sum() / k.actual_units.sum(), 3))

# ---------------- F15 e-commerce: platform × SKU × day
ec = FI[FI.channel_id == 'EC'].copy()
plat = ec.account_id.unique()
days = calendar().date
out = []
for p in plat:
    e = ec[ec.account_id == p]
    skus = e.groupby('sku').net_amt.sum().nlargest(250).index
    sin = e[e.sku.isin(skus)].groupby(['sku', 'doc_date']).qty_base_ea.sum().unstack('doc_date').reindex(columns=days, fill_value=0).fillna(0)
    for s, row in sin.iterrows():
        v = row.values; base = v.mean() * 0.95
        rating = float(np.clip(r.normal(4.2, 0.35), 2.8, 4.9)); content = float(np.clip(r.normal(78, 12), 30, 100))
        inactive = np.zeros(len(days), bool)
        for _ in range(r.poisson(0.8)):
            a = int(r.integers(0, len(days))); inactive[a:a + int(r.integers(3, 21))] = True
        stock = base * 7; price = prod.list_price_sar.get(s, 10) * 1.15; rank = float(r.integers(5, 120))
        for k, d in enumerate(days):
            stock += v[k]
            dmd = base * r.lognormal(0, 0.45) * (1.25 if d.day_name() in ('Thursday', 'Friday') else 1)
            units = 0 if inactive[k] else min(stock, np.round(dmd)); stock -= units
            conv = np.clip(r.normal(0.04, 0.012), 0.01, 0.09) * (rating / 4.2)
            sess = int(units / conv) if units else int(r.poisson(max(1, base / 0.04 * 0.6)))
            rank = float(np.clip(rank * (1.15 if (units == 0) else 0.97) * r.lognormal(0, 0.05), 1, 400))
            ad = round(float(price * units * r.uniform(0.04, 0.12)), 2) if not inactive[k] else 0.0
            out.append((p, f'{p}-{s}', s, d.date(), 'inactive' if inactive[k] else ('out of stock' if stock < 1 else 'active'),
                        round(price, 2), round(stock), sess, int(units > 0) * max(1, int(units // 2)), int(units), round(units * price, 2),
                        round(rank), round(rating, 2), round(content), ad, round(ad * r.uniform(2, 8), 2) if ad else 0.0))
EC = pd.DataFrame(out, columns=['platform_id', 'listing_id', 'sku', 'date', 'listing_status', 'price_sar', 'stock_units', 'sessions', 'orders',
                                'units', 'gmv_sar', 'search_rank', 'rating', 'content_score', 'ad_spend_sar', 'ad_sales_sar'])
save(EC, 'fact_ecom_listing_daily')
print('  e-com: inactive share', round((EC.listing_status == 'inactive').mean(), 3), '| OOS share', round((EC.listing_status == 'out of stock').mean(), 3))