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

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

// ==============================================================================
// 1. FETCH FACILITY PROFILE
// ==============================================================================
$facility = [
    'hospital_name' => 'Enterprise Hospital',
    'address' => '',
    'primary_phone' => '',
    'primary_email' => ''
];
$qFacility = "SELECT hospital_name, address, primary_phone, primary_email FROM facility_profile LIMIT 1";
$resFacility = $conn->query($qFacility);
if ($resFacility && $resFacility->num_rows > 0) {
    $facility = $resFacility->fetch_assoc();
}

// ==============================================================================
// 2. FILTER INITIALIZATION (MONTH / YEAR)
// ==============================================================================
$filter_month = $_GET['month'] ?? date('m');
$filter_year = $_GET['year'] ?? date('Y');

// Construct date boundaries for the SQL query
$start_date = $conn->real_escape_string(sprintf("%04d-%02d-01 00:00:00", $filter_year, $filter_month));
$end_date = date("Y-m-t 23:59:59", strtotime($start_date));
$report_period_text = date('F Y', strtotime($start_date));

// ==============================================================================
// 3. AUDIT LOGGING
// ==============================================================================
$staff_id = isset($_SESSION['staff_id']) ? intval($_SESSION['staff_id']) : 1;
$ip_addr = $_SERVER['REMOTE_ADDR'];
$audit_action = "Generated MoH Haematology Report for $report_period_text";
$audit_json = json_encode(['action' => 'Generate Report', 'period' => $report_period_text, 'department' => 'Haematology']);

$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', 'MOH-REPORT', ?, ?)
");
if ($stmtAudit) {
    $stmtAudit->bind_param("iss", $staff_id, $audit_json, $ip_addr);
    $stmtAudit->execute();
    $stmtAudit->close();
}

// ==============================================================================
// 4. MoH AGE & GENDER BUCKET DEFINITIONS
// ==============================================================================
$age_buckets = [
    '<29 days', '1-11mths', '1-4', '5-9', '10-14', '15-17', '18-19', '20-34', '35-49', '50-59', '60-69', '70+'
];

$report_data = [];

// Fetch all active Haematology tests dynamically (Sub-Dept 1)[cite: 1]
$qServices = "SELECT service_name FROM service_catalog WHERE lab_sub_dept_id = 1 AND is_active = 1 ORDER BY service_name ASC";
$resServices = $conn->query($qServices);
while ($row = $resServices->fetch_assoc()) {
    $test_name = $row['service_name'];
    $report_data[$test_name] = [
        'Male' => array_fill_keys($age_buckets, 0),
        'Female' => array_fill_keys($age_buckets, 0)
    ];
}

// ==============================================================================
// 5. FETCH AND PROCESS STRICTLY VERIFIED CLINICAL DATA
// ==============================================================================
$qData = "
    SELECT 
        sc.service_name,
        g.gender_name,
        TIMESTAMPDIFF(DAY, p.dob, clr.verified_at) AS age_days,
        TIMESTAMPDIFF(MONTH, p.dob, clr.verified_at) AS age_months,
        TIMESTAMPDIFF(YEAR, p.dob, clr.verified_at) AS age_years
    FROM clinical_lab_requests clr
    JOIN patient p ON clr.patient_no = p.patient_no
    LEFT JOIN gender g ON p.gender_id = g.id
    JOIN service_catalog sc ON clr.service_id = sc.id
    WHERE clr.status = 'Completed' 
      AND clr.verified_by IS NOT NULL 
      AND sc.lab_sub_dept_id = 1 
      AND clr.verified_at >= ? AND clr.verified_at <= ?
";

$stmtData = $conn->prepare($qData);
$stmtData->bind_param("ss", $start_date, $end_date);
$stmtData->execute();
$resultData = $stmtData->get_result();

while ($row = $resultData->fetch_assoc()) {
    $test_name = $row['service_name'];
    $gender = ($row['gender_name'] === 'Male') ? 'Male' : 'Female';
    
    if (!isset($report_data[$test_name])) {
        $report_data[$test_name] = [
            'Male' => array_fill_keys($age_buckets, 0),
            'Female' => array_fill_keys($age_buckets, 0)
        ];
    }

    $days = (int)$row['age_days'];
    $months = (int)$row['age_months'];
    $years = (int)$row['age_years'];
    $bucket = '';

    if ($days < 29) {
        $bucket = '<29 days';
    } elseif ($months >= 1 && $years < 1) {
        $bucket = '1-11mths';
    } elseif ($years >= 1 && $years <= 4) {
        $bucket = '1-4';
    } elseif ($years >= 5 && $years <= 9) {
        $bucket = '5-9';
    } elseif ($years >= 10 && $years <= 14) {
        $bucket = '10-14';
    } elseif ($years >= 15 && $years <= 17) {
        $bucket = '15-17';
    } elseif ($years >= 18 && $years <= 19) {
        $bucket = '18-19';
    } elseif ($years >= 20 && $years <= 34) {
        $bucket = '20-34';
    } elseif ($years >= 35 && $years <= 49) {
        $bucket = '35-49';
    } elseif ($years >= 50 && $years <= 59) {
        $bucket = '50-59';
    } elseif ($years >= 60 && $years <= 69) {
        $bucket = '60-69';
    } elseif ($years >= 70) {
        $bucket = '70+';
    } else {
        $bucket = '20-34'; 
    }

    $report_data[$test_name][$gender][$bucket]++;
}
$stmtData->close();

$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>MoH Haematology Returns | <?= htmlspecialchars($facility['hospital_name']) ?></title>

<!-- Fonts & Select2 -->
<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">

<!-- DataTables CSS for Export Engine -->
<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;
  --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: 15px; flex-shrink: 0; }
.page-header h1 { font-size: 22px; color: var(--text-main); font-weight: 700; 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: 20px; align-items: flex-end; flex-shrink: 0; 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; }

/* MoH Table Specifics */
.report-card { background: var(--bg-card); border-radius: 8px; border: 1px solid var(--border-light); box-shadow: var(--shadow-sm); padding: 25px; width: 100%; }
.table-responsive-wrapper { width: 100%; overflow-x: auto; -webkit-overflow-scrolling: touch; }

.facility-header { text-align: center; margin-bottom: 20px; padding-bottom: 15px; border-bottom: 2px solid var(--brand-secondary); }
.facility-header h2 { font-size: 24px; color: var(--text-main); font-weight: 800; margin-bottom: 5px; text-transform: uppercase; }
.facility-header p { font-size: 14px; color: var(--text-muted); font-weight: 500; }
.facility-header .report-title { font-size: 18px; color: var(--brand-secondary); font-weight: 700; margin-top: 10px; }

.moh-table { width: 100%; border-collapse: collapse; font-size: 12px; }
.moh-table th, .moh-table td { border: 1px solid #cbd5e1; padding: 8px 5px; text-align: center; vertical-align: middle; }
.moh-table th { background: #f8fafc; font-weight: 700; color: var(--text-main); }
.moh-table th.gender-header { text-transform: uppercase; font-size: 13px; letter-spacing: 1px; }
.moh-table th.male-hdr { background: #eff6ff; color: #1d4ed8; }
.moh-table th.female-hdr { background: #fdf2f8; color: #be185d; }
.moh-table td.test-name { text-align: left; font-weight: 600; background: #f8fafc; min-width: 200px; position: sticky; left: 0; z-index: 1;}
.moh-table td.zero-val { color: #cbd5e1; } 

/* DataTables Overrides */
.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; }
.dt-button:hover { background: #f1f5f9 !important; }

.enterprise-footer { text-align: center; font-size: 12px; color: var(--text-muted); font-weight: 500; padding: 20px 0;}

/* Scrollbar styling for the table wrapper */
.table-responsive-wrapper::-webkit-scrollbar { height: 8px; }
.table-responsive-wrapper::-webkit-scrollbar-track { background: #f1f5f9; border-radius: 4px; }
.table-responsive-wrapper::-webkit-scrollbar-thumb { background: #cbd5e1; border-radius: 4px; }
.table-responsive-wrapper::-webkit-scrollbar-thumb:hover { background: #94a3b8; }
</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-pie" style="color: var(--brand-secondary);"></i> Monthly Haematology Returns</h1>
    </div>

    <!-- Filter Bar -->
    <div class="filter-bar">
        <div class="form-group">
            <label>Report Month</label>
            <select id="filterMonth" class="input-control">
                <?php 
                for ($m = 1; $m <= 12; $m++) {
                    $month_val = str_pad($m, 2, "0", STR_PAD_LEFT);
                    $month_name = date('F', mktime(0, 0, 0, $m, 1));
                    $selected = ($filter_month == $month_val) ? 'selected' : '';
                    echo "<option value=\"$month_val\" $selected>$month_name</option>";
                }
                ?>
            </select>
        </div>
        <div class="form-group">
            <label>Report Year</label>
            <select id="filterYear" class="input-control">
                <?php 
                $current_year = date('Y');
                for ($y = $current_year; $y >= $current_year - 5; $y--) {
                    $selected = ($filter_year == $y) ? 'selected' : '';
                    echo "<option value=\"$y\" $selected>$y</option>";
                }
                ?>
            </select>
        </div>
        <div style="display: flex; gap: 10px; margin-bottom: 2px;">
            <button class="btn btn-primary" onclick="generateReport()"><i class="fa-solid fa-rotate"></i> Generate Report</button>
        </div>
    </div>

    <div class="report-card">
        
        <div class="facility-header">
            <h2><?= htmlspecialchars($facility['hospital_name']) ?></h2>
            <p><?= htmlspecialchars($facility['address']) ?> | Contact: <?= htmlspecialchars($facility['primary_phone']) ?></p>
            <div class="report-title">Monthly Haematology Returns for <?= $report_period_text ?></div>
        </div>

        <div class="table-responsive-wrapper">
            <table id="mohReportTable" class="moh-table display nowrap" style="width:100%">
                <thead>
                    <tr>
                        <th rowspan="2" class="test-name">Age Group for<br>Test Name</th>
                        <th colspan="12" class="gender-header male-hdr">Gender Male</th>
                        <th colspan="12" class="gender-header female-hdr">Gender Female</th>
                    </tr>
                    <tr>
                        <!-- Male Age Buckets -->
                        <?php foreach($age_buckets as $bucket): ?>
                            <th><?= htmlspecialchars($bucket) ?></th>
                        <?php endforeach; ?>
                        <!-- Female Age Buckets -->
                        <?php foreach($age_buckets as $bucket): ?>
                            <th><?= htmlspecialchars($bucket) ?></th>
                        <?php endforeach; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach($report_data as $test_name => $genders): ?>
                    <tr>
                        <td class="test-name"><?= htmlspecialchars($test_name) ?></td>
                        
                        <!-- Output Male Data -->
                        <?php foreach($age_buckets as $bucket): ?>
                            <?php $val = $genders['Male'][$bucket]; ?>
                            <td class="<?= $val == 0 ? 'zero-val' : '' ?>"><?= $val ?></td>
                        <?php endforeach; ?>

                        <!-- Output Female Data -->
                        <?php foreach($age_buckets as $bucket): ?>
                            <?php $val = $genders['Female'][$bucket]; ?>
                            <td class="<?= $val == 0 ? 'zero-val' : '' ?>"><?= $val ?></td>
                        <?php endforeach; ?>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <div class="enterprise-footer">
      Laboratory Management System v2.4.1 | 21 CFR Part 11 Compliant | Powered by Afritechs Gh Ltd
    </div>

  </div>
</div>

<!-- Scripts for DataTables Export -->
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<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>
$(document).ready(function() {
    
    const facilityName = "<?= htmlspecialchars($facility['hospital_name']) ?>";
    const reportTitle = `Monthly Haematology Returns - <?= $report_period_text ?>`;
    const exportTitle = `${facilityName} - ${reportTitle}`;

    $('#mohReportTable').DataTable({
        dom: 'Bfrtip',
        paging: false,
        ordering: false, // Prevents disrupting the MoH layout
        info: false,
        scrollX: true,
        buttons: [
            {
                extend: 'excelHtml5',
                text: '<i class="fa-solid fa-file-excel" style="color: #16a34a;"></i> Export to Excel',
                title: exportTitle,
                messageTop: '<?= htmlspecialchars($facility['address']) ?> | Contact: <?= htmlspecialchars($facility['primary_phone']) ?>',
                className: 'dt-button'
            },
            {
                extend: 'pdfHtml5',
                text: '<i class="fa-solid fa-file-pdf" style="color: #dc2626;"></i> Export to PDF',
                title: exportTitle,
                messageTop: '<?= htmlspecialchars($facility['address']) ?> | Contact: <?= htmlspecialchars($facility['primary_phone']) ?>',
                orientation: 'landscape',
                pageSize: 'A3',
                className: 'dt-button',
                customize: function(doc) {
                    doc.styles.title = {
                        color: '#0f172a',
                        fontSize: '18',
                        bold: true,
                        alignment: 'center'
                    }
                    doc.styles.message = {
                        fontSize: '11',
                        color: '#64748b',
                        alignment: 'center',
                        margin: [0, 0, 0, 15]
                    }
                }
            },
            {
                extend: 'print',
                text: '<i class="fa-solid fa-print"></i> Print Form',
                title: '', // Handled by our custom HTML header on the page
                className: 'dt-button'
            }
        ]
    });
});

function generateReport() {
    const month = document.getElementById('filterMonth').value;
    const year = document.getElementById('filterYear').value;
    window.location.href = `?month=${month}&year=${year}`;
}
</script>

</body>
</html>