-- Drop tables
if object_id ('raw_dataset') is not null drop table raw_dataset
if object_id ('tmp_raw_dataset') is not null drop table tmp_raw_dataset
if object_id ('raw_organization') is not null drop table raw_organization
if object_id ('resource') is not null drop table resource

-- Dataset info from web service
create table tmp_raw_dataset (
	resourceKey			int NOT NULL,
	dataset_uuid		varchar(50),
	owningorg_uuid		varchar(50),
    resourceName		varchar(512),
    resourceEmail		varchar(255),
    content_schema		varchar(255),
	bindingURL			varchar(255),
	code				varchar(255),
	protocol			varchar(10)
)
bulk insert tmp_raw_dataset from '$(raw_dataset_file)'
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar')

select min(resourceKey) as resourceKey,	dataset_uuid, min(owningorg_uuid) as owningorg_uuid, min(resourceName) as resourceName,
    min(resourceEmail) as resourceEmail, min(content_schema) as content_schema, min(bindingURL) as bindingURL,
	min(code) as code, min(protocol) as protocol
into raw_dataset
from tmp_raw_dataset
group by dataset_uuid

-- Organisation info from web service
create table raw_organization (
	org_uuid			varchar(50),
	endorser_uuid		varchar(50),
  	provName			varchar(512),
    provIcon			varchar(1024),
    provAddress			varchar(255),
    provEmail			varchar(255)
)
bulk insert raw_organization from '$(raw_organization_file)'
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar')

-- resource
select resourceKey, dataset_uuid, endorser_uuid, owningorg_uuid, resourceName, resourceEmail, 
	case when content_schema = 'http://digir.net/schema/conceptual/darwin/2003/1.0/darwin2.xsd' 
		 then 'http://digir.sourceforge.net/schema/conceptual/darwin/2003/1.0/darwin2.xsd'
		 else content_schema
	end as content_schema, protocol,
	case when protocol = 'digir' then code + '@' + bindingURL
	     else bindingURL
	end as bindingURL,
	provName, provIcon, provAddress, provEmail,
	NULL as AccessPointKey, NULL as AccessPointUUID, NULL as resourceId
into resource
from raw_dataset
left join raw_organization on owningorg_uuid = org_uuid

alter table resource 
	alter column resourceKey int not NULL;

alter table resource add primary key(resourceKey)

