<?php
require_once '../auth/auth.php';
require_once '../models/db_connection.php';
  require_once '../models/report_model.php';
$report = new Report($conn);
  $revenue = $report->getTotalPayments();
// require_once '../models/fee_model.php';


// $fee = new Fee($conn);
// $studentPayments =  $fee->getAllPayments();
// $fromDate = '';
// $toDate = '';

$fromDate = isset($_POST['from']) ? $_POST['from'] : '';
$toDate = isset($_POST['to']) ? $_POST['to'] : '';
if (isset($_POST['filter'])) {
$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
            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' 
                AND student_payments.payament_date BETWEEN '$fromDate' AND '$toDate'
            GROUP BY students.student_id";
} else {
    // Default query when no filter is applied
    $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
            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";
}

$studentPayments = mysqli_query($conn, $query);

?>

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

<head>
  <title>Student | Add Student </title>
  <?php include("../templates/header.php"); ?>
  <script type='text/javascript'>
    function ConfirmDelete() {
      if (confirm("Are you sure you want to delete this payment?"))
        return true;
      else
        return false;
    }
  </script>
</head>

<body>
  <?php
  if (isset($_GET["message"])) {
    $message = $_GET["message"];
    echo "<script>alert('" . $message . "')</script>";
  }
  ?>
  <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">

          <form action="view_payments.php" method="POST">
            <div class="col-lg-12 grid-margin stretch-card">

              <div class="card">
                <div class="card-body">
                  <h4 class="card-title">View Payments</h4>
                  <div class="row">
                    <div class="col-4">
                      <p class="card-description">
                        Students Table <code></code>
                      </p>
                    </div>
                    <div class="col-8">
                      <div class="row">
                        <div class="col-sm-4">

                          <input placeholder="From" name="from" class="form-control" type="date" required>
                        </div>

                        <div class="col-sm-4">

                          <input placeholder="To" name="to" class="form-control" type="date" required>
                        </div>
                        <div class="col-sm-4">

                          <button type="submit" name="filter" class="btn btn-dark btn-lg ">Filter</button>
                          <a class="btn btn-primary btn-lg " href="../controllers/pdfReport.php?from=<?= $fromDate ?>&to=<?= $toDate ?>">Print</a>
                        </div>
                      </div>
          </form>
        </div>
      </div>

      <div class="table-responsive pt-3">
        <table class="table table-bordered display" id="example" style="width:100%">
          <thead>
            <tr>
              <th>SNO</th>
              <th>enrollment ID</th>
              <th>Fullname</th>
              <th>course</th>
              <th>Address</th>
              <th>Phone Number</th>
              <th>Amount Paid</th>
              <th>Payment Date</th>
              <th>Remaining Balance</th>
              <?php if ($_SESSION['role'] == 'admin') { ?>
                <th>Actions</th>
              <?php } ?>
            </tr>
          </thead>
          <tbody>
            <?php
            $x = 0;
            // Fetching student payment information along with course details
            while ($studentValue = mysqli_fetch_array($studentPayments)) {
            ?>
              <tr>
                <td><?php echo ++$x; ?></td>
                <td><?= $studentValue['en_id'] ?></td>
                <td><?= $studentValue['fullname'] ?></td>
                <td><?= isset($studentValue['course_name']) ? $studentValue['course_name'] : 'No course assigned' ?></td>
                <td><?= $studentValue['address'] ?></td>
                <td><?= $studentValue['phone_number'] ?></td>
                <td><?= number_format($studentValue['total_paid'], 2) ?></td>
                <td><?= $studentValue['payment_date'] ?></td>
                <td><?= number_format($studentValue['remaining_balance'], 2); ?></td>
                <?php if ($_SESSION['role'] == 'admin') { ?>
                  <td>
                    <a href='edit_payment.php?id=<?= $studentValue['payment_id'] ?>' class="btn btn-md"><i class="fa fa-pencil"></i></a>
                    <a href='delete_payment.php?id=<?= $studentValue['payment_id'] ?>' class="btn btn-md" onclick="return ConfirmDelete()"><i class="fa fa-trash"></i></a>
                  </td>
                <?php } ?>
              </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>