# 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 Import::Into;
my $JUST_TESTING = 0;
use Modern::Perl;
use autodie;
use DateTime;

use Data::Printer;

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 SQL::Library;

# 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);
has results => ( is => 'ro', isa => 'ArrayRef', default => sub {[]});
has excel => ( is => 'ro', isa => 'HashRef', default => sub {{}});
has dbix => (is => 'ro' , isa => 'DBIx::Simple' , lazy =>1 , builder => '_build_dbix');

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 = @_;
#    $self->dbix->query('use hilis4_clone'); # TODO REMOVE THIS LINE!!!!!!!!!!!!!!!!!!!
    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 };
}
sub reset_tmp_table {
    my $self = shift;
    my $sql = shift;
#    $self->dbix->query('use hilis4_clone'); # TODO REMOVE THIS LINE!!!!!!!!!!!!!!!!!!!
    $self->dbix->query('drop table if exists tmp');
    $self->dbix->query('create table tmp (id int)');
    $self->dbix->query($sql) if $sql;
}

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;


}
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";
}


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;