import sqlite3

DATABASE="my_db.db"

def query_weighted_data():
    try:
        con = sqlite3.connect(DATABASE)
        cursor = con.cursor()
        command = '''select distinct network_type,cell_signal_strength_dbm,device_latitude,device_longitude,device_position_accuracy,user_id,timestamp from networkmonitor 
                     where network_type !='WIFI' and device_latitude not NULL'''
        cursor.execute(command)
        result = cursor.fetchone()
        if result == None:
            sys.exit(4)

        weighted_data = ""
        cursor.close()
        con.close()
        return (True, weighted_data)
    except:
        cursor.close()
        con.close()
        return (False, "")


def query_unweighted_data():
    try:
        con = sqlite3.connect(DATABASE)
        cursor = con.cursor()
        command = '''select distinct network_type,cell_signal_strength_dbm,device_latitude,device_longitude,device_position_accuracy,user_id,timestamp from networkmonitor 
                     where network_type !='WIFI' and device_latitude not NULL'''
        cursor.execute(command)
        result = cursor.fetchall()
        if result == None:
            sys.exit(4)

        data = [result[2], result[3], result[1]]
                    
        cursor.close()
        con.close()
        return (True, "succes",data)
    except:
        cursor.close()
        con.close()
        return (False, "database error", "")

    
def signal_strength_interval(data):
    if data < -100:
        return 3
    elif data < -90:
        return 2
    elif data < -70:
        return 1
    else:
        return 0

def query_number_of_data_points():
    try:
        con = sqlite3.connect(DATABASE)
        cursor = con.cursor()
        command = '''select distinct network_type,cell_signal_strength_dbm,device_latitude,device_longitude,device_position_accuracy,user_id,timestamp from networkmonitor 
                     where network_type !='WIFI' and device_latitude not NULL'''
        cursor.execute(command)
        result = cursor.fetchone()
        if result == None:
            sys.exit(4)
        res1 = [0,0,0,0]
        res2 = [0,0,0,0]
        while result != None:
            if result[0] == "MOBILE/LTE":
                res2[ signal_strength_interval(result[1])] += 1
            else:
                res1[ signal_strength_interval(result[1])] += 1
            result = cursor.fetchone()
        cursor.close()
        con.close()
        return (True, res1, res2)
    except:
        con.close()
        return (False, "","")