text: const router = require('express').Router();
  const {promisePool} = require('../../db');
  const {DbErrorUnprocessableRequest} = require('../../utils/errors');
  const Webhook = require('../../models/webhook');
  const ServiceProvider = require('../../models/service-provider');
  const Account = require('../../models/account');
  const VoipCarrier = require('../../models/voip-carrier');
  const Application = require('../../models/application');
  const PhoneNumber = require('../../models/phone-number');
  const {hasServiceProviderPermissions, parseServiceProviderSid} = require('./utils');
  const sysError = require('../error');
  const decorate = require('./decorate');
  const preconditions = {
    'delete': noActiveAccounts
  };
  const sqlDeleteSipGateways = `DELETE from sip_gateways 
  WHERE voip_carrier_sid IN (
    SELECT voip_carrier_sid 
    FROM voip_carriers 
    WHERE service_provider_sid = ?
  )`;
  const sqlDeleteSmppGateways = `DELETE from smpp_gateways 
  WHERE voip_carrier_sid IN (
    SELECT voip_carrier_sid 
    FROM voip_carriers 
    WHERE service_provider_sid = ?
  )`;
  
  /* can not delete a service provider if it has any active accounts */
  async function noActiveAccounts(req, sid) {
    const activeAccounts = await ServiceProvider.getForeignKeyReferences('accounts.service_provider_sid', sid);
    if (activeAccounts > 0) throw new DbErrorUnprocessableRequest('cannot delete service provider with active accounts');
  
    /* ok we can delete -- no active accounts.  remove carriers and speech credentials */
    await promisePool.execute('DELETE from speech_credentials WHERE service_provider_sid = ?', [sid]);
    await promisePool.query(sqlDeleteSipGateways, [sid]);
    await promisePool.query(sqlDeleteSmppGateways, [sid]);
    await promisePool.query('DELETE from voip_carriers WHERE service_provider_sid = ?', [sid]);
  }
  
  decorate(router, ServiceProvider, ['delete'], preconditions);
  
  router.use('/:sid/SpeechCredentials', hasServiceProviderPermissions, require('./speech-credentials'));
  router.use('/:sid/PredefinedCarriers', hasServiceProviderPermissions, require('./add-from-predefined-carrier'));
  router.get('/:sid/Accounts', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const service_provider_sid = parseServiceProviderSid(req);
      const results = await Account.retrieveAll(service_provider_sid);
      res.status(200).json(results);
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  router.get('/:sid/Applications', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const service_provider_sid = parseServiceProviderSid(req);
      const results = await Application.retrieveAll(service_provider_sid);
      res.status(200).json(results);
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  router.get('/:sid/PhoneNumbers', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const service_provider_sid = parseServiceProviderSid(req);
      const results = await PhoneNumber.retrieveAllForSP(service_provider_sid);
      res.status(200).json(results);
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  router.get('/:sid/VoipCarriers', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const service_provider_sid = parseServiceProviderSid(req);
      const results = await VoipCarrier.retrieveAllForSP(service_provider_sid);
      res.status(200).json(results);
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  router.post('/:sid/VoipCarriers', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const service_provider_sid = parseServiceProviderSid(req);
      const uuid = await VoipCarrier.make({...req.body, service_provider_sid});
      res.status(201).json({sid: uuid});
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  router.put('/:sid/VoipCarriers/:voip_carrier_sid', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const rowsAffected = await VoipCarrier.update(req.params.voip_carrier_sid, req.body);
      if (rowsAffected === 0) {
        return res.sendStatus(404);
      }
      res.status(204).end();
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  router.get(':sid/Acccounts', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const service_provider_sid = parseServiceProviderSid(req);
      const results = await Account.retrieveAll(service_provider_sid);
      res.status(200).json(results);
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  
  /* add */
  router.post('/', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
  
      // create webhooks if provided
      const obj = Object.assign({}, req.body);
      for (const prop of ['registration_hook']) {
        if (obj[prop]) {
          obj[`${prop}_sid`] = await Webhook.make(obj[prop]);
          delete obj[prop];
        }
      }
  
      //logger.debug(`Attempting to add account ${JSON.stringify(obj)}`);
      const uuid = await ServiceProvider.make(obj);
      res.status(201).json({sid: uuid});
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  
  /* list */
  router.get('/', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const results = await ServiceProvider.retrieveAll();
      res.status(200).json(results);
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  
  /* retrieve */
  router.get('/:sid', async(req, res) => {
    const logger = req.app.locals.logger;
    try {
      const results = await ServiceProvider.retrieve(req.params.sid);
      if (results.length === 0) return res.status(404).end();
      return res.status(200).json(results[0]);
    }
    catch (err) {
      sysError(logger, res, err);
    }
  });
  
  /* update */
  router.put('/:sid', async(req, res) => {
    const sid = req.params.sid;
    const logger = req.app.locals.logger;
    try {
      // create webhooks if provided
      const obj = Object.assign({}, req.body);
      for (const prop of ['registration_hook']) {
        if (prop in obj && Object.keys(obj[prop]).length) {
          if ('webhook_sid' in obj[prop]) {
            const sid = obj[prop]['webhook_sid'];
            delete obj[prop]['webhook_sid'];
            await Webhook.update(sid, obj[prop]);
          }
          else {
            const sid = await Webhook.make(obj[prop]);
            obj[`${prop}_sid`] = sid;
          }
        }
        else {
          obj[`${prop}_sid`] = null;
        }
        delete obj[prop];
      }
  
      const rowsAffected = await ServiceProvider.update(sid, obj);
      if (rowsAffected === 0) {
        return res.status(404).end();
      }
      res.status(204).end();
    } catch (err) {
      sysError(logger, res, err);
    }
  });
  
  module.exports = router;
  