<?php
require_once '../config/config.php';

// Require admin login
requireRole('secretariat');

$pageTitle = 'Employee List - Secretariat - PSAU GAD';
$db = getDB();

// Get filter parameters
$search = get('search', '');
$category = get('category', 'all');
$gender = get('gender', 'all');

// Build query
$query = "
    SELECT 
        u.user_id,
        u.email,
        u.is_approved,
        u.is_active,
        ep.*,
        COUNT(DISTINCT ar.registration_id) as total_registrations,
        COUNT(DISTINCT c.certificate_id) as total_certificates
    FROM users u
    JOIN employee_profiles ep ON u.user_id = ep.user_id
    LEFT JOIN activity_registrations ar ON u.user_id = ar.user_id
    LEFT JOIN certificates c ON u.user_id = c.user_id
    WHERE u.role = 'employee'
";

$params = [];

if (!empty($search)) {
    $query .= " AND (ep.first_name LIKE ? OR ep.last_name LIKE ? OR u.email LIKE ?)";
    $params[] = "%$search%";
    $params[] = "%$search%";
    $params[] = "%$search%";
}

if ($category !== 'all') {
    $query .= " AND ep.category = ?";
    $params[] = $category;
}

if ($gender !== 'all') {
    $query .= " AND ep.gender = ?";
    $params[] = $gender;
}

$query .= " GROUP BY u.user_id ORDER BY ep.last_name ASC, ep.first_name ASC";

$stmt = $db->prepare($query);
$stmt->execute($params);
$employees = $stmt->fetchAll();

// Get category statistics
$stmt = $db->query("
    SELECT 
        ep.category,
        ep.gender,
        COUNT(*) as count
    FROM employee_profiles ep
    JOIN users u ON ep.user_id = u.user_id
    WHERE u.role = 'employee'
    GROUP BY ep.category, ep.gender
");
$categoryStats = $stmt->fetchAll();

// Organize stats by category
$stats = [];
foreach ($categoryStats as $stat) {
    if (!isset($stats[$stat['category']])) {
        $stats[$stat['category']] = ['Male' => 0, 'Female' => 0, 'Other' => 0, 'total' => 0];
    }
    $stats[$stat['category']][$stat['gender']] = $stat['count'];
    $stats[$stat['category']]['total'] += $stat['count'];
}

$categories = ['Faculty', 'Non-Teaching', 'Service Personnel', 'Other'];

include '../includes/header.php';
include '../includes/navbar.php';
?>

<div class="min-h-screen bg-gray-50 py-8">
    <div class="container mx-auto px-4">
        <!-- Page Header -->
        <div class="mb-8">
            <h1 class="text-3xl font-bold text-gray-900 mb-2">
                <i class="fas fa-users text-secretariat mr-3"></i>
                Employee List
            </h1>
            <p class="text-gray-600">View and manage employee profiles</p>
        </div>

        <!-- Category Statistics -->
        <div class="grid grid-cols-1 md:grid-cols-4 gap-6 mb-8">
            <?php foreach ($categories as $cat): ?>
                <?php $catStats = $stats[$cat] ?? ['Male' => 0, 'Female' => 0, 'Other' => 0, 'total' => 0]; ?>
                <div class="bg-white rounded-xl shadow-lg p-6">
                    <h3 class="font-semibold text-gray-700 mb-3"><?php echo $cat; ?></h3>
                    <p class="text-3xl font-bold text-secretariat mb-3"><?php echo $catStats['total']; ?></p>
                    <div class="space-y-1 text-sm text-gray-600">
                        <div class="flex items-center justify-between">
                            <span><i class="fas fa-male mr-1 text-blue-500"></i> Male</span>
                            <span class="font-semibold"><?php echo $catStats['Male']; ?></span>
                        </div>
                        <div class="flex items-center justify-between">
                            <span><i class="fas fa-female mr-1 text-pink-500"></i> Female</span>
                            <span class="font-semibold"><?php echo $catStats['Female']; ?></span>
                        </div>
                        <?php if ($catStats['Other'] > 0): ?>
                            <div class="flex items-center justify-between">
                                <span><i class="fas fa-user mr-1 text-gray-500"></i> Other</span>
                                <span class="font-semibold"><?php echo $catStats['Other']; ?></span>
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
            <?php endforeach; ?>
        </div>

        <!-- Filters -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <form method="GET" class="grid grid-cols-1 md:grid-cols-4 gap-4">
                <!-- Search -->
                <div>
                    <label class="block text-sm font-semibold text-gray-700 mb-2">Search</label>
                    <input type="text" 
                           name="search" 
                           value="<?php echo htmlspecialchars($search); ?>"
                           placeholder="Search by name or email..."
                           class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-secretariat focus:border-transparent">
                </div>

                <!-- Category Filter -->
                <div>
                    <label class="block text-sm font-semibold text-gray-700 mb-2">Category</label>
                    <select name="category" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-secretariat focus:border-transparent">
                        <option value="all" <?php echo $category === 'all' ? 'selected' : ''; ?>>All Categories</option>
                        <?php foreach ($categories as $cat): ?>
                            <option value="<?php echo $cat; ?>" <?php echo $category === $cat ? 'selected' : ''; ?>>
                                <?php echo $cat; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <!-- Gender Filter -->
                <div>
                    <label class="block text-sm font-semibold text-gray-700 mb-2">Gender</label>
                    <select name="gender" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-secretariat focus:border-transparent">
                        <option value="all" <?php echo $gender === 'all' ? 'selected' : ''; ?>>All Genders</option>
                        <option value="Male" <?php echo $gender === 'Male' ? 'selected' : ''; ?>>Male</option>
                        <option value="Female" <?php echo $gender === 'Female' ? 'selected' : ''; ?>>Female</option>
                        <option value="Other" <?php echo $gender === 'Other' ? 'selected' : ''; ?>>Other</option>
                    </select>
                </div>

                <!-- Submit -->
                <div class="flex items-end">
                    <button type="submit" class="w-full bg-secretariat text-white px-4 py-2 rounded-lg hover:bg-secretariat-dark transition">
                        <i class="fas fa-filter mr-2"></i> Filter
                    </button>
                </div>
            </form>
        </div>

        <!-- Employee List -->
        <div class="bg-white rounded-xl shadow-lg overflow-hidden">
            <?php if (empty($employees)): ?>
                <div class="text-center py-16">
                    <i class="fas fa-users-slash text-6xl text-gray-300 mb-4"></i>
                    <h3 class="text-xl font-semibold text-gray-600 mb-2">No Employees Found</h3>
                    <p class="text-gray-500">Try adjusting your filters</p>
                </div>
            <?php else: ?>
                <div class="overflow-x-auto">
                    <table class="w-full">
                        <thead class="bg-gray-50 border-b border-gray-200">
                            <tr>
                                <th class="px-6 py-4 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">Employee</th>
                                <th class="px-6 py-4 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">Category</th>
                                <th class="px-6 py-4 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">Gender</th>
                                <th class="px-6 py-4 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">Contact</th>
                                <th class="px-6 py-4 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">Activities</th>
                                <th class="px-6 py-4 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">Status</th>
                                <th class="px-6 py-4 text-right text-xs font-semibold text-gray-600 uppercase tracking-wider">Actions</th>
                            </tr>
                        </thead>
                        <tbody class="divide-y divide-gray-200">
                            <?php foreach ($employees as $employee): ?>
                                <tr class="hover:bg-gray-50 transition">
                                    <td class="px-6 py-4">
                                        <div class="flex items-center">
                                            <div class="w-10 h-10 bg-secretariat rounded-full flex items-center justify-center text-white font-semibold mr-3">
                                                <?php echo strtoupper(substr($employee['first_name'], 0, 1) . substr($employee['last_name'], 0, 1)); ?>
                                            </div>
                                            <div>
                                                <h4 class="font-semibold text-gray-900">
                                                    <?php echo htmlspecialchars($employee['first_name'] . ' ' . $employee['last_name']); ?>
                                                </h4>
                                                <p class="text-sm text-gray-600">
                                                    <?php echo htmlspecialchars($employee['position'] ?? 'N/A'); ?>
                                                </p>
                                            </div>
                                        </div>
                                    </td>
                                    <td class="px-6 py-4">
                                        <span class="px-3 py-1 rounded-full text-xs font-semibold bg-blue-100 text-blue-700">
                                            <?php echo htmlspecialchars($employee['category']); ?>
                                        </span>
                                    </td>
                                    <td class="px-6 py-4">
                                        <span class="text-sm text-gray-900">
                                            <?php if ($employee['gender'] === 'Male'): ?>
                                                <i class="fas fa-male text-blue-500 mr-1"></i>
                                            <?php elseif ($employee['gender'] === 'Female'): ?>
                                                <i class="fas fa-female text-pink-500 mr-1"></i>
                                            <?php else: ?>
                                                <i class="fas fa-user text-gray-500 mr-1"></i>
                                            <?php endif; ?>
                                            <?php echo $employee['gender']; ?>
                                        </span>
                                    </td>
                                    <td class="px-6 py-4">
                                        <div class="text-sm">
                                            <p class="text-gray-900">
                                                <i class="fas fa-envelope mr-1 text-secretariat"></i>
                                                <?php echo htmlspecialchars($employee['email']); ?>
                                            </p>
                                            <?php if (!empty($employee['contact_number'])): ?>
                                                <p class="text-gray-600">
                                                    <i class="fas fa-phone mr-1 text-secretariat"></i>
                                                    <?php echo htmlspecialchars($employee['contact_number']); ?>
                                                </p>
                                            <?php endif; ?>
                                        </div>
                                    </td>
                                    <td class="px-6 py-4">
                                        <div class="text-sm">
                                            <p class="text-gray-900">
                                                <i class="fas fa-calendar-check mr-1 text-green-500"></i>
                                                <?php echo $employee['total_registrations']; ?> registered
                                            </p>
                                            <p class="text-gray-600">
                                                <i class="fas fa-certificate mr-1 text-yellow-500"></i>
                                                <?php echo $employee['total_certificates']; ?> certificates
                                            </p>
                                        </div>
                                    </td>
                                    <td class="px-6 py-4">
                                        <?php if ($employee['is_active'] && $employee['is_approved']): ?>
                                            <span class="px-3 py-1 rounded-full text-xs font-semibold bg-green-100 text-green-700">
                                                <i class="fas fa-check-circle mr-1"></i> Active
                                            </span>
                                        <?php elseif (!$employee['is_approved']): ?>
                                            <span class="px-3 py-1 rounded-full text-xs font-semibold bg-yellow-100 text-yellow-700">
                                                <i class="fas fa-clock mr-1"></i> Pending
                                            </span>
                                        <?php else: ?>
                                            <span class="px-3 py-1 rounded-full text-xs font-semibold bg-red-100 text-red-700">
                                                <i class="fas fa-times-circle mr-1"></i> Inactive
                                            </span>
                                        <?php endif; ?>
                                    </td>
                                    <td class="px-6 py-4 text-right">
                                        <a href="employee-profile.php?id=<?php echo $employee['user_id']; ?>" 
                                           class="text-secretariat hover:text-secretariat-dark transition"
                                           title="View Profile">
                                            <i class="fas fa-eye"></i>
                                        </a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>

                <!-- Summary -->
                <div class="bg-gray-50 px-6 py-4 border-t border-gray-200">
                    <p class="text-sm text-gray-600">
                        Showing <span class="font-semibold"><?php echo count($employees); ?></span> employee(s)
                    </p>
                </div>
            <?php endif; ?>
        </div>
    </div>
</div>

<?php include '../includes/footer.php'; ?>

