#!/usr/bin/perl

#####################
# A simple perl interface to the episodes database.
######################

use DBI;

$dbh = DBI->connect("dbi:Oracle:", 'username/password');
$dbh->{AutoCommit} = 0;

## main loop ##
while (1) {
    my $input = &rl(&choices);
    &queries() if ($input =~ /a/i);
    &insertActor() if ($input =~ /b/i);
    &insertSeries() if ($input =~ /c/i);
    &updateEpisode() if ($input =~ /d/i);
    &deleteEpisode() if ($input =~ /e/i);
    &quit() if ($input =~ /(q|f)/i);
}


## read line ##
sub rl($$) {
    local $input;
    do {
	print $_[0];
	if ($_[1] ne undef) { print "(Default " . $_[1] . "): "; }
	$input = <STDIN>;
	chop($input);
	$input = $_[1] if ($input eq undef && $_[1] ne undef);
    } while ($input eq undef);
    return ($input);
}

## useful queries ##
sub queries() {
    &selections();
    my $input = &rl(&selections);

    if ($input =~ /a/i) {

	my $actor = &rl("Enter an actor name: ");
	my $data = $dbh->prepare("select website from actors where name = " . $dbh->quote($actor));
	$data->execute;

	print "-" x 15, "\n";
	print "Website for actor $actor: \n";
	while (my $row = $data->fetchrow()) {
	    print $row,"\n";
	}
	print "-" x 15, "\n";
	$data->finish();
    }


    if ($input =~ /b/i) {

	my $ep = &rl("Enter an episode title: ");
	my $st = &rl("Enter series title: ");
	my $sc = &rl("Enter series creator: ");

	my $data = $dbh->prepare("select description, multimediaURL from episodes where title = " . $dbh->quote($ep) . 
				 " AND seriesTitle = " . $dbh->quote($st) . 
				 " AND seriesCreator = " . $dbh->quote($sc));
	$data->execute;

	my ($desc, $url) = $data->fetchrow();
	
	if ($desc ne undef) {
	    print "-" x 15, "\n";
	    print "Description for episode $ep of series $st:\n";
	    print "| ", $url,"\n| ",$desc,"\n";
	    print "-" x 15, "\n";

	} else {
	    print "No information for $ep of series $st, creator $sc\n";
	}
	
	$data->finish();
    }


    if ($input =~ /c/i) {

	my $ch = &rl("Enter a character: ");
	my $st = &rl("Enter series title: ");
	my $sc = &rl("Enter series creator: ");

	my $data = $dbh->prepare("select title, description, multimediaURL from episodes,featuring where" .
				 " episodes.seriesTitle = " . $dbh->quote($st) . 
				 " AND episodes.seriesCreator = " . $dbh->quote($sc) . 
				 " AND featuring.seriesTitle = episodes.seriesTitle" . 
				 " AND featuring.seriesCreator = episodes.seriesCreator" . 
				 " AND featuring.characterName = " . $dbh->quote($ch));

	$data->execute;
	while (my ($title, $desc, $url) = $data->fetchrow()) {
	    print "-" x 15, "\n";
	    print "| Description for episode $title of series $st:\n";
	    print "| ", $url,"\n| ",$desc,"\n";
	}
	print "-" x 15, "\n";
	$data->finish();
    }


}

sub insertActor() {
    my $actorName = &rl("Enter actor name: ");
    my $actorWebsite = &rl("Enter actor website: ");

    my $rows_affected = 
	$dbh->do("INSERT INTO actors (name, website) VALUES (" . 
		 $dbh->quote($actorName) . "," . $dbh->quote($actorWebsite) . 
		 ")");

    if ($rows_affected == 1) { print "Inserted $actorName, $actorWebsite into database\n"; }

    $dbh->commit;
}

sub insertSeries() {
    my $st = &rl("Enter series title: ");
    my $sc = &rl("Enter series creator: ");
    my $wb = &rl("Enter series website: ");
    my $sd = &rl("Enter series premier date (mm-dd-yyyy): ", "01-01-1990");
    my $ed = &rl("Enter series finale date (mm-dd-yyyy): ", "01-01-1990");

    $sd =~ s/\W/\-/g;
    $ed =~ s/\W/\-/g;

    my $rows_affected = 
	$dbh->do("INSERT INTO series (title, creator, website, startdate, enddate) VALUES (" . 
		 $dbh->quote($st) . "," . $dbh->quote($sc) . "," . $dbh->quote($wb) . "," .
		 "TO_DATE(" . $dbh->quote($sd) . ", 'mm-dd-yyyy'), TO_DATE(" . 
		 $dbh->quote($ed) . ", 'mm-dd-yyyy'))");

    if ($rows_affected == 1) { print "Inserted ($st,$sc,$wb,$sd,$ed) into database\n"; }

    $dbh->commit;

}

sub updateEpisode() {
    my $et = &rl("Enter episode title: ");
    my $st = &rl("Enter series title: ");
    my $sc = &rl("Enter series creator: ");

    my $cursor = $dbh->prepare("SELECT title,to_char(airdate,'mm-dd-yyyy'),quality,description,multimediaURL,season,seriesTitle,seriesCreator FROM episodes WHERE title = " . $dbh->quote($et) . " AND seriesTitle = " . $dbh->quote($st) . " AND seriesCreator = " . $dbh->quote($sc));

    $cursor->execute();

    my @old = $cursor->fetchrow_array();
    
    $cursor->finish();

    if (scalar(@old) <= 0) {
	print "No episode like that in database.\n";
	return;
    }
    
    $new[0] = $dbh->quote(&rl("Enter new episode title ",$old[0]));
    $new[1] = $dbh->quote(&rl("Enter new airdate ",$old[1]));
    $new[2] = $dbh->quote(&rl("Enter new quality ",$old[2]));
    $new[3] = $dbh->quote(&rl("Enter new description ",$old[3]));
    $new[4] = $dbh->quote(&rl("Enter new multimedia url ",$old[4]));
    $new[5] = $dbh->quote(&rl("Enter new season ",$old[5]));
    $new[6] = $dbh->quote(&rl("Enter new series title ",$old[6]));
    $new[7] = $dbh->quote(&rl("Enter new series creator ",$old[7]));

    $old[0] = $dbh->quote($old[0]);
    $old[6] = $dbh->quote($old[6]);
    $old[7] = $dbh->quote($old[7]);

    my $update = <<END_OF_UPDATE;
    UPDATE episodes SET title = $new[0],
    airdate = TO_DATE($new[1],'mm-dd-yyyy'),
    quality = $new[2],
    description = $new[3],
    multimediaURL = $new[4],
    season = $new[5],
    seriesTitle = $new[6],
    seriesCreator = $new[7]
    WHERE title = $old[0] AND seriesTitle = $old[6] AND seriesCreator = $old[7]
END_OF_UPDATE

    my $rows_affected = $dbh->do($update);
    
    $dbh->commit;

    if ($rows_affected > 0) { print "Update successful\n"; }

}

sub deleteEpisode() {
    my $et = $dbh->quote(&rl("Enter episode title: "));
    my $st = $dbh->quote(&rl("Enter series title: "));
    my $sc = $dbh->quote(&rl("Enter series creator: "));

    my $rows_affected = $dbh->do("DELETE FROM episodes WHERE title = $et AND seriesTitle = $st AND seriesCreator = $sc");
    $dbh->do("DELETE FROM featuring WHERE episodeTitle = $et AND seriesTitle = $st AND seriesCreator = $sc");
    $dbh->commit;
    
    if ($rows_affected > 0) { print "Delete for episode $et completed.\n"; }

}

sub selections() {
    return <<END_OF_CHOICES;
Select from the following queries:
(a) Find an actor's website
(b) Find an episode description
(c) Find all episodes with featuring a character
END_OF_CHOICES

}

sub choices() {
    return <<END_OF_CHOICES;

Select from the following options:
(a) Do useful queries
(b) Insert a new actor
(c) Insert a new series
(d) Update an episode description
(e) Delete an entire episode
(f) Quit
END_OF_CHOICES

}

sub quit() {
 $dbh->disconnect;    
 exit(0);
}