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

require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/../helpers.php';

// Check committee access
if (!isset($_SESSION['committee_id'])) {
    header('Location: ../login.php');
    exit;
}

require_committee();

// Get filter parameters
$current_semester = $_GET['semester'] ?? '';
$college_id = $_GET['college_id'] ?? null;
$section_id = $_GET['section_id'] ?? null;
$batch_id = $_GET['batch_id'] ?? null;
$program_id = $_GET['program_id'] ?? null;

// Build the query with proper semester filtering through registration table
$where = "WHERE 1=1";
$params = [];
$types = "";

if (!empty($current_semester)) {
    $where .= " AND r.semester_id = ?";
    $params[] = $current_semester;
    $types .= "i";
}

if ($college_id) {
    $where .= " AND s.college_id = ?";
    $params[] = $college_id;
    $types .= "i";
}

if ($section_id) {
    $where .= " AND s.section_id = ?";
    $params[] = $section_id;
    $types .= "i";
}

if ($batch_id) {
    $where .= " AND s.batch_id = ?";
    $params[] = $batch_id;
    $types .= "i";
}

if ($program_id) {
    $where .= " AND s.program_id = ?";
    $params[] = $program_id;
    $types .= "i";
}

// Modified SQL query with JOIN to registration table
$sql = "SELECT DISTINCT
    s.student_id,
    s.student_name,
    s.student_name_eng,
    sec.name as section_name,
    b.value as batch_name,
    p.name as program_name,
    c.name as college_name
FROM students s
LEFT JOIN sections sec ON sec.id = s.section_id
LEFT JOIN batches b ON b.id = s.batch_id
LEFT JOIN programs p ON p.id = s.program_id
LEFT JOIN colleges c ON c.id = s.college_id
LEFT JOIN registrations r ON r.student_id = s.student_id
$where
ORDER BY s.student_name 
LIMIT 1000";

$st = $mysqli1->prepare($sql);
if ($types) {
    $st->bind_param($types, ...$params);
}
$st->execute();
$students = $st->get_result()->fetch_all(MYSQLI_ASSOC);
$st->close();

// Get batches that have students (for filter dropdown)
$batches_with_students_sql = "SELECT DISTINCT b.id, b.value 
                             FROM batches b 
                             INNER JOIN students s ON s.batch_id = b.id 
                             WHERE b.deleted_at IS NULL 
                             ORDER BY b.id DESC";
$batches_with_students = $mysqli1->query($batches_with_students_sql)->fetch_all(MYSQLI_ASSOC);

include __DIR__ . '/../header.php';
?>

<div class="container py-4">
    <div class="d-flex align-items-center mb-4">
        <a href="../committee_dashboard.php" class="btn btn-outline-secondary btn-sm me-3">
            <i class="fas fa-arrow-right me-1"></i>
            العودة للوحة التحكم
        </a>
        <h4 class="mb-0 fw-bold">قوائم الطلاب</h4>
        <div class="ms-auto">
            <span class="chip blue">العدد: <?= number_format(count($students)) ?></span>
        </div>
    </div>

    <!-- Filters -->
    <div class="card mb-4">
        <div class="card-body">
            <form method="get" class="row g-3 align-items-end">
                <div class="col-md-3">
                    <label class="form-label fw-semibold">الكلية:</label>
                    <select name="college_id" class="form-select">
                        <option value="">جميع الكليات</option>
                        <?php
                        $colleges = $mysqli1->query("SELECT id, name FROM colleges WHERE deleted_at IS NULL ORDER BY name");
                        while ($college = $colleges->fetch_assoc()) {
                            $selected = $college_id == $college['id'] ? 'selected' : '';
                            echo "<option value=\"{$college['id']}\" $selected>" . e($college['name']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label fw-semibold">القسم:</label>
                    <select name="section_id" class="form-select">
                        <option value="">جميع الأقسام</option>
                        <?php
                        $sections = $mysqli1->query("SELECT id, name FROM sections WHERE deleted_at IS NULL ORDER BY name");
                        while ($section = $sections->fetch_assoc()) {
                            $selected = $section_id == $section['id'] ? 'selected' : '';
                            echo "<option value=\"{$section['id']}\" $selected>" . e($section['name']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label fw-semibold">الدفعة:</label>
                    <select name="batch_id" class="form-select">
                        <option value="">جميع الدفعات</option>
                        <?php
                        // Use batches that have students instead of all batches
                        foreach ($batches_with_students as $batch) {
                            $selected = $batch_id == $batch['id'] ? 'selected' : '';
                            echo "<option value=\"{$batch['id']}\" $selected>" . e($batch['value']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label fw-semibold">البرنامج:</label>
                    <select name="program_id" class="form-select">
                        <option value="">جميع البرامج</option>
                        <?php
                        $programs = $mysqli1->query("SELECT id, name FROM programs WHERE deleted_at IS NULL ORDER BY name");
                        while ($program = $programs->fetch_assoc()) {
                            $selected = $program_id == $program['id'] ? 'selected' : '';
                            echo "<option value=\"{$program['id']}\" $selected>" . e($program['name']) . "</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label fw-semibold">الفصل الدراسي:</label>
                    <select name="semester" class="form-select">
                        <option value="">جميع الفصول</option>
                        <?php
                        $semesters = $mysqli1->query("SELECT id, name, code FROM semesters WHERE deleted_at IS NULL ORDER BY created_at DESC");
                        while ($semester = $semesters->fetch_assoc()) {
                            $selected = $current_semester == $semester['id'] ? 'selected' : '';
                            echo "<option value=\"{$semester['id']}\" $selected>" . e($semester['name'] . ' (' . $semester['code'] . ')') . "</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-12">
                    <button type="submit" class="btn btn-primary me-2">
                        <i class="fas fa-filter me-1"></i>
                        تطبيق الفلتر
                    </button>
                    <a href="student_lists.php" class="btn btn-outline-secondary">
                        <i class="fas fa-sync-alt me-1"></i>
                        إعادة تعيين
                    </a>
                </div>
            </form>
        </div>
    </div>

    <!-- Student Lists -->
    <div class="card">
        <div class="card-header bg-primary text-white">
            <div class="d-flex justify-content-between align-items-center">
                <h5 class="mb-0">
                    <i class="fas fa-users me-2"></i>
                    قوائم الطلاب
                </h5>
                <div class="d-print-none">
                    <button class="btn btn-light btn-sm me-2" onclick="exportToExcel()">
                        <i class="fas fa-file-excel me-1 text-success"></i>
                        تصدير لإكسل
                    </button>
                    <button class="btn btn-light btn-sm" onclick="printReport()">
                        <i class="fas fa-print me-1 text-primary"></i>
                        طباعة التقرير
                    </button>
                </div>
            </div>
        </div>
        <div class="card-body">
            <!-- Normal View Table -->
            <div class="table-responsive d-print-none">
                <table class="table table-bordered table-hover">
                    <thead class="table-light">
                        <tr>
                            <th width="60" class="text-center">#</th>
                            <th width="150" class="text-center">الرقم الجامعي</th>
                            <th class="text-center">اسم الطالب</th>
                            <th class="text-center">القسم</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($students)): ?>
                            <tr>
                                <td colspan="4" class="text-center text-muted py-4">
                                    <i class="fas fa-info-circle me-2"></i>
                                    لا توجد بيانات للعرض
                                </td>
                            </tr>
                        <?php else: ?>
                            <?php foreach ($students as $index => $student): ?>
                                <tr>
                                    <td class="fw-bold text-center"><?= $index + 1 ?></td>
                                    <td class="text-center" style="font-family: monospace; font-size: 14px; white-space: nowrap;">
                                        <?= e($student['student_id']) ?>
                                    </td>
                                    <td class="text-right student-name-cell">
                                        <?= e($student['student_name']) ?>
                                    </td>
                                    <td class="text-center section-cell" style="white-space: nowrap;">
                                        <?= e($student['section_name'] ?? '-') ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>

            <!-- Print View Table (Hidden on screen, visible only when printing) -->
            <div class="table-responsive d-none d-print-block">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th width="60" class="text-center">#</th>
                            <th width="120" class="text-center">الرقم الجامعي</th>
                            <th width="200" class="text-center">اسم الطالب</th>
                            <th class="text-center">القسم</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($students)): ?>
                            <tr>
                                <td colspan="4" class="text-center text-muted py-4">
                                    لا توجد بيانات للعرض
                                </td>
                            </tr>
                        <?php else: ?>
                            <?php foreach ($students as $index => $student): ?>
                                <tr>
                                    <td class="fw-bold text-center"><?= $index + 1 ?></td>
                                    <td class="text-center" style="font-family: monospace; font-size: 12px; white-space: nowrap;">
                                        <?= e($student['student_id']) ?>
                                    </td>
                                    <td class="text-right student-name-cell" style="font-size: 12px;">
                                        <?= e($student['student_name']) ?>
                                    </td>
                                    <td class="text-center section-cell" style="font-size: 12px; white-space: nowrap;">
                                        <?= e($student['section_name'] ?? '-') ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>

            <!-- Summary -->
            <div class="mt-4 border-top pt-3 d-print-none">
                <div class="row">
                    <div class="col-md-4">
                        <div class="dash-card text-center">
                            <div class="n text-primary"><?= number_format(count($students)) ?></div>
                            <div>إجمالي الطلاب</div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="dash-card text-center">
                            <div class="n text-success"><?= count(array_unique(array_column($students, 'section_name'))) ?></div>
                            <div>عدد الأقسام</div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="dash-card text-center">
                            <div class="n text-info"><?= count(array_unique(array_filter(array_column($students, 'batch_name')))) ?></div>
                            <div>عدد الدفعات</div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<style>
.student-name-cell {
    text-align: right !important;
    direction: rtl !important;
    white-space: nowrap;
    overflow: hidden;
    text-overflow: ellipsis;
    padding-right: 15px !important;
}

.section-cell {
    min-width: 120px;
    white-space: nowrap !important;
}

/* Auto-fit column widths based on content */
.table-responsive table {
    width: auto;
    min-width: 100%;
}

.table-responsive th:nth-child(1),
.table-responsive td:nth-child(1) {
    width: 60px;
}

.table-responsive th:nth-child(2),
.table-responsive td:nth-child(2) {
    width: 150px;
}

.table-responsive th:nth-child(3),
.table-responsive td:nth-child(3) {
    min-width: 200px;
}

.table-responsive th:nth-child(4),
.table-responsive td:nth-child(4) {
    min-width: 120px;
    width: auto;
}
</style>

<script>
// Export to Excel function
function exportToExcel() {
    // Create a temporary table for export
    const originalTable = document.querySelector('.table-responsive.d-print-none table');
    const tempTable = originalTable.cloneNode(true);
    
    // Add export info as caption
    const caption = tempTable.createCaption();
    caption.innerHTML = `قوائم الطلاب | تاريخ التصدير: ${new Date().toLocaleDateString('ar-EG')}`;
    caption.style.captionSide = 'top';
    caption.style.textAlign = 'center';
    caption.style.fontWeight = 'bold';
    caption.style.marginBottom = '10px';
    
    const html = tempTable.outerHTML;
    const url = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(html);
    const link = document.createElement('a');
    link.href = url;
    link.download = `قوائم_الطلاب_${new Date().toLocaleDateString('ar-EG')}.xls`;
    link.click();
}

// Print function
function printReport() {
    // Create a new window for printing
    const printWindow = window.open('', '_blank');
    
    // Get the print table HTML
    const printTable = document.querySelector('.d-print-block table').outerHTML;
    
    // Create the print content
    const printContent = `
        <!DOCTYPE html>
        <html dir="rtl">
        <head>
            <meta charset="UTF-8">
            <title>قوائم الطلاب</title>
            <style>
                @import url('https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700&display=swap');
                
                body {
                    font-family: 'Cairo', sans-serif;
                    margin: 10px;
                    direction: rtl;
                }
                .header-table {
                    width: 70%;
                    margin: 0 auto 10px auto;
                    border: none;
                }
                .banner-img {
                    width: 100%;
                    height: auto;
                    display: block;
                }
                .report-title {
                    text-align: center;
                    font-family: 'Cairo', sans-serif;
                    font-size: 22px;
                    font-weight: 700;
                    margin: 10px 0;
                    color: #333;
                }
                .print-date {
                    text-align: center;
                    font-family: 'Cairo', sans-serif;
                    font-size: 14px;
                    color: #666;
                    margin-bottom: 15px;
                }
                table {
                    width: 100%;
                    border-collapse: collapse;
                    font-size: 12px;
                    font-family: 'Cairo', sans-serif;
                }
                th, td {
                    border: 1px solid #000;
                    padding: 6px 4px;
                    text-align: center;
                }
                th {
                    background-color: #f5f5f5;
                    font-weight: bold;
                }
                .student-id {
                    font-family: monospace;
                    font-size: 11px;
                    white-space: nowrap;
                }
                .student-name-cell {
                    text-align: right !important;
                    direction: rtl !important;
                    white-space: nowrap;
                    overflow: hidden;
                    text-overflow: ellipsis;
                    padding-right: 10px !important;
                }
                .section-cell {
                    white-space: nowrap !important;
                    min-width: 100px;
                }
                .no-data {
                    text-align: center;
                    padding: 20px;
                    color: #666;
                    font-family: 'Cairo', sans-serif;
                }
                @media print {
                    body { margin: 10mm; }
                    table { width: 100%; }
                }
            </style>
        </head>
        <body>
            <!-- Header Banner -->
            <table class="header-table">
                <tr>
                    <td style="text-align: center; border: none; padding: 0;">
                        <img src="../img/banar3.jpg" alt="Banner" class="banner-img" onerror="this.style.display='none'">
                    </td>
                </tr>
            </table>

            <!-- Report Title -->
            <div class="report-title">قوائم الطلاب</div>
            
            <!-- Print Date -->
            <div class="print-date">تاريخ الطباعة: ${new Date().toLocaleDateString('ar-EG')}</div>

            <!-- Student Data Table -->
            ${printTable}
        </body>
        </html>
    `;
    
    printWindow.document.write(printContent);
    printWindow.document.close();
    
    // Wait for content to load then print
    printWindow.onload = function() {
        printWindow.print();
    };
}

// Auto-adjust table column widths
document.addEventListener('DOMContentLoaded', function() {
    const table = document.querySelector('.table-responsive table');
    if (table) {
        // Let the browser auto-adjust column widths based on content
        table.style.tableLayout = 'auto';
        
        // Force reflow to ensure proper rendering
        setTimeout(() => {
            table.style.width = '100%';
        }, 100);
    }
});
</script>

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