CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
  RETURN nt_date PIPELINED DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
  LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start + INTERVAL '1' DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

CREATE OR REPLACE FUNCTION CONVERT_TO_SECONDS( 
  i_date_string IN VARCHAR2 
)
RETURN INTEGER DETERMINISTIC
AS
BEGIN
  RETURN ( TO_DATE(i_date_string, 'HH24:MI:SS')
         - TO_DATE('00:00:00', 'HH24:MI:SS')
         ) * 86400;
END;
/  

CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'G' 
            WHEN 2 THEN 'G' 
            WHEN 3 THEN 'G' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 5;

     ALTER TABLE locations 
         ADD ( CONSTRAINT locations_pk
       PRIMARY KEY (location_id));

create table schedule(
      seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       schedule_id NUMBER(4),
       location_id number(4),
       base_date DATE,
       start_date DATE,
       end_date DATE,
          constraint schedule_pk primary key (schedule_id, location_id, base_date),
         CONSTRAINT start_min check (start_date=trunc(start_date,'MI')),   
       CONSTRAINT end_min check (end_date=trunc(end_date,'MI')),
 CONSTRAINT end_gt_start CHECK (end_date >= start_date)
      );


CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE(
  i_schedule_id IN PLS_INTEGER,
  i_base_date   IN DATE,
  i_offset      IN PLS_INTEGER DEFAULT 0, 
  i_incr        IN PLS_INTEGER DEFAULT 10,
  i_duration    IN PLS_INTEGER DEFAULT 5
)
AS 
  l_offset   interval day to second;
  l_incr     interval day to second;
  l_duration interval day to second;
BEGIN
  l_offset   := NUMTODSINTERVAL(i_offset, 'SECOND') ;
  l_incr     := NUMTODSINTERVAL(i_incr, 'MINUTE') ;
  l_duration := NUMTODSINTERVAL(i_duration, 'MINUTE') ;

  MERGE INTO schedule dst
  USING (
    SELECT   i_schedule_id AS schedule_id,
             l.location_id,
             i_base_date AS base_date,
             i_base_date + l_offset + (l_incr * (ROWNUM - 1))
               AS start_date,
             i_base_date + l_offset + (l_incr * (ROWNUM - 1)) + l_duration
               AS end_date
    FROM     locations l
    where  location_id in ( 
      select location_id
      from   locations
      where  location_type = 'G'
    ) 

  ) src
  ON (   src.schedule_id = dst.schedule_id
     AND src.location_id = dst.location_id
     AND src.base_date   = dst.base_date
  )
  WHEN NOT MATCHED THEN
    INSERT (
      schedule_id,
      location_id,
      base_date,
      start_date,
      end_date
    ) VALUES (
      src.schedule_id,
      src.location_id,
      src.base_date,
      src.start_date,
      src.end_date
    );
END;
/


EXEC CREATE_SCHEDULE(1,TRUNC(SYSDATE),CONVERT_TO_SECONDS('16:00:00'));
/