#!/usr/bin/env python3
"""
Migrate stock history from Finale Inventory exports
"""
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():
    """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 parse_date(date_str):
    """Parse date from M/D/YYYY format to MySQL format"""
    if not date_str:
        return None

    try:
        parts = date_str.split('/')
        if len(parts) == 3:
            month, day, year = parts
            if len(year) == 2:
                year = '20' + year
            return datetime(int(year), int(month), int(day))
    except:
        pass

    return datetime.now()

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()

    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 'GO-PARTS' in sublocation or 'GA' in sublocation:
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])
    else:
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])

def map_transaction_type(transaction_desc):
    """Map Finale transaction description to action type"""
    if not transaction_desc:
        return 'adjustment'

    transaction_desc = transaction_desc.lower()

    if 'quick stock change' in transaction_desc:
        return 'adjustment'
    elif 'stock take' in transaction_desc:
        return 'stock_take'
    elif 'receive' in transaction_desc or 'receipt' in transaction_desc:
        return 'purchase_receive'
    elif 'sale' in transaction_desc or 'sold' in transaction_desc:
        return 'sale'
    elif 'transfer' in transaction_desc:
        return 'transfer'
    elif 'return' in transaction_desc:
        return 'return'
    else:
        return 'adjustment'

def extract_stock_history(limit=None):
    """Extract stock history from JSON file"""
    print("📄 Loading stock history data...")

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

    print(f"   Total records in file: {len(history_data)}")

    if limit:
        history_data = history_data[:limit]
        print(f"   Processing first {limit} records")

    transactions = []
    for item in history_data:
        if item.get('Product ID') and item.get('Qty'):
            qty = int(float(item.get('Qty', 0)))
            if qty != 0:  # Only process non-zero quantity changes
                transactions.append({
                    'record_date': parse_date(item.get('Record date')),
                    'product_id': item['Product ID'].strip(),
                    'sublocation': (item.get('Sublocation') or '').strip(),
                    'transaction_desc': item.get('Transaction description') or '',
                    'stock_type': item.get('Stock\ntype') or 'In stock',
                    'quantity': qty,
                    'lot_id': item.get('Lot ID'),
                    'details': item.get('Transaction details') or ''
                })

    print(f"✅ Extracted {len(transactions)} valid transactions")
    return transactions

def migrate_stock_history(batch_size=1000, limit=None):
    """Migrate stock history to database"""
    conn = get_db_connection()
    cursor = conn.cursor()

    # Get warehouse mappings 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}")

    transactions = extract_stock_history(limit)

    print(f"\n🔄 Migrating stock history (batch size: {batch_size})...")

    # Get product ID mappings
    cursor.execute("SELECT id, product_id FROM products")
    product_map = {row[1]: row[0] for row in cursor.fetchall()}
    print(f"   Loaded {len(product_map)} product mappings")

    # Get location mappings
    cursor.execute("""
        SELECT l.id, l.location, l.warehouse_id
        FROM locations l
    """)
    location_map = {}
    for loc_id, loc_code, warehouse_id in cursor.fetchall():
        location_map[f"{warehouse_id}_{loc_code}"] = loc_id

    print(f"   Loaded {len(location_map)} location mappings")

    successful = 0
    failed = 0
    skipped_no_product = 0
    batch_data = []

    for i, trans in enumerate(transactions):
        # Look up product ID
        if trans['product_id'] not in product_map:
            skipped_no_product += 1
            continue

        product_id_value = product_map[trans['product_id']]
        warehouse_id = determine_warehouse_from_sublocation(trans['sublocation'], warehouse_map)

        # Look up location ID
        location_key = f"{warehouse_id}_{trans['sublocation']}"
        if location_key in location_map:
            location_id = location_map[location_key]
        else:
            # Create default location if not found
            location_id = 1  # Default location

        # Map action type
        action_type = map_transaction_type(trans['transaction_desc'])

        # Prepare batch data
        batch_data.append((
            product_id_value,
            warehouse_id,
            location_id,
            action_type,
            0,  # quantity_before (would need calculation)
            trans['quantity'],  # quantity_change
            trans['quantity'],  # quantity_after (simplified)
            None,  # unit_price
            'finale_import',  # reference_type
            None,  # reference_id
            None,  # user_id
            trans['details'],  # notes
            trans['record_date']  # created_at
        ))

        # Insert batch when full
        if len(batch_data) >= batch_size:
            try:
                cursor.executemany("""
                    INSERT INTO stock_history (
                        product_id,
                        warehouse_id,
                        location_id,
                        action_type,
                        quantity_before,
                        quantity_change,
                        quantity_after,
                        unit_price,
                        reference_type,
                        reference_id,
                        user_id,
                        notes,
                        created_at
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, batch_data)

                successful += len(batch_data)
                conn.commit()

                print(f"   Processed {successful} records...")
                batch_data = []

            except Exception as e:
                failed += len(batch_data)
                print(f"   ⚠️ Batch insert failed: {str(e)}")
                batch_data = []

    # Insert remaining records
    if batch_data:
        try:
            cursor.executemany("""
                INSERT INTO stock_history (
                    product_id,
                    warehouse_id,
                    location_id,
                    action_type,
                    quantity_before,
                    quantity_change,
                    quantity_after,
                    unit_price,
                    reference_type,
                    reference_id,
                    user_id,
                    notes,
                    created_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, batch_data)

            successful += len(batch_data)
            conn.commit()

        except Exception as e:
            failed += len(batch_data)
            print(f"   ⚠️ Final batch insert failed: {str(e)}")

    print(f"\n✅ Stock history migration completed!")
    print(f"   Successful: {successful}")
    print(f"   Failed: {failed}")
    print(f"   Skipped (no product): {skipped_no_product}")

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

    cursor.execute("SELECT COUNT(DISTINCT product_id) FROM stock_history")
    product_count = cursor.fetchone()[0]

    cursor.execute("""
        SELECT action_type, COUNT(*) as count
        FROM stock_history
        GROUP BY action_type
        ORDER BY count DESC
    """)
    action_types = cursor.fetchall()

    print(f"\n📊 Database totals:")
    print(f"   History records: {history_count}")
    print(f"   Unique products: {product_count}")
    print(f"   Action types:")
    for action_type, count in action_types:
        print(f"      {action_type}: {count}")

    cursor.close()
    conn.close()

    return successful

if __name__ == "__main__":
    # Import ALL stock history records
    migrate_stock_history(batch_size=1000, limit=None)

    print("\n✅ Note: All stock history records have been imported.")