#!/usr/bin/env python3
  """
  Convert CSV data to Excel import template.
  
  Reads CSV file (goods_with_pic.5years_congku.csv.shuf.1w) and generates Excel file
  based on the template format (商品导入模板.xlsx).
  
  Each CSV row corresponds to 1 SPU and 1 SKU, which will be exported as a single
  S (Single variant) row in the Excel template.
  """
  
  import sys
  import os
  import csv
  import random
  import argparse
  import re
  from pathlib import Path
  from datetime import datetime, timedelta
  import pandas as pd
  from openpyxl import load_workbook
  from openpyxl.styles import Font, Alignment
  from openpyxl.utils import get_column_letter
  
  # Add parent directory to path
  sys.path.insert(0, str(Path(__file__).parent.parent))
  
  
  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 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 csv_to_excel_row(csv_data: dict) -> dict:
      """
      Convert CSV data row to Excel template row.
      
      Each CSV row represents a single product with one variant (S type in Excel).
      
      Args:
          csv_data: Parsed CSV row data
          
      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 (similar to import_tenant2_csv.py)
      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 ruSkuName as SKU title, fallback to name
      sku_title = csv_data['ruSkuName'] or csv_data['name'] or 'SKU'
      
      # Use skuId as SKU code
      sku_code = csv_data['skuId'] or ''
      
      # Generate barcode
      try:
          sku_id = int(csv_data['skuId'])
          barcode = f"BAR{sku_id:08d}"
      except:
          barcode = ''
      
      # 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 (mapping to Excel template columns)
      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',  # Default to N
          'SEO关键词': seo_keywords,
          '商品上架': 'Y',  # Published by default
          '需要物流': 'Y',  # Requires shipping
          '商品收税': 'N',  # Not taxable by default
          '商品spu': '',  # Empty
          '启用虚拟销量': 'N',  # No fake sales
          '虚拟销量值': '',  # Empty
          '跟踪库存': 'Y',  # Track inventory
          '库存规则*': '1',  # Allow purchase when stock is 0
          '专辑名称': csv_data['categoryName'] or '',  # Category as album
          '标签': tags,
          '供应商名称': csv_data['supplierName'] or '',
          '供应商URL': '',  # Empty
          '款式1': '',  # Not used for S type
          '款式2': '',  # Not used for S type
          '款式3': '',  # Not used for S type
          '商品售价*': price,
          '商品原价': compare_at_price,
          '成本价': cost_price,
          '商品SKU': sku_code,
          '商品重量': weight,
          '重量单位': weight_unit,
          '商品条形码': barcode,
          '商品库存': inventory_quantity,
          '尺寸信息': '',  # Empty
          '原产地国别': '',  # Empty
          'HS（协调制度）代码': '',  # Empty
          '商品图片*': csv_data['imageUrl'] or '',  # Image URL
          '商品备注': '',  # Empty
          '款式备注': '',  # Empty
          '商品主图': csv_data['imageUrl'] or '',  # Main image URL
      }
      
      return excel_row
  
  
  def create_excel_from_template(template_file: str, output_file: str, csv_data_list: list):
      """
      Create Excel file from template and fill with CSV data.
      
      Args:
          template_file: Path to Excel template file
          output_file: Path to output Excel file
          csv_data_list: List of parsed CSV data dictionaries
      """
      # Load template
      wb = load_workbook(template_file)
      ws = wb.active  # Use the active sheet (Sheet4)
      
      # Find header row (row 2, index 1)
      header_row_idx = 2  # Row 2 in Excel (1-based, but header is at index 1 in pandas)
      
      # 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 (after header and instructions)
      data_start_row = 4  # Row 4 in Excel (1-based)
      
      # Clear existing data rows (from row 4 onwards, but keep header and instructions)
      # Find the last row with data in the template
      last_template_row = ws.max_row
      if last_template_row >= data_start_row:
          # Clear data rows (keep header and instruction rows)
          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
      
      # Convert CSV data to Excel rows
      for row_idx, csv_data in enumerate(csv_data_list):
          excel_row = csv_to_excel_row(csv_data)
          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 for text fields
                  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(csv_data_list)}")
  
  
  def main():
      parser = argparse.ArgumentParser(description='Convert CSV data to Excel import template')
      parser.add_argument('--csv-file', 
                         default='data/customer1/goods_with_pic.5years_congku.csv.shuf.1w',
                         help='CSV file path (default: data/customer1/goods_with_pic.5years_congku.csv.shuf.1w)')
      parser.add_argument('--template', 
                         default='docs/商品导入模板.xlsx',
                         help='Excel template file path (default: docs/商品导入模板.xlsx)')
      parser.add_argument('--output', 
                         default='商品导入数据.xlsx',
                         help='Output Excel file path (default: 商品导入数据.xlsx)')
      parser.add_argument('--limit', 
                         type=int, 
                         default=None,
                         help='Limit number of rows to process (default: all)')
      
      args = parser.parse_args()
      
      # 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 rows if specified
      if args.limit:
          csv_data_list = csv_data_list[:args.limit]
          print(f"Limited to {len(csv_data_list)} rows")
      
      # Create Excel file
      print(f"Creating Excel file from template: {args.template}")
      print(f"Output file: {args.output}")
      create_excel_from_template(args.template, args.output, csv_data_list)
      
      print(f"\nDone! Generated {len(csv_data_list)} product rows in Excel file.")
  
  
  if __name__ == '__main__':
      main()