#!/usr/bin/env perl

use warnings;
use strict;

use DBI;
use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock';
use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );

my $dbname = 'ippToPsps';
my $dbserver = 'ippdb01';
my $dbuser = 'ipp';
my $dbpass = 'ipp';
GetOptions( 'dbname|d=s' => \$dbname);

# connect to database
my $db = DBI->connect("DBI:mysql:database=${dbname};host=${dbserver};" .
        "mysql_socket=" . DB_SOCKET(),
        ${dbuser},${dbpass},
        { RaiseError => 1, AutoCommit => 1}
        ) or die "Unable to connect to database $DBI::errstr\n";

print "\n*******************************************************************************\n*\n";

print "* Connected to '$dbname' on 'dbserver'\n";

if (!doesTableExist("revision")) {
    createRevision_1();
}


$db->disconnect();
print "* Disconnected from '$dbname' on 'dbserver'\n";
print "*\n*******************************************************************************\n\n";

#######################################################################################
# 
# Create revision 1 of the database 
#
#######################################################################################
sub createRevision_1 {

    print "* Creating revision 1 of '$dbname'\n";

    my $query = $db->prepare(<<SQL);
    CREATE TABLE revision (

            revision INT, 
            created TIMESTAMP DEFAULT NOW(),
            primary key (revision)
            );
SQL
        $query->execute;

    $query = $db->prepare(<<SQL);
    INSERT INTO revision (revision) VALUES (1);
SQL
        $query->execute;

    $query = $db->prepare(<<SQL);

    CREATE TABLE batches (

            batch_id BIGINT NOT NULL, 
            exp_id BIGINT NOT NULL, 
            survey_id VARCHAR(30) DEFAULT "NONE",
            processed TINYINT DEFAULT 0,
            on_datastore TINYINT DEFAULT 0,
            loaded_to_ODM TINYINT DEFAULT 0,
            merge_worthy TINYINT DEFAULT 0,
            deleted TINYINT DEFAULT 0,
            created TIMESTAMP DEFAULT NOW(),
            primary key (batch_id, exp_id)
            );

SQL
    $query->execute;

}

#######################################################################################
# 
# Gets current revision of ippToPsps database
#
#######################################################################################
sub doesTableExist {
    my ($table) = @_;

    my $query = $db->prepare(<<SQL);

    SELECT COUNT(*) 
        FROM information_schema.tables 
        WHERE table_schema = '$dbname'  
        AND table_name = '$table';
SQL

        $query->execute;

    my $count = $query->fetchrow_array();

    printf( "* Table '$table' %s\n", $count ? "exists" : "does not exist");

    return $count;
}

