-- Sessions and magic-link tokens.
--
-- Both are stored hashed: the plaintext never reaches the database, so a dump
-- cannot be replayed into a login.

-- name: UpsertUser :one
INSERT INTO users (id, email, email_verified)
VALUES (@id, @email, true)
ON CONFLICT (email) DO UPDATE
    SET email_verified = true, updated_at = now()
RETURNING id, email, name, is_admin;

-- name: CreateVerification :exec
INSERT INTO verifications (id, identifier, value, expires_at)
VALUES (@id, @identifier, @value, @expires_at);

-- name: ConsumeVerification :one
UPDATE verifications SET consumed_at = now(), updated_at = now()
WHERE value = @value AND consumed_at IS NULL AND expires_at > now()
RETURNING identifier;

-- name: CreateSession :exec
INSERT INTO sessions (id, token, expires_at, user_id, ip_address, user_agent)
VALUES (@id, @token, @expires_at, @user_id, @ip_address, @user_agent);

-- name: UserBySessionToken :one
SELECT u.id, u.email, u.name, u.is_admin
FROM sessions s
JOIN users u ON u.id = s.user_id
WHERE s.token = @token AND s.expires_at > now();

-- name: DeleteSession :exec
DELETE FROM sessions WHERE token = @token;

-- name: PurgeExpiredSessions :exec
DELETE FROM sessions WHERE expires_at < now();

-- name: PurgeExpiredVerifications :exec
DELETE FROM verifications WHERE expires_at < now() - interval '1 day';