<?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_name=$class1['class_name'];
 
 $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_name=="FORM ONE"){
$scoring_subject = ["B-MATH","KISW", "BIO","HIST-M","BUS","ENGL","GEO","PHY", "CHEM","HIST","EDK", "AGRIC"]; // Used for division 
} else {
$scoring_subject = ["B-MATH","KISW","BIO","GS","HIST","GEO","ENGL", "PHY", "CHEM","EDK"]; // Used for division 
}
$subject_list = "'" . implode("','", $scoring_subject) . "'";

// 1. Get marks for scoring subjects
 

$sql = "
    SELECT *
    FROM marks_srms m
    JOIN student_srms s ON m.student_roll_no = s.student_roll_no
    WHERE m.exam_id = '$ex_id'
      AND s.student_status='Enable'
      AND m.class_id = '$class_id'
      AND m.identity_sub IN ($subject_list)
    ORDER BY m.student_roll_no, m.marks DESC
";

$result = $conn->query($sql);

// 2. Process: Get top 7 points per student
$students = [];

while ($row = $result->fetch_assoc()) {
    $id = $row['student_roll_no'];
    $gender = $row['student_gender'];
    $point = (int)$row['point'];

    if (!isset($students[$id])) {
        $students[$id] = [
            'gender' => $gender,
            'points' => []
        ];
    }

    if (count($students[$id]['points']) < 7) {
        $students[$id]['points'][] = $point;
    }
}

// 3. Calculate totals and classify into divisions
$divisions = ['Division I', 'Division II', 'Division III', 'Division IV', 'Division 0'];
$gender_division = [
    'Male' => array_fill_keys($divisions, 0),
    'Female' => array_fill_keys($divisions, 0)
];
$division_totals = array_fill_keys($divisions, 0);
$total_students = 0;

foreach ($students as $student) {
    $gender = $student['gender'];
    $total = array_sum($student['points']);
    $division = '';

    if ($total >= 7 && $total <= 17) $division = "Division I";
    elseif ($total >= 18 && $total <= 21) $division = "Division II";
    elseif ($total >= 22 && $total <= 25) $division = "Division III";
    elseif ($total >= 26 && $total <= 33) $division = "Division IV";
    else $division = "Division 0";

    $gender_division[$gender][$division]++;
    $division_totals[$division]++;
    $total_students++;
}

// 4. Display HTML Table
 $html .= "<table class='M' style='border-style: dashed; margin: 5px;'> <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>";

foreach ($gender_division as $gender => $counts) {
    $row_total = array_sum($counts);
 $html .= "<tr>
        <td>$gender</td>
        <td>{$counts['Division I']}</td>
        <td>{$counts['Division II']}</td>
        <td>{$counts['Division III']}</td>
        <td>{$counts['Division IV']}</td>
        <td>{$counts['Division 0']}</td>
        <td><strong>$row_total</strong></td>
    </tr>";
}

$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>$total_students</strong></th>
</tr></tbody>";

$html .= "</table>";

 // Fill missing subjects with "-"
  if ($class_name=="FORM ONE"){
  
  } else {
   
  }
        if ($class_name=="FORM ONE"){
             $display_subjects = ["B-MATH","KISW", "BIO","HIST-M","BUS","ENGL","GEO","PHY", "CHEM","HIST","EDK", "AGRT"]; // Used for division 
        $all_subjects = ["B-MATH","KISW", "BIO","HIST-M","BUS","ENGL","GEO","PHY", "CHEM","HIST","EDK", "AGRIC"]; // Used for division 
        } else {
        $display_subjects = ["B-MATH","KISW","BIO","CIVICS","HIST","GEO","ENGL", "PHY", "CHEM","EDK"]; // Used for division
        $all_subjects = ["B-MATH","KISW","BIO","GS","HIST","GEO","ENGL", "PHY", "CHEM","EDK"]; // Used for division 
        }
  
$subject_list = "'" . implode("','", $all_subjects) . "'";
$student_rows = [];

// 1. Get all students
$sql = "SELECT DISTINCT student_srms.student_gender,student_srms.student_roll_no,student_srms.student_name,student_srms.p_mobile,student_srms.student_status FROM student_srms,marks_srms WHERE marks_srms.student_roll_no=student_srms.student_roll_no AND marks_srms.class_id='$class_id' AND student_srms.student_status='Enable' ";
$res = $conn->query($sql);
while ($r = $res->fetch_assoc()) {
    $student_id = $r['student_roll_no'];
    $student_rows[$student_id] = [
        'student_roll_no' => $student_id,
        'student_name' => $r['student_name'],
        'student_gender' => $r['student_gender'],
        'identity' => [],
        'total_marks' => 0,
        'total_point' => 0,
        'division' => ''
    ];
}

// 2. Get subject scores
foreach ($student_rows as $student_id => &$row) {
    $sql_sub = "SELECT general_subject.identity, marks_srms.marks, marks_srms.point, marks_srms.grade 
                FROM marks_srms 
                JOIN general_subject ON marks_srms.subject_id = general_subject.sub_id 
                WHERE marks_srms.student_roll_no = '$student_id'
                 AND marks_srms.year_of_study='$year'
                  AND marks_srms.class_id = '$class_id' 
                  AND marks_srms.exam_id = '$ex_id' 
                  AND general_subject.identity IN ($subject_list)
                ORDER BY marks_srms.marks DESC";

    $res_sub = $conn->query($sql_sub);
    $count = 0;
    while ($s = $res_sub->fetch_assoc()) {
        $identity = $s['identity'];
        $row['identity'][$identity] = [
            'marks' => $s['marks'],
            'grade' => $s['grade'],
            'point' => $s['point']
        ];

        if ($count < 7) {
            $row['total_marks'] += (int)$s['marks'];
            $row['total_point'] += (int)$s['point'];
            $count++;
        }
    }

    // 3. Assign division
    $tp = $row['total_point'];
    if ($tp >= 7 && $tp <= 17) $row['division'] = "I";
    elseif ($tp >= 18 && $tp <= 21) $row['division'] = "II";
    elseif ($tp >= 22 && $tp <= 25) $row['division'] = "III";
    elseif ($tp >= 26 && $tp <= 33) $row['division'] = "IV";
    else $row['division'] = "0";
}
unset($row);

// 4. Sort by points ascending
usort($student_rows, function($a, $b) {
    return $a['total_point'] <=> $b['total_point'];
});

// 5. Print table
$html .= "<table class='table table-head-fixed text-nowrap table-bordered'>
        <thead>
        <tr>
            <th>S/N</th> 
            <th>Student Name</th>
            <th>SEX</th>";
foreach ($display_subjects as $subject) {
   $html .= "<th>$subject</th>";
}
$html .= "<th>T-M</th> <th>Point</th> <th>Div</th><th>PST</th>
      </tr>
      </thead><tbody>";

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

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

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

    $position++;
    $i++;
}
$html .= "</tbody></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 = '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 'F' THEN 1
        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 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_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 'F' THEN 1
        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='7'><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', 'landscape'); 
$dompdf->render();
$dompdf->stream(' '. $class1['class_name'] . '.pdf');
?>