#!/usr/bin/env python3
"""
Complete stock-to-stock analysis: Finale vs Database
Reconstructs Oct 11 database state and compares to Finale
"""
import os
import sys
import json
import csv
from decimal import Decimal
from collections import defaultdict
import subprocess

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 execute_mysql_query(query):
    """Execute MySQL query using mysql command"""
    config = load_env_config()

    db_user = config.get('DB_USERNAME', 'root')
    db_pass = config.get('DB_PASSWORD', '')
    db_name = config.get('DB_DATABASE', 'whims_dev')

    # Write query to temp file
    query_file = '/tmp/temp_query.sql'
    with open(query_file, 'w') as f:
        f.write(query)

    # Build mysql command with input redirection
    cmd = f"mysql -u {db_user} -p'{db_pass}' {db_name} < {query_file}"

    try:
        result = subprocess.run(cmd, shell=True, capture_output=True, text=True, check=False)
        if result.returncode != 0:
            print(f"Query error: {result.stderr}")
            return None
        return result.stdout
    except Exception as e:
        print(f"Error: {e}")
        return None

def main():
    print("="*100)
    print("COMPLETE STOCK-TO-STOCK ANALYSIS")
    print("="*100)

    # Load Finale data
    print("\n[1/5] Loading Finale data...")

    finale_products = {}
    with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/ProductListScreenReport-Oct11.csv', 'r') as f:
        reader = csv.DictReader(f)
        for row in reader:
            product_id = row['Product ID']
            avg_cost = row['Average cost']
            finale_products[product_id] = {
                'price': Decimal(avg_cost) if avg_cost and avg_cost.strip() else Decimal('0')
            }

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

    finale_stock = {}
    current_product = None

    for record in stock_data:
        product_id = record.get('Product ID')
        if product_id and product_id not in [None, '', ' ', 'TOTAL:'] and 'Units\nQoH' not in record:
            current_product = product_id
            if current_product not in finale_stock:
                finale_stock[current_product] = 0
        elif product_id is None and current_product and 'Stock item description' in record:
            desc = record.get('Stock item description', '')
            if 'TOTAL:' in desc:
                qty = record.get('Units\nQoH', 0)
                if qty:
                    finale_stock[current_product] += int(qty)

    finale_data = {}
    for pid, qty in finale_stock.items():
        if qty > 0:
            price = finale_products.get(pid, {}).get('price', Decimal('0'))
            finale_data[pid] = {
                'qty': qty,
                'price': price,
                'value': Decimal(qty) * price
            }

    print(f"   ✓ Finale: {len(finale_data):,} products, {sum(p['qty'] for p in finale_data.values()):,} units")

    # Load current database stocks
    print("\n[2/5] Loading current database stocks...")

    query = """
    SELECT p.product_id, p.average_price, COALESCE(SUM(s.quantity), 0) as current_qty
    FROM products p
    LEFT JOIN stocks s ON p.id = s.product_id
    GROUP BY p.product_id, p.average_price
    """

    result = execute_mysql_query(query)
    if not result:
        print("Failed to get current stocks")
        sys.exit(1)

    db_current = {}
    for line in result.strip().split('\n')[1:]:  # Skip header
        parts = line.split('\t')
        if len(parts) >= 3:
            product_id = parts[0]
            price = Decimal(parts[1]) if parts[1] else Decimal('0')
            qty = int(float(parts[2])) if parts[2] else 0
            if qty > 0:
                db_current[product_id] = {
                    'qty': qty,
                    'price': price,
                    'value': Decimal(qty) * price
                }

    print(f"   ✓ Current DB: {len(db_current):,} products, {sum(p['qty'] for p in db_current.values()):,} units")

    # Load stock movements since Oct 11
    print("\n[3/5] Loading stock movements since Oct 11...")

    query = """
    SELECT p.product_id, SUM(sh.quantity_change) as total_change
    FROM stock_history sh
    JOIN products p ON sh.product_id = p.id
    WHERE sh.created_at > '2025-10-11 23:59:59'
    GROUP BY p.product_id
    """

    result = execute_mysql_query(query)
    if not result:
        print("Failed to get stock movements")
        sys.exit(1)

    movements = {}
    for line in result.strip().split('\n')[1:]:  # Skip header
        parts = line.split('\t')
        if len(parts) >= 2:
            product_id = parts[0]
            change = int(parts[1]) if parts[1] else 0
            movements[product_id] = change

    print(f"   ✓ Movements: {len(movements):,} products changed, {sum(movements.values()):,} net units")

    # Reconstruct Oct 11 database state
    print("\n[4/5] Reconstructing database Oct 11 state...")

    db_oct11 = {}
    for product_id, current_data in db_current.items():
        current_qty = current_data['qty']
        movement = movements.get(product_id, 0)
        oct11_qty = current_qty - movement  # Subtract movements to get Oct 11 qty

        if oct11_qty > 0:
            price = current_data['price']
            db_oct11[product_id] = {
                'qty': oct11_qty,
                'price': price,
                'value': Decimal(oct11_qty) * price
            }

    print(f"   ✓ DB Oct 11 (reconstructed): {len(db_oct11):,} products, {sum(p['qty'] for p in db_oct11.values()):,} units")

    # Compare product by product
    print("\n[5/5] Comparing product-by-product...")

    all_products = set(list(finale_data.keys()) + list(db_oct11.keys()))

    discrepancies = []

    for product_id in all_products:
        finale = finale_data.get(product_id, {'qty': 0, 'price': Decimal('0'), 'value': Decimal('0')})
        db = db_oct11.get(product_id, {'qty': 0, 'price': Decimal('0'), 'value': Decimal('0')})
        current = db_current.get(product_id, {'qty': 0, 'price': Decimal('0'), 'value': Decimal('0')})

        qty_diff = db['qty'] - finale['qty']
        value_diff = db['value'] - finale['value']

        if qty_diff != 0 or value_diff != 0:
            discrepancies.append({
                'product_id': product_id,
                'finale_qty': finale['qty'],
                'finale_price': float(finale['price']),
                'finale_value': float(finale['value']),
                'db_oct11_qty': db['qty'],
                'db_oct11_price': float(db['price']),
                'db_oct11_value': float(db['value']),
                'current_qty': current['qty'],
                'qty_diff': qty_diff,
                'value_diff': float(value_diff)
            })

    # Calculate totals
    finale_total = sum(p['value'] for p in finale_data.values())
    db_oct11_total = sum(p['value'] for p in db_oct11.values())
    total_diff = db_oct11_total - finale_total

    # Sort by value difference
    discrepancies.sort(key=lambda x: abs(x['value_diff']), reverse=True)

    # Generate report
    print("\n" + "="*100)
    print("ANALYSIS RESULTS")
    print("="*100)

    print(f"\nFinale Oct 11 total:     ${finale_total:,.2f}")
    print(f"DB Oct 11 total:         ${db_oct11_total:,.2f}")
    print(f"{'─'*50}")
    print(f"Difference:              ${total_diff:,.2f}")

    print(f"\nProducts with discrepancies: {len(discrepancies):,}")

    # Show top discrepancies
    print("\n" + "="*100)
    print("TOP 20 DISCREPANCIES (by value)")
    print("="*100)
    print(f"{'Product ID':<25} {'Finale':>20} {'DB Oct 11':>20} {'Difference':>15}")
    print(f"{'':25} {'Qty | Value':>20} {'Qty | Value':>20} {'Qty | Value':>15}")
    print("-"*100)

    for disc in discrepancies[:20]:
        print(f"{disc['product_id']:<25} " +
              f"{disc['finale_qty']:>3} | ${disc['finale_value']:>8.2f}  " +
              f"{disc['db_oct11_qty']:>3} | ${disc['db_oct11_value']:>8.2f}  " +
              f"{disc['qty_diff']:>+3} | ${disc['value_diff']:>+8.2f}")

    # Save full report
    report = {
        'summary': {
            'finale_total': float(finale_total),
            'db_oct11_total': float(db_oct11_total),
            'difference': float(total_diff),
            'products_with_discrepancies': len(discrepancies)
        },
        'discrepancies': discrepancies
    }

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

    print("\n" + "="*100)
    print("✓ Full report saved to: finale-files/complete_stock_analysis.json")
    print("="*100)

if __name__ == "__main__":
    main()