# create a temp table for that holds the ids of all type specimen records
create table tmp_type_specimen
select occurrence_id
from GBIF.typification_record
group by occurrence_id;

alter table tmp_type_specimen
	add key (occurrence_id);

# Now, create main table
create table bioRecord (
	bioRecordKey 	      	int primary key,
  institution_code     	varchar(255),
  collection_code      	varchar(255),
	bioRecordId			      varchar(255),
	bioRecordTaxonKey		  int,
	bioRecordResourceKey 	int,
  bioRecordCountryCode	 char(2),
  gatheringDate			    date,
  gatheringYear 		     smallint unsigned,
  collector              varchar(127),
	guid						varchar(127),
	fieldNumber					varchar(127),
	collectorNumber				varchar(127),
	accessionNumber				varchar(127),
	localityText			     varchar(127),
  geoLongitude			     float,
  geoLatitude			      float,
  recordBasis 			     Char(1) default NULL,
  key(bioRecordTaxonKey), key (bioRecordResourceKey), key(bioRecordCountryCode),
  key (fieldNumber), key (collectorNumber), key(accessionNumber), key(guid), 
	key(bioRecordId),
  fulltext(collector), fulltext(localityText)
);

create table tmp_bioRecord like bioRecord;

alter table bioRecord disable keys;
alter table tmp_bioRecord disable keys;

# IMPORTANT NOTE:
# taxon in SYN cache is equivalent to GBIF.taxon_concept (plus the taxon name from GBIF.taxon_name)
# thus, bioRecordTaxonKey saves a GBIF-taxon_concept.id
insert ignore into tmp_bioRecord
#explain
select straight_join
  o.id, ro.institution_code, ro.collection_code, ro.catalogue_number,
  IfNull(nub_concept_id, taxon_concept_id) as bioRecordTaxonKey, o.data_resource_id, o.iso_country_code, 
	o.occurrence_date, o.year, ro.collector_name, 
	trim('\n' from guid.identifier), trim('\n' from fn.identifier), trim('\n' from cn.identifier), trim('\n' from an.identifier), 
	ro.locality, o.longitude, o.latitude,
  case 
  	when t.occurrence_id is not null then 't' 
    else 
      case
    	when o.basis_of_record = 1              then 'o'
    	when o.basis_of_record between 2 and 6  then 's'
        else NULL
      end
  end
from GBIF.occurrence_record o ignore index(ix_or_iso_country_code)
join GBIF.raw_occurrence_record ro	 on ro.id = o.id
left join GBIF.identifier_record guid on (o.id = guid.occurrence_id and guid.identifier_type = 1)
left join GBIF.identifier_record fn  on (o.id = fn.occurrence_id and fn.identifier_type = 2)
left join GBIF.identifier_record cn  on (o.id = cn.occurrence_id and cn.identifier_type = 3)
left join GBIF.identifier_record an  on (o.id = an.occurrence_id and an.identifier_type = 4)
left join tmp_type_specimen t        on o.id = t.occurrence_id
where o.iso_country_code in
  ('ZA', 'DZ', 'AO', 'BJ', 'BW', 'BF', 'BI', 'CM', 'CV', 'CF', 'TD', 'KM', 'CD', 'CG', 'CI', 'DJ', 'EG', 'GQ', 'ER', 'ET',
   'GA', 'GM', 'GH', 'GN', 'GW', 'KE', 'LS', 'LR', 'LY', 'MG', 'MW', 'ML', 'MR', 'MU', 'YT', 'MA', 'MZ', 'NA', 'NE', 'NG',
   'RW', 'ST', 'SN', 'SC', 'SL', 'SO', 'SD', 'SZ', 'TZ', 'TG', 'TN', 'UG', 'ZM', 'ZW');
/*or o.data_provider_id in
  (4, 5, 8, 10, 12, 16, 21, 27, 27, 34, 39, 49, 51, 82, 92, 104, 108, 111, 119, 120, 123, 124, 126, 127, 130, 139, 140, 
	 143, 147, 148, 149, 155, 158, 161, 164, 169, 173, 183, 184, 185, 196, 199, 208, 220, 225, 232, 241, 250, 273, 274, 
	 134, 199);*/
	 
insert into bioRecord
select * from tmp_bioRecord
order by bioRecordTaxonKey;

alter table bioRecord enable keys;

## Now, get the IDs of all dependent objects
create table tmp_taxa
select distinct bioRecordTaxonKey as tcKey
from bioRecord;

create table tmp_countries
select distinct bioRecordCountryCode as countryCode
from bioRecord;

create table tmp_resource
select distinct bioRecordResourceKey as resKey
from bioRecord;

## Create a temp table for taxon
create table tmp (
	taxonKey int,
	taxonName varchar(255),
	genus varchar(255),
	author varchar(255),
	kingdom varchar(255),
	phylum varchar(255),
	class varchar(255),
	ordo varchar(255),
	family  varchar(255)
);

insert into tmp
select straight_join
     tc.id, taxon.canonical, taxon.generic, taxon.author,
     kingdom.canonical, phylum.canonical, class.canonical, ordo.canonical, family.canonical
from tmp_taxa
join GBIF.taxon_concept tc							on 										tcKey = tc.id
join GBIF.taxon_name    taxon      			on         tc.taxon_name_id = taxon.id
left join GBIF.taxon_concept kingdom_tc on    tc.kingdom_concept_id = kingdom_tc.id
left join GBIF.taxon_name    kingdom    on kingdom_tc.taxon_name_id = kingdom.id
left join GBIF.taxon_concept phylum_tc  on     tc.phylum_concept_id = phylum_tc.id
left join GBIF.taxon_name    phylum     on  phylum_tc.taxon_name_id = phylum.id
left join GBIF.taxon_concept class_tc   on      tc.class_concept_id = class_tc.id
left join GBIF.taxon_name    class      on   class_tc.taxon_name_id = class.id
left join GBIF.taxon_concept ordo_tc    on      tc.order_concept_id = ordo_tc.id
left join GBIF.taxon_name    ordo       on    ordo_tc.taxon_name_id = ordo.id
left join GBIF.taxon_concept family_tc  on     tc.family_concept_id = family_tc.id
left join GBIF.taxon_name    family     on  family_tc.taxon_name_id = family.id;

## 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, taxonName, author, genus, family, 0 from tmp;

alter table taxon enable keys;


## Table Higher Taxon

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

insert into higherTaxon
select taxonKey, kingdom, 'kingdom' from tmp where kingdom is not null;

insert into higherTaxon
select taxonKey, phylum, 'phylum' from tmp where phylum is not null;

insert into higherTaxon
select taxonKey, class, 'class' from tmp where class is not null;

insert into higherTaxon
select taxonKey, ordo, 'order' from tmp where ordo is not null;

insert into higherTaxon
select taxonKey, family, 'family' from tmp where family is not null;

alter table higherTaxon enable keys;


## Vernacular Name

create table vernacularName
select straight_join taxonKey as vernacularTaxonKey, name as vernacularName
from tmp
join GBIF.common_name   cn on taxonKey = cn.taxon_concept_id;

alter table vernacularName
	add key (vernacularTaxonKey),
	add index (vernacularName);


## Table imageRecord

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

insert into imageRecord
select id, occurrence_id, url, description
from bioRecord
join GBIF.image_record on bioRecordKey = occurrence_id;

alter table imageRecord enable keys;

## Table isoCountry

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

insert into isoCountry
select iso_country_code, name
from tmp_countries
join GBIF.country_name on countryCode = iso_country_code
where locale = 'en';


## Table resource

create table resource (
    resourceKey       int primary key,
    AccessPointKey    int,
    AccessPointUUID   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(AccessPointKey)
);

insert into resource
select
  res.id, res_ap.id, res_ap.uuid,
	remote_id_at_url, res.display_name, agent.email,
	prov.name, prov.logo_url, prov.address, prov.email,
	schema_ns.namespace,
  case when InStr(namespace, 'digir') = 0 then url else concat(remote_id_at_url, '@', url) end,
  case when InStr(namespace, 'digir') <> 0
      then 'DiGIR'
      else case when InStr(namespace, 'abcd') <> 0
              then 'BioCASe'
              else 'TAPIR'
           end
  end
from tmp_resource
join GBIF.data_resource res on resKey = res.id
join GBIF.resource_access_point res_ap on res_ap.data_resource_id = res.id
join GBIF.data_provider prov on res_ap.data_provider_id = prov.id
left join GBIF.data_provider_agent prov_agent on (prov.id = prov_agent.data_provider_id and agent_type = 1)
left join GBIF.agent on prov_agent.agent_id = agent.id
LEFT JOIN GBIF.namespace_mapping schema_ns_id on (res_ap.id = schema_ns_id.resource_access_point_id and schema_ns_id.priority = 1)
LEFT JOIN GBIF.property_store_namespace schema_ns on schema_ns_id.property_store_namespace_id = schema_ns.id
group by resKey;     /* required because there is more than one agent for two providers */

drop table tmp_type_specimen, tmp_taxa, tmp_countries, tmp_resource, tmp, tmp_bioRecord;