if object_id ('raw_taxon') is not null drop table raw_taxon
if object_id ('raw_vernacularName') is not null drop table raw_vernacularName
if object_id ('vernacularName') is not null drop table vernacularName
if object_id ('taxon') is not null drop table taxon
if object_id ('taxonAtomised') is not null drop table taxonAtomised
if object_id ('higherTaxon') is not null drop table higherTaxon

-- raw_taxon
create table raw_taxon (
    taxonKey				int NOT NULL,
    taxonName				varchar(512),
	--taxonCanonical			varchar(512),
	kingdom					varchar(255),
	phylum					varchar(255),
	class					varchar(255),
	ordo					varchar(255),
	family					varchar(255),
	genus					varchar(255)
)
bulk insert raw_taxon from '$(raw_taxon_file)'
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar', firstrow=2)

-- raw_vernacular
create table raw_vernacularName (
	vernacularTaxonKey int,
	vernacularName nvarchar(400),
	iso_language_code char(2)
)
bulk insert raw_vernacularName from '$(raw_vernacular_file)'
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar', firstrow=2)

-- Function to check whether a unicode string can be expressed in latin1
if object_id ('fn_isLatin1') is not null drop function fn_isLatin1
go

CREATE FUNCTION fn_isLatin1(@s nvarchar(400)) RETURNS BIT AS
BEGIN
	DECLARE @pos int
	SET @pos = 1

	WHILE @pos <= DATALENGTH(@s)
	BEGIN
		IF ASCII(SUBSTRING(@s, @pos, 1)) = 63
			RETURN 0
		SET @pos = @pos + 1
	END

	RETURN 1
END
go

--grant execute on fn_isLatin1 to syn_updater
--go

-- vernacularName:  Use this function to only use latin1-able vernacular names
select distinct vernacularTaxonKey, vernacularName, iso_language_code 
into vernacularName 
from raw_vernacularName
where dbo.fn_isLatin1(vernacularName) = 1

create clustered index vTaxonKey on vernacularName(vernacularTaxonKey)

-- taxon
select taxonKey, taxonName, NULL as taxonAuthor, genus, family, 0 as unitCount
into taxon from raw_taxon

alter table taxon add primary key(taxonKey)

-- taxonAtomised for name cleaning
CREATE TABLE [dbo].[taxonAtomised](
	[originalname] [varchar](255) NOT NULL,
	[canonical] [varchar](255) NULL,
	[author] [varchar](255) NULL,
	[year] [varchar](255) NULL,
	[bracketAuthorship] [varchar](255) NULL,
	[bracketYear] [varchar](255) NULL,
	[genus] [varchar](255) NULL,
	[subgenus] [varchar](255) NULL,
	[species] [varchar](255) NULL,
	[subspecies] [varchar](255) NULL,
	[rank] [varchar](255) NULL,
	[status] [varchar](255) NULL,
	[problem] [int] NULL,
	[canonicalNameComplete] [varchar](255) NULL,
	[debugged] [int] NULL,
	[identificationQualifier] [varchar](45) NULL,
	[nameAddendum] [varchar](45) NULL,
	[qualifierPosition] [int] NULL,
	[cultivarOrBreed] [varchar](255) NULL,
	[fullscientificnamegenerated] [varchar](255) NULL,
	[reg] [varchar](455) NULL
)

grant  DELETE, INSERT, REFERENCES, SELECT, UPDATE on taxonAtomised to webuser
grant  UPDATE on taxon to webuser

-- higherTaxon
select taxonKey, kingdom as higherTaxonName, 'kingdom' as higherTaxonRank
into higherTaxon
from raw_taxon where kingdom is not null
	union all
select taxonKey, phylum, 'phylum' from raw_taxon where phylum is not null
	union all
select taxonKey, class, 'class' from raw_taxon where class is not null
	union all
select taxonKey, ordo, 'order' from raw_taxon where ordo is not null
	union all
select taxonKey, family, 'family' from raw_taxon where family is not null

create clustered index htTaxonKey on higherTaxon(taxonKey)
