#!/usr/bin/env python3
"""
Minimal .xlsx reader — standard library only (no openpyxl/pandas).

An .xlsx file is a zip of XML parts. This reads worksheet cell values, resolving
shared strings and inline strings, and returns plain Python rows. Enough for
tabular imports; it does NOT evaluate formulas (it returns the cached value if
present) and ignores styling/number formats.

API:
    from xlsx_read import read_sheet, sheet_names
    names = sheet_names("file.xlsx")
    rows = read_sheet("file.xlsx", "To-Do List")   # -> list[list[str|None]]
    table = read_table("file.xlsx", "To-Do List")  # -> (headers, list[dict])
"""

from __future__ import annotations

import re
import zipfile
from xml.etree import ElementTree as ET

_NS = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
_RID = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id"
_RNS = "{http://schemas.openxmlformats.org/package/2006/relationships}"


def _col_to_num(ref):
    """'B7' -> 2 (1-based column index)."""
    letters = re.match(r"[A-Z]+", ref).group()
    n = 0
    for ch in letters:
        n = n * 26 + (ord(ch) - 64)
    return n


def _shared_strings(zf):
    if "xl/sharedStrings.xml" not in zf.namelist():
        return []
    root = ET.fromstring(zf.read("xl/sharedStrings.xml"))
    out = []
    for si in root.findall(f"{_NS}si"):
        out.append("".join(t.text or "" for t in si.iter(f"{_NS}t")))
    return out


def _sheet_map(zf):
    """Return ordered list of (sheet_name, worksheet_part_path)."""
    wb = ET.fromstring(zf.read("xl/workbook.xml"))
    rels = ET.fromstring(zf.read("xl/_rels/workbook.xml.rels"))
    rid2target = {r.get("Id"): r.get("Target")
                  for r in rels.iter(f"{_RNS}Relationship")}
    sheets = []
    for s in wb.iter(f"{_NS}sheet"):
        target = rid2target.get(s.get(_RID))
        if not target:
            continue
        if not target.startswith("xl/"):
            target = "xl/" + target.lstrip("/")
        sheets.append((s.get("name"), target))
    return sheets


def sheet_names(path):
    with zipfile.ZipFile(path) as zf:
        return [name for name, _ in _sheet_map(zf)]


def read_sheet(path, sheet_name):
    """Return rows as lists of cell values (str or None), padded to max width."""
    with zipfile.ZipFile(path) as zf:
        shared = _shared_strings(zf)
        target = None
        for name, part in _sheet_map(zf):
            if name == sheet_name:
                target = part
                break
        if target is None:
            raise KeyError(f"Sheet '{sheet_name}' not found. "
                           f"Available: {[n for n, _ in _sheet_map(zf)]}")
        root = ET.fromstring(zf.read(target))
        rows = []
        max_col = 0
        for row in root.iter(f"{_NS}row"):
            cells = {}
            for c in row.findall(f"{_NS}c"):
                ref = c.get("r")
                if not ref:
                    continue
                ctype = c.get("t")
                v = c.find(f"{_NS}v")
                inline = c.find(f"{_NS}is")
                val = None
                if ctype == "s" and v is not None:
                    val = shared[int(v.text)]
                elif ctype == "inlineStr" and inline is not None:
                    val = "".join(t.text or "" for t in inline.iter(f"{_NS}t"))
                elif v is not None:
                    val = v.text
                idx = _col_to_num(ref)
                cells[idx] = val
                max_col = max(max_col, idx)
            rows.append(cells)
        return [[r.get(i) for i in range(1, max_col + 1)] for r in rows]


def read_table(path, sheet_name, header_row=0):
    """Return (headers, list[dict]) using the given 0-based header row index.

    Rows above the header are skipped; the first row at/after header_row whose
    first cell is non-empty is treated as the header.
    """
    rows = read_sheet(path, sheet_name)
    if not rows:
        return [], []
    headers = [(h or "").strip() for h in rows[header_row]]
    records = []
    for r in rows[header_row + 1:]:
        rec = {}
        for i, h in enumerate(headers):
            if not h:
                continue
            rec[h] = r[i] if i < len(r) else None
        records.append(rec)
    return headers, records


if __name__ == "__main__":
    import sys
    p = sys.argv[1]
    print("Sheets:", sheet_names(p))
    if len(sys.argv) > 2:
        hdrs, recs = read_table(p, sys.argv[2])
        print("Headers:", hdrs)
        print(f"{len(recs)} data rows")