#!/usr/bin/env python3
"""Read .xlsx with the pure standard library (no openpyxl / pandas dependency).

Implements only the minimal capabilities needed for batch annotation:
- list sheet names
- read a sheet by name into a list of [dict(col_letter -> value), ...] rows
- auto-parse sharedStrings and numeric cells

Purpose: iHARP batch scripts read input xlsx and calibration tables (cluster/curve).
"""

from __future__ import annotations

import zipfile
from xml.etree import ElementTree as ET
from typing import Any

_NS = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"


def _col_letters(ref: str) -> str:
    return "".join(ch for ch in ref if ch.isalpha())


def _read_shared_strings(z: zipfile.ZipFile) -> list[str]:
    if "xl/sharedStrings.xml" not in z.namelist():
        return []
    root = ET.fromstring(z.read("xl/sharedStrings.xml").decode("utf-8", errors="replace"))
    strings: list[str] = []
    for si in root.iter(f"{_NS}si"):
        txt = "".join(t.text or "" for t in si.iter(f"{_NS}t"))
        strings.append(txt)
    return strings


def _sheet_targets(z: zipfile.ZipFile) -> dict[str, str]:
    """Return {sheet_name: worksheet.xml path}."""
    wb_root = ET.fromstring(z.read("xl/workbook.xml").decode("utf-8", errors="replace"))
    rels_root = ET.fromstring(z.read("xl/_rels/workbook.xml.rels").decode("utf-8", errors="replace"))
    rid_to_target = {}
    for rel in rels_root:
        rid_to_target[rel.get("Id")] = rel.get("Target")
    name_to_target: dict[str, str] = {}
    for sheet in wb_root.iter(f"{_NS}sheet"):
        name = sheet.get("name")
        rid = sheet.get(
            "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id"
        )
        target = rid_to_target.get(rid)
        if name and target:
            # target looks like worksheets/sheet1.xml, may be a relative path
            if not target.startswith("xl/"):
                target = "xl/" + target.lstrip("/")
            name_to_target[name] = target
    return name_to_target


def _parse_sheet(z: zipfile.ZipFile, target: str, strings: list[str]) -> list[dict[str, Any]]:
    root = ET.fromstring(z.read(target).decode("utf-8", errors="replace"))
    rows: list[dict[str, Any]] = []
    for row in root.iter(f"{_NS}row"):
        cells: dict[str, Any] = {}
        for c in row.iter(f"{_NS}c"):
            ref = c.get("r") or ""
            col = _col_letters(ref)
            t = c.get("t")
            v = c.find(f"{_NS}v")
            val: Any = None
            if t == "s" and v is not None:
                val = strings[int(v.text)]
            elif v is not None:
                val = v.text
            elif t == "inlineStr":
                is_el = c.find(f"{_NS}is")
                if is_el is not None:
                    val = "".join(tt.text or "" for tt in is_el.iter(f"{_NS}t"))
            cells[col] = val
        if cells:
            rows.append(cells)
    return rows


def sheet_names(path: str) -> list[str]:
    with zipfile.ZipFile(path) as z:
        return list(_sheet_targets(z).keys())


def read_sheet(path: str, name: str | None = None) -> list[dict[str, Any]]:
    """Read the specified sheet; when name is None read the first sheet. Return a list of rows."""
    with zipfile.ZipFile(path) as z:
        strings = _read_shared_strings(z)
        targets = _sheet_targets(z)
        if name is None:
            name = next(iter(targets))
        if name not in targets:
            raise KeyError(f"sheet does not exist: {name}")
        return _parse_sheet(z, targets[name], strings)


def read_sheet_as_rows(path: str, name: str | None = None) -> list[list[Any]]:
    """Read as a [column-value list] (in column-letter A,B,C... order), suitable for header/data separation."""
    dicts = read_sheet(path, name)
    if not dicts:
        return []
    # determine column order
    cols = sorted({c for row in dicts for c in row})
    result: list[list[Any]] = []
    for row in dicts:
        result.append([row.get(c) for c in cols])
    return result