DROP VIEW group_members_expanded;

CREATE VIEW

group_members_expanded

WITH all_members AS (

SELECT user_id, group_id FROM group_members

UNION

SELECT user_id, organization_id AS group_id FROM organization_members

)

users.id AS user_id,

users.username,

users.avatar_url AS user_avatar_url,

groups.organization_id AS organization_id,

groups.name AS group_name,

all_members.group_id AS group_id

all_members

users ON users.id = all_members.user_id

groups ON groups.id = all_members.group_id

users.deleted = 'false';

COMMENT ON VIEW group_members_expanded IS 'Joins group members with user information, organization ID, group name. Includes both regular group members and organization members (as part of the "Everyone" group).';