--Create Oracle New User and Connect
--------------------------------------
--CREATE USER apimuser IDENTIFIED BY wso2; 
--GRANT CONNECT,RESOURCE to apimuser WITH ADMIN OPTION; 
--CONNECT apimuser/wso2
--

CREATE TABLE IDN_BASE_TABLE (
            PRODUCT_NAME VARCHAR2 (20),
            PRIMARY KEY (PRODUCT_NAME)
)
/

INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server')
/

CREATE TABLE IDN_OAUTH_CONSUMER_APPS (
            CONSUMER_KEY VARCHAR2 (512),
            CONSUMER_SECRET VARCHAR2 (512),
            USERNAME VARCHAR2 (255),
            TENANT_ID INTEGER DEFAULT 0,
            APP_NAME VARCHAR2 (255),
            OAUTH_VERSION VARCHAR2 (128),
            CALLBACK_URL VARCHAR2 (1024),
            LOGIN_PAGE_URL VARCHAR (1024),
            ERROR_PAGE_URL VARCHAR (1024),
            CONSENT_PAGE_URL VARCHAR (1024),
            GRANT_TYPES VARCHAR (1024),
            PRIMARY KEY (CONSUMER_KEY)
)
/

CREATE TABLE IDN_OAUTH1A_REQUEST_TOKEN (
            REQUEST_TOKEN VARCHAR2 (512),
            REQUEST_TOKEN_SECRET VARCHAR2 (512),
            CONSUMER_KEY VARCHAR2 (512),
            CALLBACK_URL VARCHAR2 (1024),
            SCOPE VARCHAR2(2048),
            AUTHORIZED VARCHAR2 (128),
            OAUTH_VERIFIER VARCHAR2 (512),
            AUTHZ_USER VARCHAR2 (512),
            PRIMARY KEY (REQUEST_TOKEN),
            FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY)
)
/


CREATE TABLE IDN_OAUTH1A_ACCESS_TOKEN (
            ACCESS_TOKEN VARCHAR2 (512),
            ACCESS_TOKEN_SECRET VARCHAR2 (512),
            CONSUMER_KEY VARCHAR2 (512),
            SCOPE VARCHAR2(2048),
            AUTHZ_USER VARCHAR2 (512),
            PRIMARY KEY (ACCESS_TOKEN),
            FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY)
)
/

CREATE TABLE IDN_OAUTH2_AUTHORIZATION_CODE (
            AUTHORIZATION_CODE VARCHAR2 (512),
            CONSUMER_KEY VARCHAR2 (512),
            CALLBACK_URL VARCHAR2 (1024),
            SCOPE VARCHAR2(2048),
            AUTHZ_USER VARCHAR2 (512),
            TIME_CREATED TIMESTAMP,
            VALIDITY_PERIOD NUMBER(19),
            PRIMARY KEY (AUTHORIZATION_CODE),
            FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY)
)
/

CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN (
			ACCESS_TOKEN VARCHAR2 (255),
			REFRESH_TOKEN VARCHAR2 (255),
			CONSUMER_KEY VARCHAR2 (255),
			AUTHZ_USER VARCHAR2 (255),
			USER_TYPE VARCHAR2 (25),
			TIME_CREATED TIMESTAMP,
			VALIDITY_PERIOD NUMBER(19),
			TOKEN_SCOPE VARCHAR2 (25),
			TOKEN_STATE VARCHAR2 (25) DEFAULT 'ACTIVE',
			TOKEN_STATE_ID VARCHAR (256) DEFAULT 'NONE',
			PRIMARY KEY (ACCESS_TOKEN),
            FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE ,
            CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY, AUTHZ_USER,USER_TYPE,TOKEN_SCOPE,TOKEN_STATE,TOKEN_STATE_ID)
)
/

CREATE TABLE IDN_OPENID_USER_RPS (
			USER_NAME VARCHAR(255) NOT NULL,
			TENANT_ID INTEGER DEFAULT 0,
			RP_URL VARCHAR(255) NOT NULL,
			TRUSTED_ALWAYS VARCHAR(128) DEFAULT 'FALSE',
			LAST_VISIT DATE NOT NULL,
			VISIT_COUNT INTEGER DEFAULT 0,
			DEFAULT_PROFILE_NAME VARCHAR(255) DEFAULT 'DEFAULT',
			PRIMARY KEY (USER_NAME, TENANT_ID, RP_URL))
/

CREATE TABLE AM_SUBSCRIBER (
    SUBSCRIBER_ID INTEGER,
    USER_ID VARCHAR2(50) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    EMAIL_ADDRESS VARCHAR2(256) NULL,
    DATE_SUBSCRIBED DATE NOT NULL,
    PRIMARY KEY (SUBSCRIBER_ID),
    UNIQUE (TENANT_ID,USER_ID)
)
/

CREATE SEQUENCE AM_SUBSCRIBER_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_SUBSCRIBER_TRIGGER
		            BEFORE INSERT
                    ON AM_SUBSCRIBER
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_SUBSCRIBER_SEQUENCE.nextval INTO :NEW.SUBSCRIBER_ID FROM dual;
                    END;
/
--TODO: Have to add ON UPDATE CASCADE for the FOREIGN KEY(SUBSCRIBER_ID) relation
CREATE TABLE AM_APPLICATION (
    APPLICATION_ID INTEGER,
    NAME VARCHAR2(100),
    SUBSCRIBER_ID INTEGER,
    APPLICATION_TIER VARCHAR2(50) DEFAULT 'Unlimited',
    CALLBACK_URL VARCHAR2(512),
    DESCRIPTION VARCHAR2(512),
	APPLICATION_STATUS VARCHAR2(50) DEFAULT 'APPROVED',
    FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE CASCADE,
    PRIMARY KEY(APPLICATION_ID),
    UNIQUE (NAME,SUBSCRIBER_ID)
)
/

CREATE SEQUENCE AM_APPLICATION_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_APPLICATION_TRIGGER
		            BEFORE INSERT
                    ON AM_APPLICATION
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_APPLICATION_SEQUENCE.nextval INTO :NEW.APPLICATION_ID FROM dual;
                    END;
/

CREATE TABLE AM_API (
    API_ID INTEGER,
    API_PROVIDER VARCHAR2(256),
    API_NAME VARCHAR2(256),
    API_VERSION VARCHAR2(30),
    CONTEXT VARCHAR2(256),
    PRIMARY KEY(API_ID),
    UNIQUE (API_PROVIDER,API_NAME,API_VERSION)
)
/

CREATE SEQUENCE AM_API_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_API_TRIGGER
		            BEFORE INSERT
                    ON AM_API
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_API_SEQUENCE.nextval INTO :NEW.API_ID FROM dual;
                    END;
/

CREATE TABLE AM_API_URL_MAPPING (
    URL_MAPPING_ID INTEGER,
    API_ID INTEGER NOT NULL,
    HTTP_METHOD VARCHAR(20) NULL,
    AUTH_SCHEME VARCHAR(50) NULL,
    URL_PATTERN VARCHAR(512) NULL,
    THROTTLING_TIER varchar(512) DEFAULT NULL,
    PRIMARY KEY(URL_MAPPING_ID)
)
/

CREATE SEQUENCE AM_API_URL_MAPPING_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_API_URL_MAPPING_TRIGGER
		            BEFORE INSERT
                    ON AM_API_URL_MAPPING
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_API_URL_MAPPING_SEQUENCE.nextval INTO :NEW.URL_MAPPING_ID FROM dual;
                    END;
/

--TODO: Have to add ON UPDATE CASCADE for the FOREIGN KEY(SUBSCRIPTION_ID) relation
CREATE TABLE AM_SUBSCRIPTION (
    SUBSCRIPTION_ID INTEGER,
    TIER_ID VARCHAR2(50),
    API_ID INTEGER,
    LAST_ACCESSED DATE NULL,
    APPLICATION_ID INTEGER,
    SUB_STATUS VARCHAR(50),
    FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
    FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    PRIMARY KEY (SUBSCRIPTION_ID)
)
/

CREATE SEQUENCE AM_SUBSCRIPTION_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_SUBSCRIPTION_TRIGGER
		            BEFORE INSERT
                    ON AM_SUBSCRIPTION
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_SUBSCRIPTION_SEQUENCE.nextval INTO :NEW.SUBSCRIPTION_ID FROM dual;
                    END;
/
--TODO: Have to add ON UPDATE CASCADE for the FOREIGN KEY(APPLICATION_ID) and FOREIGN KEY(API_ID) relations
CREATE TABLE AM_SUBSCRIPTION_KEY_MAPPING (
    SUBSCRIPTION_ID INTEGER,
    ACCESS_TOKEN VARCHAR2(512),
    KEY_TYPE VARCHAR2(512) NOT NULL,
    FOREIGN KEY(SUBSCRIPTION_ID) REFERENCES AM_SUBSCRIPTION(SUBSCRIPTION_ID) ON DELETE CASCADE,
    PRIMARY KEY(SUBSCRIPTION_ID,ACCESS_TOKEN)
)
/
--TODO: Have to add ON UPDATE CASCADE for the FOREIGN KEY(APPLICATION_ID) relation
CREATE TABLE AM_APPLICATION_KEY_MAPPING (
    APPLICATION_ID INTEGER,
    CONSUMER_KEY VARCHAR2(512),
    KEY_TYPE VARCHAR2(512) NOT NULL,
    STATE VARCHAR2(30) NOT NULL,
    FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
    PRIMARY KEY(APPLICATION_ID,KEY_TYPE)
)
/

CREATE TABLE AM_APPLICATION_REGISTRATION (
    REG_ID INTEGER ,
    SUBSCRIBER_ID INTEGER,
    WF_REF VARCHAR2(255) NOT NULL,
    APP_ID INTEGER,
    TOKEN_TYPE VARCHAR2(30),
    ALLOWED_DOMAINS VARCHAR2(256),
    VALIDITY_PERIOD NUMBER(19),
    UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE),
    FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID),
    FOREIGN KEY(APP_ID) REFERENCES AM_APPLICATION(APPLICATION_ID),
    PRIMARY KEY (REG_ID)
)
/
--TODO: Have to add ON UPDATE CASCADE for the FOREIGN KEY(API_ID) relation
CREATE TABLE AM_API_LC_EVENT (
    EVENT_ID INTEGER,
    API_ID INTEGER NOT NULL,
    PREVIOUS_STATE VARCHAR2(50),
    NEW_STATE VARCHAR2(50) NOT NULL,
    USER_ID VARCHAR2(50) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    EVENT_DATE DATE NOT NULL,
    FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    PRIMARY KEY (EVENT_ID)
)
/

CREATE TABLE AM_APP_KEY_DOMAIN_MAPPING (
    CONSUMER_KEY VARCHAR(255),
    AUTHZ_DOMAIN VARCHAR(255) DEFAULT 'ALL',
    PRIMARY KEY (CONSUMER_KEY,AUTHZ_DOMAIN),
    FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY)
)
/

CREATE TABLE AM_API_COMMENTS (
    COMMENT_ID INTEGER,
    COMMENT_TEXT VARCHAR2(512),
    COMMENTED_USER VARCHAR2(255),
    DATE_COMMENTED DATE NOT NULL,
    API_ID INTEGER NOT NULL,
    FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    PRIMARY KEY (COMMENT_ID)
)
/

CREATE SEQUENCE AM_API_COMMENTS_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_API_COMMENTS_TRIGGER
		            BEFORE INSERT
                    ON AM_API_COMMENTS
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_API_COMMENTS_SEQUENCE.nextval INTO :NEW.COMMENT_ID FROM dual;
                    END;
/

CREATE TABLE AM_API_RATINGS (
    RATING_ID INTEGER,
    API_ID INTEGER,
    RATING INTEGER,
    SUBSCRIBER_ID INTEGER,
    FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE CASCADE,
    PRIMARY KEY (RATING_ID)
)
/

CREATE SEQUENCE AM_API_RATINGS_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_API_RATINGS_TRIGGER
		            BEFORE INSERT
                    ON AM_API_RATINGS
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_API_RATINGS_SEQUENCE.nextval INTO :NEW.RATING_ID FROM dual;
                    END;
/

CREATE TABLE AM_TIER_PERMISSIONS (
    TIER_PERMISSIONS_ID INTEGER,
    TIER VARCHAR2(50) NOT NULL,
    PERMISSIONS_TYPE VARCHAR2(50) NOT NULL,
    ROLES VARCHAR2(512) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY(TIER_PERMISSIONS_ID)
)
/

CREATE SEQUENCE AM_TIER_PERMISSIONS_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_TIER_PERMISSIONS_TRIGGER
		            BEFORE INSERT
                    ON AM_TIER_PERMISSIONS
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_TIER_PERMISSIONS_SEQUENCE.nextval INTO :NEW.TIER_PERMISSIONS_ID FROM dual;
                    END;
/

CREATE SEQUENCE AM_API_LC_EVENT_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_API_LC_EVENT_TRIGGER
		            BEFORE INSERT
                    ON AM_API_LC_EVENT
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_API_LC_EVENT_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual;
                    END;
/

CREATE TABLE IDN_THRIFT_SESSION (
                 SESSION_ID VARCHAR2(255) NOT NULL,
                 USER_NAME VARCHAR2(255) NOT NULL,
                 CREATED_TIME VARCHAR2(255) NOT NULL,
                 LAST_MODIFIED_TIME VARCHAR2(255) NOT NULL,
                 PRIMARY KEY (SESSION_ID)
)
/

CREATE TABLE AM_EXTERNAL_STORES (
    APISTORE_ID INTEGER,
    API_ID INTEGER,
    STORE_ID VARCHAR2(255) NOT NULL,
    STORE_DISPLAY_NAME VARCHAR2(255) NOT NULL,
    STORE_ENDPOINT VARCHAR2(255) NOT NULL,
    STORE_TYPE VARCHAR2(255) NOT NULL,
    FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    PRIMARY KEY (APISTORE_ID)
)
/

CREATE TABLE AM_WORKFLOWS(
    WF_ID INTEGER,
    WF_REFERENCE VARCHAR(255) NOT NULL,
    WF_TYPE VARCHAR(255) NOT NULL,
    WF_STATUS VARCHAR(255) NOT NULL,
    WF_CREATED_TIME TIMESTAMP,
    WF_UPDATED_TIME TIMESTAMP,
    WF_STATUS_DESC VARCHAR(1000),
    TENANT_ID INTEGER,
    TENANT_DOMAIN VARCHAR(255),
    WF_EXTERNAL_REFERENCE VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (WF_ID)
)
/
CREATE TABLE IDN_OAUTH2_SCOPE (
  SCOPE_ID INTEGER,
  SCOPE_KEY VARCHAR2 (100) NOT NULL,
  NAME VARCHAR2 (255) NULL,
  DESCRIPTION VARCHAR2 (512) NULL,
  TENANT_ID INTEGER DEFAULT 0 NOT NULL ,
  PRIMARY KEY (SCOPE_ID))
/
CREATE SEQUENCE IDN_OAUTH2_SCOPE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TABLE IDN_OAUTH2_RESOURCE_SCOPE (
  "RESOURCE" VARCHAR2 (255) NOT NULL,
  SCOPE_ID INTEGER NOT NULL,
  PRIMARY KEY ("RESOURCE"),
  FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE
)
/
CREATE TABLE AM_API_SCOPES (
   API_ID  INTEGER NOT NULL,
   SCOPE_ID  INTEGER NOT NULL,
   FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE,
   FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE
)
/
CREATE SEQUENCE AM_APP_REGISTRATION_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_APP_REGISTRATION_TRIGGER
		            BEFORE INSERT
                    ON AM_APPLICATION_REGISTRATION
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_APP_REGISTRATION_SEQUENCE.nextval INTO :NEW.REG_ID FROM dual;
                    END;
/

CREATE SEQUENCE AM_WORKFLOWS_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_WORKFLOWS_TRIGGER
		            BEFORE INSERT
                    ON AM_WORKFLOWS
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_WORKFLOWS_SEQUENCE.nextval INTO :NEW.WF_ID FROM dual;
                    END;
/

CREATE SEQUENCE AM_EXTERNAL_STORES_SEQUENCE START WITH 1 INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER AM_EXTERNAL_STORES_TRIGGER
		            BEFORE INSERT
                    ON AM_EXTERNAL_STORES
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_EXTERNAL_STORES_SEQUENCE.nextval INTO :NEW.APISTORE_ID FROM dual;
                    END;
/

CREATE TABLE IDN_APPMGT_APP (
                           ID NUMBER,
	                         APP_NAME VARCHAR (255) NOT NULL ,
                           USERNAME VARCHAR (255) NOT NULL ,
                           TENANT_ID NUMBER NOT NULL,
	                         USER_STORE VARCHAR (255) NOT NULL,
	                         ROLE_CLAIM VARCHAR (512),
                           PRIMARY KEY (ID)
)
/

CREATE SEQUENCE IDN_APPMGT_APP_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_APPMGT_APP_TRIGGER
                    BEFORE INSERT
                    ON IDN_APPMGT_APP
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT IDN_APPMGT_APP_SEQUENCE.nextval INTO :NEW.ID FROM dual;
                    END;
/
CREATE TABLE IDN_APPMGT_CLIENT (
            ID NUMBER,
            CLIENT_ID VARCHAR (512) NOT NULL,
	          CLIENT_SECRETE VARCHAR (512),
	          TENANT_ID NUMBER NOT NULL,
            CLIENT_TYPE VARCHAR (255) NOT NULL ,
	          APP_ID NUMBER NOT NULL,
            PRIMARY KEY (ID),
            FOREIGN KEY (APP_ID) REFERENCES IDN_APPMGT_APP (ID) ON DELETE CASCADE
)
/
CREATE SEQUENCE IDN_APPMGT_CLIENT_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_APPMGT_CLIENT_TRIGGER
                    BEFORE INSERT
                    ON IDN_APPMGT_CLIENT
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT IDN_APPMGT_CLIENT_SEQUENCE.nextval INTO :NEW.ID FROM dual;
                    END;
/
CREATE TABLE IDN_APPMGT_STEP (
            ID NUMBER,
	          STEP_ORDER NUMBER DEFAULT 1,
            APP_ID NUMBER NOT NULL ,
            PRIMARY KEY (ID),
            FOREIGN KEY (APP_ID) REFERENCES IDN_APPMGT_APP (ID) ON DELETE CASCADE
)
/
CREATE SEQUENCE IDN_APPMGT_STEP_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_APPMGT_STEP_TRIGGER
                    BEFORE INSERT
                    ON IDN_APPMGT_STEP
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                     SELECT IDN_APPMGT_STEP_SEQUENCE.nextval INTO :NEW.ID FROM dual;
                    END;
/
CREATE TABLE IDN_APPMGT_STEP_IDP (
            STEP_ID NUMBER NOT NULL,
            IDP_NAME VARCHAR (255) NOT NULL,
	          AUTHENTICATOR_NAME VARCHAR (255) NOT NULL,
            PRIMARY KEY (STEP_ID, IDP_NAME, AUTHENTICATOR_NAME),
            FOREIGN KEY (STEP_ID) REFERENCES IDN_APPMGT_STEP (ID) ON DELETE CASCADE
)
/
CREATE TABLE IDN_APPMGT_CLAIM_MAPPING (
	          ID NUMBER,
	          IDP_CLAIM VARCHAR (512) NOT NULL ,
            SP_CLAIM VARCHAR (512) NOT NULL ,
	          APP_ID NUMBER NOT NULL,
            TENANT_ID NUMBER NOT NULL,
            PRIMARY KEY (ID),
            FOREIGN KEY (APP_ID) REFERENCES IDN_APPMGT_APP (ID) ON DELETE CASCADE
)
/
CREATE SEQUENCE IDN_APMGT_CLAIM_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_APMGT_CLAIM_MAP_TRG
                    BEFORE INSERT
                    ON IDN_APPMGT_CLAIM_MAPPING
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                     SELECT IDN_APMGT_CLAIM_MAP_SEQ.nextval INTO :NEW.ID FROM dual;
                    END;
/
CREATE TABLE IDN_APPMGT_ROLE_MAPPING (
	    ID NUMBER,
	    IDP_ROLE VARCHAR (255) NOT NULL ,
      SP_ROLE VARCHAR (255) NOT NULL ,
	    APP_ID NUMBER NOT NULL,
      TENANT_ID NUMBER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (APP_ID) REFERENCES IDN_APPMGT_APP (ID) ON DELETE CASCADE
)
/
CREATE SEQUENCE IDN_APMGT_ROLE_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER IDN_APMGT_ROLE_MAP_SEQ_TRG
                    BEFORE INSERT
                    ON IDN_APPMGT_ROLE_MAPPING
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT IDN_APMGT_ROLE_MAP_SEQ.nextval INTO :NEW.ID FROM dual;
                    END;
/

CREATE TABLE AM_API_DEFAULT_VERSION (
            DEFAULT_VERSION_ID NUMBER, 
            API_NAME VARCHAR(256) NOT NULL ,
            API_PROVIDER VARCHAR(256) NOT NULL , 
            DEFAULT_API_VERSION VARCHAR(30) , 
            PUBLISHED_DEFAULT_API_VERSION VARCHAR(30) ,
            PRIMARY KEY (DEFAULT_VERSION_ID)
)
/
CREATE SEQUENCE AM_API_DEFAULT_VERSION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER AM_API_DEFAULT_VERSION_TRG
                    BEFORE INSERT
                    ON AM_API_DEFAULT_VERSION
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT AM_API_DEFAULT_VERSION_SEQ.nextval INTO :NEW.DEFAULT_VERSION_ID FROM dual;
                    END;
/
CREATE INDEX IDX_SUB_APP_ID ON AM_SUBSCRIPTION (APPLICATION_ID, SUBSCRIPTION_ID)
/

CREATE INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY, AUTHZ_USER, TOKEN_STATE, USER_TYPE)
/


