backend/internal/database/queries/auth.sql

-- Фаза 1A: Аутентификация (КРИТИЧНО)
-- Запросы для регистрации, входа и управления токенами

-- ============================================================================
-- Пользователи
-- ============================================================================

-- name: CreateUser :one
INSERT INTO users (
    email,
    phone,
    password_hash,
    first_name,
    last_name,
    location,
    address,
    city
) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    ST_SetSRID(ST_MakePoint($6, $7), 4326)::geography,
    $8,
    $9
) RETURNING
    id,
    email,
    phone,
    password_hash,
    first_name,
    last_name,
    avatar_url,
    ST_Y(location::geometry) as latitude,
    ST_X(location::geometry) as longitude,
    address,
    city,
    volunteer_rating,
    completed_requests_count,
    is_verified,
    is_blocked,
    email_verified,
    created_at,
    updated_at,
    last_login_at,
    deleted_at;

-- name: GetUserByEmail :one
SELECT
    id,
    email,
    phone,
    password_hash,
    first_name,
    last_name,
    avatar_url,
    ST_Y(location::geometry) as latitude,
    ST_X(location::geometry) as longitude,
    address,
    city,
    volunteer_rating,
    completed_requests_count,
    is_verified,
    is_blocked,
    email_verified,
    created_at,
    updated_at,
    last_login_at,
    deleted_at
FROM users
WHERE email = $1 AND deleted_at IS NULL;

-- name: GetUserByID :one
SELECT
    id,
    email,
    phone,
    password_hash,
    first_name,
    last_name,
    avatar_url,
    ST_Y(location::geometry) as latitude,
    ST_X(location::geometry) as longitude,
    address,
    city,
    volunteer_rating,
    completed_requests_count,
    is_verified,
    is_blocked,
    email_verified,
    created_at,
    updated_at,
    last_login_at,
    deleted_at
FROM users
WHERE id = $1 AND deleted_at IS NULL;

-- name: EmailExists :one
SELECT EXISTS(
    SELECT 1 FROM users
    WHERE email = $1 AND deleted_at IS NULL
);

-- name: UpdateLastLogin :exec
UPDATE users
SET last_login_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- ============================================================================
-- Refresh Tokens
-- ============================================================================

-- name: CreateRefreshToken :one
INSERT INTO refresh_tokens (
    user_id,
    token,
    expires_at,
    user_agent,
    ip_address
) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5
) RETURNING *;

-- name: GetRefreshToken :one
SELECT * FROM refresh_tokens
WHERE token = $1
    AND revoked = FALSE
    AND expires_at > CURRENT_TIMESTAMP;

-- name: RevokeRefreshToken :exec
UPDATE refresh_tokens
SET revoked = TRUE, revoked_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: RevokeAllUserTokens :exec
UPDATE refresh_tokens
SET revoked = TRUE, revoked_at = CURRENT_TIMESTAMP
WHERE user_id = $1 AND revoked = FALSE;

-- name: CleanupExpiredTokens :exec
DELETE FROM refresh_tokens
WHERE expires_at < CURRENT_TIMESTAMP
    OR (revoked = TRUE AND revoked_at < CURRENT_TIMESTAMP - INTERVAL '30 days');

-- ============================================================================
-- User Sessions
-- ============================================================================

-- name: CreateUserSession :one
INSERT INTO user_sessions (
    user_id,
    session_token,
    refresh_token_id,
    expires_at,
    user_agent,
    ip_address,
    device_info
) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6,
    $7
) RETURNING *;

-- name: GetUserSession :one
SELECT * FROM user_sessions
WHERE session_token = $1
    AND expires_at > CURRENT_TIMESTAMP;

-- name: UpdateSessionActivity :exec
UPDATE user_sessions
SET last_activity_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: InvalidateUserSession :exec
DELETE FROM user_sessions
WHERE id = $1;

-- name: InvalidateAllUserSessions :exec
DELETE FROM user_sessions
WHERE user_id = $1;

-- name: CleanupExpiredSessions :exec
DELETE FROM user_sessions
WHERE expires_at < CURRENT_TIMESTAMP
    OR last_activity_at < CURRENT_TIMESTAMP - INTERVAL '7 days';