"""Shared parsing for CBS 'Local Authorities in Israel' (p_libud) workbooks.

Headers span several merged rows above the row labelled 'שם הרשות'; each column gets the list of
its header parts (outer group first, leaf last). Data rows are those whose authority-code column
holds an integer (summary rows such as 'כלל ארצי' have no code and are skipped).
"""
import openpyxl


def is_code(v):
    return isinstance(v, int) or (isinstance(v, float) and v.is_integer()) or (isinstance(v, str) and v.strip().isdigit())


def norm(s):
    return " ".join(str(s).replace("''", '"').split())


def sheet_table(ws, code_col=2):
    merged = {}
    for rng in ws.merged_cells.ranges:
        v = ws.cell(rng.min_row, rng.min_col).value
        for r in range(rng.min_row, rng.max_row + 1):
            for c in range(rng.min_col, rng.max_col + 1):
                merged[(r, c)] = v
    rows = list(ws.iter_rows(values_only=True))
    label = next(i for i, r in enumerate(rows) if r[0] and norm(r[0]).startswith("שם"))  # 0-based
    last_head = label + 1 if label + 1 < len(rows) and rows[label + 1][0] is None else label
    heads = []
    for c in range(1, ws.max_column + 1):
        parts = []
        for r in range(2, last_head + 2):  # 1-based rows 2..last_head+1
            v = ws.cell(r, c).value
            v = v if v is not None else merged.get((r, c))
            if v is not None:
                s = norm(v)
                if s and s not in parts:
                    parts.append(s)
        heads.append(parts)
    data = [r for r in rows[last_head + 1:] if is_code(r[code_col - 1])]
    return heads, data


def find_sheet(wb, *needles):
    for ws in wb.worksheets:
        if all(n in ws.title for n in needles):
            return ws
    raise KeyError(needles)


def load(path):
    return openpyxl.load_workbook(path, data_only=True)