5 files changed, 134 insertions, 24 deletions
diff --git a/src/db/rest/media/_api_serve_system_media.sql b/src/db/rest/media/_api_serve_system_media.sql
new file mode 100644
index 0000000..5cd87c2
--- /dev/null
+++ b/src/db/rest/media/_api_serve_system_media.sql
@@ -0,0 +1,37 @@
+CREATE FUNCTION _api.serve_system_media(
+	_media_id INTEGER
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_headers TEXT;
+	_data BYTEA;
+BEGIN
+	SELECT FORMAT(
+      '[{"Content-Type": "%s"},'
+       '{"Content-Disposition": "inline; filename=\"%s\""},'
+       '{"Cache-Control": "max-age=259200"}]'
+      ,	m.mime, m.name)
+	FROM admin.media m
+	WHERE m.id = _media_id
+	INTO _headers;
+
+	SELECT m.content
+	FROM admin.media m
+	WHERE m.id = _media_id
+	INTO _data;
+
+	IF _data IS NOT NULL THEN
+		PERFORM SET_CONFIG('response.headers', _headers, true);
+		RETURN(_data);
+	ELSE
+		PERFORM _api.raise_not_found();
+	END IF;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.serve_system_media(INTEGER)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.media
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/media/_api_serve_user_media.sql b/src/db/rest/media/_api_serve_user_media.sql
new file mode 100644
index 0000000..3487493
--- /dev/null
+++ b/src/db/rest/media/_api_serve_user_media.sql
@@ -0,0 +1,37 @@
+CREATE FUNCTION _api.serve_user_media(
+	_media_id INTEGER
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_headers TEXT;
+	_data BYTEA;
+BEGIN
+	SELECT FORMAT(
+      '[{"Content-Type": "%s"},'
+       '{"Content-Disposition": "inline"},'
+       '{"Cache-Control": "max-age=259200"}]'
+      ,	m.mime)
+	FROM admin.user_media m
+	WHERE m.id = _media_id
+	INTO _headers;
+
+	SELECT m.content
+	FROM admin.user_media m
+	WHERE m.id = _media_id
+	INTO _data;
+
+	IF _data IS NOT NULL THEN
+		PERFORM SET_CONFIG('response.headers', _headers, true);
+		RETURN(_data);
+	ELSE
+		PERFORM _api.raise_not_found();
+	END IF;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.serve_user_media(INTEGER)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user_media
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/media/_api_serve_user_or_default_media.sql b/src/db/rest/media/_api_serve_user_or_default_media.sql
new file mode 100644
index 0000000..c079ba9
--- /dev/null
+++ b/src/db/rest/media/_api_serve_user_or_default_media.sql
@@ -0,0 +1,41 @@
+CREATE FUNCTION _api.serve_user_or_default_media(
+	_user_id INTEGER,
+	_type admin.user_media_type,
+	_default TEXT
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_media_id INTEGER;
+BEGIN
+
+	SELECT id
+	FROM admin.user_media m
+	WHERE m.type = _type
+	AND m.user_id = _user_id
+	INTO _media_id;
+
+	IF FOUND THEN
+		RETURN _api.serve_user_media(_media_id);
+	END IF;
+
+	SELECT id
+	FROM admin.media m
+	WHERE m.name = _default
+	INTO _media_id;
+
+	IF FOUND THEN
+		RETURN _api.serve_system_media(_media_id);
+	END IF;
+
+	PERFORM _api_raise_not_found();
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.serve_user_or_default_media(INTEGER, admin.user_media_type, TEXT)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user_media
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.media
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/media/api_profile_avatar.sql b/src/db/rest/media/api_profile_avatar.sql
index 8607999..b3e456c 100644
--- a/src/db/rest/media/api_profile_avatar.sql
+++ b/src/db/rest/media/api_profile_avatar.sql
@@ -5,32 +5,16 @@ RETURNS sys."*/*"
 LANGUAGE plpgsql VOLATILE
 AS $BODY$
 DECLARE
-	_id INTEGER;
-	_mod INTEGER;
-	_name TEXT;
+	_default TEXT;
 BEGIN
-	SELECT media_id INTO _id
-	FROM admin.user_media m
-	WHERE m.user_id = profile_avatar.user_id
-	AND type = 'avatar'::admin.user_media_type;
-
-	-- get default if not exists
-	IF NOT FOUND THEN
-		_mod = MOD(user_id, 24);
-		_name = 'default_avatar_' || _mod || '.png';
-
-		SELECT id INTO _id
-		FROM admin.media
-		WHERE name = _name;
-	END IF;
-
-	RETURN _api.serve_media(_id);
+	_default := 'default_avatar_' || MOD(user_id, 25) || '.png';
+	RETURN _api.serve_user_or_default_media(
+		user_id,
+		'avatar'::admin.user_media_type,
+		_default
+	);
 END
 $BODY$;
 
 GRANT EXECUTE ON FUNCTION api.profile_avatar(INTEGER)
 	TO rest_anon, rest_user;
-GRANT SELECT ON TABLE admin.user_media
-	TO rest_anon, rest_user;
-GRANT SELECT ON TABLE admin.media
-	TO rest_anon, rest_user;
diff --git a/src/db/rest/media/api_profile_banner.sql b/src/db/rest/media/api_profile_banner.sql
index 272d021..d98f553 100644
--- a/src/db/rest/media/api_profile_banner.sql
+++ b/src/db/rest/media/api_profile_banner.sql
@@ -4,10 +4,21 @@ CREATE FUNCTION api.profile_banner(
 RETURNS sys."*/*"
 LANGUAGE plpgsql VOLATILE
 AS $BODY$
+DECLARE
+	_default TEXT;
 BEGIN
-	PERFORM _api.raise_deny();
+	_default := 'default_banner_' || MOD(user_id, 25) || '.png';
+	RETURN _api.serve_user_or_default_media(
+		user_id,
+		'banner'::admin.user_media_type,
+		_default
+	);
 END
 $BODY$;
 
 GRANT EXECUTE ON FUNCTION api.profile_banner(INTEGER)
 	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user_media
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.media
+	TO rest_anon, rest_user;