# allocates rack_id, vial_location & signed_out values from source 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 use_prototypes => 0;
use Spreadsheet::Read;
use Modern::Perl;
use Local::DB;

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

#==============================================================================
my $JUST_TESTING = $opt_t;
my $src_file  = './MaPLe Plasma Plate layout 1-6.xlsx';
#==============================================================================

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

my $system_user_id = $dbix->select('users', 'id',
    { username => 'hmds-lth'})->value; # p $system_user_id;
my $signed_out = '2017-07-05 16:00:00'; # approx sign-out time

my $xls = ReadData($src_file); # p $xls;

my %RACKS; # to hold plate_id => storage_racks.id
my $i = 0;

process_sheet($_) for 1 .. 6;

sub process_sheet {
    my $sheet_num = shift; # starts at #1
    my $sheet = $xls->[$sheet_num]; # p $sheet;
    # rows() not exported so call as fully qualified method:
    my @rows = Spreadsheet::Read::rows($sheet); # p @rows; # AoA

    my $headers = shift @rows; # p $headers; exit;

    ROW:
    for my $row (@rows) { # arrayref
        my $data = _to_hash($row, $headers); # p $data; next;

        my $plateId = $data->{plateId};
        my $labnum  = $data->{Lab_number};
        my $vialId  = $data->{vialId};
        my $well    = $data->{Well};

        { # check vialId belongs to labnum:
            my ($request_num, $yr) = split '/', $labnum; # p [$request_num, $yr];
            my %h = ( request_number => $request_num, year => $yr + 2000 );
            my $req_id = $dbix->select('requests', 'id', \%h)->value; # p $req_id;
            warn "cannot find request id for $labnum" unless $req_id;

            my $rack_id = _get_rack_id($plateId)
                or warn "cannot get rack ID for $plateId";

            my %data = (
                vial_location => $well,
                signed_out    => $signed_out,
                rack_id       => $rack_id,
            );
            my $result = $dbix->update('request_storage', \%data,
                { request_id => $req_id, vialId => $vialId });

            if ($result->rows) { # p [$request_num, $yr];
                # set plate inactive (will do multiple identical updates unless use hash):
                $dbix->update('storage_racks',
                    { is_active => 'no' }, { id => $rack_id });
                # update request_lab_test_history:
                my @actions = (
                    "vialId $vialId scanned to plate",
                    "scanned out vialId $vialId",
                );
                $dbix->insert('request_lab_test_history', {
                    action     => $_,
                    user_id    => $system_user_id,
                    request_id => $req_id
                }) for @actions;
                $i += $result->rows;
            }
            else {
                warn "cannot update $vialId";
            }
        }
    }
}

warn "updated $i records";

sub _get_rack_id {
    my $plate_id = shift;

    # return if already seen:
    return $RACKS{$plate_id} if $RACKS{$plate_id};

    my $table = 'storage_racks';
    my %h = ( plateId => $plate_id );
    # return if not already seen but in storage_racks:
    if ( my $id = $dbix->select($table, 'id', \%h)->value ) {
        $RACKS{$plate_id} = $id;
        return $id;
    }
    # create new row & return:
    $dbix->insert($table, \%h);
    my $last_insert_id = $dbix->query('select max(id) from '.$table)->value;
    $RACKS{$plate_id} = $last_insert_id;
    return $last_insert_id;
}

sub _to_hash {
    my ($ref, $headers) = @_; # p $ref; # arrayref
    my %h;
    my $i = 0;
    for (@$headers) { # p $_;
        $h{$_} = $ref->[$i++];
    } # p %h;
    return \%h;
}