<?php
header('Content-Type: application/json');

// Include the database connection file
include('db_connection.php');

// Fetch groups that the student can join
$sql = "SELECT g.group_id, g.name, g.description, g.image_url, COUNT(gm.group_id) as member_count
        FROM groups g
        LEFT JOIN group_members gm ON g.group_id = gm.group_id
        LEFT JOIN student_groups sg ON g.group_id = sg.group_id
        WHERE sg.student_id IS NULL
        GROUP BY g.group_id";

$result = $conn->query($sql);

$groups = array();
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $groups[] = array(
            'group_id' => $row['group_id'],
            'name' => $row['name'],
            'description' => $row['description'],
            'image_url' => $row['image_url'],
            'member_count' => $row['member_count']
        );
    }
}

$conn->close();
echo json_encode($groups);
?>
