SELECT util.search_path_append('util');

SELECT create_if_not_exists($$CREATE INDEX "Specimen.infrarank" ON $$||:table_str||$$ ("EQUIV#to:usdaRank#infrarank")$$); -- runtime: 0.1 s ("Time: 135.984 ms") @starscream

-- remove mismapped values
UPDATE :table SET "EQUIV#to:usdaRank#infrarank" = NULL WHERE "EQUIV#to:usdaRank#infrarank" = 'L.'; -- runtime: ~0 ("Time: 2.370 ms") @starscream


SELECT mk_derived_col((:table_str, 'specimenDonorInstitution'),
-- UNCCD likely = UNCC database
$$_map('UNCCD=>UNCC, *=>*', "specimenDonorInstitution_verbatim")$$)
; -- runtime: 1 s ("Time: 1057.222 ms") @starscream

SELECT mk_derived_col((:table_str, 'recordedBy'),
$$_join("recordedBy__primary", "recordedBy__additional__1", "recordedBy__additional__2", "recordedBy__additional__3")$$)
; -- runtime: 1 s ("Time: 1001.616 ms") @starscream

SELECT mk_derived_col((:table_str, 'occurrenceRemarks'),
$$_join("occurrenceRemarks__1", "occurrenceRemarks__2", "occurrenceRemarks__3", "occurrenceRemarks__4")$$)
; -- runtime: 1 s ("Time: 996.729 ms") @starscream


SELECT mk_derived_col((:table_str, 'cultivated__campus'),
/* "this refers to occurrence on the UNCC campus, but I do not know the meaning
of the codes. Presumably C = cultivated"
(https://projects.nceas.ucsb.edu/nceas/attachments/download/723/UNCC-validate_rkp_bb_rkp.xlsx#Campus)
*/
$$_map('*=>true', "cultivated__campus__verbatim")$$)
; -- runtime: 0.5 s ("Time: 353.373 ms") @starscream

SELECT mk_derived_col((:table_str, 'cultivated__main'),
-- for our purposes, anything non-NULL should be assumed to be cultivated
$$_map('*=>true', "cultivated__main__verbatim")$$)
; -- runtime: 0.5 s ("Time: 403.916 ms") @starscream

SELECT mk_derived_col((:table_str, 'cultivated'),
$$_alt("cultivated__main", "cultivated__campus")$$)
; -- runtime: 0.5 s ("Time: 394.182 ms") @starscream


CREATE OR REPLACE FUNCTION map_reproductive_condition(value text)
  RETURNS text AS
$BODY$
/*
"
Most are typos

A = absent

I = immature

M = mature
" (mailto:jim@habitatassessment.com?Habitat_Assessment.Jim_Matthews.to(uniola@email.unc.edu?Bob_Peet).2010-12-3:17:08.Re:_herbarium_phenology_codes_(fwd))
*/
SELECT util._map('A=>absent, I=>immature, M=>mature, *=>NULL', $1)
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

SELECT mk_derived_col((:table_str, 'flower'), $$map_reproductive_condition("flower_verbatim")$$); -- runtime: 0.5 s ("Time: 635.054 ms") @starscream
SELECT mk_derived_col((:table_str, 'fruit' ), $$map_reproductive_condition("fruit_verbatim" )$$); -- runtime: 0.5 s ("Time: 591.854 ms") @starscream

SELECT mk_derived_col((:table_str, 'reproductiveCondition'),
-- using the suggested values at DwC.vegpath.org?reproductiveCondition
$$_join(
  _map(('{immature,mature}'::text[] => 'in bloom'     ) || '*=>NULL', "flower")
, _map(('{immature,mature}'::text[] => 'fruit-bearing') || '*=>NULL', "fruit" )
)$$)
; -- runtime: 1.5 s ("Time: 1433.204 ms") @starscream