CREATE TABLE IF NOT EXISTS outbound_messages (
    id UUID PRIMARY KEY,
    user_uuid UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    channel VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    available_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    attempts INTEGER NOT NULL DEFAULT 0,
    max_attempts INTEGER NOT NULL DEFAULT 3,
    leased_by VARCHAR(255),
    lease_until TIMESTAMPTZ,
    last_error TEXT,
    idempotency_key VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    delivered_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    CONSTRAINT outbound_messages_status_check
        CHECK (status IN ('pending', 'delivering', 'delivered', 'failed', 'cancelled')),
    CONSTRAINT outbound_messages_attempts_check
        CHECK (attempts >= 0 AND max_attempts > 0)
);

CREATE INDEX IF NOT EXISTS outbound_messages_claim_idx
    ON outbound_messages (channel, available_at, created_at)
    WHERE status = 'pending';

CREATE INDEX IF NOT EXISTS outbound_messages_expired_lease_idx
    ON outbound_messages (lease_until)
    WHERE status = 'delivering';

CREATE INDEX IF NOT EXISTS outbound_messages_user_idx
    ON outbound_messages (user_uuid, created_at DESC);