<?php
session_start();
ini_set('display_errors', 0);
error_reporting(E_ALL);
ob_start();

// Connect to database from application root (4 levels up from this file)
require_once '../db.php';
header('Content-Type: application/json');

$action = $_POST['action'] ?? '';

if ($action === 'fetch_dashboard') {
    $start_date = !empty($_POST['start_date']) ? $conn->real_escape_string($_POST['start_date']) : date('Y-m-d');
    $end_date = !empty($_POST['end_date']) ? $conn->real_escape_string($_POST['end_date']) : date('Y-m-d');
    $search = trim($conn->real_escape_string($_POST['search'] ?? ''));

    $response = ['status' => 'success', 'currency' => 'GHS', 'kpis' => [], 'table_data' => []];

    try {
        // Fetch System Currency if available
        $qSys = $conn->query("SELECT currency_symbol FROM system_workflow_rules LIMIT 1");
        if ($qSys && $rowSys = $qSys->fetch_assoc()) {
            if (!empty($rowSys['currency_symbol'])) {
                $response['currency'] = $rowSys['currency_symbol'];
            }
        }

        // KPI 1 & 2: Pending Bills & Expected Cash for Today
        $qKPI_Inv = $conn->query("
            SELECT COUNT(i.id) as pending_count, SUM(i.total_amount - i.amount_paid) as expected_cash 
            FROM billing_invoices i 
            WHERE i.status IN ('unpaid', 'partial') AND DATE(i.created_at) = '$start_date'
        ");
        $kpi_inv = $qKPI_Inv ? $qKPI_Inv->fetch_assoc() : ['pending_count' => 0, 'expected_cash' => 0];

        // KPI 3: Cash Collected Today from Payments
        $qKPI_Pay = $conn->query("
            SELECT SUM(p.amount) as collected_cash 
            FROM billing_payments p 
            WHERE DATE(p.created_at) = '$start_date'
        ");
        $kpi_pay = $qKPI_Pay ? $qKPI_Pay->fetch_assoc() : ['collected_cash' => 0];

        $response['kpis'] = [
            'pending_count' => (int)($kpi_inv['pending_count'] ?? 0),
            'expected_cash' => (float)($kpi_inv['expected_cash'] ?? 0),
            'collected_cash' => (float)($kpi_pay['collected_cash'] ?? 0)
        ];

        // Build Table Query with robust search handling (Patient Name, Patient ID, Invoice ID)
        $table_sql = "
            SELECT i.id as invoice_no, i.created_at, i.status, i.total_amount, i.amount_paid, 
                   (i.total_amount - i.amount_paid) as balance_due, 
                   pat.first_name, pat.last_name, pat.patient_no 
            FROM billing_invoices i 
            LEFT JOIN patient pat ON i.patient_id = pat.id 
        ";

        if (!empty($search)) {
            $table_sql .= " WHERE (i.id LIKE '%$search%' OR pat.patient_no LIKE '%$search%' OR pat.first_name LIKE '%$search%' OR pat.last_name LIKE '%$search%') ";
        } else {
            // Default to today's records if no search query is typed, allowing user to see bills saved today
            $table_sql .= " WHERE DATE(i.created_at) >= '$start_date' AND DATE(i.created_at) <= '$end_date' ";
        }

        $table_sql .= " ORDER BY CASE WHEN i.status IN ('unpaid', 'partial') THEN 1 ELSE 2 END, i.created_at DESC LIMIT 100";

        $qTable = $conn->query($table_sql);

        if ($qTable) {
            while ($row = $qTable->fetch_assoc()) {
                $row['date_formatted'] = date('d M, Y H:i', strtotime($row['created_at']));
                
                $patient_no = !empty($row['patient_no']) ? $row['patient_no'] : 'WALK-IN';
                $row['patient_name'] = ($patient_no === 'WALK-IN') 
                    ? 'Walk-In Client' 
                    : trim(($row['first_name'] ?? '') . ' ' . ($row['last_name'] ?? ''));
                $row['patient_no'] = $patient_no;
                
                $row['total_amount_fmt'] = number_format((float)$row['total_amount'], 2);
                $row['balance_due_fmt'] = number_format((float)$row['balance_due'], 2);
                $row['status'] = strtoupper($row['status']);
                
                $response['table_data'][] = $row;
            }
        } else {
            throw new Exception("Database Query Failed: " . $conn->error);
        }

        ob_end_clean();
        echo json_encode($response);
        exit;

    } catch (Exception $e) {
        ob_end_clean();
        echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
        exit;
    }
}
?>