reviews: Add schema & display for comments

Not really sure how to integrate and handle this, to be honest. I'll
just play around and see what works.

5 files changed, 51 insertions, 11 deletions

diff --git a/lib/VNWeb/Discussions/Thread.pm b/lib/VNWeb/Discussions/Thread.pm
index 8e443f5f..0949309d 100644
--- a/lib/VNWeb/Discussions/Thread.pm
+++ b/lib/VNWeb/Discussions/Thread.pm
@@ -94,12 +94,13 @@ sub metabox_ {
 }
 
 
+# Also used by Reviews::Page for review comments.
 sub posts_ {
     my($t, $posts, $page) = @_;
     my sub url { "/$t->{id}".($_?"/$_":'') }
 
     paginate_ \&url, $page, [ $t->{count}, 25 ], 't';
-    div_ class => 'mainbox thread', sub {
+    div_ class => 'mainbox thread', id => 'threadstart', sub {
         table_ class => 'stripe', sub {
             tr_ mkclass(deleted => $_->{hidden}), id => $_->{num}, sub {
                 td_ class => 'tc1', $_ == $posts->[$#$posts] ? (id => 'last') : (), sub {
diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm
index 774d4113..56e8ca8f 100644
--- a/lib/VNWeb/Reviews/Page.pm
+++ b/lib/VNWeb/Reviews/Page.pm
@@ -15,9 +15,9 @@ sub review_ {
             td_ sub {
                 b_ style => 'float: right', 'Vote: '.fmtvote($w->{vote}) if $w->{vote};
                 user_ $w;
-                my($date, $lastmod) = map fmtdate($_,'compact'), $w->@{'date', 'lastmod'};
+                my($date, $lastmod) = map $_&&fmtdate($_,'compact'), $w->@{'date', 'lastmod'};
                 txt_ " on $date";
-                b_ class => 'grayedout', " last updated on $lastmod" if $date ne $lastmod;
+                b_ class => 'grayedout', " last updated on $lastmod" if $lastmod && $date ne $lastmod;
             }
         };
         tr_ sub {
@@ -55,32 +55,51 @@ sub review_ {
 }
 
 
-TUWF::get qr{/$RE{wid}}, sub {
+TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
+    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
               , rel.title AS rtitle, rel.original AS roriginal, rel.type AS rtype
-              , COALESCE(s.up,0) AS up, COALESCE(s.down,0) AS down, rv.vote AS my
+              , COALESCE(c.count,0) AS count, COALESCE(s.up,0) AS up, COALESCE(s.down,0) AS down, rv.vote AS my
               , ', sql_user(), ',', sql_totime('r.date'), 'AS date,', sql_totime('r.lastmod'), 'AS lastmod
            FROM reviews r
            LEFT JOIN releases rel ON rel.id = r.rid
            LEFT JOIN users u ON u.id = r.uid
            LEFT JOIN ulist_vns uv ON uv.uid = r.uid AND uv.vid = r.vid
            LEFT JOIN (SELECT id, COUNT(*) FILTER(WHERE vote), COUNT(*) FILTER(WHERE NOT vote) FROM reviews_votes GROUP BY id) AS s(id,up,down) ON s.id = r.id
+           LEFT JOIN (SELECT id, COUNT(*) FROM reviews_posts GROUP BY id) AS c(id,count) ON c.id = r.id
            LEFT JOIN reviews_votes rv ON rv.id = r.id AND rv.uid =', \auth->uid, '
-          WHERE r.id =', \tuwf->capture('id')
+          WHERE r.id =', \$id
     );
     return tuwf->resNotFound if !$w->{id};
 
     enrich_flatten lang => rid => id => sub { sql 'SELECT id, lang FROM releases_lang WHERE id IN', $_, 'ORDER BY id, lang' }, $w;
     enrich_flatten platforms => rid => id => sub { sql 'SELECT id, platform FROM releases_platforms WHERE id IN', $_, 'ORDER BY id, platform' }, $w;
 
+    my $page = $sep eq '/' ? $num||1 : $sep ne '.' ? 1
+        : ceil((tuwf->dbVali('SELECT COUNT(*) FROM reviews_posts WHERE num <=', \$num, 'AND id =', \$id)||9999)/25);
+    $num = 0 if $sep ne '.';
+
+    my $posts = tuwf->dbPagei({ results => 25, page => $page },
+        'SELECT rp.id, rp.num, rp.hidden, rp.msg',
+             ',', sql_user(),
+             ',', sql_totime('rp.date'), ' as date',
+             ',', sql_totime('rp.edited'), ' as edited
+           FROM reviews_posts rp
+           LEFT JOIN users u ON rp.uid = u.id
+          WHERE rp.id =', \$id, '
+          ORDER BY rp.num'
+    );
+
     my $v = db_entry v => $w->{vid};
     VNWeb::VN::Page::enrich_vn($v);
 
-    framework_ title => "Review of $v->{title}", index => 1, type => 'v', dbobj => $v, hiddenmsg => 1, sub {
+    framework_ title => "Review of $v->{title}", index => 1, type => 'v', dbobj => $v, hiddenmsg => 1,
+        js => 1, pagevars => {sethash=>$num?$num:$page>1?'threadstart':'review'},
+    sub {
         VNWeb::VN::Page::infobox_($v);
         VNWeb::VN::Page::tabs_($v, 'reviews');
-        div_ class => 'mainbox', sub {
+        div_ class => 'mainbox', id => 'review', sub {
             p_ class => 'mainopts', sub {
                 if(can_edit w => $w) {
                     a_ href => "/$w->{id}/edit", 'Edit';
@@ -88,9 +107,14 @@ TUWF::get qr{/$RE{wid}}, sub {
                 }
                 a_ href => "/report/w/$w->{id}", 'Report'; # TODO
             };
-            h1_ "Review";
+            h1_ 'Review';
             review_ $w;
         };
+        if(grep !$_->{hidden}, @$posts) {
+            h1_ class => 'boxtitle', 'Comments'; # XXX: How does this look with pagination?
+            VNWeb::Discussions::Thread::posts_($w, $posts, $page);
+        }
+        # TODO: "Add comment" form + fix post editing and reporting.
     };
 };
 
diff --git a/lib/VNWeb/Reviews/VNTab.pm b/lib/VNWeb/Reviews/VNTab.pm
index 3e105a0b..c39ef098 100644
--- a/lib/VNWeb/Reviews/VNTab.pm
+++ b/lib/VNWeb/Reviews/VNTab.pm
@@ -11,12 +11,13 @@ sub reviews_ {
     # TODO: Order
     my $lst = tuwf->dbAlli(
         'SELECT r.id, r.rid, r.summary, r.text <> \'\' AS isfull, r.spoiler, uv.vote
-              , COALESCE(s.up,0) AS up, COALESCE(s.down,0) AS down, rv.vote AS my
+              , COALESCE(c.count,0) AS count, COALESCE(s.up,0) AS up, COALESCE(s.down,0) AS down, rv.vote AS my
               , ', sql_totime('r.date'), 'AS date, ', sql_user(), '
            FROM reviews r
            LEFT JOIN users u ON r.uid = u.id
            LEFT JOIN ulist_vns uv ON uv.uid = r.uid AND uv.vid = r.vid
            LEFT JOIN (SELECT id, COUNT(*) FILTER(WHERE vote), COUNT(*) FILTER(WHERE NOT vote) FROM reviews_votes GROUP BY id) AS s(id,up,down) ON s.id = r.id
+           LEFT JOIN (SELECT id, COUNT(*) FROM reviews_posts GROUP BY id) AS c(id,count) ON c.id = r.id
            LEFT JOIN reviews_votes rv ON rv.uid =', \auth->uid, ' AND rv.id = r.id
           WhERE r.vid =', \$v->{id}
     );
@@ -53,8 +54,8 @@ sub reviews_ {
                     }
                 };
                 div_ sub {
-                    span_ '' if !$r->{isfull};
                     a_ href => "/$r->{id}#review", 'Full review »' if $r->{isfull};
+                    a_ href => "/$r->{id}#threadstart", $r->{count} == 1 ? '1 comment' : "$r->{count} comments";
                     elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, { %$r, can => !!auth }, sub {
                         span_ sprintf '👍 %d 👎 %d', $r->{up}, $r->{down};
                     };
diff --git a/sql/perms.sql b/sql/perms.sql
index cd379d7f..33210c75 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -39,6 +39,7 @@ GRANT SELECT, INSERT,         DELETE ON releases_vn              TO vndb_site;
 GRANT SELECT, INSERT                 ON releases_vn_hist         TO vndb_site;
 GRANT SELECT, INSERT, UPDATE         ON reports                  TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON reviews                  TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_posts            TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_votes            TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON rlists                   TO vndb_site;
 -- No access to the 'sessions' table, managed by the user_* functions.
diff --git a/util/updates/wip-reviews.sql b/util/updates/wip-reviews.sql
index c9699ab2..82b6f327 100644
--- a/util/updates/wip-reviews.sql
+++ b/util/updates/wip-reviews.sql
@@ -14,6 +14,17 @@ CREATE TABLE reviews (
   spoiler boolean NOT NULL
 );
 
+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)
+);
+
 CREATE TABLE reviews_votes (
   id      vndbid NOT NULL,
   uid     int,
@@ -28,6 +39,8 @@ CREATE UNIQUE INDEX reviews_votes_id_uid ON reviews_votes (id,uid);
 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;