<?php
include 'shikanisha.kts.php';

if (isset($_GET['year'])) {
    $selectedYear = $_GET['year'];

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

    // Fetch unique payment methods
    $payment_methods_sql = "SELECT DISTINCT payment_method FROM payments";
    $payment_methods_result = $conn->query($payment_methods_sql);

    $payment_methods = [];
    if ($payment_methods_result->num_rows > 0) {
        while ($row = $payment_methods_result->fetch_assoc()) {
            $payment_methods[] = $row['payment_method'];
        }
    }

    // Prepare dynamic part of the SQL query
    $payment_methods_select = [];
    foreach ($payment_methods as $method) {
        $method_safe = $conn->real_escape_string($method); // Sanitize method name
        $payment_methods_select[] = "SUM(CASE WHEN t.payment_method = '{$method_safe}' THEN t.amount ELSE 0 END) AS '{$method_safe}_total'";
    }
    $payment_methods_select_sql = implode(", ", $payment_methods_select);

    // Prepare SQL query
    $sql = "SELECT 
                MONTHNAME(cs.seller_date) AS month, 
                SUM(cs.total) AS total_sales,
                SUM(cs.amount_received) AS total_received,
                SUM(cs.total) - SUM(cs.amount_received) AS total_debt,
                $payment_methods_select_sql
            FROM transaction_receipts cs
            LEFT JOIN payments t ON cs.trans_id = t.trans_id
            WHERE YEAR(cs.seller_date) = ?
            GROUP BY MONTH(cs.seller_date), MONTHNAME(cs.seller_date)
            ORDER BY MONTH(cs.seller_date)";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        die("Error preparing statement: " . $conn->error);
    }
    $stmt->bind_param('i', $selectedYear);
    $stmt->execute();
    $result = $stmt->get_result();

    $labels = array();
    $data = array();
    foreach ($payment_methods as $method) {
        $data[$method] = [];
    }
    $debt = array();

    while ($row = $result->fetch_assoc()) {
        $labels[] = $row["month"];
        foreach ($payment_methods as $method) {
            $method_safe = $conn->real_escape_string($method); // Sanitize method name
            $data[$method][] = $row["{$method_safe}_total"];
        }
        $debt[] = $row["total_debt"];
    }

    // Ensure all months are included with default values
    $months = array('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
    foreach ($months as $month) {
        if (!in_array($month, $labels)) {
            $labels[] = $month;
            foreach ($payment_methods as $method) {
                $data[$method][] = 0;
            }
            $debt[] = 0;
        }
    }

    // Sort the labels and data arrays by month order
    array_multisort(array_map(function($month) use ($months) {
        return array_search($month, $months);
    }, $labels), $labels, $debt);

    foreach ($payment_methods as $method) {
        array_multisort(array_map(function($month) use ($months) {
            return array_search($month, $months);
        }, $labels), $data[$method]);
    }

    $response = array(
        'labels' => $labels,
        'data' => $data,
        'debt' => $debt
    );

    echo json_encode($response);

    $stmt->close();
    $conn->close();
} else {
    echo "Invalid request";
}
?>
