-- ABS-67076/ ABS-83022

--------------------- HIVELOG ---------------------
CREATE SEQUENCE SEQ_HIVELOG
START WITH 1
INCREMENT BY 1;

CREATE TABLE HIVELOG
(
  HIVELOG_ID                NUMBER NOT NULL,
  ROUTE_ID                  NUMBER NOT NULL,
  TYPE                      NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT CHK_HIVELOG_TYPE CHECK (TYPE IN (1, 2, 3, 4)),
  LOCATIONSREQUESTED        NUMBER DEFAULT 0 NOT NULL,
  LOCATIONSRETURNED         NUMBER DEFAULT 0 NOT NULL,
  LOCATIONSOCCUPIED         NUMBER DEFAULT 0 NOT NULL,
  OPTIMISATIONMEASURE       NUMBER DEFAULT 0 NOT NULL,
  FRAGMENTATIONMEASURE      NUMBER DEFAULT 0 NOT NULL,
  RESERVATIONSDELETED       NUMBER DEFAULT 0 NOT NULL,
  ALREADYOCCUPIEDBYROUTE_ID NUMBER NULL,
  DISTANCEOUTOFPLACE        NUMBER DEFAULT 0 NOT NULL,
  STOREDBYUSER              NUMBER DEFAULT 0 NOT NULL,
  SHORTEDCUSTOMER_LINK_ID   NUMBER NULL,
  PLANT_ID                  NUMBER NOT NULL,
  LOGGEDDATE                DATE NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

COMMENT ON COLUMN HIVELOG.TYPE
  IS '1 = Optimisation algorithm
2 = Deleted reservations
3 = Occupied location
4 = Unexpected short
';
COMMENT ON COLUMN HIVELOG.LOCATIONSREQUESTED
  IS 'TYPE 1';
COMMENT ON COLUMN HIVELOG.LOCATIONSRETURNED
  IS 'TYPE 1';
COMMENT ON COLUMN HIVELOG.LOCATIONSOCCUPIED
  IS 'TYPE 1';
COMMENT ON COLUMN HIVELOG.OPTIMISATIONMEASURE
  IS 'TYPE 1';
COMMENT ON COLUMN HIVELOG.FRAGMENTATIONMEASURE
  IS 'TYPE 1';
COMMENT ON COLUMN HIVELOG.RESERVATIONSDELETED
  IS 'TYPE 2';
COMMENT ON COLUMN HIVELOG.ALREADYOCCUPIEDBYROUTE_ID
  IS 'TYPE 3';
COMMENT ON COLUMN HIVELOG.DISTANCEOUTOFPLACE
  IS 'TYPE 3';
COMMENT ON COLUMN HIVELOG.STOREDBYUSER
  IS 'ID of SystemUser, no FK, type 3 and 4';
COMMENT ON COLUMN HIVELOG.SHORTEDCUSTOMER_LINK_ID
  IS 'TYPE 4';

ALTER TABLE HIVELOG
  ADD CONSTRAINT XPKHIVELOG PRIMARY KEY (HIVELOG_ID);
  
CREATE INDEX XIF1HIVELOG ON HIVELOG (PLANT_ID ASC);

CREATE INDEX XIE1HIVELOG ON HIVELOG (PLANT_ID ASC, LOGGEDDATE ASC);
  
ALTER TABLE HIVELOG
  ADD CONSTRAINT FK_HIVELOG_PLANT_ID FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID);

--------------------- PURGETABLE ---------------------
INSERT INTO PURGETABLE
  (PURGETABLE_ID,
   TABLENAME,
   DESCRIPTION,
   REMARK,
   WHERECLAUSE,
   DATEFIELDNAME,
   HINT,
   PLANTFIELDNAME,
   BUSINESSUNITFIELDNAME,
   COMMITSIZE,
   SYSTEMUSER_ID,
   TIMESTAMP)
  VALUES (SEQ_PURGETABLE.NEXTVAL,
         'HiveLog',
         'Bulk packing assistant hive log file',
         NULL,
         NULL,
         'LOGGEDDATE',
         NULL,
         'PLANT_ID',
         NULL,
         5000,
         GETMINSYSTEMUSER(),
         SYSDATE);
COMMIT;