<?php
ob_start(); // Start output buffering to capture any output

// Include your database connection code or configuration here
// ...
include 'database/shikanisha.kts.php';

// Retrieve distinct years from the data
$yearSql = "SELECT DISTINCT YEAR(cs.sale_date) AS year FROM transaction_receipts cs ORDER BY year DESC";
$yearResult = $conn->query($yearSql);
$yearOptions = '';
$currentYear = date('Y');

if ($yearResult->num_rows > 0) {
    while ($yearRow = $yearResult->fetch_assoc()) {
        $year = $yearRow["year"];
        $selected = ($year == $currentYear) ? 'selected' : '';
        $yearOptions .= "<option value=\"$year\" $selected>$year</option>";
    }
}

// Perform the database query to fetch the data
$selectedYear = isset($_GET['year']) ? $_GET['year'] : $currentYear;

// Use prepared statement to avoid SQL injection
$sql = "SELECT
    YEAR(cs.seller_date) AS year,
    MONTH(cs.seller_date) AS month,
    MONTHNAME(cs.seller_date) AS month_name,
    SUM(s.selling_price * s.quantity - p.buying_price * s.quantity) AS profit,
    (SUM(cs.total) - COALESCE(SUM(cs.amount_received), 0)) AS total_debt,
    SUM(cs.total) AS total_sales
FROM
    sales s
JOIN
    transaction_receipts cs ON s.trans_id = cs.trans_id
JOIN
    products p ON s.product_id = p.product_id
WHERE
    YEAR(cs.seller_date) = ?  -- Use a placeholder for the year
GROUP BY
    YEAR(cs.seller_date), MONTH(cs.seller_date), month_name
ORDER BY
    year, month;
";

$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $selectedYear);  // "i" stands for integer, adjust if needed
$stmt->execute();
$result = $stmt->get_result();
$stmt->close();

if ($result->num_rows > 0) {
    // Prepare arrays to store the data
    $labels = array();
    $totalSales = array();
    $profit = array();
    $totalDebt = array();

    while ($row = $result->fetch_assoc()) {
        $labels[] = $row["month_name"]; // Use the month_name alias
        $totalSales[] = $row["total_sales"];
        $profit[] = $row["profit"];
        $totalDebt[] = $row["total_debt"];
    }

    // Prepare the data array
    $data = array(
        'labels' => $labels,
        'totalSales' => $totalSales,
        'profit' => $profit,
        'totalDebt' => $totalDebt
    );

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

    // Encode the data array as JSON and output it
    echo json_encode($data);
} else {
    // Return an empty JSON object when there's no data
    echo json_encode(array());
}

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

// Clear the output buffer
ob_end_flush();
?>
