#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Data::Dumper;
use XML::LibXML;
use Env qw(
    HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
    MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
);
use Pod::Usage;
use Switch;
use Cwd 'abs_path';
use FindBin;
my $mig_bin = "$FindBin::Bin/";
use lib "$FindBin::Bin/";
use Mig;
use open ':encoding(utf8)';

pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';

my $analyst;
my $next_arg_is_analyst;
my $report_title;
my $next_arg_is_report_title;
my $reports_xml;
my $next_arg_is_reports_xml;
my $tags;
my $next_arg_is_tags;
my $added_page_title;
my $next_arg_is_added_page_title;
my $added_page_file;
my $next_arg_is_added_page_file;
my $i = 0;
my $parser = XML::LibXML->new();
my $lines_per_page = 42;

foreach my $arg (@ARGV) {
    if ($arg eq '--report_title') {
        $next_arg_is_report_title = 1;
        next;
    }
    if ($next_arg_is_report_title) {
        $report_title = $arg;
        $next_arg_is_report_title = 0;
        next;
    }
    if ($arg eq '--analyst') {
        $next_arg_is_analyst = 1;
        next;
    }
    if ($next_arg_is_analyst) {
        $analyst = $arg;
        $next_arg_is_analyst = 0;
        next;
    }
    if ($arg eq '--reports_xml') {
        $next_arg_is_reports_xml = 1;
        next;
    }
    if ($next_arg_is_reports_xml) {
        $reports_xml = $arg;
        $next_arg_is_reports_xml = 0;
        next;
    }
    if ($arg eq '--tags') {
        $next_arg_is_tags = 1;
        next;
    }
    if ($next_arg_is_tags) {
        $tags = $arg;
        $next_arg_is_tags = 0;
        next;
    }
    if ($arg eq '--added_page_title') {
        $next_arg_is_added_page_title = 1;
        next;
    }
    if ($next_arg_is_added_page_title) {
        $added_page_title = $arg;
        $next_arg_is_added_page_title = 0;
        next;
    }
    if ($arg eq '--added_page_file') {
        $next_arg_is_added_page_file = 1;
        next;
    }
    if ($next_arg_is_added_page_file) {
        $added_page_file = $arg;
        $next_arg_is_added_page_file = 0;
        next;
    }
}

if (!defined $tags) {$tags = 'Circs.Holds.Actors.Bibs.Assets.Money'};
if (!defined $analyst) { abort('--analyst must be supplied'); }
if (!defined $report_title) { abort('--report_title must be supplied'); }

my $mig_path = abs_path($0);
$mig_path =~ s|[^/]+$||;
if (!defined $reports_xml) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; } 
    else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; }
print "$reports_xml \n";
my $dom = $parser->parse_file($reports_xml);

if (defined $added_page_file or defined $added_page_title) {
    abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
    }
if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
if ($MIGSCHEMA eq 'full') { $MIGSCHEMA = ''; }

my $dbh = Mig::db_connect();
my $report_file = create_report_name($report_title);
$report_file = $MIGGITDIR . $report_file;
my $mig_func_schema = $MIGSCHEMA;

open(my $fh, '>', $report_file) or die "Could not open output file!";

write_title_page($report_title,$fh,$analyst);

if (defined $added_page_file and defined $added_page_title) { 
    print $fh "<<<\n";
    print $fh "== $added_page_title\n";
    print "$added_page_file\t$added_page_title\n";
    open(my $an,'<:encoding(UTF-8)', $added_page_file) or die "Could not open $added_page_file !";
    while ( my $line = <$an> ) {
        print $fh $line;
    }
    print $fh "\n";
    close $an;
}

foreach my $func ($dom->findnodes('//function')) {
    my $fdrop = $func->findvalue('./drop');
    my $fcreate = $func->findvalue('./create');    
    my $fname = $func->findvalue('./name');
    $fdrop =~ s/mig_func_schema/$mig_func_schema/g;
    $fcreate =~ s/mig_func_schema/$mig_func_schema/g;
    my $sdrop = $dbh->prepare($fdrop);
    my $screate = $dbh->prepare($fcreate);
    print "dropping function $fname ... ";
    $sdrop->execute();
    print "creating function $fname\n\n";
    $screate->execute();
}

$tags = lc($tags);
my @report_tags = split(/\./,$tags);
foreach my $t (@report_tags) {
    print "\n\n=========== Starting to process tag $t\n";
    print   "==========================================\n";
    print_section_header(ucfirst($t),$fh);
    my $linecount = $lines_per_page;
    my $r;

    my @report_names;

    foreach my $report ($dom->findnodes('//report')) {
        if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
            push @report_names, $report->findvalue('./name');
        }
    }
    
    print Dumper(@report_names);

    #only has one level of failover now but could change to array of hashes and loops
    foreach my $rname (@report_names) {
        my %report0;
        my %report1;
        my $check_tables0;
        my $check_tables1;

        print "\nchecking for $rname ... ";
        %report0 = find_report($dom,$t,$rname,'0');
        $check_tables0 = check_table($report0{query},$MIGSCHEMA);
        if ($check_tables0 == 1) {
           $r =  print_query($fh,%report0); 
        } else {
        %report1 = find_report($dom,$t,$rname,'1');
        if (defined $report1{query}) {
            $check_tables1 = check_table($report1{query},$MIGSCHEMA);
            if ($check_tables1 == 1) {$r = print_query($fh,%report1);}    
            }
        }
    }
}
# end of main logic

print "\n";
close $fh;

sub find_report {
    my $dom = shift;
    my $tag = shift;
    my $name = shift;
    my $iteration = shift;
    my %report;

    print "iteration $iteration ";
    foreach my $node ($dom->findnodes('//report')) {
        if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
            print "succeeded ... ";
            %report = (
                name => $node->findvalue('./name'),
                report_title => $node->findvalue('./report_title'),
                query => $node->findvalue('./query'),
                heading => $node->findvalue('./heading'),
                tag => $node->findvalue('./tag'),
                iteration => $node->findvalue('./iteration'),
                note => $node->findvalue('./note'),
            );
            return %report;
        }
    }
    print "failed ... ";
    return %report = (
        name => "eaten by grue"
    );
}

sub print_section_header {
    my $t = shift;
    my $fh = shift;
    $t =~ s/_/ /g;
    #$t =~ s/(\w+)/\u$1/g;;
    print $fh "<<<\n";
    print $fh "== $t Reports\n";
}

sub create_report_name {
    my $rt = shift;
    my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
    $year += 1900;
    my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
    my $report_file = $rt . ' ' . $date . '.asciidoc';
    $report_file =~ s/ /_/g;
    return $report_file;
}

sub write_title_page {
    my $rt = shift;
    my $fh = shift;
    my $a = shift;

    my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
    my $l = length($report_title);
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
    $year += 1900;
    print $fh "= $rt\n"; 
    print $fh "$mday $abbr[$mon] $year\n";
    print $fh "$a\n";
    print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
    print $fh ":toc:\n";
    print $fh "\n";
}

sub check_table {
    my $query = shift;
    my $MIGSCHEMA = shift;

    my $i = 0;
    my $return_flag = 1;   
    my @qe = split(/ /,$query);
    my @tables = grep /MIGSCHEMA/, @qe;

    print "checking tables ... ";
    foreach my $table (@tables) {
        $table =~ s/MIGSCHEMA.//g;
        $table =~ s/\)//g;
        $table =~ s/\<//g;
        my $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
        my $sth = $dbh->prepare($sql);
        $sth->execute();
        while (my @row = $sth->fetchrow_array) {
            if ($row[0] eq '1') {
                    next;
                } else {
                    $return_flag = 0;
                }
            if ($row[0] eq '0') {$return_flag = 0;}
        }
    }
    if ($return_flag == 1) {print "succeeded ... ";} else {print "failed ... ";}
    return $return_flag;
}

sub print_query {
    my $fh = shift;
    my %report = @_;
    my $query = $report{query};
    $query =~ s/MIGSCHEMA/$MIGSCHEMA/g;
    $query =~ s/mig_func_schema/$mig_func_schema/g;
    my $sth = $dbh->prepare($query);
    $sth->execute();

    my $header_flag = 0;

    while (my @row = $sth->fetchrow_array) {
            if ($header_flag == 0) {
                print $fh "\n.*$report{report_title}*\n";
                print $fh "|===\n";
                my @h = split(/\./,$report{heading});
                my $h_length = @h;
                my $h_count = 1;
                while ($h_count <= $h_length) {
                    print $fh "|$h[$h_count-1] ";
                    $h_count++;
                }
                print $fh "\n";
                $header_flag = 1;
            }
            my $row_length = @row;
            my $r = 1;
            while ($r <= $row_length) {
                if (! defined $row[$r-1] ) {
                    $row[$r-1] = 'none';
                }
                print $fh "|$row[$r-1] ";
                $r++;
            }
            print $fh "\n";
        }
    if ($header_flag == 1) { 
        print $fh "|===\n\n"; 
        print $fh $report{note};
        print $fh "\n\n";
    }
    print "successfully wrote output for $report{name}.\n\n";
}

sub abort {
    my $msg = shift;
    print STDERR "$0: $msg", "\n";
    exit 1;
}