/*** Put slicing in here ***************************************************************************************************************/



/*** End of slicing ********************************************************************************************************************/



/*** Merge *****************************************************************************************************************************/
-- Create non-fulltext indexes for bioRecord table
alter table bioRecord_base add primary key (bioRecordKey);
alter table bioRecord_ext add constraint brKey primary key (brKey);

create index brInstCode on bioRecord_base(institution_code_id);
create index brCollCode on bioRecord_base(collection_code_id);
create index brTaxonKey on bioRecord_base(bioRecordTaxonKey) include (bioRecordCountryCode, recordBasis);
create index brResourceKey on bioRecord_base(bioRecordResourceKey);
create index brCountryCode on bioRecord_base(bioRecordCountryCode) include(bioRecordTaxonKey);
create index brRecordBasis on bioRecord_base(recordBasis);
create index brGatheringYear on bioRecord_base(gatheringYear);
create index brRecordId on bioRecord_ext (bioRecordId);
create index brGuid on bioRecord_ext(guid);
create index brFieldNumber on bioRecord_ext(fieldNumber);
create index brAccessionNumber on bioRecord_ext(accessionNumber);
create index brCollectorNumber on bioRecord_ext(collectorNumber);
create index brCollector on bioRecord_ext(collector);

-- create temporary tables that contain lists of all dependent objects that need to be merged
select distinct bioRecordTaxonKey into #tmpTaxa from bioRecord_base;
select distinct bioRecordResourceKey as resKey into #tmpResources from bioRecord_base;
select distinct bioRecordCountryCode as couCode into #tmpCountries from bioRecord_base;
select distinct institution_code_id into #instCodes from bioRecord_base;
select distinct collection_code_id  into #collCodes from bioRecord_base;

-- create dependent tables
select taxonKey, taxonName, taxonAuthor, genus, family, unitCount
into taxon
from syn_raw.dbo.taxon
join #tmpTaxa on taxonKey = bioRecordTaxonKey;

select higherTaxon.*
into higherTaxon
from syn_raw.dbo.higherTaxon
join #tmpTaxa on taxonKey = bioRecordTaxonKey;

select vernacularName.*
into vernacularName
from syn_raw.dbo.vernacularName
join #tmpTaxa on vernacularTaxonKey = bioRecordTaxonKey;

select resource.*
into resource
from syn_raw.dbo.resource
join #tmpResources on resourceKey = resKey;

select institution_code.*
into institution_code
from syn_raw.dbo.institution_code
join #instCodes on id = institution_code_id;

select collection_code.*
into collection_code
from syn_raw.dbo.collection_code
join #collCodes on id = collection_code_id;

select isoCountryCode, syn_raw.dbo.fn_lowerCountryName(countryName) as countryName
into isoCountry
from syn_raw.dbo.isoCountry
join #tmpCountries on isoCountryCode = couCode;

select imageRecord.*
into imageRecord
from syn_raw.dbo.imageRecord
join bioRecord_base on imageRecordBioRecordKey = bioRecordKey;

-- create indexes for dependent tables
alter table taxon add primary key(taxonKey);
create index tTaxonName on taxon(taxonName);
create index tGenus on taxon(genus) include(taxonName);
create index tFamily on taxon(family) include(taxonName);

create clustered index htTaxonKey on higherTaxon(taxonKey);
create index htName on higherTaxon(higherTaxonName);

create clustered index vTaxonKey on vernacularName(vernacularTaxonKey);
create index vName on vernacularName(vernacularName);
create index vLanguage on vernacularName(iso_language_code);

create clustered index irBioRecordKey on imageRecord(imageRecordBioRecordKey);
create index irKey on imageRecord(imageRecordKey);

alter table isoCountry add primary key(isoCountryCode);
create index cName on isoCountry(countryName);

alter table resource add primary key(resourceKey);
create index rAPkey on resource(AccessPointKey);

alter table institution_code add primary key(id);
create index icName on institution_code(institution_code);

alter table collection_code add primary key(id);
create index ccName on collection_code(collection_code);

drop table #tmpTaxa, #tmpResources, #tmpCountries, #instCodes, #collCodes;
go

-- Last, fulltext indexes
/*CREATE FULLTEXT CATALOG fulltext WITH ACCENT_SENSITIVITY = OFF AS DEFAULT;*/
CREATE FULLTEXT INDEX ON bioRecord_ext(localityText language 0x0, collector language 0x0)
	KEY INDEX brKey
	WITH CHANGE_TRACKING = OFF, NO POPULATION;
ALTER FULLTEXT INDEX ON bioRecord_ext
	START FULL POPULATION;
	
/*** Suggest ***************************************************************************************************************************/
-- First, count the taxa and update the counts in table "taxon"
select bioRecordTaxonKey as tKey, count(*) as uCount
into #tmpTaxa
from bioRecord_base
group by bioRecordTaxonKey;

update taxon
set unitCount = IsNull(uCount, 0)
from taxon
left join #tmpTaxa on tKey = taxonKey;

-- then, create all the suggest tables
select taxonName as Name, sum(unitCount) as unitCount
into suggestTaxon
from taxon
group by taxonName;

select genus as Name, sum(unitCount) as unitCount
into suggestGenus
from taxon
group by genus;

select family as Name, sum(unitCount) as unitCount
into suggestFamily
from taxon
group by family;

select higherTaxonName as Name, sum(unitCount) as unitCount
into suggestHigherTaxon
from higherTaxon
join taxon on taxon.taxonKey = higherTaxon.taxonKey
group by higherTaxonName;

select vernacularName as Name, sum(unitCount) as unitCount
into suggestVernacular
from vernacularName
join taxon on vernacularTaxonKey = taxonKey
group by vernacularName;

select countryName as Name, count(*) as unitCount
into suggestCountry
from bioRecord_base
join isoCountry on bioRecordCountryCode = isoCountryCode
group by countryName;

select institution_code as Name, count(*) as unitCount
into suggestInstCode
from bioRecord_base
join institution_code on id = institution_code_id
group by institution_code;

select collection_code as Name, count(*) as unitCount
into suggestCollCode
from bioRecord_base
join collection_code on id = collection_code_id
group by collection_code;

-- create indexes
create clustered index idxInstCode on suggestInstCode(Name);
create clustered index idxCollCode on suggestCollCode(Name);
create clustered index idxCountry on suggestCountry(Name);
create clustered index idxVernacular on suggestVernacular(Name);
create clustered index idxHigherTaxon on suggestHigherTaxon(Name);
create clustered index idxTaxon on suggestTaxon(Name);
create clustered index idxFamily on suggestFamily(Name);
create clustered index idxGenus on suggestGenus(Name);

drop table #tmpTaxa;

/*** "Finish" script *******************************************************************************************************************/
-- View for base table bioRecord
if object_id ('bioRecord') is not null drop view bioRecord
go

create view bioRecord as
select bioRecordKey, bioRecordTaxonKey, bioRecordResourceKey, bioRecordCountryCode, gatheringYear, recordBasis,
	bioRecordId, geoLatitude, geoLongitude, gatheringDate, collector, localityText, guid, fieldNumber, collectorNumber, accessionNumber,
	institution_code, collection_code
from bioRecord_base
left join bioRecord_ext on brKey = bioRecordKey
left join institution_code on institution_code_id = institution_code.id
left join collection_code on collection_code_id = collection_code.id
go

-- View for specimen of the day on gbif.de
if object_id ('unitoftheday_html') is not null drop view unitoftheday_html
go

create view unitoftheday_html as
select top 1 
   '<a href="http://search.biocase.de/botany/" target="_blank"><img src="http://search.biocase.de/botany/static/images/sotd/' + REPLACE(dbo.specimenOfTheDay.which_day, '-', '_') + '.jpg"></a>' AS imageHTML, which_day
from specimenOfTheDay
join imageRecord on imageRecord.imageRecordKey = specimenOfTheDay.imagerecordkey
order by specimenOfTheDay.which_day desc
go

-- View with all data except repeatable element (higher Taxa, vernacular names, images)
if object_id ('bioRecord_full') is not null drop view bioRecord_full
go

create view bioRecord_full as 
select
	bioRecordKey,
	bioRecordId, institution_code, collection_code,
	gatheringYear, gatheringDate, collector,
	recordBasis,
	bioRecordCountryCode, isoCountryCode, countryName, geoLatitude, geoLongitude, localityText,
	guid, fieldNumber, collectorNumber, accessionNumber,
	taxonKey, taxonName, taxonAuthor, genus, family,
	AccessPointKey, AccessPointUUID, bindingURL,
	resourceKey, resourceID, resourceName, resourceEmail,
	provName, provIcon, provAddress, provEmail,
	content_schema, protocol
from bioRecord
left join taxon on taxonKey = bioRecordTaxonKey
left join isoCountry on isoCountryCode = bioRecordCountryCode
left join resource on resourceKey = bioRecordResourceKey;
go

-- Function to retrieve all image URLs for a given bioRecord
if object_id ('fn_getURLs') is not null drop function fn_getURLs
go

CREATE FUNCTION fn_getURLs(@brKey INT) RETURNS varchar(8000) AS
BEGIN
	DECLARE @list varchar(8000)

	select @list = COALESCE(@list + ',,', '') + imageRecordURL 
	from imageRecord
	where imageRecordBioRecordKey = @brKey
	
	RETURN @list
END
go

grant execute on fn_getURLs to webuser
go

-- Function for the specimen of the day
if object_id ('sp_sotd') is not null drop procedure sp_sotd
go

CREATE PROCEDURE sp_sotd AS
BEGIN
	select top 1000 imageRecordKey, imageRecordURL 
	from resource
	join bioRecord   on bioRecordResourceKey    = resourceKey
	join imageRecord on imageRecordBioRecordKey = bioRecordKey
	where protocol <> 'http'
	order by NEWID()
END;
GO

grant execute on sp_sotd to sotd;


-- last, compute statistics
update statistics institution_code with fullscan, norecompute;
update statistics collection_code with fullscan, norecompute;
update statistics vernacularName with fullscan, norecompute;
update statistics taxon with fullscan, norecompute;
update statistics higherTaxon with fullscan, norecompute;
update statistics isoCountry with fullscan, norecompute;
update statistics resource with fullscan, norecompute;
update statistics imageRecord with fullscan, norecompute;
update statistics bioRecord_base with fullscan, norecompute;
update statistics bioRecord_ext with fullscan, norecompute;
update statistics suggestInstCode with fullscan, norecompute;
update statistics suggestCollCode with fullscan, norecompute;
update statistics suggestCountry with fullscan, norecompute;
update statistics suggestVernacular with fullscan, norecompute;
update statistics suggestHigherTaxon with fullscan, norecompute;
update statistics suggestTaxon with fullscan, norecompute;
update statistics suggestFamily with fullscan, norecompute;
update statistics suggestGenus with fullscan, norecompute;