<?php
/**
 * Test Reports Page
 * Audiologist Role - Generate and View Test Reports
 */

require_once 'layouts/session.php';
require_once 'layouts/dbconn.php';

if (!isset($_SESSION['user_id']) || $_SESSION['role_code'] != 'AUDIOLOGIST') {
    header("Location: login.php");
    exit();
}

$page_title = "Test Reports";
$user_id = $_SESSION['user_id'];

// Get filter parameters
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-t');
$test_category = $_GET['test_category'] ?? 'ALL';

// Build query based on filters
$where_clauses = ["lot.tested_by = ?", "lot.status = 'COMPLETED'", "ett.category IN ('AUDIOMETRY')"];
$params = [$user_id];
$param_types = "i";

if ($date_from) {
    $where_clauses[] = "DATE(lot.completed_at) >= ?";
    $params[] = $date_from;
    $param_types .= "s";
}
if ($date_to) {
    $where_clauses[] = "DATE(lot.completed_at) <= ?";
    $params[] = $date_to;
    $param_types .= "s";
}
if ($test_category != 'ALL') {
    $where_clauses[] = "ett.test_code LIKE ?";
    $params[] = $test_category . '%';
    $param_types .= "s";
}

$where_sql = implode(' AND ', $where_clauses);

// Get report data
$reports_sql = "SELECT 
                    ett.test_name,
                    COUNT(*) as test_count,
                    COUNT(CASE WHEN lot.abnormal_flag = 'NORMAL' THEN 1 END) as normal_count,
                    COUNT(CASE WHEN lot.abnormal_flag IN ('ABNORMAL', 'HIGH', 'LOW') THEN 1 END) as abnormal_count,
                    COUNT(CASE WHEN lot.abnormal_flag = 'CRITICAL' THEN 1 END) as critical_count,
                    AVG(lot.test_cost) as avg_cost
                FROM lab_order_tests lot
                INNER JOIN lab_orders lo ON lot.order_id = lo.order_id
                INNER JOIN ent_test_types ett ON lot.test_type_id = ett.test_type_id
                WHERE $where_sql
                GROUP BY ett.test_name
                ORDER BY test_count DESC";

$stmt = $conn->prepare($reports_sql);
$stmt->bind_param($param_types, ...$params);
$stmt->execute();
$reports_result = $stmt->get_result();

// Get summary statistics
$summary_sql = "SELECT 
                    COUNT(*) as total_tests,
                    COUNT(DISTINCT lo.patient_id) as unique_patients,
                    COUNT(CASE WHEN lot.abnormal_flag = 'NORMAL' THEN 1 END) as normal_count,
                    COUNT(CASE WHEN lot.abnormal_flag != 'NORMAL' THEN 1 END) as abnormal_count
                FROM lab_order_tests lot
                INNER JOIN lab_orders lo ON lot.order_id = lo.order_id
                INNER JOIN ent_test_types ett ON lot.test_type_id = ett.test_type_id
                WHERE $where_sql";

$stmt = $conn->prepare($summary_sql);
$stmt->bind_param($param_types, ...$params);
$stmt->execute();
$summary = $stmt->get_result()->fetch_assoc();
?>

<?php include 'layouts/main.php'; ?>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => $page_title)); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/datatables.net-bs5/css/dataTables.bootstrap5.min.css" rel="stylesheet" />
    <link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
    <style>
        .page-header { background: white; padding: 1.5rem; border-radius: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1); margin-bottom: 1.5rem; }
        .stat-card { background: white; border-radius: 12px; padding: 1.5rem; box-shadow: 0 1px 3px rgba(0,0,0,0.1); text-align: center; }
        .stat-value { font-size: 2rem; font-weight: 700; margin-bottom: 0.5rem; }
        .info-card { background: white; border-radius: 12px; padding: 1.5rem; box-shadow: 0 1px 3px rgba(0,0,0,0.1); margin-bottom: 1.5rem; }
        .info-card h5 { font-size: 1.125rem; font-weight: 600; margin-bottom: 1.25rem; padding-bottom: 0.75rem; border-bottom: 2px solid #e2e8f0; }
        .filter-box { background: #f8fafc; border-radius: 8px; padding: 1.5rem; margin-bottom: 1.5rem; border: 1px solid #e2e8f0; }
    </style>
</head>
<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    
                    <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"><?php echo $page_title; ?></h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="index.php">Dashboard</a></li>
                                        <li class="breadcrumb-item">Test Management</li>
                                        <li class="breadcrumb-item active"><?php echo $page_title; ?></li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="page-header">
                        <h4><i class="ri-bar-chart-box-line me-2" style="color: #2d7a2d;"></i>Audiology Test Reports</h4>
                        <p class="mb-0">Generate statistical reports for completed tests</p>
                    </div>
                    
                    <!-- Filter Form -->
                    <div class="filter-box">
                        <form method="GET" class="row g-3">
                            <div class="col-md-3">
                                <label class="form-label">Date From</label>
                                <input type="date" class="form-control" name="date_from" value="<?php echo $date_from; ?>">
                            </div>
                            <div class="col-md-3">
                                <label class="form-label">Date To</label>
                                <input type="date" class="form-control" name="date_to" value="<?php echo $date_to; ?>">
                            </div>
                            <div class="col-md-3">
                                <label class="form-label">Test Category</label>
                                <select class="form-select" name="test_category">
                                    <option value="ALL" <?php echo $test_category == 'ALL' ? 'selected' : ''; ?>>All Tests</option>
                                    <option value="AUD001" <?php echo $test_category == 'AUD001' ? 'selected' : ''; ?>>Pure Tone Audiometry</option>
                                    <option value="AUD002" <?php echo $test_category == 'AUD002' ? 'selected' : ''; ?>>Speech Audiometry</option>
                                    <option value="AUD003" <?php echo $test_category == 'AUD003' ? 'selected' : ''; ?>>Tympanometry</option>
                                </select>
                            </div>
                            <div class="col-md-3">
                                <label class="form-label">&nbsp;</label>
                                <div>
                                    <button type="submit" class="btn btn-primary"><i class="ri-search-line"></i> Generate Report</button>
                                </div>
                            </div>
                        </form>
                    </div>
                    
                    <!-- Summary Statistics -->
                    <div class="row mb-4">
                        <div class="col-xl-3 col-md-6 mb-3">
                            <div class="stat-card">
                                <div class="stat-value" style="color: #3b82f6;"><?php echo $summary['total_tests']; ?></div>
                                <div class="stat-label">Total Tests</div>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6 mb-3">
                            <div class="stat-card">
                                <div class="stat-value" style="color: #8b5cf6;"><?php echo $summary['unique_patients']; ?></div>
                                <div class="stat-label">Unique Patients</div>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6 mb-3">
                            <div class="stat-card">
                                <div class="stat-value" style="color: #10b981;"><?php echo $summary['normal_count']; ?></div>
                                <div class="stat-label">Normal Results</div>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6 mb-3">
                            <div class="stat-card">
                                <div class="stat-value" style="color: #f59e0b;"><?php echo $summary['abnormal_count']; ?></div>
                                <div class="stat-label">Abnormal Results</div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Detailed Report Table -->
                    <div class="row">
                        <div class="col-12">
                            <div class="info-card">
                                <h5><i class="ri-file-list-3-line me-2"></i>Test Breakdown</h5>
                                <div class="table-responsive">
                                    <table id="reportsTable" class="table table-striped nowrap w-100">
                                        <thead>
                                            <tr>
                                                <th>Test Type</th>
                                                <th>Total Count</th>
                                                <th>Normal</th>
                                                <th>Abnormal</th>
                                                <th>Critical</th>
                                                <th>Avg Cost</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php if ($reports_result->num_rows > 0): ?>
                                                <?php while ($report = $reports_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><strong><?php echo htmlspecialchars($report['test_name']); ?></strong></td>
                                                        <td><?php echo $report['test_count']; ?></td>
                                                        <td><span class="badge bg-success"><?php echo $report['normal_count']; ?></span></td>
                                                        <td><span class="badge bg-warning"><?php echo $report['abnormal_count']; ?></span></td>
                                                        <td><span class="badge bg-danger"><?php echo $report['critical_count']; ?></span></td>
                                                        <td>KES <?php echo number_format($report['avg_cost'], 2); ?></td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            <?php else: ?>
                                                <tr><td colspan="6" class="text-center">No data for selected period</td></tr>
                                            <?php endif; ?>
                                        </tbody>
                                    </table>
                                </div>
                                
                                <div class="mt-3">
                                    <button class="btn btn-success" onclick="exportReport()"><i class="ri-file-excel-2-line"></i> Export to Excel</button>
                                    <button class="btn btn-danger" onclick="printReport()"><i class="ri-printer-line"></i> Print Report</button>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    
    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/libs/datatables.net/js/jquery.dataTables.min.js"></script>
    <script src="assets/libs/datatables.net-bs5/js/dataTables.bootstrap5.min.js"></script>
    <script>
        $(document).ready(function() { 
            $('#reportsTable').DataTable({ 
                responsive: true, 
                order: [[1, 'desc']],
                pageLength: 25 
            }); 
        });
        function exportReport() {
            window.location.href = 'ajax/export_test_report.php?date_from=<?php echo $date_from; ?>&date_to=<?php echo $date_to; ?>&test_category=<?php echo $test_category; ?>';
        }
        function printReport() {
            window.print();
        }
    </script>
    <script src="assets/js/app.js"></script>
</body>
</html>