-- createdb dnet_dedup
-- manage db with dbService

CREATE TABLE entities (
    id character varying(255) NOT NULL,
    entitytype character varying(255) NOT NULL,
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),
    PRIMARY KEY(id)
);

CREATE TABLE groups (
    id character varying(255) NOT NULL,
    entitytype character varying(255) NOT NULL,
    actionsetid character varying(255) NOT NULL,
    date timestamp without time zone default now(),
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),
    PRIMARY KEY(id)
);

CREATE TABLE similarity_groups (
    groupid character varying(255) NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    objidentifier character varying(255) NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),
    PRIMARY KEY(groupid, objidentifier)
);

CREATE TABLE dissimilarities (
    id1 character varying(255) NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    id2 character varying(255) NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    actionsetid character varying(255) NOT NULL,
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),
    PRIMARY KEY(id1, id2, actionsetid)
);