CREATE TABLE AM_SYSTEM_APPS (
  ID INTEGER NOT NULL,
  NAME VARCHAR (50) NOT NULL,
  CONSUMER_KEY VARCHAR (512) NOT NULL,
  CONSUMER_SECRET VARCHAR (512) NOT NULL,
  CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (ID)
)
/
CREATE SEQUENCE AM_SYSTEM_APP_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER AM_SYSTEM_APPS_TRIG
  BEFORE INSERT
  ON AM_SYSTEM_APPS
  REFERENCING NEW AS NEW
  FOR EACH ROW
     BEGIN
         SELECT SEQUENCE AM_SYSTEM_APP_SEQUENCE.nextval INTO :NEW.ID FROM dual;
     END;
/

CREATE TABLE AM_API_CLIENT_CERTIFICATE (
  TENANT_ID INTEGER NOT NULL,
  ALIAS VARCHAR2(45) NOT NULL,
  API_ID INTEGER NOT NULL,
  CERTIFICATE BLOB NOT NULL,
  REMOVED INTEGER DEFAULT 0 NOT NULL,
  TIER_NAME VARCHAR2 (512),
  FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE,
  PRIMARY KEY (ALIAS, TENANT_ID, REMOVED)
)
/

ALTER TABLE AM_POLICY_SUBSCRIPTION ADD (
  MONETIZATION_PLAN VARCHAR(25) DEFAULT NULL NULL, 
  FIXED_RATE VARCHAR(15) DEFAULT NULL NULL, 
  BILLING_CYCLE VARCHAR(15) DEFAULT NULL NULL, 
  PRICE_PER_REQUEST VARCHAR(15) DEFAULT NULL NULL, 
  CURRENCY VARCHAR(15) DEFAULT NULL NULL
)
/

CREATE TABLE AM_MONETIZATION_USAGE_PUBLISHER (
	ID VARCHAR(100) NOT NULL,
	STATE VARCHAR(50) NOT NULL,
	STATUS VARCHAR(50) NOT NULL,
	STARTED_TIME VARCHAR(50) NOT NULL,
	PUBLISHED_TIME VARCHAR(50) NOT NULL,
	PRIMARY KEY(ID)
)
/

ALTER TABLE AM_API_COMMENTS
MODIFY COMMENT_ID VARCHAR(255)
/

ALTER TABLE AM_API_RATINGS
MODIFY RATING_ID VARCHAR(255) NOT NULL
/

CREATE TABLE AM_NOTIFICATION_SUBSCRIBER (
    UUID VARCHAR2(255),
    CATEGORY VARCHAR2(255),
    NOTIFICATION_METHOD VARCHAR2(255),
    SUBSCRIBER_ADDRESS VARCHAR2(255) NOT NULL,
    PRIMARY KEY(UUID, SUBSCRIBER_ADDRESS)
)
/

DROP SEQUENCE AM_API_COMMENTS_SEQUENCE
/

DROP TRIGGER AM_API_COMMENTS_TRIGGER
/
