<?php
require_once __DIR__ . '/../config/database.php';

requireLogin();
requireAnyRole(['admin', 'super_admin']);

$db = Database::getInstance();

// Overall stats
$stats = [
    'users' => 0,
    'courses' => 0,
    'enrollments' => 0,
    'certificates' => 0,
    'revenue' => 0,
];

$row = $db->get("SELECT COUNT(*) AS cnt FROM users WHERE status = 'active'");
$stats['users'] = (int)($row['cnt'] ?? 0);

$row = $db->get("SELECT COUNT(*) AS cnt FROM courses WHERE status = 'published'");
$stats['courses'] = (int)($row['cnt'] ?? 0);

$row = $db->get("SELECT COUNT(*) AS cnt FROM course_enrollments WHERE status IN ('active','completed')");
$stats['enrollments'] = (int)($row['cnt'] ?? 0);

$row = $db->get("SELECT COUNT(*) AS cnt FROM certificates WHERE is_revoked = 0");
$stats['certificates'] = (int)($row['cnt'] ?? 0);

$row = $db->get("SELECT COALESCE(SUM(amount), 0) AS total FROM payments WHERE payment_status = 'completed'");
$stats['revenue'] = (float)($row['total'] ?? 0);

// Recent enrollments
$recentEnrollments = $db->getAll(
    "SELECT ce.*, c.title AS course_title, u.first_name, u.last_name
     FROM course_enrollments ce
     JOIN courses c ON ce.course_id = c.id
     JOIN users u ON ce.student_id = u.id
     ORDER BY ce.enrollment_date DESC
     LIMIT 10"
);

// Top courses by enrollments
$topCourses = $db->getAll(
    "SELECT c.*, COUNT(ce.id) AS enrollment_count
     FROM courses c
     LEFT JOIN course_enrollments ce ON c.id = ce.course_id
     WHERE c.status = 'published'
     GROUP BY c.id
     ORDER BY enrollment_count DESC
     LIMIT 10"
);

// User registrations by month (last 6 months)
$registrationsByMonth = $db->getAll(
    "SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS count
     FROM users
     WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
     GROUP BY DATE_FORMAT(created_at, '%Y-%m')
     ORDER BY month"
);

$pageTitle = 'Analytics';
include __DIR__ . '/includes/header.php';
?>

<div class="d-flex justify-content-between align-items-center mb-4">
  <div>
    <h1 class="h4 mb-0">Analytics</h1>
    <p class="text-muted mb-0">System overview and insights</p>
  </div>
</div>

<div class="row g-4 mb-4">
  <div class="col-sm-6 col-lg-3">
    <div class="card">
      <div class="card-body">
        <div class="d-flex align-items-center">
          <div class="avatar">
            <div class="avatar-initial bg-primary rounded shadow-xs">
              <i class="icon-base ri ri-user-3-line icon-24px"></i>
            </div>
          </div>
          <div class="ms-3">
            <p class="mb-0">Active Users</p>
            <h5 class="mb-0"><?php echo number_format($stats['users']); ?></h5>
          </div>
        </div>
      </div>
    </div>
  </div>
  <div class="col-sm-6 col-lg-3">
    <div class="card">
      <div class="card-body">
        <div class="d-flex align-items-center">
          <div class="avatar">
            <div class="avatar-initial bg-success rounded shadow-xs">
              <i class="icon-base ri ri-book-2-line icon-24px"></i>
            </div>
          </div>
          <div class="ms-3">
            <p class="mb-0">Published Courses</p>
            <h5 class="mb-0"><?php echo number_format($stats['courses']); ?></h5>
          </div>
        </div>
      </div>
    </div>
  </div>
  <div class="col-sm-6 col-lg-3">
    <div class="card">
      <div class="card-body">
        <div class="d-flex align-items-center">
          <div class="avatar">
            <div class="avatar-initial bg-warning rounded shadow-xs">
              <i class="icon-base ri ri-team-line icon-24px"></i>
            </div>
          </div>
          <div class="ms-3">
            <p class="mb-0">Enrollments</p>
            <h5 class="mb-0"><?php echo number_format($stats['enrollments']); ?></h5>
          </div>
        </div>
      </div>
    </div>
  </div>
  <div class="col-sm-6 col-lg-3">
    <div class="card">
      <div class="card-body">
        <div class="d-flex align-items-center">
          <div class="avatar">
            <div class="avatar-initial bg-info rounded shadow-xs">
              <i class="icon-base ri ri-award-line icon-24px"></i>
            </div>
          </div>
          <div class="ms-3">
            <p class="mb-0">Certificates</p>
            <h5 class="mb-0"><?php echo number_format($stats['certificates']); ?></h5>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>

<div class="row">
  <div class="col-lg-8 mb-4">
    <div class="card">
      <div class="card-header">
        <h5 class="mb-0">Recent Enrollments</h5>
      </div>
      <div class="card-body">
        <?php if (!$recentEnrollments): ?>
          <p class="text-muted">No enrollments yet.</p>
        <?php else: ?>
          <div class="table-responsive">
            <table class="table table-striped">
              <thead>
                <tr>
                  <th>Student</th>
                  <th>Course</th>
                  <th>Status</th>
                  <th>Date</th>
                </tr>
              </thead>
              <tbody>
                <?php foreach ($recentEnrollments as $e): ?>
                  <tr>
                    <td><?php echo htmlspecialchars(($e['first_name'] ?? '') . ' ' . ($e['last_name'] ?? '')); ?></td>
                    <td><?php echo htmlspecialchars($e['course_title'] ?? ''); ?></td>
                    <td>
                      <span class="badge bg-<?php echo $e['status']==='active'?'success':($e['status']==='completed'?'info':'secondary'); ?>">
                        <?php echo htmlspecialchars(ucfirst($e['status'] ?? '')); ?>
                      </span>
                    </td>
                    <td><?php echo htmlspecialchars($e['enrollment_date'] ?? ''); ?></td>
                  </tr>
                <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        <?php endif; ?>
      </div>
    </div>
  </div>

  <div class="col-lg-4 mb-4">
    <div class="card">
      <div class="card-header">
        <h5 class="mb-0">Top Courses</h5>
      </div>
      <div class="card-body">
        <?php if (!$topCourses): ?>
          <p class="text-muted">No courses yet.</p>
        <?php else: ?>
          <?php foreach ($topCourses as $c): ?>
            <div class="d-flex justify-content-between align-items-center mb-3">
              <div>
                <h6 class="mb-0"><?php echo htmlspecialchars($c['title'] ?? ''); ?></h6>
                <small class="text-muted"><?php echo number_format((int)($c['enrollment_count'] ?? 0)); ?> enrollments</small>
              </div>
              <div class="text-end">
                <span class="badge bg-primary"><?php echo number_format((int)($c['enrollment_count'] ?? 0)); ?></span>
              </div>
            </div>
          <?php endforeach; ?>
        <?php endif; ?>
      </div>
    </div>
  </div>
</div>

<div class="row">
  <div class="col-12">
    <div class="card">
      <div class="card-header">
        <h5 class="mb-0">User Registrations (Last 6 Months)</h5>
      </div>
      <div class="card-body">
        <?php if (!$registrationsByMonth): ?>
          <p class="text-muted">No data available.</p>
        <?php else: ?>
          <div class="table-responsive">
            <table class="table table-striped">
              <thead>
                <tr>
                  <th>Month</th>
                  <th>New Users</th>
                </tr>
              </thead>
              <tbody>
                <?php foreach ($registrationsByMonth as $r): ?>
                  <tr>
                    <td><?php echo htmlspecialchars($r['month'] ?? ''); ?></td>
                    <td><?php echo (int)($r['count'] ?? 0); ?></td>
                  </tr>
                <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        <?php endif; ?>
      </div>
    </div>
  </div>
</div>

<?php include __DIR__ . '/includes/footer.php'; ?>
