DROP TABLE AM_ALERT_TYPES
/
DROP SEQUENCE AM_ALERT_TYPES_SEQ
/
CREATE TABLE  AM_ALERT_TYPES (
    ALERT_TYPE_ID INTEGER,
    ALERT_TYPE_NAME VARCHAR(255) NOT NULL ,
	STAKE_HOLDER VARCHAR(100) NOT NULL,
    PRIMARY KEY (ALERT_TYPE_ID))
/
CREATE SEQUENCE AM_ALERT_TYPES_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER AM_ALERT_TYPES_TRIG
    BEFORE INSERT
    ON AM_ALERT_TYPES
    REFERENCING NEW AS NEW
    FOR EACH ROW
        BEGIN
            SELECT AM_ALERT_TYPES_SEQ.nextval INTO :NEW.ALERT_TYPE_ID FROM dual;
        END;
/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalResponseTime', 'publisher')
/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalBackendTime', 'publisher')
/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalRequestsPerMin', 'subscriber')
/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalRequestPattern', 'subscriber')
/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('UnusualIPAccess', 'subscriber')
/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('FrequentTierLimitHitting', 'subscriber')
/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('ApiHealthMonitor', 'publisher')
/
BEGIN
  DECLARE
  COUNT_CONSTRAINTS INTEGER;
  BEGIN
    COUNT_CONSTRAINTS :=0;
    SELECT COUNT(*) INTO COUNT_CONSTRAINTS
      FROM USER_CONSTRAINTS
      WHERE CONSTRAINT_NAME = 'END_POINT_CONSTRAINT';
     IF COUNT_CONSTRAINTS > 0 THEN
      EXECUTE IMMEDIATE 'ALTER TABLE AM_CERTIFICATE_METADATA DROP CONSTRAINT END_POINT_CONSTRAINT';
     END IF;
  END;
END;
/
