from copy import copy
from datetime import datetime
from pathlib import Path
import shutil

from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell
from openpyxl.formula.translate import Translator
from openpyxl.utils import get_column_letter


INPUT = Path("/Users/jack/Downloads/智慧营养健康餐厅-功能等材料介绍_V3_带设备图片候选.xlsx")
WORK_COPY = Path(
    "/Users/jack/code/010-cpt/008-zhct/zhctprompt/work/2026-06-06-smart-canteen-front-back-hardware-strategy/智慧营养健康餐厅-功能等材料介绍_V3_带设备图片候选.xlsx"
)


SPECS = [
    # sheet, image column, insert image column here, label column meaning
    ("2-前厅产品线及功能模块", 12, 3, "模块旁"),
    ("3-后厨产品线及功能模块-政企版", 9, 3, "模块旁"),
    ("4-后厨产品线及功能模块-学校版", 10, 3, "模块旁"),
    ("进销存系统招标参数", 7, 4, "子系统旁"),
    ("食安系统招标参数", 7, 4, "子系统旁"),
]


def clone_cell(src, dst):
    if isinstance(src, MergedCell):
        return
    dst.value = src.value
    if src.has_style:
        dst._style = copy(src._style)
    if src.comment:
        dst.comment = copy(src.comment)
    if src.hyperlink:
        dst._hyperlink = copy(src.hyperlink)
    dst.number_format = src.number_format
    dst.protection = copy(src.protection)
    dst.alignment = copy(src.alignment)


def collect_image_positions(ws):
    positions = []
    for img in list(getattr(ws, "_images", [])):
        anchor = img.anchor
        if hasattr(anchor, "_from"):
            positions.append((img, anchor._from.row + 1, anchor._from.col + 1))
        else:
            positions.append((img, None, None))
    return positions


def resize_thumbnail(img, max_width=165, max_height=105):
    if not getattr(img, "width", None) or not getattr(img, "height", None):
        return
    ratio = min(max_width / img.width, max_height / img.height, 1)
    img.width = int(img.width * ratio)
    img.height = int(img.height * ratio)


def map_col(old_col, image_col, insert_col):
    if old_col == image_col:
        return insert_col
    if insert_col <= old_col < image_col:
        return old_col + 1
    return old_col


def move_image_column(ws, image_col, insert_col):
    if image_col <= insert_col:
        raise ValueError(f"{ws.title}: image_col must be right of insert_col")

    original_widths = {
        c: ws.column_dimensions[get_column_letter(c)].width
        for c in range(1, ws.max_column + 1)
    }
    original_merges = list(ws.merged_cells.ranges)
    image_cells = {}
    formulas = []

    for row in range(1, ws.max_row + 1):
        src = ws.cell(row, image_col)
        image_cells[row] = src
        for cell in ws[row]:
            if isinstance(cell.value, str) and cell.value.startswith("="):
                formulas.append((cell.row, cell.column, cell.coordinate, cell.value))

    image_positions = collect_image_positions(ws)

    for merged in original_merges:
        ws.unmerge_cells(str(merged))

    ws.insert_cols(insert_col)

    for row, src in image_cells.items():
        clone_cell(src, ws.cell(row, insert_col))

    shifted_old_image_col = image_col + 1
    ws.delete_cols(shifted_old_image_col)

    # Rebuild column widths after the cut-and-insert operation.
    for old_col, width in original_widths.items():
        new_col = map_col(old_col, image_col, insert_col)
        ws.column_dimensions[get_column_letter(new_col)].width = width
    ws.column_dimensions[get_column_letter(insert_col)].width = 24

    # Formula cells move with the columns. Re-translate formulas for moved cells.
    for row, old_col, old_coord, formula in formulas:
        if old_col == image_col:
            continue
        new_col = map_col(old_col, image_col, insert_col)
        new_coord = f"{get_column_letter(new_col)}{row}"
        if new_coord != old_coord:
            try:
                formula = Translator(formula, origin=old_coord).translate_formula(new_coord)
            except Exception:
                pass
        ws[new_coord].value = formula

    for merged in original_merges:
        min_col = map_col(merged.min_col, image_col, insert_col)
        max_col = map_col(merged.max_col, image_col, insert_col)
        if min_col > max_col:
            min_col, max_col = max_col, min_col
        ws.merge_cells(
            start_row=merged.min_row,
            start_column=min_col,
            end_row=merged.max_row,
            end_column=max_col,
        )

    for img, row, col in image_positions:
        if row is None or col is None:
            continue
        new_col = map_col(col, image_col, insert_col)
        img.anchor = f"{get_column_letter(new_col)}{row}"
        if col == image_col:
            resize_thumbnail(img)
            ws.row_dimensions[row].height = max(ws.row_dimensions[row].height or 15, 92)

    header = ws.cell(1, insert_col).value or ws.cell(2, insert_col).value or ws.cell(3, insert_col).value
    if header:
        for r in range(1, min(ws.max_row, 3) + 1):
            if ws.cell(r, insert_col).value and "设备图片" in str(ws.cell(r, insert_col).value):
                ws.cell(r, insert_col).value = "设备图片/候选图片\n（贴近设备名称）"


def main():
    if not INPUT.exists():
        raise FileNotFoundError(INPUT)

    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup = INPUT.with_name(f"{INPUT.stem}_布局调整前备份_{timestamp}{INPUT.suffix}")
    shutil.copy2(INPUT, backup)

    wb = load_workbook(INPUT)
    for sheet_name, image_col, insert_col, _note in SPECS:
        move_image_column(wb[sheet_name], image_col, insert_col)

    wb.save(INPUT)
    WORK_COPY.parent.mkdir(parents=True, exist_ok=True)
    shutil.copy2(INPUT, WORK_COPY)
    print(f"updated={INPUT}")
    print(f"backup={backup}")
    print(f"work_copy={WORK_COPY}")


if __name__ == "__main__":
    main()