CREATE TABLE IF NOT EXISTS fundingpaths (
	_dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT 'temp_' || md5(clock_timestamp() :: TEXT) || '_' || md5(random() :: TEXT),
	id                         CHARACTER VARYING(512) PRIMARY KEY,
	path                       TEXT NOT NULL,
	funder                     CHARACTER VARYING(512) REFERENCES organizations (id),
	jurisdiction               CHARACTER VARYING(16),
	description                CHARACTER VARYING(512),
	optional1                  CHARACTER VARYING(255),
	optional2                  CHARACTER VARYING(255)
);

CREATE TABLE IF NOT EXISTS project_fundingpath (
	_dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT 'temp_' || md5(clock_timestamp() :: TEXT) || '_' || md5(random() :: TEXT),
	funding                    CHARACTER VARYING(255) NOT NULL REFERENCES fundingpaths (id),
	project                    CHARACTER VARYING(255) NOT NULL REFERENCES projects (id),
	startdate                  DATE,
	enddate                    DATE,
	semanticclass              CHARACTER VARYING(255) REFERENCES class (code),
	semanticscheme             CHARACTER VARYING(255) REFERENCES scheme (code),
	optional1                  CHARACTER VARYING(255),
	optional2                  CHARACTER VARYING(255),
	PRIMARY KEY (funding, project)
);

UPDATE datasources
SET namespaceprefix = 'wt__________'
WHERE id = 'openaire____::wellcometrust';

delete from project_fundingpath where funding ='ec__________::EC::FP7::UNKNOWN::UNKNOWN';
delete from fundingpaths where id = 'ec__________::EC::FP7::UNKNOWN::UNKNOWN';
