<?php
session_start();
require '../assets/db_connect.php';
header('Content-Type: application/json');

$shop_id = $_SESSION['user']['shop_id'] ?? 0;
$product_id = intval($_POST['product_id'] ?? 0);

if (!$shop_id || !$product_id) {
    echo json_encode(['status' => 'error', 'message' => 'Invalid data']);
    exit;
}

// Get the batch with highest quantity and earliest expiry date
$stmt = $pdo->prepare("
    SELECT 
        pb.id as batch_id,
        pb.batch_no,
        pb.sell_price as price,
        pb.expiry_date,
        COALESCE(SUM(
            CASE 
                WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                ELSE 0
            END
        ), 0) as available_qty
    FROM product_batches pb
    LEFT JOIN inventory_movements im ON pb.id = im.batch_id
    WHERE pb.product_id = ? 
    AND pb.shop_id = ? 
    AND pb.is_active = 1
    AND (pb.expiry_date IS NULL OR pb.expiry_date >= CURDATE())
    GROUP BY pb.id, pb.batch_no, pb.sell_price, pb.expiry_date
    HAVING available_qty > 0
    ORDER BY available_qty DESC, pb.expiry_date ASC
    LIMIT 1
");

$stmt->execute([$product_id, $shop_id]);
$batch = $stmt->fetch(PDO::FETCH_ASSOC);

if ($batch) {
    $expiry = $batch['expiry_date'] ? date('d/m/Y', strtotime($batch['expiry_date'])) : 'No expiry';
    echo json_encode([
        'status' => 'success',
        'batch_id' => $batch['batch_id'],
        'batch_no' => $batch['batch_no'],
        'price' => $batch['price'],
        'expiry' => $expiry,
        'qty' => $batch['available_qty']
    ]);
} else {
    echo json_encode([
        'status' => 'error',
        'message' => 'No available stock for this product'
    ]);
}
?>