--  First, drop all the tables
DROP TABLE LOT_MOVE CASCADE CONSTRAINTS;
DROP TABLE LOT_LOC CASCADE CONSTRAINTS;
DROP TABLE MASS_TRTMNT CASCADE CONSTRAINTS;
DROP TABLE LOT CASCADE CONSTRAINTS;
DROP TABLE PEN CASCADE CONSTRAINTS;
DROP TABLE DBF_FILE CASCADE CONSTRAINTS;
DROP TABLE BKP CASCADE CONSTRAINTS;
DROP TABLE FDYD CASCADE CONSTRAINTS;
DROP TABLE APPLICATION CASCADE CONSTRAINTS;

-- Second, create all the tables needed

CREATE TABLE FDYD
   (	FDYD_ID INTEGER	PRIMARY KEY,
	FDYD_SHORT_NAME	CHAR(1),
	FDYD_MNGR_LNAME	CHAR(1) UNIQUE  ) ;
	  
CREATE TABLE APPLICATION
   (	A_NAME			CHAR(1) PRIMARY KEY,
	A_DESCRIPTION		VARCHAR2(30),
	A_DATE_DIRECTORY	DATE  ) ;

CREATE TABLE DBF_FILE
   (	A_NAME		CHAR(1),
	DBF_NAME	CHAR(1),
	DBF_DESCRIPTION	VARCHAR2(30),
	DBF_USED	NUMBER(1),
	PRIMARY KEY (A_NAME, DBF_NAME),
	FOREIGN KEY (A_NAME) REFERENCES APPLICATION  ) ;

CREATE TABLE BKP
   (	FDYD_ID		INTEGER,
	BKP_ID		INTEGER,
	YEAR_MONTH	CHAR(10),
	DATE_PROCESSED	DATE,
	QUIRKS		INTEGER,
	VTRC_LAST_DATE_MOD	DATE,
	ARCH_LAST_DATE_MOD	DATE,
	PRIMARY KEY (FDYD_ID, BKP_ID),
	FOREIGN KEY (FDYD_ID) REFERENCES FDYD  ) ;

CREATE TABLE LOT
   (	FDYD_ID		INTEGER,
	LOT_ID_NUM	INTEGER,
	LOT_ID		INTEGER,
	GNDR_CODE	CHAR(1),
	PROJ_CLOSE_OUT	DATE,
	IN_WEIGHT	INTEGER,
	VALID		NUMBER(1),
	OWNER		CHAR(1),
	LOT_COMMENT	VARCHAR2(30),
	BKP_ID		INTEGER,
	A_NAME		CHAR(1),
	DBF_NAME	CHAR(1),
	DBF_UPDATE_RECNO	INTEGER,
	PRIMARY KEY (FDYD_ID, LOT_ID_NUM),
	FOREIGN KEY (FDYD_ID) REFERENCES FDYD,
	CONSTRAINT FK_LT_BKP FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP,
	FOREIGN KEY (A_NAME, DBF_NAME) REFERENCES DBF_FILE  ) ;

CREATE TABLE PEN
   (	FDYD_ID		INTEGER,
	PEN_ID		INTEGER,
	PEN_TYPE_CODE	CHAR(1),
	BUNK_LENGTH	INTEGER,
	APRON_WIDTH	INTEGER,
	PEN_AREA	INTEGER,
	WATER_SAPCE	INTEGER,
	BKP_ID		INTEGER,
	PRIMARY KEY (FDYD_ID, PEN_ID),
	FOREIGN KEY (FDYD_ID) REFERENCES FDYD,
	CONSTRAINT FK_PEN_BKP FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP  ) ;

CREATE TABLE LOT_LOC
   (	FDYD_ID		INTEGER,
	LOT_ID_NUM	INTEGER,
	PEN_ID		INTEGER,
	HD_CNT		INTEGER,
	YEAR_MONTH	CHAR(10),
	PRIMARY KEY (FDYD_ID, LOT_ID_NUM, PEN_ID),
	CONSTRAINT FK_LOC_LOT FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT,
	FOREIGN KEY (FDYD_ID, PEN_ID) REFERENCES PEN  ) ;

CREATE TABLE LOT_MOVE
   (	FDYD_ID		INTEGER,
	LOT_ID_NUM	INTEGER,
	FROM_PEN_ID	INTEGER,
	TO_PEN_ID	INTEGER,
	HD_CNT		INTEGER,
	BKP_ID		INTEGER,
	A_NAME		CHAR(1),
	DBF_NAME	CHAR(1),
	PRIMARY KEY (FDYD_ID, LOT_ID_NUM, FROM_PEN_ID, TO_PEN_ID),
	FOREIGN KEY (FDYD_ID, FROM_PEN_ID) REFERENCES PEN(FDYD_ID, PEN_ID),
	FOREIGN KEY (FDYD_ID, TO_PEN_ID) REFERENCES PEN(FDYD_ID, PEN_ID),
	FOREIGN KEY (A_NAME, DBF_NAME) REFERENCES DBF_FILE,
	CONSTRAINT FK_MOVE_LOT FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT,
	CONSTRAINT FK_MOVE_BKP FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP  ) ;

CREATE TABLE MASS_TRTMNT
   (	FDYD_ID		INTEGER,
	LOT_ID_NUM	INTEGER,
	PEN_ID		INTEGER,
	M_TRTMNT_AVG_WGT	INTEGER,
	M_TRTMNT_CODE	CHAR(1),
	M_TRTMNT_HD	INTEGER,
	BKP_ID		INTEGER,
	PRIMARY KEY (FDYD_ID, LOT_ID_NUM, PEN_ID),
	FOREIGN KEY (FDYD_ID, PEN_ID) REFERENCES PEN,
	CONSTRAINT FK_MASS_LOT FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT,
	CONSTRAINT FK_MASS_BKP FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP  ) ;

