<?php
session_start();

// Database connection
$host = 'localhost';
$dbname = 'jv_overseas';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Authentication check
if (!isset($_SESSION['user_id']) || $_SESSION['user_type'] !== 'admin') {
    header('Location: admin-login.php');
    exit;
}

// Get admin data
$stmt = $pdo->prepare("SELECT a.*, u.email FROM admins a JOIN users u ON a.id = u.id WHERE a.id = ?");
$stmt->execute([$_SESSION['user_id']]);
$admin = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$admin) {
    die("Admin profile not found.");
}

// Get date range
$date_from = $_GET['date_from'] ?? date('Y-m-01'); // First day of current month
$date_to = $_GET['date_to'] ?? date('Y-m-t'); // Last day of current month

// Get student statistics
$stats = [
    'total_students' => $pdo->query("SELECT COUNT(*) FROM students")->fetchColumn(),
    'new_students' => $pdo->query("SELECT COUNT(*) FROM students WHERE created_at BETWEEN '$date_from' AND '$date_to'")->fetchColumn(),
    'active_students' => $pdo->query("SELECT COUNT(*) FROM students WHERE status = 'active'")->fetchColumn(),
    'prospective_students' => $pdo->query("SELECT COUNT(*) FROM students WHERE status = 'prospective'")->fetchColumn(),
    'admitted_students' => $pdo->query("SELECT COUNT(*) FROM students WHERE status = 'admitted'")->fetchColumn(),
    'rejected_students' => $pdo->query("SELECT COUNT(*) FROM students WHERE status = 'rejected'")->fetchColumn(),
];

// Get students by country
$students_by_country = $pdo->query("
    SELECT c.name as country_name, COUNT(*) as count 
    FROM students s 
    JOIN countries c ON JSON_CONTAINS(s.preferred_countries, CONCAT('\"', c.name, '\"'))
    GROUP BY c.id, c.name 
    ORDER BY count DESC
")->fetchAll();

// Get students by education level
$students_by_level = $pdo->query("
    SELECT education_level, COUNT(*) as count 
    FROM students 
    WHERE education_level IS NOT NULL 
    GROUP BY education_level 
    ORDER BY count DESC
")->fetchAll();

// Get counselor performance
$counselor_performance = $pdo->query("
    SELECT 
        CONCAT(c.first_name, ' ', c.last_name) as counselor_name,
        c.performance_rating,
        COUNT(s.id) as student_count,
        (SELECT COUNT(*) FROM applications a WHERE a.counselor_id = c.id AND a.application_status = 'accepted') as accepted_count
    FROM counselors c
    LEFT JOIN students s ON c.id = s.assigned_counselor_id
    GROUP BY c.id
    ORDER BY c.performance_rating DESC
")->fetchAll();

// Get student conversion rates
$conversion_rates = [
    'prospective_to_active' => $stats['total_students'] > 0 ? round(($stats['active_students'] / $stats['total_students']) * 100, 1) : 0,
    'application_to_acceptance' => $pdo->query("SELECT COUNT(*) FROM applications WHERE application_status = 'accepted'")->fetchColumn(),
    'total_applications' => $pdo->query("SELECT COUNT(*) FROM applications")->fetchColumn(),
];

$conversion_rates['acceptance_rate'] = $conversion_rates['total_applications'] > 0 ? 
    round(($conversion_rates['application_to_acceptance'] / $conversion_rates['total_applications']) * 100, 1) : 0;

// Logout
if (isset($_GET['logout'])) {
    session_destroy();
    header('Location: admin-login.php');
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Student Reports - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Jost:wght@500;600;700&family=Open+Sans:wght@400;600&display=swap" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.10.0/css/all.min.css" rel="stylesheet">
    <link href="lib/owlcarousel/assets/owl.carousel.min.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
    <style>
        .stat-card {
            border-left: 5px solid #3fb7fe;
            margin-bottom: 20px;
        }
        .progress-container {
            background: #f8f9fa;
            border-radius: 10px;
            padding: 15px;
            margin-bottom: 20px;
        }
        .progress-label {
            display: flex;
            justify-content: space-between;
            margin-bottom: 10px;
        }
        .chart-container {
            height: 300px;
            margin-bottom: 30px;
        }
        .country-item {
            padding: 10px 15px;
            border-bottom: 1px solid #eee;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        .country-item:last-child {
            border-bottom: none;
        }
        .country-name {
            font-weight: 600;
        }
        .country-count {
            background: #e9ecef;
            padding: 5px 10px;
            border-radius: 15px;
            font-weight: 600;
        }
    </style>
</head>
<body>

<?php include 'admin-navbar.php'; ?>

<div class="container py-5">
    <div class="section-title position-relative mb-4">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Reports</h6>
        <h1 class="display-5">Student Analytics Report</h1>
    </div>

    <!-- Date Range Filter -->
    <div class="row mb-4">
        <div class="col-lg-6">
            <form method="GET" class="d-flex">
                <div class="flex-grow-1 mr-2">
                    <label>Date From</label>
                    <input type="date" name="date_from" class="form-control" value="<?= htmlspecialchars($date_from) ?>">
                </div>
                <div class="flex-grow-1 mr-2">
                    <label>Date To</label>
                    <input type="date" name="date_to" class="form-control" value="<?= htmlspecialchars($date_to) ?>">
                </div>
                <div class="align-self-end">
                    <button type="submit" class="btn btn-primary">Filter</button>
                </div>
            </form>
        </div>
    </div>

    <!-- Summary Stats -->
    <div class="row mb-4">
        <div class="col-md-3 mb-3">
            <div class="stat-card">
                <div class="card-body">
                    <h6>Total Students</h6>
                    <h3><?= $stats['total_students'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #28a745;">
                <div class="card-body">
                    <h6>New Students</h6>
                    <h3><?= $stats['new_students'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #007bff;">
                <div class="card-body">
                    <h6>Active Students</h6>
                    <h3><?= $stats['active_students'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #ffc107;">
                <div class="card-body">
                    <h6>Admitted Students</h6>
                    <h3><?= $stats['admitted_students'] ?></h3>
                </div>
            </div>
        </div>
    </div>

    <!-- Conversion Rates -->
    <div class="progress-container">
        <div class="progress-label">
            <span>Prospective to Active Conversion Rate</span>
            <span><?= $conversion_rates['prospective_to_active'] ?>%</span>
        </div>
        <div class="progress" style="height: 20px;">
            <div class="progress-bar bg-success" role="progressbar" 
                 style="width: <?= $conversion_rates['prospective_to_active'] ?>%;" 
                 aria-valuenow="<?= $conversion_rates['prospective_to_active'] ?>" aria-valuemin="0" aria-valuemax="100">
                <?= $conversion_rates['prospective_to_active'] ?>%
            </div>
        </div>
    </div>

    <div class="progress-container">
        <div class="progress-label">
            <span>Application to Acceptance Rate</span>
            <span><?= $conversion_rates['acceptance_rate'] ?>%</span>
        </div>
        <div class="progress" style="height: 20px;">
            <div class="progress-bar bg-info" role="progressbar" 
                 style="width: <?= $conversion_rates['acceptance_rate'] ?>%;" 
                 aria-valuenow="<?= $conversion_rates['acceptance_rate'] ?>" aria-valuemin="0" aria-valuemax="100">
                <?= $conversion_rates['acceptance_rate'] ?>%
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-lg-6">
            <!-- Students by Country -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Demographics</h6>
                <h1 class="display-6">Students by Preferred Country</h1>
            </div>
            <div class="bg-white p-4 rounded">
                <?php foreach ($students_by_country as $country): ?>
                <div class="country-item">
                    <span class="country-name"><?= htmlspecialchars($country['country_name']) ?></span>
                    <span class="country-count"><?= $country['count'] ?></span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>

        <div class="col-lg-6">
            <!-- Students by Education Level -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Academic Background</h6>
                <h1 class="display-6">Students by Education Level</h1>
            </div>
            <div class="bg-white p-4 rounded">
                <?php foreach ($students_by_level as $level): ?>
                <div class="country-item">
                    <span class="country-name"><?= htmlspecialchars($level['education_level']) ?></span>
                    <span class="country-count"><?= $level['count'] ?></span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>

    <!-- Counselor Performance -->
    <div class="section-title position-relative mb-4 mt-5">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Performance</h6>
        <h1 class="display-6">Counselor Performance</h1>
    </div>
    <div class="table-responsive">
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Counselor</th>
                    <th>Rating</th>
                    <th>Students</th>
                    <th>Acceptances</th>
                    <th>Acceptance Rate</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($counselor_performance as $counselor): ?>
                <tr>
                    <td><?= htmlspecialchars($counselor['counselor_name']) ?></td>
                    <td>
                        <div class="rating">
                            <?php for ($i = 1; $i <= 5; $i++): ?>
                                <i class="fas <?= $i <= $counselor['performance_rating'] ? 'fa-star' : 'fa-star-half-alt' ?>"></i>
                            <?php endfor; ?>
                        </div>
                    </td>
                    <td><?= $counselor['student_count'] ?></td>
                    <td><?= $counselor['accepted_count'] ?></td>
                    <td>
                        <?php 
                        $rate = $counselor['student_count'] > 0 ? round(($counselor['accepted_count'] / $counselor['student_count']) * 100, 1) : 0;
                        ?>
                        <?= $rate ?>%
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <div class="text-center mt-5">
        <a href="#" class="btn btn-primary">Export Full Report (PDF)</a>
        <a href="#" class="btn btn-outline-primary">Export Data (CSV)</a>
    </div>
</div>

<?php include 'footer.php'; ?>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.bundle.min.js"></script>
<script>
    document.getElementById('year').textContent = new Date().getFullYear();
</script>
</body>
</html>