"""Descriptive precision audit of published DCMS K3/K6 cells; no new survey estimates."""
import csv
import hashlib
import html
import json
import re
import statistics
from collections import Counter
from datetime import datetime, timezone
from pathlib import Path

ROOT = Path(__file__).resolve().parent
DATA = ROOT / 'data'
DATA.mkdir(exist_ok=True)
SOURCE = ROOT / 'sources/libraries-2025-26.ods'
SOURCE_SHA = '98298225d25ff0ac08c6c5f5e4f1bbbab5ed4b459dab55094de9fb04c8ccc33e'
assert hashlib.sha256(SOURCE.read_bytes()).hexdigest() == SOURCE_SHA
PLAN = json.loads((ROOT / 'predeclared-plan.json').read_text(encoding='utf-8'))
SHEETS = {s['name']: s for s in json.loads((ROOT / 'workbook-sparse.json').read_text(encoding='utf-8'))}
URL = 'https://assets.publishing.service.gov.uk/media/6a705344862aaf18d9c62b27/community_and_engagement_2025_26_libraries_data_tables.ods'
ATTRIBUTION = 'Contains public sector information licensed under the Open Government Licence v3.0. Source: DCMS Community and Engagement Survey 2025/26. Calculations and presentation: Alisson Bento.'

def cells(row):
    return {c['column']: c for c in row['cells']}

def text(mapping, column):
    return mapping.get(column, {}).get('text', '')

def number(mapping, column):
    item = mapping.get(column, {})
    value = item.get('value_raw')
    return None if value is None else float(value)

def csv_out(name, rows):
    if not rows:
        return
    with (DATA / name).open('w', encoding='utf-8-sig', newline='') as handle:
        writer = csv.DictWriter(handle, fieldnames=list(rows[0]))
        writer.writeheader()
        writer.writerows(rows)

records, excluded, totals, summaries = [], [], {}, []
all_codes, valid_codes = {}, {}
for mode, sheet_name in [('physical', 'K3'), ('digital', 'K6')]:
    sheet = SHEETS[sheet_name]
    rows = sheet['rows']
    national_cells = cells(next(row for row in rows if row['row'] == 9))
    totals[mode] = {
        'mode': mode, 'estimate_pct': number(national_cells, 4),
        'lower95_pct': number(national_cells, 5), 'upper95_pct': number(national_cells, 6),
        'unweighted_count': int(number(national_cells, 7)), 'unweighted_base': int(number(national_cells, 8)),
        'sheet': sheet_name, 'estimate_cell': 'D9', 'lower_cell': 'E9', 'upper_cell': 'F9',
        'count_cell': 'G9', 'base_cell': 'H9', 'source_url': URL, 'source_sha256': SOURCE_SHA,
    }
    assert 0 <= totals[mode]['lower95_pct'] <= totals[mode]['estimate_pct'] <= totals[mode]['upper95_pct'] <= 100
    totals[mode]['interval_width_pp'] = totals[mode]['upper95_pct'] - totals[mode]['lower95_pct']
    coded, usable = [], []
    for source_row in rows:
        mapping = cells(source_row)
        code = text(mapping, 14)
        if not re.fullmatch(r'E\d{8}', code):
            continue
        assert code not in coded, ('duplicate district code', sheet_name, code)
        coded.append(code)
        n = source_row['row']
        values = [number(mapping, c) for c in [4, 5, 6, 7, 8]]
        if any(v is None for v in values):
            excluded.append({'mode': mode, 'lad25_code': code, 'local_authority': text(mapping, 2), 'source_sheet': sheet_name, 'source_row': n, 'estimate_flag': text(mapping, 4), 'lower_flag': text(mapping, 5), 'upper_flag': text(mapping, 6), 'count_flag': text(mapping, 7), 'base_flag': text(mapping, 8), 'reason': 'Source suppression/unavailable marker retained; not recoded to zero.'})
            continue
        estimate, lower, upper, count, base = values
        assert 0 <= lower <= estimate <= upper <= 100
        assert count.is_integer() and base.is_integer() and 0 <= count <= base
        record = {
            'mode': mode, 'lad25_code': code, 'local_authority': text(mapping, 2),
            'itl1_name': text(mapping, 17), 'itl1_code': text(mapping, 18),
            'itl2_name': text(mapping, 15), 'itl2_code': text(mapping, 16),
            'estimate_pct': estimate, 'lower95_pct': lower, 'upper95_pct': upper,
            'interval_width_pp': upper - lower,
            'contains_england_point': lower <= totals[mode]['estimate_pct'] <= upper,
            'unweighted_count': int(count), 'unweighted_base': int(base),
            'source_sheet': sheet_name, 'source_row': n,
            'estimate_cell': 'D' + str(n), 'lower_cell': 'E' + str(n), 'upper_cell': 'F' + str(n),
            'count_cell': 'G' + str(n), 'base_cell': 'H' + str(n), 'lad_code_cell': 'N' + str(n),
            'period': '2025/26', 'geography_scope': 'England', 'population_age': '16+',
            'source_url': URL, 'source_sha256': SOURCE_SHA,
        }
        records.append(record)
        usable.append(record)
    all_codes[mode], valid_codes[mode] = set(coded), {r['lad25_code'] for r in usable}
    widths = [r['interval_width_pp'] for r in usable]
    widths_sorted = sorted(usable, key=lambda r: (r['interval_width_pp'], r['lad25_code']))
    by_base = sorted(usable, key=lambda r: (r['unweighted_base'], r['lad25_code']))
    bins = Counter('0 to under 5' if w < 5 else '5 to under 10' if w < 10 else '10 to under 15' if w < 15 else '15 to under 20' if w < 20 else '20 or more' for w in widths)
    summaries.append({
        'mode': mode, 'coded_districts': len(coded), 'usable_districts': len(usable),
        'excluded_districts': len(coded) - len(usable),
        'england_estimate_pct': totals[mode]['estimate_pct'],
        'england_lower95_pct': totals[mode]['lower95_pct'], 'england_upper95_pct': totals[mode]['upper95_pct'],
        'england_interval_width_pp': totals[mode]['interval_width_pp'],
        'median_district_interval_width_pp': statistics.median(widths),
        'min_district_interval_width_pp': min(widths), 'max_district_interval_width_pp': max(widths),
        'median_width_to_national_width_ratio': statistics.median(widths) / totals[mode]['interval_width_pp'],
        'intervals_containing_national_point': sum(r['contains_england_point'] for r in usable),
        'intervals_containing_national_point_share_districts_pct': 100 * sum(r['contains_england_point'] for r in usable) / len(usable),
        'width_bins': dict(bins),
        'unweighted_base_min': by_base[0]['unweighted_base'], 'unweighted_base_median': statistics.median(r['unweighted_base'] for r in usable), 'unweighted_base_max': by_base[-1]['unweighted_base'],
        'illustrative_districts_by_base': [{'selection': label, 'lad25_code': row['lad25_code'], 'name': row['local_authority'], 'estimate_pct': row['estimate_pct'], 'lower95_pct': row['lower95_pct'], 'upper95_pct': row['upper95_pct'], 'unweighted_base': row['unweighted_base']} for label, row in [('minimum base', by_base[0]), ('median base', by_base[len(by_base) // 2]), ('maximum base', by_base[-1])]],
        'summary_weighting': 'One vote per usable district, not population-weighted.',
    })

assert all_codes['physical'] == all_codes['digital']
paired_codes = valid_codes['physical'] & valid_codes['digital']
summary = {
    'status': 'calculated_within_year_descriptive_precision_audit',
    'calculated_at_utc': datetime.now(timezone.utc).isoformat(),
    'source_sha256': SOURCE_SHA,
    'plan_sha256': hashlib.sha256((ROOT / 'predeclared-plan.json').read_bytes()).hexdigest(),
    'district_codes_total': len(all_codes['physical']), 'paired_usable_districts': len(paired_codes),
    'usable_mode_rows': len(records), 'excluded_mode_rows': len(excluded),
    'unmatched_usable_codes': {'physical_only': sorted(valid_codes['physical'] - valid_codes['digital']), 'digital_only': sorted(valid_codes['digital'] - valid_codes['physical'])},
    'summaries': summaries, 'national_totals': totals,
    'attribution': ATTRIBUTION,
    'interpretation': 'Widths and containment summarize the published estimates. Containment is not a formal difference/equivalence test. No local league table, cross-year trend or causal inference is estimated.',
}
(DATA / 'analysis.json').write_text(json.dumps(summary, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
(DATA / 'district-estimates.json').write_text(json.dumps(records, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
csv_out('district-estimates.csv', records)
csv_out('excluded-districts.csv', excluded)
csv_out('national-estimates.csv', list(totals.values()))
flat = [{k: v for k, v in s.items() if k not in ['width_bins', 'illustrative_districts_by_base']} for s in summaries]
csv_out('precision-summary.csv', flat)
headers = ['Mode', 'Local authority', 'Estimate (%)', 'Lower 95% (%)', 'Upper 95% (%)', 'Width (percentage points)', 'Unweighted base', 'Source cells']
parts = ['<table><caption>Published library-use estimates and calculated interval widths, England people aged 16+, 2025/26</caption><thead><tr>' + ''.join('<th scope="col">' + html.escape(x) + '</th>' for x in headers) + '</tr></thead><tbody>']
for r in records:
    values = [r['mode'], r['local_authority'], f"{r['estimate_pct']:.2f}", f"{r['lower95_pct']:.2f}", f"{r['upper95_pct']:.2f}", f"{r['interval_width_pp']:.2f}", str(r['unweighted_base']), r['source_sheet'] + '!' + r['estimate_cell'] + ':' + r['base_cell']]
    parts.append('<tr>' + ''.join('<td>' + html.escape(x) + '</td>' for x in values) + '</tr>')
parts.append('</tbody></table>')
(DATA / 'district-estimates-table.html').write_text('\n'.join(parts), encoding='utf-8')
print(json.dumps(summary, ensure_ascii=False, indent=2))