# -*- coding: utf-8 -*-
"""
B25_BatchProcessExcel_en.py
B25_批量处理Excel_en.py

Companion tutorial: B25 *Batch-Process Excel with Python + openpyxl*
Purpose : Read SalesReport_Raw.xlsx, compute quarterly / annual totals and
          completion rate, restyle, sort by annual total desc, add summary
          rows, and save as SalesReport_Processed.xlsx.

Requires : openpyxl>=3.0
Run       : python 批量处理Excel_en.py
"""
from __future__ import annotations

import sys
from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter


# ---------- 1. Paths ----------
INPUT  = Path("SalesReport_Raw.xlsx")
OUTPUT = Path("SalesReport_Processed.xlsx")


# ---------- 2. Pre-flight ----------
if not INPUT.exists():
    print(f"[Error] Input file not found: {INPUT}")
    print(f"        Run `python 生成示例文件_en.py` first to create a test file.")
    sys.exit(1)

print(f"[Start] Reading: {INPUT}")

# ---------- 3. Open + read ----------
wb = load_workbook(INPUT)
ws = wb.active

headers = [cell.value for cell in ws[1]]
print(f"[Info] Headers: {headers}")

def find_col(name_list, target):
    """Return 1-based column index of `target` in `name_list`, or None."""
    for i, n in enumerate(name_list, start=1):
        if n == target:
            return i
    return None

col_target = find_col(headers, "AnnualTarget")
col_rate   = find_col(headers, "CompletionRate")

if col_target is None or col_rate is None:
    print(f"[Warn] Could not find 'AnnualTarget' or 'CompletionRate' headers; " \
          f"will use 0 / 0% defaults.")
    col_target = col_target or 7
    col_rate   = col_rate   or 8

# Collect data rows
data_rows = []
for row in ws.iter_rows(min_row=2, values_only=True):
    if all(v is None for v in row):
        continue
    data_rows.append(list(row))

print(f"[Info] Read {len(data_rows)} data rows")

# ---------- 4. Business logic ----------
# Column indices (after potential shift):
#   0=Department, 1=Manager, 2=Q1, 3=Q2, 4=Q3, 5=Q4, 6=AnnualTarget, 7=CompletionRate
processed = []
for row in data_rows:
    dept, mgr, q1, q2, q3, q4, target, _old_rate = row
    nums = [q1 or 0, q2 or 0, q3 or 0, q4 or 0]
    y_sum = sum(nums)
    rate  = (y_sum / target) if target else 0.0
    processed.append([dept, mgr, q1, q2, q3, q4, target, y_sum, rate])

# Sort by annual total desc
processed.sort(key=lambda r: r[7], reverse=True)

# ---------- 5. Write the new sheet ----------
new_wb = Workbook()
new_ws = new_wb.active
new_ws.title = "Processed"

new_headers = headers[:7] + ["AnnualTotal", "CompletionRateRecalc"]
new_ws.append(new_headers)

for row in processed:
    new_ws.append(row)

sum_q1  = sum((r[2] or 0) for r in processed)
sum_q2  = sum((r[3] or 0) for r in processed)
sum_q3  = sum((r[4] or 0) for r in processed)
sum_q4  = sum((r[5] or 0) for r in processed)
sum_all = sum((r[7] or 0) for r in processed)
sum_tgt = sum((r[6] or 0) for r in processed)
overall_rate = (sum_all / sum_tgt) if sum_tgt else 0.0

n = len(processed)
new_ws.append(["TOTAL", "", sum_q1, sum_q2, sum_q3, sum_q4, sum_tgt, sum_all, overall_rate])
new_ws.append(["AVERAGE", "", sum_q1 / n, sum_q2 / n, sum_q3 / n, sum_q4 / n,
               sum_tgt / n, sum_all / n, overall_rate])

# ---------- 6. Styling ----------
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
for col_idx in range(1, len(new_headers) + 1):
    cell = new_ws.cell(row=1, column=col_idx)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

for row_idx in range(2, new_ws.max_row + 1):
    for col_idx in [3, 4, 5, 6, 7, 8]:
        new_ws.cell(row=row_idx, column=col_idx).number_format = "#,##0"
    new_ws.cell(row=row_idx, column=9).number_format = "0.0%"

summary_fill = PatternFill(start_color="E7E6E6", end_color="E7E6E6", fill_type="solid")
summary_font = Font(bold=True)
for col_idx in range(1, len(new_headers) + 1):
    c1 = new_ws.cell(row=new_ws.max_row - 1, column=col_idx)
    c2 = new_ws.cell(row=new_ws.max_row,     column=col_idx)
    c1.fill = summary_fill
    c2.fill = summary_fill
    c1.font = summary_font
    c2.font = summary_font

thin = Side(border_style="thin", color="BFBFBF")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in new_ws.iter_rows(min_row=1, max_row=new_ws.max_row,
                            min_col=1, max_col=len(new_headers)):
    for cell in row:
        cell.border = border

for col_idx in range(1, len(new_headers) + 1):
    new_ws.column_dimensions[get_column_letter(col_idx)].width = 16

# ---------- 7. Save (NEVER overwrite the input) ----------
new_wb.save(OUTPUT)

print()
print(f"[Done] Generated: {OUTPUT}")
print(f"[Info] Processed {len(processed)} rows + 2 summary rows (TOTAL + AVERAGE)")
print()
print("Open the result:")
print(f"  - Original {INPUT} is untouched (backup principle)")
print(f"  - New file {OUTPUT} is sorted by 'AnnualTotal' descending")