<?php
header("Content-Type: application/json");
require('config.php');

// Initialize response array with a static structure
$response = [
    "error" => false,
    "data" => [
        "today" => [
            "Cash" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0],
            "Credit" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0]
        ],
        "this_week" => [
            "Cash" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0],
            "Credit" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0]
        ],
        "this_month" => [
            "Cash" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0],
            "Credit" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0]
        ],
        "this_year" => [
            "Cash" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0],
            "Credit" => ["transaction_count" => 0, "total_quantity" => 0, "total_sales" => 0.0]
        ]
    ],
    "message" => "Sales data retrieved successfully."
];

// Define time periods
$todayStart = date('Y-m-d');
$thisWeekStart = date('Y-m-d', strtotime('monday this week'));
$thisMonthStart = date('Y-m-01');
$thisYearStart = date('Y-01-01');

// Periods and their conditions
$periods = [
    "today" => "DATE(releaseDate) = '$todayStart'",
    "this_week" => "DATE(releaseDate) >= '$thisWeekStart' AND DATE(releaseDate) <= DATE(NOW())",
    "this_month" => "DATE(releaseDate) >= '$thisMonthStart' AND DATE(releaseDate) <= DATE(NOW())",
    "this_year" => "DATE(releaseDate) >= '$thisYearStart' AND DATE(releaseDate) <= DATE(NOW())"
];

// Fetch sales data for each period
try {
    foreach ($periods as $key => $condition) {
        $query = "
            SELECT 
                transactions.productCode,
                transactions.releaseType,
                COUNT(transactions.id) AS transaction_count,
                SUM(COALESCE(transactions.quantity, 0)) AS total_quantity,
                SUM(COALESCE(transactions.amount, 0)) AS total_sales
            FROM transactions
            WHERE $condition
            GROUP BY transactions.productCode, transactions.releaseType
        ";

        $result = mysqli_query($connect, $query);

        if ($result) {
            while ($row = mysqli_fetch_assoc($result)) {
                $productID = $row['productCode'];
                $releaseType = $row['releaseType'] ?? "Unknown";

                // Ensure that the product data exists in the response structure
                if (!isset($response["data"][$key][$releaseType])) {
                    $response["data"][$key][$releaseType] = [
                        "transaction_count" => 0,
                        "total_quantity" => 0,
                        "total_sales" => 0.0
                    ];
                }

                // Update the corresponding releaseType data for the product
                $response["data"][$key][$releaseType]["transaction_count"] += (int)$row['transaction_count'];
                $response["data"][$key][$releaseType]["total_quantity"] += (int)$row['total_quantity'];
                $response["data"][$key][$releaseType]["total_sales"] += (float)$row['total_sales'];
            }
        } else {
            throw new Exception("Error retrieving data for $key: " . mysqli_error($connect));
        }
    }
} catch (Exception $e) {
    $response["error"] = true;
    $response["message"] = $e->getMessage();
}

// Output JSON response
echo json_encode($response);

// Close the database connection
mysqli_close($connect);
?>
