#!/usr/bin/env python3
"""
Generate test data for Shoplazza SPU and SKU tables.

Generates 100 SPU records with 1-5 SKU variants each.
"""

import sys
import os
import random
import argparse
from pathlib import Path
from datetime import datetime, timedelta

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


def generate_spu_data(num_spus: int = 100, tenant_id: str = "1", start_id: int = 1):
    """
    Generate SPU test data.

    Args:
        num_spus: Number of SPUs to generate
        tenant_id: Tenant ID
        start_id: Starting ID for SPUs

    Returns:
        List of SPU data dictionaries
    """
    categories = ["电子产品", "服装", "家居用品", "美妆", "食品", "运动用品", "图书", "玩具"]
    vendors = ["Sony", "Nike", "Apple", "Samsung", "华为", "小米", "美的", "海尔"]
    
    products = [
        ("蓝牙耳机", "Bluetooth Headphone", "高品质无线蓝牙耳机", "High-quality wireless Bluetooth headphone"),
        ("运动鞋", "Running Shoes", "舒适透气的运动鞋", "Comfortable and breathable running shoes"),
        ("智能手机", "Smartphone", "高性能智能手机", "High-performance smartphone"),
        ("笔记本电脑", "Laptop", "轻薄便携笔记本电脑", "Lightweight and portable laptop"),
        ("智能手表", "Smart Watch", "多功能智能手表", "Multi-function smart watch"),
        ("平板电脑", "Tablet", "高清平板电脑", "High-definition tablet"),
        ("无线鼠标", "Wireless Mouse", "人体工学无线鼠标", "Ergonomic wireless mouse"),
        ("机械键盘", "Mechanical Keyboard", "RGB背光机械键盘", "RGB backlit mechanical keyboard"),
        ("显示器", "Monitor", "4K高清显示器", "4K high-definition monitor"),
        ("音响", "Speaker", "蓝牙无线音响", "Bluetooth wireless speaker"),
    ]

    spus = []
    for i in range(num_spus):
        spu_id = start_id + i
        product = random.choice(products)
        category = random.choice(categories)
        vendor = random.choice(vendors)
        
        # Generate handle
        handle = f"product-{spu_id}"
        
        # Generate title (Chinese)
        title_zh = f"{product[0]} {vendor}"
        
        # Generate brief
        brief_zh = product[2]
        
        # Generate description
        description_zh = f"<p>{product[2]}，来自{vendor}品牌。{product[3]}</p>"
        
        # Generate SEO fields
        seo_title = f"{title_zh} - {category}"
        seo_description = f"购买{vendor}{product[0]}，{product[2]}"
        seo_keywords = f"{product[0]},{vendor},{category}"
        
        # Generate tags
        tags = f"{category},{vendor},{product[0]}"
        
        # Generate image
        image_src = f"//cdn.example.com/products/{spu_id}.jpg"
        
        # Generate dates
        created_at = datetime.now() - timedelta(days=random.randint(1, 365))
        updated_at = created_at + timedelta(days=random.randint(0, 30))
        
        spu = {
            'id': spu_id,
            'shop_id': 1,
            'shoplazza_id': f"spu-{spu_id}",
            'handle': handle,
            'title': title_zh,
            'brief': brief_zh,
            'description': description_zh,
            'spu': '',
            'vendor': vendor,
            'vendor_url': f"https://{vendor.lower()}.com",
            'seo_title': seo_title,
            'seo_description': seo_description,
            'seo_keywords': seo_keywords,
            'image_src': image_src,
            'image_width': 800,
            'image_height': 600,
            'image_path': f"products/{spu_id}.jpg",
            'image_alt': title_zh,
            'inventory_policy': '',
            'inventory_quantity': 0,
            'inventory_tracking': '0',
            'published': 1,
            'published_at': created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'requires_shipping': 1,
            'taxable': 0,
            'fake_sales': 0,
            'display_fake_sales': 0,
            'mixed_wholesale': 0,
            'need_variant_image': 0,
            'has_only_default_variant': 0,
            'tags': tags,
            'note': '',
            'category': category,
            'shoplazza_created_at': created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'shoplazza_updated_at': updated_at.strftime('%Y-%m-%d %H:%M:%S'),
            'tenant_id': tenant_id,
            'creator': '1',
            'create_time': created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updater': '1',
            'update_time': updated_at.strftime('%Y-%m-%d %H:%M:%S'),
            'deleted': 0
        }
        spus.append(spu)
    
    return spus


def generate_sku_data(spus: list, start_sku_id: int = 1):
    """
    Generate SKU test data for SPUs.

    Args:
        spus: List of SPU data
        start_sku_id: Starting ID for SKUs

    Returns:
        List of SKU data dictionaries
    """
    colors = ["黑色", "白色", "红色", "蓝色", "绿色", "灰色"]
    sizes = ["S", "M", "L", "XL", "XXL"]
    
    skus = []
    sku_id = start_sku_id
    
    for spu in spus:
        spu_id = spu['id']
        num_variants = random.randint(1, 5)
        
        # Base price
        base_price = random.uniform(50, 500)
        
        for i in range(num_variants):
            # Generate variant options
            color = random.choice(colors) if num_variants > 1 else None
            size = random.choice(sizes) if num_variants > 2 else None
            
            # Generate title
            title_parts = []
            if color:
                title_parts.append(color)
            if size:
                title_parts.append(size)
            title = " / ".join(title_parts) if title_parts else ""
            
            # Generate SKU
            sku_code = f"SKU-{spu_id}-{i+1}"
            
            # Generate price (variation from base)
            price = base_price + random.uniform(-20, 50)
            compare_at_price = price * random.uniform(1.2, 1.5)
            
            # Generate stock
            stock = random.randint(0, 100)
            
            # Generate dates
            created_at = datetime.now() - timedelta(days=random.randint(1, 365))
            updated_at = created_at + timedelta(days=random.randint(0, 30))
            
            sku = {
                'id': sku_id,
                'spu_id': spu_id,
                'shop_id': 1,
                'shoplazza_id': f"sku-{sku_id}",
                'shoplazza_product_id': spu['shoplazza_id'],
                'shoplazza_image_id': '',
                'title': title,
                'sku': sku_code,
                'barcode': f"BAR{sku_id:08d}",
                'position': i + 1,
                'price': round(price, 2),
                'compare_at_price': round(compare_at_price, 2),
                'cost_price': round(price * 0.6, 2),
                'option1': color if color else '',
                'option2': size if size else '',
                'option3': '',
                'inventory_quantity': stock,
                'weight': round(random.uniform(0.1, 5.0), 2),
                'weight_unit': 'kg',
                'image_src': '',
                'wholesale_price': '[{"price": ' + str(round(price * 0.8, 2)) + ', "minQuantity": 10}]',
                'note': '',
                'extend': '',
                'shoplazza_created_at': created_at.strftime('%Y-%m-%d %H:%M:%S'),
                'shoplazza_updated_at': updated_at.strftime('%Y-%m-%d %H:%M:%S'),
                'tenant_id': spu['tenant_id'],
                'creator': '1',
                'create_time': created_at.strftime('%Y-%m-%d %H:%M:%S'),
                'updater': '1',
                'update_time': updated_at.strftime('%Y-%m-%d %H:%M:%S'),
                'deleted': 0
            }
            skus.append(sku)
            sku_id += 1
    
    return skus


def generate_sql_inserts(spus: list, skus: list, output_file: str):
    """
    Generate SQL INSERT statements.

    Args:
        spus: List of SPU data
        skus: List of SKU data
        output_file: Output file path
    """
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write("-- SPU Test Data\n")
        f.write("INSERT INTO shoplazza_product_spu (\n")
        f.write("    id, shop_id, shoplazza_id, handle, title, brief, description, spu,\n")
        f.write("    vendor, vendor_url, seo_title, seo_description, seo_keywords,\n")
        f.write("    image_src, image_width, image_height, image_path, image_alt,\n")
        f.write("    inventory_policy, inventory_quantity, inventory_tracking,\n")
        f.write("    published, published_at, requires_shipping, taxable,\n")
        f.write("    fake_sales, display_fake_sales, mixed_wholesale, need_variant_image,\n")
        f.write("    has_only_default_variant, tags, note, category,\n")
        f.write("    shoplazza_created_at, shoplazza_updated_at, tenant_id,\n")
        f.write("    creator, create_time, updater, update_time, deleted\n")
        f.write(") VALUES\n")
        
        for i, spu in enumerate(spus):
            values = (
                f"({spu['id']}, {spu['shop_id']}, '{spu['shoplazza_id']}', "
                f"'{spu['handle']}', '{spu['title']}', '{spu['brief']}', "
                f"'{spu['description']}', '{spu['spu']}', '{spu['vendor']}', "
                f"'{spu['vendor_url']}', '{spu['seo_title']}', '{spu['seo_description']}', "
                f"'{spu['seo_keywords']}', '{spu['image_src']}', {spu['image_width']}, "
                f"{spu['image_height']}, '{spu['image_path']}', '{spu['image_alt']}', "
                f"'{spu['inventory_policy']}', {spu['inventory_quantity']}, "
                f"'{spu['inventory_tracking']}', {spu['published']}, "
                f"'{spu['published_at']}', {spu['requires_shipping']}, {spu['taxable']}, "
                f"{spu['fake_sales']}, {spu['display_fake_sales']}, {spu['mixed_wholesale']}, "
                f"{spu['need_variant_image']}, {spu['has_only_default_variant']}, "
                f"'{spu['tags']}', '{spu['note']}', '{spu['category']}', "
                f"'{spu['shoplazza_created_at']}', '{spu['shoplazza_updated_at']}', "
                f"'{spu['tenant_id']}', '{spu['creator']}', '{spu['create_time']}', "
                f"'{spu['updater']}', '{spu['update_time']}', {spu['deleted']})"
            )
            f.write(values)
            if i < len(spus) - 1:
                f.write(",\n")
            else:
                f.write(";\n\n")
        
        f.write("-- SKU Test Data\n")
        f.write("INSERT INTO shoplazza_product_sku (\n")
        f.write("    id, spu_id, shop_id, shoplazza_id, shoplazza_product_id, shoplazza_image_id,\n")
        f.write("    title, sku, barcode, position, price, compare_at_price, cost_price,\n")
        f.write("    option1, option2, option3, inventory_quantity, weight, weight_unit,\n")
        f.write("    image_src, wholesale_price, note, extend,\n")
        f.write("    shoplazza_created_at, shoplazza_updated_at, tenant_id,\n")
        f.write("    creator, create_time, updater, update_time, deleted\n")
        f.write(") VALUES\n")
        
        for i, sku in enumerate(skus):
            values = (
                f"({sku['id']}, {sku['spu_id']}, {sku['shop_id']}, '{sku['shoplazza_id']}', "
                f"'{sku['shoplazza_product_id']}', '{sku['shoplazza_image_id']}', "
                f"'{sku['title']}', '{sku['sku']}', '{sku['barcode']}', {sku['position']}, "
                f"{sku['price']}, {sku['compare_at_price']}, {sku['cost_price']}, "
                f"'{sku['option1']}', '{sku['option2']}', '{sku['option3']}', "
                f"{sku['inventory_quantity']}, {sku['weight']}, '{sku['weight_unit']}', "
                f"'{sku['image_src']}', '{sku['wholesale_price']}', '{sku['note']}', "
                f"'{sku['extend']}', '{sku['shoplazza_created_at']}', "
                f"'{sku['shoplazza_updated_at']}', '{sku['tenant_id']}', "
                f"'{sku['creator']}', '{sku['create_time']}', '{sku['updater']}', "
                f"'{sku['update_time']}', {sku['deleted']})"
            )
            f.write(values)
            if i < len(skus) - 1:
                f.write(",\n")
            else:
                f.write(";\n")


def main():
    parser = argparse.ArgumentParser(description='Generate test data for Shoplazza tables')
    parser.add_argument('--num-spus', type=int, default=100, help='Number of SPUs to generate')
    parser.add_argument('--tenant-id', default='1', help='Tenant ID')
    parser.add_argument('--start-spu-id', type=int, default=1, help='Starting SPU ID')
    parser.add_argument('--start-sku-id', type=int, default=1, help='Starting SKU ID')
    parser.add_argument('--output', default='test_data.sql', help='Output SQL file')
    
    args = parser.parse_args()
    
    print(f"Generating {args.num_spus} SPUs with variants...")
    
    # Generate SPU data
    spus = generate_spu_data(args.num_spus, args.tenant_id, args.start_spu_id)
    print(f"Generated {len(spus)} SPUs")
    
    # Generate SKU data
    skus = generate_sku_data(spus, args.start_sku_id)
    print(f"Generated {len(skus)} SKUs")
    
    # Generate SQL file
    generate_sql_inserts(spus, skus, args.output)
    print(f"SQL file generated: {args.output}")


if __name__ == '__main__':
    import json
    main()