## first, create a table with relevant geoRegionRecords

create table geoRegionRecord(
	geoRegionRecordBioRecordKey		int,
	geoRegionRecordRegionKey		  int,
	isoCountryCode					      char(2),
  key(geoRegionRecordBioRecordKey), key (geoRegionRecordRegionKey)
);
alter table geoRegionRecord disable keys;

insert into geoRegionRecord
select STRAIGHT_JOIN geoRegionRecordBioRecordKey, geoRegionRecordRegionKey, isoCountryCode
from updatedRecords
join GBIF.geoRegionRecord	on geoRegionRecordBioRecordKey  = brKey
left join GBIF.isoCountry on geoRegionRecordCountryKey    = isoCountryKey;

alter table geoRegionRecord enable keys;

## then, extract geoRegionRecords with countryKey

create table tmpGeoRegionRecords
select geoRegionRecordBioRecordKey, isoCountryCode
from geoRegionRecord
where isoCountryCode is not null;

alter table syn_delta.tmpGeoRegionRecords
	add key (geoRegionRecordBioRecordKey);

## now create main table bioRecord

create table bioRecord (
	bioRecordKey 			      int primary key,
	bioRecordId				      varchar(127),
	bioRecordModifiedDate 	datetime,
	bioRecordTaxonKey		    int,
	bioRecordResourceKey	  int,
	bioRecordCollectionKey 	int,
  bioRecordCountryCode	  char(2),
	gatheringDate			      varchar(127),
	localityText			      varchar(255),
  geoPrecision			      int,
  geoLongitude			      float,
  geoLatitude				      float,
  recordBasis 			      Char(1) default NULL,
  key(bioRecordTaxonKey), key(bioRecordResourceKey), key(bioRecordCollectionKey), key(bioRecordCountryCode)
) max_rows = 200000000;
alter table bioRecord disable keys;

insert into bioRecord
select SQL_BIG_RESULT STRAIGHT_JOIN
	bioRecordKey, bioRecordId, bioRecord.bioRecordModifiedDate,
	bioRecordTaxonKey, bioRecordResourceKey, bioRecordCollectionKey,isoCountryCode,
	temporalRecordText, geoLocationText,
  geoPrecision, geoLongitude, geoLatitude,
  NULL
from updatedRecords
     join GBIF.bioRecord            on bioRecordKey 				        = brKey
left join GBIF.temporalRecord       on temporalRecordBioRecordKey   = bioRecordKey
left join GBIF.geoRecord            on geoBioRecordKey              = bioRecordKey
left join tmpGeoRegionRecords     	on geoRegionRecordBioRecordKey  = bioRecordKey;

alter table bioRecord enable keys;

## create temporary tables with all existing objects

create table tmpTaxa 				select distinct	bioRecordTaxonKey 			 from bioRecord;
create table tmpCollections select distinct	bioRecordCollectionKey	 from bioRecord where bioRecordCollectionKey <> 0 and bioRecordCollectionKey is not null;
create table tmpCountries 	select distinct	bioRecordCountryCode 	   from bioRecord where bioRecordCountryCode is not null;
create table tmpRegions 		select distinct	geoRegionRecordRegionKey from geoRegionRecord;

alter table syn_delta.tmpTaxa			    add key (bioRecordTaxonKey);
alter table syn_delta.tmpCollections	add key (bioRecordCollectionKey);
alter table syn_delta.tmpCountries		add key (bioRecordCountryCode);
alter table syn_delta.tmpRegions		  add key (geoRegionRecordRegionKey);

## now, create table resource

create table resource (
    resourceKey       int primary key,
    serviceKey        int,
    serviceuuid       varchar(50),
    resourceId        varchar(127),
    resourceName      varchar(255),
    resourceEmail     varchar(255),
  	provName          varchar(127),
    provIcon          varchar(255),
    provAddress       varchar(255),
    provEmail         varchar(255),
    content_schema    varchar(255),
	  bindingURL        varchar(255),
	  protocol          varchar(255),
    key(serviceKey)
);

insert into resource
select resourceKey, serviceKey, serviceuuid, resourceId, resourceName, resourceEmail,
  	   provName, provIcon, provAddress, provEmail, resourceConceptualSchema,
	     case when InStr(resourceConceptualSchema, 'abcd') = 0 then concat(resourceId, '@', bindingURL) else bindingURL end,
	     case when InStr(resourceConceptualSchema, 'abcd') = 0 then 'DiGIR' else 'BioCASe' end
from GBIF.resource
left join GBIF.service               on resourceServiceKey           = serviceKey
left join GBIF.provider              on serviceProviderKey           = ProvKey
left join GBIF.binding		 					 on bindingServiceKey						 = serviceKey
group by resourceKey;

## Table collection

create table collection (
    collectionKey             int primary key,
    collectionInstitutionCode varchar(255),
    collectionCollectionCode  varchar(255),
    index (collectionInstitutionCode), index (collectionCollectionCode)
);

insert into collection
select collection.*
from GBIF.collection
join tmpCollections	on bioRecordCollectionKey = collectionKey;

## Table isoCountry

create table isoCountry (
    isoCountryCode char(2) primary key,
    countryName    varchar(127),
    index(countryName)
);

insert into isoCountry
select straight_join isoCountryCode, lingElemName as countryName
		 from tmpCountries
join GBIF.isoCountry 	  			on bioRecordCountryCode	 = isoCountryCode
join GBIF.linguisticElement		on isoCountryLingElemKey = lingElemKey;

## Table imageRecord

create table imageRecord (
    imageRecordKey           int primary key,
    imageRecordBioRecordKey  int,
    imageRecordURL           varchar(255),
    imageRecordComment       text,
    key (imageRecordBioRecordKey)
);

insert into imageRecord
select imageRecord.*
from updatedRecords
join GBIF.imageRecord	  on imageRecordbioRecordKey	= brKey
where imageRecordURL <> 'NULL';

## Table Higher Taxon

create table higherTaxon (
    higherTaxonKey   int primary key,
    taxonKey         int,
		higherTaxonName  varchar(127),
		higherTaxonRank  varchar(127),
    key (taxonKey), index(higherTaxonName), index(higherTaxonRank)
);
alter table higherTaxon disable keys;

insert into higherTaxon
select straight_join relKey, relFromTaxonKey, taxoncanonical, cast(rankName.lingElemName as char)
from      tmpTaxa
		 join GBIF.relationship								 	 on bioRecordTaxonKey	= relFromTaxonKey
left join GBIF.status												 on relStatusKey      = statusKey
left join GBIF.linguisticElement statusName  on statusLingElemKey = statusName.lingElemKey
left join GBIF.taxon  											 on relTotaxonKey   	= taxonKey
left join GBIF.rank						 						   on taxonRankKey 			= rankKey
left join GBIF.linguisticElement rankName    on rankLingElemKey   = rankName.lingElemKey
where cast(statusName.lingElemName as Char) like 'higher taxon';

alter table higherTaxon enable keys;

## Table taxon

create table taxon (
    taxonKey       int primary key,
    taxonName      varchar(127),
    taxonAuthor    varchar(127),
	  genus          varchar(127),
	  family         varchar(127),
	  unitCount      int default 0,
    index(taxonName), index(genus), index(family)
);
alter table taxon disable keys;

insert into taxon
select taxonKey, taxonCanonical, taxonAuthor,
       case when InStr(taxonCanonical, ' ') > 1 then left (taxonCanonical, InStr(taxonCanonical, ' ') - 1) else null end,
       NULL, 0
from tmpTaxa
join GBIF.taxon on bioRecordTaxonKey	= taxonKey;

update syn_delta.taxon, syn_delta.higherTaxon
set family = higherTaxonName
where higherTaxon.taxonKey = taxon.taxonKey
  and higherTaxonRank = 'Family';

alter table taxon enable keys;

## Vernacular Name

create table vernacularName
select vernacularKey, vernacularTaxonKey, vernacularName
from tmpTaxa
join GBIF.vernacular on bioRecordTaxonKey	= vernacularTaxonKey;

alter table syn_delta.vernacularName
  add primary key (vernacularKey),
	add key (vernacularTaxonKey),
	add index (vernacularName);

## Finally, two tables that are required for slicing (geoRegion and scientific)

create table scientific
select scientific.*
from tmpTaxa
join GBIF.scientific on scitaxonkey = bioRecordTaxonKey;

alter table syn_delta.scientific
  add primary key (scikey),
  add key (scitaxonkey);


create table geoRegion
select straight_join geoRegionKey, geoRegionTypeKey, geoRegionName, isoCountryCode
from tmpRegions
join GBIF.geoRegion on geoRegionRecordRegionKey	= geoRegionKey
left join GBIF.isoCountry on geoRegionCountryKey = isoCountryKey;

alter table syn_delta.geoRegion
	add primary key (geoRegionKey),
	add key (geoRegionTypeKey),
	add key (isoCountryCode);

## Remove tmp tables
	tmpTaxa, tmpCollections, tmpCountries;
