--SO 20014879

--SOILBAGTAG TABLE

CREATE SEQUENCE SEQ_SOILBAGTAG
START WITH 1
INCREMENT BY 1;

CREATE TABLE SOILBAGTAG (
SOILBAGTAG_ID    NUMBER NOT NULL,
SOILTICKET_ID    NUMBER NOT NULL,
SOILBAGTAGNUMBER NUMBER NOT NULL,
STATUS           NUMBER DEFAULT 0 NOT NULL CONSTRAINT STATUSSOILBAGTAG_VALID CHECK (STATUS IN (0, 1)),
SYSTEMUSER_ID    NUMBER NOT NULL,
TIMESTAMP        DATE NOT NULL
);


ALTER TABLE SOILBAGTAG
ADD CONSTRAINT  XPKSOILBAGTAG PRIMARY KEY (SOILBAGTAG_ID);

CREATE UNIQUE INDEX XAK1SOILBAGTAG ON SOILBAGTAG
(SOILBAGTAGNUMBER   ASC);

CREATE INDEX XIF1SOILBAGTAG ON SOILBAGTAG
(SOILTICKET_ID   ASC);

CREATE INDEX XIE1SOILBAGTAG ON SOILBAGTAG
(SYSTEMUSER_ID   ASC);


COMMENT ON COLUMN SOILBAGTAG.STATUS IS '0 = Generated,
1 = Received, default 0';

ALTER TABLE SOILBAGTAG
ADD CONSTRAINT SOILBAGTAG_ST FOREIGN KEY (SOILTICKET_ID) REFERENCES SOILTICKET (SOILTICKET_ID) ON DELETE CASCADE;


--AUTOSOILQUANTITY TABLE

CREATE SEQUENCE SEQ_AUTOSOILQUANTITY
START WITH 1
INCREMENT BY 1;

CREATE TABLE AUTOSOILQUANTITY (
AUTOSOILQUANTITY_ID          NUMBER NOT NULL,
PRODUCTLISTPERCUSTOMER_ID    NUMBER NOT NULL,
DAY_ID                       NUMBER NOT NULL,
QUANTITY                     NUMBER NOT NULL,
SYSTEMUSER_ID                NUMBER NOT NULL,
TIMESTAMP                    DATE NOT NULL
);


ALTER TABLE AUTOSOILQUANTITY
ADD CONSTRAINT  XPKAUTOSOILQUANTITY PRIMARY KEY (AUTOSOILQUANTITY_ID);

CREATE UNIQUE INDEX XAK1AUTOSOILQUANTITY ON AUTOSOILQUANTITY
(PRODUCTLISTPERCUSTOMER_ID   ASC, DAY_ID ASC);

CREATE INDEX XIF1AUTOSOILQUANTITY ON AUTOSOILQUANTITY
(PRODUCTLISTPERCUSTOMER_ID   ASC);

CREATE INDEX XIF2AUTOSOILQUANTITY ON AUTOSOILQUANTITY
(DAY_ID   ASC);

ALTER TABLE AUTOSOILQUANTITY
ADD CONSTRAINT AUTOSOILQUANTITY_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID) REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE AUTOSOILQUANTITY
ADD CONSTRAINT AUTOSOILQUANTITY_DAY FOREIGN KEY (DAY_ID) REFERENCES DAY (DAY_ID) ON DELETE CASCADE;


-- table PRODUCTLISTPERCUSTOMER

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (AUTOSOIL NUMBER DEFAULT 0 NOT NULL);
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (AUTOSOILPERCENTAGE NUMBER DEFAULT 0 NOT NULL CONSTRAINT AUTOSOILPERC_VALID CHECK (AUTOSOILPERCENTAGE IN (0, 1, 2)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.AUTOSOIL IS '0 = No Auto Soil
1 = Auto Soil Percentage
2 = Auto Soil Quantity
';


-- table SOILTICKETLINE

ALTER TABLE SOILTICKETLINE ADD (AUTOSOILQUANTITY NUMBER NULL);


-- table SOILTICKETLINEPERUSER

CREATE SEQUENCE SEQ_SOILTICKETLINEPERUSER
START WITH 1
INCREMENT BY 1;

CREATE TABLE SOILTICKETLINEPERUSER (
SOILTICKETLINEPERUSER_ID    NUMBER NOT NULL,
SOILTICKETLINE_ID           NUMBER NOT NULL,
COUNTKEYPAD                 NUMBER NOT NULL,
COUNTCONVERTED              NUMBER NOT NULL,
COUNTLIGHTFRAME             NUMBER NOT NULL,
COUNTABUSED                 NUMBER NOT NULL,
COUNTLOSS                   NUMBER NOT NULL,
SYSTEMUSER_ID               NUMBER NOT NULL,
TIMESTAMP                   DATE NOT NULL
);


ALTER TABLE SOILTICKETLINEPERUSER
ADD CONSTRAINT  XPKSOILTICKETLINEPERUSER PRIMARY KEY (SOILTICKETLINEPERUSER_ID);

CREATE INDEX XIF1SOILTICKETLINEPERUSER ON SOILTICKETLINEPERUSER
(SOILTICKETLINE_ID   ASC);

CREATE INDEX XIE1SOILTICKETLINEPERUSER ON SOILTICKETLINEPERUSER
(SOILTICKETLINE_ID   ASC, SYSTEMUSER_ID   ASC);

ALTER TABLE SOILTICKETLINEPERUSER
ADD CONSTRAINT SOILTICKETLINEPERUSER_STL FOREIGN KEY (SOILTICKETLINE_ID) REFERENCES SOILTICKETLINE (SOILTICKETLINE_ID) ON DELETE CASCADE;