<?php
$db = getDB();

// ---------- 1. Total users ----------
$totalUsers = $db->query("SELECT COUNT(*) FROM users")->fetch_row()[0];

// ---------- 2. Total courses ----------
$totalCourses = $db->query("SELECT COUNT(*) FROM courses")->fetch_row()[0];

// ---------- 3. Active subscriptions ----------
$activeSubs = $db->query("SELECT COUNT(*) FROM subscriptions WHERE status = 'active'")->fetch_row()[0];

// ---------- 4. Total successful revenue (BDT) ----------
$result = $db->query("SELECT COALESCE(SUM(amount), 0) AS total FROM payment_transactions WHERE status = 'success'");
$totalRevenue = $result->fetch_assoc()['total'];

// ---------- 5. New users in the last 7 days ----------
$newUsers = $db->query("SELECT COUNT(*) FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)")->fetch_row()[0];

// ---------- 6. Revenue this month (successful transactions) ----------
$result = $db->query("SELECT COALESCE(SUM(amount), 0) AS month_rev 
                       FROM payment_transactions 
                       WHERE status = 'success' 
                         AND MONTH(created_at) = MONTH(CURRENT_DATE()) 
                         AND YEAR(created_at) = YEAR(CURRENT_DATE())");
$monthlyRevenue = $result->fetch_assoc()['month_rev'];

// ---------- 7. Additional interesting stats ----------
$totalLessons = $db->query("SELECT COUNT(*) FROM lessons")->fetch_row()[0];
$totalFlashcards = $db->query("SELECT COUNT(*) FROM flashcards")->fetch_row()[0];
$totalExamQuestions = $db->query("SELECT COUNT(*) FROM exam_questions")->fetch_row()[0];
?>

<!-- Inline styles for gradient backgrounds and hover effects -->
<style>
  .card-gradient {
    border: none;
    border-radius: 1rem;
    box-shadow: 0 4px 20px rgba(0,0,0,0.08);
    transition: transform 0.2s ease, box-shadow 0.2s ease;
  }
  .card-gradient:hover {
    transform: translateY(-3px);
    box-shadow: 0 8px 25px rgba(0,0,0,0.15);
  }
  .card-gradient .icon-circle {
    width: 55px;
    height: 55px;
    border-radius: 50%;
    display: flex;
    align-items: center;
    justify-content: center;
    font-size: 1.8rem;
    color: #fff;
    margin-bottom: 1rem;
  }
  .card-gradient h2 {
    font-weight: 700;
    margin-bottom: 0.25rem;
  }
  .card-gradient .card-title {
    font-size: 0.95rem;
    text-transform: uppercase;
    letter-spacing: 0.5px;
    opacity: 0.9;
  }

  /* Gradient backgrounds for different card types */
  .bg-gradient-blue   { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: #fff; }
  .bg-gradient-green  { background: linear-gradient(135deg, #11998e 0%, #38ef7d 100%); color: #fff; }
  .bg-gradient-orange { background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); color: #fff; }
  .bg-gradient-teal   { background: linear-gradient(135deg, #0fd6c5 0%, #0091b8 100%); color: #fff; }
  .bg-gradient-purple { background: linear-gradient(135deg, #a18cd1 0%, #fbc2eb 100%); color: #333; }
  .bg-gradient-sunset { background: linear-gradient(135deg, #f6d365 0%, #fda085 100%); color: #333; }
  .bg-gradient-dark   { background: linear-gradient(135deg, #434343 0%, #000000 100%); color: #fff; }
  .bg-gradient-light  { background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%); color: #333; }
</style>

<h3 class="mb-4 fw-bold">Dashboard Overview</h3>

<!-- First row: main KPIs -->
<div class="row g-4 mb-4">
    <div class="col-md-4 col-lg-4">
        <div class="card card-gradient bg-gradient-blue p-4">
            <div class="icon-circle bg-white bg-opacity-25">
                <i class="fa fa-users"></i>
            </div>
            <div>
                <h5 class="card-title">Total Users</h5>
                <h2><?= number_format($totalUsers) ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-4 col-lg-4">
        <div class="card card-gradient bg-gradient-green p-4">
            <div class="icon-circle bg-white bg-opacity-25">
                <i class="fa fa-book"></i>
            </div>
            <div>
                <h5 class="card-title">Courses</h5>
                <h2><?= number_format($totalCourses) ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-4 col-lg-4">
        <div class="card card-gradient bg-gradient-orange p-4">
            <div class="icon-circle bg-white bg-opacity-25">
                <i class="fa fa-check-circle"></i>
            </div>
            <div>
                <h5 class="card-title">Active Subscriptions</h5>
                <h2><?= number_format($activeSubs) ?></h2>
            </div>
        </div>
    </div>
</div>

<!-- Revenue row -->
<div class="row g-4 mb-4">
    <div class="col-md-6">
        <div class="card card-gradient bg-gradient-teal p-4">
            <div class="icon-circle bg-white bg-opacity-25">
                <i class="fa fa-money-bill"></i>
            </div>
            <div>
                <h5 class="card-title">Total Revenue</h5>
                <h2><?= number_format($totalRevenue, 2) ?> BDT</h2>
            </div>
        </div>
    </div>
    <div class="col-md-6">
        <div class="card card-gradient bg-gradient-purple p-4">
            <div class="icon-circle bg-white bg-opacity-25">
                <i class="fa fa-calendar"></i>
            </div>
            <div>
                <h5 class="card-title">Revenue This Month</h5>
                <h2><?= number_format($monthlyRevenue, 2) ?> BDT</h2>
            </div>
        </div>
    </div>
</div>

<!-- Content stats -->
<div class="row g-4 mb-4">
    <div class="col-md-6 col-lg-3">
        <div class="card card-gradient bg-gradient-light p-4">
            <div class="icon-circle bg-dark bg-opacity-10 text-dark">
                <i class="fa fa-user-plus"></i>
            </div>
            <div>
                <h5 class="card-title">New Users (7d)</h5>
                <h2><?= $newUsers ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-lg-3">
        <div class="card card-gradient bg-gradient-light p-4">
            <div class="icon-circle bg-dark bg-opacity-10 text-dark">
                <i class="fa fa-list-alt"></i>
            </div>
            <div>
                <h5 class="card-title">Lessons</h5>
                <h2><?= number_format($totalLessons) ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-lg-3">
        <div class="card card-gradient bg-gradient-light p-4">
            <div class="icon-circle bg-dark bg-opacity-10 text-dark">
                <i class="fa fa-clone"></i>
            </div>
            <div>
                <h5 class="card-title">Flashcards</h5>
                <h2><?= number_format($totalFlashcards) ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-lg-3">
        <div class="card card-gradient bg-gradient-light p-4">
            <div class="icon-circle bg-dark bg-opacity-10 text-dark">
                <i class="fa fa-question-circle"></i>
            </div>
            <div>
                <h5 class="card-title">Exam Questions</h5>
                <h2><?= number_format($totalExamQuestions) ?></h2>
            </div>
        </div>
    </div>
</div>