-- name: GetUserIdentityByProviderSubject :one
SELECT id, user_id, provider, provider_subject, email, email_verified,
       display_name, avatar_url, created_at, updated_at, last_login_at
FROM user_identities
WHERE provider = $1 AND provider_subject = $2;

-- name: ListUserIdentitiesByUser :many
SELECT id, user_id, provider, provider_subject, email, email_verified,
       display_name, avatar_url, created_at, updated_at, last_login_at
FROM user_identities
WHERE user_id = $1
ORDER BY provider;

-- name: InsertUserIdentity :one
INSERT INTO user_identities (
    user_id, provider, provider_subject, email, email_verified,
    display_name, avatar_url, last_login_at
)
VALUES ($1, $2, $3, $4, $5, $6, $7, now())
RETURNING id, user_id, provider, provider_subject, email, email_verified,
          display_name, avatar_url, created_at, updated_at, last_login_at;

-- name: UpdateUserIdentityLogin :one
UPDATE user_identities
SET email = $3,
    email_verified = $4,
    display_name = COALESCE($5, display_name),
    avatar_url = COALESCE($6, avatar_url),
    last_login_at = now(),
    updated_at = now()
WHERE provider = $1 AND provider_subject = $2
RETURNING id, user_id, provider, provider_subject, email, email_verified,
          display_name, avatar_url, created_at, updated_at, last_login_at;

-- name: UpdateUserIdentityEmail :one
UPDATE user_identities
SET email = $3,
    updated_at = now()
WHERE provider = $1 AND provider_subject = $2
RETURNING id, user_id, provider, provider_subject, email, email_verified,
          display_name, avatar_url, created_at, updated_at, last_login_at;