our @FILE_KEYS = qw(id username thread filename title date size private );
our @FILE_KEYS_KEYWORD = qw(id username thread filename title date size private keyword);
our $BUCKY_DB_TABLE_USERS	 	= 'users';
our $BUCKY_DB_TABLE_KEYWORDS	= 'keywords';
our $BUCKY_DB_TABLE_THREADS 	= 'threads';
our $BUCKY_DB_TABLE_COMMENTS 	= 'comments';
our $BUCKY_DB_TABLE_FILES 		= 'files';
our $BUCKY_DB_TABLE_THREADSTAGS	= 'threadstags';
our $BUCKY_DB_TABLE_TAGS 		= 'tags';
our $BUCKY_DB_TABLE_INVITES	 	= 'invites';

our @BUCKY_DB_COLUMNS_USERS =
	qw(id username password realname email aim grass ulevel firstseen lastseen lastsession timezone stickies sink display boxes);
our @BUCKY_DB_COLUMNS_THREADSTAGS = 
	qw(threadid tagid username createdate);
our @BUCKY_DB_COLUMNS_TAGS =
	qw(id tag createdate owner ops public display);
our @BUCKY_DB_COLUMNS_THREADS = 
	qw(title username keyword createdate lastmodified size private allowed color display viewed revision);

our @BUCKY_DB_COLUMNS_INVITES = 
	qw(id hash state attest created expired username password realname email grass keywords);
our @BUCKY_INVITE_STATES =
	qw(approved active request expired rejected redeemed);
our $BUCKY_INVITE_REQUEST = 2;
our $BUCKY_INVITE_ACTIVE = 1;
our $BUCKY_INVITE_APPROVED = 0;
our $BUCKY_INVITE_REDEEMED = -1;
our $BUCKY_INVITE_REJECTED = -2;
our $BUCKY_INVITE_EXPIRED = -3;

our $BUCKY_DB_ERROR_INSERT_USERS 		= 'failed to add user!';
our $BUCKY_DB_ERROR_INSERT_INVITES 		= 'failed to add invite!';
our $BUCKY_DB_ERROR_INSERT_TAGS 		= 'failed to add tag!';
our $BUCKY_DB_ERROR_INSERT_THREADS 		= 'failed to add post!';
our $BUCKY_DB_ERROR_INSERT_THREADSTAGS	= 'failed to add tag to post!';

sub new_user
	{
    my ($new_user) = @_;
	my $data;
	$data->{row}->{username} = $new_user->{username};
	$data->{row}->{password} = $new_user->{password}; # should already be encrypted
	$data->{row}->{email} = $new_user->{email};
	$data->{row}->{grass} = $new_user->{grass};
	$data->{row}->{realname} = $new_user->{realname};
#    $data->{row}->{boxes} = $dbh->quote($BUCKY_DEFAULT_BOXES);
    $data->{row}->{boxes} = $BUCKY_DEFAULT_BOXES;
	$data->{row}->{ulevel} = 1;
	$data->{row}->{firstseen} = time;
	$data->{row}->{lastseen} = time;
	$data->{row}->{lastsession} = time;
	$data->{row}->{timezone} = -8;
	$data->{table} = $BUCKY_DB_TABLE_USERS;
	$data->{columns} = \@BUCKY_DB_COLUMNS_USERS;
	$data->{error} = $BUCKY_DB_ERROR_INSERT_USERS;
	return add_row_by_hash( $data );
	}

sub get_user
	{
	my ($uname) = @_;
	my @fields = qw(id username password ulevel lastseen lastsession timezone stickies sink display boxes);
	my @row;
	my %temphash;

	$uname = $dbh->quote($uname);

	$query = "SELECT ";
	foreach my $k (@fields)
		{
		$query .= $k;
		if (++$i != @fields)
			{ $query .= ","; }
		}
	$query .= " FROM users WHERE username=$uname";

	$sth = $dbh->prepare($query);
	$sth->execute();
	@row = $sth->fetchrow_array();
	$sth->finish ();

	if (@row == 0)
		{ return -1; }

	for (my $i = 0; $i < @row; $i++)
		{
		$temphash{$fields[$i]} = $row[$i];
		}

	return \%temphash;
	}

sub get_uid
	{
	my ($name) = @_;
	my $user_id;
	my $rows = 0;
	$name = $dbh->quote($name);
	my $query = "SELECT id FROM users WHERE username = $name";
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		$user_id = $row[0];
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No such user!\n" if ($DEBUG);
		return -1;
		}
	return $user_id;
	}

sub get_all_users
	{
	my ($limit) = @_;
	my @rows;
	my $rows = 0;
	my @keys =  @BUCKY_DB_COLUMNS_USERS;
  my $keylist = join ",", @keys;
	my $query = "SELECT $keylist FROM users WHERE ulevel > -1";
	$query .= " AND lastseen > $limit" if (defined($limit));
	print $query."<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		print $row[0]." " if ($DEBUG);
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No users!\n" if ($DEBUG);
		return -1;
		}

	my $ccount = count_comments_by_user();
	my $fcount = count_files_by_user();
	
	foreach my $user (@rows)
		{
		my $this_user = $user->{username};
		$user->{ccount} = exists($ccount->{$this_user}) ? $ccount->{$this_user} : 0;
		$user->{fcount} = exists($fcount->{$this_user}) ? $fcount->{$this_user} : 0;
		}
	return \@rows;
	}

sub count_files_by_user
	{
	my %temphash;
	my $query = "SELECT username,COUNT(*) FROM files GROUP BY username";
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		$temphash{"$row[0]"} = $row[1];
		}
	$sth->finish();
	return \%temphash;
	}

sub count_comments_by_user
	{
	my %temphash;
	my $query = "SELECT username,COUNT(*) FROM comments GROUP BY username";
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		$temphash{"$row[0]"} = $row[1];
		}
	$sth->finish();
	return \%temphash;
	}

sub get_username
	{
	my ($id) = @_;
	my $username;
	$id = $dbh->quote($id);
	my $query = "SELECT username FROM users WHERE id = $id";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($username) = $sth->fetchrow_array();
	$sth->finish ();
	return $username;
	}

sub get_user_profile
	{
	my ($uname) = @_;
	my @fields = qw[username realname email aim phone location timezone lastseen display boxes stickies sink];
	my @row;
	my %temphash;
	$uname = $dbh->quote($uname);
	my $query = "SELECT username,realname,email,aim,phone,location,timezone,lastseen,display,boxes,stickies,sink FROM users WHERE username=$uname";
	$sth = $dbh->prepare($query);
	$sth->execute();
	@row = $sth->fetchrow_array();
	$sth->finish ();
	for (my $i = 0; $i < @row; $i++)
		{
		$temphash{$fields[$i]} = $row[$i];
		}
	return \%temphash;
	}

sub update_profile
	{
	my ($uname, $prof) = @_;
	my %q;
	my $i = 0;
	my $query;
	my @fields = qw[realname email aim phone location timezone display stickies sink];

	$q{date} = $dbh->quote(time);
	$uname = $dbh->quote($uname);

	foreach my $key (@fields)
		{
		$q{$key} = $dbh->quote($$prof{$key});
		}

	$query = "UPDATE users SET ";
	foreach my $k (@fields)
		{
		$query .= $k."=".$q{$k};
		if (++$i != @fields)
			{ $query .= ","; }
		}
	$query .= " WHERE username=$uname";

	my $rows = $dbh->do($query);

	if ($rows == 0)
	       { print "failed to update profile !"; return 0; }

	return 1;
	}

sub update_user_boxes
	{
	my ($uname, $boxes) = @_;
	$uname = $dbh->quote($uname);
	$boxes = $dbh->quote($boxes);
	$query = "UPDATE users SET boxes=$boxes WHERE username=$uname";
	$dbh->do($query);
	}

sub update_lastsession
	{
	my ($uname) = @_;
	$uname = $dbh->quote($uname);
	$query = "UPDATE users SET lastsession=lastseen WHERE username=$uname";
	$dbh->do($query);
	}

sub update_password
	{
	my ($uname, $newpass) = @_;
	$uname = $dbh->quote($uname);
	$newpass = $dbh->quote($newpass);
	$query = "UPDATE users SET password=$newpass WHERE username=$uname";
	$dbh->do($query);
	}

sub touch_user
	{
	my ($uname) = @_;
	my $t = time;

	$t = $dbh->quote($t);
	$uname = $dbh->quote($uname);
	my $query = "UPDATE users SET lastseen=$t WHERE id=$uname";
	my $rows = $dbh->do($query);

	if ($rows == undef)
		{ error("failed to touch user $uname !"); }

	return 1;
	}

sub update_user_sticky
	{
	my ($uname, $stick) = @_;

	$uname = $dbh->quote($uname);
	$stick = $dbh->quote($stick);
	my $query = "UPDATE users SET stickies=$stick WHERE username=$uname";
	print $query if ($DEBUG);
	my $rows = $dbh->do($query);
	}

sub update_user_sink
	{
	my ($uname, $sink) = @_;

	$uname = $dbh->quote($uname);
	$sink = $dbh->quote($sink);
	my $query = "UPDATE users SET sink=$sink WHERE username=$uname";
	print $query if ($DEBUG);
	my $rows = $dbh->do($query);
	}

sub get_lastlog
	{
	my @rows;
	my $rows = 0;
	my @keys = qw[username lastseen];
	my $query = "SELECT username,lastseen FROM users WHERE ulevel > 0 ORDER BY lastseen DESC LIMIT 6";
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No users!\n" if ($DEBUG);
		return -1;
		}

	return \@rows;
	}

#########################################
# INVITES
#########################################

sub new_invite
	{
	my ($invitehash) = @_;
	my $data;
	$data->{row} = $invitehash;
	$data->{row}->{created} = time;
	$data->{row}->{expired} = time + 86400*7;
	return add_invite_by_hash( $data );
	}

sub add_invite_by_hash
	{
	my ($data) = @_;
	$data->{table} = $BUCKY_DB_TABLE_INVITES;
	$data->{columns} = \@BUCKY_DB_COLUMNS_INVITES;
	$data->{error} = $BUCKY_DB_ERROR_INSERT_INVITES;
	return add_row_by_hash( $data );
	}

sub set_invite_state
	{
	my ($invite, $state) = @_;

	if (! ref($invite))
		{ return -1; }

	$invite->{state} = $state;

	$id = $dbh->quote($invite->{id});
	$state = $dbh->quote($state);
	$query = "UPDATE $BUCKY_DB_TABLE_INVITES SET state=$state WHERE id=$id";
	print $query."<br>" if $DEBUG;
	$dbh->do($query);
	}

sub set_invite_expired
	{
	my ($invite, $expired) = @_;

	if (! ref($invite))
		{ return -1; }

	$invite->{expired} = $expired;

	$id = $dbh->quote($invite->{id});
	$expired = $dbh->quote($expired);
	$query = "UPDATE $BUCKY_DB_TABLE_INVITES SET expired=$expired WHERE id=$id";
	print $query."<br>" if $DEBUG;
	$dbh->do($query);
	}

sub set_invite_username
	{
	my ($invite, $username) = @_;

	if (! ref($invite))
		{ return -1; }

	$invite->{username} = $username;

	$id = $dbh->quote($invite->{id});
	$username = $dbh->quote($username);
	$query = "UPDATE $BUCKY_DB_TABLE_INVITES SET username=$username WHERE id=$id";
	print $query."<br>" if $DEBUG;
	$dbh->do($query);
	}

sub get_invite_from_id
	{
	my ($id) = @_;
    my @rows;
    my $query;
    my @columns = @BUCKY_DB_COLUMNS_INVITES;
	my $joined_keys = join ",", @columns;
    my $rows = 0;
    my %finalhash;

    $id = $dbh->quote($id);
    $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE id=$id";

    print $query."<br>" if ($DEBUG);
    $sth = $dbh->prepare($query);
    $sth->execute();
    if ((@row) = $sth->fetchrow_array())
        {
        for (my $i = 0; $i < @row; $i++)
            {
            $finalhash{$columns[$i]} = $row[$i];
            }
        $rows++;
        }
    $sth->finish();

    if ($rows == 0)
        {
        print "No such invite!\n" if ($DEBUG);
        return -1;
        }

    return \%finalhash;
	}

sub get_invite_from_hash
	{
	my ($hash) = @_;
    my @rows;
    my $query;
    my @columns = @BUCKY_DB_COLUMNS_INVITES;
	my $joined_keys = join ",", @columns;
    my $rows = 0;
    my %finalhash;

    $hash = $dbh->quote($hash);
    $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE hash=$hash";

    print $query."<br>" if ($DEBUG);
    $sth = $dbh->prepare($query);
    $sth->execute();
    if ((@row) = $sth->fetchrow_array())
        {
        for (my $i = 0; $i < @row; $i++)
            {
            $finalhash{$columns[$i]} = $row[$i];
            }
        $rows++;
        }
    $sth->finish();

    if ($rows == 0)
        {
        print "No such invite!\n" if ($DEBUG);
        return -1;
        }

    return \%finalhash;
	}

sub get_active_invites
	{
    my $query;
    my @columns = @BUCKY_DB_COLUMNS_INVITES;
	my $joined_keys = join ",", @columns;
    my $rows = 0;
    my @finalarray;
	my $now = $dbh->quote(time);

    $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE expired > $now";

    print $query."<br>" if ($DEBUG);
    $sth = $dbh->prepare($query);
    $sth->execute();
    while (my (@row) = $sth->fetchrow_array())
        {
        my %temphash;
        for (my $i = 0; $i < @row; $i++)
            {
            $temphash{$columns[$i]} = $row[$i];
            }
        push @finalarray, \%temphash;
        $rows++;
        }
    $sth->finish();

    if ($rows == 0)
        {
        print "No invites!\n" if ($DEBUG);
        return -1;
        }

    return \@finalarray;
	}

sub get_invites_by_user
	{
	my ($username) = @_;

    my $query;
    my @columns = @BUCKY_DB_COLUMNS_INVITES;
	my $joined_keys = join ",", @columns;
    my $rows = 0;
    my @finalarray;
	my $username = $dbh->quote($username);

    $query = "SELECT $joined_keys FROM $BUCKY_DB_TABLE_INVITES WHERE attest=$username";

    print $query."<br>" if ($DEBUG);
    $sth = $dbh->prepare($query);
    $sth->execute();
    while (my (@row) = $sth->fetchrow_array())
        {
        my %temphash;
        for (my $i = 0; $i < @row; $i++)
            {
            $temphash{$columns[$i]} = $row[$i];
            }
        push @finalarray, \%temphash;
        $rows++;
        }
    $sth->finish();

    if ($rows == 0)
        {
        print "No invites!\n" if ($DEBUG);
        return -1;
        }

    return \@finalarray;
	}

sub count_active_user_invites
	{
	my ($username) = @_;
    my $count = 0;
    $username = $dbh->quote($username);
    my $query = "SELECT COUNT(*) FROM $BUCKY_DB_TABLE_INVITES WHERE attest=$username AND state > 0";
    $sth = $dbh->prepare($query);
    $sth->execute();
    ($count) = $sth->fetchrow_array();
    $sth->finish ();
    return $count;
    }

#########################################
# BOXES
#########################################

sub get_boxes
	{
	my ($uname) = @_;
	my @rows;
	my $query;
	my @keys = qw(id mbox owner mcount editable);
	my $rows = 0;

	$uname = $dbh->quote($uname);
	$query = "SELECT id,mbox,owner,mcount,editable FROM boxes WHERE owner=$uname";

	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No messages!\n" if ($DEBUG);
		return -1;
		}

	return \@rows;
	}

sub recount_mailbox
	{
	my ($box) = @_;

	my $count = count_messages($box);

	$box = $dbh->quote($box);
	$count = $dbh->quote($count);
	my $query = "UPDATE boxes SET mcount=$count WHERE mbox=$box";
	my $rows = $dbh->do($query);

	if ($rows == 0)
		{ error("failed to tweez mcount of $box!"); }

	return 1;
	}

sub count_messages
	{
	my ($box) = @_;
	my $count = 0;
	$box = $dbh->quote($box);
	my $query = "SELECT COUNT(*) FROM messages WHERE mbox=$box";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($count) = $sth->fetchrow_array();
	$sth->finish ();
	return $count;
	}

sub get_messages
	{
	my ($mbox, $limit, $startdate) = @_;
	my @rows;
	my $query;
	my @keys = qw(id mbox unread sender recipient date subject size);
	my $rows = 0;

	$mbox = $dbh->quote($mbox);
	$query = "SELECT id,mbox,unread,sender,recipient,date,subject,length(body) FROM messages WHERE mbox=$mbox";
	
	if ($startdate && $startdate ne "now")
		{
		$query .= " AND (date < $startdate)";
		}
	$query .= " ORDER BY date DESC";
	if ($limit)
		{
		$query .= " LIMIT $limit";
		}

	print "<tt>$query</tt><br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No messages!\n" if ($DEBUG);
		return -1;
		}

	return \@rows;
	}

sub add_mailbox
	{
	my ($box, $name, $editable) = @_;
	$box = $dbh->quote($box);
	$name = $dbh->quote($name);
	$editable = $dbh->quote($editable);
	my $query = "INSERT boxes (mbox,owner,editable) VALUES($box,$name,$editable)";
	my $rows = $dbh->do($query);
	return $rows;
	}

#########################################
# MESSAGES
#########################################

sub unflag_message
	{
	my ($id) = @_;

	$id = $dbh->quote($id);
	my $query = "UPDATE messages SET unread=0 WHERE id=$id";
	my $rows = $dbh->do($query);

	if ($rows == undef)
		{ error("failed to unread $id !"); }

	return 1;
	}

sub get_message
	{
	my ($id) = @_;
	my @row;
	my @keys = qw(id mbox unread sender recipient date subject body);
	my $rows = 0;
	my %temphash;
	$id = $dbh->quote($id);
	my $query = "SELECT id,mbox,unread,sender,recipient,date,subject,body FROM messages WHERE id=$id";
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows++;
		}
	$sth->finish ();
	if ($rows == 0)
		{
		print "No such message!\n" if ($DEBUG);
		return -1;
		}
	return \%temphash;
	}

sub count_new_messages
	{
	my ($uname) = @_;
	my $count = 0;
	$uname = $dbh->quote($uname);
	$ur = $dbh->quote("1");
	my $query = "SELECT COUNT(*) FROM messages WHERE recipient=$uname AND unread=$ur";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($count) = $sth->fetchrow_array();
	$sth->finish ();
	return $count;
	}

sub new_message
	{
	my ($mbox, $m) = @_;
	my %q;

	$q{date} = $dbh->quote(time);

	$qmbox = $dbh->quote($mbox);
	foreach my $key (keys %$m)
		{
		$q{$key} = $dbh->quote($$m{$key});
		}
	my $query = "INSERT messages (mbox,unread,sender,recipient,date,subject,body) VALUES($qmbox,$q{unread},$q{sender},$q{recipient},$q{date},$q{subject},$q{body})";
	my $rows = $dbh->do($query);
       if ($rows == 0)
	       { print "failed to send message !"; return 0; }

	recount_mailbox($mbox);
	return 1;
	}

sub update_message
	{
	my ($id, $m) = @_;
	my %q;
	my $i = 0;
	my $query;
	my @fields = qw(sender recipient date subject body);
	$q{date} = $dbh->quote(time);

	$id = $dbh->quote($id);

	foreach my $key (keys %$m)
		{
		$q{$key} = $dbh->quote($m->{$key});
		}

	$query = "UPDATE messages SET ";
	foreach my $k (@fields)
		{
		$query .= $k."=".$q{$k};
		if (++$i != 5)
			{ $query .= ","; }
		}
	$query .= " WHERE id=$id";

	my $rows = $dbh->do($query);

	if ($rows == 0)
	       { print "failed to update message !"; return 0; }

	return 1;
	}


sub delete_message
	{
	my ($id) = @_;
	$id = $dbh->quote($id);
	my $query = "DELETE FROM messages WHERE id=$id";
	my $rows = $dbh->do($query);
	}
#########################################
# FILES
#########################################

sub get_file
	{
	my ($id) = @_;
	my @rows;
	my $rows = 0;
	my $query;
	my %temphash;

	$id = $dbh->quote($id);
	my $keys = join ",", @FILE_KEYS;
	$query = "SELECT $keys FROM files WHERE id=$id";

	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$FILE_KEYS[$i]} = $row[$i];
			}
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No files!\n" if ($DEBUG);
		return -1;
		}

	return \%temphash;
	}

sub get_file_from_filename
	{
	my ($filename) = @_;
	my @rows;
	my $rows = 0;
	my $query;
	my %temphash;

	$filename = $dbh->quote($filename);
	my $keys = join ",", @FILE_KEYS;
	$query = "SELECT $keys FROM files WHERE filename=$filename";

	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		for (my $i = 0; $i < @row; $i++)
			{ $temphash{$FILE_KEYS[$i]} = $row[$i]; }
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No files!\n" if ($DEBUG);
		return -1;
		}

	return \%temphash;
	}

sub get_files
	{
	my ($pid) = @_;
	my @rows;
	my $rows = 0;
	my $query;

	$pid = $dbh->quote($pid);
	my $keys = join ",", @FILE_KEYS;
	$query = "SELECT $keys FROM files WHERE thread=$pid";

	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$FILE_KEYS[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{ return -1; }

	return \@rows;
	}
sub get_recent_files
	{
	my @rows;
	my $rowcount = 0;
	my $query;

	my $keys = join ",", @FILE_KEYS;
	$query = "SELECT $keys FROM files ORDER BY date DESC LIMIT 20";


	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$FILE_KEYS[$i]} = $row[$i];
			}
		push(@rows, \%temphash );
		$rowcount++;
		}
	$sth->finish();

	if ($rowcount == 0)
		{ return -1; }

	return \@rows;
	}
sub get_thread_comment_modified
	{
	my ($id) = @_;
	my $query;

	$id = $dbh->quote($id);
	$query = "SELECT MAX(date) FROM comments WHERE id=$id";

	$sth = $dbh->prepare($query);
	$sth->execute();
	my ($date) = $sth->fetchrow_array();
	$sth->finish();

	if ($date == 0)
		{ return -1; }

	return $date;
	}



sub get_tag_files
	{
	my ($tag) = @_;
	my @rows;
	my @FILE_KEYS_JOIN = qw(files.id files.username thread filename files.title files.date files.size files.private threads.keyword);
	my $rows = 0;
	$tag= $dbh->quote($tag);
	my $keys = join ",", @FILE_KEYS_JOIN;

	my $query = "SELECT $keys FROM files,threads,threadstags,tags " .
		"WHERE files.thread=threads.id AND threads.id=threadstags.threadid " .
		"AND threadstags.tagid=tags.id AND tags.tag=$tag";
	$sth = $dbh->prepare( $query );
	$sth->execute();

	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$FILE_KEYS_KEYWORD[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No files!\n" if ($DEBUG);
		return -1;
		}

	return \@rows;
	}
sub get_keyword_files
	{
	my ($keyword) = @_;
	my @rows;
	my @FILE_KEYS_JOIN = qw(files.id files.username thread filename files.title files.date files.size files.private threads.keyword);
	my $rows = 0;
	$keyword = $dbh->quote($keyword);
	my $keys = join ",", @FILE_KEYS_JOIN;

	my $query = "SELECT $keys FROM files,threads WHERE files.thread=threads.id AND threads.keyword=$keyword";
	$sth = $dbh->prepare( $query );
	$sth->execute();

	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$FILE_KEYS_KEYWORD[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No files!\n" if ($DEBUG);
		return -1;
		}

	return \@rows;
	}
sub get_user_files
	{
	my ($uname) = @_;
	my @rows;
	my $rows = 0;
	my $query;

	$uname = $dbh->quote($uname);
	my @FILE_KEYS_JOIN = qw(files.id files.username files.thread files.filename files.title files.date files.size files.private threads.keyword);
	my $keys = join ",", @FILE_KEYS_JOIN;
	$query = "SELECT $keys FROM files,threads WHERE files.username=$uname AND files.thread=threads.id";

	$sth = $dbh->prepare($query);
	$sth->execute();
	print $query."<br>" if ($DEBUG);
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$FILE_KEYS_KEYWORD[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No files!\n" if ($DEBUG);
		return -1;
		}

	return \@rows;
	}

sub get_largest_files
	{
	my @rows;
	my $rows = 0;
	my $query;
	my @keys = qw(id username thread filename date size private);

	$pid = $dbh->quote($pid);
	my $keys = join ",", @FILE_KEYS;
	$query = "SELECT $keys FROM files WHERE size > 100000000";

	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$FILE_KEYS[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{ return -1; }

	return \@rows;
	}


sub add_file
	{
	my ($pid, $uname, $filename, $size, $date) = @_;
	my $f_id;

	$pid = $dbh->quote($pid);
	$uname = $dbh->quote($uname);
	$filename = $dbh->quote($filename);
	$date = $dbh->quote($date);
	$size = $dbh->quote($size);
	my $query = "INSERT files (thread,username,date,filename,size) VALUES($pid,$uname,$date,$filename,$size)";
	my $rows = $dbh->do($query);
	if ($rows == 0)
		{ error("failed to add file !"); }

	$query = "SELECT id FROM files WHERE username=$uname AND date=$date";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($f_id) = $sth->fetchrow_array();
	$sth->finish ();

	return $f_id;
	}

sub delete_file_record
	{
	my ($id) = @_;
	$id = $dbh->quote($id);
	my $query = "DELETE FROM files WHERE id=$id";
	my $rows = $dbh->do($query);
	}

sub count_user_files
	{
	my ($uname) = @_;
	my @row;
	$uname = $dbh->quote($uname);
	my $query = "SELECT COUNT(*), SUM(size) FROM files WHERE username=$uname";
	$sth = $dbh->prepare($query);
	$sth->execute();
	(@row) = $sth->fetchrow_array();
	$sth->finish ();
	return @row;
	}

sub count_files
	{
	my ($thread) = @_;
	my $count;
	$thread = $dbh->quote($thread);
	my $query = "SELECT COUNT(*) FROM files WHERE thread=$thread";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($count) = $sth->fetchrow_array();
	$sth->finish ();
	return $count;
	}

#########################################
# COMMENTS
#########################################

sub get_comment
	{
	my ($comment_id) = @_;

	#$comment_id = $dbh->quote($comment_id);

	$query = "SELECT * FROM comments WHERE id=$comment_id";

	print "$query<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	my $comment = $sth->fetchrow_hashref();
	$sth->finish ();

	return $comment || -1;
	}

sub get_comments
	{
	my ($thread, $count) = @_;
	my %rows_by_id;
	my $query;
	my $rows = 0;

	return -1 if (!defined($thread));
	$query = "SELECT * FROM comments WHERE thread=$thread"; 
  if ($count)
    { $query .= " ORDER BY id DESC LIMIT $count"; }
print $query."<br>" if $DEBUG;
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my $row = $sth->fetchrow_hashref)
		{
		my $id = $row->{'id'};
		$rows_by_id{$id} = $row;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{ return -1; }

	return \%rows_by_id;
	}

sub count_comments
	{
	my ($thread) = @_;
	my $count;
	$thread = $dbh->quote($thread);
	my $query = "SELECT COUNT(*) FROM comments WHERE thread = $thread";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($count) = $sth->fetchrow_array();
	$sth->finish ();
	return $count;
	}

sub add_comment
	{
	my ($thread, $pid, $uname, $comment, $time) = @_;
	my ($t) = $time || time;
	my $qpid;
	my $c_id;

	$thread = $dbh->quote($thread);
	$pid = $dbh->quote($pid);
	$uname = $dbh->quote($uname);
	$comment = $dbh->quote($comment);
	$t = $dbh->quote($t);
    my $query = "INSERT comments (thread,parent_id,username,date,comment) VALUES($thread,$pid,$uname,$t,$comment)";
    my $rows = $dbh->do($query);
    if ($rows == undef)
	{ error("failed to add comment !"); }

	$query = "SELECT id FROM comments WHERE username=$uname AND date=$t";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($c_id) = $sth->fetchrow_array();
	$sth->finish ();

	return $c_id;
	}

sub update_comment
	{
	my ($id, $comment) = @_;
	my ($t) = time;

	$id = $dbh->quote($id);
	$comment = $dbh->quote($comment);
	$t = $dbh->quote($t);
	my $query = "UPDATE comments SET date=$t,comment=$comment where id=$id";
	my $rows = $dbh->do($query);
	if ($rows == undef)
		{ error("failed to edit comment !"); }

	return 1;
	}

sub delete_comment
	{
	my ($id) = @_;
	$id = $dbh->quote($id);
	my $query = "DELETE FROM comments WHERE id=$id";
	my $rows = $dbh->do($query);
	}

#########################################
# TAGS
#########################################

sub get_tag
	{
	my ($tag_name) = @_;
	$tag_name = $dbh->quote( $tag_name );
	my @columns = qw(id tag createdate owner ops public display);
	my $query = "SELECT id,tag,createdate,owner,ops,public,display FROM tags WHERE tag =$tag_name";
	print "$query<br>" if ($DEBUG);
	my $sth = $dbh->prepare( $query );
	$sth->execute();
	my $rows = 0;
	my %temphash;
	if ((@row) = $sth->fetchrow_array())
		{
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$columns[$i]} = $row[$i];
			}
		$rows++;
		}
	if ($rows == 0)
		{
		print "No such tag!\n" if ($DEBUG);
		return -1;
		}
	return \%temphash;
	}
sub get_tag_count
	{
	my ($tag) = @_;
	my $count = 0;
	$tag = $dbh->quote( $tag );
	my $query = "SELECT COUNT(*) FROM tags WHERE tag=$tag";
	my $sth = $dbh->prepare( $query );
	$sth->execute;
	($count) = $sth->fetchrow_array();
	$sth->finish();
	return $count;
	}
sub get_tag_names
	{
	my @tags;
	my $query = "SELECT DISTINCT tag from tags,threadstags,threads " .
		"WHERE threadstags.tagid=tags.id AND threadstags.threadid=threads.id " .
		"AND ((threads.private = 0)";
	if ( $USER == -1 )
		{ $query .= ")"; }
	else
		{ $query .= " OR (threads.allowed LIKE \"% " . $USER->{id} . " %\"))"; }
	print "$query<br>" if ($DEBUG);
	$sth = $dbh->prepare( $query );
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		push ( @tags, $row[0] );
		}
	$sth->finish();
	if (@tags == 0)
		{
#		print "No tags!\n" if ($DEBUG);
		return -1;
		}
	return \@tags;
	}
sub get_tags_for_thread
	{
	my ($id) = @_;
	my @keys = qw(tag createdate owner ops public display);
	my @rows;
	my %temphash;
	$id = $dbh->quote($id) unless ($id =~ /'/);
	my $query = "SELECT tags.tag FROM threadstags,tags WHERE threadstags.threadid=$id AND threadstags.tagid=tags.id";
	print "$query<br>" if ($DEBUG);
	$sth = $dbh->prepare( $query );
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		push( @rows, $row[0] );
		}
	$sth->finish ();
	if (@rows == 0)
		{
#		print "No tags for post!\n" if ($DEBUG);
		return -1;
		}
	return \@rows;
	}

sub delete_tag_for_thread
	{
	my ($tag_id, $thread_id) = @_;

	if (ref($tag_id))
		{ $tag_id = $tag_id->{id}; }
	if (ref($thread_id))
		{ $thread_id = $thread_id->{id}; }

	# Escape id numbers for SQL
	$tag_id = $dbh->quote($tag_id);
	$thread_id = $dbh->quote($thread_id);

	my $query = "DELETE FROM threadstags WHERE threadid=$thread_id AND tagid=$tag_id";
	my $rows = $dbh->do($query);
	}

sub increment_tag_for_thread_by_hash
	{
	}
sub new_tag
	{
	my ($tag) = @_;
	my $data;
	$data->{row}->{tag} = $tag;
	$data->{row}->{owner} = $USER->{username};
	$data->{row}->{createdate} = time;
	$data->{row}->{public} = 0;
	return add_tag_by_hash( $data );
	}

sub add_tag_by_hash
	{
	my ($data) = @_;
	$data->{table} = $BUCKY_DB_TABLE_TAGS;
	$data->{columns} = \@BUCKY_DB_COLUMNS_TAGS;
	$data->{error} = $BUCKY_DB_ERROR_INSERT_TAGS;
	return add_row_by_hash( $data );
	}

sub add_row_by_hash
	{
	# Data hash
	my ($data) = @_;

	# Return row id, retrieved identifier, or -1 if failure
	my $id = -1;

	# Columns for this table/row, required
	my $columns = $data->{columns} || return $returnVal;

	# SQL statement handle
	my $sth;

	# Initialize query with SQL INSERT
	my $query = "INSERT " . $data->{table} . " SET ";

	# Flag for prepending comma after first column
	my $first_column = 0;

#	print "tag: " . $data->{row}->{tag} . "<br>\n";
#	print $data->{row}->{owner} . "<br>\n";
#	print "columns : " .$columns  . "<br>\n";
	# Loop through row's columns to build SQL statement
	my $first_set = 0;
	foreach my $column (@$columns)
		{	
#		print $data->{row}->{$column} . "<BR>\n";
		# skip if this column is not provided for this row
		next unless exists( $data->{row}->{$column} );

		# Prepend comma for every column after the first
		$query .= ", " unless ($first_set++ == 0);

		# SQL quote each column, append column name and value to insert statement
		$query .= $column . "=" . $dbh->quote( $data->{row}->{$column} );
 		}

	# Debug output the SQL query
	print $query . "<br>" if ($DEBUG);

	# Execute SQL query
	my $rows = $dbh->do( $query );

	# Return any query error
	if ($rows == undef)
		{ error( $data->{error} ); }

	# If there is a special identifying value to return for this inserted row
	if ( ref($data->{retrieve}) )
		{
		# Construct query for the requested column 
		$query = "SELECT " . $data->{retrieve}->{column} . " FROM " . $data->{table};

		# Specify qualifiers with key and value
		$query .= " WHERE " . $data->{retrieve}->{key} . "=" . $data->{retrieve}->{value};

		# Prepare and execute SQL query
		$sth = $dbh->prepare( $query );
		$sth->execute();

		# Return identifying value (the first one, hopefully no multiple matches
		($id) = $sth->fetchrow_array();
		# Close SQL statement handle
		$sth->finish();
		}
	elsif ( $data->{retrieve} )
		{
		$id = 0;
		}
	else
		{	
		# Retrieve last auto-incremented value as a result of the last SQL INSERT- this is the ID
		$id = $dbh->{'mysql_insertid'};
		}


	# Return ID column or identifying value
	return $id;
	}

sub add_tag_for_thread_by_hash
	{
	my ($newt) = @_;
	my $thread_id = -1;
	@keys = qw(title username keyword createdate lastmodified size private allowed color display viewed);
	my @qkeys;
	my %q;
	my $i = 0;
	my $sth;

	foreach my $key (@keys)
		{
		if (exists $newt->{$key})
			{
			$q{$key} = $dbh->quote($newt->{$key});
			}
		}

	@qkeys = keys %q;

	$query = "INSERT threads SET ";
	foreach my $k (@qkeys)
		{
		$query .= $k."=".$q{$k};
		if (++$i != @qkeys)
			{ $query .= ","; }
		}

	print $query."<br>" if ($DEBUG);
	my $rows = $dbh->do($query);
	if ($rows == undef)
		{ error("failed to add post!"); }

	$query = "SELECT id FROM threads WHERE title=$q{title}";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($thread_id) = $sth->fetchrow_array();
	$sth->finish ();

	return $thread_id;
	}
sub update_tag_for_thread
	{
	my ($tag_id, $thread_id) = @_;

	if (ref($tag_id))
		{ $tag_id = $tag_id->{id}; }
	if (ref($thread_id))
		{ $thread_id = $thread_id->{id}; }

	$data->{table} = $BUCKY_DB_TABLE_THREADSTAGS;
	$data->{columns} = \@BUCKY_DB_COLUMNS_THREADSTAGS;
	$data->{error} = $BUCKY_DB_ERROR_INSERT_THREADSTAGS;
	$data->{row}->{'threadid'} = $thread_id;
	$data->{row}->{'tagid'} = $tag_id;
	$data->{row}->{'username'} = $USER->{username};
	$data->{row}->{'createdate'} = time;

	add_row_by_hash( $data );
	return 1;
	}
#########################################
# THREADS
#########################################
sub get_thread
	{
	my ($id) = @_;
	my @row;
	my @keys = qw(id title username keyword createdate lastmodified size private allowed flagged display color zipped viewed revision);
	my $rows = 0;
	my %temphash;
	$id = $dbh->quote($id);
	my $query = "SELECT id,title,username,keyword,createdate,lastmodified,size,private,allowed,flagged,display,color,zipped,viewed,revision FROM threads WHERE id=$id";
	print "$query<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	if ((@row) = $sth->fetchrow_array())
		{
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows++;
		}
	$sth->finish ();
	if ($rows == 0)
		{
		print "No such post!\n" if ($DEBUG);
		return -1;
		}
	$temphash{comments} = count_comments($temphash{id});
	$temphash{files} = count_files($temphash{id});
	$temphash{tags} = []; # get_tags_for_thread( $id );
	return \%temphash;
	}

sub add_tag_by_hash_v2
	{
	}
sub add_tag_for_thread_by_hash_v2
	{
	}
sub add_thread_by_hash_v2
	{
	my ($data) = @_;
	$data->{table} = $BUCKY_DB_TABLE_THREADS;
	$data->{columns} = \@BUCKY_DB_COLUMNS_THREADS;
	$data->{error} = $BUCKY_DB_ERROR_INSERT_THREADS;
	return add_row_by_hash( $data );
	}

sub add_thread_by_hash
	{
	my ($newt) = @_;
	my $thread_id = -1;
	@keys = qw(title username keyword createdate lastmodified size private allowed color display viewed);
	my @qkeys;
	my %q;
	my $i = 0;
	my $sth;

	foreach my $key (@keys)
		{
		if (exists $newt->{$key})
			{
			$q{$key} = $dbh->quote($newt->{$key});
			}
		}

	@qkeys = keys %q;

	$query = "INSERT threads SET ";
	foreach my $k (@qkeys)
		{
		$query .= $k."=".$q{$k};
		if (++$i != @qkeys)
			{ $query .= ","; }
		}

	print $query."<br>" if ($DEBUG);
	my $rows = $dbh->do($query);
	if ($rows == undef)
		{ error("failed to add post!"); }

	$query = "SELECT id FROM threads WHERE title=$q{title}";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($thread_id) = $sth->fetchrow_array();
	$sth->finish ();

	return $thread_id;
	}

sub add_thread
	{
	my ($title, $uname, $private) = @_;
	my ($t) = time;
	my $thread_id;

	if (!defined($private))
		{ $private = 0; }

	$title = $dbh->quote($title);
	$uname = $dbh->quote($uname);
	$private = $dbh->quote($private);
	my $viewed = 0;
	$t = $dbh->quote($t);
	my $query = "INSERT threads (title,username,createdate,lastmodified,private,viewed) VALUES($title,$uname,$t,$t,$private,$viewed)";
	my $rows = $dbh->do($query);
	if ($rows == undef)
		{ error("failed to add post!"); }
	
	$query = "SELECT id FROM threads WHERE title=$title";
	$sth = $dbh->prepare($query);
	$sth->execute();
	($thread_id) = $sth->fetchrow_array();
	$sth->finish ();

	return $thread_id;
	}

sub touch_thread
	{
	my ($thread) = @_;
	my $time = time;

	return 0 if ($thread == -1);

	my $id = $dbh->quote($thread->{id});
	my $revision = $dbh->quote($thread->{revision} + 1);
	$time = $dbh->quote($time);

	my $query = "UPDATE threads SET lastmodified=$time,revision=$revision WHERE id=$id";
	my $rows = $dbh->do($query);

	if ($rows == undef)
		{ error("failed to touch post $id !"); }

	return 1;
	}

sub update_thread_size
	{
	my ($id) = @_;
	my @row;
	my $rows;
	$id = $dbh->quote($id);
	my $query = "SELECT SUM(size) FROM files WHERE thread=$id";
	$sth = $dbh->prepare($query);
	$sth->execute();
	(@row) = $sth->fetchrow_array();
	$size = $row[0];
	$sth->finish ();

	$size = $dbh->quote($size);
	$query = "UPDATE threads SET size=$size WHERE id=$id";
	$rows = $dbh->do($query);
	}

sub update_thread_lastmodified
	{
	my ($id, $disp) = @_;
	$id = $dbh->quote($id);
	$disp = $dbh->quote($disp);
	$query = "UPDATE threads SET lastmodified=$disp WHERE id=$id";
	$dbh->do($query);
	}
sub update_thread_display
	{
	my ($id, $disp) = @_;
	$id = $dbh->quote($id);
	$disp = $dbh->quote($disp);
	$query = "UPDATE threads SET display=$disp WHERE id=$id";
	$dbh->do($query);
	}

sub update_thread_color
	{
	my ($id, $disp) = @_;
	$id = $dbh->quote($id);
	$disp = $dbh->quote($disp);
	$query = "UPDATE threads SET color=$disp WHERE id=$id";
	$dbh->do($query);
	}

sub update_thread_zipped
	{
	my ($id, $val) = @_;
	$id = $dbh->quote($id);
	$val = $dbh->quote($val);
	$query = "UPDATE threads SET zipped=$val WHERE id=$id";
	$dbh->do($query);
	}

sub update_thread_viewed
	{
	my ($id, $val) = @_;
	$id = $dbh->quote($id);
	$val = $dbh->quote($val);
	$query = "UPDATE threads SET viewed=$val WHERE id=$id";
	$dbh->do($query);
	}

sub get_threads
	{
	my @rows;
	my $query;
	my @keys = qw(id title username keyword date size private allowed flagged color viewed revision);
	my $rows = 0;

	$query = "SELECT id,title,username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads";

	print "$query<br>\n" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No posts!\n" if ($DEBUG);
		return -1;
		}

	for (my $i = 0; $i < @rows; $i++)
		{
		$rows[$i]{comments} = count_comments($rows[$i]{id});
		$rows[$i]{files} = count_files($rows[$i]{id});
		$rows[$i]{tags} = []; # get_tags_for_thread($rows[$i]{id});
		}

	return \@rows;
	}

sub throttle_threads
	{
	my ($args) = @_;
	my $keyword = $args->{keyword} || undef;
	my $tag = $args->{tag} || undef;
	my $limit = $args->{limit} || undef;
	my $newest = $args->{newest} || undef;
	my $oldest = $args->{oldest} || undef;
#	my ($keyword, $limit, $newest, $oldest) = @_;
	my @rows;
	my $query;
	my $where = "WHERE";
	my @keys = qw(id title username keyword date size private allowed flagged color viewed revision);
	my $rows = 0;

	$query = "SELECT threads.id,title,threads.username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads";

	if (defined($tag))
		{
		$tag = $dbh->quote($tag);
		$query .= ",threadstags,tags WHERE threads.id=threadstags.threadid AND threadstags.tagid=tags.id AND tags.tag=$tag";

		}
	elsif (defined($keyword) && $keyword ne "all" && $keyword != -1)
		{
		if ($keyword eq "unsorted")
			{ $query .= " WHERE ISNULL(keyword)"; }
		else
			{
			$keyword = $dbh->quote($keyword);
			$query .= " WHERE keyword=$keyword";
			}
		$where = "AND";
		}

	if (defined($newest) && $newest ne "now")
		{
		$query .= " $where lastmodified < $newest";
		$where = "AND";
		}
	if (defined($oldest))
		{
		$query .= " $where lastmodified > $oldest";
		$where = "AND";
		}
	$query .= " ORDER BY lastmodified DESC";
	if ($limit)
		{ $query .= " LIMIT $limit"; }

	print "$query<br>\n" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No posts!\n" if ($DEBUG);
		return -1;
		}

	for (my $i = 0; $i < @rows; $i++)
		{
		$rows[$i]{comments} = count_comments($rows[$i]{id});
		$rows[$i]{files} = count_files($rows[$i]{id});
		$rows[$i]{tags} = []; # get_tags_for_thread($rows[$i]{id});
		}

	return \@rows;
	}

sub get_threads_by_user
	{
	my ($uname) = @_;
	my @rows;
	my $query;
	my @keys = qw(id title username keyword date size private allowed flagged color viewed revision);
	my $rows = 0;

	$uname = $dbh->quote($uname);
	$query = "SELECT id,title,username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads WHERE username=$uname";

	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No posts!\n" if ($DEBUG);
		return -1;
		}

	for (my $i = 0; $i < @rows; $i++)
		{
		$rows[$i]{comments} = count_comments($rows[$i]{id});
		$rows[$i]{files} = count_files($rows[$i]{id});
		$rows[$i]{tags} = []; # get_tags_for_thread($rows[$i]{id});
		}

	return \@rows;
	}

sub get_threads_by_tag
	{
	my ($tag) = @_;
	my @rows;
	my $query;
	my @keys = qw(id title username keyword date size private allowed flagged color viewed revision);
	my $rows = 0;

	$tag = $dbh->quote($tag);
	$query = "SELECT threads.id,title,threads.username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads,threadstags,tags WHERE threads.id = threadstags.threadid AND threadstags.tagid = tags.id AND tags.tag = $tag";
	print $query."<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;

		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No posts!\n" if ($DEBUG);
		return -1;
		}

	for (my $i = 0; $i < @rows; $i++)
		{
		$rows[$i]{comments} = count_comments($rows[$i]{id});
		$rows[$i]{files} = count_files($rows[$i]{id});
		$rows[$i]{tags} = []; # get_tags_for_thread($rows[$i]{id});
		}

	return \@rows;
	}

sub get_threads_by_keyword
	{
	my ($keyword) = @_;
	my @rows;
	my $query;
	my @keys = qw(id title username keyword date size private allowed flagged color viewed revision);
	my $rows = 0;

	$keyword = $dbh->quote($keyword);
	$query = "SELECT id,title,username,keyword,lastmodified,size,private,allowed,flagged,color,viewed,revision FROM threads WHERE keyword=$keyword";

	print $query."<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No posts!\n" if ($DEBUG);
		return -1;
		}

	for (my $i = 0; $i < @rows; $i++)
		{
		$rows[$i]{comments} = count_comments($rows[$i]{id});
		$rows[$i]{files} = count_files($rows[$i]{id});
		$rows[$i]{tags} = []; # get_tags_for_thread($rows[$i]{id});
		}

	return \@rows;
	}

sub update_flagged
	{
	my ($id, $state) = @_;

	$id = $dbh->quote($id);
	$state = $dbh->quote($state);
	my $query = "UPDATE threads SET flagged=$state WHERE id=$id";
	print $query."<br>" if ($DEBUG);
	my $rows = $dbh->do($query);

	if ($rows == 0)
		{ error("failed to switch post $thread!"); }

	return 1;
	}

sub update_thread_title
	{
	my ($id, $state) = @_;

	$id = $dbh->quote($id);
	$state = $dbh->quote($state);
	my $query = "UPDATE threads SET title=$state WHERE id=$id";
	print $query."<br>" if ($DEBUG);
	my $rows = $dbh->do($query);

	if ($rows == 0)
		{ error("failed to switch post $thread!"); }

	return 1;
	}

sub update_thread_tag
	{
	my ($id, $state) = @_;

	$id = $dbh->quote($id);
	}

sub update_thread_keyword
	{
	my ($id, $state) = @_;

	$id = $dbh->quote($id);
	if ($state ne "NULL")
		{ $state = $dbh->quote($state); }
	my $query = "UPDATE threads SET keyword=$state WHERE id=$id";
	print $query."<br>" if ($DEBUG);
	my $rows = $dbh->do($query);

	if ($rows == 0)
		{ error("failed to switch post $thread!"); }

	return 1;
	}


#########################################
# KEYWORDS
#########################################

sub get_tags
	{
	my @rows;
	my @keys = qw(id tag createdate owner ops public display);
	my %finalhash;
	my $query = "SELECT id,tag,createdate,owner,ops,public,display FROM tags";
	print $query."<br>" if ($DEBUG);
	$sth = $dbh->prepare( $query );
	$sth->execute();
	while ( my (@row) = $sth->fetchrow_array() )
		{
		my %temphash;
		for ( my $i = 0; $i < @row; $i++ )
			{ $temphash{$keys[$i]} = $row[$i]; }
		$finalhash{$temphash{"tag"}} = \%temphash;
		$rows++;
		}
	$sth->finish();
	if ( $rows == 0 )
		{
#		print "No tags!\n" if ($DEBUG);
		return -1;
		}
	return \%finalhash;
	}

sub get_keywords
	{
	my @rows;
	my $query;
	my @keys = qw(id keyword threads owner ops public agglutinate color);
	my $rows = 0;
	my %finalhash;

	$query = "SELECT id,keyword,threads,owner,ops,public,agglutinate,color FROM keywords";

	print $query."<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
			{
			$temphash{$keys[$i]} = $row[$i];
			}
		$finalhash{$temphash{"keyword"}} = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No keywords!\n" if ($DEBUG);
		return -1;
		}

	return \%finalhash;
	}

sub get_keyword
	{
	my ($keyword) = @_;
	my @rows;
	my $query;
	my @keys = qw(id keyword threads owner ops public agglutinate color);
	my $rows = 0;
	my %finalhash;

	$keyword = $dbh->quote($keyword);
	$query = "SELECT id,keyword,threads,owner,ops,public,agglutinate,color FROM keywords WHERE keyword=$keyword";

	print $query."<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	if ((@row) = $sth->fetchrow_array())
		{
		for (my $i = 0; $i < @row; $i++)
			{
			$finalhash{$keys[$i]} = $row[$i];
			}
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No keywords!\n" if ($DEBUG);
		return -1;
		}

	return \%finalhash;
	}

sub add_keyword
	{
	my ($newk) = @_;
	my @keys = qw(keyword threads owner ops public agglutinate color);
	my @qkeys;
	my %q;
	my $i = 0;

	$q{createdate} = $dbh->quote(time);

	foreach my $key (@keys)
		{
		if (exists $newk->{$key})
			{
			$q{$key} = $dbh->quote($newk->{$key});
			}
		}

	@qkeys = keys %q;

	$query = "INSERT keywords SET ";
	foreach my $k (@qkeys)
		{
		$query .= $k."=".$q{$k};
		if (++$i != @qkeys)
			{ $query .= ","; }
		}

	print $query."<br>" if ($DEBUG);
	my $rows = $dbh->do($query);

	if ($rows == 0)
		{ error("failed to add keyword $newk->{keyword}!"); }

	return 1;
	}

sub update_keyword
	{
	my ($keyword, $newk) = @_;
	my @keys = qw(threads ops public agglutinate color);
	my @qkeys;
	my %q;
	my $i = 0;

	my $keyword = $dbh->quote($keyword);

	foreach my $key (@keys)
		{
		if (defined($newk->{$key}))
			{
			$q{$key} = $dbh->quote($newk->{$key});
			}
		}

	@qkeys = keys %q;

	$query = "UPDATE keywords SET ";
	foreach my $k (@qkeys)
		{
		$query .= $k."=".$q{$k};
		if (++$i != @qkeys)
			{ $query .= ","; }
		}
	$query .= " WHERE keyword=$keyword";

	print $query."<br>" if ($DEBUG);
	my $rows = $dbh->do($query);

	if ($rows == 0)
		{ error("failed to update keyword $keyword!"); }

	return 1;
	}

sub update_keyword_color
	{
	my ($key, $disp) = @_;
	$key = $dbh->quote($key);
	$disp = $dbh->quote($disp);
	$query = "UPDATE keywords SET color=$disp WHERE keyword=$key";
	$dbh->do($query);
	}

#########################################
# PRIVACY WHITELISTING
#########################################

sub switch_thread_privacy
	{
	my ($id, $state) = @_;

	$id = $dbh->quote($id);
	$state = $dbh->quote($state);
	my $query = "UPDATE threads SET private=$state WHERE id=$id";
	my $rows = $dbh->do($query);

	if ($rows == 0)
		{ error("failed to switch post $thread!"); }

	switch_file_privacy($id, $state, 1);

	return 1;
	}

sub switch_file_privacy
	{
	my ($id, $state, $q) = @_;

	unless (defined($q))
		{
		$id = $dbh->quote($id);
		$state = $dbh->quote($state);
		}

	my $query = "UPDATE files SET private=$state WHERE thread=$id";
	my $rows = $dbh->do($query);

	return 1;
	}

sub switch_whitelist
	{
	my ($id, $state) = @_;

	$id = $dbh->quote($id);
	$state = $dbh->quote($state);
	my $query = "UPDATE threads SET allowed=$state WHERE id=$id";
	my $rows = $dbh->do($query);

	if ($rows == undef)
		{ error("failed to switch post $thread!"); }

	return 1;
	}


#########################################
# ET CETERA

sub commit_murder
	{
	my ($uname) = @_;
	$uname = $dbh->quote($uname);
	$dbh->do("UPDATE users SET ulevel=-2 WHERE username=$uname");
	}

sub commit_blessing
	{
	my ($uname) = @_;
	my $lev;
	$uname = $dbh->quote($uname);
	$sth = $dbh->prepare("SELECT ulevel FROM users WHERE username=$uname");
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{ $lev = $row[0]; }
	$sth->finish();

	$lev++;
	$lev = $dbh->quote($lev);
	$dbh->do("UPDATE users SET ulevel=$lev WHERE username=$uname");
	}

sub delete_thread
	{
	my ($id) = @_;
	my $rows;
	$id = $dbh->quote($id);
	$dbh->do("DELETE FROM files WHERE thread=$id");
	$dbh->do("DELETE FROM comments WHERE thread=$id");
	$dbh->do("DELETE FROM threads WHERE id=$id");
	system("$RM_PATH", "-rf", "$data_path/$id");
	}

sub get_participation
	{
	my ($id) = @_;
	$id = $dbh->quote($id);
	my %temphash;
	my $i = 0;

	$sth = $dbh->prepare("SELECT username FROM files WHERE thread=$id");
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{ $temphash{$row[0]} = 1; }
	$sth->finish();

	$sth = $dbh->prepare("SELECT username FROM comments WHERE thread=$id");
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{ $temphash{$row[0]} = 1; }
	$sth->finish();

	foreach (keys %temphash)
		{ $i++; }

	return $i;
	}

{
my $usermax = 0; # cache
my %rands;
sub get_random_user
	{
	my $selected = -1;
	my $randy = 1;

	unless ($usermax)
		{
		my @keys = qw[$usermax];
		my $query = "SELECT MAX(id) FROM users";
		print $query."<br>" if ($DEBUG);
		$sth = $dbh->prepare($query);
		$sth->execute();
		($usermax) = $sth->fetchrow_array();
		$sth->finish ();
		}

	do
		{ $randy = (int rand $usermax) + 1; }
	while ( exists($rands{$randy}) );
	$rands{$randy} += 1;
	$selected = get_username($randy);

	print "Random user: $selected<br>" if ($DEBUG);

	return $selected;
	}
}

sub get_random_flagged_image
	{
	my @rows;
	my $rows = 0;
	my $selected = -1;
	my @keys = qw[thread filename];
	my $query = "SELECT threads.id,files.filename FROM threads,files WHERE threads.flagged=files.id AND threads.private=0";
	print $query."<br>" if ($DEBUG);
	$sth = $dbh->prepare($query);
	$sth->execute();
	while (my (@row) = $sth->fetchrow_array())
		{
		my %temphash;
		for (my $i = 0; $i < @row; $i++)
{ $temphash{$keys[$i]} = $row[$i]; }
		$rows[$rows] = \%temphash;
		$rows++;
		}
	$sth->finish();

	if ($rows == 0)
		{
		print "No flagged images!<br>\n" if ($DEBUG);
		return -1;
		}

	$selected = $rows[(int rand @rows)];
	print "Random flagged image: $selected->{thread}/$selected->{filename}<br>" if ($DEBUG);

	return $selected;
	}
# 

### given an id:
### my (@fields) = get_row_from_id("threads", $id);
### ("author, startdate, lastmodified, flagged_file_id")

### my ($ = get_rows_from_pid("files", $pid);
### select %(id, path, filename, author, date, size)
###     from files where pid = $pid
### foreach $aref (@$arrayref) { my $id = $aref[0]; %q{$id}=($aref[1..$#$aref]); }

### get_rows_from_pid("comments", $pid);
### select %(id, author, date, comment)
###     from comments where pid = $pid

1;