use Modern::Perl;

# updates request_storage.part_number value from csv file

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

use Local::DB;
use Data::Printer alias => 'ddp';
use Data::Dumper;
use SQL::Abstract::More;
use Text::CSV;
use IO::File;

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

my $src_file  = './part_numbers_update.csv';

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; exit;

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

my $i = 0;
my $tbl = 'hilis4.request_storage';

my $sqla = SQL::Abstract::More->new;

while ( my $ref = $csv->getline_hr($io) ) { # ddp $ref;  next;
	my $original_hmds_num 	= $ref->{original_HMDS_No}; # print $original_hmds_num, " ";
	my $vial_id     		= $ref->{possible_vial_id}; # ddp $vial_id; next;
	my $part_number 		= $ref->{currrent_part_number}; # NOTE 3 r's !!!!! ddp  $part_number; next;
	my $source      		= $ref->{original_source};

	$original_hmds_num =~ s/^H//; # print $original_hmds_num, "\n"; next;
	
	my @join = ( 'request_storage|rs' => 'rs.request_id=r.id' => 'requests|r' );
	my ($sql, @bind) = $sqla->select(
        -columns  => [ qw/r.id r.request_number r.year rs.part_number rs.source/ ],
        -from     => [ -join => @join ],
        -where    => { vialId => $vial_id },
	); # ddp $sql; ddp @bind;
	my $res = $dbix->query($sql, @bind)->hash; # ddp $res; next;
	my $lab_number = sprintf '%s/%.02d', $res->{request_number}, $res->{year} - 2000;
	# check csv data matches db data:

	do {		
		warn Dumper [$lab_number, $original_hmds_num];
		next;
	} unless ( $lab_number eq $original_hmds_num )
		&& ( $part_number eq $res->{part_number} )
		&& ( $source eq $res->{source} );

	# say "update $tbl set part_number = " . $ref->{correct_part_ID} . " where vialId = $vial_id"; next;
	#	my $action = sprintf 'updated storage vialID %s part number [%s -> %s]', 
	#		$vial_id, $part_number, $ref->{correct_part_ID};
	#	say "insert into $tbl values($res->{id}, 14, $action)"; next;
	$dbix->update($tbl, 
		{ part_number => $ref->{correct_part_ID} }, 
		{ vialId => $vial_id }
	);
	{
		my $action = sprintf 'updated storage vialID %s part number [%s -> %s]', 
			$vial_id, $part_number, $ref->{correct_part_ID};
			
		my %h = (
			request_id => $res->{id},
			user_id => 14,
			action => $action, 		
		);
		$dbix->insert('request_lab_test_history', \%h);
	}

	$i++;
}	

warn "updated $i records";