--Order 20012604.180 App for linen ordering

---------------------------------------ORDERLINE TABLE-----------------------------------

CREATE SEQUENCE SEQ_ORDERDEADLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE ORDERDEADLINE
(
	ORDERDEADLINE_ID     NUMBER NOT NULL ,
	PLANT_ID             NUMBER NOT NULL ,
	DELIVERYDAY_ID       NUMBER NOT NULL ,
	ORDERDAY_ID          NUMBER NOT NULL ,
	DEADLINE             DATE NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN ORDERDEADLINE.DEADLINE IS 'Only the time (HHMM) are used.';

ALTER TABLE ORDERDEADLINE
ADD CONSTRAINT  XPKORDERDEADLINE PRIMARY KEY (ORDERDEADLINE_ID);

CREATE  INDEX XIF1ORDERDEADLINE ON ORDERDEADLINE
(PLANT_ID   ASC);

ALTER TABLE ORDERDEADLINE
ADD CONSTRAINT ORDERDEADLINE_PLANT_ID FOREIGN KEY (PLANT_ID) REFERENCES PLANT (PLANT_ID);

CREATE  INDEX XIF2ORDERDEADLINE ON ORDERDEADLINE
(DELIVERYDAY_ID   ASC);

ALTER TABLE ORDERDEADLINE
ADD CONSTRAINT ORDERDEADLINE_DELIVERYDAY_ID FOREIGN KEY (DELIVERYDAY_ID) REFERENCES DAY (DAY_ID);

CREATE  INDEX XIF3ORDERDEADLINE ON ORDERDEADLINE
(ORDERDAY_ID   ASC);

ALTER TABLE ORDERDEADLINE
DROP CONSTRAINT ORDERDEADLINE_DAY_ID;

ALTER TABLE ORDERDEADLINE
ADD CONSTRAINT ORDERDEADLINE_ORDERDAY_ID FOREIGN KEY (ORDERDAY_ID) REFERENCES DAY (DAY_ID);

CREATE UNIQUE INDEX XAK1ORDERDEADLINE ON ORDERDEADLINE
(PLANT_ID   ASC,DELIVERYDAY_ID   ASC);


---------------------------------------WEBORDER TABLE-----------------------------------

ALTER TABLE WEBORDER
	ADD (ORIGIN NUMBER  DEFAULT 0 NOT NULL CONSTRAINT Origin_valid4 CHECK (ORIGIN IN (0, 1)));


COMMENT ON COLUMN WEBORDER.ORIGIN IS '0 = Web application
1 = Laundry order app
';


---------------------------------------WEBUSER TABLE-----------------------------------

ALTER TABLE WEBUSER
	ADD (PHONEID VARCHAR2(30) NULL);






