-- CREATE  DATABASE --
-- Modify "current_plot" to the name of your site database --

use current_plot;
source createTemptables.sql;

--  LOAD CENSUS DATA --
-- Change structure to conform with the structure of your text files --


CREATE TABLE stageCensus(
	Tag              CHAR(10),    
	StemTag          CHAR(32), 
	Mnemonic         CHAR(10),
	QuadratName	     CHAR(12),
	x                FLOAT(8),
    y                FLOAT(8),
	DBH              FLOAT(8) DEFAULT NULL,
	codes            VARCHAR(50) DEFAULT NULL,
	HOM              FLOAT(8) DEFAULT NULL,
	ExactDate        DATE,
    PlotID		     INT UNSIGNED,
	PlotCensusNumber INT UNSIGNED,
	CensusID         INT UNSIGNED,
	tempID           INT UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY (tempID)
)ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'census.txt' INTO TABLE stageCensus fields TERMINATED BY '\t' ENCLOSED BY '' IGNORE 1 LINES
    (Tag,StemTag,Mnemonic,QuadratName,x,y,DBH,codes,HOM,Exactdate);


--  Replace all PlotID, PlotCensusNumber, and CensusID with correct numbers --
UPDATE stageCensus SET PlotID=1,PlotCensusNumber=2,CensusID=2;

-- IF site does not use stemtags:
-- ALTER TABLE stageCensus ADD INDEX(Tag,PlotID); 

-- If site uses stemtags:
ALTER TABLE stageCensus ADD INDEX(Tag,StemTag,PlotID); 

-- Below is for sites that have already uploaded some data into the database

INSERT INTO TempOldTrees (QuadratName,Tag,StemTag,Mnemonic,DBH,codes,HOM,ExactDate,x,y,PlotID,PlotCensusNumber,CensusID) 
   SELECT QuadratName,Tag,StemTag,Mnemonic,DBH,codes,HOM,ExactDate,x,y,PlotID,PlotCensusNumber,CensusID
        FROM stageCensus WHERE tag IN (SELECT DISTINCT tag FROM ViewFullTable WHERE plotid=1);

INSERT INTO TempNewPlants (QuadratName,Tag,StemTag,Mnemonic,DBH,codes,HOM,ExactDate,x,y,PlotID,PlotCensusNumber,CensusID) 
   SELECT QuadratName,Tag,StemTag,Mnemonic,DBH,codes,HOM,ExactDate,x,y,PlotID,PlotCensusNumber,CensusID
        FROM stageCensus WHERE TAG NOT IN (SELECT DISTINCT tag FROM ViewFullTable WHERE plotid=1);
		
INSERT INTO TempLocations (QuadratName,Tag, StemTag, X, Y,PlotCensusNumber,PlotID,CensusID) 
   SELECT DISTINCT QuadratName,Tag, StemTag, x, y, PlotCensusNumber, PlotID, CensusID
        FROM stageCensus WHERE Tag NOT IN (SELECT DISTINCT tag FROM ViewFullTable WHERE plotid=1);
      		

-- If site DOES TAG MULTIPLE STEMS, you have to do the following steps also:		

INSERT INTO TempMultiStems (QuadratName,Tag,StemTag,Mnemonic,DBH,codes,HOM,ExactDate,x,y,PlotID,PlotCensusNumber,CensusID) 
   SELECT QuadratName,Tag,StemTag,Mnemonic,DBH,codes,HOM,ExactDate,x,y,PlotID,PlotCensusNumber,CensusID
        FROM TempOldTrees WHERE (Tag,StemTag) NOT IN (SELECT Tag,StemTag FROM ViewFullTable);

DELETE FROM TempOldTrees WHERE (Tag,StemTag) IN (SELECT Tag,StemTag FROM TempMultiStems);
