MS Teams - Verify Service

/**
 * Identifier: msTeamsVerifyService
 * Check for Active Service matching service_id and user_id
 *  OR Check for Active Services with the Contact Owner matching user_id return list of Active Services
 *  OR Verify that Active Service exists and return the Contact Owner
 */

/** @var array $request; */

$verifySQL = <<<SQL
SELECT TYPE, CONTACTS_RECID, OWNER, SERVICES_RECID, SERVICE_ID
FROM (
    SELECT 'CONTACT' AS TYPE,
           RECID as CONTACTS_RECID,
           CUSTOMER_NUMBER as OWNER,
           NULL as SERVICES_RECID,
           NULL as SERVICE_ID
    FROM CONTACTS
    WHERE CUSTOMER_NUMBER = :userId
UNION 
    SELECT 'OWNER' as TYPE,
           OWNER_CONTACTS_RECID as CONTACTS_RECID,
           C.CUSTOMER_NUMBER as OWNER,
           S.RECID as SERVICES_RECID,
           SERVICE_ID
    FROM SERVICES S 
    INNER JOIN CONTACTS C ON OWNER_CONTACTS_RECID = C.RECID
    INNER JOIN LISTS SERVICE_STATUS ON SERVICE_STATUS.RECID = S.SERVICE_STATUS_LISTS_RECID
    WHERE C.CUSTOMER_NUMBER = :userId
    AND SERVICE_STATUS.CODE = 'ACTIVE'
UNION 
    SELECT 'SERVICE' as TYPE,
           OWNER_CONTACTS_RECID as CONTACTS_RECID,
           C.CUSTOMER_NUMBER as OWNER,
           S.RECID as SERVICES_RECID,
           SERVICE_ID
    FROM SERVICES S
    INNER JOIN CONTACTS C ON S.OWNER_CONTACTS_RECID = C.RECID
    INNER JOIN LISTS SERVICE_STATUS ON SERVICE_STATUS.RECID = S.SERVICE_STATUS_LISTS_RECID
    WHERE S.SERVICE_ID = :serviceId
    AND SERVICE_STATUS.CODE = 'ACTIVE'
) VERIFY
SQL;

$verification = $this->query(
    $verifySQL,
    [
        ':userId' => $request['user_id'] ?? null,
        ':serviceId' => $request['service_id'] ?? null,
    ]);

$contact = $service = null;
$owner = [];
foreach ($verification as $verify) {
    if ($verify['TYPE'] === 'CONTACT') {
        $contact = $verify;
    } elseif ($verify['TYPE'] === 'OWNER') {
        $owner[] = $verify['SERVICE_ID'];
    } elseif ($verify['TYPE'] === 'SERVICE') {
        $service = $verify;
    }
}

$response['status'] = 'valid';
if (isset($request['user_id']) && $contact === null) {
    // user_id is not in CONTACTS table
    $response['status'] = 'invalid';
} elseif (isset($request['user_id']) && !isset($request['service_id'])) {
    $response['services'] = $owner;
} elseif (isset($request['user_id']) && isset($request['service_id'])) {
    if ($service['OWNER'] !== $request['user_id']) {
        // supplied user_id does not match the supplied Active service_id
        $response['status'] = 'invalid';
    }
} else {
    // !isset($request['user_id']) && isset($request['service_id'])
    if ($service === null) {
        $response['status'] = 'invalid';
    } else {
        $response['user_id'] = $service['OWNER'];
    }
}