#!/usr/bin/env python3
"""
Import only ACTIVE products from ProductListScreen.csv
(products with stock, history, or purchase orders)
"""
import csv
import json
import mysql.connector
import sys
from datetime import datetime

def load_env_config():
    """Load database configuration from .env file"""
    config = {}
    env_path = '/home/whgoparts/public_html/whims-dev/.env'

    with open(env_path, 'r') as f:
        for line in f:
            line = line.strip()
            if line and not line.startswith('#') and '=' in line:
                key, value = line.split('=', 1)
                config[key] = value.strip('"').strip("'")

    return config

def get_db_connection(database='whims_dev'):
    """Create database connection"""
    config = load_env_config()

    # Use different database config for goparts
    if database == 'goparts':
        return mysql.connector.connect(
            host=config.get('GOPARTS_DB_HOST', config.get('DB_HOST', 'localhost')),
            port=int(config.get('GOPARTS_DB_PORT', config.get('DB_PORT', '3306'))),
            user=config.get('GOPARTS_DB_USERNAME', config.get('DB_USERNAME', 'root')),
            password=config.get('GOPARTS_DB_PASSWORD', config.get('DB_PASSWORD', '')),
            database=config.get('GOPARTS_DB_DATABASE', 'goparts')
        )
    else:
        return mysql.connector.connect(
            host=config.get('DB_HOST', 'localhost'),
            port=int(config.get('DB_PORT', '3306')),
            user=config.get('DB_USERNAME', 'root'),
            password=config.get('DB_PASSWORD', ''),
            database=config.get('DB_DATABASE', 'whims_dev')
        )

def identify_active_products():
    """Identify products that have stock, history, or are in purchase orders"""
    print("🔍 Identifying active products...")

    active_products = set()

    # 1. Products with current stock
    print("   Checking stock quantities...")
    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/StockQuantityBySublocationInUnitsWDetail-Oct11.json', 'r') as f:
        stock_data = json.load(f)

    current_product = None
    for item in stock_data:
        if item.get('Product ID'):
            current_product = item['Product ID'].strip()
        elif current_product and item.get('Stock item description'):
            if 'TOTAL' not in item.get('Stock item description', ''):
                qoh = int(item.get('Units\nQoH', 0) or 0)
                packed = int(item.get('Units\nPacked', 0) or 0)
                transit = int(item.get('Units\nTransit', 0) or 0)
                wip = int(item.get('Units\nWIP', 0) or 0)

                total_qty = qoh + packed + transit + wip
                if total_qty > 0:
                    active_products.add(current_product)

    products_with_stock = len(active_products)
    print(f"   ✓ Found {products_with_stock} products with current stock")

    # 2. Products with stock history
    print("   Checking stock history...")
    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/StockHistoryTransactionDetails-Oct11.json', 'r') as f:
        history_data = json.load(f)

    initial_count = len(active_products)
    for item in history_data:
        if item.get('Product ID'):
            product_id = item['Product ID'].strip()
            if product_id:
                active_products.add(product_id)

    products_with_history = len(active_products) - initial_count
    print(f"   ✓ Found {products_with_history} additional products with stock history")

    # 3. Products in purchase orders
    print("   Checking purchase orders...")
    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/PurchaseOrderWDetail-Oct11.json', 'r') as f:
        po_data = json.load(f)

    initial_count = len(active_products)
    for item in po_data:
        if item.get('Product ID'):
            product_id = item['Product ID'].strip()
            if product_id:
                active_products.add(product_id)

    products_in_orders = len(active_products) - initial_count
    print(f"   ✓ Found {products_in_orders} additional products in purchase orders")

    print(f"\n✅ Total active products identified: {len(active_products)}")
    return active_products

def load_goparts_categories():
    """Load category mappings from goparts database"""
    print("\n📄 Loading categories from goparts database...")

    conn = get_db_connection('goparts')
    cursor = conn.cursor()

    # Get partslink to category mappings
    cursor.execute("""
        SELECT DISTINCT
            p.partslink,
            p.category_id,
            c.category,
            c.category_singular_name
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.category_id
        WHERE p.partslink IS NOT NULL
        AND p.partslink != ''
        AND p.category_id != 'OGP'
    """)

    partslink_categories = {}
    for partslink, category_id, category, category_name in cursor.fetchall():
        if partslink:
            # Clean up category name
            if category:
                category = category.strip().replace('\r', '').replace('\n', '')
            partslink_categories[partslink] = {
                'category_id': category_id,
                'category': category,
                'category_name': category_name
            }

    cursor.close()
    conn.close()

    print(f"✅ Loaded {len(partslink_categories)} partslink-category mappings")
    return partslink_categories

def import_active_products():
    """Import only active products with category lookup"""

    # First, identify active products
    active_products = identify_active_products()

    # Load category mappings
    partslink_categories = load_goparts_categories()

    # Load and filter products from CSV
    print("\n📄 Loading products from ProductListScreen.csv...")
    products_to_import = []
    total_in_csv = 0
    excluded_count = 0

    csv_path = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/ProductListScreenReport-Oct11.csv'
    with open(csv_path, 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            product_id = row.get('Product ID', '').strip()
            if product_id:
                total_in_csv += 1

                # Check if product is active
                if product_id in active_products:
                    products_to_import.append({
                        'product_id': product_id,
                        'partslink': row.get('partslink', '').strip(),
                        'supplier': row.get('supplier', '').strip(),
                        'supplier_part_number': row.get('supplier_partnumber', '').strip(),
                        'warehouse': row.get('warehouse', '').strip(),
                        'warehouse_location': row.get('warehouse_location', '').strip(),
                        'average_cost': row.get('Average cost', '').strip(),
                        'description': row.get('Description', '').strip(),
                        'status': row.get('Product Status', '').strip()
                    })
                else:
                    excluded_count += 1

    print(f"   Total products in CSV: {total_in_csv}")
    print(f"   Products to import (active): {len(products_to_import)}")
    print(f"   Products excluded (inactive): {excluded_count}")

    # Connect to whims_dev database
    conn = get_db_connection('whims_dev')
    cursor = conn.cursor()

    print("\n🔄 Importing active products to database...")

    # Track statistics
    inserted = 0
    updated = 0
    with_category = 0
    without_category = 0
    category_distribution = {}

    for product in products_to_import:
        try:
            # Look up category from partslink
            part_type = None
            if product['partslink'] and product['partslink'] in partslink_categories:
                category_info = partslink_categories[product['partslink']]
                # Use the category name as part_type
                part_type = category_info['category']
                with_category += 1

                # Track distribution
                if part_type:
                    category_distribution[part_type] = category_distribution.get(part_type, 0) + 1
            else:
                # If partslink not found, set as ValueLine
                part_type = 'ValueLine'
                without_category += 1
                category_distribution['ValueLine'] = category_distribution.get('ValueLine', 0) + 1

            # Parse average cost
            avg_cost = 0.00
            if product['average_cost']:
                try:
                    avg_cost = float(product['average_cost'])
                except (ValueError, TypeError):
                    avg_cost = 0.00

            # Determine status
            status = 'active' if product['status'].lower() == 'active' else 'inactive'

            # Check if product exists
            cursor.execute("""
                SELECT id FROM products WHERE product_id = %s
            """, (product['product_id'],))

            existing = cursor.fetchone()

            if existing:
                # Update existing product
                cursor.execute("""
                    UPDATE products
                    SET partslink = %s,
                        supplier = %s,
                        supplier_part_number = %s,
                        part_type = %s,
                        average_price = %s,
                        status = %s,
                        updated_at = %s
                    WHERE product_id = %s
                """, (
                    product['partslink'] or None,
                    product['supplier'] or None,
                    product['supplier_part_number'] or None,
                    part_type,
                    avg_cost,
                    status,
                    datetime.now(),
                    product['product_id']
                ))
                updated += 1
            else:
                # Insert new product
                cursor.execute("""
                    INSERT INTO products (
                        product_id,
                        partslink,
                        supplier,
                        supplier_part_number,
                        part_type,
                        average_price,
                        status,
                        created_at,
                        updated_at
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (
                    product['product_id'],
                    product['partslink'] or None,
                    product['supplier'] or None,
                    product['supplier_part_number'] or None,
                    part_type,
                    avg_cost,
                    status,
                    datetime.now(),
                    datetime.now()
                ))
                inserted += 1

            if (inserted + updated) % 100 == 0:
                print(f"   Processed {inserted + updated} products...")
                conn.commit()

        except Exception as e:
            print(f"   ⚠️ Error processing product {product['product_id']}: {str(e)}")

    conn.commit()

    print(f"\n✅ Active product import completed!")
    print(f"   Products inserted: {inserted}")
    print(f"   Products updated: {updated}")
    print(f"   Products with category from goparts: {with_category}")
    print(f"   Products without goparts match (set as ValueLine): {without_category}")
    print(f"   Inactive products excluded: {excluded_count}")

    # Show top categories
    if category_distribution:
        print(f"\n📊 Top 10 Categories Assigned:")
        sorted_categories = sorted(category_distribution.items(), key=lambda x: x[1], reverse=True)
        for category, count in sorted_categories[:10]:
            print(f"   {category}: {count}")

    # Verify final statistics
    cursor.execute("""
        SELECT
            COUNT(*) as total,
            SUM(CASE WHEN part_type IS NOT NULL AND part_type != '' THEN 1 ELSE 0 END) as with_type,
            SUM(CASE WHEN partslink IS NOT NULL AND partslink != '' THEN 1 ELSE 0 END) as with_partslink,
            SUM(CASE WHEN supplier IS NOT NULL AND supplier != '' THEN 1 ELSE 0 END) as with_supplier
        FROM products
    """)

    result = cursor.fetchone()
    print(f"\n📊 Final Database Statistics:")
    print(f"   Total products: {result[0]}")
    print(f"   With part type: {result[1]} ({(result[1]/result[0]*100):.1f}%)")
    print(f"   With partslink: {result[2]} ({(result[2]/result[0]*100):.1f}%)")
    print(f"   With supplier: {result[3]} ({(result[3]/result[0]*100):.1f}%)")

    cursor.close()
    conn.close()

if __name__ == "__main__":
    import_active_products()