-- Husk at lave databasen "fakenews_scrape" først og køre jupyter notebooken før du kører dette.
-- Åben postgres og sørg for at connecte til fakenewscorpus databasen først. Kør så denne query.
create extension if not exists pg_trgm;
create extension if not exists dblink;

drop VIEW IF EXISTS scraped cascade;
SET pg_trgm.similarity_threshold = 0.1;
CREATE VIEW scraped AS
    select * FROM dblink('dbname=fakenews_scrape user=postgres password=postgres','select article_id, title from article') AS our_scraped(article_id integer, title character varying(512));

drop view if exists similarity_table;
create view similarity_table as
select
article.article_id as "Corpus article_id",
article.title as "Corpus title",
scraped.article_id as "Scraped aricle_id",
scraped.title as "Scraped title",
word_similarity(scraped.title, article.title) as sim_word
from article,
where article.title is not null and scraped.title is not null
;

select * from similarity_table
order by sim_word desc
fetch first 10 rows only;


ARTIKEL 1 (The Sea Level Around Florida is Rising Six Times Faster Than Average)

-- find al information om artiklen i FakeNewsCorpus (kør i fakenews_100k database)
select * from article inner join typ on article.type_id = typ.type_id
where article_id = 33425

-- find al information om artiklen i fakenews_scrape (kør i fakenews_scrape)
select * from article
inner join has_types on article.article_id = has_types.article_id
inner join typ on has_types.type_id = typ.type_id
where article.article_id = 34

ARTIKEL 2 (Only A Century Away From An "Uninhabitable Earth?")
-- find al information om artiklen i FakeNewsCorpus (kør i fakenews_100k database)
select * from article inner join typ on article.type_id = typ.type_id
where article_id = 84114

-- find al information om artiklen i fakenews_scrape (kør i fakenews_scrape)
select * from article
inner join has_types on article.article_id = has_types.article_id
inner join typ on has_types.type_id = typ.type_id
where article.article_id = 50