VN length voting: support private votes

The database model supports private votes with a proper speed setting,
but that is currently not possible through the UI.

9 files changed, 39 insertions, 18 deletions

diff --git a/elm/VNLengthVote.elm b/elm/VNLengthVote.elm
index 8ee576d2..3d2354a8 100644
--- a/elm/VNLengthVote.elm
+++ b/elm/VNLengthVote.elm
@@ -51,7 +51,7 @@ init f =
   , defrid  = ""
   , hours   = Maybe.map (\v -> v.length // 60   ) f.vote
   , minutes = Maybe.andThen (\v -> let n = modBy 60 v.length in if n == 0 then Nothing else Just n) f.vote
-  , speed   = Maybe.map (\v -> v.speed)  f.vote |> Maybe.withDefault (Just 9)
+  , speed   = Maybe.map (\v -> if v.private then Just 8 else v.speed) f.vote |> Maybe.withDefault (Just 9)
   , length  = Maybe.map (\v -> v.length) f.vote |> Maybe.withDefault 0
   , notes   = Maybe.map (\v -> v.notes)  f.vote |> Maybe.withDefault ""
   , rels    = Nothing
@@ -64,7 +64,13 @@ encode : Model -> GV.Send
 encode m =
   { uid = m.uid
   , vid = m.vid
-  , vote = if enclen m == 0 then Nothing else Just { rid = m.rid, notes = m.notes, speed = m.speed, length = enclen m }
+  , vote = if enclen m == 0 then Nothing else Just
+      { rid     = m.rid
+      , notes   = m.notes
+      , speed   = if m.speed == Just 8 then Nothing else m.speed
+      , length  = enclen m
+      , private = m.speed == Just 8
+      }
   }
 
 type Msg
@@ -151,10 +157,16 @@ view model = div [class "lengthvotefrm"] <|
         , (Just 0, "Slow (e.g. low language proficiency or extra time spent on gameplay)")
         , (Just 1, "Normal (no content skipped, all voices listened to end)")
         , (Just 2, "Fast (e.g. fast reader or skipping through voices and gameplay)")
-        , (Nothing, "Don't count my play time")
+        , (Nothing, "Don't count my play time (public)")
+        , (Just 8, "Don't count my play time (private)")
         ]
       , case model.speed of
           Just 9 -> span [] []
+          Just 8 -> span []
+            [ text "Your play time is not counted towards the VN's average and is not visible in the listings."
+            , text " It is only saved for your own administration and counted towards the personal play time displayed on your profile."
+            , br [] []
+            ]
           Nothing -> span []
             [ text "Your play time is not counted towards the VN's average, but is still visible in the listings and saved for your own administration."
             , br [] []
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm
index 43d4dd8e..7d418a27 100644
--- a/lib/VNWeb/User/Page.pm
+++ b/lib/VNWeb/User/Page.pm
@@ -53,13 +53,13 @@ sub _info_table_ {
             a_ href => "/$u->{id}/ulist?votes=1", 'Browse votes »';
         }
     };
-    my $lengthvotes = tuwf->dbRowi('SELECT count(*) AS count, sum(length) AS sum FROM vn_length_votes WHERE uid =', \$u->{id});
+    my $lengthvotes = tuwf->dbRowi('SELECT count(*) AS count, sum(length) AS sum, bool_or(not private) as haspub FROM vn_length_votes WHERE uid =', \$u->{id});
     tr_ sub {
         td_ 'Play times';
         td_ sub {
             vnlength_ $lengthvotes->{sum};
             txt_ sprintf ' from %d submitted play times. ', $lengthvotes->{count};
-            a_ href => "/$u->{id}/lengthvotes", 'Browse votes »';
+            a_ href => "/$u->{id}/lengthvotes", 'Browse votes »' if $own || $lengthvotes->{haspub};
         };
     } if $lengthvotes->{count};
     tr_ sub {
diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
index 362ae2b5..bf39018e 100644
--- a/lib/VNWeb/VN/Length.pm
+++ b/lib/VNWeb/VN/Length.pm
@@ -55,7 +55,10 @@ sub listing_ {
                     abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' for sort keys %l;
                     join_ ',', sub { a_ href => "/$_->{id}", $_->{id} }, sort { idcmp $a->{id}, $b->{id} } $_->{rel}->@*;
                 };
-                td_ class => 'tc6'.($_->{ignore}?' grayedout':''), sub { lit_ bb_format $_->{notes}, inline => 1 };
+                td_ class => 'tc6'.($_->{ignore}?' grayedout':''), sub {
+                    b_ class => 'grayedout', '(private) ' if $_->{private};
+                    lit_ bb_format $_->{notes}, inline => 1;
+                };
                 td_ class => 'tc7', sub {
                     select_ name => "lv$_->{id}", sub {
                         option_ value => '', '--';
@@ -82,7 +85,7 @@ sub stats_ {
              , percentile_cont(', \0.5, ') WITHIN GROUP (ORDER BY l.length) AS median
           FROM vn_length_votes l
           LEFT JOIN users u ON u.id = l.uid
-         WHERE u.perm_lengthvote IS DISTINCT FROM false AND l.speed IS NOT NULL AND l.vid =', \$o->{id}, '
+         WHERE u.perm_lengthvote IS DISTINCT FROM false AND l.speed IS NOT NULL AND NOT l.private AND l.vid =', \$o->{id}, '
          GROUP BY GROUPING SETS ((speed),()) ORDER BY speed'
     );
     return if !$stats->[0]{count};
@@ -122,11 +125,12 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub {
 
     my $where = sql_and
         $mode ? sql($mode eq 'v' ? 'l.vid =' : 'l.uid =', \$o->{id}) : (),
+        $mode eq 'u' && auth && $o->{id} eq auth->uid ? () : 'NOT l.private',
         defined $opt->{ign} ? sql('l.speed IS', $opt->{ign} ? 'NULL' : 'NOT NULL') : ();
     my $count = tuwf->dbVali('SELECT COUNT(*) FROM vn_length_votes l WHERE', $where);
 
     my $lst = tuwf->dbPagei({results => $opt->{s}->results, page => $opt->{p}},
-      'SELECT l.id, l.uid, l.vid, l.length, l.speed, l.notes, l.rid::text[] AS rel, '
+      'SELECT l.id, l.uid, l.vid, l.length, l.speed, l.notes, l.private, l.rid::text[] AS rel, '
             , sql_totime('l.date'), 'AS date, u.perm_lengthvote IS NOT DISTINCT FROM false AS ignore',
               $mode ne 'u' ? (', ', sql_user()) : (),
               $mode ne 'v' ? ', v.title, v.alttitle' : (), '
@@ -184,10 +188,11 @@ our $LENGTHVOTE = form_compile any => {
     uid    => { vndbid => 'u' },
     vid    => { vndbid => 'v' },
     vote   => { type => 'hash', required => 0, keys => {
-        rid    => { type => 'array', minlength => 1, values => { vndbid => 'r' } },
-        length => { uint => 1, range => [1,26159] }, # 435h59m, largest round-ish number where the 'fast' speed adjustment doesn't overflow a smallint
-        speed  => { required => 0, uint => 1, enum => [0,1,2] },
-        notes  => { required => 0, default => '' },
+        rid     => { type => 'array', minlength => 1, values => { vndbid => 'r' } },
+        length  => { uint => 1, range => [1,26159] }, # 435h59m, largest round-ish number where the 'fast' speed adjustment doesn't overflow a smallint
+        speed   => { required => 0, uint => 1, enum => [0,1,2] },
+        private => { anybool => 1 },
+        notes   => { required => 0, default => '' },
     } },
 };
 
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 9b3f8137..ee53b079 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -167,7 +167,7 @@ sub infobox_length_ {
     return if !$v->{c_length} && !$v->{c_lengthnum} && !VNWeb::VN::Length::can_vote();
 
     my $my = VNWeb::VN::Length::can_vote()
-        && tuwf->dbRowi('SELECT rid::text[] AS rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
+        && tuwf->dbRowi('SELECT rid::text[] AS rid, length, speed, private, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
 
     tr_ sub {
         td_ 'Play time';
diff --git a/sql/func.sql b/sql/func.sql
index 53d8ce8f..8f9a1c1b 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -239,7 +239,7 @@ CREATE OR REPLACE FUNCTION update_vn_length_cache(vndbid) RETURNS void AS $$
     SELECT v.id, count(l.vid) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL)
          , percentile_cont(0.5) WITHIN GROUP (ORDER BY l.length + (l.length/4 * (l.speed-1))) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL)
       FROM vn v
-      LEFT JOIN vn_length_votes l ON l.vid = v.id AND l.speed IS NOT NULL
+      LEFT JOIN vn_length_votes l ON l.vid = v.id AND l.speed IS NOT NULL AND NOT l.private
       LEFT JOIN users u ON u.id = l.uid AND u.perm_lengthvote
      WHERE ($1 IS NULL OR v.id = $1)
      GROUP BY v.id
diff --git a/sql/schema.sql b/sql/schema.sql
index c6019b72..5719d6e7 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1206,7 +1206,8 @@ CREATE TABLE vn_length_votes (
   speed      smallint, -- [pub] NULL=uncounted/ignored, 0=slow, 1=normal, 2=fast
   uid        vndbid, -- [pub]
   rid        vndbid[] NOT NULL, -- [pub]
-  notes      text NOT NULL DEFAULT '' -- [pub]
+  notes      text NOT NULL DEFAULT '', -- [pub]
+  private    boolean NOT NULL
 );
 
 -- wikidata
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 5488d6e4..5adf35de 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -98,7 +98,7 @@ my %tables = (
     users               => { where => 'id IN(SELECT DISTINCT uvl.uid FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE NOT ul.private)'
                                  .' OR id IN(SELECT DISTINCT uid FROM tags_vn)'
                                  .' OR id IN(SELECT DISTINCT uid FROM image_votes)'
-                                 .' OR id IN(SELECT DISTINCT uid FROM vn_length_votes)' },
+                                 .' OR id IN(SELECT DISTINCT uid FROM vn_length_votes WHERE NOT private)' },
     vn                  => { where => 'NOT hidden' },
     vn_anime            => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
     vn_relations        => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
@@ -108,7 +108,7 @@ my %tables = (
                                 .' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' },
     vn_staff            => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' },
     vn_titles           => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
-    vn_length_votes     => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
+    vn_length_votes     => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden) AND NOT private'
                            , order => 'vid, uid' },
     wikidata            => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden
                                         UNION SELECT l_wikidata FROM staff WHERE NOT hidden
diff --git a/util/devdump.pl b/util/devdump.pl
index 9d801912..a36d0184 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -160,7 +160,7 @@ sub copy_entry {
     copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots vn_titles/], \@vids;
 
     # VN-related niceties
-    copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE vid IN($vids)", {uid => 'user'};
+    copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE NOT private AND vid IN($vids)", {uid => 'user'};
     copy tags_vn     => "SELECT DISTINCT ON (tag,vid,vndbid_num(uid)%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'};
     copy quotes      => "SELECT * FROM quotes WHERE vid IN($vids)";
     my $votes = "SELECT vid, vndbid('u', vndbid_num(uid)%8+2) AS uid, (percentile_cont((vndbid_num(uid)%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(vote_date) AS vote_date"
diff --git a/util/updates/2022-03-29-lengthvotes-private.sql b/util/updates/2022-03-29-lengthvotes-private.sql
new file mode 100644
index 00000000..5c721818
--- /dev/null
+++ b/util/updates/2022-03-29-lengthvotes-private.sql
@@ -0,0 +1,3 @@
+ALTER TABLE vn_length_votes ADD COLUMN private boolean NOT NULL DEFAULT FALSE;
+ALTER TABLE vn_length_votes ALTER COLUMN private DROP DEFAULT;
+\i sql/func.sql