-- ABS-57263/ ABS-70215

-- LICENSETRANSTYPE table --
CREATE TABLE LICENSETRANSTYPE
(
  LICENSETRANSTYPE_ID NUMBER NOT NULL,
  DESCRIPTION         VARCHAR2(50) NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

ALTER TABLE LICENSETRANSTYPE
  ADD CONSTRAINT XPKLICENSETRANSTYPE PRIMARY KEY (LICENSETRANSTYPE_ID);

INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 1,
       'New module added',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 2,
       'Module removed',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 3,
       'Module parameter changed',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 4,
       'Module demo end date changed',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 5,
       'Number of full task users changed',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 6,
       'Number of single task users changed',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 7,
       'Plant added',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 8,
       'Plant removed',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 9,
       'License expiration date changed',
       0,
       GETCURRENTDATE()
       FROM DUAL;
       
INSERT INTO LICENSETRANSTYPE(LICENSETRANSTYPE_ID,DESCRIPTION,SYSTEMUSER_ID,TIMESTAMP)
SELECT 10,
       'New License',
       0,
       GETCURRENTDATE()
       FROM DUAL;
COMMIT;

-- LICENSEINFOADJUSTMENT table --
CREATE SEQUENCE SEQ_LICENSEINFOADJUSTMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE LICENSEINFOADJUSTMENT
(
  LICENSEINFOADJUSTMENT_ID NUMBER NOT NULL,
  FIRSTLICENSEINFO_ID      NUMBER NOT NULL,
  SECONDLICENSEINFO_ID     NUMBER NOT NULL,
  LICENSETRANSTYPE_ID      NUMBER NOT NULL,
  PLANT_ID                 NUMBER NULL,
  MODULECODE               VARCHAR2(50) NULL,
  MODULENAME               VARCHAR2(100) NULL,
  NEWVALUE                 VARCHAR2(50) NULL,
  OLDVALUE                 VARCHAR2(50) NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

COMMENT ON COLUMN LICENSEINFOADJUSTMENT.SECONDLICENSEINFO_ID
  IS 'FirstLicenseInfo_ID and SecondLicenseInfo_ID can be the same, in case of initial start when there is not yet an active LicenseInfo they will be set the same.';
COMMENT ON COLUMN LICENSEINFOADJUSTMENT.PLANT_ID
  IS 'Must be filled for transaction type 1 - 4, 7, 8';
COMMENT ON COLUMN LICENSEINFOADJUSTMENT.MODULECODE
  IS 'Must be filled for transaction type 1 - 4';
COMMENT ON COLUMN LICENSEINFOADJUSTMENT.MODULENAME
  IS 'Must be filled for transaction type 1 - 4';
COMMENT ON COLUMN LICENSEINFOADJUSTMENT.NEWVALUE
  IS 'Must be filled for transaction type 1, 3, 4, 5, 6, 9';
COMMENT ON COLUMN LICENSEINFOADJUSTMENT.OLDVALUE
  IS 'Must be filled for transaction type 3 - 6, 9';

ALTER TABLE LICENSEINFOADJUSTMENT
  ADD CONSTRAINT XPKLICENSEINFOADJUSTMENT PRIMARY KEY (LICENSEINFOADJUSTMENT_ID);
  
CREATE INDEX XIF1LICENSEINFOADJUSTMENT ON LICENSEINFOADJUSTMENT (FIRSTLICENSEINFO_ID ASC);
CREATE INDEX XIF2LICENSEINFOADJUSTMENT ON LICENSEINFOADJUSTMENT (SECONDLICENSEINFO_ID ASC);
CREATE INDEX XIF3LICENSEINFOADJUSTMENT ON LICENSEINFOADJUSTMENT (LICENSETRANSTYPE_ID ASC);
CREATE INDEX XIF4LICENSEINFOADJUSTMENT ON LICENSEINFOADJUSTMENT (PLANT_ID ASC);

ALTER TABLE LICENSEINFOADJUSTMENT
  ADD CONSTRAINT LICENSEINFOADJUSTMENT_FLI FOREIGN KEY (FIRSTLICENSEINFO_ID)
  REFERENCES LICENSEINFO (LICENSEINFO_ID) ON DELETE CASCADE;
  
ALTER TABLE LICENSEINFOADJUSTMENT
  ADD CONSTRAINT LICENSEINFOADJUSTMENT_SLI FOREIGN KEY (SECONDLICENSEINFO_ID)
  REFERENCES LICENSEINFO (LICENSEINFO_ID) ON DELETE CASCADE;
  
ALTER TABLE LICENSEINFOADJUSTMENT
  ADD CONSTRAINT LICENSEINFOADJUSTMENT_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;
  
ALTER TABLE LICENSEINFOADJUSTMENT
  ADD CONSTRAINT LICENSEINFOADJUSTMENT_LTT FOREIGN KEY (LICENSETRANSTYPE_ID)
  REFERENCES LICENSETRANSTYPE (LICENSETRANSTYPE_ID);