"""Two-sided contract checker for CREATING an .xlsx with a real merged header cell."""

REQUIREMENTS = [
    {"id": "R1", "text": "the file loads as a valid .xlsx workbook"},
    {"id": "R2", "text": "the given range is a real merged range, not just repeated text"},
    {"id": "R3", "text": "the merge's top-left cell holds exactly the given header value"},
    {"id": "R4", "text": "every given unmerged cell holds its exact given value"},
    {"id": "R5", "text": "every cell not named by the merge or a single is empty"},
]
ARTIFACT_SUFFIX = ".xlsx"


def _cells(spec):
    return {tuple(int(x) for x in k.split(",")): v for k, v in spec.get("singles", {}).items()}


def build_reference(spec: dict, out_path: str) -> None:
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    (r0, c0), (r1, c1) = spec["merge"]["cells"]
    ws.cell(row=r0 + 1, column=c0 + 1).value = spec["merge"]["value"]
    ws.merge_cells(start_row=r0 + 1, start_column=c0 + 1, end_row=r1 + 1, end_column=c1 + 1)
    for (r, c), v in _cells(spec).items():
        ws.cell(row=r + 1, column=c + 1).value = v
    wb.save(out_path)


def check(spec: dict, path: str) -> list[tuple[str, bool, str]]:
    ids = [r["id"] for r in REQUIREMENTS]
    try:
        from openpyxl import load_workbook
        wb = load_workbook(path)
        ws = wb.active
    except Exception as e:
        return [(i, False, f"could not open as a workbook: {type(e).__name__}: {e}") for i in ids]

    (r0, c0), (r1, c1) = spec["merge"]["cells"]
    merged_ok = any(rng.min_row == r0 + 1 and rng.max_row == r1 + 1 and
                   rng.min_col == c0 + 1 and rng.max_col == c1 + 1
                   for rng in ws.merged_cells.ranges)

    top_ok = False
    top_val = None
    try:
        top_val = ws.cell(row=r0 + 1, column=c0 + 1).value
        top_ok = top_val == spec["merge"]["value"]
    except Exception:
        pass

    singles = _cells(spec)
    single_ok, bad_single = True, []
    for (r, c), want in singles.items():
        got = ws.cell(row=r + 1, column=c + 1).value
        if got != want:
            single_ok = False
            bad_single.append((r, c, got))

    merge_cells_set = {(r, c) for r in range(r0, r1 + 1) for c in range(c0, c1 + 1)}
    max_r = max([r1] + [r for r, _ in singles] + [ws.max_row - 1])
    max_c = max([c1] + [c for _, c in singles] + [ws.max_column - 1])
    empty_ok, bad_empty = True, []
    for r in range(max_r + 1):
        for c in range(max_c + 1):
            if (r, c) in merge_cells_set or (r, c) in singles:
                continue
            got = ws.cell(row=r + 1, column=c + 1).value
            if got is not None:
                empty_ok = False
                bad_empty.append((r, c, got))

    return [
        ("R1", True, "opens fine"),
        ("R2", merged_ok, "a real merge covers exactly the given range" if merged_ok
         else f"no merged range matches {spec['merge']['cells']}"),
        ("R3", top_ok, "top-left holds the header" if top_ok
         else f"top-left was {top_val!r}, wanted {spec['merge']['value']!r}"),
        ("R4", single_ok, "every single correct" if single_ok else f"wrong at {bad_single[:4]}"),
        ("R5", empty_ok, "untouched cells are empty" if empty_ok
         else f"non-empty at {bad_empty[:4]}"),
    ]


def _variant_merge_then_write(spec: dict, out_path: str) -> None:
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    (r0, c0), (r1, c1) = spec["merge"]["cells"]
    ws.merge_cells(start_row=r0 + 1, start_column=c0 + 1, end_row=r1 + 1, end_column=c1 + 1)
    ws.cell(row=r0 + 1, column=c0 + 1).value = spec["merge"]["value"]
    for (r, c), v in _cells(spec).items():
        ws.cell(row=r + 1, column=c + 1).value = v
    wb.save(out_path)


def _variant_a1_addressing(spec: dict, out_path: str) -> None:
    from openpyxl import Workbook
    from openpyxl.utils import get_column_letter
    wb = Workbook()
    ws = wb.active
    (r0, c0), (r1, c1) = spec["merge"]["cells"]
    top = f"{get_column_letter(c0 + 1)}{r0 + 1}"
    bot = f"{get_column_letter(c1 + 1)}{r1 + 1}"
    ws[top] = spec["merge"]["value"]
    ws.merge_cells(f"{top}:{bot}")
    for (r, c), v in _cells(spec).items():
        addr = f"{get_column_letter(c + 1)}{r + 1}"
        ws[addr] = v
    wb.save(out_path)


VARIANTS = [("merge_then_write", _variant_merge_then_write),
           ("a1_addressing", _variant_a1_addressing)]


def _sp_no_real_merge(path: str) -> str:
    p = path[:-5] + "_s1.xlsx"
    from openpyxl import Workbook, load_workbook
    ref = load_workbook(path)
    ws_ref = ref.active
    r0 = c0 = r1 = c1 = None
    for rng in ws_ref.merged_cells.ranges:
        r0, c0, r1, c1 = rng.min_row - 1, rng.min_col - 1, rng.max_row - 1, rng.max_col - 1
        break
    if r0 is None:
        return path
    header_val = ws_ref.cell(row=r0 + 1, column=c0 + 1).value
    wb = Workbook()
    ws = wb.active
    for r in range(r0, r1 + 1):
        for c in range(c0, c1 + 1):
            ws.cell(row=r + 1, column=c + 1).value = header_val
    for row in ws_ref.iter_rows():
        for cell in row:
            if cell.row - 1 == r0 and c0 <= cell.column - 1 <= c1:
                continue
            if cell.value is not None:
                ws.cell(row=cell.row, column=cell.column).value = cell.value
    wb.save(p)
    return p


def _sp_wrong_top(path: str) -> str:
    from openpyxl import load_workbook
    p = path[:-5] + "_s2.xlsx"
    wb = load_workbook(path)
    ws = wb.active
    for rng in ws.merged_cells.ranges:
        ws.merged_cells.remove(rng)
        ws.cell(row=rng.min_row, column=rng.min_col).value = "WRONG_HEADER"
        ws.merge_cells(start_row=rng.min_row, start_column=rng.min_col,
                       end_row=rng.max_row, end_column=rng.max_col)
        break
    wb.save(p)
    return p


def _sp_wrong_single(path: str) -> str:
    from openpyxl import load_workbook
    p = path[:-5] + "_s3.xlsx"
    wb = load_workbook(path)
    ws = wb.active
    merged_addrs = {(cell.row, cell.column) for rng in ws.merged_cells.ranges
                    for row in ws.iter_rows(min_row=rng.min_row, max_row=rng.max_row,
                                            min_col=rng.min_col, max_col=rng.max_col)
                    for cell in row}
    for row in ws.iter_rows():
        for cell in row:
            if cell.value is not None and (cell.row, cell.column) not in merged_addrs:
                cell.value = str(cell.value) + "_WRONG"
                wb.save(p)
                return p
    wb.save(p)
    return p


def _sp_truncated(path: str) -> str:
    p = path[:-5] + "_s0.xlsx"
    raw = open(path, "rb").read()
    open(p, "wb").write(raw[: len(raw) // 2])
    return p


def _sp_stray_value(path: str) -> str:
    from openpyxl import load_workbook
    p = path[:-5] + "_s4.xlsx"
    wb = load_workbook(path)
    ws = wb.active
    ws.cell(row=ws.max_row + 2, column=ws.max_column + 2).value = "STRAY"
    wb.save(p)
    return p


SPOILERS = [
    ("R1", "truncated_file", _sp_truncated),
    ("R2", "no_real_merge", _sp_no_real_merge),
    ("R3", "wrong_top_left", _sp_wrong_top),
    ("R4", "wrong_single", _sp_wrong_single),
    ("R5", "stray_value", _sp_stray_value),
]