"""M02 일위대가 실무자료 엑셀꼴 격자(PLAN 329-1) — 올린 엑셀 「일위대가」 · 「단가산출」 시트
(STmate = 「일위대가표」 · 「단가산출근거」 · 338) →
A00 산출근거 스프레드시트 Workbook(`A00_Common/spreadsheet/spreadsheet_types.ts` 꼴 ·
읽기 전용 보기).

xls = xlrd `formatting_info` — 값 · 병합 · 열 폭(÷256 글자) ·
행 높이(÷20 pt · 시트 기본과 다른 것만) · 서식 표(글꼴 · 굵게 · 맞춤 · 줄바꿈 · 테두리 · 채움 ·
숫자 형식) · 틀고정(파일 값 · 없으면 머리 4행).
xlsx = 값 · 열 폭(`<cols>`) · 넓은 시트는 머리 글(앞 4 줄) 마지막 열까지 ·
STmate 숨은 칸 글(「_x0005_`BDCOD…」) 뺌.
결과 `{책, 호표: [{번, 이름, 행}], 단산: [{번, 이름, 행}], 보기, 원본, 목록, 줄}` —
행 = 엑셀 1부터 [시작, 끝].
세 보기(779-1) = 탭 차례 일위대가표 · 단가산출표(목록표 — 엑셀꼴 단가산출총괄표 · STmate
단가산출근거목록표) · 단가산출상세 · `보기 {역할: 시트 id}` · `원본 {역할: 원본 시트 이름}` ·
`목록 {호표: [{번, 행}], 단산: [{번, 행}]}`(목록 시트의 행 1부터) ·
`줄 [{번, 차례, 행, 단산?: {번, 행: [시작, 끝]}, 일위?: {번, 행: [시작, 끝]}}]`.
"""

from __future__ import annotations

import io
import re
import zipfile
from typing import Any

from openpyxl.utils import get_column_letter
from openpyxl.xml.functions import fromstring, iterparse

from M02_MasterTemplete import M02_Hopyo_Excel as hopyo_excel
from M02_MasterTemplete import M02_Hopyo_ReadEst as hopyo_basis  # EST 단가산출 시트 이름
from M02_MasterTemplete import M02_Hopyo_Stmate as hopyo_stmate

LIST_E, HOPYO_S = hopyo_excel.LISTS
LIST_S = hopyo_stmate.LISTS[0]
SHEETS = (
    hopyo_excel.SHEET,
    LIST_E,
    hopyo_basis.SHEET,
    hopyo_stmate.SHEET,
    LIST_S,
    hopyo_stmate.CALC,
)
ROLES = {
    "일위대가표": (hopyo_excel.SHEET, hopyo_stmate.SHEET),
    "단가산출표": (LIST_E, LIST_S),
    "단가산출상세": (hopyo_basis.SHEET, hopyo_stmate.CALC),
}
_B, _D, _NO = re.compile(r"(B\d{5}|제\d+호표)"), re.compile(r"(D\d{5})"), re.compile(r"(\d+)")
_HOPYO = hopyo_excel._HOPYO
EXTRA = (LIST_E, hopyo_excel.SUMMARY, HOPYO_S)  # 격자 몫으로 더 읽는 시트
HEAD_ROWS = 4
_HALIGN = {1: "left", 2: "center", 3: "right", 6: "centerContinuous", 7: "distributed"}
_VALIGN = {0: "top", 1: "center"}
_LINES = {1: "thin", 2: "medium", 3: "dashed", 4: "dotted", 5: "thick", 6: "double", 7: "hair"}
_SIDES = (("위", "top"), ("아래", "bottom"), ("왼", "left"), ("오른", "right"))


class _Styles:
    """서식 표 — 같은 서식은 한 번만 · [0] = 기본 `{}`."""

    def __init__(self) -> None:
        self.table: list[dict[str, Any]] = [{}]
        self._index: dict[str, int] = {"{}": 0}

    def add(self, style: dict[str, Any]) -> int:
        key = repr(sorted(style.items()))
        if not style:
            return 0
        if key not in self._index:
            self._index[key] = len(self.table)
            self.table.append(style)
        return self._index[key]


def _rgb(book: Any, index: int) -> str | None:
    got = book.colour_map.get(index)
    return "#{:02X}{:02X}{:02X}".format(*got) if got else None


def _xf_style(book: Any, xf: Any, base: Any) -> dict[str, Any]:
    font = book.font_list[xf.font_index]
    out: dict[str, Any] = {}
    if font.name != base.name:
        out["글꼴"] = font.name
    if font.height != base.height:
        out["크기"] = font.height / 20
    for key, on in (
        ("굵게", font.bold),
        ("기울임", font.italic),
        ("밑줄", font.underline_type),
        ("취소선", font.struck_out),
    ):
        if on:
            out[key] = True
    color = _rgb(book, font.colour_index)
    if color and color != "#000000":
        out["글자색"] = color
    if xf.background.fill_pattern == 1 and (fill := _rgb(book, xf.background.pattern_colour_index)):
        out["채움"] = fill
    align = xf.alignment
    if align.hor_align in _HALIGN:
        out["가로"] = _HALIGN[align.hor_align]
    if align.vert_align in _VALIGN:
        out["세로"] = _VALIGN[align.vert_align]
    if align.text_wrapped:
        out["줄바꿈"] = True
    sides = {}
    for name, side in _SIDES:
        line = getattr(xf.border, f"{side}_line_style")
        if line:
            one = {"선": _LINES.get(line, "dashed")}
            color = _rgb(book, getattr(xf.border, f"{side}_colour_index"))
            if color and color != "#000000":
                one["색"] = color
            sides[name] = one
    if sides:
        out["테두리"] = sides
    fmt = book.format_map.get(xf.format_key)
    if fmt and fmt.format_str and fmt.format_str != "General":
        out["형식"] = fmt.format_str
    return out


def _value(cell: Any) -> Any:
    import xlrd

    if cell.ctype == xlrd.XL_CELL_TEXT:
        return cell.value or None
    if cell.ctype in (xlrd.XL_CELL_NUMBER, xlrd.XL_CELL_DATE):
        return int(cell.value) if cell.value == int(cell.value) else cell.value
    if cell.ctype == xlrd.XL_CELL_BOOLEAN:
        return bool(cell.value)
    if cell.ctype == xlrd.XL_CELL_ERROR:
        return xlrd.error_text_from_code.get(cell.value, "#N/A")
    return None


def _xls_sheet(book: Any, sh: Any, styles: _Styles, sid: str) -> dict[str, Any]:
    base = book.font_list[0]
    by_xf: dict[int, int] = {}
    cells: dict[str, dict[str, Any]] = {}
    for r in range(sh.nrows):
        for c in range(sh.ncols):
            cell = sh.cell(r, c)
            if cell.xf_index not in by_xf:
                by_xf[cell.xf_index] = styles.add(
                    _xf_style(book, book.xf_list[cell.xf_index], base)
                )
            one: dict[str, Any] = {}
            if (value := _value(cell)) is not None:
                one["값"] = value
            if by_xf[cell.xf_index]:
                one["서식"] = by_xf[cell.xf_index]
            if one:
                cells[f"{get_column_letter(c + 1)}{r + 1}"] = one
    merged = [
        f"{get_column_letter(c0 + 1)}{r0 + 1}:{get_column_letter(c1)}{r1}"
        for r0, r1, c0, c1 in sh.merged_cells
    ]
    cols = {
        get_column_letter(c + 1): {
            "폭": round(info.width / 256, 2),
            **({"숨김": True} if info.hidden else {}),
        }
        for c, info in sh.colinfo_map.items()
        if c < sh.ncols
    }
    rows = {
        str(r + 1): {"높이": info.height / 20, **({"숨김": True} if info.hidden else {})}
        for r, info in sh.rowinfo_map.items()
        if r < sh.nrows and (info.height != sh.default_row_height or info.hidden)
    }
    frozen = sh.panes_are_frozen and (sh.horz_split_pos or sh.vert_split_pos)
    freeze = {"행": sh.horz_split_pos, "열": sh.vert_split_pos} if frozen else {"행": HEAD_ROWS}
    out = {
        "id": sid,
        "이름": sh.name.strip(),
        "칸": cells,
        "병합": merged,
        "열": cols,
        "행": rows,
        "틀고정": {k: v for k, v in freeze.items() if v},
    }
    if sh.default_row_height:
        out["기본"] = {"행높이": sh.default_row_height / 20}
    return out


def _xls(book: Any) -> dict[str, Any]:
    """xlrd 책(`formatting_info` · 읽기가 연 것) → 격자."""
    styles = _Styles()
    found = {sh.name.strip(): sh for sh in book.sheets()}
    sheets = [
        _xls_sheet(book, found[name], styles, f"s{at}")
        for at, name in enumerate((n for n in SHEETS if n in found), 1)
    ]
    font = book.font_list[0]
    return {
        "기본": {"글꼴": font.name, "크기": font.height / 20},
        "서식": styles.table,
        "시트": sheets,
    }


def _xlsx_widths(
    data: bytes, names: tuple[str, ...] | None = None
) -> dict[str, dict[str, dict[str, Any]]]:
    """시트 이름 → 열 글자 → `{폭, 숨김}` — 시트 XML 머리 `<cols>` 만 읽음(`<sheetData>` 에서 멈춤 ·
    read_only 는 폭을 안 줌) · `names` = 이 시트만."""
    out: dict[str, dict[str, dict[str, Any]]] = {}
    try:
        with zipfile.ZipFile(io.BytesIO(data)) as zf:
            tag = lambda node: node.tag.rsplit("}", 1)[-1]  # noqa: E731
            rels = {
                r.get("Id"): r.get("Target", "")
                for r in fromstring(zf.read("xl/_rels/workbook.xml.rels"))
            }
            for sheet in fromstring(zf.read("xl/workbook.xml")).iter():
                if tag(sheet) != "sheet":
                    continue
                if names is not None and sheet.get("name", "").strip() not in names:
                    continue
                rid = next((v for k, v in sheet.attrib.items() if k.endswith("}id")), None)
                path = (rels.get(rid) or "").lstrip("/")
                path = path if path.startswith("xl/") else f"xl/{path}"
                cols: dict[str, dict[str, Any]] = {}
                for _, col in iterparse(zf.open(path), events=("start",)):
                    if tag(col) == "sheetData":
                        break
                    if tag(col) != "col":
                        continue
                    first, last = int(col.get("min", 0)), min(int(col.get("max", 0)), 200)
                    one = {"폭": round(float(col.get("width", 0)), 2)}
                    if col.get("hidden") in ("1", "true"):
                        one["숨김"] = True
                    for c in range(first, last + 1):
                        cols[get_column_letter(c)] = one
                out[sheet.get("name", "").strip()] = cols
    except (KeyError, ValueError, zipfile.BadZipFile):
        pass  # 폭을 못 읽으면 폭 없음 — 화면이 글 길이로 맞춤
    return out


def _xlsx(
    name: str, data: bytes, found: dict[str, list[list[Any]]] | None = None
) -> dict[str, Any]:
    # ponytail: xlsx 는 값 · 열 폭만(병합 · 서식은 openpyxl 비 read_only 로 무거움) — 필요하면 그때
    found = found or hopyo_excel._sheets(name, data, EXTRA)
    widths = _xlsx_widths(data, SHEETS)
    sheets = []
    for at, title in enumerate((n for n in SHEETS if n in found), 1):
        rows = found[title]
        width = max(
            (c + 1 for row in rows[:HEAD_ROWS] for c, v in enumerate(row) if v not in (None, "")),
            default=0,
        )
        cells = {
            f"{get_column_letter(c + 1)}{r + 1}": {
                "값": v if isinstance(v, (int, float, bool)) else str(v)
            }
            for r, row in enumerate(rows)
            for c, v in enumerate(row[:width])
            if v not in (None, "") and not str(v).startswith("_x000")  # STmate 숨은 칸 글
        }
        sheets.append(
            {
                "id": f"s{at}",
                "이름": title,
                "칸": cells,
                "열": widths.get(title, {}),
                "틀고정": {"행": HEAD_ROWS},
            }
        )
    return {"서식": [{}], "시트": sheets}


def _pairs(
    rows: list[list[Any]] | None, col: int, rx: re.Pattern[str], hidden: bool = False
) -> list[tuple[str, int]]:
    """목록 시트 → [(열쇠, 행)] — `col` 칸 글이 `rx` 에 꼭 맞는 줄(엑셀 1부터) · `hidden` =
    STmate 숨은 칸 「COD|…」 코드 먼저(목록 차례 번 ≠ 진짜 번)."""
    out = []
    for at, row in enumerate(rows or [], 1):
        meta = hopyo_stmate._META.search(" ".join(map(str, row))) if hidden else None
        cell = hopyo_excel._flat(hopyo_excel._text(row[col] if col < len(row) else None))
        if meta:
            out.append((meta.group(1), at))
        elif hit := rx.fullmatch(cell):
            out.append((hit.group(1), at))
    return out


def _lines(parsed: dict[str, Any]) -> list[dict[str, Any]]:
    """일위대가 줄 → 엑셀 행 · 단산 줄 → 블록 행 · 일위 줄 → 하위 호표 행."""
    block = {b["번"]: b["행"] for b in parsed.get("단산") or []}
    hopyo = {h["번"]: h["행"] for h in parsed.get("호표") or []}
    out = []
    for h in parsed.get("호표") or []:
        for line in h.get("줄") or []:
            one: dict[str, Any] = {"번": h["번"], "차례": line["차례"], "행": line.get("행")}
            ref = line.get("참조") or {}
            for key, rows in (("단산", block), ("일위", hopyo)):
                if ref.get("종류") == key and ref.get("번") in rows:
                    one[key] = {"번": ref["번"], "행": rows[ref["번"]]}
            out.append(one)
    return out


def _lists(rows: dict[str, Any], parsed: dict[str, Any]) -> dict[str, list[dict[str, int]]]:
    """목록 시트 행 ↔ 호표 번 · 단산(블록) 번."""
    if parsed.get("꼴") == "STmate":  # 숨은 코드(없으면 A 열) — 호표 = 코드 → 번 · 단산 = 코드 숫자
        code = {h["코드"]: h["번"] for h in parsed.get("호표") or [] if h.get("코드")}
        hopyo = [
            (int(k[1:-2]) if k[0] == "제" else code.get(k), at)
            for k, at in _pairs(rows.get(HOPYO_S), 0, _B, True)
        ]
        calc = [(int(k[1:]), at) for k, at in _pairs(rows.get(LIST_S), 0, _D, True)]
    else:  # B 열 「제N호표」 · 「No」
        hopyo = [(int(k), at) for k, at in _pairs(rows.get(hopyo_excel.SUMMARY), 1, _HOPYO)]
        calc = [(int(k), at) for k, at in _pairs(rows.get(LIST_E), 1, _NO)]
    pair = lambda got: [{"번": k, "행": at} for k, at in got if k is not None]  # noqa: E731
    return {"호표": pair(hopyo), "단산": pair(calc)}


def read(name: str, data: bytes, parsed: dict[str, Any]) -> dict[str, Any]:
    """엑셀 → `{책: Workbook, 호표, 단산, 보기, 원본, 목록, 줄}` — `parsed` =
    `M02_Hopyo_Excel.read_book` 결과(행 범위)."""
    rows, xls = hopyo_excel.opened(name, data, EXTRA)  # 읽기가 연 책 그대로(한 번 열기 · 776-2)
    book = _xlsx(name, data, rows) if xls is None else _xls(xls)
    keep = ("번", "이름", "행")
    ids = {s["이름"]: s["id"] for s in book["시트"]}
    on = {role: next((n for n in names if n in ids), None) for role, names in ROLES.items()}
    return {
        "책": {"종류": "통합문서", "판": 1, "열": "", **book, "활성": "s1"},
        "호표": [{k: h.get(k) for k in keep} for h in parsed.get("호표") or []],
        "단산": [{k: b.get(k) for k in keep} for b in parsed.get("단산") or []],
        "보기": {role: ids[n] for role, n in on.items() if n},
        "원본": {role: n for role, n in on.items() if n},
        "목록": _lists(rows, parsed),
        "줄": _lines(parsed),
    }