-- 
-- /www/doc/sql/user-groups.sql
--
-- Author: Philip Greenspun (philg@mit.edu), 11/15/98
--
-- augmented 3/98 by Tracy Adams (teadams@mit.edu) 
-- to handle
--    a) Database-driven roles and action permissions
--    b) Creates group_type "administration" for site and
--       module administration
--    c) Permission system that allows programmers to ask
--       "Can user x do y?"
--
-- user-groups.sql,v 3.6.2.1 2000/03/15 09:36:44 michael Exp
--

-- allows administrators and users to set up groups of users
-- and then place users in those groups with roles
-- for example, the groups could be hospitals and then a user
-- could be associated with Hospital X as "physician" and with 
-- Hospital Y as "physician".
--

-- a group type might be 'hospital' or 'cardiac_center'
-- or 'professional_society'

-- we keep group_type short because we will be building tables
-- with group_type as the name, e.g., "hospital_info" will store 
-- the extra information specified in user_group_type_fields
-- for groups of type "hospital"

-- if pretty_name is "Cardiac Center" then pretty_plural
-- is "Cardiac Centers"

-- approval_policy of "open" means users can create groups of 
-- this type and they are immediately live
-- of "closed" means that only admins can create groups of this type
-- of "wait" means that users are offered the option to create 
-- but then an admin must approve

create table user_group_types (
	group_type 	varchar(20) primary key,
	pretty_name	varchar(50) not null,
	pretty_plural	varchar(50) not null,
	approval_policy	varchar(30) not null,
	default_new_member_policy	varchar(30) default 'open' not null,
	-- if group_module_administration=full, then group administrators have full control of which modules
	-- they can use (they can add, remove, enable and disable modules)
	-- if group_module_administration=enabling, then group administrators have authority to enable and 
	-- disable modules but cannot add or remove modules
	-- if group_module_administration=none, the group administrators have no control over modules
	-- modules are explicitly set for the user group type by the system administrator 
	group_module_administration	varchar(20) default 'none',
	-- does this group type support virtual group directories 	
	-- if has_virtual_directory_p is t, then virtual url /$group_type can be used instead of /groups 
	-- to access the groups of this type
	has_virtual_directory_p		char(1) default 'f' check(has_virtual_directory_p in ('t','f')),
	-- if has_virtual_directory_p is t and group_type_public_directory is not null, then files in 
	-- group_type_public_directory will be used instead of files in default /groups directory
	-- notice also that these files will be used only when the page is accessed through /$group_type url's
	group_type_public_directory     varchar(200),
	-- if has_virtual_directory_p is t and group_type_admin_directory is not null, then files in 
	-- group_type_admin_directory will be used instead of files in default /groups/admin directory
	-- notice also that these files will be used only when the page is accessed through /$group_type url's
	group_type_admin_directory      varchar(200),
	-- if has_virtual_directory_p is t and group_public_directory is not null, then files in 
	-- group_public_directory will be used instead of files in default /groups/group directory
	-- notice also that these files will be used only when the page is accessed through /$group_type url's
	group_public_directory          varchar(200),
	-- if has_virtual_directory_p is t and group_admin_directory is not null, then files in 
	-- group_admin_directory will be used instead of files in default /groups/admin/group directory
	-- notice also that these files will be used only when the page is accessed through /$group_type url's
	group_admin_directory           varchar(200)
	constraint group_type_module_admin_check check (
	  (group_module_administration is not null)
	   and (group_module_administration in ('full', 'enabling', 'none')))
);


-- fields of info that are required for each group type
-- these will be stored in a separate table, called
-- ${group_type}_info (e.g., "hospital_info")

create table user_group_type_fields (
	group_type	varchar(20) not null references user_group_types,
	column_name	varchar(30) not null,
	pretty_name	varchar(50) not null,
	-- something generic and suitable for handing to AOLserver, 
	-- e.g., boolean or text
	column_type	varchar(50) not null,
	-- something nitty gritty and Oracle-specific, e.g.,
	-- char(1) instead of boolean
	-- things like "not null"
	column_actual_type	varchar(100) not null,
	column_extra	varchar(100),
	-- Sort key for display of columns.
	sort_key		integer not null
);


-- this table is used when group administrators are not allowed to handle module administration
-- (allow_module_administration_p is set to 0 for this group type)
-- all groups of this group type will have only modules set up for which mapping in this table exists
create sequence group_type_modules_id_sequence;
create table user_group_type_modules_map (
	group_type_module_id 	integer primary key,	
	group_type 		varchar(20) references user_group_types not null,
	module_key		varchar(30) references acs_modules not null
);

create sequence user_group_sequence;
create table user_groups (
	group_id	integer primary key,
	group_type	varchar(20) not null references user_group_types,
	group_name	varchar(100),
	short_name 	varchar(100) unique not null,
	admin_email 	varchar(100),
	registration_date	datetime not null,
	creation_user		integer not null references users(user_id),
	creation_ip_address	varchar(50) not null,
	approved_p 	char(1) check (approved_p in ('t','f')),
	active_p	char(1) default 't' check(active_p in ('t','f')),
	existence_public_p	char(1) default 't' check (existence_public_p in ('t','f')),
	new_member_policy	varchar(30) default 'open' not null,
	spam_policy		varchar(30) default 'open' not null,
	constraint user_groups_spam_policy_check check(spam_policy in ('open','closed','wait')),
	-- are the administrators notified of new membership?
	email_alert_p		char(1) default 'f' check (email_alert_p in ('t','f')),
	-- should we use the multi-role based
	multi_role_p	char(1) default 'f' check (multi_role_p in ('t','f')),
	-- can the user group administration control roles and actions?
	-- If f, only site admin pages will have the functionality to modify role-action mappings.  This is a way to "lock in" permission system.
	group_admin_permissions_p   char(1) default 'f' check (group_admin_permissions_p in ('t','f')),
	index_page_enabled_p	char(1) default 'f' check (index_page_enabled_p in ('t','f')),
	-- this is index page content
	body			lztext,
	-- html_p for the index page content
	html_p	                char(1) default 'f' check (html_p in ('t','f')),
        -- let's keep track of when these records are modified
        modification_date   datetime,
        modifying_user      integer references users,
        -- add a parent_group_id to support subgroups
        parent_group_id	integer references user_groups(group_id)
);
-- index parent_group_id to make parent lookups quick!
create index user_groups_parent_grp_id_idx on user_groups(parent_group_id);


create function user_group_group_type (integer)
returns varchar as '
DECLARE
	v_group_id alias for $1;
	v_group_type user_group_types.group_type%TYPE;
BEGIN
	select group_type into v_group_type
	from user_groups
	where group_id = v_group_id;

	return v_group_type;
END;
' language 'plpgsql';

-- this is the helper function for function short_name_from_group_name bellow
create function short_name_from_group_name2(varchar, integer)
returns varchar as '
DECLARE
	v_short_name alias for $1;
	v_identifier alias for $2;
	v_new_short_name user_groups.short_name%TYPE;
	v_test_short_name user_groups.short_name%TYPE;
BEGIN
	select short_name into v_test_short_name
	from user_groups
	where short_name = v_short_name ||
	case when v_identifier=0 then '''' else v_identifier end;

	if NOT FOUND then
	    IF v_identifier = 0
	    then return v_short_name;
	    else return v_short_name || v_identifier;
	    end if;
    else	
	return short_name_from_group_name2(v_short_name, v_identifier+1);
	end if;
END;
' language 'plpgsql';


-- this function generates unique short_name from the group_nams
-- v_group_name is the group_name of the group, this function will first transform group_name by making it lower case, 
-- and substituting spaces and underscores with dashes. thus, if group_name is Photographers, the transformed group_name
-- will be photographers. then, this function will keep adding numbers to it until it makes it unique (e.g. if short_names
-- photographers and photographers1 already exist this function will return photographers2)

create function short_name_from_group_name (varchar)
returns varchar as '
DECLARE
	v_group_name alias for $1;
BEGIN
	return short_name_from_group_name2(lower(substr(translate(v_group_name, ''_ '',''--''), 1, 80)), 0);
END;
' language 'plpgsql';


-- this procedure sets the short_name of all the groups in the user_group
-- table using short_name_from_group_name function
-- notice that simple update using short_name_from_group_name could not be
-- performed because function short_name_from_group_name is used while
-- user_groups is mutating (ORA-04091)
create function generate_short_names_for_group()
returns opaque as '
DECLARE
        v_group_id user_groups.group_id%TYPE;
	v_group_name user_groups.group_name%TYPE;
	v_short_name user_groups.short_name%TYPE;

	cursor c1 is 
	select group_id, group_name
	from user_groups;
BEGIN
	OPEN c1;

	LOOP
           FETCH c1 INTO v_group_id, v_group_name;
           EXIT WHEN c1%NOTFOUND;
	
	   v_short_name:= short_name_from_group_name(v_group_name);
	   update user_groups set short_name=v_short_name where group_id=v_group_id;

       END LOOP;
END;
' language 'plpgsql';

create function set_user_group_regdate() returns opaque as '
  declare
    group_type_row user_group_types%ROWTYPE;
  begin
    if new.registration_date isnull then
        new.registration_date := ''now'';
    end if;
    if new.approved_p isnull then
    select * into group_type_row from user_group_types ugt 
      where ugt.group_type = new.group_type;
    if group_type_row.approval_policy = ''open'' then
      new.approved_p := ''t'';
    else 
      new.approved_p := ''f'';
    end if;
  end if;  
  return new;
end;
' language 'plpgsql';

create trigger user_group_regdate
before insert on user_groups
for each row execute procedure set_user_group_regdate();

-- role = 'administrator' is magic and lets the person add other
-- members

create table user_group_map (
	group_id 	integer not null references user_groups,
	user_id		integer not null references users,
	-- define in this order because we want to 
	-- quickly see if user X belongs to requested group
	-- and/or which groups a user belongs to
	role		varchar(200),
	registration_date	datetime not null,
	-- keep track of who did this and from where
	mapping_user	integer not null references users(user_id),
	-- store the string, separated by dots, e.g., 18.23.10.101
	-- make it large enough to handle IPv6 (128 bits)
	mapping_ip_address	varchar(50) not null,
	primary key (group_id, user_id, role)
);


create function set_user_group_map_regdate() returns opaque as '
  begin
    if new.registration_date isnull then
      new.registration_date := ''now'';
    end if;
    return new;
  end;
' language 'plpgsql';

create trigger user_group_map_regdate
before insert on user_group_map
for each row execute procedure set_user_group_map_regdate();


-- holds people who've asked to be in a group but aren't 
-- approved yet

create table user_group_map_queue (
	group_id	integer references user_groups,
	user_id		integer references users,
	ip_address	varchar(50),
	queue_date	datetime default current_timestamp,
	primary key (group_id, user_id)
);

-- stores the roles used by each group
-- only meaningful for groups that use a 
-- multi_role permission system

create table user_group_roles (
	group_id	     integer not null references user_groups,
	role		     varchar(200),
	creation_date        datetime not null,
	creation_user        integer not null references users,
	creation_ip_address  varchar(200) not null,
	primary key (group_id, role)
);

create function set_user_group_roles_creation_date() returns opaque as '
  begin
    if new.creation_date isnull then
      new.creation_date := ''now'';
    end if;
    return new;
  end;
' language 'plpgsql';

create trigger user_group_roles_creation_date
before insert on user_group_roles
for each row execute procedure set_user_group_roles_creation_date();

-- stores the actions used by each group
-- only used in multi-role mode

create table user_group_actions (
	group_id	     integer not null references user_groups,
	action		     varchar(200),
	creation_date        datetime not null,
	creation_user        integer not null references users,
	creation_ip_address  varchar(200) not null,
	primary key (group_id, action));

create function set_user_group_actions_create_date() returns opaque as '
  begin
    if new.creation_date isnull then
      new.creation_date := ''now'';
    end if;
    return new;
  end;
' language 'plpgsql';

create trigger user_group_actions_create_date
before insert on user_group_actions
for each row execute procedure set_user_group_actions_create_date();

-- maps roles to allowed actions

create table user_group_action_role_map (
	group_id     	integer not null references user_groups,
	role		varchar(200) not null,
	action		varchar(200) not null,
	creation_date        datetime not null,	
	creation_user        integer not null references users,
	creation_ip_address  varchar(200) not null,
	primary key (group_id, role, action)
);

--- normal identifier is too long
create function set_user_gr_action_role_date() returns opaque as '
  begin
    if new.creation_date is null then
      new.creation_date := ''now'';
    end if;
    return new;
  end;
' language 'plpgsql';

create  trigger user_gr_action_role_map_date
before insert on user_group_action_role_map
for each row execute procedure set_user_gr_action_role_date();

create function set_user_group_map_queue_date() returns opaque as '
  begin
    if new.queue_date is null then
      new.queue_date := ''now'';
    end if;
    return new;
  end;
' language 'plpgsql';

create  trigger user_group_map_queue_date
before insert on user_group_map_queue
for each row execute procedure set_user_group_map_queue_date();

---MODULE  ADMINISTRATION

-- this table lets you ask "what is the user group that corresponds
-- to a particular module/submodule of the ACS?"

-- module would be something like "classifieds" and submodule
-- would be a classifieds domain; for a bboard, the module would
-- be "bboard" and the submodules individual bboard topics 

-- NOTE: Most tables named grouptype_info are autogenerated
-- This is the one _info table that is created by hand

create table administration_info (
 	group_id	integer not null references user_groups,
	module		varchar(300) not null,
	submodule	varchar(300),
	--- link to the module administration page
	url		varchar(300),
	unique(module, submodule)
);

create function init_system_group_types() returns integer as '
begin
 if count(*) = 0 from user_group_types where group_type = ''administration'' then
   insert into user_group_types
     (group_type, pretty_name, pretty_plural, approval_policy, default_new_member_policy, group_module_administration)
   values
     (''administration'', ''Administration'', ''Administration Groups'', ''closed'', ''closed'', ''full'');
   insert into user_group_type_fields
     (group_type, column_name, pretty_name, column_type, column_actual_type, column_extra, sort_key)
   values
     (''administration'', ''module'', ''Module'', ''text'', ''varchar(300)'', ''not null'', 1);
   insert into user_group_type_fields
     (group_type, column_name, pretty_name, column_type, column_actual_type, column_extra, sort_key)
   values
     (''administration'', ''submodule'', ''Submodule'', ''text'', ''varchar(300)'',null, 2);
   -- so that we can offer admins links from their workspace 
   insert into user_group_type_fields
     (group_type, column_name, pretty_name, column_type, column_actual_type, column_extra, sort_key)
   values
     (''administration'', ''url'', ''URL'', ''text'', ''varchar(100)'','''',3);
 end if;
 return 1;
end;
' language 'plpgsql';

select init_system_group_types();

--
-- A postgres function overload which is nice and neat for
-- backwards compatibility when there is no short name
create function administration_group_add(varchar,varchar,varchar,char,varchar)
returns integer as '
DECLARE
BEGIN
	return administration_group_add( $1 , short_name_from_group_name( $1), $2 , $3 , $4 , $5);
END;
' language 'plpgsql';

-- creates a new group of type "administration"; does nothing if the group is
-- already defined

create  function administration_group_add (varchar, varchar, varchar, varchar, char, varchar )
   returns integer as ' 
declare
  pretty_name alias for $1;
  v_short_name alias for $2;
  v_module alias for $3;
  v_submodule alias for $4;
  v_multi_role_p alias for $5;
  v_url alias for $6;
  v_group_id integer;
  n_administration_groups integer;
  v_system_user_id integer; 
  v_submodule_kludge varchar;
BEGIN
  if v_submodule = '''' then
      v_submodule_kludge := null;
      select count(group_id) into n_administration_groups
        from administration_info 
        where module = v_module 
        and submodule isnull;
      else
        v_submodule_kludge := v_submodule;
	select count(group_id) into n_administration_groups
         from administration_info
         where module = v_module 
         and submodule = v_submodule;
  end if;
  if n_administration_groups = 0 then 
     -- call procedure defined in community-core.sql to get system user
     v_system_user_id := system_user_id();
     select nextval(''user_group_sequence'') into v_group_id;
     insert into user_groups 
      (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p)
      values
      (v_group_id, ''administration'', v_short_name, pretty_name, v_system_user_id, ''0.0.0.0'', ''t'', ''f'', ''closed'', v_multi_role_p);
     insert into administration_info (group_id, module, submodule, url) values (v_group_id, v_module, v_submodule_kludge, v_url);
   end if;
   return 1;
end;
' language 'plpgsql';

--- Define an administration group for site wide administration

select
   administration_group_add ('Site-Wide Administration', 'SWA', 'site_wide', '', 'f', '/admin/'); 

--- returns the group_id of the site_wide administration group

create  function system_administrator_group_id()
returns integer as '
declare
  v_group_id	integer;
begin
  select group_id into v_group_id
   from administration_info
   where module = ''site_wide''
   and submodule isnull;
  return v_group_id;
end;
' language 'plpgsql';

---  Add the system user to the site-wide administration group

create function system_user_to_administrator_group() returns integer as '
declare
 v_system_group_id      integer;
 v_system_user_id	integer;
 n_user_id		integer;
begin
   v_system_user_id := system_user_id();
   v_system_group_id := system_administrator_group_id();
   select count(user_id) into n_user_id
   from user_group_map 
   where user_id = v_system_user_id
     and group_id = v_system_group_id;
   if n_user_id = 0 then
	insert into user_group_map
    	(group_id, user_id, role, mapping_user, mapping_ip_address)
   	values 
    	(v_system_group_id, v_system_user_id, ''administrator'', v_system_user_id, ''0.0.0.0'');
   end if;
   return 1;
end;
' language 'plpgsql';

select system_user_to_administrator_group();

--  Some query functions

create function ad_group_member_p (integer, integer)
returns varchar as '
DECLARE
	v_user_id alias for $1;
	v_group_id alias for $2;
	n_rows integer;
BEGIN
  select count(*) into n_rows
   from user_group_map 
   where user_id = v_user_id
   and group_id = v_group_id;
  IF n_rows > 0 THEN
    return ''t'';
  ELSE
    return ''f'';
  END IF;
END;
' language 'plpgsql';

create function ad_user_has_role_p(integer,integer,varchar)
returns char
as '
DECLARE
	v_user_id alias for $1;
	v_group_id alias for $2;
	v_role alias for $3;
  ad_user_has_role_p char(1);
BEGIN
  select case when count(*)=0 then ''f'' else ''t'' end
  into ad_user_has_role_p
  from user_group_map 
  where user_id = v_user_id
  and group_id = v_group_id
  and role = v_role;

  return ad_user_has_role_p;
END;
' language 'plpgsql';

create function ad_group_member_admin_role_p (integer,integer)
returns varchar as '
DECLARE
	v_user_id alias for $1;
	v_group_id alias for $2;
	n_rows integer;
BEGIN
  select count(*) into n_rows
   from user_group_map 
   where user_id = v_user_id
   and group_id = v_group_id
   and lower(role) = ''administrator'';
  IF n_rows > 0 THEN
    return ''t'';
  ELSE
    return ''f'';
  END IF;
END;
' language 'plpgsql';

create  function ad_admin_group_member_p (varchar, varchar, integer)
returns varchar
as '
DECLARE
  v_module alias for $1;
  v_submodule alias for $2;
  v_user_id alias for $3;
  n_rows  integer;
  v_submodule_kludge varchar;
BEGIN
  if v_submodule = '''' then
    v_submodule_kludge := null;
  else 
    v_submodule_kludge := v_submodule;
  end if;
  select count(*) into n_rows
   from user_group_map 
   where user_id = v_user_id
   and group_id in (select group_id from administration_info
                    where (module = v_module and submodule = v_submodule)
                    or module = ''site_wide'');
  IF n_rows > 0 THEN
    return ''t'';
  ELSE
    return ''f'';
  END IF;
END;
' language 'plpgsql';


-- This table records additional fields to be recorded per user who belongs
-- to a group of a particular type.
-- Each field can be associated with a role within a group.  This allows
-- us to present a role-specific set of fields for users to add/edit.
-- If the role field is empty we present the field to all members
-- of the group of specified group_type. aegrumet@arsdigita.com, 2000-03-10
create table user_group_type_member_fields (
	group_type	varchar(20) references user_group_types,
	role		varchar(200),
	field_name	varchar(200) not null,
	field_type	varchar(20) not null, -- short_text, long_text, boolean, date, etc.
	-- Sort key for display of columns.
	sort_key		integer not null,
        -- We can't make this a primary key since role can be NULL.
	-- The unique constraint creates an index.
	unique (group_type, role, field_name)
);


-----------------------------------------------------------------------------------------------------------


-- created by ahmeds@mit.edu on Thu Jan 13 21:29:11 EST 2000
--
-- supports a system for spamming members of a user group 
--

-- group_member_email_preferences table retains email preferences of members 
-- that belong to a particular group 

create table group_member_email_preferences (
	group_id		integer references user_groups not null,
	user_id			integer references users not null ,
	dont_spam_me_p		char (1) default 'f' check(dont_spam_me_p in ('t','f')),
	primary key (group_id, user_id)  
);


-- group_spam_history table holds the spamming log for this group 

create sequence group_spam_id_sequence  start 1;

create table group_spam_history (
	spam_id			integer primary key,
	group_id		integer references user_groups not null,
	sender_id		integer references users(user_id) not null,
	sender_ip_address	varchar(50) not null,
	from_address		varchar(100),
	subject			varchar(200),
 	body			lztext,
	send_to			varchar (50) default 'members' check (send_to in ('members','administrators')), 
	creation_date		datetime not null,
	-- approved_p matters only for spam policy='wait'
	-- approved_p = 't' indicates administrator approved the mail 
	-- approved_p = 'f' indicates administrator disapproved the mail, so it won't be listed for approval again
	-- approved_p = null indicates the mail is not approved/disapproved by the administrator yet 
	approved_p		char(1) default null check (approved_p is null or approved_p in ('t','f')),
	send_date		datetime,
	-- this holds the number of intended recipients
	n_receivers_intended	integer default 0,
	-- we'll increment this after every successful email
	n_receivers_actual	integer default 0
);


-- This function returns the number of members all of the subgroups of
-- one group_id has. Note that since we made subgroups go 1 level down
-- only, this function only looks for groups whose parent is the specified
-- v_parent_group_id
create function user_groups_number_subgroups (integer)
returns integer AS '
DECLARE
  v_group_id alias for $1;
BEGIN
  return count(*) from user_groups where parent_group_id = v_group_id;
END;' language 'plpgsql';


-- We need to be able to answer "How many total members are there in all 
-- of my subgroups?" 
create function user_groups_number_submembers (integer)
returns integer AS '
DECLARE
  v_parent_group_id alias for $1;
BEGIN
  return count(*)
    from user_group_map 
   where group_id in (select group_id 
                        from user_groups 
                       where parent_group_id = v_parent_group_id);
END;' language 'plpgsql';


-- While doing a connect by, we need to count the number of members in 
-- user_group_map. Since we can't join with a connect by, we create 
-- this function
create function user_groups_number_members (integer) returns integer AS '
DECLARE
  v_group_id alias for $1;
BEGIN
  return count(*)
    from user_group_map 
   where group_id = v_group_id;
END;' language 'plpgsql';


-- easy way to get the user_group from an id. This is important when
-- using connect by in your table and it also makes the code using 
-- user subgroups easier to read (don't have to join an additional
-- user_groups tables). However, it is recommended that you only
-- use this pls function when you have to or when it truly saves you
-- from some heinous coding
create function user_group_name_from_id (integer) returns varchar AS '
DECLARE
  v_group_id alias for $1;
BEGIN
  return group_name from user_groups where group_id = v_group_id;
END;' language 'plpgsql';


-- With subgroups, we needed an easy way to add adminstration groups
-- and tie them to parents
-- DRB: NULL args don't work reliably in PG need to thing about this
-- one...

create function administration_subgroup_add(varchar, varchar, varchar, varchar, varchar, varchar, varchar)
returns integer as '
DECLARE
	pretty_name alias for $1;
	v_short_name alias for $2;
	v_module alias for $3;
	v_submodule_kludge alias for $4;
	v_multi_role_p alias for $5;
	v_url alias for $6;
	v_parent_module alias for $7;
	v_submodule	varchar(300);
	v_group_id	integer;
	n_administration_groups integer;
	v_system_user_id integer; 
	v_parent_id integer;
BEGIN
  if v_submodule_kludge = '''' then
    v_submodule:= NULL;
  else 
    v_submodule:= v_submodule_kludge;
  end if;

  if v_submodule is null then
      select count(group_id) into n_administration_groups
        from administration_info 
        where module = v_module 
        and submodule is null;
      else
	select count(group_id) into n_administration_groups
         from administration_info
         where module = v_module 
         and submodule = v_submodule;
  end if;

  if n_administration_groups = 0 then 
     -- call procedure defined in community-core.sql to get system user
     v_system_user_id := system_user_id();
     select nextval(''user_group_sequence'') into v_group_id;
     insert into user_groups 
      (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p)
      values
      (v_group_id, ''administration'', v_short_name, pretty_name, v_system_user_id, ''0.0.0.0'', ''t'', ''f'', ''closed'', v_multi_role_p);
     insert into administration_info (group_id, module, submodule, url) values (v_group_id, v_module, v_submodule, v_url);
   end if;

   -- Not sure what this begin exception end is supposed to
   -- do, I am going to ignore it for now (BMA).
   --   Begin
      select ai.group_id into v_parent_id
      from administration_info ai, user_groups ug
      where ai.module = v_parent_module
      and ai.group_id != v_group_id
      and ug.group_id = ai.group_id
      and ug.parent_group_id is null;
   -- Exception when others then null;
   -- End;
   
   update user_groups
   set parent_group_id = v_parent_id
   where group_id = v_group_id;

   return 1;
end;' language 'plpgsql';



-- Adds the specified field_name and field_type to a group with group id v_group_id
-- if the member field already exists for this group, does nothing
-- if v_sort_key is not specified, the member_field will be added with sort_key
--   1 greater than the current max
create function user_group_member_field_add() returns integer as '
declare
  n_groups          integer;
BEGIN
  -- make sure we don''t violate the unique constraint of user_groups_member_fields
  select decode(count(*),0,0,1) into n_groups
    from all_member_fields_for_group
   where group_id = v_group_id
     and field_name = v_field_name;

  if n_groups = 0 then 
     -- member_field is new - add it

     insert into user_group_member_fields 
     (group_id, field_name, field_type, sort_key)
     values
     (v_group_id, v_field_name, v_field_type, v_sort_key);

   end if;
  return 1;
end;
' language 'plpgsql';



-- function to create new groups of a specified type 
-- This is useful mostly when loading your modules - simply use this 
-- function to create the groups you need
create function user_group_add (varchar, varchar, varchar, varchar)
RETURNS integer AS '
DECLARE
  v_group_type alias for $1;
  v_pretty_name alias for $2;
  v_short_name alias for $3;
  v_multi_role_p alias for $4;
  v_system_user_id  integer; 
BEGIN
     -- call procedure defined in community-core.sql to get system user
     v_system_user_id := system_user_id();
     -- create the actual group
     insert into user_groups 
      (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p)
     select nextval(''user_group_sequence''), v_group_type, v_short_name,
       v_pretty_name, v_system_user_id, ''0.0.0.0'', ''t'', ''f'', ''closed'',
       v_multi_role_p
     where not exists (select * from user_groups
       where upper(short_name) = upper(v_short_name));

       RETURN 1;
end;' language 'plpgsql';

-- Contains information about fields to gather per user for a user group.
-- Cannot contain a field_name that appears in the
-- user_group_type_member_fields table for the group type this group belongs to.

create table user_group_member_fields (
	group_id	integer references user_groups,
	field_name	varchar(200) not null,
	field_type	varchar(20) not null, -- short_text, long_text, boolean, date, etc.
	sort_key	integer not null,
	primary key (group_id, field_name)
);

-- View that brings together all field information for a user group, from
-- user_group_type_member_fields and user_group_member_fields.
-- We throw in the sort keys prepended by 'a' or 'b' so we can display
-- them in the correct order, with the group type fields first.
create view all_member_fields_for_group as
select group_id, field_name, field_type, 'a' || sort_key as sort_key
from user_group_type_member_fields ugtmf, user_groups ug
where ugtmf.group_type = ug.group_type;
-- DRB

-- 6.5.3 PostgreSQL doesn't implement views on unions, so the
-- users of the above view will have to do the union themselves.
-- There aren't that many pages that use this...

--union
--select group_id, field_name, field_type, 'b' || sort_key as sort_key
--from user_group_member_fields;


-- Contains extra field information for a particular user. These fields
-- were defined either in user_group_type_member_fields or 
-- user_group_member_fields
create table user_group_member_field_map (
	group_id	integer references user_groups,
	user_id		integer references users,
	field_name	varchar(200) not null,
	field_value	varchar(4000),
	primary key (group_id, user_id, field_name)
);

-- Sequence used to support a Terrible kludge to work around the
-- inability to drop a column in Postgres.  See the ug admin script
-- "field-delete-2.tcl" for a more complete explanation.

create sequence user_group_deleted_seq start 1;

-- Two little functions to help overcome lack of outer joins

create function user_group_count_group_map(integer) returns integer as '
begin
    return count(*) from user_group_map where group_id = $1;
end;
' language 'plpgsql';

create function user_group_count_groups(varchar) returns integer as '
begin
    return count(*) from user_groups where group_type = $1;
end;
' language 'plpgsql';

create function user_group_short_name_from_id (integer) returns varchar AS '
DECLARE
  v_group_id alias for $1;
BEGIN
  return short_name from user_groups where group_id = v_group_id;
END;' language 'plpgsql';

create function user_group_hierarchy_level(integer, integer, integer)
returns integer as '
DECLARE
	v_group_id alias for $1;
	v_start_id alias for $2;
	v_level alias for $3;
	v_parent_id integer;
BEGIN
	IF v_group_id = v_start_id
	then return v_level;
	end if;

	select parent_group_id into v_parent_id from user_groups where group_id= v_group_id;

	if v_parent_id is null
	then
		if v_start_id= 0
		then return v_level+1;
		else return null;
		end if;
	end if;

	return user_group_hierarchy_level(v_parent_id, v_start_id, 1+ v_level);
END;
' language 'plpgsql';

create function user_group_hierarchy_sortkey(integer, integer, char)
returns char as '
DECLARE
	v_group_id alias for $1;
	v_start_id alias for $2;
	v_sortkey alias for $3;
	v_parent_id integer;
BEGIN
	IF v_group_id = v_start_id
	then return (v_group_id::char || v_sortkey);
	end if;

	select parent_group_id into v_parent_id from user_groups where group_id= v_group_id;

	if v_parent_id is null
	then return null;
	end if;

	return user_group_hierarchy_sortkey(v_parent_id, v_start_id, ''/'' || v_group_id::char || v_sortkey);
END;
' language 'plpgsql';