-- 
-- A general permissions facility 
--
-- created by richardl@arsdigita.com on 7/14/99
-- rewritten by michael@arsdigita.com, yon@arsdigita.com & markc@arsdigita.com, 2000-02-25

create sequence gp_id_sequence start 1;

create table general_permissions (
	permission_id		integer not null primary key,
	on_what_id		integer not null,
	on_which_table		varchar(30) not null,
        scope           	varchar(20),
	user_id			integer references users,
	group_id		integer references user_groups,
	role			varchar(200),
	permission_type		varchar(20) not null,
-- This check statment makes the tuple too big for PG 7 ( >8K )
--	check ((scope = 'user' and user_id is not null
--                and group_id is null and role is null) or
--	       (scope = 'group_role' and user_id is null
--                and group_id is not null and role is not null) or
--	       (scope = 'group' and user_id is null
--                and group_id is not null and role is null) or
--	       (scope in ('registered_users', 'all_users')
--                and user_id is null
--                and group_id is null and role is null)),
	unique (on_what_id, on_which_table,
                scope, user_id, group_id, role, permission_type)
);

-- This trigger normalizes values in the on_which_table column to
-- be all lowercase. This makes it easier to implement a case-
-- insensitive API (since function-based indexes do not seem to
-- work as advertised in Oracle 8.1.5). Just make sure to call
-- LOWER whenever constructing a criterion involving
-- on_which_table.
--

create function trig_gp_on_which_table_tr() returns opaque
as '
DECLARE
BEGIN
	NEW.on_which_table:= lower(NEW.on_which_table);
	NEW.permission_type:= lower(NEW.permission_type);
	return NEW;
END;
' language 'plpgsql';

create trigger gp_on_which_table_tr
before insert or update on general_permissions
for each row
execute procedure trig_gp_on_which_table_tr();

-- This trigger normalizes values in the permission_type column to
-- be all lowercase. This makes it easier to implement a case-
-- insensitive API (since function-based indexes do not seem to
-- work as advertised in Oracle 8.1.5). Just make sure to call
-- LOWER whenever constructing a criterion involving
-- permission_type.

-- packaged this in last trigger

-- This view makes it simple to fetch a standard set of
-- permission flags (true or false) for arbitrary rows
-- in the database.
--
create view general_permissions_grid
as
select
 p.on_what_id, p.on_which_table,
 p.scope, p.user_id, p.group_id, p.role,
 case when sum(case when p.permission_type = 'read' then 1 else 0 end)= 0 then 'f'::char else 't'::char end
  as read_permission_p,
 case when sum(case when p.permission_type='comment' then 1 else 0 end)= 0 then 'f'::char else 't'::char end
  as comment_permission_p,
 case when sum(case when p.permission_type= 'write' then 1 else 0 end)= 0 then 'f'::char else 't'::char end
  as write_permission_p,
 case when sum(case when p.permission_type= 'administer' then 1 else 0 end)= 0 then 'f'::char else 't'::char end
 as administer_permission_p
from general_permissions p
group by
 p.on_what_id, p.on_which_table,
 p.scope, p.user_id, p.group_id, p.role;

-- no packages in PG (BMA)
--create or replace package ad_general_permissions
--as
 -- Returns 't' if the specified user has the specified permission on
 -- the specified database row.
 --
-- drop function gp_has_group_role(integer,varchar,integer);
create function gp_has_group_role(integer,varchar,integer) 
returns char as '
declare
        gid     alias for $1;
        role    alias for $2;
        uid     alias for $3;
        exist   char;
begin
        select into exist (case when count(1) is null then ''f'' else
                           case when count(1) = 0 then ''f'' else 
                           ''t'' end end)
          from user_group_map ugm
         where ugm.user_id = uid
           and ugm.group_id = gid
           and ugm.role = role;
           return exist;
end;
' language 'plpgsql';

-- drop function gp_group_member(integer,integer);
create function gp_group_member(integer,integer) returns char as '
declare
        gid     alias for $1;
        uid     alias for $2;
        exist   char;
begin
        select into exist (case when count(1) is null then ''f'' else
                           case when count(1) = 0 then ''f'' else
                           ''t'' end end)
          from user_group_map ugm
         where ugm.user_id = uid
           and ugm.group_id = gid;

        return exist;
end;
' language 'plpgsql';


-- drop function user_has_row_permission_p (integer, varchar, integer, varchar);
create function user_has_row_permission_p (integer, varchar, integer, varchar)
returns char as '
DECLARE
 v_user_id                     alias for $1;
 v_permission_type             alias for $2;
 v_on_what_id                  alias for $3;
 v_on_which_table              alias for $4;
 v_user_has_row_permission_p   integer;
BEGIN
 -- Return true if the permission has been granted to at least one of:
 --
 -- * all users
 -- * registered users if the user is logged in
 -- * the user directly
 -- * a role in a user group that the user plays
 -- * an entire user group of which the user is a member
 --
 select into v_user_has_row_permission_p count(*)
  from general_permissions gp
 where gp.on_what_id = v_on_what_id
   and gp.on_which_table = lower(v_on_which_table)
   and gp.permission_type = lower(v_permission_type)
   and ((gp.scope = ''all_users'')
        or (gp.scope = ''user'' and gp.user_id = v_user_id)
        or (gp.scope = ''registered_users'' and v_user_id > 0)
        or ad_group_member_p(v_user_id,system_administrator_group_id()) = ''t''
        or (gp.scope = ''group''
            and gp_group_member(gp.group_id,v_user_id) = ''t'')
        or (gp.scope = ''group_role''
            and gp_has_group_role(gp.group_id,gp.role,v_user_id) = ''t''));

 return (case when v_user_has_row_permission_p is null then ''f'' else 
         case when v_user_has_row_permission_p = 0 then ''f'' else ''t'' 
         end end);
END;
' language 'plpgsql';


 create function grant_permission_to_user ( integer, varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_user_id		alias for $1;
  v_permission_type	alias for $2;
  v_on_what_id		alias for $3;
  v_on_which_table	alias for $4;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select nextval(''gp_id_sequence'') into v_permission_id;

  insert into general_permissions
   (permission_id, on_what_id, on_which_table,
    scope, user_id, permission_type)
  values
   (v_permission_id, v_on_what_id, v_on_which_table,
    ''user'', v_user_id, v_permission_type);

  return v_permission_id;
 END;
 ' language 'plpgsql';

-- drop function grant_permission_to_role ( integer, varchar, varchar, integer, varchar );
 create function grant_permission_to_role ( integer, varchar, varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_group_id		alias for $1;
  v_role		alias for $2;
  v_permission_type	alias for $3;
  v_on_what_id		alias for $4;
  v_on_which_table	alias for $5;
  v_permission_id	integer;
 BEGIN
  select  into v_permission_id nextval(''gp_id_sequence'');

  insert into general_permissions
   (permission_id, on_what_id, on_which_table,
    scope, group_id, role, permission_type)
  values
   (v_permission_id, v_on_what_id, v_on_which_table,
    ''group_role'', v_group_id, v_role, v_permission_type);

  return v_permission_id;
 END;
 ' language 'plpgsql';

 create function grant_permission_to_group ( integer, varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_group_id		alias for $1;
  v_permission_type	alias for $2;
  v_on_what_id		alias for $3;
  v_on_which_table	alias for $4;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select nextval(''gp_id_sequence'') into v_permission_id;

  insert into general_permissions
   (permission_id, on_what_id, on_which_table,
    scope, group_id, permission_type)
  values
   (v_permission_id, v_on_what_id, v_on_which_table,
    ''group'', v_group_id, v_permission_type);

  return v_permission_id;
 END;
 ' language 'plpgsql';

 create function grant_permission_to_reg_users ( varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_permission_type	alias for $1;
  v_on_what_id		alias for $2;
  v_on_which_table	alias for $3;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select nextval(''gp_id_sequence'') into v_permission_id;

  insert into general_permissions
   (permission_id, on_what_id, on_which_table,
    scope, permission_type)
  values
   (v_permission_id, v_on_what_id, v_on_which_table,
    ''registered_users'', v_permission_type);

  return v_permission_id;
 END;
 ' language 'plpgsql';

-- drop function grant_permission_to_all_users ( varchar, integer, varchar );
 create function grant_permission_to_all_users ( varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_permission_type	alias for $1;
  v_on_what_id		alias for $2;
  v_on_which_table	alias for $3;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select nextval(''gp_id_sequence'') into v_permission_id;

  insert into general_permissions
   (permission_id, on_what_id, on_which_table,
    scope, permission_type)
  values
   (v_permission_id, v_on_what_id, v_on_which_table,
    ''all_users'', v_permission_type);

  return v_permission_id;
 END;
 ' language 'plpgsql';

-- drop function revoke_permission ( integer );
 create function revoke_permission ( integer )
 returns integer as '
 DECLARE
  v_permission_id	alias for $1;
 BEGIN
  delete from general_permissions
  where permission_id = v_permission_id;
  return 0::integer;
 END;
 ' language 'plpgsql';

 create function user_permission_id ( integer, varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_user_id		alias for $1;
  v_permission_type	alias for $2;
  v_on_what_id		alias for $3;
  v_on_which_table	alias for $4;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select permission_id
  into v_permission_id
  from general_permissions
  where on_what_id = v_on_what_id
  and on_which_table = lower(v_on_which_table)
  and scope = ''user''
  and user_id = v_user_id
  and permission_type = lower(v_permission_type);

  if not found then
   return 0;
  else
   return v_permission_id;
  end if;
 END;
 ' language 'plpgsql';

 create function group_role_permission_id ( integer, varchar, varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_group_id		alias for $1;
  v_role		alias for $2;
  v_permission_type	alias for $3;
  v_on_what_id		alias for $4;
  v_on_which_table	alias for $5;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select permission_id
  into v_permission_id
  from general_permissions
  where on_what_id = v_on_what_id
  and on_which_table = lower(v_on_which_table)
  and scope = ''group_role''
  and group_id = v_group_id
  and role = v_role
  and permission_type = lower(v_permission_type);

  if not found then
   return 0;
  else
   return v_permission_id;
  end if;
 END;
 ' language 'plpgsql';

 create function group_permission_id ( integer, varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_group_id		alias for $1;
  v_permission_type	alias for $2;
  v_on_what_id		alias for $3;
  v_on_which_table	alias for $4;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select permission_id
  into v_permission_id
  from general_permissions
  where on_what_id = v_on_what_id
  and on_which_table = lower(v_on_which_table)
  and scope = ''group''
  and group_id = v_group_id
  and permission_type = lower(v_permission_type);

  if not found then
   return 0;
  else
   return v_permission_id;
  end if;
 END;
 ' language 'plpgsql';

 create function reg_users_permission_id ( varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_permission_type	alias for $1;
  v_on_what_id		alias for $2;
  v_on_which_table	alias for $3;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select permission_id
  into v_permission_id
  from general_permissions
  where on_what_id = v_on_what_id
  and on_which_table = lower(v_on_which_table)
  and scope = ''registered_users''
  and permission_type = lower(v_permission_type);

  if not found then
   return 0;
  else
   return v_permission_id;
  end if;
 END;
 ' language 'plpgsql';

 create function all_users_permission_id ( varchar, integer, varchar )
 returns integer as '
 DECLARE
  v_permission_type	alias for $1;
  v_on_what_id		alias for $2;
  v_on_which_table	alias for $3;
  v_permission_id	general_permissions.permission_id%TYPE;
 BEGIN
  select permission_id
  into v_permission_id
  from general_permissions
  where on_what_id = v_on_what_id
  and on_which_table = lower(v_on_which_table)
  and scope = ''all_users''
  and permission_type = lower(v_permission_type);

  if not found then
   return 0;
  else
   return v_permission_id;
  end if;
 END;
 ' language 'plpgsql';

-- This table defines the valid types of permission for each
-- table. Right now, it's only used by the admin pages. We
-- need to figure out if we should use it more broadly.
--
create table general_permission_types (
	table_name	varchar(30) not null,
	permission_type	varchar(20) not null,
	primary key (table_name, permission_type)
);


create view users_view as 
select 
user_id       
,first_names  
,last_name  
,screen_name  
,priv_name       
,email  
,priv_email       
,email_bouncing_p       
,converted_p       
,password   
,url  
,to_char(on_vacation_until,'YYYY-MM-DD') as on_vacation_until     
,to_char(last_visit,'YYYY-MM-DD') as last_visit     
,to_char(second_to_last_visit,'YYYY-MM-DD') as second_to_last_visit     
,n_sessions       
,to_char(registration_date,'YYYY-MM-DD') as registration_date      
,registration_ip    
,user_state   
,to_char(approved_date,'YYYY-MM-DD') as approved_date      
,approving_user        
,approving_note  
,to_char(email_verified_date,'YYYY-MM-DD') as email_verified_date      
,to_char(rejected_date,'YYYY-MM-DD') as rejected_date      
,rejecting_user        
,rejecting_note  
,to_char(deleted_date,'YYYY-MM-DD') as deleted_date      
,deleting_user        
,deleting_note  
,to_char(banned_date,'YYYY-MM-DD') as banned_date      
,banning_user        
,banning_note  
,crm_state    
,to_char(crm_state_entered_date,'YYYY-MM-DD') as crm_state_entered_date      
,lob        
,to_char(portrait_upload_date,'YYYY-MM-DD') as portrait_upload_date      
,portrait_comment  
,portrait_client_file_name   
,portrait_file_type   
,portrait_file_extension    
,portrait_original_width        
,portrait_original_height        
,portrait_thumbnail_width        
,portrait_thumbnail_height        
,bio from users;  


create view users_null as 
select 
''::integer as user_id       
,''::varchar as first_names  
,''::varchar as last_name  
,''::varchar as screen_name  
,''::integer as priv_name       
,''::varchar as email  
,''::integer as priv_email       
,''::char as email_bouncing_p       
,''::char as converted_p       
,''::varchar as password   
,''::varchar as url  
,''::varchar as on_vacation_until     
,''::varchar as last_visit     
,''::varchar as second_to_last_visit     
,''::integer as n_sessions       
,''::varchar as registration_date      
,''::varchar as registration_ip    
,''::varchar as user_state   
,''::varchar as approved_date      
,''::integer as approving_user        
,''::varchar as approving_note  
,''::varchar as email_verified_date      
,''::varchar as rejected_date      
,''::integer as rejecting_user        
,''::varchar as rejecting_note  
,''::varchar as deleted_date      
,''::integer as deleting_user        
,''::varchar as deleting_note  
,''::varchar as banned_date      
,''::integer as banning_user        
,''::varchar as banning_note  
,''::varchar as crm_state    
,''::varchar as crm_state_entered_date      
,''::integer as lob        
,''::varchar as portrait_upload_date      
,''::varchar as portrait_comment  
,''::varchar as portrait_client_file_name   
,''::varchar as portrait_file_type   
,''::varchar as portrait_file_extension    
,''::integer as portrait_original_width        
,''::integer as portrait_original_height        
,''::integer as portrait_thumbnail_width        
,''::integer as portrait_thumbnail_height        
,''::varchar as bio;  

create view user_groups_view as 
select 
group_id      
,group_type  
,group_name 
,short_name 
,admin_email 
,to_char(registration_date,'YYYY-MM-DD') as registration_date    
,creation_user      
,creation_ip_address  
,approved_p      
,active_p      
,existence_public_p      
,new_member_policy  
,spam_policy  
,email_alert_p      
,multi_role_p      
,group_admin_permissions_p      
,index_page_enabled_p      
,body         
,html_p      
,to_char(modification_date,'YYYY-MM-DD') as modification_date     
,modifying_user       
,parent_group_id from user_groups;       


create view user_groups_null as 
select 
''::integer as group_id      
,''::varchar as group_type  
,''::varchar as group_name 
,''::varchar as short_name 
,''::varchar as admin_email 
,''::varchar as registration_date    
,''::integer as creation_user      
,''::varchar as creation_ip_address  
,''::char as approved_p      
,''::char as active_p      
,''::char as existence_public_p      
,''::varchar as new_member_policy  
,''::varchar as spam_policy  
,''::char as email_alert_p      
,''::char as multi_role_p      
,''::char as group_admin_permissions_p      
,''::char as index_page_enabled_p      
,''::text as body         
,''::char as html_p      
,''::varchar as modification_date     
,''::integer as modifying_user       
,''::integer as parent_group_id;