DROP TABLE IF EXISTS posts CASCADE;
DROP SEQUENCE IF EXISTS posts_id_seq;

CREATE SEQUENCE posts_id_seq;

CREATE TABLE posts (
  id BIGINT NOT NULL DEFAULT NEXTVAL('posts_id_seq'::regclass),
  title VARCHAR,
  content VARCHAR,
  category VARCHAR,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

INSERT INTO posts (title, content, category, created_at, updated_at) VALUES
('Title A', '---', 'backend', '2021-03-17', '2021-03-17'),
('Title B', '---', 'devops',  '2021-04-12', '2021-04-12'),
('Title C', '---', 'devops',  '2021-04-26', '2021-04-26'),
('Title D', '---', 'dba',     '2021-05-13', '2021-05-13'),
('Title E', '---', 'backend', '2021-05-14', '2021-05-14'),
('Title F', '---', 'dba',     '2021-06-13', '2021-06-13'),
('Title G', '---', 'backend', '2021-07-22', '2021-07-22'),
('Title H', '---', 'design',  '2021-07-22', '2021-07-22');

DROP TABLE IF EXISTS keywords CASCADE;
DROP SEQUENCE IF EXISTS keywords_id_seq;

CREATE SEQUENCE keywords_id_seq;

CREATE TABLE keywords (
  id BIGINT NOT NULL DEFAULT NEXTVAL('keywords_id_seq'::regclass),
  content VARCHAR,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

INSERT INTO keywords (content) VALUES
('database'),
('sql'),
('rails'),
('ruby'),
('aws'),
('linux'),
('.net'),
('python'),
('csv');

DROP TABLE IF EXISTS post_keywords CASCADE;
DROP SEQUENCE IF EXISTS post_keywords_id_seq;

CREATE SEQUENCE post_keywords_id_seq;

CREATE TABLE post_keywords (
  id BIGINT NOT NULL DEFAULT NEXTVAL('post_keywords_id_seq'::regclass),
  post_id BIGINT NOT NULL,
  keyword_id BIGINT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (post_id) REFERENCES posts (id),
  FOREIGN KEY (keyword_id) REFERENCES keywords (id)
);

INSERT INTO post_keywords (post_id, keyword_id) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 5),
(2, 8),
(3, 5),
(3, 6),
(4, 1),
(4, 2),
(4, 5),
(4, 6),
(5, 1),
(5, 2),
(5, 3),
(5, 4),
(5, 9),
(6, 1),
(7, 1),
(7, 5),
(7, 6),
(7, 7),
(8, 1);

CREATE VIEW similar_posts AS

SELECT
  posts.id AS reference_post_id,

  COALESCE(
    similar_posts_by_category.post_id,
    similar_posts_by_common_keywords.post_id
  ) AS similar_post_id,

  COALESCE(similar_posts_by_category.similarity_score, 0) +
    COALESCE(similar_posts_by_common_keywords.similarity_score, 0) AS similarity_score

FROM
    posts,

    LATERAL (
      SELECT
        posts.id reference_post_id,
        similar_category_posts.id AS post_id,
        2 AS similarity_score

      FROM
        posts AS similar_category_posts

      WHERE
        posts.id != similar_category_posts.id
        AND posts.category = similar_category_posts.category
    ) similar_posts_by_category

    FULL JOIN LATERAL (
      SELECT
        posts.id reference_post_id,
        post_keywords.post_id,
        COUNT(*) AS similarity_score

      FROM
        post_keywords

      WHERE
        posts.id != post_keywords.post_id
        AND post_keywords.keyword_id IN (
          SELECT
            keyword_id

          FROM
            post_keywords

          WHERE
            post_id = posts.id
        )

      GROUP BY
        post_keywords.post_id
    ) similar_posts_by_common_keywords
      ON similar_posts_by_category.post_id = similar_posts_by_common_keywords.post_id

ORDER BY
  reference_post_id,
  similarity_score DESC