-- get next free extension for phoneline with specified function.
-- takes company.id, phoneline.function
CREATE or REPLACE FUNCTION next_ext(integer, varchar) RETURNS integer AS '
   DECLARE
       from_nr    integer;
       to_nr      integer;
       next_ext   integer;
   BEGIN
       SELECT INTO from_nr from_extension FROM extensionrange WHERE function=$2 AND company = $1;
       SELECT INTO to_nr to_extension FROM extensionrange WHERE function=$2 AND company = $1;

       SELECT INTO next_ext min(p1.extension::text::integer) + 1
         FROM phoneline AS p1 left outer join phoneline AS p2 ON (p1.extension::text::integer + 1 = p2.extension::text::integer
                                                                  AND p2.function=$2
                                                                  AND p1.company=p2.company)
                              left outer join phoneline AS p3 ON (p1.extension::text::integer + 1 = p3.extension::text::integer
                                                                  AND p1.company=p3.company
                                                                  AND p3.function=$2
                                                                  AND p2.id != p3.id AND p3.status = ''active'')
        WHERE p1.function=$2 AND p1.company = $1 AND p1.status != ''deleted''
          AND (p2.id IS NULL or p2.status = ''deleted'') AND p3.id IS NULL;

       IF next_ext IS NULL THEN
         next_ext = from_nr;
       ELSE
         IF next_ext > to_nr THEN
           next_ext = from_nr;
         END IF;
       END IF;
       RETURN next_ext;
   END;
' LANGUAGE plpgsql;
