# refactor cron jobs which all do the same thing
# 1) run a query
# 2) make an Excel file
# 3) email it to a user

package LIMS::Local::QueryMailer;

use Modern::Perl;
use autodie;
use DateTime;
use Import::Into;

use lib '/home/raj/perl5/lib/perl5';
use FindBin qw($Bin);    # warn $Bin;
use lib $Bin . '/../../../lib';

use Excel::Writer::XLSX;

use LIMS::Local::ScriptHelpers;
use LIMS::Local::QueryLibrary;

# get tools from LIMS::Local::ScriptHelpers:
my $tools = LIMS::Local::ScriptHelpers->new();

use Moose;
use namespace::autoclean;

has testing => ( is => 'ro', isa => 'Int', default => 0 ); # flag JUST_TESTING
has results => ( # arrayref of hashes {sql=>'', bind => [] , db_output => DBIx::Simple::Result, sheet_name => ''}
    is      => 'ro',
    isa     => 'ArrayRef',
    default => sub { [] }
);
has excel => (    # hashref {filename => '' , workbook => ''}
    is      => 'ro',
    isa     => 'HashRef',
    default => sub { {} }
);
has dbix =>
  ( is => 'ro', isa => 'DBIx::Simple', lazy => 1, builder => '_build_dbix' );

# connect to appropriate database - depends on testing/production
sub _build_dbix {
    my $self = shift;
    $tools->test_only( $self->testing );
    return $tools->get_dbix();
}

# run query and push it onto results stack
sub run {
    my $self   = shift;
    my %params = @_;

    my $sql        = $params{sql};
    my $bind       = $params{bind};
    my $sheet_name = $params{sheet_name};
    my $result     = $self->dbix->query( $params{sql}, @{ $params{bind} } );
    push @{ $self->results },
      {
        sql        => $sql,
        bind       => $bind,
        db_output  => $result,
        sheet_name => $sheet_name
      };
}

# drop/create tmp incase it already exists
sub reset_tmp_table {
    my $self = shift;
    my $sql  = shift;

    $self->dbix->query('drop table if exists tmp');
    $self->dbix->query('create table tmp (id int)');
    $self->dbix->query($sql) if $sql;
}

# test method to output Excel file directly
sub save_file {
    my $self = shift;
    my $dir = shift || '';
    $dir .= '/' if $dir =~ m{[^/]$}; # add a slash if path doesnt end with one
    open my $fh, '>', $dir . $self->excel->{filename};
    binmode($fh);
    print $fh $self->excel->{workbook};
    close $fh;

}

# build excel file from results and store in string $self->excel->{workbook}
sub make_excel {
    my $self = shift;
    my $filename = shift || 'output.xlsx';    # result index
    die "no results to write" unless scalar @{ $self->results };

    $self->excel->{filename} = $filename;

    open my $fh, '>', \$self->excel->{workbook};
    my $workbook = Excel::Writer::XLSX->new($fh);
    my $bold = $workbook->add_format( bold => 1 );

    foreach my $i ( @{ $self->results } ) {
        my @column_titles = $i->{db_output}->columns;    # p \@column_titles;
        my $worksheet = $workbook->add_worksheet( $i->{sheet_name} );
        $worksheet->write_row( 0, 0, \@column_titles );
        my $y = 1;
        foreach my $row ( $i->{db_output}->arrays ) {
            $worksheet->write_row( $y++, 0, $row );
        }
    }
    $workbook->close() or die "Failed to close Excel document";
}

# mail excel to recipients if not in testing mode
sub mail_results {
    my $self       = shift;
    my %params     = @_;
    my $recipients = $params{recipients};

    my %mail = (
        config     => $tools->config,
        subject    => $params{subject},
        filename   => $self->excel->{filename},
        attachment => $self->excel->{workbook},
    );

    $tools->send_mail( \%mail, $recipients ) or die;
}
__PACKAGE__->meta->make_immutable;

# reduce the boilerplate for these crons
sub import {
    my $target = caller;
    Modern::Perl->import::into($target);
    LIMS::Local::ScriptHelpers->import::into($target);
    SQL::Library->import::into($target);
    lib->import::into($target);
    DateTime->import::into($target);
}
1;