<?php
/**
 * Export Test Report to Excel
 * Generates Excel file from test statistics
 */

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

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

$user_id = $_SESSION['user_id'];
$user_name = $_SESSION['full_name'] ?? 'Audiologist';

// 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,
                    ett.test_code,
                    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,
                    SUM(lot.test_cost) as total_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, ett.test_code
                ORDER BY test_count DESC";

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

// Set headers for Excel download
$filename = "Audiology_Test_Report_" . $date_from . "_to_" . $date_to . "_" . date('YmdHis') . ".csv";
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
header('Expires: 0');

// Create output stream
$output = fopen('php://output', 'w');

// Add BOM for UTF-8
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// Write header
fputcsv($output, ['Prodigy ENT Hospital - Audiology Test Report']);
fputcsv($output, ['Generated by: ' . $user_name]);
fputcsv($output, ['Date Range: ' . $date_from . ' to ' . $date_to]);
fputcsv($output, ['Generated On: ' . date('Y-m-d H:i:s')]);
fputcsv($output, []); // Empty line

// Write column headers
fputcsv($output, [
    'Test Code',
    'Test Name',
    'Total Tests',
    'Normal Results',
    'Abnormal Results',
    'Critical Results',
    'Average Cost (KES)',
    'Total Revenue (KES)'
]);

// Write data rows
$total_tests = 0;
$total_normal = 0;
$total_abnormal = 0;
$total_critical = 0;
$total_revenue = 0;

while ($row = $reports_result->fetch_assoc()) {
    fputcsv($output, [
        $row['test_code'],
        $row['test_name'],
        $row['test_count'],
        $row['normal_count'],
        $row['abnormal_count'],
        $row['critical_count'],
        number_format($row['avg_cost'], 2),
        number_format($row['total_cost'], 2)
    ]);
    
    $total_tests += $row['test_count'];
    $total_normal += $row['normal_count'];
    $total_abnormal += $row['abnormal_count'];
    $total_critical += $row['critical_count'];
    $total_revenue += $row['total_cost'];
}

// Write summary row
fputcsv($output, []); // Empty line
fputcsv($output, [
    'TOTAL',
    '',
    $total_tests,
    $total_normal,
    $total_abnormal,
    $total_critical,
    '',
    number_format($total_revenue, 2)
]);

// Calculate percentages
fputcsv($output, []); // Empty line
fputcsv($output, ['Summary Statistics']);
fputcsv($output, ['Total Tests Conducted', $total_tests]);
fputcsv($output, ['Normal Results', $total_normal . ' (' . ($total_tests > 0 ? round(($total_normal/$total_tests)*100, 1) : 0) . '%)']);
fputcsv($output, ['Abnormal Results', $total_abnormal . ' (' . ($total_tests > 0 ? round(($total_abnormal/$total_tests)*100, 1) : 0) . '%)']);
fputcsv($output, ['Critical Results', $total_critical . ' (' . ($total_tests > 0 ? round(($total_critical/$total_tests)*100, 1) : 0) . '%)']);
fputcsv($output, ['Total Revenue Generated', 'KES ' . number_format($total_revenue, 2)]);

// Log the export activity
$activity_sql = "INSERT INTO activity_log 
                (user_id, action, module, record_type, new_values, ip_address, logged_at)
                VALUES (?, 'EXPORT_REPORT', 'AUDIOLOGY', 'TEST_REPORT', ?, ?, NOW())";

$export_data = json_encode([
    'report_type' => 'Test Statistics',
    'date_from' => $date_from,
    'date_to' => $date_to,
    'test_category' => $test_category,
    'total_tests' => $total_tests,
    'total_revenue' => $total_revenue,
    'exported_by' => $user_name,
    'filename' => $filename
]);

$ip_address = $_SERVER['REMOTE_ADDR'] ?? 'Unknown';

$stmt = $conn->prepare($activity_sql);
$stmt->bind_param("iss", $user_id, $export_data, $ip_address);
$stmt->execute();

// Close output and database
fclose($output);
$conn->close();
exit();
?>