<?php
// admin/results.php
require_once '../config/config.php';
require_login();

// Get all exams for filter dropdown
$exams_stmt = $pdo->query("SELECT id, title FROM exams ORDER BY title");
$exams = $exams_stmt->fetchAll();

// Build query with filters
$where_conditions = ["se.status = 'completed'"];
$params = [];

// Search filter (name, phone, email, company)
if (!empty($_GET['search'])) {
    $search = '%' . $_GET['search'] . '%';
    $where_conditions[] = "(s.full_name LIKE ? OR s.mobile LIKE ? OR s.email LIKE ? OR s.company LIKE ?)";
    $params[] = $search;
    $params[] = $search;
    $params[] = $search;
    $params[] = $search;
}

// Exam filter
if (!empty($_GET['exam_id'])) {
    $where_conditions[] = "se.exam_id = ?";
    $params[] = $_GET['exam_id'];
}

// Date range filter
if (!empty($_GET['date_from'])) {
    $where_conditions[] = "DATE(se.submitted_at) >= ?";
    $params[] = $_GET['date_from'];
}
if (!empty($_GET['date_to'])) {
    $where_conditions[] = "DATE(se.submitted_at) <= ?";
    $params[] = $_GET['date_to'];
}

// Result filter
if (!empty($_GET['result'])) {
    $where_conditions[] = "se.result = ?";
    $params[] = $_GET['result'];
}

$where_clause = implode(' AND ', $where_conditions);

$sql = "
    SELECT se.*, s.full_name, s.mobile, s.email, s.company, e.title as exam_title
    FROM student_exams se
    JOIN students s ON se.student_id = s.id
    JOIN exams e ON se.exam_id = e.id
    WHERE $where_clause
    ORDER BY se.submitted_at DESC
";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$results = $stmt->fetchAll();

// Handle CSV Export
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="exam_results_' . date('Y-m-d_His') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Add BOM for Excel UTF-8 support
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // CSV Headers
    fputcsv($output, [
        'Student Name',
        'Mobile',
        'Email',
        'Company',
        'Exam Title',
        'Obtained Marks',
        'Total Marks',
        'Percentage',
        'Result',
        'Submission Date',
        'Submission Time'
    ]);
    
    // CSV Data
    foreach ($results as $r) {
        $adjusted_date = strtotime($r->submitted_at . ' +9 hours');
        fputcsv($output, [
            $r->full_name,
            $r->mobile,
            $r->email,
            $r->company,
            $r->exam_title,
            $r->obtained_marks,
            $r->total_marks,
            $r->percentage . '%',
            strtoupper($r->result),
            date('M d, Y', $adjusted_date),
            date('h:i A', $adjusted_date)
        ]);
    }
    
    fclose($output);
    exit;
}

// Calculate statistics
$total_exams = count($results);
$passed = count(array_filter($results, fn($r) => $r->result === 'pass'));
$failed = $total_exams - $passed;
$avg_percentage = $total_exams > 0 ? array_sum(array_column($results, 'percentage')) / $total_exams : 0;

$flash = get_flash();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Exam Results - Edoxi Training Center</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
    <style>
        .stat-card {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        }
        .stat-card.success {
            background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);
        }
        .stat-card.warning {
            background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);
        }
        .stat-card.info {
            background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%);
        }
        .filter-badge {
            animation: slideIn 0.3s ease-out;
        }
        @keyframes slideIn {
            from {
                opacity: 0;
                transform: translateY(-10px);
            }
            to {
                opacity: 1;
                transform: translateY(0);
            }
        }
    </style>
</head>
<body class="bg-gray-50">
    <?php include 'includes/nav.php'; ?>
    
    <div class="max-w-7xl mx-auto px-4 py-8">
        <!-- Header -->
        <div class="mb-8">
            <h1 class="text-4xl font-bold text-gray-900 mb-2">
                <i class="fas fa-chart-line text-orange-500 mr-3"></i>Exam Results Dashboard
            </h1>
            <p class="text-gray-600">Comprehensive view of all completed examinations</p>
        </div>

        <!-- Statistics Cards -->
        <div class="grid grid-cols-1 md:grid-cols-4 gap-6 mb-8">
            <div class="stat-card rounded-xl shadow-lg p-6 text-white">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-white/80 text-sm font-medium mb-1">Total Exams</p>
                        <p class="text-3xl font-bold"><?php echo $total_exams; ?></p>
                    </div>
                    <div class="bg-white/20 rounded-full p-4">
                        <i class="fas fa-clipboard-list text-2xl"></i>
                    </div>
                </div>
            </div>

            <div class="stat-card success rounded-xl shadow-lg p-6 text-white">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-white/80 text-sm font-medium mb-1">Passed</p>
                        <p class="text-3xl font-bold"><?php echo $passed; ?></p>
                    </div>
                    <div class="bg-white/20 rounded-full p-4">
                        <i class="fas fa-check-circle text-2xl"></i>
                    </div>
                </div>
            </div>

            <div class="stat-card warning rounded-xl shadow-lg p-6 text-white">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-white/80 text-sm font-medium mb-1">Failed</p>
                        <p class="text-3xl font-bold"><?php echo $failed; ?></p>
                    </div>
                    <div class="bg-white/20 rounded-full p-4">
                        <i class="fas fa-times-circle text-2xl"></i>
                    </div>
                </div>
            </div>

            <div class="stat-card info rounded-xl shadow-lg p-6 text-white">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-white/80 text-sm font-medium mb-1">Avg. Score</p>
                        <p class="text-3xl font-bold"><?php echo number_format($avg_percentage, 1); ?>%</p>
                    </div>
                    <div class="bg-white/20 rounded-full p-4">
                        <i class="fas fa-percentage text-2xl"></i>
                    </div>
                </div>
            </div>
        </div>

        <!-- Filters Section -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-6">
            <form method="GET" action="" id="filterForm">
                <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-5 gap-4 mb-4">
                    <!-- Search Box -->
                    <div class="lg:col-span-2">
                        <label class="block text-sm font-medium text-gray-700 mb-2">
                            <i class="fas fa-search text-gray-400 mr-1"></i> Search
                        </label>
                        <input type="text" 
                               name="search" 
                               value="<?php echo escape($_GET['search'] ?? ''); ?>"
                               placeholder="Name, Phone, Email, or Company..."
                               class="w-full px-4 py-2.5 border border-gray-300 rounded-lg focus:ring-2 focus:ring-orange-500 focus:border-transparent transition">
                    </div>

                    <!-- Exam Filter -->
                    <div>
                        <label class="block text-sm font-medium text-gray-700 mb-2">
                            <i class="fas fa-file-alt text-gray-400 mr-1"></i> Exam
                        </label>
                        <select name="exam_id" class="w-full px-4 py-2.5 border border-gray-300 rounded-lg focus:ring-2 focus:ring-orange-500 focus:border-transparent transition">
                            <option value="">All Exams</option>
                            <?php foreach ($exams as $exam): ?>
                                <option value="<?php echo $exam->id; ?>" <?php echo (isset($_GET['exam_id']) && $_GET['exam_id'] == $exam->id) ? 'selected' : ''; ?>>
                                    <?php echo escape($exam->title); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>

                    <!-- Date From -->
                    <div>
                        <label class="block text-sm font-medium text-gray-700 mb-2">
                            <i class="fas fa-calendar-alt text-gray-400 mr-1"></i> From Date
                        </label>
                        <input type="date" 
                               name="date_from" 
                               value="<?php echo escape($_GET['date_from'] ?? ''); ?>"
                               class="w-full px-4 py-2.5 border border-gray-300 rounded-lg focus:ring-2 focus:ring-orange-500 focus:border-transparent transition">
                    </div>

                    <!-- Date To -->
                    <div>
                        <label class="block text-sm font-medium text-gray-700 mb-2">
                            <i class="fas fa-calendar-check text-gray-400 mr-1"></i> To Date
                        </label>
                        <input type="date" 
                               name="date_to" 
                               value="<?php echo escape($_GET['date_to'] ?? ''); ?>"
                               class="w-full px-4 py-2.5 border border-gray-300 rounded-lg focus:ring-2 focus:ring-orange-500 focus:border-transparent transition">
                    </div>
                </div>

                <div class="grid grid-cols-1 md:grid-cols-2 gap-4">
                    <!-- Result Filter -->
                    <div>
                        <label class="block text-sm font-medium text-gray-700 mb-2">
                            <i class="fas fa-filter text-gray-400 mr-1"></i> Result Status
                        </label>
                        <select name="result" class="w-full px-4 py-2.5 border border-gray-300 rounded-lg focus:ring-2 focus:ring-orange-500 focus:border-transparent transition">
                            <option value="">All Results</option>
                            <option value="pass" <?php echo (isset($_GET['result']) && $_GET['result'] === 'pass') ? 'selected' : ''; ?>>Pass</option>
                            <option value="fail" <?php echo (isset($_GET['result']) && $_GET['result'] === 'fail') ? 'selected' : ''; ?>>Fail</option>
                        </select>
                    </div>

                    <!-- Action Buttons -->
                    <div class="flex items-end gap-3">
                        <button type="submit" class="flex-1 bg-gradient-to-r from-orange-500 to-orange-600 text-white px-6 py-2.5 rounded-lg font-medium hover:from-orange-600 hover:to-orange-700 transition shadow-md hover:shadow-lg">
                            <i class="fas fa-search mr-2"></i> Apply Filters
                        </button>
                        <a href="results.php" class="flex-1 bg-gray-100 text-gray-700 px-6 py-2.5 rounded-lg font-medium hover:bg-gray-200 transition text-center">
                            <i class="fas fa-redo mr-2"></i> Reset
                        </a>
                        <button type="button" onclick="exportResults()" class="bg-green-600 text-white px-6 py-2.5 rounded-lg font-medium hover:bg-green-700 transition shadow-md">
                            <i class="fas fa-file-excel mr-2"></i> Export
                        </button>
                    </div>
                </div>
            </form>

            <!-- Active Filters Display -->
            <?php if (!empty($_GET['search']) || !empty($_GET['exam_id']) || !empty($_GET['date_from']) || !empty($_GET['date_to']) || !empty($_GET['result'])): ?>
            <div class="mt-4 pt-4 border-t border-gray-200">
                <p class="text-sm font-medium text-gray-700 mb-2">Active Filters:</p>
                <div class="flex flex-wrap gap-2">
                    <?php if (!empty($_GET['search'])): ?>
                        <span class="filter-badge inline-flex items-center px-3 py-1 rounded-full text-sm bg-blue-100 text-blue-800">
                            <i class="fas fa-search mr-1.5"></i> "<?php echo escape($_GET['search']); ?>"
                            <a href="?<?php echo http_build_query(array_diff_key($_GET, ['search' => ''])); ?>" class="ml-2 hover:text-blue-900">×</a>
                        </span>
                    <?php endif; ?>
                    
                    <?php if (!empty($_GET['exam_id'])): 
                        $exam_title = array_filter($exams, fn($e) => $e->id == $_GET['exam_id']);
                        $exam_title = reset($exam_title);
                    ?>
                        <span class="filter-badge inline-flex items-center px-3 py-1 rounded-full text-sm bg-purple-100 text-purple-800">
                            <i class="fas fa-file-alt mr-1.5"></i> <?php echo escape($exam_title->title ?? 'Exam'); ?>
                            <a href="?<?php echo http_build_query(array_diff_key($_GET, ['exam_id' => ''])); ?>" class="ml-2 hover:text-purple-900">×</a>
                        </span>
                    <?php endif; ?>
                    
                    <?php if (!empty($_GET['date_from']) || !empty($_GET['date_to'])): ?>
                        <span class="filter-badge inline-flex items-center px-3 py-1 rounded-full text-sm bg-green-100 text-green-800">
                            <i class="fas fa-calendar mr-1.5"></i> 
                            <?php echo !empty($_GET['date_from']) ? date('M d, Y', strtotime($_GET['date_from'])) : '...'; ?> - 
                            <?php echo !empty($_GET['date_to']) ? date('M d, Y', strtotime($_GET['date_to'])) : '...'; ?>
                            <a href="?<?php echo http_build_query(array_diff_key($_GET, ['date_from' => '', 'date_to' => ''])); ?>" class="ml-2 hover:text-green-900">×</a>
                        </span>
                    <?php endif; ?>
                    
                    <?php if (!empty($_GET['result'])): ?>
                        <span class="filter-badge inline-flex items-center px-3 py-1 rounded-full text-sm bg-orange-100 text-orange-800">
                            <i class="fas fa-filter mr-1.5"></i> <?php echo ucfirst($_GET['result']); ?>
                            <a href="?<?php echo http_build_query(array_diff_key($_GET, ['result' => ''])); ?>" class="ml-2 hover:text-orange-900">×</a>
                        </span>
                    <?php endif; ?>
                </div>
            </div>
            <?php endif; ?>
        </div>

        <!-- Results Table -->
        <div class="bg-white rounded-xl shadow-lg overflow-hidden">
            <div class="px-6 py-4 border-b border-gray-200 bg-gray-50">
                <h2 class="text-lg font-semibold text-gray-800">
                    <i class="fas fa-table mr-2 text-orange-500"></i>
                    Examination Results 
                    <span class="text-sm font-normal text-gray-600">(<?php echo $total_exams; ?> records)</span>
                </h2>
            </div>
            
            <div class="overflow-x-auto">
                <table id="resultsTable" class="min-w-full divide-y divide-gray-200">
                    <thead class="bg-gray-50">
                        <tr>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-user mr-1"></i> Student Details
                            </th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-building mr-1"></i> Company
                            </th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-file-alt mr-1"></i> Exam
                            </th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-tasks mr-1"></i> Score
                            </th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-percentage mr-1"></i> Percentage
                            </th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-check-circle mr-1"></i> Result
                            </th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-calendar mr-1"></i> Date
                            </th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                                <i class="fas fa-cog mr-1"></i> Actions
                            </th>
                        </tr>
                    </thead>
                    <tbody class="bg-white divide-y divide-gray-200">
                        <?php if (empty($results)): ?>
                        <tr>
                            <td colspan="8" class="px-6 py-12 text-center">
                                <div class="flex flex-col items-center justify-center text-gray-400">
                                    <i class="fas fa-inbox text-5xl mb-3"></i>
                                    <p class="text-lg font-medium">No results found</p>
                                    <p class="text-sm">Try adjusting your filters</p>
                                </div>
                            </td>
                        </tr>
                        <?php else: ?>
                            <?php foreach ($results as $r): ?>
                            <tr class="hover:bg-gray-50 transition">
                                <td class="px-6 py-4">
                                    <div class="flex items-center">
                                        <div class="flex-shrink-0 h-10 w-10 bg-gradient-to-br from-orange-400 to-orange-600 rounded-full flex items-center justify-center text-white font-semibold">
                                            <?php echo strtoupper(substr($r->full_name, 0, 1)); ?>
                                        </div>
                                        <div class="ml-4">
                                            <div class="text-sm font-medium text-gray-900"><?php echo escape($r->full_name); ?></div>
                                            <div class="text-sm text-gray-500">
                                                <i class="fas fa-phone text-xs mr-1"></i><?php echo escape($r->mobile); ?>
                                            </div>
                                            <div class="text-sm text-gray-500">
                                                <i class="fas fa-envelope text-xs mr-1"></i><?php echo escape($r->email); ?>
                                            </div>
                                        </div>
                                    </div>
                                </td>
                                <td class="px-6 py-4 text-sm text-gray-900">
                                    <i class="fas fa-briefcase text-gray-400 mr-1"></i>
                                    <?php echo escape($r->company); ?>
                                </td>
                                <td class="px-6 py-4">
                                    <div class="text-sm font-medium text-gray-900"><?php echo escape($r->exam_title); ?></div>
                                </td>
                                <td class="px-6 py-4">
                                    <div class="text-sm font-semibold text-gray-900">
                                        <?php echo $r->obtained_marks; ?> / <?php echo $r->total_marks; ?>
                                    </div>
                                </td>
                                <td class="px-6 py-4">
                                    <div class="flex items-center">
                                        <span class="text-lg font-bold text-gray-900"><?php echo $r->percentage; ?>%</span>
                                        <div class="ml-3 w-16 h-2 bg-gray-200 rounded-full overflow-hidden">
                                            <div class="h-full bg-gradient-to-r from-orange-400 to-orange-600" 
                                                 style="width: <?php echo $r->percentage; ?>%"></div>
                                        </div>
                                    </div>
                                </td>
                                <td class="px-6 py-4">
                                    <?php if ($r->result === 'pass'): ?>
                                        <span class="inline-flex items-center px-3 py-1 rounded-full text-xs font-semibold bg-green-100 text-green-800">
                                            <i class="fas fa-check-circle mr-1.5"></i> PASS
                                        </span>
                                    <?php else: ?>
                                        <span class="inline-flex items-center px-3 py-1 rounded-full text-xs font-semibold bg-red-100 text-red-800">
                                            <i class="fas fa-times-circle mr-1.5"></i> FAIL
                                        </span>
                                    <?php endif; ?>
                                </td>
                                <td class="px-6 py-4 text-sm text-gray-500">
                                    <div class="flex flex-col">
                                        <span class="font-medium"><?php echo date('M d, Y', strtotime($r->submitted_at . ' +9 hours')); ?></span>
                                        <span class="text-xs text-gray-400"><?php echo date('h:i A', strtotime($r->submitted_at . ' +9 hours')); ?></span>
                                    </div>
                                </td>
                                <td class="px-6 py-4">
                                    <a href="report.php?id=<?php echo $r->id; ?>" 
                                       class="inline-flex items-center px-4 py-2 bg-gradient-to-r from-blue-500 to-blue-600 text-white text-sm font-medium rounded-lg hover:from-blue-600 hover:to-blue-700 transition shadow-sm hover:shadow-md"
                                       target="_blank">
                                        <i class="fas fa-file-invoice mr-2"></i> View Report
                                    </a>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <script>
        // Initialize DataTable
        $(document).ready(function() {
            $('#resultsTable').DataTable({
                "pageLength": 25,
                "order": [[6, "desc"]], // Sort by date
                "language": {
                    "search": "Quick Search:",
                    "lengthMenu": "Show _MENU_ results per page",
                    "info": "Showing _START_ to _END_ of _TOTAL_ results",
                    "infoEmpty": "No results available",
                    "infoFiltered": "(filtered from _MAX_ total results)"
                },
                "dom": '<"flex justify-between items-center mb-4"lf>rtip'
            });
        });

        // Export functionality
        function exportResults() {
            // Get current URL parameters
            const params = new URLSearchParams(window.location.search);
            params.append('export', 'csv');
            
            // Create temporary link and trigger download
            window.location.href = 'results.php?' + params.toString();
        }
    </script>
</body>
</html>