"""One table of every figure to change on binzagr.snop.live: exact text shown now, exact replacement, type and basis.
Numbers come from the saved test results; written to data/before_after_figures.csv and .json."""
import json, pickle, os
import pandas as pd

W = '/home/claude/binzagr/boost/work'
D = '/mnt/user-data/outputs/Binzagr_Data/06_Model_Review/data'; os.makedirs(D, exist_ok=True)
fc = pd.read_csv(f'{W}/fc_summary.csv').set_index('level')
RT = pickle.load(open(f'{W}/reco_race.pkl', 'rb'))['table'].set_index('strategy')
RES = pickle.load(open(f'{W}/reco_final_v2.pkl', 'rb'))['res']
CT = pickle.load(open(f'{W}/churn_race.pkl', 'rb'))['table'].set_index('model')
AD = pickle.load(open(f'{W}/adoption_race.pkl', 'rb')); AT = AD['table'].set_index('model'); APICK = AD['pick']
DEC = pickle.load(open(f'{W}/churn_decile.pkl', 'rb'))
N = pickle.load(open(f'{W}/report_numbers.pkl', 'rb')); LV = N['lever']

nbs = RES['ranker']['test_p10']; bs = RT.loc['Best-sellers (popularity)', 'test_p10']; rnd = RT.loc['Random SKUs', 'test_p10']
hit = RES['ranker']['outlets_hit']; p5 = RT.loc['LightGBM ranker on all scores', 'test_p5']
et, rec = CT.loc['Extra trees'], CT.loc['Recency rule']
a_pool, a_within, a_top = AT.loc[APICK, 'test_auc_pooled'], AT.loc[APICK, 'test_auc_within_mean'], AT.loc[APICK, 'test_top10_capture']
c_pool, c_top = AT.loc['Channel popularity', 'test_auc_pooled'], AT.loc['Channel popularity', 'test_top10_capture']
n_pool, n_top = AT.loc['What neighbours buy', 'test_auc_pooled'], AT.loc['What neighbours buy', 'test_top10_capture']
n_pairs, n_adopt = AD['n_test'], AD['pos']; n_top10 = -(-n_pairs // 10)
top10_rate = a_top * n_adopt / n_top10; base_rate = n_adopt / n_pairs
pc = lambda x, d=1: f'{100 * x:.{d}f}%'
x1 = lambda v: f'{v:.1f}×'
TYPES = {'Replace': 'Measured on the same test after switching the model; change it once the new model is live on the site',
         'Fix': 'A wrong or mislabelled figure; change it now, no model change needed',
         'Add': 'Keep the figure and add the note',
         'Verify first': 'Our time-honest re-test is lower; re-run the site test on a clean split, then use whichever result it gives',
         'Recompute': 'Needs the site pipeline re-run; we cannot produce the exact figure',
         'Keep': 'No change'}
R = []
def row(page, where, before, after, typ, basis, bv=None, av=None, unit=''):
    R.append(dict(no=len(R) + 1, page=page, where_on_page=where, on_site_now=before, replace_with=after, type=typ, basis=basis,
                  before_value=bv, after_value=av, unit=unit))

# ---------------- Value cockpit
row('Value cockpit', 'KPI tile, Forecast proof', '22% · less error than last week repeated',
    '22% · less error than last week repeated (forecast 15% below actual)', 'Add', 'Site SKU run bias -15.0% (base demand, weekly run)', 22, 22, '%')
row('Value cockpit', 'Proof card: Demand forecast', 'ML forecast 64% · 4-week weekday average 72% · Repeat last week 82%',
    'Keep the three values; add "ML bias −15%" under ML forecast', 'Add', 'Site scores, base demand weekly run', 64, 64, '%')
row('Value cockpit', 'Proof card: Next-best SKU', '3.0× · Model picks 12% · Best-sellers 4.0% · Random SKUs 0.7%',
    f'{x1(nbs / bs)} · Model picks {pc(nbs)} · Best-sellers {pc(bs)} · Random SKUs {pc(rnd)}', 'Replace',
    'Site test re-run: picks 25 Aug, 4,097 outlets, 8 weeks; LightGBM ranker picked on 30 Jun validation (next_best_sku_models.csv)', 12.0, round(100 * nbs, 1), '%')
row('Value cockpit', 'Proof card: Outlet churn warning', '0.77 · Churn model 0.77 · Recency rule 0.68 · top 20% of risk holds 48% of churners',
    f'{et.test_auc:.2f} · Churn model {et.test_auc:.2f} · Recency rule {rec.test_auc:.2f} · top 20% of risk holds {pc(et.test_top20_capture, 0)} of churners', 'Replace',
    'Extra trees picked on 2 validation folds; 1,314 held-out outlets at 25 Aug (churn_models.csv)', 0.77, round(et.test_auc, 3), 'AUC')
row('Value cockpit', 'Proof card: Cluster adoption', '65% · Adoption model 65% · Channel popularity 40% · What neighbours buy 12% · AUC 0.90',
    f'{pc(a_top, 0)} · Adoption model {pc(a_top, 0)} · Channel popularity {pc(c_top, 0)} · What neighbours buy {pc(n_top, 0)} · AUC {a_pool:.2f}', 'Verify first',
    'Time-honest re-test: trained on adoptions to 31 Jul, tested Aug-Oct on held-out outlets, 5 clusters (adoption_models.csv)', 65, round(100 * a_top), '%')
row('Value cockpit', 'Scorecard: Next-best SKU', 'vs best-sellers · 3.0× as good', f'vs best-sellers · {x1(nbs / bs)} as good', 'Replace', 'As row 3', 3.0, round(nbs / bs, 1), '×')
row('Value cockpit', 'Scorecard: Outlet churn warning', 'vs recency rule · +0.09 AUC', f'vs recency rule · +{et.test_auc - rec.test_auc:.2f} AUC', 'Replace',
    'As row 4; the recency rule is stronger on this test (0.73 vs 0.68), so the gap is smaller', 0.09, round(et.test_auc - rec.test_auc, 2), 'AUC')
row('Value cockpit', 'Scorecard: Cluster adoption', 'vs channel popularity · 1.6× as good', f'vs channel popularity · {x1(a_top / c_top)} as good', 'Verify first', 'As row 5', 1.6,
    round(a_top / c_top, 1), '×')
row('Value cockpit', 'Scorecard: Demand forecast', 'vs 4-week weekday average · 12% less error', 'Keep; add "3% at zero bias"', 'Add',
    'Best blend scaled to zero bias: 74.1% vs 76.5% for the 4-week average (forecast_by_level.csv)', 12, 12, '%')
row('Value cockpit', 'Recommendation 1 (leads)', 'AUC 0.902 on unseen outlets', f'AUC {a_pool:.2f} on a time-honest test ({a_within:.2f} within cluster)', 'Verify first', 'As row 5',
    0.902, round(a_pool, 2), 'AUC')
row('Value cockpit', 'Recommendation 3 (P1 accounts)', 'SAR 927,163 past due (24%)', 'SAR 590,932 past due (15%) + SAR 336,231 older than the invoice window', 'Fix',
    "The site's own collections-lever evidence (matched invoices only)", 927163, 590932, 'SAR')
row('Value cockpit', 'Recommendation 5 (win-back)', 'churn model AUC 0.77 on a later period it never saw · Top 20% risk holds 48% of churners',
    f'churn model AUC {et.test_auc:.2f} on outlets it never saw · Top 20% risk holds {pc(et.test_top20_capture, 0)} of churners', 'Replace', 'As row 4', 0.77,
    round(et.test_auc, 2), 'AUC')
row('Value cockpit', 'Explore card: Receivables', 'SAR 927K past due, 24% of the credit book', 'SAR 591K past due, 15% of the credit book (+ SAR 336K unmatched)', 'Fix',
    'As row 11', 927, 591, 'SAR K')
row('Value cockpit', 'Explore card: Next-best SKU', "12% of the model's picks were bought · 3.0× best-sellers",
    f"{pc(nbs)} of the model's picks were bought · {x1(nbs / bs)} best-sellers", 'Replace', 'As row 3', 12, round(100 * nbs, 1), '%')
row('Value cockpit', 'Hero and impact tree', 'SAR 3.45M a year', f'Keep (all model upgrades add about SAR {LV["total_change"] / 1e3:.0f}K, under 1%)', 'Keep',
    'Model-dependent levers rescaled by the measured gains', 3454548, round(3454548 + LV['total_change']), 'SAR')
# ---------------- Forecast proof
row('Forecast proof', 'Hero', 'the ML forecast made 22% less error than repeating last week',
    'Keep; add "with the forecast 15% below actual sales"', 'Add', 'As row 1', 22, 22, '%')
row('Forecast proof', 'Tile: Van-week error removed', '38.6% vs 47.2% weekly error', '40.0% vs 47.2% weekly error', 'Fix',
    'The level table on the same page uses the weekly run (40.0%); the tile used the daily run', 38.6, 40.0, '%')
row('Forecast proof', 'Section heading (Model vs planner)', 'Less error at every level where orders are placed',
    'Less error at SKU and van level; level with a simple average above that', 'Fix', 'Principal: ML 28.3% vs 27.4% for the 4-week average; cluster 21.7% vs 21.5%')
row('Forecast proof', 'Principal level chip', '−8% error', 'Level with the 4-week average (28.3% vs 27.4%)', 'Fix', 'Site scores, base demand weekly run')
row('Forecast proof', 'Company total level', 'About level · ML 14.6% vs 14.9%', 'Keep; add "four weeks cannot rank models at total"', 'Add',
    'Any flat forecast of SAR 1.08M-1.38M a week scores 16.2% (raw sales) in these four weeks')
row('Forecast proof', 'SKU level: ML forecast', '63.8%', 'Keep until re-run; expect about 1.4 points lower at the same bias', 'Recompute',
    f'Raw-sales re-run: one equal blend for all SKUs {pc(fc.loc["sku", "picked_at_site_bias"])} vs 66.9% at -17% bias (forecast_by_level.csv)', 63.8, None, '%')
row('Forecast proof', 'Van, principal, cluster: ML forecast', '40.0% · 28.3% · 21.7%', 'Keep', 'Keep',
    'Site blends beat our best by 0.6-1.4 points at the same bias (forecast_by_level.csv)')
row('Forecast proof', 'Model race text', 'Its forecast is a blend of the best one to three models',
    'SKUs: one equal blend of the three models best on validation. Vans: a blend of the best one to three', 'Recompute', 'Per-SKU racing overfits: our copy scored 77.9%')
row('Forecast proof', 'Next 12 weeks: weekly range', 'The weekly range is the sum of daily p10 and p90', 'Weekly bands calibrated on validation errors', 'Recompute',
    'Summed daily bands are too wide (the page says so)')
row('Forecast proof', 'Cluster cards: days in range', 'Ice Cream 54% · Tea & Dairy Foods 93% of days in range', 'Bands calibrated per cluster (target 80%)', 'Recompute',
    'Site cluster coverage 54%-93%')
row('Forecast proof', 'Next 12 weeks: company model', 'Company model: LightGBM + AutoETS + TiRex', 'Company model: TiRex + AutoETS + AutoTheta (the blend tested on the hold-out)',
    'Fix', 'The forward blend was not the one tested')
# ---------------- Next-best SKU
row('Next-best SKU', 'Hero', 'outlets bought 12.0% of their ten recommended SKUs, against 4.0% for the best-sellers and 0.7% for random SKUs',
    f'outlets bought {pc(nbs)} of their ten recommended SKUs, against {pc(bs)} for the best-sellers and {pc(rnd)} for random SKUs', 'Replace', 'As row 3', 12.0,
    round(100 * nbs, 1), '%')
row('Next-best SKU', 'Tile', '58.2% bought at least one pick', f'{pc(hit)} bought at least one pick', 'Replace', 'As row 3', 58.2, round(100 * hit, 1), '%')
row('Next-best SKU', 'Hold-out proof', '4,097 outlets judged. Lift: 3.0× over best-sellers, 18.2× over random.',
    f'4,097 outlets judged. Lift: {x1(nbs / bs)} over best-sellers, {x1(nbs / rnd)} over random.', 'Replace', 'As row 3', 3.0, round(nbs / bs, 1), '×')
row('Next-best SKU', 'New line under the proof', '(not shown)', f'Top five picks: {pc(p5)} bought (the list the value uses)', 'Add', 'As row 3', None, round(100 * p5, 1), '%')
row('Next-best SKU', 'Method text', "similar outlets' baskets, neighbourhood and van penetration, popularity",
    "similar outlets' baskets, neighbourhood and van penetration, popularity, plus EASE and RP3beta scores, ranked by a LightGBM ranker", 'Replace', 'Model change')
# ---------------- Outlets
row('Outlets', 'Hero', 'AUC 0.77 against 0.68 for the recency rule', f'AUC {et.test_auc:.2f} against {rec.test_auc:.2f} for the recency rule', 'Replace', 'As row 4', 0.77,
    round(et.test_auc, 2), 'AUC')
row('Outlets', 'Tile: riskiest 20%', '47.9% of the outlets that lapsed sat in the top 20% of risk', f'{pc(et.test_top20_capture)} of the outlets that lapsed sat in the top 20% of risk',
    'Replace', 'As row 4', 47.9, round(100 * et.test_top20_capture, 1), '%')
row('Outlets', 'Text under the AUC', 'Top risk decile churns at 2.9× the average rate (6.7% in the test window)',
    f'Top risk decile churns at {x1(DEC["Extra trees"]["lift"])} the average rate ({pc(DEC["Extra trees"]["base"])} in the test window)', 'Replace', 'As row 4', 2.9,
    round(DEC['Extra trees']['lift'], 1), '×')
row('Outlets', 'Test description', 'trained to 21 Aug', 'trained on cut-offs to 30 Jun, tested at 25 Aug on outlets it never saw', 'Replace', 'Test protocol of the new model')
row('Outlets', 'Risk tiers', 'High risk 57 outlets · Medium risk 129 outlets', 'Re-score every outlet with the new model (data/churn_scores_v2.csv)', 'Recompute',
    'Tier thresholds depend on the site scale')
# ---------------- Cluster harmonisation
row('Cluster harmonisation', 'Tile', '0.90 model AUC on unseen outlets', f'{a_pool:.2f} model AUC on unseen outlets (time-honest test)', 'Verify first', 'As row 5', 0.90,
    round(a_pool, 2), 'AUC')
row('Cluster harmonisation', 'Proof: top 10% of ranked pairs', 'Adoption model 65% · Channel popularity 40% · Neighbours buy it 12%',
    f'Adoption model {pc(a_top, 0)} · Channel popularity {pc(c_top, 0)} · Neighbours buy it {pc(n_top, 0)}', 'Verify first', 'As row 5', 65, round(100 * a_top), '%')
row('Cluster harmonisation', 'Proof: lift text', "6.5× more adoptions in the model's top 10% than in a random 10% (29.3% of top-10% pairs adopted vs 4.54% on average)",
    f"{x1(top10_rate / base_rate)} more adoptions in the model's top 10% than in a random 10% ({pc(top10_rate)} of top-10% pairs adopted vs {pc(base_rate, 2)} on average)",
    'Verify first', 'As row 5', 6.5, round(top10_rate / base_rate, 1), '×')
row('Cluster harmonisation', 'Ranking accuracy (AUC)', 'Adoption model 0.90 · Channel popularity 0.71 · Neighbours buy it 0.56',
    f'Adoption model {a_pool:.2f} · Channel popularity {c_pool:.2f} · Neighbours buy it {n_pool:.2f}', 'Verify first', 'As row 5', 0.90, round(a_pool, 2), 'AUC')
row('Cluster harmonisation', 'How well it ranks each cluster', 'Per-cluster AUC 0.67 to 0.86', 'Keep; add "mean within cluster 0.79" next to the pooled AUC', 'Add',
    'Pooled AUC mostly reflects that Unilever HPC adopts far more often than the other teams')
# ---------------- Receivables / labour / value
row('Receivables', 'Headline', 'SAR 927K past due (24%)', 'SAR 591K past due (15%) + SAR 336K older than the invoice window', 'Fix', 'As row 11', 927, 591, 'SAR K')
row('Value roll-up', 'Labour lever evidence', '625 badge-sharing sessions flagged', '625 high or medium-risk badge sessions (198 high)', 'Fix',
    'Site badge-risk tiers: high 198, medium 427', 625, 625, 'sessions')
row('Value roll-up', 'ML forecast to safety stock', 'SAR 65.2K a year · + SAR 296K once', 'Keep', 'Keep',
    f'Same bias after re-run: SAR {LV["safety_same_bias"] / 1e3:.1f}K + SAR {LV["safety_oneoff_same_bias"] / 1e3:.0f}K once; if forecasts must be unbiased SAR {LV["safety_zero_bias"] / 1e3:.1f}K + SAR {LV["safety_oneoff_zero_bias"] / 1e3:.0f}K',
    65179, round(LV['safety_same_bias']), 'SAR')
row('Value roll-up', 'Whitespace / next-best SKU', 'SAR 105K a year', 'Keep', 'Keep', f'Top-5 precision barely moves: SAR {LV["whitespace"] / 1e3:.1f}K', 104524,
    round(LV['whitespace']), 'SAR')
row('Value roll-up', 'Churn early-warning and win-back', 'SAR 47.3K a year', f'Up to SAR {LV["churn_upper"] / 1e3:.1f}K after the new model is live', 'Recompute',
    'Upper bound: whole lever scaled by churners caught in the riskiest 20%', 47302, round(LV['churn_upper']), 'SAR')

T = pd.DataFrame(R)
T.to_csv(f'{D}/before_after_figures.csv', index=False, encoding='utf-8-sig')
json.dump({'title': 'Binzagr PoV: before and after figures for binzagr.snop.live', 'as_of': '2026-10-23', 'site_read': '2026-10-23',
           'types': TYPES, 'rows': R}, open(f'{D}/before_after_figures.json', 'w'), indent=2, default=lambda o: None if o != o else float(o))
for old in ('figure_changes.csv', 'figure_changes.json'):
    if os.path.exists(f'{D}/{old}'): os.remove(f'{D}/{old}')
pickle.dump({'rows': R, 'types': TYPES}, open(f'{W}/replacements.pkl', 'wb'))
print(len(R), 'rows'); print(T.type.value_counts().to_dict())
print(T[['no', 'page', 'on_site_now', 'replace_with', 'type']].to_string(index=False, max_colwidth=60))