"""Turns data/processed/* into the site's JSON and the public CSV downloads.

Writes site/data/*.json (what the charts read) and site/csv/*.csv (what journalists download),
plus data/processed/story_pack.md with every headline number and how it was computed.

Run after build.py: .venv/bin/python pipeline/export.py
"""
import json
from pathlib import Path

import numpy as np
import pandas as pd

ROOT = Path(__file__).resolve().parent.parent
P = ROOT / 'data' / 'processed'
SITE = ROOT.parent / 'orca-website' / 'src' / 'data-tool'   # served at simmance.ai/data (orca-website copies it to dist/data)
(SITE / 'assets').mkdir(parents=True, exist_ok=True)
(SITE / 'csv').mkdir(parents=True, exist_ok=True)

MAJOR = {
    '1': 'Managers, directors and senior officials', '2': 'Professional occupations',
    '3': 'Associate professional occupations', '4': 'Administrative and secretarial occupations',
    '5': 'Skilled trades occupations', '6': 'Caring, leisure and other service occupations',
    '7': 'Sales and customer service occupations', '8': 'Process, plant and machine operatives',
    '9': 'Elementary occupations',
}

m = pd.read_csv(P / 'master.csv', dtype={'soc2020': str, 'major_group': str, 'sub_major_group': str})
ads = pd.read_csv(P / 'adverts_monthly.csv', dtype={'soc2020': str})
regemp = pd.read_csv(P / 'regional_employment.csv', dtype={'soc2020': str})
ads = ads[ads.soc2020.isin(m.soc2020)]


def r(v, n=0):
    return None if pd.isna(v) else (round(float(v), n) if n else int(round(float(v))))


# ---------------------------------------------------------------- estimating withheld months
# Where ONS withheld a month, the gap is filled with an estimate, never left looking like a collapse or a
# hole. The estimate uses the series' own seasonal pattern: the median, across normal years (2017 to 2025,
# leaving out the 2020 and 2021 lockdowns), of that calendar month's level relative to the average of the
# month before and after. Estimated points are flagged and drawn dotted.
SKIP_YEARS = {2020, 2021}


def seasonal_fill(series):
    """Estimate withheld months (single months or runs) from the series' own seasonal pattern.

    For a run of missing months between two published months, each missing month is the straight line
    between the published months either side, scaled by the median of how that same month compared with
    the same straight line in normal past years. Returns the filled series and a flag for estimated months.
    """
    s = series.copy()
    est = pd.Series(False, index=s.index)
    miss = s.isna()
    i, idx = 0, list(s.index)
    while i < len(idx):
        if not miss[idx[i]]:
            i += 1
            continue
        j = i
        while j < len(idx) and miss[idx[j]]:
            j += 1
        if i == 0 or j >= len(idx):          # no published month on one side: leave the gap
            i = j
            continue
        p0, p1, run = idx[i - 1], idx[j], idx[i:j]
        n = len(run) + 1
        for k, d in enumerate(run, start=1):
            line = s[p0] + (s[p1] - s[p0]) * k / n
            ratios = []
            for y in range(2017, d.year):
                if y in SKIP_YEARS:
                    continue
                shift = pd.DateOffset(years=d.year - y)
                t, a0, a1 = d - shift, p0 - shift, p1 - shift
                if all(x in series.index for x in (t, a0, a1)) and not series[[t, a0, a1]].isna().any():
                    ref = series[a0] + (series[a1] - series[a0]) * k / n
                    if ref > 0:
                        ratios.append(series[t] / ref)
            if len(ratios) >= 3:
                s[d] = float(np.median(ratios)) * line
                est[d] = True
        i = j
    return s, est


# ---------------------------------------------------------------- jobs.json
q = ads.copy()
q['quarter'] = pd.PeriodIndex(q.month, freq='M').asfreq('Q').astype(str)
quarters = sorted(q.quarter.unique())
last_full_q = quarters[-1]      # data ends Sep 2026, so the last quarter is complete
# Withheld months for a job are estimated from that job's own seasonal pattern (see seasonal_fill below);
# a quarter containing an estimate is flagged and drawn dotted. Quarters that still cannot be estimated stay blank.
qs = q.groupby(['soc2020', 'quarter']).adverts.sum(min_count=3).unstack()[quarters]
q_est = pd.DataFrame(False, index=qs.index, columns=quarters)

def fill_job_quarters():
    wide_m = ads.assign(md=pd.to_datetime(ads.month)).pivot_table(index='soc2020', columns='md', values='adverts', aggfunc='first', dropna=False)
    wide_m = wide_m.reindex(columns=pd.date_range(wide_m.columns.min(), wide_m.columns.max(), freq='MS'))
    for c in wide_m.index[wide_m.isna().any(axis=1)]:
        filled, est = seasonal_fill(wide_m.loc[c])
        if not est.any():
            continue
        qsum = filled.groupby(filled.index.to_period('Q').astype(str)).sum(min_count=3)
        qest = est.groupby(est.index.to_period('Q').astype(str)).any()
        for qq in quarters:
            if qq in qsum.index and pd.isna(qs.loc[c, qq]) and pd.notna(qsum[qq]):
                qs.loc[c, qq] = qsum[qq]
                q_est.loc[c, qq] = bool(qest.get(qq, False))


fill_job_quarters()
jobs = []
for _, j in m.sort_values('soc2020').iterrows():
    jobs.append({
        'c': j.soc2020, 't': j.title, 'g': j.major_group,
        'x': r(j.exposure, 3), 'p': r(j.exposure_percentile, 3), 'k': r(j.exposure_rank),
        'e': r(j.employment), 'pay': r(j.median_pay_ft), 'wb': r(j.wage_bill_gbp),
        'a19': r(j.adverts_change_vs_2019, 3), 'ap': r(j.adverts_oct24_sep25 / j.adverts_oct23_sep24 - 1 if j.adverts_oct23_sep24 >= 100 else np.nan, 3), 'cw': j.crosswalk_main_soc2010, 'cws': r(j.crosswalk_main_share, 2),
        'h': bool(j.highly_exposed), 'y': r(j.young_share_2021, 3), 'd': r(j.degree_share, 3), 'i': r(j.ilo_genai_score, 2), 'q': [r(v) for v in qs.loc[j.soc2020]] if j.soc2020 in qs.index else None,
        'qe': [i for i, qq in enumerate(quarters) if j.soc2020 in q_est.index and q_est.loc[j.soc2020, qq]],
    })
scored = int(m.exposure.notna().sum())
(SITE / 'assets' / 'jobs.json').write_text(json.dumps({'scored': scored, 'quarters': quarters, 'jobs': jobs}, separators=(',', ':')))

# ---------------------------------------------------------------- titles.json
# Everyday job titles from the ONS SOC 2020 coding index, so "marketing manager" finds its unit group.
idx = pd.read_excel(ROOT / 'data' / 'raw' / 'soc2020_coding_index.xlsx', sheet_name='SOC2020 coding index',
                    usecols=['SOC_2020', 'INDEXOCC_-_natural_word_order'], dtype={'SOC_2020': str})
idx.columns = ['c', 't']
idx['c'] = idx.c.str.extract(r'(\d{4})')[0]
idx['t'] = idx.t.astype(str).str.strip().str.lower()
idx = idx.dropna().drop_duplicates()
idx = idx[idx.c.isin(m.soc2020) & (idx.t.str.len() < 60)]
(SITE / 'assets' / 'titles.json').write_text(json.dumps(idx.sort_values('t').values.tolist(), separators=(',', ':')))
print('titles', len(idx))

# ---------------------------------------------------------------- door.json
# Matched index: each month compares only the jobs ONS published that month with the same jobs'
# 2019 monthly average, so a withheld cell is left out rather than counted as zero. A month where
# every job is withheld (March 2026) is a gap. Smoothed over three months, needing all three.
ads['month_dt'] = pd.to_datetime(ads.month)
groups = {
    'Professional and associate professional jobs (SOC 2 and 3)': m[m.major_group.isin(['2', '3'])].soc2020,
    'Skilled trades, caring and leisure jobs (SOC 5 and 6)': m[m.major_group.isin(['5', '6'])].soc2020,
    'Most exposed fifth of jobs': m[m.highly_exposed].soc2020,
    'Least exposed fifth of jobs': m[m.exposure_percentile <= 0.2].soc2020,
    'All jobs': m.soc2020,
}
wide = ads.pivot_table(index='soc2020', columns='month_dt', values='adverts', aggfunc='first', dropna=False)
wide = wide.reindex(columns=pd.date_range(ads.month_dt.min(), ads.month_dt.max(), freq='MS'))   # keep fully withheld months as gaps
base19 = wide.loc[:, [c for c in wide.columns if c.year == 2019]].mean(axis=1)
door = {}
door_rows = []
for name, codes in groups.items():
    w = wide.loc[wide.index.isin(codes)]
    b = base19.reindex(w.index)
    pub = w.notna()
    num_ = w.sum(axis=0, min_count=1)
    den = pub.mul(b, axis=0).sum(axis=0)
    monthly = (num_ / den * 100).where(pub.any(axis=0))
    cover = den / b.sum()
    monthly, est = seasonal_fill(monthly)
    idx = monthly.rolling(3, min_periods=3).mean()
    est3 = est.astype(int).rolling(3, min_periods=1).max().astype(bool)   # smoothed points that lean on an estimate
    door[name] = [[d.strftime('%Y-%m'), None if pd.isna(v) else round(float(v), 1), int(est3[d])] for d, v in idx.items()]
    for d in monthly.index:
        door_rows.append({'group': name, 'month': d.strftime('%Y-%m'),
                          'adverts_published_jobs': '' if pd.isna(num_[d]) else int(num_[d]),
                          'share_of_2019_base_published': round(float(cover[d]), 3),
                          'index_2019_100': '' if pd.isna(monthly[d]) else round(float(monthly[d]), 1),
                          'estimated': int(est[d]),
                          'index_2019_100_3m_avg': '' if pd.isna(idx[d]) else round(float(idx[d]), 1)})
(SITE / 'assets' / 'door.json').write_text(json.dumps(door, separators=(',', ':')))
pd.DataFrame(door_rows).to_csv(SITE / 'csv' / 'door_adverts_by_group.csv', index=False)

# ---------------------------------------------------------------- regions.json
rg = regemp.merge(m[['soc2020', 'mean_pay_all', 'highly_exposed']], on='soc2020')
rg['wage_bill'] = rg.employees * rg.mean_pay_all
regions = []
for name, d in rg.groupby('region'):
    total = d.wage_bill.sum()
    exposed = d.loc[d.highly_exposed, 'wage_bill'].sum()
    regions.append({
        'region': name, 'code': d.region_code.iloc[0],
        'share': round(exposed / total, 4), 'exposed_bn': round(exposed / 1e9, 1), 'total_bn': round(total / 1e9, 1),
        'exposed_people': int(d.loc[d.highly_exposed, 'employment'].sum()),
        'jobs_published': int(d.employees.notna().sum()),
    })
regions.sort(key=lambda x: -x['share'])
(SITE / 'assets' / 'regions.json').write_text(json.dumps(regions, separators=(',', ':')))
pd.DataFrame(regions).to_csv(SITE / 'csv' / 'regions_exposed_wage_share.csv', index=False)

# ---------------------------------------------------------------- regjobs.json
# People in each job by region (APS), so the map can show where a chosen job's workers are.
# null = suppressed by ONS as unreliable for that region.
reg_names = sorted(regemp.region.unique())
piv = regemp.pivot_table(index='soc2020', columns='region', values='employment', aggfunc='sum')[reg_names]
pub = regemp.pivot_table(index='soc2020', columns='region', values='employment', aggfunc='count')[reg_names]
reg_totals = regemp.groupby('region').employment.sum()[reg_names]
regjobs = {
    'regions': reg_names,
    'totals': [int(v) for v in reg_totals],
    'e': {c: [None if pub.loc[c, r] == 0 else int(piv.loc[c, r]) for r in reg_names] for c in piv.index},
}
(SITE / 'assets' / 'regjobs.json').write_text(json.dumps(regjobs, separators=(',', ':')))

# ---------------------------------------------------------------- ladders.json
# Career ladders by field: the SOC 2020 unit groups at each rung, from entry to senior.
# Rungs mostly follow SOC 2020 major groups, which ONS ties to skill level: 1 directors and managers,
# 2 professionals, 3 associate professionals and technicians, 4 administrative (6/7 for support roles).
# Exceptions are shown on the page (education's senior rung is head teachers and education managers, group 2).
# Rungs are skill levels within a field, not career routes.
# The grouping into fields is the author's, published in ladders_mapping.csv so it can be challenged.
RUNGS = ['Senior', 'Mid-level', 'Junior', 'Entry and support']
LADDERS = {
    'IT and digital': [['1137'], ['2131', '2132', '2133', '2134', '2135', '2136', '2137', '2139'], ['3131', '3133'], ['3132']],
    'Finance and accounting': [['1131'], ['2421', '2422', '2423', '2433'], ['3531', '3532', '3533', '3534'], ['4121', '4122', '4123', '4124', '4129', '4132']],
    'Legal': [[], ['2412', '2419'], ['3520'], ['4212']],
    'Marketing, PR and sales': [['1132', '1133'], ['2432', '2493', '2494'], ['3552', '3554', '3556'], ['4151', '7113']],
    'HR and people': [['1136'], [], ['3571'], ['4136']],
    'Business, consulting and projects': [['1139', '1258'], ['2431', '2434', '2439', '2440'], ['3543', '3544', '3549'], ['4159', '4215']],
    'Engineering': [['1121', '1122'], ['2121', '2122', '2123', '2124', '2125', '2126', '2127', '2129'], ['3112', '3113', '3114', '3116', '3119', '3120'], []],
    'Nursing and health': [['1171'], ['2231', '2232', '2233', '2234', '2235', '2236', '2237'], ['3213', '3219'], ['6131']],
    'Education': [['2321', '2322'], ['2313', '2314', '2316'], ['3231'], ['6112', '6113']],
    'Media and creative': [['1255'], ['2142', '2491', '2492'], ['3416', '3417'], []],
}
mi = m.set_index('soc2020')


def rung_stats(codes):
    d = mi.loc[[c for c in codes if c in mi.index]]
    if d.empty:
        return None
    sc = d.dropna(subset=['exposure_percentile'])
    py = d.dropna(subset=['median_pay_ft'])
    yg = d.dropna(subset=['young_share_2021'])
    dg = d.dropna(subset=['degree_share'])
    wavg = lambda x, w: None if len(x) == 0 else round(float(np.average(x, weights=w)), 3)
    return {
        'people': int(d.employment.sum()),
        'exposure_pct': wavg(sc.exposure_percentile, sc.employment),
        'pay': None if py.empty else int(round(np.average(py.median_pay_ft, weights=py.employment))),
        'young': wavg(yg.young_share_2021, yg.employment),
        'degree': wavg(dg.degree_share, dg.employment),
    }


ladders, lad_rows = [], []
for field, rungs in LADDERS.items():
    out = {'field': field, 'rungs': []}
    for name, codes in zip(RUNGS, rungs):
        for c in codes:
            assert c in mi.index, (field, c)
            lad_rows.append({'field': field, 'rung': name, 'soc2020': c, 'title': mi.loc[c, 'title']})
        out['rungs'].append({'rung': name, 'codes': codes, 'stats': rung_stats(codes)})
    ladders.append(out)
(SITE / 'assets' / 'ladders.json').write_text(json.dumps(ladders, separators=(',', ':')))
pd.DataFrame(lad_rows).to_csv(SITE / 'csv' / 'ladders_mapping.csv', index=False)

# ---------------------------------------------------------------- advert cross-check CSV
_x = json.loads((P / 'overall.json').read_text())['advert_crosscheck']
pd.DataFrame(_x['categories']).rename(columns={'adzuna': 'adzuna_change_jan_sep_2024_vs_2019', 'textkernel': 'textkernel_change_jan_sep_2024_vs_2019'}) \
    .to_csv(SITE / 'csv' / 'advert_crosscheck.csv', index=False)

# ---------------------------------------------------------------- CSV downloads
cols = ['soc2020', 'title', 'major_group', 'exposure', 'exposure_llm', 'exposure_rank', 'exposure_percentile', 'highly_exposed',
        'ilo_genai_score', 'young_share_2021', 'degree_share', 'crosswalk_coverage', 'crosswalk_main_soc2010', 'crosswalk_main_share', 'employment', 'employment_ci95', 'employees',
        'median_pay_ft', 'mean_pay_all', 'mean_pay_source', 'wage_bill_gbp', 'adverts_oct24_sep25', 'adverts_oct23_sep24',
        'adverts_nov21_oct22', 'adverts_2019', 'adverts_change_vs_nov21_oct22', 'adverts_change_vs_2019']
m[cols].to_csv(SITE / 'csv' / 'occupations_master.csv', index=False)
regemp.to_csv(SITE / 'csv' / 'employment_by_region_and_occupation.csv', index=False)
qs.reset_index().to_csv(SITE / 'csv' / 'adverts_quarterly_by_occupation.csv', index=False)

(SITE / 'csv' / 'join_log.md').write_text((P / 'join_log.md').read_text())

# ---------------------------------------------------------------- story pack numbers
s = m.dropna(subset=['exposure'])
top = s[s.highly_exposed]
bottom = s[s.exposure_percentile <= 0.2]


def wmed(d):
    d = d.dropna(subset=['median_pay_ft'])
    return float(np.average(d.median_pay_ft, weights=d.employment))


pay_gap = wmed(top) - wmed(bottom)
grads = m[m.major_group.isin(['2', '3'])]
trades = m[m.major_group.isin(['5', '6'])]
chg = lambda d, b: d.dropna(subset=['adverts_oct24_sep25', b]).adverts_oct24_sep25.sum() / d.dropna(subset=['adverts_oct24_sep25', b])[b].sum() - 1
pipe = s.sort_values('exposure_rank').head(10)
stats = {
    'pay_gap_gbp': round(wmed(top)) - round(wmed(bottom)),   # from the rounded figures, so it adds up on screen
    'top_fifth_median_pay': round(wmed(top)), 'bottom_fifth_median_pay': round(wmed(bottom)),
    'exposed_wage_bill_bn': round(top.wage_bill_gbp.sum() / 1e9),
    'exposed_wage_share': round(top.wage_bill_gbp.sum() / m.wage_bill_gbp.sum(), 3),
    'exposed_people': int(top.employment.sum()),
    'top_region': regions[0]['region'], 'top_region_share': regions[0]['share'],
    'advert_window': 'October 2024 to September 2025',
    'ons_imputed_from': '2025-10',
    'grad_adverts_vs_year_before': round(chg(grads, 'adverts_oct23_sep24'), 3),
    'trades_adverts_vs_year_before': round(chg(trades, 'adverts_oct23_sep24'), 3),
    'grad_adverts_vs_2019': round(chg(grads, 'adverts_2019'), 3),
    'trades_adverts_vs_2019': round(chg(trades, 'adverts_2019'), 3),
    'top_fifth_adverts_vs_2019': round(chg(top, 'adverts_2019'), 3),
    'bottom_fifth_adverts_vs_2019': round(chg(bottom, 'adverts_2019'), 3),
    'all_adverts_vs_2019': round(chg(m, 'adverts_2019'), 3),
    'spearman_exposure_pay': round(float(s[['exposure', 'median_pay_ft']].corr('spearman').iloc[0, 1]), 2),
    'degree_share_by_exposure_fifth': [round(float(np.average(d.degree_share.dropna(), weights=d.loc[d.degree_share.notna(), 'employment'])), 3)
                                       for _, d in s.groupby(pd.qcut(s.exposure, 5, labels=False))],
    'exposed_entry_heavy_adverts_vs_2019': round(chg(top[top.young_share_2021 >= top.young_share_2021.quantile(2 / 3)], 'adverts_2019'), 3),
    'exposed_few_young_adverts_vs_2019': round(chg(top[top.young_share_2021 <= top.young_share_2021.quantile(1 / 3)], 'adverts_2019'), 3),
    'dfe_ilo_rank_correlation': round(float(s[['exposure', 'ilo_genai_score']].dropna().corr('spearman').iloc[0, 1]), 2),
    **json.loads((P / 'overall.json').read_text()),
    'door_summary': {name: {'vs_2019': round(chg(m[m.soc2020.isin(codes)], 'adverts_2019'), 3),
                            'vs_year_before': round(chg(m[m.soc2020.isin(codes)], 'adverts_oct23_sep24'), 3)}
                     for name, codes in groups.items()},
    'top10': [{'soc': j.soc2020, 'title': j.title, 'people': int(j.employment)} for _, j in pipe.iterrows()],
}
(SITE / 'assets' / 'story.json').write_text(json.dumps(stats, indent=1))
print(json.dumps(stats, indent=1))
