/* NB: this is not a standard DwC version, but adapted to the terminology of a brief web service response with a few additions. Do not re-use for other purposes!
*/

SELECT
o.id AS 'TaxonOccurrence_gbifKey', 
concat('http://data.gbif.org/ws/rest/occurrence/get/',o.id) AS 'TaxonOccurrence_about',
lub.br_value AS 'basisOfRecordString', 
r.catalogue_number AS 'catalogNumber', 
r.collection_code AS 'collectionCode', 
replace(IFNULL(r.collector_name, ""),  "\n", " ")  AS 'collector', 
replace(IFNULL(r.country, ""),  "\n", " ")  AS 'country',  
o.iso_country_code AS 'country_code_interpreted',
r.latitude AS 'decimalLatitude',
r.longitude AS 'decimalLongitude',
o.latitude AS 'decimalLatitude_interpreted',
o.longitude AS 'decimalLatitude_interpreted',
o.geospatial_issue AS 'geospatial_issue_flag',
r.institution_code AS 'institutionCode', 
o.occurrence_date AS 'earliestDateCollected',  
o.occurrence_date AS 'latestDateCollected',
o.taxon_concept_id AS 'TaxonConcept_gbifKey', 
concat('http://data.gbif.org/ws/rest/taxon/get/',o.taxon_concept_id) AS 'TaxonConcept_about',
/* double-checked agains web service definition: it is indeed the taxon_concept_id, _not_ the nub_concept_id, that the service returns! 
*/
o.nub_concept_id AS 'NubConcept_gbifKey', /* not contained in web service response */
r.scientific_name AS 'nameComplete', 
'true' AS 'scientific',
r.scientific_name AS 'taxonName', 
replace(IFNULL(r.locality, ""),  "\n", " ")  AS 'locality', 
'Data from GBIF data index - original values.' AS 'gbifNotes'

FROM
occurrence_record o
  JOIN data_resource dr ON dr.id = o.data_resource_id 
  JOIN data_provider dp ON o.data_provider_id = dp.id 
  LEFT JOIN country c ON o.iso_country_code = c.iso_country_code 
  LEFT JOIN lookup_basis_of_record lub ON o.basis_of_record=lub.br_key
  LEFT JOIN image_record ir ON o.id = ir.occurrence_id  
  INNER JOIN raw_occurrence_record r ON o.id=r.id 
  INNER JOIN taxon_concept nc ON o.nub_concept_id=nc.id 
    INNER JOIN taxon_name nn ON nc.taxon_name_id=nn.id 
  LEFT JOIN taxon_concept kc ON nc.kingdom_concept_id=kc.id 
    LEFT JOIN taxon_name kn ON kc.taxon_name_id=kn.id 
  LEFT JOIN taxon_concept pc ON nc.phylum_concept_id=pc.id 
    LEFT JOIN taxon_name pn ON pc.taxon_name_id=pn.id 
  LEFT JOIN taxon_concept cc ON nc.class_concept_id=cc.id 
    LEFT JOIN taxon_name cn ON cc.taxon_name_id=cn.id 
  LEFT JOIN taxon_concept oc ON nc.order_concept_id=oc.id 
    LEFT JOIN taxon_name orn ON oc.taxon_name_id=orn.id 
  LEFT JOIN taxon_concept fc ON nc.family_concept_id=fc.id 
    LEFT JOIN taxon_name fn ON fc.taxon_name_id=fn.id 
  LEFT JOIN taxon_concept gc ON nc.genus_concept_id=gc.id 
    LEFT JOIN taxon_name gn ON gc.taxon_name_id=gn.id  

WHERE
(c.region LIKE 'SEU' OR
  c.iso_country_code LIKE 'FR')
/* 
AND
  o.latitude >= 34
AND
  o.latitude <= 47
AND
  o.longitude >= -12
AND
  o.longitude <= 30
this is better done in a bounding box search using cell ids... 
*/
-- bottom-left: 34/-12; top-right: 46/29
-- cell-ids: (90 + lat) * 360 + 180 + lon 
-- > c1=44808
-- > c2=49169
AND
( /* select the "rows"... */
  o.cell_id >= 44808 AND
  o.cell_id <= 49169 AND
  /* ... then trim "left" and "right" borders */
  (o.cell_id % 360>= 44808 % 360) AND
  (o.cell_id % 360<= 49169 % 360)
)