-- ABS-110804

--------------- UNIQUEITEMCLIENTPOOL ------------------

CREATE TABLE UNIQUEITEMCLIENTPOOL
(
    UNIQUEITEM_ID                    NUMBER NOT NULL,
    CLIENT_ID                        NUMBER NOT NULL,
    SYSTEMUSER_ID                    NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP                        DATE DEFAULT SYSDATE NOT NULL
); 

ALTER TABLE UNIQUEITEMCLIENTPOOL
      ADD CONSTRAINT XPKUNIQUEITEMCLIENTPOOL PRIMARY KEY (UNIQUEITEM_ID);
      
CREATE INDEX XIF2UNIQUEITEMCLIENTPOOL ON UNIQUEITEMCLIENTPOOL (CLIENT_ID ASC);
      
ALTER TABLE UNIQUEITEMCLIENTPOOL
      ADD CONSTRAINT FK_UNIQUEITEMCLIENTPOOL_UIT FOREIGN KEY (UNIQUEITEM_ID)
      REFERENCES UNIQUEITEM (UNIQUEITEM_ID);
ALTER TABLE UNIQUEITEMCLIENTPOOL
      ADD CONSTRAINT FK_UNIQUEITEMCLIENTPOOL_CLIENT FOREIGN KEY (CLIENT_ID)
      REFERENCES CLIENT (CLIENT_ID) ON DELETE CASCADE;


--------------- CLIENTDELNOTELINEPOOL ------------------

CREATE SEQUENCE SEQ_CLIENTDELNOTELINEPOOL
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLIENTDELNOTELINEPOOL
(
    CLIENTDELNOTELINEPOOL_ID          NUMBER NOT NULL,
    UNIQUEITEM_LINK_ID                NUMBER NOT NULL,
    CLIENTDELIVERYNOTELINE_ID         NUMBER NOT NULL,
    SCANTYPE                          NUMBER NOT NULL CONSTRAINT CHK_CLIENTDELNOTELINEPOOL_SCANTYPE CHECK (SCANTYPE IN (0,1)),
    SCANTIMESTAMP                     DATE NOT NULL,
    SYSTEMUSER_ID                     NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP                         TIMESTAMP DEFAULT SYSDATE NOT NULL
);

ALTER TABLE CLIENTDELNOTELINEPOOL
      ADD CONSTRAINT XPKCLIENTDELNOTELINEPOOL PRIMARY KEY (CLIENTDELNOTELINEPOOL_ID);
      
CREATE UNIQUE INDEX XAK1CLIENTDELNOTELINEPOOL ON CLIENTDELNOTELINEPOOL (UNIQUEITEM_LINK_ID ASC, CLIENTDELIVERYNOTELINE_ID ASC, SCANTYPE ASC);
CREATE INDEX XIE1CLIENTDELNOTELINEPOOL ON CLIENTDELNOTELINEPOOL (CLIENTDELIVERYNOTELINE_ID ASC, UNIQUEITEM_LINK_ID ASC, SCANTYPE ASC);
CREATE INDEX XIF1CLIENTDELNOTELINEPOOL ON CLIENTDELNOTELINEPOOL (UNIQUEITEM_LINK_ID ASC);
CREATE INDEX XIF2CLIENTDELNOTELINEPOOL ON CLIENTDELNOTELINEPOOL (CLIENTDELIVERYNOTELINE_ID ASC);

ALTER TABLE CLIENTDELNOTELINEPOOL
      ADD CONSTRAINT FK_CLIENTDELNOTELINEPOOL_UIT FOREIGN KEY (UNIQUEITEM_LINK_ID)
      REFERENCES UNIQUEITEM_LINK (UNIQUEITEM_LINK_ID)ON DELETE CASCADE;
ALTER TABLE CLIENTDELNOTELINEPOOL
      ADD CONSTRAINT FK_CLIENTDELNOTELINEPOOL_DELNOTE FOREIGN KEY (CLIENTDELIVERYNOTELINE_ID)
      REFERENCES CLIENTDELIVERYNOTELINE (CLIENTDELIVERYNOTELINE_ID) ON DELETE CASCADE;



--------------- SCAN ------------------

ALTER TABLE SCAN 
      ADD (CLIENT_LINK_ID NUMBER NULL);
COMMENT ON COLUMN SCAN.CLIENT_LINK_ID IS 
'Only filled for assigning pool items to a client via WSP interface.';

DROP INDEX XIF6SCAN;
CREATE INDEX XIF17SCAN ON SCAN (CLIENT_LINK_ID ASC);

ALTER TABLE SCAN
      ADD CONSTRAINT FK_SCAN_CLIENT FOREIGN KEY (CLIENT_LINK_ID)
      REFERENCES CLIENT_LINK (CLIENT_LINK_ID) ON DELETE SET NULL; 
