#!/usr/bin/env python3
"""Read worksheet values from an XLSX without loading its style table.

This is intentionally read-only. It exists because the source workbook contains
WPS-specific style nodes that make openpyxl fail before worksheet data is read.
"""

from __future__ import annotations

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


MAIN_NS = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
DOC_REL_NS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
PKG_REL_NS = "http://schemas.openxmlformats.org/package/2006/relationships"
NS = {"m": MAIN_NS, "r": DOC_REL_NS, "p": PKG_REL_NS}


def column_number(cell_ref: str) -> int:
    letters = re.match(r"[A-Z]+", cell_ref)
    if not letters:
        raise ValueError(f"Invalid cell reference: {cell_ref}")
    value = 0
    for char in letters.group(0):
        value = value * 26 + ord(char) - 64
    return value


def split_ref(cell_ref: str) -> tuple[int, int]:
    match = re.fullmatch(r"([A-Z]+)([0-9]+)", cell_ref)
    if not match:
        raise ValueError(f"Invalid cell reference: {cell_ref}")
    return int(match.group(2)), column_number(match.group(1))


def safe_name(name: str) -> str:
    cleaned = re.sub(r"[^0-9A-Za-z\u4e00-\u9fff_-]+", "-", name).strip("-")
    return cleaned[:80] or "sheet"


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


def workbook_sheets(archive: zipfile.ZipFile) -> list[tuple[str, str]]:
    workbook = ET.fromstring(archive.read("xl/workbook.xml"))
    rels = ET.fromstring(archive.read("xl/_rels/workbook.xml.rels"))
    targets = {
        rel.attrib["Id"]: rel.attrib["Target"]
        for rel in rels.findall("p:Relationship", NS)
        if rel.attrib.get("Type", "").endswith("/worksheet")
    }
    result = []
    sheets = workbook.find("m:sheets", NS)
    if sheets is None:
        return []
    for sheet in sheets:
        rel_id = sheet.attrib[f"{{{DOC_REL_NS}}}id"]
        target = targets[rel_id].lstrip("/")
        if not target.startswith("xl/"):
            target = f"xl/{target}"
        result.append((sheet.attrib["name"], target))
    return result


def cell_text(cell: ET.Element, shared: list[str]) -> str:
    cell_type = cell.attrib.get("t")
    if cell_type == "inlineStr":
        inline = cell.find("m:is", NS)
        if inline is None:
            return ""
        return "".join(node.text or "" for node in inline.iter(f"{{{MAIN_NS}}}t"))
    value = cell.findtext("m:v", default="", namespaces=NS)
    if cell_type == "s" and value:
        index = int(value)
        return shared[index] if 0 <= index < len(shared) else f"#BAD_SHARED_STRING:{index}"
    if cell_type == "b":
        return "TRUE" if value == "1" else "FALSE"
    return value


def parse_sheet(xml_bytes: bytes, shared: list[str]) -> tuple[list[list[str]], dict[str, object]]:
    root = ET.fromstring(xml_bytes)
    cells: dict[tuple[int, int], str] = {}
    max_row = 0
    max_col = 0
    formula_count = 0
    for cell in root.findall(".//m:sheetData/m:row/m:c", NS):
        ref = cell.attrib.get("r")
        if not ref:
            continue
        row, col = split_ref(ref)
        cells[(row, col)] = cell_text(cell, shared)
        max_row = max(max_row, row)
        max_col = max(max_col, col)
        if cell.find("m:f", NS) is not None:
            formula_count += 1

    merge_count = 0
    for merge in root.findall(".//m:mergeCells/m:mergeCell", NS):
        ref = merge.attrib.get("ref", "")
        if ":" not in ref:
            continue
        start, end = ref.split(":", 1)
        start_row, start_col = split_ref(start)
        end_row, end_col = split_ref(end)
        anchor = cells.get((start_row, start_col), "")
        if not anchor:
            continue
        merge_count += 1
        for row in range(start_row, end_row + 1):
            for col in range(start_col, end_col + 1):
                cells.setdefault((row, col), anchor)
                max_row = max(max_row, row)
                max_col = max(max_col, col)

    rows = [
        [cells.get((row, col), "") for col in range(1, max_col + 1)]
        for row in range(1, max_row + 1)
    ]
    return rows, {
        "rows": max_row,
        "columns": max_col,
        "non_empty_cells": sum(1 for value in cells.values() if value != ""),
        "formula_cells": formula_count,
        "merged_ranges_with_values": merge_count,
    }


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

    args.output_dir.mkdir(parents=True, exist_ok=True)
    selector = re.compile(args.sheet_pattern)
    manifest: dict[str, object] = {
        "input": str(args.input.resolve()),
        "method": "read-only OOXML; style table intentionally ignored",
        "sheets": [],
    }
    with zipfile.ZipFile(args.input) as archive:
        shared = read_shared_strings(archive)
        manifest["shared_string_count"] = len(shared)
        for index, (name, target) in enumerate(workbook_sheets(archive), start=1):
            if not selector.search(name):
                continue
            rows, stats = parse_sheet(archive.read(target), shared)
            filename = f"{index:02d}-{safe_name(name)}.csv"
            output = args.output_dir / filename
            with output.open("w", encoding="utf-8-sig", newline="") as handle:
                csv.writer(handle).writerows(rows)
            manifest["sheets"].append({
                "index": index,
                "name": name,
                "xml": target,
                "csv": filename,
                **stats,
            })

    (args.output_dir / "manifest.json").write_text(
        json.dumps(manifest, ensure_ascii=False, indent=2) + "\n",
        encoding="utf-8",
    )
    print(json.dumps(manifest, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()