"""Reproduce the portfolio analysis: Python 3 + openpyxl 3.1.5.
Run in this directory: python3 analyze.py
Reads the unchanged EIA workbook; writes normalized CSV and analysis JSON.
"""
import csv
import hashlib
import json
import sqlite3
from pathlib import Path
import openpyxl

ROOT = Path(__file__).resolve().parent
SOURCE = ROOT / 'eia-sc-2024.xlsx'
URL = 'https://www.eia.gov/electricity/state/xls/SEP%20Tables%20for%20SC.xlsx'

def read_table(sheet):
    assert sheet['A2'].value == 'South Carolina'
    years = {col: int(str(sheet.cell(4, col).value).split()[-1]) for col in range(2, 37)}
    assert set(years.values()) == set(range(1990, 2025))
    start = next(i for i in range(1, sheet.max_row + 1) if sheet.cell(i, 1).value == 'Total electric industry')
    result = {}
    coordinates = {}
    for row in range(start, sheet.max_row):
        name = str(sheet.cell(row, 1).value).strip()
        if name.startswith('..'):
            continue  # Technology subtotals would double-count primary fuel rows.
        for col, year in years.items():
            cell = sheet.cell(row, col)
            value = cell.value
            if value in (None, '.', ' '):
                value = None  # Preserve unreported values; never invent zeroes.
            elif not isinstance(value, (float, int)):
                raise ValueError(f'Unexpected value at {sheet.title}!{cell.coordinate}: {value}')
            result[(year, name)] = value
            coordinates[(year, name)] = f'{sheet.title}!{cell.coordinate}'
    return result, coordinates

book = openpyxl.load_workbook(SOURCE, data_only=True, read_only=False)
capacity, capacity_cells = read_table(book['4A. Capacity'])
generation, generation_cells = read_table(book['5. Generation'])
rows = []
for year, fuel in sorted(capacity.keys() | generation.keys()):
    key = (year, fuel)
    rows.append(dict(year=year, fuel=fuel, capacity_mw=capacity.get(key), generation_mwh=generation.get(key), capacity_cell=capacity_cells.get(key, ''), generation_cell=generation_cells.get(key, '')))
assert len({(r['year'], r['fuel']) for r in rows}) == len(rows)
for year in range(1990, 2025):
    assert capacity[(year, 'Total electric industry')] > 0
    assert generation[(year, 'Total electric industry')] > 0
assert generation[(2024, 'Total electric industry')] == 100485416
assert capacity[(2024, 'Total electric industry')] == 24705.6
with (ROOT / 'energy.csv').open('w', newline='') as f:
    writer = csv.DictWriter(f, fieldnames=rows[0].keys())
    writer.writeheader()
    writer.writerows(rows)
# SQL is independently executable against the normalized table.
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE energy (year INTEGER, fuel TEXT, capacity_mw REAL, generation_mwh REAL, capacity_cell TEXT, generation_cell TEXT, PRIMARY KEY(year, fuel))')
con.executemany('INSERT INTO energy VALUES (:year,:fuel,:capacity_mw,:generation_mwh,:capacity_cell,:generation_cell)', rows)
con.row_factory = sqlite3.Row
analysis = [dict(r) for r in con.execute((ROOT / 'analysis.sql').read_text())]
assert len(analysis) == 140
nuclear = next(r for r in analysis if r['year'] == 2024 and r['fuel'] == 'Nuclear')
assert round(nuclear['capacity_share'], 1) == 26.7
assert round(nuclear['generation_share'], 1) == 53.5
output = dict(source=URL, retrieved='2026-09-21', workbook_sha256=hashlib.sha256(SOURCE.read_bytes()).hexdigest(), rows=analysis)
(ROOT / 'analysis.json').write_text(json.dumps(output, indent=2) + '\n')
print(f'Validated {len(rows)} normalized records; {len(analysis)} fuel-year comparisons. Nuclear 2024: {nuclear["capacity_share"]:.1f}% capacity, {nuclear["generation_share"]:.1f}% generation.')