#!/usr/bin/env python3
"""
Migrate purchase orders 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:
        # Parse M/D/YYYY format
        parts = date_str.split('/')
        if len(parts) == 3:
            month, day, year = parts
            # Handle 2-digit year
            if len(year) == 2:
                year = '20' + year
            return f"{year}-{month.zfill(2)}-{day.zfill(2)}"
    except:
        pass

    return None

def determine_warehouse(order_id, warehouse_map):
    """Determine warehouse ID based on order ID pattern"""
    if not order_id:
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])

    order_id = order_id.upper()

    # Map order ID patterns to warehouses
    if '-GA-' in order_id or order_id.endswith('-GA'):
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])
    elif '-SP' in order_id or 'SIMPLEPRICE' in order_id:
        return warehouse_map.get('SimplePrice', list(warehouse_map.values())[0])
    elif '-TDI' in order_id or 'TODAY' in order_id:
        return warehouse_map.get('Today Delivery INC', list(warehouse_map.values())[0])
    elif '-RF' in order_id or 'RAPID' in order_id:
        return warehouse_map.get('RapidFulfillment', list(warehouse_map.values())[0])
    else:
        return warehouse_map.get('Go-Parts GA', list(warehouse_map.values())[0])

def extract_purchase_orders(warehouse_map):
    """Extract purchase orders from JSON file"""
    print("📄 Loading purchase order data...")

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

    orders = {}
    current_order = None

    for item in po_data:
        if item.get('Order ID') and item.get('Order date'):
            # New order header
            current_order = item['Order ID']
            orders[current_order] = {
                'po_number': item['Order ID'],
                'order_date': parse_date(item['Order date']),
                'status': item.get('Status', 'Completed'),
                'warehouse_id': determine_warehouse(item['Order ID'], warehouse_map),
                'items': [],
                'subtotal': 0.0
            }
        elif current_order and item.get('Product ID'):
            # Order line item
            try:
                qty = item.get('Quantity', 0)
                if qty and str(qty).strip() and str(qty).strip() != ' ':
                    qty = int(float(str(qty).strip()))
                else:
                    qty = 0

                unit_price = item.get('Unit price', 0)
                if unit_price and str(unit_price).strip():
                    unit_price = float(str(unit_price).strip())
                else:
                    unit_price = 0.0

                total = item.get('Subtotal\nsum', 0)
                if total and str(total).strip():
                    total = float(str(total).strip())
                else:
                    total = 0.0

                orders[current_order]['items'].append({
                    'product_id': item['Product ID'].strip(),
                    'quantity': qty,
                    'unit_price': unit_price,
                    'total_price': total,
                    'category': item.get('Category'),
                    'item_index': item.get('Item index')
                })
            except (ValueError, AttributeError) as e:
                print(f"   ⚠️ Skipping invalid item: {e}")

    print(f"✅ Found {len(orders)} purchase orders")
    return orders

def migrate_purchase_orders():
    """Migrate purchase orders to database"""
    conn = get_db_connection()
    cursor = conn.cursor()

    # First, get warehouse mappings
    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}")

    orders = extract_purchase_orders(warehouse_map)

    print("\n🔄 Migrating purchase orders...")

    # 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")

    successful_orders = 0
    failed_orders = 0
    successful_items = 0
    failed_items = 0

    for po_number, order_data in orders.items():
        try:
            # Ensure warehouse exists
            warehouse_id = order_data['warehouse_id']
            if warehouse_id not in warehouses:
                warehouse_id = list(warehouses.keys())[0]  # Default to first warehouse

            # Map status
            status = 'received' if order_data['status'] == 'Completed' else 'draft'

            # Insert purchase order
            cursor.execute("""
                INSERT INTO purchase_orders (
                    po_number,
                    warehouse_id,
                    order_date,
                    received_date,
                    status,
                    is_committed,
                    is_reversed,
                    committed_at,
                    subtotal,
                    tax_amount,
                    shipping_cost,
                    total_amount,
                    grand_total,
                    created_at,
                    updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                po_number,
                warehouse_id,
                order_data['order_date'] or datetime.now().date(),
                order_data['order_date'] if status == 'received' else None,
                status,
                1 if status == 'received' else 0,  # is_committed
                0,  # is_reversed
                datetime.now() if status == 'received' else None,  # committed_at
                order_data['subtotal'],  # subtotal
                0.0,  # tax_amount
                0.0,  # shipping_cost
                order_data['subtotal'],  # total_amount
                order_data['subtotal'],  # grand_total
                datetime.now(),
                datetime.now()
            ))

            po_id = cursor.lastrowid
            successful_orders += 1

            # Insert purchase order items
            for item in order_data['items']:
                try:
                    # Look up product ID
                    product_id_value = None
                    if item['product_id'] in product_map:
                        product_id_value = product_map[item['product_id']]

                    cursor.execute("""
                        INSERT INTO purchase_order_items (
                            purchase_order_id,
                            product_id,
                            product_id_raw,
                            category,
                            quantity,
                            unit,
                            unit_price,
                            total_price,
                            is_return,
                            is_valid,
                            warehouse_id,
                            created_at,
                            updated_at
                        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """, (
                        po_id,
                        product_id_value,
                        item['product_id'],
                        item.get('category'),
                        item['quantity'],
                        'EA',  # Default unit
                        item['unit_price'],
                        item['total_price'],
                        0,  # is_return
                        1 if product_id_value else 0,  # is_valid
                        warehouse_id,
                        datetime.now(),
                        datetime.now()
                    ))
                    successful_items += 1

                except Exception as e:
                    failed_items += 1
                    print(f"   ⚠️ Failed to insert item {item['product_id']}: {str(e)}")

            if successful_orders % 10 == 0:
                print(f"   Processed {successful_orders} orders...")

        except Exception as e:
            failed_orders += 1
            print(f"   ❌ Failed to insert order {po_number}: {str(e)}")

    conn.commit()

    print(f"\n✅ Purchase order migration completed!")
    print(f"   Orders - Successful: {successful_orders}, Failed: {failed_orders}")
    print(f"   Items - Successful: {successful_items}, Failed: {failed_items}")

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

    cursor.execute("SELECT COUNT(*) FROM purchase_order_items")
    item_count = cursor.fetchone()[0]

    print(f"\n📊 Database totals:")
    print(f"   Purchase Orders: {po_count}")
    print(f"   Purchase Order Items: {item_count}")

    cursor.close()
    conn.close()

    return successful_orders

if __name__ == "__main__":
    migrate_purchase_orders()