#!/usr/bin/perl

use DBI;
use strict;
use warnings;
use Time::Piece;

my $driver   = "SQLite";
my $database = "cartask.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) # connect to db
   or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(CREATE TABLE IF NOT EXISTS COMPANY
   (VIN INT PRIMARY KEY,
      PTS        BLOB,
      RELEASE         TEXT,
      DISPOSAL         TEXT);); #create a table if not exist do nothing

my $rv = $dbh->do($stmt);
if($rv < 0) {
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}
sub ADD { 				#create a function
	print "Enter VIN: ";
        my $VIN = <STDIN>;   		#read next row
        chomp $VIN; 			#avoid \n on last field

        print "Enter name of image to add(e.g., car.jpg): ";
        my $IMG = <STDIN>;		#read next row
        chomp $IMG; 			#avoid \n on last field

        open IMAGE, $IMG or die $!; 	#we open an image
        my ($image, $buff);
        while(read IMAGE, $buff, 1024) {#we read binary data from the image file
                $image .= $buff;
        }

        print "Enter date of release(e.g., YYYY-MM-DD): ";
        my $RELstr = <STDIN>;		#read next row
        my $REL = Time::Piece->strptime($RELstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
        chomp $REL; 			#avoid \n on last field

        print "Enter date of disposal(e.g., YYYY-MM-DD): ";
        my $DISstr = <STDIN>;		#read next row
        my $DIS = Time::Piece->strptime($DISstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
        chomp $DIS;			#avoid \n on last field

        my $sth = $dbh->prepare("INSERT INTO COMPANY(VIN, PTS, RELEASE, DISPOSAL) VALUES(?,?,?,?)");
        $sth->execute($VIN, $image, $REL, $DIS);
	#The two code lines prepare the SQL statement, bind the image data to the statement and execute it.
	print "Sucessfully added car into db\n";
}

sub SELECT { 								#create a function
	print "Enter date to look between that date\n";
	print "Enter date of release: ";
	my $RELIstr = <STDIN>;						#read next row
	my $RELI = Time::Piece->strptime($RELIstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
	print "Enter date of disposal: ";
	my $DISPstr = <STDIN>;						#read next row
	my $DISP = Time::Piece->strptime($DISPstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
	my $sth = qq(SELECT * from COMPANY where RELEASE BETWEEN '$RELI' AND '$DISP' or DISPOSAL BETWEEN '$RELI' AND '$DISP' OR RELEASE < '$RELI' AND DISPOSAL > '$DISP');
	my $stm = $dbh->prepare( $sth );
	my $rv = $stm->execute() or die $DBI::errstr;
	#The three code lines prepare the SQL statement and execute it.
	if($rv < 0) {
   	print $DBI::errstr;
	}

	while(my @row = $stm->fetchrow_array) { 			# loop over all row that we fit with our date and print them
      		print "IMG = ". $row[1] . "\n";
      		print "ID(VIN) = ". $row[0] . "\n";
      		print "Data of release = ". $row[2] ."\n";
      		print "Date of disposal =  ". $row[3] ."\n\n";
	}
}

while(1) { 								#
	print "Enter number:\n";
	print "1 add new value to db\n";
	print "2 select value in db\n";
	print "3 exit\n";
	my $name = <STDIN>;
	chomp $name;
	if ($name eq '1') {
		ADD();
	}
	elsif($name eq '2') {
		SELECT();
	}
	elsif($name eq '3') {
		last;
	}
	else {
	print "you need write down only number";
	}

}
print "Operation done successfully\n";
$dbh->disconnect();