# -*- encoding: utf-8 -*-
"""
sqlite3 database module for MURSAT1 Mission Dashboard
"""

import sqlite3

class Db:

  def __init__ (self):
    self.conn = sqlite3.connect ('/usr/local/mmd/db/mmd.db')
    self.conn.text_factory = str
    self.conn.row_factory = sqlite3.Row
    self.cursor = self.conn.cursor ()
    self.cursor.execute ('PRAGMA foreign_keys = ON')
    self.conn.commit ()

  def sessionFind (self, session_id):
    self.cursor.execute ('SELECT * FROM session WHERE id=?', (session_id,))
    return self.cursor.fetchone ()

  def sessionInit (self, session_id, email, expires, renewal, status):
    try:
      self.cursor.execute ('INSERT INTO session (id, email, expires, renewal, status) VALUES (?,?,?,?,?)', (session_id, email, expires, renewal, status,))
      self.conn.commit ()
      return True
    except sqlite3.IntegrityError:
      return False

  def sessionDelete (self, session_id):
    self.cursor.execute ('DELETE FROM session WHERE id=?', (session_id,))
    self.conn.commit ()

  def sessionUpdate (self, session_id, expires):
    self.cursor.execute ('UPDATE session SET expires=? WHERE id=?', (expires, session_id,))
    self.conn.commit ()

  def sessionRenew (self, session_id, expires, renewal, token):
    self.cursor.execute ('UPDATE session SET expires=?,renewal=?,id=? WHERE id=?', (expires, renewal, token, session_id,))
    self.conn.commit ()

  def sessionSetEmail (self, session_id, email):
    self.cursor.execute ('UPDATE session SET email=? WHERE id=?', (email, session_id))
    self.conn.commit ()

  def sessionSetStatus (self, session_id, status):
    self.cursor.execute ('UPDATE session SET status=? WHERE id=?', (status, session_id))
    self.conn.commit ()

  def sessionDeleteExpired (self, expires):  
    self.cursor.execute ('DELETE FROM session WHERE expires<?', (expires,))
    self.conn.commit ()
    return self.cursor.rowcount

  #
  # user related methods
  #
  def userCreate (self, user_id, firstname, lastname, email, callsign, password, code, regtimeout):
    try:
      self.cursor.execute ('INSERT INTO user (id, firstname, lastname, email, callsign, password, code, regtimeout) VALUES (?,?,?,?,?,?,?,?)',
      (user_id, firstname, lastname, email, callsign, password, code, regtimeout))
      self.conn.commit ()
      return True
    except sqlite3.IntegrityError:
      return False

  def userConfirm (self, code):
    self.cursor.execute ('UPDATE user SET status="valid" WHERE code=?', (code, ))
    self.conn.commit ()

  def userCancel (self, code):
    self.cursor.execute ('DELETE FROM user WHERE code=?', (code, ))
    self.conn.commit ()

  def userDelete (self, user_id):
    self.cursor.execute ('DELETE FROM user WHERE id=?', (user_id,))
    self.conn.commit ()

  def userFindId (self, user_id):
    self.cursor.execute ('SELECT * FROM user WHERE id=?', (user_id,))
    return self.cursor.fetchone ()

  def userFindEmail (self, email):
    self.cursor.execute ('SELECT * FROM user WHERE email=?', (email,))
    return self.cursor.fetchone ()

  def userFindPending (self, code):
    self.cursor.execute ('SELECT * FROM user WHERE code=? and status="pending"', (code,))
    return self.cursor.fetchone ()

  # location related methods
  def locationCreate (self, location_id, name, longitude, latitude, altitude, user_id, is_default):
    try:
      self.cursor.execute ('INSERT INTO location (id, name, longitude, latitude, altitude, user_id, is_default) VALUES (?,?,?,?,?,?,?)', (location_id, name, longitude, latitude, altitude, user_id, is_default))
      self.conn.commit ()
      return True
    except sqlite3.IntegrityError:
      return False

  def locationFindUserId (self, user_id):
    self.cursor.execute ('SELECT * FROM location WHERE user_id=?', (user_id,))
    return self.cursor.fetchone ()

  def locationListByUserId (self, user_id):
    self.cursor.execute ('SELECT id FROM location WHERE user_id=?', (user_id,))
    return self.cursor.fetchall ()

  def locationFindId (self, location_id):
    self.cursor.execute ('SELECT * FROM location WHERE id=?', (location_id,))
    return self.cursor.fetchone ()

  def locationDeleteId (self, location_id):
    self.cursor.execute ('DELETE FROM location WHERE id=?', (location_id,))
    self.conn.commit ()

  # satellite methods
  def satelliteGetLatestSSP (self):
    self.cursor.execute ('SELECT * FROM ssps ORDER BY timestamp DESC')
    return self.cursor.fetchone ()

  def satelliteInsertNewSSP (self, timestamp, longitude, latitude):
    self.cursor.execute ('INSERT INTO ssps (timestamp, longitude, latitude) VALUES (?,?,?)', (timestamp, longitude, latitude,))
    self.conn.commit ()

  def satelliteLoadTrajectory (self, timestamp, count):
    '''
    get count rows from table ssps, starting at >= timestamp
    '''
    self.cursor.execute ('SELECT * FROM ssps WHERE timestamp >=? ORDER BY timestamp ASC LIMIT ?', (timestamp, count,))
    return self.cursor.fetchall ()

  def satelliteLoadCurrentSSP (self, timestamp):
    self.cursor.execute ('SELECT * FROM ssps WHERE timestamp >=? LIMIT 1', (timestamp,))
    return self.cursor.fetchone ()

  def satelliteDeleteObsoleteSSPs (self, timestamp):
    self.cursor.execute ('DELETE FROM ssps WHERE timestamp >=?', (timestamp,))
    self.conn.commit ()

  def close (self):
    self.cursor.close ()
    self.conn.close ()

if __name__ == "__main__":
  pass

# vim: tw=0 ts=2 expandtab
# EOF