-- /packages/ticket-tracker-lite/sql/views-and-plsql.sql
--
-- @author dvr@arsdigita.com
-- @created 2001-02-18
--
---------------------------------------------------

-- All areas

create view ttl_v_areas as
select o.*, a.*
from acs_objects o, ttl_areas a
where o.object_id = a.area_id;

-- All active areas

create view ttl_va_areas as
select * from ttl_v_areas where active_p = 't';

-- All tickets

create view ttl_v_tickets as 
select o.*, t.* 
from acs_objects o, ttl_tickets t
where t.ticket_id = o.object_id;

-- All active tickets

create view ttl_va_tickets as 
select * from ttl_v_tickets where active_p = 't';

-- All active comments

create view ttl_va_comments
as select * from ttl_comments where active_p = 't';

-- The time of the last comment for each ticket

create view ttl_v_comments_latest as 
select * from ttl_va_comments c
where c.comment_id = (select max(c2.comment_id) 
                      from ttl_comments c2
                      where c.ticket_id = c2.ticket_id);

-- All users who assignable to tickets

create view ttl_v_users as
select 
    object_id as package_id,
    grantee_id as user_id,
    privilege
from acs_permissions
where privilege = 'write';

    --
    -- if the date is today, show just the time. Otherwise 
    -- return the date.
    --
create function ttl_relative_date (timestamptz)
returns varchar as '
declare
  relative_date     alias for $1;
begin
  if date_trunc(''day'', relative_date) = date_trunc(''day'', current_timestamp) then
      return to_char(relative_date, ''fmhh:mi am'');
  else 
      return to_char(relative_date, ''Month fmdd, yyyy'');
  end if;
end;' language 'plpgsql';

    --
    -- Return the top privilege the user has for this object
    -- At the moment this is either 'admin' or 'write'
    --
create function ttl_top_privilege_for_object (integer, integer)
returns varchar as '
declare
  p_object_id          alias for $1;
  p_user_id            alias for $2;
  v_privilege        acs_privileges.privilege%TYPE default '''';
  v_admin_p          integer;
begin
    select count(*) into v_admin_p
    where acs_permission__permission_p(p_object_id, p_user_id, ''admin'') = ''t'';

    if v_admin_p = 1 then
        v_privilege := ''admin'';
    else 
        select privilege into v_privilege
        from acs_permissions
        where object_id = p_object_id
        and grantee_id = p_user_id
        and privilege = ''write'';
    end if;

    return v_privilege;
end;' language 'plpgsql';

create function ttl__create_file(integer, integer, varchar, integer, varchar, varchar)
returns integer as '
declare
  p_package_id alias for $1;
  p_user_id alias for $2;
  p_name alias for $3;
  p_parent_id alias for $4;
  p_mime_type alias for $5;
  p_file_extension alias for $6;
  v_item_id integer;
  v_revision_id integer;
  v_mime_type varchar;
begin

-- detect mime type if mime is not yet in cr_mime_type and add it
  select mime_type into v_mime_type from cr_mime_types
    	where  mime_type = p_mime_type;
 
  if not found then
    insert into cr_mime_types (mime_type, file_extension) values (p_mime_type, p_file_extension);
  end if;

-- if the file already exists then nuke it. this become essentially write
  select item_id into v_item_id from cr_items where name = p_name;
  if found then 
    perform content_item__delete(v_item_id);
  end if;

-- create a new cr_item
  v_item_id := acs_object__new(null, ''content_item'', now(), p_user_id, null, p_package_id);
  insert into cr_items (
    item_id, parent_id, name, content_type, storage_type
  ) values (
    v_item_id, p_parent_id, p_name, ''content_revision'', ''file''
  );

-- create a new cr_revision
  v_revision_id := acs_object__new(null, ''content_revision'', now(), p_user_id, null, null);
  insert into cr_revisions (revision_id, item_id, mime_type) 
  values (v_revision_id, v_item_id, p_mime_type);

  return v_revision_id;
end;
' language 'plpgsql';