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

require_once '../../dripend/includes/db.php';

// ==============================================================================
// 1. DATE RANGE FILTERING
// ==============================================================================
$date_from_raw = $_GET['date_from'] ?? date('Y-m-01'); // Default to 1st of current month
$date_to_raw = $_GET['date_to'] ?? date('Y-m-t');      // Default to last day of current month

$date_from_sql = $conn->real_escape_string($date_from_raw . ' 00:00:00');
$date_to_sql = $conn->real_escape_string($date_to_raw . ' 23:59:59');

// ==============================================================================
// 2. AUDIT LOGGING
// ==============================================================================
$staff_id = isset($_SESSION['staff_id']) ? intval($_SESSION['staff_id']) : 1;
$ip_addr = $_SERVER['REMOTE_ADDR'];
$audit_json = json_encode(['action' => 'Viewed Lab Analytics', 'date_from' => $date_from_raw, 'date_to' => $date_to_raw]);

$stmtAudit = $conn->prepare("INSERT INTO system_audit_logs (staff_id, action_type, table_name, record_id, new_values, ip_address) VALUES (?, 'VIEW', 'clinical_lab_requests', 'PERFORMANCE-ANALYTICS', ?, ?)");
if ($stmtAudit) {
    $stmtAudit->bind_param("iss", $staff_id, $audit_json, $ip_addr);
    $stmtAudit->execute();
    $stmtAudit->close();
}

// ==============================================================================
// 3. FETCH GLOBAL KPIs
// ==============================================================================
$kpi = [
    'total_ordered' => 0,
    'total_verified' => 0,
    'total_rejected' => 0,
    'avg_testing_tat' => 0, // Minutes from Collection to Testing
    'avg_verify_tat' => 0   // Minutes from Testing to Verification
];

$qGlobal = "
    SELECT 
        COUNT(clr.id) AS total_ordered,
        SUM(CASE WHEN clr.verified_by IS NOT NULL THEN 1 ELSE 0 END) AS total_verified,
        SUM(CASE WHEN clr.reject_reason IS NOT NULL THEN 1 ELSE 0 END) AS total_rejected,
        AVG(TIMESTAMPDIFF(MINUTE, csc.collected_at, clr.completed_at)) AS avg_testing_tat,
        AVG(TIMESTAMPDIFF(MINUTE, clr.completed_at, clr.verified_at)) AS avg_verify_tat
    FROM clinical_lab_requests clr
    LEFT JOIN clinical_sample_collections csc ON clr.id = csc.request_id
    WHERE clr.request_date >= ? AND clr.request_date <= ?
";
$stmtGlobal = $conn->prepare($qGlobal);
$stmtGlobal->bind_param("ss", $date_from_sql, $date_to_sql);
$stmtGlobal->execute();
$resGlobal = $stmtGlobal->get_result()->fetch_assoc();
if ($resGlobal) {
    $kpi['total_ordered'] = (int)$resGlobal['total_ordered'];
    $kpi['total_verified'] = (int)$resGlobal['total_verified'];
    $kpi['total_rejected'] = (int)$resGlobal['total_rejected'];
    $kpi['avg_testing_tat'] = round((float)$resGlobal['avg_testing_tat'], 1);
    $kpi['avg_verify_tat'] = round((float)$resGlobal['avg_verify_tat'], 1);
}
$stmtGlobal->close();

// ==============================================================================
// 4. FETCH CHART DATA: VOLUME BY SUB-DEPARTMENT
// ==============================================================================
$chart_labels = [];
$chart_data = [];

$qChart = "
    SELECT 
        COALESCE(lsd.sub_dept_name, 'Uncategorized') AS department,
        COUNT(clr.id) AS volume
    FROM clinical_lab_requests clr
    LEFT JOIN service_catalog sc ON clr.service_id = sc.id
    LEFT JOIN lab_sub_departments lsd ON sc.lab_sub_dept_id = lsd.id
    WHERE clr.request_date >= ? AND clr.request_date <= ? AND clr.service_id IS NOT NULL
    GROUP BY lsd.sub_dept_name
    ORDER BY volume DESC
";
$stmtChart = $conn->prepare($qChart);
$stmtChart->bind_param("ss", $date_from_sql, $date_to_sql);
$stmtChart->execute();
$resChart = $stmtChart->get_result();
while ($row = $resChart->fetch_assoc()) {
    $chart_labels[] = $row['department'];
    $chart_data[] = $row['volume'];
}
$stmtChart->close();

// ==============================================================================
// 5. FETCH DETAILED TEST PERFORMANCE TABLE
// ==============================================================================
$analytics_table = [];
$qTable = "
    SELECT 
        sc.service_name,
        COALESCE(lsd.sub_dept_name, 'Uncategorized') AS sub_department,
        COUNT(clr.id) AS total_ordered,
        SUM(CASE WHEN clr.verified_by IS NOT NULL THEN 1 ELSE 0 END) AS total_verified,
        SUM(CASE WHEN clr.reject_reason IS NOT NULL THEN 1 ELSE 0 END) AS total_rejected,
        ROUND(AVG(TIMESTAMPDIFF(MINUTE, csc.collected_at, clr.completed_at)), 1) AS avg_testing_tat_mins,
        ROUND(AVG(TIMESTAMPDIFF(MINUTE, clr.completed_at, clr.verified_at)), 1) AS avg_verify_tat_mins
    FROM clinical_lab_requests clr
    LEFT JOIN clinical_sample_collections csc ON clr.id = csc.request_id
    LEFT JOIN service_catalog sc ON clr.service_id = sc.id
    LEFT JOIN lab_sub_departments lsd ON sc.lab_sub_dept_id = lsd.id
    WHERE clr.request_date >= ? AND clr.request_date <= ? AND clr.service_id IS NOT NULL
    GROUP BY sc.service_name, lsd.sub_dept_name
    ORDER BY total_ordered DESC
";
$stmtTable = $conn->prepare($qTable);
$stmtTable->bind_param("ss", $date_from_sql, $date_to_sql);
$stmtTable->execute();
$resTable = $stmtTable->get_result();
while ($row = $resTable->fetch_assoc()) {
    $analytics_table[] = $row;
}
$stmtTable->close();

// Side Bar Details
$domain = 'https://dripdental.kqmucpub.com';
$iconPath = $domain . '/dripend/assets/icons/dashboard';
$current_page = basename($_SERVER['PHP_SELF']); 
$userName = isset($_SESSION['user_full_name']) ? $_SESSION['user_full_name'] : 'Dr. Admin';
$userRole = isset($_SESSION['user_role']) ? $_SESSION['user_role'] : 'Administrator';
$userInitial = strtoupper(substr($userName, 0, 1));
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Laboratory Analytics | Tier 1 LIS</title>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700;800&display=swap" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.dataTables.min.css">

<style>
:root {
  --sidebar-width: 300px;
  --bg-app: #f4f7f9;
  --bg-card: #ffffff;
  --brand-secondary: #2563eb;
  --success: #10b981;
  --danger: #ef4444;
  --warning: #f59e0b;
  --purple: #8b5cf6;
  --border-light: #e2e8f0;
  --text-main: #0f172a;
  --text-muted: #64748b;
  --shadow-sm: 0 1px 2px 0 rgba(0, 0, 0, 0.05);
  --shadow-md: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
}

* { margin: 0; padding: 0; box-sizing: border-box; }
body { font-family: 'Inter', sans-serif; background: var(--bg-app); color: var(--text-main); -webkit-font-smoothing: antialiased; }

.main-content { margin-left: var(--sidebar-width); padding: 25px 30px; display: flex; flex-direction: column; min-height: 100vh; }
@media (max-width: 1024px) { .main-content { margin-left: 0; padding: 15px; } }

.container { max-width: 1600px; margin: 0 auto; width: 100%; flex: 1; display: flex; flex-direction: column; }

.page-header { display: flex; justify-content: space-between; align-items: center; margin-bottom: 25px; }
.page-header h1 { font-size: 24px; color: var(--text-main); font-weight: 800; display: flex; align-items: center; gap: 10px; }

/* Filter Bar */
.filter-bar { display: flex; gap: 20px; background: #fff; padding: 15px 25px; border-radius: 8px; border: 1px solid var(--border-light); box-shadow: var(--shadow-sm); margin-bottom: 25px; align-items: flex-end; flex-wrap: wrap; }
.filter-bar .form-group { flex: 1; min-width: 200px;}
.filter-bar label { display: block; font-size: 11px; font-weight: 700; color: var(--text-muted); margin-bottom: 6px; text-transform: uppercase; letter-spacing: 0.5px; }
.input-control { width: 100%; padding: 10px 12px; border: 1px solid var(--border-light); border-radius: 6px; font-size: 13px; font-weight: 600; outline: none; background: #fff; font-family: inherit;}
.input-control:focus { border-color: var(--brand-secondary); box-shadow: 0 0 0 3px rgba(37, 99, 235, 0.1); }
.btn { padding: 10px 20px; border-radius: 6px; font-size: 13px; font-weight: 600; cursor: pointer; border: none; display: inline-flex; align-items: center; gap: 8px; }
.btn-primary { background: var(--brand-secondary); color: #fff; }

/* KPI Grid */
.kpi-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin-bottom: 25px; }
.kpi-card { background: var(--bg-card); border-radius: 12px; padding: 20px; border: 1px solid var(--border-light); box-shadow: var(--shadow-sm); display: flex; flex-direction: column; gap: 10px; }
.kpi-header { display: flex; justify-content: space-between; align-items: center; color: var(--text-muted); font-size: 13px; font-weight: 700; text-transform: uppercase; letter-spacing: 0.5px; }
.kpi-val { font-size: 28px; font-weight: 800; color: var(--text-main); }
.kpi-sub { font-size: 12px; color: var(--text-muted); font-weight: 500; }

/* Chart & Table Grid */
.dashboard-grid { display: grid; grid-template-columns: 1fr 2fr; gap: 25px; margin-bottom: 30px; }
@media (max-width: 1200px) { .dashboard-grid { grid-template-columns: 1fr; } }

.card { background: var(--bg-card); border-radius: 12px; border: 1px solid var(--border-light); box-shadow: var(--shadow-sm); overflow: hidden; display: flex; flex-direction: column; }
.card-header { padding: 18px 25px; border-bottom: 1px solid var(--border-light); background: #f8fafc; display: flex; justify-content: space-between; align-items: center; }
.card-header h2 { font-size: 16px; font-weight: 700; color: var(--text-main); }

/* Table Overrides */
.table-wrapper { padding: 20px; width: 100%; overflow-x: auto; }
.analytics-table { width: 100%; border-collapse: collapse; font-size: 13px; }
.analytics-table th, .analytics-table td { padding: 12px 15px; border-bottom: 1px solid var(--border-light); }
.analytics-table th { background: #f8fafc; font-weight: 700; color: var(--text-muted); text-transform: uppercase; font-size: 11px; letter-spacing: 0.5px; white-space: nowrap;}
.analytics-table tr:hover { background: #f8fafc; }
.analytics-table .tat-good { color: var(--success); font-weight: 600; }
.analytics-table .tat-bad { color: var(--danger); font-weight: 600; }

.dt-buttons { margin-bottom: 15px; }
.dt-button { background: #fff !important; border: 1px solid var(--border-light) !important; color: var(--text-main) !important; border-radius: 4px !important; font-size: 12px !important; font-weight: 600 !important; padding: 6px 12px !important; }

.enterprise-footer { text-align: center; font-size: 12px; color: var(--text-muted); font-weight: 500; padding: 20px 0; margin-top: auto;}
</style>
</head>
<body>

<?php include('../../dripend/statpages/sidebar.php'); ?>

<div class="main-content" id="mainContent">
  <div class="container">
    
    <div class="page-header">
      <h1><i class="fa-solid fa-chart-line" style="color: var(--purple);"></i> Laboratory Operations & TAT Analytics</h1>
    </div>

    <!-- Filter Bar -->
    <div class="filter-bar">
        <div class="form-group">
            <label>Analyze From Date</label>
            <input type="date" id="dateFrom" class="input-control" value="<?= htmlspecialchars($date_from_raw) ?>">
        </div>
        <div class="form-group">
            <label>Analyze To Date</label>
            <input type="date" id="dateTo" class="input-control" value="<?= htmlspecialchars($date_to_raw) ?>">
        </div>
        <div style="display: flex; gap: 10px; margin-bottom: 2px;">
            <button class="btn btn-primary" onclick="generateAnalytics()"><i class="fa-solid fa-bolt"></i> Run Analytics</button>
        </div>
    </div>

    <!-- Global KPIs -->
    <div class="kpi-grid">
        <div class="kpi-card" style="border-top: 4px solid var(--brand-secondary);">
            <div class="kpi-header"><span>Total Requisitions</span> <i class="fa-solid fa-file-prescription"></i></div>
            <div class="kpi-val"><?= number_format($kpi['total_ordered']) ?></div>
            <div class="kpi-sub">Tests ordered within period</div>
        </div>
        <div class="kpi-card" style="border-top: 4px solid var(--success);">
            <div class="kpi-header"><span>Total Verified</span> <i class="fa-solid fa-check-double"></i></div>
            <div class="kpi-val"><?= number_format($kpi['total_verified']) ?></div>
            <div class="kpi-sub">Tests completed & released</div>
        </div>
        <div class="kpi-card" style="border-top: 4px solid var(--warning);">
            <div class="kpi-header"><span>Avg Testing TAT</span> <i class="fa-solid fa-microscope"></i></div>
            <div class="kpi-val"><?= $kpi['avg_testing_tat'] ?> <span style="font-size:14px; font-weight:600;">mins</span></div>
            <div class="kpi-sub">From Sample Collection to Result Entry</div>
        </div>
        <div class="kpi-card" style="border-top: 4px solid var(--purple);">
            <div class="kpi-header"><span>Avg Auth TAT</span> <i class="fa-solid fa-file-signature"></i></div>
            <div class="kpi-val"><?= $kpi['avg_verify_tat'] ?> <span style="font-size:14px; font-weight:600;">mins</span></div>
            <div class="kpi-sub">From Result Entry to Pathologist Sign-off</div>
        </div>
        <div class="kpi-card" style="border-top: 4px solid var(--danger);">
            <div class="kpi-header"><span>Rejection Rate</span> <i class="fa-solid fa-ban"></i></div>
            <?php $rej_rate = $kpi['total_ordered'] > 0 ? round(($kpi['total_rejected'] / $kpi['total_ordered']) * 100, 1) : 0; ?>
            <div class="kpi-val"><?= $rej_rate ?>%</div>
            <div class="kpi-sub"><?= number_format($kpi['total_rejected']) ?> tests rejected</div>
        </div>
    </div>

    <!-- Chart and Table Grid -->
    <div class="dashboard-grid">
        
        <!-- Left: Volume Chart -->
        <div class="card">
            <div class="card-header">
                <h2>Volume by Sub-Department</h2>
            </div>
            <div style="padding: 20px; position: relative; height: 350px;">
                <canvas id="volumeChart"></canvas>
            </div>
        </div>

        <!-- Right: Detailed Analytics Table -->
        <div class="card">
            <div class="card-header">
                <h2>Test-Level Turnaround Time & Performance Breakdown</h2>
            </div>
            <div class="table-wrapper">
                <table id="analyticsTable" class="analytics-table display nowrap">
                    <thead>
                        <tr>
                            <th>Test / Service Name</th>
                            <th>Sub-Department</th>
                            <th>Ordered</th>
                            <th>Verified</th>
                            <th>Rejected</th>
                            <th>Avg Testing TAT (Mins)</th>
                            <th>Avg Verification TAT (Mins)</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach($analytics_table as $row): ?>
                        <tr>
                            <td style="font-weight: 600; color: var(--text-main);"><?= htmlspecialchars($row['service_name']) ?></td>
                            <td><?= htmlspecialchars($row['sub_department']) ?></td>
                            <td style="font-weight: 600;"><?= number_format($row['total_ordered']) ?></td>
                            <td style="color: var(--success); font-weight: 600;"><?= number_format($row['total_verified']) ?></td>
                            <td style="color: var(--danger); font-weight: 600;"><?= number_format($row['total_rejected']) ?></td>
                            
                            <?php $t_tat = floatval($row['avg_testing_tat_mins']); ?>
                            <td class="<?= $t_tat > 60 ? 'tat-bad' : 'tat-good' ?>"><?= $row['avg_testing_tat_mins'] ?? 'N/A' ?></td>
                            
                            <?php $v_tat = floatval($row['avg_verify_tat_mins']); ?>
                            <td class="<?= $v_tat > 30 ? 'tat-bad' : 'tat-good' ?>"><?= $row['avg_verify_tat_mins'] ?? 'N/A' ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>

    </div>

    <div class="enterprise-footer">
      Laboratory Management System v2.4.1 | Analytics Engine | Powered by Afritechs Gh Ltd
    </div>

  </div>
</div>

<!-- Scripts -->
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<!-- DataTables -->
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.print.min.js"></script>

<script>
function generateAnalytics() {
    const from = document.getElementById('dateFrom').value;
    const to = document.getElementById('dateTo').value;
    if(!from || !to) {
        alert("Please select both dates.");
        return;
    }
    window.location.href = `?date_from=${from}&date_to=${to}`;
}

$(document).ready(function() {
    // Initialize DataTable with Export Buttons
    $('#analyticsTable').DataTable({
        dom: 'Bfrtip',
        pageLength: 15,
        order: [[2, 'desc']], // Sort by Total Ordered descending
        scrollX: true,
        buttons: [
            {
                extend: 'excelHtml5',
                text: '<i class="fa-solid fa-file-excel" style="color: #16a34a;"></i> Export Data',
                title: `Lab_Performance_Analytics_<?= $date_from_raw ?>_to_<?= $date_to_raw ?>`,
                className: 'dt-button'
            },
            {
                extend: 'pdfHtml5',
                text: '<i class="fa-solid fa-file-pdf" style="color: #dc2626;"></i> Export PDF',
                title: `Laboratory Performance & TAT Analytics \n(<?= $date_from_raw ?> to <?= $date_to_raw ?>)`,
                orientation: 'landscape',
                className: 'dt-button'
            }
        ]
    });

    // Render Chart.js
    const ctx = document.getElementById('volumeChart').getContext('2d');
    
    // PHP arrays injected into JS
    const chartLabels = <?= json_encode($chart_labels) ?>;
    const chartData = <?= json_encode($chart_data) ?>;
    
    new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: chartLabels,
            datasets: [{
                data: chartData,
                backgroundColor: [
                    '#2563eb', '#10b981', '#f59e0b', '#8b5cf6', '#ef4444', '#0ea5e9', '#64748b'
                ],
                borderWidth: 0,
                hoverOffset: 4
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: {
                    position: 'right',
                    labels: { font: { family: 'Inter', size: 12, weight: '500' } }
                }
            },
            cutout: '70%'
        }
    });
});
</script>

</body>
</html>