#!/usr/bin/env python3
"""
Shared helpers for generating Shoplazza product import Excel files from the
official template `docs/商品导入模板.xlsx`.

We keep this module small and dependency-light (openpyxl only) so other scripts
can reuse the same template-writing behavior (header row mapping, data start
row, alignment).
"""

import re
from datetime import datetime
from typing import Dict, Iterable, List, Optional

from openpyxl import load_workbook
from openpyxl.styles import Alignment


def generate_handle(title: str) -> str:
    """
    Generate URL-friendly handle from title (ASCII only), suitable for Shoplazza
    `SEO URL Handle` field. Caller may prepend `products/`.
    """
    if not title:
        return "product"

    handle = str(title).lower()
    handle = re.sub(r"[^a-z0-9\s-]", "", handle)
    handle = re.sub(r"[-\s]+", "-", handle).strip("-")

    if len(handle) > 255:
        handle = handle[:255]

    return handle or "product"


def parse_date_to_datetime_str(value) -> str:
    """
    Parse common date strings into Shoplazza template datetime string:
    `YYYY-MM-DD HH:MM:SS`. If parsing fails, returns empty string.
    """
    if value is None:
        return ""

    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d %H:%M:%S")

    s = str(value).strip()
    if not s:
        return ""

    # Most competitor sheets use YYYY-MM-DD
    for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%d", "%Y/%m/%d", "%m/%d/%Y"):
        try:
            dt = datetime.strptime(s, fmt)
            if fmt in ("%Y-%m-%d", "%Y/%m/%d", "%m/%d/%Y"):
                dt = dt.replace(hour=0, minute=0, second=0)
            return dt.strftime("%Y-%m-%d %H:%M:%S")
        except Exception:
            pass

    return ""


def create_excel_from_template(
    template_file: str,
    output_file: str,
    excel_rows: List[Dict[str, object]],
    *,
    header_row_idx: int = 2,
    data_start_row: int = 4,
    sheet_name: Optional[str] = None,
) -> None:
    """
    Create an Excel file from Shoplazza import template and fill rows.

    - Header row is expected at row 2 (1-based) in the official template.
    - Data starts at row 4 (1-based), after the instruction row(s).
    """
    wb = load_workbook(template_file)
    ws = wb[sheet_name] if sheet_name else wb.active

    column_mapping: Dict[str, int] = {}
    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

    # 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
            value = excel_row[field_name]
            cell = ws.cell(row=excel_row_num, column=col_idx)
            cell.value = value
            if isinstance(value, str):
                cell.alignment = Alignment(vertical="top", wrap_text=True)
            elif isinstance(value, (int, float)):
                cell.alignment = Alignment(vertical="top")

    wb.save(output_file)