#!/usr/bin/env python3
"""
天正公司 2025 碳盤查清冊 — 排版優化腳本
對每個 sheet 做：
  1. 欄寬自動調整（依據內容最大長度）
  2. 列高統一
  3. 標題列格式（粗體、深藍底白字、置中）
  4. 表頭列格式（灰底、粗體、置中）
  5. 資料列格式（垂直置中、適當對齊）
  6. 數值欄右對齊、千分位逗號
  7. 全表格加細邊框
  8. 凍結標題列
"""

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

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

# ── Style constants ──────────────────────────────────────
BLUE_DARK  = "1F4E79"
BLUE_MED   = "2E75B6"
GRAY_LIGHT = "F2F2F2"
WHITE      = "FFFFFF"
BORDER_THIN = Border(
    left=Side(style='thin', color='D9D9D9'),
    right=Side(style='thin', color='D9D9D9'),
    top=Side(style='thin', color='D9D9D9'),
    bottom=Side(style='thin', color='D9D9D9'),
)
BORDER_BOTTOM = Border(
    bottom=Side(style='medium', color='2E75B6'),
)

title_font   = Font(name='Microsoft JhengHei', size=14, bold=True, color=WHITE)
title_fill   = PatternFill('solid', fgColor=BLUE_DARK)
title_align  = Alignment(horizontal='center', vertical='center', wrap_text=True)

header_font  = Font(name='Microsoft JhengHei', size=11, bold=True, color=BLUE_DARK)
header_fill  = PatternFill('solid', fgColor=GRAY_LIGHT)
header_align = Alignment(horizontal='center', vertical='center', wrap_text=True)

data_font    = Font(name='Microsoft JhengHei', size=10.5)
data_align_c = Alignment(horizontal='center', vertical='center')
data_align_l = Alignment(horizontal='left', vertical='center', wrap_text=True)
data_align_r = Alignment(horizontal='right', vertical='center')

# Numeric format patterns
NUM_0 = '#,##0'
NUM_2 = '#,##0.00'
NUM_4 = '#,##0.0000'
NUM_6 = '#,##0.000000'
PCT   = '0.0%'

def is_header_row(ws, row_num):
    """Check if row is header (row 3 in most sheets, or row 7-8 in 待確認事項)"""
    for c in range(1, ws.max_column + 1):
        cell = ws.cell(row_num, c)
        if cell.font and cell.font.bold:
            return True
    return False

def is_title_row(ws, row_num):
    """Check if row is a title row (merged, large font)"""
    for mr in ws.merged_cells.ranges:
        if mr.min_row == row_num and mr.max_row == row_num:
            return True
    return False

def detect_num_format(cell):
    """Detect appropriate number format based on cell value"""
    v = cell.value
    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) > 100):
        return NUM_0
    if abs(v) < 0.001:
        return NUM_6
    if abs(v) < 1:
        return NUM_4
    if abs(v) < 100:
        return NUM_2
    return NUM_2

def auto_col_width(ws):
    """Set column widths based on content"""
    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):
            cell = ws.cell(row_idx, col_idx)
            if cell.value is not None:
                val_str = str(cell.value)
                # Estimate width: CJK chars count as ~2.2, others as 1
                width = 0
                for ch in val_str:
                    if ord(ch) > 0x4E00:
                        width += 2.2
                    else:
                        width += 1
                # Account for merged cells - use only first cell
                is_merged_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:
                        if mr.min_col != col_idx:
                            is_merged_start = False
                if is_merged_start:
                    max_len = max(max_len, width)
        # Add padding
        adjusted = min(max_len + 3, 40)
        adjusted = max(adjusted, 8)
        ws.column_dimensions[col_letter].width = adjusted

def format_sheet(ws, sheet_name):
    """Apply consistent formatting to a sheet"""
    
    # ── Auto column width ──
    auto_col_width(ws)
    
    # ── Title rows: deep blue bg, white bold font ──
    for mr in ws.merged_cells.ranges:
        if mr.min_row == mr.max_row:  # Single-row merge = likely title
            cell = ws.cell(mr.min_row, mr.min_col)
            cell.font = title_font
            cell.fill = title_fill
            cell.alignment = title_align
            ws.row_dimensions[mr.min_row].height = 32
            # Fill all cells in the merge with the same style
            for r in range(mr.min_row, mr.max_row + 1):
                for c in range(mr.min_col, mr.max_col + 1):
                    ws.cell(r, c).fill = title_fill
    
    # ── Find header row(s) ──
    header_rows = set()
    for row_idx in range(1, ws.max_row + 1):
        # Skip title rows
        if is_title_row(ws, row_idx):
            continue
        # Check if this row has header-like content
        has_bold = False
        has_text = False
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(row_idx, c)
            if cell.value is not None:
                has_text = True
                if cell.font and cell.font.bold:
                    has_bold = True
        if has_bold and has_text:
            # Check if next row has data (not another header)
            header_rows.add(row_idx)
    
    # ── Apply header formatting ──
    for hr in header_rows:
        ws.row_dimensions[hr].height = 26
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(hr, c)
            if cell.value is not None:
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = header_align
                cell.border = BORDER_THIN
    
    # ── 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 = 22
        
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(row_idx, c)
            if cell.value is None:
                continue
            
            cell.font = data_font
            cell.border = BORDER_THIN
            
            # Determine alignment
            if isinstance(cell.value, (int, float)):
                cell.alignment = data_align_r
                fmt = detect_num_format(cell)
                if fmt:
                    cell.number_format = fmt
            elif isinstance(cell.value, str):
                # Check if it's a percentage-like value
                if cell.value in ('Y', 'N', '是', '否'):
                    cell.alignment = data_align_c
                elif cell.value == '✓':
                    cell.alignment = data_align_c
                    cell.font = Font(name='Microsoft JhengHei', size=10.5, color='27AE60')
                elif cell.value.startswith('2025.') or cell.value.startswith('2026'):
                    cell.alignment = data_align_c
                else:
                    cell.alignment = data_align_l
            else:
                cell.alignment = data_align_l
    
    # ── Freeze panes: freeze after row 3 (header) ──
    ws.freeze_panes = 'A4'

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

# Remove 待確認事項 sheet's instruction rows (rows 2-3 are instructions, keep but format nicely)
# Just format all sheets

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

# Special handling for 待確認事項: the summary table at top needs special treatment
ws = wb['待確認事項']
# Row 7 is the sub-header for the summary table
# Make sure rows 4-6 (summary data) look clean
for row_idx in range(4, 7):
    for c in range(1, ws.max_column + 1):
        cell = ws.cell(row_idx, c)
        if cell.value is not None:
            if isinstance(cell.value, (int, float)):
                cell.alignment = data_align_c
                cell.number_format = NUM_0
            cell.font = data_font
            cell.border = BORDER_THIN

# Row 14 is the detail header
ws.row_dimensions[14].height = 26
for c in range(1, ws.max_column + 1):
    cell = ws.cell(14, c)
    if cell.value is not None:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = BORDER_THIN

# Format detail rows (15+)
for row_idx in range(15, ws.max_row + 1):
    ws.row_dimensions[row_idx].height = 28  # taller for readability
    for c in range(1, ws.max_column + 1):
        cell = ws.cell(row_idx, c)
        if cell.value is not None:
            cell.font = data_font
            cell.border = BORDER_THIN
            if isinstance(cell.value, (int, float)):
                cell.alignment = data_align_r
            elif isinstance(cell.value, str) and len(cell.value) > 20:
                cell.alignment = data_align_l
                cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)

# 版本修正紀錄 — special formatting
ws = wb['版本修正紀錄']
for row_idx in range(4, ws.max_row + 1):
    ws.row_dimensions[row_idx].height = 36  # taller for long text
    for c in range(1, ws.max_column + 1):
        cell = ws.cell(row_idx, c)
        if cell.value is not None:
            cell.font = data_font
            cell.border = BORDER_THIN
            if isinstance(cell.value, str) and len(cell.value) > 15:
                cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)

wb.save(DST)
print(f'\nSaved to: {DST}')