class IOCData(object):
    """A wrapper to connect to the IOC database via MySQL"""

    def __init__(self, dbid, procserver, prefix):
        """Constructor

        Args:
            dbid (string): The id of the database that holds IOC information
            procserver (ProcServWrapper): An instance of ProcServWrapper, used to start and stop IOCs
            prefix (string): The pv prefix of the instrument the server is being run on
        """

        # Set up the database connection
        self._db = SQLAbstraction(dbid, dbid, "$" + dbid)

        self._procserve = procserver
        self._prefix = prefix
        self._running_iocs = list()
        self._running_iocs_lock = RLock()

    def get_iocs(self):
        """Gets a list of all the IOCs in the database and whether or not they are running

        Returns:
            dict : IOCs and their running status
        """
        try:
            sqlquery = "SELECT iocname FROM iocs"
            iocs = dict((element[0], dict()) for element in self._db.query(sqlquery))
        except Exception as err:
            print_and_log("could not get IOCS from database: %s" % err, "MAJOR", "DBSVR")
            iocs = dict()
        for ioc in iocs.keys():
            ioc = ioc.encode('ascii', 'replace')
            with self._running_iocs_lock:
                # Create a copy so we don't lock the list for longer than necessary (do we need to do this?)
                running = list(self._running_iocs)
            if ioc in running:
                iocs[ioc]["running"] = True
            else:
                iocs[ioc]["running"] = False
        return iocs

    def get_active_iocs(self):
        """Gets a list of all the running IOCs

        Returns:
            list : The names of running IOCs
        """
        return self._running_iocs

    def get_pars(self, category):
        """Gets parameters of a particular category from the IOC database of

        Returns:
            list : A list of the names of PVs associated with the parameter category
        """
        values = []
        try:
            sqlquery = "SELECT DISTINCT pvinfo.pvname FROM pvinfo"
            sqlquery += " INNER JOIN pvs ON pvs.pvname = pvinfo.pvname"
            sqlquery += " WHERE (infoname='PVCATEGORY' AND value LIKE '%" + category + "%' AND pvinfo.pvname NOT LIKE '%:SP')"
            # Get as a plain list
            values = [str(element[0]) for element in self._db.query(sqlquery)]
            # Convert any bytearrays
            for i, pv in enumerate(values):
                for j, element in enumerate(pv):
                    if type(element) == bytearray:
                        values[i][j] = element.decode("utf-8")
        except Exception as err:
            print_and_log("could not get parameters category %s from database: %s" % (category, err), "MAJOR", "DBSVR")
        return values

    def get_beamline_pars(self):
        """Gets the beamline parameters from the IOC database

        Returns:
            list : A list of the names of PVs associated with beamline parameters
        """
        return self.get_pars('BEAMLINEPAR')

    def get_sample_pars(self):
        """Gets the sample parameters from the IOC database

        Returns:
            list : A list of the names of PVs associated with sample parameters
        """
        return self.get_pars('SAMPLEPAR')

    def get_user_pars(self):
        """Gets the user parameters from the IOC database

        Returns:
            list : A list of the names of PVs associated with user parameters
        """
        return self.get_pars('USERPAR')

    def update_iocs_status(self):
        """Accesses the db to get a list of IOCs and checks to see if they are currently running

        Returns:
            list : The names of running IOCs
        """
        with self._running_iocs_lock:
            self._running_iocs = list()
            try:
                # Get all the iocnames and whether they are running, but ignore IOCs associated with PSCTRL
                sqlquery = "SELECT iocname, running FROM iocrt WHERE (iocname NOT LIKE 'PSCTRL_%')"
                rows = self._db.query(sqlquery)
                for row in rows:
                    # Check to see if running using CA and procserv
                    try:
                        if self._procserve.get_ioc_status(self._prefix, row[0]).upper() == "RUNNING":
                            self._running_iocs.append(row[0])
                            if row[1] == 0:
                                # This should only get called if the IOC failed to tell the DB it started
                                self._db.update("UPDATE iocrt SET running=1 WHERE iocname='%s'" % row[0])
                        else:
                            if row[1] == 1:
                                self._db.update("UPDATE iocrt SET running=0 WHERE iocname='%s'" % row[0])
                    except Exception as err:
                        # Fail but continue - probably couldn't find procserv for the ioc
                        print_and_log("issue with updating IOC status: %s" % err, "MAJOR", "DBSVR")
            except Exception as err:
                print_and_log("issue with updating IOC statuses: %s" % err, "MAJOR", "DBSVR")

            return self._running_iocs

    def get_interesting_pvs(self, level="", ioc=None):
        """Queries the database for PVs based on their interest level and their IOC.

        Args:
            level (string, optional): The interest level to search for, either High, Medium or Facility. Default to
                                    all interest levels
            ioc (string, optional): The IOC to search. Default is all IOCs.

        Returns:
            list : A list of the PVs that match the search given by level and ioc

        """
        values = []
        sqlquery = "SELECT DISTINCT pvinfo.pvname, pvs.record_type, pvs.record_desc, pvs.iocname FROM pvinfo"
        sqlquery += " INNER JOIN pvs ON pvs.pvname = pvinfo.pvname"
        where_ioc = ''

        if ioc is not None and ioc != "":
            where_ioc = "AND iocname='%s'" % ioc

        try:
            if level.lower().startswith('h'):
                sqlquery += " WHERE (infoname='INTEREST' AND value='HIGH' {0})".format(where_ioc)
            elif level.lower().startswith('m'):
                sqlquery += " WHERE (infoname='INTEREST' AND value='MEDIUM' {0})".format(where_ioc)
            elif level.lower().startswith('f'):
                sqlquery += " WHERE (infoname='INTEREST' AND value='FACILITY' {0})".format(where_ioc)
            else:
                # Try to get all pvs!
                pass

            # Get as a plain list of lists
            values = [list(element) for element in self._db.query(sqlquery)]
            # Convert any bytearrays
            for i, pv in enumerate(values):
                for j, element in enumerate(pv):
                    if type(element) == bytearray:
                        values[i][j] = element.decode("utf-8")
        except Exception as err:
            print_and_log("issue with getting interesting PVs: %s" % err, "MAJOR", "DBSVR")
        return values

    def get_active_pvs(self):
        """Queries the database for active PVs.

        Returns:
            list : A list of the PVs in running IOCs

        """
        values = []
        sqlquery = "SELECT pvinfo.pvname, pvs.record_type, pvs.record_desc, pvs.iocname FROM pvinfo"
        sqlquery += " INNER JOIN pvs ON pvs.pvname = pvinfo.pvname"
        # Ensure that only active IOCs are considered
        sqlquery += " WHERE (pvs.iocname in (SELECT iocname FROM iocrt WHERE running=1) AND infoname='INTEREST')"

        try:
            # Get as a plain list of lists
            values = [list(element) for element in self._db.query(sqlquery)]
            # Convert any bytearrays
            for i, pv in enumerate(values):
                for j, element in enumerate(pv):
                    if type(element) == bytearray:
                        values[i][j] = element.decode("utf-8")
        except Exception as err:
            print_and_log("issue with getting active PVs: %s" % err, "MAJOR", "DBSVR")

        return values
class ExpData(object):
    """A wrapper to connect to the IOC database via MySQL"""

    """Constant list of PVs to use"""
    EDPV = {
        'ED:RBNUMBER:SP': {
            'type': 'char',
            'count': 16000,
            'value': [0],
        },
        'ED:USERNAME:SP': {
            'type': 'char',
            'count': 16000,
            'value': [0],
        },
    }

    def make_ascii_mappings():
        """create mapping for characters not converted to 7 bit by NFKD"""
        mappings_in = [ ord(char) for char in u'\xd0\xd7\xd8\xde\xdf\xf0\xf8\xfe' ]
        mappings_out = u'DXOPBoop'
        d = dict(zip(mappings_in, mappings_out))
        d[ord(u'\xc6')] = u'AE'
        d[ord(u'\xe6')] = u'ae'
        return d

    _toascii = make_ascii_mappings()

    def __init__(self, prefix, ca=ChannelAccess):
        """Constructor

        Args:
            dbid (string): The id of the database that holds IOC information
            prefix (string): The pv prefix of the instrument the server is being run on
        """
        # Set up the database connection
        self._db = SQLAbstraction('exp_data', "exp_data", "$exp_data")

        # Build the PV names to be used
        self._simrbpv = prefix + "ED:SIM:RBNUMBER"
        self._daerbpv = prefix + "ED:RBNUMBER:DAE:SP"
        self._simnames = prefix + "ED:SIM:USERNAME"
        self._daenamespv = prefix + "ED:USERNAME:DAE:SP"
        self._surnamepv = prefix + "ED:SURNAME"
        self._orgspv = prefix + "ED:ORGS"

        # Set the channel access server to use
        self.ca = ca

    # def __open_connection(self):
    #     return self._db.__open_connection()

    def _get_team(self, experimentID):
        """Gets the team members

        Args:
            experimentID (string): the id of the experiment to load related data from

        Returns:
            team (list): the team data found by the SQL query
        """
        try:
            sqlquery = "SELECT user.name, user.organisation, role.name"
            sqlquery += " FROM role, user, experimentteams"
            sqlquery += " WHERE role.roleID = experimentteams.roleID"
            sqlquery += " AND user.userID = experimentteams.userID"
            sqlquery += " AND experimentteams.experimentID = %s" % experimentID
            sqlquery += " ORDER BY role.priority"
            team = [list(element) for element in self._db.query(sqlquery)]
            if len(team) == 0:
                raise Exception("unable to find team details for experiment ID %s" % experimentID)
            else:
                return team
        except Exception as err:
            raise Exception("issue getting experimental team: %s" % err)

    def _experiment_exists(self, experimentID):
        """ Gets the experiment

        Args:
            experimentID (string): the id of the experiment to load related data from

        Returns:
            exists (boolean): TRUE if the experiment exists, FALSE otherwise
        """
        try:
            sqlquery = "SELECT experiment.experimentID"
            sqlquery += " FROM experiment "
            sqlquery += " WHERE experiment.experimentID = \"%s\"" % experimentID
            id = self._db.query(sqlquery)
            if len(id) >= 1:
                return True
            else:
                return False
        except Exception as err:
            raise Exception("error finding the experiment: %s" % err)

    def encode4return(self, data):
        """Converts data to JSON, compresses it and converts it to hex.

        Args:
            data (string): The data to encode

        Returns:
            string : The encoded data
        """
        return compress_and_hex(json.dumps(data).encode('utf-8', 'replace'))

    def _get_surname_from_fullname(self, fullname):
        try:
            return fullname.split(" ")[-1]
        except:
            return fullname

    def updateExperimentID(self, experimentID):
        """Updates the associated PVs when an experiment ID is set

        Args:
            experimentID (string): the id of the experiment to load related data from

        Returns:
            None specifically, but the following information external to the server is set
            # TODO: Update with the correct PVs for this part

        """
        # Update the RB Number for lookup - SIM for testing, DAE for production
        self.ca.caput(self._simrbpv, experimentID)
        self.ca.caput(self._daerbpv, experimentID)

        # Check for the experiment ID
        names = []
        surnames = []
        orgs = []

        if not self._experiment_exists(experimentID):
            self.ca.caput(self._simnames, self.encode4return(names))
            self.ca.caput(self._surnamepv, self.encode4return(surnames))
            self.ca.caput(self._orgspv, self.encode4return(orgs))
            raise Exception("error finding the experiment: %s" % experimentID)

        # Get the user information from the database and update the associated PVs
        if self._db is not None:
            teammembers = self._get_team(experimentID)
            if teammembers is not None:
                # Generate the lists/similar for conversion to JSON
                for member in teammembers:
                    fullname = unicode(member[0])
                    org = unicode(member[1])
                    role = unicode(member[2])
                    if not role == "Contact":
                        surnames.append(self._get_surname_from_fullname(fullname))
                    orgs.append(org)
                    name = user(fullname, org, role.lower())
                    names.append(name.__dict__)
            orgs = list(set(orgs))
            self.ca.caput(self._simnames, self.encode4return(names))
            self.ca.caput(self._surnamepv, self.encode4return(surnames))
            self.ca.caput(self._orgspv, self.encode4return(orgs))
            # The value put to the dae names pv will need changing in time to use compressed and hexed json etc. but
            # this is not available at this time in the ICP
            self.ca.caput(self._daenamespv, ExpData.make_name_list_ascii(surnames))

    def updateUsername(self, users):
        """Updates the associated PVs when the User Names are altered

        Args:
            users (string): uncompressed and dehexed json string with the user details

        Returns:
            None specifically, but the following information external to the server is set
            # TODO: Update with the correct PVs for this part
        """
        names = []
        surnames = []
        orgs = []
        if len(users) > 3:
            # Format the string into a list of JSON strings for decoding/encoding
            users = users[1:-1]
            users = users.split("},{")
            if len(users) > 1:
                # Strip the {} from the beginning and the end to allow for easier editing of the teammembers
                users[0] = users[0][1:]
                users[-1] = users[-1][:len(users[-1])-1]
                # Add a {} to EACH teammember
                for ndx, member in enumerate(users):
                    users[ndx] = "{" + member + "}"

            # Loop through the list of strings to generate the lists/similar for conversion to JSON
            for teammember in users:
                member = json.loads(teammember)
                fullname = unicode(member['name'])
                org = unicode(member['institute'])
                role = unicode(member['role'])
                if not role == "Contact":
                    surnames.append(self._get_surname_from_fullname(fullname))
                orgs.append(org)
                name = user(fullname, org, role.lower())
                names.append(name.__dict__)
            orgs = list(set(orgs))
        self.ca.caput(self._simnames, self.encode4return(names))
        self.ca.caput(self._surnamepv, self.encode4return(surnames))
        self.ca.caput(self._orgspv, self.encode4return(orgs))
        # The value put to the dae names pv will need changing in time to use compressed and hexed json etc. but
        # this is not available at this time in the ICP
        if not surnames:
            self.ca.caput(self._daenamespv, " ")
        else:
            self.ca.caput(self._daenamespv, ExpData.make_name_list_ascii(surnames))

    @staticmethod
    def make_name_list_ascii(names):
        """Takes a unicode list of names and creates a best ascii comma separated list
            this implementation is a temporary fix until we install the PyPi unidecode module
        
            Args:
                name(list): list of unicode names
            
            Returns:
                comma separated ascii string of names with special characters adjusted
                
        """
        nlist = u','.join(names)
        nfkd_form = unicodedata.normalize('NFKD', nlist)
        nlist_no_sc = u''.join([c for c in nfkd_form if not unicodedata.combining(c)])
        return nlist_no_sc.translate(ExpData._toascii).encode('ascii','ignore')