CREATE TABLE websites (
  id INTEGER PRIMARY KEY,
  domain TEXT UNIQUE,
  last_collected TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  url TEXT UNIQUE,
  last_updated_by_version TEXT,
  content TEXT,
  content_tsv TSVECTOR
);

CREATE INDEX "posts_fti_idx" ON posts USING GIN(content_tsv);
CREATE TRIGGER "posts_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
  ON posts FOR EACH ROW 
  EXECUTE PROCEDURE tsvector_update_trigger(posts_fti, content_tsv, 'pg_catalog.english', content);

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  url TEXT UNIQUE
);

CREATE TABLE posts_keywords (
  post_id INTEGER,
  keyword TEXT,
  
  PRIMARY KEY(post_id, keyword),
  CONSTRAINT fk_post FOREIGN KEY(post_id) REFERENCES posts(id)
  -- CONSTRAINT fk_keyword FOREIGN KEY(keyword) REFERENCES keywords(keyword)
);

CREATE INDEX "posts_keywords_idx" ON posts_keywords(keyword);