<?php
/**
 * Check if there are any batches with expiry dates
 */

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

echo "Checking for batches with expiry dates...\n";

// Check total batches
$stmt = db()->query("SELECT COUNT(*) as count FROM batches");
$result = $stmt->fetch();
echo "Total batches: " . $result['count'] . "\n";

// Check batches with expiry dates
$stmt = db()->query("SELECT COUNT(*) as count FROM batches WHERE expiry_date IS NOT NULL");
$result = $stmt->fetch();
echo "Batches with expiry dates: " . $result['count'] . "\n";

// Check stock records
$stmt = db()->query("SELECT COUNT(*) as count FROM stock");
$result = $stmt->fetch();
echo "Total stock records: " . $result['count'] . "\n";

// Check stock with batch_id
$stmt = db()->query("SELECT COUNT(*) as count FROM stock WHERE batch_id IS NOT NULL");
$result = $stmt->fetch();
echo "Stock records with batch_id: " . $result['count'] . "\n";

// Check stock with quantity > 0
$stmt = db()->query("SELECT COUNT(*) as count FROM stock WHERE quantity > 0");
$result = $stmt->fetch();
echo "Stock records with quantity > 0: " . $result['count'] . "\n";

// Show sample batches
echo "Sample batches:\n";
$stmt = db()->query("SELECT id, product_id, batch_no, expiry_date, tenant_id FROM batches LIMIT 5");
$batches = $stmt->fetchAll();
foreach ($batches as $batch) {
    echo "  ID: {$batch['id']}, Product: {$batch['product_id']}, Batch: {$batch['batch_no']}, Expiry: {$batch['expiry_date']}, Tenant: {$batch['tenant_id']}\n";
}

// Show sample stock with batch_id
echo "Sample stock with batch_id:\n";
$stmt = db()->query("SELECT id, batch_id, quantity, tenant_id, location_id FROM stock WHERE batch_id IS NOT NULL LIMIT 5");
$stocks = $stmt->fetchAll();
foreach ($stocks as $stock) {
    echo "  ID: {$stock['id']}, Batch: {$stock['batch_id']}, Qty: {$stock['quantity']}, Tenant: {$stock['tenant_id']}, Location: {$stock['location_id']}\n";
}

// Check if products exist
echo "Checking products for batches:\n";
$stmt = db()->query("SELECT id, name FROM products WHERE id IN (5, 11)");
$products = $stmt->fetchAll();
foreach ($products as $product) {
    echo "  Product ID: {$product['id']}, Name: {$product['name']}\n";
}

// Check locations
echo "Checking locations:\n";
$stmt = db()->query("SELECT id, name FROM locations WHERE id = 1");
$locations = $stmt->fetchAll();
foreach ($locations as $location) {
    echo "  Location ID: {$location['id']}, Name: {$location['name']}\n";
}

// Check the expiry query
echo "Running expiry query...\n";
$sql = "SELECT COUNT(*) as count
        FROM batches b
        JOIN products p ON p.id = b.product_id
        LEFT JOIN categories c ON c.id = p.category_id
        JOIN stock s ON s.batch_id = b.id AND s.tenant_id = b.tenant_id
        JOIN locations l ON l.id = s.location_id
        WHERE b.tenant_id = 2 AND b.expiry_date IS NOT NULL AND s.quantity > 0";

try {
    $stmt = db()->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetch();
    echo "Expiry report query result: " . $result['count'] . "\n";
} catch (Exception $e) {
    echo "Error running query: " . $e->getMessage() . "\n";
}

echo "Done.\n";