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_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)
)
/

INSERT INTO AM_APP_KEY_DOMAIN_MAPPING(CONSUMER_KEY,AUTHZ_DOMAIN)
SELECT IDN_OAUTH_CONSUMER_APPS.CONSUMER_KEY AS a , 'ALL' AS b
FROM IDN_OAUTH_CONSUMER_APPS;
/

ALTER TABLE AM_APPLICATION ADD CALLBACK_URL VARCHAR(512);

ALTER TABLE AM_SUBSCRIPTION  ADD SUB_STATUS VARCHAR(512) DEFAULT 'UNBLOCKED' NOT NULL;
