-- 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;

-- 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;