plumber/db/queries/posts.sql

-- name: CreatePost :exec
INSERT INTO posts (
  id, parent_id, author_id, title, body, city, post_date, post_state, created_at, updated_at
)
VALUES (
  sqlc.arg(id),
  sqlc.arg(parent_id),
  sqlc.arg(author_id),
  sqlc.arg(title),
  sqlc.arg(body),
  sqlc.arg(city),
  sqlc.arg(post_date),
  sqlc.arg(post_state),
  sqlc.arg(created_at),
  sqlc.arg(updated_at)
);

-- name: GetPost :one
SELECT
  p.id, p.parent_id, p.author_id, u.name AS author_name, u.role AS author_role,
  p.title, p.body, p.city, p.post_date, p.post_state, p.created_at, p.updated_at
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.id = sqlc.arg(id);

-- name: ListPostThread :many
WITH RECURSIVE thread AS (
  SELECT p.*
  FROM posts p
  WHERE p.id = sqlc.arg(root_id) AND p.parent_id IS NULL

  UNION ALL

  SELECT child.*
  FROM posts child
  JOIN thread parent ON child.parent_id = parent.id
)
SELECT
  thread.id, thread.parent_id, thread.author_id,
  u.name AS author_name, u.role AS author_role,
  thread.title, thread.body, thread.city, thread.post_date,
  thread.post_state, thread.created_at, thread.updated_at
FROM thread
JOIN users u ON u.id = thread.author_id
ORDER BY thread.created_at, thread.id;

-- name: GetRootPostVoteSummary :one
SELECT
  COALESCE(SUM(value), 0)::bigint AS score,
  COALESCE(
    MAX(value) FILTER (WHERE user_id = sqlc.arg(viewer_id)),
    0
  )::bigint AS user_vote
FROM post_votes
WHERE post_id = sqlc.arg(root_id);

-- name: UpdatePost :execrows
UPDATE posts
SET
  body = sqlc.arg(body),
  updated_at = sqlc.arg(updated_at)
WHERE id = sqlc.arg(id);

-- name: UpdateRootPostState :execrows
UPDATE posts
SET
  post_state = sqlc.arg(post_state),
  updated_at = sqlc.arg(updated_at)
WHERE id = sqlc.arg(id)
  AND parent_id IS NULL;

-- name: ListRootPosts :many
WITH RECURSIVE roots AS (
  SELECT p.*
  FROM posts p
  WHERE p.parent_id IS NULL
    AND p.post_date = sqlc.arg(post_date)
    AND p.post_state <> sqlc.arg(hidden_state)
),
thread AS (
  SELECT roots.id AS root_id, child.id AS post_id, child.author_id
  FROM roots
  JOIN posts child ON child.parent_id = roots.id

  UNION ALL

  SELECT thread.root_id, child.id, child.author_id
  FROM thread
  JOIN posts child ON child.parent_id = thread.post_id
),
answered AS (
  SELECT DISTINCT thread.root_id
  FROM thread
  JOIN users u ON u.id = thread.author_id
  WHERE u.role = 'admin'
),
scores AS (
  SELECT votes.post_id, SUM(votes.value)::bigint AS score
  FROM roots
  JOIN post_votes votes ON votes.post_id = roots.id
  GROUP BY votes.post_id
)
SELECT
  roots.id, roots.parent_id, roots.author_id,
  u.name AS author_name, u.role AS author_role,
  roots.title, roots.body, roots.city, roots.post_date,
  roots.post_state, roots.created_at, roots.updated_at,
  COALESCE(scores.score, 0)::bigint AS score,
  (answered.root_id IS NOT NULL)::bool AS answered,
  COALESCE(viewer_vote.value, 0)::bigint AS user_vote
FROM roots
JOIN users u ON u.id = roots.author_id
LEFT JOIN scores ON scores.post_id = roots.id
LEFT JOIN answered ON answered.root_id = roots.id
LEFT JOIN post_votes viewer_vote
  ON viewer_vote.user_id = sqlc.arg(viewer_id)
  AND viewer_vote.post_id = roots.id
ORDER BY score DESC, roots.created_at, roots.id
LIMIT sqlc.arg(row_limit);

-- name: ListRootPostsByAuthor :many
SELECT
  p.id, p.parent_id, p.author_id,
  u.name AS author_name, u.role AS author_role,
  p.title, p.body, p.city, p.post_date,
  p.post_state, p.created_at, p.updated_at
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.parent_id IS NULL
  AND p.author_id = sqlc.arg(author_id)
  AND p.post_state <> sqlc.arg(hidden_state)
ORDER BY p.created_at DESC, p.id DESC
LIMIT sqlc.arg(row_limit);

-- name: ListRootPostsAnsweredBy :many
WITH RECURSIVE ancestors AS (
  SELECT p.id, p.parent_id
  FROM posts p
  WHERE p.author_id = sqlc.arg(admin_id)
    AND p.parent_id IS NOT NULL

  UNION

  SELECT parent.id, parent.parent_id
  FROM posts parent
  JOIN ancestors child ON child.parent_id = parent.id
)
SELECT DISTINCT
  root.id, root.parent_id, root.author_id,
  u.name AS author_name, u.role AS author_role,
  root.title, root.body, root.city, root.post_date,
  root.post_state, root.created_at, root.updated_at
FROM posts root
JOIN ancestors ON ancestors.id = root.id
JOIN users u ON u.id = root.author_id
WHERE root.parent_id IS NULL
  AND root.post_state <> sqlc.arg(hidden_state)
ORDER BY root.created_at DESC, root.id DESC
LIMIT sqlc.arg(row_limit);

-- name: PostIsVisibleRoot :one
SELECT EXISTS(
  SELECT 1
  FROM posts
  WHERE id = sqlc.arg(id)
    AND parent_id IS NULL
    AND post_state <> sqlc.arg(hidden_state)
)::bool;

-- name: DeletePostVote :exec
DELETE FROM post_votes
WHERE user_id = sqlc.arg(user_id)
  AND post_id = sqlc.arg(post_id);

-- name: UpsertPostVoteOnVisibleRoot :execrows
INSERT INTO post_votes (user_id, post_id, value)
SELECT sqlc.arg(user_id), sqlc.arg(post_id), sqlc.arg(value)
FROM posts p
WHERE p.id = sqlc.arg(post_id)
  AND p.parent_id IS NULL
  AND p.post_state <> sqlc.arg(hidden_state)
ON CONFLICT (user_id, post_id) DO UPDATE
SET value = excluded.value;