package Cyber::Database;
use strict;
use warnings;

use DBIx::Abstract;
use Data::Dumper;

#Constructor
sub new {
	my $class = shift;
	
	my $obj = {};
	
	unless ( $::dbi_handles->{'main_dbh'}->connected ) {
		$::dbi_handles->{'main_dbh'}->ensure_connection();
	}
	$obj->{'main_dbh'} = $::dbi_handles->{'main_dbh'};
	
	bless $obj, $class;
	
	return $obj;
}

# select from the database
# expects array of key => value pairs
# select - columns to select
# table - table(s) to select from (optional)
# where - where clause
# group - group by clause
# order - order by clause
# join - join clause(s)
# hash (flag) - select as array ref of hashrefs
sub get
{
	my $class = shift;
	
	my %args = ref($_[0]) ? %{$_[0]} : @_;
	my $param = {};
	
	if ( defined $args{'select'} ) {
		$param->{'fields'} = $args{'select'};
	}
	else {
		$param->{'fields'} = '*';
	}
	
	if (defined $args{'table'} ) {
		if ( ref($args{'table'}) && $args{'table'} =~ /ARRAY/ ) {
			$param->{'table'} = join(',', @{$args{'table'}});
		}
		else {
			$param->{'table'} = $args{'table'};
		}
	}
	else {
		$param->{'table'} = $class->{'table'};
	}
	
	if ( defined $args{'where'} ) {
		$param->{'where'} = $args{'where'};
	}
	
	if ( defined $args{'order'} ) {
		$param->{'order'} = $args{'order'};
	}
	
	if ( defined $args{'group'} ) {
		$param->{'group'} = $args{'group'};
	}
	
	if ( defined $args{'join'} ) {
		$param->{'join'} = $args{'join'};
	}
	
	if ( defined $args{'limit'} ) {
		$param->{'LIMIT'} = $args{'limit'};
	}

	my $db = $class->{'main_dbh'};
	
	#$db->ensure_connection();
	my $sth = $db->select($param);
	
	if ( $args{'hash'} ) {
		my $row = undef;
		my @result = ();
		
		while ($row = $sth->fetchrow_hashref()) {
			push @result, $row;
		}
		
		if ( $args{'result'} ) {
			return $result[0];
		}
		return \@result;
	}
	else {
		my $result = $sth->fetchall_arrayref();
		if ( $args{'result'} ) {
			if ( $param->{'fields'} eq '*' ) {
				return $result->[0];
			}
			else {
				return $result->[0]->[0];
			}
		}
		return $result;
	}
}

sub get_record_with_limit {
	my $class = shift;

	my %args = ref($_[0]) ? %{$_[0]} : @_;

	my $query = '';
	if ( defined $args{'select'} ) {
		$query .= "SELECT $args{'select'} ";
	}
	
	if (defined $args{'table'} ) {
		$query .= "FROM $args{'table'} ";
	}
	
	if ( defined $args{'where'} ) {
		my $string = '';
		if ( ref $args{'where'} eq 'HASH' ) {
			foreach ( keys %{$args{'where'}} ) {
				$string .= "$_ = '$args{'where'}->{$_}' AND ";
			}
		}
		elsif ( ref $args{'where'} eq 'ARRAY' ) {
			foreach ( @{$args{'where'}} ) {
				if ( $_ =~ /^AND$/i ) {
					next;
				}
				if ( ref $_ eq 'HASH' ) {
					foreach my $k ( keys %$_ ) {
						$string .= "$k = '$_->{$k}' AND ";
					}
				}
				else {
					$string .= "$_ AND ";
				}
			}
		}

		$string =~ s/ AND $//;
		if ( $string !~ /^\s*$/ ) {
			$query .= "WHERE $string ";
		}
	}
	
	if ( defined $args{'order'} ) {
		$query .= "ORDER BY $args{'order'} ";
	}
	
	if ( defined $args{'group'} ) {
		$query .= "GROUP BY $args{'group'} ";
	}
	
	if ( defined $args{'limit'} ) {
		$query .= "LIMIT $args{'limit'} ";
	}

	my $db = $class->{'main_dbh'};
	
	#$db->ensure_connection();
	my $sth = $db->query($query);
	my $result;
	my $row = undef;
	while ($row = $sth->fetchrow_hashref())
	{
		if ( defined $args{'key'} ) {
			if ( exists $result->{$row->{$args{'key'}}} ) {
				my $temp = $result->{$row->{$args{'key'}}};
				if ( ref $temp ne 'ARRAY' ) {
					$temp = [$temp];
				}
				push @$temp, $row;
				$result->{$row->{$args{'key'}}} = $temp;
			}
			else {
				$result->{$row->{$args{'key'}}} = $row;
			}
		}
		else {
			push @$result, $row;
		}
	}
	return $result;
}

sub get_record_with_key {
	my $class = shift;

	my %args = ref($_[0]) ? %{$_[0]} : @_;

	my $query = '';
	if ( defined $args{'select'} ) {
		$query .= "SELECT $args{'select'} ";
	}
	
	if (defined $args{'table'} ) {
		$query .= "FROM $args{'table'} ";
	}
	
	if ( defined $args{'where'} ) {
		my $string = '';
		if ( ref $args{'where'} eq 'HASH' ) {
			foreach ( keys %{$args{'where'}} ) {
				$string .= "$_ = '$args{'where'}->{$_}' AND ";
			}
		}
		elsif ( ref $args{'where'} eq 'ARRAY' ) {
			foreach ( @{$args{'where'}} ) {
				if ( $_ =~ /^AND$/i ) {
					next;
				}
				if ( ref $_ eq 'HASH' ) {
					foreach my $k ( keys %$_ ) {
						$string .= "$k = '$_->{$k}' AND ";
					}
				}
				else {
					$string .= "$_ AND ";
				}
			}
		}

		$string =~ s/ AND $//;
		if ( $string !~ /^\s*$/ ) {
			$query .= "WHERE $string ";
		}
	}
	
	if ( defined $args{'order'} ) {
		$query .= "ORDER BY $args{'order'} ";
	}
	
	if ( defined $args{'group'} ) {
		$query .= "GROUP BY $args{'group'} ";
	}
	
	if ( defined $args{'limit'} ) {
		$query .= "LIMIT $args{'limit'} ";
	}

	my $db = $class->{'main_dbh'};
	
	#$db->ensure_connection();
	my $sth = $db->query($query);
	my $result;
	my $row = undef;
	while ($row = $sth->fetchrow_hashref())
	{
		if ( defined $args{'key'} ) {
			if ( exists $result->{$row->{$args{'key'}}} ) {
				my $temp = $result->{$row->{$args{'key'}}};
				push @$temp, $row;
				$result->{$row->{$args{'key'}}} = $temp;
			}
			else {
				$result->{$row->{$args{'key'}}} = [ $row ]; 
			}
		}
		else {
			push @$result, $row;
		}

	}
	return $result;
}

sub count {
	my ($obj, @param) = @_;
	my $args = ref($param[0]) ? $param[0] : {@param};
	
	return $obj->get(
		'select' => 'count(id)',
		'where'  => $args,
	)->[0]->[0];
}

# insert, update or replace rows
# expects: array of key => value pairs
# table  - table name (optional)
# insert - hashref to insert
# replace - hashref to replace
# update - hashref to update
# where - where clause (only for update)
sub set {
	my $class = shift;
	my %args  = ref($_[0]) ? %{$_[0]} : @_;
	my $param = {};
	
	if ( defined $args{'table'} ) {
		$param->{'table'} = $args{'table'};
	}
	else {
		$param->{'table'} = $class->{'table'};
	}
	my $db = $class->{'main_dbh'};;
	#$db->ensure_connection();

	if ( defined $args{'insert'} ) {
		$param->{'fields'} = $args{'insert'};
		
		my $result = $db->insert($param);
		
		return $result;
	}
	elsif ( defined $args{'update'} ) {
		$param->{'fields'} = $args{'update'};
		
		if ( defined $args{'where'} ) {
			$param->{'where'} = $args{'where'};
		}
		
		my $result = $db->update($param);
		
		return $result;
	}
	elsif (defined $args{'replace'}) {
		
		$param->{'fields'} = $args{'replace'};
		
		my $result = $db->replace($param);
		
		return $result;
	}
}

# delete one or more table rows
# expects: array of key => value pairs
# table - table name (optional)
# where - where clause for delete
sub delete
{
	my $class = shift;
	my %args  = ref($_[0]) ? %{$_[0]} : @_;
	my $param = {};
	
	if ( defined $args{'table'} ) {
		$param->{'table'} = $args{'table'};
	}
	else {
		$param->{'table'} = $class->{'table'};
	}
	
	if ( defined $args{'where'} ) {
		$param->{'where'} = $args{'where'};
	}
	
	my $db = $class->{'main_dbh'};
	#$db->ensure_connection();
	my $result = $db->delete($param);
	
	return $result;
}

# read a single record as a hashref
# expects: primary key column, row id
sub read
{
	my $class = shift;
	my $table = shift;
	my $pk    = shift;
	my $id    = shift;
	
	my $qry = $class->get(
		'table' => $table,
		'hash' => 1,
		'where' => {$pk => $id},
	);

	if ($#{$qry} > -1) {
		return $qry->[0];
	}
	else {
		return undef;
	}
}

# get the id of the last row inserted
sub last_insert_id
{
	my $class = shift;
	
	my $qry = $class->query('select last_insert_id()');
	
	return $qry->[0]->[0];
}

# pass through to the database object
sub query {
	my $class = shift;
	my $sth = $class->{'main_dbh'}->query(@_);
	my $result = $sth->fetchall_arrayref();
	return $result;
}

# shortcut to just insert a row and return the new row id
# expects: list of key => value pairs, or hashref, containing row data
sub add {
	my $class = shift;
	my $table = shift;
	my %inp   = ref($_[0]) ? %{$_[0]} : @_;
	
	$class->set(
		'table'  => 
		'insert' => \%inp
	);
	
	return $class->last_insert_id();
}

# get a Cyber::Table object for a table;
# expects: table name
sub table
{
	my $obj = shift;
	my $name = shift;
	return new Cyber::Table(
		'database' => $obj,
		'table' => $name,
	);
}

1;