#!/usr/bin/perl

use strict;
use warnings;
use v5.26;
use DBI; ## postgresql access via dbi
use YAML::Tiny; ## Used to call default answers
use IO::Prompt; ## Used to prompt for the current information
use POSIX;


######################################################################
## postgresql database login configurations                          #
######################################################################
my $driver = "Pg";
my $database = "venue_visits";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $username = "postgres";
my $password = "somepassword";

#####################################################################
## get user's visit inputs                                          #
#####################################################################
my @visits_array = get_visit_information();

#####################################################################
## Connect to postgresql database                                   #
#####################################################################
my %attr = (PrintError=>0,RaiseError=>1);
my $dbh = DBI->connect($dsn,$username,$password,\%attr);

#####################################################################
## Prepare to insert data into the appropriate postgresql table     #
#####################################################################
my $sql = "INSERT INTO visits(id, arrival_date, departure_date, arrival_time, departure_time, venue, city, state, game, stake, kill, hi_lo, v_limit, buy_in, cash_out)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

my $stmt = $dbh->prepare($sql);

#####################################################################
## execute the query                                                #
#####################################################################
foreach my $visit(@visits_array) {
    if ( $stmt->execute($visit->{id}, $visit->{arrival_date}, $visit->{departure_date}, $visit->{arrival_time}, $visit->{departure_time}, $visit->{venue}, $visit->{city}, $visit->{state}, $visit->{game}, $visit->{stake}, $visit->{kill}, $visit->{hi_lo}, $visit->{v_limit}, $visit->{buy_in}, $visit->{cash_out})) {
        print( "visit $visit->{id} inserted successfully \n" );
    }
}
$stmt->finish();
 
#####################################################################
## disconnect from the postgresql database                          #
#####################################################################
$dbh->disconnect();

#####################################################################
# The below sub obtains the information required for the above      #
# actions                                                           #
#####################################################################
sub get_visit_information {
	
    ######################################################################
    ## Read the visit_configuration file to retrieve the default answers #
    ######################################################################
    my $visit_configuration = YAML::Tiny->read( 'visit_configuration.yaml' );

    #####################################################################
    # Create the id which will go into the venue_visits database        #
    # visits table                                                      #
    #####################################################################
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time) or die "Unable to obtaing from localtime: $!";
    my $id = "$year$yday$hour$min";
    $id =~ s/^\s+|\s+$//g;

    my $cmd = '';
    my @visit_sub_array;
    ## get visits from the command line
    my($arrival_date, $departure_date, $arrival_time, $departure_time,
       $venue, $city, $state, $game, $stake, $kill, $hi_lo, $v_limit,
       $buy_in, $cash_out);

    ## repeatedly ask for visit data from command line
    $arrival_date = prompt -d => "$visit_configuration->[0]->{arrival_date}", "Arrival Date: " or die "Unable to obtain arrival date: $!";
    $arrival_date =~ s/^\s+|\s+$//g;
    $departure_date = prompt -d => "$visit_configuration->[0]->{departure_date}", "Departure Date: " or die "Unable to obtaian departure date: $!";
    $departure_date =~ s/^\s+|\s+$//g;
    $arrival_time = prompt -d => "$visit_configuration->[0]->{arrival_time}", "Arrival Time: " or die "Unable to obtain arrival time: $!";
    $arrival_time =~ s/^\s+|\s+$//g;
    $departure_time = prompt -d => "$visit_configuration->[0]->{departure_time}", "Departure Time: " or die "Unable to obtain departure time: $!";
    $departure_time =~ s/^\s+|\s+$//g;
    $venue = prompt -d => "$visit_configuration->[0]->{venue}", "Venue: " or die "Unable to obtain venue: $!";
    $venue =~ s/^\s+|\s+$//g;
    $city = prompt -d => "$visit_configuration->[0]->{city}", "City: ", or die "Unable to obtain city: $!";
    $city =~ s/^\s+|\s+$//g;
    $state = prompt -d => "$visit_configuration->[0]->{state}", "State: ", or die "Unable to obtain state: $!";
    $state =~ s/^\s+|\s+$//g;
    $game = prompt -d => "$visit_configuration->[0]->{game}", "Game: ", or die "Unable to obtain game: $!";
    $game =~ s/^\s+|\s+$//g;
    $stake = prompt -d => "$visit_configuration->[0]->{stake}", "Stake: ", or die "Unable to obtain stake: $!";
    $stake =~ s/^\s+|\s+$//g;
    $kill = prompt -d => "$visit_configuration->[0]->{kill}", "Kill: ", or die "Unable to obtain kill: $!";
    $kill =~ s/^\s+|\s+$//g;
    $hi_lo = prompt -d => "$visit_configuration->[0]->{hi_lo}", "Hi Lo: " or die "Unable to obtain hi_lo: $!";
    $hi_lo =~ s/^\s+|\s+$//g;
    $v_limit = prompt -d => "$visit_configuration->[0]->{limit}", "Limit: " or die "Unable to obtain limit: $!";
    $v_limit =~ s/^\s+|\s+$//g;
    $buy_in = prompt -d => "$visit_configuration->[0]->{buy_in}", "Buy In: " or die "Unable to obtain buy_in: $!";
    $buy_in =~ s/^\s+|\s+$//g;
    $cash_out = prompt -d => "$visit_configuration->[0]->{cash_out}", "Cash Out: " or die "Unable to obtain cash_out: $!";
    $cash_out =~ s/^\s+|\s+$//g;

    my %visit_sub_hash = (id=>$id, arrival_date=>$arrival_date, departure_date=>$departure_date,
	                  arrival_time=>$arrival_time, departure_time=>$departure_time,
		          venue=>$venue, city=>$city, state=>$state, game=>$game,
			  stake=>$stake, kill=>$kill, hi_lo=>$hi_lo, v_limit=>$v_limit,
			  buy_in=>$buy_in, cash_out=>$cash_out );

    push(@visit_sub_array,\%visit_sub_hash);

    return @visit_sub_array;
}

1;

__END__

=head1 NAME

 build_sql_entries 

=cut

=head1 SYNOPSIS

 The build_sql_entries file allows a user to enter poker statistics into a postgresql database. The user is prompeted for the correct information. The configuration file is visit_configuration.yaml.

=cut

=head1 DESCRIPTION

    The information is formatted as necessary. More description tbd.

=cut

=head1 AUTHOR

   Sherman L. Willden
=cut

=head1 BUGS

 TDB 

=cut

=head1 SEE ALSO

  Any PERL book and regular expression book

=cut

=head1 COPYRIGHT

build_sql_entries: Copyright (C) 2018, Sherman Willden.  The file is licensed under the terms of the GNU Lesser General Public License 2.1. See <http://www.gnu.org/licenses/lgpl-2.1.html>.

This program is free software; you can redistribute it and/or modify it under the terms of the Artistic License 2.0. You can redistribute it and/or modify it under the same terms as Perl 5.26.0

This program is distributed in the hope that it will be useful, but it is provided “as is” and without any express or implied warranties. 

=cut