import sqlite3
import plotly.plotly as py
from plotly.graph_objs import *

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

try:
    con = sqlite3.connect("day1.db")
    cursor = con.cursor()
    command = "select distinct network_type,cell_signal_strength_dbm,device_latitude,device_longitude,device_position_accuracy,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()

    con.close()
except:
    con.close()


trace1 = Bar(
    x=['Above -70 dBm', '-70 to -90 dBm', '-90 to -100 dBm', 'Below -100 dBm'],
    y=res1,
    name='3G'
)

lte = Bar(
    x=['Above -70 dBm', '-70 to -90 dBm', '-90 to -100 dBm', 'Below -100 dBm'],
    y=res2,
    name='LTE'
)

data = Data([trace1, lte])
layout = Layout(
    barmode='stack'
)
fig = Figure(data=data, layout=layout)
plot_url = py.plot(fig, filename='day1-stacked-bar')