#!/usr/bin/perl

use strict;
use warnings;
use Carp qw/croak/;
use Data::Dumper qw/Dumper/;
use Perl6::Say;
use Benchmark;

use FindBin qw/$Bin/;
use lib $Bin . '/..';
use Bulletinboard::Schema;
use Config::Auto;

my $t0 = Benchmark->new();

my $cfg = Config::Auto::parse($Bin.'/../config/config.pl');

my $schema = Bulletinboard::Schema->connection(
	$cfg->{db}->{dsn},
	$cfg->{db}->{username},
	$cfg->{db}->{password},
	$cfg->{db}->{attributes},
);
#$schema->storage->debug(1);

my @topic_loop	= ();

my $trs = $schema->resultset('Topic')->search(
	undef,
	{
		order_by	=> 'me.position ASC',
		prefetch	=> [qw/boards/],
	}
);
while( my $topic = $trs->next() ) {
	
	say $topic->topic();
	
	my @board_loop = ();
	foreach my $board ( $topic->boards() ) {
	
		say "\t" . $board->title();
	
		# -- get the latest thread
		my ($lastthread) = $board->threads(
			undef,
			{
				order_by	=> 'date_of_creation DESC',
			}
		)->slice(0,1);
		
		print "\t\t" . $lastthread->subject() . ": ";
		
		my $lastpost = undef;
		if( defined $lastthread ) {
			($lastpost) = $lastthread->posts(
				undef,
				{
					select		=> 'timestamp',
					order_by 	=> 'timestamp DESC',
				}
			)->slice(0,1);
			say $lastpost->timestamp();
		}else{
			say "no posts";
		}

	}

}

my $t1 = Benchmark->new();

my $td = Benchmark::timediff($t1, $t0);
say 'benchmark_timediff: ' . timestr($td);

#!/usr/bin/perl

use strict;
use warnings;
use Local::DBIC_Schema;
use Benchmark qw(cmpthese);

my $schema = Local::DBIC_Schema->connect( 'DBI:SQLite:pktm' );

cmpthese(
    100_000,
    {
        first => sub {
         
            my ($ding) = $schema->resultset('T')->search(
                undef,
                {
                    order_by => 'testid DESC',
                }
            )->first;
        },
        slicer => sub {
            my ($ding2) = $schema->resultset('T')->search(
                undef,
                {
                    order_by => 'testid DESC',
                }
            )->slice(0,1);
        },
    }
);

C:\>pktm_test.pl
        Rate slicer  first
slicer 434/s     --   -42%
first  748/s    72%     --

sub overview {
	my $self	= shift;
	
	return $self->authz->forbidden() unless $self->authz('privileges')->authorize(__PACKAGE__, $self->get_current_runmode());
	
	my $schema		= $self->schema();
	my @topic_loop	= ();
	
	my $trs = $schema->resultset('Topic')->search(
		undef,
		{
			order_by	=> 'me.position ASC',
			prefetch	=> [qw/boards/],
		}
	);
	while( my $topic = $trs->next() ) {
		
		my @board_loop = ();
		my $board_rs = $topic->boards(
			undef,
			{
				'+select'	=> [{count => 'thread_id'}],
				'+as'		=> ['thread_count'],,
				join		=> [qw/threads/],
				group_by	=> [qw/board_id/],
			},
		);
		while( my $board = $board_rs->next() ) {

			# -- get the latest thread
			my ($lastthread) = $board->threads(
				undef,
				{
					order_by => 'date_of_creation DESC',
				}
			)->first();
			
			my $lastpost = undef;
			if( defined $lastthread ) {
				($lastpost) = $lastthread->posts(
					undef,
					{
						'+select'	=> [qw/user.username user.user_id/],
						'+as'		=> [qw/username user_id/],
						select		=> 'timestamp',
						order_by 	=> 'timestamp DESC',
						join		=> [qw/user/],
					}
				)->first();
			}
			
			my $reply_count = 0;
			if( defined $lastthread ) { # if there is a thread
				my $rs = $board->threads(
					undef,
					{
						'+select' => {count => 'posts.post_id'},
						'+as' => 'reply_count',
						join => [qw/posts/],
						group_by => [qw/me.thread_id/],
					}
				);
				foreach my $t ( $rs->all() ) {
					$reply_count += $t->get_column('reply_count') - 1;
				}
			}
			
			push @board_loop, {
				board_id			=> $board->board_id(),
				title				=> $board->title(),
				thread_count		=> $board->get_column('thread_count'),
				reply_count			=> $reply_count,
				
				last_post_username	=> (defined $lastpost ? $lastpost->get_column('username') : undef),
				last_post_timestamp	=> (defined $lastpost ? $lastpost->timestamp() : undef),
				last_post_user_id	=> (defined $lastpost ? $lastpost->get_column('user_id') : undef),
				
				last_thread_subject	=> (defined $lastthread ? $lastthread->subject() : undef),
				last_thread_id		=> (defined $lastthread ? $lastthread->thread_id() : undef),
			};
		}
		
		push @topic_loop, {
			topic_id	=> $topic->topic_id(),
			topic		=> $topic->topic(),
			boards		=> \@board_loop,
		};
	}
	
	$self->param(topics => \@topic_loop);
	
	my $t = $self->load_tmpl('_overview.tmpl', associate => $self);
	return $t->output();
} # /overview