-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
BEGIN;
CREATE SCHEMA IF NOT EXISTS marketminds;

CREATE TABLE IF NOT EXISTS marketminds.newschannel
(
    id serial NOT NULL,
    rssurl text NOT NULL,
    imageurl text,
    title text NOT NULL,
    link text,
    description text,
    language text,
    copyright text,
    lastbuilddate date,
    updatedat timestamp with time zone NOT NULL,
    createdat timestamp with time zone NOT NULL,
    PRIMARY KEY (id)
);

--Newsitem from channel
CREATE TABLE IF NOT EXISTS marketminds.newsitem
(
    id bigserial NOT NULL,
    title text NOT NULL,
    description text,
    thumbnailurl text,
    link text,
    pubdate timestamp with time zone,
    category text,
    guid text,
    enclosureurl text,
    mediacopyright text,
    mediatitle text,
    version text,
    createdat timestamp with time zone NOT NULL,
    "newschannelId" integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS marketminds.sentimentresult
(
    id bigserial NOT NULL,
    titlepositive double precision NOT NULL,
    titleneutral double precision NOT NULL,
    titlenegative double precision NOT NULL,
    descriptionpostive double precision,
    descriptionneutral double precision,
    descriptionnegative double precision,
    createdat timestamp with time zone NOT NULL,
    "newsitemId" bigint NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS marketminds.namedentity
(
    name text NOT NULL,
    type text,
    createdat timestamp with time zone NOT NULL,
    PRIMARY KEY (name)
);

CREATE TABLE IF NOT EXISTS marketminds.nerresult
(
    id bigserial NOT NULL,
    createdat timestamp with time zone NOT NULL,
    "newsitemId" bigint NOT NULL,
    "namedentityName" text NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS marketminds.newsitem
    ADD FOREIGN KEY ("newschannelId")
    REFERENCES marketminds.newschannel (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS marketminds.sentimentresult
    ADD FOREIGN KEY ("newsitemId")
    REFERENCES marketminds.newsitem (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS marketminds.nerresult
    ADD FOREIGN KEY ("newsitemId")
    REFERENCES marketminds.newsitem (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS marketminds.nerresult
    ADD FOREIGN KEY ("namedentityName")
    REFERENCES marketminds.namedentity (name) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;