---

BEGIN;

CREATE TABLE report.unv_notification_queue (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

    request_id uuid NOT NULL
        REFERENCES report.unv_api_request (request_id),

    body jsonb NOT NULL,

    status text NOT NULL DEFAULT 'pending',

    attempts integer NOT NULL DEFAULT 0,

    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

    next_attempt_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

    sent_at timestamp,

    last_error text,

    CONSTRAINT unv_notification_queue_request_uq
        UNIQUE (request_id),

    CONSTRAINT unv_notification_queue_status_chk
        CHECK (status IN ('pending', 'sent')),

    CONSTRAINT unv_notification_queue_attempts_chk
        CHECK (attempts >= 0)
);

CREATE INDEX idx_unv_notification_queue_pending
    ON report.unv_notification_queue (
        next_attempt_at,
        created_at
    )
    WHERE status = 'pending';

COMMIT;




SELECT pg_get_functiondef(
    'integration.submit_unv_application_api(text)'::regprocedure
);