--ABS-84147 / ABS-88098

------------- SORTINGCABINETBOX --------------
CREATE SEQUENCE SEQ_SORTINGCABINETBOX
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTINGCABINETBOX (SORTINGCABINETBOX_ID NUMBER NOT NULL,
                                WORKSTATION_ID NUMBER NOT NULL,
                                BOXNUMBER NUMBER NOT NULL CONSTRAINT SORTINGCABINETBOX_BOXNUMBER CHECK(BOXNUMBER BETWEEN 1 AND 999),
                                ROWNUMBER NUMBER NOT NULL CONSTRAINT SORTINGCABINETBOX_ROWNUMBER CHECK(ROWNUMBER BETWEEN 0 AND 99),
                                POSITION NUMBER NOT NULL CONSTRAINT SORTINGCABINETBOX_POSITION CHECK(POSITION BETWEEN 0 AND 99),
                                CUSTOMER_ID NUMBER NOT NULL,
                                QUANTITY NUMBER NOT NULL,
                                LASTINTIMESTAMP DATE NOT NULL,
                                SYSTEMUSER_ID NUMBER NOT NULL,
                                TIMESTAMP DATE NOT NULL);
                               
COMMENT ON COLUMN SORTINGCABINETBOX.BOXNUMBER
IS '1-999';
COMMENT ON COLUMN SORTINGCABINETBOX.ROWNUMBER
IS '0-99';
COMMENT ON COLUMN SORTINGCABINETBOX.POSITION
IS '0-99';
COMMENT ON COLUMN SORTINGCABINETBOX.CUSTOMER_ID
IS 'Note: not a unique index. Initially there will only be 1 box per customer, but the option will be left open to have more than one per customer.';
COMMENT ON COLUMN SORTINGCABINETBOX.QUANTITY
IS 'The number of items in the box.';
COMMENT ON COLUMN SORTINGCABINETBOX.LASTINTIMESTAMP
IS 'Timestamp of last item in the box';

ALTER TABLE SORTINGCABINETBOX ADD CONSTRAINT XPKSORTINGCABINETBOX PRIMARY KEY(SORTINGCABINETBOX_ID);
CREATE INDEX XAK1SORTINGCABINETBOX ON SORTINGCABINETBOX(WORKSTATION_ID ASC, BOXNUMBER ASC); 
CREATE INDEX XIF1SORTINGCABINETBOX ON SORTINGCABINETBOX(WORKSTATION_ID ASC);
CREATE INDEX XIF2SORTINGCABINETBOX ON SORTINGCABINETBOX(CUSTOMER_ID ASC);

ALTER TABLE SORTINGCABINETBOX ADD CONSTRAINT FK_SORTINGCABINETBOX_WORKSTAT FOREIGN KEY(WORKSTATION_ID)
REFERENCES WORKSTATION(WORKSTATION_ID) ON DELETE CASCADE;
ALTER TABLE SORTINGCABINETBOX ADD CONSTRAINT FK_SORTINGCABINETBOX_CUSTOMER FOREIGN KEY(CUSTOMER_ID)
REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE;


---------------------------- SORTINGCABINETBOXCONTENTS ------------------

CREATE TABLE SORTINGCABINETBOXCONTENTS(SORTINGCABINETBOX_ID NUMBER NOT NULL,
                                       UNIQUERESIDENTIALITEM_ID NUMBER NOT NULL,
                                       SYSTEMUSER_ID NUMBER NOT NULL,
                                       TIMESTAMP DATE NOT NULL);
                                       
ALTER TABLE SORTINGCABINETBOXCONTENTS ADD CONSTRAINT XPKSORTINGCABINETBOXCONTENTS PRIMARY KEY(SORTINGCABINETBOX_ID, UNIQUERESIDENTIALITEM_ID);
CREATE INDEX XIF1SORTINGCABINETBOXCONTENTS ON SORTINGCABINETBOXCONTENTS(SORTINGCABINETBOX_ID ASC);
CREATE INDEX XIF2SORTINGCABINETBOXCONTENTS ON SORTINGCABINETBOXCONTENTS(UNIQUERESIDENTIALITEM_ID ASC);

ALTER TABLE SORTINGCABINETBOXCONTENTS ADD CONSTRAINT FK_SORTCABBOXCONT_SORTCABBOXID FOREIGN KEY(SORTINGCABINETBOX_ID)
REFERENCES SORTINGCABINETBOX(SORTINGCABINETBOX_ID) ON DELETE CASCADE;
ALTER TABLE SORTINGCABINETBOXCONTENTS ADD CONSTRAINT FK_SORTCABBOXCONT_UNIRESITEMID FOREIGN KEY(UNIQUERESIDENTIALITEM_ID)
REFERENCES UNIQUERESIDENTIALITEM(UNIQUERESIDENTIALITEM_ID) ON DELETE CASCADE;  


------------------------------------ SERIALDEVICE ---------------
INSERT INTO SERIALDEVICE(SERIALDEVICE_ID,
                         BRANDTYPE,
                         NAME,
                         DEVICETYPE,
                         BAUDRATE,
                         PARITY,
                         DATABITS,
                         STOPBITS,
                         ENDCHARACTER1,
                         ALIBIPRINTER,
                         ACCURACYLEVELWEIGHT)
              VALUES  (SEQ_SERIALDEVICE.NEXTVAL,
                       'Vega',
                       'Vega Sorting Cabinet',
                       'SRT',
                       9600,
                       'N',
                       8,
                       1,
                       0,
                       'N',
                       0);
COMMIT;