backend/internal/database/queries/requests.sql

-- Фаза 2A: Управление заявками (ВЫСОКИЙ ПРИОРИТЕТ)
-- CRUD операции для заявок на помощь

-- ============================================================================
-- Создание и получение заявок
-- ============================================================================

-- name: CreateRequest :one
INSERT INTO requests (
    requester_id,
    request_type_id,
    title,
    description,
    location,
    address,
    city,
    desired_completion_date,
    urgency,
    contact_phone,
    contact_notes
) VALUES (
    $1,
    $2,
    $3,
    $4,
    ST_SetSRID(ST_MakePoint($5, $6), 4326)::geography,
    $7,
    $8,
    $9,
    $10,
    $11,
    $12
) RETURNING
    id,
    requester_id,
    request_type_id,
    title,
    description,
    ST_Y(location::geometry) as latitude,
    ST_X(location::geometry) as longitude,
    address,
    city,
    desired_completion_date,
    urgency,
    contact_phone,
    contact_notes,
    status,
    assigned_volunteer_id,
    created_at,
    updated_at,
    deleted_at;

-- name: GetRequestByID :one
SELECT
    r.id,
    r.requester_id,
    r.request_type_id,
    r.title,
    r.description,
    ST_Y(r.location::geometry) as latitude,
    ST_X(r.location::geometry) as longitude,
    r.address,
    r.city,
    r.desired_completion_date,
    r.urgency,
    r.contact_phone,
    r.contact_notes,
    r.status,
    r.assigned_volunteer_id,
    r.created_at,
    r.updated_at,
    r.deleted_at,
    r.completed_at,
    rt.name as request_type_name,
    rt.icon as request_type_icon,
    (u.first_name || ' ' || u.last_name) as requester_name,
    u.phone as requester_phone,
    u.email as requester_email,
    (av.first_name || ' ' || av.last_name) as assigned_volunteer_name,
    av.phone as assigned_volunteer_phone
FROM requests r
JOIN request_types rt ON rt.id = r.request_type_id
JOIN users u ON u.id = r.requester_id
LEFT JOIN users av ON av.id = r.assigned_volunteer_id
WHERE r.id = $1 AND r.deleted_at IS NULL;

-- name: GetRequestsByRequester :many
SELECT
    r.id,
    r.requester_id,
    r.request_type_id,
    r.title,
    r.description,
    ST_Y(r.location::geometry) as latitude,
    ST_X(r.location::geometry) as longitude,
    r.address,
    r.city,
    r.desired_completion_date,
    r.urgency,
    r.contact_phone,
    r.contact_notes,
    r.status,
    r.assigned_volunteer_id,
    r.created_at,
    r.updated_at,
    r.deleted_at,
    rt.name as request_type_name,
    rt.icon as request_type_icon
FROM requests r
JOIN request_types rt ON rt.id = r.request_type_id
WHERE r.requester_id = $1
    AND r.deleted_at IS NULL
ORDER BY r.created_at DESC
LIMIT $2 OFFSET $3;

-- name: GetRequestsByStatus :many
SELECT
    r.id,
    r.requester_id,
    r.request_type_id,
    r.title,
    r.description,
    ST_Y(r.location::geometry) as latitude,
    ST_X(r.location::geometry) as longitude,
    r.address,
    r.city,
    r.desired_completion_date,
    r.urgency,
    r.contact_phone,
    r.contact_notes,
    r.status,
    r.assigned_volunteer_id,
    r.created_at,
    r.updated_at,
    r.deleted_at,
    rt.name as request_type_name,
    (u.first_name || ' ' || u.last_name) as requester_name
FROM requests r
JOIN request_types rt ON rt.id = r.request_type_id
JOIN users u ON u.id = r.requester_id
WHERE r.status = $1
    AND r.deleted_at IS NULL
ORDER BY r.created_at DESC
LIMIT $2 OFFSET $3;

-- ============================================================================
-- Обновление заявок
-- ============================================================================

-- name: UpdateRequestStatus :exec
UPDATE requests SET
    status = $2,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
    AND deleted_at IS NULL;

-- name: AssignVolunteerToRequest :exec
UPDATE requests SET
    assigned_volunteer_id = $2,
    status = 'in_progress',
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
    AND deleted_at IS NULL;

-- name: CompleteRequest :exec
UPDATE requests SET
    status = 'completed',
    completed_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
    AND deleted_at IS NULL;

-- name: CancelRequest :exec
UPDATE requests SET
    status = 'cancelled',
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
    AND deleted_at IS NULL;

-- name: ModerateRequest :exec
UPDATE requests SET
    status = $2,
    moderated_by = $3,
    moderated_at = CURRENT_TIMESTAMP,
    moderation_comment = $4,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- ============================================================================
-- Удаление заявок
-- ============================================================================

-- name: DeleteRequest :exec
UPDATE requests SET
    deleted_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
    AND requester_id = $2
    AND deleted_at IS NULL;

-- ============================================================================
-- Типы заявок
-- ============================================================================

-- name: ListRequestTypes :many
SELECT * FROM request_types
WHERE is_active = TRUE
ORDER BY name;

-- name: GetRequestTypeByID :one
SELECT * FROM request_types
WHERE id = $1;

-- name: GetRequestTypeByName :one
SELECT * FROM request_types
WHERE name = $1;

-- ============================================================================
-- Статистика
-- ============================================================================

-- name: CountRequestsByRequester :one
SELECT COUNT(*) FROM requests
WHERE requester_id = $1
    AND deleted_at IS NULL;

-- name: CountRequestsByStatus :one
SELECT COUNT(*) FROM requests
WHERE status = $1
    AND deleted_at IS NULL;

-- ============================================================================
-- Модерация заявок
-- ============================================================================

-- name: GetPendingModerationRequests :many
SELECT
    r.id,
    r.requester_id,
    r.request_type_id,
    r.title,
    r.description,
    ST_Y(r.location::geometry) as latitude,
    ST_X(r.location::geometry) as longitude,
    r.address,
    r.city,
    r.desired_completion_date,
    r.urgency,
    r.contact_phone,
    r.contact_notes,
    r.status,
    r.created_at,
    r.updated_at,
    rt.name as request_type_name,
    rt.icon as request_type_icon,
    (u.first_name || ' ' || u.last_name) as requester_name,
    u.email as requester_email,
    u.phone as requester_phone
FROM requests r
JOIN request_types rt ON rt.id = r.request_type_id
JOIN users u ON u.id = r.requester_id
WHERE r.status = 'pending_moderation'
    AND r.deleted_at IS NULL
ORDER BY r.created_at ASC
LIMIT $1 OFFSET $2;

-- name: ApproveRequest :exec
UPDATE requests SET
    status = 'approved',
    moderated_by = $2,
    moderated_at = CURRENT_TIMESTAMP,
    moderation_comment = sqlc.narg('moderation_comment'),
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
    AND status = 'pending_moderation'
    AND deleted_at IS NULL;

-- name: RejectRequest :exec
UPDATE requests SET
    status = 'rejected',
    moderated_by = $2,
    moderated_at = CURRENT_TIMESTAMP,
    moderation_comment = $3,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1
    AND status = 'pending_moderation'
    AND deleted_at IS NULL;

-- name: GetModeratedRequests :many
SELECT
    r.id,
    r.requester_id,
    r.request_type_id,
    r.title,
    r.description,
    ST_Y(r.location::geometry) as latitude,
    ST_X(r.location::geometry) as longitude,
    r.address,
    r.status,
    r.moderated_by,
    r.moderated_at,
    r.moderation_comment,
    r.created_at,
    rt.name as request_type_name,
    (u.first_name || ' ' || u.last_name) as requester_name,
    (m.first_name || ' ' || m.last_name) as moderator_name
FROM requests r
JOIN request_types rt ON rt.id = r.request_type_id
JOIN users u ON u.id = r.requester_id
LEFT JOIN users m ON m.id = r.moderated_by
WHERE r.moderated_by = $1
    AND r.deleted_at IS NULL
ORDER BY r.moderated_at DESC
LIMIT $2 OFFSET $3;

-- ============================================================================
-- Аудит действий модераторов
-- ============================================================================

-- name: GetModeratorActionsByRequest :many
SELECT
    ma.*,
    (u.first_name || ' ' || u.last_name) as moderator_name,
    u.email as moderator_email
FROM moderator_actions ma
JOIN users u ON u.id = ma.moderator_id
WHERE ma.target_request_id = $1
ORDER BY ma.created_at DESC;

-- name: GetModeratorActionsByModerator :many
SELECT
    ma.*,
    r.title as request_title,
    r.status as request_status
FROM moderator_actions ma
LEFT JOIN requests r ON r.id = ma.target_request_id
WHERE ma.moderator_id = $1
ORDER BY ma.created_at DESC
LIMIT $2 OFFSET $3;