-- creates the calendar object
--
-- @author Gary Jin (gjin@arsdigita.com)
-- @creation-date Nov 17, 2000
-- @cvs-id $Id: calendar-create.sql,v 1.21 2024/09/03 15:37:35 gustafn Exp $
--
-- ported by Charles Mok (mok_cl@eelab.usyd.edu.au)

------------------------------------------------------------------
-- calendar system permissions
------------------------------------------------------------------

  -- creating the basic set of permissions for cal_item
  --
  -- 1  create: create a new item
  -- 2. read: can view the cal_item
  -- 3. write: edit an existing cal_item
  -- 4. delete: can delete the cal_item
  -- 5. invite: can allow other parties to view or edit the cal_item



  select acs_privilege__create_privilege('cal_item_create', 'Add a new item', null);
  select acs_privilege__create_privilege('cal_item_read',   'view an cal_item', null);
  select acs_privilege__create_privilege('cal_item_write',  'Edit an existing cal_item', null);
  select acs_privilege__create_privilege('cal_item_delete', 'Delete cal_item', null );
  select acs_privilege__create_privilege('cal_item_invite', 'Allow others to view cal_item', null);

  select acs_privilege__add_child('create', 'cal_item_create');
  select acs_privilege__add_child('read', 'cal_item_read');
  select acs_privilege__add_child('write', 'cal_item_write');

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

  select acs_privilege__create_privilege('calendar_on', 'Implies that a calendar is selected', null);
  select acs_privilege__create_privilege('calendar_show', 'Show a calendar', null);

  select acs_privilege__add_child('read', 'calendar_on');
  select acs_privilege__add_child('read', 'calendar_show');

  select acs_privilege__create_privilege('calendar_create', 'Create a new calendar', null);
  select acs_privilege__create_privilege('calendar_read', 'View items on an existing calendar', null);
  select acs_privilege__create_privilege('calendar_write', 'Edit items of an existing calendar', null);
  select acs_privilege__create_privilege('calendar_delete','Delete an calendar', null);

  select acs_privilege__add_child('create', 'calendar_create');
  select acs_privilege__add_child('read', 'calendar_read');
  select acs_privilege__add_child('write', 'calendar_write');
  select acs_privilege__add_child('delete', 'calendar_delete');

  select acs_privilege__add_child('calendar_create', 'cal_item_create');
  select acs_privilege__add_child('calendar_read', 'cal_item_read');
  select acs_privilege__add_child('calendar_write', 'cal_item_write');
  select acs_privilege__add_child('calendar_delete', 'cal_item_delete');

  select acs_privilege__create_privilege('calendar_admin', 'calendar administrator', null);
  select acs_privilege__add_child('admin', 'calendar_admin');
  select acs_privilege__add_child('calendar_admin', 'calendar_read');
  select acs_privilege__add_child('calendar_admin', 'calendar_write');
  select acs_privilege__add_child('calendar_admin', 'calendar_delete');
  select acs_privilege__add_child('calendar_admin', 'calendar_create');
  select acs_privilege__add_child('calendar_admin', 'cal_item_invite');

----------------------------------------------------------
--  calendar_object
-----------------------------------------------------------

create or replace function inline_0(
) returns integer AS $$
declare
	attr_id acs_attributes.attribute_id%TYPE;
begin
	PERFORM
	    acs_object_type__create_type(
		'calendar',	-- object_type
		'Calendar',	-- pretty_name
		'Calendar',	-- pretty_plural
		'acs_object',	-- supertype
		'calendars',	-- table_name
		'calendar_id',  -- id_column
		null,		-- package_name
		'f',		-- abstract_p
		null,		-- type_extension_table
		null		-- name_method
	    );

	    attr_id := acs_attribute__create_attribute (
		'calendar',       -- object_type
		'owner_id',       -- attribute_name
		'integer',        -- datatype
		'Owner',          -- pretty_name
		'Owners',         -- pretty_plural
		null,             -- table_name (default)
		null,             -- column_name (default)
		null,             -- default_value (default)
		1,                -- min_n_values (default)
		1,                -- max_n_values (default)
		null,             -- sort_order (default)
		'type_specific',  -- storage (default)
		'f'               -- static_p (default)
	    );

	    attr_id := acs_attribute__create_attribute (
		'calendar',          -- object_type
		'private_p',         -- attribute_name
		'boolean',           -- datatype
		'Private Calendar',  -- pretty_name
		'Private Calendars', -- pretty_plural
		null,                -- table_name (default)
		null,                -- column_name (default)
		null,                -- default_value (default)
		1,                   -- min_n_values (default)
		1,                   -- max_n_values (default)
		null,                -- sort_order (default)
		'type_specific',     -- storage (default)
		'f'                  -- static_p (default)
	    );

	    attr_id := acs_attribute__create_attribute (
		'calendar',          -- object_type
		'calendar_name',     -- attribute_name
		'string',            -- datatype
		'Calendar Name',     -- pretty_name
		'Calendar Names',    -- pretty_plural
		null,                -- table_name (default)
		null,                -- column_name (default)
		null,                -- default_value (default)
		1,                   -- min_n_values (default)
		1,                   -- max_n_values (default)
		null,                -- sort_order (default)
		'type_specific',     -- storage (default)
		'f'                  -- static_p (default)
	    );
	    return 0;

end;
$$ LANGUAGE plpgsql;

SELECT inline_0();

DROP function inline_0();

-- Calendar is a collection of events. Each calendar must
-- belong to somebody (a party).
create table calendars (
	  -- primary key
	calendar_id             integer
				constraint calendars_calendar_id_fk
				references acs_objects
				constraint calendars_calendar_id_pk
				primary key,
	  -- the name of the calendar
	calendar_name           varchar(200),
	  -- the individual or party that owns the calendar
	owner_id                integer
				constraint calendars_calendar_owner_id_fk
				references parties
				on delete cascade,
	  -- keep track of package instances
	package_id              integer
				constraint calendars_package_id_fk
				references apm_packages(package_id)
				on delete cascade,
	  -- whether or not the calendar is a private personal calendar or a
	  -- public calendar.
	private_p               boolean
				default 'f'
				constraint calendars_private_p_ck
				check (private_p in (
					't',
					'f'
					)
				)
);

comment on table calendars is '
	Table calendars maps the many to many relationship betweens
	calendar and its owners.
';

comment on column calendars.calendar_id is '
	Primary Key
';

comment on column calendars.calendar_name is '
	the name of the calendar. This would be unique to avoid confusion
';

comment on column calendars.owner_id is '
	the individual or party that owns the calendar
';

comment on column calendars.package_id is '
	keep track of package instances
';

-- create a partial index on public calendars
create index calendars_package_id_pidx on calendars(package_id) where private_p = false;



-- Calendar Item Types

create sequence cal_item_type_seq;

create table cal_item_types (
       item_type_id              integer not null
				 constraint cal_item_type_id_pk
				 primary key,
       calendar_id               integer not null
				 constraint cal_item_type_cal_id_fk
				 references calendars(calendar_id),
       type                      varchar(100) not null,
       -- this constraint is obvious given that item_type_id
       -- is unique, but it's necessary to allow strong
       -- references to the pair calendar_id, item_type_id (ben)
       constraint cal_item_types_un
       unique (calendar_id, item_type_id)
);

-------------------------------------------------------------
-- Load cal_item_object
-------------------------------------------------------------
\i cal-item-create.sql
-------------------------------------------------------------
-- create package calendar
-------------------------------------------------------------

select define_function_args ('calendar__new', 'calendar_id,calendar_name,object_type;calendar,owner_id,private_p,package_id,context_id,creation_date,creation_user,creation_ip');

--
-- procedure calendar__new/10
--

create or replace function calendar__new(
   new__calendar_id integer,
   new__calendar_name varchar(200),
   new__object_type varchar, -- default 'calendar'
   new__owner_id integer,
   new__private_p boolean,
   new__package_id integer,
   new__context_id integer,
   new__creation_date timestamptz,
   new__creation_user integer,
   new__creation_ip varchar

) returns integer AS $$
declare
	v_calendar_id           calendars.calendar_id%TYPE;
begin
	v_calendar_id := acs_object__new(
		new__calendar_id,
		new__object_type,
		new__creation_date,
		new__creation_user,
		new__creation_ip,
		new__context_id
	);

	insert into     calendars
			(calendar_id, calendar_name, owner_id, package_id, private_p)
	values          (v_calendar_id, new__calendar_name, new__owner_id, new__package_id, new__private_p);

	PERFORM acs_permission__grant_permission (
	      v_calendar_id,
	      new__owner_id,
	      'calendar_admin'
	);


	return v_calendar_id;
end;
$$ LANGUAGE plpgsql;


--
-- procedure calendar__delete/1
--

select define_function_args('calendar__delete','calendar_id');

create or replace function calendar__delete(
       integer            -- calendar.calendar_id%TYPE
) returns integer AS $$
declare
	delete__calendar_id		alias for $1;
begin
	delete from calendars
	where calendar_id = delete__calendar_id;

	-- Delete all privileges associate with this calendar

	delete from     acs_permissions
	where           object_id = delete__calendar_id;

        delete from     acs_permissions
	where           object_id in (
				select  cal_item_id
				from    cal_items
				where   on_which_calendar = delete__calendar_id
			);

	PERFORM acs_object__delete(delete__calendar_id);

    return 0;
end;
$$ LANGUAGE plpgsql;



-----------------------------------------------------------------
-- load related sql files
-----------------------------------------------------------------
--\i cal-item-create.sql
--
\i cal-table-create.sql

\i calendar-notifications-init.sql
