Made a start on the notification system

The current setup should be able to handle all kinds of notifications,
though only PMs are implemented at this point. More to come.

6 files changed, 177 insertions, 38 deletions

diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 1c27c22e..9da25384 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -5,7 +5,7 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount dbPostRead|;
+our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount|;
 
 
 # Options: id, type, iid, results, page, what, notusers, sort, reverse
@@ -252,15 +252,5 @@ sub dbPostAdd {
 }
 
 
-sub dbPostRead { # thread id, user id, last post number
-  my($s, $tid, $uid, $num) = @_;
-  $s->dbExec(q|
-    UPDATE threads_boards
-      SET lastread = ?
-      WHERE tid = ? AND type = 'u' AND iid = ?|,
-    $num, $tid, $uid);
-}
-
-
 1;
 
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 36f589ac..10c90cf6 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -5,11 +5,14 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|;
+our @EXPORT = qw|
+  dbUserGet dbUserEdit dbUserAdd dbUserDel dbSessionAdd dbSessionDel
+  dbNotifyGet dbNotifyMarkRead dbNotifyRemove
+|;
 
 
 # %options->{ username passwd mail session uid ip registered search results page what sort reverse }
-# what: stats extended
+# what: notifycount stats extended
 # sort: username registered votes changes tags
 sub dbUserGet {
   my $s = shift;
@@ -53,6 +56,8 @@ sub dbUserGet {
       qw|mail rank salt skin customcss show_nsfw ign_votes|,
       q|encode(passwd, 'hex') AS passwd|
     ) : (),
+    $o{what} =~ /notifycount/ ?
+      '(SELECT COUNT(*) FROM notifications WHERE uid = u.id AND read IS NULL) AS notifycount' : (),
     $o{what} =~ /stats/ ? (
       '(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount',
       '(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount',
@@ -129,20 +134,6 @@ sub dbUserDel {
 }
 
 
-# Returns number of unread messages
-sub dbUserMessageCount { # uid
-  my($s, $uid) = @_;
-  return $s->dbRow(q{
-    SELECT SUM(tbi.count) AS cnt FROM (
-      SELECT t.count-COALESCE(tb.lastread,0)
-        FROM threads_boards tb
-        JOIN threads t ON t.id = tb.tid AND NOT t.hidden
-       WHERE tb.type = 'u' AND tb.iid = ?
-    ) AS tbi (count)
-  }, $uid)->{cnt}||0;
-}
-
-
 # Adds a session to the database
 # If no expiration is supplied the database default is used
 # uid, 40 character session token, expiration time (timestamp)
@@ -164,5 +155,64 @@ sub dbSessionDel {
 }
 
 
+# %options->{ uid id what results page }
+# what: titles
+sub dbNotifyGet {
+  my($s, %o) = @_;
+  $o{what} ||= '';
+  $o{results} ||= 10;
+  $o{page} ||= 1;
+
+  my %where = (
+    'n.uid = ?' => $o{uid},
+    $o{id} ? (
+      'n.id = ?' => $o{id} ) : (),
+    defined($o{read}) ? (
+      'n.read !s' => $o{read} ? 'IS NOT NULL' : 'IS NULL' ) : (),
+  );
+
+  my @join = (
+    $o{what} =~ /titles/ ? (
+      q|LEFT JOIN threads t ON n.ltype = 't' AND t.id = n.iid|,
+      q|LEFT JOIN threads_posts tp ON n.ltype = 't' AND tp.tid = t.id AND n.subid = tp.num|,
+      q|LEFT JOIN users tu ON tp.uid = tu.id|
+    ) : ()
+  );
+
+  my @select = (
+    qw|n.id n.ntype n.ltype n.iid n.subid|,
+    q|extract('epoch' from n.date) as date|,
+    q|extract('epoch' from n.read) as read|,
+    $o{what} =~ /titles/ ? (
+      q|COALESCE(t.title,'') AS title|,
+      q|COALESCE(tu.username,'') AS subtitle|,
+    ) : (),
+  );
+
+  my($r, $np) = $s->dbPage(\%o, q|
+    SELECT !s
+      FROM notifications n
+      !s
+      !W
+      ORDER BY n.id
+  |, join(', ', @select), join(' ', @join), \%where);
+  return wantarray ? ($r, $np) : $r;
+}
+
+
+# ids
+sub dbNotifyMarkRead {
+  my $s = shift;
+  $s->dbExec('UPDATE notifications SET read = NOW() WHERE id IN(!l)', \@_);
+}
+
+
+# ids
+sub dbNotifyRemove {
+  my $s = shift;
+  $s->dbExec('DELETE FROM notifications WHERE id IN(!l)', \@_);
+}
+
+
 1;
 
diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm
index fc477de2..c8431c2c 100644
--- a/lib/VNDB/Handler/Discussions.pm
+++ b/lib/VNDB/Handler/Discussions.pm
@@ -29,13 +29,7 @@ sub thread {
   my $p = $self->dbPostGet(tid => $tid, results => 25, page => $page, what => 'user');
   return 404 if !$p->[0];
 
-  # mark as read when this thread is posted in the board of the currently logged in user
-  my $uid = $self->authInfo->{id};
-  $self->dbPostRead($t->{id}, $uid, $p->[$#$p]{num})
-    if $uid && grep $_->{type} eq 'u' && $_->{iid} == $uid, @{$t->{boards}};
-
   $self->htmlHeader(title => $t->{title});
-
   div class => 'mainbox';
    h1 $t->{title};
    h2 mt '_thread_postedin';
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index 75ce411e..d31d176b 100644
--- a/lib/VNDB/Handler/Users.pm
+++ b/lib/VNDB/Handler/Users.pm
@@ -3,7 +3,7 @@ package VNDB::Handler::Users;
 
 use strict;
 use warnings;
-use YAWF ':html';
+use YAWF ':html', 'xml_escape';
 use VNDB::Func;
 
 
@@ -18,6 +18,8 @@ YAWF::register(
   qr{u([1-9]\d*)/posts}       => \&posts,
   qr{u([1-9]\d*)/del(/[od])?} => \&delete,
   qr{u/(all|[0a-z])}          => \&list,
+  qr{u([1-9]\d*)/notifies}    => \&notifies,
+  qr{u([1-9]\d*)/notify/([1-9]\d*)} => \&readnotify,
 );
 
 
@@ -519,5 +521,108 @@ sub list {
 }
 
 
+sub notifies {
+  my($self, $uid) = @_;
+  return $self->htmlDenied if !$self->authInfo->{id} || $uid != $self->authInfo->{id};
+
+  my $u = $self->dbUserGet(uid => $uid)->[0];
+
+  my $f = $self->formValidate(
+    { name => 'p', required => 0, default => 1, template => 'int' },
+    { name => 'r', required => 0, default => 0, enum => [0,1] },
+  );
+  return 404 if $f->{_err};
+
+  if($self->reqMethod() eq 'POST') {
+    my $frm = $self->formValidate(
+      { name => 'notifysel', multi => 1, required => 0, template => 'int' },
+      { name => 'markread', required => 0 },
+      { name => 'remove', required => 0 }
+    );
+    return 404 if $frm->{_err};
+    my @ids = grep $_, @{$frm->{notifysel}};
+    $self->dbNotifyMarkRead(@ids) if @ids && $frm->{markread};
+    $self->dbNotifyRemove(@ids) if @ids && $frm->{remove};
+  }
+
+  my($list, $np) = $self->dbNotifyGet(
+    uid => $uid,
+    page => $f->{p},
+    results => 25,
+    what => 'titles',
+    read => $f->{r} == 1 ? undef : 0,
+  );
+
+  $self->htmlHeader(title => mt('_usern_title'), noindex => 1);
+  $self->htmlMainTabs(u => $u);
+  div class => 'mainbox';
+   h1 mt '_usern_title';
+   p class => 'browseopts';
+    a !$f->{r} ? (class => 'optselected') : (), href => "/u$uid/notifies?r=0", mt '_usern_o_unread';
+    a  $f->{r} ? (class => 'optselected') : (), href => "/u$uid/notifies?r=1", mt '_usern_o_alsoread';
+   end;
+   p mt '_usern_nonotifies' if !@$list;
+  end;
+
+  if(@$list) {
+    form action => "/u$uid/notifies?r=$f->{r}", method => 'post';
+    $self->htmlBrowse(
+      items    => $list,
+      options  => $f,
+      nextpage => $np,
+      class    => 'notifies',
+      pageurl  => "/u$uid/notifies?r=$f->{r}",
+      header   => [
+        [ '<input type="checkbox" class="checkall" name="notifysel" value="0" />' ],
+        [ mt '_usern_col_type' ],
+        [ mt '_usern_col_age' ],
+        [ mt '_usern_col_id' ],
+        [ mt '_usern_col_desc' ],
+      ],
+      row     => sub {
+        my($s, $n, $l) = @_;
+        Tr class => join ' ', $n%2?'odd':'', $l->{read}?'':'unread';
+         td class => 'tc1';
+          input type => 'checkbox', name => 'notifysel', value => "$l->{id}";
+         end;
+         td class => 'tc2', mt "_usern_type_$l->{ntype}";
+         td class => 'tc3', $self->{l10n}->age($l->{date});
+         td class => 'tc4';
+          a href => "/u$uid/notify/$l->{id}", "$l->{ltype}$l->{iid}".($l->{subid}?".$l->{subid}":'');
+         end;
+         td class => 'tc5';
+          lit mt '_usern_n_'.(
+            $l->{ltype} eq 't' ? ($l->{subid} == 1 ? 't_new' : 't_reply')
+            : die("unknown notification type")),
+            sprintf('<i>%s</i>', xml_escape $l->{title}), sprintf('<i>%s</i>', xml_escape $l->{subtitle});
+         end;
+        end;
+      },
+      footer => sub {
+        Tr;
+         td colspan => 5;
+          input type => 'submit', name => 'markread', value => mt '_usern_but_markread';
+          input type => 'submit', name => 'remove', value => mt '_usern_but_remove';
+         end;
+        end;
+      }
+    );
+    end;
+  }
+  $self->htmlFooter;
+}
+
+
+sub readnotify {
+  my($self, $uid, $nid) = @_;
+  return $self->htmlDenied if !$self->authInfo->{id} || $uid != $self->authInfo->{id};
+  my $n = $self->dbNotifyGet(uid => $uid, id => $nid)->[0];
+  return 404 if !$n->{iid};
+  $self->dbNotifyMarkRead($n->{id}) if !$n->{read};
+  # NOTE: for t+.+ IDs, this will create a double redirect, which is rather awkward...
+  $self->resRedirect("/$n->{ltype}$n->{iid}".($n->{subid}?".$n->{subid}":''), 'perm');
+}
+
+
 1;
 
diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm
index 19a58a0f..99434c3f 100644
--- a/lib/VNDB/Util/Auth.pm
+++ b/lib/VNDB/Util/Auth.pm
@@ -25,7 +25,7 @@ sub authInit {
   return _rmcookie($self) if length($cookie) < 41;
   my $token = substr($cookie, 0, 40);
   my $uid  = substr($cookie, 40);
-  $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended')->[0];
+  $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended notifycount')->[0];
   return _rmcookie($self) if !$self->{_auth};
 }
 
@@ -95,7 +95,7 @@ sub _authCheck {
 
   return 0 if !$user || length($user) > 15 || length($user) < 2 || !$pass;
 
-  my $d = $self->dbUserGet(username => $user, what => 'extended')->[0];
+  my $d = $self->dbUserGet(username => $user, what => 'extended notifycount')->[0];
   return 0 if !defined $d->{id} || !$d->{rank};
 
   if(_authEncryptPass($self, $pass, $d->{salt}) eq $d->{passwd}) {
diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm
index 084b9a4e..0783a9e4 100644
--- a/lib/VNDB/Util/LayoutHTML.pm
+++ b/lib/VNDB/Util/LayoutHTML.pm
@@ -79,8 +79,8 @@ sub _menu {
 
    div class => 'menubox';
     if($self->authInfo->{id}) {
-      my $msg = $self->dbUserMessageCount($self->authInfo->{id});
       my $uid = sprintf '/u%d', $self->authInfo->{id};
+      my $nc = $self->authInfo->{notifycount};
       h2;
        a href => $uid, ucfirst $self->authInfo->{username};
        # note: user ranks aren't TL'ed (but might be in the future, hmm)
@@ -90,7 +90,7 @@ sub _menu {
        a href => "$uid/edit", mt '_menu_myprofile'; br;
        a href => "$uid/list", mt '_menu_myvnlist'; br;
        a href => "$uid/wish", mt '_menu_mywishlist'; br;
-       a href => "/t$uid",    $msg ? (class => 'standout') : (), mt '_menu_mymessages', $msg; br;
+       a href => "$uid/notifies", $nc ? (class => 'standout') : (), mt '_menu_mynotifications', $nc; br;
        a href => "$uid/hist", mt '_menu_mychanges'; br;
        a href => "$uid/tags", mt '_menu_mytags'; br;
        br;