<?php
/**
 * Stock Reconciliation Migration - For ALL Products
 * Consolidates fragmented stock records and fixes discrepancies
 */
require_once 'config/config.php';

$pdo = new PDO(
    "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET,
    DB_USER,
    DB_PASS
);

echo "╔════════════════════════════════════════════════════════════╗\n";
echo "║     GLOBAL STOCK RECONCILIATION & CONSOLIDATION            ║\n";
echo "║     Fixing all products with fragmented stock records      ║\n";
echo "╚════════════════════════════════════════════════════════════╝\n\n";

// Step 1: Get all active products
$stmt = $pdo->prepare('SELECT id, sku, name, tenant_id FROM products WHERE is_active = 1 ORDER BY sku');
$stmt->execute();
$allProducts = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "Found " . count($allProducts) . " active products to process.\n";
echo str_repeat("─", 60) . "\n\n";

$processedCount = 0;
$reconciledCount = 0;
$consolidatedCount = 0;
$errors = [];

foreach ($allProducts as $product) {
    $productId = $product['id'];
    $sku = $product['sku'];
    $name = $product['name'];
    
    try {
        // Get correct balance from stock_movements (NOW INCLUDING RETURNS)
        $stmt = $pdo->prepare('
            SELECT
                COALESCE(SUM(CASE 
                    WHEN type IN ("purchase", "transfer_in", "adjustment_in", "return", "adjustment") 
                    THEN ABS(quantity)
                    ELSE 0 
                END), 0) as total_in,
                COALESCE(SUM(CASE 
                    WHEN type IN ("sale", "transfer_out") 
                    THEN ABS(quantity)
                    ELSE 0 
                END), 0) as total_out
            FROM stock_movements
            WHERE product_id = ?
        ');
        $stmt->execute([$productId]);
        $movements = $stmt->fetch(PDO::FETCH_ASSOC);
        $correctBalance = intval($movements['total_in'] - $movements['total_out']);
        
        // Get current stock records
        $stmt = $pdo->prepare('
            SELECT id, location_id, batch_id, quantity 
            FROM stock 
            WHERE product_id = ? 
            ORDER BY id
        ');
        $stmt->execute([$productId]);
        $stockRecords = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $currentTotal = array_sum(array_column($stockRecords, 'quantity'));
        
        $needsConsolidation = false;
        $needsReconciliation = false;
        
        // Check if consolidation needed
        if (count($stockRecords) > 1) {
            $needsConsolidation = true;
        } elseif (count($stockRecords) === 1 && $stockRecords[0]['batch_id'] !== null) {
            // Single record with batch_id needs consolidation too
            $needsConsolidation = true;
        }
        
        // Check if reconciliation needed
        if ((float)$currentTotal !== (float)$correctBalance && count($stockRecords) > 0) {
            $needsReconciliation = true;
        }
        
        if (!$needsConsolidation && !$needsReconciliation) {
            echo "✓ " . str_pad("$sku | $name", 50) . " [OK]\n";
            $processedCount++;
            continue;
        }
        
        // Perform transaction
        $pdo->beginTransaction();
        
        $location_id = count($stockRecords) > 0 ? $stockRecords[0]['location_id'] : 1;
        
        // Get tenant_id
        $stmt = $pdo->prepare('SELECT tenant_id FROM products WHERE id = ?');
        $stmt->execute([$productId]);
        $tenant = $stmt->fetch(PDO::FETCH_ASSOC);
        $tenant_id = $tenant['tenant_id'] ?? 1;
        
        // Delete all fragmented records
        $stmt = $pdo->prepare('DELETE FROM stock WHERE product_id = ?');
        $stmt->execute([$productId]);
        $deleted = $pdo->prepare('SELECT CHANGES() as cnt')->fetch()['cnt'] ?? count($stockRecords);
        
        // Insert consolidated record with correct balance
        $stmt = $pdo->prepare('
            INSERT INTO stock (tenant_id, product_id, location_id, batch_id, quantity) 
            VALUES (?, ?, ?, NULL, ?)
        ');
        $stmt->execute([$tenant_id, $productId, $location_id, $correctBalance]);
        
        $pdo->commit();
        
        // Build status message
        $status = [];
        if ($needsConsolidation) {
            $status[] = "consolidated " . count($stockRecords) . " entries";
            $consolidatedCount++;
        }
        if ($needsReconciliation) {
            $status[] = "reconciled (" . $currentTotal . " → " . $correctBalance . ")";
            $reconciledCount++;
        }
        
        echo "⚙ " . str_pad("$sku | $name", 50) . " [" . implode(", ", $status) . "]\n";
        $processedCount++;
        
    } catch (Exception $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        $errors[] = "$sku: " . $e->getMessage();
        echo "✗ " . str_pad("$sku | $name", 50) . " [ERROR: " . substr($e->getMessage(), 0, 30) . "]\n";
    }
}

echo "\n" . str_repeat("─", 60) . "\n";
echo "\n📊 SUMMARY\n";
echo "   Total Processed: $processedCount / " . count($allProducts) . " products\n";
echo "   Consolidated: $consolidatedCount products (fragmented entries merged)\n";
echo "   Reconciled: $reconciledCount products (stock/movement sync fixed)\n";

if (count($errors) > 0) {
    echo "\n⚠ ERRORS (" . count($errors) . "):\n";
    foreach ($errors as $error) {
        echo "   - $error\n";
    }
}

echo "\n" . str_repeat("═", 60) . "\n";
echo "✓ Stock Reconciliation Complete!\n";
echo str_repeat("═", 60) . "\n";

// Verify results
echo "\nVerification Sample (first 5 products with movements):\n";
$stmt = $pdo->prepare('
    SELECT p.id, p.sku, p.name,
           (SELECT SUM(quantity) FROM stock WHERE product_id = p.id) as stock_total,
           (SELECT SUM(CASE WHEN type IN ("purchase","transfer_in","adjustment_in","return","adjustment") THEN ABS(quantity) ELSE 0 END) FROM stock_movements WHERE product_id = p.id) as movements_in,
           (SELECT SUM(CASE WHEN type IN ("sale","transfer_out") THEN ABS(quantity) ELSE 0 END) FROM stock_movements WHERE product_id = p.id) as movements_out
    FROM products p
    WHERE p.is_active = 1 AND EXISTS (SELECT 1 FROM stock_movements WHERE product_id = p.id)
    ORDER BY p.name
    LIMIT 5
');
$stmt->execute();
$samples = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($samples as $sample) {
    $calculated = intval($sample['movements_in'] - $sample['movements_out']);
    $match = $sample['stock_total'] == $calculated ? "✓" : "✗";
    echo "   $match " . $sample['sku'] . " | Stock: " . intval($sample['stock_total']) . " | Calculated: $calculated\n";
}

$pdo = null;
