use utf8;
package ORI::Schema::Result::GetOrgs;

use strict;
use warnings;
use parent qw(DBIx::Class::Core);

__PACKAGE__->table_class("DBIx::Class::ResultSource::View");
__PACKAGE__->table("getorgs");
__PACKAGE__->add_columns(
  "id" =>
  {
    data_type         => "integer",
    is_auto_increment => 1,
    is_nullable       => 0,
    sequence          => "org_id_seq",
  },
  "lat" =>
  { data_type => "real", is_nullable => 1 },
  "long" =>
  { data_type => "real", is_nullable => 1 },
  "city" =>
  { data_type => "varchar", is_nullable => 1, size => 256 },
  "country" =>
  { data_type => "varchar", is_nullable => 1, size => 256 },
  "name_urls" =>  # Actually an array of arrays
  { dat_type => "varchar[]", is_nullable => 1}

);
# do not attempt to deploy() this view
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition( &create_org_select() );

#### use a sub to return a string that is the SQL statement
sub create_org_select {

  my $statement;

  $statement = "SELECT
   o.id	
 , o.lat
 , o.long
 , o.city
 , o.country
/*
  below used function array_to_array_agg() collects all attributes in one array
  N.B. Attributes must be cast to varchar type beforehand because sql arrays can't contain mixed data type
*/
 , array_to_array_agg(
     ARRAY[[
       n.id::varchar,        --name.id
       n.name::varchar,      --name of org 
       n.acronym::varchar,   --acronym of org 
       n.npref::varchar,     --is org's prefered name?
       n.pri::varchar,       --is org's primary name?
       u.url::varchar,       --an array of properties from urls table
       p.parent_id::varchar, --an array of parent_ids
       p.child_id::varchar   --an array of child_ids
     ]]
   ) AS name_urls
FROM org o

-- link records in org table with matching records in name table
LEFT JOIN name n
  ON (o.id = n.table_id)

-- link records in org table with matching records in virtual table u which is transformed version of urls table
LEFT JOIN (
  SELECT
      o.id
    , u.tbl
    , array_to_array_agg(
        ARRAY[[
            u.id::varchar
          , u.url::varchar
          , u.pri::varchar
          , u.last_check_good::varchar
          , u.date_checked::varchar
        ]]
      ) AS url  --aggregate url properties per org.id
  FROM urls u
  LEFT JOIN org o
    ON (o.id = u.table_id)

    GROUP BY o.id, u.tbl 			--force aggregation per org.id and tbl type
) u
  ON (o.id = u.id)

-- link records in org table with matching records in virtual table p which is a collection of parent and child ids per org.id  
LEFT JOIN (
        SELECT
		  o.id
		, array_accum(DISTINCT p.parent_id) AS parent_id	--an array of parent ids for org.id
		, array_accum(DISTINCT p.child_id) AS child_id		--an array of child ids for org.id
        FROM org o
        LEFT JOIN parents p
		ON (o.id = p.child_id OR o.id = p.child_id)
        GROUP BY o.id 				--force aggregation per org.id
    ) p
	ON (o.id = p.id)	
    
WHERE
	    u.tbl = 'org'
	AND n.tbl = 'org'
 GROUP BY o.id, o.lat, o.long
 ORDER BY o.id";
  return $statement;
};

1;