login and register, liking on homepage

30 files changed, 251 insertions, 30 deletions

diff --git a/db/rest/comment/api_comment.sql b/src/db/rest/comment/api_comment.sql
index e50ca2f..c8a0e19 100644
--- a/db/rest/comment/api_comment.sql
+++ b/src/db/rest/comment/api_comment.sql
@@ -4,10 +4,24 @@ CREATE VIEW api.comment AS
 		c.user_id,
 		c.post_id,
 		c.content,
-		c.date
+		c.created,
+		c.modified
 	FROM
 		admin.comment c
-	ORDER BY id ASC;
+	LEFT JOIN
+		admin.post p
+	ON
+		p.id = c.post_id
+	LEFT JOIN
+		admin.user u
+	ON
+		u.id = c.user_id
+	WHERE
+		c.deleted <> TRUE AND
+		p.deleted <> TRUE AND
+		u.deleted <> TRUE
+	ORDER BY
+		id ASC;
 
 GRANT SELECT ON TABLE api.comment
 	TO rest_anon, rest_user;
diff --git a/db/rest/comment/api_comment_delete.sql b/src/db/rest/comment/api_comment_delete.sql
index d7db8a4..262b2ed 100644
--- a/db/rest/comment/api_comment_delete.sql
+++ b/src/db/rest/comment/api_comment_delete.sql
@@ -11,9 +11,10 @@ BEGIN
 		PERFORM _api.raise_deny();
 	END IF;
 
-	DELETE FROM admin.comment
-	WHERE user_id = _user_id
-	AND id = OLD.id;
+	UPDATE admin.comment SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
 END
 $BODY$;
 
@@ -21,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.comment_delete()
 	TO rest_user;
 GRANT DELETE ON TABLE api.comment
 	TO rest_user;
-GRANT DELETE ON TABLE admin.comment
+GRANT UPDATE ON TABLE admin.comment
 	TO rest_user;
 
 CREATE TRIGGER api_comment_delete_trgr
diff --git a/db/rest/comment/api_comment_insert.sql b/src/db/rest/comment/api_comment_insert.sql
index 878e194..990beef 100644
--- a/db/rest/comment/api_comment_insert.sql
+++ b/src/db/rest/comment/api_comment_insert.sql
@@ -34,7 +34,9 @@ BEGIN
 		_user_id,
 		NEW.post_id,
 		NEW.content
-	);
+	)
+	RETURNING id
+	INTO NEW.id;
 
 	RETURN NEW;
 END
diff --git a/db/rest/comment/api_comment_update.sql b/src/db/rest/comment/api_comment_update.sql
index d6b4aca..b8fc16d 100644
--- a/db/rest/comment/api_comment_update.sql
+++ b/src/db/rest/comment/api_comment_update.sql
@@ -27,8 +27,9 @@ BEGIN
 	END IF;
 
 	IF _changed THEN
-		UPDATE admin.comment
-			SET content = NEW.content
+		UPDATE admin.comment SET
+			content = NEW.content,
+			modified = clock_timestamp()
 			WHERE id = OLD.id;
 	END IF;
 
diff --git a/src/db/rest/like/api_like.sql b/src/db/rest/like/api_like.sql
new file mode 100644
index 0000000..6588b43
--- /dev/null
+++ b/src/db/rest/like/api_like.sql
@@ -0,0 +1,16 @@
+CREATE VIEW api.like AS
+	SELECT
+		l.id,
+		l.user_id,
+		l.post_id,
+		l.comment_id,
+		l.value,
+		l.created,
+		l.modified
+	FROM
+		admin.like l;
+
+GRANT SELECT ON TABLE api.like
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.like
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/like/api_like_delete.sql b/src/db/rest/like/api_like_delete.sql
new file mode 100644
index 0000000..7209a40
--- /dev/null
+++ b/src/db/rest/like/api_like_delete.sql
@@ -0,0 +1,32 @@
+CREATE FUNCTION _api.like_delete()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	UPDATE admin.like SET
+		value = FALSE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.like_delete()
+	TO rest_user;
+GRANT DELETE ON TABLE api.like
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.like
+	TO rest_user;
+
+CREATE TRIGGER api_like_delete_trgr
+	INSTEAD OF DELETE
+			ON api.like
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.like_delete();
diff --git a/src/db/rest/like/api_like_insert.sql b/src/db/rest/like/api_like_insert.sql
new file mode 100644
index 0000000..a02ad4e
--- /dev/null
+++ b/src/db/rest/like/api_like_insert.sql
@@ -0,0 +1,51 @@
+CREATE FUNCTION _api.like_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF
+		NEW.post_id IS NULL AND
+		NEW.comment_id IS NULL
+	THEN
+		-- for now
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.value := COALESCE(NEW.value, TRUE);
+
+	INSERT INTO admin.like (
+		user_id,
+		post_id,
+		comment_id,
+		value
+	) VALUES (
+		_user_id,
+		NEW.post_id,
+		NEW.comment_id,
+		NEW.value
+	)
+	RETURNING id
+	INTO NEW.id;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.like_insert()
+	TO rest_user;
+GRANT INSERT ON TABLE api.like
+	TO rest_user;
+GRANT INSERT ON TABLE admin.like
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.like_id_seq
+	TO rest_user;
+
+CREATE TRIGGER api_like_insert_trgr
+	INSTEAD OF INSERT
+			ON api.like
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.like_insert();
diff --git a/src/db/rest/like/api_like_update.sql b/src/db/rest/like/api_like_update.sql
new file mode 100644
index 0000000..76db73a
--- /dev/null
+++ b/src/db/rest/like/api_like_update.sql
@@ -0,0 +1,44 @@
+CREATE FUNCTION _api.like_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_changed BOOLEAN;
+BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.value = COALESCE(NEW.value, OLD.value);
+
+	IF NEW.value IS DISTINCT FROM OLD.value THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.like SET
+			value = NEW.value,
+			modified = clock_timestamp()
+			WHERE id = OLD.id;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.like_update()
+	TO rest_user;
+GRANT UPDATE ON TABLE api.like
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.like
+	TO rest_user;
+
+CREATE TRIGGER api_like_update_trgr
+	INSTEAD OF UPDATE
+			ON api.like
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.like_update();
diff --git a/db/rest/login/_api_sign_jwt.sql b/src/db/rest/login/_api_sign_jwt.sql
index dc8e920..dc8e920 100644
--- a/db/rest/login/_api_sign_jwt.sql
+++ b/src/db/rest/login/_api_sign_jwt.sql
diff --git a/db/rest/login/_api_validate_role.sql b/src/db/rest/login/_api_validate_role.sql
index 9f1e54f..9f1e54f 100644
--- a/db/rest/login/_api_validate_role.sql
+++ b/src/db/rest/login/_api_validate_role.sql
diff --git a/db/rest/login/_api_verify_jwt.sql b/src/db/rest/login/_api_verify_jwt.sql
index f5a6daf..9e63cc9 100644
--- a/db/rest/login/_api_verify_jwt.sql
+++ b/src/db/rest/login/_api_verify_jwt.sql
@@ -8,6 +8,7 @@ DECLARE
 	_payload JSON;
 	_valid BOOLEAN;
 	_jwt_secret TEXT;
+	_user_id INTEGER;
 BEGIN
 	SELECT jwt_secret INTO _jwt_secret
 	FROM sys.database_info
@@ -28,7 +29,13 @@ BEGIN
 		RETURN NULL;
 	END IF;
 
-	RETURN _payload->>'user_id';
+	_user_id = _payload->>'user_id';
+
+	UPDATE admin.user
+	SET seen = clock_timestamp()
+	WHERE id = _user_id;
+
+	RETURN _user_id;
 END
 $BODY$;
 
@@ -36,3 +43,5 @@ GRANT EXECUTE ON FUNCTION _api.verify_jwt(TEXT)
 	TO rest_anon, rest_user;
 GRANT SELECT ON TABLE sys.database_info
 	TO rest_anon, rest_user;
+GRANT UPDATE ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/db/rest/login/api_login.sql b/src/db/rest/login/api_login.sql
index 0cf0535..0cf0535 100644
--- a/db/rest/login/api_login.sql
+++ b/src/db/rest/login/api_login.sql
diff --git a/db/rest/post/api_post.sql b/src/db/rest/post/api_post.sql
index 375f292..0d60473 100644
--- a/db/rest/post/api_post.sql
+++ b/src/db/rest/post/api_post.sql
@@ -3,7 +3,8 @@ CREATE VIEW api.post AS
 		p.id,
 		p.user_id,
 		p.content,
-		p.date,
+		p.created,
+		p.modified,
 		COALESCE(c.cc, 0)
 			AS comment_count
 	FROM
@@ -16,8 +17,19 @@ CREATE VIEW api.post AS
 			admin.comment c
 		GROUP BY
 			c.post_id
-	) c ON p.id = c.post_id
-	ORDER BY p.id DESC;
+	) c
+	ON
+		p.id = c.post_id
+	LEFT JOIN
+		admin.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;
diff --git a/db/rest/post/api_post_delete.sql b/src/db/rest/post/api_post_delete.sql
index e3dec55..8f26b40 100644
--- a/db/rest/post/api_post_delete.sql
+++ b/src/db/rest/post/api_post_delete.sql
@@ -11,9 +11,10 @@ BEGIN
 		PERFORM _api.raise_deny();
 	END IF;
 
-	DELETE FROM admin.post
-	WHERE user_id = _user_id
-	AND id = OLD.id;
+	UPDATE admin.post SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
 END
 $BODY$;
 
@@ -21,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.post_delete()
 	TO rest_user;
 GRANT DELETE ON TABLE api.post
 	TO rest_user;
-GRANT DELETE ON TABLE admin.post
+GRANT UPDATE ON TABLE admin.post
 	TO rest_user;
 
 CREATE TRIGGER api_post_delete_trgr
diff --git a/db/rest/post/api_post_insert.sql b/src/db/rest/post/api_post_insert.sql
index 8b2eb48..e0594dc 100644
--- a/db/rest/post/api_post_insert.sql
+++ b/src/db/rest/post/api_post_insert.sql
@@ -22,7 +22,9 @@ BEGIN
 	) VALUES (
 		_user_id,
 		NEW.content
-	);
+	)
+	RETURNING id
+	INTO NEW.id;
 
 	RETURN NEW;
 END
diff --git a/db/rest/post/api_post_update.sql b/src/db/rest/post/api_post_update.sql
index 70230d0..7b4360d 100644
--- a/db/rest/post/api_post_update.sql
+++ b/src/db/rest/post/api_post_update.sql
@@ -27,8 +27,9 @@ BEGIN
 	END IF;
 
 	IF _changed THEN
-		UPDATE admin.post
-			SET content = NEW.content
+		UPDATE admin.post SET
+			content = NEW.content,
+			modified = clock_timestamp()
 			WHERE id = OLD.id;
 	END IF;
 
diff --git a/db/rest/rest.sql b/src/db/rest/rest.sql
index 54f5118..3e6737c 100644
--- a/db/rest/rest.sql
+++ b/src/db/rest/rest.sql
@@ -41,6 +41,12 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user;
 \i /db/rest/comment/api_comment_update.sql;
 \i /db/rest/comment/api_comment_delete.sql;
 
+-- like
+\i /db/rest/like/api_like.sql;
+\i /db/rest/like/api_like_insert.sql;
+\i /db/rest/like/api_like_update.sql;
+\i /db/rest/like/api_like_delete.sql;
+
 -- login
 \i /db/rest/login/_api_sign_jwt.sql;
 \i /db/rest/login/_api_verify_jwt.sql;
diff --git a/db/rest/user/api_avatar.sql b/src/db/rest/user/api_avatar.sql
index 981409f..981409f 100644
--- a/db/rest/user/api_avatar.sql
+++ b/src/db/rest/user/api_avatar.sql
diff --git a/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql
index e45768a..6735775 100644
--- a/db/rest/user/api_user.sql
+++ b/src/db/rest/user/api_user.sql
@@ -9,13 +9,15 @@ CREATE VIEW api.user AS
 		u.middle_name,
 		u.email,
 		u.gender,
-		u.join_date,
 		u.birth_date,
-		u.profile_avatar,
-		u.profile_banner,
-		u.profile_bio
+		u.profile_bio,
+		u.created,
+		u.modified,
+		u.seen
 	FROM
-		admin.user u;
+		admin.user u
+	WHERE
+		u.deleted <> TRUE;
 
 GRANT SELECT ON TABLE api.user
 	TO rest_anon, rest_user;
diff --git a/db/rest/user/api_user_delete.sql b/src/db/rest/user/api_user_delete.sql
index 8d7d52f..4389fa0 100644
--- a/db/rest/user/api_user_delete.sql
+++ b/src/db/rest/user/api_user_delete.sql
@@ -11,8 +11,10 @@ BEGIN
 		PERFORM _api.raise_deny();
 	END IF;
 
-	DELETE FROM admin.user
-	WHERE id = _user_id;
+	UPDATE admin.user SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = _user_id;
 END
 $BODY$;
 
@@ -20,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.user_delete()
 	TO rest_user;
 GRANT DELETE ON TABLE api.user
 	TO rest_user;
-GRANT DELETE ON TABLE admin.user
+GRANT UPDATE ON TABLE admin.user
 	TO rest_user;
 
 CREATE TRIGGER api_user_delete_trgr
diff --git a/db/rest/user/api_user_insert.sql b/src/db/rest/user/api_user_insert.sql
index 2297ecd..1a6ef7c 100644
--- a/db/rest/user/api_user_insert.sql
+++ b/src/db/rest/user/api_user_insert.sql
@@ -104,7 +104,9 @@ BEGIN
 		NEW.gender,
 		NEW.birth_date,
 		NEW.profile_bio
-	);
+	)
+	RETURNING id
+	INTO NEW.id;
 
 	NEW.password := NULL;
 
diff --git a/db/rest/user/api_user_update.sql b/src/db/rest/user/api_user_update.sql
index 28e4368..2e7cd50 100644
--- a/db/rest/user/api_user_update.sql
+++ b/src/db/rest/user/api_user_update.sql
@@ -145,7 +145,8 @@ BEGIN
 			email = NEW.email,
 			gender = NEW.gender,
 			birth_date = NEW.birth_date,
-			profile_bio = NEW.profile_bio
+			profile_bio = NEW.profile_bio,
+			modified = clock_timestamp()
 			WHERE id = OLD.id;
 	END IF;
 
diff --git a/src/db/rest/util/_api_get_user_id.sql b/src/db/rest/util/_api_get_user_id.sql
new file mode 100644
index 0000000..e86afc3
--- /dev/null
+++ b/src/db/rest/util/_api_get_user_id.sql
@@ -0,0 +1,22 @@
+CREATE FUNCTION _api.get_user_id()
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = CURRENT_SETTING(
+		'request.jwt.claims',
+		TRUE
+	)::JSON->>'user_id';
+
+	UPDATE admin.user
+	SET seen = clock_timestamp()
+	WHERE id = _user_id;
+
+	RETURN _user_id;
+END
+$BODY$;
+
+GRANT UPDATE ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/db/rest/util/_api_raise.sql b/src/db/rest/util/_api_raise.sql
index 5c740c6..5c740c6 100644
--- a/db/rest/util/_api_raise.sql
+++ b/src/db/rest/util/_api_raise.sql
diff --git a/db/rest/util/_api_raise_deny.sql b/src/db/rest/util/_api_raise_deny.sql
index 17406b7..17406b7 100644
--- a/db/rest/util/_api_raise_deny.sql
+++ b/src/db/rest/util/_api_raise_deny.sql
diff --git a/db/rest/util/_api_raise_null.sql b/src/db/rest/util/_api_raise_null.sql
index be6ee29..be6ee29 100644
--- a/db/rest/util/_api_raise_null.sql
+++ b/src/db/rest/util/_api_raise_null.sql
diff --git a/db/rest/util/_api_raise_unique.sql b/src/db/rest/util/_api_raise_unique.sql
index a18d960..a18d960 100644
--- a/db/rest/util/_api_raise_unique.sql
+++ b/src/db/rest/util/_api_raise_unique.sql
diff --git a/db/rest/util/_api_serve_media.sql b/src/db/rest/util/_api_serve_media.sql
index 8b0f0b8..8b0f0b8 100644
--- a/db/rest/util/_api_serve_media.sql
+++ b/src/db/rest/util/_api_serve_media.sql
diff --git a/db/rest/util/_api_trim.sql b/src/db/rest/util/_api_trim.sql
index c972282..c972282 100644
--- a/db/rest/util/_api_trim.sql
+++ b/src/db/rest/util/_api_trim.sql
diff --git a/db/rest/util/_api_validate_text.sql b/src/db/rest/util/_api_validate_text.sql
index ff3a227..ff3a227 100644
--- a/db/rest/util/_api_validate_text.sql
+++ b/src/db/rest/util/_api_validate_text.sql