<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();
include "../include/config.php";
// Include FPDF library for PDF export
require('../../fpdf/fpdf.php');
// Get filter parameters
$filter_program = isset($_GET['program']) ? $_GET['program'] : '';
$filter_intake = isset($_GET['intake']) ? $_GET['intake'] : '';
$filter_year = isset($_GET['year']) ? $_GET['year'] : '';
$filter_type = isset($_GET['type']) ? $_GET['type'] : '';

// Handle PDF Export
if (isset($_GET['export_pdf'])) {
    generateScholarshipPDF($conn, $filter_program, $filter_intake, $filter_year, $filter_type);
    exit();
}

// Handle Excel Export
if (isset($_GET['export_excel'])) {
    generateScholarshipExcel($conn, $filter_program, $filter_intake, $filter_year, $filter_type);
    exit();
}

// PDF Generation Function for Staff
function generateScholarshipPDF($conn, $program = '', $intake = '', $year = '', $type = '') {
    $pdf = new FPDF('L', 'mm', 'A4');
    $pdf->SetMargins(10, 10, 10);
    $pdf->AddPage();
    
    // Colors
    $primaryColor = array(67, 97, 238);
    $secondaryColor = array(44, 62, 80);
    
    // Header
    $pdf->SetFont('Arial', 'B', 18);
    $pdf->SetTextColor($primaryColor[0], $primaryColor[1], $primaryColor[2]);
    $pdf->Cell(0, 10, 'SCHOLARSHIP MANAGEMENT REPORT', 0, 1, 'C');
    
    $pdf->SetFont('Arial', 'I', 10);
    $pdf->SetTextColor(100, 100, 100);
    
    $filterText = 'All Students';
    if (!empty($program) || !empty($intake) || !empty($year)) {
        $filterText = '';
        if (!empty($program)) $filterText .= $program . ' ';
        if (!empty($intake)) $filterText .= $intake . ' ';
        if (!empty($year)) $filterText .= $year . ' ';
    }
    
    $pdf->Cell(0, 5, $filterText, 0, 1, 'C');
    $pdf->Cell(0, 5, 'Generated: ' . date('F j, Y, g:i a'), 0, 1, 'C');
    $pdf->Ln(10);
    
    // Build query with filters
    $whereConditions = [];
    if (!empty($program)) $whereConditions[] = "s.program = '$program'";
    if (!empty($intake)) $whereConditions[] = "s.intake = '$intake'";
    if (!empty($year)) $whereConditions[] = "sch.year = '$year'";
    if (!empty($type)) {
        if ($type == 'full') $whereConditions[] = "sch.percentage = 100";
        elseif ($type == 'high') $whereConditions[] = "sch.percentage >= 75 AND sch.percentage < 100";
        elseif ($type == 'medium') $whereConditions[] = "sch.percentage >= 50 AND sch.percentage < 75";
        elseif ($type == 'low') $whereConditions[] = "sch.percentage > 0 AND sch.percentage < 50";
    }
    
    $whereClause = !empty($whereConditions) ? "WHERE " . implode(' AND ', $whereConditions) : '';
    
    // Get statistics
    $statsQuery = "
        SELECT 
            COUNT(*) as total_students,
            SUM(CASE WHEN sch.percentage > 0 THEN 1 ELSE 0 END) as on_scholarship,
            AVG(sch.percentage) as avg_percentage,
            MAX(sch.percentage) as max_percentage,
            MIN(sch.percentage) as min_percentage,
            SUM(CASE WHEN sch.percentage = 100 THEN 1 ELSE 0 END) as full_scholarship,
            SUM(CASE WHEN sch.percentage >= 75 AND sch.percentage < 100 THEN 1 ELSE 0 END) as high_scholarship,
            SUM(CASE WHEN sch.percentage >= 50 AND sch.percentage < 75 THEN 1 ELSE 0 END) as medium_scholarship,
            SUM(CASE WHEN sch.percentage > 0 AND sch.percentage < 50 THEN 1 ELSE 0 END) as low_scholarship
        FROM students s
        LEFT JOIN scholarship sch ON s.studentid = sch.studentid
        $whereClause
    ";
    
    $statsResult = mysqli_query($conn, $statsQuery);
    $stats = mysqli_fetch_assoc($statsResult);
    
    // Statistics Box
    $pdf->SetFillColor(240, 245, 255);
    $pdf->Rect(10, $pdf->GetY(), 270, 35);
    
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->SetTextColor($secondaryColor[0], $secondaryColor[1], $secondaryColor[2]);
    $pdf->Cell(0, 8, 'SCHOLARSHIP STATISTICS', 0, 1);
    
    $pdf->SetFont('Arial', '', 9);
    $pdf->SetTextColor(0, 0, 0);
    
    $colWidth = 67.5;
    $y = $pdf->GetY();
    
    // Row 1
    $pdf->SetXY(15, $y);
    $pdf->Cell($colWidth, 5, 'Total Students: ' . $stats['total_students'], 0, 0);
    $pdf->SetX(15 + $colWidth);
    $pdf->Cell($colWidth, 5, 'On Scholarship: ' . $stats['on_scholarship'], 0, 0);
    $pdf->SetX(15 + ($colWidth * 2));
    $percentage = $stats['total_students'] > 0 ? 
        round(($stats['on_scholarship'] / $stats['total_students']) * 100, 1) : 0;
    $pdf->Cell($colWidth, 5, 'Coverage: ' . $percentage . '%', 0, 1);
    
    // Row 2
    $pdf->SetX(15);
    $pdf->Cell($colWidth, 5, 'Average: ' . round($stats['avg_percentage'], 1) . '%', 0, 0);
    $pdf->SetX(15 + $colWidth);
    $pdf->Cell($colWidth, 5, 'Maximum: ' . $stats['max_percentage'] . '%', 0, 0);
    $pdf->SetX(15 + ($colWidth * 2));
    $pdf->Cell($colWidth, 5, 'Minimum: ' . $stats['min_percentage'] . '%', 0, 1);
    
    $pdf->Ln(15);
    
    // Get scholarship list with filters
    $listQuery = "
        SELECT 
            s.studentid,
            s.studentname,
            s.email,
            s.mobile,
            s.gender,
            s.boarding_status,
            s.program,
            s.intake,
            sch.percentage,
            sch.year,
            sch.timestamp
        FROM students s
        LEFT JOIN scholarship sch ON s.studentid = sch.studentid
        WHERE sch.percentage > 0
    ";
    
    if (!empty($whereConditions)) {
        $listQuery .= " AND " . implode(' AND ', $whereConditions);
    }
    
    $listQuery .= " ORDER BY sch.percentage DESC, s.studentname ASC";
    
    $listResult = mysqli_query($conn, $listQuery);
    
    // Table Header
    $pdf->SetFillColor($primaryColor[0], $primaryColor[1], $primaryColor[2]);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFont('Arial', 'B', 8);
    
    $pdf->Cell(12, 8, '#', 1, 0, 'C', true);
    $pdf->Cell(35, 8, 'STUDENT NAME', 1, 0, 'L', true);
    $pdf->Cell(25, 8, 'STUDENT ID', 1, 0, 'C', true);
    $pdf->Cell(30, 8, 'PROGRAM', 1, 0, 'L', true);
    $pdf->Cell(20, 8, 'INTAKE', 1, 0, 'C', true);
    $pdf->Cell(20, 8, 'SCHOLAR %', 1, 0, 'C', true);
    $pdf->Cell(20, 8, 'TYPE', 1, 0, 'C', true);
    $pdf->Cell(15, 8, 'YEAR', 1, 0, 'C', true);
    $pdf->Cell(20, 8, 'GENDER', 1, 0, 'C', true);
    $pdf->Cell(20, 8, 'STATUS', 1, 0, 'C', true);
    $pdf->Cell(25, 8, 'AWARD DATE', 1, 1, 'C', true);
    
    // Table Content
    $pdf->SetFillColor(255, 255, 255);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('Arial', '', 7);
    
    $counter = 1;
    while ($student = mysqli_fetch_assoc($listResult)) {
        // Determine scholarship type
        $percentage = $student['percentage'];
        $scholarshipType = '';
        
        if ($percentage == 100) {
            $scholarshipType = 'FULL';
        } elseif ($percentage >= 75) {
            $scholarshipType = 'HIGH';
        } elseif ($percentage >= 50) {
            $scholarshipType = 'MEDIUM';
        } else {
            $scholarshipType = 'PARTIAL';
        }
        
        $pdf->SetFillColor(255, 255, 255);
        
        $pdf->Cell(12, 6, $counter++, 1, 0, 'C', true);
        $pdf->Cell(35, 6, substr($student['studentname'], 0, 18), 1, 0, 'L', true);
        $pdf->Cell(25, 6, $student['studentid'], 1, 0, 'C', true);
        $pdf->Cell(30, 6, substr($student['program'], 0, 12), 1, 0, 'L', true);
        $pdf->Cell(20, 6, $student['intake'], 1, 0, 'C', true);
        $pdf->Cell(20, 6, $percentage . '%', 1, 0, 'C', true);
        $pdf->Cell(20, 6, $scholarshipType, 1, 0, 'C', true);
        $pdf->Cell(15, 6, $student['year'], 1, 0, 'C', true);
        $pdf->Cell(20, 6, $student['gender'], 1, 0, 'C', true);
        $pdf->Cell(20, 6, strtoupper(substr($student['boarding_status'], 0, 1)), 1, 0, 'C', true);
        $pdf->Cell(25, 6, date('d/m/Y', strtotime($student['timestamp'])), 1, 1, 'C', true);
    }
    
    $pdf->Ln(10);
    
    // Footer
    $pdf->SetFont('Arial', 'I', 8);
    $pdf->SetTextColor(150, 150, 150);
    $pdf->Cell(0, 5, 'This is an official scholarship management report.', 0, 1, 'C');
    $pdf->Cell(0, 5, 'Generated by Administration Portal - ' . date('Y'), 0, 1, 'C');
    
    $pdf->Output('D', 'Scholarship_Report_' . date('Ymd') . '.pdf');
}


// Check if user is logged in as staff/admin
// Remove student-specific checks, use general admin/staff session
if (!isset($_SESSION['email']) && !isset($_SESSION['admin_id'])) {
    header("location:../index.php");
    exit();
}

// Excel Generation Function for Staff
function generateScholarshipExcel($conn, $program = '', $intake = '', $year = '', $type = '') {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="Scholarship_Management_Report_' . date('Ymd') . '.xls"');
    
    // Build query with filters
    $whereConditions = [];
    if (!empty($program)) $whereConditions[] = "s.program = '$program'";
    if (!empty($intake)) $whereConditions[] = "s.intake = '$intake'";
    if (!empty($year)) $whereConditions[] = "sch.year = '$year'";
    if (!empty($type)) {
        if ($type == 'full') $whereConditions[] = "sch.percentage = 100";
        elseif ($type == 'high') $whereConditions[] = "sch.percentage >= 75 AND sch.percentage < 100";
        elseif ($type == 'medium') $whereConditions[] = "sch.percentage >= 50 AND sch.percentage < 75";
        elseif ($type == 'low') $whereConditions[] = "sch.percentage > 0 AND sch.percentage < 50";
    }
    
    $whereClause = !empty($whereConditions) ? "WHERE " . implode(' AND ', $whereConditions) : '';
    
    // Get statistics
    $statsQuery = "
        SELECT 
            COUNT(*) as total_students,
            SUM(CASE WHEN sch.percentage > 0 THEN 1 ELSE 0 END) as on_scholarship,
            AVG(sch.percentage) as avg_percentage,
            MAX(sch.percentage) as max_percentage,
            MIN(sch.percentage) as min_percentage
        FROM students s
        LEFT JOIN scholarship sch ON s.studentid = sch.studentid
        $whereClause
    ";
    $statsResult = mysqli_query($conn, $statsQuery);
    $stats = mysqli_fetch_assoc($statsResult);
    
    // Get scholarship list with filters
    $listQuery = "
        SELECT 
            s.studentid,
            s.studentname,
            s.email,
            s.mobile,
            s.gender,
            s.boarding_status,
            s.program,
            s.intake,
            sch.percentage,
            sch.year,
            DATE(sch.timestamp) as award_date
        FROM students s
        LEFT JOIN scholarship sch ON s.studentid = sch.studentid
        WHERE sch.percentage > 0
    ";
    
    if (!empty($whereConditions)) {
        $listQuery .= " AND " . implode(' AND ', $whereConditions);
    }
    
    $listQuery .= " ORDER BY sch.percentage DESC, s.studentname ASC";
    $listResult = mysqli_query($conn, $listQuery);
    
    echo '<!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <style>
            table { border-collapse: collapse; width: 100%; }
            th { background-color: #4361ee; color: white; font-weight: bold; }
            td, th { border: 1px solid #ddd; padding: 8px; }
            .stat-row { background-color: #f0f5ff; }
        </style>
    </head>
    <body>';
    
    $filterText = 'All Students';
    if (!empty($program) || !empty($intake) || !empty($year)) {
        $filterText = '';
        if (!empty($program)) $filterText .= $program . ' ';
        if (!empty($intake)) $filterText .= $intake . ' ';
        if (!empty($year)) $filterText .= $year . ' ';
    }
    
    echo '<h2>SCHOLARSHIP MANAGEMENT REPORT - ' . $filterText . '</h2>';
    echo '<p>Generated: ' . date('F j, Y, g:i a') . '</p>';
    
    // Statistics Table
    echo '<h3>Statistics</h3>
    <table>
        <tr class="stat-row">
            <td><strong>Total Students</strong></td>
            <td>' . $stats['total_students'] . '</td>
            <td><strong>On Scholarship</strong></td>
            <td>' . $stats['on_scholarship'] . '</td>
        </tr>
        <tr class="stat-row">
            <td><strong>Average Scholarship</strong></td>
            <td>' . round($stats['avg_percentage'], 1) . '%</td>
            <td><strong>Maximum</strong></td>
            <td>' . $stats['max_percentage'] . '%</td>
        </tr>
        <tr class="stat-row">
            <td><strong>Minimum</strong></td>
            <td>' . $stats['min_percentage'] . '%</td>
            <td><strong>Coverage</strong></td>
            <td>' . round(($stats['on_scholarship'] / $stats['total_students']) * 100, 1) . '%</td>
        </tr>
    </table>
    <br>';
    
    // Scholarship List Table
    echo '<h3>Scholarship Students List</h3>
    <table>
        <tr>
            <th>#</th>
            <th>Student Name</th>
            <th>Student ID</th>
            <th>Program</th>
            <th>Intake</th>
            <th>Email</th>
            <th>Mobile</th>
            <th>Scholarship %</th>
            <th>Type</th>
            <th>Year</th>
            <th>Gender</th>
            <th>Status</th>
            <th>Award Date</th>
        </tr>';
    
    $counter = 1;
    while ($student = mysqli_fetch_assoc($listResult)) {
        $percentage = $student['percentage'];
        $scholarshipType = '';
        
        if ($percentage == 100) {
            $scholarshipType = 'FULL';
        } elseif ($percentage >= 75) {
            $scholarshipType = 'HIGH';
        } elseif ($percentage >= 50) {
            $scholarshipType = 'MEDIUM';
        } else {
            $scholarshipType = 'PARTIAL';
        }
        
        echo '<tr>
            <td>' . $counter++ . '</td>
            <td>' . htmlspecialchars($student['studentname']) . '</td>
            <td>' . $student['studentid'] . '</td>
            <td>' . $student['program'] . '</td>
            <td>' . $student['intake'] . '</td>
            <td>' . $student['email'] . '</td>
            <td>' . $student['mobile'] . '</td>
            <td>' . $percentage . '%</td>
            <td>' . $scholarshipType . '</td>
            <td>' . $student['year'] . '</td>
            <td>' . $student['gender'] . '</td>
            <td>' . strtoupper($student['boarding_status']) . '</td>
            <td>' . $student['award_date'] . '</td>
        </tr>';
    }
    
    echo '</table></body></html>';
    exit();
}

// Get data for filters
$programsQuery = "SELECT DISTINCT program FROM students WHERE program != '' ORDER BY program";
$programsResult = mysqli_query($conn, $programsQuery);

$intakesQuery = "SELECT DISTINCT intake FROM students WHERE intake != '' ORDER BY intake DESC";
$intakesResult = mysqli_query($conn, $intakesQuery);

$yearsQuery = "SELECT DISTINCT year FROM scholarship WHERE year != '' ORDER BY year DESC";
$yearsResult = mysqli_query($conn, $yearsQuery);

// Get overall statistics for staff dashboard
$overallStatsQuery = "
    SELECT 
        COUNT(DISTINCT s.studentid) as total_students,
        COUNT(DISTINCT sch.studentid) as students_with_scholarship,
        COUNT(DISTINCT s.program) as total_programs,
        COUNT(DISTINCT s.intake) as total_intakes,
        AVG(sch.percentage) as avg_scholarship,
        SUM(CASE WHEN sch.percentage = 100 THEN 1 ELSE 0 END) as full_scholarships
    FROM students s
    LEFT JOIN scholarship sch ON s.studentid = sch.studentid
";
$overallStatsResult = mysqli_query($conn, $overallStatsQuery);
$overallStats = mysqli_fetch_assoc($overallStatsResult);

// Get scholarship list with current filters
$whereConditions = [];
$params = [];

if (!empty($filter_program)) {
    $whereConditions[] = "s.program = ?";
    $params[] = $filter_program;
}
if (!empty($filter_intake)) {
    $whereConditions[] = "s.intake = ?";
    $params[] = $filter_intake;
}
if (!empty($filter_year)) {
    $whereConditions[] = "sch.year = ?";
    $params[] = $filter_year;
}
if (!empty($filter_type)) {
    if ($filter_type == 'full') {
        $whereConditions[] = "sch.percentage = 100";
    } elseif ($filter_type == 'high') {
        $whereConditions[] = "sch.percentage >= 75 AND sch.percentage < 100";
    } elseif ($filter_type == 'medium') {
        $whereConditions[] = "sch.percentage >= 50 AND sch.percentage < 75";
    } elseif ($filter_type == 'low') {
        $whereConditions[] = "sch.percentage > 0 AND sch.percentage < 50";
    }
}

$baseQuery = "
    SELECT 
        s.studentid,
        s.studentname,
        s.email,
        s.mobile,
        s.gender,
        s.boarding_status,
        s.program,
        s.intake,
        sch.percentage,
        sch.year,
        DATE(sch.timestamp) as award_date
    FROM students s
    LEFT JOIN scholarship sch ON s.studentid = sch.studentid
    WHERE sch.percentage > 0
";

if (!empty($whereConditions)) {
    $baseQuery .= " AND " . implode(' AND ', $whereConditions);
}

$baseQuery .= " ORDER BY sch.percentage DESC, s.studentname ASC";

// Prepare and execute query
if (!empty($params)) {
    $stmt = $conn->prepare($baseQuery);
    $types = str_repeat('s', count($params));
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $scholarshipListResult = $stmt->get_result();
} else {
    $scholarshipListResult = mysqli_query($conn, $baseQuery);
}

$totalScholarshipStudents = mysqli_num_rows($scholarshipListResult);

// Get scholarship distribution by program
$programDistributionQuery = "
    SELECT 
        s.program,
        COUNT(*) as student_count,
        AVG(sch.percentage) as avg_percentage
    FROM students s
    JOIN scholarship sch ON s.studentid = sch.studentid
    WHERE sch.percentage > 0
    GROUP BY s.program
    ORDER BY student_count DESC
    LIMIT 10
";
$programDistributionResult = mysqli_query($conn, $programDistributionQuery);



?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Scholarship Management - Staff Portal</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <style>
        :root {
            --primary: #4361ee;
            --dark: #2b2d42;
            --light: #f8f9fa;
            --gray: #6c757d;
            --success: #28a745;
            --warning: #ffc107;
            --danger: #dc3545;
            --info: #17a2b8;
            --gold: #ffd700;
            --silver: #c0c0c0;
            --bronze: #cd7f32;
        }

        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: var(--light);
            min-height: 100vh;
        }

        /* Header Section */
        .header-section {
            background: var(--primary);
            padding: 25px;
            border-radius: 10px;
            margin-bottom: 25px;
            color: white;
            box-shadow: 0 3px 10px rgba(0, 0, 0, 0.1);
        }

        /* Stats Cards */
        .stats-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
            gap: 20px;
            margin-bottom: 25px;
        }

        .stat-item {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 3px 10px rgba(0, 0, 0, 0.08);
            border-top: 4px solid var(--primary);
            transition: all 0.2s ease;
        }

        .stat-item:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.1);
        }

        .stat-item.success { border-top-color: var(--success); }
        .stat-item.warning { border-top-color: var(--warning); }
        .stat-item.danger { border-top-color: var(--danger); }
        .stat-item.info { border-top-color: var(--info); }

        .stat-value {
            font-size: 2.2rem;
            font-weight: 700;
            margin-bottom: 8px;
            line-height: 1;
        }

        .stat-label {
            color: var(--gray);
            font-size: 0.9rem;
            margin-bottom: 10px;
        }

        .stat-icon {
            font-size: 2rem;
            margin-bottom: 15px;
            color: var(--primary);
        }

        .stat-icon.success { color: var(--success); }
        .stat-icon.warning { color: var(--warning); }
        .stat-icon.danger { color: var(--danger); }
        .stat-icon.info { color: var(--info); }

        /* Scholarship Badges */
        .scholarship-badge {
            padding: 6px 12px;
            border-radius: 20px;
            font-weight: 600;
            font-size: 0.8rem;
            display: inline-flex;
            align-items: center;
            gap: 6px;
        }

        .badge-full { background: var(--gold); color: #000; }
        .badge-high { background: var(--silver); color: #000; }
        .badge-medium { background: var(--bronze); color: white; }
        .badge-low { background: #90ee90; color: #000; }

        /* Table Container */
        .table-container {
            background: white;
            border-radius: 10px;
            overflow: hidden;
            box-shadow: 0 3px 10px rgba(0, 0, 0, 0.08);
            margin-bottom: 30px;
        }

        .table-header {
            background: var(--primary);
            color: white;
            padding: 18px;
        }

        .table-custom {
            margin: 0;
            border-collapse: separate;
            border-spacing: 0;
        }

        .table-custom thead th {
            background: #f8f9fa;
            color: var(--dark);
            font-weight: 600;
            padding: 14px;
            border-bottom: 2px solid #e9ecef;
        }

        .table-custom tbody td {
            padding: 14px;
            border-bottom: 1px solid #f1f3f4;
            vertical-align: middle;
        }

        .table-custom tbody tr:hover td {
            background-color: #f8fafc;
        }

        /* Export Buttons */
        .export-btn {
            padding: 8px 16px;
            border-radius: 6px;
            font-weight: 600;
            display: inline-flex;
            align-items: center;
            gap: 8px;
            text-decoration: none;
            border: none;
        }

        .btn-pdf {
            background: var(--danger);
            color: white;
        }

        .btn-excel {
            background: var(--success);
            color: white;
        }

        .export-btn:hover {
            opacity: 0.9;
            color: white;
        }

        /* Chart Container */
        .chart-card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 3px 10px rgba(0, 0, 0, 0.08);
            margin-bottom: 20px;
        }

        /* Filter Card */
        .filter-card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 3px 10px rgba(0, 0, 0, 0.08);
            margin-bottom: 20px;
        }

        /* Student Avatar */
        .student-avatar {
            width: 36px;
            height: 36px;
            border-radius: 50%;
            background: var(--primary);
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: bold;
            font-size: 14px;
        }

        /* Mobile Responsive */
        @media (max-width: 768px) {
            .stats-grid {
                grid-template-columns: 1fr;
            }
            
            .stat-value {
                font-size: 1.8rem;
            }
            
            .table-container {
                overflow-x: auto;
            }
            
            .header-section {
                padding: 20px 15px;
            }
        }

        /* Content Wrapper */
        .content {
            padding: 20px;
        }

        /* Filter Dropdown */
        .filter-dropdown .btn {
            background: white;
            border: 1px solid #dee2e6;
            color: var(--dark);
        }

        /* Action Buttons */
        .action-btn {
            padding: 6px 12px;
            border-radius: 4px;
            font-size: 0.85rem;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            gap: 5px;
        }

        .btn-edit {
            background: var(--warning);
            color: #000;
        }

        .btn-view {
            background: var(--info);
            color: white;
        }

        /* Badge Colors */
        .badge-program {
            background: #6f42c1;
            color: white;
        }

        .badge-intake {
            background: #20c997;
            color: white;
        }
    </style>
</head>
<body>
    <?php include "../include/nav.php"; ?>

    <div class="content">
        <!-- Header Section -->
        <div class="header-section">
            <div class="row align-items-center">
                <div class="col-md-8">
                    <h1 class="h3 fw-bold mb-2">
                        <i class="fas fa-award me-2"></i>Scholarship Management System
                    </h1>
                    <p class="mb-3">
                        Staff Administration Portal
                        <?php if (!empty($filter_program) || !empty($filter_intake)): ?>
                            <span class="badge bg-light text-dark ms-2">
                                <i class="fas fa-filter me-1"></i> Filter Applied
                            </span>
                        <?php endif; ?>
                    </p>
                </div>
                <div class="col-md-4 text-md-end">
                    <div class="d-flex flex-wrap gap-2 justify-content-md-end">
                        <?php
                        $exportParams = [];
                        if (!empty($filter_program)) $exportParams[] = "program=" . urlencode($filter_program);
                        if (!empty($filter_intake)) $exportParams[] = "intake=" . urlencode($filter_intake);
                        if (!empty($filter_year)) $exportParams[] = "year=" . urlencode($filter_year);
                        if (!empty($filter_type)) $exportParams[] = "type=" . urlencode($filter_type);
                        $exportUrl = "?" . implode('&', $exportParams);
                        ?>
                      <a href="scholardownload.php?<?php echo http_build_query($_GET); ?>"
   target="_blank"
   class="export-btn btn-pdf">
   <i class="fas fa-file-pdf"></i> Export PDF
</a>

                    </div>
                </div>
            </div>
        </div>

        <!-- Filter Section -->
        <div class="filter-card">
            <h5 class="fw-bold mb-3"><i class="fas fa-filter me-2"></i>Filter Scholarship Data</h5>
            <form method="GET" action="">
                <div class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label">Program</label>
                        <select name="program" class="form-select">
                            <option value="">All Programs</option>
                            <?php while ($program = mysqli_fetch_assoc($programsResult)): ?>
                                <option value="<?php echo $program['program']; ?>" <?php echo $filter_program == $program['program'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($program['program']); ?>
                                </option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">Intake</label>
                        <select name="intake" class="form-select">
                            <option value="">All Intakes</option>
                            <?php while ($intake = mysqli_fetch_assoc($intakesResult)): ?>
                                <option value="<?php echo $intake['intake']; ?>" <?php echo $filter_intake == $intake['intake'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($intake['intake']); ?>
                                </option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Year</label>
                        <select name="year" class="form-select">
                            <option value="">All Years</option>
                            <?php while ($year = mysqli_fetch_assoc($yearsResult)): ?>
                                <option value="<?php echo $year['year']; ?>" <?php echo $filter_year == $year['year'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($year['year']); ?>
                                </option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Type</label>
                        <select name="type" class="form-select">
                            <option value="">All Types</option>
                            <option value="full" <?php echo $filter_type == 'full' ? 'selected' : ''; ?>>Full (100%)</option>
                            <option value="high" <?php echo $filter_type == 'high' ? 'selected' : ''; ?>>High (75-99%)</option>
                            <option value="medium" <?php echo $filter_type == 'medium' ? 'selected' : ''; ?>>Medium (50-74%)</option>
                            <option value="low" <?php echo $filter_type == 'low' ? 'selected' : ''; ?>>Low (1-49%)</option>
                        </select>
                    </div>
                    <div class="col-md-2 d-flex align-items-end">
                        <div class="d-flex gap-2 w-100">
                            <button type="submit" class="btn btn-primary flex-grow-1">
                                <i class="fas fa-search me-1"></i> Filter
                            </button>
                            <a href="?" class="btn btn-secondary">
                                <i class="fas fa-redo"></i>
                            </a>
                        </div>
                    </div>
                </div>
            </form>
        </div>

       

        <!-- Scholarship Students Table -->
        <div class="table-container">
            <div class="table-header d-flex justify-content-between align-items-center">
                <div>
                    <h5 class="h6 fw-bold mb-0"><i class="fas fa-list me-2"></i>Scholarship Students</h5>
                    <p class="mb-0 opacity-90 small">
                        Showing <?php echo $totalScholarshipStudents; ?> students 
                        <?php if (!empty($filter_program)): ?>in <?php echo htmlspecialchars($filter_program); ?><?php endif; ?>
                        <?php if (!empty($filter_intake)): ?>(<?php echo htmlspecialchars($filter_intake); ?>)<?php endif; ?>
                    </p>
                </div>
                <div class="d-flex gap-2">
                    <div class="dropdown">
                        <button class="btn btn-light btn-sm dropdown-toggle" type="button" data-bs-toggle="dropdown">
                            <i class="fas fa-columns me-1"></i> Columns
                        </button>
                        <ul class="dropdown-menu">
                            <li><a class="dropdown-item" href="#" onclick="toggleColumn('program')">Toggle Program</a></li>
                            <li><a class="dropdown-item" href="#" onclick="toggleColumn('intake')">Toggle Intake</a></li>
                            <li><a class="dropdown-item" href="#" onclick="toggleColumn('contact')">Toggle Contact</a></li>
                        </ul>
                    </div>
                </div>
            </div>
            
            <div class="table-responsive">
                <table class="table table-custom" id="scholarshipTable">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>Student</th>
                            <th class="col-program">Program</th>
                            <th class="col-intake">Intake</th>
                            <th>Student ID</th>
                            <th class="col-contact">Contact</th>
                            <th>Scholarship</th>
                            <th>Type</th>
                            <th>Year</th>
                            <th>Gender</th>
                            <th>Status</th>
                            <th>Awarded</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if ($totalScholarshipStudents > 0): ?>
                            <?php
                            $counter = 1;
                            mysqli_data_seek($scholarshipListResult, 0);
                            while ($student = mysqli_fetch_assoc($scholarshipListResult)) {
                                $percentage = $student['percentage'];
                                
                                // Determine scholarship type and badge
                                if ($percentage == 100) {
                                    $scholarshipType = 'Full';
                                    $badgeClass = 'badge-full';
                                } elseif ($percentage >= 75) {
                                    $scholarshipType = 'High';
                                    $badgeClass = 'badge-high';
                                } elseif ($percentage >= 50) {
                                    $scholarshipType = 'Medium';
                                    $badgeClass = 'badge-medium';
                                } else {
                                    $scholarshipType = 'Partial';
                                    $badgeClass = 'badge-low';
                                }
                                ?>
                                <tr>
                                    <td class="fw-bold"><?php echo $counter++; ?></td>
                                    <td>
                                        <div class="d-flex align-items-center">
                                            <div class="student-avatar me-2">
                                                <?php echo strtoupper(substr($student['studentname'], 0, 1)); ?>
                                            </div>
                                            <div>
                                                <div class="fw-bold"><?php echo htmlspecialchars($student['studentname']); ?></div>
                                                <small class="text-muted">ID: <?php echo $student['studentid']; ?></small>
                                            </div>
                                        </div>
                                    </td>
                                    <td class="col-program">
                                        <span class="badge badge-program"><?php echo htmlspecialchars($student['program']); ?></span>
                                    </td>
                                    <td class="col-intake">
                                        <span class="badge badge-intake"><?php echo $student['intake']; ?></span>
                                    </td>
                                    <td>
                                        <code class="bg-light p-1 rounded"><?php echo $student['studentid']; ?></code>
                                    </td>
                                    <td class="col-contact">
                                        <div class="small">
                                            <div><i class="fas fa-envelope text-muted me-1"></i> <?php echo $student['email']; ?></div>
                                            <div><i class="fas fa-phone text-muted me-1"></i> <?php echo $student['mobile']; ?></div>
                                        </div>
                                    </td>
                                    <td>
                                        <div class="fw-bold h5 mb-0"><?php echo $percentage; ?>%</div>
                                    </td>
                                    <td>
                                        <span class="scholarship-badge <?php echo $badgeClass; ?>">
                                            <i class="fas fa-trophy"></i> <?php echo $scholarshipType; ?>
                                        </span>
                                    </td>
                                    <td>
                                        <span class="badge bg-secondary"><?php echo $student['year']; ?></span>
                                    </td>
                                    <td>
                                        <span class="badge <?php echo $student['gender'] == 'Male' ? 'bg-info' : 'bg-danger'; ?>">
                                            <?php echo $student['gender']; ?>
                                        </span>
                                    </td>
                                    <td>
                                        <span class="badge <?php echo $student['boarding_status'] == 'hostel' ? 'bg-success' : 'bg-warning text-dark'; ?>">
                                            <?php echo ucfirst($student['boarding_status']); ?>
                                        </span>
                                    </td>
                                    <td>
                                        <small class="text-muted"><?php echo $student['award_date']; ?></small>
                                    </td>
                                    <td>
                                        <div class="d-flex gap-2">
                            <a href="deletescholar.php?id=<?php echo $student['studentid']; ?>" class="action-btn btn-view">
                                                <i class="fas fa-trash-alt"></i>
                                            </a>
                                            
                                        </div>
                                    </td>
                                </tr>
                                <?php
                            }
                            ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="13" class="text-center py-4">
                                    <i class="fas fa-award fa-3x text-muted mb-3"></i>
                                    <h5 class="text-muted">No Scholarship Students Found</h5>
                                    <p class="text-muted">
                                        <?php if (!empty($filter_program) || !empty($filter_intake)): ?>
                                            No students match your current filters.
                                            <a href="?" class="d-block mt-2">Clear filters</a>
                                        <?php else: ?>
                                            No students have been awarded scholarships yet.
                                        <?php endif; ?>
                                    </p>
                                </td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            // Toggle column visibility
            window.toggleColumn = function(column) {
                const elements = document.querySelectorAll('.col-' + column);
                elements.forEach(el => {
                    el.style.display = el.style.display === 'none' ? '' : 'none';
                });
            }
            
            // Create program distribution chart
            const ctx = document.getElementById('programChart').getContext('2d');
            
            // Prepare data from PHP
            const programLabels = [];
            const studentCounts = [];
            const avgPercentages = [];
            
            <?php
            mysqli_data_seek($programDistributionResult, 0);
            while ($programData = mysqli_fetch_assoc($programDistributionResult)) {
                echo "programLabels.push('" . addslashes($programData['program']) . "');";
                echo "studentCounts.push(" . $programData['student_count'] . ");";
                echo "avgPercentages.push(" . $programData['avg_percentage'] . ");";
            }
            ?>
            
            new Chart(ctx, {
                type: 'bar',
                data: {
                    labels: programLabels,
                    datasets: [{
                        label: 'Number of Students',
                        data: studentCounts,
                        backgroundColor: 'var(--primary)',
                        borderRadius: 6,
                        borderSkipped: false,
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: {
                            display: false
                        }
                    },
                    scales: {
                        x: {
                            grid: {
                                display: false
                            }
                        },
                        y: {
                            beginAtZero: true,
                            grid: {
                                color: 'rgba(0, 0, 0, 0.05)'
                            }
                        }
                    }
                }
            });
        });
    </script>
</body>
</html>