--
-- packages/acs-kernel/sql/acs-create.sql
--
-- @author rhs@mit.edu
-- @creation-date 2000-08-22
-- @cvs-id acs-create.sql,v 1.1.2.9 2000/08/24 07:09:18 rhs Exp
--

create table acs_magic_objects (
	name		varchar(100)
			constraint acs_magic_objects_pk primary key,
	object_id	integer not null constraint acs_magic_objects_object_id_fk
                        references acs_objects(object_id)
);

create index acs_mo_object_id_idx on acs_magic_objects (object_id);

comment on table acs_magic_objects is '
 This table allows us to provide semantic names for certain special
 objects like the site-wide organization, and the all users party.
';

-- create or replace package acs
-- as
-- 
--   function add_user (
--     user_id		in users.user_id%TYPE default null,
--     object_type		in acs_objects.object_type%TYPE
-- 	 		   default 'user',
--     creation_date	in acs_objects.creation_date%TYPE
-- 			   default sysdate,
--     creation_user	in acs_objects.creation_user%TYPE
-- 			   default null,
--     creation_ip		in acs_objects.creation_ip%TYPE default null,
--     email		in parties.email%TYPE,
--     url			in parties.url%TYPE default null,
--     first_names		in persons.first_names%TYPE,
--     last_name		in persons.last_name%TYPE,
--     password		in users.password%TYPE,
--     salt		in users.salt%TYPE,
--     password_question   in users.password_question%TYPE default null,
--     password_answer	in users.password_answer%TYPE default null,
--     screen_name		in users.screen_name%TYPE default null,
--     email_verified_p 	in users.email_verified_p%TYPE default 't',
--     member_state	in membership_rels.member_state%TYPE default 'approved'
--   )
--   return users.user_id%TYPE;
-- 
--   procedure remove_user (
--     user_id	in users.user_id%TYPE
--   );
-- 
--   function magic_object_id (
--      name	in acs_magic_objects.name%TYPE
--   ) return acs_objects.object_id%TYPE;
-- 
-- end acs;

-- show errors

-- create or replace package body acs
-- function add_user
create function acs__add_user (integer,varchar,timestamp,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,varchar)
returns integer as '
declare
  user_id                alias for $1;  -- default null    
  object_type            alias for $2;  -- default ''user''
  creation_date          alias for $3;  -- default now()
  creation_user          alias for $4;  -- default null
  creation_ip            alias for $5;  -- default null
  email                  alias for $6;  
  url                    alias for $7;  -- default null
  first_names            alias for $8;  
  last_name              alias for $9;  
  password               alias for $10; 
  salt                   alias for $11; 
  password_question      alias for $12; -- default null
  password_answer        alias for $13; -- default null
  screen_name            alias for $14; -- default null
  email_verified_p       alias for $15; -- default ''t''
  member_state           alias for $16; -- default ''approved''
  v_user_id              users.user_id%TYPE;
  v_rel_id               membership_rels.rel_id%TYPE;
begin
    v_user_id := acs_user__new (user_id, object_type, creation_date,
				creation_user, creation_ip, email,
				url, first_names, last_name, password,
				salt, password_question, password_answer,
				screen_name, email_verified_p,null);
   
    v_rel_id := membership_rel__new (
      null,
      ''membership_rel'',
      acs__magic_object_id(''registered_users''),      
      v_user_id,
      member_state,
      null,
      null);

    PERFORM acs_permission__grant_permission (
      v_user_id,
      v_user_id,
      ''read''
      );

    PERFORM acs_permission__grant_permission (
      v_user_id,
      v_user_id,
      ''write''
      );

    return v_user_id;
   
end;' language 'plpgsql';


-- procedure remove_user
create function acs__remove_user (integer)
returns integer as '
declare
  remove_user__user_id                alias for $1;  
begin
    delete from users
    where user_id = remove_user__user_id;

    return 0; 
end;' language 'plpgsql';


-- function magic_object_id
create function acs__magic_object_id (varchar)
returns integer as '
declare
  magic_object_id__name                   alias for $1;  
  magic_object_id__object_id              acs_objects.object_id%TYPE;
begin
    select object_id
    into magic_object_id__object_id
    from acs_magic_objects
    where name = magic_object_id__name;

    return magic_object_id__object_id;
   
end;' language 'plpgsql';



-- show errors

-- ******************************************************************
-- * Community Core API
-- ******************************************************************

create view registered_users
as
  select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state
  from parties p, persons pe, users u, group_member_map m, membership_rels mr
  where party_id = person_id
  and person_id = user_id
  and u.user_id = m.member_id
  and m.rel_id = mr.rel_id
  and m.group_id = acs__magic_object_id('registered_users')
  and mr.member_state = 'approved'
  and u.email_verified_p = 't';

create view cc_users
as
select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id
from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr
where o.object_id = pa.party_id
and pa.party_id = pe.person_id
and pe.person_id = u.user_id
and u.user_id = m.member_id
and m.group_id = acs__magic_object_id('registered_users')
and m.rel_id = mr.rel_id
and m.container_id = m.group_id;

-----------------------------------
-- Community Core Initialization --
-----------------------------------

-- The very first thing we must do is create the security_context_root
-- object.

create function inline_0 ()
returns integer as '
declare
  root_id integer;
begin
  
  root_id := acs_object__new (
    0,
    ''acs_object'',
    now(),
    null,
    null,
    null
    );

  insert into acs_magic_objects
   (name, object_id)
  values
   (''security_context_root'', 0);


  return root_id;

end;' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();


-- show errors

begin;

 --------------------------------------------------------------
 -- Some privilege that will be fundamental to all objects. --
 --------------------------------------------------------------

 select acs_privilege__create_privilege('read', null, null);
 select acs_privilege__create_privilege('write', null, null);
 select acs_privilege__create_privilege('create', null, null);
 select acs_privilege__create_privilege('delete', null, null);
 select acs_privilege__create_privilege('admin', null, null);

 ---------------------------------------------------------
 -- Administrators can read, write, create, and delete. -- 
 ---------------------------------------------------------

 -- temporarily drop this trigger to avoid a data-change violation 
 -- on acs_privilege_hierarchy_index while updating the child privileges.

 drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy;

 select acs_privilege__add_child('admin', 'read');
 select acs_privilege__add_child('admin', 'write');
 select acs_privilege__add_child('admin', 'create');

 -- re-enable the trigger before the last insert to force the 
 -- acs_privilege_hierarchy_index table to be updated.

 create trigger acs_priv_hier_ins_del_tr after insert or delete
 on acs_privilege_hierarchy for each row
 execute procedure acs_priv_hier_ins_del_tr ();

 select acs_privilege__add_child('admin', 'delete');

end;


-- show errors

create function inline_2 ()
returns integer as '
declare
  v_object_id integer;
begin

 insert into acs_objects
  (object_id, object_type)
 values
  (-1, ''party'');

 insert into parties
  (party_id)
 values
  (-1);

 insert into acs_magic_objects
  (name, object_id)
 values
  (''the_public'', -1);

  return 0;
end;' language 'plpgsql';

select inline_2 ();

drop function inline_2 ();


create function inline_3 ()
returns integer as '
declare
  group_id integer;
begin

  group_id := acs_group__new (
    -2,
    ''group'',
    now(),
    null,
    null,
    null,
    null,
    ''Registered Users'',
    null,
    null
  );

 insert into acs_magic_objects
  (name, object_id)
 values
  (''registered_users'', -2);

  return 0;
end;' language 'plpgsql';

select inline_3 ();

drop function inline_3 ();


-- show errors