#/usr/bin/perl
use strict;
use warnings;
use utf8;
use Encode;
use Perl6::Say;
use Data::Dumper;
sub p ($) { warn Dumper shift }
 
use SQL::Parser;
 
my $parser = SQL::Parser->new;
 
#p $parser->parse(q{SELECT * FROM foo});
#p $parser->structure;
#
#p $parser->parse(q{SELECT * FROM foo ORDER BY created DESC});
#p $parser->structure;
#
#p $parser->parse(q{SELECT * FROM foo WHERE name IN (?) ORDER BY created DESC});
#my $structure = $parser->structure;
 
p $parser->parse(q{SELECT * FROM foo WHERE name IN (?) AND id is not null AND created > 2 ORDER BY created DESC, id LIMIT 2,10});
my $structure = $parser->structure;
 
#p $parser->parse(q{SELECT *, created as test_foo FROM foo WHERE name IN (?) ORDER BY created DESC LIMIT 0,10});
#my $structure = $parser->structure;
 
#p $parser->parse(q{INSERT INTO blog (a, b, c) VALUES (?, ?, ?)});
#p $parser->structure;
 
use SQL::Abstract::Limit;
 
p $structure;
 
my $method = lc $structure->{command};
 
my $sql = SQL::Abstract::Limit->new( limit_dialect => "LimitOffset" );
 
my $tables  = $structure->{org_table_names};
my $columns = $structure->{column_names};
my $where   = [];
my $order   = [];
my $limit   = $structure->{limit_clause}->{limit};
my $offset  = $structure->{limit_clause}->{offset};
 
for my $sort (@{ $structure->{sort_spec_list} }) {
	my $name = (keys %$sort)[0];
	my $dir  = $sort->{$name} || "asc";
	push @$order, {
		"-$dir" => lc $name
	};
}
 
sub construct_where {
	my ($structure) = @_;
 
	if (lc $structure->{op} eq "is") {
		$structure->{op} = $structure->{neg} ? "!=" : "=";
	}
 
	my $op = ($structure->{op} =~ /^\w+$/) ?
		sprintf("-%s%s", $structure->{neg} ? "not_" : "", lc $structure->{op}):
		$structure->{op};
 
	if ($op eq "-and" || $op eq "-or") {
		+{
			$op => [
				construct_where($structure->{arg1}),
				construct_where($structure->{arg2}),
			]
		}
	} else {
		+{
			lc $structure->{arg1}->{value} => {
				$op,
				$structure->{arg2}->{value}
			}
		}
	}
}
 
$where = construct_where($structure->{where_clause});
p $where;
 
my ($stmt, @bind) = $sql->$method( $tables, $columns, $where, $order, $limit, $offset );
p [$stmt, @bind];