#!/usr/bin/env python3
"""
Shared utilities for generating Shoplazza (店匠) product import Excel files
based on the provided template `docs/商品导入模板.xlsx`.

We keep this in `scripts/` to maximize reuse by existing ad-hoc pipeline scripts.
"""

from openpyxl import load_workbook
from openpyxl.styles import Alignment


def load_template_column_mapping(ws, header_row_idx=2):
    """
    Read the header row in the template sheet and build a mapping:
    header_name -> column_index (1-based).
    """
    column_mapping = {}
    for col_idx in range(1, ws.max_column + 1):
        cell_value = ws.cell(row=header_row_idx, column=col_idx).value
        if cell_value:
            column_mapping[str(cell_value).strip()] = col_idx
    return column_mapping


def create_excel_from_template(template_file, output_file, excel_rows, header_row_idx=2, data_start_row=4):
    """
    Create an Excel file from the Shoplazza template and fill with data rows.

    Args:
        template_file: Path to Excel template file
        output_file: Path to output Excel file
        excel_rows: List[Dict[str, Any]] mapping template header -> value
        header_row_idx: Header row index in template (default 2)
        data_start_row: Data start row index in template (default 4)
    """
    wb = load_workbook(template_file)
    ws = wb.active

    column_mapping = load_template_column_mapping(ws, header_row_idx=header_row_idx)

    # Clear existing data rows
    last_template_row = ws.max_row
    if last_template_row >= data_start_row:
        for row in range(data_start_row, last_template_row + 1):
            for col in range(1, ws.max_column + 1):
                ws.cell(row=row, column=col).value = None

    # Write data rows
    for row_idx, excel_row in enumerate(excel_rows):
        excel_row_num = data_start_row + row_idx
        for field_name, col_idx in column_mapping.items():
            if field_name not in excel_row:
                continue
            cell = ws.cell(row=excel_row_num, column=col_idx)
            value = excel_row[field_name]
            cell.value = value
            if isinstance(value, str):
                cell.alignment = Alignment(vertical='top', wrap_text=True)
            else:
                cell.alignment = Alignment(vertical='top')

    wb.save(output_file)
    print("Excel file created: {}".format(output_file))
    print("  - Total rows: {}".format(len(excel_rows)))