<?php
require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');
require_once('source/restrict_admin.php');

// Initialize message variable
$msg = '';
if (isset($_GET['msg'])) {
    $messages = [
        'refered' => "<div class='alert alert-success'>Referred successfully</div>",
        'failed' => "<div class='alert alert-warning'>Something went wrong, please try again</div>",
    ];
    $msg = $messages[$_GET['msg']] ?? '';
}

if (isset($_SESSION['msg']) && $_SESSION['msg'] == "update") {
    $msg = "<div class='alert alert-success text-center'>Order Update Successful. Customer will receive email. <a href='new_order.php' class='btn btn-sm btn-outline-success'>Refresh</a></div>";
    unset($_SESSION['msg']);
}

// Validate and sanitize input parameters
$registration_no = $_GET['myreg'] ?? '';
$myclass = trim($_GET['myclass'] ?? '');
$term = trim($_GET['term'] ?? '');
$examyear = trim($_GET['examyear'] ?? '');

if (empty($registration_no) || empty($myclass) || empty($term) || empty($examyear)) {
    die("<div class='alert alert-danger'>Required parameters are missing.</div>");
}

// Fetch student details
$student = sql($DBH, "SELECT * FROM tbl_user WHERE registration_no = ?", [$registration_no], "rows");
if (empty($student)) {
    die("<div class='alert alert-danger'>Student not found.</div>");
}
$student = $student[0];

$user_name = $student['fullname'];
$user_gender = $student['gender'];
$user_age = $student['age'];
$user_profile_pic = $student['profile_pic'] ?: 'default';
$profile_pic = $user_profile_pic == 'default' 
    ? "<img class='img-fluid rounded-circle profile-pic' width='100' src='../coll_admin/profile/profile.jpg' alt='Profile'>" 
    : "<img class='img-fluid rounded-circle profile-pic' width='100' src='../coll_admin/profile_pic/$user_profile_pic' alt='Profile'>";

// Calculate age - Fixing the mktime() error
$age = '';
if (!empty($user_age)) {
    // Handle different date formats (assuming format could be Y-m-d or m/d/Y)
    if (strpos($user_age, '/') !== false) {
        // For dates in m/d/Y format
        $birthDate = explode('/', $user_age);
        $month = (int)$birthDate[0];
        $day = (int)$birthDate[1];
        $year = (int)$birthDate[2];
    } elseif (strpos($user_age, '-') !== false) {
        // For dates in Y-m-d format
        $birthDate = explode('-', $user_age);
        $year = (int)$birthDate[0];
        $month = (int)$birthDate[1];
        $day = (int)$birthDate[2];
    } else {
        // Invalid date format
        $month = 1;
        $day = 1;
        $year = 2000;
    }

    $currentYear = date('Y');
    $currentMonth = date('m');
    $currentDay = date('d');

    $age = $currentYear - $year;
    
    // Adjust if birthday hasn't occurred yet this year
    if ($currentMonth < $month || ($currentMonth == $month && $currentDay < $day)) {
        $age--;
    }
} else {
    $age = 'N/A';
}

// Fetch result summary
error_log("Count Student Query Params: myclass='$myclass', term='$term', examyear='$examyear'");
try {
    $stmt = $DBH->prepare("SELECT COUNT(DISTINCT registration_no) AS student_count FROM tbl_student_results WHERE TRIM(class) = ? AND TRIM(term) = ? AND TRIM(year) = ?");
    $stmt->execute([$myclass, $term, $examyear]);
    $count_student = (int)$stmt->fetchColumn();
    error_log("Number in Class for $myclass, $term, $examyear: $count_student");
} catch (PDOException $e) {
    error_log("Error counting students: " . $e->getMessage());
    $count_student = 0;
}
if ($count_student === 0) {
    error_log("Warning: No students found for class='$myclass', term='$term', year='$examyear'");
}

// Fetch all results for the student
$results = sql($DBH, "SELECT * FROM tbl_student_results WHERE registration_no = ? AND class = ? AND term = ? AND year = ?", [$registration_no, $myclass, $term, $examyear], "rows");

// Calculate student average
if (empty($results)) {
    $average_score = 0;
    $grand_total_current = 0;
} else {
    $total_sum = 0;
    foreach ($results as $row) {
        $total = ($row['test1'] + $row['test2'] + $row['exam']);
        $total_sum += $total;
    }
    $grand_total_current = $total_sum;
    $average_score = round($total_sum / count($results), 2);
}

// Calculate class average
$class_results = sql($DBH, "SELECT * FROM tbl_student_results WHERE class = ? AND term = ? AND year = ?", [$myclass, $term, $examyear], "rows");
if (empty($class_results)) {
    $class_avg = 0;
    $min_score = 0;
    $max_score = 0;
} else {
    $class_total_sum = 0;
    $scores = [];
    foreach ($class_results as $row) {
        $total = ($row['test1'] + $row['test2'] + $row['exam']);
        $class_total_sum += $total;
        $scores[] = $total;
    }
    $class_avg = round($class_total_sum / count($class_results), 2);
    $min_score = min($scores);
    $max_score = max($scores);
}

// Grade and comments configuration
$grade_comments = [
    ['range' => [0, 49], 'grade' => 'F', 'remark' => 'Fail', 'comment' => 'Poor result, requires hard work and consistent effort to excel.'],
    ['range' => [50, 59], 'grade' => 'D', 'remark' => 'Weak Pass', 'comment' => 'Poor pass! Requires genuine interest and extra effort to make good grades.'],
    ['range' => [60, 64], 'grade' => 'C+', 'remark' => 'Pass', 'comment' => 'Pass, needs to study harder next term to achieve outstanding grades.'],
    ['range' => [65, 69], 'grade' => 'C+', 'remark' => 'Pass', 'comment' => 'Satisfactory result, ensure you work harder next term.'],
    ['range' => [70, 74], 'grade' => 'B', 'remark' => 'Credit', 'comment' => 'Good result, remain focused and work harder next term.'],
    ['range' => [75, 79], 'grade' => 'B+', 'remark' => 'Credit', 'comment' => 'Very good result. Do not relent, extra hard work needed next term.'],
    ['range' => [80, 89], 'grade' => 'A', 'remark' => 'Distinction', 'comment' => 'Fantastic performance! Stay on track to improve more.'],
    ['range' => [90, 100], 'grade' => 'A+', 'remark' => 'Distinction', 'comment' => 'Bravo! Keep soaring.']
];

// Determine grade and comments
$grade_info = null;
foreach ($grade_comments as $grade) {
    if ($average_score >= $grade['range'][0] && $average_score <= $grade['range'][1]) {
        $grade_info = $grade;
        break;
    }
}

$cgrade = $grade_info ? $grade_info['grade'] : 'N/A';
$cgradeR = $grade_info ? $grade_info['remark'] : 'N/A';
$htcomments = $grade_info ? $grade_info['comment'] : 'No comment available.';

// Fetch attendance
$attendance = sql($DBH, "SELECT * FROM attendance WHERE registration_no = ? AND class = ? AND term = ? AND year = ?", [$registration_no, $myclass, $term, $examyear], "rows")[0] ?? [];
$SchOpen = $attendance['SchOpen'] ?? 0;
$Present = $attendance['Present'] ?? 0;
$Absent = $SchOpen - $Present;
$DateOfReport = $attendance['DateOfReport'] ?? '';
$NextTermBegins = $attendance['NextTermBegins'] ?? '';
$TeachersRemarks = $attendance['TeachersRemarks'] ?? '';
$PrincipalsComment = $attendance['PrincipalsComment'] ?? '';

// Fetch skills
$skills = sql($DBH, "SELECT * FROM skills WHERE registration_no = ? AND class = ? AND term = ? AND year = ?", [$registration_no, $myclass, $term, $examyear], "rows")[0] ?? [];
$psychomotor = [
    'Handwriting' => $skills['Handwriting'] ?? '',
    'Fluency' => $skills['Fluency'] ?? '',
    'Games' => $skills['Games'] ?? '',
    'HandlingTools' => $skills['HandlingTools'] ?? '',
    'Drawing' => $skills['Drawing'] ?? '',
    'Creativity' => $skills['Creativity'] ?? '',
    'MusicalSkills' => $skills['MusicalSkills'] ?? ''
];
$affective = [
    'Punctuality' => $skills['Punctuality'] ?? '',
    'AttendanceToClass' => $skills['AttendanceToClass'] ?? '',
    'Reliability' => $skills['Reliability'] ?? '',
    'Neatness' => $skills['Neatness'] ?? '',
    'Politeness' => $skills['Politeness'] ?? '',
    'Honesty' => $skills['Honesty'] ?? '',
    'Obedience' => $skills['Obedience'] ?? '',
    'RelationshipWithOthers' => $skills['RelationshipWithOthers'] ?? '',
    'SelfControl' => $skills['SelfControl'] ?? '',
    'Attentiveness' => $skills['Attentiveness'] ?? '',
    'Initiative' => $skills['Initiative'] ?? '',
    'OrganizationalAbility' => $skills['OrganizationalAbility'] ?? '',
    'Perseverance' => $skills['Perseverance'] ?? ''
];

// Fetch cumulative results and positions
$terms = ['1st Term', '2nd Term', '3rd Term'];
$cumulative = [];
$position_data = []; // To store position information

foreach ($terms as $t) {
    // Get term totals and averages
    $result = sql($DBH, "SELECT SUM(test1 + test2 + exam) as total, COUNT(*) as count FROM tbl_student_results WHERE registration_no = ? AND class = ? AND term = ? AND year = ?", 
        [$registration_no, $myclass, $t, $examyear], "rows")[0];
    
    $cumulative[$t] = [
        'total' => $result['total'] ?? 0,
        'avg' => $result['count'] > 0 ? round($result['total'] / $result['count'], 2) : 'N/A'
    ];

    // Get position data for each term
    $pos_query = sql($DBH, "SELECT position FROM tbl_overall WHERE reg_no = ? AND class = ? AND term = ? AND year = ?",
        [$registration_no, $myclass, $t, $examyear], "rows");
    
    $position_data[$t] = !empty($pos_query) ? $pos_query[0]['position'] : 'N/A';
}

$grand_total = array_sum(array_column($cumulative, 'total'));
$valid_avgs = array_filter(array_column($cumulative, 'avg'), function($v) { return $v !== 'N/A'; });
$cummulative_avg = !empty($valid_avgs) ? round(array_sum($valid_avgs) / count($valid_avgs), 2) : 'N/A';

// Enhanced cumulative data for 3rd Term
$subject_cumulative = [];
$overall_position = 'N/A';

if ($term == '3rd Term') {
    // Get cumulative averages and positions per subject
    foreach ($results as $row) {
        $subject = $row['subject'];
        
        // Get all three terms' data for this subject
        $terms_data = sql($DBH, 
            "SELECT term, (test1 + test2 + exam) as total, position 
             FROM tbl_student_results 
             WHERE registration_no = ? AND class = ? AND year = ? AND subject = ? 
             ORDER BY term",
            [$registration_no, $myclass, $examyear, $subject], 
            "rows"
        );
        
        // Calculate cumulative average
        $sum = 0;
        $count = 0;
        $positions = [];
        foreach ($terms_data as $term_data) {
            $sum += $term_data['total'];
            $count++;
            if (isset($term_data['position'])) {
                $positions[] = $term_data['position'];
            }
        }
        
        $subject_cumulative[$subject] = [
            'avg' => $count > 0 ? round($sum / $count, 2) : 'N/A',
            'position' => !empty($positions) ? min($positions) : 'N/A' // Show best position
        ];
    }
    
    // Get overall cumulative position in class
    $overall_position_query = sql($DBH,
        "SELECT position FROM tbl_overall 
         WHERE reg_no = ? AND class = ? AND year = ? AND term = '3rd Term'",
        [$registration_no, $myclass, $examyear],
        "rows"
    );
    
    if (!empty($overall_position_query)) {
        $overall_position = $overall_position_query[0]['position'];
    }
}

// Function to format position badges
function formatPositionBadge($position, $full_text = false) {
    if (!is_numeric($position)) {
        return htmlspecialchars($position);
    }
    
    $position_text = $full_text ? 
        ($position == 1 ? '1st Position' : 
         ($position == 2 ? '2nd Position' : 
          ($position == 3 ? '3rd Position' : 
           ($position <= 10 ? 'Top 10 (#' . $position . ')' : '#' . $position)))) : 
        $position;
    
    $badge_class = '';
    if ($position == 1) {
        $badge_class = 'position-gold';
        $icon = '🥇';
    } elseif ($position == 2) {
        $badge_class = 'position-silver';
        $icon = '🥈';
    } elseif ($position == 3) {
        $badge_class = 'position-bronze';
        $icon = '🥉';
    } elseif ($position <= 10) {
        $badge_class = 'position-top10';
        $icon = '🏅';
    } else {
        $icon = '';
    }
    
    return "<span class='badge rounded-pill position-badge $badge_class'>$icon $position_text</span>";
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Verbum Dei International College - Student Result</title>
    <!-- Bootstrap 5 CDN -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Font Awesome CDN -->
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.2/css/all.min.css" rel="stylesheet">
    <!-- Google Fonts -->
    <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@300;400;600;700&family=Playfair+Display:wght@700&display=swap" rel="stylesheet">
    <style>
        :root {
            --primary-color: #0d47a1;
            --secondary-color: #1976d2;
            --accent-color: #bbdefb;
            --dark-color: #002171;
            --light-color: #e3f2fd;
            --success-color: #2e7d32;
            --warning-color: #ff8f00;
            --danger-color: #c62828;
        }
        
        body {
            font-family: 'Poppins', sans-serif;
            background-color: #f8f9fa;
            color: #333;
        }
        
        .header {
            background: linear-gradient(135deg, var(--primary-color) 0%, var(--secondary-color) 100%);
            color: white;
            padding: 1.5rem 0;
            margin-bottom: 2rem;
            box-shadow: 0 4px 12px rgba(0,0,0,0.1);
        }
        
        .school-name {
            font-family: 'Playfair Display', serif;
            font-weight: 700;
            font-size: 2rem;
            margin-bottom: 0.5rem;
        }
        
        .school-motto {
            font-style: italic;
            opacity: 0.9;
            font-size: 0.9rem;
        }
        
        .card {
            border: none;
            border-radius: 10px;
            box-shadow: 0 4px 12px rgba(0,0,0,0.05);
            margin-bottom: 1.5rem;
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        
        .card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 16px rgba(0,0,0,0.1);
        }
        
        .card-header {
            background: linear-gradient(135deg, var(--secondary-color) 0%, var(--primary-color) 100%);
            color: white;
            font-weight: 600;
            border-radius: 10px 10px 0 0 !important;
            padding: 1rem 1.5rem;
        }
        
        .profile-pic {
            border: 4px solid white;
            box-shadow: 0 4px 8px rgba(0,0,0,0.1);
        }
        
        .table {
            border-radius: 8px;
            overflow: hidden;
        }
        
        .table th {
            background-color: var(--light-color);
            color: var(--primary-color);
            font-weight: 600;
        }
        
        .badge-grade {
            font-size: 0.8rem;
            padding: 0.35rem 0.6rem;
            border-radius: 4px;
            font-weight: 600;
        }
        
        .grade-A { background-color: #2e7d32; color: white; }
        .grade-B { background-color: #689f38; color: white; }
        .grade-C { background-color: #ff8f00; color: white; }
        .grade-D { background-color: #ef6c00; color: white; }
        .grade-F { background-color: #c62828; color: white; }
        
        .btn-print {
            background-color: var(--primary-color);
            color: white;
            font-weight: 600;
            padding: 0.5rem 1.5rem;
            border-radius: 8px;
            transition: all 0.3s ease;
        }
        
        .btn-print:hover {
            background-color: var(--dark-color);
            color: white;
            transform: translateY(-2px);
        }
        
        .footer {
            background-color: var(--dark-color);
            color: white;
            padding: 1.5rem 0;
            margin-top: 2rem;
        }
        
        /* Position badge styling */
        .position-badge {
            font-size: 0.9rem;
            padding: 0.5rem 1rem;
            font-weight: 700;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            transition: all 0.3s ease;
        }
        
        .position-cell {
            text-align: center;
        }
        
        .position-gold {
            background: linear-gradient(135deg, #FFD700 0%, #D4AF37 100%);
            color: #000;
        }
        
        .position-silver {
            background: linear-gradient(135deg, #C0C0C0 0%, #A8A8A8 100%);
            color: #000;
        }
        
        .position-bronze {
            background: linear-gradient(135deg, #CD7F32 0%, #A67C52 100%);
            color: #fff;
        }
        
        .position-top10 {
            background: linear-gradient(135deg, #4CAF50 0%, #2E7D32 100%);
            color: #fff;
        }
        
        /* Hover effects for position badges */
        .position-badge:hover {
            transform: scale(1.05);
            box-shadow: 0 4px 8px rgba(0,0,0,0.2);
        }
        
        @media print {
            body {
                background-color: white;
                font-size: 10pt;
                padding: 0;
                margin: 0;
            }
            
            .no-print {
                display: none !important;
            }
            
            .header {
                background: var(--primary-color) !important;
                -webkit-print-color-adjust: exact;
                print-color-adjust: exact;
            }
            
            .card {
                box-shadow: none;
                border: 1px solid #ddd;
                page-break-inside: avoid;
            }
            
            .card-header {
                background: var(--primary-color) !important;
                -webkit-print-color-adjust: exact;
                print-color-adjust: exact;
            }
            
            .position-badge {
                -webkit-print-color-adjust: exact;
                print-color-adjust: exact;
            }
            
            @page {
                size: A4;
                margin: 1cm;
            }
        }
    </style>
</head>
<body>
    <!-- Header -->
    <div class="header text-center">
        <div class="container">
            <h1 class="school-name">Verbum Dei International College</h1>
            <p class="school-motto">"Achieving Excellence Together"</p>
            <h3 class="mb-0">Student Academic Report</h3>
        </div>
    </div>

    <!-- Main Content -->
    <div class="container">
        <?php if ($msg) echo $msg; ?>
        
        <!-- Student Info -->
        <div class="card">
            <div class="card-header">
                <i class="fas fa-user-graduate me-2"></i> Student Information
            </div>
            <div class="card-body">
                <div class="row align-items-center">
                    <div class="col-md-2 text-center">
                        <?php echo $profile_pic; ?>
                    </div>
                    <div class="col-md-10">
                        <div class="row">
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-user me-2"></i> Name:</strong> 
                                <?php echo htmlspecialchars($user_name); ?>
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-id-card me-2"></i> Reg No:</strong> 
                                <?php echo htmlspecialchars($registration_no); ?>
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-venus-mars me-2"></i> Gender:</strong> 
                                <?php echo htmlspecialchars($user_gender); ?>
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-child me-2"></i> Age:</strong> 
                                <?php echo $age; ?> Years
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-layer-group me-2"></i> Class:</strong> 
                                <?php echo htmlspecialchars($myclass); ?>
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-calendar-alt me-2"></i> Term:</strong> 
                                <?php echo htmlspecialchars($term); ?>
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-calendar me-2"></i> Session:</strong> 
                                <?php echo htmlspecialchars($examyear); ?>
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-users me-2"></i> Class Size:</strong> 
                                <?php echo $count_student; ?>
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-chart-line me-2"></i> Student Average:</strong> 
                                <?php echo $average_score; ?>%
                            </div>
                            <div class="col-md-4 mb-2">
                                <strong><i class="fas fa-chart-bar me-2"></i> Class Average:</strong> 
                                <?php echo $class_avg; ?>%
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Attendance -->
        <div class="card">
            <div class="card-header">
                <i class="fas fa-calendar-check me-2"></i> Attendance Record
            </div>
            <div class="card-body">
                <div class="row">
                    <div class="col-md-3 mb-2">
                        <strong><i class="fas fa-door-open me-2"></i> School Days:</strong> 
                        <?php echo $SchOpen; ?>
                    </div>
                    <div class="col-md-3 mb-2">
                        <strong><i class="fas fa-check-circle me-2"></i> Present:</strong> 
                        <?php echo $Present; ?>
                    </div>
                    <div class="col-md-3 mb-2">
                        <strong><i class="fas fa-times-circle me-2"></i> Absent:</strong> 
                        <?php echo $Absent; ?>
                    </div>
                    <div class="col-md-3 mb-2">
                        <strong><i class="fas fa-calendar-day me-2"></i> Term Ends:</strong> 
                        <?php echo htmlspecialchars($DateOfReport); ?>
                    </div>
                    <div class="col-md-3 mb-2">
                        <strong><i class="fas fa-calendar-plus me-2"></i> Next Term:</strong> 
                        <?php echo htmlspecialchars($NextTermBegins); ?>
                    </div>
                    <div class="col-md-3 mb-2">
                        <strong><i class="fas fa-percentage me-2"></i> Attendance Rate:</strong> 
                        <?php echo $SchOpen > 0 ? round(($Present/$SchOpen)*100, 2) : 0; ?>%
                    </div>
                </div>
            </div>
        </div>

        <!-- Performance Card -->
        <div class="card">
            <div class="card-header">
                <i class="fas fa-poll me-2"></i> Academic Performance - <?php echo strtoupper(htmlspecialchars($term)); ?>, <?php echo htmlspecialchars($examyear); ?>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th>Subject</th>
                                <th>1st CA<br>(20)</th>
                                <th>2nd CA<br>(20)</th>
                                <th>Exam<br>(60)</th>
                                <?php if ($term == '3rd Term'): ?>
                                    <th>3rd Term<br>Total (100)</th>
                                    <th>1st Term</th>
                                    <th>2nd Term</th>
                                    <th>Cum.<br>Avg</th>
                                    <th>Grade</th>
                                    <th>Remark</th>
                                <?php else: ?>
                                    <th>Total<br>(100)</th>
                                    <th>Class<br>Avg</th>
                                    <th>Position</th>
                                    <th>Grade</th>
                                    <th>Remark</th>
                                <?php endif; ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            foreach ($results as $row) {
                                $total = ($row['test1'] + $row['test2'] + $row['exam']);
                                $subject = $row['subject'];
                                
                                // Get class average for subject
                                $class_results_subj = sql($DBH, 
                                    "SELECT * FROM tbl_student_results 
                                     WHERE class = ? AND term = ? AND year = ? AND subject = ?", 
                                    [$myclass, $term, $examyear, $subject], 
                                    "rows"
                                );
                                
                                $class_avg_subj = 0;
                                if (!empty($class_results_subj)) {
                                    $class_total = 0;
                                    foreach ($class_results_subj as $class_row) {
                                        $class_total += ($class_row['test1'] + $class_row['test2'] + $class_row['exam']);
                                    }
                                    $class_avg_subj = round($class_total / count($class_results_subj), 2);
                                }
                                
                                // Get position for current term/subject
                                $subject_position = $row['position'] ?? 'N/A';
                                
                                // Get previous term results for 3rd Term
                                $second_term_result = '';
                                $first_term_result = '';
                                if ($term == '3rd Term') {
                                    // Get 2nd Term result
                                    $second_term = sql($DBH, 
                                        "SELECT (test1 + test2 + exam) as total 
                                         FROM tbl_student_results 
                                         WHERE registration_no = ? AND class = ? AND term = '2nd Term' AND year = ? AND subject = ?", 
                                        [$registration_no, $myclass, $examyear, $subject], 
                                        "rows"
                                    );
                                    $second_term_result = !empty($second_term) ? $second_term[0]['total'] : 'N/A';
                                    
                                    // Get 1st Term result
                                    $first_term = sql($DBH, 
                                        "SELECT (test1 + test2 + exam) as total 
                                         FROM tbl_student_results 
                                         WHERE registration_no = ? AND class = ? AND term = '1st Term' AND year = ? AND subject = ?", 
                                        [$registration_no, $myclass, $examyear, $subject], 
                                        "rows"
                                    );
                                    $first_term_result = !empty($first_term) ? $first_term[0]['total'] : 'N/A';
                                }
                                
                                // Find grade info for this subject
                                $grade_info_subj = null;
                                foreach ($grade_comments as $grade) {
                                    if ($total >= $grade['range'][0] && $total <= $grade['range'][1]) {
                                        $grade_info_subj = $grade;
                                        break;
                                    }
                                }
                                $subject_grade = $grade_info_subj ? $grade_info_subj['grade'] : 'N/A';
                                $subject_remark = $grade_info_subj ? $grade_info_subj['remark'] : 'N/A';
                                $grade_class = $subject_grade ? 'grade-' . substr($subject_grade, 0, 1) : '';

                                echo "<tr>
                                    <td>" . htmlspecialchars($subject) . "</td>
                                    <td>" . htmlspecialchars($row['test1']) . "</td>
                                    <td>" . htmlspecialchars($row['test2']) . "</td>
                                    <td>" . htmlspecialchars($row['exam']) . "</td>
                                    <td>" . htmlspecialchars($total) . "</td>";
                                    
                                if ($term == '3rd Term') {
                                    $cum_avg = $subject_cumulative[$subject]['avg'] ?? 'N/A';
                                    
                                    echo "<td>" . htmlspecialchars($first_term_result) . "</td>
                                          <td>" . htmlspecialchars($second_term_result) . "</td>
                                          <td>" . htmlspecialchars($cum_avg) . "</td>
                                          <td><span class='badge-grade $grade_class'>" . htmlspecialchars($subject_grade) . "</span></td>
                                          <td>" . htmlspecialchars($subject_remark) . "</td>";
                                } else {
                                    echo "<td>" . htmlspecialchars($class_avg_subj) . "</td>
                                          <td class='position-cell'>" . formatPositionBadge($subject_position) . "</td>
                                          <td><span class='badge-grade $grade_class'>" . htmlspecialchars($subject_grade) . "</span></td>
                                          <td>" . htmlspecialchars($subject_remark) . "</td>";
                                }
                                
                                echo "</tr>";
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
                
                <div class="row mt-3">
                    <div class="col-md-3">
                        <strong><i class="fas fa-calculator me-2"></i> Grand Total:</strong> 
                        <?php echo htmlspecialchars($grand_total_current); ?>
                    </div>
                    <div class="col-md-3">
                        <strong><i class="fas fa-percentage me-2"></i> Average Score:</strong> 
                        <?php echo htmlspecialchars($average_score); ?>%
                    </div>
                    <div class="col-md-3">
                        <strong><i class="fas fa-comment me-2"></i> Remarks:</strong> 
                        <?php echo htmlspecialchars($cgradeR); ?>
                    </div>
                    <div class="col-md-3">
                        <strong><i class="fas fa-star me-2"></i> Grade:</strong> 
                        <span class="badge-grade grade-<?php echo substr($cgrade, 0, 1); ?>">
                            <?php echo htmlspecialchars($cgrade); ?>
                        </span>
                    </div>
                </div>
                
                <?php if ($term == '3rd Term'): ?>
                <div class="row mt-3">
                    <div class="col-md-3">
                        <strong><i class="fas fa-calendar-alt me-2"></i> 1st Term Average:</strong> 
                        <?php echo htmlspecialchars($cumulative['1st Term']['avg']); ?>%
                        <?php if ($position_data['1st Term'] != 'N/A'): ?>
                            <span class="ms-2">(Pos: <?php echo formatPositionBadge($position_data['1st Term']); ?>)</span>
                        <?php endif; ?>
                    </div>
                    <div class="col-md-3">
                        <strong><i class="fas fa-calendar-alt me-2"></i> 2nd Term Average:</strong> 
                        <?php echo htmlspecialchars($cumulative['2nd Term']['avg']); ?>%
                        <?php if ($position_data['2nd Term'] != 'N/A'): ?>
                            <span class="ms-2">(Pos: <?php echo formatPositionBadge($position_data['2nd Term']); ?>)</span>
                        <?php endif; ?>
                    </div>
                    <div class="col-md-3">
                        <strong><i class="fas fa-calendar-alt me-2"></i> 3rd Term Average:</strong> 
                        <?php echo htmlspecialchars($cumulative['3rd Term']['avg']); ?>%
                        <?php if ($position_data['3rd Term'] != 'N/A'): ?>
                            <span class="ms-2">(Pos: <?php echo formatPositionBadge($position_data['3rd Term']); ?>)</span>
                        <?php endif; ?>
                    </div>
                    <div class="col-md-3">
                        <strong><i class="fas fa-chart-line me-2"></i> Cumulative Average:</strong> 
                        <?php echo htmlspecialchars($cummulative_avg); ?>%
                    </div>
                    <div class="col-md-6 mt-2">
                        <strong><i class="fas fa-sort-numeric-down me-2"></i> Cumulative Position:</strong> 
                        <?php if ($overall_position != 'N/A'): ?>
                            <?php echo formatPositionBadge($overall_position, true); ?>
                            <small class="text-muted">out of <?php echo $count_student; ?> students</small>
                        <?php else: ?>
                            <span class="text-muted">Position not available</span>
                        <?php endif; ?>
                    </div>
                    <div class="col-md-6 mt-2">
                        <strong><i class="fas fa-chart-bar me-2"></i> Annual Total:</strong> 
                        <?php echo htmlspecialchars($grand_total); ?>
                    </div>
                </div>
                <?php endif; ?>
            </div>
        </div>

        <!-- Skills -->
        <div class="card">
            <div class="card-header">
                <i class="fas fa-brain me-2"></i> Skills Assessment
            </div>
            <div class="card-body">
                <div class="row">
                    <div class="col-md-6">
                        <h5 class="text-center mb-3"><i class="fas fa-running me-2"></i> Psychomotor Skills</h5>
                        <table class="table table-bordered">
                            <?php foreach ($psychomotor as $skill => $value): ?>
                                <?php if ($value): ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($skill); ?></td>
                                    <td>
                                        <div class="progress">
                                            <div class="progress-bar" role="progressbar" style="width: <?php echo ($value/5)*100; ?>%" 
                                                 aria-valuenow="<?php echo $value; ?>" aria-valuemin="0" aria-valuemax="5">
                                                <?php echo $value; ?>
                                            </div>
                                        </div>
                                    </td>
                                </tr>
                                <?php endif; ?>
                            <?php endforeach; ?>
                        </table>
                    </div>
                    <div class="col-md-6">
                        <h5 class="text-center mb-3"><i class="fas fa-heart me-2"></i> Affective Traits</h5>
                        <table class="table table-bordered">
                            <?php foreach ($affective as $skill => $value): ?>
                                <?php if ($value): ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($skill); ?></td>
                                    <td>
                                        <div class="progress">
                                            <div class="progress-bar" role="progressbar" style="width: <?php echo ($value/5)*100; ?>%" 
                                                 aria-valuenow="<?php echo $value; ?>" aria-valuemin="0" aria-valuemax="5">
                                                <?php echo $value; ?>
                                            </div>
                                        </div>
                                    </td>
                                </tr>
                                <?php endif; ?>
                            <?php endforeach; ?>
                        </table>
                    </div>
                </div>
                <div class="alert alert-info mt-3">
                    <strong>Rating Scale:</strong> 5 = Excellent, 4 = Very Good, 3 = Good, 2 = Fair, 1 = Needs Improvement
                </div>
            </div>
        </div>

        <!-- Comments -->
        <div class="card">
            <div class="card-header">
                <i class="fas fa-comments me-2"></i> Comments & Recommendations
            </div>
            <div class="card-body">
                <div class="mb-4">
                    <h5><i class="fas fa-chalkboard-teacher me-2"></i> Class Teacher's Remarks</h5>
                    <div class="p-3 bg-light rounded">
                        <?php echo htmlspecialchars($TeachersRemarks); ?>
                    </div>
                </div>
                
                <div class="mb-4">
                    <h5><i class="fas fa-user-tie me-2"></i> Principal's Comments</h5>
                    <div class="p-3 bg-light rounded">
                        <?php echo htmlspecialchars($htcomments); ?>
                    </div>
                </div>
                
                <div class="text-center mt-4">
                    <div class="d-inline-block p-3 border rounded">
                        <div class="mb-2"><strong>Authorized Signature</strong></div>
                        <div class="text-muted small">Verbum Dei International College</div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Print Button -->
        <div class="text-center no-print my-4">
            <button class="btn btn-print" onclick="window.print()">
                <i class="fas fa-print me-2"></i> Print Result
            </button>
        </div>
    </div>

    <!-- Footer -->
    <footer class="footer no-print">
        <div class="container text-center">
            <div class="mb-2">
                <img src="vdi_logo.png" alt="Verbum Dei International College" height="40" class="me-3">
                <img src="dataswift_logo.png" alt="Powered by DataSwift" height="30">
            </div>
            <p class="mb-0">
                &copy; <?php echo date('Y'); ?> Verbum Dei International College. All Rights Reserved.<br>
                <small>Powered by DataSwift Educational Solutions</small>
            </p>
        </div>
    </footer>

    <!-- Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>