# updates request_storage location values from csv file

use Getopt::Std;
getopts('qt'); # output sql query
our($opt_q,$opt_t); # warn $opt_q; exit;

use lib '/home/raj/perl-lib';

use Data::Printer alias => 'ddp';
use Modern::Perl;
use Local::DB;
use Text::CSV;
use IO::File;

$ENV{SQL_TRACE}  = $opt_q; # warn $ENV{SQL_TRACE}; switch on query trace

#==============================================================================
my $JUST_TESTING = $opt_t;
my $src_file  = './Micronic_96_04102017_120338_597.csv';
#==============================================================================

my $dbname = $JUST_TESTING ? 'lims_test' : 'hilis4'; # say $dbname; exit;

my $dbix = Local::DB->dbix({ dbname => 'hilis4' });

my $io = new IO::File;
open( $io, '<', $src_file ) || die $!;

my $opt = { binary => 1 }; # recommended to set true

my $csv = Text::CSV->new($opt); # ddp $csv;

my $head = $csv->getline($io); # ddp $head;
$csv->column_names( $head );

my $system_user_id = $dbix->select('users', 'id',
    { username => 'hmds-lth'})->value; # say $system_user_id; exit;

# 1st column = PlateID,<plate-ref>, so need to take plate-ref into var for use as col header:
my $well_id_col_name = $head->[0]; # 1st cell of 1st row
my $vial_id_col_name = $head->[1]; # 2nd cell of 1st row

# $vial_id_col_name is also plate ID, so check it doesn't already exist then create it:
my $storage_rack_id;
if ( $dbix->count($dbname.'.storage_racks', { plateId => $vial_id_col_name }) ) {
    die "$vial_id_col_name already exists in storage_racks table";
}
else {
    my $table = "$dbname.storage_racks";
    $dbix->insert( $table, { plateId => $vial_id_col_name } )
        or die $dbix->error;
    $storage_rack_id = $dbix->select( $table, 'max(id)' )->value
        or die "cannot get max id from storage_racks";
}

my $i = 0;
{ # update request_storage table:
    my $table = "$dbname.request_storage";

    while ( my $ref = $csv->getline_hr($io) ) { # ddp $ref; next;
        my $wellId = $ref->{$well_id_col_name} or next;
        my $vialId = $ref->{$vial_id_col_name}; # ddp $vialId;
        next if $vialId =~ 'NO READ'; # contains spaces

        my $request_id = $dbix->select( $table, 'request_id',
            { vialId => $vialId } )->value;
        unless ( $request_id ) {
            say "$vialId not present in $table table"; next;
        } # say "$vialId IS present"; next;

        my $result = $dbix->update($table,
            { rack_id => $storage_rack_id, vial_location => $wellId },
            { vialId  => $vialId }
        );
        if ( $result->rows ) {
            my %h = (
                request_id => $request_id,
                user_id    => $system_user_id,
                action     => "vialId $vialId scanned to plate",
            );
            $dbix->insert("$dbname.request_lab_test_history", \%h);
            $i++;
        }
        else {
            $result->error;
        }
    }
}
warn "updated $i records";