use SRS::Perl;
use SRS::MyDBI;
use SRS::Config;
use SRX::Bulkmail::EmailList;
use SRX::Bulkmail;
use SRS::Utils;
use SRS::User;
use SRS::Cache;
use List::MoreUtils qw(uniq);
use SRS::Type::DateTime qw(dt);
use Data::Dumper;


use utf8;

my @mailists = (1,8);

foreach my $mail_list (@mailists) {
    process_maillist($mail_list);
}

sub process_maillist {
    my $subsr_list_id = shift;

    return unless $subsr_list_id && is_integer($subsr_list_id);

    my $sth = dbh_ro->prepare( "SELECT sid, user_id, email FROM maillist_subscribers WHERE listid = $subsr_list_id");
    $sth->execute();

    while ( my $subscriber = $sth->fetchrow_hashref ) {
        my $email = $subscriber->{email} ?
                    $subscriber->{email} : SRS::User->new( $subscriber->{user_id} )->{email};
        my $last_open_time = find_last_opened_bulkmail($email, $subsr_list_id);
        SRS::Subscriptions::set_last_open_time( $subscriber->{sid}, $last_open_time ) if $last_open_time;
    }
}


sub find_last_opened_bulkmail {
    my ($email, $subsr_list_id) = @_;

    dbh_rw->selectrow_array("
          SELECT
            bulkmail_email_lists.changed
          FROM
            bulkmail_email_lists
          LEFT JOIN
            bulkmails ON bulkmail_email_lists.list_id = bulkmails.list_id
          WHERE
            bulkmail_email_lists.email = ?
          AND
            bulkmail_email_lists.state = 'opened'
          AND
            bulkmails.maillist_id = ?
          ORDER BY
            bulkmail_email_lists.changed DESC
          LIMIT 1
          ", undef, $email, $subsr_list_id );
}