<?php
//  Database connection string
require '../../database/shikanisha.kts.php';


if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Get the current month
$currentMonth = date('m');

$topSellingQuery = "SELECT s.product_name, s.product_id, SUM(s.quantity) AS total_quantity
                    FROM sales s
                    JOIN transaction_receipts tr ON s.trans_id = tr.trans_id
                    WHERE MONTH(tr.seller_date) = $currentMonth
                    GROUP BY s.product_id
                    ORDER BY total_quantity DESC
                    LIMIT 5";
// Execute the query and fetch the results
$topSellingResult = $conn->query($topSellingQuery);



if (!$topSellingResult) {
    die('Error fetching top selling data: ' . $conn->error);
}

$topSellingData = array();
while ($row = $topSellingResult->fetch_assoc()) {
    $topSellingData[] = $row;
}


// Iterate through the results and calculate progress, buying price, and budget
$tableData = [];
foreach ($topSellingData as $row) {
    $productName = $row['product_name'];
    $productId = $row['product_id'];
    $currentMonthQuantity = $row['total_quantity'];

    // Query to get the quantity from the previous month
    $previousMonth = ($currentMonth - 1 == 0) ? 12 : $currentMonth - 1;
    $previousMonthQuery = "SELECT SUM(s.quantity) AS total_quantity
                      FROM sales s
                      JOIN transaction_receipts tr ON s.trans_id = tr.trans_id
                      WHERE MONTH(tr.seller_date) = $previousMonth AND s.product_id = '$productId'";

    $previousMonthResult = $conn->query($previousMonthQuery);
    $previousMonthData = $previousMonthResult->fetch_assoc();
    $previousMonthQuantity = ($previousMonthData) ? $previousMonthData['total_quantity'] : 0;

    // Calculate progress
    $progressPercentage = ($previousMonthQuantity > 0) ? (($currentMonthQuantity - $previousMonthQuantity) / $previousMonthQuantity) * 100 : 0;

    // Query to get buying price
    $buyingPriceQuery = "SELECT buying_price FROM products WHERE product_id = '$productId'";
    $buyingPriceResult = $conn->query($buyingPriceQuery);
    $buyingPriceData = $buyingPriceResult->fetch_assoc();
    $buyingPrice = ($buyingPriceData) ? $buyingPriceData['buying_price'] : 0;

    // Calculate budget
    $budget = $buyingPrice * $currentMonthQuantity;

    // Add data to the tableData array
    $tableData[] = [
        'productName' => $productName,
        'quantity' => $currentMonthQuantity,
        'progress' => number_format($progressPercentage, 2),
        'budget' => number_format($budget, 2),
    ];
}

// Encode the tableData array as JSON and send it to the client
echo json_encode($tableData);

// Close the database connection
$conn->close();
?>
