create schema CollectionDB;

set search_path = CollectionDB, pg_catalog;

CREATE OR REPLACE FUNCTION update_Description() RETURNS trigger AS 
$$
DECLARE
	i integer;
BEGIN
	INSERT INTO CollectionDB.Descriptions (id) VALUES (NEW.id);
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION is_in_ows_DataDescription(i integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
        res int;
        mymax int;
BEGIN
	SELECT id from CollectionDB.ows_DataDescription where id=i INTO res ;
	if res is NULL then
	   return false;
	else
	   return true;
	end if;
END;
$$;

create table CollectionDB.Descriptions (
       id serial primary key
);

create table CollectionDB.ows_Metadata (
       id serial primary key,
       title text,
       role text,
       href text
);

create table CollectionDB.DescriptionsMetadataAssignment(
       descriptions_id int references CollectionDB.Descriptions(id),
       metadata_id int references CollectionDB.ows_Metadata(id)
);

create table CollectionDB.ows_Keywords (
    id serial primary key,
    keyword varchar
);

create table CollectionDB.DescriptionsKeywordsAssignment(
       descriptions_id int references CollectionDB.Descriptions(id),
       keywords_id int references CollectionDB.ows_Keywords(id)
);

create table CollectionDB.ows_AdditionalParameters (
    id serial primary key,
    title varchar,
    role varchar,
    href varchar
);

create table CollectionDB.DescriptionsAdditionalParametersAssignment (
       descriptions_id int references CollectionDB.Descriptions(id),
       additional_parameters_id int references CollectionDB.ows_AdditionalParameters(id)
);

--
-- See reference for primitive datatypes
-- https://www.w3.org/TR/xmlschema-2/#built-in-primitive-datatypes
-- 
create table CollectionDB.PrimitiveDataTypes (
       id serial primary key,
       name varchar(255)
);
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('string');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('boolean');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('integer');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('float');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('double');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('duration');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('dateTime');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('time');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('date');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gYearMonth');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gYear');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gMonthDay');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gDay');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gMonth');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('hexBinary');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('base64Binary');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('anyURI');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('QName');
INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('NOTATION');

--
-- List all primitive formats
--
create table CollectionDB.PrimitiveFormats (
       id serial primary key,
       mime_type varchar(255),
       encoding varchar(15),
       schema varchar(255)
);

-- https://tools.ietf.org/html/rfc4180
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/csv','utf-8');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/css','utf-8');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/html','utf-8');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/javascript','utf-8');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/plain','utf-8');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding,schema)
       VALUES ('text/xml','utf-8','http://schema.opengis.net/gml/3.2.1/gml.xsd');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding,schema)
       VALUES ('text/xml','utf-8','http://schema.opengis.net/gml/3.1.0/gml.xsd');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('application/gml+xml','utf-8');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('application/json','utf-8');
-- https://tools.ietf.org/html/rfc3302
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/tiff');
-- https://www.ietf.org/rfc/rfc4047.txt
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/fits');
-- https://tools.ietf.org/html/rfc3745
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/jp2');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/png');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/jpeg');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/gif');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('application/octet-stream');
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('application/vnd.google-earth.kml+xml');
-- https://www.iana.org/assignments/media-types/application/zip
INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('application/zip');
-- https://www.iana.org/assignments/media-types/application/xml
INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('application/xml','utf-8');

create table CollectionDB.ows_Format (
    id serial primary key,
    primitive_format_id int references CollectionDB.PrimitiveFormats(id),
    maximum_megabytes int,
    def boolean,
	use_mapserver bool,
	ms_styles text
);

create table CollectionDB.ows_DataDescription (
    id serial primary key,
    format_id int references CollectionDB.ows_Format(id)
);

create table CollectionDB.PrimitiveUom (
	id serial primary key,
	uom varchar
);
-- source : Open Geospatial Consortium - URNs of definitions in ogc namespace
insert into CollectionDB.PrimitiveUom (uom) values ('degree');
insert into CollectionDB.PrimitiveUom (uom) values ('radian');
insert into CollectionDB.PrimitiveUom (uom) values ('metre');
insert into CollectionDB.PrimitiveUom (uom) values ('unity');

create table CollectionDB.LiteralDataDomain (
    possible_literal_values varchar,
    default_value varchar,
    data_type_id int references CollectionDB.PrimitiveDataTypes(id),
    uom int references CollectionDB.PrimitiveUom(id),
    def boolean
) inherits (CollectionDB.ows_DataDescription);
alter table CollectionDB.LiteralDataDomain add constraint literal_data_domain_id unique (id);

create table CollectionDB.BoundingBoxData (
    epsg int
) inherits (CollectionDB.ows_DataDescription);
alter table CollectionDB.BoundingBoxData add constraint bounding_box_data_id unique (id);

create table CollectionDB.ComplexData (
) inherits (CollectionDB.ows_DataDescription);
alter table CollectionDB.ComplexData add constraint complex_data_id unique (id);

create table CollectionDB.AllowedValues (
    id serial primary key,
    allowed_value varchar(255)
);

create table CollectionDB.AllowedValuesAssignment (
    id serial primary key,
    literal_data_domain_id int references CollectionDB.LiteralDataDomain (id),
    allowed_value_id int references CollectionDB.AllowedValues (id)
);

create table CollectionDB.ows_AdditionalParameter (
    id serial primary key,
    key varchar,
    value varchar,
    additional_parameters_id int references CollectionDB.ows_AdditionalParameters(id)
);

create table CollectionDB.ows_Input (
    id int primary key default nextval('collectiondb.descriptions_id_seq'::regclass),
    title text,
    abstract text,
    identifier varchar(255),
    min_occurs int,
    max_occurs int
); -- inherits (CollectionDB.Descriptions);
alter table CollectionDB.ows_Input add constraint codb_input_id unique (id);
CREATE TRIGGER ows_Input_proc AFTER INSERT ON CollectionDB.ows_Input FOR EACH ROW EXECUTE PROCEDURE update_Description();

create table CollectionDB.ows_Output (
    id int primary key default nextval('collectiondb.descriptions_id_seq'::regclass),
    title text,
    abstract text,
    identifier varchar(255)
); --inherits (CollectionDB.Descriptions);
alter table CollectionDB.ows_Output add constraint codb_output_id unique (id);
CREATE TRIGGER ows_Output_proc AFTER INSERT ON CollectionDB.ows_Output FOR EACH ROW EXECUTE PROCEDURE update_Description();

create table CollectionDB.zoo_PrivateMetadata (
    id serial primary key,
    identifier varchar,
    metadata_date timestamp
);

create table CollectionDB.ows_Process (
    id int primary key default nextval('collectiondb.descriptions_id_seq'::regclass),
    title text,
    abstract text,
    identifier varchar(255),
    availability boolean,
    process_description_xml text,
    private_metadata_id int references CollectionDB.zoo_PrivateMetadata(id)
); -- inherits (CollectionDB.Descriptions);
alter table CollectionDB.ows_Process add constraint codb_process_id unique (id);
alter table CollectionDB.ows_Process add constraint codb_process_identifier unique (identifier);
CREATE TRIGGER ows_Process_proc AFTER INSERT ON CollectionDB.ows_Process FOR EACH ROW EXECUTE PROCEDURE update_Description();

create table CollectionDB.InputInputAssignment (
    id serial primary key,
    parent_input int references CollectionDB.ows_Input(id),
    child_input int references CollectionDB.ows_Input(id)
);

create table CollectionDB.InputDataDescriptionAssignment (
    id serial primary key,
    input_id int references CollectionDB.ows_Input(id),
    data_description_id int check (CollectionDB.is_in_ows_DataDescription(data_description_id))
);

create table CollectionDB.OutputOutputAssignment (
    id serial primary key,
    parent_output int references CollectionDB.ows_Output(id),
    child_output int references CollectionDB.ows_Output(id)
);

create table CollectionDB.OutputDataDescriptionAssignment (
    id serial primary key,
    output_id int references CollectionDB.ows_Output(id),
    data_description_id int check (CollectionDB.is_in_ows_DataDescription(data_description_id))
);

create table CollectionDB.zoo_ServiceTypes (
	id serial primary key,
	service_type varchar
);
insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('HPC');
insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('C');
insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('Java');
insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('Mono');
insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('JS');
insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('PHP');
insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('Python');

insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('OTB');

create table CollectionDB.zoo_DeploymentMetadata (
    id serial primary key,
    executable_name varchar,
    configuration_identifier varchar,
	service_type_id int references CollectionDB.zoo_ServiceTypes(id)
);

create table CollectionDB.zoo_PrivateProcessInfo (
    id serial primary key
);

create table CollectionDB.PrivateMetadataDeploymentMetadataAssignment (
    id serial primary key,
    private_metadata_id int references CollectionDB.zoo_PrivateMetadata(id),
    deployment_metadata_id int references CollectionDB.zoo_DeploymentMetadata(id)
);

create table CollectionDB.PrivateMetadataPrivateProcessInfoAssignment (
    id serial primary key,
    private_metadata_id int references CollectionDB.zoo_PrivateMetadata(id),
    private_process_info_id int references CollectionDB.zoo_PrivateProcessInfo(id)
);

create table CollectionDB.ProcessInputAssignment (
    id serial primary key,
    process_id int references CollectionDB.ows_Process(id),
    input_id int references CollectionDB.ows_Input(id),
    index int
);

create table CollectionDB.ProcessOutputAssignment (
    id serial primary key,
    process_id int references CollectionDB.ows_Process(id),
    output_id int references CollectionDB.ows_Output(id),
    index int
);

CREATE OR REPLACE VIEW public.ows_process AS
       (SELECT
	id,
	identifier,
	title,
	abstract,
	(SELECT service_type FROM CollectionDB.zoo_ServiceTypes WHERE id = (SELECT service_type_id FROM CollectionDB.zoo_DeploymentMetadata WHERE id = (SELECT deployment_metadata_id FROM CollectionDB.PrivateMetadataDeploymentmetadataAssignment WHERE private_metadata_id=(SELECT id FROM CollectionDB.zoo_PrivateMetadata WHERE id = CollectionDB.ows_Process.private_metadata_id)))) as service_type,
	(SELECT executable_name  as service_provider FROM CollectionDB.zoo_DeploymentMetadata WHERE id = (SELECT deployment_metadata_id FROM CollectionDB.PrivateMetadataDeploymentmetadataAssignment WHERE private_metadata_id=(SELECT id FROM CollectionDB.zoo_PrivateMetadata WHERE id = CollectionDB.ows_Process.private_metadata_id))) as service_provider,
	availability
	FROM CollectionDB.ows_Process
	WHERE
	 availability
	);