-- Tables
create table bioRecord_base (
	bioRecordKey 	      		int primary key,
	institution_code_id			int,
	collection_code_id			int,
	bioRecordTaxonKey			int,
	bioRecordResourceKey 		smallint,
	bioRecordCountryCode		char(2),
	gatheringDate				date,
	gatheringYear 				smallint,
	geoLongitude			    float,
	geoLatitude					float,
	recordBasis 			    Char(1) default NULL,
	/* for duplicate detection */
	data_provider_id			smallint,
	gbif_basis_of_record		tinyint,
	species_concept_id			int,
	degree						float,
	gatheringDay				tinyint,
	gatheringMonth				tinyint
);

create table bioRecord_ext (
	brKey 			     		int primary key,
	bioRecordId					varchar(255),
	collector					varchar(255),
	localityText			    varchar(2000),
	guid						varchar(255),
	fieldNumber					varchar(255),
	collectorNumber				varchar(255),
	accessionNumber				varchar(255),
	/* for duplicate detection */
	state_province				varchar(100),
	county						varchar(100),
	continent_ocean				varchar(100),
	identifier_name				varchar(255),
	identification_date			date,
	lat_lon_precision			float,
	min_altitude				varchar(50),
	max_altitude				varchar(50),
	altitude_precision			varchar(50),
	min_depth					varchar(50),
	max_depth					varchar(50),
	depth_precision				varchar(50)
);

create table institution_code (
	id							int primary key,
	institution_code			varchar(255)
);

create table collection_code (
	id							int primary key,
	collection_code				varchar(255)
);

create table taxon (
    taxonKey				int primary key,
    taxonName				varchar(255),
    taxonAuthor				varchar(255),
	genus					varchar(255),
	family					varchar(255),
	unitCount				int default 0,
	specific_epithet		varchar(255),
	infraspecific			varchar(255),
	infraspecific_marker	varchar(255)
);

create table higherTaxon (
    taxonKey         int,
	higherTaxonName  varchar(255),
	higherTaxonRank  varchar(255),
);
create clustered index htTaxonKey on higherTaxon(taxonKey);

create table vernacularName (
	vernacularTaxonKey int,
	vernacularName nvarchar(255),
	iso_language_code char(2)
);
create clustered index vTaxonKey on vernacularName(vernacularTaxonKey);

create table imageRecord (
	imageRecordKey			 int primary key nonclustered,
    imageRecordBioRecordKey  int,
    imageRecordURL           varchar(255),
    imageRecordComment       varchar(1000)
);
create clustered index irBioRecordKey on imageRecord(imageRecordBioRecordKey);

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

create table resource (
    resourceKey       int primary key,
    AccessPointKey    int,
    AccessPointUUID   varchar(50),
    resourceId        varchar(255),
    resourceName      varchar(255),
    resourceEmail     varchar(255),
  	provName          varchar(255),
    provIcon          varchar(255),
    provAddress       varchar(255),
    provEmail         varchar(255),
    content_schema    varchar(255),
	bindingURL        varchar(255),
	protocol          varchar(255)
);

/*create table corm_datasource(
	binding_uuid		varchar(50) primary key,
	european_data_only	bit
);

create table corm_resource(
	resKey				int primary key,
	european_data_only	bit
);*/


-- View for the raw_data table
if object_id ('raw_data') is not null drop view raw_data
go

create view raw_data as
select bioRecord_base.*, bioRecord_ext.*, 
	tn.taxonName as taxon_name, tn.genus as tn_genus, tn.specific_epithet as tn_species, tn.infraspecific as tn_infraspecies, tn.infraspecific_marker as tn_marker,
	sn.taxonName as species_name, sn.genus as sn_genus, sn.specific_epithet as sn_species, sn.infraspecific as sn_infraspecies, sn.infraspecific_marker as sn_marker,
	institution_code, collection_code
from bioRecord_base
left join bioRecord_ext on brKey = bioRecordKey
left join taxon tn on bioRecordTaxonKey = tn.taxonKey
left join taxon sn on species_concept_id = sn.taxonKey
left join institution_code on institution_code_id = institution_code.id
left join collection_code on collection_code_id = collection_code.id;
go
grant select on raw_data to webuser;

-- Function to produce a de-capitalized version of a COUNTRY NAME
if object_id ('fn_lowerCountryName') is not null drop function fn_lowerCountryName
go

CREATE FUNCTION fn_lowerCountryName(@name varchar(255)) RETURNS varchar(255) AS
BEGIN
	declare @res varchar(255) = '', @s varchar(255) = '', @pos int
	
	-- add a trailing space (in order to meet the while condition after the last word)
	set @name = @name + ' '
	
	-- de-capitalize every word in the given phrase
	while CharIndex(' ', @name) > 0
	begin
		-- find the first word and get it, store the remainder
		SET @pos = CharIndex(' ', @name)
		SET @s = Left(@name, @pos)
		SET @name = case when @pos < Len(@name) then Right(@name, Len(@name) - @pos + 1)
					else '' end
		
		-- add the decapitalized word to the result string:
		-- First letter as given (first two letters if the first letter is a parenthesis), the rest lowered
		SET @res = @res + Left(@s, 1) + 
					case when Left(@s, 1) = '(' then SubString(@s, 2, 1) + Lower(Right(@s, Len(@s)-1))
						else Lower(Right(@s, Len(@s))) end
	end
	
	return @res
END
go
