#!/usr/bin/perl
use strict;

#Database connection
use DBI;

my $dsn = "DBI:mysql:fall2018:localhost";
my $dbUsername  = "root";
my $dbPassword = "password";

#Connect or die
my $dbh = DBI->connect($dsn, $dbUsername, $dbPassword) or die("Error connecting to the database.\n");


#Firstly want to check if user is root or not.
my $rootAccess = 0;
print  "Welcome, enter a username.\n";
my $username = <STDIN>;
chomp $username;
print "Enter a password.\n";
my $password = <STDIN>;
chomp $password;
if($username eq "root" && $password eq "password") {
	print "Successful root login. \n";
	$rootAccess = 1;
} else {
	print "Login failed\n";
	exit(0);
}

#If the user is identified as root
if($rootAccess == 1) {

	print "The actions you can preform are as follows: 1. Add an entry, 2. Update an entry, 3. Delete an entry, 4. Preform a search, -1. To exit.\n";
	my $rootInput = <STDIN>;
	chomp $rootInput;

  #Root chose to add an entry
	if($rootInput == 1) {
		print "Enter the artist.\n";
		my $artist = <STDIN>;
		chomp $artist;
		print "Enter the title.\n";
		my $title = <STDIN>;
		chomp $title;
		print "Enter the album.\n";
		my $album = <STDIN>;
		chomp $album;
		print "Enter the time.\n";
		my $time = <STDIN>;
		chomp $time;
		#Catch bad input
		if($artist eq "" or $title eq "" or $album eq "" or $time eq "") {
			print "Must have entered a valid input."

		}

		#Insert data into the table
		my $query = "INSERT INTO songs (artist, title, album, time) VALUES (?, ?, ?, ?)";
		my $statement = $dbh->prepare($query);
		$statement->execute($artist, $title, $album, $time);

  }
  #Root chose to update
	if($rootInput == 2) {
		print "List of all the entries are. \n";
		my $query =  "SELECT *  FROM songs";
		my $statement = $dbh->prepare($query);
		$statement->execute();
		while(my @data = $statement->fetchrow_array()) {
			my $exID = $data[0];
			my $exArtist = $data[1];
			my $exTitle = $data[2];
			my $exAlbum = $data[3];
			my $exTime = $data[4];
			print "Song ID: $exID, Artist: $exArtist Title: $exTitle, Album: $exAlbum, Time: $exTime \n";

		}

		#Prompt user to enter what info they want to change
		print "Enter the Song ID of the entry you would like to change\n";
		my $id = <STDIN>;
		chomp $id;
		print "Enter in blank for no change or enter in the change at the desired section.\n Artist: ";
		my $artist = <STDIN>;
		chomp $artist;
		print "Title: ";
		my $title = <STDIN>;
		chomp $title;
		print "Album: ";
		my $album = <STDIN>;
		chomp $album;
		print "Time: ";
		my $time =  <STDIN>;
		chomp $time;

		my $query =  "SELECT songid, artist, title, album, time  FROM songs WHERE songID=$id";
		my $statement = $dbh->prepare($query);
		$statement->execute();
		my @data = $statement->fetchrow_array();

		if($artist eq "") {
			$artist = $data[1];
		}
		if($title eq "") {
			$title = $data[2];
		}
		if($album eq "") {
			$album = $data[3];
		}
		if($time eq "") {
			$time = $data[4];
		}

		#Updating the table with new values
		my $updateQuery = "UPDATE songs SET artist = ?, title = ?, album = ?, time = ? WHERE songid=$id";
		my $updateStatement = $dbh->prepare($updateQuery);
		$updateStatement->execute($artist, $title, $album, $time);

		#Reprint the table
		my $query =  "SELECT *  FROM songs";
		my $statement = $dbh->prepare($query);
		$statement->execute();
		while(my @data = $statement->fetchrow_array()) {
			my $exID = $data[0];
			my $exArtist = $data[1];
			my $exTitle = $data[2];
			my $exAlbum = $data[3];
			my $exTime = $data[4];
			print "Song ID: $exID, Artist: $exArtist Title: $exTitle, Album: $exAlbum, Time: $exTime \n";

		}
}

#Delete from the table
	if($rootInput == 3) {
		print "Enter the Song id of what you would like to delete. Enter a empty string to do nothing\n";
		my $query =  "SELECT *  FROM songs";
		my $statement = $dbh->prepare($query);
		$statement->execute();
		while(my @data = $statement->fetchrow_array()) {
			my $exID = $data[0];
			my $exArtist = $data[1];
			my $exTitle = $data[2];
			my $exAlbum = $data[3];
			my $exTime = $data[4];
			print "Song ID: $exID, Artist: $exArtist Title: $exTitle, Album: $exAlbum, Time: $exTime \n";

		}
		print "Enter the Song ID you would like to delete.\n";
		my $delSong =  <STDIN>;
		chomp $delSong;

		#Check to see if string is empty
		if($delSong eq "") {

		} else {
			my $query = "DELETE FROM songs WHERE songid=$delSong";
			my $statement = $dbh->prepare($query);
			$statement->execute();
			print "DELETED.\n";
		}

		my $query =  "SELECT *  FROM songs";
		my $statement = $dbh->prepare($query);
		$statement->execute();
		while(my @data = $statement->fetchrow_array()) {
			my $exID = $data[0];
			my $exArtist = $data[1];
			my $exTitle = $data[2];
			my $exAlbum = $data[3];
			my $exTime = $data[4];
			print "Song ID: $exID, Artist: $exArtist Title: $exTitle, Album: $exAlbum, Time: $exTime \n";

		}


	}
	#Searching algorithm
	if($rootInput == 4) {
		print "Press 1 to search by ARTIST or TITLE and 2 for ALBUM search";
		my $searchType = <STDIN>;
		chomp $searchType;
		if($searchType == 1) {
			print "Enter a search string.\n";
			my $search = <STDIN>;
			chomp $search;
			my $query = "SELECT * FROM songs WHERE artist RLIKE \'$search\'";
			my $statement = $dbh->prepare($query);
			$statement->execute();
			print "EXECUTING ARTIST SEARCH\n";
			while(my @data = $statement->fetchrow_array()) {
				my $exID = $data[0];
				my $exArtist = $data[1];
				my $exTitle = $data[2];
				my $exAlbum = $data[3];
				my $exTime = $data[4];
				print "Song ID: $exID, Artist: $exArtist Title: $exTitle, Album: $exAlbum, Time: $exTime \n";

			}

			print "\n\nEXECUTING TITLE SEARCH\n";
			my $query = "SELECT * FROM songs WHERE title RLIKE \'$search\'";
			my $statement = $dbh->prepare($query);
			$statement->execute();
			while(my @data = $statement->fetchrow_array()) {
				my $exID = $data[0];
				my $exArtist = $data[1];
				my $exTitle = $data[2];
				my $exAlbum = $data[3];
				my $exTime = $data[4];
				print "Song ID: $exID, Artist: $exArtist Title: $exTitle, Album: $exAlbum, Time: $exTime \n";

			}
		}
		if($searchType == 2 ) {
			print "Enter a search string.\n";
			my $search = <STDIN>;
			chomp $search;
			my $query = "SELECT * FROM songs WHERE album RLIKE \'$search\'";
			my $statement = $dbh->prepare($query);
			$statement->execute();
			print "EXECUTING ALBUM SEARCH\n";
			while(my @data = $statement->fetchrow_array()) {
				my $exID = $data[0];
				my $exArtist = $data[1];
				my $exTitle = $data[2];
				my $exAlbum = $data[3];
				my $exTime = $data[4];
				print "Song ID: $exID, Artist: $exArtist Title: $exTitle, Album: $exAlbum, Time: $exTime \n";

			}

		}

	}
}
#Discconnect from DB
$dbh->disconnect();