import os
import sqlite3

from genshi.template import TemplateLoader
from genshi.template.base import Context

BASE_PATH = '/home/dsorber/bfcs'
DB_PATH = os.path.join(BASE_PATH, 'beer_temps.db')
TEMPLATE_PATH = os.path.join(BASE_PATH, 'templates')

# It accepts two arguments:
# environ points to a dictionary containing CGI like environment variables
# which is filled by the server for each received request from the client
# start_response is a callback function supplied by the server
# which will be used to send the HTTP status and headers to the server

def application(environ, start_response):

    loader = TemplateLoader(TEMPLATE_PATH, auto_reload=True)

    if not environ['PATH_INFO']:
        # Load the index template
        template = loader.load('index.html')

        # Create the response args
        response_args = {'title': 'Beer Fermentation Control System'}

        # Open up the connection to the database, execute the query,
        # grab the results, then close the DB connection.
        db_conn = sqlite3.connect(DB_PATH)
        db = db_conn.cursor()
        db.execute("select strftime('%m/%d/%Y', date(ts, 'unixepoch')), "
                   "       count(ts), avg(temp1), max(temp1), min(temp1),"
                   "       avg(temp2), max(temp2), min(temp2), avg(temp3),"
                   "       max(temp3), min(temp3), avg(temp4), max(temp4),"
                   "       min(temp4)"
                   "from temp_data "
                   "group by date(ts, 'unixepoch')")
        response_args['results'] = db.fetchall()
        db_conn.close()

        # Generate the stream and then render it to HTML
        stream = template.generate(**response_args)
        response_body = stream.render('html', doctype='html', encoding='utf-8')

        # Set the appropriate response headers
        response_headers = [('Content-Type', 'text/html; charset=utf-8'),
                            ('Content-Length', str(len(response_body)))]

    elif environ['PATH_INFO'].startswith('/day/'):
        # Load the day template
        template = loader.load('day.html')

        # Parse the date out of the URL (TODO: validate all this)
        day = environ['PATH_INFO'][5:7]
        month = environ['PATH_INFO'][7:9]
        year = environ['PATH_INFO'][9:13]
        date = '%s/%s/%s' % (month, day, year)

        # Create the response args
        response_args = {'title': 'Sensor Readings for %s' % date}

        # Open up the connection to the database, execute the query,
        # grab the results, then close the DB connection.
        db_conn = sqlite3.connect(DB_PATH)
        db = db_conn.cursor()
        db.execute("select strftime('%%H:%%M:%%S', ts, 'unixepoch'), "
                   "       temp1, temp2, temp3, temp4, relay_status "
                   "from temp_data "
                   "where date(ts, 'unixepoch') is date('%s-%s-%s')"
                   "order by ts desc" % (year, month, day))
        response_args['results'] = db.fetchall()

        # Chart query
        db.execute("select strftime('%%H:%%M:%%S', ts, 'unixepoch'), temp1, "
                   "       temp2, temp3, temp4 from ( "
                   "select ts, temp1, temp2, temp3, temp4 "
                   "from temp_data "
                   "where date(ts, 'unixepoch') is date('%s-%s-%s')"
                   "order by ts desc limit 60) order by ts" 
                   % (year, month, day))
        chart_results = db.fetchall()
        # This is a bit of hack to get around a javascript issue in
        # the template
        response_args['chart_results'] = chart_results[:-1]
        response_args['chart_last_row'] = chart_results[-1]

        db_conn.close()

        # Generate the stream and then render it to HTML
        stream = template.generate(**response_args)
        response_body = stream.render('html', doctype='html', encoding='utf-8')

        # Set the appropriate response headers
        response_headers = [('Content-Type', 'text/html; charset=utf-8'),
                            ('Content-Length', str(len(response_body)))]
    else:
        # User supplied an invalid URL
        response_body = 'Invalid URL "%s"' % environ['PATH_INFO']
        response_headers = [('Content-Type', 'text/plain'),
                            ('Content-Length', str(len(response_body)))]

    # HTTP response code and message
    status = '200 OK'

    # Send the status and response headers to the server
    start_response(status, response_headers)

    # Return the response body. (Notice it is wrapped in a list although it
    # could be any iterable.)
    return [response_body]