package HBCU::Database::Marketplace;

use strict;

use HBCU::Database;

our @ISA = qw( HBCU::Database );

our $VERSION = '1.0.1';

################################################################################
sub new {
  return (+shift)->SUPER::new(
                              Database => 'marketplace',
			      @_
                             );
}

################################################################################
sub getCategoryItemCount {
  my $self = shift;
  my $sql = q(
SELECT count(*) As num
  FROM item
 WHERE deleted = 0
   AND activated = 1
   AND (categories = ?
    OR categories like '%:?:%'
    OR categories like '%:?'
    OR categories like '?:%')
);

  return $self->execute($sql, @_)->{num} || 0;
}

################################################################################
sub getFeaturedItems {
  my $self = shift;
  my @recs = map {
    my $picture = $self->getMainPicture($_->{id});

    $_->{path} = $picture->{path};
    $_->{price} = sprintf("\$%.2f", $_->{price});
    $_->{hbcu} ||= 'All HBCUs';
    $_;
  } $self->getItems(WHERE => {featured => 1});

  return \@recs;
  my @records = ();
  for (my $i = 0; $i <= $#recs; $i++) {
    my @array = ();
    push @array, $recs[$i++];

    unless ($i > $#recs) {
      push @array, $recs[$i++];
    }

    unless ($i > $#recs) {
      push @array, $recs[$i++];
    }

    unless ($i > $#recs) {
      push @array, $recs[$i];
    }

    push @records, {columns => \@array};
  }

  return @records;
}

################################################################################
sub getFeaturedSellers {
  my $self = shift;
  my $sql = q(
SELECT s.id As id
      ,s.name As name
      ,concat(substring(s.description, 1, 200), '...') As description
      ,count(i.id) As item_count
  FROM seller As s
  LEFT
  JOIN item As i
    ON i.seller = s.id
 WHERE s.store = 1
   AND s.featured = 1
 GROUP
    BY s.id
 ORDER
    BY item_count desc
);

  return $self->execute($sql);
}


################################################################################
sub getItemPurchaseCount {
  my $self = shift;
  my $sql = q(
SELECT count(*) As num
  FROM purchase
 WHERE item = ?
);

  return $self->execute($sql, @_)->{num} || 0;
}

################################################################################
sub getItemReviewAverage {
  my $self = shift;
  my $sql = q(
SELECT round(avg(rating)) As num
  FROM review
 WHERE item = ?
);

  return $self->execute($sql, @_)->{num} || 0;
}

################################################################################
sub getMainPicture {
  my $self = shift;
  my $sql = q(
SELECT *
  FROM picture
 WHERE item = ?
 ORDER
    BY date_created asc
 LIMIT 0, 1
);

  return $self->execute($sql, @_);
}

################################################################################
sub getNumItemsSold {
  my $self = shift;
  my $sql = q(
SELECT count(*) As num
  FROM purchase
 WHERE item = ?
);

  return $self->execute($sql, @_)->{num} || 0;
}

################################################################################
sub getSellerItemCategories {
  my ($self, $seller) = @_;

  my @records = map {
    my @subs = $self->getCategories(WHERE => {parent => $_->{id}}, ORDER => 'name');

    for (my $i = 0; $i < scalar(@subs) - 1; $i++) {
      $subs[$i]->{comma} = 1;
    }
    $_->{subcategories} = \@subs;
    $_->{item_count} = $self->getSellerCategoryItemCount($seller, $_->{id});
    $_;
  } $self->getCategories(WHERE => {parent => 0}, ORDER => 'name');

  return \@records;
}

################################################################################
sub getSellerCategoryItemCount {
  my $self = shift;
  my $sql = q(
SELECT count(*) As num
  FROM item
 WHERE deleted != 1
   AND activated = 1
   AND seller = ?
   AND (categories = ?
    OR categories like '%:?:%'
    OR categories like '%:?'
    OR categories like '?:%')
);

  return $self->execute($sql, @_)->{num} || 0;
}

################################################################################
sub getSellerItems {
  my $self = shift;
  my $sql = q(
SELECT i.id As id
      ,i.title As title
      ,i.hits As hits
      ,i.activated As activated
      ,unix_timestamp(i.date_expires) As date_expires
      ,unix_timestamp( now() ) As crt_date
  FROM item As i
 WHERE i.seller = ?
   AND deleted != 1
);

  return $self->execute($sql, @_);
}

################################################################################
sub getSellerPopularItems {
  my $self = shift;
  my $sql = q(
SELECT i.id As id
      ,i.title As title
      ,i.price As price
  FROM item As i
 WHERE seller = ?
 ORDER
    BY hits desc
 LIMIT 0, 12
);

  return $self->execute($sql, @_);
}

################################################################################
sub getSellerReviewAverage {
  my $self = shift;
  my $sql = q(
SELECT avg(rating) As num
  FROM review
 WHERE seller = ?
);

  return $self->execute($sql, @_)->{num} || 0;
}

################################################################################
sub getUserPurchasedItems {
  my $self = shift;
  my $sql = q(
SELECT *
  FROM purchase
 WHERE buyer = ?
   AND hidden != 1
);

  return $self->execute($sql, @_);
}

################################################################################
sub getUserSavedItems {
  my $self = shift;
  my $sql = q(
SELECT s.item As item
      ,i.date_expires As date
      ,i.id As id
      ,i.title As title
      ,i.price As price
      ,i.seller As seller
      ,unix_timestamp(i.date_expires) As date_expires
      ,unix_timestamp( now() ) As crt_date
  FROM saved_item As s
      ,item As i
 WHERE s.registry_id = ?
   AND s.item = i.id
);

  return $self->execute($sql, @_);
}

################################################################################
sub getUserSavedSearches {
  my $self = shift;
  my $sql = q(
SELECT *
  FROM saved_search
 WHERE registry_id = ?
   AND name is not null
);

  return $self->execute($sql, @_);
}

1;