"""Opportunity levers sized from data in hand (Riyadh sales + Jeddah labour model). Annual SAR profit impact, low / base / high.
Inputs: 02_Cleaned riyadh_sales_lines, riyadh_return_lines; 03_Simulated inf_outlet_activity, inf_salesman_route_profile, sim_scenarios, sim_monte_carlo_summary.
Gross margin 16%: Almunajem Foods (listed Saudi food distributor) FY2025, multiples.vc/public-comps/almunajem-foods-valuation-multiples (range 12-20% assumed)."""
import pandas as pd, numpy as np, os
B = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..')) + '/'
s = pd.read_parquet(B + '02_Cleaned/data/riyadh_sales_lines.parquet'); r = pd.read_parquet(B + '02_Cleaned/data/riyadh_return_lines.parquet')
s['doc_date'] = pd.to_datetime(s.doc_date)
o = pd.read_parquet(B + '03_Simulated/data/inf_outlet_activity.parquet')
rp = pd.read_parquet(B + '03_Simulated/data/inf_salesman_route_profile.parquet')
sc = pd.read_parquet(B + '03_Simulated/data/sim_scenarios.parquet').set_index('scenario')
ms = pd.read_parquet(B + '03_Simulated/data/sim_monte_carlo_summary.parquet').set_index('metric')
START, END = pd.Timestamp('2026-03-01'), pd.Timestamp('2026-10-20'); AF = 365 / ((END - START).days + 1)
GM = {'low': 0.12, 'base': 0.16, 'high': 0.20}
rows = []
def add(lever, module, driver_sar, driver_desc, method, params, conf):
    for k in ['low', 'base', 'high']:
        rows.append(dict(lever=lever, module=module, case=k, driver_annual_sar=driver_sar, driver=driver_desc, method=method,
                         profit_annual_sar=params[k], confidence=conf, basis='Simulated'))
# 1 Jeddah labour (existing model)
add('Jeddah labour pace', 'Warehouse', sc.loc['Base', 'labour_cost_annual_sar'], 'simulated Jeddah labour cost',
    'Full shifts benchmarked on top-quartile shifts of the same role; 70% capture; loaded cost per hour',
    {'low': ms.loc['recoverable_labour_annual_sar', 'p10'], 'base': sc.loc['Base', 'recoverable_labour_annual_sar'], 'high': ms.loc['recoverable_labour_annual_sar', 'p90']}, 'Medium')
# 2 van-rep productivity
a = rp[rp.is_active & (rp.active_days >= 60)].copy(); a['spd'] = a.total_sales_sar / a.active_days
gap = ((a.spd.median() - a.spd).clip(lower=0) * a.active_days * AF).sum()
cap = {'low': 0.25, 'base': 0.5, 'high': 0.7}
add('Van-rep productivity', 'Retail (van sales)', gap, f'sales gap of below-median reps to the median ({int((a.spd < a.spd.median()).sum())} of {len(a)} reps)',
    'Reps below median sales per working day close part of the gap; extra sales x gross margin', {k: gap * cap[k] * GM[k] for k in cap}, 'Low-Medium')
# 3 outlet win-back
l = o[o.activity_status.str.startswith('lapsed')]
span = ((l.last_purchase - l.first_purchase).dt.days + l.median_days_between_purchases.fillna(30)).clip(lower=90)
lapsed = (l.sales_sar / span * 365).sum()
sw = s[s.cust_cd.isin(o[o.activity_status == 'slowing'].cust_cd)]; cut = END - pd.Timedelta(days=90)
recent = sw[sw.doc_date > cut].groupby('cust_cd').net_amt.sum() / 90 * 365
prior = sw[sw.doc_date <= cut].groupby('cust_cd').net_amt.sum() / ((cut - START).days + 1) * 365
slowing = (prior - recent.reindex(prior.index).fillna(0)).clip(lower=0).sum()
risk = lapsed + slowing; wb = {'low': 0.15, 'base': 0.30, 'high': 0.45}
add('Outlet win-back', 'Distribution', risk, f'annual sales lost from {len(l)} lapsed and slowing outlets',
    'Win back part of lapsed and slowing outlets; recovered sales x gross margin', {k: risk * wb[k] * GM[k] for k in wb}, 'Medium')
# 4 price leakage
x = s[(s.sls_type == 'Sellable') & (~s.is_foc) & (s.unit_net_price_per_ea > 0)].copy()
g = x.groupby(['prd_cd', 'month']).unit_net_price_per_ea; x['p50'] = g.transform('median'); x['n'] = g.transform('size')
y = x[x.n >= 20]
leak = (np.where(y.unit_net_price_per_ea < 0.95 * y.p50, (y.p50 - y.unit_net_price_per_ea) * y.qty_base_ea, 0)).sum() * AF
rec = {'low': 0.10, 'base': 0.25, 'high': 0.50}
add('Price leakage', 'Retail (van sales)', leak, 'value of lines sold >5% below the product\'s median price that month',
    'Recover part of below-median pricing (may include approved deals); flows straight to profit', {k: leak * rec[k] for k in rec}, 'Low-Medium')
# 5 returns
rs = s.groupby('slsman_cd').net_amt.sum(); rr = r.groupby('slsman_cd').net_amt.sum().reindex(rs.index).fillna(0)
m = rs > 200000; rate = (rr / rs)[m]; ex = ((rate - rate.median()).clip(lower=0) * rs[m]).sum() * AF
av = {'low': 0.25, 'base': 0.5, 'high': 0.7}; wo = {'low': 0.3, 'base': 0.5, 'high': 0.7}
add('Returns reduction', 'Distribution', ex, f'returns above the median rep return rate ({rate.median():.1%})',
    'Avoid part of excess returns; saving = goods at cost x share written off (no reason codes)', {k: ex * av[k] * (1 - GM['base']) * wo[k] for k in av}, 'Low')
df = pd.DataFrame(rows)
os.makedirs(B + '03_Simulated/csv', exist_ok=True)
df.to_parquet(B + '03_Simulated/data/sim_opportunity_levers.parquet', index=False)
df.to_csv(B + '03_Simulated/csv/sim_opportunity_levers.csv', index=False, encoding='utf-8-sig')
p = df.pivot_table(index=['lever', 'module', 'confidence'], columns='case', values='profit_annual_sar').reindex(columns=['low', 'base', 'high'])
print(p.round(0)); print(p.sum().round(0).to_dict())