From 70628d89c0b0e0fb4694b57a3657d49eb26abb54 Mon Sep 17 00:00:00 2001
From: Dessalines <happydooby@gmail.com>
Date: Tue, 13 Aug 2019 19:52:43 -0700
Subject: Squashed commit of the following:

commit 309d3ec01fb2372e67920cdd7b028c0b80c4ebe6
Author: Dessalines <happydooby@gmail.com>
Date:   Tue Aug 13 19:49:38 2019 -0700

    Adding some docs

commit da18d304377c96e31a511b11b1a4cf1d0b0f0ab7
Author: Dessalines <happydooby@gmail.com>
Date:   Tue Aug 13 19:28:46 2019 -0700

    Adding save user settings

commit 0cd84440f4b35dbc7d7fa825291c783114b327c9
Merge: 3246d5d c148eef
Author: Dessalines <happydooby@gmail.com>
Date:   Tue Aug 13 17:26:25 2019 -0700

    Merge branch 'dev' into nsfw

commit 3246d5d670d0d5e10310a90c6fd81a7dc9e97e54
Author: Dessalines <happydooby@gmail.com>
Date:   Sun Aug 11 20:55:09 2019 -0700

    nsfw mostly done, except for settings page.
---
 .../2019-08-11-000918_add_nsfw_columns/down.sql    | 80 ++++++++++++++++++++++
 1 file changed, 80 insertions(+)
 create mode 100644 server/migrations/2019-08-11-000918_add_nsfw_columns/down.sql

(limited to 'server/migrations/2019-08-11-000918_add_nsfw_columns/down.sql')

diff --git a/server/migrations/2019-08-11-000918_add_nsfw_columns/down.sql b/server/migrations/2019-08-11-000918_add_nsfw_columns/down.sql
new file mode 100644
index 00000000..2eefece4
--- /dev/null
+++ b/server/migrations/2019-08-11-000918_add_nsfw_columns/down.sql
@@ -0,0 +1,80 @@
+drop view community_view;
+drop view post_view;
+alter table community drop column nsfw;
+alter table post drop column nsfw;
+alter table user_ drop column show_nsfw;
+
+-- the views
+create view community_view as 
+with all_community as
+(
+  select *,
+  (select name from user_ u where c.creator_id = u.id) as creator_name,
+  (select name from category ct where c.category_id = ct.id) as category_name,
+  (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers,
+  (select count(*) from post p where p.community_id = c.id) as number_of_posts,
+  (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments,
+  hot_rank((select count(*) from community_follower cf where cf.community_id = c.id), c.published) as hot_rank
+  from community c
+)
+
+select
+ac.*,
+u.id as user_id,
+(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
+from user_ u
+cross join all_community ac
+
+union all
+
+select 
+ac.*,
+null as user_id,
+null as subscribed
+from all_community ac
+;
+
+
+-- Post view
+create view post_view as
+with all_post as
+(
+  select        
+  p.*,
+  (select name from user_ where p.creator_id = user_.id) as creator_name,
+  (select name from community where p.community_id = community.id) as community_name,
+  (select removed from community c where p.community_id = c.id) as community_removed,
+  (select deleted from community c where p.community_id = c.id) as community_deleted,
+  (select count(*) from comment where comment.post_id = p.id) as number_of_comments,
+  coalesce(sum(pl.score), 0) as score,
+  count (case when pl.score = 1 then 1 else null end) as upvotes,
+  count (case when pl.score = -1 then 1 else null end) as downvotes,
+  hot_rank(coalesce(sum(pl.score) , 0), p.published) as hot_rank
+  from post p
+  left join post_like pl on p.id = pl.post_id
+  group by p.id
+)
+
+select
+ap.*,
+u.id as user_id,
+coalesce(pl.score, 0) as my_vote,
+(select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
+(select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
+(select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
+from user_ u
+cross join all_post ap
+left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
+
+union all
+
+select 
+ap.*,
+null as user_id,
+null as my_vote,
+null as subscribed,
+null as read,
+null as saved
+from all_post ap
+;
+
-- 
cgit v1.2.3

