#!/usr/bin/env python3
"""Extract worksheet values from XLSX files using only Python's standard library."""

from __future__ import annotations

import argparse
import csv
import re
import zipfile
from pathlib import Path
from xml.etree import ElementTree as ET

NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
REL_NS = {"r": "http://schemas.openxmlformats.org/package/2006/relationships"}
DOC_REL = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id"


def column_index(cell_ref: str) -> int:
    letters = re.match(r"[A-Z]+", cell_ref).group(0)
    value = 0
    for letter in letters:
        value = value * 26 + ord(letter) - ord("A") + 1
    return value - 1


def shared_strings(archive: zipfile.ZipFile) -> list[str]:
    try:
        root = ET.fromstring(archive.read("xl/sharedStrings.xml"))
    except KeyError:
        return []
    values = []
    for item in root.findall("m:si", NS):
        values.append("".join(node.text or "" for node in item.iterfind(".//m:t", NS)))
    return values


def sheet_targets(archive: zipfile.ZipFile) -> list[tuple[str, str]]:
    workbook = ET.fromstring(archive.read("xl/workbook.xml"))
    relationships = ET.fromstring(archive.read("xl/_rels/workbook.xml.rels"))
    targets = {node.attrib["Id"]: node.attrib["Target"] for node in relationships.findall("r:Relationship", REL_NS)}
    sheets = []
    for sheet in workbook.findall("m:sheets/m:sheet", NS):
        target = targets[sheet.attrib[DOC_REL]].lstrip("/")
        if not target.startswith("xl/"):
            target = "xl/" + target
        sheets.append((sheet.attrib["name"], target))
    return sheets


def read_sheet(archive: zipfile.ZipFile, target: str, strings: list[str]) -> list[list[str]]:
    root = ET.fromstring(archive.read(target))
    output = []
    for row in root.findall(".//m:sheetData/m:row", NS):
        values: dict[int, str] = {}
        for cell in row.findall("m:c", NS):
            idx = column_index(cell.attrib["r"])
            kind = cell.attrib.get("t")
            value_node = cell.find("m:v", NS)
            if kind == "inlineStr":
                value = "".join(node.text or "" for node in cell.iterfind(".//m:t", NS))
            elif value_node is None:
                value = ""
            elif kind == "s":
                value = strings[int(value_node.text)]
            elif kind == "b":
                value = "TRUE" if value_node.text == "1" else "FALSE"
            else:
                value = value_node.text or ""
            values[idx] = value
        if values:
            width = max(values) + 1
            output.append([values.get(i, "") for i in range(width)])
        else:
            output.append([])
    return output


def safe_name(name: str) -> str:
    cleaned = re.sub(r"[^A-Za-z0-9._-]+", "_", name).strip("_")
    return cleaned or "sheet"


def extract(input_path: Path, output_dir: Path) -> None:
    output_dir.mkdir(parents=True, exist_ok=True)
    with zipfile.ZipFile(input_path) as archive:
        strings = shared_strings(archive)
        for order, (sheet_name, target) in enumerate(sheet_targets(archive), start=1):
            rows = read_sheet(archive, target, strings)
            destination = output_dir / f"{order:02d}_{safe_name(sheet_name)}.csv"
            with destination.open("w", encoding="utf-8", newline="") as handle:
                csv.writer(handle).writerows(rows)
            print(f"{sheet_name}\t{destination}\t{len(rows)} rows")


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("input", type=Path)
    parser.add_argument("output_dir", type=Path)
    args = parser.parse_args()
    extract(args.input, args.output_dir)


if __name__ == "__main__":
    main()
