<?php
/**
 * Simple Stock Discrepancy Fix
 * Updates stock table to match stock_movements
 */

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

Auth::startSession();

if (!Auth::check()) {
    die("Please login first");
}

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

echo "<h1>Stock Discrepancy Fix</h1>";
echo "<p>Tenant ID: $tid</p>";
echo "<hr>";

// Find products with discrepancies
$stmt = db()->prepare("
    SELECT p.id, p.name, p.sku,
           COALESCE(SUM(s.quantity), 0) as stock_qty,
           (
               SELECT COALESCE(SUM(CASE 
                   WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in', 'return') THEN ABS(sm.quantity)
                   ELSE 0
               END), 0) - 
               COALESCE(SUM(CASE 
                   WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') THEN ABS(sm.quantity)
                   ELSE 0
               END), 0)
               FROM stock_movements sm
               WHERE sm.product_id = p.id AND sm.tenant_id = p.tenant_id
           ) as movement_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
    HAVING ABS(stock_qty - movement_qty) > 0.01
    ORDER BY p.name
");
$stmt->execute([$tid]);
$discrepancies = $stmt->fetchAll();

if (empty($discrepancies)) {
    echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px;'>";
    echo "<strong>✓ No discrepancies found</strong>";
    echo "</div>";
} else {
    echo "<div style='background: #fff3cd; color: #856404; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<strong>Found " . count($discrepancies) . " products with discrepancies</strong>";
    echo "</div>";
    
    echo "<table border='1' cellpadding='8' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr style='background: #f0f0f0;'>";
    echo "<th>Product</th>";
    echo "<th>Stock Table</th>";
    echo "<th>Movement Balance</th>";
    echo "<th>Difference</th>";
    echo "</tr>";
    
    foreach ($discrepancies as $d) {
        $diff = $d['stock_qty'] - $d['movement_qty'];
        $color = $diff > 0 ? '#f8d7da' : '#d1ecf1';
        echo "<tr style='background: $color;'>";
        echo "<td>" . htmlspecialchars($d['name']) . "</td>";
        echo "<td>" . number_format($d['stock_qty'], 2) . "</td>";
        echo "<td>" . number_format($d['movement_qty'], 2) . "</td>";
        echo "<td><strong>" . number_format($diff, 2) . "</strong></td>";
        echo "</tr>";
    }
    echo "</table>";
    
    echo "<hr>";
    echo "<h3>Fix Options</h3>";
    
    echo "<form method='POST'>";
    echo "<p><strong>Update stock table to match movement history:</strong></p>";
    echo "<input type='hidden' name='action' value='fix'>";
    echo "<button type='submit' style='background: #28a745; color: white; padding: 10px 20px; border: none; border-radius: 5px; cursor: pointer;'>Fix All Discrepancies</button>";
    echo "</form>";
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'fix') {
    $fixed = 0;
    
    foreach ($discrepancies as $d) {
        $movementQty = $d['movement_qty'];
        
        // Check if stock record exists
        $checkStmt = db()->prepare("SELECT id FROM stock WHERE product_id = ? AND tenant_id = ?");
        $checkStmt->execute([$d['id'], $tid]);
        $stockRecord = $checkStmt->fetch();
        
        if ($stockRecord) {
            // Update existing
            $updateStmt = db()->prepare("UPDATE stock SET quantity = ? WHERE product_id = ? AND tenant_id = ?");
            $updateStmt->execute([$movementQty, $d['id'], $tid]);
        } else {
            // 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 && $movementQty > 0) {
                $insertStmt = db()->prepare("INSERT INTO stock (tenant_id, product_id, location_id, quantity) VALUES (?, ?, ?, ?)");
                $insertStmt->execute([$tid, $d['id'], $location['id'], $movementQty]);
            }
        }
        $fixed++;
    }
    
    echo "<hr>";
    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>";
    echo "<p><a href=''>Check again</a></p>";
}
