#!/usr/bin/env python3
"""
Convert CSV data to Excel import template with multi-variant support.

Reads CSV file (goods_with_pic.5years_congku.csv.shuf.1w) and generates Excel file
based on the template format (商品导入模板.xlsx).

Features:
- 30% products as Single variant (S type)
- 70% products as Multi variant (M+P type) with color, size, material options
"""

import sys
import os
import csv
import random
import argparse
import re
from pathlib import Path
from datetime import datetime, timedelta
import itertools
from openpyxl import load_workbook
from openpyxl.styles import Alignment

# Add parent directory to path
sys.path.insert(0, str(Path(__file__).parent.parent))

# Color definitions
COLORS = [
    "Red", "Blue", "Green", "Yellow", "Black", "White", "Orange", "Purple",
    "Pink", "Brown", "Gray", "Navy", "Beige", "Cream", "Maroon", "Olive",
    "Teal", "Cyan", "Magenta", "Lime", "Indigo", "Gold", "Silver", "Bronze",
    "Coral", "Turquoise", "Violet", "Khaki", "Charcoal", "Ivory"
]


def clean_value(value):
    """
    Clean and normalize value.
    
    Args:
        value: Value to clean
        
    Returns:
        Cleaned string value
    """
    if value is None:
        return ''
    value = str(value).strip()
    # Remove surrounding quotes
    if value.startswith('"') and value.endswith('"'):
        value = value[1:-1]
    return value


def parse_csv_row(row: dict) -> dict:
    """
    Parse CSV row and extract fields.
    
    Args:
        row: CSV row dictionary
        
    Returns:
        Parsed data dictionary
    """
    return {
        'skuId': clean_value(row.get('skuId', '')),
        'name': clean_value(row.get('name', '')),
        'name_pinyin': clean_value(row.get('name_pinyin', '')),
        'create_time': clean_value(row.get('create_time', '')),
        'ruSkuName': clean_value(row.get('ruSkuName', '')),
        'enSpuName': clean_value(row.get('enSpuName', '')),
        'categoryName': clean_value(row.get('categoryName', '')),
        'supplierName': clean_value(row.get('supplierName', '')),
        'brandName': clean_value(row.get('brandName', '')),
        'file_id': clean_value(row.get('file_id', '')),
        'days_since_last_update': clean_value(row.get('days_since_last_update', '')),
        'id': clean_value(row.get('id', '')),
        'imageUrl': clean_value(row.get('imageUrl', ''))
    }


def generate_handle(title: str) -> str:
    """
    Generate URL-friendly handle from title.
    
    Args:
        title: Product title
        
    Returns:
        URL-friendly handle (ASCII only)
    """
    # Convert to lowercase
    handle = title.lower()
    
    # Remove non-ASCII characters, keep only letters, numbers, spaces, and hyphens
    handle = re.sub(r'[^a-z0-9\s-]', '', handle)
    
    # Replace spaces and multiple hyphens with single hyphen
    handle = re.sub(r'[-\s]+', '-', handle)
    handle = handle.strip('-')
    
    # Limit length
    if len(handle) > 255:
        handle = handle[:255]
    
    return handle or 'product'


def extract_material_from_title(title: str) -> str:
    """
    Extract material from title by taking the last word after splitting by space.
    
    按照商品标题空格分割后的最后一个字符串作为material。
    例如："消防套 塑料【英文包装】" -> 最后一个字符串是 "塑料【英文包装】"
    
    Args:
        title: Product title
        
    Returns:
        Material string (single value)
    """
    if not title:
        return 'default'
    
    # Split by spaces (只按空格分割，保持原样)
    parts = title.strip().split()
    if parts:
        # Get last part (最后一个字符串)
        material = parts[-1]
        # Remove brackets but keep content
        material = re.sub(r'[【】\[\]()（）]', '', material)
        material = material.strip()
        if material:
            return material
    
    return 'default'


def generate_single_variant_row(csv_data: dict, base_sku_id: int = 1) -> dict:
    """
    Generate Excel row for Single variant (S type) product.
    
    Args:
        csv_data: Parsed CSV row data
        base_sku_id: Base SKU ID for generating SKU code
        
    Returns:
        Dictionary mapping Excel column names to values
    """
    # Parse create_time
    try:
        created_at = datetime.strptime(csv_data['create_time'], '%Y-%m-%d %H:%M:%S')
        create_time_str = created_at.strftime('%Y-%m-%d %H:%M:%S')
    except:
        created_at = datetime.now() - timedelta(days=random.randint(1, 365))
        create_time_str = created_at.strftime('%Y-%m-%d %H:%M:%S')
    
    # Generate title - use name or enSpuName
    title = csv_data['name'] or csv_data['enSpuName'] or 'Product'
    
    # Generate handle - prefer enSpuName, then name_pinyin, then title
    handle_source = csv_data['enSpuName'] or csv_data['name_pinyin'] or title
    handle = generate_handle(handle_source)
    if handle and not handle.startswith('products/'):
        handle = f'products/{handle}'
    
    # Generate SEO fields
    seo_title = f"{title} - {csv_data['categoryName']}" if csv_data['categoryName'] else title
    seo_description = f"购买{csv_data['brandName']}{title}" if csv_data['brandName'] else title
    seo_keywords_parts = [title]
    if csv_data['categoryName']:
        seo_keywords_parts.append(csv_data['categoryName'])
    if csv_data['brandName']:
        seo_keywords_parts.append(csv_data['brandName'])
    seo_keywords = ','.join(seo_keywords_parts)
    
    # Generate tags from category and brand
    tags_parts = []
    if csv_data['categoryName']:
        tags_parts.append(csv_data['categoryName'])
    if csv_data['brandName']:
        tags_parts.append(csv_data['brandName'])
    tags = ','.join(tags_parts) if tags_parts else ''
    
    # Generate prices
    price = round(random.uniform(50, 500), 2)
    compare_at_price = round(price * random.uniform(1.2, 1.5), 2)
    cost_price = round(price * 0.6, 2)
    
    # Generate random stock
    inventory_quantity = random.randint(0, 100)
    
    # Generate random weight
    weight = round(random.uniform(0.1, 5.0), 2)
    weight_unit = 'kg'
    
    # Use skuId as SKU code
    sku_code = csv_data['skuId'] or f'SKU-{base_sku_id}'
    
    # Generate barcode
    try:
        sku_id = int(csv_data['skuId']) if csv_data['skuId'] else base_sku_id
        barcode = f"BAR{sku_id:08d}"
    except:
        barcode = f"BAR{base_sku_id:08d}"
    
    # Build description
    description = f"<p>{csv_data['name']}</p>" if csv_data['name'] else ''
    
    # Build brief (subtitle)
    brief = csv_data['name'] or ''
    
    # Excel row data
    excel_row = {
        '商品ID': '',  # Empty for new products
        '创建时间': create_time_str,
        '商品标题*': title,
        '商品属性*': 'S',  # Single variant product
        '商品副标题': brief,
        '商品描述': description,
        'SEO标题': seo_title,
        'SEO描述': seo_description,
        'SEO URL Handle': handle,
        'SEO URL 重定向': 'N',
        'SEO关键词': seo_keywords,
        '商品上架': 'Y',
        '需要物流': 'Y',
        '商品收税': 'N',
        '商品spu': '',
        '启用虚拟销量': 'N',
        '虚拟销量值': '',
        '跟踪库存': 'Y',
        '库存规则*': '1',
        '专辑名称': csv_data['categoryName'] or '',
        '标签': tags,
        '供应商名称': csv_data['supplierName'] or '',
        '供应商URL': '',
        '款式1': '',  # Empty for S type
        '款式2': '',  # Empty for S type
        '款式3': '',  # Empty for S type
        '商品售价*': price,
        '商品原价': compare_at_price,
        '成本价': cost_price,
        '商品SKU': sku_code,
        '商品重量': weight,
        '重量单位': weight_unit,
        '商品条形码': barcode,
        '商品库存': inventory_quantity,
        '尺寸信息': '',
        '原产地国别': '',
        'HS（协调制度）代码': '',
        '商品图片*': csv_data['imageUrl'] or '',
        '商品备注': '',
        '款式备注': '',
        '商品主图': csv_data['imageUrl'] or '',
    }
    
    return excel_row


def generate_multi_variant_rows(csv_data: dict, base_sku_id: int = 1) -> list:
    """
    Generate Excel rows for Multi variant (M+P type) product.
    
    Returns a list of rows:
    - First row: M (主商品) with option names
    - Following rows: P (子款式) with option values
    
    Args:
        csv_data: Parsed CSV row data
        base_sku_id: Base SKU ID for generating SKU codes
        
    Returns:
        List of dictionaries mapping Excel column names to values
    """
    rows = []
    
    # Parse create_time
    try:
        created_at = datetime.strptime(csv_data['create_time'], '%Y-%m-%d %H:%M:%S')
        create_time_str = created_at.strftime('%Y-%m-%d %H:%M:%S')
    except:
        created_at = datetime.now() - timedelta(days=random.randint(1, 365))
        create_time_str = created_at.strftime('%Y-%m-%d %H:%M:%S')
    
    # Generate title
    title = csv_data['name'] or csv_data['enSpuName'] or 'Product'
    
    # Generate handle
    handle_source = csv_data['enSpuName'] or csv_data['name_pinyin'] or title
    handle = generate_handle(handle_source)
    if handle and not handle.startswith('products/'):
        handle = f'products/{handle}'
    
    # Generate SEO fields
    seo_title = f"{title} - {csv_data['categoryName']}" if csv_data['categoryName'] else title
    seo_description = f"购买{csv_data['brandName']}{title}" if csv_data['brandName'] else title
    seo_keywords_parts = [title]
    if csv_data['categoryName']:
        seo_keywords_parts.append(csv_data['categoryName'])
    if csv_data['brandName']:
        seo_keywords_parts.append(csv_data['brandName'])
    seo_keywords = ','.join(seo_keywords_parts)
    
    # Generate tags
    tags_parts = []
    if csv_data['categoryName']:
        tags_parts.append(csv_data['categoryName'])
    if csv_data['brandName']:
        tags_parts.append(csv_data['brandName'])
    tags = ','.join(tags_parts) if tags_parts else ''
    
    # Extract material from title (last word after splitting by space)
    material = extract_material_from_title(title)
    
    # Generate color options: randomly select 2-10 colors from COLORS list
    num_colors = random.randint(2, 10)
    selected_colors = random.sample(COLORS, min(num_colors, len(COLORS)))
    
    # Generate size options: 1-30, randomly select 4-8
    num_sizes = random.randint(4, 8)
    all_sizes = [str(i) for i in range(1, 31)]
    selected_sizes = random.sample(all_sizes, num_sizes)
    
    # Material has only one value
    materials = [material]
    
    # Generate all combinations (Cartesian product)
    variants = list(itertools.product(selected_colors, selected_sizes, materials))
    
    # Generate M row (主商品)
    description = f"<p>{csv_data['name']}</p>" if csv_data['name'] else ''
    brief = csv_data['name'] or ''
    
    m_row = {
        '商品ID': '',
        '创建时间': create_time_str,
        '商品标题*': title,
        '商品属性*': 'M',  # Main product
        '商品副标题': brief,
        '商品描述': description,
        'SEO标题': seo_title,
        'SEO描述': seo_description,
        'SEO URL Handle': handle,
        'SEO URL 重定向': 'N',
        'SEO关键词': seo_keywords,
        '商品上架': 'Y',
        '需要物流': 'Y',
        '商品收税': 'N',
        '商品spu': '',
        '启用虚拟销量': 'N',
        '虚拟销量值': '',
        '跟踪库存': 'Y',
        '库存规则*': '1',
        '专辑名称': csv_data['categoryName'] or '',
        '标签': tags,
        '供应商名称': csv_data['supplierName'] or '',
        '供应商URL': '',
        '款式1': 'color',  # Option name
        '款式2': 'size',   # Option name
        '款式3': 'material',  # Option name
        '商品售价*': '',  # Empty for M row
        '商品原价': '',
        '成本价': '',
        '商品SKU': '',  # Empty for M row
        '商品重量': '',
        '重量单位': '',
        '商品条形码': '',
        '商品库存': '',  # Empty for M row
        '尺寸信息': '',
        '原产地国别': '',
        'HS（协调制度）代码': '',
        '商品图片*': csv_data['imageUrl'] or '',  # Main product image
        '商品备注': '',
        '款式备注': '',
        '商品主图': csv_data['imageUrl'] or '',
    }
    rows.append(m_row)
    
    # Generate P rows (子款式) for each variant combination
    base_price = round(random.uniform(50, 500), 2)
    
    for variant_idx, (color, size, mat) in enumerate(variants):
        # Generate price variation (within ±20% of base)
        price = round(base_price * random.uniform(0.8, 1.2), 2)
        compare_at_price = round(price * random.uniform(1.2, 1.5), 2)
        cost_price = round(price * 0.6, 2)
        
        # Generate random stock
        inventory_quantity = random.randint(0, 100)
        
        # Generate random weight
        weight = round(random.uniform(0.1, 5.0), 2)
        weight_unit = 'kg'
        
        # Generate SKU code
        sku_code = f"{csv_data['skuId']}-{color}-{size}-{mat}" if csv_data['skuId'] else f'SKU-{base_sku_id}-{variant_idx+1}'
        
        # Generate barcode
        barcode = f"BAR{base_sku_id:08d}{variant_idx+1:03d}"
        
        p_row = {
            '商品ID': '',
            '创建时间': create_time_str,
            '商品标题*': title,  # Same as M row
            '商品属性*': 'P',  # Variant
            '商品副标题': '',  # Empty for P row
            '商品描述': '',  # Empty for P row
            'SEO标题': '',  # Empty for P row
            'SEO描述': '',  # Empty for P row
            'SEO URL Handle': '',  # Empty for P row
            'SEO URL 重定向': '',
            'SEO关键词': '',
            '商品上架': 'Y',
            '需要物流': 'Y',
            '商品收税': 'N',
            '商品spu': '',
            '启用虚拟销量': 'N',
            '虚拟销量值': '',
            '跟踪库存': 'Y',
            '库存规则*': '1',
            '专辑名称': '',  # Empty for P row
            '标签': '',  # Empty for P row
            '供应商名称': '',  # Empty for P row
            '供应商URL': '',
            '款式1': color,  # Option value
            '款式2': size,   # Option value
            '款式3': mat,    # Option value
            '商品售价*': price,
            '商品原价': compare_at_price,
            '成本价': cost_price,
            '商品SKU': sku_code,
            '商品重量': weight,
            '重量单位': weight_unit,
            '商品条形码': barcode,
            '商品库存': inventory_quantity,
            '尺寸信息': '',
            '原产地国别': '',
            'HS（协调制度）代码': '',
            '商品图片*': '',  # Empty for P row (uses main product image)
            '商品备注': '',
            '款式备注': '',
            '商品主图': '',
        }
        rows.append(p_row)
    
    return rows


def read_csv_file(csv_file: str) -> list:
    """
    Read CSV file and return list of parsed rows.
    
    Args:
        csv_file: Path to CSV file
        
    Returns:
        List of parsed CSV data dictionaries
    """
    csv_data_list = []
    
    with open(csv_file, 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            parsed = parse_csv_row(row)
            csv_data_list.append(parsed)
    
    return csv_data_list


def create_excel_from_template(template_file: str, output_file: str, excel_rows: list):
    """
    Create Excel file from template and fill with data rows.
    
    Args:
        template_file: Path to Excel template file
        output_file: Path to output Excel file
        excel_rows: List of dictionaries mapping Excel column names to values
    """
    # Load template
    wb = load_workbook(template_file)
    ws = wb.active  # Use the active sheet (Sheet4)
    
    # Find header row (row 2)
    header_row_idx = 2
    
    # Get column mapping from header row
    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[cell_value] = col_idx
    
    # Start writing data from row 4
    data_start_row = 4
    
    # 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
        
        # Write each field to corresponding column
        for field_name, col_idx in column_mapping.items():
            if field_name in excel_row:
                cell = ws.cell(row=excel_row_num, column=col_idx)
                value = excel_row[field_name]
                cell.value = value
                
                # Set alignment
                if isinstance(value, str):
                    cell.alignment = Alignment(vertical='top', wrap_text=True)
                elif isinstance(value, (int, float)):
                    cell.alignment = Alignment(vertical='top')
    
    # Save workbook
    wb.save(output_file)
    print(f"Excel file created: {output_file}")
    print(f"  - Total rows: {len(excel_rows)}")


def main():
    parser = argparse.ArgumentParser(description='Convert CSV data to Excel import template with multi-variant support')
    parser.add_argument('--csv-file', 
                       default='data/customer1/goods_with_pic.5years_congku.csv.shuf.1w',
                       help='CSV file path')
    parser.add_argument('--template', 
                       default='docs/商品导入模板.xlsx',
                       help='Excel template file path')
    parser.add_argument('--output', 
                       default='商品导入数据.xlsx',
                       help='Output Excel file path')
    parser.add_argument('--limit', 
                       type=int, 
                       default=None,
                       help='Limit number of products to process')
    parser.add_argument('--single-ratio', 
                       type=float, 
                       default=0.3,
                       help='Ratio of single variant products (default: 0.3 = 30%%)')
    parser.add_argument('--seed', 
                       type=int, 
                       default=None,
                       help='Random seed for reproducible results')
    
    args = parser.parse_args()
    
    # Set random seed if provided
    if args.seed is not None:
        random.seed(args.seed)
    
    # Check if files exist
    if not os.path.exists(args.csv_file):
        print(f"Error: CSV file not found: {args.csv_file}")
        sys.exit(1)
    
    if not os.path.exists(args.template):
        print(f"Error: Template file not found: {args.template}")
        sys.exit(1)
    
    # Read CSV file
    print(f"Reading CSV file: {args.csv_file}")
    csv_data_list = read_csv_file(args.csv_file)
    print(f"Read {len(csv_data_list)} rows from CSV")
    
    # Limit products if specified
    if args.limit:
        csv_data_list = csv_data_list[:args.limit]
        print(f"Limited to {len(csv_data_list)} products")
    
    # Generate Excel rows
    print(f"\nGenerating Excel rows...")
    print(f"  - Single variant ratio: {args.single_ratio*100:.0f}%")
    print(f"  - Multi variant ratio: {(1-args.single_ratio)*100:.0f}%")
    
    excel_rows = []
    single_count = 0
    multi_count = 0
    
    for idx, csv_data in enumerate(csv_data_list):
        # Decide if this product should be single or multi variant
        is_single = random.random() < args.single_ratio
        
        if is_single:
            # Generate single variant (S type)
            row = generate_single_variant_row(csv_data, base_sku_id=idx+1)
            excel_rows.append(row)
            single_count += 1
        else:
            # Generate multi variant (M+P type)
            rows = generate_multi_variant_rows(csv_data, base_sku_id=idx+1)
            excel_rows.extend(rows)
            multi_count += 1
    
    print(f"\nGenerated:")
    print(f"  - Single variant products: {single_count}")
    print(f"  - Multi variant products: {multi_count}")
    print(f"  - Total Excel rows: {len(excel_rows)}")
    
    # Create Excel file
    print(f"\nCreating Excel file from template: {args.template}")
    print(f"Output file: {args.output}")
    create_excel_from_template(args.template, args.output, excel_rows)
    
    print(f"\nDone! Generated {len(excel_rows)} rows in Excel file.")


if __name__ == '__main__':
    main()