CREATE OR REPLACE FUNCTION route_auth_function(billid character varying, caller character varying, callee character varying) RETURNS record AS $$
  DECLARE
    result RECORD;
    client RECORD;
    dest   RECORD;
  BEGIN
    ---- Prefix based client authorization
    SELECT
      name, prefix, SUBSTR(callee, LENGTH(prefix) + 1) AS dialed, gid
    INTO client FROM clients
    WHERE
      callee LIKE prefix || '%' AND
      (CASE WHEN type = 'prepaid' AND balance <= 0 THEN false ELSE true END) AND
      active
    ORDER BY LENGTH(prefix) DESC LIMIT 1;

    IF client.prefix IS NULL THEN
      RAISE NOTICE 'Prefix not found, invalid client';
      RETURN NULL;
    ELSE
      RAISE NOTICE 'Client:       %', client.name;
      RAISE NOTICE 'Prefix:       %', client.prefix;
      RAISE NOTICE 'Billing ID:   %', billid;
      RAISE NOTICE 'Caller:       %', caller;
      RAISE NOTICE 'Callee:       %', client.dialed;
    END IF;

    ---- Check banned callers list
    IF (SELECT CASE WHEN (SELECT caller LIKE number AS valid FROM banned_callers ORDER BY valid DESC LIMIT 1) THEN true ELSE false END) THEN
      RAISE NOTICE 'Caller is banned';
      RETURN NULL;
    END IF;

    ---- Check banned callees lisT
    IF (SELECT CASE WHEN (SELECT client.dialed LIKE number AS valid FROM banned_numbers ORDER BY valid DESC LIMIT 1) THEN true ELSE false END) THEN
      RAISE NOTICE 'Callee is banned';
      RETURN NULL;
    END IF;

    ---- Route lookup
    SELECT
      route, rid, did, description, country, cinit, cinc, pinit, pinc, rate, cost
    INTO dest FROM routes
    WHERE
      rid = ANY((SELECT routes FROM groups WHERE gid = client.gid AND active)::varchar[]) AND
      ((route = SUBSTR(client.dialed, 1, 1) AND LENGTH(route) = 1) OR
       (route = SUBSTR(client.dialed, 1, 2) AND LENGTH(route) = 2) OR
       (route = SUBSTR(client.dialed, 1, 3) AND LENGTH(route) = 3) OR
       (LENGTH(route) >= 4 AND SUBSTR(route, 1, 4) = SUBSTR(client.dialed, 1, 4) AND (client.dialed LIKE (route || '%')))) AND
      active
    ORDER BY route DESC, cost ASC LIMIT 1;

    ---- Populate incoming route_cache
    INSERT INTO route_cache VALUES ('incoming', billid, client.prefix, dest.route, dest.rid, dest.did, dest.description, dest.country, dest.cinit, dest.cinc, dest.pinit, dest.pinc, dest.rate, dest.cost);

    IF dest.route IS NULL THEN
      RAISE NOTICE 'No route found';
      RETURN NULL;
    ELSE
      RAISE NOTICE 'Route:        %', dest.route;
      RAISE NOTICE 'Description:  %', dest.description;
    END IF;

    ---- Populate outgoing route_cache
    INSERT INTO route_cache VALUES ('outgoing', billid, client.prefix, dest.route, dest.rid, dest.did, dest.description, dest.country, dest.cinit, dest.cinc, dest.pinit, dest.pinc, dest.rate, dest.cost);

    ---- Form Yate reply & return
    SELECT
      (CASE WHEN LOWER(protocol) = 'wp' THEN
         LOWER(protocol) || '/' || host || '/' || prefix || client.dialed
       ELSE
         LOWER(protocol) || '/' || (CASE WHEN LOWER(protocol) = 'sip' THEN 'sip:' ELSE '' END) || prefix || client.dialed || '@' || host
       END) AS route,
      (CASE WHEN LOWER(protocol) = 'wp' THEN
         'no'::varchar
       ELSE
          (SELECT rtp_forward FROM clients WHERE prefix = client.prefix)
       END) AS rtp_forward
    INTO result FROM providers
    WHERE did = dest.did AND active;

    RAISE NOTICE 'Destination:  %', result.route;
    RAISE NOTICE 'Forward RTP:  %', result.rtp_forward;

    RETURN result;
  END;
$$ LANGUAGE plpgsql;