| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- ─── org_members ─────────────────────────────────────────────────── |
| 4 | |
| 5 | -- name: AddOrgMember :exec |
| 6 | -- Idempotent on (org_id, user_id): re-adding a member is a no-op |
| 7 | -- rather than an error. The role is taken from the supplied row when |
| 8 | -- the member is new; existing rows keep their current role (use |
| 9 | -- ChangeOrgMemberRole to update). |
| 10 | INSERT INTO org_members (org_id, user_id, role, invited_by_user_id) |
| 11 | VALUES ($1, $2, $3, sqlc.narg(invited_by_user_id)::bigint) |
| 12 | ON CONFLICT (org_id, user_id) DO NOTHING; |
| 13 | |
| 14 | -- name: RemoveOrgMember :exec |
| 15 | DELETE FROM org_members WHERE org_id = $1 AND user_id = $2; |
| 16 | |
| 17 | -- name: ChangeOrgMemberRole :exec |
| 18 | UPDATE org_members SET role = $3 WHERE org_id = $1 AND user_id = $2; |
| 19 | |
| 20 | -- name: GetOrgMember :one |
| 21 | SELECT * FROM org_members WHERE org_id = $1 AND user_id = $2; |
| 22 | |
| 23 | -- name: ListOrgMembers :many |
| 24 | -- Members of an org with usernames + roles for the people page. |
| 25 | SELECT m.org_id, m.user_id, m.role, m.invited_by_user_id, m.joined_at, |
| 26 | u.username, u.display_name |
| 27 | FROM org_members m |
| 28 | JOIN users u ON u.id = m.user_id |
| 29 | WHERE m.org_id = $1 |
| 30 | AND u.deleted_at IS NULL |
| 31 | ORDER BY m.role ASC, u.username ASC; |
| 32 | |
| 33 | -- name: CountOrgOwners :one |
| 34 | -- Used by the last-owner protection: refuses to remove or demote the |
| 35 | -- only owner. Caller compares `count = 1` before allowing the change. |
| 36 | SELECT count(*) FROM org_members WHERE org_id = $1 AND role = 'owner'; |
| 37 | |
| 38 | -- name: ListOrgsForUser :many |
| 39 | -- Profile-page input: every org a user is a member of, with role. |
| 40 | SELECT m.org_id, m.role, o.slug, o.display_name, o.avatar_object_key |
| 41 | FROM org_members m |
| 42 | JOIN orgs o ON o.id = m.org_id |
| 43 | WHERE m.user_id = $1 |
| 44 | AND o.deleted_at IS NULL |
| 45 | ORDER BY o.slug ASC; |
| 46 |