<?php
session_start();
require_once 'config.php';

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

// Get counselor data
$stmt = $pdo->prepare("SELECT first_name, last_name FROM counselors WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$counselor = $stmt->fetch();

// Get stats - fixed by separating prepare, execute, and fetch
$stats = [
    'my_students' => 0,
    'active_applications' => 0,
    'accepted_applications' => 0,
    'total_applications' => 0
];

// My Students count
$stmt = $pdo->prepare("SELECT COUNT(*) FROM students WHERE assigned_counselor_id = ?");
$stmt->execute([$_SESSION['user_id']]);
$stats['my_students'] = $stmt->fetchColumn();

// Active Applications count
$stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ? AND application_status IN ('submitted', 'under_review', 'interview_scheduled')");
$stmt->execute([$_SESSION['user_id']]);
$stats['active_applications'] = $stmt->fetchColumn();

// Accepted Applications count
$stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ? AND application_status IN ('accepted', 'conditionally_accepted')");
$stmt->execute([$_SESSION['user_id']]);
$stats['accepted_applications'] = $stmt->fetchColumn();

// Total Applications count
$stmt = $pdo->prepare("SELECT COUNT(*) FROM applications WHERE counselor_id = ?");
$stmt->execute([$_SESSION['user_id']]);
$stats['total_applications'] = $stmt->fetchColumn();

// Calculate acceptance rate
$acceptance_rate = $stats['total_applications'] > 0 ? 
    ($stats['accepted_applications'] / $stats['total_applications']) * 100 : 0;

// Get top universities
$top_universities = $pdo->prepare("SELECT u.name, u.logo, COUNT(*) as count
                                  FROM applications a
                                  JOIN programs p ON a.program_id = p.id
                                  JOIN universities u ON p.university_id = u.id
                                  WHERE a.counselor_id = ?
                                  GROUP BY u.id
                                  ORDER BY count DESC
                                  LIMIT 5");
$top_universities->execute([$_SESSION['user_id']]);
$top_universities = $top_universities->fetchAll();

// Get application status distribution
$status_distribution = $pdo->prepare("SELECT application_status, COUNT(*) as count
                                     FROM applications
                                     WHERE counselor_id = ?
                                     GROUP BY application_status");
$status_distribution->execute([$_SESSION['user_id']]);
$status_distribution = $status_distribution->fetchAll();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Reports - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <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="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;
            width: 40px;
            object-fit: contain;
            margin-right: 15px;
        }
    </style>
</head>
<body>

<?php include 'counselor-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">Performance Reports for <?= htmlspecialchars($counselor['first_name']) ?></h1>
    </div>

    <div class="row">
        <div class="col-md-3 mb-4">
            <div class="card stat-card">
                <div class="card-body">
                    <h5>My Students</h5>
                    <h2><?= $stats['my_students'] ?></h2>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-4">
            <div class="card stat-card" style="border-left-color: #28a745;">
                <div class="card-body">
                    <h5>Accepted</h5>
                    <h2><?= $stats['accepted_applications'] ?></h2>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-4">
            <div class="card stat-card" style="border-left-color: #ffc107;">
                <div class="card-body">
                    <h5>Pending</h5>
                    <h2><?= $stats['active_applications'] ?></h2>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-4">
            <div class="card stat-card" style="border-left-color: #dc3545;">
                <div class="card-body">
                    <h5>Acceptance Rate</h5>
                    <h2><?= round($acceptance_rate) ?>%</h2>
                </div>
            </div>
        </div>
    </div>

    <!-- Acceptance Rate Progress -->
    <div class="progress-container">
        <div class="progress-label">
            <span>Overall Acceptance Rate</span>
            <span><?= round($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">
                <?= round($acceptance_rate) ?>%
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-lg-6 mb-4">
            <!-- Application Status Distribution -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Application Status</h6>
                <h1 class="display-6">Distribution</h1>
            </div>
            <div class="table-responsive">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Status</th>
                            <th>Count</th>
                            <th>Percentage</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($status_distribution as $status): 
                            $percentage = $stats['total_applications'] > 0 ? 
                                ($status['count'] / $stats['total_applications']) * 100 : 0;
                        ?>
                        <tr>
                            <td><?= ucfirst(str_replace('_', ' ', $status['application_status'])) ?></td>
                            <td><?= $status['count'] ?></td>
                            <td><?= round($percentage, 1) ?>%</td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <div class="col-lg-6 mb-4">
            <!-- Top Universities -->
            <div class="section-title position-relative mb-4">
                <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Top Universities</h6>
                <h1 class="display-6">Most Applied To</h1>
            </div>
            <div class="list-group">
                <?php foreach ($top_universities as $uni): ?>
                <a href="#" class="list-group-item list-group-item-action d-flex justify-content-between align-items-center">
                    <div class="d-flex align-items-center">
                        <img src="<?= htmlspecialchars($uni['logo'] ?: 'img/university-placeholder.png') ?>" 
                             alt="<?= htmlspecialchars($uni['name']) ?>" class="university-logo">
                        <?= htmlspecialchars($uni['name']) ?>
                    </div>
                    <span class="badge badge-primary badge-pill"><?= $uni['count'] ?></span>
                </a>
                <?php endforeach; ?>
            </div>
        </div>
    </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>
</body>
</html>