#!/usr/bin/env python3
"""
Migrate stock/inventory from Finale Inventory exports
"""
import json
import csv
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():
    """Create database connection"""
    config = load_env_config()

    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 determine_warehouse_from_sublocation(sublocation, warehouse_map):
    """Determine warehouse ID based on sublocation name"""
    if not sublocation:
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])

    sublocation = sublocation.upper()

    # Check for specific warehouse names in sublocation
    if 'SIMPLEPRICE' in sublocation:
        return warehouse_map.get('SimplePrice', list(warehouse_map.values())[0])
    elif 'TODAY' in sublocation or 'TDI' in sublocation:
        return warehouse_map.get('Today Delivery INC', list(warehouse_map.values())[0])
    elif 'RAPID' in sublocation:
        return warehouse_map.get('RapidFulfillment', list(warehouse_map.values())[0])
    elif 'PARTS AUTHORITY' in sublocation or 'NFS' in sublocation:
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])
    elif 'GO-PARTS' in sublocation or 'GA' in sublocation:
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])
    else:
        # Default logic: numeric or alphanumeric locations go to main warehouse
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])

def load_average_costs_from_csv():
    """Load average costs from ProductListScreen CSV"""
    print("📄 Loading average costs from CSV...")

    csv_path = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/ProductListScreenReport-Oct11.csv'
    costs = {}

    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:
                avg_cost_str = row.get('Average cost', '')
                try:
                    costs[product_id] = float(str(avg_cost_str).replace(',', '').replace('$', '').strip()) if avg_cost_str else 0.0
                except:
                    costs[product_id] = 0.0

    print(f"✅ Loaded costs for {len(costs)} products")
    return costs

def extract_stock_data(warehouse_map):
    """Extract stock data from JSON file"""
    print("📄 Loading stock quantity data...")

    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/StockQuantityBySublocationInUnitsWDetail-Oct11.json', 'r') as f:
        stock_data = json.load(f)

    stocks = []
    current_sublocation = None
    current_product = None

    for item in stock_data:
        if item.get('Sublocation'):
            # New sublocation section
            current_sublocation = item['Sublocation'].strip()

        elif item.get('Product ID'):
            # New product in current sublocation
            current_product = item['Product ID'].strip()

        elif current_sublocation and current_product and item.get('Stock item description'):
            # Stock details for current product/sublocation
            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)

                # Only add if there's actual stock
                total_qty = qoh + packed + transit + wip
                if total_qty > 0:
                    stocks.append({
                        'product_id': current_product,
                        'sublocation': current_sublocation,
                        'warehouse_id': determine_warehouse_from_sublocation(current_sublocation, warehouse_map),
                        'quantity_on_hand': qoh,
                        'quantity_packed': packed,
                        'quantity_transit': transit,
                        'quantity_wip': wip,
                        'total_quantity': total_qty
                    })

    print(f"✅ Found {len(stocks)} stock records with positive quantities")
    return stocks

def create_or_get_location(cursor, warehouse_id, location_code):
    """Create or get location ID for a given location code"""
    # Check if location exists
    cursor.execute("""
        SELECT id FROM locations
        WHERE warehouse_id = %s AND location = %s
    """, (warehouse_id, location_code))

    result = cursor.fetchone()
    if result:
        return result[0]

    # Create new location
    location_type = 'storage'
    description = f"Location {location_code}"

    # Determine location type based on code pattern
    if location_code.upper().startswith('BOX'):
        location_type = 'box'
        description = f"Box storage {location_code}"
    elif 'RADIATOR' in location_code.upper():
        location_type = 'specialty'
        description = "Radiator storage area"
    elif 'LIGHTS' in location_code.upper() or 'SPARK' in location_code.upper():
        location_type = 'specialty'
        description = "Lights and spark plugs area"
    elif any(c.isdigit() for c in location_code):
        if '.' in location_code:
            location_type = 'shelf'
            description = f"Shelf {location_code}"
        else:
            location_type = 'bin'
            description = f"Bin {location_code}"

    cursor.execute("""
        INSERT INTO locations (
            warehouse_id,
            location,
            description,
            type,
            is_active,
            created_at,
            updated_at
        ) VALUES (%s, %s, %s, %s, %s, %s, %s)
    """, (
        warehouse_id,
        location_code,
        description,
        location_type,
        1,  # is_active
        datetime.now(),
        datetime.now()
    ))

    return cursor.lastrowid

def migrate_stock_inventory():
    """Migrate stock inventory to database"""
    conn = get_db_connection()
    cursor = conn.cursor()

    # Get warehouse info first
    cursor.execute("SELECT id, name FROM warehouses")
    warehouse_rows = cursor.fetchall()
    warehouses = {row[0]: row[1] for row in warehouse_rows}
    warehouse_map = {row[1]: row[0] for row in warehouse_rows}  # name -> id mapping
    print(f"   Available warehouses: {warehouses}")

    # Load average costs from CSV
    average_costs = load_average_costs_from_csv()

    stocks = extract_stock_data(warehouse_map)

    print("\n🔄 Migrating stock inventory...")

    # Get product ID mappings (case-insensitive)
    cursor.execute("SELECT id, product_id FROM products")
    product_map = {}
    product_map_lower = {}
    for row_id, row_product_id in cursor.fetchall():
        product_map[row_product_id] = row_id
        product_map_lower[row_product_id.lower()] = row_id
    print(f"   Loaded {len(product_map)} product mappings")

    successful = 0
    failed = 0
    skipped_no_product = 0
    location_cache = {}

    for stock in stocks:
        try:
            # Look up product ID (try exact match first, then case-insensitive)
            if stock['product_id'] in product_map:
                product_id_value = product_map[stock['product_id']]
            elif stock['product_id'].lower() in product_map_lower:
                product_id_value = product_map_lower[stock['product_id'].lower()]
            else:
                skipped_no_product += 1
                continue

            warehouse_id = stock['warehouse_id']

            # Get or create location
            location_key = f"{warehouse_id}_{stock['sublocation']}"
            if location_key not in location_cache:
                location_cache[location_key] = create_or_get_location(
                    cursor, warehouse_id, stock['sublocation']
                )
            location_id = location_cache[location_key]

            # Get average cost from CSV
            avg_cost = average_costs.get(stock['product_id'], 0.0)

            # Insert stock record (use quantity_on_hand as the main quantity)
            cursor.execute("""
                INSERT INTO stocks (
                    product_id,
                    warehouse_id,
                    location_id,
                    sub_location,
                    quantity,
                    reserved_quantity,
                    average_price,
                    is_selling,
                    `condition`,
                    last_counted_at,
                    created_at,
                    updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    quantity = quantity + VALUES(quantity),
                    average_price = VALUES(average_price),
                    updated_at = VALUES(updated_at)
            """, (
                product_id_value,
                warehouse_id,
                location_id,
                stock['sublocation'],
                stock['quantity_on_hand'],  # Use on-hand quantity
                0,  # reserved_quantity
                avg_cost,  # average_price from CSV
                1,  # is_selling
                'new',  # condition
                datetime.now().date(),  # last_counted_at
                datetime.now(),
                datetime.now()
            ))

            successful += 1

            if successful % 100 == 0:
                print(f"   Processed {successful} stock records...")

        except Exception as e:
            failed += 1
            print(f"   ⚠️ Failed to insert stock for {stock['product_id']} at {stock['sublocation']}: {str(e)}")

    conn.commit()

    print(f"\n✅ Stock inventory migration completed!")
    print(f"   Successful: {successful}")
    print(f"   Failed: {failed}")
    print(f"   Skipped (no product): {skipped_no_product}")
    print(f"   Locations created/used: {len(location_cache)}")
    print(f"   Using average costs from Finale CSV")

    # Verify migration
    cursor.execute("SELECT COUNT(*) FROM stocks")
    stock_count = cursor.fetchone()[0]

    cursor.execute("SELECT COUNT(*) FROM locations")
    location_count = cursor.fetchone()[0]

    cursor.execute("SELECT SUM(quantity) FROM stocks")
    total_quantity = cursor.fetchone()[0] or 0

    print(f"\n📊 Database totals:")
    print(f"   Stock records: {stock_count}")
    print(f"   Locations: {location_count}")
    print(f"   Total quantity: {total_quantity}")

    cursor.close()
    conn.close()

    return successful

if __name__ == "__main__":
    migrate_stock_inventory()