""" Interacts with sqlite3 db
"""
from __future__ import absolute_import
from __future__ import division
from __future__ import print_function

import six
import sqlite3
import os
import hashlib
import random
import time

import DIRAC
from DIRAC import gLogger, S_OK, S_ERROR
from DIRAC.FrameworkSystem.private.monitoring.Activity import Activity
from DIRAC.Core.Utilities import Time


class MonitoringCatalog(object):
    """
    This class is used to perform all kinds queries to the sqlite3 database.
    """

    def __init__(self, dataPath):
        """
        Initialize monitoring catalog
        """
        self.dbConn = False
        self.dataPath = dataPath
        self.log = gLogger.getSubLogger("ActivityCatalog")
        self.createSchema()

    def __connect(self):
        """
        Connects to database
        """
        if not self.dbConn:
            dbPath = "%s/monitoring.db" % self.dataPath
            self.dbConn = sqlite3.connect(dbPath, timeout=20, isolation_level=None)
            # These two settings dramatically increase the performance
            # at the cost of a small corruption risk in case of OS crash
            # It is acceptable though, given the nature of the data
            # details here https://www.
sqlite.org/pragma.html
            c = self.dbConn.cursor()
            c.execute("PRAGMA synchronous = OFF")
            c.execute("PRAGMA journal_mode = TRUNCATE")

    def __dbExecute(self, query, values=False):
      
  """
        Executes a sql statement.

        :type query: string
        :param query: The query to be executed.
        :type values: bool
        :param values: To execute query with values or not.
        :return: the cursor.
        """
        cursor = self.dbConn.cursor()  # pylint: disable=no-member
        self.log.debug("Executing %s" % query)
        executed = False
        retry = 0
        while not executed and retry < 10:
            retry += 1
            try:
                if values:
                    cursor.execute(query, values)
                else:
                    cursor.execute(query)
                executed = True
            except Exception as e:
                self.log.exception("Exception executing statement", "query: %s, values: %s" % (query, values))
                time.sleep(random.random())
        if not executed:
            self.log.error("Could not execute query, big mess ahead", "query: %s, values: %s" % (query, values))
        return cursor

    def __createTables(self):
        """
        Creates tables if not already created
        """
        self.log.info("Creating tables in db")
        try:
            filePath = "%s/monitoringSchema.sql" % os.path.dirname(__file__)
            fd = open(filePath)
            buff = fd.read()
            fd.close()
        except IOError as e:
            DIRAC.abort(1, "Can't read monitoring schema", filePath)
        while buff.find(";") > -1:
            limit = buff.find(";") + 1
            sqlQuery = buff[:limit].replace("\n", "")
            buff = buff[limit:]
            try:
                self.__dbExecute(sqlQuery)
            except Exception as e:
                DIRAC.abort(1, "Can't create tables", str(e))

    def createSchema(self):
        """
        Creates all the sql schema if it does not exist
        """
        self.__connect()
        try:
            sqlQuery = "SELECT name FROM sqlite_master WHERE type='table';"
            c = self.__dbExecute(sqlQuery)
            tablesList = c.fetchall()
            if len(tablesList) < 2:
                self.__createTables()
        except Exception as e:
            self.log.fatal("Failed to startup db engine", str(e))
            return False
        return True

    def __delete(self, table, dataDict):
        """
        Executes an sql delete.

        :type table: string
        :param table: name of the table.
        :type dataDict: dictionary
        :param dataDict: the data dictionary.
        """
        query = "DELETE FROM %s" % table
        valuesList = []
        keysList = []
        for key in dataDict:
            if isinstance(dataDict[key], list):
                orList = []
                for keyValue in dataDict[key]:
                    valuesList.append(keyValue)
                    orList.append("%s = ?" % key)
                keysList.append("( %s )" % " OR ".join(orList))
            else:
                valuesList.append(dataDict[key])
                keysList.append("%s = ?" % key)
        if keysList:
            query += " WHERE %s" % (" AND ".join(keysList))
        self.__dbExecute("%s;" % query, values=valuesList)

    def __select(self, fields, table, dataDict, extraCond="", queryEnd=""):
        """
        Executes a sql select.

        :type fields: string
        :param fields: The fields required in a string.
        :type table: string
        :param table: name of the table.
        :type dataDict: dictionary
        :param dataDict: the data dictionary.
        :return: a list of values.
        """
        valuesList = []
        keysList = []
        for key in dataDict:
            if isinstance(dataDict[key], list):
                orList = []
                for keyValue in dataDict[key]:
                    valuesList.append(keyValue)
                    orList.append("%s = ?" % key)
                keysList.append("( %s )" % " OR ".join(orList))
            else:
                valuesList.append(dataDict[key])
                keysList.append("%s = ?" % key)
        if isinstance(fields, six.string_types):
            fields = [fields]
        if len(keysList) > 0:
            whereCond = "WHERE %s" % (" AND ".join(keysList))
        else:
            whereCond = ""
        if extraCond:
            if whereCond:
                whereCond += " AND %s" % extraCond
            else:
                whereCond = "WHERE %s" % extraCond
        query = "SELECT %s FROM %s %s %s;" % (",".join(fields), table, whereCond, queryEnd)
        c = self.__dbExecute(query, values=valuesList)
        return c.fetchall()

    def __insert(self, table, specialDict, dataDict):
        """
        Executes an sql insert.

        :type table: string
        :param table: name of the table.
        :type specialDict: dictionary
        :param specialDict: the special dictionary.
        :type dataDict: dictionary
        :param dataDict: the data dictionary.
        :return: the number of rows inserted.
        """
        valuesList = []
        valuePoitersList = []
        namesList = []
        for key in specialDict:
            namesList.append(key)
            valuePoitersList.append(specialDict[key])
        for key in dataDict:
            namesList.append(key)
            valuePoitersList.append("?")
            valuesList.append(dataDict[key])
        query = "INSERT INTO %s (%s) VALUES (%s);" % (table, ", ".join(namesList), ",".join(valuePoitersList))
        c = self.__dbExecute(query, values=valuesList)
        return c.rowcount

    def __update(self, newValues, table, dataDict, extraCond=""):
        """
        Executes a sql update.

        :type table: string
        :param table: name of the table.
        :type newValues: dictionary
        :param newValues: a dictionary with new values.
        :type dataDict: dictionary
        :param dataDict: the data dictionary.
        :return: the number of rows updated.
        """
        valuesList = []
        keysList = []
        updateFields = []
        for key in newValues:
            updateFields.append("%s = ?" % key)
            valuesList.append(newValues[key])
        for key in dataDict:
            if isinstance(dataDict[key], list):
                orList = []
                for keyValue in dataDict[key]:
                    valuesList.append(keyValue)
                    orList.append("%s = ?" % key)
                keysList.append("( %s )" % " OR ".join(orList))
            else:
                valuesList.append(dataDict[key])
                keysList.append("%s = ?" % key)
        if len(keysList) > 0:
            whereCond = "WHERE %s" % (" AND ".join(keysList))
        else:
            whereCond =