# first, create required tables if they don't exist
create table if not exists lastUpdated (
	id tinyint default 1 primary key,
	lastRun timestamp null,
	maxModified datetime null,
  maxDeleted datetime null,
  tcModified datetime null);

insert ignore into lastUpdated(maxModified, maxDeleted, tcModified)
values('2000-01-01', '2000-01-01', '2000-01-01');

## Get variables with update/delete dates
select @mod   := maxModified from lastUpdated;
select @del   := maxDeleted from lastUpdated;
select @tcMod := tcModified from lastUpdated;

## ENABLE THIS PART IF USED FOR UPDATING AN EXISTING CACHE
## Look for deleted records, insert IDs into removedRecords
#create temporary table removed
#select id, deleted from GBIF.raw_occurrence_record where deleted > @del;
#
#insert ignore into removedRecords
#select id from removed;
#
#select @del_new := max(deleted) from removed;

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

alter table 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),
  fieldNumber						varchar(127),
  collectorNumber        varchar(127),
	localityText			     varchar(255),
  geoLongitude			     float,
  geoLatitude			      float,
  recordBasis 			     Char(1) default NULL,
	bioRecordModifiedDate  datetime,
  key(bioRecordTaxonKey), key (bioRecordResourceKey),
  key (fieldNumber), key (collectorNumber), key(bioRecordId),
  fulltext(collector), fulltext(localityText)
) max_rows = 200000000;

alter table 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 bioRecord
select
  ro.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 fn.identifier), trim('\n' from cn.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,
  ro.modified
from GBIF.occurrence_record o
join GBIF.raw_occurrence_record ro	 on ro.id = o.id
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 type_specimen t            on o.id = t.occurrence_id
order by bioRecordTaxonKey;

## ENABLE THIS PART IF USED FOR UPDATING AN EXISTING CACHE (and move it to the approprate section in the SELECT above)
# left join includeRecords       		  on ro.id = brKey
# where ro.modified > @mod    /* either this record was updated*/
#   or brKey is not null;    /* or we have to examine it again because the corm flag was changed */


select @mod_new := max(bioRecordModifiedDate) from bioRecord;

## all taxon concepts in bioRecord have to get included
create table includeTC (tcKey int);

## ENABLE THIS PART IF USED FOR UPDATING AN EXISTING CACHE
#insert into includeTC
#select distinct bioRecordTaxonKey as tcKey from bioRecord;
#alter table includeTC add primary key (tcKey);

create temporary 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),
  tc_modified datetime
);

insert into tmp
select straight_join
     tc.id, taxon.canonical, taxon.generic, taxon.author,
     kingdom.canonical, phylum.canonical, class.canonical, ordo.canonical, family.canonical,
     tc.modified
from GBIF.taxon_concept tc
left join includeTC                     on                    tc.id = tcKey
     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
where tc.modified > @tcMod    /* either modified */
   or tcKey is not null;      /* or in the updated bioRecord table */

select @tcMod_new := max(tc_modified) from tmp;

# 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;


## Table Higher Taxon

create table higherTaxon (
    htKey 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 100000000 + taxonKey, taxonKey, kingdom, 'kingdom' from tmp where kingdom is not null;

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

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

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

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


## Vernacular Name

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

alter table vernacularName
  add primary key (vernacularKey),
	add key (vernacularTaxonKey),
	add index (vernacularName),
  disable keys;


## 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;

## Table isoCountry

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

insert into isoCountry
select iso_country_code, name
from GBIF.country_name
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 GBIF.resource_access_point res_ap
join GBIF.data_resource res 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 res.id;     /* required because there is more than one agent for two providers */

# Last, update the status table
update lastUpdated
set lastRun = now(),
    maxModified = If(@mod_new > MaxModified, @mod_new, maxModified),
    maxDeleted  = If(@del_new > MaxDeleted,  @del_new, maxDeleted),
    tcModified  = If(@tcMod_new > tcModified, @tcMod_new, tcModified);
