from bottle import route, redirect, request, jinja2_view, static_file, Jinja2Template, url, abort
import math
import collections
import os
import bottle
import psycopg2
import json
import functools

MOVED_TEMP = 307
MOVED_PERM = 301

Jinja2Template.defaults = {"url": url, "site_name": "PØLL"}
Jinja2Template.settings = {"autoescape": True}
view = functools.partial(jinja2_view, template_lookup=["templates"])

@route('/')
@view("index.html")
def index():
    return {"title": "PØLL - Create new pøll"}    

@route('/create', method="POST")
def create_poll():
    title = request.forms.title
    questions = [(int(k[1:]),v) for k,v in request.forms.decode().items() if (k[0] == 'q') and v]
    questions.sort(key=lambda x: x[0])
    questions = [x[1] for x in questions]
    print(title, questions)

    dbconn = get_database()

    # step one: create poll row

    cursor = dbconn.cursor()
    cursor.execute("INSERT INTO polls (title, flags) VALUES (%s, %s) RETURNING id;", (title, 0))
    poll_id = cursor.fetchone()[0]
    print("Created poll with poll_id %d" % poll_id)

    # step two: create questions

    for question in questions:
        cursor.execute("INSERT INTO questions (poll_id, text) VALUES (%s, %s);", (poll_id, question));

    # step three: commit the changes

    dbconn.commit()
    cursor.close()

    return redirect("/%d" % (poll_id), code=301)

@route('/vote', method="POST")
def vote():
    poll_id = int(request.forms.poll_id)
    question_id = request.forms.question
    ip = request.remote_addr

    dbconn = get_database()

    cursor = dbconn.cursor()
    cursor.execute("SELECT ip FROM responses WHERE poll_id = %s", (poll_id,))

    ips = [item for sublist in cursor.fetchall() for item in sublist]

    if (ip in ips):
        # this piece of trash already voted
        cursor.close()
        return "je hebt al gestemd, klootviool"

    cursor.execute("INSERT INTO responses (poll_id, question_id, ip) VALUES (%s, %s, %s)",
            (poll_id, question_id, ip))

    cursor.close()
    dbconn.commit()

    return redirect("/%d/r" % (poll_id))

@route('/favicon.ico')
def favicon():
    return static('favicon.jpg')

@route('/static/:path#.+#', name='static')
def static(path):
    return static_file(path, root="static")


@route('/<poll_id:re:\d+/?>')
@view('poll.html')
def get_poll(poll_id):
    dbconn = get_database()
    poll_id = poll_id.strip("/")
    cursor = dbconn.cursor()
    print("Viewing poll #%s" % (poll_id))
    cursor.execute("""SELECT polls.title, array_agg(questions.id), array_agg(questions.text)
                      FROM polls, questions WHERE polls.id = %s and questions.poll_id = polls.id
                      GROUP BY polls.title""", (poll_id,))
    row = cursor.fetchone()
    if row is None:
        redirect("/", code=MOVED_TEMP)

    cursor.close()
    dbconn.commit()

    title = row[0]
    questions = dict(zip(*row[1:]))

    return {"poll_id": poll_id, "title": title, "questions": questions}


@route('/<poll_id>/r')
@view('results.html')
def get_poll_results(poll_id):
    dbconn = get_database()
    try:
        int(poll_id)
    except ValueError:
        redirect("/", code=MOVED_TEMP)
    cursor = dbconn.cursor()
    print("Viewing results for poll #%s" % (poll_id))
    cursor.execute("""SELECT polls.id, polls.title, r.*, q.* FROM polls NATURAL JOIN LATERAL
                     (SELECT array_agg(r.question_id) AS res FROM responses r WHERE r.poll_id = polls.id)
                     AS r NATURAL JOIN LATERAL (SELECT array_agg(id) AS qids, array_agg(text)
                     AS questions FROM questions q WHERE q.poll_id = polls.id) AS q
                     WHERE polls.id = %s;""", (poll_id,))
    row = cursor.fetchone()
    print(row)

    pid, title, responses, qids, qs = row

    if responses == None:
        total_votes = 0
        resp_dict = dict()
    else:
       total_votes = len(responses)
       resp_dict = {i:round(100*(responses.count(i)/total_votes)) for i in set(responses)}

    q_dict = dict(zip(qids, qs))

    return {"pid": pid, "title": title, "q_dict": collections.OrderedDict(sorted(q_dict.items(), key=lambda x: resp_dict.get(x[0], 0), reverse=True)), "resp_dict": resp_dict, "total_votes": total_votes}

# read configuration file
config = json.load(open("config.json", "r"))

def get_database():
    # set up psycopg2
    dbconn = psycopg2.connect(database=config["database"], user=config["user"],
        password=config["password"], host=config["host"])
    return dbconn


application = app = bottle.default_app()
app.catchall = False

if __name__ == '__main__':
    app.run(host="0.0.0.0", port="80", debug=True, reloader=True, server="cherrypy")