import gradio as gr
import openpyxl
import os
from openpyxl.utils import get_column_letter
from copy import copy

TEMPLATE_FILE = "template.xlsx"

def process_direct_merge(file_objs, template_height):
    if not file_objs:
        return None, "請先上傳檔案！"
    
    try:
        template_height = int(template_height)
    except:
        return None, "模板高度必須是數字！"

    files = file_objs if isinstance(file_objs, list) else [file_objs]
    
    # 檔案排序邏輯 (OF- > DF- > SF- > PF-)
    prefix_priority = {"OF-": 1, "DF-": 2, "SF-": 3, "PF-": 4}
    def sort_key(f):
        name = os.path.basename(f.name).upper()
        for prefix, weight in prefix_priority.items():
            if name.startswith(prefix): return (weight, name)
        return (99, name)
    files.sort(key=sort_key)

    try:
        temp_wb = openpyxl.load_workbook(TEMPLATE_FILE)
        temp_ws = temp_wb.worksheets[0]
        out_wb = openpyxl.Workbook()
        out_ws = out_wb.active
        out_ws.title = "合併報表"

        # --- 頁面設置：水平方向與邊界調整 ---
        # 設定為水平 (Landscape)
        out_ws.page_setup.orientation = out_ws.ORIENTATION_LANDSCAPE 
        
        # 設定邊界 (單位英吋): 2cm ≈ 0.787, 1cm ≈ 0.394
        out_ws.page_margins.top = 0.787     # 上 2cm
        out_ws.page_margins.bottom = 0.787  # 下 2cm
        out_ws.page_margins.left = 0.394    # 左 1cm
        out_ws.page_margins.right = 0.394   # 右 1cm
        
        # 強制每一頁寬度符合一頁 (避免欄位溢出)
        out_ws.page_setup.fitToWidth = 1
        out_ws.page_setup.fitToHeight = 0 

        # 1. 預先複製欄寬
        for i in range(1, 31):
            col_l = get_column_letter(i)
            if col_l in temp_ws.column_dimensions:
                out_ws.column_dimensions[col_l].width = temp_ws.column_dimensions[col_l].width

        current_base_row = 1

        for file_obj in files:
            wb = openpyxl.load_workbook(file_obj.name, data_only=True)
            file_funds_data = []

            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                for row in ws.iter_rows():
                    first_val = str(row[0].value) if row[0].value else ""
                    if "彙總表" in first_val or "近10年度" in first_val:
                        fund_name = first_val.replace("近10年度", "").replace("收支概況彙總表", "").strip()
                        records = []
                        start_row_idx = row[0].row + 4
                        for r_idx in range(start_row_idx, start_row_idx + 10):
                            # 跳過原始檔 B 欄，讀取 A + C~U 欄
                            year_val = ws.cell(row=r_idx, column=1).value
                            data_row = [ws.cell(row=r_idx, column=c).value for c in range(3, 22)]
                            records.append([year_val] + data_row)
                        file_funds_data.append({"name": fund_name, "data": records})

            # 寫入資料與樣式
            for fund in file_funds_data:
                for r in range(1, template_height + 1):
                    target_row = current_base_row + r - 1
                    # 同步範本列高
                    if r in temp_ws.row_dimensions:
                        out_ws.row_dimensions[target_row].height = temp_ws.row_dimensions[r].height

                    for c in range(1, 26):
                        s_cell = temp_ws.cell(row=r, column=c)
                        t_cell = out_ws.cell(row=target_row, column=c)
                        
                        # 非數據區複製模板文字
                        if not (5 <= r <= 14): t_cell.value = s_cell.value
                        
                        if s_cell.has_style:
                            t_cell.font = copy(s_cell.font)
                            t_cell.border = copy(s_cell.border)
                            t_cell.fill = copy(s_cell.fill)
                            t_cell.alignment = copy(s_cell.alignment)
                            t_cell.number_format = copy(s_cell.number_format)

                # 套用合併格
                if temp_ws.merged_cells:
                    for m_range in temp_ws.merged_cells.ranges:
                        if m_range.min_row <= template_height:
                            min_col, min_row, max_col, max_row = m_range.bounds
                            out_ws.merge_cells(
                                start_row=current_base_row + min_row - 1,
                                start_column=min_col,
                                end_row=current_base_row + max_row - 1,
                                end_column=max_col
                            )

                # 填入內容 (標題與 10 年數據)
                out_ws.cell(row=current_base_row, column=1).value = f"{fund['name']}近10年度收支概況彙總表"
                for r_idx, row_vals in enumerate(fund['data']):
                    for c_idx, val in enumerate(row_vals):
                        out_ws.cell(row=current_base_row + 4 + r_idx, column=c_idx + 1).value = val
                
                current_base_row += template_height

            # 補空白列時同步範本列高
            if len(file_funds_data) % 2 != 0:
                for r in range(1, template_height + 1):
                    target_row = current_base_row + r - 1
                    if r in temp_ws.row_dimensions:
                        out_ws.row_dimensions[target_row].height = temp_ws.row_dimensions[r].height
                current_base_row += template_height

        output_path = os.path.abspath("Final_Merged_Report.xlsx")
        out_wb.save(output_path)
        return output_path, f"合併成功！範本高度 {template_height} 列，水平列印且邊界已設定。"

    except Exception as e:
        return None, f"發生錯誤: {str(e)}"

# Gradio UI
with gr.Blocks() as app:
    gr.Markdown("# 基金報表合併系統 (18 列範本版)")
    with gr.Row():
        f_in = gr.File(label="上傳 Excel", file_count="multiple")
        h_in = gr.Number(label="設定模板高度 (已設為新範本 18)", value=18)
    btn = gr.Button("🚀 開始一鍵生成", variant="primary")
    f_out = gr.File(label="下載結果")
    status = gr.Textbox(label="狀態")
    btn.click(process_direct_merge, [f_in, h_in], [f_out, status])

if __name__ == "__main__":
    app.launch(server_name="0.0.0.0", server_port=7860)