#!/usr/bin/perl
use DBI;
use Getopt::Long;
use Pod::Usage;
use File::Temp qw(tempfile);
use Term::ReadKey;
use strict;

my $cfgdir		= sprintf('%s/.pjam', $ENV{HOME});
my $cfgfile		= sprintf('%s/config', $cfgdir);
my $pjam_version	= '1.0.2';
my $pjam_desc		= "\npjam-dbtool version $pjam_version for PerlJammer\n";
my %opts;


if (scalar @ARGV == 0)
{
    pod2usage(-message => "$pjam_desc\nInsufficient arguments specified\n",
              -exitstatus => -1,
              -verbose => 0);
}
elsif (-d $cfgdir && -f $cfgfile)
{
    %opts = get_prefs($cfgfile);

    if (GetOptions(\%opts,
                   'host=s',
                   'port=i',
                   'dbname=s',
                   'user=s',
                   'admin=s',
                   'sqlcfg=s',
                   'group=s',
                   'update',
                   'help|usage|?',
                   'man',
                   'version'))
    {
        if ($opts{version})
        {
            pod2usage(-message => $pjam_desc,
                      -exitstatus => 0,
                      -verbose => 0);
        }
        elsif ($opts{help})
        {
            pod2usage(-message => $pjam_desc,
                      -exitstatus => 0,
                      -verbose => 1);
        }
        elsif ($opts{man})
        {
            pod2usage(-exitstatus => 0,
                      -verbose => 2);
        }
        elsif (($opts{host} && $opts{admin} && $opts{sqlcfg})
            && ($opts{user} || $opts{update})
            && ($opts{dbname} || $opts{sqldb})
            && ($opts{group} || $opts{sqlgroup}))
        {
            dbadmin();
        }
        else
        {
            
            pod2usage(-message => "$pjam_desc\nInsufficient arguments specified\n",
                      -exitstatus => -1,
                      -verbose => 0);
        }
    }
    else
    {
        pod2usage(-message => $pjam_desc,
                  -exitstatus => -1,
                  -verbose => 0);
    }
}
else
{
    print "The config file ~/.pjam/config was not found.  Please run PerlJammer\n",
          "once to create it.  Preferably, you should edit it to set the sqlcfg,\n",
          "sqldb, and sqlgroup preferences.  You will then not need to use -sqlcfg\n",
          "-dbname, or -group here.\n";
}

exit (0);


sub get_prefs
{
    my %o;

    open(PREFS, $_[0]);
    while (<PREFS>)
    {
        next if (/^#/);
        if (/^(\S+)\s*:\s+(yes|true)$/i)
        {
            $o{$1} = 1;
        }
        elsif (/^(\S+)\s*:\s+(no|false)$/i)
        {
            $o{$1} = 0;
        }
        elsif (/^(\S+)\s*:\s+(\S.*\S)\s*$/)
        {
            $o{$1} = $2;
        }
        elsif (/^(\S+)\s*:\s+(\d+)\s*$/)
        {
            $o{$1} = $2;
        }
    }

    return(%o);
}


sub dbadmin
{
    my $home = $ENV{HOME};

    if ($opts{sqlcfg})
    {
        print "Found cfgfile setting\n";
        $opts{sqlcfg} =~ s/~/$home/;
        if (-f $opts{sqlcfg})
        {
            my ($adminpass, $userpass, $userpass2);

            printf("Enter the ADMIN PASSWORD for the MySQL server on host %s: ",
                   $opts{host});
            ReadMode('noecho');
            chomp($adminpass = ReadLine(0));
            ReadMode('normal');
            print "\n";

            unless ($opts{update})
            {
                printf("Enter the USER PASSWORD for the music dabatase on host %s: ",
                       $opts{host});
                ReadMode('noecho');
                chomp($userpass = ReadLine(0));
                ReadMode('normal');
                print "\n";

                printf("Confirm the USER PASSWORD for the music dabatase on host %s: ",
                       $opts{host});
                ReadMode('noecho');
                chomp($userpass2 = ReadLine(0));
                ReadMode('normal');
                print "\n";
            }

            if ($userpass eq $userpass2)
            {
                if ($opts{update})
                {
                    updatedb($adminpass, $userpass);
                }
                else
                {
                    makedb($adminpass, $userpass);
                }
            }
            else
            {
                print "User passwords do not match.  Quitting; please try again.\n"
            }
        }
    }
}


sub makedb
{
    my ($adminpass, $userpass) = @_;
    my ($dbh, $sth, $query);

    print "Connecting to MySQL server ...\n";

    $dbh = open_db('mysql', $adminpass, 1);

    # Create the database

    print "Creating your database ...\n";

    $query = sprintf('CREATE DATABASE %s', $opts{dbname} || $opts{sqldb});
    $dbh->do($query);

    # Grant rights on the database

    print "Granting rights ...\n";

    $query = sprintf("GRANT INSERT, SELECT, UPDATE ON %s.* TO '%s'\@'%%' IDENTIFIED BY '%s'",
                     $opts{dbname} || $opts{sqldb},
                     $opts{user},
                     $opts{userpass});
    $dbh->do($query);

    # Switch to the new DB

    $query = sprintf('USE %s', $opts{dbname} || $opts{sqldb});
    $dbh->do($query);
    
    # Create the tables

    print "Creating db_info table ...\n";

    $dbh->do("CREATE TABLE `db_info` (`db_version` int(10) unsigned DEFAULT NULL)");
    $dbh->do("INSERT INTO db_info (db_version) VALUES ('3')");

    print "Creating artist table ...\n";

    $dbh->do("CREATE TABLE `artist` (`id` int(11) NOT NULL AUTO_INCREMENT,
                                     `artist` varchar(80) DEFAULT NULL,
                                     `num_plays` int(11) DEFAULT NULL,
                                     `last_play` datetime DEFAULT NULL,
                                     `date_added` date DEFAULT NULL,
                                     PRIMARY KEY (`id`))");

    print "Creating disc table ...\n";

    $dbh->do("CREATE TABLE `disc` (`id` int(11) NOT NULL AUTO_INCREMENT,
                                   `disc` varchar(100) DEFAULT NULL,
                                   `artistid` int(11) NOT NULL,
                                   `num_plays` int(11) DEFAULT NULL,
                                   `last_play` datetime DEFAULT NULL,
                                   `genre` varchar(20) DEFAULT NULL,
                                   `year` int(11) DEFAULT NULL,
                                   `date_added` date DEFAULT NULL,
                                   `gain_adj` tinyint(2) NOT NULL DEFAULT '0',
                                   PRIMARY KEY (`id`),
                                   KEY `artistid` (`artistid`))");

    print "Creating song table ...\n";

    $dbh->do("CREATE TABLE `song` (`id` int(11) NOT NULL AUTO_INCREMENT,
                                   `filename` varchar(255) DEFAULT NULL,
                                   `title` varchar(80) DEFAULT NULL,
                                   `artistid` int(11) NOT NULL,
                                   `discid` int(11) NOT NULL,
                                   `genre` varchar(20) DEFAULT NULL,
                                   `track_num` int(11) DEFAULT NULL,
                                   `play_time` varchar(5) DEFAULT NULL,
                                   `num_plays` int(11) DEFAULT NULL,
                                   `last_play` datetime DEFAULT NULL,
                                   `year` int(11) DEFAULT NULL,
                                   `date_added` date DEFAULT NULL,
                                   `gain_adj` tinyint(2) NOT NULL DEFAULT '0',
                                   `disable` bit(64) NOT NULL DEFAULT b'0',
                                   PRIMARY KEY (`id`),
                                   KEY `artistid` (`artistid`),
                                   KEY `discid` (`discid`))");

    print "Done.  Disconnecting from MySQL server.\n";

    $dbh->disconnect();

    # Rewrite the mysql user config file if no correct [group]
    # tag is found.  If it DOES already exist, assume it's correct.

    if (-f $opts{sqlcfg})
    {
        open (CFG, $opts{sqlcfg});
        my @cfgdata = <CFG>;
        close (CFG);

        my $group = $opts{group} || $opts{sqlgroup};
        my $sqlcfg = $opts{sqlcfg};

        if (grep(/^\[$group\]$/, @cfgdata))
        {
            print "$sqlcfg already contains a [$group] tag.  Please check that it is correct.\n";
        }
        else
        {
            push (@cfgdata, sprintf("\n[%s]\n", $group));
            push (@cfgdata, sprintf("user=%s\n", $opts{user}));
            push (@cfgdata, sprintf("host=%s\n", $opts{host}));
            push (@cfgdata, sprintf("port=%d\n", $opts{port} || 3306));
            push (@cfgdata, sprintf("password=%s\n\n", $userpass));

            open (CFG, ">$sqlcfg");
            print CFG @cfgdata;
            close (CFG);

            print "$sqlcfg rewritten.  Please check that it is correct.\n";
        }
    }
}


sub updatedb
{
    my ($adminpass, $userpass) = @_;
    my ($dbh, $sth, $query, $version);

    print "Connecting to MySQL server ...\n";

    $dbh = open_db($opts{sqldb}, $adminpass, 0);

    print "Checking database version ... ";

    $query = 'SELECT db_version FROM db_info';
    $sth = $dbh->prepare($query) || die "Error:" . $dbh->errstr . "\n";
    $sth->execute || die "Error:" . $dbh->errstr . "\n";

    if (my $ref = $sth->fetchrow_arrayref)
    {
        $version = $$ref[0];
    }
    $sth->finish();

    if ($version == 3)
    {
        print "Version 3.\nYour database is already up to date.\n";
    }
    else
    {
        print "Version 2.\nPreparing to update your database ...\n";

        print "Adding new columns ...\n";
        $dbh->do("ALTER TABLE song ADD COLUMN disable bit(64) NOT NULL DEFAULT b'0'");

        print "Modifying columns ...\n";
        $dbh->do("ALTER TABLE disc CHANGE COLUMN title disc varchar(100) DEFAULT NULL");
        $dbh->do("ALTER TABLE disc CHANGE COLUMN gain_adjustment gain_adj int(11) DEFAULT NULL");
        $dbh->do("ALTER TABLE disc MODIFY COLUMN gain_adj tinyint(2) NOT NULL DEFAULT '0'");
        $dbh->do("ALTER TABLE song CHANGE COLUMN gain_adjustment gain_adj int(11) DEFAULT NULL");
        $dbh->do("ALTER TABLE song MODIFY COLUMN gain_adj tinyint(2) NOT NULL DEFAULT '0'");

        print "Dropping unused columns ...\n";
        $dbh->do("ALTER TABLE song DROP COLUMN start_frame");
        $dbh->do("ALTER TABLE song DROP COLUMN num_frames");
        $dbh->do("ALTER TABLE song DROP COLUMN bpm");

        print "Dropping redundant index artistid_2 from disc table ...\n";
        $dbh->do("ALTER TABLE disc DROP INDEX artistid_2");

        print "Updating database version number ...\n";
        $dbh->do("UPDATE db_info SET db_version = '3'");

        print "Fixing up 0-based track numbers ...\n";
        $dbh->do("CREATE TEMPORARY TABLE trackfix (discid int(11) NOT NULL DEFAULT 0)");
        $dbh->do("INSERT INTO trackfix (discid) SELECT discid FROM song WHERE track_num = 0");
        $dbh->do("UPDATE SONG SET track_num = track_num + 1 WHERE discid IN (SELECT discid FROM trackfix)");
        $dbh->do("DROP TABLE trackfix");

        print "Committing all changes ...\n";
        $dbh->do("COMMIT");
    }

    print "Disconnecting from MySQL server.\n";

    $dbh->disconnect();
}


sub open_db
{
    my ($db, $password, $commit) = @_;
    my ($dsn, $dbh);

    $dsn = sprintf("DBI:mysql:database=%s;host=%s;port=%s;"
                 . "mysql_mysql_client_found_rows=TRUE;"
                 . "mysql_mysql_ssl=TRUE",
                   $db,
                   $opts{host},
                   $opts{port} || 3306);

    $dbh = DBI->connect($dsn, $opts{admin}, $password, {RaiseError => 1, AutoCommit => $commit});
    $dbh->do("SET NAMES latin1");

    return ($dbh);
}


__END__

=head1 NAME

B<pjam-dbtool> - Creates an MP3 database for B<PerlJammer>

=head1 VERSION

Version 1.0.2

=head1 SYNOPSIS

pjam-dbtool [options]

  Options:
    -host       [required]
    -user       [required]
    -admin      [required]
    -port       [required if not port 3306]
    -dbname     [required if sqldb not set in ~/.pjam/config]
    -group      [required if sqlgroup not set in ~/.pjam/config]
    -sqlcfg     [required if sqlcfg not set in ~/.pjam/config]
    -help, -usage, -?
    -man
    -version

=head1 OPTIONS

=over 4

=item B<-host>

Specify the SQL host on which to create the database

=item B<-user>

Specify the username that will be granted access to the new database

=item B<-admin>

Specify the name of the privileged DB user (needed to create or modify the DB)

=item B<-port>

Specify the SQL server port (default: 3306)

=item B<-dbname>

The name of the SQL database to be created

=item B<-group>

The group name to use in the MySQL config file (default: pjam)

=item B<-sqlcfg>

The location of the user MySQL config file (default: ~/.my.cnf)

=item B<-update>

Update an existing database from version 2 (B<DigitalDJ>) to version 3 (B<PerlJammer>)

=item B<-help, -usage, -?>

List command-line options and usage, then exit

=item B<-man>

Display full documentation and exit

=item B<-version>

Display version string and exit

=back

=head1 DESCRIPTION

B<pjam-dbtool> creates a SQL music database for B<PerlJammer> if you do not already have
an existing B<DigitalDJ> database.  The database it creates is compatible with B<Grip>, but
is not a full replica of the B<DigitalDJ> database.  (See the 00_README file in the install
package for a complete discussion of the database schema changes.)

If you have an existing B<DigitalDJ> database, B<pjam-dbtool> also has the capability to
update the database schema from B<DigitalDJ>'s version 2 to B<PerlJammer>'s version 3 for
full compatibility with B<PerlJammer>.  If updating an existing database, B<pjam-dbtool>
will also update all track numbers in the database to begin from 1 instead of from 0.

B<pjam-dbtool> requires six pieces of information to create the database:  the desired
name of the DB, the SQL host to create it on, an admin user name and password on that
SQL server with sufficient rights to create the database, and the user name and password
by which the music DB will be accessed.

B<NOTE THAT CREATING THE DATABASE REQUIRES ADMINISTRATIVE RIGHTS ON THE SQL SERVER.>
Authentication is performed securely, and the SQL DB admin user's password will NOT be
visible on the command line or on the network.


=head1 REPORTING BUGS

Please send all bug reports to the author.

=head1 LICENSE

B<PerlJammer> and its supporting tools are free software.  You may redistribute
and/or modify them under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation; either version 2.1 of the License,
or (at your option) any later version.

=head1 OBTAINING PerlJammer

B<PerlJammer> can be downloaded from http://co.ordinate.org/perljammer/.

=head1 AUTHOR

B<PerlJammer> and its supporting tools are written and maintained by Phil Stracchino
(phil@co.ordinate.org).

=cut

