reviews: Make reviews mod-only for now to allow some beta testing

It's totally not finished yet, but it's useful to gather some feedback
before I start with integration with the rest of the site.

6 files changed, 53 insertions, 7 deletions

diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm
index 9817d95c..fdd2da79 100644
--- a/lib/VNWeb/Reviews/Page.pm
+++ b/lib/VNWeb/Reviews/Page.pm
@@ -75,6 +75,7 @@ sub review_ {
 
 
 TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
+    return tuwf->resNotFound if !auth->permReview; #XXX:While in beta
     my($id, $sep, $num) = (tuwf->capture('id'), tuwf->capture('sep')||'', tuwf->capture('num'));
     my $w = tuwf->dbRowi(
         'SELECT r.id, r.vid, r.rid, r.summary, r.text, r.spoiler, uv.vote
diff --git a/lib/VNWeb/Reviews/VNTab.pm b/lib/VNWeb/Reviews/VNTab.pm
index f49a9e1c..4b81a601 100644
--- a/lib/VNWeb/Reviews/VNTab.pm
+++ b/lib/VNWeb/Reviews/VNTab.pm
@@ -69,6 +69,7 @@ sub reviews_ {
 
 
 TUWF::get qr{/$RE{vid}/reviews}, sub {
+    return tuwf->resNotFound if !auth->permReview; #XXX:While in beta
     my $v = db_entry v => tuwf->capture('id');
     return tuwf->resNotFound if !$v;
     VNWeb::VN::Page::enrich_vn($v);
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 885b6cb7..bb25c015 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -387,7 +387,7 @@ sub infobox_ {
 sub tabs_ {
     my($v, $tab) = @_;
     my $chars = tuwf->dbVali('SELECT COUNT(DISTINCT c.id) FROM chars c JOIN chars_vns cv ON cv.id = c.id WHERE NOT c.hidden AND cv.vid =', \$v->{id});
-    my $reviews = tuwf->dbVali('SELECT COUNT(*) FROM reviews WHERE vid =', \$v->{id});
+    my $reviews = auth->permReview ? tuwf->dbVali('SELECT COUNT(*) FROM reviews WHERE vid =', \$v->{id}) : 0;
 
     return if !$chars && !$reviews && !auth->permEdit && !auth->permReview;
     $tab ||= '';
diff --git a/sql/schema.sql b/sql/schema.sql
index 88ceb384..40d70c0a 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -69,10 +69,11 @@ CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
 CREATE TYPE session_type      AS ENUM ('web', 'pass', 'mail');
 
 -- Sequences used for ID generation
-CREATE SEQUENCE covers_seq;
 CREATE SEQUENCE charimg_seq;
-CREATE SEQUENCE threads_id_seq;
+CREATE SEQUENCE covers_seq;
+CREATE SEQUENCE reviews_seq;
 CREATE SEQUENCE screenshots_seq;
+CREATE SEQUENCE threads_id_seq;
 
 
 
@@ -497,6 +498,39 @@ CREATE TABLE reports (
   objectnum  integer -- The sub-id of the thing to be reported
 );
 
+-- reviews
+CREATE TABLE reviews (
+  id      vndbid PRIMARY KEY DEFAULT vndbid('w', nextval('reviews_seq')::int) CONSTRAINT reviews_id_check CHECK(vndbid_type(id) = 'w'),
+  vid     int NOT NULL,
+  uid     int,
+  rid     int,
+  date    timestamptz NOT NULL DEFAULT NOW(),
+  lastmod timestamptz,
+  summary text NOT NULL,
+  text    text,
+  spoiler boolean NOT NULL
+);
+
+-- reviews_posts
+CREATE TABLE reviews_posts (
+  id      vndbid NOT NULL,
+  num     smallint NOT NULL,
+  uid     integer,
+  date    timestamptz NOT NULL DEFAULT NOW(),
+  edited  timestamptz,
+  hidden  boolean NOT NULL DEFAULT FALSE,
+  msg     text NOT NULL DEFAULT '',
+  PRIMARY KEY(id, num)
+);
+
+-- reviews_votes
+CREATE TABLE reviews_votes (
+  id      vndbid NOT NULL,
+  uid     int,
+  date    timestamptz NOT NULL,
+  vote    boolean NOT NULL -- true = upvote, false = downvote
+);
+
 -- rlists
 CREATE TABLE rlists (
   uid integer NOT NULL DEFAULT 0, -- [pub]
@@ -865,7 +899,8 @@ CREATE TABLE users (
   perm_imgmod     boolean NOT NULL DEFAULT false,
   max_sexual      smallint NOT NULL DEFAULT 0,
   max_violence    smallint NOT NULL DEFAULT 0,
-  last_reports    timestamptz -- For mods: Most recent activity seen on the reports listing
+  last_reports    timestamptz, -- For mods: Most recent activity seen on the reports listing
+  perm_review     boolean NOT NULL DEFAULT false -- TODO: DEFAULT true when out of beta.
 );
 
 -- vn
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index a1dd16b6..191e18fe 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -42,6 +42,13 @@ ALTER TABLE releases_vn              ADD CONSTRAINT releases_vn_id_fkey
 ALTER TABLE releases_vn              ADD CONSTRAINT releases_vn_vid_fkey               FOREIGN KEY (vid)       REFERENCES vn            (id);
 ALTER TABLE releases_vn_hist         ADD CONSTRAINT releases_vn_hist_chid_fkey         FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE releases_vn_hist         ADD CONSTRAINT releases_vn_hist_vid_fkey          FOREIGN KEY (vid)       REFERENCES vn            (id);
+ALTER TABLE reviews                  ADD CONSTRAINT reviews_vid_fkey                   FOREIGN KEY (vid)       REFERENCES vn            (id) ON DELETE CASCADE;
+ALTER TABLE reviews                  ADD CONSTRAINT reviews_uid_fkey                   FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
+ALTER TABLE reviews                  ADD CONSTRAINT reviews_rid_fkey                   FOREIGN KEY (rid)       REFERENCES releases      (id) ON DELETE SET DEFAULT;
+ALTER TABLE reviews_posts            ADD CONSTRAINT reviews_posts_id_fkey              FOREIGN KEY (id)        REFERENCES reviews       (id) ON DELETE CASCADE;
+ALTER TABLE reviews_posts            ADD CONSTRAINT reviews_posts_uid_fkey             FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
+ALTER TABLE reviews_votes            ADD CONSTRAINT reviews_votes_id_fkey              FOREIGN KEY (id)        REFERENCES reviews       (id) ON DELETE CASCADE;
+ALTER TABLE reviews_votes            ADD CONSTRAINT reviews_votes_uid_fkey             FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE rlists                   ADD CONSTRAINT rlists_uid_fkey                    FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE rlists                   ADD CONSTRAINT rlists_rid_fkey                    FOREIGN KEY (rid)       REFERENCES releases      (id);
 ALTER TABLE sessions                 ADD CONSTRAINT sessions_uid_fkey                  FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
@@ -117,6 +124,9 @@ CREATE        INDEX releases_producers_pid ON releases_producers (pid);
 CREATE        INDEX releases_vn_vid        ON releases_vn (vid);
 CREATE        INDEX reports_new            ON reports (date) WHERE status = 'new';
 CREATE        INDEX reports_lastmod        ON reports (lastmod);
+CREATE UNIQUE INDEX reviews_vid_uid        ON reviews (vid,uid);
+CREATE        INDEX reviews_uid            ON reviews (uid);
+CREATE UNIQUE INDEX reviews_votes_id_uid   ON reviews_votes (id,uid);
 CREATE        INDEX staff_alias_id         ON staff_alias (id);
 CREATE UNIQUE INDEX tags_vn_pkey           ON tags_vn (tag,vid,uid);
 CREATE        INDEX tags_vn_date           ON tags_vn (date);
diff --git a/util/updates/wip-reviews.sql b/util/updates/2020-08-17-reviews.sql
index 677eb7b6..87ded565 100644
--- a/util/updates/wip-reviews.sql
+++ b/util/updates/2020-08-17-reviews.sql
@@ -5,7 +5,6 @@ ALTER TABLE reports DROP COLUMN rtype;
 DROP TYPE report_type;
 
 
--- WIP: The modifications in this file are not final and haven't been integrated in sql/ yet.
 
 CREATE SEQUENCE reviews_seq;
 
@@ -51,8 +50,8 @@ ALTER TABLE reviews_posts ADD CONSTRAINT reviews_posts_uid_fkey FOREIGN KEY (uid
 ALTER TABLE reviews_votes ADD CONSTRAINT reviews_votes_id_fkey  FOREIGN KEY (id)  REFERENCES reviews  (id) ON DELETE CASCADE;
 ALTER TABLE reviews_votes ADD CONSTRAINT reviews_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users    (id) ON DELETE CASCADE;
 
-ALTER TABLE users ADD COLUMN perm_review boolean NOT NULL DEFAULT true;
-UPDATE users SET perm_review = false WHERE not perm_board;
+ALTER TABLE users ADD COLUMN perm_review boolean NOT NULL DEFAULT false;
+UPDATE users SET perm_review = false WHERE not perm_dbmod;
 
 \i sql/perms.sql