from datetime import datetime, date, timedelta
import pymysql.cursors

cnx = pymysql.connect(user="Mico",
                      password="1234",
                      host="35.189.217.208",
                      database="Grundfoss")
cursor = cnx.cursor()

def weekly_avg():

    avg = None
    cold_data = []
    warm_data = []
    water_data = []
    cold_sum = 0
    warm_sum = 0
    water_sum = 0

    for i in range(7):
        cold_query = ('SELECT SUM(Cold_water) FROM Consumption WHERE DATE_ADD(CURDATE(), INTERVAL - %s DAY) = Date')
        cursor.execute(cold_query, (i+1))
        cold_data.append(cursor.fetchall())

    for i in range(7):
        warm_query = ('SELECT SUM(Warm_water) FROM Consumption WHERE DATE_ADD(CURDATE(), INTERVAL - %s DAY) = Date')
        cursor.execute(warm_query, (i+1))
        warm_data.append(cursor.fetchall())

    for i in range(7):
        water_query = ('SELECT SUM(Water) FROM Consumption WHERE DATE_ADD(CURDATE(), INTERVAL - %s DAY) = Date')
        cursor.execute(water_query, (i+1))
        water_data.append(cursor.fetchall())

    for item in cold_data:
        cold_sum += item

    for item in warm_data:
        warm_sum += item

    for item in water_data:
        water_sum += item

    avg_cold = cold_sum/7

    avg_warm = warm_sum/7

    avg_water = water_sum/7

    return avg_cold, avg_warm, avg_water

def min_and_max():

    today = date.today()
    past_year_date = today - timedelta(days=365)

    cold_query = ('SELECT SUM(Cold_water) FROM Consumption WHERE Date >= %s ORDER BY Date GROUP BY dorm_id')
    warm_query = ('SELECT SUM(Warm_water) FROM Consumption WHERE Date >= %s ORDER BY Date GROUP BY dorm_id')
    water_query = ('SELECT SUM(Water) FROM Consumption WHERE Date >= %s ORDER BY Date GROUP BY dorm_id')

    cursor.execute(cold_query, (past_year_date))
    cold_data = cursor.fetchall()

    cursor.execute(warm_query, (past_year_date))
    warm_data = cursor.fetchall()

    cursor.execute(water_query, (past_year_date))
    water_data = cursor.fetchall()

    cold_min = min(cold_data)
    cold_max = max(cold_data)

    warm_min = min(warm_data)
    warm_max = max(warm_data)

    water_min = min(water_data)
    water_max = max(water_data)

    return cold_min, cold_max, warm_min, warm_max, water_min, water_max

def yesterday_comp():

    today = date.today()
    yesterday = today - timedelta(days=1)


    cold_query = ('SELECT SUM(RealColdFlowGFsensor) FROM test WHERE DateCreated LIKE %s')
    warm_query = ('SELECT SUM(RealHotFlowGF) FROM test WHERE DateCreated LIKE %s')
#    water_query = ('SELECT SUM(Water) FROM Consumption WHERE Date = %s GROUP BY dorm_id')

    current_cold = ('SELECT SUM(RealColdFlowGFsensor) FROM test WHERE DateCreated = CURDATE()')
    current_warm = ('SELECT SUM(RealHotFlowGF) FROM test WHERE DateCreated = CURDATE()')
    current_water = ('SELECT SUM(Water) FROM Consumption WHERE Date = CURDATE() GROUP BY dorm_id')

    cursor.execute(cold_query, ('%' + yesterday + '%'))
    cold_data = cursor.fetchall()

    cursor.execute(warm_query, (yesterday))
    warm_data = cursor.fetchall()

    cursor.execute(water_query, (yesterday))
    water_data = cursor.fetchall()

    cursor.execute(current_cold)
    cold_today = cursor.fetchall()

    cursor.execute(current_warm)
    warm_today = cursor.fetchall()

    cursor.execute(current_water)
    water_today = cursor.fetchall()

    diff_cold = cold_data - cold_today
    diff_warm = warm_data - warm_today
    diff_water = water_data - water_today