#!/usr/bin/perl
use warnings;
use strict;
use DBI;

my $database = 'botany';
my $host = '10.1.4.29';
my $username = 'anonymous';
my $password = 'oxalis';
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", $username, $password) or die "Cannot connect to database: $DBI::errstr\n";

while (1) {
	system("clear");
	print "\nWelcome to the Botany database update program.\nPlease choose from the list of update options on the menu below.\n";
	print "\nMenu:
	1) Insert new Group
	2) Insert new Trait
	3) Insert new Plant
	4) Insert new Trait->Plant Mapping
	5) Delete Group
	6) Delete Trait
	7) Delete Plant
	8) Delete Trait->Plant Mapping
	9) Quit
	\nChoice: ";

	chomp( my $action = <STDIN> );

	if    ($action == 1) { insertGroup() }
	elsif ($action == 2) { insertTrait() }
	elsif ($action == 3) { insertPlant() }
	elsif ($action == 4) { insertMapping() }
	elsif ($action == 5) { deleteGroup() }
	elsif ($action == 6) { deleteTrait() }
	elsif ($action == 7) { deletePlant() }
	elsif ($action == 8) { deleteMapping() }
	elsif ($action == 9) { last; }
	else { print "Unacceptable value.  Please try again."; sleep(1); }
}

$dbh->disconnect();

### Only subroutines below here

sub insertGroup {

	### Declare some variables

	my $sth;
	my $currentGroups;
	my ($name, $description, $exclusive);
	my $currentGIDs;
	my ($currentHighestGID, $newGID);

	### Get the full list of Names currently in the "groups" table
	### and print them out so the user can try to avoid repeating names

	print "The current groups in the database are:\n\n";
	$sth = $dbh->prepare( "SELECT gID, Name FROM groups" );
	$sth->execute();
	$currentGroups = $sth->fetchall_arrayref();
	foreach (@$currentGroups) {
		print "@$_\n";
	}
	print "\n";

	### Prompt the user for a group name and description

	print "New Group Name [one word, 1-32 characters only]: ";
	chomp( $_ = <STDIN> );
	$name = $_ || "NULL";
	print "Description [text or URL, 1-32 characters only, may be left blank]: ";
	chomp( $_ = <STDIN> );
	$description = $_ || "NULL";

	### Ask whether this field is exclusive (i.e., a radiobutton or checkbox field)

	print "Is this a RadioButton or CheckBox field [answer with 0 or 1 respectively]: ";
	chomp( $_ = <STDIN> );
	$exclusive = $_ || 0;

	### Calculate the new group's gID by finding the currentHighestGID
	### and adding +1

	$sth = $dbh->prepare( "SELECT gID FROM groups" );
	$sth->execute();
	$currentGIDs = $sth->fetchall_arrayref();
	($currentHighestGID) = @{ ${$currentGIDs}[-1] || [0] };
	$newGID = $currentHighestGID + 1;

	$sth = $dbh->prepare( "INSERT INTO groups VALUES (?, ?, ?, ?)" );
	$sth->bind_param(1, $newGID);
	$sth->bind_param(2, $name);
	$sth->bind_param(3, $description);
	$sth->bind_param(4, $exclusive);
	$sth->execute();
}

sub insertTrait {

	### Declare some variables

	my $sth;
	my ($currentTraits, $currentGroups);
	my ($name, $description);
	my $currentTIDs;
	my ($currentHighestTID, $newTID, $newGID);

	### Get the full list of Names currently in the "traits" table
	### and print them out so the user can try to avoid repeating names

	print "The current traits in the database are:\n\n";
	$sth = $dbh->prepare( "SELECT tID, gID, Name FROM traits" );
	$sth->execute();
	$currentTraits = $sth->fetchall_arrayref();
	foreach (@$currentTraits) {
		print "@$_\n";
	}
	print "\n";

	### Prompt the user for a trait name and description

	print "New Trait Name [one word, 1-32 characters only]: ";
	chomp( $_ = <STDIN> );
	$name = $_ || "NULL";
	print "Description [text or URL, 1-32 characters only, may be left blank]: ";
	chomp( $_ = <STDIN> );
	$description = $_ || "NULL";

	### Determine to which group this trait will belong

	### Get the full list of Names currently in the "groups" table
	### and print them out so the user can select one easily

	print "To which group does this trait belong?\n\n";
	$sth = $dbh->prepare( "SELECT gID, Name FROM groups" );
	$sth->execute();
	$currentGroups = $sth->fetchall_arrayref();
	foreach (@$currentGroups) {
		print "@$_\n";
	}

	print "\nChoice [answer with its numeric ID]: ";
	chomp( $newGID = <STDIN> );

	### Calculate the new trait's tID by finding the currentHighestTID
	### and adding +1

	$sth = $dbh->prepare( "SELECT tID FROM traits" );
	$sth->execute();
	$currentTIDs = $sth->fetchall_arrayref();
	($currentHighestTID) = @{ ${$currentTIDs}[-1] || [0] };
	$newTID = $currentHighestTID + 1;

	$sth = $dbh->prepare( "INSERT INTO traits VALUES (?, ?, ?, ?)" );
	$sth->bind_param(1, $newTID);
	$sth->bind_param(2, $newGID);
	$sth->bind_param(3, $name);
	$sth->bind_param(4, $description);
	$sth->execute();
}


sub insertPlant {

	### Declare some variables

	my $sth;
	my $currentPlants;
	my ($commonName, $latinName, $description);
	my $currentPIDs;
	my ($currentHighestPID, $newPID);

	### Get the full list of Plants currently in the "plants" table
	### and print them out so the user can try to avoid repeating names

	print "The current plants in the database are:\n\n";
	$sth = $dbh->prepare( "SELECT pID, Common_Name, Latin_Name FROM plants" );
	$sth->execute();
	$currentPlants = $sth->fetchall_arrayref();
	foreach (@$currentPlants) {
		print "@$_\n";
	}
	print "\n";

	### Prompt the user for the plant's names and description

	print "New Plant Common Name [1-32 characters only]: ";
	chomp( $_ = <STDIN> );
	$commonName = $_ || "NULL";
	print "New Plant Latin Name [1-32 characters only]: ";
	chomp( $_ = <STDIN> );
	$latinName = $_ || "NULL";
	print "Description [text or URL, 1-32 characters only, may be left blank]: ";
	chomp( $_ = <STDIN> );
	$description = $_ || "NULL";

	### Calculate the new plant's pID by finding the currentHighestPID
	### and adding +1

	$sth = $dbh->prepare( "SELECT pID FROM plants" );
	$sth->execute();
	$currentPIDs = $sth->fetchall_arrayref();
	($currentHighestPID) = @{ ${$currentPIDs}[-1] || [0] };
	$newPID = $currentHighestPID + 1;

	$sth = $dbh->prepare( "INSERT INTO plants VALUES (?, ?, ?, ?)" );
	$sth->bind_param(1, $newPID);
	$sth->bind_param(2, $commonName);
	$sth->bind_param(3, $latinName);
	$sth->bind_param(4, $description);
	$sth->execute();
}

sub insertMapping {

	### Declare some variables

	my $sth;
	my ($currentMappings, $currentTraits, $currentPlants);
	my $currentMIDs;
	my ($currentHighestMID, $newMID, $newTID, $newPID);

	### Get the full list of Plants currently in the "plants" table
	### and print them out so the user can make his selection

	print "Which plant's traits do you want to update?\n\n";
	$sth = $dbh->prepare( "SELECT pID, Common_Name, Latin_Name FROM plants" );
	$sth->execute();
	$currentPlants = $sth->fetchall_arrayref();
	foreach (@$currentPlants) {
		print "@$_\n";
	}
	print "\n";

	print "Choice [answer with its numeric ID]: ";
	chomp( $newPID = <STDIN> );

	### Get the full list of Mappings currently in the "mappings" table
	### and print them out so the user can make his selection

	print "The current mappings for this plant in the database are:\n\n";
	$sth = $dbh->prepare( "SELECT mID, tID FROM mappings WHERE pID = ?" );
	$sth->bind_param(1, $newPID);
	$sth->execute();
	$currentMappings = $sth->fetchall_arrayref();
	foreach (@$currentMappings) {
		print "@$_\n";
	}
	print "\n";

	### Get the full list of Traits currently in the "traits" table
	### and print them out so the user can make his selection

	print "The current traits in the database are:\n\n";
	$sth = $dbh->prepare( "SELECT tID, gID, Name FROM traits" );
	$sth->execute();
	$currentTraits = $sth->fetchall_arrayref();
	foreach (@$currentTraits) {
		print "@$_\n";
	}
	print "\n";

	print "Choice [answer with its numeric ID]: ";
	chomp( $newTID = <STDIN> );

	### Calculate the new mapping's mID by finding the currentHighestMID
	### and adding +1

	$sth = $dbh->prepare( "SELECT mID FROM mappings" );
	$sth->execute();
	$currentMIDs = $sth->fetchall_arrayref();
	($currentHighestMID) = @{ ${$currentMIDs}[-1] || [0] };
	$newMID = $currentHighestMID + 1;

	$sth = $dbh->prepare( "INSERT INTO mappings VALUES (?, ?, ?)" );
	$sth->bind_param(1, $newMID);
	$sth->bind_param(2, $newTID);
	$sth->bind_param(3, $newPID);
	$sth->execute();
}

sub deleteGroup {

	### Declare some variables

	my $sth;
	my $currentGroups;
	my $thisGID;
	my $destroyedTraits;

	### Get the full list of Names currently in the "groups" table
	### and print them out so the user can pick which one to delete

	print "Which group do you want to delete?\n\n";
	$sth = $dbh->prepare( "SELECT gID, Name FROM groups" );
	$sth->execute();
	$currentGroups = $sth->fetchall_arrayref();
	foreach (@$currentGroups) {
		print "@$_\n";
	}
	print "\n";

	print "Choice [answer with its numeric ID]: ";
	chomp( $thisGID = <STDIN> );

	$sth = $dbh->prepare( "DELETE FROM groups WHERE gID = ?" );
	$sth->bind_param(1, $thisGID);
	$sth->execute();

	$sth = $dbh->prepare( "SELECT tID FROM traits WHERE gID = ?" );
	$sth->bind_param(1, $thisGID);
	$sth->execute();
	$destroyedTraits = $sth->fetchall_arrayref();

	$sth = $dbh->prepare( "DELETE FROM traits WHERE gID = ?" );
	$sth->bind_param(1, $thisGID);
	$sth->execute();

	$sth = $dbh->prepare( "DELETE FROM mappings WHERE tID = ?" );
	foreach (@$destroyedTraits) {
		$sth->bind_param(1, ${$_}[0]);
		$sth->execute();
	}

}

sub deleteTrait {

	### Declare some variables

	my $sth;
	my $currentTraits;
	my $thisTID;

	### Get the full list of Names currently in the "traits" table
	### and print them out so the user can pick which one to delete

	print "Which trait do you want to delete?\n\n";
	$sth = $dbh->prepare( "SELECT tID, gID, Name FROM traits" );
	$sth->execute();
	$currentTraits = $sth->fetchall_arrayref();
	foreach (@$currentTraits) {
		print "@$_\n";
	}
	print "\n";

	print "Choice [answer with its numeric ID]: ";
	chomp( $thisTID = <STDIN> );

	$sth = $dbh->prepare( "DELETE FROM traits WHERE tID = ?" );
	$sth->bind_param(1, $thisTID);
	$sth->execute();

	$sth = $dbh->prepare( "DELETE FROM mappings WHERE tID = ?" );
	$sth->bind_param(1, $thisTID);
	$sth->execute();
}

sub deletePlant {

	### Declare some variables

	my $sth;
	my $currentPlants;
	my $thisPID;

	### Get the full list of Names currently in the "plants" table
	### and print them out so the user can pick which one to delete

	print "Which plant do you want to delete?\n\n";
	$sth = $dbh->prepare( "SELECT pID, Common_Name, Latin_Name FROM plants" );
	$sth->execute();
	$currentPlants = $sth->fetchall_arrayref();
	foreach (@$currentPlants) {
		print "@$_\n";
	}
	print "\n";

	print "Choice [answer with its numeric ID]: ";
	chomp( $thisPID = <STDIN> );

	$sth = $dbh->prepare( "DELETE FROM plants WHERE pID = ?" );
	$sth->bind_param(1, $thisPID);
	$sth->execute();

	$sth = $dbh->prepare( "DELETE FROM mappings WHERE pID = ?" );
	$sth->bind_param(1, $thisPID);
	$sth->execute();

}

sub deleteMapping {

	### Declare some variables

	my $sth;
	my $currentMappings;
	my $thisMID;

	### Get the full list of Mappings currently in the "mappings" table
	### and print them out so the user can pick which one to delete

	print "Which mapping do you want to delete?\n\n";
	$sth = $dbh->prepare( "SELECT mID, tID, pID FROM mappings" );
	$sth->execute();
	$currentMappings = $sth->fetchall_arrayref();
	foreach (@$currentMappings) {
		print "@$_\n";
	}
	print "\n";

	print "Choice [answer with its numeric ID]: ";
	chomp( $thisMID = <STDIN> );

	$sth = $dbh->prepare( "DELETE FROM mappings WHERE mID = ?" );
	$sth->bind_param(1, $thisMID);
	$sth->execute();
}
