# updates hilis4.diagnosis.ngis_indication_id column using $src file
# need to have run setup/geneq.sql on diagnosis table
# safe to re-run, just updates existing column data

# switch on sql output to console:
$ENV{SQL_TRACE}  = $opt_q;

#===============================================================================
my $src = 'Diagnostic_terms_coded_to_GeNEQ_clinical_indication.xlsx';
#===============================================================================

use Spreadsheet::Read qw(ReadData rows);
use FindBin qw($Bin);
use Data::Printer;
use Modern::Perl;

use lib '/home/raj/perl-lib';
use Local::DB;

my $dbix = Local::DB->dbix({ dbname => 'hilis4' });
my $xls  = ReadData($Bin.'/'.$src) || die "cannot read $src"; # p $xls; exit;

# active diagnoses:
my $active = $xls->[2];      # p $active; # 2nd sheet
# inactive diagnoses:
my $inactive = $xls->[3];    # p $inactive; # 3rd sheet
my @rows = ( rows($active), rows($inactive) ); # p @rows; exit;
my $headers = shift @rows; # p $headers; exit;

my $diagnoses 
    = $dbix->select('diagnoses', [ qw/name id/ ])->map; # p $diagnoses;
my $ngis_ids  
    = $dbix->select('ngis_clinical_indications', [ qw/code id/ ])->map; # p $ngis_ids;

ROW:
for my $row (@rows) { # p $row; next; # arrayref
    my ($diagnosis, $indication) = @$row[0,1]; # 1st 2
        # p $diagnosis; p $indication;
    # skip row if text not M + digits (eg Clinical trials, Non-diagnostic, etc):
    next ROW unless $indication && $indication =~ /^M\d+/; # ie non-ICDO3
        # p $diagnosis; p $indication;

    $diagnosis =~ s/'/'/;
    $diagnosis =~ s/&/&/g;
    $diagnosis =~ s/&lt;/</g;
    $diagnosis =~ s/&gt;/>/g; # p $diagnosis;

    my $diagnosis_id = $diagnoses->{$diagnosis};
    say "no id for $diagnosis" and next ROW if not $diagnosis_id;

    my $indication_id = $ngis_ids->{$indication};
    say "no indication_id for $indication" and next ROW if not $indication_id;

    $dbix->update('diagnoses', { ngis_indication_id => $indication_id },
           { id => $diagnosis_id } );
}

# manual updates (failed to match >= symbol):
$dbix->update('diagnoses', { ngis_indication_id => 27 },
    { name => { rlike => 'MRD-(nega|posi)tive at iwCLL threshold' } });