# Create a table for type_records
create table type_record (occurrence_id int primary key);
insert ignore into type_record
select occurrence_id from GBIF.typification_record;

# 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
create table bioRecord_base
select o.id, 
	institution_code_id, collection_code_id, 
	IfNull(nub_concept_id, taxon_concept_id) as taxon_id,
	o.data_resource_id, iso_country_code, occurrence_date, year,
  o.longitude, o.latitude,
  case 
  	when ty.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 as bor,
  # these data are required for duplicate detection only
  data_provider_id, basis_of_record, species_concept_id,
	cast(2 * atan(  case when o.longitude<0 then o.longitude+360 else o.longitude end 
	              / case when o.latitude=0 then 0.00001 else
				             case when o.latitude<0 then o.latitude+360 else o.latitude end
				          end)
			 /pi() as decimal(6,5)) as coord,
	dayofmonth(o.occurrence_date) as day, month(o.occurrence_date) as month
from GBIF.occurrence_record o
left join type_record ty on o.id = occurrence_id;

select * from bioRecord_base
limit 0, 300000000
INTO OUTFILE 'e:/synthesys/dump/bioRecord_base1.txt'
FIELDS ESCAPED BY '';

select * from bioRecord_base
limit 300000000, 300000000
INTO OUTFILE 'e:/synthesys/dump/bioRecord_base2.txt'
FIELDS ESCAPED BY '';

# Now, create a temp table for the identifiers we need
create table identifier (
	occurrence_id int, 
	identifier_type smallint unsigned, 
	identifier varchar(255),
	primary key (occurrence_id, identifier_type)
);

insert ignore into identifier
select occurrence_id, identifier_type, replace(replace(identifier, '\n', ' '), '\t', ' ')
from GBIF.identifier_record
where identifier_type between 1 and 4;

create table bioRecord_ext
select straight_join
  ro.id, 
  replace(replace(catalogue_number, '\n', ' '), '\t', ' ') as catalogue_number,
  replace(replace(ro.collector_name, '\n', ' '), '\t', ' ') as collector_name,
  replace(replace(replace(ro.locality, '\Z', ' '), '\n', ' '), '\t', ' ') as locality,
  guid.identifier as guid, fn.identifier as fn, cn.identifier as cn, an.identifier as an,
  # these data are required for duplicate detection only
  replace(replace(ro.state_province, '\n', ' '), '\t', ' ') as state_province, 
  replace(replace(ro.county, '\n', ' '), '\t', ' ') as county, 
  replace(replace(ro.continent_ocean, '\n', ' '), '\t', ' ') as continent_ocean, 
  replace(replace(ro.identifier_name, '\n', ' '), '\t', ' ') as identifier_name, 
  ro.identification_date, 
  case when cast(ro.lat_long_precision as decimal(10,2)) = 0 then NULL else cast(ro.lat_long_precision as decimal(10,2)) end as lat_long_precision,  
  ro.min_altitude, ro.max_altitude, ro.altitude_precision, 
  ro.min_depth, ro.max_depth, ro.depth_precision
from GBIF.raw_occurrence_record ro
left join identifier 						guid  		on ro.id = guid.occurrence_id and guid.identifier_type = 1
left join identifier 						fn 		on ro.id = fn.occurrence_id and fn.identifier_type = 2
left join identifier 						cn		on ro.id = cn.occurrence_id and cn.identifier_type = 3
left join identifier 						an 		on ro.id = an.occurrence_id and an.identifier_type = 4;

select * from bioRecord_ext
limit 0, 200000000
INTO OUTFILE 'e:/synthesys/dump/bioRecord_ext1.txt'
FIELDS ESCAPED BY '';

select * from bioRecord_ext
limit 200000000, 200000000
INTO OUTFILE 'e:/synthesys/dump/bioRecord_ext2.txt'
FIELDS ESCAPED BY '';

select * from bioRecord_ext
limit 400000000, 200000000
INTO OUTFILE 'e:/synthesys/dump/bioRecord_ext3.txt'
FIELDS ESCAPED BY '';

# get all taxon_concepts that need to be in the cache
create table tmpTaxa (tcKey int primary key);

insert ignore into tmpTaxa
select IfNull(nub_concept_id, taxon_concept_id) from GBIF.occurrence_record;

insert ignore into tmpTaxa
select species_concept_id from GBIF.occurrence_record;

create temporary table tmp
select
     tc.id as taxonKey, taxon.canonical as taxonName, taxon.generic as genus, taxon.author,
     taxon.specific_epithet, taxon.infraspecific, taxon.infraspecific_marker,
     kingdom.canonical as kingdom, phylum.canonical as phylum, class.canonical as class, ordo.canonical as ordo, family.canonical as family
from tmpTaxa
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
select taxonKey, taxonName, author, genus, family, 0,
	  specific_epithet, infraspecific, infraspecific_marker
INTO OUTFILE 'e:/synthesys/dump/taxon.txt'
FIELDS ESCAPED BY ''
from tmp;

## Table Higher Taxon

create temporary table higherTaxon
select taxonKey, kingdom as name, 'kingdom'as rank 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;

select * 
INTO OUTFILE 'e:/synthesys/dump/higherTaxon.txt'
FIELDS ESCAPED BY ''
from higherTaxon;

## Vernacular Name
select taxonKey, name, iso_language_code
INTO OUTFILE 'e:/synthesys/dump/vernacularName.txt'
FIELDS ESCAPED BY ''
from tmp
join GBIF.common_name cn on taxonKey = cn.taxon_concept_id;

## Table imageRecord
select id, occurrence_id,	url, description
INTO OUTFILE 'e:/synthesys/dump/imageRecord.txt'
FIELDS ESCAPED BY ''
from GBIF.image_record
where url like 'http://%' or url like 'https://%';

## Table isoCountry
select iso_country_code, name
INTO OUTFILE 'e:/synthesys/dump/isoCountry.txt'
FIELDS ESCAPED BY ''
from GBIF.country_name
where locale = 'en';

## Table resource
select
	res.id, res_ap.id, IfNull(gbif_registry_service_uuid, IfNull(res_ap.uuid, res_ap.id)),
	remote_id_at_url, res.display_name, agent.email,
	prov.name, prov.logo_url, prov.address, prov.email,
	content_ns.namespace,
	case when InStr(content_ns.namespace, 'digir') = 0 then url else concat(remote_id_at_url, '@', url) end,
	case protocol_ns.namespace
			when 'http://www.biocase.org/schemas/protocol/1.3'			then 'biocase'
			when 'http://rs.tdwg.org/tapir/1.0'											then 'tapir'
			when 'http://manis.digir.net/schema/protocol/2003/1.0'	then 'digir'
			when 'http://digir.net/schema/protocol/2003/1.0'				then 'digir'
			when 'HTTP'																							then 'http'
			else 'http'
	end	
INTO OUTFILE 'e:/synthesys/dump/resource.txt'
FIELDS ESCAPED BY ''
from GBIF.resource_access_point res_ap
join GBIF.data_resource res on res_ap.data_resource_id = res.id
left join GBIF.dataset_uuid_to_service_uuid_mappings_13june2013 legacy 
	on res.id = legacy.gbif_portal_data_resource_id and url = legacy.gbif_registry_service_url
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 content_ns_id on (res_ap.id = content_ns_id.resource_access_point_id and content_ns_id.priority = 1)
LEFT JOIN GBIF.property_store_namespace content_ns on content_ns_id.property_store_namespace_id = content_ns.id
LEFT JOIN GBIF.namespace_mapping protocol_ns_id on (res_ap.id = protocol_ns_id.resource_access_point_id and protocol_ns_id.priority = 0)
LEFT JOIN GBIF.property_store_namespace protocol_ns on (protocol_ns_id.property_store_namespace_id = protocol_ns.id)
where res_ap.deleted is null and res.deleted is null
  and res_ap.data_resource_id <> 0
group by res.id;     /* required because there is more than one agent for two providers */

## Institution + collection codes
select id, code
INTO OUTFILE 'e:/synthesys/dump/inst_code.txt'
FIELDS ESCAPED BY ''
from GBIF.institution_code;

select id, replace(replace(code, '\n', ' '), '\t', ' ')
INTO OUTFILE 'e:/synthesys/dump/coll_code.txt'
FIELDS ESCAPED BY ''
from GBIF.collection_code;