CREATE TABLE IDN_NOTIFICATION_TYPE (
  ID INTEGER NOT NULL,
  TYPE_KEY VARCHAR(255) NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  CHANNEL VARCHAR(255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT NOTIFICATION_TYPE_KEY_CONSTRAINT UNIQUE (TYPE_KEY, CHANNEL, TENANT_ID),
  CONSTRAINT NOTIFICATION_TYPE_NAME_CONSTRAINT UNIQUE (NAME, CHANNEL, TENANT_ID)
)
/
CREATE SEQUENCE IDN_NOTIFICATION_TYPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_NOTIFICATION_TYPE_TRIG
  BEFORE INSERT
    ON IDN_NOTIFICATION_TYPE
    REFERENCING NEW AS NEW
    FOR EACH ROW
  BEGIN
  SELECT IDN_NOTIFICATION_TYPE_SEQ.nextval INTO :NEW.CURSOR_KEY FROM dual;
  END;
/
CREATE TABLE IDN_NOTIFICATION_ORG_TEMPLATE (
  ID INTEGER NOT NULL,
  TEMPLATE_KEY VARCHAR(50) NOT NULL,
  LOCALE VARCHAR(50) NOT NULL,
  SUBJECT VARCHAR(4000),
  BODY CLOB,
  FOOTER CLOB,
  CONTENT_TYPE VARCHAR(50),
  TYPE_ID INTEGER NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (TYPE_ID) REFERENCES IDN_NOTIFICATION_TYPE(ID) ON DELETE CASCADE,
  CONSTRAINT ORG_NOTIFICATION_TEMPLATE_KEY_CONSTRAINT UNIQUE (TEMPLATE_KEY, TYPE_ID, TENANT_ID),
  CONSTRAINT ORG_NOTIFICATION_TEMPLATE_LOCALE_CONSTRAINT UNIQUE (LOCALE, TYPE_ID, TENANT_ID)
)
/
CREATE SEQUENCE IDN_NOTIFICATION_ORG_TEMPLATE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_NOTIFICATION_ORG_TEMPLATE_TRIG
  BEFORE INSERT
    ON IDN_NOTIFICATION_ORG_TEMPLATE
    REFERENCING NEW AS NEW
    FOR EACH ROW
  BEGIN
  SELECT IDN_NOTIFICATION_APP_TEMPLATE_SEQ.nextval INTO :NEW.CURSOR_KEY FROM dual;
  END;
/
CREATE TABLE IDN_NOTIFICATION_APP_TEMPLATE (
  ID INTEGER NOT NULL,
  TEMPLATE_KEY VARCHAR(50) NOT NULL,
  LOCALE VARCHAR(50) NOT NULL,
  SUBJECT VARCHAR(4000),
  BODY CLOB,
  FOOTER CLOB,
  CONTENT_TYPE VARCHAR(50),
  TYPE_ID INTEGER NOT NULL,
  APP_ID VARCHAR(255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (TYPE_ID) REFERENCES IDN_NOTIFICATION_TYPE(ID) ON DELETE CASCADE,
  CONSTRAINT APP_NOTIFICATION_TEMPLATE_KEY_CONSTRAINT UNIQUE (TEMPLATE_KEY, TYPE_ID, APP_ID, TENANT_ID),
  CONSTRAINT APP_NOTIFICATION_TEMPLATE_LOCALE_CONSTRAINT UNIQUE (LOCALE, TYPE_ID, APP_ID, TENANT_ID)
)
/
CREATE SEQUENCE IDN_NOTIFICATION_APP_TEMPLATE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_NOTIFICATION_APP_TEMPLATE_TRIG
  BEFORE INSERT
    ON IDN_NOTIFICATION_APP_TEMPLATE
    REFERENCING NEW AS NEW
    FOR EACH ROW
  BEGIN
  SELECT IDN_NOTIFICATION_APP_TEMPLATE_SEQ.nextval INTO :NEW.CURSOR_KEY FROM dual;
  END;
/
