#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把 16 份「作业群收集」xlsx 按日期顺序合并进一个 Excel，每个文件一个 sheet，保留超链接。"""
import glob, os
from copy import copy
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.hyperlink import Hyperlink

SRC_DIR = "/root/.openclaw/media/inbound"
OUT = "/home/mi/.openclaw/workspace/作业群收集_按日期合并.xlsx"

# 日期顺序（sheet 名, 文件名前缀）
ORDER = [
    ("4月14日",        "4_E6_9C_8814"),
    ("第一周第二节课", "E7_AC_AC"),
    ("4月21日",        "4_E6_9C_8821"),
    ("4月24日",        "4_24_"),
    ("4月28日",        "4_28_"),
    ("5月12日",        "5_12_"),
    ("5月19日",        "5_19_"),
    ("5月22日",        "5_22_"),
    ("5月26日",        "5_26_"),
    ("5月29日",        "5_29_"),
    ("6月2日",         "6_2_"),
    ("6月5日",         "6_5_"),
    ("6月9日",         "6_9_"),
    ("6月12日",        "6_12_"),
    ("6月23日",        "6_23_"),
    ("6月26日",        "6_26_"),
]

all_files = sorted(glob.glob(os.path.join(SRC_DIR, "*.xlsx")))

def match_file(prefix):
    hits = [f for f in all_files if os.path.basename(f).startswith(prefix)]
    if len(hits) != 1:
        raise RuntimeError(f"prefix {prefix!r} -> {len(hits)} matches: {hits}")
    return hits[0]

def copy_sheet(src_ws, dst_ws):
    # 值 + 样式 + 超链接
    for row in src_ws.iter_rows():
        for c in row:
            nc = dst_ws.cell(row=c.row, column=c.column)
            nc.value = c.value
            if c.has_style:
                nc.font = copy(c.font)
                nc.fill = copy(c.fill)
                nc.border = copy(c.border)
                nc.alignment = copy(c.alignment)
                nc.number_format = c.number_format
                nc.protection = copy(c.protection)
            if c.hyperlink is not None:
                h = c.hyperlink
                nc.hyperlink = Hyperlink(
                    ref=nc.coordinate,
                    target=h.target,
                    location=h.location,
                    tooltip=h.tooltip,
                    display=h.display,
                )
    # 合并单元格
    for rng in src_ws.merged_cells.ranges:
        dst_ws.merge_cells(str(rng))
    # 列宽
    for letter, dim in src_ws.column_dimensions.items():
        if dim.width is not None:
            dst_ws.column_dimensions[letter].width = dim.width
    # 行高
    for r, dim in src_ws.row_dimensions.items():
        if dim.height is not None:
            dst_ws.row_dimensions[r].height = dim.height
    # 冻结窗格
    if src_ws.freeze_panes:
        dst_ws.freeze_panes = src_ws.freeze_panes

out = openpyxl.Workbook()
out.remove(out.active)  # 去掉默认 sheet

for sheet_name, prefix in ORDER:
    src_path = match_file(prefix)
    src_wb = openpyxl.load_workbook(src_path)
    src_ws = src_wb.active
    dst_ws = out.create_sheet(title=sheet_name)
    copy_sheet(src_ws, dst_ws)
    print(f"OK  {sheet_name:12} <- {os.path.basename(src_path)}  (rows={src_ws.max_row}, cols={src_ws.max_column})")
    src_wb.close()

out.save(OUT)
print("saved:", OUT)
print("sheets:", out.sheetnames)