<?php
session_start();
include 'layouts/session.php';
include 'layouts/dbconn.php';
// Check authentication
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}
$user_id = $_SESSION['user_id'];
// Get filters
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-m-d', strtotime('-7 days'));
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-d');
$status_filter = isset($_GET['status']) ? $_GET['status'] : '';
$search = isset($_GET['search']) ? $_GET['search'] : '';

// Pre-assign the base date parameters as variables (for reference binding)
$param_date_from = $date_from . ' 00:00:00';
$param_date_to = $date_to . ' 23:59:59';

// Build query and types
$sql = "SELECT
    lo.order_id,
    lo.order_number,
    lo.priority,
    lo.status,
    lo.total_cost,
    lo.ordered_at,
    p.patient_id,
    p.patient_number,
    u.full_name as patient_name,
    u.phone_primary,
    u.full_name as ordered_by_name,
    pv.registration_type,
    pv.smart_session_id,
    (SELECT COUNT(*) FROM lab_order_tests WHERE order_id = lo.order_id) as total_tests,
    (SELECT COUNT(*) FROM lab_order_tests WHERE order_id = lo.order_id AND status = 'COMPLETED') as completed_tests,
    (SELECT GROUP_CONCAT(DISTINCT ett.category SEPARATOR ', ')
     FROM lab_order_tests lot
     JOIN ent_test_types ett ON lot.test_type_id = ett.test_type_id
     WHERE lot.order_id = lo.order_id) as test_categories
FROM lab_orders lo
JOIN patients p ON lo.patient_id = p.patient_id
JOIN users u ON lo.ordered_by = u.user_id
LEFT JOIN patient_visits pv ON lo.visit_id = pv.visit_id
WHERE lo.ordered_at BETWEEN ? AND ?";
$types = "ss";  // Base types for dates

$has_status = !empty($status_filter);
$has_search = !empty($search);

if ($has_status) {
    $sql .= " AND lo.status = ?";
    $types .= "s";
    $param_status = $status_filter;  // Assign to variable
}

if ($has_search) {
    $sql .= " AND (lo.order_number LIKE ? OR p.patient_number LIKE ? OR p.full_name LIKE ?)";
    $types .= "sss";
    $param_search = "%$search%";  // Single variable, reused for all 3
    // Note: We'll pass $param_search three times in bind_param
}

$sql .= " ORDER BY lo.ordered_at DESC LIMIT 100";

$stmt = $conn->prepare($sql);
if (!$stmt) {
    die('Prepare failed: ' . $conn->error);
}

// Bind parameters based on active filters (always at least 2)
if (!$has_status && !$has_search) {
    // Only dates
    $stmt->bind_param($types, $param_date_from, $param_date_to);
} elseif ($has_status && !$has_search) {
    // Dates + status
    $stmt->bind_param($types, $param_date_from, $param_date_to, $param_status);
} elseif (!$has_status && $has_search) {
    // Dates + search (3x same param)
    $stmt->bind_param($types, $param_date_from, $param_date_to, $param_search, $param_search, $param_search);
} else {
    // Dates + status + search
    $stmt->bind_param($types, $param_date_from, $param_date_to, $param_status, $param_search, $param_search, $param_search);
}

$stmt->execute();
$lab_orders = $stmt->get_result();

// Get statistics (unchanged, as it was already correct)
$stats_sql = "SELECT
    COUNT(*) as total_orders,
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) as pending,
    SUM(CASE WHEN status = 'IN_PROGRESS' THEN 1 ELSE 0 END) as in_progress,
    SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) as completed,
    SUM(total_cost) as total_revenue
FROM lab_orders
WHERE ordered_at BETWEEN ? AND ?";
$stats_stmt = $conn->prepare($stats_sql);
if (!$stats_stmt) {
    die('Stats prepare failed: ' . $conn->error);
}
$stats_stmt->bind_param("ss", $param_date_from, $param_date_to);  // Reuse the date vars
$stats_stmt->execute();
$stats = $stats_stmt->get_result()->fetch_assoc();
?>

<?php include 'layouts/main.php'; ?>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Lab Orders')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
    <link href="assets/libs/flatpickr/flatpickr.min.css" rel="stylesheet">
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Page Title -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">
                                    <i class="ri-test-tube-line me-2"></i>Laboratory Orders
                                </h4>
                                <div class="page-title-right">
                                    <a href="create_lab_order.php" class="btn btn-primary">
                                        <i class="ri-add-line me-1"></i>New Lab Order
                                    </a>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Statistics Cards -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium text-uppercase mb-3">Total Orders</p>
                                            <h4 class="fs-22 fw-semibold mb-0"><?php echo $stats['total_orders']; ?></h4>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-primary-subtle text-primary rounded fs-3">
                                                <i class="ri-file-list-3-line"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium text-uppercase mb-3">Pending</p>
                                            <h4 class="fs-22 fw-semibold mb-0 text-warning"><?php echo $stats['pending']; ?></h4>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-warning-subtle text-warning rounded fs-3">
                                                <i class="ri-time-line"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium text-uppercase mb-3">In Progress</p>
                                            <h4 class="fs-22 fw-semibold mb-0 text-info"><?php echo $stats['in_progress']; ?></h4>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-info-subtle text-info rounded fs-3">
                                                <i class="ri-loader-4-line"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium text-uppercase mb-3">Revenue</p>
                                            <h4 class="fs-22 fw-semibold mb-0">
                                                KES <?php echo number_format($stats['total_revenue'], 2); ?>
                                            </h4>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-success-subtle text-success rounded fs-3">
                                                <i class="ri-money-dollar-circle-line"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filters & Search -->
                    <div class="card">
                        <div class="card-body">
                            <form method="GET" class="row g-3">
                                <div class="col-md-3">
                                    <label class="form-label">From Date</label>
                                    <input type="text" class="form-control flatpickr-date" name="date_from" 
                                           value="<?php echo $date_from; ?>">
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">To Date</label>
                                    <input type="text" class="form-control flatpickr-date" name="date_to" 
                                           value="<?php echo $date_to; ?>">
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label">Status</label>
                                    <select class="form-select" name="status">
                                        <option value="">All Status</option>
                                        <option value="PENDING" <?php echo ($status_filter == 'PENDING') ? 'selected' : ''; ?>>Pending</option>
                                        <option value="SCHEDULED" <?php echo ($status_filter == 'SCHEDULED') ? 'selected' : ''; ?>>Scheduled</option>
                                        <option value="SAMPLE_COLLECTED" <?php echo ($status_filter == 'SAMPLE_COLLECTED') ? 'selected' : ''; ?>>Sample Collected</option>
                                        <option value="IN_PROGRESS" <?php echo ($status_filter == 'IN_PROGRESS') ? 'selected' : ''; ?>>In Progress</option>
                                        <option value="COMPLETED" <?php echo ($status_filter == 'COMPLETED') ? 'selected' : ''; ?>>Completed</option>
                                    </select>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Search</label>
                                    <input type="text" class="form-control" name="search" 
                                           placeholder="Order #, Patient..." value="<?php echo htmlspecialchars($search); ?>">
                                </div>
                                <div class="col-md-1">
                                    <label class="form-label">&nbsp;</label>
                                    <button type="submit" class="btn btn-primary w-100">
                                        <i class="ri-search-line"></i>
                                    </button>
                                </div>
                            </form>
                        </div>
                    </div>

                    <!-- Orders Table -->
                    <div class="card">
                        <div class="card-header">
                            <div class="d-flex align-items-center">
                                <h5 class="card-title flex-grow-1 mb-0">Lab Orders</h5>
                                <div class="flex-shrink-0">
                                    <button class="btn btn-success btn-sm" onclick="exportToExcel()">
                                        <i class="ri-file-excel-line me-1"></i>Export
                                    </button>
                                </div>
                            </div>
                        </div>
                        <div class="card-body">
                            <div class="table-responsive">
                                <table class="table table-hover align-middle mb-0">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Order #</th>
                                            <th>Patient</th>
                                            <th>Tests</th>
                                            <th>Priority</th>
                                            <th>Ordered By</th>
                                            <th>Date</th>
                                            <th>Progress</th>
                                            <th>Amount</th>
                                            <th>Status</th>
                                            <th>Actions</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php if ($lab_orders->num_rows > 0): ?>
                                            <?php while($order = $lab_orders->fetch_assoc()): ?>
                                                <?php
                                                $priority_badge = [
                                                    'STAT' => 'bg-danger',
                                                    'URGENT' => 'bg-warning',
                                                    'ROUTINE' => 'bg-info'
                                                ];
                                                $priority_class = $priority_badge[$order['priority']] ?? 'bg-secondary';
                                                
                                                $status_badge = [
                                                    'PENDING' => 'bg-warning',
                                                    'SCHEDULED' => 'bg-primary',
                                                    'SAMPLE_COLLECTED' => 'bg-info',
                                                    'IN_PROGRESS' => 'bg-info',
                                                    'COMPLETED' => 'bg-success',
                                                    'CANCELLED' => 'bg-secondary'
                                                ];
                                                $status_class = $status_badge[$order['status']] ?? 'bg-secondary';
                                                
                                                $progress_percent = ($order['completed_tests'] / $order['total_tests']) * 100;
                                                ?>
                                                <tr>
                                                    <td>
                                                        <strong><?php echo $order['order_number']; ?></strong>
                                                    </td>
                                                    <td>
                                                        <div>
                                                            <strong><?php echo htmlspecialchars($order['patient_name']); ?></strong>
                                                            <?php if ($order['smart_session_id']): ?>
                                                                <span class="badge bg-primary-subtle text-primary ms-1">SMART</span>
                                                            <?php endif; ?>
                                                        </div>
                                                        <small class="text-muted"><?php echo $order['patient_number']; ?></small>
                                                    </td>
                                                    <td>
                                                        <div>
                                                            <strong><?php echo $order['total_tests']; ?></strong> test(s)
                                                        </div>
                                                        <small class="text-muted">
                                                            <?php echo substr($order['test_categories'], 0, 30); ?>...
                                                        </small>
                                                    </td>
                                                    <td>
                                                        <span class="badge <?php echo $priority_class; ?>">
                                                            <?php echo $order['priority']; ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <small><?php echo htmlspecialchars($order['ordered_by_name']); ?></small>
                                                    </td>
                                                    <td>
                                                        <div><?php echo date('M d, Y', strtotime($order['ordered_at'])); ?></div>
                                                        <small class="text-muted"><?php echo date('h:i A', strtotime($order['ordered_at'])); ?></small>
                                                    </td>
                                                    <td>
                                                        <div class="d-flex align-items-center">
                                                            <div class="flex-grow-1">
                                                                <div class="progress" style="height: 6px;">
                                                                    <div class="progress-bar bg-success" 
                                                                         style="width: <?php echo $progress_percent; ?>%"></div>
                                                                </div>
                                                            </div>
                                                            <span class="ms-2">
                                                                <?php echo $order['completed_tests']; ?>/<?php echo $order['total_tests']; ?>
                                                            </span>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <strong>KES <?php echo number_format($order['total_cost'], 2); ?></strong>
                                                    </td>
                                                    <td>
                                                        <span class="badge <?php echo $status_class; ?>">
                                                            <?php echo str_replace('_', ' ', $order['status']); ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <div class="btn-group btn-group-sm">
                                                            <a href="view_lab_order.php?id=<?php echo $order['order_id']; ?>" 
                                                               class="btn btn-outline-primary" title="View Details">
                                                                <i class="ri-eye-line"></i>
                                                            </a>
                                                            <a href="patient_profile.php?id=<?php echo $order['patient_id']; ?>" 
                                                               class="btn btn-outline-info" title="Patient Profile">
                                                                <i class="ri-user-line"></i>
                                                            </a>
                                                            <?php if ($order['status'] != 'CANCELLED'): ?>
                                                                <button onclick="printOrder(<?php echo $order['order_id']; ?>)" 
                                                                        class="btn btn-outline-secondary" title="Print">
                                                                    <i class="ri-printer-line"></i>
                                                                </button>
                                                            <?php endif; ?>
                                                        </div>
                                                    </td>
                                                </tr>
                                            <?php endwhile; ?>
                                        <?php else: ?>
                                            <tr>
                                                <td colspan="10" class="text-center py-4">
                                                    <i class="ri-file-list-3-line fs-1 text-muted"></i>
                                                    <p class="text-muted mt-2">No lab orders found</p>
                                                </td>
                                            </tr>
                                        <?php endif; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
    <script src="assets/libs/flatpickr/flatpickr.min.js"></script>

    <script>
        flatpickr('.flatpickr-date', {
            dateFormat: 'Y-m-d'
        });

        function printOrder(orderId) {
            window.open('print_lab_order.php?id=' + orderId, '_blank');
        }

        function exportToExcel() {
            window.location.href = 'export_lab_orders.php?' + $('form').serialize();
        }
    </script>
</body>
</html>