<?php
require_once '../assets/db_connect.php';

echo "<h2>Available Products in Shop ID 1</h2>";

$sql = "SELECT 
            p.id,
            p.sku,
            p.name,
            p.generic_name,
            p.category,
            p.selling_price,
            p.is_active,
            COUNT(pb.id) as batch_count,
            COALESCE(SUM(CASE WHEN pb.is_active = 1 AND pb.qty > 0 THEN pb.qty ELSE 0 END), 0) as total_stock
        FROM products p
        LEFT JOIN product_batches pb ON p.id = pb.product_id
        WHERE p.shop_id = 1
        AND p.is_active = 1
        GROUP BY p.id
        ORDER BY p.name";

$stmt = $pdo->query($sql);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<table border='1' cellpadding='5'>
        <tr>
            <th>ID</th>
            <th>SKU</th>
            <th>Name</th>
            <th>Generic</th>
            <th>Category</th>
            <th>Price</th>
            <th>Active</th>
            <th>Batches</th>
            <th>Stock</th>
        </tr>";

foreach ($products as $product) {
    echo "<tr>";
    echo "<td>{$product['id']}</td>";
    echo "<td>{$product['sku']}</td>";
    echo "<td>{$product['name']}</td>";
    echo "<td>{$product['generic_name']}</td>";
    echo "<td>{$product['category']}</td>";
    echo "<td>" . number_format($product['selling_price'], 2) . "</td>";
    echo "<td>" . ($product['is_active'] ? 'Yes' : 'No') . "</td>";
    echo "<td>{$product['batch_count']}</td>";
    echo "<td>{$product['total_stock']}</td>";
    echo "</tr>";
}
echo "</table>";

// Show available batches for each product
echo "<h3>Available Batches:</h3>";
foreach ($products as $product) {
    echo "<h4>{$product['name']} (ID: {$product['id']})</h4>";
    
    $batchSql = "SELECT 
                    id, batch_no, cost_price, sell_price, 
                    expiry_date, qty, is_active 
                 FROM product_batches 
                 WHERE product_id = ? 
                 AND shop_id = 1 
                 AND is_active = 1 
                 AND qty > 0
                 ORDER BY expiry_date";
    
    $batchStmt = $pdo->prepare($batchSql);
    $batchStmt->execute([$product['id']]);
    $batches = $batchStmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($batches) > 0) {
        echo "<table border='1' cellpadding='3' style='margin-bottom: 20px;'>
                <tr>
                    <th>Batch ID</th>
                    <th>Batch No</th>
                    <th>Cost</th>
                    <th>Sell</th>
                    <th>Expiry</th>
                    <th>Qty</th>
                    <th>Active</th>
                </tr>";
        
        foreach ($batches as $batch) {
            echo "<tr>";
            echo "<td>{$batch['id']}</td>";
            echo "<td>{$batch['batch_no']}</td>";
            echo "<td>" . number_format($batch['cost_price'], 2) . "</td>";
            echo "<td>" . number_format($batch['sell_price'], 2) . "</td>";
            echo "<td>{$batch['expiry_date']}</td>";
            echo "<td>{$batch['qty']}</td>";
            echo "<td>" . ($batch['is_active'] ? 'Yes' : 'No') . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color: orange;'>No active batches with stock available</p>";
    }
}
?>