#!/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()