-- +goose Up
-- +goose StatementBegin

-- =========================================
-- ФУНКЦИЯ: find_requests_nearby - Геопоиск заявок
-- =========================================
CREATE OR REPLACE FUNCTION find_requests_nearby(
    lat DOUBLE PRECISION,
    lon DOUBLE PRECISION,
    radius_meters INTEGER DEFAULT 5000,
    req_status request_status DEFAULT 'approved'
)
RETURNS TABLE (
    id BIGINT,
    title VARCHAR(255),
    description TEXT,
    address TEXT,
    distance_meters DOUBLE PRECISION,
    urgency VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        r.id,
        r.title,
        r.description,
        r.address,
        ST_Distance(
            r.location,
            ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography
        ) as distance_meters,
        r.urgency,
        r.created_at
    FROM requests r
    WHERE
        r.status = req_status
        AND r.deleted_at IS NULL
        AND ST_DWithin(
            r.location,
            ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography,
            radius_meters
        )
    ORDER BY distance_meters;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION find_requests_nearby IS 'Поиск заявок в радиусе от точки с возвратом расстояния в метрах';

-- =========================================
-- ФУНКЦИЯ: cleanup_expired_tokens - Очистка истёкших токенов
-- =========================================
CREATE OR REPLACE FUNCTION cleanup_expired_tokens()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Удаляем истёкшие refresh токены
    DELETE FROM refresh_tokens
    WHERE expires_at < CURRENT_TIMESTAMP
    AND revoked = FALSE;

    GET DIAGNOSTICS deleted_count = ROW_COUNT;

    -- Удаляем истёкшие сессии
    DELETE FROM user_sessions
    WHERE expires_at < CURRENT_TIMESTAMP;

    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION cleanup_expired_tokens IS 'Удаление истёкших токенов и сессий. Рекомендуется запускать по расписанию';

-- =========================================
-- ФУНКЦИЯ: calculate_distance_meters - Расчёт расстояния между точками
-- =========================================
CREATE OR REPLACE FUNCTION calculate_distance_meters(
    lat1 DOUBLE PRECISION,
    lon1 DOUBLE PRECISION,
    lat2 DOUBLE PRECISION,
    lon2 DOUBLE PRECISION
)
RETURNS DOUBLE PRECISION AS $$
BEGIN
    RETURN ST_Distance(
        ST_SetSRID(ST_MakePoint(lon1, lat1), 4326)::geography,
        ST_SetSRID(ST_MakePoint(lon2, lat2), 4326)::geography
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION calculate_distance_meters IS 'Расчёт расстояния между двумя точками в метрах';

-- =========================================
-- ФУНКЦИЯ: get_user_permissions - Получение прав пользователя
-- =========================================
CREATE OR REPLACE FUNCTION get_user_permissions(p_user_id BIGINT)
RETURNS TABLE (
    permission_name VARCHAR(100),
    resource VARCHAR(50),
    action VARCHAR(50)
) AS $$
BEGIN
    RETURN QUERY
    SELECT DISTINCT
        p.name,
        p.resource,
        p.action
    FROM users u
    JOIN user_roles ur ON ur.user_id = u.id
    JOIN role_permissions rp ON rp.role_id = ur.role_id
    JOIN permissions p ON p.id = rp.permission_id
    WHERE u.id = p_user_id
    AND u.deleted_at IS NULL
    AND u.is_blocked = FALSE;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_user_permissions IS 'Получение всех разрешений пользователя через его роли';

-- =========================================
-- ФУНКЦИЯ: has_permission - Проверка наличия разрешения
-- =========================================
CREATE OR REPLACE FUNCTION has_permission(
    p_user_id BIGINT,
    p_permission_name VARCHAR(100)
)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS(
        SELECT 1
        FROM users u
        JOIN user_roles ur ON ur.user_id = u.id
        JOIN role_permissions rp ON rp.role_id = ur.role_id
        JOIN permissions p ON p.id = rp.permission_id
        WHERE u.id = p_user_id
        AND p.name = p_permission_name
        AND u.deleted_at IS NULL
        AND u.is_blocked = FALSE
    );
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION has_permission IS 'Быстрая проверка наличия конкретного разрешения у пользователя';

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin

DROP FUNCTION IF EXISTS has_permission;
DROP FUNCTION IF EXISTS get_user_permissions;
DROP FUNCTION IF EXISTS calculate_distance_meters;
DROP FUNCTION IF EXISTS cleanup_expired_tokens;
DROP FUNCTION IF EXISTS find_requests_nearby;

-- +goose StatementEnd