-- name: UpsertUserByAuthentikSub :one
INSERT INTO users (id, authentik_sub, email, display_name, is_sysadmin)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (authentik_sub) DO UPDATE
    SET email = EXCLUDED.email,
        display_name = EXCLUDED.display_name
RETURNING *;

-- name: GetUserByID :one
SELECT * FROM users WHERE id = $1;

-- name: GetUserByEmail :one
SELECT * FROM users WHERE email = $1;

-- name: PromoteSysadmin :exec
UPDATE users SET is_sysadmin = true WHERE id = $1;

-- name: ListMemberships :many
SELECT m.*, o.slug, o.name
FROM memberships m JOIN orgs o ON o.id = m.org_id
WHERE m.user_id = $1
ORDER BY m.org_id;

-- name: AddMembership :exec
INSERT INTO memberships (org_id, user_id, role)
VALUES ($1, $2, $3)
ON CONFLICT (org_id, user_id) DO UPDATE SET role = EXCLUDED.role;

-- name: RemoveMembership :exec
DELETE FROM memberships WHERE org_id = $1 AND user_id = $2;