-- build a query that will use the proper tables
CREATE OR REPLACE FUNCTION q_follow_schedule(varchar, integer, time without time zone) RETURNS varchar AS '
  DECLARE 
    tbl ALIAS for $1;
    id ALIAS for $2;
    ctime ALIAS for $3;
    q varchar;
  BEGIN
    q = ''select '';
    q = q || ''soundfile.name || ''''#'''' || 
        '';
    q = q || ''pl1.extension || ''''#'''' || 
        '';
    q = q || ''pl2.extension || ''''#'''' || 
        '';
    q = q || ''pl3.extension || ''''#'''' as result '';
    q = q || ''
   from '' || tbl || '' left join '' || tbl || ''_schedule on '' || tbl || ''_schedule.'' || tbl || '' = '' || tbl || ''.id 
               '';
    q = q || ''left join schedule on schedule.id = '' || tbl || ''_schedule.schedule
               '';
    q = q || ''left join phoneline as pl1 on pl1.id = schedule.phoneline1 
               '';
    q = q || ''left join phoneline as pl2 on pl2.id = schedule.phoneline2 
               '';
    q = q || ''left join phoneline as pl3 on pl3.id = schedule.phoneline3 
               '';
    q = q || ''left join soundfile on soundfile.id = schedule.greeting '';

    q = q || ''
  where ('' || tbl || ''.redirect_on = ''''yes'''' and '' || tbl || ''.id = '' || quote_literal(id) || '') '';

-- now every following AND statement handles one set of fields from the schedule table, each.
-- * starttime & endtime
--   If starttime or endtime is null, then they dont limit the schedule timewise.
--   There is a difference between the case where starttime is before endtime, compared with the other way around.
--   If starttime is before endtime, the current time should be in between both.
--   If endtime is before starttime, the current time should be either after starttime or before endtime.

    q = q || ''
      and ((starttime is not null and endtime is not null and
            starttime < endtime and '' || quote_literal(ctime) || '' > starttime and '' || quote_literal(ctime) || '' < endtime) or
           (starttime is not null and endtime is not null and
            starttime > endtime and ('' || quote_literal(ctime) || '' > starttime or '' || quote_literal(ctime) || '' < endtime)) or
           (starttime is null and endtime is not null and '' || quote_literal(ctime) || '' < endtime) or
           (starttime is not null and endtime is null and '' || quote_literal(ctime) || '' > starttime)) '';

-- * atdate & enddate

    q = q || ''
      and (atdate is null or current_date >= atdate)
      and (enddate is null or current_date <= enddate) '';

-- * interval
--   is one of the following:
--   * none

    q = q || ''
      and ((interval = ''''none'''') or '';

--   * day
--     when the schedule is repeated daily, the every field defines that it is for example every 3 days.
--     it is calculated as follows: atdate is substracted from currentdate. That gives the number of days
--     between then and now. For some reason, this result needs to be cast to the integer type.
--     Then using the % (modulo) operator, we check wether this number of days is divisible by every.
--     In case every is 0, this leads to division by zero. To prevent that, we say that every needs to be not 0
--     before doing the actual calculation.

    q = q || ''
         (interval = ''''day''''  and
          ((every = 0) or
           (every != 0 and (cast((current_date - atdate) as integer) % every) = 0))) or '';

--   * week
--     same idea as with interval = day.
--     Except, this time we also need to check if the database field value for the current day is set to yes.
--     For example: if today is friday, then date_part(dow, current_date) needs to be 5, and friday set to yes.

    q = q || ''
         (interval = ''''week''''  and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(''''week'''', current_date) - date_part(''''week'''', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (date_part(''''dow'''', current_date) = 0 and sunday    = ''''yes'''') or
           (date_part(''''dow'''', current_date) = 1 and monday    = ''''yes'''') or
           (date_part(''''dow'''', current_date) = 2 and tuesday   = ''''yes'''') or
           (date_part(''''dow'''', current_date) = 3 and wednesday = ''''yes'''') or
           (date_part(''''dow'''', current_date) = 4 and thursday  = ''''yes'''') or
           (date_part(''''dow'''', current_date) = 5 and friday    = ''''yes'''') or
           (date_part(''''dow'''', current_date) = 6 and saturday  = ''''yes'''')
          )
         ) or '';

--   * month
--     Only for interval=month, the repeat_by field is used. This field is either date or day.
--     If the value is date, then the schedule repeats monthly by date - for example, every 18th of the month.
--     If the value is day, then the schedule repeats by weekday of the month - for example, every second tuesday.

    q = q || ''
         (interval = ''''month''''  and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(''''month'''', current_date) - date_part(''''month'''', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (repeat_by = ''''date'''' and 
            (date_part(''''day'''', current_date)) = (date_part(''''day'''', atdate))) or
           (
            repeat_by = ''''day''''  and 
            (date_part(''''dow'''', current_date)) = (date_part(''''dow'''', atdate)) and '';

--     Say it is monday 14. This is the second monday of the month - the 7th is the first monday.
--     One day later it is tuesday 15. This is the third tuesday of the month - the 1st is the first tuesday.
--     In one day, the number /weekday of the month/ goes from 2 to 3.
--     Because weekdays such as tuesday repeat every 7 days, we divide the day of the month by 7.
--     14 / 7 = 2.
--     15 / 7 = 2.14
--     13 / 7 = 1.86 - appearently, the jump from 1 to 2 does not happen from the 14th to the 15th, but
--                     from the 13th to the 14th.
--     So, before dividing by 7, we need to substract 1. Then the floor() function rounds it down to an integer.
--     Note that this doesnt give the actual weekday of the month; in order to get that, we need to add 1 to
--     the result. But for comparing 2 dates, that isnt neccessary.

    q = q || ''
            floor((date_part(''''day'''', atdate) -1) / 7) = floor((date_part(''''day'''', current_date) -1) / 7)
           )
          )
         ) or '';

--   * year

    q = q || ''
         (interval = ''''year''''  and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(''''year'''', current_date) - date_part(''''year'''', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (date_part(''''day'''', current_date) = date_part(''''day'''', atdate)) and
           (date_part(''''month'''', current_date) = date_part(''''month'''', atdate))
          )
         ) '';

-- Take priorities into account, only select the one with highest priority.

    q = q || ''
        ) 
  order by priority limit 1; '';
    RETURN q;
  END;
' LANGUAGE plpgsql;
