4 files changed, 96 insertions, 1 deletions
diff --git a/src/db/rest/media/api_delete_user_media.sql b/src/db/rest/media/api_delete_user_media.sql
new file mode 100644
index 0000000..d9a4529
--- /dev/null
+++ b/src/db/rest/media/api_delete_user_media.sql
@@ -0,0 +1,26 @@
+CREATE FUNCTION api.delete_user_media(
+	media_type admin.user_media_type
+)
+RETURNS void
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_data BYTEA;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	DELETE FROM
+		admin.user_media
+	WHERE
+		"type" = media_type AND
+		"user_id" = _user_id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.delete_user_media(admin.user_media_type)
+	TO rest_user;
+GRANT DELETE ON TABLE admin.user_media
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.user_media_id_seq
+	TO rest_user;
diff --git a/src/db/rest/media/api_update_user_media.sql b/src/db/rest/media/api_update_user_media.sql
new file mode 100644
index 0000000..be8835a
--- /dev/null
+++ b/src/db/rest/media/api_update_user_media.sql
@@ -0,0 +1,41 @@
+CREATE FUNCTION api.update_user_media(
+	media_type admin.user_media_type,
+	mime TEXT,
+	content TEXT
+)
+RETURNS void
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_data BYTEA;
+BEGIN
+	_user_id = _api.get_user_id();
+	_data = decode(content, 'base64');
+
+	INSERT INTO admin.user_media (
+		user_id,
+		content,
+		mime,
+		type
+	) VALUES (
+		_user_id,
+		_data,
+		mime,
+		media_type
+	) ON CONFLICT (
+		"user_id", "type"
+	) DO UPDATE SET
+		"content" = excluded.content,
+		"mime" = excluded.mime,
+		"type" = excluded.type,
+		"modified" = clock_timestamp();
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.update_user_media(admin.user_media_type, TEXT, TEXT)
+	TO rest_user;
+GRANT INSERT, UPDATE ON TABLE admin.user_media
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.user_media_id_seq
+	TO rest_user;
diff --git a/src/db/rest/rest.sql b/src/db/rest/rest.sql
index 3db77a2..2b71ebe 100644
--- a/src/db/rest/rest.sql
+++ b/src/db/rest/rest.sql
@@ -57,6 +57,8 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user;
 \i /db/rest/media/_api_serve_user_or_default_media.sql;
 \i /db/rest/media/api_profile_avatar.sql;
 \i /db/rest/media/api_profile_banner.sql;
+\i /db/rest/media/api_update_user_media.sql;
+\i /db/rest/media/api_delete_user_media.sql;
 
 -- login
 \i /db/rest/login/_api_sign_jwt.sql;
diff --git a/src/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql
index d71fd1b..0660f42 100644
--- a/src/db/rest/user/api_user.sql
+++ b/src/db/rest/user/api_user.sql
@@ -23,7 +23,11 @@ CREATE VIEW api.user AS
 		COALESCE(p.pc, 0)
 			AS post_count,
 		COALESCE(l.lc, 0)
-			AS like_count
+			AS like_count,
+		ma.mime
+			AS avatar_mime,
+		mb.mime
+			AS banner_mime
 	FROM
 		admin.user u
 	LEFT JOIN (
@@ -81,6 +85,28 @@ CREATE VIEW api.user AS
 	) l
 	ON
 		u.id = l.user_id
+	LEFT JOIN (
+		SELECT
+			ma.mime,
+			ma.user_id
+		FROM
+			admin.user_media ma
+		WHERE
+			ma.type = 'avatar'
+	) ma
+	ON
+		u.id = ma.user_id
+	LEFT JOIN (
+		SELECT
+			mb.mime,
+			mb.user_id
+		FROM
+			admin.user_media mb
+		WHERE
+			mb.type = 'banner'
+	) mb
+	ON
+		u.id = mb.user_id
 	WHERE
 		u.deleted <> TRUE;