/*ORDER 502338*/      
      /*
      ACTION is CREATE Table DISTRICT
      */

CREATE SEQUENCE SEQ_DISTRICT
START WITH 1
INCREMENT BY 1;

CREATE TABLE DISTRICT (
       DISTRICT_ID          NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       DESCRIPTION          VARCHAR2(30) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1DISTRICT ON DISTRICT
(
       CODE                           
);


ALTER TABLE DISTRICT
       ADD  ( CONSTRAINT XPKDISTRICT PRIMARY KEY (DISTRICT_ID) ) ;

--CREATE DISTRICT RECORD WITH CODE AND DESCRIPTION '-'

INSERT INTO DISTRICT VALUES(SEQ_DISTRICT.NEXTVAL,'-','-',getminsystemuser(),SYSDATE);

      /*
      ACTION is CREATE Table DISTRICTPLANT
      */

CREATE TABLE DISTRICTPLANT (
       DISTRICT_ID          NUMBER NOT NULL,
       PLANT_ID             NUMBER NOT NULL,
       EMPLOYEE_ID          NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1DISTRICTPLANT ON DISTRICTPLANT
(
       DISTRICT_ID                    ASC
);

CREATE INDEX XIF2DISTRICTPLANT ON DISTRICTPLANT
(
       PLANT_ID                       ASC
);

CREATE INDEX XIF3DISTRICTPLANT ON DISTRICTPLANT
(
       EMPLOYEE_ID                    ASC
);


ALTER TABLE DISTRICTPLANT
       ADD  ( CONSTRAINT XPKDISTRICTPLANT PRIMARY KEY (DISTRICT_ID, 
              PLANT_ID) ) ;

 
      /*
      CHANGE REPORT for Table ROUTE
          - Adding column DISTRICT_ID
          - Adding column ROUTEMANAGER_ID
          - Adding index XIF10ROUTE
          - Adding index XIF9ROUTE
      ACTION is ALTER or modify Table ROUTE
      */

ALTER TABLE ROUTE ADD (DISTRICT_ID          NUMBER NULL);


ALTER TABLE ROUTE ADD (ROUTEMANAGER_ID      NUMBER NULL);


CREATE INDEX XIF10ROUTE ON ROUTE
(
       ROUTEMANAGER_ID                ASC
);

CREATE INDEX XIF11ROUTE ON ROUTE
(
       DISTRICT_ID                    ASC,
       PLANT_ID                       ASC
);

CREATE INDEX XIF9ROUTE ON ROUTE
(
       DISTRICT_ID                    ASC
);


ALTER TABLE DISTRICTPLANT
       ADD  ( CONSTRAINT DISTRICTPLANT_EMPLOYEE_ID
              FOREIGN KEY (EMPLOYEE_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE DISTRICTPLANT
       ADD  ( CONSTRAINT DISTRICTPLANT_PLANT_ID
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT ) ;


ALTER TABLE DISTRICTPLANT
       ADD  ( CONSTRAINT DISTRICTPLANT_DISTRICT_ID
              FOREIGN KEY (DISTRICT_ID)
                             REFERENCES DISTRICT ) ;



ALTER TABLE ROUTE
       ADD  ( CONSTRAINT ROUTE_ROUTEMANAGER_ID
              FOREIGN KEY (ROUTEMANAGER_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE ROUTE
       ADD  ( CONSTRAINT ROUTE_DISTRICT_ID
              FOREIGN KEY (DISTRICT_ID)
                             REFERENCES DISTRICT );

ALTER TABLE ROUTE
       ADD  ( CONSTRAINT ROUTE_DISTRICT_ID_PLANT_ID
              FOREIGN KEY (DISTRICT_ID,PLANT_ID)
                             REFERENCES DISTRICTPLANT );

--CREATE RECORDS IN TABLE DISTRICTPLANT                             
                             /*
INSERT INTO DISTRICTPLANT
  (SELECT DD, PL, EMP, getminsystemuser(), sysdate
     FROM (SELECT DISTRICT_ID DD FROM DISTRICT D WHERE D.CODE = '-'),
          (SELECT E.PLANT_ID PL ,E.EMPLOYEE_ID EMP
             FROM EMPLOYEE E, PLANT
            WHERE E.PLANT_ID = PLANT.PLANT_ID
            AND E.EMPLOYEENUMBER = (SELECT MIN(E.EMPLOYEENUMBER) FROM EMPLOYEE)));
							*/
INSERT INTO DISTRICTPLANT(district_id, plant_id,employee_id,systemuser_id, timestamp)
SELECT dd.district_id,
       pl.plant_id,
       nvl((select min(e.employee_id) from employee e where e.plant_id = pl.plant_id),(select min(employee_id) from employee)),
       getminsystemuser(),
       sysdate
  FROM (SELECT DISTRICT_ID FROM DISTRICT WHERE CODE = '-') dd,
       (SELECT PLANT_ID FROM PLANT) pl
	   where not exists ( select 1 
	   from districtplant x
	   where x.district_id=dd.district_id
	   and x.plant_id=pl.plant_id);

            
UPDATE ROUTE SET DISTRICT_ID = 
(SELECT DISTRICT_ID FROM DISTRICT D WHERE D.CODE = '-');


ALTER TABLE ROUTE MODIFY (DISTRICT_ID       NOT NULL);


