users.sql

-- name: CreateUser :exec
INSERT INTO users (
  id,
  email,
  google_id,
  google_profile_pic,
  is_profile_complete
) VALUES (
  $1, $2, $3, $4, false
);

-- name: UserExistsByEmail :one
SELECT EXISTS (
  SELECT 1
  FROM users
  WHERE email = $1
);

-- name: GetUserIDByEmail :one
SELECT id
FROM users
WHERE email = $1;

-- name: GetAllUsers :many
SELECT u.*, t.round_qualified
FROM users u
JOIN teams t ON t.id = u.team_id
WHERE (u.name LIKE '%' || $1 || '%'
       OR u.reg_no LIKE '%' || $1 || '%'
       OR u.email LIKE '%' || $1 || '%')
  AND u.id > $2
  AND ($4 = '' OR u.gender = $4)
ORDER BY u.id
LIMIT $3;

-- name: GetUsersByGender :many
SELECT * FROM users WHERE gender = $1;

-- name: GetUsersByTeamId :many
SELECT
  name,
  email,
  reg_no,
  phone_no,
  hostel_block
FROM users
WHERE team_id = $1::uuid;

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

-- name: GetTeamIDByEmail :one
SELECT team_id
FROM users
WHERE email = $1;

-- name: GetTeamLeaderByID :one
SELECT *
FROM users
WHERE team_id = $1::uuid
  AND is_leader = true;

-- name: BanUser :exec
UPDATE users
SET is_banned = TRUE
WHERE email = $1;

-- name: UnbanUser :exec
UPDATE users
SET is_banned = FALSE
WHERE email = $1;

-- name: UpdateTeamByEmail :exec
UPDATE users
SET team_id = $1
WHERE email = $2;

-- name: GetUserByRegNo :one
SELECT *
FROM users
WHERE reg_no = $1;

-- name: CompleteUserProfile :exec
UPDATE users
SET
  name = $2,
  reg_no = $3,
  phone_no = $4,
  gender = $5,
  residency = $6,
  hostel_block = $7,
  room_no = $8,
  github_profile = $9,
  is_profile_complete = TRUE,
  updated_at = now()
WHERE email = $1;

-- name: CompleteOauth :exec
UPDATE users
SET
  name = $2,
  updated_at = now()
WHERE email = $1;

-- name: MakeLeader :exec
UPDATE users
SET
  is_leader = TRUE,
  role = 'leader',
  team_id = $1,
  updated_at = now()
WHERE email = $2
  AND role = 'user'
  AND is_leader = FALSE;

-- name: LeaveTeam :exec
UPDATE users
SET
  team_id = NULL,
  role = 'user',
  is_leader = FALSE,
  updated_at = now()
WHERE id = $1::uuid;


-- name: GetTeamIDByUID :one
SELECT team_id
FROM users
WHERE id = $1::uuid;


-- name: SwapLeader :one
WITH new_leader AS (
  SELECT id
  FROM users
  WHERE team_id = $1::uuid
    AND is_leader = FALSE
  LIMIT 1
),
updated AS (
  UPDATE users
  SET
    is_leader = CASE
      WHEN id = (SELECT id FROM new_leader) THEN TRUE
      ELSE FALSE
    END,
    role = CASE
      WHEN id = (SELECT id FROM new_leader) THEN 'leader'
      ELSE 'user'
    END,
    team_id = CASE
      WHEN id = (SELECT id FROM new_leader) THEN team_id
      ELSE NULL
    END,
    updated_at = now()
  WHERE team_id = $1::uuid
    AND (
      is_leader = TRUE
      OR id = (SELECT id FROM new_leader)
    )
    AND EXISTS (SELECT 1 FROM new_leader)
  RETURNING *
)
SELECT * FROM updated;

-- name: UpdateRegNo :exec
UPDATE users
SET
  reg_no = $2,
  updated_at = now()
WHERE email = $1;