<?php
require_once '../auth/auth.php';
require_once '../models/db_connection.php';
require_once '../models/student_model.php';
require_once '../models/fee_model.php';
require_once '../models/report_model.php';

$fee = new Fee($conn);
$studentFeeData =  $fee->getAllStudentFees();
$student =  new Student($conn);
$studentsData = $student->getAllStudents();
$report = new Report($conn);
$revenue = $report->getTotalPayments();
//$totalDue = $report->totalDue();
$dueAmmount = $totalDue[0]['total_difference']; 


?>

<!DOCTYPE html>
<html lang="en">

<head>
 <title>Student | Add Student </title>
 <?php include("../templates/header.php"); ?>
</head>
<body>
  <div class="container-scroller">
    <!-- partial:partials/_navbar.html -->
    <?php include("../templates/nav.php"); ?>
    <!-- partial -->
    <div class="container-fluid page-body-wrapper">
      <!-- partial:partials/_sidebar.html -->
      <?php include("../templates/sidenav.php"); ?>
      <!-- partial -->
      <div class="main-panel">
        <div class="content-wrapper">
        
        <div class="col-lg-12 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <h4 class="card-title">View All Students</h4>
                  <p class="card-description">
                    Students Table <code></code>
                  </p>
                  <div class="table-responsive pt-3">
                    <table class="table table-bordered display"  id="example"  style="width:100%">
                      <thead>
                        <tr>
                          <th> # </th>
                          <th>Enrollment ID</th>
                          <th> Fullname</th>
                          <th> Total Fees</th>
                          <th> Paid</th>
                          <th> Due</th>
                        </tr>
                      </thead>
                      <tbody>
                        <?php 
                        $x = 0;
                        $totalPaid = 0;
                         $totalUnpaid = 0;
                            foreach ($studentFeeData as $studentValue) {
                                $totalPaid += $studentPayment[0]['totalAmmount'];
                                  $totalUnpaid += ($studentValue['ammount'] - $studentPayment[0]['totalAmmount']);
                                $studentPayment = $fee->getTotalPaymentsForStudent($studentValue['student_id']);
                                
                              ?>
                              <tr>
                          <td><?= ++$x ?></td>
                          <td><?= $studentValue['en_id'] ?></td>
                          <td><?= $studentValue['fullname'] ?></td>
                          <td><?= number_format($studentValue['ammount'] )?></td>
                          <td><?=number_format( $studentPayment[0]['totalAmmount'] )?></td>
                          <td><?php echo ( number_format($studentValue['ammount'] - $studentPayment[0]['totalAmmount']))?></td>
                          
                         
  
                        </tr>
                           <?php }?>
                        
                        
                      </tbody>
                    </table>
                  </div>
                    <div>
                         <div class="card">
                            <div class="card-body">
                                    <p>Total Paid : <?= number_format($revenue); ?></p>
                                    <p>Total Due : 
                                    <?php
                    $total_remaining_balance = 0;

                    // SQL Query to get the student data along with remaining balance
                    $query = "
    SELECT 
        students.en_id, 
        students.fullname, 
        students.work, 
        students.address, 
        students.phone_number, 
        IFNULL(SUM(student_payments.amount), 0) AS total_paid, 
        MAX(student_payments.payament_date) AS payment_date, 
        fees.ammount AS course_price,
        (IFNULL(fees.ammount, 0) - IFNULL(SUM(student_payments.amount), 0)) AS remaining_balance,
        fees.name AS course_name,
        student_payments.id AS payment_id  -- Get the payment id from student_payments
    FROM 
        students 
    LEFT JOIN 
        student_course ON students.en_id = student_course.s_id
    LEFT JOIN 
        fees ON student_course.course_id = fees.id
    LEFT JOIN 
        student_payments ON students.student_id = student_payments.st_id
    WHERE 
        students.status = 'enrolled'
    GROUP BY 
        students.student_id
";

                    // Execute the query
                    $result = mysqli_query($conn, $query);

                    // Check if there are results
                    if ($result) {
                      while ($row = mysqli_fetch_assoc($result)) {
                        // Display student information (Optional)
                        //echo "Student: " . $row['fullname'] . "<br>";
                        //echo "Remaining Balance: " . number_format($row['remaining_balance'], 2) . "<br>";

                        // Add remaining balance to the total
                        $total_remaining_balance += $row['remaining_balance'];
                      }
                      echo  number_format($total_remaining_balance,  0);
                    }
                    ?>
                                    </p>
                                    
                                 </div>
                               </div>
                    </div>
                </div>
              </div>
            </div>        
        </div>
        <!-- content-wrapper ends -->
        <!-- partial:partials/_footer.html -->
        <?php include("../templates/footer.php"); ?>
        
        <!-- partial -->
      </div>
      <!-- main-panel ends -->
    </div>
    <!-- page-body-wrapper ends -->
  </div>
    <?php include("../templates/inc_scripts.php"); ?>
    <script>new DataTable('#example');</script>
</body>

</html>

