#!/usr/bin/perl

use strict;

use lib '../../../lib';
use lib 'lib';

use My::Product;

package My::Product;

sub average_price
{
  my($self) = shift;

  my %args;

  if(my $ref = ref $_[0])
  {
    if($ref eq 'HASH')
    {
      %args = (query => [ %{shift(@_)} ], @_);
    }
    elsif(ref $_[0] eq 'ARRAY')
    {
      %args = (query => shift, @_);
    }
  }
  else { %args = @_ }

  my $meta         = $self->meta;
  my $relationship = $self->meta->relationship('prices');
  my $ft_columns   = $relationship->key_columns;
  my $query_args   = $relationship->query_args || [];
  my $mgr_args     = $relationship->manager_args || {};

  my $average;

  # Get query key
  my %key;

  while(my($local_column, $foreign_column) = each(%$ft_columns))
  {
    my $local_method = $meta->column_accessor_method_name($local_column);

    $key{$foreign_column} = $self->$local_method();

    # Comment this out to allow null keys
    unless(defined $key{$foreign_column})
    {
      keys(%$ft_columns); # reset iterator
      $self->error("Could not get average via average_price() - the " .
                   "$local_method attribute is undefined");
      return;
    }
  }

  # Merge query args
  my @query = (%key, @$query_args, @{delete $args{'query'} || []});      

  # Merge the rest of the arguments
  foreach my $param (keys %args)
  {
    if(exists $mgr_args->{$param})
    {
      my $ref = ref $args{$param};

      if($ref eq 'ARRAY')
      {
        unshift(@{$args{$param}}, ref $mgr_args->{$param} ? 
                @{$mgr_args->{$param}} :  $mgr_args->{$param});
      }
      elsif($ref eq 'HASH')
      {
        while(my($k, $v) = each(%{$mgr_args->{$param}}))
        {
          $args{$param}{$k} = $v  unless(exists $args{$param}{$k});
        }
      }
    }
  }

  while(my($k, $v) = each(%$mgr_args))
  {
    $args{$k} = $v  unless(exists $args{$k});
  }

  $args{'object_class'} = $relationship->class;

  my $debug = $Rose::DB::Object::Manager::Debug || $args{'debug'};

  # Make query for average
  eval
  {
    my($sql, $bind) = 
      Rose::DB::Object::Manager->get_objects_sql(
        select => [ \q(AVG(price)) ],
        query => \@query, db => $self->db, %args);

    $debug && warn "$sql (", join(', ', @$bind), ")\n";

    my $sth = $self->db->dbh->prepare($sql);
    $sth->execute(@$bind);

    $average = $sth->fetchrow_array;
  };

  if($@)
  {
    $self->error("Could not average $args{'object_class'} objects - " . 
                 Rose::DB::Object::Manager->error);
    $meta->handle_error($self);
    return wantarray ? () : $average;
  }

  return $average;
}


package main;

# use My::Price;
# use My::Color;
# use My::Vendor;
# $p = My::Product->new(id => 1, name => 'A');
# $p->prices(My::Price->new(product_id => 1, region => 'IS', price => 1.23),
#            My::Price->new(product_id => 1, region => 'DE', price => 4.56));
# 
# $p->colors(My::Color->new(code => 'CC1', name => 'red'),
#            My::Color->new(code => 'CC2', name => 'green'));
# 
# $p->vendor(My::Vendor->new(id => 1, name => 'V1'));
# $p->save;

my $p = My::Product->new(id => 1)->load;

print "AVG(price) = ", $p->average_price, "\n";
print $p->vendor->name, "\n";

print join(', ', map { $_->region . ': ' . $_->price } $p->prices), "\n";
print join(', ', map { $_->name } $p->colors), "\n";

# Rose::DB::Object::Manager->get_objects(
#   object_class => 'My::Product',
#   debug => 1,
#   query =>
#   [
#     name => { like => 'Kite%' },
#     id   => { gt => 15 },
#   ],
#   require_objects => [ 'vendor' ],
#   with_objects    => [ 'colors', 'prices' ],
#   multi_many_ok   => 1,
#   sort_by => 'name');
# 
# 
# Rose::DB::Object::Manager->get_objects(
#   object_class => 'My::Product',
#   debug => 1,
#   query =>
#   [
#     name => { like => 'Kite%' },
#     id   => { gt => 15 },
#   ],
#   require_objects => [ 'vendor' ],
#   with_objects    => [ 'colors' ],
#   sort_by => 'name');
# 
# 
# Rose::DB::Object::Manager->get_objects(
#   object_class => 'My::Product',
#   debug => 1,
#   query =>
#   [
#     name => { like => 'Kite%' },
#     id   => { gt => 15 },
#   ],
#   with_objects => [ 'colors' ],
#   sort_by => 'name');
# 
# 
# Rose::DB::Object::Manager->get_objects(
#   object_class => 'My::Product',
#   debug => 1,
#   query =>
#   [
#     name => { like => 'Kite%' },
#     id   => { gt => 15 },
#   ],
#   require_objects => [ 'vendor' ],
#   with_objects    => [ 'prices' ],
#   sort_by => 'name');
# 
# 
# Rose::DB::Object::Manager->get_objects(
#   object_class => 'My::Product',
#   debug => 1,
#   query =>
#   [
#     name => { like => 'Kite%' },
#     id   => { gt => 15 },
#   ],
#   with_objects => [ 'prices' ],
#   sort_by => 'name');
# 
# 
# Rose::DB::Object::Manager->get_objects(
#   object_class => 'My::Product',
#   debug => 1,
#   query =>
#   [
#     'vendor.region.name' => 'UK',
#     'name' => { like => 'Kite%' },
#     'id'   => { gt => 15 },
#   ],
#   require_objects => [ 'vendor.region' ],
#   with_objects    => [ 'colors', 'prices' ],
#   multi_many_ok   => 1,
#   sort_by => 'name');

__END__

DROP TABLE product_colors CASCADE;
DROP TABLE prices CASCADE;
DROP TABLE products CASCADE;
DROP TABLE colors CASCADE;
DROP TABLE vendors CASCADE;
DROP TABLE regions CASCADE;

CREATE TABLE regions
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255)
);

CREATE TABLE vendors
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255),
  region_id INT REFERENCES regions (id)
);

CREATE TABLE colors
(
  code  CHAR(3) NOT NULL PRIMARY KEY,
  name  VARCHAR(255)
);

CREATE TABLE products
(
  id        SERIAL NOT NULL PRIMARY KEY,
  name      VARCHAR(255),
  vendor_id INT NOT NULL REFERENCES vendors (id)
);

CREATE TABLE prices
(
  price_id    SERIAL NOT NULL PRIMARY KEY,
  product_id  INT NOT NULL REFERENCES products (id),
  region      CHAR(2) NOT NULL DEFAULT 'US',
  price       DECIMAL(10,2) NOT NULL
);

CREATE TABLE product_colors
(
  id           SERIAL NOT NULL PRIMARY KEY,
  product_id   INT NOT NULL REFERENCES products (id),
  color_code   CHAR(3) NOT NULL REFERENCES colors (code)
);

INSERT INTO vendors (id, name) VALUES (1, 'V1');
INSERT INTO vendors (id, name) VALUES (2, 'V2');

INSERT INTO products (id, name, vendor_id) VALUES (1, 'A', 1);
INSERT INTO products (id, name, vendor_id) VALUES (2, 'B', 2);
INSERT INTO products (id, name, vendor_id) VALUES (3, 'C', 1);

INSERT INTO prices (product_id, region, price) VALUES (1, 'US', 1.23);
INSERT INTO prices (product_id, region, price) VALUES (1, 'DE', 4.56);
INSERT INTO prices (product_id, region, price) VALUES (2, 'US', 5.55);
INSERT INTO prices (product_id, region, price) VALUES (3, 'US', 5.78);
INSERT INTO prices (product_id, region, price) VALUES (3, 'US', 9.99);

INSERT INTO colors (code, name) VALUES ('CC1', 'red');
INSERT INTO colors (code, name) VALUES ('CC2', 'green');
INSERT INTO colors (code, name) VALUES ('CC3', 'blue');
INSERT INTO colors (code, name) VALUES ('CC4', 'pink');

INSERT INTO product_colors (product_id, color_code) VALUES (1, 'CC1');
INSERT INTO product_colors (product_id, color_code) VALUES (1, 'CC2');

INSERT INTO product_colors (product_id, color_code) VALUES (2, 'CC4');

INSERT INTO product_colors (product_id, color_code) VALUES (3, 'CC2');
INSERT INTO product_colors (product_id, color_code) VALUES (3, 'CC3');