reviews: Allow anonymous voting + display overrule counting

Anonymous votes count for 0.01, regular votes for 1 and overrule votes
for 1000. It's a bit crude, but ought to do the job.

Reason for this change is that most reviews don't seem to get any votes
at all. With this anonymous voting we can do some sort of
scoring/ordering even with potentially less reliable votes.

10 files changed, 20 insertions, 20 deletions

diff --git a/data/style.css b/data/style.css
index 240981e3..a9c673b6 100644
--- a/data/style.css
+++ b/data/style.css
@@ -633,7 +633,7 @@ div.producerbrowse    { padding-bottom: 10px }
 .reviewlist td.tc2 { width: 110px; }
 .reviewlist td.tc3 { width: 50px; text-align: right }
 .reviewlist td.tc4 { width: 50px }
-.reviewlist td.tc6 { width: 80px }
+.reviewlist td.tc6 { width: 140px }
 .reviewlist td.tc7 { width: 30px; text-align: right }
 .reviewlist td.tc8 { width: 250px; text-align: right }
 
diff --git a/lib/VNWeb/Reviews/Elm.pm b/lib/VNWeb/Reviews/Elm.pm
index 385c8b0f..9b520501 100644
--- a/lib/VNWeb/Reviews/Elm.pm
+++ b/lib/VNWeb/Reviews/Elm.pm
@@ -13,13 +13,12 @@ my  $VOTE_IN  = form_compile in  => $VOTE;
 our $VOTE_OUT = form_compile out => $VOTE;
 
 elm_api ReviewsVote => $VOTE_OUT, $VOTE_IN, sub {
-    return elm_Unauth if !auth;
     my($data) = @_;
-    my %id = (uid => auth->uid, id => $data->{id});
+    my %id = (auth ? (uid => auth->uid) : (ip => tuwf->reqIP()), id => $data->{id});
     my %val = (vote => $data->{my}?1:0, overrule => auth->permBoardmod ? $data->{overrule}?1:0 : 0, date => sql 'NOW()');
     tuwf->dbExeci(
         defined $data->{my}
-        ? sql 'INSERT INTO reviews_votes', {%id,%val}, 'ON CONFLICT (id,uid) DO UPDATE SET', \%val
+        ? sql 'INSERT INTO reviews_votes', {%id,%val}, 'ON CONFLICT (id,', auth ? 'uid' : 'ip', ') DO UPDATE SET', \%val
         : sql 'DELETE FROM reviews_votes WHERE', \%id
     );
     elm_Success
diff --git a/lib/VNWeb/Reviews/Lib.pm b/lib/VNWeb/Reviews/Lib.pm
index 2872966c..1f7c6e4e 100644
--- a/lib/VNWeb/Reviews/Lib.pm
+++ b/lib/VNWeb/Reviews/Lib.pm
@@ -7,8 +7,8 @@ our @EXPORT = qw/reviews_vote_ reviews_format/;
 sub reviews_vote_ {
     my($w) = @_;
     span_ sub {
-        elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, {%$w, mod => auth->permBoardmod} if auth && ($w->{can} || auth->permBoardmod);
-        b_ class => 'grayedout', sprintf ' %d/%d', $w->{c_up}, $w->{c_down} if auth->permBoardmod;
+        elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, {%$w, mod => auth->permBoardmod||0} if $w->{can} || auth->permBoardmod;
+        b_ class => 'grayedout', sprintf ' %.2f/%.2f', $w->{c_up}/100, $w->{c_down}/100 if auth->permBoardmod;
     }
 }
 
diff --git a/lib/VNWeb/Reviews/List.pm b/lib/VNWeb/Reviews/List.pm
index 94c65625..eed0abf9 100644
--- a/lib/VNWeb/Reviews/List.pm
+++ b/lib/VNWeb/Reviews/List.pm
@@ -27,7 +27,7 @@ sub tablebox_ {
                 td_ class => 'tc3', fmtvote $_->{vote};
                 td_ class => 'tc4', $_->{isfull} ? 'Full' : 'Mini';
                 td_ class => 'tc5', sub { a_ href => "/$_->{id}", $_->{title}; b_ class => 'grayedout', ' (flagged)' if $_->{c_flagged} };
-                td_ class => 'tc6', sprintf '👍 %d 👎 %d', $_->{c_up}, $_->{c_down} if auth->isMod;
+                td_ class => 'tc6', sprintf '👍 %.2f 👎 %.2f', $_->{c_up}/100, $_->{c_down}/100 if auth->isMod;
                 td_ class => 'tc7', $_->{c_count};
                 td_ class => 'tc8', $_->{c_lastnum} ? sub {
                     user_ $_, 'lu_';
diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm
index 7673996a..d70a612f 100644
--- a/lib/VNWeb/Reviews/Page.pm
+++ b/lib/VNWeb/Reviews/Page.pm
@@ -94,7 +94,7 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
            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(*) 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, '
+           LEFT JOIN reviews_votes rv ON rv.id = r.id AND', auth ? ('rv.uid =', \auth->uid) : ('rv.ip =', \tuwf->reqIP()), '
            LEFT JOIN reviews rm ON rm.vid = r.vid AND rm.uid =', \auth->uid, '
           WHERE r.id =', \$id
     );
diff --git a/lib/VNWeb/Reviews/VNTab.pm b/lib/VNWeb/Reviews/VNTab.pm
index 8b372e5a..30408299 100644
--- a/lib/VNWeb/Reviews/VNTab.pm
+++ b/lib/VNWeb/Reviews/VNTab.pm
@@ -14,7 +14,7 @@ sub reviews_ {
            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 reviews_votes rv ON rv.uid =', \auth->uid, ' AND rv.id = r.id
+           LEFT JOIN reviews_votes rv ON rv.id = r.id AND', auth ? ('rv.uid =', \auth->uid) : ('rv.ip =', \tuwf->reqIP()), '
            LEFT JOIN reviews rm ON rm.vid = r.vid AND rm.uid =', \auth->uid, '
           WhERE NOT r.c_flagged AND r.vid =', \$v->{id}, 'AND', ($mini ? 'NOT' : ''), 'r.isfull
           ORDER BY r.c_up-r.c_down DESC'
diff --git a/sql/func.sql b/sql/func.sql
index 30e9be4d..0d86dcd9 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -188,15 +188,10 @@ END; $$ LANGUAGE plpgsql;
 -- Update reviews.c_up, c_down and c_flagged
 CREATE OR REPLACE FUNCTION update_reviews_votes_cache(vndbid) RETURNS void AS $$
 BEGIN
-  WITH stats(id,up,down,flag) AS (
+  WITH stats(id,up,down) AS (
     SELECT r.id
-         , COUNT(*) FILTER(WHERE rv.vote AND NOT u.ign_votes AND r2.id IS NULL)
-         , COUNT(*) FILTER(WHERE NOT rv.vote AND NOT u.ign_votes AND r2.id IS NULL)
-           -- flag score = up-down < -10, overrule votes count for 10000 (this algorithm is subject to tuning)
-         , COALESCE(
-            SUM((CASE WHEN rv.vote THEN 1 ELSE -1 END)*(CASE WHEN rv.overrule THEN 10000 ELSE 1 END))
-            FILTER(WHERE NOT u.ign_votes AND (r2.id IS NULL OR rv.overrule)),
-           0) < -1000
+         , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE     rv.vote AND NOT u.ign_votes AND r2.id IS NULL), 0)
+         , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE NOT rv.vote AND NOT u.ign_votes AND r2.id IS NULL), 0)
       FROM reviews r
       LEFT JOIN reviews_votes rv ON rv.id = r.id
       LEFT JOIN users u ON u.id = rv.uid
@@ -204,8 +199,8 @@ BEGIN
      WHERE $1 IS NULL OR r.id = $1
      GROUP BY r.id
   )
-  UPDATE reviews SET c_up = up, c_down = down, c_flagged = flag
-    FROM stats WHERE reviews.id = stats.id AND (reviews.c_up,reviews.c_down,reviews.c_flagged) <> (stats.up,stats.down,stats.flag);
+  UPDATE reviews SET c_up = up, c_down = down, c_flagged = up-down<-10000
+    FROM stats WHERE reviews.id = stats.id AND (c_up,c_down,c_flagged) <> (up,down,up-down<10000);
 END; $$ LANGUAGE plpgsql;
 
 
diff --git a/sql/schema.sql b/sql/schema.sql
index 934a3de9..046bb833 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -546,7 +546,8 @@ CREATE TABLE reviews_votes (
   uid     int,
   date    timestamptz NOT NULL,
   vote    boolean NOT NULL, -- true = upvote, false = downvote
-  overrule boolean NOT NULL DEFAULT false
+  overrule boolean NOT NULL DEFAULT false,
+  ip      inet -- Only for anonymous votes
 );
 
 -- rlists
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 8e408b92..829640d8 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -128,6 +128,7 @@ CREATE UNIQUE INDEX reviews_vid_uid        ON reviews (vid,uid);
 CREATE        INDEX reviews_uid            ON reviews (uid);
 CREATE        INDEX reviews_posts_uid      ON reviews_posts (uid);
 CREATE UNIQUE INDEX reviews_votes_id_uid   ON reviews_votes (id,uid);
+CREATE UNIQUE INDEX reviews_votes_id_ip    ON reviews_votes (id,ip);
 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/2020-10-15-reviews-anonymous-votes.sql b/util/updates/2020-10-15-reviews-anonymous-votes.sql
new file mode 100644
index 00000000..721543f6
--- /dev/null
+++ b/util/updates/2020-10-15-reviews-anonymous-votes.sql
@@ -0,0 +1,4 @@
+ALTER TABLE reviews_votes ADD COLUMN ip      inet;
+CREATE UNIQUE INDEX reviews_votes_id_ip    ON reviews_votes (id,ip);
+\i sql/func.sql
+SELECT update_reviews_votes_cache(id) FROM reviews;