CREATE VIEW api.post AS
	SELECT
		p.id,
		p.user_id,
		p.content,
		p.created,
		p.modified,
		COALESCE(c.cc, 0)
			AS comment_count,
		COALESCE(l.lc, 0)
			AS like_count
	FROM
		xssbook.post p
	LEFT JOIN (
		SELECT
			COUNT(c.id) as cc,
			c.post_id
		FROM
			xssbook.comment c
		GROUP BY
			c.post_id
	) c
	ON
		p.id = c.post_id
	LEFT JOIN (
		SELECT
			COUNT(l.id) as lc,
			l.post_id
		FROM
			xssbook.like l
		GROUP BY
			l.post_id
	) l
	ON
		p.id = l.post_id
	LEFT JOIN
		xssbook.user u
	ON
		u.id = p.user_id
	WHERE
		p.deleted <> TRUE
	AND
		u.deleted <> TRUE
	ORDER BY
		p.id DESC;

GRANT SELECT ON TABLE api.post
	TO rest_anon, rest_user;
GRANT SELECT ON TABLE xssbook.post
	TO rest_anon, rest_user;