#!/usr/bin/env python3
"""
天正公司 2025 碳盤查清冊 — 公式修復 + 超連結 + 環境部格式排版
"""

import openpyxl
from openpyxl.styles import (
    Font, Alignment, Border, Side, PatternFill
)
from openpyxl.utils import get_column_letter

SRC = '/Users/lingochen/.openclaw/media/inbound/天正公司_2025碳盤查清冊_客戶填寫_視覺公式優化版---0e19ecfa-db15-4e33-82df-ab3e3b471dda.xlsx'
DST = '/Users/lingochen/Desktop/天正公司_2025碳盤查清冊_排版優化.xlsx'

# ══════════════════════════════════════════════════════════
# Styles — 環境部官方配色
# ══════════════════════════════════════════════════════════
C_TITLE_BG  = "006B3F"
C_TITLE_FG  = "FFFFFF"
C_HEADER_BG = "D9EAD3"
C_HEADER_FG = "006B3F"
C_ALT_BG    = "F5F9F5"
C_FORMULA_BG= "F2F2F2"
C_INPUT_BG  = "FFF2CC"
C_BLACK     = "000000"
C_LINK_BLUE = "0563C1"

font_title  = Font(name='Microsoft JhengHei', size=13, bold=True, color=C_TITLE_FG)
font_header = Font(name='Microsoft JhengHei', size=10.5, bold=True, color=C_HEADER_FG)
font_data   = Font(name='Microsoft JhengHei', size=10)
font_data_b = Font(name='Microsoft JhengHei', size=10, bold=True)
font_link   = Font(name='Microsoft JhengHei', size=10, color=C_LINK_BLUE, underline='single')

align_c  = Alignment(horizontal='center', vertical='center')
align_l  = Alignment(horizontal='left', vertical='center', wrap_text=True)
align_r  = Alignment(horizontal='right', vertical='center')
align_lc = Alignment(horizontal='left', vertical='center')

fill_title  = PatternFill('solid', fgColor=C_TITLE_BG)
fill_header = PatternFill('solid', fgColor=C_HEADER_BG)
fill_alt    = PatternFill('solid', fgColor=C_ALT_BG)
fill_formula= PatternFill('solid', fgColor=C_FORMULA_BG)
fill_input  = PatternFill('solid', fgColor=C_INPUT_BG)
fill_summary= PatternFill('solid', fgColor='E8F5E9')

thin_side = Side(style='thin', color=C_BLACK)
border_all = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
border_bottom = Border(left=thin_side, right=thin_side, top=thin_side,
                       bottom=Side(style='medium', color=C_BLACK))

NUM_INT = '#,##0'
NUM_2   = '#,##0.00'
NUM_4   = '#,##0.0000'
NUM_6   = '#,##0.000000'


def auto_width(ws):
    for col_idx in range(1, ws.max_column + 1):
        max_len = 0
        col_letter = get_column_letter(col_idx)
        for row_idx in range(1, ws.max_row + 1):
            is_start = True
            for mr in ws.merged_cells.ranges:
                if mr.min_col <= col_idx <= mr.max_col and \
                   mr.min_row <= row_idx <= mr.max_row and \
                   mr.min_col != col_idx:
                    is_start = False
                    break
            if not is_start:
                continue
            cell = ws.cell(row_idx, col_idx)
            if cell.value is None:
                continue
            val = str(cell.value)
            w = sum(2.15 if ord(ch) > 0x2E80 else 1.0 for ch in val)
            max_len = max(max_len, w)
        ws.column_dimensions[col_letter].width = max(min(max_len + 2.5, 45), 8)


def is_title_row(ws, row_idx):
    for mr in ws.merged_cells.ranges:
        if mr.min_row == row_idx and mr.max_row == row_idx:
            return True
    return False


def find_header_rows(ws):
    headers = []
    for row_idx in range(1, ws.max_row + 1):
        if is_title_row(ws, row_idx):
            continue
        bold_count = 0
        text_count = 0
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(row_idx, c)
            if cell.value is not None:
                text_count += 1
                if cell.font and cell.font.bold:
                    bold_count += 1
        if text_count > 0 and bold_count >= text_count * 0.5:
            headers.append(row_idx)
    return headers


def detect_num_fmt(v):
    if v is None or not isinstance(v, (int, float)):
        return None
    if isinstance(v, int) or (isinstance(v, float) and v == int(v) and abs(v) >= 10):
        return NUM_INT
    av = abs(v)
    if av == 0: return NUM_2
    if av < 0.001: return NUM_6
    if av < 1: return NUM_4
    return NUM_2


def format_sheet(ws, sheet_name):
    auto_width(ws)

    # Title rows
    for mr in ws.merged_cells.ranges:
        if mr.min_row == mr.max_row:
            for c in range(mr.min_col, mr.max_col + 1):
                cell = ws.cell(mr.min_row, c)
                cell.font = font_title
                cell.fill = fill_title
                cell.alignment = align_c
                cell.border = border_all
            ws.row_dimensions[mr.min_row].height = 30

    header_rows = find_header_rows(ws)

    for hr in header_rows:
        ws.row_dimensions[hr].height = 24
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(hr, c)
            if cell.value is not None:
                cell.font = font_header
                cell.fill = fill_header
                cell.alignment = align_c
                cell.border = border_bottom

    for row_idx in range(1, ws.max_row + 1):
        if row_idx in header_rows or is_title_row(ws, row_idx):
            continue
        ws.row_dimensions[row_idx].height = 20
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(row_idx, c)
            if cell.value is None:
                continue
            # Skip formula cells — don't overwrite their format
            if isinstance(cell.value, str) and cell.value.startswith('='):
                cell.font = font_data
                cell.border = border_all
                cell.alignment = align_r
                continue
            cell.font = font_data
            cell.border = border_all
            v = cell.value
            if isinstance(v, (int, float)):
                cell.alignment = align_r
                fmt = detect_num_fmt(v)
                if fmt:
                    cell.number_format = fmt
            elif isinstance(v, str):
                if v in ('Y', 'N', '是', '否'):
                    cell.alignment = align_c
                elif v == '✓':
                    cell.alignment = align_c
                    cell.font = Font(name='Microsoft JhengHei', size=10, color='006B3F', bold=True)
                elif v.startswith('2025.') or v.startswith('2026'):
                    cell.alignment = align_c
                elif len(v) > 30:
                    cell.alignment = align_l
                else:
                    cell.alignment = align_lc
            else:
                cell.alignment = align_l
            if row_idx % 2 == 0 and not cell.fill or cell.fill.fgColor.rgb == '00000000':
                cell.fill = fill_alt

    ws.freeze_panes = 'A4'


# ══════════════════════════════════════════════════════════
# Sheet 名稱對照表（待確認事項 C 欄 → 實際 sheet 名稱）
# ══════════════════════════════════════════════════════════
SHEET_MAP = {
    '組織邊界': '組織邊界',
    '1.1固定燃燒_柴油': '1.1固定燃燒_柴油',
    '1.1固定燃燒_汽油': '1.1固定燃燒_汽油',
    '1.2移動燃燒': '1.2移動燃燒',
    '1.4逸散排放': '1.4逸散排放',
    '2.1外購電力': '2.1外購電力',
    '其他間接_參考': '其他間接_參考',
    '全工作表': None,  # 不做連結
}


# ══════════════════════════════════════════════════════════
# Main
# ══════════════════════════════════════════════════════════
wb = openpyxl.load_workbook(SRC)

# ── Step 1: 修復待確認事項的公式 ──
ws_todo = wb['待確認事項']
print('修復公式: 待確認事項')

# Summary formulas (row 5)
formula_map = {
    'A5': '=COUNTA($D$15:$D$28)',
    'C5': '=COUNTIF($K$15:$K$28,"已完成")',
    'E5': '=COUNTIFS($K$15:$K$28,"<>已完成",$K$15:$K$28,"<>不適用")',
    'G5': '=IFERROR(COUNTIF($K$15:$K$28,"已完成")/COUNTA($D$15:$D$28),0)',
    'I5': '=COUNTIFS($H$15:$H$28,"高",$K$15:$K$28,"<>已完成")',
}

# Category formulas (rows 8-13)
categories = [
    (8, 'A. 邊界'),
    (9, 'B. 固定燃燒'),
    (10, 'C. 移動燃燒'),
    (11, 'D. 逸散排放'),
    (12, 'E. 外購電力'),
    (13, 'F. 其他間接'),
]

for row, cat in categories:
    formula_map[f'B{row}'] = f'=COUNTIF($A$15:$A$28,"{cat}")'
    formula_map[f'C{row}'] = f'=COUNTIFS($A$15:$A$28,"{cat}",$K$15:$K$28,"<>已完成",$K$15:$K$28,"<>不適用")'
    formula_map[f'D{row}'] = f'=COUNTIFS($A$15:$A$28,"{cat}",$H$15:$H$28,"高",$K$15:$K$28,"<>已完成")'
    formula_map[f'E{row}'] = f'=IFERROR((B{row}-C{row})/B{row},0)'
    formula_map[f'F{row}'] = f'=E{row}'

# Write formulas
for cell_ref, formula in formula_map.items():
    cell = ws_todo[cell_ref]
    cell.value = formula
    # Style: formula cells get gray background
    cell.font = font_data_b
    cell.fill = fill_formula
    cell.alignment = align_r
    cell.border = border_all
    cell.number_format = NUM_2 if 'G5' in cell_ref or 'E' in cell_ref or 'F' in cell_ref else NUM_INT

# ── Step 2: 加超連結（.detail rows → target sheet）──
print('新增超連結: 待確認事項 → 各 sheet')

# Target cells for each sheet (what to link to)
LINK_TARGETS = {
    '組織邊界': 'A3',        # 組織邊界表頭
    '1.1固定燃燒_柴油': 'A3',
    '1.1固定燃燒_汽油': 'A3',
    '1.2移動燃燒': 'A3',
    '1.4逸散排放': 'A3',
    '2.1外購電力': 'A3',
    '其他間接_參考': 'A3',
}

for row_idx in range(15, 29):
    c_cell = ws_todo.cell(row_idx, 3)  # C = 工作表
    sheet_name = c_cell.value
    if sheet_name and sheet_name in SHEET_MAP and SHEET_MAP[sheet_name]:
        target_sheet = SHEET_MAP[sheet_name]
        target_cell = LINK_TARGETS.get(sheet_name, 'A3')
        # Add hyperlink to cell C
        c_cell.hyperlink = f"#'{target_sheet}'!{target_cell}"
        c_cell.font = font_link
        # Also add hyperlink to cell D (待確認事項 description)
        d_cell = ws_todo.cell(row_idx, 4)
        d_cell.hyperlink = f"#'{target_sheet}'!{target_cell}"
        d_cell.font = font_link

# ── Step 3: Format all sheets ──
for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    if sheet_name == '待確認事項':
        continue  # Already handled above
    print(f'格式化: {sheet_name}')
    format_sheet(ws, sheet_name)

# ── Step 4: 待確認事項 special formatting ──
print('格式化: 待確認事項')
ws = wb['待確認事項']
auto_width(ws)

# Title rows (merged)
for mr in ws.merged_cells.ranges:
    if mr.min_row == mr.max_row:
        for c in range(mr.min_col, mr.max_col + 1):
            cell = ws.cell(mr.min_row, c)
            cell.font = font_title
            cell.fill = fill_title
            cell.alignment = align_c
            cell.border = border_all
        ws.row_dimensions[mr.min_row].height = 30

# Summary header (row 4)
ws.row_dimensions[4].height = 24
for c in range(1, ws.max_column + 1):
    cell = ws.cell(4, c)
    if cell.value is not None:
        cell.font = font_header
        cell.fill = fill_header
        cell.alignment = align_c
        cell.border = border_bottom

# Summary data (row 5)
ws.row_dimensions[5].height = 24
for c in range(1, ws.max_column + 1):
    cell = ws.cell(5, c)
    if cell.value is not None:
        cell.border = border_all
        if isinstance(cell.value, str) and cell.value.startswith('='):
            cell.font = font_data_b
            cell.fill = fill_formula
            cell.alignment = align_r

# Category header (row 7)
ws.row_dimensions[7].height = 24
for c in range(1, 7):
    cell = ws.cell(7, c)
    if cell.value is not None:
        cell.font = font_header
        cell.fill = fill_header
        cell.alignment = align_c
        cell.border = border_bottom

# Category data (rows 8-13)
for row_idx in range(8, 14):
    ws.row_dimensions[row_idx].height = 22
    for c in range(1, 7):
        cell = ws.cell(row_idx, c)
        if cell.value is not None:
            cell.border = border_all
            if isinstance(cell.value, str) and cell.value.startswith('='):
                cell.font = font_data_b
                cell.fill = fill_formula
                cell.alignment = align_r
            elif isinstance(cell.value, str):
                cell.font = font_data
                cell.alignment = align_lc
            if row_idx % 2 == 1:
                cell.fill = fill_alt

# Detail header (row 14)
ws.row_dimensions[14].height = 24
for c in range(1, ws.max_column + 1):
    cell = ws.cell(14, c)
    if cell.value is not None:
        cell.font = font_header
        cell.fill = fill_header
        cell.alignment = align_c
        cell.border = border_bottom

# Detail data (rows 15-28)
for row_idx in range(15, 29):
    ws.row_dimensions[row_idx].height = 28
    for c in range(1, ws.max_column + 1):
        cell = ws.cell(row_idx, c)
        if cell.value is not None:
            cell.border = border_all
            if isinstance(cell.value, str) and cell.value.startswith('#'):
                # Hyperlink cell — keep link style
                pass
            elif cell.hyperlink:
                cell.font = font_link
            else:
                cell.font = font_data
            # Alignment
            v = cell.value
            if c == 8:  # H = priority
                cell.alignment = align_c
                if v == '高':
                    cell.font = Font(name='Microsoft JhengHei', size=10, bold=True, color='C62828')
                elif v == '中':
                    cell.font = Font(name='Microsoft JhengHei', size=10, color='E65100')
            elif c == 11:  # K = status
                cell.alignment = align_c
                if v == '已完成':
                    cell.font = Font(name='Microsoft JhengHei', size=10, color='2E7D32')
                elif v == '待確認':
                    cell.font = Font(name='Microsoft JhengHei', size=10, color='F57F17')
                elif v == '待補':
                    cell.font = Font(name='Microsoft JhengHei', size=10, color='D84315')
            elif isinstance(v, str) and len(v) > 20:
                cell.alignment = align_l
            elif isinstance(v, (int, float)):
                cell.alignment = align_r
            else:
                cell.alignment = align_lc

ws.freeze_panes = 'A15'

# ── Step 5: 排放量總表 — 小計/總計加粗 ──
ws = wb['排放量總表']
for row_idx in range(1, ws.max_row + 1):
    a_val = ws.cell(row_idx, 1).value
    if a_val and ('小計' in str(a_val) or '總計' in str(a_val) or '主報告' in str(a_val)):
        ws.row_dimensions[row_idx].height = 24
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(row_idx, c)
            if cell.value is not None:
                cell.font = font_data_b
                cell.fill = fill_summary
                cell.border = border_bottom

# ── Step 6: 版本修正紀錄 — 加高列高 ──
ws = wb['版本修正紀錄']
for row_idx in range(4, ws.max_row + 1):
    ws.row_dimensions[row_idx].height = 40
    for c in range(1, ws.max_column + 1):
        cell = ws.cell(row_idx, c)
        if cell.value is not None:
            cell.font = font_data
            cell.border = border_all
            if isinstance(cell.value, str) and len(cell.value) > 15:
                cell.alignment = align_l

# ── Step 7: 使用說明 — 保持可讀性 ──
ws = wb['使用說明']
for row_idx in range(1, ws.max_row + 1):
    for c in range(1, ws.max_column + 1):
        cell = ws.cell(row_idx, c)
        if cell.value is not None and isinstance(cell.value, str) and len(cell.value) > 20:
            ws.row_dimensions[row_idx].height = 36
            cell.alignment = align_l

wb.save(DST)
print(f'\n✅ Saved: {DST}')