#!/usr/bin/env python
# -*- coding: utf-8 -*-

from dbsettings import *

cursor = db_conn.cursor();

def year_minmaxavg(year):
    r = {}
    
    cursor.execute("SELECT time, u_min FROM longtime WHERE YEAR(time) = %d ORDER BY u_min ASC LIMIT 1" % year)
    r['min'] = cursor.fetchall()[0]
    
    cursor.execute("SELECT time, u_max FROM longtime WHERE YEAR(time) = %d ORDER BY u_max DESC LIMIT 1" % year)
    r['max'] = cursor.fetchall()[0]
    
    cursor.execute("SELECT AVG(u_avg) FROM longtime WHERE YEAR(time) = %d" % year)
    r['avg'] = cursor.fetchall()[0][0]
    
    return r

def year_hellepv(year):
    r = {}
    
    cursor.execute("SELECT COUNT(time) FROM longtime WHERE YEAR(time) = %d AND u_max >= 25.1" % year)
    r['hellepv'] = cursor.fetchall()[0][0]
    
    cursor.execute("SELECT time, u_avg FROM longtime WHERE YEAR(time) = %d ORDER BY u_avg DESC LIMIT 1" % year)
    r['lampiminpv'] = cursor.fetchall()[0]
    
    return r


def year_hellekausi(year):
    r = {}
    cursor.execute("SELECT time FROM longtime WHERE YEAR(time) = %d AND u_max >= 25.1 ORDER BY time" % year)
    
    count = 0
    start = None
    prev = None
    counts = []
    
    for time, in cursor.fetchall():
        if prev is None:
            start = time
            prev = time
            continue
        
        count += 1
        if (time - prev).days != 1:
            counts.append((count, start, prev))
            start = time
            count = 0
        prev = time
    
    if prev is not None:
        counts.append((count + 1, start, prev))
    
    if counts:
        r['hellekausi'] = max(counts, key = lambda c: c[0])
    else:
        r['hellekausi'] = None
    return r

def year_pakkaset(year):
    r = {}
    
    cursor.execute("SELECT MAX(time) FROM longtime WHERE YEAR(time) = %d AND MONTH(time) < 8 AND u_max <= 0" % year)
    r['pvpakkanenloppu'] = cursor.fetchall()[0][0]
    
    cursor.execute("SELECT MIN(time) FROM longtime WHERE YEAR(time) = %d AND MONTH(time) >= 8 AND u_max <= 0" % year)
    r['pvpakkanenalku'] = cursor.fetchall()[0][0]
    
    cursor.execute("SELECT MAX(time) FROM longtime WHERE YEAR(time) = %d AND MONTH(time) < 8 AND u_min < 0" % year)
    r['yopakkanenloppu'] = cursor.fetchall()[0][0]
    
    cursor.execute("SELECT MIN(time) FROM longtime WHERE YEAR(time) = %d AND MONTH(time) >= 8 AND u_min < 0" % year)
    r['yopakkanenalku'] = cursor.fetchall()[0][0]
    
    return r

def year_lampyo(year):
    r = {}
    
    cursor.execute("SELECT time, u_min FROM longtime WHERE YEAR(time) = %d ORDER BY u_min DESC LIMIT 1" % year)
    r['lampiminyo'] = cursor.fetchall()[0]
    
    return r

def getfulldata(startyear, endyear):
    funcs = [year_minmaxavg, year_hellepv, year_hellekausi, year_pakkaset, year_lampyo]
    data = {}
    
    for year in range(startyear, endyear + 1):
        data[year] = {}
        for func in funcs:
            data[year].update(func(year))
    
    return data

def sortlist(lst, *args, **kwargs):
    '''Sort list and return it, without modifying the original'''
    lst = lst[:]
    lst.sort(*args, **kwargs)
    return lst

def table_formatrows(data):
    dateformat = "%d.%m"
    
    format_d = lambda v: '<td>&nbsp;</td><td>%s</td>' % v.strftime(dateformat)
    format_t = lambda v: '<td>%0.1f°C</td><td>&nbsp;</td>' % v
    format_td = lambda v: '<td>%0.1f°C</td><td>%s</td>' % (v[1], v[0].strftime(dateformat))
    format_c = lambda v: '<td>%d pv</td><td>&nbsp;</td>' % v
    format_cdd = lambda v: '<td>%d pv</td><td>%s - %s</td>' % (v[0], v[1].strftime(dateformat), v[2].strftime(dateformat))
    
    rowtypes = [ # keyname, description, formatter
        ('min', 'Alin lämpötila', format_td),
        ('max', 'Korkein lämpötila', format_td),
        ('avg', 'Keskilämpötila', format_t),
        (None, None, None),
        ('lampiminpv', 'Korkein päivän keskilämpötila', format_td),
        ('lampiminyo', 'Korkein yölämpötila', format_td),
        ('hellekausi', 'Pisin yhtenäinen hellejakso', format_cdd),
        ('hellepv', 'Yhteensä hellepäiviä', format_c),
        (None, None, None),
        ('pvpakkanenloppu', 'Kevään viimeinen pakkaspäivä', format_d),
        ('yopakkanenloppu', 'Kevään viimeiset yöpakkaset', format_d),
        ('pvpakkanenalku', 'Syksyn ensimmäinen pakkaspäivä', format_d),
        ('yopakkanenalku', 'Syksyn ensimmäiset yöpakkaset', format_d)
        ]
    
    r = ""
    separate = False
    idx = 0
    for keyname, description, formatter in rowtypes:
        if keyname is None:
            separate = True
            continue
        
        idx += 1
        styles = []
        if separate:
            separate = False
            styles.append("separatebefore")
        
        if idx % 2:
            styles.append("odd")
        
        if styles:
            r += "<tr class=\"%s\"><td>%s</td>" % (' '.join(styles), description)
        else:
            r += "<tr><td>%s</td>" % description
        
        for year in sortlist(data.keys()):
            value = data[year][keyname]
            if value is not None:
                r += formatter(value)
            else:
                r += "<td>&nbsp;</td><td>&nbsp;</td>"
        
        r += "</tr>\n"
    return r

def table_formatdata(data):
    r = ""
    r += "<table>\n"
    
    endyear = sortlist(data.keys())[-1]
    
    colheaders = ["<th>2005</th><th>(pvm)</th>"]
    colheaders += ["<th colspan=\"2\">%d</th>" % year for year in range(2005 + 1, endyear)]
    colheaders.append("<th colspan=\"2\">%d (tähän mennessä)</th>" % endyear)
    
    r += "<tr><th>&nbsp;</th>" + ''.join(colheaders) + "</tr>\n"
    r += table_formatrows(data)
    r += "</table>"
    return r

def sections_formatsection(yeardict):
    dateformat = "%d.%m"
    
    format_d = lambda v: '%s' % v.strftime(dateformat)
    format_t = lambda v: '%0.1f°C' % v
    format_td = lambda v: '%0.1f°C (%s)' % (v[1], v[0].strftime(dateformat))
    format_c = lambda v: '%d pv' % v
    format_cdd = lambda v: '%d pv (%s - %s)' % (v[0], v[1].strftime(dateformat), v[2].strftime(dateformat))
    
    rowtypes = [ # keyname, description, formatter
        ('min', 'Alin lämpötila', format_td),
        ('max', 'Korkein lämpötila', format_td),
        ('avg', 'Keskilämpötila', format_t),
        (None, None, None),
        ('lampiminpv', 'Korkein päivän keskilämpötila', format_td),
        ('lampiminyo', 'Korkein yölämpötila', format_td),
        ('hellekausi', 'Pisin yhtenäinen hellejakso', format_cdd),
        ('hellepv', 'Yhteensä hellepäiviä', format_c),
        (None, None, None),
        ('pvpakkanenloppu', 'Kevään viimeinen pakkaspäivä', format_d),
        ('yopakkanenloppu', 'Kevään viimeiset yöpakkaset', format_d),
        ('pvpakkanenalku', 'Syksyn ensimmäinen pakkaspäivä', format_d),
        ('yopakkanenalku', 'Syksyn ensimmäiset yöpakkaset', format_d)
        ]

    
    r = "<ul>\n"
    for keyname, description, formatter in rowtypes:
        if keyname is None:
            r += "</ul><ul>"
            continue
        
        if yeardict[keyname] is None:
            value = "Ei tietoa"
        else:
            value = formatter(yeardict[keyname])
        
        r += "    <li><strong>%s:</strong> %s</li>\n" % (description, value)
    r += "</ul>\n"
    
    return r

def sections_formatdata(data):
    r = ""
    endyear = sortlist(data.keys())[-1]
    for year in sortlist(data.keys()):
        if year == endyear:
            r += "<h3>%d (tähän mennessä)</h3>\n" % year
        else:
            r += "<h3>%d</h3>\n" % year
        
        r += sections_formatsection(data[year])
    
    return r

def tablesections_formatrows(yeardict):
    dateformat = "%d.%m"
    
    format_d = lambda v: '<td>&nbsp;</td><td>%s</td>' % v.strftime(dateformat)
    format_t = lambda v: '<td>%0.1f°C</td><td>&nbsp;</td>' % v
    format_td = lambda v: '<td>%0.1f°C</td><td>%s</td>' % (v[1], v[0].strftime(dateformat))
    format_c = lambda v: '<td>%d pv</td><td>&nbsp;</td>' % v
    format_cdd = lambda v: '<td>%d pv</td><td>%s - %s</td>' % (v[0], v[1].strftime(dateformat), v[2].strftime(dateformat))
    
    rowtypes = [ # keyname, description, formatter
        ('min', 'Alin lämpötila', format_td),
        ('max', 'Korkein lämpötila', format_td),
        ('avg', 'Keskilämpötila', format_t),
        (None, None, None),
        ('lampiminpv', 'Korkein päivän keskilämpötila', format_td),
        ('lampiminyo', 'Korkein yölämpötila', format_td),
        ('hellekausi', 'Pisin yhtenäinen hellejakso', format_cdd),
        ('hellepv', 'Yhteensä hellepäiviä', format_c),
        (None, None, None),
        ('pvpakkanenloppu', 'Kevään viimeinen pakkaspäivä', format_d),
        ('yopakkanenloppu', 'Kevään viimeiset yöpakkaset', format_d),
        ('pvpakkanenalku', 'Syksyn ensimmäinen pakkaspäivä', format_d),
        ('yopakkanenalku', 'Syksyn ensimmäiset yöpakkaset', format_d)
        ]
    
    r = ""
    separate = False
    idx = 0
    for keyname, description, formatter in rowtypes:
        if keyname is None:
            separate = True
            continue
        
        idx += 1
        styles = []
        if separate:
            separate = False
            styles.append("separatebefore")
        
        if idx % 2:
            styles.append("odd")
        
        if styles:
            r += "<tr class=\"%s\"><td>%s</td>" % (' '.join(styles), description)
        else:
            r += "<tr><td>%s</td>" % description
        
        value = yeardict[keyname]
        if value is not None:
            r += formatter(value)
        else:
            r += "<td>&nbsp;</td><td>&nbsp;</td>"
        
        r += "</tr>\n"
    return r

def tablesections_formatdata(data):
    r = ""
    endyear = sortlist(data.keys())[-1]
    for year in sortlist(data.keys()):
        if year == endyear:
            r += "<h3>%d (tähän mennessä)</h3>\n" % year
        else:
            r += "<h3>%d</h3>\n" % year
        
        r += "<table>\n"
        r += "<tr><th>Suure</th><th>Arvo</th><th>Päivämäärä</th></tr>\n"
        r += tablesections_formatrows(data[year])
        r += "</table>\n"
    
    return r


if __name__ == '__main__':
    import datetime
    endyear = datetime.datetime.now().year

    data = getfulldata(2005, endyear)
    print tablesections_formatdata(data)