-- name: CreateUserSession :one
INSERT INTO user_sessions (
    user_id, session_type, issued_at, expires_at, last_active,
    ip_address, user_agent, access_token_id, refresh_token_id,
    device_info, is_active
) VALUES (
    $1, $2, NOW(), $3, $4,
    $5, $6, $7, $8,
    $9, TRUE
)
RETURNING *;

-- name: ListActiveUserSessions :many
SELECT * FROM user_sessions
WHERE user_id = $1
  AND is_active = TRUE
ORDER BY issued_at DESC;

-- name: GetUserSessionByAccessJTI :one
SELECT * FROM user_sessions
WHERE access_token_id = $1
  AND is_active = TRUE;

-- name: GetUserSessionByRefreshJTI :one
SELECT * FROM user_sessions
WHERE refresh_token_id = $1;

-- name: RevokeUserSession :exec
UPDATE user_sessions
SET is_active = FALSE,
    revoked_at = NOW()
WHERE id = $1
  AND is_active = TRUE;

-- name: UpdateSessionLastActive :exec
UPDATE user_sessions
SET last_active = NOW()
WHERE id = $1
  AND is_active = TRUE;

-- name: UpdateSessionTokens :exec
UPDATE user_sessions
SET access_token_id = $2, refresh_token_id = $3, expires_at = $4
WHERE id = $1
  AND is_active = TRUE;

-- name: ListAllSessions :many
SELECT * FROM user_sessions
ORDER BY issued_at DESC
LIMIT $1 OFFSET $2;

-- name: GetUserSessions :many
SELECT sqlc.embed(session), sqlc.embed(u)
FROM user_sessions AS session
JOIN users AS u ON u.id = session.user_id
ORDER BY session.issued_at DESC
LIMIT $1 OFFSET $2;

-- name: GetUserSessionsCount :one
SELECT COUNT(*) FROM user_sessions;