--
-- time_interval__overlaps_p/3
--
create or replace function time_interval__overlaps_p(
  integer,
  timestamp with time zone,
  timestamp with time zone
) returns bool 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;


--
-- time_interval__overlaps_p/2
--
create or replace function time_interval__overlaps_p(
  integer,
  integer
) returns bool 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;


--
-- time_interval__overlaps_p/4
--
create or replace function time_interval__overlaps_p(
  timestamp with time zone,
  timestamp with time zone,
  timestamp with time zone,
  timestamp with time zone
) returns bool 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;