DROP TABLE IF EXISTS IDN_NOTIFICATION_TYPE;
DROP SEQUENCE IF EXISTS IDN_NOTIFICATION_TYPE_SEQ;
CREATE SEQUENCE IDN_NOTIFICATION_TYPE_SEQ;
CREATE TABLE IDN_NOTIFICATION_TYPE (
    ID INTEGER DEFAULT NEXTVAL('IDN_NOTIFICATION_TYPE_SEQ'),
    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)
);

DROP TABLE IF EXISTS IDN_NOTIFICATION_ORG_TEMPLATE;
DROP SEQUENCE IF EXISTS IDN_NOTIFICATION_ORG_TEMPLATE_SEQ;
CREATE SEQUENCE IDN_NOTIFICATION_ORG_TEMPLATE_SEQ;
CREATE TABLE IDN_NOTIFICATION_ORG_TEMPLATE (
    ID INTEGER DEFAULT NEXTVAL('IDN_NOTIFICATION_ORG_TEMPLATE_SEQ'),
    TEMPLATE_KEY VARCHAR(50) NOT NULL,
    LOCALE VARCHAR(50) NOT NULL,
    SUBJECT VARCHAR(4000),
    BODY TEXT,
    FOOTER TEXT,
    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)
);

DROP TABLE IF EXISTS IDN_NOTIFICATION_APP_TEMPLATE;
DROP SEQUENCE IF EXISTS IDN_NOTIFICATION_APP_TEMPLATE_SEQ;
CREATE SEQUENCE IDN_NOTIFICATION_APP_TEMPLATE_SEQ;
CREATE TABLE IDN_NOTIFICATION_APP_TEMPLATE (
    ID INTEGER DEFAULT NEXTVAL('IDN_NOTIFICATION_APP_TEMPLATE_SEQ'),
    TEMPLATE_KEY VARCHAR(50) NOT NULL,
    LOCALE VARCHAR(50) NOT NULL,
    SUBJECT VARCHAR(4000),
    BODY TEXT,
    FOOTER TEXT,
    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)
);
