-- logout a user from a given phone
-- logoutuser(phone)
CREATE or REPLACE FUNCTION  logoutuser(integer) RETURNS integer AS '
   DECLARE
       phonereboot varchar;
       contactid integer;
       ch record; -- channel
   BEGIN
       -- should phones be rebooted? Normally yes.
       SELECT INTO phonereboot value from astglobals where name=''phonereboot'';

       -- who is currently logged in to this phone?
       SELECT INTO contactid id from contact where phone = $1 and status <> ''deleted'' limit 1;

       update contact set phone = 1 where phone = $1;
       update channel set inuse = ''no'' where phone = $1;
       update channel set inuse = ''yes'' where phone = $1 and line = ''1'';
       IF contactid IS NOT NULL THEN
         RAISE NOTICE ''user % logged out'', contactid;
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''modify'', ''contact'', contactid, ''QUEUED'', NOW() );
         INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
               (NOW(), contactid, contactid, ''update'', ''phone'', $1, ''logged off'');
       END IF;
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''phone'', $1, ''QUEUED'', NOW() );
       FOR ch IN select id from channel where phone = $1 LOOP
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''modify'', ''channel'', ch.id, ''QUEUED'', NOW() );
       END LOOP;
       IF phonereboot = ''yes'' THEN
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''module'', ''phone-check-sync'', $1, ''QUEUED'', NOW() );
       END IF;
       return ''1'';
   END;
' LANGUAGE plpgsql;

-- login a user to a phone, returns the previous phone
-- loginuser(phone,user)
CREATE or REPLACE FUNCTION loginuser(integer,integer) RETURNS integer AS '
   DECLARE
       query varchar;
       phonereboot varchar;
       prevphone integer;
       pt integer; -- phonetype
       comp integer; -- company
       curuser integer; -- current logged in user on this phone
       tmp integer;
       usecount integer;
       pl record; -- phoneline
       ch record; -- channel
   BEGIN
       -- should phones be rebooted? Normally yes.
       SELECT INTO phonereboot value from astglobals where name=''phonereboot'';

       -- see if someone is already logged in to this phone
       select into curuser id from contact where phone = $1;
       IF curuser > 1 THEN
         INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
               (NOW(), $2, curuser, ''update'', ''phone'', $1, ''logged off'');
         update contact set phone = 1 where id = curuser;
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''modify'', ''contact'', curuser, ''QUEUED'', NOW() );
       END IF;

       -- ok, get the previous phone she was logged in to
       select into prevphone phone from contact where id = $2;

       -- get the phonetype of the phone she is logging in to
       select into pt,comp phonetype,company from phone where id = $1;

       -- Set new phone, set the active channels on that phone,
       -- insert activate record to recreate the provisioning files,
       -- and finally reboot the phone
       update contact set phone = $1 where id = $2;
       update channel set inuse = ''no'' where phone = $1;

       FOR pl IN select line from phoneline where function = ''contact'' and lookupid = $2 and status <> ''deleted'' LOOP
         query := ''UPDATE channel SET inuse = '' || quote_literal(''yes'') ||
                  '' WHERE phone = '' || quote_literal($1) || '' and line = '' || quote_literal(pl.line);
         -- raise notice ''query: %'', query;
         execute query;
       END LOOP;
       -- ensure that at least one channel is enabled, some phones refuse to start up without channels
       SELECT INTO usecount count(*) from channel where phone = $1 and inuse = ''yes'';
       IF usecount < ''1'' THEN
         update channel set inuse = ''yes'' where phone = $1 and line = ''1'';
       END IF;

       RAISE NOTICE ''user % logged in to phone %'', $2, $1;
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''phone'', $1, ''QUEUED'', NOW() );
       INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
             (NOW(), $2, $2, ''update'', ''phone'', $1, ''logged on'');
       FOR ch IN select id from channel where phone = $1 LOOP
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''modify'', ''channel'', ch.id, ''QUEUED'', NOW() );
       END LOOP;
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''contact'', $2, ''QUEUED'', NOW() );
       IF phonereboot = ''yes'' THEN
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''module'', ''phone-check-sync'', $1, ''QUEUED'', NOW() );
       END IF;

       -- Recreate provisioning files for the old phone (if there was one)
       -- and  reboot that phone as well
       IF prevphone <> ''1'' AND prevphone <> $1 THEN
         INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
               (NOW(), $2, $2, ''update'', ''phone'', prevphone, ''logged off'');
         update channel set inuse = ''no'' where phone = prevphone;
         update channel set inuse = ''yes'' where phone = prevphone and line = 1;
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''modify'', ''phone'', prevphone, ''QUEUED'', NOW() );
         FOR ch IN select id from channel where phone = prevphone LOOP
           INSERT INTO activate(action, type, which, status, created )
                  VALUES (''modify'', ''channel'', ch.id, ''QUEUED'', NOW() );
         END LOOP;
         IF phonereboot = ''yes'' THEN
           INSERT INTO activate(action, type, which, status, created )
                  VALUES (''module'', ''phone-check-sync'', prevphone, ''QUEUED'', NOW() );
         END IF;
       END IF;
       return prevphone;
   END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION loginagent(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 queue_contact.id
       from   queue_contact left join queue on queue.id = queue_contact.queue
                          left join phoneline on phoneline.id = queue.phoneline
       where  phoneline.extension = qline and queue_contact.contact = uid;

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

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

       IF qid IS NULL THEN
           RAISE NOTICE ''queue % not found'', qline;
           return ''0''; -- error: queue not found
       END IF;
-- okay, the queue 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 queue.department_only
         FROM queue
        WHERE id = qid and department_only = ''no'';

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

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

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

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

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

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

-- okay, the queue 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 queue_id queue.id
         FROM queue,phoneline where queue.phoneline=phoneline.id and phoneline.extension=qline;

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

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