IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'AM_SYSTEM_APPS') AND TYPE IN (N'U'))
CREATE TABLE AM_SYSTEM_APPS (
  ID INTEGER IDENTITY,
  NAME VARCHAR(50) NOT NULL,
  CONSUMER_KEY VARCHAR(512) NOT NULL,
  CONSUMER_SECRET VARCHAR(512) NOT NULL,
  CREATED_TIME DATETIME2(6) DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (NAME),
  UNIQUE (CONSUMER_KEY),
  PRIMARY KEY (ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_CLIENT_CERTIFICATE]') AND TYPE IN (N'U'))
CREATE TABLE AM_API_CLIENT_CERTIFICATE (
    TENANT_ID INTEGER NOT NULL,
    ALIAS VARCHAR(45) NOT NULL,
    API_ID INTEGER NOT NULL,
    CERTIFICATE VARBINARY(MAX) NOT NULL,
    REMOVED BIT NOT NULL DEFAULT 0,
    TIER_NAME VARCHAR(512),
    PRIMARY KEY (ALIAS, TENANT_ID, REMOVED),
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE
);

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

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_MONETIZATION_USAGE]') AND TYPE IN (N'U'))

CREATE TABLE AM_MONETIZATION_USAGE (
	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)
);

DECLARE @con_com as VARCHAR(8000);
SET @con_com = (SELECT name from sys.objects where parent_object_id=object_id('AM_API_COMMENTS') AND type='PK');
EXEC('ALTER TABLE AM_API_COMMENTS
drop CONSTRAINT ' + @con_com);
ALTER TABLE AM_API_COMMENTS
DROP COLUMN COMMENT_ID;
ALTER TABLE AM_API_COMMENTS
ADD COMMENT_ID VARCHAR(255) NOT NULL DEFAULT NEWID();
ALTER TABLE AM_API_COMMENTS
ADD PRIMARY KEY (COMMENT_ID);

DECLARE @con_rat as VARCHAR(8000);
SET @con_rat = (SELECT name from sys.objects where parent_object_id=object_id('AM_API_RATINGS') AND type='PK');
EXEC('ALTER TABLE AM_API_RATINGS
drop CONSTRAINT ' + @con_rat);
ALTER TABLE AM_API_RATINGS
DROP COLUMN RATING_ID;
ALTER TABLE AM_API_RATINGS
ADD RATING_ID VARCHAR(255) NOT NULL DEFAULT NEWID();
ALTER TABLE AM_API_RATINGS
ADD PRIMARY KEY (RATING_ID);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_NOTIFICATION_SUBSCRIBER]') AND TYPE IN (N'U'))
CREATE TABLE AM_NOTIFICATION_SUBSCRIBER (
    UUID VARCHAR(255),
    CATEGORY VARCHAR(255),
    NOTIFICATION_METHOD VARCHAR(255),
    SUBSCRIBER_ADDRESS VARCHAR(255) NOT NULL,
    PRIMARY KEY(UUID, SUBSCRIBER_ADDRESS)
);

ALTER TABLE AM_EXTERNAL_STORES
ADD LAST_UPDATED_TIME DATETIME DEFAULT GETDATE();

ALTER TABLE AM_API
    ADD API_TYPE VARCHAR(10) NULL DEFAULT NULL;

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_PRODUCT_MAPPING]') AND TYPE IN (N'U'))

CREATE TABLE AM_API_PRODUCT_MAPPING (
  API_PRODUCT_MAPPING_ID INTEGER IDENTITY(1,1),
  API_ID INTEGER,
  URL_MAPPING_ID INTEGER,
  FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
  FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
  PRIMARY KEY(API_PRODUCT_MAPPING_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_REVOKED_JWT]') AND TYPE IN (N'U'))
CREATE TABLE AM_REVOKED_JWT (
  UUID VARCHAR(255) NOT NULL,
  SIGNATURE VARCHAR(2048) NOT NULL,
  EXPIRY_TIMESTAMP BIGINT NOT NULL,
  TENANT_ID INTEGER DEFAULT -1,
  TOKEN_TYPE VARCHAR(15) DEFAULT 'DEFAULT',
  TIME_CREATED DATETIME DEFAULT GETDATE(),
  PRIMARY KEY (UUID)
);

-- UMA tables --
IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_RESOURCE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_RESOURCE (
  ID INTEGER IDENTITY NOT NULL,
  RESOURCE_ID VARCHAR(255),
  RESOURCE_NAME VARCHAR(255),
  TIME_CREATED DATETIME NOT NULL,
  RESOURCE_OWNER_NAME VARCHAR(255),
  CLIENT_ID VARCHAR(255),
  TENANT_ID INTEGER DEFAULT -1234,
  USER_DOMAIN VARCHAR(50),
  PRIMARY KEY (ID)
);

CREATE INDEX IDX_RID ON IDN_UMA_RESOURCE (RESOURCE_ID);

CREATE INDEX IDX_USER ON IDN_UMA_RESOURCE (RESOURCE_OWNER_NAME, USER_DOMAIN);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_RESOURCE_META_DATA]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_RESOURCE_META_DATA (
  ID INTEGER IDENTITY NOT NULL,
  RESOURCE_IDENTITY INTEGER NOT NULL,
  PROPERTY_KEY VARCHAR(40),
  PROPERTY_VALUE VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_RESOURCE_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_RESOURCE_SCOPE (
  ID INTEGER IDENTITY NOT NULL,
  RESOURCE_IDENTITY INTEGER NOT NULL,
  SCOPE_NAME VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
);

CREATE INDEX IDX_RS ON IDN_UMA_RESOURCE_SCOPE (SCOPE_NAME);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_PERMISSION_TICKET]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_PERMISSION_TICKET (
  ID INTEGER IDENTITY NOT NULL,
  PT VARCHAR(255) NOT NULL,
  TIME_CREATED DATETIME NOT NULL,
  EXPIRY_TIME DATETIME NOT NULL,
  TICKET_STATE VARCHAR(25) DEFAULT 'ACTIVE',
  TENANT_ID INTEGER     DEFAULT -1234,
  PRIMARY KEY (ID)
);

CREATE INDEX IDX_PT ON IDN_UMA_PERMISSION_TICKET (PT);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_PT_RESOURCE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_PT_RESOURCE (
  ID INTEGER IDENTITY NOT NULL,
  PT_RESOURCE_ID INTEGER NOT NULL,
  PT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_ID) REFERENCES IDN_UMA_PERMISSION_TICKET (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_RESOURCE (ID)
);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_PT_RESOURCE_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_PT_RESOURCE_SCOPE (
  ID INTEGER IDENTITY NOT NULL,
  PT_RESOURCE_ID INTEGER NOT NULL,
  PT_SCOPE_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_PT_RESOURCE (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_SCOPE_ID) REFERENCES IDN_UMA_RESOURCE_SCOPE (ID)
);
