package BetterDatabase;
use strict;
use DBI;
1;

sub new {
    my $class = shift;
    my($results) = @_;
    # XXX this stuff should SO be in a config file
    my $connect = 'DBI:mysql:databaseXXX:XXX.example.org:3306';
    my $username = 'usernameXXX';
    my $password = 'passwordXXX';
    my $handle = DBI->connect($connect, $username, $password,
                              {RaiseError => 1, PrintError => 1, AutoCommit => 1, Taint => 0})
        || die "Database connection not made: $DBI::errstr";

    return bless {'results' => $results, 'handle' => $handle}, $class;
}

sub setup {
    my $self = shift;
    $self->{handle}->do('CREATE TABLE keyValues ( tableName VARCHAR(12) NOT NULL PRIMARY KEY, nextKey INTEGER NOT NULL )');
    $self->{handle}->do('CREATE TABLE suites ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(128) NOT NULL )');
    $self->{handle}->do('CREATE TABLE products ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL, lastUse INTEGER NOT NULL DEFAULT 0 )');
    $self->{handle}->do('CREATE TABLE runs ( id INTEGER NOT NULL PRIMARY KEY, product INTEGER, build VARCHAR(128), tester VARCHAR(32), comment TEXT, start INTEGER NOT NULL )');
    $self->{handle}->do('CREATE TABLE tests ( id INTEGER NOT NULL PRIMARY KEY, suite INTEGER NOT NULL, uri TEXT NOT NULL, weight INTEGER NOT NULL DEFAULT 1)');
    $self->{handle}->do('CREATE TABLE results ( run INTEGER NOT NULL, test INTEGER NOT NULL, result VARCHAR(3) NOT NULL, PRIMARY KEY (run, test) )');
    # users table should have passwords unencrypted so that we can use digest auth one day
    $self->{handle}->do('CREATE TABLE users ( username VARCHAR(32) NOT NULL PRIMARY KEY, password VARCHAR(128) NOT NULL, level INTEGER NOT NULL DEFAULT 0 )');
    $self->addUser('admin', 'password', 1);
}

sub newRunID {
    my $self = shift;
    my $reuse = time() - 60 * 60 * 24; # allow ids to be re-used for a day
    my $id = $self->{handle}->selectrow_array('SELECT id FROM runs WHERE tester IS NULL AND start < ?', undef, $reuse);
    if (defined $id) {
        my $ok = $self->{handle}->do('UPDATE runs SET start=? WHERE id=? AND tester IS NULL AND start < ?',
                                     undef, time(), $id, $reuse);
        return $id if $ok;
    }
    # otherwise, create a new one for us
    $id = $self->_incrementID('runs');
    $self->{handle}->do('INSERT INTO runs SET id=?, start=?', undef, $id, time());
    return $id;
}

sub getRun {
    my $self = shift;
    my($id) = @_;
    return $self->{handle}->selectrow_array('SELECT runs.product, products.name, runs.build, runs.tester, runs.comment FROM runs LEFT JOIN products ON runs.product = products.id WHERE runs.id=?', undef, $id);
}

sub setRun {
    my $self = shift;
    my($id, $product, $build, $tester, $comment) = @_;
    foreach ($build, $comment) {
        $_ = '' unless defined $_;
    }
    my $ok = $self->{handle}->do('UPDATE runs SET product=?, build=?, tester=?, comment=? WHERE id=?', undef, $product, $build, $tester, $comment, $id);
    $self->{handle}->do('UPDATE products SET lastUse=? WHERE id=?', undef, time(), $product);
    return $ok;
}

sub getSuites {
    my $self = shift;
    my($run) = @_;
    my $sth = $self->{handle}->prepare('SELECT id, name FROM suites ORDER BY name');
    $sth->execute();
    my($id, $name); # Bind Perl variables to columns:
    $sth->bind_columns(\$id, \$name);
    my @suites;
    while ($sth->fetch) {
        push(@suites, {'id' => $id, 'name' => $name});
    }
    return \@suites;
}

sub getProducts {
    my $self = shift;
    my(@products, $sth, $id, $name);
    $sth = $self->{handle}->prepare('SELECT id, name FROM products ORDER BY lastUse DESC LIMIT 7');
    $sth->execute();
    $sth->bind_columns(\$id, \$name);
    while ($sth->fetch) {
        push(@products, {'id' => $id, 'name' => $name});
    }
    if (@products >= 7) {
        $sth = $self->{handle}->prepare('SELECT id, name FROM products ORDER BY name');
        $sth->execute();
        $sth->bind_columns(\$id, \$name);
        while ($sth->fetch) {
            push(@products, {'id' => $id, 'name' => $name});
        }
    }
    return \@products;
}

sub nextTest {
    my $self = shift;
    my($run, $suite) = @_;
    my($id, $uri, $weight, $result) = $self->{handle}->selectrow_array('SELECT tests.id, tests.uri, tests.weight, results.result FROM tests LEFT JOIN results ON results.run=? AND tests.id = results.test WHERE tests.suite=? AND (results.result IS NULL OR results.result = \'\?\') ORDER BY tests.uri', undef, $run, $suite);
    my $remaining = $self->{handle}->selectrow_array('SELECT COUNT(id) FROM tests LEFT JOIN results ON results.run=? AND results.test = tests.id WHERE results.test IS NULL AND tests.suite=?', undef, $run, $suite);
    my $total = $self->{handle}->selectrow_array('SELECT COUNT(id) FROM tests WHERE tests.suite=?', undef, $suite);
    if (defined $id) {
        return {
            'id' => $id,
            'uri' => $uri,
            'weight' => $weight,
            'result' => $result,
            'remaining' => $remaining,
            'total' => $total,
        };
    }
    return undef;
}

sub getResult {
    my $self = shift;
    my($run, $test) = @_;
    return $self->{handle}->selectrow_array('SELECT result FROM results WHERE run=? AND test=?', undef, $run, $test);
}

sub setResult {
    my $self = shift;
    my($run, $test, $result) = @_;
    if (defined $result && $result ne '') {
        $self->{handle}->do('REPLACE INTO results SET run=?, test=?, result=?', undef, $run, $test, $result);
    } else {
        $self->{handle}->do('DELETE FROM results WHERE run=? AND test=?', undef, $run, $test);
    }
}

sub checkPassword {
    my $self = shift;
    my($username, $password, $minLevel) = @_;
    $minLevel = 0 unless defined $minLevel;
    return scalar @{[ $self->{handle}->selectrow_array('SELECT username FROM users WHERE username=? AND password=? AND level>=?', undef, $username, $password, $minLevel) ]};
}

sub getRuns {
    my $self = shift;
    my $sth = $self->{handle}->prepare('SELECT runs.id, runs.product, products.name, products.lastUse, runs.build, runs.tester, runs.comment, runs.start FROM runs LEFT JOIN products ON runs.product = products.id WHERE runs.tester IS NOT NULL', undef);
    $sth->execute();
    my(@runs, $runID, $productID, $name, $lastUse, $build, $tester, $comment, $start);
    $sth->bind_columns(\$runID, \$productID, \$name, \$lastUse, \$build, \$tester, \$comment, \$start);
    while ($sth->fetch) {
        my $run = {
            'runID' => $runID,
            'productID' => $productID,
            'productName' => $name,
            'lastUse' => $lastUse,
            'build' => $build,
            'tester' => $tester,
            'comment' => $comment,
            'start' => $start,
        };
        my($code, $count);
        my $results = $self->{handle}->prepare('SELECT result, COUNT(result) FROM results WHERE run=? GROUP BY result', undef);
        $results->execute($runID);
        $results->bind_columns(\$code, \$count);
        while ($results->fetch) {
            $run->{"result$code"} = $count;
        }
        $run->{remaining} = $self->{handle}->selectrow_array('SELECT COUNT(id) FROM tests LEFT JOIN results ON results.run=? AND results.test = tests.id WHERE results.test IS NULL', undef, $runID);
        push(@runs, $run);
    }
    return \@runs;
}

sub getResults {
    my $self = shift;
    my($runID) = @_;
    my $sth = $self->{handle}->prepare('SELECT tests.id, tests.uri, tests.weight, results.result, tests.suite, suites.name FROM tests LEFT JOIN results ON results.test = tests.id AND results.run=? LEFT JOIN suites ON suites.id = tests.suite', undef);
    $sth->execute($runID);
    my(@results, $id, $uri, $weight, $result, $suiteID, $suite);
    $sth->bind_columns(\$id, \$uri, \$weight, \$result, \$suiteID, \$suite);
    while ($sth->fetch) {
        push(@results, {
            'id' => $id,
            'uri' => $uri,
            'weight' => $weight,
            'result' => $result,
            'suiteID' => $suiteID,
            'suite' => $suite,
        });
    }
    return \@results;
}

sub addUser {
    my $self = shift;
    my($user, $password, $level) = @_;
    $level = 0 unless defined $level;
    $self->{handle}->do('REPLACE users SET username=?, password=?, level=?', undef, $user, $password, $level);
}

sub addProduct {
    my $self = shift;
    my($product) = @_;
    my $id = $self->_incrementID('products');
    $self->{handle}->do('INSERT INTO products SET id=?, name=?', undef, $id, $product);
}

sub addSuite {
    my $self = shift;
    my($suite) = @_;
    my $id = $self->_incrementID('suites');
    $self->{handle}->do('INSERT INTO suites SET id=?, name=?', undef, $id, $suite);
}

sub addTest {
    my $self = shift;
    my($suite, $uri, $weight) = @_;
    $weight = 1 unless defined $weight;
    my $id = $self->_incrementID('tests');
    $self->{handle}->do('INSERT INTO tests SET id=?, suite=?, uri=?, weight=?', undef, $id, $suite, $uri, $weight);
}

sub DESTROY {
    my $self = shift;
    if ($self->{handle}) {
        $self->{handle}->disconnect();
        $self->{handle} = undef;
    }
}

sub _incrementID {
    my $self = shift;
    my($table) = @_;
    $self->{handle}->do('LOCK TABLES keyValues WRITE');
    my $id = $self->{handle}->selectrow_array('SELECT nextKey FROM keyValues WHERE tableName=?', undef, $table);
    $id = 0 unless defined $id;
    $self->{handle}->do('REPLACE keyValues SET tableName=?, nextKey=?', undef, $table, $id + 1);
    $self->{handle}->do('UNLOCK TABLES');
    return $id;
}