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