import sqlite3
import ipdb
from enums import Table, States, Services, Datatypes

class DBconnection:

  tables = ["msg","history"]

  structure = {
    Table.id : "integer PRIMARY KEY",
    Table.path : "text",
    Table.serv : "text",
    Table.dest : "text",
    Table.type : "text",
    Table.state : "text"
  }

  def __init__(self,db):
    self.db = db
    query = "CREATE TABLE IF NOT EXISTS msg("
    for column in DBconnection.structure:
      query += column + " " + DBconnection.structure[column] + ","
    query = query.strip(",") + ")"
    # main table
    self.query(query)
    # delivered messages that were informed
    self.query(query.replace("msg","history",1))

  def query(self,query,*args):
    if query.upper().find("INSERT") == 0:
      return "Use la funcion insert() para insertar"
    entities = None
    for ar in args:
      entities = ar
    con = sqlite3.connect(self.db)
    cursor = con.cursor()
    if entities:
      cursor.execute(query,entities)
    else:
      cursor.execute(query)
    rows = cursor.fetchall()
    con.commit()
    con.close()
    return rows

  def insert(self,table,insertions):
    if not table in DBconnection.tables:
      return "La tabla " + table + " no existe o no está contemplada"
    query = "INSERT INTO " + table + "("
    values = " VALUES("
    for column in insertions:
      if not self.check(column,insertions[column]):
        return "El dato '" + insertions[column] + "' no es valido"
      query += column + ","
      values += "'" + insertions[column] + "',"
    query = query.strip(",") + ")"
    values = values.strip(",") + ")"
    con = sqlite3.connect(self.db)
    cursor = con.cursor()
    cursor.execute(query + values)
    cursor.execute("SELECT last_insert_rowid()")
    id = cursor.fetchall()[0][0]
    con.commit()
    con.close()
    return id

  def update(self,table,comparator,alterations):
    if not table in DBconnection.tables:
      return "La tabla " + table + " no existe o no está contemplada"
    if not Table.validate(comparator[0]):
      return "El comparador no es una columna valida"
    query = "UPDATE " + table + " SET "
    where = " WHERE " + comparator[0] + "=" + comparator[1]
    for column in alterations:
      if not self.check(column,alterations[column]):
        return "El dato '" + alterations[column] + "' no es valido"
      query += column + "='" + alterations[column] + "',"
    query = query.strip(",")
    ipdb.set_trace()
    con = sqlite3.connect(self.db)
    cursor = con.cursor()
    cursor.execute(query + where)
    con.commit()
    con.close()

  def check(self,column,data):
    if column == Table.path:
      return True
    if column == Table.serv:
      return Services.validate(data)
    if column == Table.dest:
      return True
    if column == Table.type:
      return Datatypes.validate(data)
    if column == Table.state:
      return States.validate(data)
    return False

  @staticmethod
  def parseToTable(rows):
    dictarray = []
    for row in rows:
      dictrow = {}
      i = 0
      for column in DBconnection.structure:
        dictrow[column] = row[i]
        i += 1
      dictarray.append(dictrow)
    return dictarray