use strict;
use warnings;
use Term::ReadKey;
use DBI;
use List::Util qw( min max );
use Data::Types qw/:all/;
use DateTime;
use DateTime::Format::Strptime;
use Date::Calc qw(:all);

my $dates_file = $ARGV[0];

my $dsn = "DBI:mysql:DiabetesDB";
print "Please give your username:";
my $username =  <STDIN>;
chomp $username;

ReadMode(0);
print "Password for user \"$username:\"";        
ReadMode('noecho');
my $password = ReadLine(0);
ReadMode 'normal';
chomp $password;

# connect to MySQL database
my %attr =  (   PrintError=>0,      # turn off error reporting via warn()
                RaiseError=>1       # turn on error reporting via die()           
            );

my $dbh = DBI->connect($dsn,$username,$password, \%attr);
print "\nUser \"".$username."\" is connected to the \"DiabetesDB\" database.\n";

my ($patient_id_date, $specific_date, $wanted_date, $type_of_search, $max_window, $sql_query)='';

open DATES, $dates_file;
while(<DATES>)
{
    chomp;
    if($_=~/^(.*?)\t(.*?)\t(\d+)\t(.*)/)
    {
        $patient_id_date=$1;
        $specific_date=$2;
        $max_window=$3;
        $type_of_search=$4;

        my %p; 
        @p{qw(year month day)} = split /\//, $specific_date;
        my $dt = DateTime->new(%p);

        my $wanted_date = $dt->clone->add(months => $max_window)->strftime('%Y/%m/%d'); #this is the date I am interested in, +X months

        if($type_of_search eq 'past')
        {
            $sql_query = "SELECT * FROM MEASUREMENTS WHERE MEASUREMENTS.patient_id='".$patient_id_date.
                         "' AND MEASUREMENTS.measure_date<'".$wanted_date."' ORDER BY MEASUREMENTS.measure_date DESC LIMIT 1";
        }

        elsif($type_of_search eq 'future')
        {
            $sql_query = "SELECT * FROM MEASUREMENTS WHERE MEASUREMENTS.patient_id='".$patient_id_date. 
                         "' AND MEASUREMENTS.measure_date>'".$wanted_date."' ORDER BY MEASUREMENTS.measure_date ASC LIMIT 1";
        }

        elsif($type_of_search eq 'both')
        {
            $sql_query = "(SELECT * FROM MEASUREMENTS WHERE MEASUREMENTS.patient_id='".$patient_id_date. 
                         "' AND MEASUREMENTS.measure_date<'".$wanted_date."' ORDER BY MEASUREMENTS.measure_date DESC LIMIT 1)".
                         "  UNION (SELECT * FROM MEASUREMENTS WHERE MEASUREMENTS.patient_id='".$patient_id_date.
                         "' AND MEASUREMENTS.measure_date>'".$wanted_date."' ORDER BY MEASUREMENTS.measure_date ASC LIMIT 1)";
        
        }

        my $sth = $dbh->prepare($sql_query);
        $sth->execute();
        $sth->dump_results( );
        $sth->finish();
        print "\n";
    }
}
close DATES;