<?php
// test_batches.php - For direct browser testing
require_once '../assets/db_connect.php';

// Set parameters for testing
$product_id = 1;
$shop_id = 1;

echo "<h2>Testing Batches for Product ID: $product_id, Shop ID: $shop_id</h2>";

// Check if product exists
$checkProduct = $pdo->prepare("SELECT id, name FROM products WHERE id = ? AND shop_id = ?");
$checkProduct->execute([$product_id, $shop_id]);
$product = $checkProduct->fetch(PDO::FETCH_ASSOC);

if (!$product) {
    echo "<p style='color: red;'>Product not found!</p>";
    exit;
}

echo "<p>Product found: <strong>" . htmlspecialchars($product['name']) . "</strong></p>";

// Query to get batches
$sql = "SELECT 
            pb.id,
            pb.batch_no,
            pb.cost_price,
            pb.sell_price,
            pb.expiry_date,
            pb.initial_qty,
            pb.is_active,
            COALESCE(
                pb.initial_qty - 
                COALESCE(
                    (SELECT SUM(sd.quantity) 
                     FROM sale_details sd
                     JOIN sales s ON sd.sale_id = s.id
                     WHERE sd.batch_id = pb.id
                     AND s.shop_id = pb.shop_id
                     AND s.status != 'cancelled'), 0
                ), pb.initial_qty
            ) as available_qty
        FROM product_batches pb
        WHERE pb.product_id = ?
        AND pb.shop_id = ?
        ORDER BY pb.expiry_date ASC";

$stmt = $pdo->prepare($sql);
$stmt->execute([$product_id, $shop_id]);
$batches = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<h3>Raw Database Data (" . count($batches) . " batches):</h3>";
echo "<table border='1' cellpadding='5'>";
echo "<tr>
        <th>ID</th>
        <th>Batch No</th>
        <th>Cost Price</th>
        <th>Sell Price</th>
        <th>Expiry Date</th>
        <th>Initial Qty</th>
        <th>Available Qty</th>
        <th>Is Active</th>
      </tr>";

foreach ($batches as $batch) {
    echo "<tr>";
    echo "<td>" . $batch['id'] . "</td>";
    echo "<td>" . htmlspecialchars($batch['batch_no']) . "</td>";
    echo "<td>" . $batch['cost_price'] . "</td>";
    echo "<td>" . $batch['sell_price'] . "</td>";
    echo "<td>" . $batch['expiry_date'] . "</td>";
    echo "<td>" . $batch['initial_qty'] . "</td>";
    echo "<td>" . $batch['available_qty'] . "</td>";
    echo "<td>" . $batch['is_active'] . "</td>";
    echo "</tr>";
}
echo "</table>";

// Show only active batches with stock
echo "<h3>Active Batches with Stock (>0):</h3>";
$availableBatches = array_filter($batches, function($batch) {
    return $batch['is_active'] == 1 && $batch['available_qty'] > 0;
});

echo "<p>Available batches: " . count($availableBatches) . "</p>";

if (count($availableBatches) > 0) {
    echo "<ul>";
    foreach ($availableBatches as $batch) {
        echo "<li>Batch: " . htmlspecialchars($batch['batch_no']) . 
             " - Available: " . $batch['available_qty'] . 
             " - Price: " . $batch['sell_price'] . 
             ($batch['expiry_date'] && $batch['expiry_date'] != '0000-00-00' ? 
              " (Exp: " . $batch['expiry_date'] . ")" : "") . 
             "</li>";
    }
    echo "</ul>";
} else {
    echo "<p style='color: orange;'>No active batches with stock found!</p>";
    
    // Show why batches aren't available
    echo "<h4>Possible reasons:</h4>";
    echo "<ul>";
    echo "<li>is_active = 0 (inactive)</li>";
    echo "<li>available_qty = 0 (no stock)</li>";
    echo "<li>No batches exist for this product</li>";
    echo "</ul>";
}
?>