# ============================================================
# core/reports/excel_base.py
# Helper Excel chung dùng cho tất cả báo cáo
# ============================================================

import os
import sys

try:
    import openpyxl
    from openpyxl.styles import (
        Font, PatternFill, Alignment, Border, Side, numbers
    )
    from openpyxl.utils import get_column_letter
except ImportError:
    print("❌ Cần cài: pip install openpyxl")
    sys.exit(1)

# ── Palette màu ──────────────────────────────────────────
C_HEADER    = "1F4E79"   # Navy
C_TITLE     = "2E75B6"   # Blue
C_SUBTOTAL  = "BDD7EE"   # Light blue
C_TOTAL     = "1F4E79"   # Navy
C_WHITE     = "FFFFFF"
C_ALT       = "F2F2F2"   # Light gray alternating rows

fmt_vnd = '#,##0'
fmt_vnd_zero = '#,##0;-#,##0;"-"'

thin = Side(style="thin", color="CCCCCC")
medium = Side(style="medium", color="888888")

def thin_border():
    return Border(left=thin, right=thin, top=thin, bottom=thin)

def make_header_font(color=C_WHITE):
    return Font(name="Arial", bold=True, size=10, color=color)

def make_normal_font():
    return Font(name="Arial", size=9)

def make_title_font(size=14):
    return Font(name="Arial", bold=True, size=size, color=C_TITLE)


def new_workbook() -> openpyxl.Workbook:
    wb = openpyxl.Workbook()
    wb.remove(wb.active)
    return wb


def write_title(ws, row: int, col: int, text: str, span: int = 1,
                size: int = 13) -> None:
    ws.cell(row, col, text).font = make_title_font(size)
    if span > 1:
        ws.merge_cells(
            start_row=row, start_column=col,
            end_row=row, end_column=col + span - 1
        )
    ws.cell(row, col).alignment = Alignment(horizontal="center")


def write_header_row(ws, row: int, headers: list,
                     start_col: int = 1) -> None:
    for i, h in enumerate(headers):
        cell = ws.cell(row, start_col + i, h)
        cell.font     = make_header_font()
        cell.fill     = PatternFill("solid", fgColor=C_HEADER)
        cell.alignment = Alignment(horizontal="center", wrap_text=True)
        cell.border   = thin_border()


def write_data_row(ws, row: int, values: list, start_col: int = 1,
                   alt: bool = False, number_cols: list = None) -> None:
    """Ghi 1 dòng dữ liệu, tô màu xen kẽ."""
    fill = PatternFill("solid", fgColor=C_ALT) if alt else None
    for i, v in enumerate(values):
        cell = ws.cell(row, start_col + i, v)
        cell.font   = make_normal_font()
        cell.border = thin_border()
        if fill:
            cell.fill = fill
        if number_cols and i in number_cols:
            cell.number_format = fmt_vnd_zero
            cell.alignment = Alignment(horizontal="right")


def write_subtotal_row(ws, row: int, values: list,
                       start_col: int = 1) -> None:
    for i, v in enumerate(values):
        cell = ws.cell(row, start_col + i, v)
        cell.font   = Font(name="Arial", bold=True, size=9)
        cell.fill   = PatternFill("solid", fgColor=C_SUBTOTAL)
        cell.border = thin_border()
        if isinstance(v, (int, float)):
            cell.number_format = fmt_vnd_zero
            cell.alignment = Alignment(horizontal="right")


def write_total_row(ws, row: int, values: list,
                    start_col: int = 1) -> None:
    for i, v in enumerate(values):
        cell = ws.cell(row, start_col + i, v)
        cell.font      = Font(name="Arial", bold=True, size=10, color=C_WHITE)
        cell.fill      = PatternFill("solid", fgColor=C_TOTAL)
        cell.border    = thin_border()
        if isinstance(v, (int, float)):
            cell.number_format = fmt_vnd_zero
            cell.alignment = Alignment(horizontal="right")


def auto_width(ws, min_width: int = 8, max_width: int = 50) -> None:
    """Tự điều chỉnh độ rộng cột."""
    for col in ws.columns:
        max_len = 0
        col_letter = get_column_letter(col[0].column)
        for cell in col:
            try:
                if cell.value:
                    max_len = max(max_len, len(str(cell.value)))
            except Exception:
                pass
        ws.column_dimensions[col_letter].width = min(max(max_len + 2, min_width), max_width)


def freeze_header(ws, row: int = 2, col: int = 1) -> None:
    from openpyxl.utils.cell import get_column_letter
    ws.freeze_panes = f"{get_column_letter(col)}{row}"
