-- ABS-42375/ ABS-61217

-- LICENSEINFO table --
CREATE SEQUENCE SEQ_LICENSEINFO
START WITH 1
INCREMENT BY 1;

CREATE TABLE LICENSEINFO
(
  LICENSEINFO_ID    NUMBER NOT NULL,
  LICENSE           BLOB NOT NULL,
  LICENSEFILENAME   VARCHAR2(30) NOT NULL,
  CREATIONTIMESTAMP DATE NOT NULL,
  REMARK            VARCHAR2(500) NULL,
  SYSTEMUSER_ID     NUMBER NOT NULL,
  TIMESTAMP         DATE NOT NULL
)
;

COMMENT ON COLUMN LICENSEINFO.LICENSE
  IS 'Store the license file (encrypted file).';
COMMENT ON COLUMN LICENSEINFO.LICENSEFILENAME
  IS 'Filename of the license file (without the path).';
COMMENT ON COLUMN LICENSEINFO.REMARK
  IS 'Free text to enter additional information on the license version.';

ALTER TABLE LICENSEINFO
  ADD CONSTRAINT XPKLICENSEINFO PRIMARY KEY (LICENSEINFO_ID);
  
CREATE UNIQUE INDEX XAK1LICENSEINFO ON LICENSEINFO (LICENSEFILENAME ASC);

-- LICENSEINFOEVENT table --
CREATE SEQUENCE SEQ_LICENSEINFOEVENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE LICENSEINFOEVENT
(
  LICENSEINFOEVENT_ID  NUMBER NOT NULL,
  LICENSEINFO_ID       NUMBER NOT NULL,
  APPLICATIONSERVER_ID NUMBER NOT NULL,
  SEQUENCENUMBER       NUMBER NOT NULL,
  ACTIVATIONTIMESTAMP  DATE NOT NULL,
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
);

ALTER TABLE LICENSEINFOEVENT
  ADD CONSTRAINT XPKLICENSEINFOEVENT PRIMARY KEY (LICENSEINFOEVENT_ID);
  
CREATE UNIQUE INDEX XAK1LICENSEINFOEVENT ON LICENSEINFOEVENT (LICENSEINFO_ID ASC, APPLICATIONSERVER_ID ASC, SEQUENCENUMBER ASC);

CREATE INDEX XIE1LICENSEINFOEVENT ON LICENSEINFOEVENT (APPLICATIONSERVER_ID ASC, SEQUENCENUMBER ASC);
CREATE INDEX XIE2LICENSEINFOEVENT ON LICENSEINFOEVENT (APPLICATIONSERVER_ID ASC, ACTIVATIONTIMESTAMP ASC);
 
CREATE INDEX XIF1LICENSEINFOEVENT ON LICENSEINFOEVENT (LICENSEINFO_ID ASC);
CREATE INDEX XIF2LICENSEINFOEVENT ON LICENSEINFOEVENT (APPLICATIONSERVER_ID ASC);

ALTER TABLE LICENSEINFOEVENT
  ADD CONSTRAINT LICENSEINFOEVENT_LI FOREIGN KEY (LICENSEINFO_ID)
  REFERENCES LICENSEINFO (LICENSEINFO_ID) ON DELETE CASCADE;
ALTER TABLE LICENSEINFOEVENT
  ADD CONSTRAINT LICENSEINFOEVENT_AS FOREIGN KEY (APPLICATIONSERVER_ID)
  REFERENCES APPLICATIONSERVER (APPLICATIONSERVER_ID) ON DELETE CASCADE;

-- APPLICATIONSERVERMESSAGE table --
CREATE SEQUENCE SEQ_APPLICATIONSERVERMESSAGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE APPLICATIONSERVERMESSAGE
(
  APPLICATIONSERVERMESSAGE_ID NUMBER NOT NULL,
  MESSAGETYPE                 NUMBER NOT NULL CONSTRAINT MESSAGETYPE_VALID1_1
                              CHECK (MESSAGETYPE IN (1, 2)),
  FACTORYDESCRIPTION          VARCHAR2(200) NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
)
;

COMMENT ON COLUMN APPLICATIONSERVERMESSAGE.MESSAGETYPE
  IS '1 = Rolling patch - Server shut down
2 = New license file
';
 
ALTER TABLE APPLICATIONSERVERMESSAGE
  ADD CONSTRAINT XPKAPPLICATIONSERVERMESSAGE PRIMARY KEY (APPLICATIONSERVERMESSAGE_ID);

insert into applicationservermessage
select 1,
       1,
       'The Server Application to which you are connected will be shut down at scheduled time:',
       getminsystemuser(),
       sysdate
from dual;
commit;

insert into applicationservermessage
select 2,
       2,
       'A new license file has been activated. Please restart your client application.',
       getminsystemuser(),
       sysdate
from dual;
commit;

-- APPLICATIONSERVERMESSAGE_DESC table --
CREATE TABLE APPLICATIONSERVERMESSAGE_DESC
(
  APPLICATIONSERVERMESSAGE_ID NUMBER NOT NULL,
  LANGUAGE_ID                 NUMBER NOT NULL,
  DESCRIPTION                 VARCHAR2(200) NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
);

CREATE INDEX XIF1APPLICATIONSERVERMESSAGE_D ON APPLICATIONSERVERMESSAGE_DESC (APPLICATIONSERVERMESSAGE_ID ASC);
CREATE INDEX XIF2APPLICATIONSERVERMESSAGE_D ON APPLICATIONSERVERMESSAGE_DESC (LANGUAGE_ID ASC);

ALTER TABLE APPLICATIONSERVERMESSAGE_DESC
  ADD CONSTRAINT APPLICATIONSERVERMESSAGE_D_ASM FOREIGN KEY (APPLICATIONSERVERMESSAGE_ID)
  REFERENCES APPLICATIONSERVERMESSAGE (APPLICATIONSERVERMESSAGE_ID) ON DELETE CASCADE;
  
ALTER TABLE APPLICATIONSERVERMESSAGE_DESC
  ADD CONSTRAINT APPLICATIONSERVERMESSAGE_D_L FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES (LANGUAGE_ID);

insert into applicationservermessage_desc asmd
select 1,
       sapm.language_id,
       sapm.shutdownmessage,
       sapm.systemuser_id,
       sysdate
from shutdownappserverclmsg sapm;
commit;

-- SYSTEMUSER  table --
ALTER TABLE SYSTEMUSER ADD (MANAGELICENSES VARCHAR2(1) DEFAULT 'N' NOT NULL 
                                           CONSTRAINT YN_VALIDATIONFIELD409 CHECK (MANAGELICENSES IN ('Y', 'N')));

