-- packages/acs-events/sql/timespan-create.sql
--
-- This script defines the data models and API for both time_interval and timespan.
--
-- @author smeeks@arsdigita.com
-- @creation-date 2000-11-16
--
-- $Id: timespan-create.sql,v 1.4 2006/08/08 21:26:13 donb Exp $

create sequence timespan_sequence start  1;
create view timespan_seq as select nextval('timespan_sequence') as nextval from dual;

-- Table for storing time intervals.  Note that time intervals can be open on 
-- either end.  This is represented by a null value for start_date or end_date.
-- Applications can determine how to interpret null values.  However, this is 
-- the default interpretation used by the overlaps_p functions. A null value
-- for start_date is treated as extending to the beginning of time.  A null
-- value for end_date is treated as extending to the end of time.  The net effect
-- is that an interval with an open start overlaps any interval whose start
-- is before the end of the interval with the open start.  Likewise, an interval
-- with an open end overlaps any interval whose end is after the start of the
-- interval with the open end.
create table time_intervals (
    interval_id         integer
                        constraint time_intervals_pk
                        primary key,
    start_date          timestamptz,
    end_date            timestamptz,
    constraint time_interval_date_order_ck
    check(start_date <= end_date)
);

create index time_intervals_start_idx on time_intervals(start_date);

comment on table time_intervals is '
    A time interval is represented by two points in time.
';      

-- API:
--
--       new        (start_date, end_date)
--       delete     ()
--
--       edit       (start_date, end_date)
--
--       shift      (start_offset, end_offset)
--
--       overlaps_p (interval_id) 
--       overlaps_p (start_date, end_date)
--


create function time_interval__new (
       -- 
       -- Creates a new time interval
       --
       -- @author W. Scott Meeks
       --
       -- @param start_date   Sets this as start_date of new interval
       -- @param end_date     Sets this as end_date of new interval
       --
       -- @return id of new time interval
       --
       timestamptz,     -- time_intervals.start_date%TYPE default null,
       timestamptz      -- time_intervals.end_date%TYPE default null
) 
returns integer as '    -- time_intervals.interval_id%TYPE
declare
       new__start_date  alias for $1; -- default null,
       new__end_date    alias for $2; -- default null
       v_interval_id     time_intervals.interval_id%TYPE;
begin
       select timespan_seq.nextval into v_interval_id from dual;

       insert into time_intervals 
            (interval_id, start_date, end_date)
       values
            (v_interval_id, new__start_date, new__end_date);
                
       return v_interval_id;

end;' language 'plpgsql'; 


create function time_interval__delete (
       --
       -- Deletes the given time interval
       --
       -- @author W. Scott Meeks
       --
       -- @param interval_id  id of the interval to delete
       --
       -- @return 0 (procedure dummy)
       --
       integer          -- time_intervals.interval_id%TYPE
)
returns integer as '
declare
       delete__interval_id      alias for $1;
begin
        
       delete from time_intervals
       where  interval_id = delete__interval_id;

       return 0;

end;' language 'plpgsql'; 

 
create function time_interval__edit (
       -- 
       -- Updates the start_date or end_date of an interval
       --
       -- @author W. Scott Meeks
       --
       -- @param interval_id  id of the interval to update
       -- @param start_date   Sets this as the new 
       --                     start_date of the interval.
       -- @param end_date     Sets this as the new 
       --                     end_date of the interval.
       --
       -- @return 0 (procedure dummy)
       --
       integer,             -- time_intervals.interval_id%TYPE,
       timestamptz,         -- time_intervals.start_date%TYPE default null,
       timestamptz          -- time_intervals.end_date%TYPE default null
)
returns integer as '
declare
        edit__interval_id     alias for $1;
        edit__start_date      alias for $2; -- default null,
        edit__end_date        alias for $3; -- default null
begin

        -- JS: I hate deeply nested if-else-ifs!!! 

        -- Null for start_date or end_date means dont change.
        if edit__start_date is not null and edit__end_date is not null then
            update time_intervals
            set    start_date  = edit__start_date,
                   end_date    = edit__end_date
            where  interval_id = edit__interval_id;
        end if;

        -- Update only the end date if not null even if start date is null
        if edit__start_date is not null and edit__end_date is null 
        then
            update time_intervals
            set    start_date  = edit__start_date
            where  interval_id = edit__interval_id;
        end if;
            
        -- Update only the start date if not null even if end date is null
        if edit__end_date is not null and edit__start_date is null 
        then
                 update time_intervals
                 set end_date       = edit__end_date
                 where interval_id  = edit__interval_id;
        end if;

        return 0;

end;' language 'plpgsql'; 


create function time_interval__shift (
       --
       -- Updates the start_date or end_date of an interval based on offsets (general)
       --
       -- @author W. Scott Meeks
       -- @param interval_id  The interval to update.
       -- @param start_offset Adds this date interval to the
       --                     start_date of the interval.  No effect if 
       --                     start_date is null.
       -- @param end_offset   Adds this date interval to the
       --                     end_date of the interval.  No effect if 
       --                     end_date is null.
       --
       -- @return 0 (procedure dummy)
       --
       integer,              -- time_intervals.interval_id%TYPE,
       interval,                     
       interval         
)
returns integer as '
declare 
       shift__interval_id      alias for $1;
       shift__start_offset     alias for $2; -- default 0,
       shift__end_offset       alias for $3; -- default 0
begin
       update time_intervals
       set    start_date = start_date + shift__start_offset,
              end_date   = end_date + shift__end_offset
       where  interval_id = shift__interval_id;

       return 0;

end;' language 'plpgsql'; 


create function time_interval__shift (
       --
       -- Updates the start_date or end_date of an interval based on offsets of
       -- fractional days.
       --
       -- JS: Overloaded function to make above compatible with Oracle behavior
       -- JS: when an integer (for number of days) is supplied as a parameter.
       --
       -- @param interval_id  The interval to update.
       -- @param start_offset Adds this number of days to the
       --                     start_date of the interval.  No effect if 
       --                     start_date is null.
       -- @param end_offset   Adds this number of days to the
       --                     end_date of the interval.  No effect if 
       --                     end_date is null.
       --
       -- @return 0 (procedure dummy)
       --
       integer,              -- time_intervals.interval_id%TYPE,
       integer,              
       integer          
)
returns integer as '
declare 
      shift__interval_id      alias for $1;
      shift__start_offset     alias for $2; -- default 0,
      shift__end_offset       alias for $3; -- default 0
begin

      return time_interval__shift(
                 shift__interval_id,
                 to_interval(shift__start_offset,''days''),
                 to_interval(shift__end_offset,''days'')
                 );

end;' language 'plpgsql';


create function time_interval__overlaps_p (
       -- 
       -- Returns true if the two intervals overlap, false otherwise.
       --
       -- @author W. Scott Meeks
       --
       -- @param interval_1_id
       -- @param interval_2_id
       --
       -- @return true if the two intervals overlap, false otherwise.
       --
       integer,         -- time_intervals.interval_id%TYPE,
       integer          -- time_intervals.interval_id%TYPE
)
returns boolean as '
declare
       overlaps_p__interval_id_1   alias for $1;
       overlaps_p__interval_id_2   alias for $2;
       v_start_1                   timestamptz;
       v_start_2                   timestamptz;
       v_end_1                     timestamptz;
       v_end_2                     timestamptz;
begin
       -- Pull out the start and end dates and call the main overlaps_p.
       select start_date, end_date
       into   v_start_1, v_end_1
       from   time_intervals
       where  interval_id = overlaps_p__interval_id_1;

       select start_date, end_date
       into   v_start_2, v_end_2
       from   time_intervals
       where  interval_id = overlaps_p__interval_id_2;

       return time_interval__overlaps_p(
                  v_start_1, 
                  v_end_1, 
                  v_start_2, 
                  v_end_2
                  );

end;' language 'plpgsql'; 


create function time_interval__overlaps_p (
       --
       -- Returns true if the interval bounded by the given start_date or
       -- end_date overlaps the given interval, false otherwise.
       --
       -- @author W. Scott Meeks
       --
       -- @param start_date  See if it overlaps the interval starting from this date.
       -- @param end_date    See if it overlaps the interval ending on this date.
       --
       -- @return true if the interval bounded by start_date through end_date, false otherwise.
       --
       integer,         -- time_intervals.interval_id%TYPE,
       timestamptz,     -- time_intervals.start_date%TYPE default null,
       timestamptz      -- time_intervals.end_date%TYPE default null
)
returns boolean as '
declare
       overlaps_p__interval_id     alias for $1; 
       overlaps_p__start_date      alias for $2; -- default null,
       overlaps_p__end_date        alias for $3; -- default null
       v_interval_start            time_intervals.start_date%TYPE;
       v_interval_end              time_intervals.end_date%TYPE;
begin
       -- Pull out the start and end date and call the main overlaps_p.
       select start_date, end_date
       into   v_interval_start, v_interval_end
       from   time_intervals
       where  interval_id = overlaps_p__interval_id;

       return time_interval__overlaps_p(
                  v_interval_start, 
                  v_interval_end, 
                  overlaps_p__start_date, 
                  overlaps_p__end_date
                  );

end;' language 'plpgsql'; 


create function time_interval__overlaps_p (
       --
       -- Checks if two intervals overlaps
       -- JS:  There is a simpler way to evaluate whether intervals overlap, 
       -- JS:  so this function can be optimized.
       --
       -- @author W. Scott Meeks
       --
       -- @param interval_1_id First interval
       -- @param interval_2_id Second interval
       -- 
       -- @return true if intervals overlap, otherwise false.
       --
       timestamptz,     -- time_intervals.start_date%TYPE,
       timestamptz,     -- time_intervals.end_date%TYPE,
       timestamptz,     -- time_intervals.start_date%TYPE,
       timestamptz      -- time_intervals.end_date%TYPE
)
returns boolean as '
declare
       overlaps_p__start_1      alias for $1;
       overlaps_p__end_1        alias for $2;
       overlaps_p__start_2      alias for $3;
       overlaps_p__end_2        alias for $4;
begin

       -- JS: Modified yet another deeply nested if-else-if
       -- JS: Note that null date is the representation for infinite 
       -- (positive or negative) time. 
       if overlaps_p__start_1 is null 
       then
            -- No overlap if 2nd interval starts after 1st ends
            if overlaps_p__end_1 < overlaps_p__start_2 
            then
                return false;
            else
                return true;
            end if;
       end if;

       if overlaps_p__start_2 is null 
       then
            -- No overlap if 2nd interval ends before 1st starts
            if overlaps_p__end_2 < overlaps_p__start_1 
            then
                return false;
            else
                return true;
            end if;
       end if;

       -- Okay, both start dates are not null
       if overlaps_p__start_1 <= overlaps_p__start_2 
       then
            -- 1st starts before 2nd
            if overlaps_p__end_1 < overlaps_p__start_2 
            then
                  -- No overlap if 1st ends before 2nd starts
                  return false;
            else
                 -- No overlap or at least one null
                 return true;
            end if;

       else

            -- 1st starts after 2nd
            if overlaps_p__end_2 < overlaps_p__start_1 
            then
                 -- No overlap if 2nd ends before 1st starts
                 return false;
            else
                 -- No overlap or at least one null
                 return true;
            end if;

       end if;

end;' language 'plpgsql'; 


create function time_interval__eq (
       --
       -- Checks if two intervals are equal
       --
       -- @author W. Scott Meeks
       --
       -- @param interval_1_id First interval
       -- @param interval_2_id Second interval
       -- 
       -- @return true if intervals are equal, otherwise false.
       --
       integer,         -- time_intervals.interval_id%TYPE,
       integer          -- time_intervals.interval_id%TYPE
)
returns boolean as '    --  return boolean
declare
       eq__interval_1_id   alias for $1;
       eq__interval_2_id   alias for $2;
       interval_1_row time_intervals%ROWTYPE;
       interval_2_row time_intervals%ROWTYPE;
begin
       select * into interval_1_row
       from   time_intervals
       where  interval_id = eq__interval_1_id;

       select * into interval_2_row
       from   time_intervals
       where  interval_id = eq__interval_2_id;

       if interval_1_row.start_date = interval_2_row.start_date and 
          interval_1_row.end_date = interval_2_row.end_date 
       then
            return true;
       else
            return false;
       end if;

end;' language 'plpgsql'; 


create or replace function time_interval__copy(
       --
       -- Creates a new copy of a time interval, offset by optional offset
       --
       -- JS: We need to be careful in interpreting the copy offset.
       -- JS: Oracle interprets integers as full days when doing
       -- JS: date arithmetic.  Thus,
       -- JS: 
       -- JS:    select sysdate()+1 from dual;
       -- JS:
       -- JS: will yield the next date, correct up to the second of the next day
       -- JS: that the query was run.  
       -- JS: 
       -- JS: In PostgreSQL, we need to specify the type of interval when
       -- JS: doing date arithmetic.  if, say, an integer is used in date arithmetic, 
       -- JS: the results are weird.  For example, 
       -- JS:
       -- JS:    select now()+1 from dual;
       -- JS:
       -- JS: will yield the MIDNIGHT of the next date that the query was run, i.e.,
       -- JS: the timestamp is typecasted as a date with a day granularity. To get the 
       -- JS: same effect as Oracle, we need to use explicitly typecast the integer into 
       -- JS: a day interval. 
       --
       -- @author W. Scott Meeks
       --
       -- @param interval_id   Interval to copy
       -- @param offset        Interval is offset by this date interval
       --
       -- @return interval_id of the copied interval
       --
       integer,         -- time_intervals.interval_id%TYPE,
       interval        
)
returns integer as '    -- time_intervals.interval_id%TYPE
declare    
       copy__interval_id     alias for $1;
       copy__offset          alias for $2; -- default 0
       interval_row           time_intervals%ROWTYPE;
       v_foo                 timestamptz;
begin
       select * into interval_row
       from   time_intervals
       where  interval_id = copy__interval_id;
	
       return time_interval__new(
                  (interval_row.start_date ::timestamp + copy__offset) :: timestamptz,
                  (interval_row.end_date ::timestamp + copy__offset) :: timestamptz
                  );

end;' language 'plpgsql'; 

create function time_interval__copy (
       --
       -- Creates a new copy of a time interval.
       -- JS: Overloaded versaion of above, no offset
       --
       -- @param interval_id   Interval to copy
       --
       -- @return interval_id of the copied interval
       --
       integer
)
returns integer as '    -- return time_intervals.interval_id%TYPE
declare    
       copy__interval_id     alias for $1;
       v_query               varchar;
       v_result              time_intervals.interval_id%TYPE;
       rec_datecalc          record;
begin
        return time_interval__copy(
                   copy__interval_id,
                   interval ''0 days''
                   );

end;' language 'plpgsql';


create function time_interval__copy(
       --
       -- Creates a new copy of a time interval, offset by optional offset
       --
       -- JS: Overloaded function to make above compatible with Oracle behavior
       -- JS: when an integer (for number of days) is supplied as a parameter.
       --
       -- @param interval_id   Interval to copy
       -- @param offset        Interval is offset by this number of days
       --
       -- @return interval_id of the copied interval
       --
       integer,         -- time_intervals.interval_id%TYPE,
       integer
)
returns integer as '    -- time_intervals.interval_id%TYPE
declare    
       copy__interval_id     alias for $1;
       copy__offset          alias for $2; -- default 0
begin

       return time_interval__copy(
                  copy__interval_id,
                  to_interval(copy__offset,''days'')
                  );

end;' language 'plpgsql';







-- Timespans table. A timespan is a set of intervals.  This table contains
-- mappings of intervals into a set that comprises a timespan.
create table timespans (           
    -- Can't be primary key because of the one to many relationship with
    -- interval_id, but we can declare it not null and index it.
    timespan_id     integer not null,
    interval_id     integer
                    constraint tm_ntrvl_sts_interval_id_fk
                    references time_intervals on delete cascade
);

create index timespans_idx on timespans(timespan_id);

-- 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
-- JS: Not sure if this applies to PostgreSQL, but an index cant hurt, can it?
create index timespans_interval_id_idx on timespans(interval_id);

comment on table timespans is '
    Establishes a relationship between timespan_id and multiple time
    intervals.  Represents a range of moments at which an event can occur.
';


-- TimeSpan API
--
-- Quick reference for the API supported for timespans.  All procedures take timespan_id
-- as the first argument (not shown explicitly):
-- 
--     new          (interval_id)
--     new          (start_date, end_date)
--     delete       ()
--
-- Methods to join additional time intervals with an existing timespan:
--
--     join          (timespan_id)
--     join_interval (interval_id)
--     join          (start_date, end_date)
--
--     interval_delete (interval_id)
--     interval_list   ()
--
-- Tests for overlap:
-- 
--     overlaps_p   (timespan_id)
--     overlaps_p   (interval_id)
--     overlaps_p   (start_date, end_date)
--
-- Info:
--
--         exists_p                     ()
--     multi_interval_p ()


create function timespan__new (
       --
       -- Creates a new timespan (20.20.10)
       -- given a time_interval
       --
       -- JS: Allow user to specify whether the itme interval is to be copied or not
       -- JS: This gives more flexibility of not making a copy instead of requiring 
       -- JS: the caller responsible for deleting the copy.
       --
       -- @author W. Scott Meeks
       --
       -- @param interval_id    Id of interval to be included/copied in timespan, 
       -- @param copy_p         If true, make another copy of the interval, 
       --                       else simply include the interval in the timespan
       --
       -- @return Id of new timespan       
       --
       integer,         -- time_intervals.interval_id%TYPE
       boolean          
)
returns integer as '    -- timespans.timespan_id%TYPE
declare
        new__interval_id        alias for $1;
        new__copy_p             alias for $2;
        v_timespan_id           timespans.timespan_id%TYPE;
        v_interval_id           time_intervals.interval_id%TYPE;
begin
        -- get a new id;
        select timespan_seq.nextval into v_timespan_id from dual;

        if new__copy_p
        then      
             -- JS: Note use of overloaded function (zero offset)
             v_interval_id := time_interval__copy(new__interval_id);
        else
             v_interval_id := new__interval_id;
        end if;
        
        insert into timespans
            (timespan_id, interval_id)
        values
            (v_timespan_id, v_interval_id);
        
        return v_timespan_id;

end;' language 'plpgsql'; 
--  end new;

create function timespan__new (
       --
       -- Creates a new timespan (20.20.10)
       -- given a time_interval
       --
       -- JS: I understand why we want to copy here (since interval_id
       -- JS: may be used by another), but see note on time_span__copy
       -- JS: below.   THE ONLY REASON WHY DEFAULT IS TRUE IS TO MAINTAIN
       -- JS: COMPATIBILITY WITH ORIGINAL VERSION.  I DO NOT THINK TRUE
       -- JS: SHOULD BE THE DEFAULT.
       --
       -- @param interval_id    Id of interval to be copied in timespan, 
       --
       -- @return Id of new timespan       
       --
       integer          -- time_intervals.interval_id%TYPE
)
returns integer as '    -- timespans.timespan_id%TYPE
declare
        new__interval_id        alias for $1;
begin
        return timespan__new(
                   new__interval_id,
                   true
                   );
        
end;' language 'plpgsql'; 


create function timespan__new (
       --
       -- Creates a new timespan (20.20.10)
       -- given a start date and end date.  A new time interval with the 
       -- start and end dates is automatically created.
       --
       -- @param start_date     Start date of interval to be included/copied in timespan, 
       -- @param end_date       End date of interval to be included/copied in timespan, 
       --
       -- @return Id of new timespan       
       --
       timestamptz,     --  time_intervals.start_date%TYPE default null,
       timestamptz      --  time_intervals.end_date%TYPE default null
)
returns integer as '    --  timespans.timespan_id%TYPE
declare
       new__start_date  alias for $1; -- default null,
       new__end_date    alias for $2; -- default null
begin

       -- JS: If we simply call timespan__new with default copy_p = true,
       -- JS: there will be two new time intervals that will be created
       -- JS: everytime this function is called. The first one will never be used!!! 
       -- JS: To fix, we use the timespan__new with copy_p parameter and
       -- JS: setting copy_p to false.
       return timespan__new(time_interval__new(new__start_date, new__end_date),false);

end;' language 'plpgsql'; 


create function timespan__delete (
       -- 
       -- Deletes the timespan and any contained intervals 
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   Id of timespan to delete
       --
       -- @return 0 (procedure dummy)
       --
       integer      -- timespans.timespan_id%TYPE
)
returns integer as '
declare
       delete__timespan_id alias for $1; 
begin
       -- Delete intervals, corresponding timespan entries deleted by
       -- cascading constraints

       delete from time_intervals
       where  interval_id in (select interval_id
                              from   timespans
                              where  timespan_id = delete__timespan_id);
       return 0;

end;' language 'plpgsql'; 


create function timespan__join_interval (
       --
       -- Join a time interval to an existing timespan
       --
       -- JS: Slight changes from original
       -- JS: Return the interval_id being joined, since it will not be the
       -- JS: same as join_interval__interval_id if join_interval__copy_p is true
       -- JS: The Oracle version is a procedure, so this change is completely free.
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   Id of timespan to join to
       -- @param interval_id   Id of interval to include/copy into timespan
       -- @param copy_p        If true, make a new copy of he interval for inclusion
       --                      into the timespan, otherwise simply include the interval
       --
       -- @return Id of interval being joined
       --
       integer,         -- timespans.timespan_id%TYPE,
       integer,         -- time_intervals.interval_id%TYPE,
       boolean
)
returns integer as '    -- time_intervals.interval_id%TYPE
declare
       join_interval__timespan_id     alias for $1;
       join_interval__interval_id     alias for $2;
       join_interval__copy_p          alias for $3; -- default true
       v_interval_id                  time_intervals.interval_id%TYPE;
begin
       if join_interval__copy_p then
           v_interval_id := time_interval__copy(join_interval__interval_id);
       else
           v_interval_id := join_interval__interval_id;
       end if;
        
       insert into timespans
            (timespan_id, interval_id)
       values
            (join_interval__timespan_id, v_interval_id);

       -- JS: We might as well return the interval id being joined, instead of returning a dummy integer
       return v_interval_id;

end;' language 'plpgsql'; 


create function timespan__join (
       --
       -- Join a new interval with start and end dates to an existing timespan
       --
       -- JS: Slight change from original
       -- JS: Return the interval_id being joined (Oracle version is a procedure)
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   Id of timespan to join new interval
       -- @param start_date    Start date of new interval to join to timespan
       -- @param end_date      End date of new interval to join to timespan
       --
       -- @return Id of interval being joined
       --
       integer,         -- timespans.timespan_id%TYPE,
       timestamptz,     -- time_intervals.start_date%TYPE
       timestamptz      -- time_intervals.end_date%TYPE
)
returns integer as '    -- time_intervals.interval_id%TYPE
declare
       join__timespan_id   alias for $1;
       join__start_date    alias for $2; -- default null,
       join__end_date      alias for $3; -- default null
begin

       -- JS: This will create a new interval with start_date and end_date
       -- JS: so we might as well return the interval id 
       return timespan__join_interval(
                join__timespan_id, 
                time_interval__new(join__start_date, join__end_date),
                false
                );

end;' language 'plpgsql'; 


create function timespan__join (
       --
       -- Join a new timespan or time interval to an existing timespan
       --
       -- JS: Slight changes from original
       -- JS: Return the last interval_id being joined. Although probably not useful
       -- JS: we return the interval_id anyways to make the function consisted with
       -- JS: the rest.  Oracle version is a procedure.
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   Id of timespan to join to
       -- @param timespan_id   Id of timespan to join from
       --
       -- @return Id of last interval in timespan being joined
       --
       integer,         -- timespans.timespan_id%TYPE,
       integer          -- timespans.timespan_id%TYPE
)
returns integer as '    -- time_intervals.interval_id%TYPE
declare
       join__timespan_1_id   alias for $1;
       join__timespan_2_id   alias for $2;
       v_interval_id          time_intervals.interval_id%TYPE;
       rec_timespan           record;
begin
       -- Loop over intervals in 2nd timespan, join with 1st.
       for rec_timespan in 
            select * 
            from   timespans
            where  timespan_id = join__timespan_2_id
       loop
            v_interval_id := timespan__join_interval(
                                  join__timespan_1_id, 
                                  rec_timespan.interval_id,
                                  false
                                  );
       end loop;

       -- JS: Return the last interval id joined.  Not very useful, since there may be
       -- JS: more than one interval joined
       return v_interval_id;

end;' language 'plpgsql'; 


create function timespan__interval_delete (
       --
       -- Deletes an interval from the given timespan
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   timespan to delete from
       -- @param interval_id   delete this interval from the set
       --
       -- @return 0 (procedure dummy)
       --
       integer,         -- timespans.timespan_id%TYPE,
       integer          -- time_intervals.interval_id%TYPE
)
returns integer as '
declare
       interval_delete__timespan_id   alias for $1;
       interval_delete__interval_id   alias for $2;
begin

       delete from timespans
       where timespan_id = interval_delete__timespan_id
       and   interval_id = interval_delete__interval_id;

       return 0;

end;' language 'plpgsql'; 


create function timespan__exists_p (
       --
       -- If its contained intervals are all deleted, then a timespan will
       -- automatically be deleted.  This checks a timespan_id to make sure it is
       -- still valid.
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   id of timespan to check
       --
       -- @return true if interval is in timespan set, otherwise false.
       --
       integer        -- timespans.timespan_id%TYPE
) 
returns boolean as '
declare
       exists_p__timespan_id   alias for $1;
       v_result         integer;
begin
       -- Only need to check if any rows exist. 
       select count(*)
       into   v_result
       from dual
       where exists (select timespan_id
                     from   timespans
                     where  timespan_id = exists_p__timespan_id);

       if v_result = 0 then
           return false;
       else
           return true;
       end if;

end;' language 'plpgsql'; 


create function timespan__multi_interval_p (
       --
       -- Checks if timespan contains more than one interval
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   id of timespan to check
       --
       -- @return true if timespan has more than one interval, otherwise false.
       --
       integer       -- timespans.timespan_id%TYPE
)
returns boolean as '
declare
       multi_interval_p__timespan_id   alias for $1;
       v_result                 boolean;
begin
       -- ''f'' if 0 or 1 intervals, ''t'' otherwise
       -- use the simple case syntax
       select (case count(timespan_id) 
                    when 0 then false
                    when 1 then false 
                    else true
               end)
       into v_result
       from timespans
       where timespan_id = multi_interval_p__timespan_id;
        
       return v_result;

end;' language 'plpgsql'; 


create function timespan__overlaps_interval_p (
       --
       -- Checks to see interval overlaps any of the intervals in the timespan. 
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   id of timespan as reference
       -- @param timespan_id   id of timespan to check 
       --
       -- @return true if interval overlaps with anyinterval in timespan, otherwise false.
       --
       integer,         -- timespans.timespan_id%TYPE,
       integer          -- time_intervals.interval_id%TYPE default null
)
returns boolean as '
declare
       overlaps_interval_p__timespan_id   alias for $1;
       overlaps_interval_p__interval_id   alias for $2; -- default null
       v_start_date               timestamptz;
       v_end_date                 timestamptz;
begin
       select start_date, end_date
       into   v_start_date, v_end_date
       from   time_intervals
       where  interval_id = overlaps_interval_p__interval_id;
        
       return timespan__overlaps_p(
                        overlaps_interval_p__timespan_id, 
                        v_start_date, 
                        v_end_date
                        );

end;' language 'plpgsql'; 


create function timespan__overlaps_p (
       --
       -- Checks to see if any intervals in a timespan overlap any of the intervals
       -- in the second timespan. 
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   id of timespan as reference
       -- @param timespan_id   id of timespan to check 
       --
       -- @return true if timespan overlaps with second timespan, otherwise false.
       --
       integer,        -- timespans.timespan_id%TYPE,
       integer         -- timespans.timespan_id%TYPE
)
returns boolean as '
declare
       overlaps_p__timespan_1_id   alias for $1;
       overlaps_p__timespan_2_id   alias for $2;
       v_result             boolean;
       rec_timespan                 record;
begin
       -- Loop over 2nd timespan, checking each interval against 1st
       for rec_timespan in 
            select * 
            from timespans
            where timespan_id = overlaps_p__timespan_2_id
       loop
            v_result := timespan__overlaps_interval_p(
                                overlaps_p__timespan_1_id,
                                rec_timespan.interval_id
                                );
            if v_result then
                return true;
            end if;
       end loop;

       return false;

end;' language 'plpgsql'; 


create function timespan__overlaps_p (
       --
       -- Checks to see if interval with start and end dates overlap any of the intervals
       -- in the timespan. 
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   Id of timespan as reference
       -- @param start_date    Start date of interval
       -- @param end_date    End date of interval
       --
       -- @return true if interval with start and end dates overlaps with second timespan, otherwise false.
       --
       integer,         -- timespans.timespan_id%TYPE,
       timestamptz,     -- time_intervals.start_date%TYPE
       timestamptz      -- time_intervals.end_date%TYPE
) 
returns boolean as '
declare
       overlaps_p__timespan_id     alias for $1;
       overlaps_p__start_date      alias for $2; -- default null,
       overlaps_p__end_date        alias for $3; -- default null
       v_result                    boolean;
       rec_timespan                record;
begin
       -- Loop over each interval in timespan, checking against dates.
       for rec_timespan in
            select * 
            from timespans
            where timespan_id = overlaps_p__timespan_id
       loop
            v_result := time_interval__overlaps_p(
                                rec_timespan.interval_id, 
                                overlaps_p__start_date,
                                overlaps_p__end_date
                                );

            if v_result then
                return true;
            end if;
       end loop;

       return false;

end;' language 'plpgsql'; 


create function timespan__copy (
       --
       -- Creates a new copy of a timespan, offset by optional offset
       -- JS:  See note on intervals on time_interval__copy
       --
       -- @author W. Scott Meeks
       --
       -- @param timespan_id   Timespan to copy
       -- @param offset        Offset al dates in timespan by this date interval
       --
       -- @return Id of copied timespan
       --
       integer,         -- timespans.timespan_id%TYPE,
       interval 
)
returns integer as '    -- timespans.timespan_id%TYPE
declare
       copy__timespan_id        alias for $1;
       copy__offset             alias for $2; --  default 0
       rec_timespan             record;
       v_interval_id            timespans.interval_id%TYPE;
       v_timespan_id            timespans.timespan_id%TYPE;
begin
       v_timespan_id := null;

       -- Loop over each interval in timespan, creating a new copy
       for rec_timespan in 
            select * 
            from timespans
            where timespan_id = copy__timespan_id
       loop
            v_interval_id := time_interval__copy(
                                rec_timespan.interval_id, 
                                copy__offset
                                );

            if v_timespan_id is null 
            then
                 -- JS: NOTE DEFAULT BEHAVIOR OF timespan__new
                v_timespan_id := timespan__new(v_interval_id);
            else
                -- no copy, use whatever is generated by time_interval__copy
                PERFORM timespan__join_interval(
                                v_timespan_id, 
                                v_interval_id,
                                false);
            end if;

       end loop;

       return v_timespan_id;

end;' language 'plpgsql'; 


create function timespan__copy (
       --
       -- Creates a new copy of a timespan, no offset
       --
       -- @param timespan_id   Timespan to copy
       -- @param offset        Offset al dates in timespan by this date interval
       --
       -- @return Id of copied timespan
       --
       integer          -- timespans.timespan_id%TYPE,
)
returns integer as '    -- timespans.timespan_id%TYPE
declare
       copy__timespan_id        alias for $1;
begin

       return timespan__copy(
                    copy__timespan_id,
                    interval ''0 days''
                    );

end;' language 'plpgsql'; 
        

create function timespan__copy (
       --
       -- Creates a new copy of a timespan, offset by optional offset
       -- JS: Overloaded function to make above compatible with Oracle behavior
       -- JS: when an integer (for number of days) is supplied as a parameter.
       --
       -- @param timespan_id   Timespan to copy
       -- @param offset        Offset all dates in timespan by this number of days
       --
       -- @return Id of copied timespan
       --
       integer,         -- timespans.timespan_id%TYPE,
       integer
)
returns integer as '    -- timespans.timespan_id%TYPE
declare
       copy__timespan_id        alias for $1;
       copy__offset             alias for $2;
begin

       return timespan__copy(
                    copy__timespan_id,
                    to_interval(copy__offset,''days'')
                    );

end;' language 'plpgsql'; 
