DROP TABLE IF EXISTS TempOldTrees;
CREATE TABLE TempOldTrees (
	TempID 			int(10) unsigned NOT NULL auto_increment,
	TreeID 			int(10) unsigned default NULL,
	Tag 			varchar(10) default NULL,
	StemID 			int(10) unsigned default NULL,
	StemTag 		varchar(32) default NULL,
	QuadratID 		int(10) unsigned default NULL,
	QuadratName 	varchar(12) default NULL,
	Subquad 		int(10) unsigned default NULL,
	SpeciesID 		int(10) unsigned default NULL,
	Mnemonic 		varchar(10) default NULL,
	SubSpeciesID 	int(10) unsigned default NULL,
	PlotID 			int(10) unsigned default NULL,
	CensusID 		int(10) unsigned default NULL,
	PlotCensusNumber int(10) unsigned default NULL,
	PrimaryStem 	varchar(20) default NULL,
	X 				float default NULL,
	Y 				float default NULL,
	DBH 			float default NULL,
	OldDBH 			float default NULL,
	HOM 			float default NULL,
	OldHOM 			float default NULL,
	Codes 			varchar(50) default NULL,
	OldCodes 		varchar(50) default NULL,
	Comments 		varchar(256) default NULL,
	ExactDate 		date default NULL,
	Errors 			varchar(256) default NULL,
	OldTrees 		varchar(10) default NULL,
	BigTrees 		varchar(10) default NULL,
	Extra1 			varchar(80) default NULL,
	Extra2 			varchar(80) default NULL,
	Extra3 			varchar(80) default NULL,
	Extra4 			varchar(80) default NULL,
	Extra5 			varchar(80) default NULL,
	PRIMARY KEY  (TempID),
	KEY indexTagStemTag (Tag,StemTag),
	KEY indexTreeIDStemID (TreeID,StemID),
	KEY indexQuadratNamePlotID (QuadratName,PlotID),
	KEY indexMnemonic (Mnemonic)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS TempMultStems;
CREATE TABLE TempMultStems(
	TempID 			int(10) unsigned NOT NULL auto_increment,
	TreeID 			int(10) unsigned default NULL,
	Tag 			varchar(10) default NULL,
	StemID 			int(10) unsigned default NULL,
	StemTag 		varchar(32) default NULL,
	QuadratID 		int(10) unsigned default NULL,
	QuadratName 	varchar(12) default NULL,
	Subquad 		int(10) unsigned default NULL,
	SpeciesID 		int(10) unsigned default NULL,
	Mnemonic 		varchar(10) default NULL,
	SubSpeciesID 	int(10) unsigned default NULL,
	PlotID 			int(10) unsigned default NULL,
	CensusID 		int(10) unsigned default NULL,
	PlotCensusNumber int(10) unsigned default NULL,
	PrimaryStem 	varchar(20) default NULL,
	X 				float default NULL,
	Y 				float default NULL,
	DBH 			float default NULL,
	OldDBH 			float default NULL,
	HOM 			float default NULL,
	OldHOM 			float default NULL,
	Codes 			varchar(50) default NULL,
	OldCodes 		varchar(50) default NULL,
	Comments 		varchar(256) default NULL,
	ExactDate 		date default NULL,
	Errors 			varchar(256) default NULL,
	OldTrees 		varchar(10) default NULL,
	BigTrees 		varchar(10) default NULL,
	Extra1 			varchar(80) default NULL,
	Extra2 			varchar(80) default NULL,
	Extra3 			varchar(80) default NULL,
	Extra4 			varchar(80) default NULL,
	Extra5 			varchar(80) default NULL,
	PRIMARY KEY  (TempID),
	KEY indexTagStemTag (Tag,StemTag),
	KEY indexTreeIDStemID (TreeID,StemID),
	KEY indexQuadratNamePlotID (QuadratName,PlotID),
	KEY indexMnemonic (Mnemonic)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS TempNewPlants;
CREATE TABLE TempNewPlants (
	TempID 			int(10) unsigned NOT NULL auto_increment,
	TreeID 			int(10) unsigned default NULL,
	Tag 			varchar(10) default NULL,
	StemID 			int(10) unsigned default NULL,
	StemTag 		varchar(32) default NULL,
	QuadratID 		int(10) unsigned default NULL,
	QuadratName 	varchar(12) default NULL,
	Subquad 		int(10) unsigned default NULL,
	SpeciesID 		int(10) unsigned default NULL,
	Mnemonic 		varchar(10) default NULL,
	SubSpeciesID 	int(10) unsigned default NULL,
	PlotID 			int(10) unsigned default NULL,
	CensusID 		int(10) unsigned default NULL,
	PlotCensusNumber int(10) unsigned default NULL,
	PrimaryStem 	varchar(20) default NULL,
	X 				float default NULL,
	Y 				float default NULL,
	DBH 			float default NULL,
	OldDBH 			float default NULL,
	HOM 			float default NULL,
	OldHOM 			float default NULL,
	Codes 			varchar(50) default NULL,
	OldCodes 		varchar(50) default NULL,
	Comments 		varchar(256) default NULL,
	ExactDate 		date default NULL,
	Errors 			varchar(256) default NULL,
	OldTrees 		varchar(10) default NULL,
	BigTrees 		varchar(10) default NULL,
	Extra1 			varchar(80) default NULL,
	Extra2 			varchar(80) default NULL,
	Extra3 			varchar(80) default NULL,
	Extra4 			varchar(80) default NULL,
	Extra5 			varchar(80) default NULL,
	PRIMARY KEY  (TempID),
	KEY indexTagStemTag (Tag,StemTag),
	KEY indexTreeIDStemID (TreeID,StemID),
	KEY indexQuadratNamePlotID (QuadratName,PlotID),
	KEY indexMnemonic (Mnemonic)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS TempSpecies;
CREATE TABLE TempSpecies(
    TempID           INT UNSIGNED AUTO_INCREMENT,
	Mnemonic         VARCHAR(10),
    Genus            VARCHAR(32),
    SpeciesName      VARCHAR(64),
    FieldFamily      VARCHAR(32),
    Authority        VARCHAR(128),
    IDLevel          VARCHAR(8),
    GenusID          INT(10),
    SpeciesID        INT(10),
    Family           CHAR(32),
    Errors           VARCHAR(256),
    PRIMARY KEY (TempID),
	KEY indexGenus (Genus)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS TempLocations;
CREATE TABLE TempLocations(
    TempID		     INT UNSIGNED  AUTO_INCREMENT,
    QuadratName      VARCHAR(12),
    Tag              VARCHAR(10),
	StemTag 		 VARCHAR(32) default NULL,
    X                FLOAT(8),
    Y                FLOAT(8),
    Comments         VARCHAR(256) DEFAULT NULL,
    TreeID           INT UNSIGNED,
    PlotCensusNumber INT UNSIGNED,
    CensusID         INT UNSIGNED,
    PlotID           INT UNSIGNED,
    Errors           VARCHAR(256),
    OldTrees         VARCHAR(10),
    PRIMARY KEY (TempID),
	KEY indexTag (Tag),
	KEY indexSTemTag (Tag,StemTag),
	KEY indexTreeID (TreeID),
	KEY indexQuadratName (QuadratName)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS TempPersonnelInfo;
CREATE TABLE TempPersonnelInfo(
	TempID 			 INT(10) unsigned NOT NULL auto_increment,
    QuadratName      VARCHAR(12) DEFAULT NULL,
	QuadratID        INT(10) UNSIGNED DEFAULT NULL,
    PersonnelID      SMALLINT(5) UNSIGNED DEFAULT NULL,
	Form             VARCHAR(32) DEFAULT NULL,
	PlotID           INT(10) UNSIGNED DEFAULT NULL,
    CensusID         INT(10) UNSIGNED DEFAULT NULL,
	PlotCensusNumber INT(10) UNSIGNED DEFAULT NULL,
	Role             VARCHAR(128) DEFAULT NULL,
    RoleID           SMALLINT(5) UNSIGNED DEFAULT NULL,
    DateWorked       Date DEFAULT NULL,
    Errors           VARCHAR(256) DEFAULT NULL,
    PRIMARY KEY (TempID),
	KEY indexQuadratName (QuadratName)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS TempQuadratDates;
CREATE TABLE TempQuadratDates(
    TempQuadID       INT UNSIGNED  AUTO_INCREMENT,
	QuadratName      VARCHAR(12),
    PrevDate         Date,
    PRIMARY KEY (TempQuadID)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS TempBigTrees;
CREATE TABLE TempBigTrees(
    QuadratName      CHAR(12),
    subquad          INT UNSIGNED,
    Tag              CHAR(10),
    StemTag          VARCHAR(32),
    Mnemonic         CHAR(10),
    DBH              FLOAT(8) DEFAULT NULL,
    HOM              FLOAT(8) DEFAULT NULL,
    cylindrical      CHAR(5) DEFAULT NULL,
    comments         VARCHAR(256) DEFAULT NULL,
    ExactDate        DATE,
    TreeID           INT UNSIGNED,
    StemID           INT UNSIGNED DEFAULT NULL,
    QuadratID        INT UNSIGNED,
    x                FLOAT(8),
    y                FLOAT(8),
    codes            VARCHAR(50) DEFAULT NULL,
    SpeciesID        INT UNSIGNED,
    SubSpeciesID     INT UNSIGNED,
    MeasureID        INT UNSIGNED,
    PlotCensusNumber INT UNSIGNED,
    CensusID         INT UNSIGNED,
    PrimaryStem      VARCHAR(20),
    PlotID           INT UNSIGNED,
    Errors           VARCHAR(256),
    TempID           INT UNSIGNED  AUTO_INCREMENT,
    PRIMARY KEY (TempID)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS TempQuadrat;
CREATE TABLE TempQuadrat(
    QuadratID      INT UNSIGNED  NOT NULL,
    PlotID         INT UNSIGNED,
    QuadratName    CHAR(8),
    StartX         FLOAT(8),
    StartY         FLOAT(8),
    DimX           FLOAT(8),
    DimY           FLOAT(8),
	CensusID	   INT UNSIGNED NOT NULL,
	PlotCensusNumber  INT UNSIGNED NOT NULL,
    PRIMARY KEY (QuadratID, PlotID)
)ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS TempNewPlantsError like TempNewPlants;
CREATE TABLE IF NOT EXISTS TempMultStemsError like TempMultStems;
CREATE TABLE IF NOT EXISTS TempLocationsError like TempLocations;
CREATE TABLE IF NOT EXISTS TempBigTreesError like TempBigTrees;
CREATE TABLE IF NOT EXISTS TempOldTreesError like TempOldTrees;
CREATE TABLE IF NOT EXISTS TempSpeciesError like TempSpecies;
