use strict;	
use warnings;
use 5.18.2;
use DBI;

#my $data_source = "/Documents/Perl/leavebal.db";
my $dbh = DBI->connect('dbi:SQLite:dbname=leavebal.db', undef, undef, 
				{RaiseError =>1, AutoCommit =>0}) or die $DBI::errstr;

my $sth = $dbh->prepare('SELECT name FROM sqlite_master where type="table"');
$sth -> execute();

my @data;
say "Current tables are:";
while (@data = $sth->fetchrow_array())
	{
		print "\n";	#$data1[0] $data1[1] $data1[2] $data1[3]";
		say @data;
	}

$sth->finish;

 my $sql = <<'END SQL';
	CREATE TABLE Users(
		UsersID	integer primary key,
		UsersFirstName varchar(100),
		UsersLastName varchar(100),
		UsersEmail varchar(100) unique not null,
		UsersPWD varchar (20),
		UsersAccrualRate integer,
		UsersDate datetime DEFAULT Current_Timestamp
		);
END SQL

$dbh->do($sql);

my$sql1 = <<'End_SQL';
	
	Create TABLE LeaveBal(
		LeaveBalID integer primary key,
		UsersID integer,
		Vac integer,
		Sick integer,
		HolCred integer,
		P10 integer,
		PersHol integer,
		Excess integer,
		PDD integer,
		ITO integer
		LeaveBalDate datetime DEFAULT Current_Timestamp,
		FOREIGN KEY (UsersID) References Users(UsersID)
	);
End_SQL
	
$dbh->do($sql1);
				
my $sql2 = <<'EndSQL';	
	Create TABLE LeaveUse(
		LVUseID integer primary key,
		LeaveBalID integer,
		Amount integer,
		DayUsed varchar(20),
		MonthUsed varchar(20),
		YearUsed varchar(5),
		LeaveNotes varchar(255),
		LeaveUseDate datetime DEFAULT Current_Timestamp,
		FOREIGN KEY (LeaveBalID) References LeaveBal(LeaveBalID)
	);	
EndSQL

$dbh->do($sql2);
					
my $sth2 = $dbh->prepare('SELECT name FROM sqlite_master where type="table"');
$sth2 -> execute();

my @data2;
say "Current tables are:";
while (@data2 = $sth2->fetchrow_array())
	{
		print "\n";	
		say @data2;
	}

$sth2->finish;

$dbh->disconnect;