/*
 *  IP Engineering RADIUS Servers
 *
 *  ippool-demo.sql - inserts demo data into  RADIUS IP Pools database
 *
 */


SET ROLE radius_user;


/*
 *  find user's previous address if available
 *
 *  This query is provided as an example, production systems should use the
 *  function ip_autoassign().
 */
\set myUsername    'jdoe8@example.net'
\set myNasName     'br1.sol'
\set myPoolName    'DHCP'
SELECT              ip.address       AS    address,
                    ip.modified      AS    modified,
                    ip.username      AS    username,
                    pool.name        AS    pool,
                    region.name      AS    region
   FROM             ip
   INNER JOIN       netblock
      ON            netblock.network >>=   ip.address
   INNER JOIN       region
      ON            region.id        =     netblock.regionId
   INNER JOIN       pool
      ON            pool.id          =     netblock.poolId
   INNER JOIN       nas
      ON            nas.regionId     =     region.id
   WHERE            ip.nasId         IS    NULL
      AND           ip.username      LIKE  lower( :'myUsername' )
      AND           nas.name         LIKE  lower( :'myNasName' )
      AND           pool.name        LIKE  lower( :'myPoolName' )
      AND           netblock.assignable  =     TRUE
   ORDER BY         ip.modified      DESC,
                    ip.address       ASC
   LIMIT            1;


/*
 *  find next available IP address for a given NAS and pool
 *
 *  This query is provided as an example, production systems should use the
 *  function ip_autoassign().
 */
 \set myNasName     'br1.sol'
 \set myPoolName    'DHCP'
SELECT              ip.address       AS    address,
                    ip.modified      AS    modified,
                    ip.username      AS    username,
                    pool.name        AS    pool,
                    region.name      AS    region
   FROM             ip
   INNER JOIN       netblock
      ON            netblock.network >>=   ip.address
         AND        netblock.assignable  IS    TRUE
   INNER JOIN       region
      ON            region.id        =     netblock.regionId
   INNER JOIN       pool
      ON            pool.id          =     netblock.poolId
   INNER JOIN       nas
      ON            nas.regionId     =     region.id
   WHERE            ip.nasId         IS    NULL
      AND           nas.name         LIKE  lower( :'myNasName' )
      AND           pool.name        LIKE  lower( :'myPoolName' )
      AND           netblock.assignable  =     TRUE
   ORDER BY         ip.modified      ASC,
                    ip.address       ASC
   LIMIT            1;


/*
 *  list all IP addresses with referenced/inferred data
 *
 *  This query will return a potentialy large dataset which makes it
 *  unsuitable for a function or stored procedure.  The implementation of
 *  `RETURN QUERY` in PostgreSQL 11 stores the entire result set before
 *  returning from a function.  If the dataset is very large, the result
 *  set will be written to disk to avoid memory exchaustion. This will lead
 *  to performance issues.
 *
 *  Additionally, the following query is very generic and most likely an
 *  administrator will need to customize the query to obtain the needed
 *  result set for a given need.
 */
 --
 -- Method 1:
SELECT DISTINCT      pool.name         AS poolName,
                     region.name       AS regionName,
                     ip.address        AS address,
                     TRUE              = ALL
                     (
                       SELECT          nb2.assignable
                          FROM         netblock   AS  nb2
                          WHERE        ip.address <<= nb2.network
                     )                 AS active,
                     (
                       nasId           IS NOT NULL
                     )                 AS assigned,
                     ip.modified       AS modified,
                     nas.name          AS nasName,
                     ip.username       AS username
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   INNER JOIN        pool
      ON             netblock.poolId   =   pool.id
   LEFT OUTER JOIN   region
      ON             netblock.regionId =   region.id
   LEFT OUTER JOIN   nas
      ON             ip.nasId          =   nas.id
   ORDER BY          address;
--
-- Method 2:
SELECT DISTINCT      pool.name         AS poolName,
                     region.name       AS regionName,
                     ip.address        AS address,
                     NOT EXISTS
                     (
                       SELECT          *
                          FROM         netblock   AS  nb2
                          WHERE        ip.address <<= nb2.network
                             AND NOT   nb2.assignable
                     )                 AS active,
                     (
                       nasId           IS NOT NULL
                     )                 AS assigned,
                     ip.modified       AS modified,
                     nas.name          AS nasName,
                     ip.username       AS username
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   INNER JOIN        pool
      ON             netblock.poolId   =   pool.id
   LEFT OUTER JOIN   region
      ON             netblock.regionId =   region.id
   LEFT OUTER JOIN   nas
      ON             ip.nasId          =   nas.id
   ORDER BY          address;


/*
 *  list all inactive IP addresses
 */
SELECT DISTINCT      ip.address        AS  ipAddress,
                     netblock.network  AS  network,
                     netblock.assignable   AS  active
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   WHERE             netblock.assignable   =   FALSE
   ORDER BY          ipAddress;


/*
 *  list all active IP addresses
 */
SELECT DISTINCT      netblock.regionId AS  regionId,
                     ip.address        AS  ipAddress,
                     netblock.assignable   AS  active
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   WHERE             netblock.assignable   =   TRUE
      AND            ip.address             NOT IN
                     (
                       SELECT          address AS ipAddress
                          FROM         ip
                          INNER JOIN   netblock
                             ON        ip.address          <<= netblock.network
                                AND    netblock.assignable     =   FALSE
                     )
   ORDER BY          ipAddress;


/* end of sql */