#!/usr/bin/env python3
"""
Validate migrated data by comparing Finale JSON files with database

This script performs comprehensive validation:
1. Product count validation
2. Stock quantity validation per product
3. Purchase order validation
4. Price validation from purchase orders

Usage: python3 08_validate_data_integrity.py
"""

import json
import mysql.connector
from decimal import Decimal
from collections import defaultdict
import sys

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

def extract_products_from_json():
    """Extract all unique products from Finale JSON files"""
    print("\n📄 Reading products from Finale JSON files...")

    products = {}

    # 1. From Stock Quantities
    print("   Reading StockQuantityBySublocationInUnitsWDetail...")
    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/StockQuantityBySublocationInUnitsWDetail-Oct11.json', 'r') as f:
        stock_data = json.load(f)

    current_product = None
    for item in stock_data:
        if item.get('Product ID'):
            current_product = item['Product ID'].strip()
            if current_product not in products:
                products[current_product] = {
                    'stock_qty': 0,
                    'po_count': 0,
                    'prices': []
                }
        elif current_product and item.get('Stock item description'):
            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)

                products[current_product]['stock_qty'] += qoh + packed + transit + wip

    print(f"   ✓ Found {len(products)} unique products with stock")

    # 2. From Purchase Orders
    print("   Reading PurchaseOrderWDetail...")
    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/PurchaseOrderWDetail-Oct11.json', 'r') as f:
        po_data = json.load(f)

    for item in po_data:
        product_id = item.get('Product ID', '').strip()
        if product_id:
            if product_id not in products:
                products[product_id] = {
                    'stock_qty': 0,
                    'po_count': 0,
                    'prices': []
                }

            products[product_id]['po_count'] += 1

            # Extract price
            unit_price = item.get('Unit Price')
            if unit_price:
                try:
                    price = float(str(unit_price).replace('$', '').replace(',', '').strip())
                    if price > 0:
                        products[product_id]['prices'].append(price)
                except:
                    pass

    print(f"   ✓ Total unique products: {len(products)}")

    # 3. From Stock History (for products not in stock or POs)
    print("   Reading StockHistoryTransactionDetails...")
    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/StockHistoryTransactionDetails-Oct11.json', 'r') as f:
        history_data = json.load(f)

    history_count = 0
    for item in history_data:
        product_id = item.get('Product ID', '').strip()
        if product_id and product_id not in products:
            products[product_id] = {
                'stock_qty': 0,
                'po_count': 0,
                'prices': []
            }
            history_count += 1

    print(f"   ✓ Found {history_count} additional products from history")
    print(f"\n📊 Total unique products in Finale files: {len(products)}")

    return products

def get_database_stats():
    """Get product and stock statistics from database"""
    print("\n📄 Reading data from WHIMS database...")

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

    # Get products
    cursor.execute("""
        SELECT
            product_id,
            average_price,
            part_type
        FROM products
    """)
    db_products = {row['product_id']: row for row in cursor.fetchall()}
    print(f"   ✓ Found {len(db_products)} products in database")

    # Get stock quantities per product
    cursor.execute("""
        SELECT
            p.product_id,
            SUM(s.quantity) as total_quantity
        FROM stocks s
        JOIN products p ON s.product_id = p.id
        GROUP BY p.product_id
    """)
    db_stock = {row['product_id']: int(row['total_quantity']) for row in cursor.fetchall()}
    print(f"   ✓ Found {len(db_stock)} products with stock")

    # Get purchase order counts per product
    cursor.execute("""
        SELECT
            p.product_id,
            COUNT(*) as po_count,
            AVG(poi.unit_price) as avg_price
        FROM purchase_order_items poi
        JOIN products p ON poi.product_id = p.id
        GROUP BY p.product_id
    """)
    db_po = {}
    for row in cursor.fetchall():
        db_po[row['product_id']] = {
            'count': int(row['po_count']),
            'avg_price': float(row['avg_price']) if row['avg_price'] else 0
        }
    print(f"   ✓ Found {len(db_po)} products in purchase orders")

    cursor.close()
    conn.close()

    return db_products, db_stock, db_po

def validate_data(finale_products, db_products, db_stock, db_po):
    """Validate data between Finale and database"""
    print("\n" + "="*70)
    print("   VALIDATION RESULTS")
    print("="*70)

    # Track issues
    issues = {
        'missing_products': [],
        'stock_mismatch': [],
        'missing_stock': [],
        'price_mismatch': [],
        'missing_category': []
    }

    validated_products = 0
    matched_stock = 0
    matched_prices = 0

    print("\n🔍 Validating products...")

    # Only validate active products (those with stock, PO, or history)
    active_finale_products = {
        pid: data for pid, data in finale_products.items()
        if data['stock_qty'] > 0 or data['po_count'] > 0
    }

    print(f"   Active products in Finale: {len(active_finale_products)}")

    for product_id, finale_data in active_finale_products.items():
        # Check if product exists in database
        if product_id not in db_products:
            issues['missing_products'].append(product_id)
            continue

        validated_products += 1

        # Validate stock quantity
        finale_stock = finale_data['stock_qty']
        db_stock_qty = db_stock.get(product_id, 0)

        if finale_stock > 0:
            if abs(finale_stock - db_stock_qty) > 0:
                issues['stock_mismatch'].append({
                    'product_id': product_id,
                    'finale_qty': finale_stock,
                    'db_qty': db_stock_qty,
                    'difference': finale_stock - db_stock_qty
                })
            else:
                matched_stock += 1

        # Validate prices
        if finale_data['prices']:
            finale_avg_price = sum(finale_data['prices']) / len(finale_data['prices'])
            db_price_info = db_po.get(product_id, {})
            db_avg_price = db_price_info.get('avg_price', 0)

            if db_avg_price > 0:
                price_diff_pct = abs(finale_avg_price - db_avg_price) / finale_avg_price * 100
                if price_diff_pct > 1:  # More than 1% difference
                    issues['price_mismatch'].append({
                        'product_id': product_id,
                        'finale_avg': round(finale_avg_price, 2),
                        'db_avg': round(db_avg_price, 2),
                        'diff_pct': round(price_diff_pct, 2)
                    })
                else:
                    matched_prices += 1

        # Check if product has category
        if not db_products[product_id]['part_type']:
            issues['missing_category'].append(product_id)

    # Print results
    print(f"\n✅ VALIDATION SUMMARY:")
    print(f"   Total active products in Finale: {len(active_finale_products)}")
    print(f"   Products validated in database: {validated_products}")
    print(f"   Missing products: {len(issues['missing_products'])}")
    print(f"   Stock matches: {matched_stock}")
    print(f"   Stock mismatches: {len(issues['stock_mismatch'])}")
    print(f"   Price matches: {matched_prices}")
    print(f"   Price mismatches: {len(issues['price_mismatch'])}")
    print(f"   Products without category: {len(issues['missing_category'])}")

    # Show sample issues
    if issues['missing_products']:
        print(f"\n⚠️  MISSING PRODUCTS (showing first 10):")
        for pid in issues['missing_products'][:10]:
            print(f"   - {pid}")
        if len(issues['missing_products']) > 10:
            print(f"   ... and {len(issues['missing_products']) - 10} more")

    if issues['stock_mismatch']:
        print(f"\n⚠️  STOCK MISMATCHES (showing first 10):")
        for item in issues['stock_mismatch'][:10]:
            print(f"   - {item['product_id']}: Finale={item['finale_qty']}, DB={item['db_qty']}, Diff={item['difference']}")
        if len(issues['stock_mismatch']) > 10:
            print(f"   ... and {len(issues['stock_mismatch']) - 10} more")

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

    # Calculate success rate
    success_rate = (validated_products / len(active_finale_products) * 100) if active_finale_products else 0

    print(f"\n📊 OVERALL VALIDATION SCORE:")
    print(f"   Product Migration: {success_rate:.1f}% ({validated_products}/{len(active_finale_products)})")

    if matched_stock > 0:
        stock_accuracy = (matched_stock / validated_products * 100) if validated_products > 0 else 0
        print(f"   Stock Accuracy: {stock_accuracy:.1f}%")

    if matched_prices > 0:
        price_accuracy = (matched_prices / validated_products * 100) if validated_products > 0 else 0
        print(f"   Price Accuracy: {price_accuracy:.1f}%")

    # Save detailed report
    report_path = '/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/data_integrity_report.json'
    report = {
        'summary': {
            'total_finale_products': len(active_finale_products),
            'validated_products': validated_products,
            'success_rate': round(success_rate, 2),
            'stock_matches': matched_stock,
            'price_matches': matched_prices
        },
        'issues': {
            'missing_products': issues['missing_products'],
            'stock_mismatches': issues['stock_mismatch'],
            'price_mismatches': issues['price_mismatch'],
            'missing_category': issues['missing_category'][:100]  # Limit to first 100
        }
    }

    with open(report_path, 'w') as f:
        json.dump(report, f, indent=2)

    print(f"\n📄 Detailed report saved to: finale-files/data_integrity_report.json")

    # Return status
    if len(issues['missing_products']) == 0 and len(issues['stock_mismatch']) == 0:
        print("\n✅ DATA INTEGRITY CHECK PASSED!")
        return True
    else:
        print("\n⚠️  DATA INTEGRITY CHECK FOUND ISSUES")
        return False

def main():
    """Main validation process"""
    print("="*70)
    print("   FINALE TO WHIMS DATA INTEGRITY VALIDATION")
    print("="*70)

    try:
        # Extract data from Finale JSON files
        finale_products = extract_products_from_json()

        # Get data from database
        db_products, db_stock, db_po = get_database_stats()

        # Validate
        success = validate_data(finale_products, db_products, db_stock, db_po)

        sys.exit(0 if success else 1)

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

if __name__ == "__main__":
    main()