package SQL::Builder::Select;
use strict;
use warnings;
use Exporter;
use Carp;
use vars qw / $VERSION @ISA /;

$VERSION = 1.00;
@ISA = qw/ Exporter /;


sub new {
  my $class = shift;
  my $self = {};
  
  bless ($self, $class);
  return $self;
}


#####################################################################


sub column {
	my $class = shift;
	
	push @{$class->{column}}, @_;
	return scalar( @{$class->{column}} );
}


sub table {
	my $class = shift;
	
	push @{$class->{table}}, @_;
	return scalar( @{$class->{table}} );
}


#####################################################################


sub where {
	my $class = shift;
	
	push @{$class->{where}}, @_;
	return scalar( @{$class->{where}} );
}


sub where_and {
	my $class = shift;
	
	push @{$class->{where_and}}, @_;
	return scalar( @{$class->{where_and}} );
}


sub where_or {
	my $class = shift;
	
	push @{$class->{where_or}}, @_;
	return scalar( @{$class->{where_or}} );
}


sub where_eq_and {
	my $class = shift;
	
	push @{$class->{where_and}}, map { $_ . ' = ?' } @_;
	return scalar( @{$class->{where_and}} );
}


sub where_ne_and {
	my $class = shift;
	
	push @{$class->{where_and}}, map { $_ . ' != ?' } @_;
	return scalar( @{$class->{where_and}} );
}


sub where_eq_or {
	my $class = shift;
	
	push @{$class->{where_or}}, map { $_ . ' = ?' } @_;
	return scalar( @{$class->{where_or}} );
}


sub where_ne_or {
	my $class = shift;
	
	push @{$class->{where_or}}, map { $_ . ' != ?' } @_;
	return scalar( @{$class->{where_or}} );
}


sub where_logic {
	my $class = shift;
	
	$class->{where_logic} = shift;
}


#####################################################################


sub order_by {
	my $class = shift;
	
	push @{$class->{order_by}}, @_;
	return scalar( @{$class->{order_by}} );
}


#####################################################################


sub limit_offset {
	my $class = shift;
	
	$class->{limit_offset} = shift;
}


sub limit_value {
	my $class = shift;
	
	$class->{limit_value} = shift;
}


#####################################################################


sub return {
	my $class = shift;
	my $sql;
	
	unless (@{$class->{column}}) {
		croak "column() not defined in SQL::Builder::Select::return";
	}
	
	unless (@{$class->{table}}) {
		croak "table() not defined in SQL::Builder::Select::return";
	}
	
	
	$sql = 'SELECT '
				. join (', ', @{$class->{column}})
				. ' FROM '
				. join (', ', @{$class->{table}});
	
	push @{$class->{error}}, 'sql column table', $sql; ### DEBUG
	
	
	my $s = $class->return_where();
	
	if (defined $s) {
		$sql .= ' WHERE '
					. $s;
		
		push @{$class->{error}}, 'return_where output', $s; ### DEBUG
	}
	
	
	if (defined $class->{order_by}) {
		$sql .= ' ORDER BY '
					. join (', ', @{$class->{order_by}});
		
		push @{$class->{error}}, 'sql order by', $sql; ### DEBUG
	}
	
	
	if (defined $class->{limit_value}) {
		$sql .= ' LIMIT ';
		
		if (defined $class->{limit_offset}) {
			$sql .= $class->{limit_offset}
						. ', ';
		}
		
		$sql .= $class->{limit_value};
		
		push @{$class->{error}}, 'sql limit', $sql; ### DEBUG
	}
	
	
	if (defined $class->{debug}) {
		if (defined $ENV{SERVER_NAME}) {
			print "Content-type: text/plain\n\n";
			$/ = '<br>';
		}
		
		if (defined ${$class->{error}}[0]) {
			print map { $_, $/ } @{$class->{error}};
			print $/;
		}
		print $sql;
		exit;
	}
	
	return $sql;
}


#####################################################################


sub return_where {
	my $class = shift;		
			
	if (defined $class->{where_logic}) {
		my $logic = $class->{where_logic};
		
		if ($logic =~ /^and$/i) {
			$class->{where_logic} = 'AND';
			return $class->_where_and_or();
		}
		elsif ($logic =~ /^or$/i) {
			$class->{where_logic} = 'OR';
			return $class->_where_and_or();
		}
		else {
			my ($decision, @func);
			
			if ($logic =~ /and/i) {
				$decision = 'AND';
				@func = split( /and/i, $logic);
			}
			elsif ($logic =~ /or/i) {
				$decision = 'OR';
				@func = split( /or/i, $logic);
			}
			else {
				return;
			}
			
			unless (defined $func[1]) {
				return;
			}
			
			
			for ($func[0], $func[1]) {
				if ($_ =~ /w/i) {
					push @{$class->{sql}}, $class->_join_values( 'where', $decision );
				}
				elsif ($_ =~ /a/i) {
					push @{$class->{sql}}, $class->_join_values( 'where_and', 'AND' );
				}
				elsif ($_ =~ /o/i) {
					push @{$class->{sql}}, $class->_join_values( 'where_or', 'OR' );
				}
				else {
					return;
				}
			}
			
			return $class->_join_values( 'sql', $decision );
		}
	}
	else {
		$class->{where_logic} = 'AND';
		
		return $class->_where_and_or();
	}
	
	return;
}


#####################################################################


sub debug {
	my $class = shift;
	
	if (@_) {
		my $result = shift;
		
		if (defined $result && $result ne '' && $result != 0) {
			$class->{debug} = 1;
		}
		return 1;
	}
	elsif (defined $class->{debug} && $class->{debug} == 1) {
		return 1;
	}
	
	return;
}


#####################################################################
#####################################################################


sub _where_and_or {
	my $class = shift;
	my $logic = $class->{where_logic};
	
	if (defined ${$class->{where}}[0]) {
		
		return $class->_join_values( 'where', $logic );
	}
	elsif (defined ${$class->{where_and}}[0]) {
		
		return $class->_join_values( 'where_and', 'AND' );
	}
	elsif (defined ${$class->{where_or}}[0]) {
		
		return $class->_join_values( 'where_or', 'OR' );
	}
	
	return;
}

sub _join_values {
	my $class = shift;
	my $name 	= shift;
	my $logic = shift if (defined ${$class->{$name}}[0]);
	
	if (defined ${$class->{$name}}[1]) {
		my $s = '('
					. join(") $logic (", @{$class->{$name}})
					. ')';
		
		return $s;
	}
	elsif (defined ${$class->{$name}}[0]) {
		return @{$class->{$name}}[0];
	}
	
	return;
}


1;

##</code><code>##

use SQL::Builder::Select;

$select = new SQL::Builder::Select;

$select->column('col1', 'col2');
$select->table('table');

$select->order_by('col1');

$select->limit_value('10');
$select->limit_offset('10');

print $select->return();

##</code><code>##

SELECT col1, col2 FROM table ORDER BY col1 LIMIT 10, 10

##</code><code>##

use SQL::Builder::Select;

$select = new SQL::Builder::Select;

$select->column('*');
$select->table('table');

$select->where_and('col1 = 1', 'col2 = 1');

print $select->return();

##</code><code>##

SELECT * FROM table WHERE (col1 = 1) AND (col2 = 1)

##</code><code>##

use SQL::Builder::Select;

$select = new SQL::Builder::Select;

$select->where_eq_or('col1', 'col2');

$where = $select->return_where();


$select = new SQL::Builder::Select;

$select->column('*');
$select->table('table');

$select->where( $where );
$select->where_ne_or('col1', 'col2');
$select->where_logic('w AND o');

print $select->return();

##</code><code>##

SELECT * FROM table WHERE ((col1 = ?) OR (col2 = ?)) AND ((col1 != ?) OR (col2 != ?))