<?php
// transcript.php - Fixed version with proper table layout
error_reporting(E_ALL);
ini_set('display_errors', 1);
require 'db.php';
require 'helpers.php';
require_committee();

// Start session
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// CSRF token
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

// Validation functions
function validateInt($input, $default = 0) {
    if (isset($input) && ctype_digit((string)$input)) {
        return (int)$input;
    }
    return $default;
}

function validateString($input, $default = '') {
    if (isset($input) && is_string($input)) {
        $clean = trim($input);
        $clean = strip_tags($clean);
        $clean = htmlspecialchars($clean, ENT_QUOTES, 'UTF-8');
        return $clean;
    }
    return $default;
}

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

// Get filter data
$colleges = [];
$result = $mysqli1->query("SELECT id, name FROM colleges WHERE deleted_at IS NULL ORDER BY name");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $colleges[] = [
            'id' => (int)$row['id'],
            'name' => validateString($row['name'])
        ];
    }
}

$batches = [];
$result = $mysqli1->query("SELECT id, value FROM batches WHERE deleted_at IS NULL ORDER BY id DESC");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $batches[] = [
            'id' => (int)$row['id'],
            'value' => validateString($row['value'])
        ];
    }
}

$semesters = [];
$result = $mysqli1->query("SELECT id, name FROM semesters WHERE deleted_at IS NULL ORDER BY id");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $semesters[] = [
            'id' => (int)$row['id'],
            'name' => validateString($row['name'])
        ];
    }
}

// Get sections for filter
$sections = [];
$result = $mysqli1->query("SELECT id, name FROM sections WHERE deleted_at IS NULL ORDER BY name");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $sections[] = [
            'id' => (int)$row['id'],
            'name' => validateString($row['name'])
        ];
    }
}

// Get programs for filter
$programs = [];
$result = $mysqli1->query("SELECT id, name FROM programs WHERE deleted_at IS NULL ORDER BY name");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $programs[] = [
            'id' => (int)$row['id'],
            'name' => validateString($row['name'])
        ];
    }
}

// Filter colleges for committee access
if (!$canAll && $scopeCollege !== null) {
    $colleges = array_filter($colleges, function($college) use ($scopeCollege) {
        return $college['id'] == $scopeCollege;
    });
}

// Get filter parameters
$college_id = validateInt($_GET['college_id'] ?? 0);
$section_id = validateInt($_GET['section_id'] ?? 0);
$program_id = validateInt($_GET['program_id'] ?? 0);
$batch_id = validateInt($_GET['batch_id'] ?? 0);
$semester_id = validateInt($_GET['semester_id'] ?? 0);
$student_id = validateString($_GET['student_id'] ?? '');
$transcript_type = validateString($_GET['transcript_type'] ?? 'batch');
$is_final = isset($_GET['is_final']) ? (bool)$_GET['is_final'] : false;

// Get names
$college_name = '';
$college_result = $mysqli1->query("SELECT name FROM colleges WHERE id = $college_id");
if ($college_result && $row = $college_result->fetch_assoc()) {
    $college_name = validateString($row['name']);
}

$section_name = '';
$section_result = $mysqli1->query("SELECT name FROM sections WHERE id = $section_id");
if ($section_result && $row = $section_result->fetch_assoc()) {
    $section_name = validateString($row['name']);
}

$program_name = '';
$program_result = $mysqli1->query("SELECT name FROM programs WHERE id = $program_id");
if ($program_result && $row = $program_result->fetch_assoc()) {
    $program_name = validateString($row['name']);
}

$batch_value = '';
$batch_result = $mysqli1->query("SELECT value FROM batches WHERE id = $batch_id");
if ($batch_result && $row = $batch_result->fetch_assoc()) {
    $batch_value = validateString($row['value']);
}

$semester_name = '';
$semester_result = $mysqli1->query("SELECT name FROM semesters WHERE id = $semester_id");
if ($semester_result && $row = $semester_result->fetch_assoc()) {
    $semester_name = validateString($row['name']);
}

// Helper functions
function getCumulativePoints($db, $student_id, $up_to_semester) {
    $query = "SELECT SUM(sub.credit_hours * ss.weight) as total_points
              FROM studentsubjects ss
              JOIN subjects sub ON ss.subjectcode = sub.subjectcode
              WHERE ltrim(ss.student_id) = '" . $db->real_escape_string($student_id) . "'
              AND ss.semester_id <= " . (int)$up_to_semester
              . " AND ss.grade IS NOT NULL AND ss.grade != ''";
    $result = $db->query($query);
    if ($result && $row = $result->fetch_assoc()) {
        return (float)$row['total_points'];
    }
    return 0;
}

function getCumulativeHours($db, $student_id, $up_to_semester) {
    $query = "SELECT SUM(sub.credit_hours) as total_hours
              FROM studentsubjects ss
              JOIN subjects sub ON ss.subjectcode = sub.subjectcode
              WHERE ltrim(ss.student_id) = '" . $db->real_escape_string($student_id) . "'
              AND ss.semester_id <= " . (int)$up_to_semester
              . " AND ss.grade IS NOT NULL AND ss.grade != ''";
    $result = $db->query($query);
    if ($result && $row = $result->fetch_assoc()) {
        return (float)$row['total_hours'];
    }
    return 0;
}

function getFailedCountInSemester($db, $student_id, $semester) {
    $query = "SELECT COUNT(ss.id) as fail_count
              FROM studentsubjects ss
              WHERE ltrim(ss.student_id) = '" . $db->real_escape_string($student_id) . "'
              AND ss.semester_id = " . (int)$semester
              . " AND ss.grade IN ('F', 'F*', 'F**', 'F***', 'FF', 'FF*')";
    $result = $db->query($query);
    if ($result && $row = $result->fetch_assoc()) {
        return (int)$row['fail_count'];
    }
    return 0;
}

function getTotalFailedCountAllSemesters($db, $student_id) {
    $query = "SELECT COUNT(ss.id) as total_fail_count
              FROM studentsubjects ss
              WHERE ltrim(ss.student_id) = '" . $db->real_escape_string($student_id) . "'
              AND ss.grade IN ('F', 'F*', 'F**', 'F***', 'FF', 'FF*')";
    $result = $db->query($query);
    if ($result && $row = $result->fetch_assoc()) {
        return (int)$row['total_fail_count'];
    }
    return 0;
}

// Function to check if student has any grades
function hasAnyGrade($student) {
    foreach ($student['grades'] as $grade) {
        if (!empty($grade['grade']) && $grade['grade'] != '') {
            return true;
        }
    }
    return false;
}

// Get transcript data
$subject_list = [];
$subject_stats = [];
$students_data = [];
$total_students = 0;

if ($college_id && $batch_id && $semester_id) {
    
    $whereConditions = "s.college_id = ? AND ss.batch_id = ? AND ss.semester_id = ? AND s.deleted_at IS NULL AND sub.deleted_at IS NULL";
    $params = [$college_id, $batch_id, $semester_id];
    $types = "iii";
    
    if ($section_id > 0) {
        $whereConditions .= " AND s.section_id = ?";
        $params[] = $section_id;
        $types .= "i";
    }
    
    if ($program_id > 0) {
        $whereConditions .= " AND s.program_id = ?";
        $params[] = $program_id;
        $types .= "i";
    }
    
    // TABLE 1: SUBJECTS LIST
    $subjectQuery = "
        SELECT DISTINCT 
            ss.subjectcode,
            sub.subjectnamea,
            sub.credit_hours
        FROM studentsubjects ss
        JOIN subjects sub ON ss.subjectcode = sub.subjectcode
        JOIN students s ON ss.student_id = s.student_id
        WHERE $whereConditions
        ORDER BY ss.subjectcode
    ";
    
    $stmt = $mysqli1->prepare($subjectQuery);
    if ($stmt) {
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        $counter = 1;
        while ($row = $result->fetch_assoc()) {
            $subject_list[] = [
                'no' => $counter++,
                'code' => validateString($row['subjectcode']),
                'name_ar' => validateString($row['subjectnamea']),
                'credit_hours' => (float)$row['credit_hours']
            ];
        }
        $stmt->close();
    }
    
    // TABLE 2: STATISTICS
    $statsQuery = "
        SELECT 
            sub.subjectcode,
            sub.subjectnamea,
            sub.credit_hours,
            SUM(CASE WHEN ss.grade = 'A' THEN 1 ELSE 0 END) as A_count,
            SUM(CASE WHEN ss.grade = 'B+' THEN 1 ELSE 0 END) as Bplus_count,
            SUM(CASE WHEN ss.grade = 'B' THEN 1 ELSE 0 END) as B_count,
            SUM(CASE WHEN ss.grade = 'C+' THEN 1 ELSE 0 END) as Cplus_count,
            SUM(CASE WHEN ss.grade = 'C' THEN 1 ELSE 0 END) as C_count,
            SUM(CASE WHEN ss.grade = 'D+' THEN 1 ELSE 0 END) as Dplus_count,
            SUM(CASE WHEN ss.grade = 'D' THEN 1 ELSE 0 END) as D_count,
            SUM(CASE WHEN ss.grade = 'F' OR ss.grade = 'F*' OR ss.grade = 'F**' OR ss.grade = 'F***' THEN 1 ELSE 0 END) as F_count,
            COUNT(DISTINCT ss.student_id) as total_students
        FROM studentsubjects ss
        JOIN subjects sub ON ss.subjectcode = sub.subjectcode
        JOIN students s ON ss.student_id = s.student_id
        WHERE $whereConditions
        GROUP BY sub.subjectcode, sub.subjectnamea, sub.credit_hours
        ORDER BY sub.subjectcode
    ";
    
    $stmt = $mysqli1->prepare($statsQuery);
    if ($stmt) {
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        $counter = 1;
        while ($row = $result->fetch_assoc()) {
            $subject_stats[] = [
                'no' => $counter++,
                'code' => validateString($row['subjectcode']),
                'name_ar' => validateString($row['subjectnamea']),
                'credit_hours' => (float)$row['credit_hours'],
                'A' => (int)$row['A_count'],
                'Bplus' => (int)$row['Bplus_count'],
                'B' => (int)$row['B_count'],
                'Cplus' => (int)$row['Cplus_count'],
                'C' => (int)$row['C_count'],
                'Dplus' => (int)$row['Dplus_count'],
                'D' => (int)$row['D_count'],
                'F' => (int)$row['F_count'],
                'total' => (int)$row['total_students']
            ];
        }
        $stmt->close();
    }
    
    // Get enrolled students for this batch/semester (including those with no subjects)
    $enrolledStudentsQuery = "
        SELECT DISTINCT s.student_id, s.student_name
        FROM students s
        WHERE s.college_id = ? AND s.batch_id = ? AND s.deleted_at IS NULL
    ";
    
    $enrolledParams = [$college_id, $batch_id];
    $enrolledTypes = "ii";
    
    if ($section_id > 0) {
        $enrolledStudentsQuery .= " AND s.section_id = ?";
        $enrolledParams[] = $section_id;
        $enrolledTypes .= "i";
    }
    
    if ($program_id > 0) {
        $enrolledStudentsQuery .= " AND s.program_id = ?";
        $enrolledParams[] = $program_id;
        $enrolledTypes .= "i";
    }
    
    $enrolledStudentsQuery .= " ORDER BY s.student_name";
    
    $all_students = [];
    $stmt = $mysqli1->prepare($enrolledStudentsQuery);
    if ($stmt) {
        $stmt->bind_param($enrolledTypes, ...$enrolledParams);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $all_students[$row['student_id']] = [
                'student_id' => validateString($row['student_id']),
                'student_name' => validateString($row['student_name']),
                'grades' => [],
                'semester_credits' => 0,
                'semester_points' => 0,
                'subjects_count' => 0
            ];
        }
        $stmt->close();
    }
    
    // TABLE 3: STUDENT GRADES
    $studentQuery = "
        SELECT 
            s.student_id,
            s.student_name,
            ss.subjectcode,
            ss.degree,
            ss.grade,
            ss.weight,
            sub.credit_hours
        FROM studentsubjects ss
        JOIN students s ON ss.student_id = s.student_id
        JOIN subjects sub ON ss.subjectcode = sub.subjectcode
        WHERE $whereConditions
    ";
    
    if ($student_id) {
        $studentQuery .= " AND s.student_id = ?";
    }
    
    $studentQuery .= " ORDER BY s.student_name, ss.subjectcode";
    
    $stmt = $mysqli1->prepare($studentQuery);
    if ($stmt) {
        $queryParams = $params;
        $queryTypes = $types;
        if ($student_id) {
            $queryParams[] = $student_id;
            $queryTypes .= "s";
        }
        $stmt->bind_param($queryTypes, ...$queryParams);
        $stmt->execute();
        $result = $stmt->get_result();
        
        while ($row = $result->fetch_assoc()) {
            $student_id_key = $row['student_id'];
            
            // Initialize student data if not already in all_students
            if (!isset($all_students[$student_id_key])) {
                $all_students[$student_id_key] = [
                    'student_id' => validateString($row['student_id']),
                    'student_name' => validateString($row['student_name']),
                    'grades' => [],
                    'semester_credits' => 0,
                    'semester_points' => 0,
                    'subjects_count' => 0
                ];
            }
            
            $all_students[$student_id_key]['grades'][$row['subjectcode']] = [
                'degree' => $row['degree'] !== null ? (float)$row['degree'] : null,
                'grade' => validateString($row['grade'] ?? ''),
                'weight' => $row['weight'] !== null ? (float)$row['weight'] : 0,
                'credit_hours' => (float)$row['credit_hours']
            ];
            
            if ($row['credit_hours'] > 0 && $row['weight'] !== null) {
                $all_students[$student_id_key]['semester_credits'] += $row['credit_hours'];
                $all_students[$student_id_key]['semester_points'] += $row['credit_hours'] * $row['weight'];
                $all_students[$student_id_key]['subjects_count']++;
            }
        }
        $stmt->close();
    }
    
    // Convert all_students to students_data and calculate academic data
    $students_data = [];
    $row_no = 1;
    foreach ($all_students as $student_data) {
        $student = $student_data;
        $student['no'] = $row_no++;
        $student['semester_gpa'] = $student['semester_credits'] > 0 ? $student['semester_points'] / $student['semester_credits'] : 0;
        
        $student['cumulative_points'] = getCumulativePoints($mysqli1, $student['student_id'], $semester_id);
        $student['cumulative_credits'] = getCumulativeHours($mysqli1, $student['student_id'], $semester_id);
        $student['cumulative_gpa'] = $student['cumulative_credits'] > 0 ? $student['cumulative_points'] / $student['cumulative_credits'] : 0;
        
        if ($semester_id > 1) {
            $prev_points = getCumulativePoints($mysqli1, $student['student_id'], $semester_id - 1);
            $prev_hours = getCumulativeHours($mysqli1, $student['student_id'], $semester_id - 1);
            $student['prev_cumulative_gpa'] = $prev_hours > 0 ? $prev_points / $prev_hours : 0;
        } else {
            $student['prev_cumulative_gpa'] = null;
        }
        
        if ($semester_id > 2) {
            $second_prev_points = getCumulativePoints($mysqli1, $student['student_id'], $semester_id - 2);
            $second_prev_hours = getCumulativeHours($mysqli1, $student['student_id'], $semester_id - 2);
            $student['second_prev_cumulative_gpa'] = $second_prev_hours > 0 ? $second_prev_points / $second_prev_hours : 0;
        } else {
            $student['second_prev_cumulative_gpa'] = null;
        }
        
        // Get current semester failed count
        $current_fail_count = getFailedCountInSemester($mysqli1, $student['student_id'], $semester_id);
        $total_fail_count = getTotalFailedCountAllSemesters($mysqli1, $student['student_id']);
        
        // Check if student has any grades at all
        $has_grades = hasAnyGrade($student);
        
        // Calculate status only if student has grades
        if ($has_grades) {
            if ($is_final) {
                // FINAL RESULT (Scientific Council) - Show grade based on CGPA
                $cgpa = $student['cumulative_gpa'];
                if ($cgpa >= 3.5) {
                    $student['status'] = 'ممتاز';
                } elseif ($cgpa >= 3.0) {
                    $student['status'] = 'جيد جداً';
                } elseif ($cgpa >= 2.5) {
                    $student['status'] = 'جيد';
                } elseif ($cgpa >= 2.0) {
                    $student['status'] = 'مقبول';
                } else {
                    $student['status'] = 'راسب';
                }
            } else {
                // NON-FINAL RESULT (Annual Result) - Show Pass or Fail with count
                $has_failing = false;
                $failing_count = 0;
                
                foreach ($student['grades'] as $grade) {
                    $grade_value = $grade['grade'] ?? '';
                    if (in_array($grade_value, ['F', 'F*', 'F**', 'F***', 'FF', 'FF*'])) {
                        $has_failing = true;
                        $failing_count++;
                    }
                }
                
                if (!$has_failing && $failing_count == 0) {
                    $student['status'] = 'نجاح';
                } else {
                    // Show total failed count (current + previous semesters)
                    if ($total_fail_count > 0) {
                        $student['status'] = "رسوب - {$total_fail_count}";
                    } else {
                        $student['status'] = "رسوب - {$failing_count}";
                    }
                }
            }
        } else {
            // No grades - status is empty
            $student['status'] = '';
        }
        
        $students_data[] = $student;
    }
    
    $total_students = count($students_data);
}

$display_data = $students_data;
if ($student_id && !empty($students_data)) {
    // Find the specific student
    $found = false;
    foreach ($students_data as $student) {
        if ($student['student_id'] == $student_id) {
            $display_data = [$student];
            $found = true;
            break;
        }
    }
    if (!$found) {
        $display_data = [];
    }
}

include 'header.php';
?>

<link rel="stylesheet" href="css/csstranscript.css">

<div class="transcript-wrapper">
    <div class="page-header d-flex justify-content-between align-items-center flex-wrap">
        <div>
            <h1><i class="fas fa-print ms-3"></i> كشف الدرجات - Transcript</h1>
            <p>طباعة كشوفات الدرجات للطلاب والدفعات</p>
        </div>
        <div class="mt-3 mt-md-0">
            <a href="admin_dashboard.php" class="btn-reset">
                <i class="fas fa-arrow-right ms-2"></i> العودة للوحة التحكم
            </a>
        </div>
    </div>

    <!-- Filter Section -->
    <div class="filter-card no-print">
        <form method="GET" action="" id="transcriptForm">
            <div class="row g-3">
                <div class="col-md-2">
                    <label class="form-label required-field">نوع الكشف</label>
                    <select class="form-select" name="transcript_type" id="transcript_type" required>
                        <option value="batch" <?= $transcript_type == 'batch' ? 'selected' : '' ?>>كشف دفعة كاملة</option>
                        <option value="student" <?= $transcript_type == 'student' ? 'selected' : '' ?>>كشف طالب فردي</option>
                    </select>
                </div>
                
                <div class="col-md-2">
                    <label class="form-label required-field">الكلية</label>
                    <select class="form-select" name="college_id" id="college_id" required>
                        <option value="">اختر الكلية</option>
                        <?php foreach ($colleges as $college): ?>
                            <option value="<?= $college['id'] ?>" <?= $college_id == $college['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($college['name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="col-md-2">
                    <label class="form-label">القسم</label>
                    <select class="form-select" name="section_id" id="section_id">
                        <option value="">جميع الأقسام</option>
                        <?php foreach ($sections as $section): ?>
                            <option value="<?= $section['id'] ?>" <?= $section_id == $section['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($section['name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="col-md-2">
                    <label class="form-label">البرنامج</label>
                    <select class="form-select" name="program_id" id="program_id">
                        <option value="">جميع البرامج</option>
                        <?php foreach ($programs as $program): ?>
                            <option value="<?= $program['id'] ?>" <?= $program_id == $program['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($program['name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="col-md-2" id="student_div" style="display: <?= $transcript_type == 'student' ? 'block' : 'none' ?>;">
                    <label class="form-label required-field">الطالب</label>
                    <select class="form-select" name="student_id" id="student_id">
                        <option value="">اختر الطالب</option>
                        <?php foreach ($students_data as $student): ?>
                            <option value="<?= htmlspecialchars($student['student_id']) ?>" 
                                <?= $student_id == $student['student_id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($student['student_name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="col-md-2">
                    <label class="form-label required-field">الدفعة</label>
                    <select class="form-select" name="batch_id" id="batch_id" required>
                        <option value="">اختر الدفعة</option>
                        <?php foreach ($batches as $batch): ?>
                            <option value="<?= $batch['id'] ?>" <?= $batch_id == $batch['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($batch['value']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="col-md-2">
                    <label class="form-label required-field">الفصل الدراسي</label>
                    <select class="form-select" name="semester_id" id="semester_id" required>
                        <option value="">اختر الفصل</option>
                        <?php foreach ($semesters as $semester): ?>
                            <option value="<?= $semester['id'] ?>" <?= $semester_id == $semester['id'] ? 'selected' : '' ?>>
                                الفصل <?= htmlspecialchars($semester['name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="col-md-12">
                    <div class="d-flex gap-3 justify-content-end">
                        <input type="hidden" name="is_final" id="is_final" value="<?= $is_final ? '1' : '0' ?>">
                        <button type="button" id="toggleFinalBtn" class="btn-toggle <?= $is_final ? 'final' : '' ?>">
                            <i class="fas fa-toggle-<?= $is_final ? 'on' : 'off' ?> ms-2"></i>
                            <?= $is_final ? 'المجلس العلمي' : 'نتائج سنوية' ?>
                        </button>
                        <a href="transcript.php" class="btn-reset">
                            <i class="fas fa-redo-alt ms-2"></i> إعادة تعيين
                        </a>
                        <button type="submit" class="btn-gradient">
                            <i class="fas fa-search ms-2"></i> عرض الكشف
                        </button>
                    </div>
                </div>
            </div>
        </form>
    </div>

    <!-- Transcript Display -->
    <?php if ($college_id && $batch_id && $semester_id && !empty($subject_list) && !empty($display_data)): ?>
        
        <div class="transcript-card" id="transcriptContent">
            
            <!-- UNIVERSITY HEADER - TWO ROWS -->
            <div class="university-header">
                <!-- First Row -->
                <div class="university-name">بسم الله الرحمن الرحيم</div>
                <div class="college-name"><?= htmlspecialchars($college_name) ?></div>
                <div class="dept-name">القسم: <?= htmlspecialchars($section_name ?: 'العام') ?></div>
                <div class="dept-name">البرنامج: <?= htmlspecialchars($program_name ?: 'البكالريوس') ?></div>
                <div class="dept-name">الدفعة: <?= htmlspecialchars($batch_value) ?></div>
                
                <!-- Second Row -->
                <div style="margin-top: 10px;"></div>
                <div class="dept-name">الفصل الدراسي: <?= htmlspecialchars($semester_name) ?></div>
                <div class="dept-name">عدد الطلاب: <?= $total_students ?></div>
                <div class="dept-name">نوع النتيجة: <?= $is_final ? 'المجلس العلمي للخريجين' : 'نتيجة نقل من عام إلى عام' ?></div>
            </div>
            
            <div class="filter-info">
                <span class="filter-tag"><i class="fas fa-university"></i> الكلية: <?= htmlspecialchars($college_name) ?></span>
                <?php if ($section_id): ?>
                <span class="filter-tag"><i class="fas fa-layer-group"></i> القسم: <?= htmlspecialchars($section_name) ?></span>
                <?php endif; ?>
                <?php if ($program_id): ?>
                <span class="filter-tag"><i class="fas fa-graduation-cap"></i> البرنامج: <?= htmlspecialchars($program_name) ?></span>
                <?php endif; ?>
                <span class="filter-tag"><i class="fas fa-users"></i> الدفعة: <?= htmlspecialchars($batch_value) ?></span>
                <span class="filter-tag"><i class="fas fa-calendar-alt"></i> الفصل: <?= htmlspecialchars($semester_name) ?></span>
                <span class="filter-tag"><i class="fas fa-print"></i> تاريخ الطباعة: <?= date('d/m/Y') ?></span>
            </div>
            
            <h4 style="text-align: center; margin: 15px 0; font-weight: bold;">كشف درجات الفصل</h4>
            
            <!-- TABLE 1: SUBJECTS LIST -->
            <div class="table-header">
                <i class="fas fa-book ms-2"></i> جدول (1) المقررات الدراسية
            </div>
            <div class="scroll-wrapper">
                <table class="report-table">
                    <thead>
                        <tr>
                            <th width="5%">م</th>
                            <th width="15%">رمز المادة</th>
                            <th width="55%">اسم المادة (عربي)</th>
                            <th width="10%">عدد الساعات</th>
                            <th width="15%">Subject Name (EN)</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($subject_list as $subject): ?>
                        <tr>
                            <td><?= $subject['no'] ?></td>
                            <td><?= htmlspecialchars($subject['code']) ?></td>
                            <td class="text-right"><?= htmlspecialchars($subject['name_ar']) ?></td>
                            <td><?= number_format($subject['credit_hours'], 1) ?></td>
                            <td><?= htmlspecialchars($subject['code']) ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
            
            <!-- TABLE 2: STATISTICS -->
            <div class="table-header">
                <i class="fas fa-chart-bar ms-2"></i> جدول (2) توزيع الدرجات
            </div>
            <div class="scroll-wrapper">
                <table class="report-table">
                    <thead>
                        <tr>
                            <th rowspan="2">م</th>
                            <th rowspan="2">رمز المادة</th>
                            <th rowspan="2">اسم المادة</th>
                            <th rowspan="2">س</th>
                            <th colspan="8">توزيع الدرجات</th>
                            <th rowspan="2">المجموع</th>
                        </tr>
                        <tr>
                            <th>A</th><th>B+</th><th>B</th><th>C+</th><th>C</th><th>D+</th><th>D</th><th>F</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($subject_stats as $stat): ?>
                        <tr>
                            <td><?= $stat['no'] ?></td>
                            <td><?= htmlspecialchars($stat['code']) ?></td>
                            <td class="text-right"><?= htmlspecialchars($stat['name_ar']) ?></td>
                            <td><?= number_format($stat['credit_hours'], 1) ?></td>
                            <td><?= $stat['A'] ?></td>
                            <td><?= $stat['Bplus'] ?></td>
                            <td><?= $stat['B'] ?></td>
                            <td><?= $stat['Cplus'] ?></td>
                            <td><?= $stat['C'] ?></td>
                            <td><?= $stat['Dplus'] ?></td>
                            <td><?= $stat['D'] ?></td>
                            <td><?= $stat['F'] ?></td>
                            <td><?= $stat['total'] ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
            
            <!-- TABLE 3: STUDENT GRADES -->
            <div class="table-header">
                <i class="fas fa-users ms-2"></i> جدول (3) درجات الطلاب
            </div>
            
            <?php if (!empty($display_data)): ?>
                <div class="scroll-wrapper">
                    <table class="report-table" style="font-size: 10px; min-width: 1200px;">
                        <thead>
                            <tr>
                                <th rowspan="2">م</th>
                                <th rowspan="2">رقم الطالب</th>
                                <th rowspan="2">اسم الطالب</th>
                                <?php foreach ($subject_list as $subject): ?>
                                    <th colspan="2"><?= htmlspecialchars($subject['code']) ?></th>
                                <?php endforeach; ?>
                                <th rowspan="2">ساعات<br>فصلية</th>
                                <th rowspan="2">نقاط<br>فصلية</th>
                                <th rowspan="2">معدل<br>فصلي</th>
                                <th rowspan="2">ساعات<br>تراكمية</th>
                                <th rowspan="2">نقاط<br>تراكمية</th>
                                <th rowspan="2">المعدل<br>التراكمي</th>
                                <?php if ($semester_id > 1): ?>
                                <th rowspan="2">المعدل<br>السابق</th>
                                <?php endif; ?>
                                <?php if ($semester_id > 2): ?>
                                <th rowspan="2">المعدل<br>الأسبق</th>
                                <?php endif; ?>
                                <th rowspan="2">الحالة/<br>التقدير</th>
                            </tr>
                            <tr>
                                <?php foreach ($subject_list as $subject): ?>
                                    <th>تقدير</th>
                                    <th>درجة</th>
                                <?php endforeach; ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($display_data as $student): ?>
                            <tr style="border-bottom: 1px solid #dee2e6;">
                                <td><?= $student['no'] ?></td>
                                <td><?= htmlspecialchars($student['student_id']) ?></td>
                                <td class="text-right"><?= htmlspecialchars($student['student_name']) ?></td>
                                
                                <?php foreach ($subject_list as $subject): ?>
                                    <?php $grade = $student['grades'][$subject['code']] ?? null; ?>
                                    <td class="grade-<?= str_replace('+', 'plus', $grade['grade'] ?? '') ?>">
                                        <?php 
                                        // Don't show anything if no grade
                                        $grade_display = $grade['grade'] ?? '';
                                        if ($grade_display == 'راسب' || ($grade_display == 'F' && empty($grade['degree']))) {
                                            $grade_display = '';
                                        }
                                        echo htmlspecialchars($grade_display ?: '--');
                                        ?>
                                    </td>
                                    <td><?= $grade && $grade['degree'] !== null ? number_format($grade['degree'], 0) : '--' ?></td>
                                <?php endforeach; ?>
                                
                                <td><?= number_format($student['semester_credits'], 1) ?></td>
                                <td><?= number_format($student['semester_points'], 2) ?></td>
                                <td><strong><?= number_format($student['semester_gpa'], 2) ?></strong></td>
                                <td><?= number_format($student['cumulative_credits'], 1) ?></td>
                                <td><?= number_format($student['cumulative_points'], 2) ?></td>
                                <td><strong><?= number_format($student['cumulative_gpa'], 2) ?></strong></td>
                                
                                <?php if ($semester_id > 1): ?>
                                <td><?= $student['prev_cumulative_gpa'] !== null ? number_format($student['prev_cumulative_gpa'], 2) : '--' ?></td>
                                <?php endif; ?>
                                
                                <?php if ($semester_id > 2): ?>
                                <td><?= $student['second_prev_cumulative_gpa'] !== null ? number_format($student['second_prev_cumulative_gpa'], 2) : '--' ?></td>
                                <?php endif; ?>
                                
                                                  
                                <td>
                                    <?php
                                    // Only show status if student has any grades
                                    $has_grades = false;
                                    foreach ($student['grades'] as $grade) {
                                        if (!empty($grade['grade']) && $grade['grade'] != '') {
                                            $has_grades = true;
                                            break;
                                        }
                                    }
                                    
                                    if ($has_grades && !empty($student['status'])) {
                                        if ($is_final) {
                                            // Scientific Council - Show grade based on CGPA
                                            if ($student['cumulative_gpa'] >= 3.5) {
                                                $status_class = 'status-excellent';
                                                $status_text = 'ممتاز';
                                            } elseif ($student['cumulative_gpa'] >= 3.0) {
                                                $status_class = 'status-verygood';
                                                $status_text = 'جيد جداً';
                                            } elseif ($student['cumulative_gpa'] >= 2.5) {
                                                $status_class = 'status-good';
                                                $status_text = 'جيد';
                                            } elseif ($student['cumulative_gpa'] >= 2.0) {
                                                $status_class = 'status-pass';
                                                $status_text = 'مقبول';
                                            } else {
                                                $status_class = 'status-fail';
                                                $status_text = 'راسب';
                                            }
                                        } else {
                                            // Annual Result - Show Pass or Fail with count
                                            if (strpos($student['status'], 'نجاح') !== false) {
                                                $status_class = 'status-pass';
                                                $status_text = 'نجاح';
                                            } elseif (strpos($student['status'], 'رسوب') !== false) {
                                                $status_class = 'status-fail';
                                                $status_text = $student['status'];
                                            } else {
                                                $status_class = '';
                                                $status_text = $student['status'];
                                            }
                                        }
                                        ?>
                                        <span class="status-badge <?php echo $status_class; ?>">
                                            <?php echo htmlspecialchars($status_text); ?>
                                        </span>
                                    <?php 
                                    } else { 
                                    ?>
                                        --
                                    <?php 
                                    } 
                                    ?>
                                 </div>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                
                <!-- Footer -->
                <div class="summary-footer">
                    <div>تم الطباعة بتاريخ: <?= date('d/m/Y h:i:s A') ?></div>
                    <div>عدد الطلاب: <?= count($display_data) ?></div>
                    <div>عدد المقررات: <?= count($subject_list) ?></div>
                    <div>الدفعة: <?= htmlspecialchars($batch_value) ?></div>
                    <div>الفصل الدراسي: <?= htmlspecialchars($semester_name) ?></div>
                    <div>نوع النتيجة: <?= $is_final ? 'المجلس العلمي للخريجين' : 'نتيجة نقل من عام إلى عام' ?></div>
                </div>
                
            <?php else: ?>
                <div class="summary-footer" style="text-align: center; background: #fff3cd;">
                    <strong>⚠️ عدد الطلاب كبير (<?= count($display_data) ?> طالب)</strong><br>
                    لعرض كشف مفصل، يرجى اختيار طالب محدد من الفلتر أعلاه.
                </div>
            <?php endif; ?>
            
        </div>
        
    <?php elseif ($college_id && $batch_id && $semester_id): ?>
        <div class="transcript-card text-center">
            <i class="fas fa-database fa-3x text-danger mb-3"></i>
            <h4>لا توجد بيانات</h4>
            <p class="text-muted">لم يتم العثور على مواد مسجلة أو طلاب في هذه الدفعة والفصل</p>
        </div>
        
    <?php else: ?>
        <div class="transcript-card text-center">
            <i class="fas fa-filter fa-3x text-muted mb-3 opacity-50"></i>
            <h4>اختر معايير البحث</h4>
            <p class="text-muted">الرجاء اختيار الكلية، الدفعة، والفصل الدراسي لعرض كشف الدرجات</p>
        </div>
    <?php endif; ?>
    
    <!-- Floating Action Buttons -->
    <?php if (!empty($display_data)): ?>
        <div class="action-buttons no-print">
            <button onclick="window.print()" class="btn-floating" title="طباعة">
                <i class="fas fa-print"></i>
            </button>
            <button onclick="downloadAsPDF()" class="btn-floating" title="تحميل PDF" style="background: linear-gradient(135deg, #f093fb, #f5576c);">
                <i class="fas fa-file-pdf"></i>
            </button>
        </div>
    <?php endif; ?>
</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/html2pdf.js/0.10.1/html2pdf.bundle.min.js"></script>

<script>
$(document).ready(function() {
    $('#transcript_type').on('change', function() {
        if ($(this).val() === 'student') {
            $('#student_div').show();
        } else {
            $('#student_div').hide();
            $('#student_id').val('');
        }
    });
    
    // Toggle final/non-final button
    $('#toggleFinalBtn').on('click', function() {
        var currentVal = $('#is_final').val();
        var newVal = currentVal == '1' ? '0' : '1';
        $('#is_final').val(newVal);
        
        // Update button appearance
        if (newVal == '1') {
            $(this).addClass('final');
            $(this).html('<i class="fas fa-toggle-on ms-2"></i> المجلس العلمي');
        } else {
            $(this).removeClass('final');
            $(this).html('<i class="fas fa-toggle-off ms-2"></i> نتائج سنوية');
        }
        
        // Submit the form
        $('#transcriptForm').submit();
    });
    
    let timeout;
    $('#college_id, #section_id, #program_id, #batch_id, #semester_id').on('change', function() {
        if ($('#college_id').val() && $('#batch_id').val() && $('#semester_id').val()) {
            clearTimeout(timeout);
            timeout = setTimeout(function() {
                $('#transcriptForm').submit();
            }, 500);
        }
    });
});

 function downloadAsPDF() {
    var element = document.getElementById('transcriptContent');
    
    // Get the table width
    var table = element.querySelector('.report-table');
    var tableWidth = table ? table.scrollWidth : 1200;
    
    // Calculate optimal PDF width (A4 landscape is 297mm)
    // 1 inch = 25.4mm, 1 inch = 96px at 96dpi
    var pageWidthMM = 297; // A4 landscape width in mm
    var pageHeightMM = 210; // A4 landscape height in mm
    
    // Content width in mm (assuming 96dpi)
    var contentWidthMM = (tableWidth / 96) * 25.4;
    
    // If content is wider than page, scale down
    var scale = contentWidthMM > pageWidthMM ? pageWidthMM / contentWidthMM : 1;
    
    var opt = {
        margin: [0.25, 0.25, 0.25, 0.25], // 0.25 inch margins
        filename: 'transcript_' + new Date().getTime() + '.pdf',
        image: { type: 'jpeg', quality: 0.95 },
        html2canvas: { 
            scale: Math.max(1, scale * 2),
            letterRendering: true,
            useCORS: true,
            logging: false,
            windowWidth: tableWidth,
            windowHeight: element.scrollHeight
        },
        jsPDF: { 
            unit: 'in', 
            format: 'a4', 
            orientation: 'landscape'
        }
    };
    
    var btn = event.target;
    var originalText = btn.innerHTML;
    btn.innerHTML = '<i class="fas fa-spinner fa-spin"></i> جاري التحميل...';
    btn.disabled = true;
    
    // Show loading message
    var loadingMsg = $('<div class="pdf-loading-message">جاري إنشاء ملف PDF...<br>قد يستغرق هذا بضع ثوانٍ</div>');
    $('body').append(loadingMsg);
    loadingMsg.fadeIn(200);
    
    html2pdf().set(opt).from(element).save().then(function() {
        btn.innerHTML = originalText;
        btn.disabled = false;
        loadingMsg.fadeOut(200, function() { $(this).remove(); });
    }).catch(function(err) {
        console.error('PDF Error:', err);
        btn.innerHTML = originalText;
        btn.disabled = false;
        loadingMsg.fadeOut(200, function() { $(this).remove(); });
        alert('حدث خطأ أثناء تحميل PDF. يرجى المحاولة مرة أخرى أو استخدام طباعة المتصفح (Ctrl+P).');
    });
}
    
 
</script>

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