import sqlite3
from sqlite3 import Error
import datetime as DT
import numpy as np


#create ConnectionToDB
def create_connection(db):
    try:
        conn = sqlite3.connect(db)
        return conn
    except Error as e:
        print(e)
    return None

def write_result_to_DB(category, value, conn):
    timestamp = DT.datetime.utcnow()
    cur = conn.cursor()
    query = "Insert into Results('Category', 'timestamp', 'Value') VALUES ('" + category + "','" + str(timestamp) + "','" + str(value) + "' )"
    #print(query)
    cur.execute(query)
    conn.commit()


def get_result_from_db(query,target_array, conn):
    cur = conn.cursor()
    cur.execute(query)
    print(query)
    dbstring = cur.fetchone()
    if dbstring is None:
        print("NO ENTRY")
    else:
        dbstring=dbstring[0]
        new_list = dbstring.split(';')
        #print(new_list)
        new_arr = np.asarray(new_list)
        #print("arr")
        #print(new_arr)
        for entry in new_arr:
            value_string = entry.strip('()')
            value_string = str(value_string).split(', ')
            #print(value_string[0])
            #print(value_string[1])
            target_array = np.append(target_array, np.array([(int(value_string[0]), float(value_string[1]))], dtype=target_array.dtype))
        #print(result)
    return target_array


def get_third_quartile(conn):
    cur = conn.cursor()
    t_quartile = 0
    query = "select value from results where category ='third_quartile' order by timestamp desc limit 1"
    cur.execute(query)
    dbstring = cur.fetchone()
    if dbstring is None:
        print("NO ENTRY")
    else:
        t_quartile = dbstring[0]

    return t_quartile