<?php
include("dbConfig.php");

// Check if patientId is sent via POST
if (isset($_POST['patientId'])) {
    $patientId = $db->real_escape_string($_POST['patientId']);

    $db->query("SET SESSION sql_mode = ''");

    // SQL query to fetch all relevant details and calculate the total payable amount for the given PatientId
    $sql = "SELECT BillId, BillNo, PatientId, PatientType, TotAmount, PaymentMode, BDate, BTime, 
                   PayableAmount, InsuranceName, InsuranceNo, bank, cardno, upi, cash, PreparedBy, upiid,
                   SUM(PayableAmount) AS TotalPayableAmount
            FROM bill 
            WHERE PatientId = '$patientId' 
              AND is_deleted = 1  -- Add condition to check is_deleted is 1
            GROUP BY PatientId";

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

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        // Output the TotalPayableAmount
        // echo " " . $row['TotalPayableAmount'];
        $totalPayableAmount = $row['TotalPayableAmount'] ?? 0;
    }

    // Query to fetch total advance amount from advance table
    $sqlAdvance = "SELECT 
                      SUM(Amount) AS TotalAdvanceAmount 
                   FROM advance 
                   WHERE PatId = '$patientId'";

    $resultAdvance = $db->query($sqlAdvance);
    $totalAdvanceAmount = 0;

    if ($resultAdvance->num_rows > 0) {
        $rowAdvance = $resultAdvance->fetch_assoc();
        $totalAdvanceAmount = $rowAdvance['TotalAdvanceAmount'] ?? 0;
    }

    // Calculate the final payable amount after subtracting the advance amount
    $finalPayable = $totalPayableAmount - $totalAdvanceAmount;

    // Output the final payable amount
    echo " " . $finalPayable;
} else {
    // If no record found, return a default message
    echo "Invalid Request";
}


// Close the connection
$db->close();
