<?php
require_once __DIR__ . '/config/database.php';

$tid = 1; // Change this to your tenant ID

echo "=== Check Batch Stock Data ===\n\n";

// Check the specific batches mentioned
$batchNos = ['88727', '022133'];

foreach ($batchNos as $batchNo) {
    echo "--- Batch: $batchNo ---\n";
    
    // Get batch info
    $stmt = db()->prepare("SELECT b.id, b.batch_no, b.expiry_date, p.name as product
                           FROM batches b
                           JOIN products p ON p.id = b.product_id
                           WHERE b.tenant_id = ? AND b.batch_no = ?");
    $stmt->execute([$tid, $batchNo]);
    $batch = $stmt->fetch();
    
    if ($batch) {
        echo "  Found: ID {$batch['id']}, Product: {$batch['product']}, Expiry: {$batch['expiry_date']}\n";
        
        // Check stock for this batch
        $stmt2 = db()->prepare("SELECT s.id, s.quantity, l.name as location
                                FROM stock s
                                LEFT JOIN locations l ON l.id = s.location_id
                                WHERE s.tenant_id = ? AND s.batch_id = ?");
        $stmt2->execute([$tid, $batch['id']]);
        $stocks = $stmt2->fetchAll();
        
        if ($stocks) {
            echo "  Stock records found:\n";
            foreach ($stocks as $stock) {
                echo "    - Stock ID: {$stock['id']}, Quantity: {$stock['quantity']}, Location: {$stock['location']}\n";
            }
        } else {
            echo "  NO stock records found for this batch!\n";
        }
    } else {
        echo "  Batch not found!\n";
    }
    echo "\n";
}

// Check all batches with expiry dates
echo "--- All batches with expiry dates ---\n";
$stmt = db()->prepare("SELECT b.id, b.batch_no, b.expiry_date, p.name as product
                       FROM batches b
                       JOIN products p ON p.id = b.product_id
                       WHERE b.tenant_id = ? AND b.expiry_date IS NOT NULL
                       ORDER BY b.expiry_date ASC");
$stmt->execute([$tid]);
$batches = $stmt->fetchAll();

echo "Total batches with expiry: " . count($batches) . "\n\n";

foreach ($batches as $batch) {
    echo "Batch {$batch['batch_no']} ({$batch['product']}) - Expires: {$batch['expiry_date']}\n";
    
    // Check if batch has stock
    $stmt2 = db()->prepare("SELECT COUNT(*) as count, SUM(s.quantity) as total_qty
                            FROM stock s
                            WHERE s.tenant_id = ? AND s.batch_id = ?");
    $stmt2->execute([$tid, $batch['id']]);
    $stockInfo = $stmt2->fetch();
    
    echo "  Stock records: {$stockInfo['count']}, Total quantity: {$stockInfo['total_qty']}\n";
}
