<?php
// CORRECTED SCRIPT for get_products.php

// --- Basic Setup & Error Handling ---
error_reporting(E_ALL);
ini_set('display_errors', 0); // Do not display errors to the userini_set('log_errors', 1);     // Log errors to the server's error log

// Include your database connection file
require 'db_connect.php';

// Set the content type to JSON
header('Content-Type: application/json');
$response = [];

// --- Validate Database Connection ---
if (!isset($conn) || $conn->connect_error) {
    http_response_code(500);
    $response['status'] = 'error';
    $response['message'] = 'Server Error: Database connection failed.';
    echo json_encode($response);
    exit;
}

// --- Build the SQL Query with Rating Calculation ---
// This is the key change: We join the 'reviews' table and use AVG() and COUNT()
$sql = "SELECT
            p.id, p.name, p.category, p.brand_id, b.name as brand_name, p.models,
            p.description, p.specification, p.regular_price, p.offer_price,
            p.image_url, p.gallery_images,
            COALESCE(AVG(r.rating), 0) as rating,
            COUNT(r.id) as total_reviews
        FROM
            products p
        LEFT JOIN
            brands b ON p.brand_id = b.id
        LEFT JOIN
            reviews r ON p.id = r.product_id";

// --- Add Filters based on GET Parameters (for searching) ---
$where_clauses = [];
$params = [];
$types = "";

if (isset($_GET['category']) && !empty($_GET['category'])) {
    $where_clauses[] = "p.category = ?";
    $params[] = $_GET['category'];
    $types .= "s";
}

if (isset($_GET['brand_id']) && !empty($_GET['brand_id'])) {
    $where_clauses[] = "p.brand_id = ?";
    $params[] = (int)$_GET['brand_id'];
    $types .= "i";
}

if (isset($_GET['id']) && !empty($_GET['id'])) {
    $where_clauses[] = "p.id = ?";
    $params[] = (int)$_GET['id'];
    $types .= "i";
}

if (!empty($where_clauses)) {
    $sql .= " WHERE " . implode(" AND ", $where_clauses);
}

// --- Add Grouping and Ordering ---
// GROUP BY is essential for the AVG() and COUNT() to work for each product
$sql .= " GROUP BY p.id";
$sql .= " ORDER BY p.id ASC";

if (!isset($_GET['id'])) { // Don't limit if fetching a single product
    $sql .= " LIMIT 300";
}

// --- Prepare and Execute the Query ---
$stmt = $conn->prepare($sql);

if ($stmt) {
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $result = $stmt->get_result();

    $products = [];
    while ($row = $result->fetch_assoc()) {
        // Ensure data types are correct for the app
        $row['rating'] = (float)$row['rating'];
        $row['total_reviews'] = (int)$row['total_reviews'];
        // Ensure price fields are also floats/doubles
        $row['regular_price'] = (float)$row['regular_price'];
        $row['offer_price'] = (float)$row['offer_price'];
        $products[] = $row;
    }

    // --- Success: Send the JSON response ---
    if (isset($_GET['id']) && count($products) == 1) {
        // If a single product was requested, return just that object
        echo json_encode($products[0]);
    } else {
        // Otherwise, return the array of products
        echo json_encode($products);
    }

} else {
    // --- Failure ---
    http_response_code(500);
    $response['status'] = 'error';
    $response['message'] = 'Database Error: Could not prepare the statement.';
    echo json_encode($response);
}

$stmt->close();
$conn->close();

?>