<?php
session_start();
include '../layouts/dbconn.php';

if (!isset($_SESSION['user_id'])) {
    echo json_encode([]);
    exit();
}

$search = $_GET['search'] ?? '';

$sql = "SELECT 
            v.id,
            v.invoice_number,
            CONCAT(p.first_name, ' ', p.last_name) as patient_name,
            u.phone as patient_phone,
            v.total_amount,
            COALESCE(SUM(pay.amount), 0) as paid_amount,
            (v.total_amount - COALESCE(SUM(pay.amount), 0)) as balance
        FROM visits v
        INNER JOIN patients p ON v.patient_id = p.id
        INNER JOIN users u ON p.user_id = u.id
        LEFT JOIN payments pay ON v.id = pay.visit_id AND pay.status = 'COMPLETED'
        WHERE v.status = 'PENDING'
        AND (v.total_amount - COALESCE(SUM(pay.amount), 0)) > 0
        AND (
            CONCAT(p.first_name, ' ', p.last_name) LIKE ?
            OR u.phone LIKE ?
            OR v.invoice_number LIKE ?
        )
        GROUP BY v.id
        HAVING balance > 0
        ORDER BY v.created_at DESC
        LIMIT 20";

$searchParam = "%$search%";
$stmt = $conn->prepare($sql);
$stmt->bind_param('sss', $searchParam, $searchParam, $searchParam);
$stmt->execute();
$result = $stmt->get_result();

$data = [];
while ($row = $result->fetch_assoc()) {
    $data[] = [
        'id' => $row['id'],
        'text' => sprintf(
            "%s - %s (Balance: KES %s)",
            $row['invoice_number'],
            $row['patient_name'],
            number_format($row['balance'], 2)
        )
    ];
}

echo json_encode($data);
?>