<?php
require('config.php');

$response = ["error" => false, "reportdetails" => []];

// Fetch unique product categories
$categoriesQuery = "SELECT DISTINCT productCategory FROM products";
$categoriesResult = $connect->query($categoriesQuery);

if ($categoriesResult->num_rows > 0) {
    while ($categoryRow = $categoriesResult->fetch_assoc()) {
        $category = $categoryRow['productCategory'];
        $categoryDetails = [];

        // Fetch unique qualities for this category
        $qualitiesQuery = "SELECT DISTINCT productQuality FROM products WHERE productCategory = ?";
        $stmtQualities = $connect->prepare($qualitiesQuery);
        $stmtQualities->bind_param("s", $category);
        $stmtQualities->execute();
        $qualitiesResult = $stmtQualities->get_result();

        if ($qualitiesResult->num_rows > 0) {
            while ($qualityRow = $qualitiesResult->fetch_assoc()) {
                $quality = $qualityRow['productQuality'];

                // Fetch unique product types for this quality and category
                $typesQuery = "SELECT DISTINCT productType FROM products WHERE productCategory = ? AND productQuality = ?";
                $stmtTypes = $connect->prepare($typesQuery);
                $stmtTypes->bind_param("ss", $category, $quality);
                $stmtTypes->execute();
                $typesResult = $stmtTypes->get_result();

                if ($typesResult->num_rows > 0) {
                    while ($typeRow = $typesResult->fetch_assoc()) {
                        $type = $typeRow['productType'];

                        // Fetch unique product colors for this type, quality, and category
                        $colorsQuery = "SELECT DISTINCT productColor FROM products WHERE productCategory = ? AND productQuality = ? AND productType = ?";
                        $stmtColors = $connect->prepare($colorsQuery);
                        $stmtColors->bind_param("sss", $category, $quality, $type);
                        $stmtColors->execute();
                        $colorsResult = $stmtColors->get_result();

                        if ($colorsResult->num_rows > 0) {
                            while ($colorRow = $colorsResult->fetch_assoc()) {
                                $color = $colorRow['productColor'];

                                // Fetch sizes for this color, type, quality, and category
                                $sizesQuery = "SELECT id, size, quantity FROM store_stock 
                                                WHERE productID IN (SELECT id FROM products WHERE productCategory = ? AND productQuality = ? AND productType = ? AND productColor = ?)";
                                $stmtSizes = $connect->prepare($sizesQuery);
                                $stmtSizes->bind_param("ssss", $category, $quality, $type, $color);
                                $stmtSizes->execute();
                                $sizesResult = $stmtSizes->get_result();

                                if ($sizesResult->num_rows > 0) {
                                    while ($sizeRow = $sizesResult->fetch_assoc()) {
                                        $size = $sizeRow['size'];
                                        $closingStock = $sizeRow['quantity']; // Directly from store_stock.quantity

                                        // Fetch total issues for this stockID
                                        $issuesQuery = "SELECT SUM(quantity) AS totalIssues FROM releaseRecords WHERE stockID = ?";
                                        $stmtIssues = $connect->prepare($issuesQuery);
                                        $stmtIssues->bind_param("i", $sizeRow['id']);
                                        $stmtIssues->execute();
                                        $issuesResult = $stmtIssues->get_result();

                                        $issues = 0;
                                        if ($issuesResult->num_rows > 0) {
                                            $issuesRow = $issuesResult->fetch_assoc();
                                            $issues = $issuesRow['totalIssues'] ?: 0;
                                        }

                                        // Receipts are the sum of issues and closing stock
                                        $receipts = $issues + $closingStock;

                                        // Add the record to the category details
                                        $categoryDetails[] = [
                                            "quality" => $quality,
                                            "type" => $type,
                                            "color" => $color,
                                            "size" => $size,
                                            "receipts" => $receipts,
                                            "issues" => $issues,
                                            "closingStock" => $closingStock
                                        ];
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        // Only add the category if it has details
        if (!empty($categoryDetails)) {
            $response['reportdetails'][] = [
                "category" => $category,
                "details" => $categoryDetails
            ];
        }
    }
} else {
    $response['error'] = true;
    $response['message'] = "No categories found.";
}

// Output the response as JSON
echo json_encode($response, JSON_PRETTY_PRINT);
?>
