-- ABS-70598/ ABS-70737

-- GLOBALSETTING TABLE --
CREATE SEQUENCE SEQ_GLOBALSETTING
START WITH 1
INCREMENT BY 1;

CREATE TABLE GLOBALSETTING
(
  GLOBALSETTING_ID NUMBER NOT NULL,
  NAME             VARCHAR2(30) NOT NULL,
  GROUPNAME        VARCHAR2(30) NOT NULL,
  STRINGVALUE      VARCHAR2(100) NULL,
  NUMBERVALUE      NUMBER NULL,
  BOOLEANVALUE     VARCHAR2(1) NULL
                   CONSTRAINT YN_VALIDATIONFIELD444 CHECK (BOOLEANVALUE IN ('Y', 'N')),
  TYPEOFVALUE      VARCHAR2(1) NOT NULL,
  SYSTEMUSER_ID    NUMBER NOT NULL,
  TIMESTAMP        DATE NOT NULL,
  ACTIVE           VARCHAR2(1) DEFAULT 'Y' NOT NULL
                   CONSTRAINT YN_VALIDATIONFIELD445 CHECK (ACTIVE IN ('Y', 'N')),
  DESCRIPTION      VARCHAR2(1000) NULL
)
;

COMMENT ON COLUMN GLOBALSETTING.TYPEOFVALUE
  IS 'B = Boolean
I = Integer
S = String
F = Float
';

ALTER TABLE GLOBALSETTING
  ADD CONSTRAINT XPKGLOBALSETTING PRIMARY KEY (GLOBALSETTING_ID);
  
CREATE UNIQUE INDEX XAK1GLOBALSETTING ON GLOBALSETTING (NAME ASC);
CREATE INDEX XIE1GLOBALSETTING ON GLOBALSETTING (GROUPNAME ASC);
  
ALTER TABLE GLOBALSETTING
  ADD CONSTRAINT GLOBALSETTING_VALID1
  CHECK ((TYPEOFVALUE = 'S' AND STRINGVALUE IS NOT NULL AND BOOLEANVALUE IS NULL AND NUMBERVALUE IS NULL) OR (TYPEOFVALUE IN ('I', 'F') AND NUMBERVALUE IS NOT NULL AND STRINGVALUE IS NULL AND BOOLEANVALUE IS NULL) OR (TYPEOFVALUE = 'B' AND BOOLEANVALUE IS NOT NULL AND NUMBERVALUE IS NULL AND STRINGVALUE IS NULL));

INSERT INTO GLOBALSETTING(GLOBALSETTING_ID, NAME, GROUPNAME, STRINGVALUE, NUMBERVALUE, BOOLEANVALUE, TYPEOFVALUE, SYSTEMUSER_ID, TIMESTAMP, ACTIVE, DESCRIPTION)
SELECT
       SEQ_GLOBALSETTING.NEXTVAL,
       'ONLINELICENSE',
       'Online License',
       NULL,
       NULL,
       'N',
       'B',
       GETMINSYSTEMUSER(),
       SYSDATE,
       'Y',
       'Use Online License mechanism.'
FROM DUAL;
COMMIT;

INSERT INTO GLOBALSETTING(GLOBALSETTING_ID, NAME, GROUPNAME, STRINGVALUE, NUMBERVALUE, BOOLEANVALUE, TYPEOFVALUE, SYSTEMUSER_ID, TIMESTAMP, ACTIVE, DESCRIPTION)
SELECT
       SEQ_GLOBALSETTING.NEXTVAL,
       'LICENSECHECKINTERVAL',
       'Online License',
       NULL,
       60,
       NULL,
       'I',
       GETMINSYSTEMUSER(),
       SYSDATE,
       'Y',
       NULL
FROM DUAL;
COMMIT;

INSERT INTO GLOBALSETTING(GLOBALSETTING_ID, NAME, GROUPNAME, STRINGVALUE, NUMBERVALUE, BOOLEANVALUE, TYPEOFVALUE, SYSTEMUSER_ID, TIMESTAMP, ACTIVE, DESCRIPTION)
SELECT
       SEQ_GLOBALSETTING.NEXTVAL,
       'ENVIRONMENTCHECK',
       'Licensing General',
       NULL,
       NULL,
       'N',
       'B',
       GETMINSYSTEMUSER(),
       SYSDATE,
       'Y',
       'Use environment (Production / test) check to avoid test licenses being installed on a live environment.'
FROM DUAL;
COMMIT;