#!/usr/bin/env python3
"""
Comprehensive Migration Validation Script

This script validates the complete migration from Finale to WHIMS by:
1. Comparing stock quantities (total and per sublocation)
2. Validating prices from purchase orders
3. Checking product data completeness
4. Verifying purchase order migration
5. Validating stock history

Usage: python3 09_comprehensive_migration_validation.py
"""

import json
import mysql.connector
from decimal import Decimal
from collections import defaultdict
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 connect_to_db():
    """Connect to MySQL database"""
    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')
    )

# ============================================================================
# STOCK QUANTITY VALIDATION
# ============================================================================

def extract_finale_stock_data():
    """Extract stock quantities from Finale JSON - per product and per sublocation"""
    print("\n" + "="*80)
    print("   STOCK QUANTITY VALIDATION")
    print("="*80)
    print("\n📄 Reading stock data from Finale JSON...")

    stock_file = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/StockQuantityBySublocationInUnitsWDetail-Oct11.json'

    with open(stock_file, 'r') as f:
        stock_data = json.load(f)

    # Structure: product_id -> { total_qty, locations: { sublocation: qty } }
    finale_stock = {}
    current_product = None
    current_sublocation = None

    for item in stock_data:
        # Track sublocation changes
        if item.get('Sublocation') and not item.get('Product ID'):
            current_sublocation = item['Sublocation'].strip()

        # Track product ID changes
        if item.get('Product ID'):
            current_product = item['Product ID'].strip()
            if current_product not in finale_stock:
                finale_stock[current_product] = {
                    'total_qty': 0,
                    'locations': {}
                }

        # Process stock quantities
        if current_product and item.get('Stock item description'):
            description = (item.get('Stock item description') or '').strip()

            # Skip TOTAL rows
            if 'TOTAL' in description:
                continue

            # Calculate total quantity for this location
            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 and current_sublocation:
                finale_stock[current_product]['total_qty'] += total_qty

                if current_sublocation in finale_stock[current_product]['locations']:
                    finale_stock[current_product]['locations'][current_sublocation] += total_qty
                else:
                    finale_stock[current_product]['locations'][current_sublocation] = total_qty

    # Filter to only products with stock
    finale_stock = {k: v for k, v in finale_stock.items() if v['total_qty'] > 0}

    total_products = len(finale_stock)
    total_qty = sum(p['total_qty'] for p in finale_stock.values())
    total_locations = sum(len(p['locations']) for p in finale_stock.values())

    print(f"   ✓ Products with stock: {total_products:,}")
    print(f"   ✓ Total stock quantity: {total_qty:,}")
    print(f"   ✓ Total location entries: {total_locations:,}")

    return finale_stock

def get_database_stock_data():
    """Get stock quantities from database - per product and per location"""
    print("\n📄 Reading stock data from database...")

    conn = connect_to_db()
    cursor = conn.cursor(dictionary=True)

    # Get stock data with location details
    cursor.execute("""
        SELECT
            p.product_id,
            l.location as location_name,
            s.quantity,
            w.name as warehouse_name
        FROM stocks s
        JOIN products p ON s.product_id = p.id
        JOIN locations l ON s.location_id = l.id
        JOIN warehouses w ON l.warehouse_id = w.id
        WHERE s.quantity > 0
    """)

    db_stock = {}
    for row in cursor.fetchall():
        product_id = row['product_id']
        location = row['location_name']
        qty = int(row['quantity'])

        if product_id not in db_stock:
            db_stock[product_id] = {
                'total_qty': 0,
                'locations': {}
            }

        db_stock[product_id]['total_qty'] += qty

        if location in db_stock[product_id]['locations']:
            db_stock[product_id]['locations'][location] += qty
        else:
            db_stock[product_id]['locations'][location] = qty

    total_products = len(db_stock)
    total_qty = sum(p['total_qty'] for p in db_stock.values())
    total_locations = sum(len(p['locations']) for p in db_stock.values())

    print(f"   ✓ Products with stock: {total_products:,}")
    print(f"   ✓ Total stock quantity: {total_qty:,}")
    print(f"   ✓ Total location entries: {total_locations:,}")

    cursor.close()
    conn.close()

    return db_stock

def validate_stock_quantities(finale_stock, db_stock):
    """Validate stock quantities match between Finale and database"""
    print("\n🔍 Validating stock quantities...")

    issues = {
        'missing_products': [],
        'qty_mismatch': [],
        'location_mismatch': [],
        'extra_products': []
    }

    perfect_matches = 0

    # Check Finale products in database
    for product_id, finale_data in finale_stock.items():
        if product_id not in db_stock:
            issues['missing_products'].append({
                'product_id': product_id,
                'finale_qty': finale_data['total_qty']
            })
            continue

        db_data = db_stock[product_id]

        # Check total quantity
        if finale_data['total_qty'] != db_data['total_qty']:
            issues['qty_mismatch'].append({
                'product_id': product_id,
                'finale_qty': finale_data['total_qty'],
                'db_qty': db_data['total_qty'],
                'difference': finale_data['total_qty'] - db_data['total_qty']
            })
        else:
            # Check if location breakdown also matches
            finale_locs = set(finale_data['locations'].keys())
            db_locs = set(db_data['locations'].keys())

            if finale_locs == db_locs:
                # Check quantities per location
                loc_match = True
                for loc in finale_locs:
                    if finale_data['locations'][loc] != db_data['locations'][loc]:
                        loc_match = False
                        break

                if loc_match:
                    perfect_matches += 1
                else:
                    issues['location_mismatch'].append({
                        'product_id': product_id,
                        'finale_locations': finale_data['locations'],
                        'db_locations': db_data['locations']
                    })
            else:
                issues['location_mismatch'].append({
                    'product_id': product_id,
                    'finale_locations': finale_data['locations'],
                    'db_locations': db_data['locations']
                })

    # Check for extra products in database
    for product_id in db_stock:
        if product_id not in finale_stock:
            issues['extra_products'].append({
                'product_id': product_id,
                'db_qty': db_stock[product_id]['total_qty']
            })

    # Print results
    print(f"\n✅ STOCK VALIDATION RESULTS:")
    print(f"   Perfect matches (total + locations): {perfect_matches:,}")
    print(f"   Missing products in DB: {len(issues['missing_products']):,}")
    print(f"   Quantity mismatches: {len(issues['qty_mismatch']):,}")
    print(f"   Location mismatches: {len(issues['location_mismatch']):,}")
    print(f"   Extra products in DB: {len(issues['extra_products']):,}")

    if issues['missing_products']:
        print(f"\n⚠️  MISSING PRODUCTS (first 10):")
        for item in issues['missing_products'][:10]:
            print(f"   - {item['product_id']}: Finale qty={item['finale_qty']}")

    if issues['qty_mismatch']:
        print(f"\n⚠️  QUANTITY MISMATCHES (first 10):")
        for item in issues['qty_mismatch'][:10]:
            print(f"   - {item['product_id']}: Finale={item['finale_qty']}, DB={item['db_qty']}, Diff={item['difference']}")

    if issues['location_mismatch']:
        print(f"\n⚠️  LOCATION MISMATCHES (first 5):")
        for item in issues['location_mismatch'][:5]:
            print(f"   - {item['product_id']}:")
            print(f"     Finale locations: {list(item['finale_locations'].keys())[:5]}")
            print(f"     DB locations: {list(item['db_locations'].keys())[:5]}")

    return issues

# ============================================================================
# PRICE VALIDATION
# ============================================================================

def extract_finale_price_data():
    """Extract price data from Finale purchase orders"""
    print("\n" + "="*80)
    print("   PRICE VALIDATION")
    print("="*80)
    print("\n📄 Reading price data from Finale purchase orders...")

    po_file = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/PurchaseOrderWDetail-Oct11.json'

    with open(po_file, 'r') as f:
        po_data = json.load(f)

    # Structure: product_id -> [prices]
    finale_prices = defaultdict(list)
    po_items_count = 0
    current_po = None

    for item in po_data:
        # Track PO number changes
        if item.get('Order ID'):
            current_po = item['Order ID'].strip()

        product_id = (item.get('Product ID') or '').strip()
        if not product_id or not current_po:
            continue

        unit_price = item.get('Unit price')
        if unit_price:
            try:
                price = float(str(unit_price).replace('$', '').replace(',', '').strip())
                if price > 0:
                    finale_prices[product_id].append(price)
                    po_items_count += 1
            except:
                pass

    print(f"   ✓ Products with prices: {len(finale_prices):,}")
    print(f"   ✓ Total PO line items: {po_items_count:,}")

    # Calculate average prices
    finale_avg_prices = {}
    for product_id, prices in finale_prices.items():
        finale_avg_prices[product_id] = sum(prices) / len(prices)

    return finale_prices, finale_avg_prices

def get_database_price_data():
    """Get price data from database"""
    print("\n📄 Reading price data from database...")

    conn = connect_to_db()
    cursor = conn.cursor(dictionary=True)

    # Get product average prices
    cursor.execute("""
        SELECT
            product_id,
            average_price
        FROM products
        WHERE average_price IS NOT NULL AND average_price > 0
    """)

    db_product_prices = {row['product_id']: float(row['average_price']) for row in cursor.fetchall()}

    # Get prices from purchase order items
    cursor.execute("""
        SELECT
            p.product_id,
            poi.unit_price
        FROM purchase_order_items poi
        JOIN products p ON poi.product_id = p.id
        WHERE poi.unit_price > 0
    """)

    db_po_prices = defaultdict(list)
    for row in cursor.fetchall():
        db_po_prices[row['product_id']].append(float(row['unit_price']))

    # Calculate average prices from POs
    db_avg_prices = {}
    for product_id, prices in db_po_prices.items():
        db_avg_prices[product_id] = sum(prices) / len(prices)

    print(f"   ✓ Products with prices: {len(db_avg_prices):,}")
    print(f"   ✓ Total PO line items: {sum(len(p) for p in db_po_prices.values()):,}")

    cursor.close()
    conn.close()

    return db_po_prices, db_avg_prices, db_product_prices

def validate_prices(finale_prices, finale_avg_prices, db_po_prices, db_avg_prices, db_product_prices):
    """Validate prices match between Finale and database"""
    print("\n🔍 Validating prices...")

    issues = {
        'missing_products': [],
        'avg_price_mismatch': [],
        'po_count_mismatch': []
    }

    perfect_matches = 0
    close_matches = 0  # Within 1% tolerance

    for product_id, finale_avg in finale_avg_prices.items():
        if product_id not in db_avg_prices:
            issues['missing_products'].append({
                'product_id': product_id,
                'finale_avg': round(finale_avg, 2)
            })
            continue

        db_avg = db_avg_prices[product_id]
        diff_pct = abs(finale_avg - db_avg) / finale_avg * 100 if finale_avg > 0 else 0

        if diff_pct < 0.01:  # Perfect match (less than 0.01% difference)
            perfect_matches += 1
        elif diff_pct < 1.0:  # Close match (less than 1% difference)
            close_matches += 1
        else:
            issues['avg_price_mismatch'].append({
                'product_id': product_id,
                'finale_avg': round(finale_avg, 2),
                'db_avg': round(db_avg, 2),
                'diff_pct': round(diff_pct, 2)
            })

        # Check PO item count
        finale_count = len(finale_prices[product_id])
        db_count = len(db_po_prices.get(product_id, []))

        if finale_count != db_count:
            issues['po_count_mismatch'].append({
                'product_id': product_id,
                'finale_count': finale_count,
                'db_count': db_count
            })

    print(f"\n✅ PRICE VALIDATION RESULTS:")
    print(f"   Perfect matches: {perfect_matches:,}")
    print(f"   Close matches (<1% diff): {close_matches:,}")
    print(f"   Missing products: {len(issues['missing_products']):,}")
    print(f"   Significant price differences: {len(issues['avg_price_mismatch']):,}")
    print(f"   PO count mismatches: {len(issues['po_count_mismatch']):,}")

    if issues['avg_price_mismatch']:
        print(f"\n⚠️  PRICE MISMATCHES (first 10):")
        for item in issues['avg_price_mismatch'][:10]:
            print(f"   - {item['product_id']}: Finale=${item['finale_avg']}, DB=${item['db_avg']}, Diff={item['diff_pct']}%")

    return issues

# ============================================================================
# PURCHASE ORDER VALIDATION
# ============================================================================

def validate_purchase_orders():
    """Validate purchase orders migration"""
    print("\n" + "="*80)
    print("   PURCHASE ORDER VALIDATION")
    print("="*80)
    print("\n📄 Reading purchase order data...")

    po_file = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/PurchaseOrderWDetail-Oct11.json'

    with open(po_file, 'r') as f:
        po_data = json.load(f)

    # Extract unique PO numbers
    finale_pos = set()
    finale_po_items = {}
    current_po = None

    for item in po_data:
        # Track PO number changes
        if item.get('Order ID'):
            current_po = item['Order ID'].strip()
            finale_pos.add(current_po)

        product_id = (item.get('Product ID') or '').strip()
        if product_id and current_po:
            key = f"{current_po}:{product_id}"
            qty = item.get('Quantity', 0)
            try:
                qty = int(float(qty))
            except:
                qty = 0

            # Accumulate quantity if same product appears multiple times in same PO
            if key in finale_po_items:
                finale_po_items[key] += qty
            else:
                finale_po_items[key] = qty

    print(f"   ✓ Finale POs: {len(finale_pos):,}")
    print(f"   ✓ Finale PO line items: {len(finale_po_items):,}")

    # Get database data
    conn = connect_to_db()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("SELECT COUNT(*) as count FROM purchase_orders")
    db_po_count = cursor.fetchone()['count']

    cursor.execute("SELECT COUNT(*) as count FROM purchase_order_items")
    db_poi_count = cursor.fetchone()['count']

    cursor.execute("""
        SELECT
            po.po_number,
            p.product_id,
            poi.quantity
        FROM purchase_order_items poi
        JOIN purchase_orders po ON poi.purchase_order_id = po.id
        JOIN products p ON poi.product_id = p.id
    """)

    db_po_items = {}
    for row in cursor.fetchall():
        key = f"{row['po_number']}:{row['product_id']}"
        db_po_items[key] = int(row['quantity'])

    cursor.close()
    conn.close()

    print(f"   ✓ Database POs: {db_po_count:,}")
    print(f"   ✓ Database PO line items: {db_poi_count:,}")

    # Validate
    print(f"\n✅ PURCHASE ORDER VALIDATION:")
    if len(finale_pos) == db_po_count:
        print(f"   ✓ PO count matches: {db_po_count}")
    else:
        print(f"   ⚠️  PO count mismatch: Finale={len(finale_pos)}, DB={db_po_count}")

    if len(finale_po_items) == len(db_po_items):
        print(f"   ✓ PO line item count matches: {len(db_po_items)}")
    else:
        print(f"   ⚠️  Line item count mismatch: Finale={len(finale_po_items)}, DB={len(db_po_items)}")

    # Check quantities match
    qty_matches = 0
    qty_mismatches = []

    for key, finale_qty in finale_po_items.items():
        if key in db_po_items:
            if finale_qty == db_po_items[key]:
                qty_matches += 1
            else:
                qty_mismatches.append({
                    'key': key,
                    'finale_qty': finale_qty,
                    'db_qty': db_po_items[key]
                })

    print(f"   ✓ Quantity matches: {qty_matches:,}/{len(finale_po_items):,}")

    if qty_mismatches:
        print(f"\n⚠️  QUANTITY MISMATCHES (first 10):")
        for item in qty_mismatches[:10]:
            print(f"   - {item['key']}: Finale={item['finale_qty']}, DB={item['db_qty']}")

# ============================================================================
# STOCK HISTORY VALIDATION
# ============================================================================

def validate_stock_history():
    """Validate stock history migration"""
    print("\n" + "="*80)
    print("   STOCK HISTORY VALIDATION")
    print("="*80)
    print("\n📄 Reading stock history data...")

    history_file = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/StockHistoryTransactionDetails-Oct11.json'

    with open(history_file, 'r') as f:
        history_data = json.load(f)

    # Count valid transactions
    finale_transactions = 0
    for item in history_data:
        if item.get('Product ID') and item.get('Qty'):
            qty = int(float(item.get('Qty', 0)))
            if qty != 0:
                finale_transactions += 1

    print(f"   ✓ Finale transactions: {finale_transactions:,}")

    # Get database data
    conn = connect_to_db()
    cursor = conn.cursor()

    cursor.execute("SELECT COUNT(*) FROM stock_history")
    db_transactions = cursor.fetchone()[0]

    cursor.close()
    conn.close()

    print(f"   ✓ Database transactions: {db_transactions:,}")

    print(f"\n✅ STOCK HISTORY VALIDATION:")
    if finale_transactions == db_transactions:
        print(f"   ✓ Transaction count matches: {db_transactions:,}")
    else:
        diff = finale_transactions - db_transactions
        pct = (db_transactions / finale_transactions * 100) if finale_transactions > 0 else 0
        print(f"   ⚠️  Transaction count: Finale={finale_transactions:,}, DB={db_transactions:,}")
        print(f"   Missing: {diff:,} ({100-pct:.1f}% gap)")

# ============================================================================
# MAIN EXECUTION
# ============================================================================

def main():
    """Run comprehensive migration validation"""
    print("\n" + "="*80)
    print("   COMPREHENSIVE MIGRATION VALIDATION")
    print("   Finale Inventory → WHIMS")
    print("="*80)
    print(f"\nStarted at: {datetime.now()}")

    validation_results = {
        'stock': {},
        'prices': {},
        'purchase_orders': {},
        'stock_history': {}
    }

    try:
        # 1. Validate Stock Quantities
        finale_stock = extract_finale_stock_data()
        db_stock = get_database_stock_data()
        stock_issues = validate_stock_quantities(finale_stock, db_stock)
        validation_results['stock'] = {
            'finale_products': len(finale_stock),
            'db_products': len(db_stock),
            'issues': {k: len(v) for k, v in stock_issues.items()}
        }

        # 2. Validate Prices
        finale_prices, finale_avg = extract_finale_price_data()
        db_po_prices, db_avg, db_product_prices = get_database_price_data()
        price_issues = validate_prices(finale_prices, finale_avg, db_po_prices, db_avg, db_product_prices)
        validation_results['prices'] = {
            'finale_products': len(finale_avg),
            'db_products': len(db_avg),
            'issues': {k: len(v) for k, v in price_issues.items()}
        }

        # 3. Validate Purchase Orders
        validate_purchase_orders()

        # 4. Validate Stock History
        validate_stock_history()

        # Summary
        print("\n" + "="*80)
        print("   VALIDATION SUMMARY")
        print("="*80)

        total_issues = (
            sum(validation_results['stock']['issues'].values()) +
            sum(validation_results['prices']['issues'].values())
        )

        if total_issues == 0:
            print("\n✅ MIGRATION VALIDATION PASSED!")
            print("   All data successfully migrated from Finale to WHIMS")
        else:
            print(f"\n⚠️  Found {total_issues} issues requiring review")
            print("   See detailed output above for specifics")

        print(f"\nCompleted at: {datetime.now()}")

        # Save report
        report_path = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/comprehensive_validation_report.json'
        with open(report_path, 'w') as f:
            json.dump(validation_results, f, indent=2)

        print(f"\n📄 Report saved to: finale-files/comprehensive_validation_report.json")

        return 0 if total_issues == 0 else 1

    except Exception as e:
        print(f"\n❌ Validation failed with error: {str(e)}")
        import traceback
        traceback.print_exc()
        return 1

if __name__ == "__main__":
    sys.exit(main())