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

$sql = "SELECT
    b.`booking_id`,
    r.`room_name`,
    COALESCE(total_debt.total_debt, 0) AS `total_debt`,
    COALESCE(total_payments.total_payments, 0) AS `total_payments`
FROM
    `bookings` b
JOIN
    `rooms` r ON b.`room_id` = r.`room_id`
LEFT JOIN (
    SELECT
        rb.`booking_id`,
        SUM(rb.`amount`) AS `total_debt`
    FROM
        `room_bills` rb
    GROUP BY
        rb.`booking_id`
) AS total_debt ON b.`booking_id` = total_debt.`booking_id`
LEFT JOIN (
    SELECT
        rp.`booking_id`,
        SUM(rp.`paid_amount`) AS `total_payments`
    FROM
        `room_payments` rp
    GROUP BY
        rp.`booking_id`
) AS total_payments ON b.`booking_id` = total_payments.`booking_id`
HAVING
    (`total_debt` - `total_payments`) <> 0";

$result = $conn->query($sql);

$data = array();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $row['balance'] = $row['total_debt'] - $row['total_payments'];
        $data[] = $row;
    }
}

echo json_encode(['data' => $data]);
?>
