"""Reproduce the pinned May 2025 national OEWS extract using only Python stdlib."""
import argparse
import hashlib
import io
import json
import re
import zipfile
from pathlib import Path
from xml.etree import ElementTree as ET

SOURCE_URL = 'https://www.bls.gov/oes/special-requests/oesm25nat.zip'
SOURCE_SHA256 = 'b5855a37f3e03e779f6fbf173d3bbc94aeeff33426aef32fa95ce6d025bab1af'
FIELDS = ['OCC_CODE', 'OCC_TITLE', 'TOT_EMP', 'EMP_PRSE', 'A_MEAN',
          'A_PCT10', 'A_PCT25', 'A_MEDIAN', 'A_PCT75', 'A_PCT90', 'H_MEAN',
          'H_PCT10', 'H_PCT25', 'H_MEDIAN', 'H_PCT75', 'H_PCT90', 'ANNUAL', 'HOURLY']
NS = {'s': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}


def extract(source):
    if len(source) > 5_000_000 or hashlib.sha256(source).hexdigest() != SOURCE_SHA256:
        raise ValueError('Source does not match the reviewed BLS snapshot')
    with zipfile.ZipFile(io.BytesIO(source)) as package:
        names = [n for n in package.namelist() if n.endswith('.xlsx')]
        if len(names) != 1 or package.getinfo(names[0]).file_size > 5_000_000:
            raise ValueError('Unexpected workbook archive')
        workbook_bytes = package.read(names[0])
    with zipfile.ZipFile(io.BytesIO(workbook_bytes)) as workbook:
        if sum(item.file_size for item in workbook.infolist()) > 50_000_000:
            raise ValueError('Workbook expands beyond limit')
        strings = [''.join(n.itertext()) for n in ET.fromstring(
            workbook.read('xl/sharedStrings.xml')).findall('s:si', NS)]
        sheet = ET.fromstring(workbook.read('xl/worksheets/sheet1.xml'))
        rows = []
        for row in sheet.findall('s:sheetData/s:row', NS):
            values = {}
            for cell in row.findall('s:c', NS):
                column = re.sub(r'[^A-Z]', '', cell.attrib['r'])
                raw = cell.findtext('s:v', default='', namespaces=NS)
                if cell.attrib.get('t') == 's':
                    raw = strings[int(raw)]
                elif cell.attrib.get('t') == 'inlineStr':
                    raw = ''.join(cell.find('s:is', NS).itertext())
                values[column] = raw
            rows.append(values)
    header_index = next(i for i, row in enumerate(rows) if 'OCC_CODE' in row.values())
    header = rows[header_index]
    if not set(FIELDS + ['O_GROUP', 'AREA', 'NAICS', 'OWN_CODE']).issubset(header.values()):
        raise ValueError('Missing source columns')
    records = []
    for raw in rows[header_index + 1:]:
        row = {label: raw.get(column, '') for column, label in header.items()}
        if row['O_GROUP'] != 'detailed':
            continue
        if (row['AREA'], row['NAICS'], row['OWN_CODE']) != ('99', '000000', '1235'):
            raise ValueError('Unexpected geography, industry or ownership')
        if not re.fullmatch(r'\d{2}-\d{4}', row['OCC_CODE']):
            raise ValueError('Invalid occupation code')
        for field in FIELDS:
            if field in ('OCC_CODE', 'OCC_TITLE', 'ANNUAL', 'HOURLY'):
                continue
            if row[field] not in ('*', '**', '#', '') and not re.fullmatch(r'\d+(\.\d+)?', row[field]):
                raise ValueError('Unexpected numeric cell or marker')
        records.append({field: row[field] for field in FIELDS})
    if len(records) != 830 or len({r['OCC_CODE'] for r in records}) != 830:
        raise ValueError('Expected exactly 830 distinct detailed occupations')
    return {
        'name': 'US occupational wages: BLS OEWS May 2025 national extract',
        'version': '2025-05.salario.1', 'datePublished': '2026-09-07',
        'referencePeriod': '2025-05', 'recordCount': len(records),
        'source': {'publisher': 'U.S. Bureau of Labor Statistics', 'url': SOURCE_URL,
                   'sha256': SOURCE_SHA256, 'retrievedAt': '2026-09-07',
                   'workbook': names[0], 'worksheet': 'xl/worksheets/sheet1.xml'},
        'license': 'https://www.bls.gov/bls/linksite.htm',
        'methodology': 'https://www.bls.gov/oes/2025/may/oes_tec.htm',
        'filters': {'O_GROUP': 'detailed', 'AREA': '99', 'NAICS': '000000', 'OWN_CODE': '1235'},
        'transformation': 'Select detailed national rows and the listed columns. Preserve source cell values as strings. No inflation adjustment, title remapping, projections or imputation.',
        'valueEncoding': 'All cells are strings. Preserve nonnumeric BLS markers and empty cells; they are not zero. ANNUAL/HOURLY TRUE flags indicate annual-only/hourly-only wage reporting. Percentiles are not experience levels.',
        'fields': FIELDS, 'data': records,
    }


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument('source', type=Path, help='Unmodified official oesm25nat.zip')
    parser.add_argument('output', type=Path)
    args = parser.parse_args()
    result = extract(args.source.read_bytes())
    encoded = (json.dumps(result, ensure_ascii=True, indent=2) + '\n').encode()
    if args.output.exists():
        if args.output.read_bytes() != encoded:
            raise ValueError('Output exists with different contents; refusing overwrite')
    else:
        with args.output.open('xb') as stream:
            stream.write(encoded)
    print(json.dumps({'records': result['recordCount'], 'bytes': len(encoded),
                      'sha256': hashlib.sha256(encoded).hexdigest()}))


if __name__ == '__main__':
    main()