"""Offline audit of the frozen Ministry CSV. No network calls or contact fields exported."""
import csv, json, hashlib, html, math, statistics
from pathlib import Path
from collections import Counter
from datetime import datetime, timezone

ROOT = Path(__file__).resolve().parent
SOURCE = ROOT / 'sources/library-source.csv'
EXPECTED_SHA256 = '5a851d53aa89cde640cd5313d57ddf7e48f3bd9c9b6554876aced1a11740f9ab'
assert hashlib.sha256(SOURCE.read_bytes()).hexdigest() == EXPECTED_SHA256
with SOURCE.open(encoding='utf-8-sig', newline='') as f:
    reader = csv.DictReader(f, delimiter=';')
    fields = reader.fieldnames
    records = list(reader)
assert len({r['Code_bib'] for r in records}) == len(records)
assert all(r['Code_bib'] for r in records)
MUNICIPAL = {'Bibliothèque municipale', 'Bibliothèque intercommunale', 'Bibliothèque municipale classée'}

def in_scope(r):
    return r['Statut'] in MUNICIPAL and r['type_adresse'] == 'Bâtiment ouvert'

def numeric(raw, bounded=False):
    if raw.strip() == '': return 'unreported', None
    try:
        v = float(raw.replace(',', '.'))
    except ValueError: return 'invalid_numeric', None
    if not math.isfinite(v) or v < 0: return 'invalid_numeric', v
    if bounded and v > 168: return 'above_168_hours', v
    return ('explicit_zero' if v == 0 else 'positive'), v

def categorical(raw):
    return {'Oui': 'yes', 'Non': 'no', '': 'unreported'}.get(raw, 'unrecognized')

variables = [
    ('Amplitude horaire', 'Weekly opening hours', 'numeric_hours'),
    ('ouverture_le_dimanche', 'Sunday-opening field', 'yes_no'),
    ('connexion_wi_fi', 'Wi-Fi field', 'yes_no'),
    ('nombre_d_entrees', 'Entries field', 'numeric'),
    ('nombre_de_prets', 'Loans field', 'numeric'),
    ('nombre_de_places', 'Places field', 'numeric'),
    ('nb_postes_informatiques_publics', 'Public-computer field', 'numeric'),
]
cohort = [r for r in records if in_scope(r)]
summaries = []
for cohort_name, rs in [('all_published_records', records), ('municipal_open_building_records', cohort)]:
    for field, label, kind in variables:
        statuses = Counter(categorical(r[field]) if kind == 'yes_no' else numeric(r[field], kind == 'numeric_hours')[0] for r in rs)
        usable = sum(statuses[k] for k in ('yes', 'no', 'positive', 'explicit_zero'))
        summaries.append({'cohort': cohort_name, 'field': field, 'label': label, 'denominator_records': len(rs), 'populated_usable_cells': usable, 'populated_usable_pct': round(100*usable/len(rs), 2), **{k: statuses[k] for k in ['yes', 'no', 'positive', 'explicit_zero', 'unreported', 'above_168_hours', 'invalid_numeric', 'unrecognized']}})

def write_csv(name, rows):
    with (ROOT/name).open('w', encoding='utf-8', newline='') as f:
        writer = csv.DictWriter(f, fieldnames=list(rows[0])); writer.writeheader(); writer.writerows(rows)

write_csv('field-completeness.csv', summaries)
flow = [
    {'stage': 'All published records', 'records': len(records), 'excluded_at_stage': 0},
    {'stage': 'Municipal / intercommunal / classified municipal status', 'records': sum(r['Statut'] in MUNICIPAL for r in records), 'excluded_at_stage': sum(r['Statut'] not in MUNICIPAL for r in records)},
    {'stage': 'Also labelled Bâtiment ouvert', 'records': len(cohort), 'excluded_at_stage': sum(r['Statut'] in MUNICIPAL and r['type_adresse'] != 'Bâtiment ouvert' for r in records)},
]
write_csv('cohort-flow.csv', flow)

clean = []
for r in records:
    row = {'library_id': r['Code_bib'], 'region_code': r['code_region'], 'region_label': r['Région'], 'department_code': r['code_departement'], 'status_label': r['Statut'], 'address_type_label': r['type_adresse'], 'primary_cohort': int(in_scope(r))}
    for field, _, kind in variables:
        key = {'Amplitude horaire': 'weekly_hours', 'ouverture_le_dimanche': 'sunday', 'connexion_wi_fi': 'wifi', 'nombre_d_entrees': 'entries', 'nombre_de_prets': 'loans', 'nombre_de_places': 'places', 'nb_postes_informatiques_publics': 'public_computers'}[field]
        if kind == 'yes_no': row[key+'_raw'] = r[field]; row[key+'_cell_status'] = categorical(r[field])
        else:
            status, v = numeric(r[field], kind == 'numeric_hours')
            row[key+'_raw'] = r[field]; row[key+'_value'] = '' if v is None else v; row[key+'_cell_status'] = status
    clean.append(row)
write_csv('library-record-audit.csv', clean)

hours = [numeric(r['Amplitude horaire'], True)[1] for r in cohort if numeric(r['Amplitude horaire'], True)[0] == 'positive']
complete = sum(numeric(r['Amplitude horaire'], True)[0] in ('positive','explicit_zero') and categorical(r['ouverture_le_dimanche']) in ('yes','no') and categorical(r['connexion_wi_fi']) in ('yes','no') for r in cohort)
hour_bands = Counter()
for r in cohort:
    s, v = numeric(r['Amplitude horaire'], True)
    label = s if s != 'positive' else '0<h<10' if v < 10 else '10<=h<20' if v < 20 else '20<=h<40' if v < 40 else '40<=h<=168'
    hour_bands[label] += 1
bands = [{'hours_category': k, 'records': hour_bands[k], 'cohort_denominator': len(cohort)} for k in ['unreported','explicit_zero','0<h<10','10<=h<20','20<=h<40','40<=h<=168','above_168_hours','invalid_numeric']]
band_labels = {'unreported':'Unreported', 'explicit_zero':'Explicit zero', '0<h<10':'Over 0, under 10 hours', '10<=h<20':'10 to under 20 hours', '20<=h<40':'20 to under 40 hours', '40<=h<=168':'40 to 168 hours', 'above_168_hours':'Above 168: review flag', 'invalid_numeric':'Other numeric flags'}
for band in bands: band['hours_category_label'] = band_labels[band['hours_category']]
write_csv('weekly-hour-bands.csv', bands)

regional = []
for region in sorted({r['Région'] for r in cohort}):
    rs = [r for r in cohort if r['Région'] == region]
    regional.append({'region': region or '[unreported]', 'published_cohort_records': len(rs), 'hours_unreported_cells': sum(not r['Amplitude horaire'].strip() for r in rs), 'sunday_unreported_cells': sum(not r['ouverture_le_dimanche'].strip() for r in rs), 'wifi_unreported_cells': sum(not r['connexion_wi_fi'].strip() for r in rs), 'note': 'Export-cell counts; not regional service coverage or library performance.'})
write_csv('regional-field-completeness.csv', regional)

dictionary = []
for f, label, kind in variables:
    dictionary.append({'source_field': f, 'public_label': label, 'observed_storage': 'text in semicolon CSV', 'analytical_type': kind, 'unit_or_values': 'Usual weekly opening hours, per producer description' if kind == 'numeric_hours' else 'Literal Oui / Non / empty' if kind == 'yes_no' else 'Numeric cells; no totals used', 'blank_rule': 'Unreported in this export; never recode as zero/no', 'zero_rule': 'Preserved as explicit numeric zero' if kind != 'yes_no' else 'Not a valid yes/no code', 'definition_limit': 'Published definition inspected; >168 flagged, not repaired' if kind == 'numeric_hours' else 'CSV label and actual values inspected. Full observation-2023 item dictionary not obtained; no detailed service-scope or compliance claims.'})
write_csv('analytical-variable-dictionary.csv', dictionary)

def svg_bar(name, title, subtitle, data, maximum=None, label_width=290):
    width=1040; barwidth=530; height=165+len(data)*48; maximum=maximum or max(v for _,v in data)
    bits=[f'<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 {width} {height}" role="img" aria-labelledby="title desc"><title id="title">{html.escape(title)}</title><desc id="desc">{html.escape(subtitle)}. '+html.escape('; '.join(f'{k}: {v:,}' for k,v in data))+'</desc><rect width="100%" height="100%" fill="#f8f5ef"/><g font-family="Arial,sans-serif" fill="#1d2837">',f'<text x="35" y="40" font-size="26" font-weight="700">{html.escape(title)}</text>',f'<text x="35" y="70" font-size="15">{html.escape(subtitle)}</text>']
    for i,(label,value) in enumerate(data):
        y=110+i*48; bw=value/maximum*barwidth
        bits.extend([f'<text x="35" y="{y+20}" font-size="16">{html.escape(label)}</text>',f'<rect x="{label_width}" y="{y}" width="{bw:.2f}" height="28" rx="3" fill="#176d75"/>',f'<text x="{label_width+bw+12:.2f}" y="{y+20}" font-size="16" font-weight="700">{value:,}</text>'])
    bits.append(f'<text x="35" y="{height-25}" font-size="13">Source: Ministère de la Culture; original export audit, 28 October 2026. Activity year: 2023.</text></g></svg>')
    (ROOT/name).write_text(''.join(bits),encoding='utf-8')

primary=[r for r in summaries if r['cohort']=='municipal_open_building_records']
svg_bar('chart-1-known-cells.svg','Which fields contain unflagged populated values?','Municipal/intercommunal open-building cohort; counts, not service availability',[(r['label'],r['populated_usable_cells']) for r in primary],len(cohort))
svg_bar('chart-2-hour-cells.svg','Weekly hours: blank is not zero',f'All {len(cohort):,} primary-cohort records retained; values above 168 are flagged',[(r['hours_category_label'],r['records']) for r in bands])
svg_bar('chart-3-cohort.svg','Choose the denominator before the headline','Record-selection flow; none of these is a current census of reader access',[(r['stage'].replace('Municipal / intercommunal / classified municipal status','Eligible municipal statuses').replace('Also labelled Bâtiment ouvert','Also labelled open building'),r['records']) for r in flow])

summary = {'computed_at_utc': datetime.now(timezone.utc).isoformat(), 'status': 'qualified_as_credited_export_completeness_reference', 'activity_year': 2023, 'address_snapshot_date_per_producer': '2025-06-05', 'resource_last_modified_metadata': '2025-08-27T13:07:19.762000+00:00', 'http_last_modified': '2026-10-19T10:09:23Z', 'snapshot_downloaded_utc': '2026-10-28T21:54:22.146801+00:00', 'source_sha256': EXPECTED_SHA256, 'source_sha1_matches_metadata': True, 'source_columns': len(fields), 'published_records': len(records), 'primary_cohort_records': len(cohort), 'all_three_fields_usable': complete, 'all_three_fields_usable_pct': round(100*complete/len(cohort),2), 'positive_hours_subset_records': len(hours), 'field_completeness': primary, 'hours_bands': bands, 'cohort_flow': flow, 'excluded_from_public_export': ['Telephone','Street and full postal address','Institution/person names','Website/contact routes','Coordinates','Commune population'], 'data_dictionary_limit': 'Actual CSV schema/values and published description inspected. A complete observation-2023 survey item dictionary was not obtained; ambiguous fields are excluded and the analysis concerns cells, not implied service definitions.'}
(ROOT/'analysis-summary.json').write_text(json.dumps(summary,ensure_ascii=False,indent=2)+'\n',encoding='utf-8')