VNLengthVote: Add support for multiple release selection

Using a Postgres array for this is ugly, but releases are purely
informative so no strong need for referential integrity.

Also, display languages in length vote listing.

2 files changed, 14 insertions, 5 deletions

diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
index eeff981f..e64a620a 100644
--- a/lib/VNWeb/VN/Length.pm
+++ b/lib/VNWeb/VN/Length.pm
@@ -52,7 +52,11 @@ sub listing_ {
                 } if $mode ne 'v';
                 td_ class => 'tc3'.($_->{ignore}?' grayedout':''), sub { vnlength_ $_->{length} };
                 td_ class => 'tc4'.($_->{ignore}?' grayedout':''), ['Slow','Normal','Fast']->[$_->{speed}];
-                td_ class => 'tc5', sub { a_ href => "/$_->{rid}", $_->{rid} };
+                td_ class => 'tc5', sub {
+                    my %l = map +($_,1), map $_->{lang}->@*, $_->{rel}->@*;
+                    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', sub { lit_ bb_format $_->{notes}, inline => 1 };
                 td_ class => 'tc7', sub {
                     select_ name => "$_->{vid}-$_->{uid}", sub {
@@ -119,7 +123,8 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub {
     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.uid, l.vid, l.length, l.speed, l.notes, l.rid, ', sql_totime('l.date'), 'AS date, l.ignore OR u.perm_lengthvote IS NOT DISTINCT FROM false AS ignore',
+      'SELECT l.uid, l.vid, l.length, l.speed, l.notes, l.rid::text[] AS rel, '
+            , sql_totime('l.date'), 'AS date, l.ignore OR u.perm_lengthvote IS NOT DISTINCT FROM false AS ignore',
               $mode ne 'u' ? (', ', sql_user()) : (),
               $mode ne 'v' ? ', v.title, v.original' : (), '
          FROM vn_length_votes l
@@ -128,6 +133,8 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub {
        'WHERE', $where,
        'ORDER BY', $opt->{s}->sql_order(),
     );
+    $_->{rel} = [ map +{ id => $_ }, $_->{rel}->@* ] for @$lst;
+    enrich_flatten lang => id => id => 'SELECT id, lang FROM releases_lang WHERE id IN', map $_->{rel}, @$lst;
 
     my $title = 'Length votes'.($mode ? ($mode eq 'v' ? ' for ' : ' by ').$o->{title} : '');
     framework_ title => $title, dbobj => $o, sub {
@@ -166,7 +173,7 @@ our $LENGTHVOTE = form_compile any => {
     uid    => { vndbid => 'u' },
     vid    => { vndbid => 'v' },
     vote   => { type => 'hash', required => 0, keys => {
-        rid    => { vndbid => 'r' },
+        rid    => { type => 'array', minlength => 1, values => { vndbid => 'r' } },
         length => { uint => 1, range => [1,32767] },
         speed  => { uint => 1, enum => [0,1,2] },
         notes  => { required => 0, default => '' },
@@ -178,6 +185,7 @@ elm_api VNLengthVote => undef, $LENGTHVOTE, sub {
     return elm_Unauth if !can_vote() || $data->{uid} ne auth->uid;
     my %where = ( uid => $data->{uid}, vid => $data->{vid} );
     tuwf->dbExeci('DELETE FROM vn_length_votes WHERE', \%where) if !$data->{vote};
+    $data->{vote}{rid} = sql sql_array($data->{vote}{rid}->@*), '::vndbid[]' if $data->{vote};
     tuwf->dbExeci(
         'INSERT INTO vn_length_votes', { %where, $data->{vote}->%* },
         'ON CONFLICT (uid, vid) DO UPDATE SET', $data->{vote}
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 78f93dc8..60876e1e 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -161,13 +161,14 @@ sub infobox_length_ {
 
     my $stats = tuwf->dbRowi('
         SELECT count(*) as count
-             , percentile_cont(0.5) WITHIN GROUP (ORDER BY l.length + (l.length/4 * (l.speed-1))) AS median
+             , percentile_cont(', \0.5, ') WITHIN GROUP (ORDER BY l.length + (l.length/(1+1+1+1) * (l.speed-1))) 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 NOT l.ignore AND l.vid =', \$v->{id});
     return if !$v->{length} && !$stats->{count} && !VNWeb::VN::Length::can_vote();
 
-    my $my = VNWeb::VN::Length::can_vote() && tuwf->dbRowi('SELECT rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
+    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);
 
     tr_ sub {
         td_ 'Play time';