skeleton/internal/database/sql/queries/email.sql

-- name: CreateEmail :one
INSERT INTO "email" (
  "address",
  "identity_id",
  "is_verified"
) VALUES (
  $1, $2, $3
) RETURNING *;

-- name: UpdateEmailVerified :exec
UPDATE "email" SET (
  "is_verified"
) = ( 
  $2
) WHERE "address" = $1;

-- name: UpdateEmailPrimary :exec
-- UpdateEmailPrimary sets exactly one primary email for an identity.
-- The mail address has to have been verified.
-- this query basically combines these two queries into one atomic one:
--    UPDATE "email" SET "is_primary" = false WHERE "identity_id" = $1;
--    UPDATE "email" SET "is_primary" = true WHERE "identity_id" = $1 AND "address" = $2 AND "is_verified" = true;
UPDATE "email"
SET
  "is_primary" = NOT "is_primary"
WHERE
  "identity_id" = $1 AND (
    ( "address" <> $2 AND "is_primary" = true ) OR
    ( "address" = $2 AND "is_primary" = false AND "is_verified" = true )
  );

-- name: GetEmailByAddress :one
SELECT 
  *
FROM "email" 
WHERE
  "address" = $1;

-- name: GetEmailByIdentityID :many
SELECT 
  *
FROM "email"
WHERE
  "identity_id" = $1;

-- name: GetPrimaryEmailByIdentityID :one
SELECT 
  *
FROM "email"
WHERE
  "identity_id" = $1 AND "is_primary";

-- name: CountEmailsByIdentityID :one
SELECT
  COUNT(*)
FROM
  "email"
WHERE
  "identity_id" = $1;

-- name: CountUnverifiedEmailsByIdentityID :one
SELECT
  COUNT(*)
FROM
  "email"
WHERE
  "identity_id" = $1 AND
  "is_verified" = FALSE;

-- name: DestroyEmail :exec
DELETE FROM "email" WHERE "address" = $1;