package TheDatabase;
use strict;
use DBI;
1;

sub new {
    my $class = shift;
    my($connect, $username, $password) = @_;
    my $handle = DBI->connect($connect, $username, $password,
                              {RaiseError => 1, PrintError => 1, AutoCommit => 1, Taint => 0})
        || die "Database connection not made: $DBI::errstr";

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

sub setup {
    my $self = shift;

    # Registered users: referrer-id, e-mail, password, display name, referrer-referrer-id, new e-mail, new password
    # Destinations: destination-id, uri, download-p
    # Referrals: tracking-id, registered-id, original destination-id, first download destination-id

    $self->{handle}->do('CREATE TABLE keyValues (
                           tableName VARCHAR(12) NOT NULL PRIMARY KEY,
                           nextKey INTEGER NOT NULL
                         )');
    $self->{handle}->do('CREATE TABLE users (
                           id INTEGER NOT NULL PRIMARY KEY,
                           email VARCHAR(128) NOT NULL,
                           password VARCHAR(32) NOT NULL,
                           name VARCHAR(128) NOT NULL,
                           newEmail VARCHAR(128) DEFAULT NULL,
                           newPassword VARCHAR(32) DEFAULT NULL
                         )');
    $self->{handle}->do('CREATE TABLE destinations (
                           id INTEGER NOT NULL PRIMARY KEY,
                           uri VARCHAR(128) NOT NULL,
                           type INTEGER NOT NULL DEFAULT 0
                         )'); # type: 0x00 = page, 0x01 download
    $self->{handle}->do('CREATE TABLE referrals (
                           id INTEGER NOT NULL PRIMARY KEY,
                           host VARCHAR(128) NOT NULL,
                           time INTEGER NOT NULL,
                           referrer INTEGER NOT NULL,
                           destination INTEGER,
                           download INTEGER,
                           user INTEGER
                         )'); # the user field is really a boolean (if NULL, no, if NOT NULL, yes; no means that user either didn't get a user or had one before being referred)
}

sub newUser {
    my $self = shift;
    my($email, $password, $name) = @_;
    my $id = $self->_incrementID('users');
    $self->{handle}->do('INSERT INTO users SET id=?, email=?, password=?, name=?', undef, $id, $email, $password, $name);
    return $id;
}

sub getUser {
    my $self = shift;
    my($email) = @_;
    my($password, $id, $name) = $self->{handle}->selectrow_array('SELECT password, id, name FROM users WHERE email=?', undef, $email);
    return ($password, $id, $name);
}

sub getAuthenticatedUser {
    my $self = shift;
    my($email, $passwordGiven) = @_;
    my($passwordReal, $id, $name) = $self->{handle}->selectrow_array('SELECT password, id, name FROM users WHERE email=?', undef, $email);
    if ($passwordGiven eq $passwordReal) {
        return ($id, $name);
    } else {
        return undef;
    }
}

sub getDestination {
    my $self = shift;
    my($id) = @_;
    return $self->{handle}->selectrow_array('SELECT uri FROM destinations WHERE id=?', undef, $id);
}

sub getDestinations {
    my $self = shift;
    my $results = $self->{handle}->selectall_arrayref('SELECT id, uri, type FROM destinations ORDER BY type, uri, id');
    return [] if not defined $results;
    foreach (@$results) {
        $_ = {
            'id' => $_->[0],
            'uri' => $_->[1],
            'type' => $_->[2],
        };
    }
    return $results;
}

sub addDestination {
    my $self = shift;
    my($uri, $type, $id) = @_;
    if (not defined $id) {
        my $id = $self->_incrementID('destinations');
    } else {
        $self->{handle}->do('UPDATE keyValues SET nextKey=? WHERE tableName="destinations" AND nextKey<?', undef, $id + 1, $id + 1);
    }
    $self->{handle}->do('INSERT INTO destinations SET id=?, uri=?, type=?', undef, $id, $uri, $type);
    return $id;
}

sub newReferral {
    my $self = shift;
    my($referrer, $host, $time) = @_;
    my $id = $self->_incrementID('referrals');
    $self->{handle}->do('INSERT INTO referrals SET id=?, referrer=?, host=?, time=?', undef, $id, $referrer, $host, $time);
    return $id;
}

sub checkReferral {
    my $self = shift;
    my($referral, $referrer, $destination) = @_;
    return $self->{handle}->selectrow_array('SELECT 1 FROM referrals WHERE id=? AND referrer=? AND (destination=? OR download=?)', undef, $referral, $referrer, $destination, $destination) ? 1 : 0;
}

sub removeReferral {
    my $self = shift;
    my($referral) = @_;
    $self->{handle}->do('DELETE FROM referrals WHERE id=?', undef, $referral);
}

sub updateReferralSetDestinationNotDownload {
    my $self = shift;
    my($referral, $destination) = @_;
    $self->{handle}->do('UPDATE referrals SET destination=? WHERE id=? AND destination IS NULL', undef, $destination, $referral);
}

sub updateReferralSetDestinationDownload {
    my $self = shift;
    my($referral, $download) = @_;
    $self->{handle}->do('UPDATE referrals SET download=? WHERE id=? AND download IS NULL', undef, $download, $referral);
}

sub updateReferralSetDestination {
    my $self = shift;
    my($referral, $destination) = @_;
    my $type = $self->{handle}->selectrow_array('SELECT type FROM destinations WHERE id=?', undef, $destination);
    return unless defined $type;
    if ($type == 0) {
        $self->updateReferralSetDestinationNotDownload($referral, $destination);
    } else {
        $self->updateReferralSetDestinationDownload($referral, $destination);
    }
}

sub updateReferralSetUser {
    my $self = shift;
    my($referral, $user) = @_;
    $self->{handle}->do('UPDATE referrals SET user=? WHERE id=? AND user IS NULL', undef, $user, $referral);
}

sub getReferralStatsForUser {
    my $self = shift;
    my($email) = @_;
    my($countDestinations, $countDownloads, $countUsers) = $self->{handle}->selectrow_array('SELECT COUNT(referrals.destination), COUNT(referrals.download), COUNT(referrals.user) FROM referrals, users WHERE referrals.referrer=users.id AND users.email=? GROUP BY referrals.referrer', undef, $email);
    return ($countDownloads, $countDownloads, $countUsers);
}

sub getTopReferralStats {
    my $self = shift;
    my($count) = @_;
    my $sth = $self->{handle}->prepare('SELECT users.email, users.name, COUNT(referrals.destination), COUNT(referrals.download), COUNT(referrals.user), COUNT(referrals.destination) + COUNT(referrals.download) + COUNT(referrals.user) as total FROM referrals, users WHERE users.id=referrals.referrer GROUP BY referrals.referrer ORDER BY total DESC LIMIT ?');
    $sth->execute($count);
    my $stats = [];
    while (my($email, $name, $a, $b, $c, $total) = $sth->fetchrow_array()) {
        push(@$stats, {
            'email' => $email,
            'name' => $name,
            'a' => $a,
            'b' => $b,
            'c' => $c,
            'total' => $total,
        });
    }
    return $stats;
}

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 = 1 unless defined $id;
    $self->{handle}->do('REPLACE keyValues SET tableName=?, nextKey=?', undef, $table, $id + 1);
    $self->{handle}->do('UNLOCK TABLES');
    return $id;
}