from test.helpers.psqldb import PgSQLConnection


def find_between(s, first, last):
    try:
        start = s.index(first) + len(first)
        end = s.index(last, start)
        return s[start:end]
    except ValueError:
        return ""


class TestSrv_Database(object):
    ''' TestSrv Database Helper '''

    __sqlObject = None
    __xml_setup = None

    def __init__(self):
        pass

    def open(self, xmlObj):
        self.__xml_setup = xmlObj
        self.__sqlObject = PgSQLConnection()
        return self.__sqlObject.db_connect(self.__xml_setup.getdbConnectionStr())

    def getConfig(self):
        return self.__sqlObject.getConfig()

    def create_board(self, processor_id, model_id, variant, station, bmac=None):
        '''create a new board'''
        if bmac is None:
            sql = "SELECT * FROM isee.f_create_board('{}', '{}', '{}', NULL, '{}');".format(processor_id, model_id,
                                                                                            variant,
                                                                                            station)
        else:
            sql = "SELECT * FROM isee.f_create_board('{}', '{}', '{}', '{}', '{}');".format(processor_id, model_id,
                                                                                            variant,
                                                                                            bmac, station)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            print(r)
            print(str(err))
        return None

    def get_tests_list(self, board_uuid):
        '''get the board test list'''
        sql = "SELECT * FROM isee.f_get_tests_list('{}')".format(board_uuid)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def get_test_params_list(self, testid):
        sql = "SELECT * FROM isee.f_get_test_params_list({})".format(testid)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def open_test(self, board_uuid):
        sql = "SELECT * FROM isee.f_open_test('{}')".format(board_uuid)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def run_test(self, testid_ctl, testid):
        sql = "SELECT isee.f_run_test({},{})".format(testid_ctl, testid)
        # print('>>>' + sql)
        try:
            self.__sqlObject.db_execute_query(sql)
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def finish_test(self, testid_ctl, testid, newstatus, textresult):
        sql = "SELECT isee.f_finish_test({},{},'{}','{}')".format(testid_ctl, testid, newstatus, textresult)
        # print('>>>' + sql)
        try:
            print('finish_test => SQL {}'.format(sql))
            self.__sqlObject.db_execute_query(sql)
            return True
        except Exception as err:
            r = find_between(str(err), '#', '#')
            print('finish_test => {}'.format(err))
        return False

    def upload_result_file(self, testid_ctl, testid, desc, filepath, mimetype):
        try:
            # generate a new oid
            fileoid = self.__sqlObject.db_upload_large_file(filepath)
            # insert into a table
            sql = "SELECT isee.f_upload_result_file({},{},'{}','{}','{}')".format(testid_ctl, testid, desc, fileoid,
                                                                                  mimetype)
            # print('>>>' + sql)
            self.__sqlObject.db_execute_query(sql)
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def get_task_variables_list(self, board_uuid):
        sql = "SELECT * FROM isee.f_get_task_variables_list('{}')".format(board_uuid)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None


    def get_plc_macaddr(self, board_uuid):
        sql = "SELECT * FROM isee.f_get_plcmac(\'{}\')".format(board_uuid)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            return res, ''
        except Exception as err:
            r = find_between(str(err), '#', '#')
            print(r)
        return None, r


    def get_board_macaddr(self, board_uuid):
        sql = "SELECT * FROM isee.f_get_board_macaddr('{}')".format(board_uuid)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def open_task(self, uuid):
        sql = "SELECT * FROM isee.f_open_task('{}')".format(uuid)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def create_task_result(self, taskid_ctl, name, newstatus, newinfo=None):
        sql = "SELECT isee.f_create_task_result({},'{}','{}','{}')".format(taskid_ctl, name, newstatus, newinfo)
        # print('>>>' + sql)
        try:
            self.__sqlObject.db_execute_query(sql)
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def update_taskctl_status(self, taskid_ctl, newstatus):
        sql = "SELECT isee.f_update_taskctl_status({},'{}')".format(taskid_ctl, newstatus)
        # print('>>>' + sql)
        try:
            self.__sqlObject.db_execute_query(sql)
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def set_factorycode(self, uuid, factorycode):
        sql = "SELECT isee.f_set_factorycode('{}','{}')".format(uuid, factorycode)
        # print('>>>' + sql)
        try:
            self.__sqlObject.db_execute_query(sql)
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def read_station_state(self, station):
        sql = "SELECT * FROM station.getmystate('{}');".format(station)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def change_station_state(self, station, newstate):
        sql = "SELECT station.setmystate('{}', '{}', NULL)".format(newstate, station)
        print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            print(r)
        return None

    def get_setup_variable(self, skey):
        sql = "SELECT * FROM admin.get_setupvar('{}')".format(skey)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None

    def get_setup_variable(self, skey , default):
        sql = "SELECT * FROM admin.get_setupvar('{}')".format(skey)
        # print('>>>' + sql)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return default

    def setDevelStationState(self, station, newState):
        sql = "UPDATE station.station_state SET state = {} WHERE hostname={}".format(newState, station)
        try:
            res = self.__sqlObject.db_execute_query(sql)
            # print(res)
            return res[0][0]
        except Exception as err:
            r = find_between(str(err), '#', '#')
            # print(r)
        return None