DROPTABLE IF EXISTS notification_preferences;

ALTERTABLE notification_templates

DROP COLUMN IF EXISTS method;

DROPTRIGGER IF EXISTS inhibit_enqueue_if_disabled_triggerON notification_messages;

DROPFUNCTION IF EXISTS inhibit_enqueue_if_disabled;

(

user_id uuidREFERENCES usersON DELETE CASCADENOT NULL,

notification_template_id uuidREFERENCES notification_templatesON DELETE CASCADENOT NULL,

disabled boolNOT NULL DEFAULT FALSE,

created_atTIMESTAMP WITH TIME ZONENOT NULL DEFAULTCURRENT_TIMESTAMP,

updated_atTIMESTAMP WITH TIME ZONENOT NULL DEFAULTCURRENT_TIMESTAMP

);

ALTERTABLE notification_preferences

ADDCONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);

ALTERTABLE notification_templates

ADD COLUMN method notification_method;

COMMENT ON COLUMN notification_templates.method IS'NULL defers to the deployment-level method';

ALTERTYPE notification_message_status ADD VALUE IF NOT EXISTS'inhibited';

CREATE OR REPLACEFUNCTIONinhibit_enqueue_if_disabled()

RETURNS TRIGGERAS

$$

IF EXISTS (SELECT1

FROM notification_preferences

WHERE disabled= TRUE

AND user_id=NEW.user_id

AND notification_template_id=NEW.notification_template_id) THEN

RAISE EXCEPTION'cannot enqueue message: user has disabled this notification';

END IF;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

BEFORE INSERT

ON notification_messages

FOR EACH ROW

EXECUTE FUNCTION inhibit_enqueue_if_disabled();