# updates hilis4.ngis_diagnosis table using $src file
# safe to re-run, just updates existing data UNLESS data updated through app!!

use strict;
use warnings;

use Getopt::Std;
getopts('q');
our($opt_q);

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

# clear existing:
$dbix->dbh->do('TRUNCATE TABLE ngis_diagnosis');

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->insert('ngis_diagnosis',
        { diagnosis_id => $diagnosis_id, ngis_indication_id => $indication_id });
}