if object_id ('raw_occurrence') is not null drop table raw_occurrence
--if object_id ('tmp_raw_occurrence') is not null drop table tmp_raw_occurrence

create table raw_occurrence (
	bioRecordKey			int NOT NULL,
	--gbifID				bigint NOT NULL,
	datasetID				varchar(50),
	institution_code		varchar(512),
	collection_code			varchar(512),
	bioRecordId				varchar(512),
	recordBasis				Char(1) default NULL,
	bioRecordTaxonKey		int,
	bioRecordCountryCode	char(2),
	geoLatitude				float,
	geoLongitude			float,
	gatheringYear			smallint,
	gatheringDate			date,
	collectorNumber			varchar(255),
	fieldNumber				varchar(255),
	collector				nvarchar(450),
	localityText			nvarchar(2048),
	occurrenceID			varchar(512),
	recordedByID			varchar(2048)
	)

bulk insert raw_occurrence from 'f:/tmp/occurrence.txt' --'$(raw_occurrence_file)' --
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar', maxerrors=1000000)

/*alter table raw_occurrence
	add bioRecordKey int identity(-2147483647, 1)*/

/*select identity(int) as bioRecordKey, 
	gbifID, datasetID, bioRecordId, occurrenceID,
	min(recordBasis) as recordBasis, min(bioRecordTaxonKey) as bioRecordTaxonKey, min(bioRecordCountryCode) as bioRecordCountryCode,
	min(institution_code) as institution_code, min(collection_code) as collection_code,
	min(IsNull(geoLongitude, 0)) as geoLongitude, min(IsNull(geoLatitude, 0)) as geoLatitude,
	min(gatheringYear) as gatheringYear, min(gatheringDate) as gatheringDate, 
	min(collectorNumber) as collectorNumber, min(fieldNumber) as fieldNumber, 
	min(collector) as collector, min(localityText) as localityText
	--min(recordedByID) as recordedByID
into raw_occurrence
from tmp_raw_occurrence
group by gbifID, datasetID, bioRecordId, occurrenceID*/

-- List of personIDs found in the index
if object_id ('raw_person') is not null drop table raw_person
if object_id ('person') is not null drop table person

create table raw_person(
	personID varchar(512) NOT NULL
)

bulk insert raw_person from 'f:/tmp/person.txt'
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar', maxerrors=1000)

select distinct personID into person  from raw_person
alter table person add primary key(personID)

/*select gbifID, count(*) as cnt
into dup
from old_raw_occurrence
group by gbifID
having count(*) > 1*/

if object_id ('imageRecord') is not null drop table imageRecord
create table imageRecord (
	imageRecordBioRecordKey	 int,
    --gbifID				 bigint,
    imageRecordURL           varchar(1024),
    imageRecordComment       varchar(4096)
)

bulk insert imageRecord from  '$(image_record_file)' --'f:\tmp\multimedia.txt'
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar', maxerrors=1000000)

/*alter table imageRecord
	add imageRecordBioRecordKey int*/

-- update bioRecordKey in imageRecord to newly assigned values
/*update imageRecord
set imageRecordBioRecordKey=bioRecordKey
from imageRecord
join raw_occurrence tmp on imageRecord.gbifID=tmp.gbifID*/

alter table imageRecord	add imageRecordKey int identity

create clustered index irBioRecordKey on imageRecord(imageRecordBioRecordKey)

-- institutionCode/collCode
if object_id ('institution_code') is not null drop table institution_code
if object_id ('collection_code') is not null drop table collection_code

CREATE TABLE institution_code (
	institution_code	varchar(512) primary key,
	id					int identity)
insert into institution_code
select distinct institution_code from raw_occurrence 
where institution_code is not null

CREATE TABLE collection_code (
	collection_code		varchar(512) primary key,
	id					int identity)
insert into collection_code
select distinct collection_code from raw_occurrence
where collection_code is not null

-- insert fake inst/coll code for empty values
SET IDENTITY_INSERT institution_code ON
insert into institution_code (institution_code, id) values ('(None)', -1)
SET IDENTITY_INSERT institution_code OFF

SET IDENTITY_INSERT collection_code ON
insert into collection_code (collection_code, id) values ('(None)', -1)
SET IDENTITY_INSERT collection_code OFF
