import pymysql
import datetime

def insert(outsideTemp, insideTemp, targetTemp, fanState):
    sql = "INSERT INTO FANS ( `time`, `outside_temp`, `inside_temp`, `target_temp`, `fan_state`) "
    sql += "VALUES ( \"{0}\", {1}, {2}, {3}, {4})".format(datetime.datetime.now(), outsideTemp, insideTemp, targetTemp, fanState)
    try:
        connection = pymysql.connect(host='localhost', db='fans')
        #connection = pymysql.connect(host='localhost', user='root', db='fans', password='c0staRic4')
        #connection = pymysql.connect(host='localhost', user='pi', db='fans')
        cursor = connection.cursor()
        #print(sql)
        cursor.execute(sql)
        connection.commit()
    finally:
        connection.close()

def select_today():
    sql = "SELECT * FROM FANS WHERE DATE(time)=CURRENT_DATE()"
    return select_sql(sql)

def select_last():
    sql="select * from FANS order by time desc limit 1"
    return select_sql(sql)


def select_sql(sql):
    try:
        connection = pymysql.connect(host='localhost', db='fans')
        cursor = connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
    finally:
        connection.close()

    return result


'''
CREATE USER 'pi'@'localhost';
GRANT ALL on *.* to 'pi'@'localhost' WITH GRANT OPTION;

create database fans;
use fans;

CREATE TABLE `FANS` (
	`time` TIMESTAMP NOT NULL,
	`outside_temp` FLOAT NOT NULL,
	`inside_temp` FLOAT NOT NULL,
	`target_temp` FLOAT NOT NULL,
	`fan_state` BOOLEAN NOT NULL,
	PRIMARY KEY (`time`)
);

INSERT INTO fans ( `time`, `outside_temp`, `inside_temp`, `fan_state`)
VALUES ( datetime.datetime.now(), 56.7, 74.0, TRUE )
'''