RWDE::DB::Items

package RWDE::DB::Items;

use strict;
use warnings;

use Error qw(:try);

use RWDE::AbstractFactory;
use RWDE::Exceptions;

use vars qw($VERSION);
$VERSION = sprintf "%d", q$Revision: 508 $ =~ /(\d+)/;

sub fetch {
  my ($self, $params) = @_;

  # dynamically pull in the name of the table where our object is mapped
  my $table = $self->get_static({ value => '_table' });

  my @fieldnames = @{ $self->get_static({ value => '_fieldnames' }) };

  my ($where, $query_params) = $self->generate_where($params);

  my $ordering = $self->generate_ordering($params);

  local ($") = ",";    #";
  my $result = $self->issue_query(
    {
      complete_query => "SELECT @fieldnames FROM $table $where $ordering",
      query_params   => $query_params
    }
  );

  my @items;
  foreach my $row (@{$result}) {
    push(@items, $self->fill_new({ row => $row }));
  }

  return \@items;
}

sub fetch_by_id_set {
  my ($self, $params) = @_;

  $self->check_params({ required => ['id_set'], supplied => $params });

  return
    unless (@{ $$params{id_set} } > 0);

  # Use the supplied identity field
  # or get the default one for the object
  my $id = $$params{'id'};

  $id = $self->get_static({ value => '_id' })
    unless defined $id;

  my @ids = @{ $$params{id_set} };

  local ($") = ",";    #"
  my $query = " $id IN (@ids) ";

  if (defined $$params{query}) {
    $$params{query} = $$params{query} . ' AND ' . $query;
  }
  else {
    $$params{query} = $query;
  }

  #FIXME make sure we have a query_params hash -- is this necessary?
  $$params{query_params} = defined($$params{query_params}) ? $$params{query_params} : undef;

  return $self->fetch($params);
}

sub fetch_single {
  my ($self, $params) = @_;

  my $select = $$params{select}
    or throw RWDE::DevelException({ info => 'No target specified for fetch_single' });

  my $table;
  try {
    $table = $self->get_static({ value => '_table' });
  }
  catch Error {

  };

  my ($where, $query_params) = $self->generate_where($params);

  my $ordering = $self->generate_ordering($params);

  my $complete_query;
  if (defined $table) {
    $complete_query = "SELECT $select FROM $table $where $ordering";
  }
  else {
    $complete_query = "SELECT $select";
  }

  my $result = $self->issue_query({ complete_query => $complete_query, query_params => $query_params, deny_update => 1 });

  return $$result[0][0];
}

sub fetch_ids {
  my ($self, $params) = @_;

  my (@ids, $table, $term);

  #use the supplied id or get the primary key
  my $id = $$params{'id'} ? $$params{'id'} : $self->get_static({ value => '_id' });

  # dynamically pull in the name of the table where our object is mapped
  $table = $self->get_static({ value => '_table' });

  my ($where, $query_params) = $self->generate_where($params);
  my $ordering = $self->generate_ordering($params);

  my $complete_query = "SELECT $id FROM $table $where $ordering";

  my $result = $self->issue_query({ complete_query => $complete_query, query_params => $query_params });

  foreach my $row (@{$result}) {
    push(@ids, shift @{$row});
  }

  return \@ids;
}

sub fetch_join {
  my ($self, $params) = @_;

  $self->check_params({ required => [ 'jointype', 'joinkey' ], supplied => $params });

  my ($fields,     @fieldnames,     $term,    $table,     $jointerm,      @queryfieldnames);
  my ($joinfields, @joinfieldnames, $joinkey, $jointable, $jointable_key, @queryjoinfieldnames);

  my @items;
  my $jointype = $$params{'jointype'};

  # dynamically require the object type we are going to try and use
  $term     = RWDE::AbstractFactory->instantiate({ class => $self });
  $jointerm = RWDE::AbstractFactory->instantiate({ class => $jointype });

  # dynamically pull in the name of the table where our object is mapped
  $table     = $term->{_table};
  $jointable = $jointerm->{_table};

  #typically both items will share the same name to join on
  $joinkey = $$params{joinkey};

  #special exceptions if the 2 items to be joined do not share the same key
  $jointable_key = $$params{jointable_key};

  # dynamically pull in the list of fieldnames we will be dealing with for that table
  @fieldnames     = @{ $term->{_fieldnames} };
  @joinfieldnames = @{ $jointerm->{_fieldnames} };

  @queryfieldnames     = @{ $term->{_fieldnames} };
  @queryjoinfieldnames = @{ $jointerm->{_fieldnames} };

  # expand out the list of potential fieldnames for a database query
  foreach my $f (@queryfieldnames) {
    $f = $table . "." . $f . " AS " . $f;
  }

  foreach my $f (@queryjoinfieldnames) {
    $f = $jointable . "." . $f . " AS " . $f;
  }

  $fields     .= join(',', @queryfieldnames);
  $joinfields .= join(',', @queryjoinfieldnames);

  my ($where, $query_params) = $self->generate_where($params);
  my $ordering = $self->generate_ordering($params);

  my $join_on = (defined($jointable_key)) ? "$table.$joinkey = $jointable.$jointable_key" : "$table.$joinkey = $jointable.$joinkey";
  my $complete_query = "SELECT $fields, $joinfields FROM $table JOIN $jointable ON ($join_on) $where $ordering";

  my $result = $self->issue_query({ complete_query => $complete_query, query_params => $query_params });

  foreach my $row (@{$result}) {
    my $item     = $self->fill_new({ row     => $row });
    my $joinitem = $jointype->fill_new({ row => $row });

    $item->{$jointype} = ($joinitem);
    push(@items, $item);
  }

  return \@items;
}

sub join_count_query {
  my ($self, $params) = @_;

  $self->check_params({ required => [ 'jointype', 'joinkey' ], supplied => $params });

  my ($fields,     @fieldnames,     $term,    $table,     $jointerm,      @queryfieldnames);
  my ($joinfields, @joinfieldnames, $joinkey, $jointable, $jointable_key, @queryjoinfieldnames);

  my @items;

  # dynamically require the object type we are going to try and use
  $term     = RWDE::AbstractFactory->instantiate({ class => $self });
  $jointerm = RWDE::AbstractFactory->instantiate({ class => $$params{'jointype'} });

  # dynamically pull in the name of the table where our object is mapped
  $table     = $term->{_table};
  $jointable = $jointerm->{_table};

  $joinkey = $$params{joinkey};

  #special exceptions if the 2 items to be joined do not share the same key
  $jointable_key = $$params{jointable_key};

  my ($where, $query_params) = $self->generate_where($params);
  my $join_on        = (defined($jointable_key)) ? "$table.$joinkey = $jointable.$jointable_key" : "$table.$joinkey = $jointable.$joinkey";
  my $complete_query = "SELECT COUNT(*) FROM $table JOIN $jointable ON ($join_on) $where";
  my $result         = $self->issue_query({ complete_query => $complete_query, query_params => $query_params });

  return $$result[0][0];
}

sub count_query {
  my ($self, $params) = @_;

  if (!defined($$params{count})) {
   $$params{select} = 'COUNT(*)';
  }
  else {
   $$params{select} = 'COUNT('.$$params{count}.')';
  }

  # make sure that we don't accidentally pass in anything which would break the count query
  return $self->fetch_single({ query => $$params{query}, query_params => $$params{query_params}, select => $$params{select} });
}
                   
# Allow Record classes to optimize lookups: i.e. split tables
sub optimize_query{}

sub issue_query {
  my ($self, $params) = @_;

  $self->check_params({ required => ['complete_query'], supplied => $params });

  my $dbh = $self->get_dbh();

  my $query = $$params{complete_query};

  #if we are in a transaction (passively invoked by get_dbh) - then insert a "FOR UPDATE" for this call
  if (!$$params{deny_update} && (RWDE::DB::DbRegistry->has_transaction({ db => $self->get_db() }))) {
    $query .= ' FOR UPDATE';
  }                             

  $self->optimize_query({ query => \$query, query_params => $$params{query_params} });

  my $sth = $dbh->prepare($query)
    or throw RWDE::DevelException({ info => 'Failure to prepare query.' });

  if (defined($$params{query_params}) and scalar @{ $$params{query_params} } > 0) {
    $sth->execute(@{ $$params{query_params} })
      or throw RWDE::DevelException({ info => 'Failure to execute query:' . $dbh->errstr() });
  }
  else {
    $sth->execute()
      or throw RWDE::DevelException({ info => 'Failure to execute query:' . $dbh->errstr() });
  }

  return $sth->fetchall_arrayref;
}

sub fetch_count {
  my ($self, $params) = @_;

  my $terms_ref = $self->fetch($params);
  my $count     = $self->count_query($params);

  return ($terms_ref, $count);
}

sub fetch_join_count {
  my ($self, $params) = @_;

  my $result       = $self->fetch_join($params);
  my $result_count = $self->join_count_query($params);

  return ($result, $result_count);
}

# Return objects in a hash structure indexed by term's id
sub fetch_hash {
  my ($self, $params) = @_;

  my $term_hash = {};

  foreach my $term (@{ $self->fetch($params) }) {
    $$term_hash{ $term->get_id } = $term;
  }

  return $term_hash;
}

sub generate_ordering {
  my ($self, $params) = @_;

  my $ordering = '';

  # Handle paging if necessary
  if ($$params{order}) {
    $ordering .= " ORDER BY $$params{order} ";

    #ascending is the default unless this is declared
    if (defined($$params{ordering})) {
      $ordering .= $$params{ordering};
    }
  }

  if (defined $$params{startidx}) {
    my $startidx = $$params{startidx};
    $startidx = 0 if ($startidx < 0);
    $ordering .= " OFFSET $startidx ";
  }

  if (defined $$params{maxreturn}) {
    my $maxreturn = $$params{maxreturn};
    $maxreturn += 0;
    $maxreturn = 50 if ($maxreturn > 200 or $maxreturn < 1);
    $ordering .= " LIMIT $maxreturn ";
  }

  return $ordering;
}

sub generate_where {
  my ($self, $params) = @_;

  my $where = '';
  my @query_params;

  #either we have an SQL query passed with params
  if (defined($$params{query}) || defined $$params{query_params}) {
    $where = $$params{query};

    if (defined $$params{query_params}) {
      @query_params = @{ $$params{query_params} };
    }

  }

  #or we have a bunch of search_fields we should sift through
  #and generate a query
  elsif (defined($$params{search_fields})) {
    my @queryparts;    # parts for the "WHERE clause"

    foreach my $field (@{ $$params{search_fields} }) {
      next
        if not defined $$params{$field};

      if (ref $$params{$field} eq 'ARRAY') {

        #TODO add placeholders instead of explicit quoting
        push @queryparts, "$field IN ('" . join("','", @{ $$params{$field} }) . "')";
      }
      elsif ($field =~ m/^(.*)\_not/o) {
        push(@queryparts,   "$1 <> ?");
        push(@query_params, $$params{$field});
      }
      elsif ($field =~ m/^(.*)\_start/o) {
        push(@queryparts,   "$1 >= ?");
        push(@query_params, $$params{$field});
      }
      elsif ($field =~ m/^(.*)\_end/o) {
        push(@queryparts,   "$1 <= ?");
        push(@query_params, $$params{$field});
      }
      elsif ($$params{$field} =~ m/%/) {
        push(@queryparts,   "$field LIKE ?");
        push(@query_params, $$params{$field});
      }
      else {
        push(@queryparts,   "$field = ?");
        push(@query_params, $$params{$field});
      }
    }    #end foreach field

    if (scalar @queryparts > 0) {
      $where = join ' AND ', @queryparts;
    }
  }

  if (defined $where and $where ne '') {
    $where = " WHERE $where";
  }

  return ($where, \@query_params);
}

1;