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.ID 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_ORG_TEMPLATE_SEQ.nextval INTO :NEW.ID 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.ID FROM dual;
              END;
/
CREATE TABLE SP_TRUSTED_APPS (
			ID INTEGER NOT NULL,
			SP_ID INTEGER NOT NULL,
			PLATFORM_TYPE VARCHAR(255) NOT NULL,
			APP_IDENTIFIER VARCHAR(255) NOT NULL,
			THUMBPRINTS VARCHAR(2048),
			IS_FIDO_TRUSTED NUMBER(1,0) DEFAULT 0,
			TENANT_ID INTEGER NOT NULL,
			PRIMARY KEY (ID),
			UNIQUE (SP_ID, PLATFORM_TYPE),
			FOREIGN KEY (SP_ID) REFERENCES SP_APP(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE SP_TRUSTED_APPS_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER SP_TRUSTED_APPS_TRIG
            BEFORE INSERT
            ON SP_TRUSTED_APPS
            REFERENCING NEW AS NEW
            FOR EACH ROW
                BEGIN
                    SELECT SP_TRUSTED_APPS_SEQ.nextval INTO :NEW.ID FROM dual;
                END;
/
