#!/usr/bin/env python3
"""
天正公司 2025 碳盤查清冊 — 參考環境部官方格式重新排版
環境部格式特色：
  1. 深綠標題列（#006B3F）白字粗體
  2. 淺綠表頭列（#D9EAD3）深綠字
  3. 白底=使用者填寫、灰底=自動公式、橘底=下拉選項
  4. 細黑邊框（#000000 1px）
  5. 數值右對齊、千分位、保留小數
  6. 文字左對齊、自動換行
  7. 欄寬適配內容
  8. 凍結窗格（表頭固定）
"""

import openpyxl
from openpyxl.styles import (
    Font, Alignment, Border, Side, PatternFill, numbers
)
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'

# ══════════════════════════════════════════════════════════
# Style constants — 參考環境部「溫室氣體排放量清冊表單（範例）」
# ══════════════════════════════════════════════════════════

# Colors
C_TITLE_BG   = "006B3F"   # 深綠 — 標題列背景
C_TITLE_FG   = "FFFFFF"   # 白色 — 標題列文字
C_HEADER_BG  = "D9EAD3"   # 淺綠 — 表頭列背景
C_HEADER_FG  = "006B3F"   # 深綠 — 表頭列文字
C_DATA_BG    = "FFFFFF"   # 白色 — 資料列（使用者填寫）
C_FORMULA_BG = "F2F2F2"   # 灰色 — 公式自動計算欄位
C_INPUT_BG   = "FFF2CC"   # 淺橘 — 需使用者填寫的欄位
C_ALT_BG     = "F5F9F5"   # 極淡綠 — 交替列
C_WHITE      = "FFFFFF"
C_BLACK      = "000000"

# Fonts
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_note    = Font(name='Microsoft JhengHei', size=9, italic=True, color='666666')

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

# Fills
fill_title  = PatternFill('solid', fgColor=C_TITLE_BG)
fill_header = PatternFill('solid', fgColor=C_HEADER_BG)
fill_data   = PatternFill('solid', fgColor=C_DATA_BG)
fill_formula= PatternFill('solid', fgColor=C_FORMULA_BG)
fill_input  = PatternFill('solid', fgColor=C_INPUT_BG)
fill_alt    = PatternFill('solid', fgColor=C_ALT_BG)

# Border — 細黑邊框，參考環境部格式
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_thick = Border(
    left=thin_side, right=thin_side, top=thin_side,
    bottom=Side(style='medium', color=C_BLACK)
)

# Number formats
NUM_INT   = '#,##0'
NUM_2     = '#,##0.00'
NUM_4     = '#,##0.0000'
NUM_6     = '#,##0.000000'
PCT       = '0.00%'


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):
            # Skip merged cells that aren't the start
            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)
            width = 0
            for ch in val:
                if ord(ch) > 0x2E80:  # CJK
                    width += 2.15
                else:
                    width += 1.0
            max_len = max(max_len, width)

        # Clamp
        w = max(min(max_len + 2.5, 45), 8)
        ws.column_dimensions[col_letter].width = w


def is_title_row(ws, row_idx):
    """Check if row is a title (merged, single row)"""
    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
        # Header: majority of cells are bold
        if text_count > 0 and bold_count >= text_count * 0.5:
            headers.append(row_idx)
    return headers


def is_numeric(v):
    return isinstance(v, (int, float))


def detect_num_fmt(v):
    if v is None or not is_numeric(v):
        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):
    """套用環境部風格格式"""

    # ── Step 1: Auto column width ──
    auto_width(ws)

    # ── Step 2: Title rows (merged, deep green) ──
    for mr in ws.merged_cells.ranges:
        if mr.min_row == mr.max_row:
            for r in range(mr.min_row, mr.max_row + 1):
                for c in range(mr.min_col, mr.max_col + 1):
                    cell = ws.cell(r, 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

    # ── Step 3: Find header rows ──
    header_rows = find_header_rows(ws)

    # Apply header formatting
    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_thick

    # ── Step 4: Data rows ──
    for row_idx in range(1, ws.max_row + 1):
        if row_idx in header_rows:
            continue
        if 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

            cell.font = font_data
            cell.border = border_all

            v = cell.value

            if is_numeric(v):
                cell.alignment = align_r
                fmt = detect_num_fmt(v)
                if fmt:
                    cell.number_format = fmt
                # Alternate row fill
                if row_idx % 2 == 0:
                    cell.fill = fill_alt
            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
                    cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
                else:
                    cell.alignment = align_lc
                # Alternate row fill for text
                if row_idx % 2 == 0:
                    cell.fill = fill_alt
            else:
                cell.alignment = align_l
                if row_idx % 2 == 0:
                    cell.fill = fill_alt

    # ── Step 5: Freeze panes ──
    ws.freeze_panes = 'A4'


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

for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    print(f'Formatting: {sheet_name}')
    format_sheet(ws, sheet_name)

# ── 特殊處理：待確認事項 ──
ws = wb['待確認事項']
# Detail header at 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_thick

# Detail data rows (15+)
for row_idx in range(15, ws.max_row + 1):
    ws.row_dimensions[row_idx].height = 30
    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) > 20:
                cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
ws.freeze_panes = 'A15'

# ── 特殊處理：版本修正紀錄 ──
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 = Alignment(horizontal='left', vertical='center', wrap_text=True)

# ── 特殊處理：排放量總表 — 小計/總計列加粗 ──
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 = PatternFill('solid', fgColor='E8F5E9')  # 淡綠底
                cell.border = border_bottom_thick

# ── 特殊處理：設定_係數與來源 — 兩段式表頭 ──
ws = wb['設定_係數與來源']
# Row 9 is the second header row for 係數 table
for c in range(1, ws.max_column + 1):
    cell = ws.cell(9, c)
    if cell.value is not None:
        cell.font = font_header
        cell.fill = fill_header
        cell.alignment = align_c
        cell.border = border_bottom_thick

# ── 特殊處理：使用說明 — 保持可讀性 ──
ws = wb['使用說明']
for row_idx in range(1, ws.max_row + 1):
    ws.row_dimensions[row_idx].height = 22
    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):
            if len(cell.value) > 20:
                cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
                ws.row_dimensions[row_idx].height = 36

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