MS Teams - Add Service

/**
 * Identifier: msTeamsAddService
 * Check for INACTIVE services with the same Contact Owner
 *  OR Check the AUDIT for previously used Services
 *  OR Grab an available number - oldest available (only pick 578 numbers)
 *
 * Make sure the Serv Catalog is "Phones / Voice Service > Phone Service"
 * Set the Service Host to "MS Teams"
 * Set the Billable flag to false
 * Set a Contact Owner based on the primary User ID from MS Teams "user_id"
 */

/** @var array $request; */

$response = [];
$serviceCatalog = "Phones/Voice Services > Phone Service";

$scatalog = $this->query(<<<SQL
        SELECT SCAT.RECID, SC.REPORT_911
        FROM SERV_CATALOG_TEXTPATH SCAT
        INNER JOIN SERV_CATALOG SC
        ON SC.RECID = SCAT.RECID
        WHERE SCAT.PATH = :serviceCatalog
    SQL,
    [':serviceCatalog' => $serviceCatalog]
);

$teamsSH = 8; //MS Teams
$servStatus = 373; // Active

$contact = $this->query(<<<SQL
        SELECT RECID
        FROM CONTACTS
        WHERE CUSTOMER_NUMBER = :customerNumber
    SQL,
    [':customerNumber' => $request['user_id']]
);

if ($scatalog) {
    if ($contact) {
        $servicesAudit = $availableService = null;

        $inactiveServices = $this->query(<<<SQL
            SELECT S.RECID, SERVICE_ID, SERVICE_ID_FMT
            FROM SERVICES S
            INNER JOIN LISTS S_STATUS ON S_STATUS.RECID = S.SERVICE_STATUS_LISTS_RECID
            WHERE S_STATUS.CODE = 'INACTIVE'
            AND S.SERVICE_ID LIKE '225578%'
            AND S.OWNER_CONTACTS_RECID = {$contact[0]['RECID']}
            ORDER BY S.STATUS_DATE ASC
            LIMIT 1
        SQL);

        if (empty($inactiveServices)) {
            $date = new DateTime();
            $p1m = new DateInterval("P1M");
            $p4m = new DateInterval("P4M");

            $auditTables = [
                'AUDITS',
                'AUDITS_' . $date->sub($p4m)->format('ym'),
                'AUDITS_' . $date->sub($p1m)->format('ym'),
                'AUDITS_' . $date->sub($p1m)->format('ym'),
            ];

            foreach ($auditTables as $auditTable) {
                $servicesAudit = $this->query(<<<SQL
                    SELECT S.RECID, SERVICE_ID, SERVICE_ID_FMT
                    FROM {$auditTable} A
                    INNER JOIN SERVICES S ON A.PRIMARY_KEY = S.RECID AND A.TABLE_NAME = 'SERVICES'
                    INNER JOIN LISTS S_STATUS ON S_STATUS.RECID = S.SERVICE_STATUS_LISTS_RECID
                    WHERE S_STATUS.CODE = 'AVAILABLE'
                    AND S.SERVICE_ID LIKE '225578%'
                    AND CAST(JSON_EXTRACT(A.FROM_DATA, '$.OWNER_CONTACTS_RECID') AS UNSIGNED) = {$contact[0]['RECID']}
                    ORDER BY S.STATUS_DATE ASC
                    LIMIT 1
                SQL);

                if (!empty($servicesAudit)) break;
            }

            if (empty($servicesAudit)) {
                $availableService = $this->query(<<<SQL
                    SELECT S.RECID, SERVICE_ID, SERVICE_ID_FMT
                    FROM SERVICES S
                    INNER JOIN LISTS S_STATUS ON S_STATUS.RECID = S.SERVICE_STATUS_LISTS_RECID
                    WHERE S_STATUS.CODE = 'AVAILABLE'
                    AND S.SERVICE_ID LIKE '225578%'
                    ORDER BY S.STATUS_DATE ASC
                    LIMIT 1
                SQL);
            }
        }

        // if available is not null then it did not find the other options
        $service = $availableService ?? $servicesAudit ?? $inactiveServices;

        if ($service) {

            $udf = $this->query(<<<SQL
                SELECT L.RECID
                FROM LISTS L
                INNER JOIN LIST_TYPES LT ON L.LIST_TYPES_RECID = LT.RECID
                WHERE LT.TYPE = 'UDF_TEAMS_FEATURES'
                AND (L.CODE = 'PPN' OR L.CODE = 'PRIMARY_PHONE_NUMBER')
            SQL);

            $result = $this->call(
                'saveService',
                [
                    'recid' => $service[0]['RECID'],
                    'serv_catalog' => $scatalog[0]['RECID'],
                    'status' => $servStatus,
                    'service_host' => $teamsSH,
                    'owner_type' => 'contact',
                    'owner' => $contact[0]['RECID'],
                    'remarks' => 'Activated by MS Teams API',
                    'billable' => 0,
                    'directory' => 0,
                    'report_911' => $scatalog[0]['REPORT_911'],
                    'udf-TEAMS_FEATURES' => $udf[0]['RECID']
                ]
            );
            if ($result['status'] === 'success') {
                $response['status'] = 'success';
                $response['service_id'] = $service[0]['SERVICE_ID'];
                $response['msteams_type'] = 'Primary Phone Number';
                $response['user_id'] = $request['user_id'];
            } else {
                $response['status'] = 'error';
                $response['message'] = "Failed to Update Service [{$service[0]['SERVICE_ID_FMT']}]: {$result['message']}";
            }
        } else {
            $response['status'] = 'error';
            $response['message'] = 'Cannot find Available Service with Service ID like: (225) 578-';
        }
    } else {
        $response['status'] = 'error';
        $response['message'] = 'Cannot find Contact Owner with Customer Number: ' . $request['user_id'];
    }
} else {
    $response['status'] = 'error';
    $response['message'] = "Cannot find correct Service Catalog: ${serviceCatalog}";
}