<?php  
$page_title = 'Dashboard';
include 'header.php';

// Get statistics
$sel_alumini = mysqli_query($con,"select * from alumini");
$tot_alumini = mysqli_num_rows($sel_alumini);

$sel_events = mysqli_query($con,"select * from events");
$tot_events = mysqli_num_rows($sel_events);

$sel_events_active = mysqli_query($con,"select * from events where is_active = 1");
$tot_events_active = mysqli_num_rows($sel_events_active);

$sel_events_inactive = mysqli_query($con,"select * from events where is_active = 0");
$tot_events_inactive = mysqli_num_rows($sel_events_inactive);

// Today's logins
$today = date('Y-m-d');
$sel_login_today = mysqli_query($con, "SELECT COUNT(DISTINCT roll_number) as total FROM alumni_login_logs WHERE DATE(login_datetime) = '$today'");
$tot_login_today = mysqli_num_rows($sel_login_today);

// Total logins
$sel_login_total = mysqli_query($con, "SELECT * FROM alumni_login_logs");
$tot_login_total = mysqli_num_rows($sel_login_total);

// Get unique logins (total unique users who logged in)
$sel_login_unique = mysqli_query($con, "SELECT DISTINCT roll_number FROM alumni_login_logs");
$tot_login_unique = mysqli_num_rows($sel_login_unique);

// College count
$sel_college = mysqli_query($con,"select * from college");
$tot_college = mysqli_num_rows($sel_college);

// Get monthly alumni registration data for chart
$monthly_alumni = [];
for ($i = 11; $i >= 0; $i--) {
    $month = date('Y-m', strtotime("-$i months"));
    $sel = mysqli_query($con, "SELECT * FROM alumini WHERE DATE_FORMAT(created_at, '%Y-%m') = '$month'");
    $monthly_alumni[] = mysqli_num_rows($sel);
}
$monthly_alumni_json = json_encode($monthly_alumni);

// Get monthly event data for chart
$monthly_events = [];
for ($i = 11; $i >= 0; $i--) {
    $month = date('Y-m', strtotime("-$i months"));
    $sel = mysqli_query($con, "SELECT * FROM events WHERE DATE_FORMAT(event_date, '%Y-%m') = '$month'");
    $monthly_events[] = mysqli_num_rows($sel);
}
$monthly_events_json = json_encode($monthly_events);

// Get monthly login data for chart
$monthly_logins = [];
for ($i = 11; $i >= 0; $i--) {
    $month = date('Y-m', strtotime("-$i months"));
    $sel = mysqli_query($con, "SELECT * FROM alumni_login_logs WHERE DATE_FORMAT(login_datetime, '%Y-%m') = '$month'");
    $monthly_logins[] = mysqli_num_rows($sel);
}
$monthly_logins_json = json_encode($monthly_logins);

// Get last 6 months labels
$month_labels = [];
for ($i = 11; $i >= 0; $i--) {
    $month_labels[] = date('M Y', strtotime("-$i months"));
}
$month_labels_json = json_encode($month_labels);
?>
<div class="content-wrapper">
  <?php include 'breadcrumb.php'; ?>
  <section class="content">
    <div class="container-fluid">
      <!-- Statistics Cards -->
      <div class="row">
        <div class="col-lg-3 col-6">
          <div class="small-box bg-info">
            <div class="inner">
              <h3><?php echo $tot_alumini; ?></h3>
              <p>Total Alumni</p>
            </div>
            <div class="icon">
              <i class="fas fa-users"></i>
            </div>
            <a href="alumini-list.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
        <div class="col-lg-3 col-6">
          <div class="small-box bg-success">
            <div class="inner">
              <h3><?php echo $tot_events; ?></h3>
              <p>Total Events</p>
            </div>
            <div class="icon">
              <i class="fas fa-calendar-alt"></i>
            </div>
            <a href="event-list.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
        <div class="col-lg-3 col-6">
          <div class="small-box bg-warning">
            <div class="inner">
              <h3><?php echo $tot_login_today; ?></h3>
              <p>Today's Logins</p>
            </div>
            <div class="icon">
              <i class="fas fa-user-clock"></i>
            </div>
            <a href="login_log_details.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
        <div class="col-lg-3 col-6">
          <div class="small-box bg-danger">
            <div class="inner">
              <h3><?php echo $tot_college; ?></h3>
              <p>Colleges</p>
            </div>
            <div class="icon">
              <i class="fas fa-university"></i>
            </div>
            <a href="college-list.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
      </div>

      <!-- Second Row Stats -->
      <div class="row">
        <div class="col-lg-3 col-6">
          <div class="small-box bg-gradient-primary">
            <div class="inner">
              <h3><?php echo $tot_events_active; ?></h3>
              <p>Active Events</p>
            </div>
            <div class="icon">
              <i class="fas fa-calendar-check"></i>
            </div>
            <a href="event-list.php?status=active" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
        <div class="col-lg-3 col-6">
          <div class="small-box bg-gradient-secondary">
            <div class="inner">
              <h3><?php echo $tot_events_inactive; ?></h3>
              <p>Inactive Events</p>
            </div>
            <div class="icon">
              <i class="fas fa-calendar-times"></i>
            </div>
            <a href="event-list.php?status=inactive" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
        <div class="col-lg-3 col-6">
          <div class="small-box bg-gradient-info">
            <div class="inner">
              <h3><?php echo $tot_login_unique; ?></h3>
              <p>Unique Logged In Users</p>
            </div>
            <div class="icon">
              <i class="fas fa-user-check"></i>
            </div>
            <a href="login_log_details.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
        <div class="col-lg-3 col-6">
          <div class="small-box bg-gradient-dark">
            <div class="inner">
              <h3><?php echo $tot_login_total; ?></h3>
              <p>Total Login Sessions</p>
            </div>
            <div class="icon">
              <i class="fas fa-sign-in-alt"></i>
            </div>
            <a href="login_log_details.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
          </div>
        </div>
      </div>

      <!-- Charts Row -->
      <div class="row">
        <!-- Monthly Alumni Registration Chart -->
        <div class="col-md-6">
          <div class="card">
            <div class="card-header">
              <h3 class="card-title">
                <i class="fas fa-chart-line mr-1"></i>
                Monthly Alumni Registrations
              </h3>
            </div>
            <div class="card-body">
              <canvas id="alumniChart" height="150"></canvas>
            </div>
          </div>
        </div>

        <!-- Monthly Events Chart -->
        <div class="col-md-6">
          <div class="card">
            <div class="card-header">
              <h3 class="card-title">
                <i class="fas fa-chart-bar mr-1"></i>
                Monthly Events Created
              </h3>
            </div>
            <div class="card-body">
              <canvas id="eventsChart" height="150"></canvas>
            </div>
          </div>
        </div>
      </div>

      <!-- Third Row Charts -->
      <div class="row">
        <!-- Login Activity Chart -->
        <div class="col-md-6">
          <div class="card">
            <div class="card-header">
              <h3 class="card-title">
                <i class="fas fa-chart-area mr-1"></i>
                Login Activity (Last 12 Months)
              </h3>
            </div>
            <div class="card-body">
              <canvas id="loginChart" height="150"></canvas>
            </div>
          </div>
        </div>

        <!-- Events Status Pie Chart -->
        <div class="col-md-6">
          <div class="card">
            <div class="card-header">
              <h3 class="card-title">
                <i class="fas fa-chart-pie mr-1"></i>
                Events Status Distribution
              </h3>
            </div>
            <div class="card-body">
              <canvas id="eventsPieChart" height="150"></canvas>
            </div>
          </div>
        </div>
      </div>

      <!-- Recent Activity -->
      <div class="row">
        <div class="col-md-6">
          <div class="card">
            <div class="card-header">
              <h3 class="card-title">
                <i class="fas fa-clock mr-1"></i>
                Recent Login Activity
              </h3>
            </div>
            <div class="card-body p-0">
              <table class="table table-striped">
                <thead>
                  <tr>
                    <th>Roll Number</th>
                    <th>Login Time</th>
                  </tr>
                </thead>
                <tbody>
                  <?php
                  $recent_logins = mysqli_query($con, "SELECT * FROM alumni_login_logs ORDER BY login_datetime DESC LIMIT 5");
                  while ($login = mysqli_fetch_assoc($recent_logins)) {
                    echo "<tr>";
                    echo "<td>" . $login['roll_number'] . "</td>";
                    echo "<td>" . date('d M Y H:i', strtotime($login['login_datetime'])) . "</td>";
                    echo "</tr>";
                  }
                  ?>
                </tbody>
              </table>
            </div>
          </div>
        </div>

        <div class="col-md-6">
          <div class="card">
            <div class="card-header">
              <h3 class="card-title">
                <i class="fas fa-calendar-plus mr-1"></i>
                Upcoming Events
              </h3>
            </div>
            <div class="card-body p-0">
              <table class="table table-striped">
                <thead>
                  <tr>
                    <th>Event Name</th>
                    <th>Date</th>
                  </tr>
                </thead>
                <tbody>
                  <?php
                  $upcoming_events = mysqli_query($con, "SELECT * FROM events WHERE event_date >= CURDATE() AND is_active = 1 ORDER BY event_date ASC LIMIT 5");
                  while ($event = mysqli_fetch_assoc($upcoming_events)) {
                    echo "<tr>";
                    echo "<td>" . $event['event_name'] . "</td>";
                    echo "<td>" . date('d M Y', strtotime($event['event_date'])) . "</td>";
                    echo "</tr>";
                  }
                  ?>
                </tbody>
              </table>
            </div>
          </div>
        </div>
      </div>

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

<!-- Chart.js -->
<script src="plugins/chart.js/Chart.min.js"></script>
<script>
  // Monthly Alumni Chart
  var alumniCtx = document.getElementById('alumniChart').getContext('2d');
  var alumniChart = new Chart(alumniCtx, {
    type: 'line',
    data: {
      labels: <?php echo $month_labels_json; ?>,
      datasets: [{
        label: 'Alumni Registrations',
        data: <?php echo $monthly_alumni_json; ?>,
        borderColor: '#17a2b8',
        backgroundColor: 'rgba(23, 162, 184, 0.2)',
        fill: true,
        tension: 0.4
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      scales: {
        y: {
          beginAtZero: true
        }
      }
    }
  });

  // Monthly Events Chart
  var eventsCtx = document.getElementById('eventsChart').getContext('2d');
  var eventsChart = new Chart(eventsCtx, {
    type: 'bar',
    data: {
      labels: <?php echo $month_labels_json; ?>,
      datasets: [{
        label: 'Events Created',
        data: <?php echo $monthly_events_json; ?>,
        backgroundColor: '#28a745',
        borderColor: '#28a745',
        borderWidth: 1
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      scales: {
        y: {
          beginAtZero: true
        }
      }
    }
  });

  // Login Activity Chart
  var loginCtx = document.getElementById('loginChart').getContext('2d');
  var loginChart = new Chart(loginCtx, {
    type: 'line',
    data: {
      labels: <?php echo $month_labels_json; ?>,
      datasets: [{
        label: 'Login Sessions',
        data: <?php echo $monthly_logins_json; ?>,
        borderColor: '#ffc107',
        backgroundColor: 'rgba(255, 193, 7, 0.2)',
        fill: true,
        tension: 0.4
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      scales: {
        y: {
          beginAtZero: true
        }
      }
    }
  });

  // Events Pie Chart
  var eventsPieCtx = document.getElementById('eventsPieChart').getContext('2d');
  var eventsPieChart = new Chart(eventsPieCtx, {
    type: 'doughnut',
    data: {
      labels: ['Active Events', 'Inactive Events'],
      datasets: [{
        data: [<?php echo $tot_events_active; ?>, <?php echo $tot_events_inactive; ?>],
        backgroundColor: ['#28a745', '#6c757d'],
        borderWidth: 0
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false
    }
  });
</script>

<?php include 'footer.php'; ?>