imgflag for public: Add link, bias weights to chars, show vote stats

4 files changed, 23 insertions, 7 deletions

diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index 9ab7630d..69413cea 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -233,6 +233,9 @@ sub _menu_ {
             a_ href => "$uid/hist", 'My Recent Changes'; br_;
             a_ href => '/g/links?u='.auth->uid, 'My Tags'; br_;
             br_;
+            if(auth->permImgvote) {
+                a_ href => '/img/vote', 'Image Flagging'; br_;
+            }
             if(auth->permEdit) {
                 a_ href => '/v/add', 'Add Visual Novel'; br_;
                 a_ href => '/p/add', 'Add Producer'; br_;
diff --git a/lib/VNWeb/User/List.pm b/lib/VNWeb/User/List.pm
index 7d5311a2..bd05abe3 100644
--- a/lib/VNWeb/User/List.pm
+++ b/lib/VNWeb/User/List.pm
@@ -19,6 +19,7 @@ sub listing_ {
                 td_ class => 'tc5', sub { txt_ 'Wishlist';   sortable_ 'wish',       $opt, \&url };
                 td_ class => 'tc6', sub { txt_ 'Edits';      sortable_ 'changes',    $opt, \&url };
                 td_ class => 'tc7', sub { txt_ 'Tags';       sortable_ 'tags',       $opt, \&url };
+                td_ class => 'tc8', sub { txt_ 'Images';     sortable_ 'images',     $opt, \&url };
             } };
             tr_ sub {
                 my $l = $_;
@@ -44,6 +45,7 @@ sub listing_ {
                     txt_ '-' if !$l->{c_tags};
                     a_ href => "/g/links?u=$l->{user_id}", $l->{c_tags} if $l->{c_tags};
                 };
+                td_ class => 'tc8', $l->{c_imgvotes}||'-';
             } for @$list;
         };
     };
@@ -56,7 +58,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
 
     my $opt = tuwf->validate(get =>
         p => { upage => 1 },
-        s => { onerror => 'registered', enum => [qw[username registered vns votes wish changes tags]] },
+        s => { onerror => 'registered', enum => [qw[username registered vns votes wish changes tags images]] },
         o => { onerror => 'd',          enum => [qw[a d]] },
         q => { onerror => '' },
     )->data;
@@ -70,7 +72,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
     );
 
     my $list = tuwf->dbPagei({ results => 50, page => $opt->{p} },
-        'SELECT', sql_user(), ',', sql_totime('registered'), 'as registered, c_vns, c_votes, c_wish, c_changes, c_tags
+        'SELECT', sql_user(), ',', sql_totime('registered'), 'as registered, c_vns, c_votes, c_wish, c_changes, c_tags, c_imgvotes
            FROM users u
           WHERE', sql_and('id > 0', @where),
          'ORDER BY', {
@@ -80,7 +82,8 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
                   votes      => 'c_votes',
                   wish       => 'c_wish',
                   changes    => 'c_changes',
-                  tags       => 'c_tags'
+                  tags       => 'c_tags',
+                  images     => 'c_imgvotes',
                 }->{$opt->{s}}, $opt->{o} eq 'd' ? 'DESC' : 'ASC'
     );
     my $count = @where ? tuwf->dbVali('SELECT count(*) FROM users WHERE', sql_and @where) : tuwf->{stats}{users};
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm
index a1d86c58..acf5d773 100644
--- a/lib/VNWeb/User/Page.pm
+++ b/lib/VNWeb/User/Page.pm
@@ -77,6 +77,10 @@ sub _info_table_ {
         };
     };
     tr_ sub {
+        td_ 'Images';
+        td_ sprintf '%d images flagged.', $u->{c_imgvotes};
+    } if $u->{c_imgvotes};
+    tr_ sub {
         my $stats = tuwf->dbRowi('SELECT COUNT(*) AS posts, COUNT(*) FILTER (WHERE num = 1) AS threads FROM threads_posts WHERE uid =', \$u->{id});
         td_ 'Forum stats';
         td_ !$stats->{posts} ? '-' : sub {
@@ -140,7 +144,7 @@ sub _votestats_ {
 
 TUWF::get qr{/$RE{uid}}, sub {
     my $u = tuwf->dbRowi(q{
-        SELECT id, c_changes, c_votes, c_tags
+        SELECT id, c_changes, c_votes, c_tags, c_imgvotes
              ,}, sql_totime('registered'), q{ AS registered
              ,}, sql_user(), q{
           FROM users u
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 77d4451e..f7eeccc1 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -151,13 +151,16 @@ $$ LANGUAGE SQL;
 --
 --   votes_weight = max(0, 10 - c_votecount)/10   -> linear weight between 0..1, 0 being OK and 1 being BAD
 --   *_stddev_weight = *_stddev/1.5               -> ^
---   weight = 1 + votes_weight*100 + sexual_stddev_weight*100 + violence_stddev_weight*100
+--   weight = min(1, votes_weight*100 + sexual_stddev_weight*100 + violence_stddev_weight*100)
 --
---   Extremes: 1 .. 301, easier to tune and reason about, but still linear
+--   Extremes: 1 .. 300, easier to tune and reason about, but still linear
 --
 -- Neither of those solutions are grounded in theory, I've no clue how
 -- statistics work. I suspect confidence intervals/levels are more appropriate
 -- for this use case.
+--
+-- Non-'ch' image weights are currently reduced to 20% in order to prioritize
+-- character images.
 CREATE OR REPLACE FUNCTION update_images_cache(image_id) RETURNS void AS $$
 BEGIN
   -- Have to dynamically construct the query here, a
@@ -169,7 +172,10 @@ BEGIN
     FROM (
       SELECT s.*,
              CASE WHEN x.id IS NULL THEN 0
-             ELSE 1 + (greatest(0, 10.0 - s.votecount)/10)*100 + coalesce(s.sexual_stddev/1.5, 0)*100 + coalesce(s.violence_stddev/1.5, 0)*100
+             ELSE greatest(1,
+                    ((greatest(0, 10.0 - s.votecount)/10)*100 + coalesce(s.sexual_stddev/1.5, 0)*100 + coalesce(s.violence_stddev/1.5, 0)*100)
+                    * (CASE WHEN (x.id).itype = 'ch' THEN 1 ELSE 0.2 END)
+                  )
              END AS weight
         FROM (
             SELECT i.id, count(iv.id) AS votecount