BEGIN;

-- get current ringgroups a user is logged in to
-- getuserringgroups(user)
CREATE or REPLACE FUNCTION getuserringgroups(integer) RETURNS varchar AS '
   DECLARE
       ext RECORD;
       result VARCHAR;
       tmp VARCHAR;
   BEGIN
       tmp = '''';
       FOR ext IN select phoneline.extension from ringgroup_contact
                         left join ringgroup on ringgroup.id = ringgroup_contact.ringgroup
                         left join phoneline on phoneline.id = ringgroup.phoneline
                  where ringgroup_contact.contact = $1 LOOP
          IF ext.extension <> ''empty'' THEN
              result := ext.extension || ''.'' || tmp;
              tmp := result;
          END IF;
       END LOOP;
       RETURN result;
   END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION loginringgroupagent(integer,integer,integer) RETURNS integer AS '
   DECLARE
       res integer;
       qid integer;
       plid integer;
       dep_only varchar(3);
       cdid integer;
       qcid integer;
       uid ALIAS for $1;
       qline ALIAS for $2;
       fromext ALIAS for $3;
   BEGIN

-- see if we already logged in
       SELECT INTO res ringgroup_contact.id
       from   ringgroup_contact left join ringgroup on ringgroup.id = ringgroup_contact.ringgroup
                          left join phoneline on phoneline.id = ringgroup.phoneline
       where  phoneline.extension = qline and ringgroup_contact.contact = uid;

       IF res IS NOT NULL THEN
           RAISE NOTICE ''user % already logged into ringgroup %, ringgroup_contact.id = %'', uid, qline, res;
           return ''1''; -- success
       END IF;

-- not already logged in. Find the proper ringgroup from the phoneline id.
       select into qid ringgroup.id
       from   ringgroup left join phoneline on phoneline.id = ringgroup.phoneline
       where  extension = qline;

       IF qid IS NULL THEN
           RAISE NOTICE ''ringgroup % not found'', qline;
           return ''0''; -- error: ringgroup not found
       END IF;
-- okay, the ringgroup is real, now log us in.
       if fromext = 1 THEN
         SELECT into plid id
         FROM   phoneline where function = ''contact'' and lookupid = uid
         limit  1;
       else
         SELECT into plid id
         FROM   phoneline where extension = fromext and function = ''contact'' and lookupid = uid
         limit  1;
       end if;

       IF plid IS NULL THEN
           RAISE NOTICE ''phoneline % not found for user %'', fromext, uid;
           return ''0''; -- our phoneline is not found
       END IF;

       SELECT into dep_only ringgroup.department_only
         FROM ringgroup
        WHERE id = qid and department_only = ''no'';

       IF dep_only IS NULL THEN
         SELECT into cdid contact_department.id
           FROM contact_department, ringgroup
          WHERE ringgroup.id = qid AND ringgroup.department = contact_department.department
            AND contact_department.contact = uid;

         IF cdid IS NULL THEN
             RAISE NOTICE ''contact % not in same department as ringgroup %'', uid, qid;
             return ''0'';
         END IF;
       END IF;

       insert into ringgroup_contact(ringgroup, contact, phoneline) values (qid, uid, plid);
       select into qcid ringgroup_contact.id from ringgroup_contact where (contact=uid and ringgroup=qid);
       RAISE NOTICE ''user % logged in to ringgroup %'', uid, qline;
       INSERT INTO activate(action, type, which, status, created ) 
              VALUES (''add'', ''ringgroup_contact'', qcid, ''QUEUED'', NOW() );
       INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
             (NOW(), uid, uid, ''update'', ''ringgroup'', qid, ''logged on to this ringgroup'');
       INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
             (NOW(), uid, uid, ''update'', ''contact'', uid, ''logged on to ringgroup '' || qline);
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''ringgroup'', qid, ''QUEUED'', NOW() );
       return ''1'';
   END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION logoutringgroupagent(integer,integer,integer) RETURNS integer AS '
   DECLARE
       res integer;
       qid integer;
       plid integer;
       qcid integer;
       ringgroup_id integer;
       uid ALIAS for $1;
       qline ALIAS for $2;
       fromext ALIAS for $3;
   BEGIN

       select into qid ringgroup.id
       from   ringgroup left join phoneline on phoneline.id = ringgroup.phoneline
       where  extension = qline;

       IF qid IS NULL THEN
           RAISE NOTICE ''ringgroup % not found'', qline;
           return ''1''; -- error: ringgroup not found, but still logged off
       END IF;

-- okay, the ringgroup is real, now log us out
       if fromext = 1 THEN
         SELECT into plid id
         FROM   phoneline where function = ''contact'' and lookupid = uid
         limit  1;
       else
         SELECT into plid id
         FROM   phoneline where extension = fromext and function = ''contact'' and lookupid = uid
         limit  1;
       end if;

       SELECT into ringgroup_id ringgroup.id
         FROM ringgroup,phoneline where ringgroup.phoneline=phoneline.id and phoneline.extension=qline;

       select into qcid ringgroup_contact.id from ringgroup_contact where (contact = uid and phoneline = plid and ringgroup = ringgroup_id);
       IF qcid IS NULL THEN
           RAISE NOTICE ''user % alreaddy logged out of ringgroup %'', uid, qline;
           return ''1'';
       END IF;

       delete from ringgroup_contact where id = qcid;
       RAISE NOTICE ''user % logged out of ringgroup %'', uid, qline;
       INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
             (NOW(), uid, uid, ''update'', ''ringgroup'', qid, ''logged off this ringgroup'');
       INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
             (NOW(), uid, uid, ''update'', ''contact'', uid, ''logged out of ringgroup '' || qline);
       -- note: must be in this order: first ringgroup_contact, then ringgroup
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''delete'', ''ringgroup_contact'', qcid, ''QUEUED'', NOW() );
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''ringgroup'', qid, ''QUEUED'', NOW() );
       return ''1'';
   END;
' LANGUAGE plpgsql;

COMMIT;

