--
-- packages/acs-subsite/sql/application_groups-create.sql
--
-- @author oumi@arsdigita.com
-- @creation-date 2000-02-02
-- @cvs-id $Id: application-groups-create.sql,v 1.14 2015/04/27 15:28:17 victorg Exp $
--

------------------------
-- APPLICATION GROUPS --
------------------------

select acs_object_type__create_type (
       'application_group',
       'Application Group',
       'Application Groups',
       'group',
       'application_groups',
       'group_id',
       'application_group',
       'f',
       'group_types',
       'acs_group__name'
);



create table application_groups (
	group_id		integer constraint application_groups_group_id_fk
				references groups (group_id) on delete cascade
				constraint application_groups_group_id_pk
				primary key,
        package_id              integer constraint application_groups_package_id_fk
                                references apm_packages,
                                constraint application_groups_package_id_un
                                unique (package_id)
);


-- old define_function_args('application_group__new','group_id,object_type;application_group,creation_date;now(),creation_user,creation_ip,email,url,group_name,package_id,join_policy,context_id')
-- new
select define_function_args('application_group__new','group_id,object_type;application_group,creation_date;now(),creation_user;null,creation_ip;null,email;null,url;null,group_name,package_id,join_policy,context_id;null');




--
-- procedure application_group__new/11
--
CREATE OR REPLACE FUNCTION application_group__new(
   new__group_id integer,
   new__object_type varchar,       -- default 'application_group',
   new__creation_date timestamptz, -- default sysdate, -- default 'now()'
   new__creation_user integer,     -- default null,
   new__creation_ip varchar,       -- default null,
   new__email varchar,             -- default null,
   new__url varchar,               -- default null,
   new__group_name varchar,
   new__package_id integer,
   new__join_policy varchar,
   new__context_id integer         -- default null

) RETURNS integer AS $$
DECLARE
  v_group_id		     application_groups.group_id%TYPE;
BEGIN
  v_group_id := acs_group__new (
    new__group_id,
    new__object_type,
    new__creation_date,
    new__creation_user,
    new__creation_ip,
    new__email,
    new__url,
    new__group_name,
    new__join_policy,
    new__context_id
  );

  insert into application_groups (group_id, package_id) 
    values (v_group_id, new__package_id);

  return v_group_id;

END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('application_group__delete','group_id');

--
-- procedure application_group__delete/1
--
CREATE OR REPLACE FUNCTION application_group__delete(
   group_id integer
) RETURNS integer AS $$
DECLARE
BEGIN
    PERFORM acs_group__delete(group_id);

    return 0;
END;
$$ LANGUAGE plpgsql;




-- added
select define_function_args('application_group__group_id_from_package_id','package_id,no_complain_p;f');

--
-- procedure application_group__group_id_from_package_id/2
--
CREATE OR REPLACE FUNCTION application_group__group_id_from_package_id(
   group_id_from_package_id__package_id integer,
   group_id_from_package_id__no_complain_p boolean -- default 'f'

) RETURNS integer AS $$
DECLARE
  v_group_id				  application_groups.group_id%TYPE;
  v_object_name				  varchar;
BEGIN

  select group_id 
    into v_group_id
    from application_groups 
    where package_id = group_id_from_package_id__package_id;

-- TODO: does this shortcut the exception in Oracle?
--    return v_group_id;

  if not found then
    if group_id_from_package_id__no_complain_p != 't' then
      v_object_name := acs_object__name(group_id_from_package_id__package_id);
      raise EXCEPTION '-20000: No group_id found for package % (%)', group_id_from_package_id__package_id, v_object_name;
    end if;
    return null;
  else
    return v_group_id;
  end if;

END;
$$ LANGUAGE plpgsql stable;

insert into group_type_rels
(group_rel_type_id, group_type, rel_type)
values
(nextval('t_acs_object_id_seq'), 'application_group', 'composition_rel');

insert into group_type_rels
(group_rel_type_id, group_type, rel_type)
values
(nextval('t_acs_object_id_seq'), 'application_group', 'membership_rel');

insert into group_type_rels
(group_rel_type_id, group_type, rel_type)
values
(nextval('t_acs_object_id_seq'), 'application_group', 'admin_rel');

-----------
-- Views --
-----------

create view application_group_element_map as
select g.package_id, g.group_id, 
       m.element_id, m.container_id, m.rel_id, m.rel_type, m.ancestor_rel_type
from application_groups g,
     group_element_map m
where g.group_id = m.group_id;

create view app_group_distinct_element_map as
select distinct package_id, group_id, element_id
from application_group_element_map;

create view app_group_distinct_rel_map as
select distinct package_id, group_id, rel_id, rel_type, ancestor_rel_type
from application_group_element_map;

create view application_group_segments as
select g.package_id, s.segment_id, s.group_id, s.rel_type, s.segment_name
from application_groups g,
     group_element_map m,
     rel_segments s
where g.group_id = m.group_id
  and m.element_id = s.group_id
UNION ALL
select g.package_id, s.segment_id, s.group_id, s.rel_type, s.segment_name
from application_groups g,
     rel_segments s
where g.group_id = s.group_id;