-- Фаза 1C: Управление профилем (КРИТИЧНО)
-- Запросы для получения и обновления профилей пользователей

-- ============================================================================
-- Профиль пользователя
-- ============================================================================

-- name: GetUserProfile :one
SELECT
    id,
    email,
    phone,
    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
FROM users
WHERE id = $1 AND deleted_at IS NULL;

-- name: UpdateUserProfile :exec
UPDATE users SET
    phone = COALESCE(sqlc.narg('phone'), phone),
    first_name = COALESCE(sqlc.narg('first_name'), first_name),
    last_name = COALESCE(sqlc.narg('last_name'), last_name),
    avatar_url = COALESCE(sqlc.narg('avatar_url'), avatar_url),
    address = COALESCE(sqlc.narg('address'), address),
    city = COALESCE(sqlc.narg('city'), city),
    updated_at = CURRENT_TIMESTAMP
WHERE id = sqlc.arg('user_id');

-- name: UpdateUserLocation :exec
UPDATE users SET
    location = ST_SetSRID(ST_MakePoint($2, $3), 4326)::geography,
    address = COALESCE(sqlc.narg('address'), address),
    city = COALESCE(sqlc.narg('city'), city),
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: VerifyUserEmail :exec
UPDATE users SET
    email_verified = TRUE,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: UpdateUserPassword :exec
UPDATE users SET
    password_hash = $2,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: BlockUser :exec
UPDATE users SET
    is_blocked = TRUE,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: UnblockUser :exec
UPDATE users SET
    is_blocked = FALSE,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: SoftDeleteUser :exec
UPDATE users SET
    deleted_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- ============================================================================
-- Поиск пользователей
-- ============================================================================

-- name: GetUsersByIDs :many
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 = ANY($1::bigint[])
    AND deleted_at IS NULL;

-- name: SearchUsersByName :many
SELECT
    id,
    email,
    first_name,
    last_name,
    avatar_url,
    volunteer_rating,
    completed_requests_count,
    is_verified
FROM users
WHERE (first_name ILIKE '%' || $1 || '%' OR last_name ILIKE '%' || $1 || '%' OR (first_name || ' ' || last_name) ILIKE '%' || $1 || '%')
    AND deleted_at IS NULL
    AND is_blocked = FALSE
ORDER BY volunteer_rating DESC NULLS LAST
LIMIT $2 OFFSET $3;

-- name: GetVolunteerStatistics :one
SELECT
    id,
    first_name,
    last_name,
    volunteer_rating,
    completed_requests_count,
    created_at as member_since
FROM users
WHERE id = $1
    AND deleted_at IS NULL;