#!/usr/bin/perl
use DBI;

$| = 1;
my %drivers = map { $_, 1 } DBI->available_drivers;

print "What database type do you use (MySQL, Oracle, etc) [mysql]: ";
chomp($dbtype = <STDIN>);
$dbtype ||= "mysql";
die "Sorry, $dbtype not available.  Try @{[keys %drivers]}\n"
    unless $drivers{$dbtype};

print "What database name (i.e., catalog) do want to use [thermd]: ";
chomp($catalog = <STDIN>);
$catalog ||= "thermd";

print "What is the user name to use [root]: ";
chomp($username = <STDIN>);
$username ||= "root";

print "What is the password to use []: ";
chomp($password = <STDIN>);

print "What is the hostname the server is running on [localhost]: ";
chomp($host = <STDIN>);
$host ||= "localhost";

print "Where is the thermd script located [/var/www/KLEIN/thermd/thermd]: ";
chomp($thermd = <STDIN>);
$thermd ||= "/var/www/KLEIN/thermd/thermd";

print "Where is the thermd config file located [/etc/thermd.conf]: ";
chomp($config = <STDIN>);
$config ||= "/etc/thermd.conf";

my $dbh = DBI->connect("DBI:$dbtype:$catalog:$host", $username, $password)
    || die "Can't connect";

#
# Create the tables if necessary
#
%tables = map { s/`//g; s/.*\.//; ($_, 1) } $dbh->tables();
if (exists $tables{logfiles}) {
    warn "Table 'logfiles' found in database '$catalog'\n";
    }
else {
    warn "Creating table 'logfiles' in database '$catalog'\n";
    $sth = $dbh->prepare(qq{
	create table logfiles (
	    name	VARCHAR(80) NOT NULL,
	    log_id	INT AUTO_INCREMENT PRIMARY KEY
	    );
	});
    $sth->execute();
    die $sth->errstr()	if $sth->err();
    }

if (exists $tables{readings}) {
    warn "Table 'readings' found in database '$catalog'\n";
    }
else {
    warn "Creating table 'readings' in database '$catalog'\n";
    $sth = $dbh->prepare(qq{
	create table readings (
	    logtime	INT,
		INDEX (logtime),
	    log_id	INT,
		FOREIGN KEY (log_id) REFERENCES logfiles (log_id),
	    value	FLOAT
	    );
	});
    $sth->execute();
    die $sth->errstr()	if $sth->err();
    }

if (exists $tables{current}) {
    warn "Table 'current' found in database '$catalog'\n";
    }
else {
    warn "Creating table 'current' in database '$catalog'\n";
    $sth = $dbh->prepare(qq{
	create table current (
	    logtime	INT,
	    value	FLOAT,
	    units	VARCHAR(15),
	    log_id	INT,
		FOREIGN KEY(log_id) REFERENCES logfiles(log_id),
	    log_name	VARCHAR(80)
	    );
	});
    $sth->execute();
    die $sth->errstr()	if $sth->err();
    }

#
# Verify that the tables look correct from a column standpoint
#
my %type = (
    readings => {
	logtime	=> "INT",
	log_id	=> "INT",
	value	=> "FLOAT",
	},
    logfiles => {
	name	=> "VARCHAR",
	log_id	=> "INT",
	},
    current => {
	logtime	=> "INT",
	value	=> "FLOAT",
	units	=> "VARCHAR",
	log_id	=> "INT",
	log_name=> "VARCHAR",
	},
    );
%tables = map { s/`//g; s/.*\.//; ($_, 1) } $dbh->tables();
if (exists $tables{readings} && exists $tables{logfiles}) {
    for my $table (qw(readings logfiles)) {
	my $sth = $dbh->column_info(undef, $catalog, $table, "%");
	$info = $sth->fetchall_arrayref({});
	warn "Non-fatal: Column count is wrong in table '$table'\n"	unless @$info == keys %{ $type{$table} };
	for my $col (@$info) {
	    my $cname = $col->{COLUMN_NAME};
	    die "Column $cname is missing in table '$table'\n"
		unless exists $type{$table}{$cname};
	    die "Column $cname type in table '$table' is $col->{TYPE_NAME}, should be $type{$table}{$cname}\n"
		unless $col->{TYPE_NAME} eq $type{$table}{$cname};
	    }
	}
    }
else {
    die "Can't find tables 'readings' and 'logfiles' in database '$catalog'\n";
    }

@list = `$thermd -config $config -checkconfig -nowarn -list`;
die $?	if $?;

LOGFILE: for my $item (@list) {
    chomp($item);
    ($key, $val) = split /\s+/, $item;
    if ($key eq "LOGDIR") {
	$logdir = $val;
	}
    elsif ($key eq "LOGSQL") {
	die "Config file already says LogFormat SQL - I think I'm done\n";
	}
    elsif ($key eq "LOGFILE") {
	my $count = 0;
	my $snsr = $val;
	$snsr =~ s#(.*)/(.*)#\2\@\1#;
	$sth = $dbh->prepare(qq{SELECT * FROM logfiles WHERE name = "$snsr"});
	$sth->execute();
	$h = $sth->fetchrow_hashref();
	if ($h->{log_id}) {
	    print "= $snsr logfile '$val' already exists as log_id $h->{log_id}\n";
	    next LOGFILE;
	    }
	$sth = $dbh->prepare(qq{INSERT INTO logfiles values ("$snsr",null)});
	$sth->execute();
	$sth = $dbh->prepare(qq{SELECT * FROM logfiles WHERE name = "$snsr"});
	$sth->execute();
	$h = $sth->fetchrow_hashref();
	print "+ Created $snsr logfile '$val' as log_id $h->{log_id}\n";
	print "Converting";
	$val =~ s#^#$logdir/#	unless $val =~ m#^/#;
	open FD, $val	or die "Can't open logfile $val\n";
	$values = "";
	while (<FD>) {
	    chomp;
	    ($t, $v) = split;
	    if ($values) {
		$values .= ",";
		}
	    $values .= "($t,$h->{log_id},$v)";
	    if (++$count % 1000 == 0) {
		print ".";
		$sth = $dbh->prepare("INSERT INTO readings (logtime, log_id, value) VALUES $values");
		$sth->execute();
		$values = "";
		}
	    }
	if ($values) {
	    $sth = $dbh->prepare("INSERT INTO readings (logtime, log_id, value) VALUES $values");
	    $sth->execute();
	    }
	print "($count entries)\n";
	}
    }
$sth->finish();
$dbh->disconnect();

die "Can't update $config - $config.bak already exists!\n" if -e "$config.bak";
print "Updating (and backing up) your config file\n";
$tmpfile = "$config.$$";
open IN, $config	or die "Can't read $config - !$\n";
open OUT, "> $tmpfile"	or die "Can't write $tmpfile - !$\n";
$password = ":$password"	if $password;
print OUT "LogFormat SQL $dbtype $catalog $username$password\@$host\n";
while (<IN>) {
    next if /^\s*Log(Read|Write|Format)/i;
    print OUT;
    }
close IN;
close OUT;
rename $config, "$config.bak"	or die "Can't move $config -> $config.bak - !$\n";
rename $tmpfile, $config	or die "Can't move $tmpfile -> $config - !$\n";
print "Done!\n";
