#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long;

my $sort_fld = 'tot';
my $show = 25;
GetOptions ("show=i" => \$show, "sort=s" => \$sort_fld) || usage('Invalid Option');
my $fn = $ARGV[0] || usage('Please specify a logfile');
usage("$fn does not exist") if (! -e $fn);
usage("$fn is not readable. Check permissions") if (! -r $fn);
usage("$fn is 0 size") if (-z $fn);
usage("$fn is not a regular file") if (! -f $fn);
usage('Invalid sort option') if (! ($sort_fld eq 'tot' or $sort_fld eq 'avg' or $sort_fld eq 'cost'));

my ($lines ) = `wc -l $fn`;
$lines =~ s/\D+//g;
my %q;
my $get_q = 0;
my $query = '';
my $buffer = '';
open(FH,'<',$fn) or die "Could not open $fn: $!";
print "Reading file...\n";
my $cnt = 0;
while (my $line = <FH>) {
    if ($line =~/^DEBUG:  query:/){
        $get_q++;
    } elsif ($get_q and $line =~ /^DEBUG: /) {
        $query = $buffer;
        $buffer = '';
        $get_q = 0;
    }
    if ($get_q) {
        $buffer .= $line;
        $buffer =~ s/^DEBUG:  query://g;
        $buffer =~ s/\s+/ /g;
    }
    if (!$get_q and $line =~ /^\s+:total_cost\s+(\S+)/) {
        my $cost = $1;
        no warnings;
        $cost =~ s/([\d.]+)/$1/g;
        $q{$_}{cost} = 0 if ($q{$_}{cost} =~ /[^\d.]/);
        $q{$query}{cost} = $cost > $q{query}{cost} ? $cost : $q{$query}{cost};
        $q{$query}{tot} += $cost;
        push(@{$q{$query}{costs}},$cost);
        $q{$query}{cnt}++;
        use warnings;
    }
    $q{$query}{scan} = 'SEQ' if (!$get_q and $line =~ /^\s+{\s+SEQSCAN/);
    $q{$query}{scan} = 'IDX' if (!$get_q and $line =~ /^\s+{\s+INDEXSCAN/);

    $cnt++;
    print "Reading line: $cnt of $lines\r" if (!($cnt % 1000));
}
print "\n Cleaning...";
foreach ( keys %q ) {
    if (/pg_/){ delete $q{$_}; next } #skip internal tables
    my $tot;
    foreach my $cost (@{$q{$_}{costs}}) {
        $tot += $cost;
    }

    $q{$_}{cost} = 0 if (!$q{$_}{cost} or $q{$_}{cost} =~ /[^\d.]/);
    $tot ||= 0;
    $q{$_}{cnt} ||= 0;
    if ($q{$_}{cnt}) {
        $q{$_}{avg} = $tot / $q{$_}{cnt};
    }
    $q{$_}{avg} ||= 0;
    $q{$_}{tot} ||= 0;
}

print "\n DONE\n Sorting... ";

my @srt_keys =  sort { $q{$a}{$sort_fld} <=> $q{$b}{$sort_fld} } keys %q;
@srt_keys = @srt_keys[$#srt_keys-($show-1)..$#srt_keys];

print "\n";
print " Total     Cost      Avg    IDX  Query\n";
print "-------- -------- --------  ---  ------->\n";
foreach (@srt_keys) {
    printf("%8s ",sprintf("%.3f",$q{$_}{tot}));
    printf("%8s ",sprintf("%.3f",$q{$_}{cost}));
    printf("%8s ",sprintf("%.3f",$q{$_}{avg}));
    print " $q{$_}{scan} $_\n";
}


sub usage {
    print "@_\n" if (@_);
    print "plog [--sort=tot|cost|avg] [--show=1000] /path/to/logfile\n";
    print "\t--sort : sort results by tot(Total Time) cost(Max Cost) or avg(Average Cost)\n";
    print "\t--show : show number of lines (default: 25)\n";

    exit();
}