<?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 application statistics
$stats = [
    'total_applications' => $pdo->query("SELECT COUNT(*) FROM applications")->fetchColumn(),
    'new_applications' => $pdo->query("SELECT COUNT(*) FROM applications WHERE created_at BETWEEN '$date_from' AND '$date_to'")->fetchColumn(),
    'submitted_applications' => $pdo->query("SELECT COUNT(*) FROM applications WHERE application_status = 'submitted'")->fetchColumn(),
    'under_review_applications' => $pdo->query("SELECT COUNT(*) FROM applications WHERE application_status = 'under_review'")->fetchColumn(),
    'accepted_applications' => $pdo->query("SELECT COUNT(*) FROM applications WHERE application_status = 'accepted'")->fetchColumn(),
    'rejected_applications' => $pdo->query("SELECT COUNT(*) FROM applications WHERE application_status = 'rejected'")->fetchColumn(),
];

// Get applications by country
$applications_by_country = $pdo->query("
    SELECT 
        c.name as country_name,
        COUNT(*) as total_count,
        SUM(CASE WHEN a.application_status = 'accepted' THEN 1 ELSE 0 END) as accepted_count,
        SUM(CASE WHEN a.application_status = 'rejected' THEN 1 ELSE 0 END) as rejected_count
    FROM applications a
    JOIN programs p ON a.program_id = p.id
    JOIN universities u ON p.university_id = u.id
    JOIN countries c ON u.country_id = c.id
    GROUP BY c.id, c.name
    ORDER BY total_count DESC
")->fetchAll();

// Get applications by program level
$applications_by_level = $pdo->query("
    SELECT 
        p.degree_level,
        COUNT(*) as total_count,
        SUM(CASE WHEN a.application_status = 'accepted' THEN 1 ELSE 0 END) as accepted_count
    FROM applications a
    JOIN programs p ON a.program_id = p.id
    GROUP BY p.degree_level
    ORDER BY total_count DESC
")->fetchAll();

// Get top universities by applications
$top_universities = $pdo->query("
    SELECT 
        u.name as university_name,
        u.logo,
        COUNT(*) as application_count,
        SUM(CASE WHEN a.application_status = 'accepted' THEN 1 ELSE 0 END) as acceptance_count
    FROM applications a
    JOIN programs p ON a.program_id = p.id
    JOIN universities u ON p.university_id = u.id
    GROUP BY u.id
    ORDER BY application_count DESC
    LIMIT 10
")->fetchAll();

// Calculate overall acceptance rate
$acceptance_rate = $stats['total_applications'] > 0 ? 
    round(($stats['accepted_applications'] / $stats['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>Application 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;
        }
        .university-logo {
            height: 40px;
            object-fit: contain;
            background: white;
            padding: 3px;
            border-radius: 5px;
        }
        .rate-badge {
            font-size: 0.9em;
            padding: 5px 10px;
            border-radius: 20px;
        }
        .rate-high {
            background: #d4edda;
            color: #155724;
        }
        .rate-medium {
            background: #fff3cd;
            color: #856404;
        }
        .rate-low {
            background: #f8d7da;
            color: #721c24;
        }
    </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">Application 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 Applications</h6>
                    <h3><?= $stats['total_applications'] ?></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 Applications</h6>
                    <h3><?= $stats['new_applications'] ?></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>Under Review</h6>
                    <h3><?= $stats['under_review_applications'] ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #dc3545;">
                <div class="card-body">
                    <h6>Rejected</h6>
                    <h3><?= $stats['rejected_applications'] ?></h3>
                </div>
            </div>
        </div>
    </div>

    <!-- Overall Acceptance Rate -->
    <div class="progress-container">
        <div class="progress-label">
            <span>Overall Acceptance Rate</span>
            <span><?= $acceptance_rate ?>%</span>
        </div>
        <div class="progress" style="height: 20px;">
            <div class="progress-bar bg-success" role="progressbar" 
                 style="width: <?= $acceptance_rate ?>%;" 
                 aria-valuenow="<?= $acceptance_rate ?>" aria-valuemin="0" aria-valuemax="100">
                <?= $acceptance_rate ?>%
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-lg-6">
            <!-- Applications by Country -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Geographic Distribution</h6>
                <h1 class="display-6">Applications by Country</h1>
            </div>
            <div class="table-responsive">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Country</th>
                            <th>Total</th>
                            <th>Accepted</th>
                            <th>Rate</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($applications_by_country as $country): ?>
                        <tr>
                            <td><?= htmlspecialchars($country['country_name']) ?></td>
                            <td><?= $country['total_count'] ?></td>
                            <td><?= $country['accepted_count'] ?></td>
                            <td>
                                <?php 
                                $rate = $country['total_count'] > 0 ? round(($country['accepted_count'] / $country['total_count']) * 100, 1) : 0;
                                $badge_class = $rate >= 70 ? 'rate-high' : ($rate >= 50 ? 'rate-medium' : 'rate-low');
                                ?>
                                <span class="rate-badge <?= $badge_class ?>">
                                    <?= $rate ?>%
                                </span>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <div class="col-lg-6">
            <!-- Applications by Program Level -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Academic Level</h6>
                <h1 class="display-6">Applications by Program Level</h1>
            </div>
            <div class="table-responsive">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Level</th>
                            <th>Total</th>
                            <th>Accepted</th>
                            <th>Rate</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($applications_by_level as $level): ?>
                        <tr>
                            <td><?= ucfirst($level['degree_level']) ?></td>
                            <td><?= $level['total_count'] ?></td>
                            <td><?= $level['accepted_count'] ?></td>
                            <td>
                                <?php 
                                $rate = $level['total_count'] > 0 ? round(($level['accepted_count'] / $level['total_count']) * 100, 1) : 0;
                                $badge_class = $rate >= 70 ? 'rate-high' : ($rate >= 50 ? 'rate-medium' : 'rate-low');
                                ?>
                                <span class="rate-badge <?= $badge_class ?>">
                                    <?= $rate ?>%
                                </span>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- Top Universities -->
    <div class="section-title position-relative mb-4 mt-5">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">University Performance</h6>
        <h1 class="display-6">Top Universities by Application Volume</h1>
    </div>
    <div class="table-responsive">
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>University</th>
                    <th>Applications</th>
                    <th>Acceptances</th>
                    <th>Acceptance Rate</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($top_universities as $uni): ?>
                <tr>
                    <td>
                        <div class="d-flex align-items-center">
                            <img src="<?= htmlspecialchars($uni['logo'] ?: 'img/university-placeholder.png') ?>" 
                                 alt="<?= htmlspecialchars($uni['university_name']) ?>" class="university-logo mr-2">
                            <?= htmlspecialchars($uni['university_name']) ?>
                        </div>
                    </td>
                    <td><?= $uni['application_count'] ?></td>
                    <td><?= $uni['acceptance_count'] ?></td>
                    <td>
                        <?php 
                        $rate = $uni['application_count'] > 0 ? round(($uni['acceptance_count'] / $uni['application_count']) * 100, 1) : 0;
                        $badge_class = $rate >= 70 ? 'rate-high' : ($rate >= 50 ? 'rate-medium' : 'rate-low');
                        ?>
                        <span class="rate-badge <?= $badge_class ?>">
                            <?= $rate ?>%
                        </span>
                    </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>