from flask import Flask
from flask import render_template
from flask import request

import os
from urllib.parse import urlparse
import json

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

app = Flask(__name__)

# connection string and db connection initialization
POSTGRESQL_URL = os.environ['POSTGRESQL_URL']
parsed = urlparse(POSTGRESQL_URL)

# Open a connection to the default compose database
conn = psycopg2.connect(
    host=parsed.hostname,
    port=parsed.port,
    user=parsed.username,
    password=parsed.password,
    database='compose')

# Now query it to see if the "grand_tour" database exists
cursor=conn.cursor()
cursor.execute("SELECT COUNT(*) = 0 FROM pg_catalog.pg_database WHERE datname = 'grand_tour'")
not_exists_row = cursor.fetchone()
not_exists = not_exists_row[0]
if not_exists:
    # It doesn't. We get to create it here.
    # We have to tweak the isolation level so we aren't in a transaction
    conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    # Now we get our cursor again
    cursor=conn.cursor()
    # And we create the database
    cursor.execute('CREATE DATABASE grand_tour')

# Whatever happened previously, now we can open a connection to the
# "grand_tour" database and carry on...

conn = psycopg2.connect(
    host=parsed.hostname,
    port=parsed.port,
    user=parsed.username,
    password=parsed.password,
    database='grand_tour')

@app.route('/')
# top-level page display, creates table if it doesn't exist
def serve_page(name=None):
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS words (
		id serial primary key,
		word varchar(256) NOT NULL,
		definition varchar(256) NOT NULL) """)
    return render_template('index.html', name=name)

@app.route('/words', methods=['PUT'])
# triggers on hitting the 'Add' button; inserts word/definition into table
def handle_words(name=None):
    cur = conn.cursor()
    cur.execute("""INSERT INTO words (word, definition)
        VALUES (%s, %s)""",(request.form['word'],request.form['definition']))
    conn.commit()
    return "ECHO: PUT\n"

@app.route('/words', methods=['GET'])
# query for all the rows in the table,\
# makes a dictionary object from the column names and the results,\
# makes json from the dict for display on the page.
def display_find(name=None):
    cur = conn.cursor()
    cur.execute("""SELECT word, definition FROM words""")
    cursor_obj = cur.fetchall()

    labels = [column[0] for column in cur.description]
    results_list = []

    for row in cursor_obj:
        results_list.append(dict(zip(labels, row)))

    return json.dumps(results_list)

if __name__ == "__main__":
    app.run()