#
#===============================================================================
#
#  DESCRIPTION: unit test LIMS::Local::QueryMailer.pm
#
#===============================================================================
package TestsFor::LIMS::Local::QueryMailer;

use Modern::Perl;
use utf8;

use Test::Class::Moose;
use DDP caller_info => 1;
use Try::Tiny;

with 'Test::Class::Moose::Role::AutoUse';

sub test_some : Tests {
    my $test         = shift;
    my $class        = $test->test_class;
    my $outfile_name = "dlbcl2_test.xlsx";
    reset_database();

    #
    note "test create object";
    #
    my $query = LIMS::Local::QueryMailer->new( testing => 1 );
    ok( defined($query), 'create object' );
    $query->dbix->query('use lims_test');
    is $query->dbix->query('select database()')->list, 'lims_test',
      'Using test database';

    #
    note "test 'run' method";
    #
    is( scalar @{ $query->results() }, 0, "initialised with no results" );
    my $sql = "select * from requests limit 5";
    $query->run( sql => $sql, sheet_name => 'requests' );
    is( scalar @{ $query->results() }, 1, "has 1st result" );
    ok( $query->results->[0]{db_output}->isa('DBIx::Simple::Result'),
        'run produced a DBIx::Simple::Result' );
    $sql = "select * from patients limit 5";
    $query->run( sql => $sql, sheet_name => 'patients' );
    is( scalar @{ $query->results() }, 2, "has 2nd result" );

    #
    # "test bad sql";
    #
    try {
        $query->run( sql => 'sql' );
    }
    catch {
        like $_, qr/error in your SQL syntax/, 'caught error in bad sql string';
    };
    is( scalar @{ $query->results() }, 2, "still only has 2 results" );

    # type of result
    #        note explain $query->results->[0]->{db_output}->arrays;
    #        $query->run($sql);
    #        is( scalar @{ $query->results() }, 2, "has 2 results" );
    #        # type of result
    #        note explain $query->results->[1]->{db_output}->hashes;

    #
    note "test 'make_excel' method";
    #
    $query->make_excel($outfile_name);
    lives_ok { check_xlsx($query) } "Excel file contains correct headers";

    my $query_blank = LIMS::Local::QueryMailer->new( testing => 1 );
    $query_blank->dbix->query('use lims_test');
    dies_ok { $query_blank->make_excel($outfile_name) }
    "Excel file not created if there are no DB results";

    my @recipients = ('garry.quested@nhs.net');
    lives_ok {
        $query->mail_results(
            recipients => \@recipients,
            subject    => "DLBCL data this week"
          )
    }
    'sends mail (subject to HILIS testing rules) successfully';
    #
    note "test 'save_file' method";
    #
    lives_ok { $query->save_file('/tmp/') } 'saves file to /tmp';
    ok -f "/tmp/$outfile_name", 'Excel file created';

    #unlink "/tmp/$outfile_name";

    throws_ok { $query->save_file('/') } qr/Permission denied/,
      'dies when it doesnt have write permission';
    throws_ok { $query->save_file('/tmp_doesnt_exist/') }
    qr/No such file or directory/, 'dies when directory doesnt exist';

    return 1;
}

# creates a file and tests validity using Spreadsheet::Read if available
# doesnt break if module not available because its only used in testing.
# should be recommended though
sub check_xlsx {
  SKIP: {
        my $res = eval { require Spreadsheet::Read };
        skip 'need to install Spreadsheet::Read to run these tests', 2, if $@;
        my $data    = $_[0]->excel->{workbook};
        my $results = $_[0]->results;

        #create an excel file from data
        my $filename = "/tmp/testfile$$.xlsx";
        open my $fh, '>:raw', $filename
          or die "could not open $filename for writing";
        binmode($fh);
        print $fh $data;
        close $fh or die "could not close $filename ";

# test file by comparing each header cell in each sheet with it's corresponding result column name from DBIx::Simple::Result->column
        my $workbook = Spreadsheet::Read::ReadData($filename);
        foreach my $sheet ( 1 .. $workbook->[0]{sheets} )
        {    # sheets numbered 1..n
            my @columns =
              $results->[ $sheet - 1 ]{db_output}->columns;    # DBIC method
            foreach my $i ( 0 .. $#columns ) {

      #                is $columns[$i] , $workbook->[$sheet]{cell}[ $i + 1 ][1];
                die if $columns[$i] ne $workbook->[$sheet]{cell}[ $i + 1 ][1];
            }
        }

        # tidy up
        unlink $filename || die;
    }
    return;
}

sub reset_database {
    return 1;
}
1;