import sqlite3

class Conversation:
  def __init__(self, dbPath = '/home/user/.rtcom-eventlogger/el.db'):
    self.conn = sqlite3.connect(dbPath)
    self.c = self.conn.cursor()

  def getServices(self, serviceType = None):
    query = 'select id, name from Services'
    result = self.c.execute(query)

    if serviceType is not None:
      for (sid, sname) in result:
        if serviceType.upper() in sname.upper():
          return sid

    return result

  def getEventTypes(self, eventType):
    query = 'select id, name from EventTypes'
    result = self.c.execute(query)

    if eventType is not None:
      for (eid, ename) in result:
        if eventType.upper() in ename.upper():
          return eid

    return result

  def getSMSsFromBetween(self, low, high):
    sid = self.getServices('sms')
    eid = self.getEventTypes('sms_inbound')

    query = "select start_time, free_text from Events"
    query += " where service_id = %s and event_type_id = %s" % (sid, eid)
    query += " and cast(remote_uid as integer) between %s and %s" % (low, high)

    return self.c.execute(query)

if __name__ == '__main__':
  convo = Conversation()
  nagios = convo.getSMSsFromBetween(0, 9999)

  for text in nagios:
    print text