<?php

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




$fee = new Fee($conn);
$student =  new Student($conn);
$course =  new Course($conn);
$report =  new Report($conn);
$studentsData = $student->getAllStudents();
$studentFeeData =  $fee->getAllStudentFees();
$due =  $report->getTotalDueUnpaid();
$revenue = $report->getTotalPayments();
$totalStudents = $report->getTotalEnrolledStudents();
//$duesData =  $report->getFiveTopDues();
//$totalDue = $report->totalDue();
//$dueAmmount = $totalDue[0]['total_difference']; 
$monthData = $report->getPaymentsPerMonth();
$newArray = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
foreach ($monthData as $data) {
  $newArray[$data['month'] - 1] = $data['total_payments'];
}




?>

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

<head>
  <title> SMS | DASHBOARD </title>
  <!-- Include Chart.js library -->
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
  <?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="row">
            <div class="col-sm-4 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <div class="chartjs-size-monitor">
                    <div class="chartjs-size-monitor-expand">
                      <div class=""></div>
                    </div>
                    <div class="chartjs-size-monitor-shrink">
                      <div class=""></div>
                    </div>
                  </div>
                  <h4 class="card-title">Students</h4>
                  <p>Total Enrolled Students</p>
                  <h4 class="text-dark font-weight-bold mb-2"><?= number_format($totalStudents) ?></h4>

                </div>
              </div>
            </div>

            <div class="col-sm-4 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <div class="chartjs-size-monitor">
                    <div class="chartjs-size-monitor-expand">
                      <div class=""></div>
                    </div>
                    <div class="chartjs-size-monitor-shrink">
                      <div class=""></div>
                    </div>
                  </div>
                  <h4 class="card-title">Revenue</h4>
                  <p>Total Revenue</p>
                  <h4 class="text-dark font-weight-bold mb-2"><?= number_format($revenue) ?> Tsh</h4>

                </div>
              </div>
            </div>

            <div class="col-sm-4 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <div class="chartjs-size-monitor">
                    <div class="chartjs-size-monitor-expand">
                      <div class=""></div>
                    </div>
                    <div class="chartjs-size-monitor-shrink">
                      <div class=""></div>
                    </div>
                  </div>
                  <h4 class="card-title">Dues</h4>
                  <p>Total Unpaid fees </p>
                  <h4 class="text-dark font-weight-bold mb-2">

                    <?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);
                    }
                    ?> Tsh
                  </h4>

                </div>
              </div>
            </div>
          </div>

          <div class="row">
            <div class="col-lg-6 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <div class="chartjs-size-monitor">
                    <div class="chartjs-size-monitor-expand">
                      <div class=""></div>
                    </div>
                    <div class="chartjs-size-monitor-shrink">
                      <div class=""></div>
                    </div>
                  </div>
                  <h4 class="card-title">Revenue Chart per month</h4>
                  <canvas id="barChart" width="408" height="204" style="display: block; width: 408px; height: 204px;" class="chartjs-render-monitor"></canvas>
                </div>
              </div>
            </div>
            <div class="col-lg-6 grid-margin stretch-card">

              <div class="card">
                <div class="card-body">
                  <div class="chartjs-size-monitor">
                    <div class="chartjs-size-monitor-expand">
                      <div class=""></div>
                    </div>
                    <div class="chartjs-size-monitor-shrink">
                      <div class=""></div>
                    </div>
                  </div>
                  <h4 class="card-title">Revenue against Dues chart</h4>
                  <canvas id="pieChart" width="408" height="204" style="display: block; width: 100%; " class="chartjs-render-monitor"></canvas>
                </div>
              </div>
            </div>


            <div class="col-lg-12 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <h4 class="card-title">Top 5 Dues</h4>
                  <p class="card-description">
                    LIst of top five dues
                  </p>
                  <div class="table-responsive">
                    <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;
                        $query = "
    SELECT 
        students.en_id, 
        students.fullname, 
        IFNULL(SUM(student_payments.amount), 0) AS total_paid, 
        fees.ammount AS course_price,
        (IFNULL(fees.ammount, 0) - IFNULL(SUM(student_payments.amount), 0)) AS remaining_balance
    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
    ORDER BY 
        remaining_balance DESC  -- Order by highest remaining balance
    LIMIT 5";  // Limit to top 5

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

                        // Check if there are results
                        if ($result) {
                          while ($studentValue = mysqli_fetch_assoc($result)) {
                            // Calculate total amount paid and remaining balance
                            $totalPaid = $studentValue['total_paid'];
                            $coursePrice = $studentValue['course_price'];
                            $remainingBalance = $studentValue['remaining_balance'];
                        ?>
                            <tr>
                              <td><?= ++$x ?></td>
                              <td><?= $studentValue['en_id'] ?></td>
                              <td><?= $studentValue['fullname'] ?></td>
                              <td><?= number_format($coursePrice) ?></td>
                              <td><?= number_format($totalPaid) ?></td>
                              <td class='text-danger'><?= number_format($remainingBalance) ?></td>
                            </tr>
                        <?php
                          }
                        } else {
                          echo "<tr><td colspan='6'>No data found.</td></tr>";
                        }
                        ?>
                      </tbody>
                    </table>

                  </div>
                </div>
              </div>
            </div>

          </div>
          <script>
            <?php

            $tDues =  $report->getTotalDueUnpaid();
            $tPayments = $report->getTotalPayments();
            $neededToPay = $tDues + $tPayments;

            $aDues = round(($tDues / $neededToPay) * 100);
            $aPayments = round(($tPayments / $neededToPay) * 100);



            $barChartdata = [$aDues, $aPayments];
            $barChartJsonData = json_encode($barChartdata);







            ?>
            // Get the canvas element
            var ctx = document.getElementById('pieChart').getContext('2d');

            // Define data for the chart


            var data = {
              labels: ['Dues', 'Payments'],
              datasets: [{
                data: <?= $barChartJsonData ?>,
                backgroundColor: [
                  'rgba(255, 99, 132, 0.7)',
                  'rgba(255, 159, 64, 0.7)',
                ],
              }]
            };

            // Configure options for the chart
            var options = {
              responsive: false,
              maintainAspectRatio: false
            };

            // Create the pie chart
            var myPieChart = new Chart(ctx, {
              type: 'pie',
              data: data,
              options: options
            });
          </script>




          <script>
            // Get the canvas element
            var ctx = document.getElementById('barChart').getContext('2d');



            // Define data for the chart
            var data = {
              labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
              datasets: [{
                label: 'Number of Items',
                data: <?= json_encode($newArray) ?>,
                backgroundColor: [
                  'rgba(255, 99, 132, 0.2)',
                  'rgba(255, 159, 64, 0.2)',
                  'rgba(255, 205, 86, 0.2)',
                  'rgba(75, 192, 192, 0.2)',
                  'rgba(54, 162, 235, 0.2)',
                  'rgba(153, 102, 255, 0.2)'
                ],
                borderColor: [
                  'rgb(255, 99, 132)',
                  'rgb(255, 159, 64)',
                  'rgb(255, 205, 86)',
                  'rgb(75, 192, 192)',
                  'rgb(54, 162, 235)',

                ],
                borderWidth: 1
              }]
            };

            // Configure options for the chart
            var options = {
              scales: {
                yAxes: [{
                  ticks: {
                    beginAtZero: true
                  }
                }]
              }
            };

            // Create the bar chart
            var myBarChart = new Chart(ctx, {
              type: 'bar',
              data: data,
              options: options
            });
          </script>





        </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>