"""Offline ONS extraction and original SVG generation. Python standard library.
Run: python3 reproduce.py --out reproduced [--render-png]
Frozen article and workbook are in raw/. No network, credentials or telemetry.
"""
from pathlib import Path
from hashlib import sha256
from html.parser import HTMLParser
import argparse,csv,html,json,re,subprocess,xml.etree.ElementTree as ET,zipfile

WORKBOOK_SHA='3d2ce76054548b9c3251de38add285209c757d72344e3737dc9bc74a3fef0f67'
ARTICLE_SHA='4a1417a1f09d931ad992ea2d83838544c6c763c0ef0c35c128f0823bd419a7e0'
NS={'s':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
OCCUPATIONS=['Managers, directors and senior officials','Professional occupations','Associate professional occupations','Administrative and secretarial occupations']
EXPECTED=[(26,5,47,30,56,48,64,240),(20,9,32,90,46,40,51,600),(23,13,33,110,44,32,56,120),(14,7,20,140,60,37,83,30)]
INK,MUTED,BLUE,RUST,PAPER,GRID='#172128','#53616b','#2457c5','#b54718','#faf9f6','#d9dedf'
FONT='-apple-system, BlinkMacSystemFont, Helvetica Neue, Arial, sans-serif'

class Text(HTMLParser):
    def __init__(self,s):
        super().__init__();self.parts=[];self.skip=0;self.feed(s)
    def handle_starttag(self,tag,attrs):
        if tag in ['script','style']:self.skip+=1
    def handle_endtag(self,tag):
        if tag in ['script','style']:self.skip=max(0,self.skip-1)
    def handle_data(self,s):
        if not self.skip:self.parts.append(s)
    def value(self):return ' '.join(' '.join(self.parts).split())

def tables(path):
    assert sha256(path.read_bytes()).hexdigest()==WORKBOOK_SHA,'Frozen ONS workbook differs'
    with zipfile.ZipFile(path) as z:
        strings=[''.join(x.itertext()) for x in ET.fromstring(z.read('xl/sharedStrings.xml')).findall('s:si',NS)]
        result={}
        rels={x.attrib['Id']:x.attrib['Target'] for x in ET.fromstring(z.read('xl/_rels/workbook.xml.rels'))}
        for sh in ET.fromstring(z.read('xl/workbook.xml')).findall('s:sheets/s:sheet',NS):
            target=rels[sh.attrib['{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id']]
            target=target.lstrip('/') if target.startswith('/') else 'xl/'+target
            rows={}
            for row in ET.fromstring(z.read(target)).findall('s:sheetData/s:row',NS):
                cells={}
                for c in row.findall('s:c',NS):
                    node=c.find('s:v',NS);v='' if node is None else node.text
                    if c.attrib.get('t')=='s':v=strings[int(v)]
                    elif c.attrib.get('t')=='inlineStr':v=''.join(c.find('s:is',NS).itertext())
                    cells[re.sub(r'\d','',c.attrib['r'])]=v or ''
                rows[int(row.attrib['r'])]=cells
            result[sh.attrib['name']]=rows
    return result

def extract(root):
    sheets=tables(root/'raw/ons-supplementary.xlsx');rows=sheets['Table_1']
    assert rows[2]['A']=='Great Britain, 8 January to 30 March 2025'
    assert rows[11]['D']=='Estimate (%)' and rows[11]['E']=='LCL' and rows[11]['F']=='UCL'
    assert '95%' in sheets['Cover'][17]['A'] and '30 or fewer' in sheets['Cover'][22]['A']
    assert 'Income includes income from all sources.' in sheets['Notes'][8]['B']
    all_rows=[];selected=[]
    for n,c in rows.items():
        if n<12 or not c.get('D'):continue
        all_rows.append({'source_sheet':'Table_1','source_row':n,'breakdown_category':c.get('A',''),'breakdown':c.get('B',''),'response':c.get('C',''),'estimate_pct':c.get('D',''),'ci95_low_pct':c.get('E',''),'ci95_high_pct':c.get('F',''),'sample_size_rounded':c.get('G',''),'weighted_count':c.get('H','')})
    for occupation,expected in zip(OCCUPATIONS,EXPECTED):
        pair={'occupation':occupation,'geography':'Great Britain','period_start':'2025-01-08','period_end':'2025-03-30'}
        for key,band in [('lower','Up to £20,000'),('higher','£50,000 and over')]:
            matches=[r for r in all_rows if r['breakdown_category']=='Income by occupation' and r['breakdown']==band+' '+occupation and r['response']=='Both worked from home and travelled to work']
            assert len(matches)==1
            r=matches[0]
            for field in ['estimate_pct','ci95_low_pct','ci95_high_pct','sample_size_rounded','weighted_count']:pair[key+'_'+field]=int(r[field])
            pair[key+'_source_row']=r['source_row'];pair[key+'_source_cell']='D'+str(r['source_row'])
            pair[key+'_income_band']=band
            pair[key+'_small_sample_caution']=str(int(r['sample_size_rounded'])<=30).lower()
        values=tuple(pair[k+'_'+f] for k in ['lower','higher'] for f in ['estimate_pct','ci95_low_pct','ci95_high_pct','sample_size_rounded'])
        assert values==expected,'Source values differ from independently checked cells'
        pair['point_gap_percentage_points']=pair['higher_estimate_pct']-pair['lower_estimate_pct']
        pair['gap_interpretation']='Difference of rounded point estimates; no confidence interval for this difference computed'
        selected.append(pair)
    article=root/'raw/ons-article.html';assert sha256(article.read_bytes()).hexdigest()==ARTICLE_SHA
    text=Text(article.read_text()).value();section=text.rsplit('Hybrid working was more common in higher income bands',1)[1].split('Higher earners tend',1)[0]
    article_checks=[]
    for p in selected:
        fragment=p['occupation'].lower()+' ('+str(p['higher_estimate_pct'])+'%, compared with '+str(p['lower_estimate_pct'])+'%, respectively)'
        assert fragment in section.lower(),'Article and workbook point estimates differ'
        article_checks.append(fragment)
    assert 'all income received from a range of sources' in text
    return selected,all_rows,article_checks

def save_csv(path,rows):
    with path.open('w',newline='') as f:
        w=csv.DictWriter(f,fieldnames=list(rows[0]));w.writeheader();w.writerows(rows)

def svg(rows,mobile=False):
    w,h=(400,1410) if mobile else (1200,1120)
    a=[f'<svg xmlns="http://www.w3.org/2000/svg" width="{w}" height="{h}" viewBox="0 0 {w} {h}" role="img" aria-labelledby="title desc">',
       '<title id="title">Hybrid work and personal income within four broad occupations</title>',
       '<desc id="desc">Great Britain, 8 January to 30 March 2025. Lower and higher personal annual gross income bands, rounded point estimates with published 95% confidence intervals. Managers26 and56 percent; professionals20 and46; associate professionals23 and44; administrative and secretarial14 and60. Two subgroups have rounded sample sizes of30. Broad groups do not compare the same job or identify causal effects.</desc>',
       f'<rect width="{w}" height="{h}" fill="{PAPER}"/>',f'<g font-family="{FONT}" fill="{INK}">']
    def t(x,y,s,size=20,color=INK,weight=400,anchor='start'):
        a.append(f'<text x="{x}" y="{y}" font-size="{size}" fill="{color}" font-weight="{weight}" text-anchor="{anchor}">{html.escape(s)}</text>')
    def line(x1,y1,x2,y2,color=GRID,width=1,extra=''):
        a.append(f'<line x1="{x1}" y1="{y1}" x2="{x2}" y2="{y2}" stroke="{color}" stroke-width="{width}" {extra}/>')
    def dot(x,y,color):a.append(f'<circle cx="{x}" cy="{y}" r="{5 if mobile else 8}" fill="{color}" stroke="{PAPER}" stroke-width="2"/>')
    m=24 if mobile else 58
    t(m,37 if mobile else 52,'MEPLAR / WORK & INCOME',12 if mobile else 16,MUTED,650)
    if mobile:
        for y,s in [(84,'Hybrid work has'),(121,'an income gap.'),(162,'Even within broad'),(192,'occupation groups.')]:t(m,y,s,32 if y<150 else 25,weight=700)
        t(m,230,'Great Britain · 8 Jan–30 Mar 2025',16,MUTED)
        t(m,259,'Share who worked both at home and elsewhere',13,MUTED)
        t(m,280,'in the prior seven days',13,MUTED)
        t(m,312,'Personal annual gross income',14,weight=650)
        dot(m+6,337,BLUE);t(m+21,342,'Up to £20,000',15,BLUE,650)
        dot(m+6,365,RUST);t(m+21,370,'£50,000 and over',15,RUST,650)
        t(m,403,'Dots: estimates   Bars: 95% confidence intervals',12,MUTED)
        x0,x1=28,372;top=510;step=151;bottom=top+3*step+45
        names=[['Managers, directors','and senior officials'],['Professional occupations'],['Associate professional','occupations'],['Administrative and','secretarial occupations']]
        for i,parts in enumerate(names):
            y=top+i*step
            for j,s in enumerate(parts):t(m,y-67+j*19,s,16,weight=600)
    else:
        t(m,116,'Hybrid work has an income gap.',48,weight=750)
        t(m,172,'Even within broad occupation groups.',40,weight=700)
        t(m,215,'Great Britain · 8 January–30 March 2025',23,MUTED)
        t(m,254,'Share who worked both at home and elsewhere in the prior seven days',22,MUTED)
        t(m,298,'Personal annual gross income',20,weight=650)
        dot(m+7,332,BLUE);t(m+25,339,'Up to £20,000',22,BLUE,650)
        dot(330,332,RUST);t(350,339,'£50,000 and over',22,RUST,650)
        t(m,379,'Dots: estimates     Bars: published 95% confidence intervals',18,MUTED)
        x0,x1=420,1118;top=456;step=112;bottom=top+3*step+35
        names=[['Managers, directors','and senior officials'],['Professional occupations'],['Associate professional','occupations'],['Administrative and','secretarial occupations']]
        for i,parts in enumerate(names):
            y=top+i*step
            for j,s in enumerate(parts):t(m,y-4+j*25,s,22,weight=600)
    x=lambda v:round(x0+(x1-x0)*v/100,2)
    for tick in range(0,101,20):
        line(x(tick),top-14,x(tick),bottom,GRID,0.8)
        t(x(tick),bottom+24,str(tick)+'%',12 if mobile else 17,MUTED,anchor='middle')
    for i,r in enumerate(rows):
        y=top+i*step
        # A neutral connector represents the point-estimate gap only. Colour bars retain each survey interval.
        line(x(r['lower_estimate_pct']),y,x(r['higher_estimate_pct']),y,'#b2babd',2)
        for key,color,dy in [('lower',BLUE,-3),('higher',RUST,3)]:
            lo,hi,point=[r[key+'_'+f] for f in ['ci95_low_pct','ci95_high_pct','estimate_pct']]
            line(x(lo),y+dy,x(hi),y+dy,color,3 if mobile else 4,'opacity="0.48"')
            for endpoint in [lo,hi]:line(x(endpoint),y+dy-5,x(endpoint),y+dy+5,color,1.5)
            dot(x(point),y+dy,color)
            caution='*' if r[key+'_small_sample_caution']=='true' else ''
            t(x(point),y-19,str(point)+'%'+caution,17 if mobile else 24,color,700,'middle')
        if mobile:
            t(m,y+35,'Lower: '+str(r['lower_ci95_low_pct'])+'–'+str(r['lower_ci95_high_pct'])+'%   Higher: '+str(r['higher_ci95_low_pct'])+'–'+str(r['higher_ci95_high_pct'])+'%',13,MUTED)
    if mobile:
        footer=[(1035,'* Rounded subgroup sample size: 30.'),(1055,'Treat these estimates with caution.'),(1086,'Four groups where hybrid work was most common.'),(1106,'Broad occupations are not the same job; this'),(1126,'comparison does not establish an income effect.'),(1157,'Income includes earnings, pensions, benefits,'),(1177,'savings, investments and property income.'),(1208,'Source: ONS Opinions and Lifestyle Survey,'),(1228,'supplementary Table 1; released 11 June 2025.'),(1248,'Weighted survey estimates; rounded percentages.'),(1278,'Contains public sector information licensed'),(1298,'under the Open Government Licence v3.0.'),(1330,'meplar.com · Source captured 22 Oct 2026')]
        for y,s in footer:t(m,y+50,s,14,MUTED)
    else:
        for y,s in [(897,'* Rounded subgroup sample size: 30. Treat these estimates with caution.'),(932,'Four groups where hybrid work was most common. Broad occupations are not the same job;'),(958,'this comparison does not establish an income effect. Income includes earnings and other sources.'),(1004,'Source: ONS Opinions and Lifestyle Survey, supplementary Table 1. Released 11 June 2025.'),(1030,'Weighted survey estimates; rounded percentages. Contains public sector information licensed under OGL v3.0.')]:t(m,y,s,18,MUTED)
        t(m,1081,'meplar.com',19,INK,650);t(1142,1081,'Source captured 22 Oct 2026',16,MUTED,anchor='end')
    a.append('</g></svg>\n');return '\n'.join(a)

def run(root,out,render=False):
    rows,all_rows,article_checks=extract(root);out.mkdir(parents=True,exist_ok=True)
    save_csv(out/'hybrid-income-by-occupation.csv',rows);save_csv(out/'ons-table1-source-extract.csv',all_rows)
    tidy=[]
    for r in rows:
        for key in ['lower','higher']:
            tidy.append({'occupation':r['occupation'],'income_band':r[key+'_income_band'],'hybrid_share_pct':r[key+'_estimate_pct'],'ci95_low_pct':r[key+'_ci95_low_pct'],'ci95_high_pct':r[key+'_ci95_high_pct'],'sample_size_rounded':r[key+'_sample_size_rounded'],'small_sample_caution':r[key+'_small_sample_caution'],'source_sheet':'Table_1','source_row':r[key+'_source_row'],'geography':r['geography'],'period_start':r['period_start'],'period_end':r['period_end']})
    save_csv(out/'chart-data.csv',tidy)
    for name,mobile in [('chart',False),('chart_mobile',True)]:
        (out/(name+'.svg')).write_text(svg(rows,mobile))
        if render:
            subprocess.run(['/opt/homebrew/bin/rsvg-convert','--width','800' if mobile else '1800','--output',str(out/(name+'.png')),str(out/(name+'.svg'))],check=True)
    return {'status':'PASS','selectedRows':len(rows),'sourceRows':len(all_rows),'articlePointEstimateChecks':len(article_checks),'pointGaps':[r['point_gap_percentage_points'] for r in rows],'sourceCells':[r[k+'_source_cell'] for r in rows for k in ['lower','higher']],'allSelectedCIAndSampleSizesVerified':True,'smallSampleCells':['G46','G98'],'incomeBandNote':'Workbook: Up to £20,000. Bulletin prose says under £20,000; retain workbook band label rather than invent a boundary interpretation.','confidenceNote':'Associate professional intervals overlap (13–33 vs32–56). No all-four statistical-significance claim or confidence interval for differences.'}

if __name__=='__main__':
    ap=argparse.ArgumentParser();ap.add_argument('--out',type=Path,default=Path('reproduced'));ap.add_argument('--render-png',action='store_true');args=ap.parse_args()
    print(json.dumps(run(Path(__file__).resolve().parent,args.out,args.render_png),indent=2))