<?php
/**
 * Stock Reconciliation Diagnostic Tool
 * Compares stock table quantities with stock_movements calculated balance
 */

require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../core/Auth.php';
require_once __DIR__ . '/../core/Model.php';

// Initialize session
Auth::startSession();

// Check if user is logged in
if (!Auth::check()) {
    die("Please login to access this page.");
}

$tid = Auth::tenantId();
if (!$tid) {
    die("No tenant found.");
}

echo "<h1>Stock Reconciliation Diagnostic</h1>";
echo "<p>Comparing stock table vs stock_movements for tenant ID: $tid</p>";
echo "<hr>";

// Get all products with stock
$stmt = db()->prepare("
    SELECT p.id, p.name, p.sku,
           COALESCE(SUM(s.quantity), 0) as stock_table_qty
    FROM products p
    LEFT JOIN stock s ON s.product_id = p.id AND s.tenant_id = p.tenant_id
    WHERE p.tenant_id = ? AND p.is_active = 1
    GROUP BY p.id
    ORDER BY p.name
");
$stmt->execute([$tid]);
$products = $stmt->fetchAll();

$discrepancies = [];

foreach ($products as $product) {
    $productId = $product['id'];
    
    // Get movement summary
    $movementStmt = db()->prepare("
        SELECT
            COALESCE(SUM(CASE WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in', 'return') THEN ABS(sm.quantity) ELSE 0 END), 0) as total_in,
            COALESCE(SUM(CASE WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') THEN ABS(sm.quantity) ELSE 0 END), 0) as total_out
        FROM stock_movements sm
        WHERE sm.product_id = ? AND sm.tenant_id = ?
    ");
    $movementStmt->execute([$productId, $tid]);
    $movementResult = $movementStmt->fetch();
    
    $totalIn = (float)($movementResult['total_in'] ?? 0);
    $totalOut = (float)($movementResult['total_out'] ?? 0);
    $movementBalance = $totalIn - $totalOut;
    $stockTableQty = (float)$product['stock_table_qty'];
    
    $difference = $stockTableQty - $movementBalance;
    
    // Allow small floating point differences
    if (abs($difference) > 0.01) {
        $discrepancies[] = [
            'product_id' => $productId,
            'name' => $product['name'],
            'sku' => $product['sku'],
            'stock_table_qty' => $stockTable_qty,
            'movement_balance' => $movementBalance,
            'difference' => $difference,
            'total_in' => $totalIn,
            'total_out' => $totalOut
        ];
    }
}

if (empty($discrepancies)) {
    echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 20px 0;'>";
    echo "<strong>✓ All stock records are reconciled!</strong>";
    echo "<br>Stock table matches stock_movements for all products.";
    echo "</div>";
} else {
    echo "<div style='background: #f8d7da; color: #721c24; padding: 15px; border-radius: 5px; margin: 20px 0;'>";
    echo "<strong>⚠ Found " . count($discrepancies) . " products with discrepancies:</strong>";
    echo "</div>";
    
    echo "<table border='1' cellpadding='10' style='border-collapse: collapse; width: 100%;'>";
    echo "<thead>";
    echo "<tr style='background: #f0f0f0;'>";
    echo "<th>Product</th>";
    echo "<th>SKU</th>";
    echo "<th>Stock Table</th>";
    echo "<th>Movement Balance</th>";
    echo "<th>Difference</th>";
    echo "<th>Total In</th>";
    echo "<th>Total Out</th>";
    echo "</tr>";
    echo "</thead>";
    echo "<tbody>";
    
    foreach ($discrepancies as $discrepancy) {
        $rowStyle = $discrepancy['difference'] > 0 ? 'background: #fff3cd;' : 'background: #f8d7da;';
        echo "<tr style='$rowStyle'>";
        echo "<td><strong>" . htmlspecialchars($discrepancy['name']) . "</strong></td>";
        echo "<td>" . htmlspecialchars($discrepancy['sku'] ?: '-') . "</td>";
        echo "<td>" . number_format($discrepancy['stock_table_qty'], 2) . "</td>";
        echo "<td>" . number_format($discrepancy['movement_balance'], 2) . "</td>";
        echo "<td><strong>" . number_format($discrepancy['difference'], 2) . "</strong></td>";
        echo "<td>" . number_format($discrepancy['total_in'], 2) . "</td>";
        echo "<td>" . number_format($discrepancy['total_out'], 2) . "</td>";
        echo "</tr>";
    }
    
    echo "</tbody>";
    echo "</table>";
    
    echo "<hr>";
    echo "<h2>How to Fix Discrepancies</h2>";
    echo "<p><strong>Option 1: Reconcile to Stock Movements (Recommended)</strong></p>";
    echo "<p>This updates the stock table to match the movement history, which is the source of truth.</p>";
    echo "<form method='POST'>";
    echo "<input type='hidden' name='action' value='reconcile_to_movements'>";
    echo "<button type='submit' style='background: #28a745; color: white; padding: 10px 20px; border: none; border-radius: 5px; cursor: pointer;'>Reconcile All to Movement History</button>";
    echo "</form>";
    
    echo "<p><strong>Option 2: Reconcile to Stock Table</strong></p>";
    echo "<p>This creates adjustment movements to match the current stock table values.</p>";
    echo "<form method='POST'>";
    echo "<input type='hidden' name='action' value='reconcile_to_stock_table'>";
    echo "<button type='submit' style='background: #17a2b8; color: white; padding: 10px 20px; border: none; border-radius: 5px; cursor: pointer;'>Create Adjustment Movements</button>";
    echo "</form>";
}

// Handle reconciliation
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'])) {
    $action = $_POST['action'];
    $userId = Auth::userId();
    
    echo "<hr>";
    echo "<h2>Reconciliation Results</h2>";
    
    if ($action === 'reconcile_to_movements') {
        // Update stock table to match movement balance
        $fixed = 0;
        foreach ($discrepancies as $discrepancy) {
            $movementBalance = $discrepancy['movement_balance'];
            
            // Get current stock records
            $stockStmt = db()->prepare("SELECT * FROM stock WHERE product_id = ? AND tenant_id = ?");
            $stockStmt->execute([$discrepancy['product_id'], $tid]);
            $stockRecords = $stockStmt->fetchAll();
            
            if (empty($stockRecords)) {
                // No stock records, create one if movement balance > 0
                if ($movementBalance > 0) {
                    // Get default location
                    $locStmt = db()->prepare("SELECT id FROM locations WHERE tenant_id = ? AND is_default = 1 LIMIT 1");
                    $locStmt->execute([$tid]);
                    $location = $locStmt->fetch();
                    
                    if ($location) {
                        $insertStmt = db()->prepare("INSERT INTO stock (tenant_id, product_id, location_id, quantity) VALUES (?, ?, ?, ?)");
                        $insertStmt->execute([$tid, $discrepancy['product_id'], $location['id'], $movementBalance]);
                        $fixed++;
                    }
                }
            } else {
                // Update existing stock records
                // For simplicity, update the first record to match movement balance
                $updateStmt = db()->prepare("UPDATE stock SET quantity = ? WHERE product_id = ? AND tenant_id = ? LIMIT 1");
                $updateStmt->execute([$movementBalance, $discrepancy['product_id'], $tid]);
                $fixed++;
            }
        }
        
        echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px;'>";
        echo "<strong>✓ Fixed $fixed products</strong>";
        echo "<br>Stock table has been updated to match movement history.";
        echo "</div>";
        
    } elseif ($action === 'reconcile_to_stock_table') {
        // Create adjustment movements to match stock table
        $fixed = 0;
        foreach ($discrepancies as $discrepancy) {
            $difference = $discrepancy['difference'];
            
            if (abs($difference) > 0.01) {
                // Get default location
                $locStmt = db()->prepare("SELECT id FROM locations WHERE tenant_id = ? AND is_default = 1 LIMIT 1");
                $locStmt->execute([$tid]);
                $location = $locStmt->fetch();
                
                if ($location) {
                    $movementType = $difference > 0 ? 'adjustment_in' : 'adjustment_out';
                    $movementStmt = db()->prepare("
                        INSERT INTO stock_movements (tenant_id, product_id, location_id, type, quantity, reference, note, user_id)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    ");
                    $movementStmt->execute([
                        $tid,
                        $discrepancy['product_id'],
                        $location['id'],
                        $movementType,
                        $difference,
                        'RECONCILIATION',
                        'Stock reconciliation adjustment',
                        $userId
                    ]);
                    $fixed++;
                }
            }
        }
        
        echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px;'>";
        echo "<strong>✓ Created $fixed adjustment movements</strong>";
        echo "<br>Movements have been created to reconcile with stock table.";
        echo "</div>";
    }
    
    echo "<p><a href=''>Run diagnostic again</a></p>";
}
