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

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

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

require_committee();

$committeeId   = (int)($_SESSION['committee_id'] ?? 0);
$committeeName = (string)($_SESSION['committee_name'] ?? '');
$canAll        = (bool)($_SESSION['committee_can_all'] ?? false);
$scopeCollege  = isset($_SESSION['committee_college_id']) ? (int)$_SESSION['committee_college_id'] : null;

// 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;

// Build query conditions for partially registered students
// Students who have payments but registration is not marked as paid
$where = "WHERE r.id IS NOT NULL AND r.is_paid = 0";
$params = [];
$types = "";

if (!empty($current_semester)) {
    $where .= " AND r.semester_id = ?";
    $params[] = $current_semester;
    $types .= "i";
} else {
    // If no semester selected, get the latest semester
    $current_semester_query = $mysqli1->query("SELECT id FROM semesters WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT 1");
    if ($current_semester_query->num_rows > 0) {
        $current_semester = $current_semester_query->fetch_assoc()['id'];
        $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";
}

// First, let's check if we have a payments table and its structure
$payments_table_exists = $mysqli1->query("SHOW TABLES LIKE 'payments'")->num_rows > 0;

if ($payments_table_exists) {
    // Check payments table structure
    $payments_columns = $mysqli1->query("SHOW COLUMNS FROM payments");
    $payment_cols = [];
    while ($col = $payments_columns->fetch_assoc()) {
        $payment_cols[] = $col['Field'];
    }
    
    // Determine the correct column names for payments
    $amount_col = in_array('amount', $payment_cols) ? 'amount' : 
                 (in_array('payment_amount', $payment_cols) ? 'payment_amount' : 'NULL');
    $registration_col = in_array('registration_id', $payment_cols) ? 'registration_id' : 
                       (in_array('student_id', $payment_cols) ? 'student_id' : 'NULL');
    
    // Query for partially registered students with payments check
    $sql = "SELECT 
        s.student_id,
        s.student_name,
        s.student_name_eng,
        sec.name as section_name,
        b.value as batch_name,
        prog.name as program_name,
        c.name as college_name,
        sem.name as semester_name,
        r.payment_amount as total_paid,
        r.study_fees,
        r.registration_fees,
        r.insurance_fees,
        r.discount,
        r.penalty,
        r.stamp,
        r.payment_reference,
        r.is_paid,
        (SELECT COUNT(*) FROM payments p WHERE p.{$registration_col} = r.id) as payment_count,
        (SELECT COALESCE(SUM(p.{$amount_col}), 0) FROM payments p WHERE p.{$registration_col} = r.id) as total_payments,
        (r.study_fees + r.registration_fees + r.insurance_fees + COALESCE(r.penalty, 0) + COALESCE(r.stamp, 0) - COALESCE(r.discount, 0)) as total_fees,
        ((r.study_fees + r.registration_fees + r.insurance_fees + COALESCE(r.penalty, 0) + COALESCE(r.stamp, 0) - COALESCE(r.discount, 0)) - 
         (SELECT COALESCE(SUM(p.{$amount_col}), 0) FROM payments p WHERE p.{$registration_col} = r.id)) as remaining_amount
    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 prog ON prog.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
    LEFT JOIN semesters sem ON sem.id = r.semester_id
    $where
    HAVING total_payments > 0 AND remaining_amount > 0
    ORDER BY remaining_amount DESC, s.student_id DESC";
} else {
    // If no payments table, use registration data only
    $sql = "SELECT 
        s.student_id,
        s.student_name,
        s.student_name_eng,
        sec.name as section_name,
        b.value as batch_name,
        prog.name as program_name,
        c.name as college_name,
        sem.name as semester_name,
        r.payment_amount as total_paid,
        r.study_fees,
        r.registration_fees,
        r.insurance_fees,
        r.discount,
        r.penalty,
        r.stamp,
        r.payment_reference,
        r.is_paid,
        0 as payment_count,
        r.payment_amount as total_payments,
        (r.study_fees + r.registration_fees + r.insurance_fees + COALESCE(r.penalty, 0) + COALESCE(r.stamp, 0) - COALESCE(r.discount, 0)) as total_fees,
        ((r.study_fees + r.registration_fees + r.insurance_fees + COALESCE(r.penalty, 0) + COALESCE(r.stamp, 0) - COALESCE(r.discount, 0)) - r.payment_amount) as remaining_amount
    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 prog ON prog.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
    LEFT JOIN semesters sem ON sem.id = r.semester_id
    $where
    HAVING total_payments > 0 AND remaining_amount > 0
    ORDER BY remaining_amount DESC, s.student_id DESC";
}

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

// Include header with correct path
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 orange">العدد: <?= number_format(count($students)) ?></span>
        </div>
    </div>

    <!-- Filters -->
    <div class="card mb-4 d-print-none filters">
        <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
                        $batches = $mysqli1->query("SELECT id, value FROM batches WHERE deleted_at IS NULL ORDER BY id DESC");
                        while ($batch = $batches->fetch_assoc()) {
                            $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="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="partially_registered.php" class="btn btn-outline-secondary">
                        <i class="fas fa-sync-alt me-1"></i>
                        إعادة تعيين
                    </a>
                </div>
            </form>
        </div>
    </div>

    <!-- Report Data -->
    <div class="card main-report">
        <div class="card-header bg-warning text-dark">
            <h5 class="mb-0">
                <i class="fas fa-exclamation-circle me-2"></i>
                الطلاب المسجلين جزئياً
                <?php if (!empty($current_semester)): ?>
                    - <?= e($students[0]['semester_name'] ?? 'الفصل المحدد') ?>
                <?php endif; ?>
            </h5>
        </div>
        <div class="card-body">
            <!-- Normal View Table -->
            <div class="table-responsive d-print-none">
                <table class="table table-striped table-hover">
                    <thead class="table-light">
                        <tr>
                            <th>#</th>
                            <th>الرقم الجامعي</th>
                            <th>اسم الطالب</th>
                            <th>الكلية</th>
                            <th>القسم</th>
                            <th>البرنامج</th>
                            <th>الدفعة</th>
                            <th class="text-end">عدد السداديات</th>
                            <th class="text-end">المدفوع</th>
                            <th class="text-end">المتبقي</th>
                            <th class="text-end">الإجمالي</th>
                            <th>نسبة السداد</th>
                            <th>الإجراءات</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($students)): ?>
                            <tr>
                                <td colspan="13" 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): ?>
                                <?php
                                $completion_rate = $student['total_fees'] > 0 ? ($student['total_payments'] / $student['total_fees']) * 100 : 0;
                                ?>
                                <tr>
                                    <td class="fw-bold"><?= $index + 1 ?></td>
                                    <td><code><?= e($student['student_id']) ?></code></td>
                                    <td><?= e($student['student_name'] ?: $student['student_name_eng']) ?></td>
                                    <td><?= e($student['college_name']) ?></td>
                                    <td><?= e($student['section_name']) ?></td>
                                    <td><?= e($student['program_name']) ?></td>
                                    <td><?= e($student['batch_name']) ?></td>
                                    <td class="text-end"><?= number_format($student['payment_count']) ?></td>
                                    <td class="text-end fw-bold text-success">
                                        <?= number_format($student['total_payments'] ?? 0, 2) ?> ر.س
                                    </td>
                                    <td class="text-end fw-bold text-danger">
                                        <?= number_format($student['remaining_amount'] ?? 0, 2) ?> ر.س
                                    </td>
                                    <td class="text-end fw-bold text-primary">
                                        <?= number_format($student['total_fees'] ?? 0, 2) ?> ر.س
                                    </td>
                                    <td>
                                        <div class="progress" style="height: 20px; min-width: 100px;">
                                            <div class="progress-bar bg-warning" 
                                                 style="width: <?= $completion_rate ?>%"
                                                 title="<?= number_format($completion_rate, 1) ?>%">
                                                <?= number_format($completion_rate, 1) ?>%
                                            </div>
                                        </div>
                                    </td>
                                    <td>
                                        <a href="../committee_student_profile.php?student_id=<?= urlencode($student['student_id']) ?>" 
                                           class="btn btn-sm btn-outline-primary">
                                            <i class="fas fa-eye me-1"></i>
                                            عرض الملف
                                        </a>
                                    </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">
                <div class="text-center mb-3">
                    <h4>تقرير الطلاب المسجلين جزئياً</h4>
                    <?php if (!empty($current_semester)): ?>
                        <p class="mb-2">الفصل الدراسي: <?= e($students[0]['semester_name'] ?? 'الفصل المحدد') ?></p>
                    <?php endif; ?>
                    <p class="text-muted">تاريخ الطباعة: <?= date('Y-m-d H:i:s') ?></p>
                </div>
                
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th width="50">#</th>
                            <th width="120">الرقم الجامعي</th>
                            <th>اسم الطالب</th>
                            <th>الكلية</th>
                            <th>القسم</th>
                            <th width="100">الدفعة</th>
                            <th width="120">الفصل الدراسي</th>
                            <th width="100" class="text-end">المدفوع</th>
                            <th width="100" class="text-end">المتبقي</th>
                            <th width="100" class="text-end">الإجمالي</th>
                            <th width="80" class="text-center">نسبة السداد</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($students)): ?>
                            <tr>
                                <td colspan="11" class="text-center text-muted py-4">
                                    لا توجد بيانات للعرض
                                </td>
                            </tr>
                        <?php else: ?>
                            <?php foreach ($students as $index => $student): ?>
                                <?php
                                $completion_rate = $student['total_fees'] > 0 ? ($student['total_payments'] / $student['total_fees']) * 100 : 0;
                                ?>
                                <tr>
                                    <td class="fw-bold text-center"><?= $index + 1 ?></td>
                                    <td class="text-center"><?= e($student['student_id']) ?></td>
                                    <td><?= e($student['student_name'] ?: $student['student_name_eng']) ?></td>
                                    <td><?= e($student['college_name']) ?></td>
                                    <td><?= e($student['section_name']) ?></td>
                                    <td class="text-center"><?= e($student['batch_name']) ?></td>
                                    <td class="text-center"><?= e($student['semester_name']) ?></td>
                                    <td class="text-end"><?= number_format($student['total_payments'] ?? 0, 2) ?> ر.س</td>
                                    <td class="text-end text-danger"><?= number_format($student['remaining_amount'] ?? 0, 2) ?> ر.س</td>
                                    <td class="text-end"><?= number_format($student['total_fees'] ?? 0, 2) ?> ر.س</td>
                                    <td class="text-center fw-bold"><?= number_format($completion_rate, 1) ?>%</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-3">
                        <div class="dash-card text-center">
                            <div class="n text-warning"><?= number_format(count($students)) ?></div>
                            <div>إجمالي الطلاب المسجلين جزئياً</div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="dash-card text-center">
                            <div class="n text-primary">
                                <?php
                                $totalPayments = array_sum(array_column($students, 'payment_count'));
                                echo number_format($totalPayments);
                                ?>
                            </div>
                            <div>إجمالي السداديات</div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="dash-card text-center">
                            <div class="n text-success">
                                <?php
                                $totalPaid = array_sum(array_column($students, 'total_payments'));
                                echo number_format($totalPaid, 2);
                                ?> ر.س
                            </div>
                            <div>إجمالي المبالغ المدفوعة</div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="dash-card text-center">
                            <div class="n text-danger">
                                <?php
                                $totalRemaining = array_sum(array_column($students, 'remaining_amount'));
                                echo number_format($totalRemaining, 2);
                                ?> ر.س
                            </div>
                            <div>إجمالي المبالغ المتبقية</div>
                        </div>
                    </div>
                </div>
                <div class="row mt-3">
                    <div class="col-md-6 offset-md-3">
                        <div class="dash-card text-center">
                            <div class="n text-info">
                                <?php
                                $totalFees = array_sum(array_column($students, 'total_fees'));
                                $avgCompletion = $totalFees > 0 ? ($totalPaid / $totalFees) * 100 : 0;
                                echo number_format($avgCompletion, 1);
                                ?>%
                            </div>
                            <div>متوسط نسبة السداد</div>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Export Options -->
            <div class="mt-4 border-top pt-3 d-print-none">
                <div class="d-flex justify-content-between align-items-center">
                    <div class="text-muted">
                        <i class="fas fa-info-circle me-1"></i>
                        آخر تحديث: <?= date('Y-m-d H:i:s') ?>
                    </div>
                    <div>
                        <button class="btn btn-success btn-sm" onclick="exportToExcel()">
                            <i class="fas fa-file-excel me-1"></i>
                            تصدير لإكسل
                        </button>
                        <button class="btn btn-danger btn-sm" onclick="window.print()">
                            <i class="fas fa-print me-1"></i>
                            طباعة
                        </button>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<style>
@media print {
    .d-print-none,
    .btn,
    .card-header,
    .filters,
    .dash-card,
    .border-top,
    .text-muted,
    .chip,
    .d-flex.mb-4,
    .card:not(.main-report) {
        display: none !important;
    }
    
    .d-print-block {
        display: block !important;
    }
    
    .table-responsive {
        overflow: visible !important;
    }
    
    .card, 
    .card-body {
        border: none !important;
        box-shadow: none !important;
        padding: 0 !important;
        margin: 0 !important;
    }
    
    .container {
        max-width: 100% !important;
        padding: 0 !important;
        margin: 0 !important;
    }
    
    .table {
        font-size: 12px;
        width: 100% !important;
    }
    
    .table th {
        background-color: #f8f9fa !important;
        border: 1px solid #dee2e6 !important;
    }
    
    body {
        padding: 10px !important;
        margin: 0 !important;
    }
    
    table { 
        page-break-inside: auto;
    }
    tr { 
        page-break-inside: avoid; 
        page-break-after: auto;
    }
}

.orange {
    background-color: #ffa726;
    color: white;
    padding: 4px 12px;
    border-radius: 20px;
    font-size: 14px;
    font-weight: bold;
}

.progress {
    background-color: #e9ecef;
    border-radius: 4px;
    overflow: hidden;
}

.progress-bar {
    transition: width 0.6s ease;
    font-size: 12px;
    line-height: 20px;
}
</style>

<script>
function exportToExcel() {
    // Simple table export implementation
    const table = document.querySelector('table');
    const html = table.outerHTML;
    const url = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(html);
    const link = document.createElement('a');
    link.href = url;
    link.download = 'الطلاب_المسجلين_جزئياً.xls';
    link.click();
}
</script>

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