# eme_collecting_event   10/10/2005

## caution!  any new fields must also be added to eme_collecting_event_deleted as well as myschema.p

CREATE TABLE eme_collecting_event (

EventID   		       serial,  # key for table eme: coll_eventID
OtherEventID		       varchar(128),  # see OtherEventInst for whose this is
ProjectName		       varchar(128), # "Fiji"
HoldingInstitution             varchar(255), 
DateFirstEntered               datetime,  
EnteredBy                      varchar(128), 
DateLastModified               datetime,
ModifiedBy                     varchar(128), 

Collector                      varchar(255),
Collector2                     varchar(128),   ## Collectors2-5 added 10/14/04 GO
Collector3                     varchar(128),
Collector4                     varchar(128),
Collector5                     varchar(128),
Collector_List                 varchar(255),    ## Col1, Col2, Col3, Col4, Col5
YearCollected                  mediumint unsigned,     --  Darwin Core= "Year"
MonthCollected                 tinyint unsigned,       -- Darwin Core= "Month"
DayCollected                   tinyint unsigned,       -- Darwin Core= "Day"
TimeofDay		       varchar(128),
YearCollected2                 mediumint unsigned,
MonthCollected2                tinyint unsigned,
DayCollected2                  tinyint unsigned,

ContinentOcean		       varchar(128),
IslandGroup		       varchar(128),
Island			       varchar(128),
Country			       varchar(128),
StateProvince		       varchar(128),
County			       varchar(128),
Locality		       text,
OriginalLocalityData           text,          ## added Dec 2010
Loc_Num	                       varchar(64),   ## for future use
Collectors_Loc		       varchar(64),   ## loc num used in the field or other num

DecimalLongitude	       decimal (10,5),
DecimalLatitude		       decimal (10,5),
HorizontalDatum		       varchar(50),
MaxErrorInMeters	       decimal (10,4), 

MinElevationMeters	       decimal (10,4),
MaxElevationMeters	       decimal (10,4),
MinDepthMeters		       decimal (10,4),
MaxDepthMeters		       decimal (10,4),

GeorefReference                text,                ## added March 28, 2011
Lat_Long_Determined_By         varchar(255),        ## added March 28, 2011
Lat_Long_Determined_Date       date,                ## added March 28, 2011
GeorefRemarks                  text,                ## added March 28, 2011


IndividualCount		       varchar(50),  
Habitat                        varchar(255),
MicroHabitat                   varchar(255),
Associated_Taxon               varchar(255),
Association_Type               varchar(255),
Collection_Method	       varchar(64),   # Malaise Trap
Landowner		       varchar(255),
Permit_Info		       text,
Remarks			       varchar(255),
OtherEventInst		       varchar(64),    # if OtherEventID, whose it is "Mandala"
OtherEventID2		varchar(128),
OtherMiscID		varchar(255),

VerbatimLongitude              varchar(255),
VerbatimLatitude               varchar(255),
Disposition		       varchar(255),   # where is this sample now?
TaxonNotes		       varchar(255),    # added 11/15/2005
Collectors_ID                  varchar(50),    ## collectors field notes no. 02/13/06

batch_id                       varchar(50)    # added June 26, 2023
);



create index eme_coll_OtherEventID on eme_collecting_event (OtherEventID);
create index eme_coll_OtherEventID2 on eme_collecting_event (OtherEventID2);
create index eme_coll_OtherMiscID on eme_collecting_event (OtherMiscID);
create index eme_coll_Collectors_ID on eme_collecting_event (Collectors_ID);
create index eme_coll_ProjectName on eme_collecting_event (ProjectName);
create index eme_coll_Collector_List on eme_collecting_event (Collector_List);

#create index eme_coll_Locality on eme_collecting_event (Locality);
CREATE FULLTEXT INDEX eme_coll_Locality on eme_collecting_event ( Locality );

create index eme_coll_Habitat_Host on eme_collecting_event (Habitat_Host);
create index eme_coll_Collection_Method on eme_collecting_event (Collection_Method);
create index eme_coll_Country on eme_collecting_event (Country);
create index eme_coll_StateProvince on eme_collecting_event (StateProvince);
create index eme_coll_Island on eme_collecting_event (Island);
create index eme_coll_HoldingInstitution on eme_collecting_event (HoldingInstitution);
create index eme_coll_TaxonNotes on eme_collecting_event (TaxonNotes);
create index eme_coll_EnteredBy on eme_collecting_event (EnteredBy);