"""Offline conversion and denominator audit of frozen official Aragón PX tables."""
from pathlib import Path
from itertools import product
from collections import Counter
from datetime import datetime, timezone
import csv, hashlib, html, json, math, re

ROOT=Path(__file__).resolve().parent
OUT=ROOT/'derived'
OUT.mkdir(exist_ok=True)

def sha(path): return hashlib.sha256(path.read_bytes()).hexdigest()
def quoted(text): return [s.replace('""','"') for s in re.findall(r'"((?:[^"]|"")*)"',text)]

def parse(code):
    path=ROOT/'sources'/('aragon-'+code+'.px')
    raw=path.read_bytes()
    expected=json.loads((ROOT/'source-hashes.json').read_text(encoding='utf-8'))
    assert hashlib.sha256(raw).hexdigest()==expected[path.name], 'Frozen source hash mismatch'
    encoding=re.search(br'CODEPAGE="([^"]+)"',raw).group(1).decode('ascii')
    text=raw.decode(encoding)
    metadata,data=re.split(r'\bDATA\s*=\s*',text,maxsplit=1)
    axes=quoted(re.search(r'STUB=(.*?);',metadata,re.S).group(1))+quoted(re.search(r'HEADING=(.*?);',metadata,re.S).group(1))
    values={axis:quoted(re.search(r'VALUES\("'+re.escape(axis)+r'"\)=(.*?);',metadata,re.S).group(1)) for axis in axes}
    tokens=re.findall(r'"(?:[^"]|"")*"|[^\s;]+',data)
    assert len(tokens)==math.prod(len(values[a]) for a in axes)
    records=[]
    for coordinate,token in zip(product(*(values[a] for a in axes)),tokens):
        value=None if token=='".."' else float(token)
        if value is not None and value.is_integer(): value=int(value)
        row=dict(zip(axes,coordinate));row['value']=value;row['source_token']=token
        records.append(row)
    notes=' '.join(quoted(re.search(r'NOTE=(.*?);',metadata,re.S).group(1))) if 'NOTE=' in metadata else ''
    return {'code':code,'file':path.name,'sha256':sha(path),'encoding':encoding,'axes':axes,'values':values,'notes':notes,'records':records,'cells':len(records)}

tables={code:parse(code) for code in ['A01','A03','C03','E01','E02']}
def cell(code,year,territory,index):
    table=tables[code];axis=table['axes'][-1];indicator=table['values'][axis][index]
    return next(row['value'] for row in table['records'] if row['Año']==str(year) and row['Territorio'].strip()==territory and row[axis]==indicator)

long=[]
for code,t in tables.items():
    for row in t['records']:
        indicator=row[t['axes'][-1]]
        unit='library administrative units'
        if code=='E01':
            unit='registered accounts (thousands)' if 'En miles' in indicator else ('registered accounts per100000 inhabitants' if '100.000' in indicator else 'registered accounts per reporting library')
        elif code=='E02':
            unit='visits (thousands)' if 'En miles' in indicator else ('visits per100000 inhabitants' if '100.000' in indicator else 'visits per reporting library')
        long.append({'table':code,'year':row['Año'],'territory':row['Territorio'].strip(),'source_territory_label':row['Territorio'],'indicator':indicator,'unit':unit,'value':row['value'],'missing':row['value'] is None,'source_token':row['source_token'],'source_file':t['file']})

summary=[]
types=[]
for year in [2024,2022,2020]:
    for territory in ['Aragón','Huesca','Teruel','Zaragoza','España']:
        census=cell('A01',year,territory,0);responding=cell('A01',year,territory,1)
        assert responding==cell('A03',year,territory,0)==cell('C03',year,territory,0)
        broad=[cell('A03',year,territory,i) for i in range(1,6)]
        assert sum(broad)==responding
        row={'year':year,'territory':territory,'geographic_scope':('country total' if territory=='España' else 'autonomous community' if territory=='Aragón' else 'province'),'censused_administrative_units':census,'returned_questionnaires':responding,'response_rate_percent_derived':100*responding/census,'public_type_respondents':cell('A03',year,territory,2),'public_type_share_of_respondents_percent_derived':100*cell('A03',year,territory,2)/responding,'registered_accounts_thousands':cell('E01',year,territory,0),'adult_registered_accounts_thousands':cell('E01',year,territory,1),'child_registered_accounts_thousands':cell('E01',year,territory,2),'visits_thousands':cell('E02',year,territory,0),'visits_per_reporting_library_published':cell('E02',year,territory,1),'in_room_internet_positive_reports':cell('C03',year,territory,1),'wifi_positive_reports':cell('C03',year,territory,2),'online_catalogue_positive_reports':cell('C03',year,territory,3),'electronic_collection_positive_reports':cell('C03',year,territory,4),'electronic_collection_item_nonresponse_warning':True}
        summary.append(row)
        if year==2024:
            for index in range(1,6):
                n=cell('A03',year,territory,index)
                types.append({'year':year,'territory':territory,'broad_library_type':tables['A03']['values']['Tipología'][index],'reporting_administrative_units':n,'share_of_reporting_units_percent_derived':100*n/responding})

def csv_write(path,rows):
    with path.open('w',encoding='utf-8-sig',newline='') as handle:
        writer=csv.DictWriter(handle,fieldnames=list(rows[0]));writer.writeheader();writer.writerows(rows)
csv_write(OUT/'official-source-cells-utf8.csv',long)
csv_write(OUT/'library-denominators.csv',summary)
csv_write(OUT/'broad-library-types-2024.csv',types)

aragon=next(r for r in summary if r['year']==2024 and r['territory']=='Aragón')
provinces=[r for r in summary if r['year']==2024 and r['geographic_scope']=='province']
notes={
 'bulletin_prose_visits_registration_label_mismatch':{'bulletin_prose_label':'usuarios inscritos','bulletin_prose_number':4336600,'bulletin_table_registered_accounts_thousands':396.8,'raw_E01_registered_accounts_thousands':aragon['registered_accounts_thousands'],'raw_E02_visits_thousands':aragon['visits_thousands'],'interpretation':'The prose figure matches the visits variable after scaling thousands, while the bulletin table agrees with raw registered-account figures. This is a source-consistency discrepancy, not an acknowledged correction. No official response obtained.'},
 'provincial_visits_nonreconciliation':{'province_cells_sum_thousands':sum(r['visits_thousands'] for r in provinces),'regional_cell_thousands':aragon['visits_thousands'],'difference_thousands':sum(r['visits_thousands'] for r in provinces)-aragon['visits_thousands'],'interpretation':'Preserve original values and precision. Cause not established; do not silently reconcile or assume all cells are exact unrounded event counts.'},
 'source_limits':['The unit is a library administrative unit, not necessarily one physical service point.','Only returned-questionnaire units underlie detailed tables.','Registered users are recorded registrations/eligibilities, not demonstrated unique individuals. REBIUN consortium eligibility is included from2020.','Visits are events, not unique people, books or completed reading.','Electronic-collection access has explicitly high item nonresponse in public libraries; low positive reports cannot be treated as proven service absence.','Territories overlap: Spain includesAragón, which includesHuesca/Teruel/Zaragoza. Do not add them.','A03 includes both TOTAL/subtotal and nested specialised categories. Only five mutually exclusive broad groups are used for the composition chart.','HTTP headers saidUTF-8, but internal CODEPAGE and actual bytes requireISO-8859-15 decoding. Derived exports areUTF-8.','The PXCREATION-DATE20230704 is legacy metadata, not the observation year or publication date. Source files include2024 and were last modified08July2026.']}
analysis={'status':'qualified_dated_source_accessibility_and_denominator_audit','generated_at_utc':datetime.now(timezone.utc).isoformat(),'observation_years':[2024,2022,2020],'headline_observation_year':2024,'scope':'Aragón plus its three provinces and a Spain-total comparator as provided in the same official tables. Not a complete community-by-community Spain study.','raw_cells':len(long),'raw_missing_cells':sum(r['missing'] for r in long),'summary_rows':len(summary),'broad_type_2024_rows':len(types),'headline':aragon,'notes':notes,'license':{'name':'CC BY4.0 data-specific default','url':'https://opendata.aragon.es/informacion/terminos-de-uso-licencias','attribution':'Fuente de los datos: Gobierno de Aragón. IAEST / Ministerio de Cultura, Estadística de Bibliotecas.','modifications':'PX decoded toUTF-8 and reshaped; response/type shares calculated; source-unit and source-consistency annotations added.','endorsement':'None claimed.','scope_note':'Data-specific terms explicitly allow derived analysis and commercial/noncommercial reuse by default. General portal terms expressly delegate Open Data to its own terms. No government logos or graphic design reused.'},'novelty':'A dated explanatory transformation and consistency audit, not a first-ever library count, new survey or first national analysis. Exact underlying source statistics remain attributed.'}
(OUT/'analysis.json').write_text(json.dumps(analysis,ensure_ascii=False,indent=2)+'\n',encoding='utf-8')

fields=['territory','censused_administrative_units','returned_questionnaires','response_rate_percent_derived','registered_accounts_thousands','visits_thousands','public_type_respondents']
parts=['<table><caption>Official2024 library statistics: distinct denominators and units</caption><thead><tr>'+''.join('<th scope="col">'+html.escape(k.replace('_',' '))+'</th>' for k in fields)+'</tr></thead><tbody>']
for r in summary:
    if r['year']==2024: parts.append('<tr>'+''.join('<td>'+html.escape(str(round(r[k],4) if isinstance(r[k],float) else r[k]))+'</td>' for k in fields)+'</tr>')
parts.append('</tbody></table><p>Source: Gobierno de Aragón/IAEST; derived response rates. Registrations are not unique people; visits are events. Library totals are responding administrative units. Geography rows overlap.</p>')
(OUT/'table-2024.html').write_text('\n'.join(parts),encoding='utf-8')

def esc(s): return html.escape(str(s),quote=True)
COLORS=['#14554c','#3a766d','#70a094','#a5c0b7','#d1ddd6']
def svg_start(title,desc,height=560):
    return [f'<svg xmlns="http://www.w3.org/2000/svg" width="1200" height="{height}" viewBox="0 0 1200 {height}" role="img" aria-labelledby="title desc"><title id="title">{esc(title)}</title><desc id="desc">{esc(desc)}</desc><rect width="1200" height="{height}" fill="#f7faf7"/><g font-family="Arial, sans-serif" fill="#153d37"><text x="56" y="55" font-size="30" font-weight="700">{esc(title)}</text>']
def svg_end(parts,height,footnote):
    parts += [f'<text x="56" y="{height-66}" font-size="17">{esc(footnote)}</text>',f'<text x="56" y="{height-36}" font-size="15">Source: Gobierno de Aragón / IAEST · 2024 data · Derived analysis for Alisson Bento research</text>','</g></svg>']
    return '\n'.join(parts)

rows2024=[r for r in summary if r['year']==2024]
parts=svg_start('Census entries and returned questionnaires, 2024','Five source territories. Bars show returned/censused library administrative units, not physical branches.')
for i,r in enumerate(rows2024):
    y=118+i*65;pct=r['response_rate_percent_derived'];parts.extend([f'<text x="56" y="{y+19}" font-size="20">{esc(r["territory"])}</text>',f'<rect x="225" y="{y}" width="650" height="30" rx="5" fill="#e2ebe5"/>',f'<rect x="225" y="{y}" width="{650*pct/100:.3f}" height="30" rx="5" fill="#14554c"/>',f'<text x="899" y="{y+21}" font-size="21">{r["returned_questionnaires"]:,} / {r["censused_administrative_units"]:,} · {pct:.2f}%</text>'])
(OUT/'response-denominators.svg').write_text(svg_end(parts,560,'Detailed statistics use the returned-questionnaire denominator. Geography rows overlap.'),encoding='utf-8')

parts=svg_start('Registrations and visits are different measures','Aragón and provinces in2024. Two separately scaled panels retain the source units of thousands. Neither measures unique readers.',760)
territories=['Aragón','Huesca','Teruel','Zaragoza']
for panel,(field,label,maximum,color) in enumerate([('registered_accounts_thousands','Recorded registrations ·thousands',450,'#14554c'),('visits_thousands','Recorded visits ·thousands',4500,'#3a766d')]):
    top=105+panel*270;parts.append(f'<text x="56" y="{top}" font-size="23" font-weight="700">{esc(label.replace("·thousands", "· thousands"))}</text>')
    for i,territory in enumerate(territories):
        r=next(r for r in rows2024 if r['territory']==territory);y=top+28+i*48;value=r[field]
        parts.extend([f'<text x="56" y="{y+21}" font-size="20">{esc(territory)}</text>',f'<rect x="225" y="{y}" width="{730*value/maximum:.3f}" height="29" fill="{color}" rx="4"/>',f'<text x="{240+730*value/maximum:.3f}" y="{y+21}" font-size="21">{value:,.1f}</text>'])
(OUT/'registrations-and-visits.svg').write_text(svg_end(parts,760,'Different units and panel scales. Source cells retain reported precision; provincial visits do not exactly sum.'),encoding='utf-8')

parts=svg_start('Which libraries are in Aragón’s 293 responses?','Five mutually exclusive broad types in 2024; specialised subcategories are not added a second time.')
labels=['National/regional central','Public','Specific user groups','Higher education','Specialised']
for i,(index,label) in enumerate(zip(range(1,6),labels)):
    n=cell('A03',2024,'Aragón',index);y=115+i*64;parts.extend([f'<text x="56" y="{y+21}" font-size="20">{esc(label)}</text>',f'<rect x="340" y="{y}" width="{640*n/230:.3f}" height="30" rx="4" fill="{COLORS[i]}"/>',f'<text x="{355+640*n/230:.3f}" y="{y+22}" font-size="21">{n} · {100*n/293:.2f}%</text>'])
(OUT/'reporting-library-types.svg').write_text(svg_end(parts,560,'Administrative units, not an all-public-library or unique-visitor denominator. Broad categories sum to 293.'),encoding='utf-8')

import pymupdf
for path in OUT.glob('*.svg'):
    svg_doc=pymupdf.open(path)
    pdf_doc=pymupdf.open('pdf',svg_doc.convert_to_pdf())
    pdf_doc[0].get_pixmap(matrix=pymupdf.Matrix(1,1),alpha=False).save(path.with_suffix('.png'))

validation={'status':'passed_offline_reconciliation','raw_cells':len(long),'missing_cells_preserved_as_null':sum(r['missing'] for r in long),'row_axes_verified':True,'all15_detail_totals_equal_response_totals':True,'all15_broad_type_sums_equal_response_totals':True,'all_source_numbers_retained_without_silent_correction':True,'published_province_visits_nonreconciliation_disclosed':True,'raw_sources':{t['file']:t['sha256'] for t in tables.values()},'derived_files':{p.name:sha(p) for p in sorted(OUT.iterdir()) if p.is_file()},'no_unique_people_or_causal_inference':True,'no_emails_or_publication':True}
(ROOT/'validation.json').write_text(json.dumps(validation,ensure_ascii=False,indent=2)+'\n',encoding='utf-8')
print(json.dumps({'cells':len(long),'missing':validation['missing_cells_preserved_as_null'],'aragon2024':aragon,'province_visits_difference_thousands':notes['provincial_visits_nonreconciliation']['difference_thousands']},ensure_ascii=False))