-- Vocabularies

CREATE TABLE vocabularies (
	id          text PRIMARY KEY,
	name        text NOT NULL,
	description text
);

CREATE TABLE vocabulary_terms (
	vocabulary  text NOT NULL REFERENCES vocabularies(id) ON UPDATE CASCADE ON DELETE CASCADE,
	code        text NOT NULL,
	name        text NOT NULL,
	encoding    text DEFAULT 'OPENAIRE',
	synonyms    jsonb,
	PRIMARY KEY (vocabulary, code)
);

CREATE INDEX ON vocabulary_terms (vocabulary);

CREATE TABLE protocols (
	id text PRIMARY KEY
);
INSERT INTO protocols(id) VALUES ('oai'),('oai_sets'),('http'),('file'),('classpath'),('fileCSV'),('httpCSV'),('ftp'),('sftp'),('filesystem'),('files_from_metadata'),('files_from_mdstore'),('mongoDump'),('targz'),('zip'),('fileGzip'),('httpList'),('remoteMdstore');

CREATE TABLE protocol_params (
	protocol         text    NOT NULL REFERENCES protocols(id) ON UPDATE CASCADE ON DELETE CASCADE,
	param_name       text    NOT NULL,
	param_label      text    NOT NULL,
	param_type       text    NOT NULL DEFAULT 'TEXT',
	optional         boolean NOT NULL default false,
	has_sel_function boolean NOT NULL default false,
	PRIMARY KEY (protocol, param_name)
);
INSERT INTO protocol_params(protocol, param_name, param_label, param_type, optional, has_sel_function) VALUES
('oai', 'set', 'OAI set', 'LIST', true, true),
('oai', 'format', 'OAI Metadata Format', 'TEXT', false, false),
('http', 'splitOnElement', 'splitOnElement', 'TEXT', false, false),
('file', 'splitOnElement', 'splitOnElement', 'TEXT', false, false),
('classpath', 'splitOnElement', 'splitOnElement', 'TEXT', false, false),
('fileCSV', 'header', 'header', 'TEXT', false, false),
('fileCSV', 'separator', 'separator', 'TEXT', false, false),
('fileCSV', 'identifier', 'identifier', 'TEXT', false, false),
('fileCSV', 'quote', 'quote', 'TEXT', false, false),
('httpCSV', 'separator', 'separator', 'TEXT', false, false),
('httpCSV', 'identifier', 'identifier', 'TEXT', false, false),
('httpCSV', 'quote', 'quote', 'TEXT', false, false),
('ftp', 'username', 'username', 'TEXT', false, false),
('ftp', 'password', 'password', 'TEXT', false, false),
('ftp', 'recursive', 'recursive', 'BOOLEAN', false, false),
('ftp', 'extensions', 'extensions', 'LIST', false, false),
('sftp', 'username', 'username', 'TEXT', false, false),
('sftp', 'password', 'password', 'TEXT', true, false),
('sftp', 'authMethod', 'authMethod', 'TEXT', true, false),
('sftp', 'privateKeyPath', 'privateKeyPath', 'TEXT', true, false),
('sftp', 'port', 'port', 'TEXT', true, false),
('sftp', 'recursive', 'recursive', 'BOOLEAN', false, false),
('sftp', 'extensions', 'extensions', 'LIST', false, false);

-- Contexts

CREATE TABLE contexts (
	id     text PRIMARY KEY,
	label  text NOT NULL,
	type   text NOT NULL,
	params jsonb
);

CREATE TABLE context_categories (
	id     text    NOT NULL PRIMARY KEY,
	parent text    NOT NULL REFERENCES contexts(id) ON UPDATE CASCADE ON DELETE CASCADE,
	label  text    NOT NULL,
	claim  boolean NOT NULL,
	params jsonb
);

CREATE TABLE context_cat_concepts_lvl_0 (
	id     text    NOT NULL PRIMARY KEY,
	parent text    NOT NULL REFERENCES context_categories(id) ON UPDATE CASCADE ON DELETE CASCADE,
	label  text    NOT NULL,
	claim  boolean NOT NULL,
	params jsonb
);

CREATE TABLE context_cat_concepts_lvl_1 (
	id     text    NOT NULL PRIMARY KEY,
	parent text    NOT NULL REFERENCES context_cat_concepts_lvl_0(id) ON UPDATE CASCADE ON DELETE CASCADE,
	label  text    NOT NULL,
	claim  boolean NOT NULL,
	params jsonb
);

CREATE TABLE context_cat_concepts_lvl_2 (
	id     text    NOT NULL PRIMARY KEY,
	parent text    NOT NULL REFERENCES context_cat_concepts_lvl_1(id) ON UPDATE CASCADE ON DELETE CASCADE,
	label  text    NOT NULL,
	claim  boolean NOT NULL,
	params jsonb
);

CREATE INDEX ON context_categories         (parent);
CREATE INDEX ON context_cat_concepts_lvl_0 (parent);
CREATE INDEX ON context_cat_concepts_lvl_1 (parent);
CREATE INDEX ON context_cat_concepts_lvl_2 (parent);

-- WF History

CREATE TABLE wf_history (
	process_id  text PRIMARY KEY,
	name        text NOT NULL,
	family      text NOT NULL,
	status      text NOT NULL,
	start_date  timestamp NOT NULL,
	end_date    timestamp NOT NULL,
	ds_id       text,
	ds_name     text,
	ds_api      text,
	details     jsonb
);

-- Other Resources

CREATE TABLE resource_types(
	id           text PRIMARY KEY, 
	name         text NOT NULL,
	content_type text NOT NULL DEFAULT 'text/plain'
);

INSERT INTO resource_types(id, name, content_type) VALUES 
	('transformation_rule_xslt', 'Transformation Rules (xslt)', 'application/xml'), 
	('transformation_rule_legacy', 'Transformation Rules (legacy)', 'text/plain'), 
	('cleaning_rule', 'Cleaning Rules', 'application/xml'),
	('hadoop_job_configuration', 'Hadoop Job Configurations', 'application/xml')
	('dedup_configuration', 'Dedup Configurations', 'application/json');

CREATE TABLE resources (
	id                text PRIMARY KEY,
	name              text NOT NULL,
	description       text,
	content           text NOT NULL DEFAULT '',
	type              text NOT NULL REFERENCES resource_types(id),
	creation_date     timestamp NOT NULL DEFAULT now(),
    modification_date timestamp NOT NULL DEFAULT now()
);

CREATE VIEW resource_types_view AS (
	SELECT 
		t.id           AS id, 
		t.name         AS name, 
		t.content_type AS content_type,
		count(r.id)    AS count,
		true           AS simple
	FROM resource_types t
	LEFT OUTER JOIN resources r ON (r.type = t.id)
	GROUP BY t.id, t.name
	ORDER BY t.name
) UNION ALL (
	SELECT 
		'vocabulary'   AS id,
		'Vocabularies' AS name,
		'text/plain'   AS content_type,
		count(*)       AS count,
		false          AS simple
	FROM vocabularies
) UNION ALL (
	SELECT 
		'context'    AS id,
		'Contexts'   AS name,
		'text/plain' AS content_type,
		count(*)     AS count,
		false        AS simple
	FROM contexts
) UNION ALL (
	SELECT 
		'protocol'    AS id,
		'Protocols'   AS name,
		'text/plain' AS content_type,
		count(*)     AS count,
		false        AS simple
	FROM protocols
);
