#!/usr/bin/perl
use DBI;
use Getopt::Long;
use Pod::Usage;
use File::Temp qw(tempfile);
use Term::ReadKey;
use strict;
use utf8;
use open ':std', ':encoding(UTF-8)';


my $cfgdir		= sprintf('%s/.pjam', $ENV{HOME});
my $cfgfile		= sprintf('%s/config', $cfgdir);
my $pjam_version	= '1.2.0';
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*$/)        # " reset
        {
            $o{$1} = $2;
        }
        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 ('4')");

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

    $dbh->do("CREATE TABLE artist (id int(11) NOT NULL AUTO_INCREMENT,
                                   artist varchar(128) DEFAULT NULL,
                                   num_plays int(11) DEFAULT NULL,
                                   last_play datetime DEFAULT NULL,
                                   date_added date DEFAULT NULL,
                                   PRIMARY KEY (id))
                                   ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PACK_KEYS=1 ROW_FORMAT=DYNAMIC");

    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))
                                 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PACK_KEYS=1 ROW_FORMAT=DYNAMIC");

    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',
                                 next_id int(11) DEFAULT NULL,
                                 prev_id int(11) DEFAULT NULL,
                                 PRIMARY KEY (id),
                                 KEY artistid (artistid),
                                 KEY discid (discid))
                                 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PACK_KEYS=1 ROW_FORMAT=DYNAMIC");

    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 == 5)
    {
        print "Version 5.\nYour database is already up to date.\n";
    }
    elsif ($version == 4)
    {
        print "Version 4.\nPreparing to update your database ...\n";

        print "Updating character set and collation ...\n";
        $dbh->do("ALTER TABLE artist CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE artist DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE disc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE disc DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE song CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE song DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

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

        print "Committing all changes ...\n";
        $dbh->do("COMMIT");
    }
    elsif ($version == 3)
    {
        print "Version 3.\nPreparing to update your database ...\n";

        print "Adding columns ...\n";
        $dbh->do("ALTER TABLE song ADD COLUMN next_id int(11) DEFAULT NULL");
        $dbh->do("ALTER TABLE song ADD COLUMN prev_id int(11) DEFAULT NULL");

        print "Updating character set and collation ...\n";
        $dbh->do("ALTER TABLE artist CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE artist DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE disc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE disc DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE song CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE song DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

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

        print "Committing all changes ...\n";
        $dbh->do("COMMIT");
    }
    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'");
        $dbh->do("ALTER TABLE song ADD COLUMN next_id int(11) DEFAULT NULL");
        $dbh->do("ALTER TABLE song ADD COLUMN prev_id int(11) DEFAULT NULL");

        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 character set and collation ...\n";
        $dbh->do("ALTER TABLE artist CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE artist DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE disc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE disc DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE song CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $dbh->do("ALTER TABLE song DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

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

        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, undef, undef,
                        {RaiseError => 1, AutoCommit => 1, mysql_enable_utf8 => 1});

    my $sth = $dbh->prepare('SET NAMES utf8mb4') || die "Error:" . $dbh->errstr . "\n";
    $sth->execute || die "Error:" . $dbh->errstr . "\n";

    return ($dbh);
}


__END__

=head1 NAME

B<pjam-dbtool> - Creates or updates the MP3 database for B<PerlJammer>

=head1 VERSION

Version 1.2.0

=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]
    -update
    -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>) or version 3 (B<PerlJammer>) to version 4

=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<pjam-dbtool>, part of the B<PerlJammer> package, is copyright (C) 2003 Phil Stracchino.

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.

=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

