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

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Initialize the response structure with default values
$response = [
    'stock' => [
        'totalCount' => '0',
        'totalValue' => '0'
    ],
    'releases' => [
        'Today' => ['count' => '0', 'quantity' => '0', 'totalAmount' => '0'],
        'This Week' => ['count' => '0', 'quantity' => '0', 'totalAmount' => '0'],
        'This Month' => ['count' => '0', 'quantity' => '0', 'totalAmount' => '0']
    ],
    'productSummary' => [],
    'error' => 'false',
    'message' => ''
];

try {
    // 1. Stock totals from `store_stock`
    $stockQuery = "
        SELECT 
            COUNT(id) as totalStockCount, 
            COALESCE(SUM(quantity * productPrice), 0) as totalStockValue 
        FROM store_stock
    ";
    $stockResult = $connect->query($stockQuery);

    if ($stockResult === false) {
        throw new Exception("Error executing stock query: " . $connect->error);
    }

    $stockData = $stockResult->fetch_assoc();
    $response['stock'] = [
        'totalCount' => isset($stockData['totalStockCount']) ? (string)$stockData['totalStockCount'] : '0',
        'totalValue' => isset($stockData['totalStockValue']) ? (string)$stockData['totalStockValue'] : '0'
    ];

    // 2. Releases summary from `releaseRecords` including total amount
    $releasesQuery = "
        SELECT 
            COUNT(releaseRecords.id) AS releaseCount,
            COALESCE(SUM(releaseRecords.quantity), 0) AS totalQuantity,
            COALESCE(SUM(releaseRecords.quantity * store_stock.store_price), 0) AS totalAmount,
            CASE 
                WHEN DATE(releaseRecords._timestamp) = DATE(CURDATE()) THEN 'Today'
                WHEN WEEK(releaseRecords._timestamp) = WEEK(CURDATE()) THEN 'This Week'
                WHEN MONTH(releaseRecords._timestamp) = MONTH(CURDATE()) THEN 'This Month'
            END AS timeCategory
        FROM releaseRecords
        JOIN store_stock ON releaseRecords.productID = store_stock.productID
        WHERE DATE(releaseRecords._timestamp) >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
        GROUP BY timeCategory
    ";
    $releasesResult = $connect->query($releasesQuery);

    if ($releasesResult === false) {
        throw new Exception("Error executing releases query: " . $connect->error);
    }

    while ($row = $releasesResult->fetch_assoc()) {
        $category = $row['timeCategory'];
        if ($category) { // Exclude "Other" category
            $response['releases'][$category] = [
                'count' => isset($row['releaseCount']) ? (string)$row['releaseCount'] : '0',
                'quantity' => isset($row['totalQuantity']) ? (string)$row['totalQuantity'] : '0',
                'totalAmount' => isset($row['totalAmount']) ? (string)$row['totalAmount'] : '0'
            ];
        }
    }

    // 3. Product summary from `products` and `store_stock`
    $productSummaryQuery = "
        SELECT 
            products.productName,
            products.productPicture AS productImage, /* Adding product image */
            store_stock.size,
            store_stock.quantity,
            store_stock.productPrice,
            COALESCE(store_stock.quantity * store_stock.productPrice, 0) AS totalValue
        FROM products
        JOIN store_stock ON products.id = store_stock.productID
    ";
    $productSummaryResult = $connect->query($productSummaryQuery);

    if ($productSummaryResult === false) {
        throw new Exception("Error executing product summary query: " . $connect->error);
    }

    while ($row = $productSummaryResult->fetch_assoc()) {
        $response['productSummary'][] = [
            'productName' => $row['productName'],
            'productImage' => $row['productImage'], // Including product image in the response
            'size' => $row['size'],
            'quantity' => (string)$row['quantity'],
            'productPrice' => (string)$row['productPrice'],
            'totalValue' => (string)$row['totalValue']
        ];
    }

} catch (Exception $e) {
    $response['error'] = 'true';
    $response['message'] = $e->getMessage();
}

// Output the dashboard data as JSON
echo json_encode($response);
?>
