backend/migrations/00015_create_matching_functions.sql

-- +goose Up
-- +goose StatementBegin

-- =========================================
-- ФУНКЦИЯ: match_requests_for_volunteer
-- Алгоритм матчинга заявок для волонтёра
-- =========================================
CREATE OR REPLACE FUNCTION match_requests_for_volunteer(
    volunteer_user_id BIGINT,
    max_distance_meters INTEGER DEFAULT 10000,
    limit_count INTEGER DEFAULT 20
)
RETURNS TABLE (
    request_id BIGINT,
    title VARCHAR(255),
    description TEXT,
    address TEXT,
    city VARCHAR(100),
    distance_meters DOUBLE PRECISION,
    urgency VARCHAR(20),
    request_type_name VARCHAR(100),
    requester_name VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE,
    match_score DOUBLE PRECISION
) AS $$
DECLARE
    v_location GEOGRAPHY;
    v_rating NUMERIC;
    v_completed_count INTEGER;
BEGIN
    -- Получаем данные волонтёра
    SELECT location, volunteer_rating, completed_requests_count
    INTO v_location, v_rating, v_completed_count
    FROM users
    WHERE id = volunteer_user_id
    AND deleted_at IS NULL
    AND is_blocked = FALSE;

    -- Проверяем, что волонтёр существует и имеет геолокацию
    IF v_location IS NULL THEN
        RAISE EXCEPTION 'Volunteer location not set or user not found';
    END IF;

    RETURN QUERY
    SELECT
        r.id as request_id,
        r.title,
        r.description,
        r.address,
        r.city,
        ST_Distance(r.location, v_location) as distance_meters,
        r.urgency,
        rt.name as request_type_name,
        (u.first_name || ' ' || u.last_name) as requester_name,
        r.created_at,
        -- Расчёт score для сортировки (чем выше, тем лучше подходит)
        (
            -- Фактор 1: Близость (50% веса)
            -- Чем ближе, тем выше score
            (1000000.0 / GREATEST(ST_Distance(r.location, v_location), 100)) * 0.5 +

            -- Фактор 2: Срочность (30% веса)
            (CASE r.urgency
                WHEN 'urgent' THEN 100
                WHEN 'high' THEN 70
                WHEN 'medium' THEN 40
                WHEN 'low' THEN 20
                ELSE 30
            END) * 0.3 +

            -- Фактор 3: Давность заявки (20% веса)
            -- Старые заявки получают больший приоритет
            (EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - r.created_at)) / 3600) * 0.2
        ) as match_score

    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 = 'approved'
        AND r.deleted_at IS NULL

        -- Заявка ещё не взята
        AND r.assigned_volunteer_id IS NULL

        -- Волонтёр ещё не откликался на эту заявку
        AND NOT EXISTS (
            SELECT 1
            FROM volunteer_responses vr
            WHERE vr.request_id = r.id
            AND vr.volunteer_id = volunteer_user_id
        )

        -- В пределах указанного радиуса
        AND ST_DWithin(r.location, v_location, max_distance_meters)

        -- Заявитель не заблокирован
        AND u.deleted_at IS NULL
        AND u.is_blocked = FALSE

    -- Сортировка по score (лучшие подходят первыми)
    ORDER BY match_score DESC

    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION match_requests_for_volunteer IS 'Алгоритм матчинга: подбирает подходящие заявки для волонтёра на основе геолокации, срочности и давности. Факторы: близость (50%), срочность (30%), давность (20%)';

-- =========================================
-- ФУНКЦИЯ: find_volunteers_for_request
-- Поиск подходящих волонтёров для заявки
-- =========================================
CREATE OR REPLACE FUNCTION find_volunteers_for_request(
    p_request_id BIGINT,
    max_distance_meters INTEGER DEFAULT 10000,
    min_rating NUMERIC DEFAULT 0.0,
    limit_count INTEGER DEFAULT 20
)
RETURNS TABLE (
    volunteer_id BIGINT,
    volunteer_name VARCHAR(255),
    volunteer_rating NUMERIC,
    completed_requests_count INTEGER,
    distance_meters DOUBLE PRECISION,
    match_score DOUBLE PRECISION
) AS $$
DECLARE
    r_location GEOGRAPHY;
    r_urgency VARCHAR(20);
BEGIN
    -- Получаем данные заявки
    SELECT location, urgency
    INTO r_location, r_urgency
    FROM requests
    WHERE id = p_request_id
    AND deleted_at IS NULL;

    IF r_location IS NULL THEN
        RAISE EXCEPTION 'Request not found or has no location';
    END IF;

    RETURN QUERY
    SELECT
        u.id as volunteer_id,
        (u.first_name || ' ' || u.last_name) as volunteer_name,
        u.volunteer_rating,
        u.completed_requests_count,
        ST_Distance(u.location, r_location) as distance_meters,
        -- Score для сортировки волонтёров
        (
            -- Близость (40%)
            (1000000.0 / GREATEST(ST_Distance(u.location, r_location), 100)) * 0.4 +

            -- Рейтинг волонтёра (40%)
            (u.volunteer_rating * 20) * 0.4 +

            -- Опыт (количество выполненных заявок) (20%)
            (LEAST(u.completed_requests_count, 50) * 2) * 0.2
        ) as match_score

    FROM users u
    -- Проверяем, что у пользователя есть роль волонтёра
    WHERE EXISTS (
        SELECT 1
        FROM user_roles ur
        JOIN roles r ON r.id = ur.role_id
        WHERE ur.user_id = u.id
        AND r.name = 'volunteer'
    )
    -- Активный и не заблокированный
    AND u.deleted_at IS NULL
    AND u.is_blocked = FALSE

    -- Есть геолокация
    AND u.location IS NOT NULL

    -- Минимальный рейтинг
    AND u.volunteer_rating >= min_rating

    -- В пределах радиуса
    AND ST_DWithin(u.location, r_location, max_distance_meters)

    -- Волонтёр ещё не откликался на эту заявку
    AND NOT EXISTS (
        SELECT 1
        FROM volunteer_responses vr
        WHERE vr.request_id = p_request_id
        AND vr.volunteer_id = u.id
    )

    ORDER BY match_score DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION find_volunteers_for_request IS 'Поиск подходящих волонтёров для заявки на основе близости, рейтинга и опыта';

-- =========================================
-- ФУНКЦИЯ: get_volunteer_statistics
-- Статистика волонтёра
-- =========================================
CREATE OR REPLACE FUNCTION get_volunteer_statistics(p_volunteer_id BIGINT)
RETURNS TABLE (
    total_responses INTEGER,
    accepted_responses INTEGER,
    completed_requests INTEGER,
    average_rating NUMERIC,
    total_ratings INTEGER,
    acceptance_rate NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(DISTINCT vr.id)::INTEGER as total_responses,
        COUNT(DISTINCT CASE WHEN vr.status = 'accepted' THEN vr.id END)::INTEGER as accepted_responses,
        COUNT(DISTINCT r.id)::INTEGER as completed_requests,
        COALESCE(ROUND(AVG(r.rating), 2), 0) as average_rating,
        COUNT(DISTINCT r.id)::INTEGER as total_ratings,
        CASE
            WHEN COUNT(DISTINCT vr.id) > 0
            THEN ROUND((COUNT(DISTINCT CASE WHEN vr.status = 'accepted' THEN vr.id END)::NUMERIC / COUNT(DISTINCT vr.id)::NUMERIC) * 100, 2)
            ELSE 0
        END as acceptance_rate
    FROM users u
    LEFT JOIN volunteer_responses vr ON vr.volunteer_id = u.id
    LEFT JOIN ratings r ON r.volunteer_id = u.id
    WHERE u.id = p_volunteer_id
    AND u.deleted_at IS NULL;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_volunteer_statistics IS 'Получение детальной статистики волонтёра';

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin

DROP FUNCTION IF EXISTS get_volunteer_statistics;
DROP FUNCTION IF EXISTS find_volunteers_for_request;
DROP FUNCTION IF EXISTS match_requests_for_volunteer;

-- +goose StatementEnd