#!/usr/bin/env python3
"""
Validate the migration results
"""
import json
import mysql.connector
import sys
from datetime import datetime
from decimal import Decimal

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 validate_migration():
    """Validate migration results"""
    conn = get_db_connection()
    cursor = conn.cursor()

    print("\n" + "="*60)
    print("   MIGRATION VALIDATION REPORT")
    print("="*60)

    validation_results = []
    has_warnings = False
    has_errors = False

    # 1. Check products
    print("\n📦 PRODUCTS VALIDATION")
    cursor.execute("SELECT COUNT(*) FROM products")
    product_count = cursor.fetchone()[0]
    print(f"   Total products: {product_count}")

    cursor.execute("""
        SELECT COUNT(*) FROM products
        WHERE average_price = 0 OR average_price IS NULL
    """)
    no_price_count = cursor.fetchone()[0]
    if no_price_count > 0:
        print(f"   ⚠️ Products without price: {no_price_count}")
        has_warnings = True

    cursor.execute("""
        SELECT part_type, COUNT(*) as count
        FROM products
        GROUP BY part_type
        ORDER BY count DESC
        LIMIT 5
    """)
    print("   Part types distribution:")
    for part_type, count in cursor.fetchall():
        print(f"      {part_type}: {count}")

    # 2. Check purchase orders
    print("\n📋 PURCHASE ORDERS VALIDATION")
    cursor.execute("SELECT COUNT(*) FROM purchase_orders")
    po_count = cursor.fetchone()[0]
    print(f"   Total purchase orders: {po_count}")

    cursor.execute("""
        SELECT status, COUNT(*) as count
        FROM purchase_orders
        GROUP BY status
    """)
    print("   Order status distribution:")
    for status, count in cursor.fetchall():
        print(f"      {status}: {count}")

    cursor.execute("SELECT COUNT(*) FROM purchase_order_items")
    po_items_count = cursor.fetchone()[0]
    print(f"   Total order items: {po_items_count}")

    cursor.execute("""
        SELECT COUNT(*) FROM purchase_order_items
        WHERE product_id IS NULL
    """)
    unmapped_items = cursor.fetchone()[0]
    if unmapped_items > 0:
        print(f"   ⚠️ Unmapped order items: {unmapped_items}")
        has_warnings = True

    # 3. Check warehouses and locations
    print("\n🏭 WAREHOUSES & LOCATIONS VALIDATION")
    cursor.execute("SELECT COUNT(*) FROM warehouses")
    warehouse_count = cursor.fetchone()[0]
    print(f"   Total warehouses: {warehouse_count}")

    cursor.execute("""
        SELECT w.name, COUNT(l.id) as location_count
        FROM warehouses w
        LEFT JOIN locations l ON w.id = l.warehouse_id
        GROUP BY w.id, w.name
    """)
    print("   Locations per warehouse:")
    for name, count in cursor.fetchall():
        print(f"      {name}: {count} locations")

    # 4. Check stock inventory
    print("\n📊 STOCK INVENTORY VALIDATION")
    cursor.execute("SELECT COUNT(*) FROM stocks")
    stock_count = cursor.fetchone()[0]
    print(f"   Total stock records: {stock_count}")

    cursor.execute("SELECT SUM(quantity) FROM stocks")
    total_quantity = cursor.fetchone()[0] or 0
    total_quantity = int(total_quantity) if isinstance(total_quantity, Decimal) else total_quantity
    print(f"   Total quantity in stock: {total_quantity}")

    cursor.execute("""
        SELECT COUNT(DISTINCT product_id) FROM stocks
        WHERE quantity > 0
    """)
    products_in_stock = cursor.fetchone()[0]
    print(f"   Products with stock: {products_in_stock}")

    cursor.execute("""
        SELECT
            w.name,
            COUNT(s.id) as stock_records,
            SUM(s.quantity) as total_qty
        FROM stocks s
        JOIN warehouses w ON s.warehouse_id = w.id
        GROUP BY w.id, w.name
    """)
    print("   Stock by warehouse:")
    for name, records, qty in cursor.fetchall():
        print(f"      {name}: {records} records, {qty} units")

    # 5. Check stock history
    print("\n📜 STOCK HISTORY VALIDATION")
    cursor.execute("SELECT COUNT(*) FROM stock_history")
    history_count = cursor.fetchone()[0]
    print(f"   Total history records: {history_count}")

    cursor.execute("""
        SELECT action_type, COUNT(*) as count
        FROM stock_history
        GROUP BY action_type
        ORDER BY count DESC
    """)
    print("   History by action type:")
    for action_type, count in cursor.fetchall():
        print(f"      {action_type}: {count}")

    # 6. Data integrity checks
    print("\n🔍 DATA INTEGRITY CHECKS")

    # Check for orphaned stock records
    cursor.execute("""
        SELECT COUNT(*)
        FROM stocks s
        LEFT JOIN products p ON s.product_id = p.id
        WHERE p.id IS NULL
    """)
    orphaned_stocks = cursor.fetchone()[0]
    if orphaned_stocks > 0:
        print(f"   ❌ Orphaned stock records: {orphaned_stocks}")
        has_errors = True
    else:
        print(f"   ✅ No orphaned stock records")

    # Check for invalid locations
    cursor.execute("""
        SELECT COUNT(*)
        FROM stocks s
        LEFT JOIN locations l ON s.location_id = l.id
        WHERE l.id IS NULL
    """)
    invalid_locations = cursor.fetchone()[0]
    if invalid_locations > 0:
        print(f"   ❌ Stock with invalid locations: {invalid_locations}")
        has_errors = True
    else:
        print(f"   ✅ All stock locations valid")

    # Check for negative quantities
    cursor.execute("""
        SELECT COUNT(*) FROM stocks WHERE quantity < 0
    """)
    negative_qty = cursor.fetchone()[0]
    if negative_qty > 0:
        print(f"   ❌ Stocks with negative quantity: {negative_qty}")
        has_errors = True
    else:
        print(f"   ✅ No negative stock quantities")

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

    if has_errors:
        print("❌ ERRORS FOUND - Please review and fix critical issues")
    elif has_warnings:
        print("⚠️  WARNINGS FOUND - Non-critical issues that may need attention")
    else:
        print("✅ ALL VALIDATIONS PASSED")

    print(f"\n📊 Migration Statistics:")
    print(f"   Products:        {product_count}")
    print(f"   Purchase Orders: {po_count}")
    print(f"   Stock Records:   {stock_count}")
    print(f"   History Records: {history_count}")
    print(f"   Warehouses:      {warehouse_count}")
    print(f"   Total Inventory: {total_quantity} units")

    # Save validation report
    report = {
        'timestamp': datetime.now().isoformat(),
        'statistics': {
            'products': product_count,
            'purchase_orders': po_count,
            'stock_records': stock_count,
            'history_records': history_count,
            'warehouses': warehouse_count,
            'total_inventory': total_quantity
        },
        'warnings': {
            'products_without_price': no_price_count,
            'unmapped_order_items': unmapped_items
        },
        'errors': {
            'orphaned_stocks': orphaned_stocks,
            'invalid_locations': invalid_locations,
            'negative_quantities': negative_qty
        },
        'has_errors': has_errors,
        'has_warnings': has_warnings
    }

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

    print("\n📄 Validation report saved to finale-files/validation_report.json")

    cursor.close()
    conn.close()

    return not has_errors

if __name__ == "__main__":
    success = validate_migration()
    sys.exit(0 if success else 1)