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

Generates 100 SPU records with 1-5 SKUs 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_skus = random.randint(1, 5)
        
        # Base price
        base_price = random.uniform(50, 500)
        
        for i in range(num_skus):
            # Generate variant options
            color = random.choice(colors) if num_skus > 1 else None
            size = random.choice(sizes) if num_skus > 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': None,  # JSON field, use NULL instead of empty string
                '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 escape_sql_string(value: str) -> str:
    """
    Escape SQL string value (replace single quotes with doubled quotes).
    
    Args:
        value: String value to escape
        
    Returns:
        Escaped string
    """
    if value is None:
        return ''
    return str(value).replace("'", "''").replace("\\", "\\\\")


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']}, '{escape_sql_string(spu['shoplazza_id'])}', "
                f"'{escape_sql_string(spu['handle'])}', '{escape_sql_string(spu['title'])}', "
                f"'{escape_sql_string(spu['brief'])}', '{escape_sql_string(spu['description'])}', "
                f"'{escape_sql_string(spu['spu'])}', '{escape_sql_string(spu['vendor'])}', "
                f"'{escape_sql_string(spu['vendor_url'])}', '{escape_sql_string(spu['seo_title'])}', "
                f"'{escape_sql_string(spu['seo_description'])}', '{escape_sql_string(spu['seo_keywords'])}', "
                f"'{escape_sql_string(spu['image_src'])}', {spu['image_width']}, "
                f"{spu['image_height']}, '{escape_sql_string(spu['image_path'])}', "
                f"'{escape_sql_string(spu['image_alt'])}', '{escape_sql_string(spu['inventory_policy'])}', "
                f"{spu['inventory_quantity']}, '{escape_sql_string(spu['inventory_tracking'])}', "
                f"{spu['published']}, '{escape_sql_string(spu['published_at'])}', "
                f"{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"'{escape_sql_string(spu['tags'])}', '{escape_sql_string(spu['note'])}', "
                f"'{escape_sql_string(spu['category'])}', '{escape_sql_string(spu['shoplazza_created_at'])}', "
                f"'{escape_sql_string(spu['shoplazza_updated_at'])}', '{escape_sql_string(spu['tenant_id'])}', "
                f"'{escape_sql_string(spu['creator'])}', '{escape_sql_string(spu['create_time'])}', "
                f"'{escape_sql_string(spu['updater'])}', '{escape_sql_string(spu['update_time'])}', "
                f"{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):
            # Handle extend field (JSON, can be NULL)
            extend_value = 'NULL' if sku['extend'] is None else f"'{escape_sql_string(sku['extend'])}'"
            
            values = (
                f"({sku['id']}, {sku['spu_id']}, {sku['shop_id']}, '{escape_sql_string(sku['shoplazza_id'])}', "
                f"'{escape_sql_string(sku['shoplazza_product_id'])}', '{escape_sql_string(sku['shoplazza_image_id'])}', "
                f"'{escape_sql_string(sku['title'])}', '{escape_sql_string(sku['sku'])}', "
                f"'{escape_sql_string(sku['barcode'])}', {sku['position']}, "
                f"{sku['price']}, {sku['compare_at_price']}, {sku['cost_price']}, "
                f"'{escape_sql_string(sku['option1'])}', '{escape_sql_string(sku['option2'])}', "
                f"'{escape_sql_string(sku['option3'])}', {sku['inventory_quantity']}, {sku['weight']}, "
                f"'{escape_sql_string(sku['weight_unit'])}', '{escape_sql_string(sku['image_src'])}', "
                f"'{escape_sql_string(sku['wholesale_price'])}', '{escape_sql_string(sku['note'])}', "
                f"{extend_value}, '{escape_sql_string(sku['shoplazza_created_at'])}', "
                f"'{escape_sql_string(sku['shoplazza_updated_at'])}', '{escape_sql_string(sku['tenant_id'])}', "
                f"'{escape_sql_string(sku['creator'])}', '{escape_sql_string(sku['create_time'])}', "
                f"'{escape_sql_string(sku['updater'])}', '{escape_sql_string(sku['update_time'])}', "
                f"{sku['deleted']})"
            )
            f.write(values)
            if i < len(skus) - 1:
                f.write(",\n")
            else:
                f.write(";\n")


def get_max_ids_from_db(db_config=None):
    """
    Get maximum IDs from database to avoid primary key conflicts.
    
    Args:
        db_config: Optional database config dict with keys: host, port, database, username, password
        
    Returns:
        tuple: (max_spu_id, max_sku_id) or (0, 0) if cannot connect
    """
    if not db_config:
        return 0, 0
    
    try:
        from utils.db_connector import create_db_connection
        from sqlalchemy import text
        
        db_engine = create_db_connection(
            host=db_config['host'],
            port=db_config['port'],
            database=db_config['database'],
            username=db_config['username'],
            password=db_config['password']
        )
        
        with db_engine.connect() as conn:
            result = conn.execute(text('SELECT MAX(id) FROM shoplazza_product_spu'))
            max_spu_id = result.scalar() or 0
            
            result = conn.execute(text('SELECT MAX(id) FROM shoplazza_product_sku'))
            max_sku_id = result.scalar() or 0
            
            return max_spu_id, max_sku_id
    except Exception as e:
        print(f"Warning: Could not get max IDs from database: {e}")
        return 0, 0


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=None, help='Starting SPU ID (default: auto-calculate from DB)')
    parser.add_argument('--start-sku-id', type=int, default=None, help='Starting SKU ID (default: auto-calculate from DB)')
    parser.add_argument('--output', default='test_data.sql', help='Output SQL file')
    parser.add_argument('--db-host', help='Database host (for auto-calculating start IDs)')
    parser.add_argument('--db-port', type=int, default=3306, help='Database port (default: 3306)')
    parser.add_argument('--db-database', help='Database name (for auto-calculating start IDs)')
    parser.add_argument('--db-username', help='Database username (for auto-calculating start IDs)')
    parser.add_argument('--db-password', help='Database password (for auto-calculating start IDs)')
    
    args = parser.parse_args()
    
    # Auto-calculate start IDs if not provided and DB config available
    start_spu_id = args.start_spu_id
    start_sku_id = args.start_sku_id
    
    if (start_spu_id is None or start_sku_id is None) and args.db_host and args.db_database and args.db_username and args.db_password:
        print("Auto-calculating start IDs from database...")
        db_config = {
            'host': args.db_host,
            'port': args.db_port,
            'database': args.db_database,
            'username': args.db_username,
            'password': args.db_password
        }
        max_spu_id, max_sku_id = get_max_ids_from_db(db_config)
        if start_spu_id is None:
            start_spu_id = max_spu_id + 1
        if start_sku_id is None:
            start_sku_id = max_sku_id + 1
        print(f"  Max SPU ID in DB: {max_spu_id}, using start SPU ID: {start_spu_id}")
        print(f"  Max SKU ID in DB: {max_sku_id}, using start SKU ID: {start_sku_id}")
    else:
        if start_spu_id is None:
            start_spu_id = 1
        if start_sku_id is None:
            start_sku_id = 1
        print(f"Using start SPU ID: {start_spu_id}, start SKU ID: {start_sku_id}")
    
    print(f"Generating {args.num_spus} SPUs with skus...")
    
    # Generate SPU data
    spus = generate_spu_data(args.num_spus, args.tenant_id, start_spu_id)
    print(f"Generated {len(spus)} SPUs")
    
    # Generate SKU data
    skus = generate_sku_data(spus, 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()