<?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 counselor performance statistics
$performance_stats = $pdo->query("
    SELECT 
        c.id,
        CONCAT(c.first_name, ' ', c.last_name) as counselor_name,
        c.performance_rating,
        c.max_students,
        COUNT(s.id) as current_students,
        (SELECT COUNT(*) FROM applications a WHERE a.counselor_id = c.id) as total_applications,
        (SELECT COUNT(*) FROM applications a WHERE a.counselor_id = c.id AND a.application_status = 'accepted') as accepted_applications,
        (SELECT COUNT(*) FROM appointments ap WHERE ap.counselor_id = c.id AND ap.scheduled_time BETWEEN '$date_from' AND '$date_to') as appointments_this_period,
        (SELECT COUNT(*) FROM tasks t WHERE t.assigned_to = c.id AND t.status = 'completed' AND t.completed_at BETWEEN '$date_from' AND '$date_to') as completed_tasks
    FROM counselors c
    LEFT JOIN students s ON c.id = s.assigned_counselor_id AND s.status = 'active'
    GROUP BY c.id
    ORDER BY c.performance_rating DESC
")->fetchAll();

// Calculate overall metrics
$total_counselors = count($performance_stats);
$avg_rating = $total_counselors > 0 ? 
    round(array_sum(array_column($performance_stats, 'performance_rating')) / $total_counselors, 2) : 0;

// Get top performing counselors
$top_counselors = array_slice($performance_stats, 0, 3);

// Get counselor utilization
$utilization_rates = [];
foreach ($performance_stats as $counselor) {
    $utilization = $counselor['max_students'] > 0 ? 
        round(($counselor['current_students'] / $counselor['max_students']) * 100, 1) : 0;
    $utilization_rates[] = $utilization;
}

$avg_utilization = count($utilization_rates) > 0 ? 
    round(array_sum($utilization_rates) / count($utilization_rates), 1) : 0;

// Get acceptance rates by counselor
$acceptance_rates = [];
foreach ($performance_stats as $counselor) {
    $rate = $counselor['total_applications'] > 0 ? 
        round(($counselor['accepted_applications'] / $counselor['total_applications']) * 100, 1) : 0;
    $acceptance_rates[] = [
        'counselor_name' => $counselor['counselor_name'],
        'rate' => $rate,
        'total' => $counselor['total_applications'],
        'accepted' => $counselor['accepted_applications']
    ];
}

// Sort by acceptance rate
usort($acceptance_rates, function($a, $b) {
    return $b['rate'] - $a['rate'];
});

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

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Counselor Performance - 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;
        }
        .rating {
            color: #ffc107;
        }
        .utilization {
            height: 20px;
            background: #f8f9fa;
            border-radius: 10px;
            overflow: hidden;
            margin: 10px 0;
        }
        .utilization-bar {
            height: 100%;
            background: #3fb7fe;
            transition: width 0.3s ease;
        }
        .performance-badge {
            font-size: 0.9em;
            padding: 5px 10px;
            border-radius: 20px;
        }
        .performance-excellent {
            background: #d4edda;
            color: #155724;
        }
        .performance-good {
            background: #d1ecf1;
            color: #0c5460;
        }
        .performance-avg {
            background: #fff3cd;
            color: #856404;
        }
        .performance-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">Counselor Performance 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>

    <!-- Performance Summary -->
    <div class="row mb-4">
        <div class="col-md-3 mb-3">
            <div class="stat-card">
                <div class="card-body">
                    <h6>Total Counselors</h6>
                    <h3><?= $total_counselors ?></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>Avg. Rating</h6>
                    <h3><?= $avg_rating ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #3fb7fe;">
                <div class="card-body">
                    <h6>Avg. Utilization</h6>
                    <h3><?= $avg_utilization ?>%</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>Top Counselor</h6>
                    <h3><?= htmlspecialchars($top_counselors[0]['counselor_name'] ?? 'N/A') ?></h3>
                </div>
            </div>
        </div>
    </div>

    <!-- Top Performing Counselors -->
    <div class="section-title position-relative mb-4">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Top Performers</h6>
        <h1 class="display-6">Top 3 Counselors</h1>
    </div>
    <div class="row">
        <?php foreach ($top_counselors as $index => $counselor): ?>
        <div class="col-md-4 mb-4">
            <div class="card text-center">
                <div class="card-body">
                    <div class="position-absolute" style="top: 10px; right: 10px;">
                        <span class="badge badge-primary">##<?= $index + 1 ?></span>
                    </div>
                    <h5><?= htmlspecialchars($counselor['counselor_name']) ?></h5>
                    <div class="rating mb-3">
                        <?php for ($i = 1; $i <= 5; $i++): ?>
                            <i class="fas <?= $i <= $counselor['performance_rating'] ? 'fa-star' : 'fa-star-half-alt' ?>"></i>
                        <?php endfor; ?>
                    </div>
                    <p class="text-muted">Rating: <?= $counselor['performance_rating'] ?>/5.0</p>
                    <div class="small">
                        <p>Students: <?= $counselor['current_students'] ?>/<?= $counselor['max_students'] ?></p>
                        <p>Applications: <?= $counselor['total_applications'] ?></p>
                        <p>Appointments: <?= $counselor['appointments_this_period'] ?></p>
                    </div>
                </div>
            </div>
        </div>
        <?php endforeach; ?>
    </div>

    <!-- Counselor Performance Table -->
    <div class="section-title position-relative mb-4 mt-5">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Detailed Performance</h6>
        <h1 class="display-6">All 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>Utilization</th>
                    <th>Applications</th>
                    <th>Acceptance Rate</th>
                    <th>Appointments</th>
                    <th>Tasks</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($performance_stats 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['current_students'] ?>/<?= $counselor['max_students'] ?>
                        <div class="utilization">
                            <?php 
                            $utilization = $counselor['max_students'] > 0 ? 
                                round(($counselor['current_students'] / $counselor['max_students']) * 100, 1) : 0;
                            ?>
                            <div class="utilization-bar" style="width: <?= min($utilization, 100) ?>%"></div>
                        </div>
                    </td>
                    <td><?= $counselor['total_applications'] ?></td>
                    <td>
                        <?php 
                        $acceptance_rate = $counselor['total_applications'] > 0 ? 
                            round(($counselor['accepted_applications'] / $counselor['total_applications']) * 100, 1) : 0;
                        $badge_class = $acceptance_rate >= 70 ? 'performance-excellent' : 
                                     ($acceptance_rate >= 50 ? 'performance-good' : 
                                     ($acceptance_rate >= 30 ? 'performance-avg' : 'performance-low'));
                        ?>
                        <span class="performance-badge <?= $badge_class ?>">
                            <?= $acceptance_rate ?>%
                        </span>
                    </td>
                    <td><?= $counselor['appointments_this_period'] ?></td>
                    <td><?= $counselor['completed_tasks'] ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <!-- Acceptance Rates by Counselor -->
    <div class="section-title position-relative mb-4 mt-5">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Application Success</h6>
        <h1 class="display-6">Acceptance Rates by Counselor</h1>
    </div>
    <div class="table-responsive">
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Counselor</th>
                    <th>Total Applications</th>
                    <th>Accepted</th>
                    <th>Acceptance Rate</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($acceptance_rates as $rate): ?>
                <tr>
                    <td><?= htmlspecialchars($rate['counselor_name']) ?></td>
                    <td><?= $rate['total'] ?></td>
                    <td><?= $rate['accepted'] ?></td>
                    <td>
                        <?php 
                        $badge_class = $rate['rate'] >= 70 ? 'performance-excellent' : 
                                     ($rate['rate'] >= 50 ? 'performance-good' : 
                                     ($rate['rate'] >= 30 ? 'performance-avg' : 'performance-low'));
                        ?>
                        <span class="performance-badge <?= $badge_class ?>">
                            <?= $rate['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>