import operator

def boolstr_to_int(string):
    if isinstance(string, str):
        if string.lower() == 'true':
            return 1
        elif string.lower() == 'false':
            return 0
    return string

def empty_to_none(string):
    if string == '':
        return None
    return string

def try_to_number(value):
    try:
        value = int(value)
    except ValueError:
        try:
            value = float(value)
        except ValueError:
            pass
    return value


COMPARE = {'>=': operator.ge, '>': operator.gt, '==': operator.eq,
           '!=': operator.ne, '<': operator.lt, '<=': operator.le}

COMPARE_ALIAS = {'=>': '>=', '=': '==', '<>': '!=', '=<': '<='}


UPDATE_ONLINE = 'UPDATE tblRoom SET ONLINE=ONLINE-1 WHERE ONLINE>0'

GET_CAMPUS_ID = 'SELECT id FROM tblCampus WHERE NAME=?'
INSERT_CAMPUS = 'INSERT INTO tblCampus (NAME, ALTNAME) VALUES (?, ?)'

GET_BUILDING_ID = 'SELECT id FROM tblBuilding WHERE idCampus=? AND NAME=?'
INSERT_BUILDING = '''
    INSERT INTO tblBuilding (idCampus, NAME, ALTNAME) VALUES (?, ?, ?)
'''

GET_ROOM_ID = 'SELECT id FROM tblRoom WHERE idBuilding=? AND NAME=?'
INSERT_ROOM = '''
    INSERT INTO tblRoom (idBuilding, NAME, ALTNAME, LASTUPDATED)
    VALUES (?, ?, ?, CURRENT_TIMESTAMP)
'''
UPDATE_ROOM = '''
    UPDATE tblRoom SET ONLINE=5, LASTUPDATED=CURRENT_TIMESTAMP
    WHERE idBuilding=? AND NAME=?
'''

GET_DEVICE_ID = 'SELECT id FROM tblDevice WHERE idRoom=? AND NAME=?'
INSERT_DEVICE = '''
    INSERT INTO tblDevice (idRoom, NAME, ALTNAME, LASTUPDATED)
    VALUES (?, ?, ?, CURRENT_TIMESTAMP)
'''
UPDATE_DEVICE = '''
    UPDATE tblDevice SET LASTUPDATED=CURRENT_TIMESTAMP
    WHERE idRoom=? AND NAME=?
'''

GET_PROP_ID = 'SELECT id FROM tblProperties WHERE idDevice=? AND NAME=?'
GET_PROP_VALUE_TYPE = '''
    SELECT VALUE, TYPE FROM tblProperties
    WHERE idDevice=? AND NAME=?
'''
INSERT_PROP = '''
    INSERT INTO tblProperties (idDevice, NAME, ALTNAME, VALUE)
    VALUES (?, ?, ?, ?)
'''
INSERT_EXT_PROP = '''
    INSERT INTO tblProperties (idDevice, NAME, ALTNAME, VALUE, {cols})
    VALUES (?, ?, ?, ?, {vals})
'''
UPDATE_PROP = '''
    UPDATE tblProperties SET VALUE=?
    WHERE idDevice=? AND NAME=? AND (TYPE!='SOURCE' AND TYPE!='SUM');
'''
UPDATE_SOURCE_SUM = '''
    UPDATE tblProperties SET VALUE=COALESCE(VALUE, 0) + CAST(? AS DECIMAL)
    WHERE idDevice=? AND NAME=? AND (TYPE='SOURCE' OR TYPE='SUM');
'''
EXT_PROP = (
    'TYPE', 'PREFIX', 'SUFFIX', 'ALERTCONFIG', 'ALERTVALUE',
    'ALERTTYPE', 'ALLOWRESET', 'CAPITALIZE'
)

GET_CMDPROC_ID = 'SELECT id FROM tblCmdProcs WHERE NAME=?'
INSERT_CMDPROC = 'INSERT INTO tblCmdProcs (NAME, ALTNAME) VALUES (?, ?)'
UPDATE_ROOM_CMDPROC = '''
    UPDATE tblRoom SET idCmdProcs=?, ARGS=?, ALTARGS=? WHERE id=?
'''

GET_BUTTON_ID = '''
    SELECT id FROM tblButtons WHERE idRoom=? AND GROUPNAME=? AND BTNNAME=?
'''
INSERT_BUTTON = '''
    INSERT INTO tblButtons (idRoom, GROUPNAME, ALTGROUPNAME, BTNNAME,
        ALTBTNNAME, COMMAND, ALTCOMMAND)
    VALUES (?, ?, ?, ?, ?, ?, ?)   
'''

GET_ALERT = '''
    SELECT VALUE, ALERTCONFIG, ALERTVALUE, ALERTSTATUS, ALLOWRESET
    FROM tblProperties WHERE id=?
'''
RESET_ALERT = '''
    UPDATE tblProperties SET ALERTSTATUS=0 WHERE ALLOWRESET=1 and id=?
'''
UPDATE_ALERT = 'UPDATE tblProperties SET ALERTSTATUS=1 WHERE id=?'
