-- packages/acs-events/sql/recurrence-create.sql
--
-- Support for temporal recurrences
--
-- @author W. Scott Meeks
--
-- $Id: recurrence-create.sql,v 1.5 2018/03/25 20:56:30 hectorr Exp $

-- Sequence for recurrence tables
create sequence recurrence_sequence start 1;

-- These columns describe how an event recurs.  The are modeled on the Palm DateBook.
-- The interval_type 'custom' indicates that the PL/SQL function referenced in
-- custom_func should be used to generate the recurrences.
create table recurrence_interval_types (
    interval_type   integer
                    constraint recurrence_interval_type_pk primary key,
    interval_name   varchar(50) not null
                    constraint rit_interval_name_un unique
);

-- Magic values
insert into recurrence_interval_types values (1,'day');
insert into recurrence_interval_types values (2,'week');
insert into recurrence_interval_types values (3,'month_by_date');
insert into recurrence_interval_types values (4,'month_by_day');
insert into recurrence_interval_types values (5,'last_of_month');
insert into recurrence_interval_types values (6,'year');
insert into recurrence_interval_types values (7,'custom');

-- Main table
create table recurrences (
    recurrence_id        integer
                         constraint recurrences_pk 
			 primary key,
    --
    -- Indicate the interval type for recurrence (see above)
    --
    -- JS: Postgres does not like it if the data type of the 
    -- JS: column is not specified.
    interval_type        integer
			 constraint recurs_interval_type_fk
                         references recurrence_interval_types
			 constraint recurs_interval_type_nn			     
			 not null,
    --
    -- Indicates how many of the given intervals between recurrences.
    -- Must be a positive number!
    --
    every_nth_interval   integer
                         constraint recurs_every_nth_interval_ck
                         check(every_nth_interval > 0),
    --
    -- If recurring on a weekly basis (interval_type = 'week')
    -- indicates which days of the week the event recurs on.
    -- This is represented as a space separated list of numbers
    -- corresponding to days of the week, where 0 corresponds to
    -- Sunday, 1 to Monday, and so on.  Null indicates no days are set.  
    -- So for example, '1' indicates recur on Mondays, '3 5' indicates
    -- recur on Wednesday and Friday.
    --
    days_of_week         varchar(20),
    --
    -- Indicates when this event should stop recurring.  Null indicates
    -- recur indefinitely.
    --
    recur_until          timestamptz,
    --
    -- Recurring events can be only partially populated if fully populating
    -- the events would require inserting too many instances.  This
    -- column indicates up to what date this event has recurred.  This
    -- allows further instances to be added if the user attempts to view
    -- a date beyond db_populated_until.  If recur_until is not null, 
    -- then this column will always be prior to or the same as recur_until.
    -- This column will be null until some recurrences have been added.
    --
    db_populated_until   timestamptz,
    --
    -- This column holds the name of a PL/SQL function that will be called
    -- to generate dates of recurrences if interval_type is 'custom'
    --
    custom_func          varchar(255)
);

-- This is important to prevent locking on update of master table.
-- See  http://www.arsdigita.com/bboard/q-and-a-fetch-msg.tcl?msg_id=000KOh
create index recurrences_interval_type_idx on recurrences(interval_type);

comment on table recurrences is '
    Describes how an event recurs.
';

comment on column recurrences.interval_type is '
    One of day, week, month_by_date, month_by_day, last_of_month, year, custom.
';

comment on column recurrences.every_nth_interval is '
    Indicates how many of the given intervals between recurrences.
';

comment on column recurrences.days_of_week is '
    For weekly recurrences, stores which days of the week the event recurs on.
';

comment on column recurrences.recur_until is '
    Indicates when this event should stop recurring.  Null indicates
    recur indefinitely.
';
        
comment on column recurrences.db_populated_until is '
    Indicates the date of the last recurrence added. Used to determine if more
    recurrences need to be added.
';

comment on column recurrences.custom_func is '
    Stores the name of a PL/SQL function that can be called to generate dates
    for special recurrences.
';

-- Recurrence API
--
-- Currently supports only new and delete methods.
--



-- added
select define_function_args('recurrence__new','interval_name,every_nth_interval,days_of_week;null,recur_until;null,custom_func;null');

--
-- procedure recurrence__new/5
--
     --
     -- Creates a new recurrence
     --
     -- @author W. Scott Meeks
     --
     -- @param interval_type        Sets interval_type of new recurrence
     -- @param every_nth_interval   Sets every_nth_interval of new recurrence
     -- @param days_of_week         Sets days_of_week of new recurrence
     -- @param recur_until          Sets recur_until of new recurrence
     -- @param custom_func          Sets name of custom recurrence function
     --                                  
     -- @return id of new recurrence
     --

CREATE OR REPLACE FUNCTION recurrence__new(
   new__interval_name varchar,
   new__every_nth_interval integer,
   new__days_of_week varchar,    -- default null,
   new__recur_until timestamptz, -- default null,
   new__custom_func varchar      -- default null

) RETURNS integer AS $$
DECLARE
       v_recurrence_id		  recurrences.recurrence_id%TYPE;
       v_interval_type_id	  recurrence_interval_types.interval_type%TYPE;
BEGIN

       select nextval('recurrence_sequence') into v_recurrence_id from dual;
        
       select interval_type
       into   v_interval_type_id 
       from   recurrence_interval_types
       where  interval_name = new__interval_name;
        
       insert into recurrences
            (recurrence_id, 
             interval_type, 
             every_nth_interval, 
             days_of_week,
             recur_until, 
             custom_func)
       values
            (v_recurrence_id, 
             v_interval_type_id, 
             new__every_nth_interval, 
             new__days_of_week,
             new__recur_until, 
             new__custom_func);
         
       return v_recurrence_id;

END;
$$ LANGUAGE plpgsql; 




-- added
select define_function_args('recurrence__delete','recurrence_id');

--
-- procedure recurrence__delete/1
--
     --
     -- Deletes the recurrence
     -- Note: this will fail if there are any events_with this recurrence 
     -- because of foreign key constraints.  use acs-events__delete instead
     --
     -- @author W. Scott Meeks
     --
     -- @param recurrence_id id of recurrence to delete
     --
     -- @return 0 (procedure dummy)
     --

CREATE OR REPLACE FUNCTION recurrence__delete(
   delete__recurrence_id integer
) RETURNS integer AS $$
DECLARE 
BEGIN
       delete from recurrences
       where  recurrence_id = delete__recurrence_id;

       return 0;
END;
$$ LANGUAGE plpgsql;