logbot-nightly

#!/usr/bin/perl
# update channel metadata, etc
use local::lib;
use v5.10;
use strict;
use warnings;

use FindBin qw( $RealBin );
use lib "$RealBin/lib";

BEGIN {
    $ENV{TZ}   = 'UTC';
    $ENV{CRON} = !$ENV{DEBUG};
}

use DateTime ();
use File::Path qw( make_path );
use JSON ();
use JSON::XS qw( encode_json );
use LogBot::Config qw( find_config load_all_configs load_config reload_config save_config );
use LogBot::Database qw( dbh replace_sql_placeholders );
use LogBot::Util qw( file_for logbot_init nick_is_bot path_for round spurt );
use LogBot::Web::Util qw( nick_hash );
use Readonly;

Readonly::Scalar my $ARCHIVE_TIME => 60 * 60 * 24 * (365 / 2);  # 6 months

my @configs;
if (@ARGV && $ARGV[0] eq '--all') {
    shift;
    @configs = values %{ load_all_configs() };
} else {
    push @configs, load_config(find_config(shift));
}
@configs || die "syntax: syntax: logbot-nightly <config file|--all>\n";

my %configs = map { $_->{name} => $_ } @configs;

foreach my $config_name (sort keys %configs) {
    my $config = $configs{$config_name};
    logbot_init($config, name => 'logbot-nightly', quiet => 1);

    # generate network meta
    generate_meta($config_name, file_for($config, 'meta', undef, 'meta'));
    generate_hours($config_name, file_for($config, 'meta', undef, 'hours'));

    foreach my $channel ('_empty', sort keys %{ $config->{channels} }) {
        next if $config->{channels}->{$channel}->{no_logs};

        say $config_name, ': ', $channel if $ENV{DEBUG};

        # generate data for about #channel
        generate_meta($config_name, file_for($config, 'meta', $channel, 'meta'), $channel);
        generate_hours($config_name, file_for($config, 'meta', $channel, 'hours'), $channel);
        generate_nicks($config_name, file_for($config, 'meta', $channel, 'nicks'), $channel);
    }
    delete $config->{channels}->{_empty};

    # stop logging and leave stale channels
    archive_stale_channels($config_name);
}

sub query {
    my %params = @_;
    my @where =
        exists($params{where})
        ? (ref($params{where}) ? @{ $params{where} } : $params{where})
        : ();
    my @values;

    if ($params{channel}) {
        push @where,  'channel = ?';
        push @values, $params{channel};
    }

    my $sql = 'SELECT ' . $params{select} . ' FROM logs';
    $sql .= ' WHERE ' . join(' AND ', @where) if @where;
    $sql .= ' ORDER BY ' . $params{order} if $params{order};

    if ($params{row_count}) {
        $sql = 'SELECT COUNT(*) FROM (' . $sql . ') t';
    } else {
        $sql .= ' LIMIT 1';
    }

    say '  ', replace_sql_placeholders($params{dbh}, $sql, \@values) if $ENV{DEBUG};
    return $params{dbh}->selectrow_array($sql, undef, @values) // undef;
}

sub generate_meta {
    my ($config_name, $output_file, $channel) = @_;
    my $config = $configs{$config_name};
    my $dbh    = dbh($config);
    my $now    = DateTime->now();

    my $six_months = $now->clone()->subtract(months => 6);
    my $one_month  = $now->clone()->subtract(months => 1);

    my $json = {
        first_time => query(
            dbh     => $dbh,
            channel => $channel,
            select  => 'time',
            order   => 'time ASC',
        ),

        active_events => query(
            dbh     => $dbh,
            channel => $channel,
            select  => 'COUNT(*)',
            where   => 'time >= ' . $six_months->epoch,
        ),

        active_events_days => $six_months->delta_days($now)->in_units('days'),

        active_nicks => query(
            dbh       => $dbh,
            channel   => $channel,
            row_count => 1,
            select    => 'DISTINCT nick',
            where     => 'time >= ' . $six_months->epoch,
        ),

        event_count => query(
            dbh     => $dbh,
            channel => $channel,
            select  => 'COUNT(*)',
        ),
    };

    # because we calculate the event count nightly for the network-wide stats,
    # round to nearest 1000 to make it clear it's an approximation
    if (!$channel) {
        $json->{event_count} = round($json->{event_count} / 1000) * 1000;
    }

    say '  ', encode_json($json) if $ENV{DEBUG};
    spurt($output_file, encode_json($json));
}

sub generate_hours {
    my ($config_name, $output_file, $channel) = @_;
    my $config = $configs{$config_name};
    my $dbh    = dbh($config);

    my $sql = 'SELECT CAST(time % (24 * 3600) AS INT), COUNT(*) FROM logs';
    $sql .= ' WHERE channel = ?' if $channel;
    $sql .= ' GROUP BY CAST(time % (24 * 3600) AS INT)';
    my @values;
    push @values, $channel if $channel;
    say '  ', replace_sql_placeholders($dbh, $sql, \@values) if $ENV{DEBUG};

    my %values;
    foreach my $row (@{ $dbh->selectall_arrayref($sql, undef, @values) }) {
        my ($ss, $value) = @{$row};
        my $hh = sprintf('%.1f', $ss / (60 * 60));
        $hh = '0.0' if $hh == 24;
        $values{$hh} += $value;
    }

    my @data;
    for (my $hh = 0; $hh < 24; $hh += 0.1) {
        $hh = sprintf('%.1f', $hh);
        push @data, [$hh * 1.0, ($values{$hh} || 0) * 1];
    }

    my $json = {
        data  => \@data,
        lines => {
            show => JSON::true,
            fill => JSON::true,
        },
    };
    spurt($output_file, encode_json($json));
}

sub generate_nicks {
    my ($config_name, $output_file, $channel) = @_;
    my $config = $configs{$config_name};
    my $dbh    = dbh($config);

    my $sql = 'SELECT nick, COUNT(*) AS count FROM logs';
    $sql .= ' WHERE channel = ?' if $channel;
    $sql .= ' GROUP BY nick';
    $sql .= ' ORDER BY COUNT(*) DESC, nick ASC';
    $sql .= ' LIMIT 20';
    my @values;
    push @values, $channel if $channel;
    say '  ', replace_sql_placeholders($dbh, $sql, \@values) if $ENV{DEBUG};

    my $data = $dbh->selectall_arrayref($sql, { Slice => {} }, @values);

    my $json = [];
    foreach my $event (@{$data}) {
        $event->{hash} = nick_hash($event->{nick});
        $event->{bot} = nick_is_bot($config, $event->{nick});
        push @{$json}, $event;
    }

    spurt($output_file, encode_json($json));
}

sub archive_stale_channels {
    my ($config_name) = @_;
    my $config = $configs{$config_name};

    my $now    = time();
    my $reload = 0;
    foreach my $channel (sort keys %{ $config->{channels} }) {
        next if $config->{channels}->{$channel}->{disabled};
        next if $config->{channels}->{$channel}->{archived};

        my $dbh = dbh($config);
        my $last_time =
            $dbh->selectrow_array('SELECT time FROM logs WHERE channel = ? ORDER BY time DESC LIMIT 1', undef,
            $channel);

        next unless $last_time;
        next unless $now - $last_time > $ARCHIVE_TIME;

        say $config->{name}, ': archiving ', $channel if $ENV{DEBUG};

        $config = reload_config($config);
        $config->{channels}->{$channel}->{archived} = 1;
        save_config($config);
        $configs{$config_name} = $config;
    }

    if ($reload) {
        system("$RealBin/logbot-irc", $config->{_internal}->{file}, '--reload');
    }
}