/*
 *  IP Engineering RADIUS Servers
 *
 *  ippool-schema.sql - builds database schema for RADIUS IP Pools
 *
 *  Usage:  psql -f ippool-schema.sql radius_ippool
 */


SET ROLE radius;
SET client_min_messages TO WARNING;
\set ON_ERROR_STOP on


-- /////////////////
-- //             //
-- //  Functions  //
-- //             //
-- /////////////////

/*
 *  Creates IP entries for the given network block
 *
 *  % SELECT ip_add_network(
 *       ipNetwork   := '198.51.100.0/24'
 *    );
 *  % SELECT ip_add_network(
 *       ipNetblockId := (
 *                         SELECT       id
 *                            FROM      netblock
 *                            WHERE     network = '198.51.100.0/24'
 *                       )
 *    );
 *
 *  ipNetwork
 *  : network address and network size being added to database
 *
 *  ipNetblockId
 *  : reference to netblock to add to database
 *
 *  Returns the number of new IP addresses registered
 */
CREATE OR REPLACE FUNCTION ip_add_network
(
   ipNetwork        CIDR            DEFAULT NULL,
   ipNetblockId     INTEGER         DEFAULT NULL
)
   RETURNS          INTEGER
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   ipAddress        INET;
   resultAddress    INET;
   ipAdded          INTEGER;
   ipMask           INTEGER;
BEGIN

   -- verifies input
   IF ( ( ipNetwork IS NULL ) AND ( ipNetblockId IS NULL ) ) THEN
      RAISE EXCEPTION 'must specify either ipNetwork or ipNetblockId';
   END IF;

   -- determines netblock
   if ( ipNetblockId IS NULL ) THEN
      SELECT        id              INTO ipNetblockId
        FROM        netblock
        WHERE       network         >>= ipNetwork;
   ELSE
      SELECT        network         INTO ipNetwork
         FROM       netblock
         WHERE      id              = ipNetblockId;
   END IF;
   if ( ( ipNetblockId IS NULL ) OR ( ipNetwork IS NULL ) ) THEN
      RAISE EXCEPTION 'specified netblock does not exist';
   END IF;

   -- restricts access to IPv4 addresses
   IF ( family(ipNetwork) != 4 ) THEN
      RAISE EXCEPTION 'only IPv4 addresses are supported';
   END IF;

   -- sets starting values
   ipAdded          := 0;
   ipAddress        := set_masklen( network(ipNetwork), 32 );

   -- loops through individual IP addresses
   LOOP
      EXIT WHEN     ipAddress > set_masklen( broadcast(ipNetwork), 32 );

      INSERT INTO   ip
                    ( address,
                      netblockId
                    )
         SELECT     ipAddress,
                    ipNetblockId
            WHERE   NOT EXISTS
                    ( SELECT address FROM ip WHERE address = ipAddress )
          RETURNING address INTO resultAddress;
      IF ( resultAddress IS NOT NULL ) THEN
         ipAdded    := ipAdded + 1;
      END IF;
      ipAddress     := ipAddress   + 1;

   END LOOP;

   RETURN ipAdded;
END;
$$;


/*
 *  Assigns specific IP address to specified user/session
 *
 *  % SELECT ip_assign(
 *       assignIp         := '198.51.100.1/32',
 *       assignUser       := 'jdoe@acsalaska.net'
 *    );
 *  % SELECT ip_assign(
 *       assignIp         := '198.51.100.1/32',
 *       assignUser       := 'jdoe@acsalaska.net',
 *       assignPortLimit  := 5,
 *       assignNas        := 'br1.cwc',
 *       assignForce      := FALSE
 *    );
 *
 *  assignIp
 *  : IP address to assign to user
 *
 *  assignUser
 *  : user to assign IP address
 *
 *  assignPortLimit
 *  : specifies maximum IP assignments per user allowed
 *
 *  assignNas
 *  : session NAS (attempt to guess if not specified)
 *
 *  assignForce
 *  : force IP assignment, even if netblock is not assignable (i.e. static
 *    assignments)
 *
 *  Returns TRUE if assigned and FALSE if not assigned
 */
CREATE OR REPLACE FUNCTION ip_assign
(
   assignIp         INET,
   assignUser       TEXT,
   assignPortLimit  INTEGER         DEFAULT 2,
   assignNas        TEXT            DEFAULT NULL,
   assignForce      BOOLEAN         DEFAULT FALSE
)
   RETURNS          BOOLEAN
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   assignNasId      INTEGER;
   assignPortCount  INTEGER;
BEGIN

   -- tallies existing sessions
   SELECT           count( address )  AS     ipCount
      INTO          assignPortCount
      FROM          ip
      WHERE         ip.username       LIKE   assignUser
         AND        ip.nasId          IS NOT NULL
         AND        ip.address        !=     assignIp;
   IF ( assignPortCount >= assignPortLimit ) THEN
      RETURN FALSE;
   END IF;

   -- verifies IP address is assignable
   IF ( assignForce = FALSE ) THEN
      PERFORM          network
         FROM          netblock
         WHERE         netblock.network >>= assignIp
            AND        netblock.busyout =   TRUE;
      IF FOUND THEN
         RETURN FALSE;
      END IF;
   END IF;

   -- attempts to determine NAS
   IF ( assignNas IS NOT NULL ) THEN
      SELECT        id
         INTO       assignNasId
         FROM       nas
         WHERE      name              LIKE assignNas;
   ELSE
      SELECT        nas.id
         INTO       assignNasId
         FROM       nas
         INNER JOIN region
            ON      region.id         = nas.regionId
         INNER JOIN netblock
            ON      netblock.regionId = regoin.id
         WHERE      netblock.network  >>= assignIp;
   END IF;
   IF ( assignNasId IS NULL ) THEN
      RETURN FALSE;
   END IF;

   -- updates record
   UPDATE           ip
      SET           nasId         = ( SELECT id FROM nas WHERE name LIKE assignNas ),
                    username      = assignUser
      WHERE         address       = set_masklen( assignIp, 32 )
         AND        nasId         IS NULL;

   RETURN FOUND;
END;
$$;


/*
 *  audits ip
 *
 *  % SELECT * FROM ip_audit();
 *
 *  Returns table of ip
 */
CREATE OR REPLACE FUNCTION ip_audit
(
)
   RETURNS          TABLE
   (
                    poolName              VARCHAR(32),
                    regionName            VARCHAR(32),
                    ipAddress             INET,
                    busyout               BOOLEAN,
                    assigned              BOOLEAN,
                    modified              TIMESTAMP WITH TIME ZONE,
                    nasName               VARCHAR(32),
                    username              VARCHAR(32)
   )
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
BEGIN

   RETURN QUERY
      SELECT DISTINCT   pool.name         AS poolName,
                        region.name       AS regionName,
                        ip.address        AS address,
                        TRUE              = ANY
                        (
                          SELECT          nb2.busyout
                             FROM         netblock   AS  nb2
                             WHERE        ip.address <<= nb2.network
                        )                 AS assignable,
                        (
                          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;

END;
$$;


/*
 *  Auto-assigns IP address to specified user/session
 *
 *  % SELECT ip_autoassign(
 *       assignUser       := 'jdoe@acsalaska.net',
 *       assignNas        := 'br1.sol',
 *       assignPool       := 'dhcp'
 *    );
 *  % SELECT ip_autoassign(
 *       assignUser       := 'jdoe@acsalaska.net',
 *       assignNas        := 'br1.sol',
 *       assignPool       := 'dhcp',
 *       assignPortLimit  := 5,
 *       assignPrevIp     := TRUE
 *    );
 *
 *  assignUser
 *  : user to assign IP address
 *
 *  assignNas
 *  : NAS to assign to IP address and implies region from which to pull IP
 *
 *  assignPool
 *  : IP pool to assign from which to assign IP address
 *
 *  assignPortLimit
 *  : specifies maximum IP assignments per user allowed
 *
 *  assignPrevIp (default: TRUE)
 *  : attempt to re-assign user's previous IP address
 *
 *  Returns INET with value of assigned IP address
 */
CREATE OR REPLACE FUNCTION ip_autoassign
(
   assignUser       TEXT,
   assignNas        TEXT,
   assignPool       TEXT,
   assignPortLimit  INTEGER          DEFAULT 2,
   assignPrevIp     BOOLEAN          DEFAULT TRUE
)
   RETURNS          INET
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   assignedIp       INET;
   assignNasId      INTEGER;
   assignRegionId   INTEGER;
   assignPoolId     INTEGER;
   assignPortCount  INTEGER;
BEGIN

   -- tallies existing sessions
   SELECT           count( address )
      INTO          assignPortCount
      FROM          ip
      WHERE         ip.username       LIKE   lower( assignUser )
         AND        ip.nasId          IS NOT NULL;
   IF ( assignPortCount >= assignPortLimit ) THEN
      RETURN NULL;
   END IF;

   -- determines NAS ID and region ID
   SELECT           region.id,
                    nas.id
      INTO          assignRegionId,
                    assignNasId
      FROM          nas
      INNER JOIN    region
         ON         region.id        = nas.regionId
      WHERE         nas.name         LIKE lower( assignNas );
   IF NOT FOUND THEN
      RETURN NULL;
   END IF;

   -- determines pool ID
   SELECT           id
      INTO          assignPoolId
      FROM          pool
      WHERE         pool.name        LIKE lower( assignPool );
   IF NOT FOUND THEN
      RETURN NULL;
   END IF;

   -- search for user's previous address in region
   IF ( assignPrevIp IS TRUE ) THEN
      SELECT        ip.address
         INTO       assignedIp
         FROM       ip
         INNER JOIN netblock         AS   nb
            ON      nb.network       >>=  ip.address
         WHERE      ip.nasId         IS   NULL
            AND     ip.username      LIKE lower( assignUser )
            AND     nb.poolId        =    assignPoolId
            AND     nb.regionId      =    assignRegionId
            AND     nb.assignable    =    TRUE
         ORDER BY   ip.modified      DESC,
                    ip.address       ASC
         LIMIT      1;
   END IF;

   -- if previous address is unavailable, find longest idle address in region
   IF ( assignedIp IS NULL ) THEN
      SELECT        ip.address
         INTO       assignedIp
         FROM       ip
         INNER JOIN netblock         AS   nb
            ON      nb.network       >>=  ip.address
         WHERE      ip.nasId         IS   NULL
            AND     nb.poolId        =    assignPoolId
            AND     nb.regionId      =    assignRegionId
            AND     nb.assignable    =    TRUE
         ORDER BY   ip.modified      ASC,
                    ip.address       ASC
         LIMIT      1;
   END IF;

   -- search for user's previous address out of region
   IF ( ( assignedIp IS NULL ) AND ( assignPrevIp IS TRUE ) ) THEN
      SELECT        ip.address
         INTO       assignedIp
         FROM       ip
         INNER JOIN netblock         AS   nb
            ON      nb.network       >>=  ip.address
         WHERE      ip.nasId         IS   NULL
            AND     ip.username      LIKE lower( assignUser )
            AND     nb.poolId        =    assignPoolId
            AND     nb.regionId      IS   NULL
            AND     nb.assignable    =    TRUE
         ORDER BY   ip.modified      DESC,
                    ip.address       ASC
         LIMIT      1;
   END IF;

   -- if previous address is unavailable, find longest idle address out of region
   IF ( assignedIp IS NULL ) THEN
      SELECT        ip.address
         INTO       assignedIp
         FROM       ip
         INNER JOIN netblock         AS   nb
            ON      nb.network       >>=  ip.address
         WHERE      ip.nasId         IS   NULL
            AND     nb.poolId        =    assignPoolId
            AND     nb.regionId      IS   NULL
            AND     nb.assignable    =    TRUE
         ORDER BY   ip.modified      ASC,
                    ip.address       ASC
         LIMIT      1;
   END IF;

   -- exits if an appropiate IP address was not found
   IF ( assignedIp IS NULL ) THEN
      RETURN NULL;
   END IF;

   -- update record
   UPDATE           ip
      SET           nasId            =    assignNasId,
                    username         =    assignUser
      WHERE         address          =    assignedIp;
   IF NOT FOUND THEN
      RETURN NULL;
   END IF;

   RETURN assignedIp;
END;
$$;



/*
 *  updates timestamp of addresses
 *
 *  returns trigger
 */
CREATE OR REPLACE FUNCTION ip_release
(
   assignIp         INET
)
   RETURNS          BOOLEAN
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
BEGIN

   -- updates record
   UPDATE           ip
      SET           nasId         = NULL
      WHERE         address       = set_masklen( assignIp, 32 );

   RETURN FOUND;
END;
$$;


/*
 *  Creates entry for NAS client
 *
 *  % SELECT nas_add(
 *       nasName    := 'br1.cwc',
 *       regionName := 'Anchorage'
 *    );
 *
 *  nasName
 *  : name of NAS being added to database
 *
 *  regionName
 *  : the name of the region in which the NAS is physically located
 *
 *  Returns the `id` of the created nas client
 */
CREATE OR REPLACE FUNCTION nas_add
(
   nasName          VARCHAR(32),
   regionName       VARCHAR(128)
)
   RETURNS          INTEGER
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   nasRegionId      INTEGER;
   returnId         INTEGER;
BEGIN
   -- searches for region id
   SELECT           id      INTO nasRegionId
      FROM          region
      WHERE         name    LIKE lower(regionName);
   IF ( nasRegionId IS NULL ) THEN
      RAISE EXCEPTION 'invalid region';
   END IF;

   -- adds new pool
   INSERT INTO      nas
                    ( name,
                      regionId
                    )
      VALUES        ( lower(nasName),
                      nasRegionId
                    )
      RETURNING     id INTO returnId;

   RETURN returnId;
END;
$$;


/*
 *  Creates entry for network block
 *
 *  % SELECT netblock_add(
 *       netAddress    := '198.51.100.0/24',
 *       poolName      := 'PPPoE'
 *    );
 *  % SELECT netblock_add(
 *       netAddress    := '203.0.113.0/24',
 *       poolName      := 'DHCP',
 *       regionName    := 'Soldotna',
 *       netAssignable := FALSE,
 *       netDesc       := 'Lab IP Pool',
 *       addIpEntries  := TRUE
 *    );
 *
 *  netAddress
 *  : network address and network size being added to database
 *
 *  poolName
 *  : IP pool network is being assigned
 *
 *  regionName
 *  : the name of the region in which the network will physically serve
 *
 *  netAssignable
 *  : TRUE if addresses within network are assignable, FALSE if addresses are
 *  : not assignable
 *
 *  netDesc
 *  : human readable description of the network
 *
 *  addIpEntries
 *  : add coorisponding ip table entries for network
 *
 *  Returns the `id` of the created nas client
 */
CREATE OR REPLACE FUNCTION netblock_add
(
   netAddress       CIDR,
   poolName         VARCHAR(32),
   regionName       VARCHAR(32)  DEFAULT NULL,
   netBusyout       BOOLEAN      DEFAULT FALSE,
   netDesc          VARCHAR(128) DEFAULT NULL,
   addIpEntries     BOOLEAN      DEFAULT TRUE
)
   RETURNS          INTEGER
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   netRegionId      INTEGER      DEFAULT NULL;
   netPoolId        INTEGER;
   returnId         INTEGER      DEFAULT 1;
BEGIN

   -- restricts access to IPv4 addresses
   IF ( family(netAddress) != 4 ) THEN
      RAISE EXCEPTION 'only IPv4 addresses are supported';
   END IF;

   -- searches for region id
   if ( regionName IS NOT NULL ) THEN
      SELECT        id      INTO netRegionId
         FROM       region
         WHERE      name    LIKE lower(regionName);
   END IF;

   -- searches for pool id
   SELECT           id      INTO netPoolId
      FROM          pool
      WHERE         name    LIKE lower(poolName);
   IF ( netPoolId IS NULL ) THEN
      RAISE EXCEPTION 'invalid pool';
   END IF;

   -- adds new pool
   INSERT INTO      netblock
                    ( network,
                      poolId,
                      regionId,
                      busyout,
                      description
                    )
      VALUES        ( netAddress,
                      netPoolId,
                      netRegionId,
                      netBusyout,
                      netDesc
                    )
      RETURNING     id INTO returnId;

   -- adds IP entries
   if ( addIpEntries IS NOT FALSE ) THEN
      PERFORM ip_add_network( ipNetblockId := returnId);
   END IF;

   RETURN returnId;
END;
$$;


/*
 *  audits netblocks
 *
 *  % SELECT * FROM netblock_audit();
 *
 *  Returns table of netblocks
 */
CREATE OR REPLACE FUNCTION netblock_audit
(
)
   RETURNS          TABLE
   (
                    poolName              VARCHAR(32),
                    regionName            VARCHAR(32),
                    network               CIDR,
                    busyout               BOOLEAN,
                    ipAssigned            BIGINT,
                    ipUnassigned          BIGINT,
                    ipAvailable           BIGINT,
                    ipTotal               BIGINT,
                    description           VARCHAR(256)
   )
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
BEGIN

   RETURN QUERY
      SELECT DISTINCT   pool.name            AS poolName,
                        region.name          AS regionname,
                        netblock.network     AS network,
                        netblock.busyout     AS busyout,
                        (
                         SELECT              count(address)
                            FROM             ip
                            WHERE            address <<= netblock.network
                               AND           ip.nasId IS NOT NULL
                        )                    AS ipAssigned,
                        (
                          SELECT             count(address)
                            FROM             ip
                            WHERE            address <<= netblock.network
                               AND           ip.nasId IS NULL
                        )                    AS ipUnassigned,
                        (
                          SELECT DISTINCT    count(address)
                             FROM            ip
                             INNER JOIN      netblock       AS  nb2
                                ON           nb2.id         =   ip.netblockId
                             WHERE           nb2.busyout IS FALSE
                                AND          ip.address     <<= netblock.network
                                AND          ip.nasId       IS NULL
                        )                    AS ipAvailable,
                        (
                          SELECT             count(address)
                            FROM             ip
                            WHERE            address <<= netblock.network
                        )                    AS ipTotal,
                        netblock.description AS description
      FROM              netblock
      INNER JOIN        pool
         ON             netblock.poolId   =   pool.id
      LEFT OUTER JOIN   region
         ON             netblock.regionId =   region.id;

END;
$$;


/*
 *  audits netblocks
 *
 *  % SELECT * FROM netblock_audit();
 *
 *  Returns table of netblocks
 */
CREATE OR REPLACE FUNCTION region_audit
(
)
   RETURNS          TABLE
   (
                    poolName              VARCHAR(32),
                    regionName            VARCHAR(32),
                    ipAssignedFree        BIGINT,
                    ipAssignedBusy        BIGINT,
                    ipAvailable           BIGINT,
                    ipBusyout             BIGINT,
                    ipTotal               BIGINT,
                    description           TEXT
   )
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
BEGIN

   RETURN QUERY
      SELECT DISTINCT   pool.name            AS poolName,
                        region.name          AS regionname,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock       AS nb2
                               ON            nb2.id         =  ip.netblockId
                            WHERE            nb2.regionId   =  region.id
                               AND           nb2.poolId     =  pool.id
                               AND           nb2.busyout    IS FALSE
                               AND           ip.nasId       IS NOT NULL
                        )                    AS ipAssignedFree,
                         (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock       AS nb2
                               ON            nb2.id         =  ip.netblockId
                            WHERE            nb2.regionId   =  region.id
                               AND           nb2.poolId     =  pool.id
                               AND           nb2.busyout    IS TRUE
                               AND           ip.nasId       IS NOT NULL
                        )                    AS ipAssignedBusy,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock       AS nb2
                               ON            nb2.id         =  ip.netblockId
                            WHERE            nb2.regionId   =  region.id
                               AND           nb2.poolId     =  pool.id
                               AND           nb2.busyout    IS FALSE
                               AND           ip.nasId       IS NULL
                        )                    AS ipAvailable,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock     AS nb2
                               ON            nb2.id       =  ip.netblockId
                            WHERE            nb2.regionId =  region.id
                               AND           nb2.poolId   =  pool.id
                               AND           nb2.busyout  IS TRUE
                        )                    AS ipBusyout,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock     AS nb2
                               ON            nb2.id       =  ip.netblockId
                            WHERE            nb2.regionId =  region.id
                               AND           nb2.poolId   =  pool.id
                        )                    AS ipTotal,
                        (
                          'Regional addresses in '
                             || upper(region.Name)
                             || ' for '
                             || upper(pool.name)
                             || ' pool'
                        )                    AS description
         FROM           netblock
         INNER JOIN     region
            ON          region.id         =   netblock.regionId
         INNER JOIN     pool
            ON          pool.id           =   netblock.poolId
      UNION ALL
      SELECT DISTINCT   pool.name            AS poolName,
                        NULL                 AS regionname,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock       AS nb2
                               ON            nb2.id         =  ip.netblockId
                            WHERE            nb2.regionId   IS NULL
                               AND           nb2.poolId     =  pool.id
                               AND           nb2.busyout    IS FALSE
                               AND           ip.nasId       IS NOT NULL
                        )                    AS ipAssignFree,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock       AS nb2
                               ON            nb2.id         =  ip.netblockId
                            WHERE            nb2.regionId   IS NULL
                               AND           nb2.poolId     =  pool.id
                               AND           nb2.busyout    IS TRUE
                               AND           ip.nasId       IS NOT NULL
                        )                    AS ipAssignFree,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock       AS nb2
                               ON            nb2.id         =  ip.netblockId
                            WHERE            nb2.regionId   IS NULL
                               AND           nb2.poolId     =  pool.id
                               AND           nb2.busyout    IS FALSE
                               AND           ip.nasId       IS NULL
                        )                    AS ipAvailable,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock     AS nb2
                               ON            nb2.id       =  ip.netblockId
                            WHERE            nb2.regionId IS NULL
                               AND           nb2.poolId   =  pool.id
                               AND           nb2.busyout  IS TRUE
                        )                    AS ipBusyout,
                        (
                          SELECT             COUNT( address )
                            FROM             ip
                            INNER JOIN       netblock     AS nb2
                               ON            nb2.id       =  ip.netblockId
                            WHERE            nb2.regionId IS NULL
                               AND           nb2.poolId   =  pool.id
                        )                    AS ipTotal,
                        (
                          'Overflow addresses for '
                             || upper(pool.name)
                             || ' pool'
                        )                    AS description
         FROM           netblock
         INNER JOIN     pool
            ON          pool.id           =   netblock.poolId
         WHERE          netblock.regionId IS NULL;

END;
$$;

/* end of sql */