-- logout a user from a given phone
-- logoutuser(phone)
CREATE or REPLACE FUNCTION  logoutuser(integer) RETURNS integer AS '
   DECLARE
       phonereboot varchar;
       _canlogout varchar;
       contactid integer;
       pw integer;
       worktime integer;
       pevent integer;
       pcontact 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, _canlogout contact.id, contact.canlogout
         from contact left join role on role.id = contact.role
         where phone = $1 and contact.status <> ''deleted'' limit 1;

       IF _canlogout <> ''yes'' THEN
         RAISE NOTICE ''user % can not logout'', contactid;
         return ''0'';
       END IF;

       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
         SELECT INTO pevent, worktime event, cast(extract(epoch FROM CURRENT_TIMESTAMP(0) - stamp) as integer) from contacthist 
               WHERE contact = contactid and event in (''40'', ''41'') order by stamp desc limit 1;
         if pevent = ''40'' THEN
           INSERT INTO contacthist (contact, stamp, event, int2, parm1) 
                  VALUES (contactid, CURRENT_TIMESTAMP(0), ''41'', worktime, ''Logoff'');
         END IF;
         RAISE NOTICE ''user % logged out'', contactid;
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''modify'', ''contact'', contactid, ''QUEUED'', CURRENT_TIMESTAMP );
         INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
               (CURRENT_TIMESTAMP, contactid, contactid, ''update'', ''phone'', $1, ''logged off'');
       END IF;
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''phone'', $1, ''QUEUED'', CURRENT_TIMESTAMP );
       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'', CURRENT_TIMESTAMP );
       END LOOP;
       IF phonereboot = ''yes'' THEN
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''module'', ''phone-check-sync'', $1, ''QUEUED'', CURRENT_TIMESTAMP );
       END IF;

       SELECT INTO pw pairedwith from phone where id = $1;
       SELECT INTO pcontact id from contact where phone = pw and status <> ''deleted'' limit 1;

       --IF pcontact > ''1'' THEN
       --  PERFORM logoutuser(pw);
       --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;
       pw integer;
       comp integer; -- company
       need_lic_check varchar; -- need license checking?
       _lic_per_company varchar; -- count per company licences?
       _lic_used integer; -- current amount of used licenses
       _licenses integer; -- assigned amount of used licenses
       curuser integer; -- current logged in user on this phone
       tmp integer;
       usecount integer;
       worktime integer;
       pl record; -- phoneline
       ch record; -- channel
   BEGIN
       -- should phones be rebooted? Normally yes.
       SELECT INTO phonereboot value from astglobals where name=''phonereboot'';

       -- set this default to yes
       need_lic_check := ''yes'';

       -- get basic information 
       select into curuser id from contact where phone = $1;
       select into comp company from contact where id = $2;

       -- see if someone is already logged in to this phone
       IF curuser > 1 THEN
         INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
               (CURRENT_TIMESTAMP, $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'', CURRENT_TIMESTAMP );
         need_lic_check := ''no'';
       END IF;

       -- ok, get the previous phone she was logged in to
       select into prevphone phone
         from contact left join role on role.id = contact.role
         where contact.id = $2 and contact.canlogout = ''yes'';

       IF NOT FOUND THEN
         raise notice ''% logged on on another phone and cannot logout'', $2;
         return ''0'';
       END IF;

       -- if found (so switching to another phone) write a Logoff record for that phone
       IF prevphone <> 1 THEN
         SELECT INTO worktime cast(extract(epoch FROM CURRENT_TIMESTAMP(0) - stamp) as integer) from contacthist
               WHERE contact = $2 and event = ''40'' order by stamp desc limit 1;
         if FOUND THEN
           raise notice ''found logon for user %'', $2;
           INSERT INTO contacthist (contact, stamp, event, int2, parm1)
                  VALUES ($2, CURRENT_TIMESTAMP(0), ''41'', worktime, ''Logoff'');
         ELSE
           raise notice ''logon for user % not found'', $2;
         END IF;
         need_lic_check := ''no'';
       END IF;

       -- check license count
       if need_lic_check = ''yes'' THEN
         raise notice ''Need licenses check company %'', comp;
         select into _lic_per_company value from astglobals where name = ''lic_per_company'';
         -- are licenses set per company?
         IF _lic_per_company = ''yes'' THEN
           raise notice ''licenses set per company'';
           select into _licenses licenses from company where id = comp;
           select into _lic_used count(*) as count from contact left join phone on phone.id = contact.phone
                 where contact.phone > 1 and contact.status != ''deleted'' and phone.phonetype != 160 and contact.company = comp;
           raise notice ''used = %, max = %'', _lic_used, _licenses;
           IF _lic_used >= _licenses THEN
             raise notice ''% cannot login: max licenses for company % = %'', $2, comp, _licenses;
             return ''-1'';
           END IF;
         END IF;
         SELECT INTO _licenses value FROM astglobals WHERE name = ''licusers'';
         SELECT INTO _lic_used count(*) as count FROM contact LEFT JOIN phone on phone.id = contact.phone 
               WHERE contact.phone > 1 and contact.status != ''deleted'' and phone.phonetype != 160;
         IF _lic_used >= _licenses THEN
           raise notice ''% cannot login: max licenses for system = %'', $2, _licenses;
           return ''-1'';
         END IF;
       END IF;

       -- 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;

       -- give this phone as much channels as the user has extensions
       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;

       -- get id of phone, this phone is paired with
       select into pw pairedwith from phone where id = $1;

       IF pw > ''1'' THEN
         -- logout whoever is on the paired phone
         PERFORM logoutuser(pw);

         -- give this phone as much channels as the user has extensions
         update channel set inuse = ''no'' where phone = pw;
         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(pw) || '' 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 = pw and inuse = ''yes'';
         IF usecount < ''1'' THEN
           update channel set inuse = ''yes'' where phone = pw and line = ''1'';
         END IF;
       END IF;

       INSERT INTO contacthist (contact, stamp, event, parm1) 
              VALUES ($2, CURRENT_TIMESTAMP(0), ''40'', ''Logon'');
       RAISE NOTICE ''user % logged in to phone %'', $2, $1;
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''phone'', $1, ''QUEUED'', CURRENT_TIMESTAMP );
       INSERT INTO history (created, creator, contact, type, lookup, lookupid, value) values
             (CURRENT_TIMESTAMP, $2, $2, ''update'', ''phone'', $1, ''logged on'');
       INSERT INTO activate(action, type, which, status, created )
              VALUES (''modify'', ''contact'', $2, ''QUEUED'', CURRENT_TIMESTAMP );
       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'', CURRENT_TIMESTAMP );
       END LOOP;
       IF phonereboot = ''yes'' THEN
         INSERT INTO activate(action, type, which, status, created )
                VALUES (''module'', ''phone-check-sync'', $1, ''QUEUED'', CURRENT_TIMESTAMP );
       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
               (CURRENT_TIMESTAMP, $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'', CURRENT_TIMESTAMP );
         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'', CURRENT_TIMESTAMP );
         END LOOP;
         IF phonereboot = ''yes'' THEN
           INSERT INTO activate(action, type, which, status, created )
                  VALUES (''module'', ''phone-check-sync'', prevphone, ''QUEUED'', CURRENT_TIMESTAMP );
         END IF;
       END IF;
       return prevphone;
   END;
' LANGUAGE plpgsql;

-- find important info on a contact
-- return name, dialplan, contacttypeid, contacttype, recording
CREATE or REPLACE FUNCTION getcontactinfo(integer) RETURNS VARCHAR AS '
   DECLARE
     _name VARCHAR;
     _dialplan VARCHAR;
     _contacttypeid INTEGER;
     _contacttype VARCHAR;
     _recording VARCHAR;
     _department VARCHAR;
     _pin VARCHAR;
   BEGIN
     select into _name, _dialplan, _contacttypeid, _contacttype, _recording, _department, _pin
                 contact.firstname || '' '' || contact.lastname as name,
                 substring(role.allow_dial_tollfree, 1, 1) || substring(role.allow_dial_local, 1, 1) ||
                 substring(role.allow_dial_longdistance, 1, 1) || substring(role.allow_dial_mobile, 1, 1) ||
                 substring(role.allow_dial_international, 1, 1) || substring(role.allow_dial_entertain, 1, 1) ||
                 substring(role.allow_dial_premium, 1, 1) || substring(role.allow_dial_adult, 1, 1) as cperm,
                 contact.contacttype, contacttype.name, phoneline.recording, department.name,
                 contact.login || contact.pin as pin
       from contact left join role on role.id = contact.role
                    left join phoneline on (phoneline.contact = contact.id
                                        or (phoneline.function = ''contact'' and phoneline.lookupid = contact.id))
                    left join contacttype on contacttype.id = contact.contacttype
                    left join contact_department on  contact.id = contact_department.contact
                    left join department on contact_department.department = department.id
       where contact.id = $1 and phoneline.contact IS NOT NULL limit 1;

     return _name || ''#'' || _dialplan || ''#'' || _contacttypeid || ''#'' || _contacttype || ''#'' || _recording || ''#'' || _department || ''#'' || _pin;
   END;
' LANGUAGE plpgsql;
