import hashlib
import json
from pathlib import Path
import posixpath
import xml.etree.ElementTree as ET
import zipfile

ROOT = Path(__file__).resolve().parents[1]
EDITION = 'website-evidence-update-2026-09-17'
DATA = ROOT / 'data' / EDITION
RESEARCH = ROOT / 'data/bfe-aramis-2026-09-17'
REVIEWED = '2026-09-17'
CALCULATIONS = 'field-evidence-calculations'

SOURCE_SPECS = [
    ('bfe-12776', 'bfe-heat-pump-metering-2026', 'Heat-pump readings compared with calibrated meters', 'OST / ETH Zürich for EnergieSchweiz', 'Report dated 11 August 2026; listed 15 September', 'PDF p.3; PDF pp.16–19 (printed pp.15–18), especially table 9; PDF pp.35–36', 'Four systems, January–April 2026. Main heat-output channels and available compressor readings have different boundaries. One unit has substantially different results for minute and monthly data. This measures agreement between data sources; national seasonal performance needs a representative sample and whole-system meters.'),
    ('bfe-12772', 'bfe-ep2060-workbook-2026', 'Energy Perspectives 2060: demographic input workbook', 'BFE', 'Workbook listed 1 September 2026', 'Both Rahmendaten scenario sheets: BC12 = 2050, BC13 = population in annual-average thousands', 'Demographic scenario inputs based on the BFS reference scenario. The same population is used in both sheets. Annual-average population is distinct from a year-end count. These inputs alone leave electricity use and the generation mix undecided.'),
    ('bfe-12773', 'bfe-ep2060-inputs-2026', 'Energy Perspectives 2060: common assumptions', 'BFE', 'Listed 1 September 2026; PDF dated 14 September 2026', 'PDF pp.6–7: input sources, scenario choice and population table', 'The catalogue-listed date precedes the internal document date; both are preserved. Page 7 identifies the BFS A-00-2025 population scenario and rounds annual-average population to thousands. Use this as an external input comparator.'),
    ('bfe-12677', 'bfe-data-centres-2026', 'Swiss data-centre electricity use and efficiency potential', 'TEP Energy / HSLU / Willers for EnergieSchweiz', 'Report dated 6 July 2026', 'PDF p.3 summary; pp.67–70: historical comparison and scope', '2024 consumption is estimated by survey and extrapolation. The summary rounds it to 2.1 TWh; p.68 reports 2.08 TWh. Small business server rooms and micro data centres sit outside the matched comparison. Future consumption cases and technical savings potential remain conditional.'),
    ('bfe-12679', 'bfe-solar-storage-2025', 'Swiss solar and associated storage statistics for 2025', 'Swissolar for BFE', 'Reference year 2025; report dated 9 July 2026', 'PDF pp.6–7: PV capacity and output; p.11: battery stock; pp.24–28: methods', 'Statistical estimates from solar-industry sales, installation and retirement assumptions. PV output uses a metered sample and fleet model. Battery stock gives reported capacity; available power, usable energy and contracted grid access need separate evidence.'),
    ('aramis-doc-72019', 'v2x-suisse-2024', 'V2X Suisse: cars supplying grid services', 'Mobility and project partners / BFE', 'Final report dated 30 August 2024', 'PDF pp.3–5: trial, technical qualification and business cases', 'A 50-car sharing-fleet demonstration met grid-service requirements. Potential service revenue is gross income under the study conditions. Expected revenues fell short of additional V2X costs in the studied cases. Apply the dated conditions before comparing current offers.'),
    ('aramis-doc-74868', 'smallflex-goms-2026', 'SmallFLEX Goms: flexibility from existing hydro', 'HES-SO and project partners / BFE', 'Final report dated 22 May 2026', 'PDF p.4; pp.52–53 and 128–136: hydraulic limits and Mörel trials; p.162: economic-model limits', 'Mörel trials support approximately ±3 MW of additional flexibility under tested conditions. Duration, inflow, air entry and safe water levels constrain delivery. Some economic modelling uses incomplete and non-time-aligned annual inputs; owner profit still needs a complete project assessment.'),
    ('bfe-11633', 'bfe-hydro-revenue-2023', 'Hydropower revenue opportunities and marketing costs', 'Swiss Economics / ETH Zürich for BFE', 'Final report dated 30 September 2023', 'PDF pp.5–7, especially p.6: reserve capacity and foregone energy-market revenue', 'Commissioned economic methodology for a support mechanism. Its treatment of competing uses supports accounting for foregone sales. Administrative cost heuristics are separate from audited operator costs or endorsement of the support mechanism.'),
]


def load(path):
    return json.loads(path.read_text())


def write(path, value):
    path.parent.mkdir(parents=True, exist_ok=True)
    path.write_text(json.dumps(value, indent=2, ensure_ascii=False, allow_nan=False) + '\n')


def artifact(path, download=None):
    content = path.read_bytes()
    result = dict(path=str(path.relative_to(ROOT)), sha256=hashlib.sha256(content).hexdigest(), bytes=len(content))
    if download:
        result['download'] = download
        target = ROOT / 'website/public' / download.lstrip('/')
        target.parent.mkdir(parents=True, exist_ok=True)
        target.write_bytes(content)
    return result


def workbook_cell(path, sheet_name, address):
    ns = {'s': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
    relationship_id = '{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id'
    with zipfile.ZipFile(path) as book:
        workbook = ET.fromstring(book.read('xl/workbook.xml'))
        sheet = next(row for row in workbook.findall('s:sheets/s:sheet', ns) if row.attrib['name'] == sheet_name)
        relationships = ET.fromstring(book.read('xl/_rels/workbook.xml.rels'))
        target = next(row.attrib['Target'] for row in relationships if row.attrib['Id'] == sheet.attrib[relationship_id])
        target = target.lstrip('/') if target.startswith('/') else posixpath.normpath('xl/' + target)
        content = ET.fromstring(book.read(target))
        cell = content.find(f'.//s:c[@r="{address}"]/s:v', ns)
        return float(cell.text)


def main():
    prior = load(ROOT / 'data/website-editorial-review-2026-09-17/catalog.json')
    catalog = json.loads(json.dumps(prior))
    original_sources = {row['source_id']: row for row in load(RESEARCH / 'source-catalog.json')['sources']}
    observations = {row['observation_id']: row for row in load(RESEARCH / 'selected-observations.json')['observations']}
    added_sources = []
    for original_id, source_id, title, publisher, edition, locator, note in SOURCE_SPECS:
        original = original_sources[original_id]
        raw = artifact(ROOT / original['path'])
        assert raw['sha256'] == original['sha256'] and raw['bytes'] == original['bytes']
        files = [raw, artifact((ROOT / original['path']).with_suffix('.source.json'))]
        if original.get('text_path'):
            files.append(artifact(ROOT / original['text_path']))
        source = dict(id=source_id, title=title, publisher=publisher, kind='primary', url=original['url'], publicationDate=original.get('declared_publication_date'), documentDate=original.get('document_date') or ('2026-07-06' if original_id == 'bfe-12677' else None), catalogueListedDate=original.get('catalogue_listed_date'), edition=edition, retrievedAt=original['retrieved_at_utc'], reviewedAt=REVIEWED, locator=locator, note=note, researchSourceId=original_id, artifacts=files)
        catalog['sources'].append(source)
        added_sources.append(source_id)

    added = []

    def add(claim_id, label, value, unit, status, period, definition, limitation, sources, observation_id=None, metric=None, scale=1, formula=None):
        row = dict(id=claim_id, label=label, value=value, unit=unit, status=status, period=period, definition=definition, limitation=limitation, formula=formula, sources=sources, reviewedAt=REVIEWED)
        catalog['claims'].append(row)
        added.append(dict(claim=row, research_observation=observation_id, research_metric=metric, scale=scale))

    def selected(observation_id, metric, claim_id, label, unit, status, definition, limitation, source, period, scale=1):
        value = observations[observation_id]['metrics'][metric] * scale
        formula = f'Study case reproduced directly from {observations[observation_id]["locator"]}.' if status == 'scenario' else None
        if scale != 1:
            formula = f'{observations[observation_id]["metrics"][metric]:g} × {scale:g} = {value:g} {unit}; unit conversion from the source value.'
        add(claim_id, label, value, unit, status, period, definition, limitation, [source], observation_id, metric, scale, formula)

    hp_limit = 'January–April comparison in four systems. Main heat-output channels; available compressor comparisons cover three systems. National efficiency requires a representative sample and whole-system measurements.'
    hp_source = 'bfe-heat-pump-metering-2026'
    selected('HP01', 'sample_systems', 'metering-heat-pump-sample', 'Systems in the heat-pump comparison', 'systems', 'observed', 'Systems from different manufacturers compared with calibrated field meters.', hp_limit, hp_source, 'January–April 2026')
    for metric, suffix, label in [('thermal_energy_deviation_percent_min', 'heat-low', 'Lowest main heat-output deviation'), ('thermal_energy_deviation_percent_max', 'heat-high', 'Highest main heat-output deviation'), ('electric_compressor_energy_deviation_percent_min', 'electric-low', 'Lowest compressor-energy deviation'), ('electric_compressor_energy_deviation_percent_max', 'electric-high', 'Highest compressor-energy deviation')]:
        selected('HP01', metric, 'metering-' + suffix, label, '%', 'observed', 'Difference between internal energy data and the calibrated field total: (internal − field) / field × 100.', hp_limit, hp_source, 'January–April 2026')
    add('metering-f017-monthly', 'F017 heat-output deviation using monthly data', -7.7, '%', 'observed', 'January–April 2026', 'Monthly internal heat total compared with calibrated field measurements for the same unit whose minute data differ by −34.7%.', 'One system and data stream. The alternative polynomial heat estimate for another system is outside the main-channel range.', [hp_source], formula='Direct transcription: table 9, PDF p.19 (printed p.18), F017 / Q_dot_H (M).')

    population_rows = [row for row in load(RESEARCH / 'ep2060-demographic-inputs.json') if row['year'] == 2050 and row['metric'] == 'resident_population_annual_average']
    assert len(population_rows) == 2
    for row in population_rows:
        assert row['cell'] == 'BC13' and row['year_cell'] == 'BC12'
        workbook = ROOT / original_sources['bfe-12772']['path']
        assert workbook_cell(workbook, row['sheet'], row['year_cell']) == 2050
        assert workbook_cell(workbook, row['sheet'], row['cell']) == row['value']
    assert len({row['value'] for row in population_rows}) == 1
    selected('EP01', 'population_annual_average_thousands', 'ep2060-population-2050', 'EP2060 reference population input', 'million residents', 'assumption', 'Annual-average permanent resident population input shared by the two published workbook sheets.', 'BFS reference-scenario input. Electricity demand also depends on activity, equipment, efficiency and weather.', 'bfe-ep2060-workbook-2026', '2050 scenario input', .001)
    assert added[-1]['claim']['value'] == population_rows[0]['value'] / 1000
    added[-1]['claim']['sources'].append('bfe-ep2060-inputs-2026')
    added[-1]['claim']['formula'] = 'Workbook BC13 = 10,306.5125 thousand; divide by 1,000 to express million residents. Both scenario sheets agree.'

    dc_source = 'bfe-data-centres-2026'
    dc_limit = 'Study scope excludes small business server rooms and micro data centres from the matched comparison. Baseline electricity already includes these users; reconcile sector growth before adding any increment.'
    selected('DC01', 'estimated_demand_twh', 'data-centres-2024', 'Data-centre electricity in the study scope', 'TWh', 'estimate', 'Rounded estimate of annual electricity use from the report summary.', dc_limit, dc_source, '2024')
    for metric, suffix, label in [('scenario_low_twh', 'low', 'Lower 2030 data-centre case'), ('scenario_high_twh', 'upper', 'Upper 2030 data-centre case'), ('additional_high_case_twh', 'high', 'Separate high 2030 data-centre case')]:
        selected('DC02', metric, 'data-centres-2030-' + suffix, label, 'TWh', 'scenario', 'Annual electricity use in the study scenario, on its stated data-centre scope.', dc_limit, dc_source, '2030')
        change = round(observations['DC02']['metrics'][metric] - observations['DC01']['metrics']['estimated_demand_twh'], 2)
        add('data-centres-increment-' + suffix, 'Data-centre growth: ' + label.lower(), change, 'TWh', 'calculated', '2024–2030', 'Difference from the rounded 2.1 TWh study baseline.', 'Approximate arithmetic using rounded summary inputs. The increment is a study comparison; its overlap with activity growth in WattInfo’s model remains to be reconciled.', [dc_source, CALCULATIONS], formula=f'{observations["DC02"]["metrics"][metric]} − 2.1 = {change} TWh. No extrapolation to 2050.')

    solar_source = 'bfe-solar-storage-2025'
    storage_limit = 'Solar-industry survey and retirement model. Reported stock capacity; deliverable grid energy needs usable-capacity, power, charge-state and participation data.'
    selected('ST01', 'total_gwh', 'solar-survey-battery-stock-2025', 'Battery capacity in the solar-industry survey', 'GWh', 'estimate', 'Estimated installed stock across the survey’s lithium-ion, salt and other battery categories.', storage_limit, solar_source, 'End of 2025')
    selected('ST01', 'total_systems', 'solar-survey-battery-systems-2025', 'Battery systems in the solar-industry survey', 'systems', 'estimate', 'Estimated system count using sales and retirement cohorts.', storage_limit, solar_source, 'End of 2025')
    selected('PV01', 'annual_generation_gwh_rounded', 'solar-generation-2025', 'Solar electricity generated in Switzerland', 'TWh', 'estimate', 'Annual PV production estimated from a metered sample and the fleet model.', 'Generation varies through the year. The growing fleet’s year-end capacity is a separate measurement basis.', solar_source, '2025', .001)
    selected('PV01', 'end_year_capacity_mw', 'solar-capacity-2025', 'Installed solar power at year-end', 'GW', 'estimate', 'Estimated PV fleet capacity at the end of the reporting year.', 'End-year capacity differs from capacity available through the year. Use time-weighted capacity for a fleet capacity factor.', solar_source, 'End of 2025', .001)

    selected('V2X01', 'cars', 'v2x-trial-cars', 'Cars in the V2X Suisse trial', 'cars', 'observed', 'Bidirectional cars in the Mobility sharing-fleet demonstration.', 'Demonstrated grid-service capability under trial conditions. Available vehicles, chargers and driving requirements constrain delivery.', 'v2x-suisse-2024', '2021–2024 trial')
    selected('V2X01', 'potential_ancillary_revenue_chf_per_vehicle_year_approx', 'v2x-potential-service-revenue', 'Potential V2X grid-service revenue', 'CHF/vehicle/year', 'scenario', 'Approximate gross potential from ancillary services in the study’s business cases.', 'Expected revenues fell below the additional V2X costs in the studied cases. Gross revenue still has to cover equipment, operation and other costs.', 'v2x-suisse-2024', '2024 final report')
    selected('HYDRO02', 'additional_flexibility_mw_plus_minus_approx', 'morel-tested-flexibility', 'Additional flexibility tested at Mörel', 'MW in either direction', 'observed', 'Approximately ±3 MW supported by the reported field tests, under the tested hydraulic conditions.', 'Short-term power adjustment. Duration, inflow, water levels and air-entry risks limit operation; this gives no annual generation increment.', 'smallflex-goms-2026', '2026 final report')

    dataset = dict(reviewedAt=REVIEWED, interpretation='Measurements, statistical estimates, study scenarios and explicit arithmetic remain separate. Existing demand-model results and supply scenarios are unchanged.', sources=added_sources, claims=added, ep2060_cells=population_rows, additional_transcription=dict(source=hp_source, locator='PDF p.19 (printed p.18), table 9', system='F017', metric='Q_dot_H (M)', value=-7.7, unit='%', review='Checked against rendered original table; main minute-series deviation is −34.7%.'), inputs=[artifact(RESEARCH / name) for name in ['source-catalog.json', 'selected-observations.json', 'ep2060-demographic-inputs.json']])
    write(DATA / 'evidence.json', dataset)
    calculation_artifacts = [artifact(DATA / 'evidence.json', '/downloads/field-evidence/evidence.json'), artifact(Path(__file__).resolve(), '/downloads/field-evidence/build_website_field_evidence.py')]
    catalog['sources'].append(dict(id=CALCULATIONS, title='Demand and field-trial evidence: selected inputs and calculations', publisher='WattInfo', kind='project-calculation', url='/downloads/field-evidence/evidence.json', publicationDate=REVIEWED, edition='Primary-evidence integration, 17 September 2026', retrievedAt=REVIEWED, reviewedAt=REVIEWED, locator='claims; ep2060_cells; additional_transcription; original source locators and archive hashes', note='Data-centre increments subtract the rounded study baseline from each 2030 case. EP2060 population cells are checked in the original workbook. These observations inform future model choices; the existing model and its totals remain unchanged.', artifacts=calculation_artifacts))
    assert catalog['sources'][:len(prior['sources'])] == prior['sources']
    assert catalog['claims'][:len(prior['claims'])] == prior['claims']
    catalog['reviewedAt'] = REVIEWED
    for path in [DATA / 'catalog.json', ROOT / 'website/src/data/catalog.json', ROOT / 'website/public/downloads/catalog.json']:
        write(path, catalog)
    print(f'Added {len(SOURCE_SPECS)} primary source records, one calculation record and {len(added)} claims; prior evidence unchanged.')


if __name__ == '__main__':
    main()