CREATE VIEW api.user AS
	SELECT
		u.id,
		u.username,
		NULL AS password,
		u.role,
		u.first_name,
		u.last_name,
		u.middle_name,
		u.email,
		u.gender,
		u.birth_date,
		u.profile_bio,
		u.created,
		u.modified,
		u.seen,
		COALESCE(f.fc, 0)
			AS follower_count,
		COALESCE(fl.fc, 0)
			AS followed_count,
		COALESCE(c.cc, 0)
			AS comment_count,
		COALESCE(p.pc, 0)
			AS post_count,
		COALESCE(l.lc, 0)
			AS like_count,
		ma.mime
			AS avatar_mime,
		mb.mime
			AS banner_mime
	FROM
		xssbook.user u
	LEFT JOIN (
		SELECT
			COUNT(f.id) as fc,
			f.followee_id
		FROM
			xssbook.follow f
		GROUP BY
			f.followee_id
	) f
	ON
		u.id = f.followee_id
	LEFT JOIN (
		SELECT
			COUNT(fl.id) as fc,
			fl.follower_id
		FROM
			xssbook.follow fl
		GROUP BY
			fl.follower_id
	) fl
	ON
		u.id = fl.follower_id
	LEFT JOIN (
		SELECT
			COUNT(c.id) as cc,
			c.user_id
		FROM
			xssbook.comment c
		GROUP BY
			c.user_id
	) c
	ON
		u.id = c.user_id
	LEFT JOIN (
		SELECT
			COUNT(p.id) as pc,
			p.user_id
		FROM
			xssbook.post p
		GROUP BY
			p.user_id
	) p
	ON
		u.id = p.user_id
	LEFT JOIN (
		SELECT
			COUNT(l.id) as lc,
			l.user_id
		FROM
			xssbook.like l
		GROUP BY
			l.user_id
	) l
	ON
		u.id = l.user_id
	LEFT JOIN (
		SELECT
			ma.mime,
			ma.user_id
		FROM
			xssbook.user_media ma
		WHERE
			ma.type = 'avatar'
	) ma
	ON
		u.id = ma.user_id
	LEFT JOIN (
		SELECT
			mb.mime,
			mb.user_id
		FROM
			xssbook.user_media mb
		WHERE
			mb.type = 'banner'
	) mb
	ON
		u.id = mb.user_id
	WHERE
		u.deleted <> TRUE;

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