/*** Create non-fulltext indexes for bioRecord table ******************************************************************************/
alter table bioRecord add constraint brKey primary key (bioRecordKey)
create index brInstCode on bioRecord(institution_code_id)
create index brCollCode on bioRecord(collection_code_id)
create index brTaxonKey on bioRecord(bioRecordTaxonKey) include (bioRecordCountryCode, recordBasis)
create index brResourceKey on bioRecord(bioRecordResourceKey)
create index brCountryCode on bioRecord(bioRecordCountryCode) include(bioRecordTaxonKey)
create index brRecordBasis on bioRecord(recordBasis)
create index brGatheringYear on bioRecord(gatheringYear)
create index brRecordId on bioRecord(bioRecordId)
create index brCollector on bioRecord(collector)
--create index brGuid on bioRecord(guid)
create index brFieldNumber on bioRecord(fieldNumber)
--create index brAccessionNumber on bioRecord(accessionNumber)
create index brCollectorNumber on bioRecord(collectorNumber)
go

/*** Create dependent tables ******************************************************************************************************/
select distinct bioRecordTaxonKey into #tmpTaxa from bioRecord
select distinct bioRecordResourceKey as resKey into #tmpResources from bioRecord
select distinct bioRecordCountryCode as couCode into #tmpCountries from bioRecord
select distinct institution_code_id into #instCodes from bioRecord
select distinct collection_code_id  into #collCodes from bioRecord

-- create dependent tables
if object_id ('taxon') is not null drop table taxon
if object_id ('higherTaxon') is not null drop table higherTaxon
if object_id ('vernacularName') is not null drop table vernacularName
if object_id ('resource') is not null drop table resource
if object_id ('institution_code') is not null drop table institution_code
if object_id ('collection_code') is not null drop table collection_code
if object_id ('isoCountry') is not null drop table isoCountry
if object_id ('imageRecord') is not null drop table imageRecord

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, countryName
into isoCountry
from syn_raw.dbo.isoCountry
join #tmpCountries on isoCountryCode = couCode

select imageRecordBioRecordKey, imageRecordURL, imageRecordComment, imageRecordKey
into imageRecord
from syn_raw.dbo.imageRecord
join bioRecord 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
	
/*** Suggest tables ***************************************************************************************************************/
if object_id ('suggestTaxon') is not null drop table suggestTaxon
if object_id ('suggestGenus') is not null drop table suggestGenus
if object_id ('suggestFamily') is not null drop table suggestFamily
if object_id ('suggestHigherTaxon') is not null drop table suggestHigherTaxon
if object_id ('suggestVernacular') is not null drop table suggestVernacular
if object_id ('suggestCountry') is not null drop table suggestCountry
if object_id ('suggestInstCode') is not null drop table suggestInstCode
if object_id ('suggestCollCode') is not null drop table suggestCollCode

select bioRecordTaxonKey as tKey, count(*) as uCount
into #tmpTaxa from bioRecord
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
join isoCountry on bioRecordCountryCode = isoCountryCode
group by countryName

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

select collection_code as Name, count(*) as unitCount
into suggestCollCode
from bioRecord
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
go

/*** Statistics *******************************************************************************************************************/
update statistics institution_code with fullscan
update statistics collection_code with fullscan
update statistics vernacularName with fullscan
update statistics taxon with fullscan
update statistics higherTaxon with fullscan
update statistics isoCountry with fullscan
update statistics resource with fullscan
update statistics imageRecord with fullscan
update statistics suggestInstCode with fullscan
update statistics suggestCollCode with fullscan
update statistics suggestCountry with fullscan
update statistics suggestVernacular with fullscan
update statistics suggestHigherTaxon with fullscan
update statistics suggestTaxon with fullscan
update statistics suggestFamily with fullscan
update statistics suggestGenus with fullscan
update statistics bioRecord with fullscan
go

/*** Last: Views, SPs and functions ***********************************************************************************************/

-- 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,
	fieldNumber, collectorNumber,
	taxonKey, taxonName, taxonAuthor, genus, family,
	AccessPointKey, AccessPointUUID, bindingURL,
	resourceKey, resourceID, resourceName, resourceEmail,
	provName, provIcon, provAddress, provEmail,
	content_schema, protocol
from bioRecord
left join institution_code ic on ic.id = institution_code_id
left join collection_code cc on cc.id = collection_code_id
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

/*** Create "done" table and start fulltext indexing ******************************************************************************/
if object_id ('z') is not null drop table z
select GetDate() as finished into z

CREATE FULLTEXT INDEX ON bioRecord(localityText language 0x0, collector language 0x0)
	KEY INDEX brKey
