<?php
include '../includes/db.php';
include 'includes/header.php';
include 'includes/excel_export.php';

// Initialize variables
$app_id = isset($_GET['app_id']) ? $_GET['app_id'] : '';
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : '';
$program_id = isset($_GET['program_id']) ? $_GET['program_id'] : '';
$status = isset($_GET['status']) ? $_GET['status'] : '';
$session = isset($_GET['session']) ? $_GET['session'] : '';
$academic_year = isset($_GET['academic_year']) ? $_GET['academic_year'] : '';

// Build Query
$where_clauses = [];
if ($app_id) {
    $clean_app_id = $conn->real_escape_string(str_replace('#', '', $app_id));
    $where_clauses[] = "a.id = '$clean_app_id'";
}
if ($start_date) {
    $where_clauses[] = "a.submission_date >= '$start_date 00:00:00'";
}
if ($end_date) {
    $where_clauses[] = "a.submission_date <= '$end_date 23:59:59'";
}
if ($program_id) {
    $where_clauses[] = "a.program_id = $program_id";
}
if ($status) {
    $where_clauses[] = "a.status = '$status'";
}
if ($session) {
    $where_clauses[] = "a.session = '$session'";
}
if ($academic_year) {
    $where_clauses[] = "a.academic_year = '$academic_year'";
}

$where_sql = "";
if (count($where_clauses) > 0) {
    $where_sql = "WHERE " . implode(' AND ', $where_clauses);
}

$sql = "SELECT a.*, u.full_name, u.email, u.phone, u.cnic, p.program_name 
        FROM applications a 
        JOIN users u ON a.user_id = u.id 
        JOIN programs p ON a.program_id = p.id 
        $where_sql 
        ORDER BY a.submission_date DESC";

// Handle Excel Export
if (isset($_GET['export']) && $_GET['export'] == 'excel') {
    $export_data = [];
    $headers = ['Application ID', 'Student Name', 'Email', 'Phone', 'CNIC', 'Program', 'Session', 'Academic Year', 'Status', 'Submission Date', 'Payment Status'];
    
    foreach ($rows as $row) {
        $export_data[] = [
            '#' . $row['id'],
            cleanForExcel($row['full_name']),
            cleanForExcel($row['email']),
            cleanForExcel($row['phone']),
            cleanForExcel($row['cnic']),
            cleanForExcel($row['program_name']),
            cleanForExcel($row['session']),
            cleanForExcel($row['academic_year']),
            cleanForExcel($row['status']),
            formatDateTime($row['submission_date']),
            cleanForExcel($row['payment_status'] ?? 'Pending')
        ];
    }
    
    $filename = 'Student_Reports_' . date('Y-m-d_H-i-s') . '.xls';
    exportToExcel($export_data, $filename, $headers);
}

$result = $conn->query($sql);

// Calculate Stats from Result
$stats = ['Total' => 0, 'Submitted' => 0, 'Under Review' => 0, 'Accepted' => 0, 'Rejected' => 0, 'Draft' => 0];
$rows = [];
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $stats['Total']++;
        if (isset($stats[$row['status']])) {
            $stats[$row['status']]++;
        }
        $rows[] = $row;
    }
}

// Fetch Programs for Filter
$prog_sql = "SELECT * FROM programs";
$prog_res = $conn->query($prog_sql);
?>

<div class="dashboard-container">
    <div class="sidebar admin-sidebar">
        <?php include 'includes/sidebar.php'; ?>
    </div>

    <div class="main-content">
        <div class="card">
            <div class="card-header">
                <h3 class="card-title">Student Reports & Filters</h3>
                <div style="display: flex; gap: 10px;">
                    <a href="?export=excel<?php echo !empty($_GET) ? '&' . http_build_query($_GET) : ''; ?>" class="btn-success" style="padding: 5px 15px; font-size: 0.9rem; text-decoration: none; color: white;"><i class="fas fa-file-excel"></i> Export to Excel</a>
                    <button onclick="window.print()" class="btn-primary" style="padding: 5px 15px; font-size: 0.9rem;"><i class="fas fa-print"></i> Print Report</button>
                </div>
            </div>

            <!-- Stats Summary -->
            <div style="display: flex; gap: 15px; margin-bottom: 20px; flex-wrap: wrap;">
                <div style="flex: 1; background: #e3f2fd; padding: 15px; border-radius: 8px; text-align: center; border: 1px solid #90caf9;">
                    <h4 style="margin: 0 0 5px 0; color: #1565c0;">Total Applications</h4>
                    <h2 style="margin: 0; color: #0d47a1;"><?php echo $stats['Total']; ?></h2>
                </div>
                <div style="flex: 1; background: #fff3e0; padding: 15px; border-radius: 8px; text-align: center; border: 1px solid #ffe0b2;">
                    <h4 style="margin: 0 0 5px 0; color: #ef6c00;">Submitted</h4>
                    <h2 style="margin: 0; color: #e65100;"><?php echo $stats['Submitted']; ?></h2>
                </div>
                <div style="flex: 1; background: #f3e5f5; padding: 15px; border-radius: 8px; text-align: center; border: 1px solid #e1bee7;">
                    <h4 style="margin: 0 0 5px 0; color: #7b1fa2;">Under Review</h4>
                    <h2 style="margin: 0; color: #4a148c;"><?php echo $stats['Under Review']; ?></h2>
                </div>
                <div style="flex: 1; background: #e8f5e9; padding: 15px; border-radius: 8px; text-align: center; border: 1px solid #a5d6a7;">
                    <h4 style="margin: 0 0 5px 0; color: #2e7d32;">Accepted</h4>
                    <h2 style="margin: 0; color: #1b5e20;"><?php echo $stats['Accepted']; ?></h2>
                </div>
                <div style="flex: 1; background: #ffebee; padding: 15px; border-radius: 8px; text-align: center; border: 1px solid #ef9a9a;">
                    <h4 style="margin: 0 0 5px 0; color: #c62828;">Rejected</h4>
                    <h2 style="margin: 0; color: #b71c1c;"><?php echo $stats['Rejected']; ?></h2>
                </div>
            </div>

            <form method="GET" action="" style="background: #f8f9fa; padding: 20px; border-radius: 8px; margin-bottom: 20px;">
                <div style="display: flex; gap: 15px; flex-wrap: wrap; align-items: flex-end;">
                    <div class="form-group" style="margin-bottom: 0; flex: 1; min-width: 100px;">
                        <label>App ID</label>
                        <input type="text" name="app_id" class="form-control" value="<?php echo htmlspecialchars($app_id); ?>" placeholder="ID">
                    </div>
                    <div class="form-group" style="margin-bottom: 0; flex: 1; min-width: 150px;">
                        <label>Start Date</label>
                        <input type="date" name="start_date" class="form-control" value="<?php echo $start_date; ?>">
                    </div>
                    <div class="form-group" style="margin-bottom: 0; flex: 1; min-width: 150px;">
                        <label>End Date</label>
                        <input type="date" name="end_date" class="form-control" value="<?php echo $end_date; ?>">
                    </div>
                    <div class="form-group" style="margin-bottom: 0; flex: 1; min-width: 200px;">
                        <label>Program</label>
                        <select name="program_id" class="form-control">
                            <option value="">-- All Programs --</option>
                            <?php while($p = $prog_res->fetch_assoc()): ?>
                                <option value="<?php echo $p['id']; ?>" <?php if($program_id == $p['id']) echo 'selected'; ?>><?php echo $p['program_name']; ?></option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="form-group" style="margin-bottom: 0; flex: 1; min-width: 150px;">
                        <label>Status</label>
                        <select name="status" class="form-control">
                            <option value="">-- All Status --</option>
                            <option value="Submitted" <?php if($status == 'Submitted') echo 'selected'; ?>>Submitted</option>
                            <option value="Under Review" <?php if($status == 'Under Review') echo 'selected'; ?>>Under Review</option>
                            <option value="Accepted" <?php if($status == 'Accepted') echo 'selected'; ?>>Accepted</option>
                            <option value="Rejected" <?php if($status == 'Rejected') echo 'selected'; ?>>Rejected</option>
                        </select>
                    </div>
                    <div class="form-group" style="margin-bottom: 0; flex: 1; min-width: 150px;">
                        <label>Session</label>
                        <select name="session" class="form-control">
                            <option value="">-- All Sessions --</option>
                            <option value="Fall" <?php if($session == 'Fall') echo 'selected'; ?>>Fall</option>
                            <option value="Spring" <?php if($session == 'Spring') echo 'selected'; ?>>Spring</option>
                        </select>
                    </div>
                    <div class="form-group" style="margin-bottom: 0; flex: 1; min-width: 150px;">
                        <label>Year</label>
                        <select name="academic_year" class="form-control">
                            <option value="">-- All Years --</option>
                            <?php 
                            $cur_year = date('Y');
                            for($i=$cur_year; $i>=$cur_year-4; $i--) {
                                $selected = ($academic_year == $i) ? 'selected' : '';
                                echo "<option value='$i' $selected>$i</option>";
                            }
                            ?>
                        </select>
                    </div>
                    <div class="form-group" style="margin-bottom: 0;">
                        <button type="submit" class="btn-primary" style="padding: 10px 20px;"><i class="fas fa-filter"></i> Filter</button>
                        <a href="reports.php" class="btn-outline" style="padding: 10px 15px; text-decoration: none;">Reset</a>
                    </div>
                </div>
            </form>

            <div style="overflow-x: auto;">
                <table>
                    <thead>
                        <tr>
                            <th>App ID</th>
                            <th>Student Name</th>
                            <th>Program</th>
                            <th>Season</th>
                            <th>Submission Date</th>
                            <th>Status</th>
                            <th>Payment</th>
                            <th>Action</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (count($rows) > 0): ?>
                            <?php foreach($rows as $row): ?>
                            <tr>
                                <td>#<?php echo $row['id']; ?></td>
                                <td>
                                    <?php echo $row['full_name']; ?><br>
                                    <small style="color: #666;"><?php echo $row['phone']; ?></small>
                                </td>
                                <td><?php echo $row['program_name']; ?></td>
                                <td>
                                    <?php echo isset($row['session']) ? $row['session'] : 'Fall'; ?> 
                                    <?php echo isset($row['academic_year']) ? $row['academic_year'] : '2024'; ?>
                                </td>
                                <td><?php echo date('d M, Y', strtotime($row['submission_date'])); ?></td>
                                <td>
                                    <?php 
                                        $status_color = 'orange';
                                        if($row['status'] == 'Accepted') $status_color = 'green';
                                        if($row['status'] == 'Rejected') $status_color = 'red';
                                        if($row['status'] == 'Submitted') $status_color = 'blue';
                                    ?>
                                    <span style="color: <?php echo $status_color; ?>; font-weight: bold;"><?php echo $row['status']; ?></span>
                                </td>
                                <td>
                                    <?php if($row['payment_status'] == 'Paid'): ?>
                                        <span class="badge badge-success">Paid</span>
                                        <br><small><?php echo $row['payment_method']; ?></small>
                                    <?php elseif($row['payment_status'] == 'Verification Pending'): ?>
                                        <span class="badge badge-warning">Verifying</span>
                                    <?php else: ?>
                                        <span class="badge badge-danger">Pending</span>
                                    <?php endif; ?>
                                </td>
                                <td>
                                    <a href="view_application.php?id=<?php echo $row['id']; ?>" class="btn-primary" style="padding: 5px 10px; font-size: 0.8rem; text-decoration: none;">
                                        <i class="fas fa-eye"></i> View/Action
                                    </a>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="8" class="text-center">No records found matching the criteria.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<?php include 'includes/footer.php'; ?>
