#!/usr/bin/perl -w
#
# mybooks.pl - Script for managing one's collection of books.
#

use strict;
use DBI;
use DBIx::DataSource qw ( create_database );
use Getopt::Std;
use vars qw ( $db_name $db_user $db_pass );


### BEGINNING of user defined variables 

$db_name = 'mybooks';  # Name of mysql database. (instead of -d option)
$db_user = 'root';     # Username to access database. (instead of -u option)
$db_pass = '';         # Password to access database. (instead of -p option)

### END of user defined variables


my %opts = ();
getopts ('Id:hp:u:', \%opts);

$db_name = $opts{'d'} || $db_name || die "No database name specified.";
$db_user = $opts{'u'} || $db_user;
$db_pass = $opts{'p'} || $db_pass;

$opts{'h'} && usage () && exit ();
$opts{'I'} && init_database () && exit ();

my %cmds = ( add => [ 'Add a book to the database.', \&add_book ],
             del => [ 'Remove a book from the database.', \&del_book ],
             mod => [ 'Change the status of a book.', \&mod_book ],
             view => [ 'View books currently in the db.', \&view_books ] );

print "Type 'quit' to exit or 'help' for a list of other commands.\n";

for (;;)
{
    my $q = get_input ("? ");
    
    if ($q eq 'quit') 
    { 
        print "Bye.\n";
        exit ();
    }
    
    if ($q eq 'help')
    {
        for (keys %cmds) { print "$_ :  $cmds{$_}[0]\n"; }
        next;
    }

    grep ($q eq $_, keys %cmds) && $cmds{$_}[1]->() && next;

    print "Unknown command '$q'.\n";
}


### SUB definitions lie after this point

sub init_database
{
    create_database ("DBI:mysql:$db_name", $db_user, $db_pass)
        or die $DBIx::DataSource::errstr;

    do_sql ("CREATE TABLE $db_name ( 
                   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
                   title VARCHAR(100) NOT NULL, 
                   author VARCHAR(60), 
                   isbn VARCHAR(15), 
                   status CHAR(1) NOT NULL DEFAULT 'W', 
                   PRIMARY KEY(id))")
        or die "Could not create table in database '$db_name'.\n";
}

sub get_input
{
    print shift ();
    chomp ($_ = <STDIN>);
    $_;
}

sub add_book
{
    my $title  = get_input ('Enter title: ');
    my $author = get_input ('Enter author: ');
    my $isbn   = get_input ('Enter isbn: ');
    my $status = get_input ('Do you own this book? (y/n) ') eq 'y' ? 'H' : 'W';
    
    do_sql ("INSERT INTO $db_name 
                 VALUES (NULL, '$title', '$author', '$isbn', '$status')");
}

sub del_book
{
    my $id = get_input ('Enter book id: ');
    
    do_sql ("DELETE FROM $db_name WHERE id=$id");
}

sub mod_book
{
    my $id = get_input ('Enter book id: ');
    my $status = get_input ('Do you own this book? (y/n) ') eq 'y' ? 'H' : 'W';
    
    do_sql ("UPDATE $db_name SET status='$status' WHERE id=$id");       
}

sub view_books
{
    my $type;

    {
        $type = get_input ('Show all/wanted/owned? (A/W/H) ');
        grep ($type eq $_, qw ( A H W )) || redo;
    }
    
    my $dbh = DBI->connect ("DBI:mysql:$db_name", $db_user, $db_pass)
        or die $DBI::errstr;
    my $sth;

    if ($type eq 'A')
    {
        $sth = $dbh->prepare ("SELECT * FROM $db_name");
    }
    else
    {
        $sth = $dbh->prepare ("SELECT * FROM $db_name WHERE status='$type'");
    }
    
    my $rv = $sth->execute ();
    my $books = $sth->fetchall_arrayref ();
    @$books = sort { $$a[2] cmp $$b[2] } @$books;

    for my $book (@$books)
    {
        print "$$book[0]: $$book[2]. $$book[1]. $$book[3]. $$book[4]\n";
    }

    $dbh->disconnect ();
}

sub do_sql
{
    my $dbh = DBI->connect ("DBI:mysql:$db_name", $db_user, $db_pass)
        or return 0;
    $dbh->do (shift ());
    $dbh->disconnect ();
    1;        
}

sub usage
{
    print <<EOF;
Usage: perl mybooks.pl [options]

Options:
    -h     Prints this help message then exits.
    -I     Initializes MySQL database then exits.
    -d X   Name of the MySQL database to use.
    -u X   Username with which to connect to MySQL.
    -p X   Password with which to connect to MySQL.
EOF
}