DROP TABLE IF EXISTS notification_preferences;

ALTER TABLE notification_templates

DROP COLUMN IF EXISTS method;

DROP TRIGGER IF EXISTS inhibit_enqueue_if_disabled_trigger ON notification_messages;

DROP FUNCTION IF EXISTS inhibit_enqueue_if_disabled;

CREATE TABLE notification_preferences

(

user_id uuid REFERENCES users ON DELETE CASCADE NOT NULL,

notification_template_id uuid REFERENCES notification_templates ON DELETE CASCADE NOT NULL,

disabled bool NOT NULL DEFAULT FALSE,

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP

);

ALTER TABLE notification_preferences

ADD CONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);

ALTER TABLE notification_templates

ADD COLUMN method notification_method;

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

ALTER TYPE notification_message_status ADD VALUE IF NOT EXISTS 'inhibited';

CREATE OR REPLACE FUNCTION inhibit_enqueue_if_disabled()

RETURNS TRIGGER AS

$$

-- Fail the insertion if the user has disabled this notification

IF EXISTS (SELECT 1

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;

CREATE TRIGGER inhibit_enqueue_if_disabled_trigger

BEFORE INSERT

ON notification_messages

FOR EACH ROW

EXECUTE FUNCTION inhibit_enqueue_if_disabled();