#!/usr/bin/python
# -*- coding: utf-8 -*-

# this script download messages (last 20) from sensor (cloud-on-chip) 
# platform to MySQL DB
# objects are list on "sig_objects" table,
# the messages are store on "sig_messages" table

# call this script regulary (with crond) for update "sig_messages" table

# call "<this_script> --help" for args help

# some libs
import sys
import sensor
import time
from struct import *
import MySQLdb as mdb
import argparse

# some consts

# some vars

# process args
parser = argparse.ArgumentParser()
parser.add_argument("-r", "--day_rebuild", type=int,
                    help="resync local DB for a number of day")
parser.add_argument("-o", "--obj_id", type=int,
                    help="resync local DB for this object ID")
parser.add_argument("-v", "--verbosity", type=int, choices=[0, 1, 2],
                    help="increase output verbosity")
args = parser.parse_args()

if args.verbosity:
  print("verbosity level %s" % args.verbosity)

if args.verbosity and args.day_rebuild:
  print("rebuild turned on for %s day(s)" % args.day_rebuild)

if args.verbosity and args.obj_id:
  print("rebuild turned on for object_id=%s" % args.obj_id)

# open database
try:
  conn = mdb.connect('localhost', 'bee_sigfox_sync', '', 'beedb')
except:
  sys.stderr.write("DB connect error\n")
  exit(1)

# create db cursor with access to row by name
c = conn.cursor(mdb.cursors.DictCursor)

# search object to poll
if (args.obj_id):
  c.execute("SELECT object_id, modem_id, modem_key from `sig_objects` "+
            "WHERE object_id=%d" % args.obj_id)
else:
  c.execute("SELECT object_id, modem_id, modem_key from `sig_objects`")

objects_rec = c.fetchall()

for row in objects_rec:
  object_id  = row['object_id']
  modem_id  = format(row['modem_id'], "04X")
  modem_key = format(row['modem_key'], "08X")
  if (args.verbosity >= 1):
   print("*** request: [OBJ_ID = %d, MOD_ID = %s]" % (object_id, modem_id))

  # use sensor object
  device = sensor.Device()
  # get token
  if (not device.set_device(modem_id, modem_key)):
    sys.stderr.write("get token ko !\n")
    exit(2)

  # init timestamp for search history end date
  history_end_search = 0
  history_max_older  = 0
  last_get_loop      = False
  end_get_loop       = False

  # get loop
  while True:
    # get message history
    msgs = device.get_history(20, until = history_end_search)
    # skip if error
    if msgs == 0:
      sys.stderr.write("sensor return empty history\n")
      # exit get loop
      break

    # last get request (sensor return less of 20 msgs : sensor reach end of db)
    if (len(msgs) != 20):
      # log message
      if (args.verbosity >= 1):
        print("receive %s instead of 20, it's last get loop" % len(msgs))
      last_get_loop = True

    for msg in msgs:
      # test and format vars from JSON, process KeyError if json vars is not set
      try:
        msg_timestamp = msg['received']/1000
        msg_type = msg['type']
        msg_station = int(msg['station'], 16)
        msg_lvl = round(float(msg['lvl']))
        msg_pld = msg['payload']
      # next message if json var missing
      except KeyError:
        continue
      # set "age" vars
      if (history_max_older < msg_timestamp):
        history_max_older = msg_timestamp

      if args.day_rebuild:
        if (history_max_older - msg_timestamp) > (args.day_rebuild * 24 * 3600):
          # log exit message
          if (args.verbosity >= 1):
            print("reach max older message, exit")
          end_get_loop = True
          break

      # insert into database (check if already exist for not ++id in InnoDB)
      # add 06/03/2014 :
      #   duplicate bug workaround (before UNB modem firmware change) :
      #   if same payload within a window of +/- 600s since
      #   last message, insert is discard
      sql_command = ("INSERT INTO `sig_messages` (`message_id`,`object_id`, "+
                     "`rx_timestamp`, `type`, `payload`, "+
                     "`station_id`, `station_lvl`) "+
                     "SELECT NULL, '"+str(object_id)+"','"+str(msg_timestamp)+
                     "', '" +str(msg_type)+"', '"+str(msg_pld)+
                     "', '"+str(msg_station)+"', '"+str(msg_lvl)+"' FROM DUAL "+
                     "WHERE NOT EXISTS (SELECT * FROM `sig_messages` "+
                     "WHERE (`object_id`='"+str(object_id)+"') AND "+
                     "((`rx_timestamp`='"+str(msg_timestamp)+"') OR "+
                     "(`rx_timestamp` BETWEEN '"+str(msg_timestamp - 600)+"' AND '"+
                     str(msg_timestamp + 600)+"' AND `payload` = '"+str(msg_pld)+
                     "')) LIMIT 1);")
# old request
#      sql_command = ("INSERT INTO `sig_messages` (`message_id`,`object_id`, "+
#                     "`rx_timestamp`, `type`, `payload`, "+
#                     "`station_id`, `station_lvl`) "+
#                     "SELECT NULL, '"+str(object_id)+"','"+str(msg_timestamp)+
#                     "', '" +str(msg_type)+"', '"+str(msg_pld)+
#                     "', '"+str(msg_station)+"', '"+str(msg_lvl)+"' FROM DUAL "+
#                     "WHERE NOT EXISTS (SELECT * FROM `sig_messages` "+
#                     "WHERE `object_id`="+str(object_id)+" AND `rx_timestamp`="+
#                     str(msg_timestamp)+" LIMIT 1);")
      if (args.verbosity >= 2):
        print("SQL: %s" % (sql_command))
      try:
        c.execute(sql_command)
        if (args.verbosity >= 1):
          print("row insert: %d [OBJ_ID = %d, MOD_ID = %s, MSG_TIMESTAMP = %d]" % (c.rowcount, object_id, modem_id, msg_timestamp))
        conn.commit()
      except mdb.IntegrityError as e:
        print "--->SQL Error: %s" % e

    # for next get_history : end_search is last msg_timestamp
    history_end_search = msg_timestamp * 1000

    # end of the get loop : - not in rebuild mode
    #                       - it's last get (get return less than 20 messages)
    #                       - max rebuild day reach
    if (not args.day_rebuild) or last_get_loop or end_get_loop:
      break

exit(0)
