---

CREATE OR REPLACE FUNCTION integration.process_unv_notification_queue(
    p_limit integer DEFAULT 10,
    p_request_id uuid DEFAULT NULL
)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_task report.unv_notification_queue%ROWTYPE;
    v_log_id integration.npf360_to_lkk_notification_logs.id%TYPE;
    v_result text;
    v_error text;

    v_processed integer := 0;
    v_sent integer := 0;
    v_failed integer := 0;
BEGIN
    IF p_limit IS NULL OR p_limit NOT BETWEEN 1 AND 100 THEN
        RAISE EXCEPTION USING
            ERRCODE = '22023',
            MESSAGE = 'Количество заданий должно быть от 1 до 100';
    END IF;

    FOR v_task IN
        SELECT q.*
        FROM report.unv_notification_queue q
        JOIN report.unv_api_request r
            ON r.request_id = q.request_id
        WHERE q.status = 'pending'
          AND q.next_attempt_at <= CURRENT_TIMESTAMP
          AND r.completed_at IS NOT NULL
          AND (
              p_request_id IS NULL
              OR q.request_id = p_request_id
          )
        ORDER BY q.next_attempt_at, q.created_at, q.id
        LIMIT p_limit
        FOR UPDATE OF q SKIP LOCKED
    LOOP
        v_processed := v_processed + 1;
        v_result := NULL;
        v_error := NULL;
        v_log_id := NULL;

        -- Одна запись общего журнала на UUID уведомления.
        SELECT l.id
        INTO v_log_id
        FROM integration.npf360_to_lkk_notification_logs l
        WHERE l.guid = v_task.id
        ORDER BY l.id
        LIMIT 1;

        IF v_log_id IS NULL THEN
            INSERT INTO integration.npf360_to_lkk_notification_logs (
                guid,
                body,
                delay
            )
            VALUES (
                v_task.id,
                v_task.body::text,
                0
            )
            RETURNING id INTO v_log_id;
        END IF;

        -- Отправляем сохранённое сообщение.
        -- При повторной попытке UUID и содержимое сохраняются.
        BEGIN
            v_result :=
                utils.send_message_rmq_pyt(v_task.body::text);
        EXCEPTION
            WHEN OTHERS THEN
                v_error := SQLSTATE || ': ' || SQLERRM;
        END;

        IF v_error IS NULL AND v_result = 'DONE' THEN
            UPDATE integration.npf360_to_lkk_notification_logs
            SET sended = true
            WHERE id = v_log_id;

            UPDATE report.unv_notification_queue
            SET status = 'sent',
                attempts = attempts + 1,
                sent_at = clock_timestamp(),
                last_error = NULL
            WHERE id = v_task.id;

            v_sent := v_sent + 1;
        ELSE
            IF v_error IS NULL THEN
                v_error :=
                    'Отправитель вернул: '
                    || COALESCE(v_result, 'NULL');
            END IF;

            UPDATE report.unv_notification_queue
            SET attempts = attempts + 1,
                next_attempt_at =
                    clock_timestamp() + INTERVAL '5 minutes',
                last_error = v_error
            WHERE id = v_task.id;

            v_failed := v_failed + 1;
        END IF;
    END LOOP;

    RETURN jsonb_build_object(
        'processed', v_processed,
        'sent', v_sent,
        'failed', v_failed
    );
END;
$function$;