"""ks-xlsx-parser -> Markdown with form-layout consolidation.

Promoted from ``scripts/inject_headers_and_parse_ks_fix.py``.

Public API:
    ksparser_to_sheets(xlsx_path) -> List[{'sheet_name': str, 'markdown': str}]
    ksparser_to_md(xlsx_path)     -> str  (whole-workbook; kept for CLI use)
"""
from __future__ import annotations

import html as _html
import logging
import re
from collections import defaultdict
from typing import List

from src.extraction_v2.ks_parser.mermaid import collect_mermaid_blocks, restore_mermaid_blocks
from src.extraction_v2.xlsx_header_injection import convert_sentinels_to_atx

logger = logging.getLogger(__name__)


# ---------------------------------------------------------------------------
# Coordinate helpers
# ---------------------------------------------------------------------------

_A1 = re.compile(r"^([A-Z]+)(\d+)$")


def _a1_to_rc(a1: str) -> tuple[int, int]:
    """Parse "AB12" -> (row=12, col=28)."""
    m = _A1.match(a1.strip())
    if not m:
        raise ValueError(f"invalid A1 reference: {a1!r}")
    col = 0
    for ch in m.group(1):
        col = col * 26 + (ord(ch) - ord("A") + 1)
    return int(m.group(2)), col


def _block_type(chunk) -> str:
    bt = chunk.block_type
    return bt if isinstance(bt, str) else getattr(bt, "value", str(bt))


# ---------------------------------------------------------------------------
# Form-layout consolidation
# ---------------------------------------------------------------------------

# Max column distance between a label and its colon/value chunk on the same
# row. The 機能概要 sample has label at AG (33) and colon at AK (37) → gap 4.
# Padded to absorb similar layouts without crossing into unrelated regions.
MAX_LABEL_VALUE_COL_GAP = 10

# Maximum label text length. Form labels are short ("パッケージ", "クラス",
# "引数", "戻り値"); long text is narrative, not a label.
MAX_LABEL_TEXT_LEN = 20

_COLON_CHARS = ("：", ":")


def _cell_has_format(cell) -> bool:
    """Cell contains an injected <b>/<s>/<span> format wrapper."""
    if cell.find("b") or cell.find("s"):
        return True
    for s in cell.find_all("span"):
        style = (s.get("style") or "").lower()
        if style.startswith("color:") or style.startswith("background-color:"):
            return True
    return False


def _cell_texts(render_html: str | None) -> list[list[str]]:
    """Return ``render_html`` as a 2-D grid of cell texts (rows × cells).

    Cells whose inner content contains an injected format wrapper
    (``<b>`` or ``<span style="color:..">``) are returned as inner HTML
    so format survives multi-row consolidation. All other cells use
    flattened text.
    """
    if not render_html:
        return []
    from bs4 import BeautifulSoup

    soup = BeautifulSoup(render_html, "html.parser")
    grid: list[list[str]] = []
    for tr in soup.find_all("tr"):
        cells = tr.find_all(["td", "th"])
        row: list[str] = []
        for cell in cells:
            if _cell_has_format(cell):
                row.append(cell.decode_contents().strip())
            else:
                row.append(cell.get_text(" ", strip=True))
        grid.append(row)
    return grid


def _plain(t: str) -> str:
    """Strip injected format wrappers for plain-text checks."""
    if not t or "<" not in t:
        return t
    from bs4 import BeautifulSoup
    return BeautifulSoup(t, "html.parser").get_text(" ", strip=True)


def _escape_or_pass(t: str) -> str:
    """HTML-escape plain text; pass through if already contains format tags."""
    if t and ("<span" in t or "<b>" in t or "</b>" in t or "<s>" in t or "</s>" in t):
        return t
    return _html.escape(t)


def _has_text_content(html: str | None) -> bool:
    """True iff the HTML carries any visible text after tag-stripping."""
    if not html:
        return False
    if "<" not in html:
        return bool(html.strip())
    from bs4 import BeautifulSoup
    return bool(BeautifulSoup(html, "html.parser").get_text("", strip=True))


def _label_text(render_html: str | None) -> str | None:
    """If the chunk looks like a single-cell form label, return its text."""
    grid = _cell_texts(render_html)
    nonempty = [t for row in grid for t in row if _plain(t)]
    if len(nonempty) != 1:
        return None
    t = nonempty[0]
    plain = _plain(t)
    if len(plain) > MAX_LABEL_TEXT_LEN:
        return None
    if plain in _COLON_CHARS or plain.startswith(_COLON_CHARS):
        return None
    return t


def _is_colon_value(render_html: str | None) -> bool:
    """Chunk's first non-empty cell text is "：" or ":"."""
    grid = _cell_texts(render_html)
    for row in grid:
        for t in row:
            plain = _plain(t)
            if not plain:
                continue
            return plain in _COLON_CHARS or plain.startswith(_COLON_CHARS)
    return False


def _extract_value_text(render_html: str | None) -> str:
    """Pull the value content out of a colon-value chunk.

    For each row, drop leading empty cells and a single leading colon
    cell, then join remaining cells with spaces. Rows are joined with
    ``<br>`` so multi-row values (``引数``, ``戻り値``) stay on one entry.
    Returns HTML-escaped text safe for direct splicing into a ``<td>``.
    Cells already wrapped in ``<span>``/``<b>`` are passed through
    un-escaped to preserve injected format.
    """
    grid = _cell_texts(render_html)
    rows_out: list[str] = []
    for cells in grid:
        i = 0
        while i < len(cells) and not _plain(cells[i]):
            i += 1
        if i < len(cells) and _plain(cells[i]) in _COLON_CHARS:
            i += 1
        parts = [c for c in cells[i:] if _plain(c) and _plain(c) not in _COLON_CHARS]
        if parts:
            rows_out.append(" ".join(_escape_or_pass(p) for p in parts))
    return "<br>".join(rows_out)


def _build_pair_html(label_chunk, label_text: str, value_chunk) -> str:
    value_html = _extract_value_text(value_chunk.render_html)
    return (
        '<table data-block-type="form_pair" '
        f'data-sheet="{_html.escape(label_chunk.sheet_name)}" '
        f'data-range="{label_chunk.top_left_cell}:{value_chunk.bottom_right_cell}">'
        f"<tr><th>{_escape_or_pass(label_text)}</th>"
        f"<td>{value_html}</td></tr>"
        "</table>"
    )


# Narrative-looking text we should NOT treat as a form label, even if short.
_NARRATIVE_PREFIXES = ("・", "→", "⇒", "(", "（", "※")
_NARRATIVE_SUFFIXES = ("。", "、", "：", ":")


def _multi_row_label_rows(grid: list[list[str]]) -> dict[int, str]:
    """Per-row index → label text, keeping only rows that look like form labels."""
    out: dict[int, str] = {}
    for r_idx, row in enumerate(grid):
        nonempty = [t for t in row if _plain(t)]
        if len(nonempty) != 1:
            continue
        t = nonempty[0]
        plain = _plain(t)
        if len(plain) > MAX_LABEL_TEXT_LEN:
            continue
        if plain in _COLON_CHARS or plain.startswith(_COLON_CHARS):
            continue
        if any(plain.startswith(p) for p in _NARRATIVE_PREFIXES):
            continue
        if plain.endswith(_NARRATIVE_SUFFIXES):
            continue
        out[r_idx] = t
    return out


def _multi_row_value_rows(grid: list[list[str]]) -> dict[int, str]:
    """Per-row index → value text for a colon-value chunk.

    Returns ``{}`` if the chunk doesn't have at least two ``：`` markers
    (i.e. doesn't actually look like a multi-row colon-value block).
    """
    out: dict[int, str] = {}
    colon_count = 0
    for r_idx, row in enumerate(grid):
        i = 0
        while i < len(row) and not _plain(row[i]):
            i += 1
        if i < len(row) and _plain(row[i]) in _COLON_CHARS:
            colon_count += 1
            i += 1
        parts = [c for c in row[i:] if _plain(c) and _plain(c) not in _COLON_CHARS]
        if parts:
            out[r_idx] = " ".join(parts)
    return out if colon_count >= 2 else {}


def _try_multi_row_merge(label_chunk, value_chunk, *, allow_order: bool = True) -> bool:
    """Detect and merge a multi-row form pair. Returns True if merged.

    Two strategies:
      1. Row-aligned: pair labels and values that share absolute row indices,
         folding continuation value rows into the preceding label.
      2. Order-based fallback (when ``allow_order``): pair 1:1 by sequence
         when no rows align — used when ks-xlsx-parser split the form into
         two adjacent column bands without row alignment.
    """
    label_grid = _cell_texts(label_chunk.render_html)
    value_grid = _cell_texts(value_chunk.render_html)

    label_rel = _multi_row_label_rows(label_grid)
    value_rel = _multi_row_value_rows(value_grid)
    if len(label_rel) < 2 or len(value_rel) < 2:
        return False

    lr_start, _ = _a1_to_rc(label_chunk.top_left_cell)
    vr_start, _ = _a1_to_rc(value_chunk.top_left_cell)
    vr_end, _ = _a1_to_rc(value_chunk.bottom_right_cell)

    label_abs = {lr_start + idx: txt for idx, txt in label_rel.items()}
    value_abs = {vr_start + idx: txt for idx, txt in value_rel.items()}

    aligned_rows = set(label_abs) & set(value_abs)
    pairs: list[list[str]] = []

    if len(aligned_rows) >= 2:
        # Strategy 1: row-aligned with continuation folding
        current_label: str | None = None
        for r in range(vr_start, vr_end + 1):
            l = label_abs.get(r)
            v = value_abs.get(r, "")
            if l:
                current_label = l
                pairs.append([l, v])
            elif v and current_label and pairs:
                prev = pairs[-1][1]
                pairs[-1][1] = (prev + "<br>" + v) if prev else v
        drop_rel_rows = {r - lr_start for r in label_abs if r in value_abs}
    elif allow_order and len(label_rel) == len(value_rel):
        # Strategy 2: order-based 1:1 pairing
        label_items = sorted(label_rel.items())  # by row index
        value_items = sorted(value_rel.items())
        pairs = [[l_txt, v_txt] for (_, l_txt), (_, v_txt) in zip(label_items, value_items)]
        drop_rel_rows = set(label_rel.keys())
    else:
        return False

    if len(pairs) < 2:
        return False

    rows_html = "".join(
        f"<tr><th>{_escape_or_pass(l)}</th><td>{_escape_or_pass(v)}</td></tr>"
        for l, v in pairs
    )
    range_start = min(label_chunk.top_left_cell, value_chunk.top_left_cell)
    range_end = max(label_chunk.bottom_right_cell, value_chunk.bottom_right_cell)
    merged_html = (
        '<table data-block-type="form_pairs" '
        f'data-sheet="{_html.escape(label_chunk.sheet_name)}" '
        f'data-range="{range_start}:{range_end}">'
        + rows_html + "</table>"
    )
    # Splice the merged form table into the value chunk's slot
    value_chunk.render_html = merged_html

    # Remove the now-merged rows from the label chunk's HTML
    from bs4 import BeautifulSoup

    soup = BeautifulSoup(label_chunk.render_html, "html.parser")
    trs = soup.find_all("tr")
    for idx in sorted(drop_rel_rows, reverse=True):
        if 0 <= idx < len(trs):
            trs[idx].decompose()
    label_chunk.render_html = "" if not soup.find_all("tr") else str(soup)
    return True


def consolidate_form_layout(chunks: list) -> list:
    """Merge (label) + (：value) ``text_block`` chunk pairs.

    Returns a new list in original chunk order; matched value chunks are
    dropped and matched label chunks have ``render_html`` rewritten in-place.
    """
    by_sheet: dict[str, list] = defaultdict(list)
    for c in chunks:
        by_sheet[c.sheet_name].append(c)

    consumed: set[int] = set()
    merged_pairs = 0

    for sheet_chunks in by_sheet.values():
        try:
            coords = [
                (c, *_a1_to_rc(c.top_left_cell), *_a1_to_rc(c.bottom_right_cell))
                for c in sheet_chunks
            ]
        except ValueError:
            continue
        coords.sort(key=lambda x: (x[1], x[2]))  # (top_row, top_col)

        for i, (lbl, lr, lc, lbr, lbc) in enumerate(coords):
            if id(lbl) in consumed:
                continue
            if _block_type(lbl) != "text_block":
                continue
            label_text = _label_text(lbl.render_html)
            if label_text is None:
                continue

            for j in range(i + 1, len(coords)):
                val, vr, vc, vbr, vbc = coords[j]
                if vr > lbr:
                    break  # past the label's row band
                if id(val) in consumed:
                    continue
                if vr < lr or vc <= lbc:
                    continue  # not strictly to the right on the same row
                if vc - lbc > MAX_LABEL_VALUE_COL_GAP:
                    break
                if _block_type(val) != "text_block":
                    continue
                if not _is_colon_value(val.render_html):
                    continue

                lbl.render_html = _build_pair_html(lbl, label_text, val)
                consumed.add(id(val))
                merged_pairs += 1
                logger.debug(
                    "merged %s %r + %s",
                    lbl.top_left_cell, label_text, val.top_left_cell,
                )
                break

    # Multi-row passes. Done in TWO sub-passes so row-aligned pairs win
    # over order-based pairs when the same label chunk could match both.
    # Vertical proximity budget for order-based pairing (value chunk
    # above or below the label chunk, no row overlap).
    MAX_BLOCK_GAP_ROWS = 25
    multi_aligned = 0
    multi_ordered = 0

    def _pass(allow_order: bool) -> int:
        merged = 0
        for sheet_chunks in by_sheet.values():
            try:
                coords = [
                    (c, *_a1_to_rc(c.top_left_cell), *_a1_to_rc(c.bottom_right_cell))
                    for c in sheet_chunks
                    if id(c) not in consumed and (c.render_html or "").strip()
                ]
            except ValueError:
                continue
            coords.sort(key=lambda x: (x[1], x[2]))

            for i, (lbl, lr, lc, lbr, lbc) in enumerate(coords):
                if id(lbl) in consumed:
                    continue
                # Label chunk can be text_block OR table (some label blocks
                # are reported as TABLE — see AG185:AH193 in the IF sheet).
                if _block_type(lbl) not in ("text_block", "table"):
                    continue
                for j in range(len(coords)):
                    if i == j:
                        continue
                    val, vr, vc, vbr, vbc = coords[j]
                    if id(val) in consumed:
                        continue
                    if _block_type(val) != "text_block":
                        continue
                    if vc <= lbc or vc - lbc > MAX_LABEL_VALUE_COL_GAP:
                        continue
                    bands_overlap = not (vbr < lr or vr > lbr)
                    if not bands_overlap:
                        if not allow_order:
                            continue
                        gap = vr - lbr if vr > lbr else lr - vbr
                        if gap > MAX_BLOCK_GAP_ROWS:
                            continue
                    if _try_multi_row_merge(lbl, val, allow_order=allow_order):
                        merged += 1
                        if not lbl.render_html:
                            consumed.add(id(lbl))
                        logger.debug(
                            "multi-row (%s) merged %s + %s",
                            "ordered" if allow_order else "aligned",
                            lbl.top_left_cell, val.top_left_cell,
                        )
                        break
        return merged

    multi_aligned = _pass(allow_order=False)
    multi_ordered = _pass(allow_order=True)

    logger.info(
        "consolidated %d single + %d aligned + %d ordered multi-row form pair(s)",
        merged_pairs, multi_aligned, multi_ordered,
    )
    return [
        c for c in chunks
        if id(c) not in consumed and (c.render_html or "").strip()
    ]


# ---------------------------------------------------------------------------
# Consecutive form-pair merging (Issue 10)
# ---------------------------------------------------------------------------

_MAX_FORM_PAIR_GAP_ROWS = 25
_MAX_FORM_PAIR_COL_DRIFT = 6


def _is_form_pair_chunk(c) -> bool:
    h = c.render_html or ""
    return 'data-block-type="form_pair' in h


def _flush_form_pair_group(group: list, out: list) -> None:
    if not group:
        return
    if len(group) == 1:
        out.append(group[0])
        group.clear()
        return
    from bs4 import BeautifulSoup
    head = group[0]
    rows: list[str] = []
    for c in group:
        soup = BeautifulSoup(c.render_html or "", "html.parser")
        for tr in soup.find_all("tr"):
            rows.append(str(tr))
    range_start = head.top_left_cell
    range_end = group[-1].bottom_right_cell
    head.render_html = (
        '<table data-block-type="form_pairs" '
        f'data-sheet="{_html.escape(head.sheet_name)}" '
        f'data-range="{range_start}:{range_end}">'
        + "".join(rows) + "</table>"
    )
    out.append(head)
    group.clear()


def merge_consecutive_form_pairs(chunks: list) -> list:
    """Merge document-adjacent form_pair chunks in same column band."""
    out: list = []
    group: list = []
    merged = 0
    for c in chunks:
        if not _is_form_pair_chunk(c):
            if group:
                if len(group) > 1:
                    merged += len(group) - 1
                _flush_form_pair_group(group, out)
            out.append(c)
            continue
        if group:
            last = group[-1]
            row_gap = c.cell_range.top_left.row - last.cell_range.bottom_right.row
            col_drift_l = abs(c.cell_range.top_left.col - last.cell_range.top_left.col)
            col_drift_r = abs(c.cell_range.bottom_right.col - last.cell_range.bottom_right.col)
            if (c.sheet_name != last.sheet_name
                    or row_gap > _MAX_FORM_PAIR_GAP_ROWS
                    or col_drift_l > _MAX_FORM_PAIR_COL_DRIFT
                    or col_drift_r > _MAX_FORM_PAIR_COL_DRIFT):
                if len(group) > 1:
                    merged += len(group) - 1
                _flush_form_pair_group(group, out)
        group.append(c)
    if group and len(group) > 1:
        merged += len(group) - 1
    _flush_form_pair_group(group, out)
    logger.info("merged %d adjacent form-pair chunk(s) into multi-row tables", merged)
    return out


# ---------------------------------------------------------------------------
# DB-layout sibling column-chunk merging (Issue 11)
# ---------------------------------------------------------------------------

MAX_DB_CHUNK_WIDTH = 3   # narrow chunk: <= this many columns
MIN_DB_CHUNK_HEIGHT = 3  # only consider chunks with enough rows
MIN_ROW_OVERLAP = 2      # sibling chunks must share at least this many rows
MAX_COL_GAP = 25         # max columns of empty space between siblings


def _chunk_bbox(ch) -> tuple[int, int, int, int]:
    """Return (row_min, col_min, row_max, col_max), all 1-based."""
    cr = ch.cell_range
    return (cr.top_left.row, cr.top_left.col,
            cr.bottom_right.row, cr.bottom_right.col)


def _effective_col_count(ch) -> int:
    """Count distinct columns that have non-empty content."""
    cells = _parse_cells_absolute(ch.render_html or "")
    return len({c for _, c, txt, *_ in cells if txt})


_BAND_GAP_ROWS = 3  # row gap that triggers a band boundary


def split_chunks_by_row_gap(chunks: list) -> list:
    """Split a chunk when its content rows fall into distinct row-bands.

    A boundary is drawn when either:
      (a) gap to next content row >= _BAND_GAP_ROWS, or
      (b) next row's column footprint is disjoint from the previous
          content row's footprint.
    Helps when several semantic sections share one chunk.
    """
    from ks_xlsx_parser.models.common import CellCoord, CellRange
    result: list = []
    split_count = 0
    for ch in chunks:
        html = ch.render_html or ""
        # Don't re-fragment already-consolidated form pairs.
        if 'data-block-type="form_pair"' in html or 'data-block-type="form_pairs"' in html:
            result.append(ch)
            continue
        cells = _parse_cells_absolute(html, preserve_inner=True)
        if not cells:
            result.append(ch)
            continue
        rows_with_content = sorted({r for r, _, txt, *_ in cells if txt})
        if len(rows_with_content) < 2:
            result.append(ch)
            continue

        def _row_cols(rr: int) -> set[int]:
            return {c for r, c, txt, *_ in cells if txt and r == rr}

        # Build row bands by gap OR disjoint column footprint vs prior row.
        bands: list[list[int]] = [[rows_with_content[0]]]
        prev_cols = _row_cols(rows_with_content[0])
        for r in rows_with_content[1:]:
            cols = _row_cols(r)
            gap = r - bands[-1][-1]
            disjoint = bool(cols) and bool(prev_cols) and cols.isdisjoint(prev_cols)
            if gap >= _BAND_GAP_ROWS or (gap > 0 and disjoint):
                bands.append([r])
            else:
                bands[-1].append(r)
            prev_cols = cols
        if len(bands) <= 1:
            result.append(ch)
            continue
        # Emit each band as a new chunk.
        for band in bands:
            r_min, r_max = band[0], band[-1]
            cells_in_band = [(r, c, t, rs, cs, tg)
                             for r, c, t, rs, cs, tg in cells
                             if r_min <= r <= r_max]
            if not cells_in_band:
                continue
            col_min = min(c for _, c, *_ in cells_in_band)
            col_max = max(c + cs - 1 for _, c, _, _, cs, _ in cells_in_band)
            H = r_max - r_min + 1
            W = col_max - col_min + 1
            grid: list[list[tuple[str, str] | None]] = [[None] * W for _ in range(H)]
            for r, c, t, rs, cs, tg in cells_in_band:
                rr = r - r_min
                cc = c - col_min
                if 0 <= rr < H and 0 <= cc < W and grid[rr][cc] is None:
                    grid[rr][cc] = (t, tg)
            tl = _rc_to_a1(r_min, col_min)
            br = _rc_to_a1(r_max, col_max)
            parts = [f'<table data-sheet="{ch.sheet_name}" data-range="{tl}:{br}" data-block-type="row_band">']
            for rr in range(H):
                parts.append("<tr>")
                for cc in range(W):
                    cell = grid[rr][cc]
                    if cell is None:
                        parts.append("<td></td>")
                    else:
                        txt, tg = cell
                        t_tag = tg if tg in ("td", "th") else "td"
                        ref = _rc_to_a1(r_min + rr, col_min + cc)
                        parts.append(f'<{t_tag} data-ref="{ref}">{txt}</{t_tag}>')
                parts.append("</tr>")
            parts.append("</table>")
            new_ch = ch.model_copy(deep=True)
            new_ch.render_html = "".join(parts)
            new_ch.cell_range = CellRange(
                top_left=CellCoord(row=r_min, col=col_min),
                bottom_right=CellCoord(row=r_max, col=col_max),
            )
            new_ch.top_left_cell = tl
            new_ch.bottom_right_cell = br
            result.append(new_ch)
        split_count += 1
    if split_count:
        logger.info("split %d chunk(s) by row-gap into row bands", split_count)
    return result


def _parse_cells_absolute(html: str, preserve_inner: bool = False) -> list[tuple[int, int, str, int, int, str]]:
    """Return [(row, col, content, rowspan, colspan, tag), ...] from data-ref.

    When ``preserve_inner`` is True, content is the inner HTML so inline
    tags (e.g. ``<br/>``) survive round-tripping.
    """
    if not html:
        return []
    from bs4 import BeautifulSoup
    soup = BeautifulSoup(html, "html.parser")
    out: list[tuple[int, int, str, int, int, str]] = []
    for tr in soup.find_all("tr"):
        for cell in tr.find_all(["td", "th"]):
            ref = cell.get("data-ref")
            if not ref:
                continue
            try:
                r, c = _a1_to_rc(ref)
            except ValueError:
                continue
            try:
                rs = int(cell.get("rowspan") or 1)
            except (TypeError, ValueError):
                rs = 1
            try:
                cs = int(cell.get("colspan") or 1)
            except (TypeError, ValueError):
                cs = 1
            if preserve_inner:
                content = cell.decode_contents().strip()
            else:
                content = cell.get_text(" ", strip=True)
            out.append((r, c, content, rs, cs, cell.name))
    return out


def _rc_to_a1(row: int, col: int) -> str:
    s = ""
    c = col
    while c > 0:
        c, rem = divmod(c - 1, 26)
        s = chr(ord("A") + rem) + s
    return f"{s}{row}"


def _merge_group_html(group: list, sheet: str) -> str:
    """Build one HTML table containing absolute-positioned cells from all chunks."""
    all_cells: list[tuple[int, int, str, int, int, str]] = []
    for ch in group:
        all_cells.extend(_parse_cells_absolute(ch.render_html or "", preserve_inner=True))
    if not all_cells:
        return ""
    min_r = min(r for r, *_ in all_cells)
    min_c = min(c for _, c, *_ in all_cells)
    max_r = max(r + rs - 1 for r, _, _, rs, _, _ in all_cells)
    max_c = max(c + cs - 1 for _, c, _, _, cs, _ in all_cells)
    H = max_r - min_r + 1
    W = max_c - min_c + 1
    grid: list[list[tuple[str, str] | None]] = [[None] * W for _ in range(H)]
    # Place cells (only origin cell carries the text; let normalize handle spans).
    for r, c, txt, rs, cs, tag in all_cells:
        rr = r - min_r
        cc = c - min_c
        if 0 <= rr < H and 0 <= cc < W:
            if grid[rr][cc] is None:
                grid[rr][cc] = (txt, tag)
    # Emit a flat HTML table; downstream normalization drops empty cols.
    tl = _rc_to_a1(min_r, min_c)
    br = _rc_to_a1(max_r, max_c)
    parts = [f'<table data-sheet="{sheet}" data-range="{tl}:{br}" data-block-type="merged_columns">']
    for rr in range(H):
        parts.append("<tr>")
        for cc in range(W):
            cell = grid[rr][cc]
            if cell is None:
                parts.append("<td></td>")
            else:
                txt, tag = cell
                t = tag if tag in ("td", "th") else "td"
                ref = _rc_to_a1(min_r + rr, min_c + cc)
                parts.append(f'<{t} data-ref="{ref}">{txt}</{t}>')
        parts.append("</tr>")
    parts.append("</table>")
    return "".join(parts)


def merge_sibling_column_chunks(chunks: list) -> list:
    """Merge narrow chunks with overlapping rows into one wider chunk.

    Siblings need not be adjacent in the chunk list — any narrow chunk
    further down the same sheet whose rows overlap and whose column lies
    to the right of the current group anchor is a candidate.
    """
    n = len(chunks)

    def _is_narrow(ch) -> bool:
        cr = ch.cell_range
        h = cr.bottom_right.row - cr.top_left.row + 1
        if h < MIN_DB_CHUNK_HEIGHT:
            return False
        ew = _effective_col_count(ch)
        return 0 < ew <= MAX_DB_CHUNK_WIDTH

    used: set[int] = set()
    groups: list[list[int]] = []
    for i in range(n):
        if i in used or not _is_narrow(chunks[i]):
            continue
        group_idx = [i]
        anchor_col = chunks[i].cell_range.bottom_right.col
        ref_rows = (chunks[i].cell_range.top_left.row,
                    chunks[i].cell_range.bottom_right.row)
        for j in range(i + 1, n):
            if j in used:
                continue
            ch2 = chunks[j]
            if ch2.sheet_name != chunks[i].sheet_name:
                continue
            if not _is_narrow(ch2):
                continue
            cr2 = ch2.cell_range
            row_overlap = (min(ref_rows[1], cr2.bottom_right.row)
                           - max(ref_rows[0], cr2.top_left.row) + 1)
            if row_overlap < MIN_ROW_OVERLAP:
                continue
            if cr2.top_left.col <= anchor_col:
                continue
            if cr2.top_left.col - anchor_col > MAX_COL_GAP:
                continue
            group_idx.append(j)
            anchor_col = cr2.bottom_right.col
        if len(group_idx) >= 2:
            for k in group_idx:
                used.add(k)
            groups.append(group_idx)

    # Build new chunk list. Replace first of each group with merged; drop rest.
    drop = set()
    first_to_group = {}
    for g in groups:
        first_to_group[g[0]] = g
        for k in g[1:]:
            drop.add(k)

    from ks_xlsx_parser.models.common import CellCoord, CellRange
    result: list = []
    for i in range(n):
        if i in drop:
            continue
        ch = chunks[i]
        if i in first_to_group:
            g = first_to_group[i]
            group_chunks = [chunks[k] for k in g]
            merged_html = _merge_group_html(group_chunks, ch.sheet_name)
            if merged_html:
                bboxes = [_chunk_bbox(c) for c in group_chunks]
                rmin = min(b[0] for b in bboxes)
                cmin = min(b[1] for b in bboxes)
                rmax = max(b[2] for b in bboxes)
                cmax = max(b[3] for b in bboxes)
                ch.render_html = merged_html
                ch.cell_range = CellRange(
                    top_left=CellCoord(row=rmin, col=cmin),
                    bottom_right=CellCoord(row=rmax, col=cmax),
                )
                ch.top_left_cell = _rc_to_a1(rmin, cmin)
                ch.bottom_right_cell = _rc_to_a1(rmax, cmax)
        result.append(ch)
    if groups:
        logger.info("merged %d DB-layout column group(s) (%d -> %d chunks)",
                    len(groups), n, len(result))
    return result


# ---------------------------------------------------------------------------
# HTML normalization (Issues 5, 12)
# ---------------------------------------------------------------------------

def _row_signature(row: list) -> frozenset:
    """Set of column indices where a non-empty value starts a new run."""
    sig: set[int] = set()
    prev_text: str | None = None
    for c, cell in enumerate(row):
        text = cell[0] if cell else ""
        if text and text != prev_text:
            sig.add(c)
        prev_text = text
    return frozenset(sig)


def _row_is_data_like(row: list) -> bool:
    """A row is data-like if it has at least one non-empty cell."""
    return any((c and c[0]) for c in row)


_SUBTABLE_GAP_ROWS = 2  # consecutive non-multi-cell rows that mark a sub-table boundary


def _split_into_subtables(grid: list[list]) -> list[list[list]]:
    """Group consecutive rows into sub-tables."""
    groups: list[list[list]] = []
    current_group: list[list] = []
    current_sig: frozenset = frozenset()
    pending_gap: list[list] = []
    gap_count = 0

    def _flush_pending_to_current():
        nonlocal pending_gap
        if current_group and pending_gap:
            current_group.extend(pending_gap)
        pending_gap = []

    for row in grid:
        sig = _row_signature(row) if _row_is_data_like(row) else frozenset()
        is_multi = len(sig) >= 2
        if not is_multi:
            pending_gap.append(row)
            gap_count += 1
            continue
        start_new = False
        if current_group:
            if gap_count >= _SUBTABLE_GAP_ROWS:
                start_new = True
            elif current_sig:
                overlap = len(sig & current_sig) / max(len(sig), len(current_sig))
                if overlap < 0.6:
                    start_new = True
        if start_new:
            _flush_pending_to_current()
            groups.append(current_group)
            current_group = [row]
            current_sig = sig
        else:
            _flush_pending_to_current()
            current_group.append(row)
            current_sig = sig
        gap_count = 0
    if current_group:
        if pending_gap:
            current_group.extend(pending_gap)
        groups.append(current_group)
    elif pending_gap:
        groups.append(pending_gap)
    return groups


def _normalize_subtable(rows: list[list]) -> list[list[tuple[str, str]]]:
    """Apply column-group merging to a sub-table's grid. Returns clean rows."""
    if not rows:
        return []
    width = max(len(r) for r in rows)
    for row in rows:
        while len(row) < width:
            row.append(None)

    def _texts(c: int) -> list[str]:
        return [rows[r][c][0] if rows[r][c] else "" for r in range(len(rows))]

    col_groups: list[list[int]] = [[0]] if width else []
    for c in range(1, width):
        cur = _texts(c)
        prev = _texts(col_groups[-1][-1])
        consistent = True
        has_duplicate = False
        for a, b in zip(prev, cur):
            if a and b and a != b:
                consistent = False
                break
            if a and b and a == b:
                has_duplicate = True
        if consistent and has_duplicate:
            col_groups[-1].append(c)
        else:
            col_groups.append([c])

    # Drop empty column groups
    kept_groups = []
    for group in col_groups:
        any_value = False
        for r in range(len(rows)):
            for c in group:
                cell = rows[r][c]
                if cell and cell[0]:
                    any_value = True
                    break
            if any_value:
                break
        if any_value:
            kept_groups.append(group)
    if not kept_groups:
        return []

    new_rows: list[list[tuple[str, str]]] = []
    for r in range(len(rows)):
        new_row: list[tuple[str, str]] = []
        for group in kept_groups:
            text = ""
            tag = "td"
            for c in group:
                cell = rows[r][c]
                if cell and cell[0]:
                    text, tag = cell
                    break
            new_row.append((text, tag))
        new_rows.append(new_row)

    # Drop colon-only column groups (label | ： | value → label | value).
    if new_rows:
        ncols = len(new_rows[0])
        drop_cols: list[int] = []
        for ci in range(1, ncols):
            non_empty = [r[ci][0] for r in new_rows if r[ci][0]]
            if len(non_empty) < 2:
                continue
            if all(_is_colon_text(v) for v in non_empty):
                drop_cols.append(ci)
        if drop_cols:
            keep = [ci for ci in range(ncols) if ci not in drop_cols]
            new_rows = [[r[ci] for ci in keep] for r in new_rows]

    # Drop trailing empty rows
    while new_rows and not any(c[0] for c in new_rows[-1]):
        new_rows.pop()
    return new_rows


def _is_colon_text(s: str) -> bool:
    """True when text is only colon-like chars (with optional whitespace).
    Strips inline HTML tags first so wrapped colons (`<span>：</span>`) match.
    """
    t = (s or "").strip()
    if not t:
        return False
    if "<" in t:
        from bs4 import BeautifulSoup
        t = BeautifulSoup(t, "html.parser").get_text().strip()
        if not t:
            return False
    return all(ch in ("：", ":", " ", "　") for ch in t)


def _set_cell_content(cell, text: str, soup) -> None:
    """Write text into cell. If text contains format-wrapper HTML (<b>, <span
    style="color:..."> from `_inject_format_tags_html`), parse and append as
    children; otherwise set as plain string."""
    if not text:
        return
    has_format = (
        "<b>" in text
        or '<span style="color:' in text
        or '<span style="background-color:' in text
    )
    if has_format:
        from bs4 import BeautifulSoup
        frag = BeautifulSoup(text, "html.parser")
        for child in list(frag.children):
            cell.append(child)
    else:
        cell.string = text


def _normalize_table_html(html: str) -> str:
    from bs4 import BeautifulSoup, NavigableString

    soup = BeautifulSoup(html, "html.parser")
    changed = False
    for table in soup.find_all("table"):
        # Already-clean form_pair tables produced by this script — skip.
        if table.get("data-block-type") in ("form_pair", "form_pairs"):
            continue

        rows = table.find_all("tr")
        if not rows:
            continue

        # Build a logical grid honoring colspan/rowspan placement.
        grid: list[list[tuple[str, str] | None]] = []
        pending: dict[tuple[int, int], tuple[str, str]] = {}

        for r_idx, tr in enumerate(rows):
            while len(grid) <= r_idx:
                grid.append([])
            row = grid[r_idx]
            c_idx = 0

            def _place(text: str, tag: str, rspan: int, cspan: int):
                nonlocal c_idx, row
                while (r_idx, c_idx) in pending:
                    while len(row) <= c_idx:
                        row.append(None)
                    row[c_idx] = pending.pop((r_idx, c_idx))
                    c_idx += 1
                for dr in range(rspan):
                    target_r = r_idx + dr
                    while len(grid) <= target_r:
                        grid.append([])
                    target_row = grid[target_r]
                    for dc in range(cspan):
                        target_c = c_idx + dc
                        if dr == 0 and dc == 0:
                            while len(target_row) <= target_c:
                                target_row.append(None)
                            target_row[target_c] = (text, tag)
                        else:
                            if dr == 0:
                                while len(target_row) <= target_c:
                                    target_row.append(None)
                                target_row[target_c] = (text, tag)
                            else:
                                pending[(target_r, target_c)] = (text, tag)
                c_idx += cspan

            for cell in tr.find_all(["td", "th"], recursive=False):
                has_fmt = bool(cell.find("b") or cell.find("s")) or any(
                    (s.get("style") or "").lower().startswith(("color:", "background-color:"))
                    for s in cell.find_all("span")
                )
                if has_fmt:
                    text = cell.decode_contents().strip()
                else:
                    text = cell.get_text(" ", strip=True)
                try:
                    rspan = int(cell.get("rowspan") or 1)
                except (TypeError, ValueError):
                    rspan = 1
                try:
                    cspan = int(cell.get("colspan") or 1)
                except (TypeError, ValueError):
                    cspan = 1
                _place(text, cell.name, rspan, cspan)
            row = grid[r_idx]
            while (r_idx, c_idx) in pending:
                while len(row) <= c_idx:
                    row.append(None)
                row[c_idx] = pending.pop((r_idx, c_idx))
                c_idx += 1

        if not grid or not any(grid):
            continue

        width = max(len(r) for r in grid)
        for row in grid:
            while len(row) < width:
                row.append(None)

        subtables = _split_into_subtables(grid)
        all_normalized: list[list[list[tuple[str, str]]]] = []
        for sub in subtables:
            norm = _normalize_subtable([row[:] for row in sub])
            if norm:
                all_normalized.append(norm)

        if not all_normalized:
            table.decompose()
            changed = True
            continue

        total_cols_after = sum(len(rows[0]) for rows in all_normalized if rows)
        total_cols_before = width * 1
        if total_cols_after >= total_cols_before:
            continue

        for child in list(table.children):
            if isinstance(child, NavigableString):
                child.extract()
            else:
                child.decompose()

        if len(all_normalized) == 1:
            new_tbody = soup.new_tag("tbody")
            for row in all_normalized[0]:
                tr = soup.new_tag("tr")
                for text, tag in row:
                    cell = soup.new_tag(tag if tag in ("td", "th") else "td")
                    _set_cell_content(cell, text, soup)
                    tr.append(cell)
                new_tbody.append(tr)
            table.append(new_tbody)
        else:
            for i, rows in enumerate(all_normalized):
                if i == 0:
                    tbody = soup.new_tag("tbody")
                    target = table
                else:
                    new_table = soup.new_tag("table")
                    for k, v in (table.attrs or {}).items():
                        if k != "data-block-type":
                            new_table[k] = v
                    new_table["data-block-type"] = "subtable"
                    tbody = soup.new_tag("tbody")
                    target = new_table
                for row in rows:
                    tr = soup.new_tag("tr")
                    for text, tag in row:
                        cell = soup.new_tag(tag if tag in ("td", "th") else "td")
                        _set_cell_content(cell, text, soup)
                        tr.append(cell)
                    tbody.append(tr)
                target.append(tbody)
                if i > 0:
                    table.insert_after(target)
                    table = target
        changed = True

    return str(soup) if changed else html


def normalize_chunks(chunks: list) -> int:
    """Apply HTML normalization to every chunk in place. Returns count changed."""
    n = 0
    for c in chunks:
        if not c.render_html:
            continue
        new_html = _normalize_table_html(c.render_html)
        if new_html != c.render_html:
            c.render_html = new_html
            n += 1
    return n


# ---------------------------------------------------------------------------
# Cell formatting (bold / text color / background color)
# ---------------------------------------------------------------------------

import re as _re_fmt

_RE_FONT_WEIGHT = _re_fmt.compile(r"font-weight\s*:\s*([^;]+)", _re_fmt.I)
_RE_COLOR = _re_fmt.compile(r"(?:^|[;\s])color\s*:\s*([^;]+)", _re_fmt.I)
_RE_BG_COLOR = _re_fmt.compile(r"background(?:-color)?\s*:\s*([^;]+)", _re_fmt.I)

_DEFAULT_TEXT_COLORS = {"#000", "#000000", "black", "inherit", "initial", "currentcolor"}
_DEFAULT_BG_COLORS = {"#fff", "#ffffff", "white", "transparent", "none", "inherit", "initial"}


def _extract_format(style: str, bgcolor_attr: str = "") -> dict:
    bold = False
    color = None
    bg = None
    if style:
        m = _RE_FONT_WEIGHT.search(style)
        if m:
            v = m.group(1).strip().lower()
            if v == "bold" or (v.isdigit() and int(v) >= 600):
                bold = True
        m = _RE_COLOR.search(style)
        if m:
            v = m.group(1).strip().lower()
            if v and v not in _DEFAULT_TEXT_COLORS:
                color = v
        m = _RE_BG_COLOR.search(style)
        if m:
            v = m.group(1).strip().lower().split()[0]
            if v and v not in _DEFAULT_BG_COLORS:
                bg = v
    if bgcolor_attr and not bg:
        v = bgcolor_attr.strip().lower()
        if v and v not in _DEFAULT_BG_COLORS:
            bg = v
    return {"bold": bold, "color": color, "bg": bg}


def _inject_format_tags_html(html: str) -> str:
    """Pre-pass: wrap each formatted cell's inner content with <b>/<span>."""
    if not html or ("style=" not in html and "bgcolor" not in html.lower()):
        return html
    from bs4 import BeautifulSoup
    soup = BeautifulSoup(html, "html.parser")
    changed = False
    for cell in soup.find_all(["td", "th"]):
        style = cell.get("style") or ""
        bgcolor_attr = cell.get("bgcolor") or ""
        fmt = _extract_format(style, bgcolor_attr)
        if not (fmt["bold"] or fmt["color"] or fmt["bg"]):
            continue
        inner = cell.decode_contents().strip()
        if not inner:
            continue
        # Skip mermaid / multi-line code cells.
        if "```mermaid" in inner or "flowchart " in inner:
            continue
        new_inner = inner
        if fmt["color"] or fmt["bg"]:
            parts = []
            if fmt["color"]:
                parts.append(f'color:{fmt["color"]}')
            if fmt["bg"]:
                parts.append(f'background-color:{fmt["bg"]}')
            new_inner = f'<span style="{";".join(parts)}">{new_inner}</span>'
        if fmt["bold"]:
            new_inner = f'<b>{new_inner}</b>'
        if new_inner == inner:
            continue
        frag = BeautifulSoup(new_inner, "html.parser")
        cell.clear()
        for child in list(frag.children):
            cell.append(child)
        changed = True
    return str(soup) if changed else html


def inject_format_tags_in_chunks(chunks: list) -> int:
    n = 0
    for c in chunks:
        if not c.render_html:
            continue
        new_html = _inject_format_tags_html(c.render_html)
        if new_html != c.render_html:
            c.render_html = new_html
            n += 1
    return n


# ---------------------------------------------------------------------------
# Strikethrough injection (Issue 13)
# ---------------------------------------------------------------------------

def _collect_strike_map(xlsx_path: str) -> dict[tuple[str, str], str]:
    """Per-cell HTML inner with <s>...</s> wrappers when strike is present."""
    try:
        from openpyxl import load_workbook
        from openpyxl.cell.rich_text import CellRichText, TextBlock
    except ImportError:
        return {}
    try:
        wb = load_workbook(xlsx_path, rich_text=True, data_only=True)
    except Exception as e:
        logger.warning("strike pass: openpyxl load failed: %s", e)
        return {}
    out: dict[tuple[str, str], str] = {}
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                v = cell.value
                if v is None:
                    continue
                if isinstance(v, CellRichText):
                    flat: list[tuple[str, bool]] = []
                    for run in v:
                        if isinstance(run, TextBlock):
                            text = run.text
                            strike = bool(run.font and getattr(run.font, "strike", False))
                        else:
                            text = str(run)
                            strike = False
                        if not text:
                            continue
                        if flat and flat[-1][1] == strike:
                            flat[-1] = (flat[-1][0] + text, strike)
                        else:
                            flat.append((text, strike))
                    has_strike = any(s for _, s in flat)
                    if has_strike:
                        parts = [
                            f"<s>{_html.escape(t)}</s>" if s else _html.escape(t)
                            for t, s in flat
                        ]
                        out[(ws.title, cell.coordinate)] = "".join(parts)
                else:
                    f = cell.font
                    if f and getattr(f, "strike", False):
                        text = str(v)
                        if text:
                            out[(ws.title, cell.coordinate)] = f"<s>{_html.escape(text)}</s>"
    return out


def inject_strike_tags_in_chunks(chunks: list, xlsx_path: str) -> int:
    strike_map = _collect_strike_map(xlsx_path)
    if not strike_map:
        return 0
    from bs4 import BeautifulSoup
    n = 0
    for c in chunks:
        html = c.render_html or ""
        if 'data-ref="' not in html:
            continue
        soup = BeautifulSoup(html, "html.parser")
        changed = False
        for cell in soup.find_all(["td", "th"]):
            ref = cell.get("data-ref")
            if not ref:
                continue
            wrapped = strike_map.get((c.sheet_name, ref))
            if not wrapped:
                continue
            if not cell.get_text("", strip=True):
                continue
            frag = BeautifulSoup(wrapped, "html.parser")
            cell.clear()
            for child in list(frag.children):
                cell.append(child)
            changed = True
        if changed:
            c.render_html = str(soup)
            n += 1
    return n


# ---------------------------------------------------------------------------
# Page-column reordering (Japanese 2-column spec docs)
# ---------------------------------------------------------------------------

_PAGE_COL_GUTTER_MIN = 10


def _get_page_row_breaks(xlsx_path: str) -> dict:
    """Return {sheet_name: [break_row_1_based, ...]} from xlsx page-break metadata."""
    try:
        from openpyxl import load_workbook
    except ImportError:
        return {}
    out: dict = {}
    try:
        wb = load_workbook(xlsx_path, read_only=False, data_only=True)
    except Exception as exc:
        logger.warning("openpyxl load failed for page-breaks: %s", exc)
        return {}
    for sn in wb.sheetnames:
        ws = wb[sn]
        rb = ws.row_breaks
        out[sn] = sorted(b.id for b in rb.brk) if rb and rb.brk else []
    return out


def _pages_from_breaks(breaks: list) -> list:
    """Convert sorted break rows to list of (row_start, row_end) pages (1-based, inclusive)."""
    pages: list = []
    prev = 1
    for b in breaks:
        if b >= prev:
            pages.append((prev, b))
            prev = b + 1
    pages.append((prev, 10**9))
    return pages


def _reorder_page_chunks(page_chunks: list) -> list:
    """If the page has 2 vertical bands separated by a wide column gutter,
    walk in row order, flushing pending left+right band buffers whenever a
    full-width chunk appears.
    """
    if len(page_chunks) < 2:
        return page_chunks
    total_cols = set()
    for c in page_chunks:
        for col in range(c.cell_range.top_left.col, c.cell_range.bottom_right.col + 1):
            total_cols.add(col)
    if len(total_cols) < 2:
        return page_chunks
    total_span = max(total_cols) - min(total_cols) + 1
    narrow_threshold = max(1, total_span // 2)
    narrow_cols = set()
    for c in page_chunks:
        width = c.cell_range.bottom_right.col - c.cell_range.top_left.col + 1
        if width <= narrow_threshold:
            for col in range(c.cell_range.top_left.col, c.cell_range.bottom_right.col + 1):
                narrow_cols.add(col)
    if len(narrow_cols) < 2:
        return sorted(page_chunks, key=lambda c: (c.cell_range.top_left.row,
                                                  c.cell_range.top_left.col))
    sorted_cols = sorted(narrow_cols)
    best_score = 0
    max_gap = 0
    gap_after = None
    for i in range(len(sorted_cols) - 1):
        gap = sorted_cols[i + 1] - sorted_cols[i]
        if gap < _PAGE_COL_GUTTER_MIN:
            continue
        candidate = sorted_cols[i]
        left_n = sum(1 for c in page_chunks
                     if c.cell_range.bottom_right.col <= candidate)
        right_n = sum(1 for c in page_chunks
                      if c.cell_range.top_left.col > candidate)
        score = gap * min(left_n, right_n)
        if score > best_score:
            best_score = score
            max_gap = gap
            gap_after = candidate
    if max_gap < _PAGE_COL_GUTTER_MIN or gap_after is None:
        return sorted(page_chunks, key=lambda c: (c.cell_range.top_left.row,
                                                  c.cell_range.top_left.col))
    key = lambda c: (c.cell_range.top_left.row, c.cell_range.top_left.col)
    ordered = sorted(page_chunks, key=key)
    out: list = []
    pending_left: list = []
    pending_right: list = []

    def flush() -> None:
        out.extend(pending_left)
        out.extend(pending_right)
        pending_left.clear()
        pending_right.clear()

    for c in ordered:
        cmin = c.cell_range.top_left.col
        cmax = c.cell_range.bottom_right.col
        if cmin <= gap_after and cmax > gap_after:
            flush()
            out.append(c)
        elif cmax <= gap_after:
            pending_left.append(c)
        else:
            pending_right.append(c)
    flush()
    return out


def reorder_chunks_by_page_columns(chunks: list, xlsx_path: str) -> list:
    """Reorder chunks so left-band reads top→bottom before right-band, per page."""
    breaks_by_sheet = _get_page_row_breaks(xlsx_path)
    if not breaks_by_sheet:
        return chunks
    by_sheet: dict = {}
    sheet_order: list = []
    for c in chunks:
        sn = c.sheet_name
        if sn not in by_sheet:
            by_sheet[sn] = []
            sheet_order.append(sn)
        by_sheet[sn].append(c)
    n_reordered = 0
    out: list = []
    for sn in sheet_order:
        sheet_chunks = by_sheet[sn]
        breaks = breaks_by_sheet.get(sn, [])
        pages = _pages_from_breaks(breaks) if breaks else [(1, 10**9)]
        for r0, r1 in pages:
            page = [c for c in sheet_chunks
                    if r0 <= c.cell_range.top_left.row <= r1]
            if not page:
                continue
            before = [id(c) for c in page]
            reordered = _reorder_page_chunks(page)
            if [id(c) for c in reordered] != before:
                n_reordered += 1
            out.extend(reordered)
    logger.info("reordered chunks on %d page(s) with 2-column layout", n_reordered)
    return out


# ---------------------------------------------------------------------------
# Sentinel-lift helpers
# ---------------------------------------------------------------------------

_TABLE_ROW_RE = re.compile(r"^\s*\|")
_SENTINEL_INLINE_RE = re.compile(
    r"\*{0,2}\s*§§(H[1-6])§§(.+?)§§/\1§§\s*\*{0,2}",
    re.DOTALL,
)
_ROW_EMPTY_RE = re.compile(r"^[\s|*]*$")

_RE_ORPHAN_EMPTY_HEADER = re.compile(r"\|\s*(\|\s*)+")
_RE_ORPHAN_SEP_ROW = re.compile(r"\|\s*(?:---+\s*\|\s*)+")


def _lift_sentinels_from_tables(md: str) -> tuple[str, int]:
    """Lift §§HN§§…§§/HN§§ sentinels out of table-row lines."""
    lines = md.splitlines()
    out: list[str] = []
    i = 0
    lifted = 0
    while i < len(lines):
        if not _TABLE_ROW_RE.match(lines[i]):
            out.append(lines[i])
            i += 1
            continue
        j = i
        while j < len(lines) and _TABLE_ROW_RE.match(lines[j]):
            j += 1
        block = lines[i:j]
        prefix: list[str] = []
        new_block: list[str] = []
        for row in block:
            matches = list(_SENTINEL_INLINE_RE.finditer(row))
            if not matches:
                new_block.append(row)
                continue
            for m in matches:
                lvl = m.group(1)
                txt = re.sub(r"\s+", " ", m.group(2).strip())
                prefix.append(f"§§{lvl}§§{txt}§§/{lvl}§§")
                lifted += 1
            stripped = _SENTINEL_INLINE_RE.sub("", row)
            if _ROW_EMPTY_RE.match(stripped):
                continue
            new_block.append(stripped)
        if prefix:
            if out and out[-1].strip():
                out.append("")
            out.extend(prefix)
            out.append("")
        out.extend(new_block)
        i = j
    return "\n".join(out), lifted


def _strip_orphan_separator_tables(md: str) -> tuple[str, int]:
    """Drop empty header + separator + (optional empty body) leftover tables."""
    lines = md.splitlines()
    out: list[str] = []
    i = 0
    dropped = 0
    while i < len(lines):
        line = lines[i]
        if _RE_ORPHAN_EMPTY_HEADER.fullmatch(line.strip()):
            j = i + 1
            if j < len(lines) and _RE_ORPHAN_SEP_ROW.fullmatch(lines[j].strip()):
                k = j + 1
                body_empty = True
                while k < len(lines) and lines[k].lstrip().startswith("|"):
                    if not _RE_ORPHAN_EMPTY_HEADER.fullmatch(lines[k].strip()):
                        body_empty = False
                        break
                    k += 1
                if body_empty:
                    dropped += 1
                    i = k
                    continue
        out.append(line)
        i += 1
    return "\n".join(out), dropped


# ---------------------------------------------------------------------------
# _FormattedConverter
# ---------------------------------------------------------------------------

def _make_formatted_converter():
    """Return a _FormattedConverter class (markdownify subclass)."""
    from markdownify import MarkdownConverter

    class _FormattedConverter(MarkdownConverter):
        def convert_span(self, el, text, parent_tags):
            style = (el.get("style") or "").strip()
            if not style or not (text and text.strip()):
                return text
            # Keep only color / background-color
            keep = []
            for part in style.split(";"):
                p = part.strip().lower()
                if p.startswith("color:") or p.startswith("background-color:"):
                    keep.append(p)
            if not keep:
                return text
            return f'<span style="{";".join(keep)}">{text}</span>'

    return _FormattedConverter


# ---------------------------------------------------------------------------
# Sentinel materialization (thin wrapper over xlsx_header_injection)
# ---------------------------------------------------------------------------

def _materialize_sentinels_safely(md: str) -> tuple[str, dict]:
    """Lift sentinels from table rows, materialize ATX headers, strip orphans."""
    md, n_lifted = _lift_sentinels_from_tables(md)
    if n_lifted:
        logger.debug("lifted %d sentinel(s) out of table rows", n_lifted)
    md, hdr_counts = convert_sentinels_to_atx(md)
    if hdr_counts:
        logger.debug("materialized headers: %s", hdr_counts)
    md, n_orphans = _strip_orphan_separator_tables(md)
    if n_orphans:
        logger.debug("stripped %d orphan separator-only table(s)", n_orphans)
    return md, hdr_counts


# ---------------------------------------------------------------------------
# Public API
# ---------------------------------------------------------------------------

def ksparser_to_sheets(xlsx_path: str) -> List[dict]:
    """Parse ``xlsx_path`` and return per-sheet markdown.

    Returns a list of ``{'sheet_name': str, 'markdown': str}`` dicts, one
    per non-empty sheet.  Sheet order follows openpyxl ``sheetnames``.

    Design (from spec Design Notes):
      mermaid_blocks collected once before the loop;
      chunks reordered whole-workbook (cross-sheet page-break context);
      per-sheet: format/strike injection → form consolidation →
        merge pairs → split by row gap → merge sibling columns →
        normalize → filter empty → HTML→MD via _FormattedConverter →
        restore mermaid → materialize sentinels.
    """
    from ks_xlsx_parser import parse_workbook
    from openpyxl import load_workbook as _load_wb

    _FormattedConverter = _make_formatted_converter()

    # Collect mermaid blocks once before the sheet loop
    mermaid_blocks = collect_mermaid_blocks(xlsx_path)
    logger.info("found %d mermaid block(s) embedded in xlsx", len(mermaid_blocks))

    # Parse workbook and reorder chunks whole-workbook (cross-sheet context)
    result = parse_workbook(path=xlsx_path)
    chunks = result.chunks

    # Inject strikethrough first so format wrappers can nest around them
    n_strike = inject_strike_tags_in_chunks(chunks, xlsx_path)
    if n_strike:
        logger.info("injected strike tags into %d chunk(s)", n_strike)

    # Inject format tags before reorder/consolidation
    n_fmt = inject_format_tags_in_chunks(chunks)
    if n_fmt:
        logger.info("injected format tags into %d chunk(s)", n_fmt)

    # Reorder whole-workbook (needs cross-sheet page break context)
    chunks = reorder_chunks_by_page_columns(chunks, xlsx_path)

    # Group chunks by sheet name, preserving openpyxl sheet order
    try:
        wb = _load_wb(xlsx_path, read_only=True, data_only=True)
        sheet_order = list(wb.sheetnames)
        wb.close()
    except Exception:
        # Fallback: derive order from chunks themselves
        seen: list[str] = []
        for c in chunks:
            if c.sheet_name not in seen:
                seen.append(c.sheet_name)
        sheet_order = seen

    by_sheet: dict[str, list] = defaultdict(list)
    for c in chunks:
        by_sheet[c.sheet_name].append(c)

    results: List[dict] = []

    for sheet_name in sheet_order:
        sheet_chunks = by_sheet.get(sheet_name, [])
        if not sheet_chunks:
            continue

        # Per-sheet pipeline
        sheet_chunks = consolidate_form_layout(sheet_chunks)
        sheet_chunks = merge_consecutive_form_pairs(sheet_chunks)
        sheet_chunks = split_chunks_by_row_gap(sheet_chunks)
        sheet_chunks = merge_sibling_column_chunks(sheet_chunks)
        normalize_chunks(sheet_chunks)

        # Filter empty chunks
        sheet_chunks = [c for c in sheet_chunks if _has_text_content(c.render_html)]
        if not sheet_chunks:
            continue

        html_body = "\n".join(c.render_html for c in sheet_chunks)
        md = _FormattedConverter(heading_style="ATX").convert(html_body)

        # Restore mermaid fences (reuse pre-collected blocks)
        md, n_restored = restore_mermaid_blocks(md, mermaid_blocks)
        if n_restored:
            logger.debug("sheet %r: restored %d mermaid block(s)", sheet_name, n_restored)

        # Materialize §§HN§§ sentinels → ATX headers
        md, _ = _materialize_sentinels_safely(md)

        if md.strip():
            results.append({'sheet_name': sheet_name, 'markdown': md})
            logger.info("ksparser_to_sheets: sheet %r → %d chars", sheet_name, len(md))

    return results


def ksparser_to_md(xlsx_path: str) -> str:
    """Convert whole workbook to a single Markdown string (CLI/legacy use)."""
    _FormattedConverter = _make_formatted_converter()

    from ks_xlsx_parser import parse_workbook

    result = parse_workbook(path=xlsx_path)
    before = len(result.chunks)
    n_strike = inject_strike_tags_in_chunks(result.chunks, xlsx_path)
    logger.info("injected strike tags into %d chunk(s)", n_strike)
    n_fmt = inject_format_tags_in_chunks(result.chunks)
    logger.info("injected format tags into %d chunk(s)", n_fmt)
    chunks = reorder_chunks_by_page_columns(result.chunks, xlsx_path)
    chunks = consolidate_form_layout(chunks)
    chunks = merge_consecutive_form_pairs(chunks)
    logger.info("chunks: %d -> %d (%d merged)", before, len(chunks), before - len(chunks))
    chunks = split_chunks_by_row_gap(chunks)
    chunks = merge_sibling_column_chunks(chunks)
    normalized = normalize_chunks(chunks)
    logger.info("normalized %d chunk table(s)", normalized)
    chunks = [c for c in chunks if _has_text_content(c.render_html)]
    html_body = "\n".join(chunk.render_html for chunk in chunks)
    return _FormattedConverter(heading_style="ATX").convert(html_body)