<?php 
ob_start();
session_start();
include ('../includes/connection.php');  
$ex_id=$_GET['id'];
$class_id=$_GET['id2'];
$year=$_GET['id3'];
 
$query= mysqli_query($conn,"SELECT * FROM exam_srms,class_srms where exam_srms.class_id=class_srms.class_id AND exam_srms.exam_id='$ex_id'")or die(mysqli_error());
$class1 = mysqli_fetch_array($query); 
$class_name1=$class1['nick']; 
 
$html .= '  <center><h3><b>  PRESIDENTS OFFICE <br>
REGIONAL ADMINISTRATION AND LOCAL GOVERNMENT<br>
SIHA DISTRICT COUNCIL</b><br></h3>
<h4>SIKIRARI SECONDARY SCHOOL<br>
<u class="dotted-line"> '.$class1['class_name'].' | '.$class1['exam_name'].' EXAMINATION RESULT - <b>'.$year.'</u></h4></center>
 '; 
 $html .= ' <style>
    table, tr,th, td {
    border: 1px solid black;
    border-collapse: collapse;
    padding: 4px; 
}  
table {
    font-size: 10; 
}   
</style>'; 

if($class_name1=="PCM"){ 
    $scoring_subject = ['PHY', 'CHEM', 'A_MATH']; // Used for division  
      }else{  
     $scoring_subject = ['PHY', 'CHEM', 'BIO']; // Used for division 
     } 
    
   $subject_list2 = "'" . implode("','", $scoring_subject) . "'";
    // Step 1: Get total points per student
    $sql2 = "SELECT marks_srms.identity_sub,marks_srms.student_roll_no,student_srms.student_gender,student_srms.student_roll_no,marks_srms.class_id,marks_srms.year_of_study, SUM(marks_srms.point) as total_P FROM marks_srms,student_srms WHERE marks_srms.student_roll_no=student_srms.student_roll_no AND marks_srms.class_id='$class_id' AND marks_srms.year_of_study='$year'  AND student_srms.student_status='Enable' AND marks_srms.exam_id='$ex_id' AND marks_srms.identity_sub IN ($subject_list2) GROUP BY marks_srms.student_roll_no,student_srms.student_gender ";

    $result1 = $conn->query($sql2);

    // Step 2: Initialize structure
    $genders = ['Male', 'Female'];
    $divisions = ['Division I', 'Division II', 'Division III','Division IV', 'Division 0'];

    $gender_report = [];
    $division_totals = array_fill_keys($divisions, 0); // to collect column totals

    foreach ($genders as $gender) {
        foreach ($divisions as $div) {
            $gender_report[$gender][$div] = 0;
        }
    }

    // Step 3: Fill gender division counts and compute totals
    foreach ($result1 as $row) {
        $gender = $row['student_gender'];
        $total_point = (int)$row['total_P'];

        if ($total_point >= 3 && $total_point <= 9) $division = "Division I";
        elseif ($total_point >= 10 && $total_point <= 12) $division = "Division II";
        elseif ($total_point >= 13 && $total_point <= 17) $division = "Division III";
        elseif ($total_point >= 18 && $total_point <= 19) $division = "Division IV";
        else $division = "Division 0";

          
        $gender_report[$gender][$division]++;
        $division_totals[$division]++;
    } 
   $html .= "<table class='M' style='border-style: dashed;'> <thead>
      <tr><th colspan='7'><center>CLASS PERFORMANCE</center></th> 
      <tr >  
          <th>Gender</th>
            <th>Division I</th>
            <th>Division II</th>
            <th>Division III</th>
            <th>Division IV</th>
            <th>Division 0</th>
            <th>Total</th>
        </tr>
      </thead>";

    $grand_total = 0;

    foreach ($gender_report as $gender => $div_counts) {
        $total = array_sum($div_counts);
        $grand_total += $total;
       $html .= "<tr>
                <td>$gender</td>
                <td>{$div_counts['Division I']}</td>
                <td>{$div_counts['Division II']}</td>
                <td>{$div_counts['Division III']}</td>
                <td>{$div_counts['Division IV']}</td>
                <td>{$div_counts['Division 0']}</td>
                <td><strong>$total</strong></td>
              </tr>";
    }

    // Output bottom total row
   $html .= "<tr>
            <th>Total</th>
            <th>{$division_totals['Division I']}</th>
            <th>{$division_totals['Division II']}</th>
            <th>{$division_totals['Division III']}</th>
            <th>{$division_totals['Division IV']}</th>
            <th>{$division_totals['Division 0']}</th>
            <th><strong>$grand_total</strong></th>
          </tr>";

    $html .= "</table>";



 $ex_id=$_GET['id']; 

if($class_name1=="PCM"){
//   $display_subjects = ['GS', 'PHY', 'CHEM', 'A_MATH']; // Display in table
  $display_subjects = ['HIST-M','A-COM', 'PHY', 'CHEM', 'A_MATH']; // Display in table
  $scoring_subjects = ['PHY', 'CHEM', 'A_MATH']; // Used for division calculation only
   }else{
//   $display_subjects = ['GS','BAM', 'PHY', 'CHEM','BIO']; // Display in table
  $display_subjects = ['HIST-M','A-COM','BAM', 'PHY', 'CHEM','BIO']; // Display in table
  $scoring_subjects = ['PHY', 'CHEM', 'BIO']; // Used for division calculation only
   } 

$students_sql = "SELECT DISTINCT student_srms.student_roll_no,student_srms.student_name,student_srms.student_gender FROM student_srms,marks_srms WHERE marks_srms.student_roll_no=student_srms.student_roll_no   AND student_srms.student_status='Enable' AND marks_srms.class_id='$class_id' AND marks_srms.exam_id='$ex_id'";
  $students_result = $conn->query($students_sql);

// 4. Collect student data first
$student_rows = [];

while ($student = $students_result->fetch_assoc()) {
      $student_id = $student['student_roll_no'];
      $student_name = $student['student_name'];
      $student_gender = $student['student_gender'];
// Placeholder
$subject_data = array_fill_keys($display_subjects, ['marks' => '-', 'grade' => '-', 'point' => 0]);
$total_point = 0;
$total_marks = 0;

// Fetch subject info
$subject_list = "'" . implode("','", $display_subjects) . "'";
$sql = "SELECT general_subject.identity, marks_srms.grade, marks_srms.marks,marks_srms.class_id,marks_srms.year_of_study, marks_srms.point FROM marks_srms,general_subject WHERE marks_srms.subject_id=general_subject.sub_id AND marks_srms.student_roll_no = '$student_id' AND marks_srms.class_id='$class_id' AND marks_srms.year_of_study='$year' AND marks_srms.exam_id='$ex_id' AND general_subject.identity IN ($subject_list) ORDER BY marks_srms.point DESC";
$result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {
$sub = $row['identity'];
$subject_data[$sub] = [
    'marks' => $row['marks'],
    'grade' => $row['grade'],
    'point' => $row['point']
];
if (in_array($sub, $scoring_subjects)) {
    $total_point += (int)$row['point'];
    $total_marks += (int)$row['marks'];
}
}

// Division logic
if ($total_point >= 3 && $total_point <= 9) {
$division = "I";
} elseif ($total_point >= 10 && $total_point <= 12) {
  $division = "II";
} elseif ($total_point >= 13 && $total_point <= 17) {
  $division = "III";
} elseif ($total_point >= 18 && $total_point <= 19) {
  $division = "IV";
} else {
  $division = "0";
}

// Store in array for sorting
$student_rows[] = [
'student_roll_no' => $student_id,
'student_name' => $student_name,
'student_gender' => $student_gender,
'identity' => $subject_data,
'total_point' => $total_point,
'total_marks' => $total_marks,
'division' => $division
];
}

// 5. Sort by total_point DESC for ranking
usort($student_rows, function($a, $b) {
return $b['total_marks'] - $a['total_marks'];
});

// 6. Display table with positions
$html .= " <table>
<tr>
  <th>S/N</th> 
  <th>Student Name</th>";

foreach ($display_subjects as $subject) {
$html .= "<th>$subject</th>";
}
$html .= "<th>T-M</th> <th>Point</th> <th>Div</th></tr>"; 

// 7. Output rows with position
$position = 1;
$i = 1;
foreach ($student_rows as $row) {
$html .= " <tr>
      <td>$i</td> 
      <td>{$row['student_name']}</td> ";

foreach ($display_subjects as $subject) {
  $s = $row['identity'][$subject]['marks'];
  $g = $row['identity'][$subject]['grade'];
 $html .= "<td>$s - $g</td>";
}

$html .= " <td>{$row['total_marks']}</td>
    <td>{$row['total_point']}</td>
    <td>{$row['division']}</td> 
   </tr>";
$position++;
$i++;
}

$html .= "</table>";

$i = 1;

$sql_subjects = "
SELECT 
    class_id,
    exam_id,
    identity_sub,
     SUM(CASE WHEN grade = 'A' THEN 1 ELSE 0 END) AS grade_A,
        SUM(CASE WHEN grade = 'B' THEN 1 ELSE 0 END) AS grade_B,
        SUM(CASE WHEN grade = 'C' THEN 1 ELSE 0 END) AS grade_C,
        SUM(CASE WHEN grade = 'D' THEN 1 ELSE 0 END) AS grade_D,
        SUM(CASE WHEN grade = 'E' THEN 1 ELSE 0 END) AS grade_E,
        SUM(CASE WHEN grade = 'S' THEN 1 ELSE 0 END) AS grade_S,
        SUM(CASE WHEN grade = 'F' THEN 1 ELSE 0 END) AS grade_F,
        COUNT(*) AS total_students,
        ROUND(SUM(
            CASE grade
                WHEN 'A' THEN 5
                WHEN 'B' THEN 4
                WHEN 'C' THEN 3
                WHEN 'D' THEN 2
                WHEN 'E' THEN 1
                WHEN 'S' THEN 0
                WHEN 'F' THEN 0
        END
    ) / COUNT(*), 2) AS GPA
FROM marks_srms
 WHERE class_id = '$class_id'  AND exam_id = '$ex_id'
GROUP BY class_id, exam_id, identity_sub
 
";

$result_subjects = $conn->query($sql_subjects);

if ($result_subjects->num_rows > 0) {
   $html .= "<h2>Grade Summary by Subject</h2>";
   $html .= "<table border='1' cellpadding='5'>
        <tr>
           <th>S/N</th> 
            <th>Subject Name</th>
            <th>Grade A</th>
            <th>Grade B</th>
            <th>Grade C</th>
            <th>Grade D</th>
            <th>Grade E</th>
            <th>Grade S</th>
            <th>Grade F</th>
            <th>Total Students</th>
            <th>GPA</th>
        </tr>";

    while($row = $result_subjects->fetch_assoc()) {
      $html .= "<tr>
           <td>$i</td> 
            <td>{$row['identity_sub']}</td>
            <td>{$row['grade_A']}</td>
            <td>{$row['grade_B']}</td>
            <td>{$row['grade_C']}</td>
            <td>{$row['grade_D']}</td>
            <td>{$row['grade_E']}</td>
            <td>{$row['grade_S']}</td>
            <td>{$row['grade_F']}</td>
            <td>{$row['total_students']}</td>
            <td>{$row['GPA']}</td>
        </tr>";
        $i++;
    }
    $sql_overall = "
    SELECT 
    class_id,
    exam_id,
    ROUND(SUM(
        CASE grade
           WHEN 'A' THEN 5
            WHEN 'B' THEN 4
            WHEN 'C' THEN 3
            WHEN 'D' THEN 2
            WHEN 'E' THEN 1
            WHEN 'S' THEN 0
            WHEN 'F' THEN 0
        END
    ) / COUNT(*), 2) AS overall_gpa,
    COUNT(*) AS total_grades
    FROM marks_srms
    WHERE class_id = '$class_id'  AND exam_id = '$ex_id'
    GROUP BY class_id, exam_id 
    ";
    
    $result_overall = $conn->query($sql_overall);
    
    if ($result_overall->num_rows > 0) {
         
        while($row = $result_overall->fetch_assoc()) {
           $html .= "<tr>
                <td colspan='9'><strong>OVERALL GPA </strong></td> 
                <td><strong>{$row['total_grades']}</strong></td>
                <td><strong>{$row['overall_gpa']}</strong></td>
            </tr>";
        }
    }
    $html .= "</table><br>";
} else {
   $html .= "<p>No subject data found  .</p>";
}
 
 
require_once '../dompdf/autoload.inc.php';
use Dompdf\Dompdf;
$dompdf = new Dompdf(); 
$dompdf->loadHtml($html); 
$dompdf->setPaper('A4', 'portrait'); 
$dompdf->render();
$dompdf->stream(' '. $class1['class_name'] . '.pdf');
?>