/* ***************** 2011 STRI DB 'CREATE' MySQL Script *****************
 *									*
 *		Most of the 2011 db is based off of the 2007 db.	*
 *		This script creates the new tables of the 2011 db	*
 *		and creates the new attributes of existing tables	*
 *		Nothing is deleted in this script. The drop.sql		*
 *		script will remove excess data and schema.		*
 *									*
 ***********************************************************************/
 
/* ***************************** Developers *****************************
 *									*
 *		Bradley University, Computer Science Dept.		*
 *		Smithsonian Tropical Research Institute			*
 *									*
 *			2011 Capstone Project				*
 *		Mario Campos, Michael Koeber, Zach Caschetta 		*
 *									*	
 ***********************************************************************/

-- Quadrat TABLE
ALTER TABLE Tree DROP FOREIGN KEY `Tree_ibfk_1`;
ALTER TABLE CensusQuadrat DROP FOREIGN KEY `CensusQuadrat_ibfk_2`;
ALTER TABLE Quadrat DROP PRIMARY KEY;
ALTER TABLE Quadrat CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;
ALTER TABLE Quadrat ADD COLUMN (Area FLOAT UNSIGNED, IsStandardShape ENUM('Y','N') NOT NULL);
ALTER TABLE Quadrat ADD COLUMN QuadratID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- CensusQuadrat TABLE
ALTER TABLE DataEntry DROP FOREIGN KEY `DataEntry_ibfk_1`;
ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_1`;
ALTER TABLE CensusQuadrat CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;
ALTER TABLE CensusQuadrat ADD COLUMN QuadratID INT UNSIGNED NOT NULL;
UPDATE CensusQuadrat CQ, Quadrat Q SET CQ.QuadratID=Q.QuadratID WHERE CQ.QuadratID2=Q.QuadratID2 AND CQ.PlotID=Q.PlotID;
ALTER TABLE CensusQuadrat DROP PRIMARY KEY;
ALTER TABLE CensusQuadrat ADD COLUMN CensusQuadratID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE CensusQuadrat ADD FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`);

-- DataCollection TABLE
ALTER TABLE DataCollection DROP PRIMARY KEY;
ALTER TABLE DataCollection ADD COLUMN DataCollectionID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE DataCollection ADD COLUMN PersonnelRoleID INT UNSIGNED NOT NULL;
ALTER TABLE DataCollection CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;
ALTER TABLE DataCollection ADD COLUMN QuadratID INT UNSIGNED NOT NULL;
UPDATE DataCollection D, Quadrat Q SET D.QuadratID=Q.QuadratID WHERE D.QuadratID2=Q.QuadratID2;
ALTER TABLE DataCollection ADD FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`);
ALTER TABLE DataCollection ADD FOREIGN KEY (`CensusID`)  REFERENCES `Census` (`CensusID`);

-- PersonnelRole TABLE
CREATE TABLE PersonnelRole (PersonnelRoleID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       	     		    PersonnelID SMALLINT UNSIGNED NOT NULL,
			    RoleID SMALLINT UNSIGNED NOT NULL
			   )ENGINE=INNODB;
INSERT INTO PersonnelRole (PersonnelID,RoleID) SELECT PersonnelID, RoleID FROM DataCollection;
ALTER TABLE PersonnelRole ADD FOREIGN KEY (RoleID) REFERENCES RoleReference(RoleID);
ALTER TABLE PersonnelRole ADD FOREIGN KEY (PersonnelID) REFERENCES Personnel(PersonnelID);
UPDATE DataCollection D, PersonnelRole P SET D.PersonnelRoleID=P.PersonnelRoleID WHERE D.PersonnelID=P.PersonnelID AND D.RoleID=P.RoleID;
ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_2`;
ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_3`;
ALTER TABLE DataCollection ADD FOREIGN KEY (`PersonnelRoleID`) REFERENCES `PersonnelRole` (`PersonnelRoleID`);

-- Tree TABLE
ALTER TABLE Tree CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;
ALTER TABLE Tree ADD COLUMN QuadratID INT UNSIGNED;
UPDATE Tree T, Quadrat Q SET T.QuadratID=Q.QuadratID WHERE T.QuadratID2=Q.QuadratID2;

-- Stem TABLE
ALTER TABLE Measurement DROP FOREIGN KEY `Measurement_ibfk_2`;
-- ALTER TABLE Remeasurement DROP FOREIGN KEY to stem --FINISH THIS LINE
ALTER TABLE Stem DROP PRIMARY KEY;
ALTER TABLE Stem ADD COLUMN (QuadratID INT UNSIGNED NOT NULL,
      	    	     	     StemNumber INT UNSIGNED NOT NULL,
			     Moved ENUM('Y','N') NOT NULL,
			     GX FLOAT,
			     GY FLOAT,
			     GZ FLOAT,
			     PX FLOAT,
			     PY FLOAT,
			     PZ FLOAT,
			     QX FLOAT,
			     QY FLOAT,
			     QZ FLOAT
			    );
UPDATE Stem S, Tree T SET S.QuadratID=T.QuadratID WHERE S.TreeID=T.TreeID;
UPDATE Stem S1, Stem S2 SET S1.StemNumber=S2.StemID WHERE S1.StemID=S2.StemID AND S1.TreeID=S2.TreeID;
UPDATE Stem S, Tree T SET S.QX=T.x, S.QY=T.y WHERE S.TreeID=T.TreeID;
ALTER TABLE Stem DROP COLUMN StemID;
ALTER TABLE Stem ADD COLUMN StemID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

-- Measurement(DBH) TABLE
ALTER TABLE LogMeasurementHistory DROP FOREIGN KEY `LogMeasurementHistory_ibfk_1`;
ALTER TABLE MeasurementAttributes DROP FOREIGN KEY `MeasurementAttributes_ibfk_1`;
ALTER TABLE Measurement DROP PRIMARY KEY;
ALTER TABLE Measurement ADD COLUMN DBHID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE Measurement ADD COLUMN Comments VARCHAR(128);
ALTER TABLE Measurement MODIFY COLUMN HOM CHAR(16);
UPDATE Measurement M, Stem S SET M.StemID=S.StemID WHERE M.StemID=S.StemNumber AND M.TreeID=S.TreeID;
ALTER TABLE Measurement ADD FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`);

-- MeasurementAttributes TABLE
ALTER TABLE MeasurementAttributes ADD COLUMN DBHID INT UNSIGNED;
UPDATE MeasurementAttributes MA, Measurement M SET MA.DBHID=M.DBHID WHERE MA.MeasureID=M.MeasureID;
ALTER TABLE MeasurementAttributes ADD FOREIGN KEY (`DBHID`) REFERENCES `Measurement` (`DBHID`);
ALTER TABLE LogMAttrHistory DROP FOREIGN KEY `LogMAttrHistory_ibfk_1`;
ALTER TABLE MeasurementAttributes DROP PRIMARY KEY;
ALTER TABLE MeasurementAttributes ADD COLUMN DBHAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- Remeasurement TABLE
ALTER TABLE RemeasAttribs DROP FOREIGN KEY `RemeasAttribs_ibfk_1`;
ALTER TABLE Remeasurement DROP PRIMARY KEY;
ALTER TABLE Remeasurement CHANGE COLUMN RemeasureID RemeasureID2 INT UNSIGNED;
ALTER TABLE Remeasurement ADD COLUMN RemeasureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
UPDATE Remeasurement R, Stem S SET R.StemID=S.StemID WHERE R.StemID=S.StemNumber AND R.TreeID=S.TreeID;
ALTER TABLE Remeasurement ADD FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`);

-- RemeasAttribs TABLE
ALTER TABLE RemeasAttribs CHANGE COLUMN RemeasureID RemeasureID2 INT UNSIGNED;
ALTER TABLE RemeasAttribs ADD COLUMN RemeasureID INT UNSIGNED NOT NULL;
UPDATE RemeasAttribs RA, Remeasurement R SET RA.RemeasureID=R.RemeasureID WHERE RA.RemeasureID2=R.RemeasureID2;
ALTER TABLE RemeasAttribs DROP PRIMARY KEY;
ALTER TABLE RemeasAttribs ADD COLUMN RmAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE RemeasAttribs ADD FOREIGN KEY (`RemeasureID`) REFERENCES `Remeasurement` (`RemeasureID`);

-- TreeAttributes TABLE
ALTER TABLE LogTreeAttrHistory DROP FOREIGN KEY `LogTreeAttrHistory_ibfk_1`;
ALTER TABLE TreeAttributes DROP PRIMARY KEY;
ALTER TABLE TreeAttributes ADD COLUMN TAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- rename tables
ALTER TABLE Measurement RENAME TO DBH;
ALTER TABLE MeasurementAttributes RENAME TO DBHAttributes;

-- add new tables
CREATE TABLE IF NOT EXISTS FeatureTypes (
					   FeatureTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
					   Type VARCHAR(32) NOT NULL
					)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Features (
					FeatureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
					FeatureTypeID INT UNSIGNED NOT NULL,
					Name VARCHAR(32) NOT NULL,
					ShortDescrip VARCHAR(32),
					LongDescrip VARCHAR(128),
					FOREIGN KEY (`FeatureTypeID`) REFERENCES `FeatureTypes` (`FeatureTypeID`)
				    )	ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Coordinates (
					  CoorID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
					  FeatureID INT UNSIGNED,
					  PlotID INT UNSIGNED,
					  QuadratID INT UNSIGNED,
					  GX FLOAT(8),
					  GY FLOAT(8),
					  GZ FLOAT(8),
					  PX FLOAT(8),
					  PY FLOAT(8),
					  PZ FLOAT(8),
					  QX FLOAT(8),
					  QY FLOAT(8),
					  QZ FLOAT(8),
					  CoordinateNo INT UNSIGNED,
					  FOREIGN KEY (`FeatureID`) REFERENCES `Features` (`FeatureID`),
					  FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`),
					  FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`)
				       )  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS MeasurementType (
						MeasurementTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
						UOM VARCHAR(32) NOT NULL,
						Type VARCHAR(256)
					   )	ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Measurement (
					MeasureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
					CensusID INT UNSIGNED NOT NULL,
					TreeID INT UNSIGNED NOT NULL,
					StemID INT UNSIGNED NOT NULL,
					MeasurementTypeID INT UNSIGNED NOT NULL,
					Measure VARCHAR(256) NOT NULL,
					ExactDate DATE NOT NULL,
					Comments VARCHAR(128),
					FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
					FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),
					FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`),
					FOREIGN KEY (`MeasurementTypeID`) REFERENCES `MeasurementType` (`MeasurementTypeID`)
				       )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS MeasurementAttributes (
       	     	    	   			  MAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
						  MeasureID INT UNSIGNED NOT NULL,
						  CensusID INT UNSIGNED NOT NULL,
						  TSMID INT UNSIGNED NOT NULL,
						  FOREIGN KEY (`MeasureID`) REFERENCES `Measurement` (`MeasureID`),
						  FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
						  FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`)
						 )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Log (
				LogID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
				PersonnelID SMALLINT UNSIGNED,
				ChangedTable VARCHAR(32) NOT NULL,
				PrimaryKey VARCHAR(32) NOT NULL,
				ChangedColumn VARCHAR(32) NOT NULL,
				ChangeDate DATE,
				ChangeTime TIMESTAMP,
				Description VARCHAR(256),
				Action ENUM('I','D','U') NOT NULL,
				Old VARCHAR(512) NOT NULL,
				New VARCHAR(512) NOT NULL,
				FOREIGN KEY (`PersonnelID`) REFERENCES `Personnel` (`PersonnelID`)
			       )ENGINE=INNODB;

-- modify existing tables to meet new schema's specs
ALTER TABLE Site ADD COLUMN (  Area INT UNSIGNED NOT NULL,
      	    	     	       QDimX INT UNSIGNED NOT NULL,
			       QDimY INT UNSIGNED NOT NULL,
			       GUOM VARCHAR(32) NOT NULL,
			       GZUOM VARCHAR(32) NOT NULL,
			       PUOM VARCHAR(32) NOT NULL,
			       QUOM VARCHAR(32) NOT NULL,
			       GCoorCollected VARCHAR(32),
			       PCoorCollected VARCHAR(32),
			       QCoorCollected VARCHAR(32)
			    ); 

ALTER TABLE Stem ADD COLUMN (	GX FLOAT(8),
				GY FLOAT(8),
				GZ FLOAT(8),
				PX FLOAT(8),
				PY FLOAT(8),
				PZ FLOAT(8),
				QX FLOAT(8),
				QY FLOAT(8),
				QZ FLOAT(8),
				Moved ENUM('Y','N') NOT NULL
			    );

ALTER TABLE Census MODIFY COLUMN PlotCensusNumber CHAR(16);