<?php
// get_best_deal_products.php - v6 (Corrected based on database schema)

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

if (!isset($conn) || $conn->connect_error) {
    http_response_code(500);
    echo json_encode([]);
    exit;
}

try {
    // --- THE FINAL FIX ---
    // 1. Join on `products.category` = `categories.name`
    // 2. Select `p.image_url` but alias it as `image_path` for the app
    $sql = "SELECT 
                c.name AS category_name, 
                p.id, 
                p.name, 
                p.regular_price, 
                p.offer_price,
                p.image_url AS image_path 
            FROM products p
            INNER JOIN categories c ON p.category = c.name
            WHERE c.best_deal = 1
            ORDER BY c.name, p.id DESC";

    $result = $conn->query($sql);
    
    if (!$result) {
        http_response_code(500);
        error_log("BestDeals Error: SQL Query Failed! " . $conn->error);
        echo json_encode([]);
        exit;
    }

    $grouped_products = [];
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $category_name = $row['category_name'];
            if (!isset($grouped_products[$category_name])) {
                $grouped_products[$category_name] = [];
            }
            $grouped_products[$category_name][] = $row;
        }
    }

    echo json_encode($grouped_products);

} catch (Exception $e) {
    http_response_code(500);
    error_log("BestDeals Error: A fatal PHP exception occurred: " . $e->getMessage());
    echo json_encode([]);
}

$conn->close();
?>