"""Reproduce descriptive comparisons from Istat's public 2024 reader tables.
Python 3 standard library only. Run in any directory; outputs beside this script.
No survey microdata, causal inference, significance testing or inferred joint counts.
Source: Istat, I cittadini e il tempo libero 2024, tables 21 and 22, CC BY 4.0.
"""
from pathlib import Path
from decimal import Decimal
import csv, hashlib, json, urllib.request, zipfile
import xml.etree.ElementTree as ET

ROOT = Path(__file__).resolve().parent
URL = 'https://www.istat.it/wp-content/uploads/2025/12/Tavole_lettura_scrittura-libri_prelettura_podcast_2024.xlsx'
SOURCE = ROOT / 'source' / 'istat-reading-2024.xlsx'
EXPECTED_SHA256 = 'cdd6568e6650e45d263c9253c8d5beac8e0f59ae530a954989168486ab6e99a3'
if not SOURCE.exists():
    SOURCE.parent.mkdir(exist_ok=True)
    with urllib.request.urlopen(URL, timeout=60) as response:
        SOURCE.write_bytes(response.read())
if hashlib.sha256(SOURCE.read_bytes()).hexdigest() != EXPECTED_SHA256:
    raise ValueError('Istat source version differs from audited snapshot; review before regenerating published results.')
NS = {'s': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
with zipfile.ZipFile(SOURCE) as archive:
    strings = [''.join(si.itertext()) for si in ET.fromstring(archive.read('xl/sharedStrings.xml')).findall('s:si', NS)]
    def cells(sheet):
        result = {}
        for cell in ET.fromstring(archive.read(f'xl/worksheets/sheet{sheet}.xml')).findall('.//s:sheetData/s:row/s:c', NS):
            v = cell.find('s:v', NS)
            if v is None: continue
            value = strings[int(v.text)] if cell.get('t') == 's' else v.text
            result[cell.attrib['r']] = value.strip()
        return result
    regional = cells(44)
    ages = cells(42)

def number(raw):
    if raw == '-': return 0.0
    if raw in ('..', '....', '', None): return None
    return float(Decimal(raw).quantize(Decimal('0.1')))

CHANNELS = ['self_choice', 'personal_recommendations', 'expert_recommendations', 'social_media', 'traditional_media', 'other']
results = []
for r in list(range(8,30)) + list(range(32,37)) + list(range(39,46)):
    label = regional['A'+str(r)]
    kind = 'province' if r in (13,14) else ('region' if r < 30 else ('macroregion' if r < 37 else ('national' if r == 45 else 'municipality_type')))
    item = dict(scope=kind, label=label, reference_year=2024, universe='leisure book readers aged 6+ in same area', source_table='22', source_row=r)
    for col, key in zip('BCDEFG', CHANNELS): item[key] = number(regional.get(col+str(r)))
    item['personal_minus_social_pp'] = round(item['personal_recommendations'] - item['social_media'], 1)
    item['personal_social_ratio'] = round(item['personal_recommendations'] / item['social_media'], 3) if item['social_media'] else None
    results.append(item)

age_results = []
for r in range(43,52):
    item = dict(scope='adult_age', label=ages['A'+str(r)], reference_year=2024, universe='leisure book readers in same adult age band, sexes combined', source_table='21', source_row=r)
    for col,key in zip('BCDEFG',CHANNELS): item[key] = number(ages.get(col+str(r)))
    item['personal_minus_social_pp'] = round(item['personal_recommendations']-item['social_media'],1)
    item['personal_social_ratio'] = round(item['personal_recommendations']/item['social_media'],3) if item['social_media'] else None
    age_results.append(item)

regions = [r for r in results if r['scope']=='region']
national = next(r for r in results if r['scope']=='national')
assert len(regions)==20
assert regional['C45']=='22.9' and number(regional['E45'])==7.2
assert number(regional['C23'])==15.6 and number(regional['E23'])==3.6
assert number(regional['C20'])==23.6 and number(regional['E20'])==10.4
assert number(ages['C43'])==28.8 and number(ages['E43'])==14.9
assert all(r['personal_recommendations'] > r['social_media'] for r in regions)
summary = {
    'regions_compared':len(regions),
    'regions_personal_exceeds_social':sum(r['personal_recommendations']>r['social_media'] for r in regions),
    'regions_personal_at_least_double_social':sum(r['personal_recommendations']>=2*r['social_media'] for r in regions),
    'national_personal_pct':national['personal_recommendations'],
    'national_social_pct':national['social_media'],
    'national_personal_minus_social_pp':national['personal_minus_social_pp'],
    'national_ratio':national['personal_social_ratio'],
    'smallest_regional_gap_pp':min(r['personal_minus_social_pp'] for r in regions),
    'largest_regional_gap_pp':max(r['personal_minus_social_pp'] for r in regions),
    'source_sha256':hashlib.sha256(SOURCE.read_bytes()).hexdigest(),
}
for filename,rows in [('results.csv',results),('adult-age-results.csv',age_results)]:
    with (ROOT/filename).open('w',encoding='utf-8',newline='') as out:
        writer=csv.DictWriter(out,fieldnames=list(rows[0]));writer.writeheader();writer.writerows(rows)
(ROOT/'results.json').write_text(json.dumps({'summary':summary,'regional_results':results,'adult_age_results':age_results},ensure_ascii=False,indent=2)+'\n',encoding='utf-8')
print(json.dumps(summary,ensure_ascii=False,indent=2))