pbx/app/app.py

#!/usr/bin/env python3

import asyncio
import aiohttp
import logging
import os
import re
import json
from datetime import date as date
from datetime import datetime as dt
from datetime import timedelta as td
from datetime import timezone as dtz
from typing import Any, Optional
from functools import wraps
from secrets import compare_digest
from databases import Database
from quart import jsonify, request, render_template_string, abort, current_app
from quart.json import JSONEncoder
from quart_openapi import Pint, Resource
from http import HTTPStatus
from panoramisk import Manager, Message
from utils import *
from cel import *
from logging.config import dictConfig
from pprint import pformat
from inspect import getmembers
from aiohttp.resolver import AsyncResolver
import copy

class ApiJsonEncoder(JSONEncoder):
  def default(self, o):
    if isinstance(o, dt):
      return o.astimezone(dtz.utc).replace(tzinfo=None).isoformat() + 'Z'
    if isinstance(o, CdrChannel):
      return str(o)
    if isinstance(o, CdrEvent):
      return o.__dict__
    if isinstance(o, CdrEvents) or isinstance(o, CelEvents):
      return o.all
    if isinstance(o, CdrCall) or isinstance(o, CelCall):
      return o.__dict__
    return JSONEncoder.default(self, o)

class PintDB:
  def __init__(self, app: Optional[Pint] = None) -> None:
    self.init_app(app)
    self._db = Database(app.config["DB_URI"])

  def init_app(self, app: Pint) -> None:
    app.before_serving(self._before_serving)
    app.after_serving(self._after_serving)

  async def _before_serving(self) -> None:
    await self._db.connect()

  async def _after_serving(self) -> None:
    await self._db.disconnect()

  def __getattr__(self, name: str) -> Any:
    return getattr(self._db, name)

# One asyncio event loop is used for AMI communication and HTTP requests routing with Quart
main_loop = asyncio.get_event_loop()

app = Pint(__name__, title=os.getenv('APP_TITLE', 'PBX API'), no_openapi=True)
app.json_encoder = ApiJsonEncoder
app.config.update({
  'TITLE':                  os.getenv('APP_TITLE', 'PBX API'),
  'APPLICATION_ROOT':       os.getenv('APP_APPLICATION_ROOT', None),
  'SCHEME':                 os.getenv('APP_SCHEME', 'http'),
  'FQDN':                   os.getenv('APP_FQDN', '127.0.0.1'),
  'PORT':               int(os.getenv('APP_API_PORT', 8000)),
  'BODY_TIMEOUT':       int(os.getenv('APP_BODY_TIMEOUT', 60)),
  'DEBUG':                  os.getenv('APP_DEBUG', 'False').lower() in TRUEs,
  'MAX_CONTENT_LENGTH': int(os.getenv('APP_MAX_CONTENT_LENGTH', 16777216)),
  'AMI_HOST':               os.getenv('APP_AMI_HOST', '127.0.0.1'),
  'AMI_PORT':           int(os.getenv('APP_AMI_PORT', 5038)),
  'AMI_USERNAME':           os.getenv('APP_AMI_USERNAME', 'app'),
  'AMI_SECRET':             os.getenv('APP_AMI_SECRET', 'secret'),
  'AMI_PING_DELAY':     int(os.getenv('APP_AMI_PING_DELAY', 10)),
  'AMI_PING_INTERVAL':  int(os.getenv('APP_AMI_PING_INTERVAL', 10)),
  'AMI_TIMEOUT':        int(os.getenv('APP_AMI_TIMEOUT', 5)),
  'AUTH_HEADER':            os.getenv('APP_AUTH_HEADER', 'APP-auth-token'),
  'AUTH_SECRET':            os.getenv('APP_AUTH_SECRET', '3bfbeaabf363dd64fe263bd36830a6b6'),
  'SWAGGER_JS_URL':         os.getenv('APP_SWAGGER_JS_URL', SWAGGER_JS_URL),
  'SWAGGER_CSS_URL':        os.getenv('APP_SWAGGER_CSS_URL', SWAGGER_CSS_URL),
  'STATE_CALLBACK_URL':     os.getenv('APP_STATE_CALLBACK_URL', None),
  'DB_URI':                 'mysql://{}:{}@{}:{}/{}'.format(os.getenv('MYSQL_USER', 'asterisk'),
                                                            os.getenv('MYSQL_PASSWORD', 'secret'),
                                                            os.getenv('MYSQL_SERVER', 'db'),
                                                            os.getenv('APP_PORT_MYSQL', '3306'),
                                                            os.getenv('FREEPBX_CDRDBNAME', None)),
  'EXTRA_API_URL':          os.getenv('APP_EXTRA_API_URL', None)})

app.cache = {'devices':{},
             'usermap':{},
             'devicemap':{},
             'usernamemap':{},
             'ustates':{},
             'pstates':{},
             'queues':{},
             'calls':{},
             'cel_queue_calls':{},
             'cel_calls':{}}

manager = Manager(
  loop=main_loop,
  host=app.config['AMI_HOST'],
  port=app.config['AMI_PORT'],
  username=app.config['AMI_USERNAME'],
  secret=app.config['AMI_SECRET'],
  ping_delay=app.config['AMI_PING_DELAY'],
  ping_interval=app.config['AMI_PING_INTERVAL'],
  reconnect_timeout=app.config['AMI_TIMEOUT'],
)

def authRequired(func):
  @wraps(func)
  async def authWrapper(*args, **kwargs):
    request.user = None
    request.device = None
    request.admin = False
    auth = request.authorization
    headers = request.headers
    if ((auth is not None) and
        (auth.type == "basic") and
        (auth.username in current_app.cache['devices']) and
        (compare_digest(auth.password, current_app.cache['devices'][auth.username]))):
      request.device = auth.username
      if request.device in current_app.cache['usermap']:
        request.user = current_app.cache['usermap'][request.device]
      return await func(*args, **kwargs)
    elif ((current_app.config['AUTH_HEADER'].lower() in headers) and
          (headers[current_app.config['AUTH_HEADER'].lower()] == current_app.config['AUTH_SECRET'])):
      request.admin = True
      return await func(*args, **kwargs)
    else:
      abort(401)
  return authWrapper

db = PintDB(app)

@manager.register_event('FullyBooted')
@manager.register_event('Reload')
async def reloadCallback(mngr: Manager, msg: Message):
  await refreshDevicesCache()
  await refreshStatesCache()
  await refreshQueuesCache()
  await rebindLostDevices()
  # await db.execute(query='CREATE TABLE IF NOT EXISTS callback_urls (device VARCHAR(16) PRIMARY KEY, url VARCHAR(255))')

@manager.register_event('ExtensionStatus')
async def extensionStatusCallback(mngr: Manager, msg: Message):
  user = msg.exten
  state = msg.statustext.lower()
  #app.logger.warning('ExtensionStatus({}, {})'.format(user, state))
  if user in app.cache['ustates']:
    prevState = getUserStateCombined(user)
    app.cache['ustates'][user] = state
    combinedState = getUserStateCombined(user)
    if combinedState != prevState:
      await userStateChangeCallback(user, combinedState, prevState)

@manager.register_event('PresenceStatus')
async def presenceStatusCallback(mngr: Manager, msg: Message):
  user = msg.exten #hint = msg.hint
  state = msg.status.lower()
  if user in app.cache['ustates']:
    prevState = getUserStateCombined(user)
    app.cache['pstates'][user] = state
    combinedState = getUserStateCombined(user)
    if combinedState != prevState:
      await userStateChangeCallback(user, combinedState, prevState)

@manager.register_event('Hangup')
async def hangupCallback(mngr: Manager, msg: Message):
  if msg.uniqueid in app.cache['calls']:
    del app.cache['calls'][msg.uniqueid]

@manager.register_event('VarSet')
async def VarSetCallback(mngr: Manager, msg: Message):
  m = re.search(r"(.*savedb_)(.*)", msg.variable)
  if (m):
    varname = m.group(2)
    value = msg.value
    app.logger.warning('insert into call_values {}, {}, {}'.format(msg.linkedid,varname,value))
    await db.execute(query='insert into call_values (linkedid,name,value) values (:linkedid,:name,:value);',values={'linkedid': msg.linkedid,'name': varname,'value':value})

@manager.register_event('Newchannel')
async def newchannelCallback(mngr: Manager, msg: Message):
  if (msg.channelstate == '4') and ('HTTP_CLIENT' in app.config):
    did = None
    cid = None
    user = None
    device = None
    uid = None
    if msg.context.startswith('from-pstn'):
      app.cache['calls'][msg.uniqueid]=msg
    elif ((msg.context in ('from-queue')) and
          (msg.linkedid in app.cache['calls']) and
          (msg.exten in app.cache['devicemap'])):
      did = app.cache['calls'][msg.linkedid].exten
      cid = app.cache['calls'][msg.linkedid].calleridnum
      user = msg.exten
      device = app.cache['devicemap'][user]
      uid = msg.linkedid
    elif ((msg.context.startswith('from-internal')) and
          (msg.exten in app.cache['devicemap'])):
      user = msg.exten
      device = app.cache['devicemap'][user]
      if msg.calleridnum in app.cache['usermap']:
        cid = app.cache['usermap'][msg.calleridnum]
      else:
        cid = msg.calleridnum
      uid = msg.uniqueid
    if device is not None:
      _cb = {'user': user,
             'device': device,
             'state': 'ringing',
             'callerId': cid,
             'did': did,
             'callId': uid}
      if (msg.linkedid in  app.cache['cel_calls']):
        fillCallbackParameters(_cb,app.cache['cel_calls'][msg.linkedid],cid)
      #reply = await doCallback(device, _cb)

def fillCallbackParameters(cb,call,cid):
    if ('WebCallId' in call):
      cb['WebCallId'] = call['WebCallId']
    if ('CallerNumber' in call):
      cb['CallerNumber'] = call['CallerNumber']
    elif ('AlertInfo' in call):
      cb['CallerNumber'] = call['AlertInfo']
    if  ('BNumber' in call):
      cb['BNumber'] = call['BNumber']
    elif ('AlertInfo' in call):
      cb['BNumber'] = call['AlertInfo']
    if ('ANumber' in call):
      cb['ANumber'] = call['ANumber']
    elif call.Context.startswith('from-pstn'):
      cb['ANumber'] = cid

async def getVariableFromPJSIP(callid,channel,variable_source,variable_destination=None):
    if (variable_destination == None):
      variable_destination = variable_source
    value = await amiChannelGetVar(channel,"PJSIP_HEADER(read,{})".format(variable_source))
    if (value):
      app.logger.warning('set {}={} for {} from header'.format(variable_destination,value,callid))
      app.cache['cel_calls'][callid][variable_destination] = value
        
async def getVariableFromChannel(callid,channel,variable_source,variable_destination=None):
    if (variable_destination == None):
      variable_destination = variable_source
    value = await amiChannelGetVar(channel,variable_source)
    if (value):
      app.logger.warning('set {}={} for {} from channel'.format(variable_destination,value,callid))
      app.cache['cel_calls'][callid][variable_destination] = value
    else:
      app.logger.warning('can\'t get {} for {} from channel'.format(variable_source,callid))
@manager.register_event('CEL')
async def celCallback(mngr: Manager, msg: Message):
  #app.logger.warning('CEL {}'.format(msg))
  lid = msg.LinkedID

    
  if ((msg.EventName == 'CHAN_START') and (lid == msg.UniqueID)):    #save first msg
    app.cache['cel_calls'][lid] = msg
    app.cache['cel_calls'][lid]['current_channels'] = {}
    app.cache['cel_calls'][lid]['all_channels'] = {}
    app.cache['cel_calls'][lid]['transfers'] = []
    #app.cache['cel_calls'][lid]['mix_monitors'] = []
    if (msg.Context.startswith('from-internal')):
      sip_call_id = await amiChannelGetVar(msg.Channel,"PJSIP_HEADER(read,UniqueId)")
      #if( False and   not sip_call_id):
      #  sip_call_id = await amiChannelGetVar(msg.Channel,"PJSIP_HEADER(read,Call-ID)")
      if (sip_call_id):
        await amiChannelSetVar(msg.Channel,"CDR(userfield)",sip_call_id)
      await getVariableFromPJSIP(lid,msg.Channel,"WebCallId")
      await getVariableFromPJSIP(lid,msg.Channel,"CallerNumber","ANumber")
      #await getVariableFromPJSIP(lid,msg.Channel,"CallerNumber")
      await getVariableFromPJSIP(lid,msg.Channel,"BNumber")
      if (len(msg.Exten)>=7):
        device = msg.CallerIDnum
        user = device
        if device in app.cache['usermap']:
          user = app.cache['usermap'][device]
        start = datetime.strptime(msg.EventTime,"%Y-%m-%d %H:%M:%S")
        _cb = {'webhook_name': "outgoing_start",
               'callId':msg.LinkedID,
               'ANumber': user,
               'BNumber': msg.Exten}
        app.logger.warning('outgoing cb {}'.format(_cb))
        reply = await doCallback(device, _cb)
    else:
      await getVariableFromChannel(lid,msg.Channel,"ALERT_INFO","AlertInfo")
    
  if (lid in app.cache['cel_calls']):
    firstMessage = app.cache['cel_calls'][lid]
    cid = firstMessage.CallerIDnum
    if firstMessage.CallerIDnum in app.cache['usermap']:
        cid = app.cache['usermap'][firstMessage.CallerIDnum]
    uid = firstMessage.LinkedID
    
    if (msg.EventName == 'ATTENDEDTRANSFER'):
      #app.logger.warning('CEL {}'.format(msg))
      extra = json.loads(msg.Extra)
      if ('transferee_channel_uniqueid' in extra) and ('channel2_uniqueid' in extra):
        first = extra['transferee_channel_uniqueid']; #unique
        second = extra['channel2_uniqueid']   #linked
        firstname = extra['transferee_channel_name']
        secondname = extra['transfer_target_channel_name'].replace(";1",";2")
        thirdname = extra['channel2_name'].replace(";1",";2")
        if (True or msg.CallerIDrdnis == '78124254209' or msg.CallerIDani == '10001'):
          if secondname != firstMessage.channel:
            await amiStopMixMonitor(secondname)
          if thirdname != firstMessage.channel: 
            await amiStopMixMonitor(thirdname)            
        if (True or msg.CallerIDrdnis == '78124254209'):
          if firstname != firstMessage.channel:
            res = await amiStopMixMonitor(firstname)
          filename = "transfer-{}-{}-{}".format(msg.Exten, msg.CallerIDnum, msg.LinkedID);
          res = await amiStartMixMonitor(firstname,filename);#2022/03/11/external-2534-1934-20220311-122726-1647001646.56557.wav
          res = await amiChannelSetVar(firstname,"CDR(recordingfile)","{}.wav".format(filename))
        ttime = datetime.strptime(msg.EventTime,"%Y-%m-%d %H:%M:%S")
        app.cache['cel_calls'][lid]['transfers'].append((first,second,ttime)) #no cdr in db here
        #app.logger.warning('first {} {}'.format(first,second))
    
    if (msg.EventName == 'CHAN_START') and (lid != msg.UniqueID) and (not msg.Channel.startswith('Local/')):
        if (msg.CallerIDnum!=''): # or (firstMessage.Context == 'from-pstn') or (firstMessage.get('groupCall',False))):#all calls 
           device = msg.CallerIDnum
           user = device
           if device in app.cache['usermap']:
             user = app.cache['usermap'][device]
           did = firstMessage.Exten
           _cb = {'webhook_name': 'user_calling',
             'user': user,
             'device': device,
             'state': 'ringing',
             'callerId': cid,
             'did': did,
             'callId': uid} 
           if ('queueName' in app.cache['cel_calls'][msg.linkedid]):
             _cb['queue'] = app.cache['cel_calls'][msg.linkedid]['queueName']  
           if (msg.linkedid in  app.cache['cel_calls']):
             fillCallbackParameters(_cb,app.cache['cel_calls'][msg.linkedid],cid)
           reply = await doCallback(device, _cb)
    if ((msg.Application == 'Queue') and
        (msg.EventName == 'APP_START') and 
        (firstMessage.Context.startswith('from-internal')) and 
        (cid is not None) and (len(cid) < 7)):
      app.cache['cel_calls'][lid]['groupCall'] = True
      app.cache['cel_calls'][lid]['queueName'] = msg.Exten
    if ((msg.Application == 'Queue') and
        (msg.EventName == 'APP_END') and 
        (firstMessage.get('groupCall',False))):
      app.cache['cel_calls'][lid]['groupCall'] = False
      app.cache['cel_calls'][lid]['queueName'] = False
    if (firstMessage.get('groupCall',False)):                         #for local calls only
      if msg.Channel.startswith('PJSIP/'):
        called = msg.CallerIDnum
        if called in app.cache['usermap']:
          called = app.cache['usermap'][called]
        if ((msg.EventName == 'CHAN_START') or
            ((msg.EventName == 'CHAN_END') and ('answered' not in firstMessage))):
          old_count = len(app.cache['cel_calls'][lid]['current_channels'])
          channel = msg.Channel
          if msg.EventName == 'CHAN_START':                          #start dial
            app.cache['cel_calls'][lid]['current_channels'][channel] = called
            app.cache['cel_calls'][lid]['all_channels'][channel] = called
            _cb = {'webhook_name':'group_user_start_calling',
                 'user': called,
                 'state': 'group_start_ringing',
                 'callerId': cid,
                 'callId': msg.UniqueID}
          else:                                                      #end dial
            app.cache['cel_calls'][uid]['current_channels'].pop(channel, False)
            _cb = {'webhook_name':'group_user_stop_calling',
                 'user': called,
                 'state': 'group_end_ringing',
                 'callerId': cid,
                 'callId': msg.UniqueID}

          if (msg.linkedid in  app.cache['cel_calls']):
            fillCallbackParameters(_cb,app.cache['cel_calls'][msg.linkedid],cid)
          reply = await doCallback('groupRinging', _cb)
        if ((msg.EventName == 'ANSWER') and
          (msg.Application == 'AppDial') and
          (lid in app.cache['cel_calls'])):
          #called = msg.Exten
          app.cache['cel_calls'][lid]['answered'] = True
          _cb = {'webhook_name':'group_user_answered',
               'user': called,
               'users': list(app.cache['cel_calls'][uid]['all_channels'].values()),
               'state': 'group_answer',
               'callerId': cid,
               'callId': msg.UniqueID}
          if (msg.linkedid in  app.cache['cel_calls']):
            fillCallbackParameters(_cb,app.cache['cel_calls'][msg.linkedid],cid)
          reply = await doCallback('groupAnswered', _cb)
          
    if ((msg.Application == 'Queue') and
        firstMessage.Context.startswith('from-pstn')):
      if (msg.EventName == 'APP_START'):
        app.cache['cel_queue_calls'][lid] = {'caller': msg.CallerIDnum, 'start': parseDatetime(msg.EventTime).isoformat()}
        app.cache['cel_calls'][lid]['queueName'] = msg.Exten
        _cb = {'webhook_name':'queue_enter',
            'callid': lid, 
            'caller': msg.CallerIDnum, 
            'start': parseDatetime(msg.EventTime).isoformat(),
            'callerfrom': firstMessage.Exten,
            'queue': msg.Exten,
            'agents': [q.user for q in app.cache['queues'][msg.Exten]]}
        reply = await doCallback('queueEnter', _cb)
      if (msg.EventName in ('APP_END', 'BRIDGE_ENTER')):
        call = app.cache['cel_queue_calls'].pop(lid,False)
        app.cache['cel_calls'][lid]['queueName'] = False
        queue_changed = (call != None)
        if queue_changed :
          _cb = {'webhook_name':'queue_leave',
              'callid': lid, 
              'queue': msg.Exten,
              'agents': [q.user for q in app.cache['queues'][msg.Exten]]}
          reply = await doCallback('queueLeave', _cb)
          
    #if ((msg.EventName == 'APP_START')  and (msg.Application == 'MixMonitor')):
    #  app.cache['cel_calls'][lid]['mix_monitors'].append(msg.Channel)
    #if ((msg.EventName == 'APP_END')  and (msg.Application == 'MixMonitor')):
    #  app.cache['cel_calls'][lid]['mix_monitors'].remove(msg.Channel)
    if (msg.EventName == "ANSWER" and msg.Application == 'AppDial' and not "answer_time" in app.cache['cel_calls'][lid]):
      app.cache['cel_calls'][lid]["answer_time"]=msg.EventTime
    if (msg.EventName == 'LINKEDID_END'):
      for t in firstMessage['transfers']:
        app.logger.warning('transfer {}'.format(t))
        (f,s,tt) = t
        await db.execute(query='update cdr set transfer_from=(select distinct linkedid from cdr where uniqueid=:first), transfer_time=:ttime where linkedid=:second;',values={'first': f,'second': s,'ttime':tt})
      if ("c2c_start_time" in app.cache['cel_calls'][lid]):
        call = app.cache['cel_calls'][lid]
        start = datetime.strptime(call["c2c_start_time"],"%Y-%m-%d %H:%M:%S")
        end = datetime.strptime(msg.EventTime,"%Y-%m-%d %H:%M:%S")
        _cb = {'webhook_name': "c2c_end",
               'callId':msg.LinkedID,
               'speakingTime': 0,
               'waitingTime': (end-start).total_seconds(),
               'answered': False,
               'ANumber': call["c2c_user"],
               'BNumber': call["c2c_phone"]}
        if "answer_time" in call:
          answer = datetime.strptime(call["answer_time"],"%Y-%m-%d %H:%M:%S")
          _cb["answered"] = True
          _cb['waitingTime'] = (answer-start).total_seconds()
          _cb['speakingTime'] = (end-answer).total_seconds()
        app.logger.warning('c2c cb {}'.format(_cb))
        reply = await doCallback(call["c2c_device"], _cb)
      elif (firstMessage.Context.startswith('from-internal') and len(firstMessage.Exten)>=7):
        call = app.cache['cel_calls'][lid]
        device = firstMessage.CallerIDnum
        user = device
        if device in app.cache['usermap']:
          user = app.cache['usermap'][device]
        start = datetime.strptime(firstMessage.EventTime,"%Y-%m-%d %H:%M:%S")
        end = datetime.strptime(msg.EventTime,"%Y-%m-%d %H:%M:%S")
        _cb = {'webhook_name': "outgoing_end",
               'callId':msg.LinkedID,
               'speakingTime': 0,
               'waitingTime': (end-start).total_seconds(),
               'answered': False,
               'ANumber': user,
               'BNumber': firstMessage.Exten}
        if "answer_time" in call:
          answer = datetime.strptime(call["answer_time"],"%Y-%m-%d %H:%M:%S")
          _cb["answered"] = True
          _cb['waitingTime'] = (answer-start).total_seconds()
          _cb['speakingTime'] = (end-answer).total_seconds()
        app.logger.warning('outgoing cb {}'.format(_cb))
        reply = await doCallback(device, _cb)
      app.cache['cel_calls'].pop(lid, False)
      app.cache['cel_queue_calls'].pop(lid, False)
      
    if (msg.EventName == 'USER_DEFINED') and (msg.UserDefType == 'SETVARIABLE'):
      varname, value = msg.AppData.split(',')[1].split('=')[0:2]
      app.cache['cel_calls'][lid][varname]=value
      app.logger.warning('set {} = {} for  {}'.format(varname,value,lid))
      if (lid in app.cache['calls']):
        app.cache['calls'][lid][varname]=value

    if (msg.EventName == 'USER_DEFINED') and (msg.UserDefType == 'CALLBACK_POSTFIX'):
      callback_type = msg.AppData.split(',')[1]
      postfix = msg.AppData.split(',')[2]
      values = msg.AppData.split(',')[3:]
      _cb = {'asteriskCallId':msg.LinkedID}
      for value in values:
        vn,vv = value.split('=')
        _cb[vn] = vv
      reply = await doCallbackPostfix(callback_type,postfix, _cb)
    if (msg.EventName == 'USER_DEFINED') and (msg.UserDefType == 'START_C2C'):
        app.cache['cel_calls'][lid]["c2c_start_time"]=msg.EventTime
        device = msg.CallerIDnum 
        app.cache['cel_calls'][lid]["c2c_device"] = device
        if device in app.cache['usermap']:
          app.cache['cel_calls'][lid]["c2c_user"] = app.cache['usermap'][device]
        else:
          app.cache['cel_calls'][lid]["c2c_user"] = device
        app.cache['cel_calls'][lid]["c2c_phone"] = msg.Exten
        _cb = {'webhook_name': "c2c_start",
             'callId':msg.LinkedID,
             'ANumber': app.cache['cel_calls'][lid]["c2c_user"],
             'BNumber': msg.Exten}
        reply = await doCallback(device, _cb)
        app.logger.warning('c2c cb {}'.format(_cb))

async def getCDR(start=None,
                 end=None,
                 table='cdr',
                 field='calldate',
                 sort='calldate, SUBSTR(uniqueid,1,10), sequence'):
  _cdr = {}
  if end is None:
    end = dt.now()
  if start is None:
    start=(end - td(hours=24))
  async for row in db.iterate(query='''SELECT *
                                       FROM {table}
                                       WHERE linkedid
                                       IN (SELECT DISTINCT(linkedid)
                                       FROM {table}
                                       WHERE {field}
                                       BETWEEN :start AND :end)
                                       ORDER BY {sort};'''.format(table=table,
                                                                  field=field,
                                                                  sort=sort),
                              values={'start':start,
                                      'end':end}):
    if row['linkedid'] in _cdr:
      _cdr[row['linkedid']].events.add(row)
    else:
      _cdr[row['linkedid']]=CdrCall(row)
  cdr = []
  for _id in sorted(_cdr.keys()):
    cdr.append(_cdr[_id])
  return cdr

async def getCallInfo(callid):
  pattern = re.compile("^[0-9]+\.[0-9]+")
  #app.logger.warning('callid {}'.format(callid)) 
  if (pattern.match(callid)):
    linkedid=callid
  else:
    row = await db.fetch_one(query='SELECT linkedid FROM cdr WHERE userfield = :callid', values={'callid': callid})
    if row:
      linkedid = row['linkedid']
    else:
      return "{}"
  #app.logger.warning('linkedid {}'.format(linkedid)) 
  _q = '''SELECT *
          FROM cel 
          WHERE linkedid=:linkedid and eventtype = :eventtype'''
  _v = {'linkedid': linkedid, 'eventtype': 'ATTENDEDTRANSFER'}
  _f = True
  uniqueids = set((linkedid,)) #get ids of transferred calls
  async for row in db.iterate(query=_q, values=_v):
    extra  = json.loads(row['extra'])
    uniqueids.add(extra['channel2_uniqueid'])
    
  _q = '''SELECT *
          FROM cdr 
          WHERE linkedid=:linkedid or linkedid in (select distinct linkedid from cdr where uniqueid in :uniqueids)
          ORDER BY sequence;'''
  _v = {'linkedid': linkedid, 'uniqueids': uniqueids}
  #app.logger.warning('values {}'.format(_v)) 
  _f = True
  unique_ids = set()
  events = CdrEvents()
  async for row in db.iterate(query=_q, values=_v):
    row = dict(row)
    #app.logger.warning('row {}'.format(row)) 
    if row['recordingfile'] is not None and row['recordingfile']  != '':
      row['recordingfile'] = '/static/records/{d.year}/{d.month:02}/{d.day:02}/{filename}'.format(d=row['calldate'],
                                                                                            filename=row['recordingfile'])
    app.logger.warning('event row {}'.format(row))   
    events.add(row)
  record = events.simple()                                                     
  return record

    
async def getUserCDR(user,
                     start=None,
                     end=None,
                     direction=None,
                     limit=None,
                     offset=None,
                     order='ASC'):
  _linked_q = f'''SELECT linkedid from cdr WHERE'''
  if direction:
    direction=direction.lower()
  if direction in ('in', True, '1', 'incoming', 'inbound'):
    direction = 'inbound'
    _linked_q += f''' dst="{user}"'''
  elif direction in ('out', False, '0', 'outgoing', 'outbound'):
    direction = 'outbound'
    _linked_q += f''' cnum="{user}"'''
  else:
    direction = None
    _linked_q += f''' (cnum="{user}" or dst="{user}")'''
  if end is None:
    end = dt.now()
  if start is None:
    start=(end - td(hours=24))
  _linked_q += f''' AND calldate BETWEEN "{start}" AND "{end}" GROUP BY linkedid'''
  if None not in (limit, offset): #This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
    _linked_q += f''' LIMIT {offset},{limit}'''
  start1 = (start-td(hours=4))
  end1 = (end+td(hours=4))
  _q = f'''SELECT * FROM cdr  WHERE linkedid in (select * from ({_linked_q}) tmp) and calldate BETWEEN "{start1}" AND "{end1}";'''
  #app.logger.warning('SQL: {}'.format(_q))
  _cdr = {}
  async for row in db.iterate(query=_q):
    if (row['disposition']=='FAILED' and row['lastapp']=='Queue'):
        continue
    if row['linkedid'] in _cdr:
      _cdr[row['linkedid']].events.add(row)
    else:
      _cdr[row['linkedid']]=CdrUserCall(user, row)
  cdr = []
  for _id in sorted(_cdr.keys(), reverse = True if (order.lower() == 'desc') else False):
    record = _cdr[_id].simple
    if (direction is not None) and (record['cnum'] == record['dst']) and (record['direction'] != direction):
      record['direction'] = direction
    if record['file'] is not None:
      record['file'] = '/static/records/{d.year}/{d.month:02}/{d.day:02}/{filename}'.format(d=record['start'],
                                                                                            filename=record['file'])
    cdr.append(record)
  return cdr

async def getCEL(start=None, end=None, table='cel', field='eventtime', sort='id'):
  return await getCDR(start, end, table, field, sort)

async def doCallback(entity, msg):
  row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device', values={'device': entity})
  if ((row is not None) and (row['url'].startswith('http')) and ('blackhole' not in row['url'])):
    app.logger.warning(f'''POST {row['url']} data: {str(msg)}''')
    if not 'HTTP_CLIENT' in app.config:
      await initHttpClient()
    try:
      reply = await app.config['HTTP_CLIENT'].post(row['url'], json=msg)
      return reply
    except Exception as e:
      app.logger.warning('callback error {}'.format(row['url']))
  else:
    app.logger.warning('No callback url defined for {}'.format(entity))
  return None

async def doCallbackPostfix(entity,postfix, msg):
  row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device', values={'device': entity})
  if ((row is not None) and (row['url'].startswith('http')) and ('blackhole' not in row['url'])):
    url = row["url"]+'/'+postfix
    app.logger.warning(f'''POST {url} data: {str(msg)}''')
    if not 'HTTP_CLIENT' in app.config:
      await initHttpClient()
    try:
      reply = await app.config['HTTP_CLIENT'].post(url, json=msg)
      return reply
    except Exception as e:
      app.logger.warning('callback error {}'.format(url))
  else:
    app.logger.warning('No callback url defined for {}'.format(entity))
  return None

@app.before_first_request
async def initHttpClient():
  app.config['HTTP_CLIENT'] = aiohttp.ClientSession(loop=main_loop,
                                                    connector=aiohttp.TCPConnector(verify_ssl=False,
                                                                                   resolver=AsyncResolver(nameservers=['192.168.171.10','1.1.1.1'])))

@app.route('/openapi.json')
async def openapi():
  '''Generates JSON that conforms OpenAPI Specification
  '''
  schema = app.__schema__
  schema['servers'] = [{'url':'http://aster.rrt.ru:8000'},
                       {'url':'{}://{}:{}'.format(app.config['SCHEME'],
                                                  app.config['FQDN'],
                                                  app.config['PORT'])}]
  if app.config['EXTRA_API_URL'] is not None:
    schema['servers'].append({'url':app.config['EXTRA_API_URL']})
  schema['components'] = {'securitySchemes':{'ApiKey':{'type': 'apiKey',
                                                       'name': app.config['AUTH_HEADER'],
                                                       'in': 'header'}}}
  schema['security'] = [{'ApiKey':[]}]
  return jsonify(schema)

@app.route('/ui')
async def ui():
  '''Swagger UI
  '''
  return await render_template_string(SWAGGER_TEMPLATE,
                                      title=app.config['TITLE'],
                                      js_url=app.config['SWAGGER_JS_URL'],
                                      css_url=app.config['SWAGGER_CSS_URL'])

@app.route('/ami/action', methods=['POST'])
async def action():
  _payload = await request.get_data()
  reply = await manager.send_action(json.loads(_payload))
  if (isinstance(reply, list) and
      (len(reply) > 1)):
    for message in reply:
      if (message.event == 'DBGetResponse'):
        return message.val
  return str(reply)

async def amiChannelGetVar(channel,variable):
  '''AMI GetVar
  Gets variable using AMI action GetVar to value in background.

  Parameters:
    channel (string)
    variable (string): Variable to get

  Returns:
    string: value if GetVar was successfull, error message overwise
  '''
  reply = await manager.send_action({'Action': 'GetVar',
                                     'Variable': variable,
                                     'Channel': channel})
  app.logger.warning('GetVar({},{}={})'.format(channel,variable,reply.value))
  return reply.value

async def amiGetVar(variable):
  '''AMI GetVar
  Returns value of requested variable using AMI action GetVar in background.

  Parameters:
    variable (string): Variable to query for

  Returns:
    string: Variable value or empty string if variable not found
  '''
  reply = await manager.send_action({'Action': 'GetVar',
                                     'Variable': variable})
  #app.logger.warning('GetVar({})->{}'.format(variable, reply.value))
  return reply.value

@app.route('/ami/auths')
@authRequired
async def amiPJSIPShowAuths():
  if not request.admin:
    abort(401)
  return successReply(app.cache['devices'])

@app.route('/blackhole', methods=['GET','POST'])
async def blackhole():
  return ''

@app.route('/ami/aors')
@authRequired
async def amiPJSIPShowAors():
  if not request.admin:
    abort(401)
  aors = {}
  reply = await manager.send_action({'Action':'PJSIPShowAors'})
  if len(reply) >= 2:
    for message in reply:
      if ((message.event == 'AorList') and
          ('objecttype' in message) and
          (message.objecttype == 'aor') and
          (int(message.maxcontacts) > 0)):
        aors[message.objectname] = message.contacts
    app.logger.warning('AorsList: {}'.format(','.join(aors.keys())))
  return successReply(aors)


async def amiStartMixMonitor(channel,filename):
  '''AMI MixMonitor
  Parameters:
    channel (string):channel to start mixmonitor
    filename (string): file name

  Returns:
    string: None if SetVar was successfull, error message overwise
  '''
  d = date.today()
  year = d.strftime("%Y")
  month = d.strftime("%m")
  day = d.strftime("%d")
  fullname = "{}/{}/{}/{}.wav".format(year,month,day,filename)
  reply = await manager.send_action({'Action': 'MixMonitor',
                                     'Channel': channel,
                                     'options': 'ai(LOCAL_MIXMON_ID)',
                                     'Command': "/etc/asterisk/scripts/wav2mp3.sh {} {} {} {}".format(year,month,day,filename),
                                     'File': fullname})
  #app.logger.warning('MixMonitor({}, {})'.format(channel, fullname))
  if isinstance(reply, Message):
    if reply.success:
      return None
    else:
      return reply.message
  return 'AMI error'
  
async def amiStopMixMonitor(channel):
  '''AMI StopMixMonitor
  Parameters:
    channel (string):channel to stop mixmonitor

  Returns:
    string: None if SetVar was successfull, error message overwise
  '''
  reply = await manager.send_action({'Action': 'StopMixMonitor',
                                     'Channel': channel})
  #app.logger.warning('StopMixMonitor({})'.format(channel))
  if isinstance(reply, Message):
    if reply.success:
      return None
    else:
      return reply.message
  return 'AMI error'
  
async def amiUserEvent(name, data):
  '''AMI UserEvent
  Generates AMI Event using AMI action UserEvent with name and data supplied.

  Parameters:
    name (string): UserEvent name
    data (dict): UserEvent data

  Returns:
    string: None if UserEvent was successfull, error message overwise
  '''
  reply = await manager.send_action({**{'Action': 'UserEvent',
                                        'UserEvent': name},
                                     **data})
  #app.logger.warning('UserEvent({})'.format(name))
  if isinstance(reply, Message):
    if reply.success:
      return None
    else:
      return reply.message
  return 'AMI error'

async def amiChannelSetVar(channel,variable, value):
  '''AMI SetVar
  Sets variable using AMI action SetVar to value in background.

  Parameters:
    channel (string)
    variable (string): Variable to set
    value (string): Value to set for variable

  Returns:
    string: None if SetVar was successfull, error message overwise
  '''
  reply = await manager.send_action({'Action': 'SetVar',
                                     'Variable': variable,
                                     'Channel': channel,
                                     'Value': value})
  app.logger.warning('SetVar({},{}={})'.format(channel,variable, value))
  if isinstance(reply, Message):
    if reply.success:
      return None
    else:
      return reply.message
  return 'AMI error'

async def amiSetVar(variable, value):
  '''AMI SetVar
  Sets variable using AMI action SetVar to value in background.

  Parameters:
    variable (string): Variable to set
    value (string): Value to set for variable

  Returns:
    string: None if SetVar was successfull, error message overwise
  '''
  reply = await manager.send_action({'Action': 'SetVar',
                                     'Variable': variable,
                                     'Value': value})
  #app.logger.warning('SetVar({}, {})'.format(variable, value))
  if isinstance(reply, Message):
    if reply.success:
      return None
    else:
      return reply.message
  return 'AMI error'

async def amiDBGet(family, key):
  '''AMI DBGet
  Returns value of requested astdb key using AMI action DBGet in background.

  Parameters:
    family (string): astdb key family to query for
    key (string): astdb key to query for

  Returns:
    string: Value or empty string if variable not found
  '''
  reply = await manager.send_action({'Action': 'DBGet',
                                     'Family': family,
                                     'Key': key})
  if (isinstance(reply, list) and
      (len(reply) > 1)):
    for message in reply:
      if (message.event == 'DBGetResponse'):
        #app.logger.warning('DBGet(/{}/{})->{}'.format(family, key, message.val))
        return message.val
  app.logger.warning('DBGet(/{}/{})->Error!'.format(family, key))
  return None

async def amiDBPut(family, key, value):
  '''AMI DBPut
  Writes value to astdb by family and key using AMI action DBPut in background.

  Parameters:
    family (string): astdb key family to write to
    key (string): astdb key to write to
    value (string): value to write

  Returns:
    boolean: True if DBPut action was successfull, False overwise
  '''
  reply = await manager.send_action({'Action': 'DBPut',
                                     'Family': family,
                                     'Key': key,
                                     'Val': value})
  app.logger.warning('DBPut(/{}/{}, {})'.format(family, key, value))
  if (isinstance(reply, Message) and reply.success):
    return True
  return False

async def amiDBDel(family, key):
  '''AMI DBDel
  Deletes key from family in astdb using AMI action DBDel in background.

  Parameters:
    family (string): astdb key family
    key (string): astdb key to delete

  Returns:
    boolean: True if DBDel action was successfull, False overwise
  '''
  reply = await manager.send_action({'Action': 'DBDel',
                                     'Family': family,
                                     'Key': key})
  app.logger.warning('DBDel(/{}/{})'.format(family, key))
  if (isinstance(reply, Message) and reply.success):
    return True
  return False

async def amiSetHint(context, user, hint):
  '''AMI SetHint
  Sets hint for user in context using AMI action DialplanUserAdd with Replace=true in background.

  Parameters:
    context (string): dialplan context
    user (string): user
    hint (string): hint for user

  Returns:
    boolean: True if DialplanUserAdd action was successfull, False overwise
  '''
  reply = await manager.send_action({'Action': 'DialplanExtensionAdd',
                                     'Context': context,
                                     'Extension': user,
                                     'Priority': 'hint',
                                     'Application': hint,
                                     'Replace': 'yes'})
  app.logger.warning('SetHint({},{},{})'.format(context, user, hint))
  if (isinstance(reply, Message) and reply.success):
    return True
  return False

async def amiPresenceState(user):
  '''AMI PresenceState request for CustomPresence provider

  Parameters:
    user (string): user

  Returns:
    boolean, string: True and state or False and error message
  '''
  reply = await manager.send_action({'Action': 'PresenceState',
                                     'Provider': 'CustomPresence:{}'.format(user)})
  #app.logger.warning('PresenceState({})'.format(user))
  if isinstance(reply, Message):
    if reply.success:
      return True, reply.state
    else:
      return False, reply.message
  return False, 'AMI error'

async def amiPresenceStateList():
  states = {}
  reply = await manager.send_action({'Action':'PresenceStateList'})
  if len(reply) >= 2:
    for message in reply:
      if message.event == 'PresenceStateChange':
        user = re.search('CustomPresence:(\d+)', message.presentity).group(1)
        states[user] = message.status
    app.logger.warning('PresenceStateList: {}'.format(','.join(states.keys())))
  return states

async def amiExtensionStateList():
  states = {}
  reply = await manager.send_action({'Action':'ExtensionStateList'})
  if len(reply) >= 2:
    for message in reply:
      if ((message.event == 'ExtensionStatus') and
          (message.context == 'ext-local')):
        states[message.exten] = message.statustext.lower()
    app.logger.warning('ExtensionStateList: {}'.format(','.join(states.keys())))
  return states

async def amiCommand(command):
  '''AMI Command
  Runs specified command using AMI action Command in background.

  Parameters:
    command (string): command to run

  Returns:
    boolean, list: tuple representing the boolean result of request and list of lines of command output
  '''
  reply = await manager.send_action({'Action': 'Command',
                                     'Command': command})
  result = []
  if (isinstance(reply, Message) and reply.success):
    if isinstance(reply.output, list):
      result = reply.output
    else:
      result = reply.output.split('\n')
    app.logger.warning('Command({})->{}'.format(command, '\n'.join(result)))
    return True, result
  app.logger.warning('Command({})->Error!'.format(command))
  return False, result

async def amiReload(module='core'):
  '''AMI Reload
  Reload specified asterisk module using AMI action reload in background.

  Parameters:
    module (string): module to reload, defaults to core

  Returns:
    boolean: True if Reload action was successfull, False overwise
  '''
  reply = await manager.send_action({'Action': 'Reload',
                                     'Module': module})
  app.logger.warning('Reload({})'.format(module))
  if (isinstance(reply, Message) and reply.success):
    return True
  return False

async def getGlobalVars():
  globalVars = GlobalVars()
  for _var in globalVars.d():
    setattr(globalVars, _var, await amiGetVar(_var))
  return globalVars

async def setUserHint(user, dial, ast):
  if dial in NONEs:
    hint = 'CustomPresence:{}'.format(user)
  else:
    _dial= [dial]
    if (ast.DNDDEVSTATE == 'TRUE'):
      _dial.append('Custom:DND{}'.format(user))
    hint = '{},CustomPresence:{}'.format('&'.join(_dial), user)
  return await amiSetHint('ext-local', user, hint)

async def amiQueues():
  queues = {}
  reply = await manager.send_action({'Action':'QueueStatus'})
  if len(reply) >= 2:
    for message in reply:
      if message.event == 'QueueMember':
        _qm = QueueMember(re.search('Local\/(\d+)', message.location).group(1))
        queues.setdefault(message.queue, []).append(_qm.fromMessage(message))
    app.logger.warning('QueuesList: {}'.format(','.join(queues.keys())))
  return queues

async def amiDeviceChannel(device):
  reply = await manager.send_action({'Action':'CoreShowChannels'})
  if len(reply) >= 2:
    for message in reply:
      if message.event == 'CoreShowChannel':
        if message.calleridnum == device:
          return message.channel
  return None

async def getUserChannel(user):
  device = await getUserDevice(user)
  if device in NONEs:
    return False
  channel = await amiDeviceChannel(device)
  if channel in NONEs:
    return False
  return channel

async def setQueueStates(user, device, state):
  for queue in [_q for _q, _ma in app.cache['queues'].items() for _m in _ma if _m.user == user]:
    await amiSetVar('DEVICE_STATE(Custom:QUEUE{}*{})'.format(device, queue), state)

async def getDeviceUser(device):
  return await amiDBGet('DEVICE', '{}/user'.format(device))

async def getDeviceType(device):
  return await amiDBGet('DEVICE', '{}/type'.format(device))

async def getDeviceDial(device):
  return await amiDBGet('DEVICE', '{}/dial'.format(device))

async def getUserCID(user):
  return await amiDBGet('AMPUSER', '{}/cidnum'.format(user))

async def setDeviceUser(device, user):
  return await amiDBPut('DEVICE', '{}/user'.format(device), user)

async def getUserDevice(user):
  return await amiDBGet('AMPUSER', '{}/device'.format(user))

async def getUserName(user):
  return await amiDBGet('AMPUSER', '{}/cidname'.format(user))

async def setUserDevice(user, device):
  if device is None:
    return await amiDBDel('AMPUSER', '{}/device'.format(user))
  else:
    return await amiDBPut('AMPUSER', '{}/device'.format(user), device)

async def unbindOtherDevices(user, newDevice, ast):
  '''Unbinds user from all devices except newDevice and sets
  all required device states.
  '''
  devices = await amiDBGet('AMPUSER', '{}/device'.format(user))
  if devices not in NONEs:
    for _device in sorted(set(devices.split('&')), key=int):
      if _device == user:
        continue
      if _device != newDevice:
        if ast.FMDEVSTATE == 'TRUE':
          await amiSetVar('DEVICE_STATE(Custom:FOLLOWME{})'.format(_device), 'INVALID')
        if ast.QUEDEVSTATE == 'TRUE':
          await setQueueStates(user, _device, 'NOT_INUSE')
        if ast.DNDDEVSTATE:
          await amiSetVar('DEVICE_STATE(Custom:DEVDND{})'.format(_device), 'NOT_INUSE')
        if ast.CFDEVSTATE:
          await amiSetVar('DEVICE_STATE(Custom:DEVCF{})'.format(_device), 'NOT_INUSE')
        await amiDBPut('DEVICE', '{}/user'.format(_device), 'none')

async def setUserDeviceStates(user, device, ast):
  if ast.FMDEVSTATE == 'TRUE':
    _followMe = await amiDBGet('AMPUSER', '{}/followme/ddial'.format(user))
    if _followMe is not None:
      await amiSetVar('DEVICE_STATE(Custom:FOLLOWME{})'.format(device), followMe2DevState(_followMe))
  if ast.QUEDEVSTATE == 'TRUE':
    await setQueueStates(user, device, 'INUSE')
  if ast.DNDDEVSTATE:
    _dnd = await amiDBGet('DND', user)
    await amiSetVar('DEVICE_STATE(Custom:DEVDND{})'.format(device), 'INUSE' if _dnd == 'YES' else 'NOT_INUSE')
  if ast.CFDEVSTATE:
    _cf = await amiDBGet('CF', user)
    await amiSetVar('DEVICE_STATE(Custom:DEVCF{})'.format(device), 'INUSE' if _cf != '' else 'NOT_INUSE')

async def refreshStatesCache():
  app.cache['ustates'] = await amiExtensionStateList()
  username = {}
  for u in app.cache['ustates']:
    username[u] = await getUserName(u)
  app.cache['usernamemap'] = copy.deepcopy(username)
  app.logger.warning('User Names {}'.format(app.cache['usernamemap']))
  app.cache['pstates'] = await amiPresenceStateList()
  return len(app.cache['ustates'])

async def refreshDevicesCache():
  auths = {}
  reply = await manager.send_action({'Action':'PJSIPShowAuths'})
  if len(reply) >= 2:
    for message in reply:
      if ((message.event == 'AuthList') and
          ('objecttype' in message) and
          (message.objecttype == 'auth')):
        auths[message.username] = message.password
  app.cache['devices'] = auths
  return len(app.cache['devices'])

async def refreshQueuesCache():
  app.cache['queues'] = await amiQueues()
  return len(app.cache['queues'])

async def rebindLostDevices():
  usermap = {}
  devicemap = {}
  ast = await getGlobalVars()
  for device in app.cache['devices']:
    user = await getDeviceUser(device)
    deviceType = await getDeviceType(device)
    if (deviceType != 'fixed') and (user != 'none') and (user in app.cache['ustates'].keys()):
      _device = await getUserDevice(user)
      if _device != device:
        app.logger.warning('Fixing bind user {} to device {}'.format(user, device))
        dial = await getDeviceDial(device)
        await setUserHint(user, dial, ast)           # Set hints for user on new device
        await setUserDeviceStates(user, device, ast) # Set device states for users device
        await setUserDevice(user, device)            # Bind device to user
    usermap[device] = user
    if user != 'none':
      devicemap[user] = device

  app.cache['usermap'] = copy.deepcopy(usermap)
  app.cache['devicemap'] = copy.deepcopy(devicemap)

async def userStateChangeCallback(user, state, prevState = None):
  reply = None
  device = None
  if (user in app.cache['devicemap']):
    device = app.cache['devicemap'][user]
    if device is not None:
      _cb = {'webhook_name': 'user_status',
             'user': user,
             'state': state,
             'prev_state':prevState}
      reply = await doCallback(device, _cb)

  _cb = {'webhook_name': 'user_status',
             'user': user,
             'state': state,
             'prev_state':prevState}
  reply = await doCallback('user_status', _cb)
  #app.logger.warning('{} changed state to: {}'.format(user, state))
  return reply

def getUserStateCombined(user):
  _uCache = app.cache['ustates']
  _pCache = app.cache['pstates']
  return combinedStates[_uCache.get(user, 'unavailable')][_pCache.get(user, 'not_set')]

def getUsersStatesCombined():
  return {user:getUserStateCombined(user) for user in app.cache['ustates']}

def getUsersNameStatesCombined():
  return {user:{'state': getUserStateCombined(user),'name':app.cache['usernamemap'][user]} for user in app.cache['ustates']}

@app.route('/atxfer/<userA>/<userB>')
class AtXfer(Resource):
  @authRequired
  @app.param('userA', 'User initiating the attended transfer', 'path')
  @app.param('userB', 'Transfer destination user', 'path')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, userA, userB):
    '''Attended call transfer
    '''
    if (userA != request.user) and (not request.admin):
      abort(401)
    channel = await getUserChannel(userA)
    if not channel:
      return noUserChannel(userA)
    reply = await manager.send_action({'Action':'Atxfer',
                                       'Channel':channel,
                                       'async':'false',
                                       'Exten':userB})
    if isinstance(reply, Message):
      if reply.success:
        return successfullyTransfered(userA, userB)
      else:
        return errorReply(reply.message)

@app.route('/bxfer/<userA>/<userB>')
class BXfer(Resource):
  @authRequired
  @app.param('userA', 'User initiating the blind transfer', 'path')
  @app.param('userB', 'Transfer destination user', 'path')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, userA, userB):
    '''Blind call transfer
    '''
    if (userA != request.user) and (not request.admin):
      abort(401)
    channel = await getUserChannel(userA)
    if not channel:
      return noUserChannel(userA)
    reply = await manager.send_action({'Action':'BlindTransfer',
                                       'Channel':channel,
                                       'async':'false',
                                       'Exten':userB})
    if isinstance(reply, Message):
      if reply.success:
        return successfullyTransfered(userA, userB)
      else:
        return errorReply(reply.message)

@app.route('/originate/<user>/<number>')
class Originate(Resource):
  @authRequired
  @app.param('user', 'User initiating the call', 'path')
  @app.param('number', 'Destination number', 'path')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, user, number):
    '''Originate call
    '''
    if (user != request.user) and (not request.admin):
      abort(401)
    device = await getUserDevice(user)
    if device in NONEs:
      return noUserDevice(user)
    device = device.replace('{}&'.format(user), '')
    _act = { 'Action':'Originate',
             'Channel':'PJSIP/{}'.format(device),
             'Context':'from-internal-c2c',
             'Exten':number,
             'Priority': '1',
             'Async':'true',
             'Callerid': '{} <{}>'.format(user, device)}
    app.logger.warning(_act)
    await manager.send_action(_act)
    return successfullyOriginated(user, number)
    #reply = await manager.send_action(_act)
    #if isinstance(reply, Message):
    #  if reply.success:
    #    return successfullyOriginated(user, number)
    #  else:
    #    return errorReply(reply.message)

@app.route('/originate_test/<user>/<number>')
class Originate_test(Resource):
  @authRequired
  @app.param('user', 'User initiating the call', 'path')
  @app.param('number', 'Destination number', 'path')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, user, number):
    '''Originate call
    '''
    if (user != request.user) and (not request.admin):
      abort(401)
    device = await getUserDevice(user)
    if device in NONEs:
      return noUserDevice(user)
    device = device.replace('{}&'.format(user), '')
    _act = { 'Action':'Originate',
             'Channel':'PJSIP/{}'.format(device),
             'Context':'from-internal-c2c-test',
             'Exten':number,
             'Priority': '1',
             'Async':'true',
             'Callerid': '{} <{}>'.format(user, device)}
    app.logger.warning(_act)
    await manager.send_action(_act)
    return successfullyOriginated(user, number)
    #reply = await manager.send_action(_act)
    #if isinstance(reply, Message):
    #  if reply.success:
    #    return successfullyOriginated(user, number)
    #  else:
    #    return errorReply(reply.message)

@app.route('/registration_call/<numberB>')
class registration_call(Resource):
  @authRequired
  @app.param('numberB', 'User calling ', 'path')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self,  numberB):
    if  (not request.admin):
      abort(401)
    numberB = re.sub('\D', '', numberB);
    row = await db.fetch_one(query='SELECT phone FROM phone_registration WHERE active=1 order by rand() limit 1');
    numberA = row['phone'];
    _act = { 'Action':'Originate',
             'Channel':'Local/{}@regcall-legA'.format(numberB),
             'Context':'regcall-legB',
             'Exten':numberB,
             'Priority': '1',
             'Async':'true',
             'Callerid': '{} <{}>'.format(numberA, numberA)
             }
    app.logger.warning(_act)
    await manager.send_action(_act)
    return successfullyOriginated(numberA, numberB)


@app.route('/autocall/<numberA>/<numberB>')
class Autocall(Resource):
  @authRequired
  @app.param('numberA', 'User calling first', 'path')
  @app.param('numberB', 'user calling after numberA enter DTMF 2', 'path')
  @app.param('callid', 'callid to be returned in callback', 'query')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, numberA, numberB):
    '''Originate autocall
    '''
    if  (not request.admin):
      abort(401)
    numberA = re.sub('\D', '', numberA);
    numberB = re.sub('\D', '', numberB);
    callid = request.args.get('callid', None)
    _act = { 'Action':'Originate',
             'Channel':'Local/{}@autocall-legA'.format(numberA),
             'Context':'autocall-legB',
             'Exten':numberB,
             'Priority': '1',
             'Async':'true',
             'Callerid': '{} <{}>'.format(1000, 1000),
             'Variable': '__callid={}'.format(callid)
             }
    app.logger.warning(_act)
    await manager.send_action(_act)
    return successfullyOriginated(numberA, numberB)

@app.route('/hangup/<user>')
class Hangup(Resource):
  @authRequired
  @app.param('user', 'User to hangup', 'path')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, user):
    '''Call hangup
    '''
    if (user != request.user) and (not request.admin):
      abort(401)
    channel = await getUserChannel(user)
    if not channel:
      return noUserChannel(user)
    reply = await manager.send_action({'Action':'Hangup',
                                       'Channel':channel})
    if isinstance(reply, Message):
      if reply.success:
        return successfullyHungup(user)
      else:
        return errorReply(reply.message)

@app.route('/users/states')
class UsersStates(Resource):
  @authRequired
  @app.response(HTTPStatus.OK, 'JSON reply with user:state map or error message')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns all users with their combined states.
    Possible states are: available, away, dnd, inuse, busy, unavailable, ringing
    '''
    if not request.admin:
      abort(401)
    #app.logger.warning('request device: {}'.format(request.device))
    #usersCount = await refreshStatesCache()
    #if usersCount == 0:
    #  return stateCacheEmpty()

    return successReply(getUsersStatesCombined())
  
@app.route('/users/namestates')
class UsersNameStates(Resource):
  @authRequired
  @app.response(HTTPStatus.OK, 'JSON reply with user:{"state":state,"name":name} map or error message')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns all users with their combined states.
    Possible states are: available, away, dnd, inuse, busy, unavailable, ringing
    '''
    if not request.admin:
      abort(401)
    #app.logger.warning('request device: {}'.format(request.device))
    #usersCount = await refreshStatesCache()
    #if usersCount == 0:
    #  return stateCacheEmpty()

    return successReply(getUsersNameStatesCombined())

@app.route('/users/states/<users_list>')
class UsersStatesSelected(Resource):
  @authRequired
  @app.param('users_list', 'Comma separated list of users to query for combined states', 'path')
  @app.response(HTTPStatus.OK, 'JSON reply with user:state map or error message')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, users_list):
    '''Returns selected users with their combined states.
    Possible states are: available, away, dnd, inuse, busy, unavailable, ringing
    '''
    if not request.admin:
      abort(401)
    users = users_list.split(',')
    states = getUsersStatesCombined()
    result={}
    for user in states:
      if user in users:
        result[user] = states[user]
    return successReply(result)

@app.route('/user/<user>/state')
class UserState(Resource):
  @authRequired
  @app.param('user', 'User to query for combined state', 'path')
  @app.response(HTTPStatus.OK, 'JSON data {"user":user,"state":state}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, user):
    '''Returns user's combined state.
    One of: available, away, dnd, inuse, busy, unavailable, ringing
    '''
    if (user != request.user) and (not request.admin):
      abort(401)
    if user not in app.cache['ustates']:
      return noUser(user)
    return successReply({'user':user,'state':getUserStateCombined(user)})

@app.route('/user/<user>/presence')
class PresenceState(Resource):
  @authRequired
  @app.param('user', 'User to query for presence state', 'path')
  @app.response(HTTPStatus.OK, 'JSON data {"user":user,"state":state}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, user):
    '''Returns user's presence state.
    One of: not_set, unavailable, available, away, xa, chat, dnd
    '''
    if (user != request.user) and (not request.admin):
      abort(401)
    if user not in app.cache['ustates']:
      return noUser(user)
    return successReply({'user':user,'state':app.cache['pstates'].get(user, 'not_set')})

@app.route('/user/<user>/presence/<state>')
class SetPresenceState(Resource):
  @authRequired
  @app.param('user', 'Target user to set the presence state', 'path')
  @app.param('state',
             'The presence state for user, one of: not_set, unavailable, available, away, xa, chat or dnd',
             'path')
  @app.response(HTTPStatus.OK, 'Json reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, user, state):
    '''Sets user's presence state.
    Allowed states: not_set | unavailable | available | away | xa | chat | dnd
    '''
    if (user != request.user) and (not request.admin):
      abort(401)
    if state not in presenceStates:
      return invalidState(state)
    if user not in app.cache['ustates']:
      return noUser(user)
    # app.logger.warning('state={}, getUserStateCombined({})={}'.format(state, user, getUserStateCombined(user)))
    # if (state.lower() in ('available','away','not_set','xa','chat')) and (getUserStateCombined(user) in ('dnd')):
    if (state.lower() not in ('dnd')):
      result = await amiDBDel('DND', '{}'.format(user))
    result = await amiSetVar('PRESENCE_STATE(CustomPresence:{})'.format(user), state)
    if result is not None:
      return errorReply(result)
    if state.lower() in ('dnd'):
      result = await amiDBPut('DND', '{}'.format(user), 'YES')
    return successfullySetState(user, state)

@app.route('/users/devices')
class UsersDevices(Resource):
  @authRequired
  @app.response(HTTPStatus.OK, 'JSON reply with user:device map or error message')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns users to device maping.
    '''
    if not request.admin:
      abort(401)
    data = {}
    for user in app.cache['ustates']:
      device = await getUserDevice(user)
      if ((device in NONEs) or (device == user)):
        device = None
      else:
        device = device.replace('{}&'.format(user), '')
      data[user]= device
    return successReply(data)

@app.route('/device/<device>/<user>/on')
@app.route('/user/<user>/<device>/on')
class UserDeviceBind(Resource):
  @authRequired
  @app.param('device', 'Device number to bind to', 'path')
  @app.param('user', 'User to bind to device', 'path')
  @app.response(HTTPStatus.OK, 'JSON reply with fields "success" and "result"')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, device, user):
    '''Binds user to device.
    Both user and device numbers are checked for existance.
    Any device user was previously bound to, is unbound.
    Any user previously bound to device is unbound also.
    '''
    if (device != request.device) and (not request.admin):
      abort(401)
    if user not in app.cache['ustates']:
      return noUser(user)
    dial = await getDeviceDial(device) # Check if device exists in astdb
    if dial is None:
      return noDevice(device)
    currentUser = await getDeviceUser(device) # Check if any user is already bound to device
    if currentUser == user:
      return alreadyBound(user, device)
    ast = await getGlobalVars()
    if currentUser not in NONEs: # If any other user is bound to device, unbind him,
      result = await amiSetVar('PRESENCE_STATE(CustomPresence:{})'.format(user), 'available')
      result = await amiDBDel('DND', '{}'.format(user))
      await setUserDevice(currentUser, None)
      if ast.QUEDEVSTATE == 'TRUE': # set device states for previous user queues
        await setQueueStates(currentUser, device, 'NOT_INUSE')
      await setUserHint(currentUser, None, ast) # set hints for previous user
    await setDeviceUser(device, user) # Bind user to device
    # If user is bound to some other devices, unbind him and set
    # device states for those devices
    await unbindOtherDevices(user, device, ast)
    if not (await setUserHint(user, dial, ast)): # Set hints for user on new device
      return hintError(user, device)
    await setUserDeviceStates(user, device, ast) # Set device states for users new device
    if not (await setUserDevice(user, device)): # Bind device to user
      return bindError(user, device)
    app.cache['usermap'][device] = user
    app.cache['devicemap'][user] = device
    await amiUserEvent('DeviceBound',{'device': device, 'newUser': user, 'oldUser': currentUser})
    return successfullyBound(user, device)

@app.route('/device/<device>/off')
class DeviceUnBind(Resource):
  @authRequired
  @app.param('device', 'Device number to unbind', 'path')
  @app.response(HTTPStatus.OK, 'JSON reply with fields "success" and "result"')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, device):
    '''Unbinds any user from device.
    Device is checked for existance.
    '''
    if (device != request.device) and (not request.admin):
      abort(401)
    dial = await getDeviceDial(device) # Check if device exists in astdb
    if dial is None:
      return noDevice(device)
    currentUser = await getDeviceUser(device) # Check if any user is bound to device
    if currentUser in NONEs:
      return noUserBound(device)
    else:
      result = await amiSetVar('PRESENCE_STATE(CustomPresence:{})'.format(currentUser), 'available')
      result = await amiDBDel('DND', '{}'.format(currentUser))
      ast = await getGlobalVars()
      await setUserDevice(currentUser, None) # Unbind device from current user
      if ast.QUEDEVSTATE == 'TRUE': # set device states for current user queues
        await setQueueStates(currentUser, device, 'NOT_INUSE')
      await setUserHint(currentUser, None, ast) # set hints for current user
    await setDeviceUser(device, 'none') # Unbind user from device
    del app.cache['usermap'][device]
    del app.cache['devicemap'][currentUser]
    await amiUserEvent('DeviceUnbound',{'device': device, 'oldUser': currentUser})
    return successfullyUnbound(currentUser, device)

@app.route('/cdr')
class CDR(Resource):
  @authRequired
  @app.param('end', 'End of datetime range. Defaults to now. Allowed formats are: timestamp, ISO 8601 or YYYYMMDDhhmmss', 'query')
  @app.param('start', 'Start of datetime range. Defaults to end-24h. Allowed formats are: timestamp, ISO 8601 or YYYYMMDDhhmmss', 'query')
  @app.response(HTTPStatus.OK, 'JSON reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns CDR data, groupped by logical call id.
    All request arguments are optional.
    '''
    if not request.admin:
      abort(401)
    start = parseDatetime(request.args.get('start'))
    end = parseDatetime(request.args.get('end'))
    cdr = await getCDR(start, end)
    return successReply(cdr)

@app.route('/cel')
class CEL(Resource):
  @authRequired
  @app.param('end', 'End of datetime range. Defaults to now. Allowed formats are: timestamp, ISO 8601 or YYYYMMDDhhmmss', 'query')
  @app.param('start', 'Start of datetime range. Defaults to end-24h. Allowed formats are: timestamp, ISO 8601 or YYYYMMDDhhmmss', 'query')
  @app.response(HTTPStatus.OK, 'JSON reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns CEL data, groupped by logical call id.
    All request arguments are optional.
    '''
    if not request.admin:
      abort(401)
    start = parseDatetime(request.args.get('start'))
    end = parseDatetime(request.args.get('end'))
    cel = await getCEL(start, end)
    return successReply(cel)

@app.route('/calls')
class Calls(Resource):
  @authRequired
  @app.param('end', 'End of datetime range. Defaults to now. Allowed formats are: timestamp, ISO 8601 and YYYYMMDDhhmmss', 'query')
  @app.param('start', 'Start of datetime range. Defaults to end-24h. Allowed formats are: timestamp, ISO 8601 and YYYYMMDDhhmmss', 'query')
  @app.response(HTTPStatus.OK, 'JSON reply')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns aggregated call data JSON. Draft implementation.
    All request arguments are optional.
    '''
    if not request.admin:
      abort(401)
    calls = []
    start = parseDatetime(request.args.get('start'))
    end = parseDatetime(request.args.get('end'))
    cdr = await getCDR(start, end)
    for c in cdr:
      _call = {'id':c.linkedid,
               'start':c.start,
               'type': c.direction,
               'numberA': c.cnum,
               'numberB': c.dst,
               'line': c.did,
               'duration': c.duration,
               'waiting': c.waiting,
               'status':c.disposition,
               'url': c.file }
      calls.append(_call)
    return successReply(calls)

@app.route('/user/<user>/calls')
class UserCalls(Resource):
  @authRequired
  @app.param('user', 'User to query for call stats', 'path')
  @app.param('end', 'End of datetime range. Defaults to now. Allowed formats are: timestamp, ISO 8601 and YYYYMMDDhhmmss', 'query')
  @app.param('start', 'Start of datetime range. Defaults to end-24h. Allowed formats are: timestamp, ISO 8601 and YYYYMMDDhhmmss', 'query')
  @app.param('direction', 'Calls direction, in or out. If not specified both are returned', 'query')
  @app.param('limit', 'Max number of returned records, defaults to unlimited. Use offset parameter together with limit', 'query')
  @app.param('offset', 'If limit is specified use offset parameter to request more results', 'query')
  @app.param('order', 'Calls sort order for datetime field. ASC or DESC. Defaults to ASC', 'query')
  @app.response(HTTPStatus.OK, 'JSON data {"status":status,"data":data,"message":message}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, user):
    '''Returns user's call stats.
    '''
    if (user != request.user) and (not request.admin):
      abort(401)
    if user not in app.cache['ustates']:
      return noUser(user)
    cdr = await getUserCDR(user,
                           parseDatetime(request.args.get('start')),
                           parseDatetime(request.args.get('end')),
                           request.args.get('direction', None),
                           request.args.get('limit', None),
                           request.args.get('offset', None),
                           request.args.get('order', 'ASC'))
    return successReply(cdr)
    
@app.route('/call/<call_id>')
class CallInfo(Resource):
  @authRequired
  @app.param('call_id', 'call_id for ', 'path')
  @app.response(HTTPStatus.OK, 'JSON data {"status":status,"data":data,"message":message}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, call_id):
    '''Returns call info.'''

    call = await getCallInfo(call_id)
    return successReply(call)

@app.route('/device/<device>/callback')
class DeviceCallback(Resource):
  @authRequired
  @app.param('device', 'Device to get/set the callback url for', 'path')
  @app.param('url', 'used to set the Callback url for the device', 'query')
  @app.response(HTTPStatus.OK, 'JSON data {"user":user,"state":state}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self, device):
    '''Returns and sets device's callback url.
    '''
    if (device != request.device) and (not request.admin):
      abort(401)
    url = request.args.get('url', None)
    if url is not None:
      await db.execute(query='REPLACE INTO callback_urls (device, url) VALUES (:device, :url)',
                       values={'device': device,'url': url})
    else:
      row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device',
                               values={'device': device})
      if row is not None:
        url = row['url']
    return successCallbackURL(device, url)

@app.route('/group/ringing/callback')
class GroupRingingCallback(Resource):
  @authRequired
  @app.param('url', 'used to set the Callback url for the group ringing callback', 'query')
  @app.response(HTTPStatus.OK, 'JSON data {"url":url}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns and sets groupRinging callback url.
    '''
    if not request.admin:
      abort(401)
    url = request.args.get('url', None)
    if url is not None:
      await db.execute(query='REPLACE INTO callback_urls (device, url) VALUES (:device, :url)',
                       values={'device': 'groupRinging','url': url})
    else:
      row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device',
                               values={'device': 'groupRinging'})
      if row is not None:
        url = row['url']
    return successCommonCallbackURL('groupRinging', url)

@app.route('/group/answered/callback')
class GroupAnsweredCallback(Resource):
  @authRequired
  @app.param('url', 'used to set the Callback url for the group answered callback', 'query')
  @app.response(HTTPStatus.OK, 'JSON data {"url":url}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns and sets groupAnswered callback url.
    '''
    if not request.admin:
      abort(401)
    url = request.args.get('url', None)
    if url is not None:
      await db.execute(query='REPLACE INTO callback_urls (device, url) VALUES (:device, :url)',
                       values={'device': 'groupAnswered','url': url})
    else:
      row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device',
                               values={'device': 'groupAnswered'})
      if row is not None:
        url = row['url']
    return successCommonCallbackURL('groupAnswered', url)

@app.route('/queue/enter/callback')
class QueueEnterCallback(Resource):
  @authRequired
  @app.param('url', 'used to set the Callback url for the queue enter callback', 'query')
  @app.response(HTTPStatus.OK, 'JSON data {"url":url}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns and sets queueEnter callback url.
    '''
    if not request.admin:
      abort(401)
    url = request.args.get('url', None)
    if url is not None:
      await db.execute(query='REPLACE INTO callback_urls (device, url) VALUES (:device, :url)',
                       values={'device': 'queueEnter','url': url})
    else:
      row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device',
                               values={'device': 'queueEnter'})
      if row is not None:
        url = row['url']
    return successCommonCallbackURL('queueEnter', url)

@app.route('/queue/leave/callback')
class QueueLeaveCallback(Resource):
  @authRequired
  @app.param('url', 'used to set the Callback url for the queue leave callback', 'query')
  @app.response(HTTPStatus.OK, 'JSON data {"url":url}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self):
    '''Returns and sets queueLeave callback url.
    '''
    if not request.admin:
      abort(401)
    url = request.args.get('url', None)
    if url is not None:
      await db.execute(query='REPLACE INTO callback_urls (device, url) VALUES (:device, :url)',
                       values={'device': 'queueLeave','url': url})
    else:
      row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device',
                               values={'device': 'queueLeave'})
      if row is not None:
        url = row['url']
    return successCommonCallbackURL('queueLeave', url)


@app.route('/callback/<type>')
class CommonCallback(Resource):
  @authRequired
  @app.param('type', 'Callback type', 'path')
  @app.param('url', 'used to set the Callback url for the autocall', 'query')
  @app.response(HTTPStatus.OK, 'JSON data {"url":url}')
  @app.response(HTTPStatus.UNAUTHORIZED, 'Authorization required')
  async def get(self,type):
    '''Returns and sets Autocall callback url.
    '''
    if not request.admin:
      abort(401)
    url = request.args.get('url', None)
    if url is not None:
      await db.execute(query='REPLACE INTO callback_urls (device, url) VALUES (:device, :url)',
                       values={'device': type,'url': url})
    else:
      row = await db.fetch_one(query='SELECT url FROM callback_urls WHERE device = :device',
                               values={'device': type})
      if row is not None:
        url = row['url']
    return successCommonCallbackURL(type, url)

manager.connect()
app.run(loop=main_loop, host='0.0.0.0', port=app.config['PORT'])