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

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

$week = $_GET['week'];

// Get unique payment methods, including in-house
$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()) {
        // Sanitize the payment method names for use as column names
        $sanitized_method = preg_replace('/[^a-zA-Z0-9_]/', '_', $row['payment_method']);
        $payment_methods[$row['payment_method']] = $sanitized_method;
    }
}

// Construct dynamic columns for payment methods
$payment_method_columns = array_map(function ($method, $sanitized_method) {
    return "COALESCE(SUM(CASE WHEN t.payment_method = '{$method}' THEN t.amount ELSE 0 END), 0) AS {$sanitized_method}_total";
}, array_keys($payment_methods), $payment_methods);

$payment_method_columns_sql = implode(", ", $payment_method_columns);

// Fetch weekly data
$sql = "SELECT
    DAYNAME(DATE(cs.seller_date)) AS day,
    COALESCE(SUM(cs.total), 0) AS total_sales,
    COALESCE(SUM(cs.amount_received), 0) AS total_received,
    COALESCE(SUM(cs.total) - SUM(cs.amount_received), 0) AS total_debt,
    $payment_method_columns_sql
FROM transaction_receipts cs
LEFT JOIN payments t ON cs.trans_id = t.trans_id
WHERE YEARWEEK(cs.seller_date) = ?
GROUP BY DAYNAME(DATE(cs.seller_date))
ORDER BY DAYOFWEEK(cs.seller_date)";

// Debug: Print the constructed SQL query
// echo $sql;

$stmt = $conn->prepare($sql);

if ($stmt === false) {
    die("Error preparing statement: " . $conn->error);
}

$stmt->bind_param('s', $week);
$stmt->execute();
$result = $stmt->get_result();

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

while ($row = $result->fetch_assoc()) {
    $labels[] = $row["day"];
    foreach ($payment_methods as $method => $sanitized_method) {
        $data[$sanitized_method][] = $row["{$sanitized_method}_total"];
    }
    $debt[] = $row["total_debt"];
}

$response = [
    'labels' => $labels,
    'data' => $data,
    'debt' => $debt
];
echo json_encode($response);

$stmt->close();
$conn->close();
?>
