/server.py

#!/usr/bin/env python2.7

"""
Q Server
To run locally
    python server.py
Go to http://localhost:8111 in your browser
"""

import os
from sqlalchemy import *
from sqlalchemy.pool import NullPool
from urlparse import urlparse, urljoin
from flask import Flask, request, render_template, g, redirect, Response,flash, url_for

tmpl_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'templates')
app = Flask(__name__, template_folder=tmpl_dir)


# XXX: The URI should be in the format of: 
#
#     postgresql://USER:PASSWORD@<IP_OF_POSTGRE_SQL_SERVER>/postgres

DATABASEURI = "postgresql://krl2130:vt6jr@104.196.175.120:5432/postgres"

engine = create_engine(DATABASEURI)

@app.before_request
def before_request():
  """
  This function is run at the beginning of every web request 
  (every time you enter an address in the web browser).
  We use it to setup a database connection that can be used throughout the request
  The variable g is globally accessible
  """
  try:
    g.conn = engine.connect()
  except:
    print "uh oh, problem connecting to database"
    import traceback; traceback.print_exc()
    g.conn = None

@app.teardown_request
def teardown_request(exception):
  """
  At the end of the web request, this makes sure to close the database connection.
  If you don't the database could run out of memory!
  """
  try:
    g.conn.close()
  except Exception as e:
    pass



def redirect_url(default='index'):
    return request.args.get('next') or \
           request.referrer or \
           url_for(default)
#
# @app.route is a decorator around index() that means:
#   run index() whenever the user tries to access the "/" path using a GET request
#
# If you wanted the user to go to e.g., localhost:8111/foobar/ with POST or GET then you could use
#
#       @app.route("/foobar/", methods=["POST", "GET"])
#
# PROTIP: (the trailing / in the path is important)
# 
# see for routing: http://flask.pocoo.org/docs/0.10/quickstart/#routing
# see for decorators: http://simeonfranklin.com/blog/2012/jul/1/python-decorators-in-12-steps/
#
@app.route('/')
def index():
  """
  request is a special object that Flask provides to access web request information:
  request.method:   "GET" or "POST"
  request.form:     if the browser submitted a form, this contains the data in the form
  request.args:     dictionary of URL arguments e.g., {a:1, b:2} for http://localhost?a=1&b=2
  See its API: http://flask.pocoo.org/docs/0.10/api/#incoming-request-data
  """
  
  top_cmd = "SELECT q.qid as qid, q.content as content, a.aid as aid, a.name as author, w.wid as wid, w.name as work FROM quotes q INNER JOIN authors a ON q.author = a.aid LEFT JOIN works w on q.work = w.wid WHERE q.qid IN (SELECT qid FROM saved_quotes GROUP BY qid ORDER BY count(*) DESC LIMIT 5) and q.num_approvals >= 10"
  top_cursor = g.conn.execute(text(top_cmd))
  top_quotes = []
  for result in top_cursor:
    top_quotes.append((result['qid'],result['content'],result['aid'],result['author'],result['wid'],result['work']))
  top_cursor.close()
  
  recent_cmd = "SELECT q.qid as qid, q.content as content, a.aid as aid, a.name as author, w.wid as wid, w.name as work FROM quotes q INNER JOIN authors a ON q.author = a.aid LEFT JOIN works w on q.work = w.wid WHERE q.num_approvals >= 10 ORDER BY q.added_time ASC LIMIT 5"
  recent_cursor = g.conn.execute(text(recent_cmd))
  recent_quotes = []
  for result in recent_cursor:
    recent_quotes.append((result['qid'],result['content'],result['aid'],result['author'],result['wid'],result['work']))
  recent_cursor.close()
  
  context = dict(top_quotes = top_quotes, recent_quotes = recent_quotes)
  return render_template("q.html", **context)
  # DEBUG: this is debugging code to see what request looks like


  #
  # example of a database query
  #
  # cursor = g.conn.execute("SELECT name FROM test")
  # names = []
  # for result in cursor:
  #   names.append(result['name'])  # can also be accessed using result[0]
  # cursor.close()

  #
  # Flask uses Jinja templates, which is an extension to HTML where you can
  # pass data to a template and dynamically generate HTML based on the data
  # (you can think of it as simple PHP)
  # documentation: https://realpython.com/blog/python/primer-on-jinja-templating/
  #
  # You can see an example template in templates/index.html
  #
  # context are the variables that are passed to the template.
  # for example, "data" key in the context variable defined below will be 
  # accessible as a variable in index.html:
  #
  #     # will print: [u'grace hopper', u'alan turing', u'ada lovelace']
  #     <div>{{data}}</div>
  #     
  #     # creates a <div> tag for each element in data
  #     # will print: 
  #     #
  #     #   <div>grace hopper</div>
  #     #   <div>alan turing</div>
  #     #   <div>ada lovelace</div>
  #     #
  #     {% for n in data %}
  #     <div>{{n}}</div>
  #     {% endfor %}
  #
  # context = dict(data = names)


  #
  # render_template looks in the templates/ folder for files.
  # for example, the below file reads template/index.html
  #
  # return render_template("q.html", **context)

#
# This is an example of a different path.  You can see it at
# 
#     localhost:8111/another
#
# notice that the functio name is another() rather than index()
# the functions for each app.route needs to have different names

# Displays profile page for a user
@app.route('/profile',methods=['GET'])
def profile():
  # Get uid from url 
  uid = request.args.get('uid')
  # Check if username is for user themselves, their friend's profile, or stranger's profile
  self_uid = request.cookies.get('uid')
  self_username = request.cookies.get('username')

  # See if uid in friends or friend_requests or self ( means can't send friend request )
  cmd = 'SELECT EXISTS( SELECT 1 FROM ( (SELECT uid2 AS uid FROM is_friends WHERE uid1 = :self_uid ) UNION  (SELECT uid1 AS uid FROM is_friends WHERE uid2 = :self_uid ) ) AS uids WHERE uid=:uid)'
  cursor = g.conn.execute(text(cmd),self_uid = self_uid,uid=uid)
  exists_friends = []
  for result in cursor:
    exists_friends.append(result[0])
  cursor.close()
  is_friends = exists_friends[0] 

   # See if uid in friends or friend_requests or self ( means can't send friend request )
  cmd = 'SELECT EXISTS( SELECT 1 FROM ( (SELECT sender as uid FROM friend_requests WHERE receiver = :self_uid ) UNION (SELECT receiver as uid FROM friend_requests WHERE sender=:self_uid) ) AS uids WHERE uid=:uid)'
  cursor = g.conn.execute(text(cmd),self_uid = self_uid,uid=uid)
  exists_pending_friends = []
  for result in cursor:
    exists_pending_friends.append(result[0])
  cursor.close()
  is_pending_friends = exists_pending_friends[0] 


  # Show the user's own saved quotes and recommended quotes
  if(uid == self_uid):
    cmd = 'SELECT q.qid as qid, q.content as content, a.aid as aid, a.name as author FROM saved_quotes sq INNER JOIN quotes q ON sq.qid = q.qid INNER JOIN users u ON sq.uid = u.uid INNER JOIN authors a on q.author = a.aid WHERE u.uid = :uid and q.num_approvals >= 10 LIMIT 5'
    cursor = g.conn.execute(text(cmd),uid=uid)
    profile_quotes = []
    for result in cursor:
      profile_quotes.append((result['qid'],result['content'],result['aid'],result['author']))  # can also be accessed using result[0]
    cursor.close()

    # Get all friend requests
    cmd = 'SELECT friend_requests.sender AS sender, users.username AS username, users.name AS name from friend_requests,users WHERE friend_requests.sender = users.uid and friend_requests.receiver = :uid'
    cursor = g.conn.execute(text(cmd),uid=uid)
    friend_requests = []
    for result in cursor:
      friend_requests.append((result['sender'],result['username'],result['name']))  # can also be accessed using result[0]
    cursor.close()
    # Render
    context = dict(username = self_username, uid=uid,profile_quotes = profile_quotes,user_relation='self',friend_requests = friend_requests)
    return render_template("profile.html", **context)
  # Show friend's saved quotes and shared quotes
  elif(is_friends == True):
    # Get friend username
    cmd = 'SELECT username FROM users WHERE users.uid = :uid '
    cursor = g.conn.execute(text(cmd),uid=uid)
    names = []
    for result in cursor:
      names.append(result['username'])
    cursor.close()
    friend_name = names[0]

    # Select friend's saved quotes
    cmd = 'SELECT q.qid as qid, q.content as content, a.aid as aid, a.name as author FROM saved_quotes sq INNER JOIN quotes q ON sq.qid = q.qid INNER JOIN users u ON sq.uid = u.uid INNER JOIN authors a on q.author = a.aid WHERE u.uid = :uid and q.num_approvals >= 10 LIMIT 5'
    cursor = g.conn.execute(text(cmd),uid=uid)
    profile_quotes = []
    for result in cursor:
      profile_quotes.append((result['qid'],result['content'],result['aid'],result['author'])) # can also be accessed using result[0]
    cursor.close()

    # Select friend's shared quotes
    cmd = 'SELECT qid, content, aid, name FROM ((SELECT quotes.qid, content, aid, name FROM saved_quotes,quotes,authors WHERE saved_quotes.uid = :self_uid and saved_quotes.qid = quotes.qid and authors.aid = quotes.author) INTERSECT (SELECT quotes.qid, content, aid, name FROM saved_quotes,quotes,authors WHERE saved_quotes.uid = :uid and saved_quotes.qid = quotes.qid and authors.aid = quotes.author)) AS shared_quotes LIMIT 5'
    cursor = g.conn.execute(text(cmd),self_uid=self_uid,uid=uid)
    shared_quotes = []
    for result in cursor:
      saved_quotes.append((result['qid'],result['content'],result['aid'],result['name'])) # can also be accessed using result[0]
    cursor.close()

    # Render
    context = dict(username = friend_name,self_uid = self_uid,uid=uid, profile_quotes = profile_quotes, shared_quotes = shared_quotes,user_relation='friend')
    return render_template("profile.html", **context)
  
  elif(is_pending_friends):
    # Get friend username
    cmd = 'SELECT username FROM users WHERE users.uid = :uid '
    cursor = g.conn.execute(text(cmd),uid=uid)
    names = []
    for result in cursor:
      names.append(result['username'])
    cursor.close()
    friend_name = names[0]

    # Select friend's saved quotes
    cmd = 'SELECT q.qid as qid, q.content as content, a.aid as aid, a.name as author FROM saved_quotes sq INNER JOIN quotes q ON sq.qid = q.qid INNER JOIN users u ON sq.uid = u.uid INNER JOIN authors a on q.author = a.aid WHERE u.uid = :uid and q.num_approvals >= 10 LIMIT 5'
    cursor = g.conn.execute(text(cmd),uid=uid)
    profile_quotes = []
    for result in cursor:
      profile_quotes.append((result['qid'],result['content'],result['aid'],result['author'])) # can also be accessed using result[0]
    cursor.close()

    # Select friend's shared quotes
    cmd = 'SELECT qid, content, aid, name FROM ((SELECT quotes.qid, content, aid, name FROM saved_quotes,quotes,authors WHERE saved_quotes.uid = :self_uid and saved_quotes.qid = quotes.qid and authors.aid = quotes.author) INTERSECT (SELECT quotes.qid, content, aid, name FROM saved_quotes,quotes,authors WHERE saved_quotes.uid = :uid and saved_quotes.qid = quotes.qid and authors.aid = quotes.author)) AS shared_quotes LIMIT 5'
    cursor = g.conn.execute(text(cmd),self_uid=self_uid,uid=uid)
    shared_quotes = []
    for result in cursor:
      saved_quotes.append((result['qid'],result['content'],result['aid'],result['name'])) # can also be accessed using result[0]
    cursor.close()
    # Render
    context = dict(username = friend_name,self_uid = self_uid,uid=uid, profile_quotes = profile_quotes, shared_quotes = shared_quotes,user_relation='is_pending_friends')
    return render_template("profile.html", **context)
  # Show stranger's saved_quotes and shared quotes
  else:

    # Get stranger username
    cmd = 'SELECT username FROM users WHERE users.uid = :uid '
    cursor = g.conn.execute(text(cmd),uid=uid)
    names = []
    for result in cursor:
      names.append(result['username'])
    cursor.close()
    stranger_name = names[0]

    # Select stranger's saved quotes
    cmd = 'SELECT q.qid as qid, q.content as content, a.aid as aid, a.name as author FROM saved_quotes sq INNER JOIN quotes q ON sq.qid = q.qid INNER JOIN users u ON sq.uid = u.uid INNER JOIN authors a on q.author = a.aid WHERE u.uid = :uid and q.num_approvals >= 10 LIMIT 5'
    cursor = g.conn.execute(text(cmd),uid=uid)
    profile_quotes = []
    for result in cursor:
      profile_quotes.append((result['qid'],result['content'],result['aid'],result['author']))  # can also be accessed using result[0]
    cursor.close()
    # Select stranger's shared quotes
    cmd = 'SELECT qid, content, aid, name FROM ((SELECT quotes.qid, content, aid, name FROM saved_quotes,quotes,authors WHERE saved_quotes.uid = :self_uid and saved_quotes.qid = quotes.qid and authors.aid = quotes.author) INTERSECT (SELECT quotes.qid, content, aid, name FROM saved_quotes,quotes,authors WHERE saved_quotes.uid = :uid and saved_quotes.qid = quotes.qid and authors.aid = quotes.author)) AS shared_quotes LIMIT 5'
    cursor = g.conn.execute(text(cmd),self_uid=self_uid,uid=uid)
    shared_quotes = []
    for result in cursor:
      shared_quotes.append((result['qid'],result['content'],result['aid'],result['author']))  # can also be accessed using result[0]
    cursor.close()

    # Render
    context = dict(username = stranger_name,self_uid = self_uid,uid=uid,profile_quotes = profile_quotes, shared_quotes = shared_quotes,user_relation='stranger')
    return render_template("profile.html", **context)


# Displays page for search results
@app.route('/search',methods=['GET'])
def search():
  query = request.args.get('query').lower()
  quotes_cmd = "SELECT * FROM quotes WHERE LOWER(quotes.content) LIKE LOWER(:query) and quotes.num_approvals >= 10"
  quotes_cursor = g.conn.execute(text(quotes_cmd),query='%' + query + '%')
  quotes = []
  for result in quotes_cursor:
    quotes.append((result['qid'],result['content']))
  quotes_cursor.close()

  authors_cmd = "SELECT * FROM authors WHERE LOWER(authors.name) LIKE LOWER(:query)"
  authors_cursor = g.conn.execute(text(authors_cmd),query='%' + query + '%')
  authors = []
  for result in authors_cursor:
    authors.append((result['aid'],result['name']))
  authors_cursor.close()

  works_cmd = "SELECT * FROM works WHERE LOWER(works.name) LIKE LOWER(:query)"
  works_cursor = g.conn.execute(text(works_cmd),query='%' + query + '%')
  works = []
  for result in works_cursor:
    works.append((result['wid'],result['name']))
  works_cursor.close()

  users_cmd = "SELECT * FROM users WHERE LOWER(users.username) LIKE LOWER(:query) or LOWER(users.name) LIKE LOWER(:query)"
  users_cursor = g.conn.execute(text(users_cmd),query='%' + query + '%')
  users = []
  for result in users_cursor:
    users.append((result['uid'],result['name'],result['username']))
  users_cursor.close()

  tags_cmd = "SELECT * FROM tags WHERE LOWER(tags.name) LIKE LOWER(:query)"
  tags_cursor = g.conn.execute(text(tags_cmd),query='%' + query + '%')
  tags = []
  for result in tags_cursor:
    tags.append(result['name'])
  tags_cursor.close()


  context = dict(quotes = quotes, authors = authors,works = works,query=query,users=users,tags=tags)
  return render_template("search.html", **context)


# Displays page for a quote 
@app.route('/quote',methods=['GET'])
def quote():
  qid = request.args.get('qid')
  cmd = 'SELECT q.content as content, a.aid as aid, a.name as author, w.wid as wid, w.name as work FROM quotes q INNER JOIN authors a ON q.author = a.aid LEFT JOIN works w ON q.work = w.wid WHERE q.qid = (:qid)'
  cursor = g.conn.execute(text(cmd),qid=qid)
  quotes = []
  for result in cursor:
    quotes.append((result['content'],result['aid'],result['author'],result['wid'],result['work']))  
  cursor.close()

  # Get tags
  cmd = 'SELECT tag FROM quote_tags WHERE quote_tags.qid =:qid' 
  cursor = g.conn.execute(text(cmd),qid=qid)
  tags = []
  for result in cursor:
    tags.append(result[0])  
  cursor.close()


  # Check if quote is saved in your quotes
  uid = request.cookies.get('uid')
  cmd= 'SELECT EXISTS(SELECT * FROM saved_quotes WHERE saved_quotes.qid= :qid and saved_quotes.uid = :uid)'
  cursor = g.conn.execute(text(cmd),qid=qid,uid=uid)
  is_saveds = []
  for result in cursor:
    is_saveds.append(result[0])
  cursor.close()
  is_saved= is_saveds[0]

  context = dict(data = quotes[0], tags = tags,is_saved=is_saved,uid=uid,qid=qid)
  return render_template("quote.html", **context)

# Lets user save quotes to profile 
@app.route('/saveQuote',methods=['POST'])
def saveQuote():
  uid=request.form['uid']
  qid=request.form['qid']


  # Check quote not saved
  cmd = 'SELECT EXISTS (SELECT 1 FROM saved_quotes WHERE saved_quotes.qid=:qid and saved_quotes.uid=:uid)'
  cursor = g.conn.execute(text(cmd),qid=qid,uid=uid)
  not_saveds = []
  for result in cursor:
    not_saveds.append(result[0])  
  cursor.close()
  not_saved = not not_saveds[0]
  # Perform appropriate actions
  if(not_saved):
    # Insert 
    cmd='INSERT INTO saved_quotes VALUES( :uid, :qid)'
    cursor = g.conn.execute(text(cmd),uid=uid,qid=qid)
    cursor.close()

    # flash('Successfully saved quote to your profile!')
    return redirect('/profile?uid=' + uid)
  else:
    # flash('Error: quote is already saved to your profile.')
    return redirect('/quote?qid=' + qid)

@app.route('/shareQuote',methods=['POST'])
def shareQuote():
  friend_username = request.form['friend_username']
  uid = request.form['uid']
  qid = request.form['qid']
  mtimestamp = request.form['mtimestamp']

  # Get friend_uid from username
  cmd = 'SELECT uid FROM users WHERE users.username=:friend_username'
  cursor = g.conn.execute(text(cmd),friend_username=friend_username)
  friend_uids = []
  for result in cursor:
    friend_uids.append(result['uid'])
  cursor.close()
  friend_uid = -1
  if(len(friend_uids) == 0):
    redirect('/quote?qid=' + qid)
  else:
    friend_uid = friend_uids[0]


# Insert message into table
  cmd = 'INSERT INTO messages(sender,receiver,qid,content,mtimestamp) VALUES(:sender,:receiver,:qid,:content,:mtimestamp)'
  cursor = g.conn.execute(text(cmd),sender=uid,receiver=friend_uid, qid=qid, content='', mtimestamp = mtimestamp)
  cursor.close()

  # return redirect('/conversation?uid=' + sender + "&friend_uid=" + receiver)
  return redirect('/conversation?uid='+str(uid) + "&friend_uid=" + str(friend_uid))

@app.route('/tag')
def tag():
  name=request.args.get('name')


  # Get authors
  cmd = 'SELECT authors.name AS name,authors.aid AS aid FROM author_tags,authors WHERE author_tags.aid=authors.aid and author_tags.tag=:name'
  cursor = g.conn.execute(text(cmd),name=name)
  authors = []
  for result in cursor:
    authors.append((result['aid'],result['name']))
  cursor.close()


  # Get quotes
  cmd = 'SELECT quotes.qid AS qid, quotes.content AS content FROM quote_tags,quotes WHERE quote_tags.qid=quotes.qid and quote_tags.tag=:name'
  cursor = g.conn.execute(text(cmd),name=name)
  quotes = []
  for result in cursor:
    quotes.append((result['qid'],result['content']))
  cursor.close()


  # Get works
  cmd = 'SELECT works.wid AS wid, works.name AS name FROM work_tags,works WHERE work_tags.wid=works.wid and work_tags.tag=:name'
  cursor = g.conn.execute(text(cmd),name=name)
  works = []
  for result in cursor:
    works.append((result['wid'],result['name']))
  cursor.close()


  context = dict(tag=name, works=works,quotes=quotes,authors=authors)
  return render_template("tag.html",**context);

# Displays page for a work
@app.route('/work',methods=['GET'])
def work():

  wid = request.args.get('wid')
  cmd = 'SELECT w.name as work, a.aid as aid, a.name as author FROM works w, authors a WHERE w.wid = (:wid) and w.aid = a.aid'
  cursor = g.conn.execute(text(cmd),wid=wid)

  works = []
  for result in cursor:
    works.append((result['work'],result['aid'],result['author']))
  cursor.close()

  # Get tags
  cmd = 'SELECT tag FROM work_tags WHERE work_tags.wid =:wid' 
  cursor = g.conn.execute(text(cmd),wid=wid)
  tags = []
  for result in cursor:
    tags.append(result[0])  
  cursor.close()

  context = dict(data = works[0],tags=tags)
  return render_template("work.html", **context)

# Displays page for an authors
@app.route('/author',methods=['GET'])
def author():
  aid = request.args.get('aid')
  cmd = 'SELECT name, bio FROM authors a WHERE a.aid = (:aid)'
  cursor = g.conn.execute(text(cmd),aid=aid)
  authors = []
  for result in cursor:
    authors.append((result['name'], result['bio']))
  cursor.close()
  
  cmd = 'SELECT qid, content FROM quotes q, authors a WHERE a.aid = (:aid) and q.author = a.aid and q.num_approvals >= 10 LIMIT 5'
  cursor = g.conn.execute(text(cmd),aid=aid)
  quotes = []
  for result in cursor:
    quotes.append((result['qid'],result['content'])) 
  cursor.close()
  
  cmd = 'SELECT wid, w.name as work FROM works w, authors a WHERE a.aid = (:aid) and w.aid = a.aid LIMIT 5'
  cursor = g.conn.execute(text(cmd),aid=aid)
  works = []
  for result in cursor:
    works.append((result['wid'],result['work']))
  cursor.close()


  # Get tags
  cmd = 'SELECT tag FROM author_tags WHERE author_tags.aid =:aid' 
  cursor = g.conn.execute(text(cmd),aid=aid)
  tags = []
  for result in cursor:
    tags.append(result[0])  
  cursor.close()

  context = dict(data = authors[0], quotes = quotes, works = works,tags=tags)
  return render_template("author.html", **context)


# Example of adding new data to the database
@app.route('/add', methods=['POST'])
def add():
  name = request.form['name']
  cmd = 'INSERT INTO test(name) VALUES (:name1), (:name2)';
  g.conn.execute(text(cmd), name1 = name, name2 = name);
  return redirect('/')

# Retrieves cookie for user information / login
@app.route('/getcookie')
def getcookie():
   uid = request.cookies.get('uid')
   username = request.cookies.get('username')
   return '<h1>welcome '+ username + ":" + uid +'</h1>'


# Logs user in and redirects
@app.route('/setcookie', methods = ['POST', 'GET'])
def setcookie():
    if request.method == 'POST':
      username = request.form['Username']
      password = request.form['Password']

      #  Check username is in table of usernames
      cmd = 'SELECT EXISTS(SELECT * FROM users WHERE users.username = :username)'
      cursor = g.conn.execute(text(cmd),username = username)
      user_exists = []
      for result in cursor:
        user_exists.append((result[0]))  
      cursor.close()
      user_exists = user_exists[0]
      if(user_exists == False):
        context = dict(message = "Incorrect username or password!!")
        return render_template("login.html",**context)

      # Check username and password correct
      cmd = 'SELECT uid,password FROM users WHERE users.username = (:username)';
      cursor = g.conn.execute(text(cmd),username = username)
      uinfo = []
      for result in cursor:
        uinfo.append((result['password'],result['uid']))  
      cursor.close()

      if(uinfo[0][0] == password):

        resp = app.make_response(redirect('/'))

        resp.set_cookie('uid', str(uinfo[0][1]))
        resp.set_cookie('username', str(username))
        return resp
      # Error login page
      else:
        context = dict(message = "Incorrect username or password!!")
        return render_template("login.html",**context)

# Renders page where users can login
@app.route('/login')
def login():
  return render_template("login.html")

# Renders page where users can sign up
@app.route('/signup')
def signup():
  return render_template("signup.html")

# Adds new user to database and redirects to login page 
@app.route('/adduser', methods=['POST','GET'])
def adduser():
  if request.method == 'POST':
    # Get information from form
    name = request.form['Name']
    username = request.form['Username']
    password = request.form['Password']

    # Get unique uid for user
    cmd = 'SELECT MAX(uid) FROM users';
    cursor = g.conn.execute(text(cmd))
    uid_count = []
    for result in cursor:
      uid_count.append(result[0])
    cursor.close()
    uid = uid_count[0] + 1

    # Check username is unique 
    cmd = 'SELECT EXISTS(SELECT 1 FROM users WHERE users.username = :username )'
    cursor = g.conn.execute(text(cmd),username = username )
    unique = []
    for result in cursor:
      unique.append(result[0]);
    if(unique[0] == True):
      context = dict(message = "Username is already taken - please choose another! :)")
      #flash("Username is already taken - please choose another! :)")
      return render_template("signup.html",**context)

    # Check that password isn't blank and is at least 5 characters
    elif(len(password) < 5):
      context = dict(message = "Password must be at least 5 characters! Try again please. :)")
      return render_template("signup.html", **context)

    # Render successful sign up if true
    else:
      cmd = 'INSERT INTO users VALUES( :uid, :name, :username, :password)'
      cursor = g.conn.execute(text(cmd),uid = uid,name = name,username = username, password = password)
      cursor.close()
      context = dict(message = "Successfully signed up! ^-^ Please try logging in right now.")
      return render_template("login.html",**context)




# Displays friends lsit of users
@app.route('/friends')
def friends():
  uid = request.args.get('uid')


  # Get username of user
  cmd = 'SELECT username FROM users WHERE users.uid = :uid'
  cursor = g.conn.execute(text(cmd),uid=uid )
  usernames= []
  for result in cursor:
    usernames.append(result['username'])
  cursor.close()
  username = usernames[0]

  # Select friends
  cmd = 'SELECT * FROM users WHERE users.uid IN ((SELECT uid2 FROM is_friends WHERE uid1 = :uid ) UNION  (SELECT uid1 FROM is_friends WHERE uid2 = :uid ))'
  cursor = g.conn.execute(text(cmd),uid=uid )

  friends = [];
  for result in cursor:
    friends.append((result['name'],result['username'],result['uid']))
  cursor.close()

  context = dict(friends = friends,username = username,uid=uid)
  return render_template("friends.html",**context)





# Shows list of users you've messaged
@app.route('/inbox')
def messages():
  uid = request.args.get('uid')
   # Get username of user
  cmd = 'SELECT username FROM users WHERE users.uid = :uid '
  cursor = g.conn.execute(text(cmd),uid=uid )
  usernames= []
  for result in cursor:
    usernames.append(result['username'])
  cursor.close()
  username = usernames[0]

  # Get friends usernames and uid
  cmd = 'SELECT * FROM ((SELECT users2.username AS username, users2.uid AS uid FROM messages,quotes,users, users AS users2 WHERE messages.sender = :uid and messages.qid = quotes.qid and messages.sender = users.uid and messages.receiver = users2.uid ORDER BY mtimestamp DESC) UNION (SELECT users.username AS username, users.uid AS uid FROM messages,quotes,users, users AS users2 WHERE messages.receiver = :uid and messages.qid = quotes.qid and messages.sender = users.uid and messages.receiver = users2.uid ORDER BY mtimestamp DESC)) AS messaged_friends'
  cursor = g.conn.execute(text(cmd),uid=uid )
  friends = [];
  for result in cursor:
    friends.append((result['username'],result['uid']))
  cursor.close()

  context = dict(username=username, uid=uid, friends = friends,is_friends=True)
  return render_template("inbox.html", **context)


# Gets conversations between two users
@app.route('/conversation')
def conversation():
  uid = request.args.get('uid')
  friend_uid = request.args.get('friend_uid')
  

  # Get username of user
  cmd = 'SELECT username FROM users WHERE users.uid = :uid '
  cursor = g.conn.execute(text(cmd),uid=uid )
  usernames= []
  for result in cursor:
    usernames.append(result['username'])
  cursor.close()
  username = usernames[0]

  # Get username of friend
  cmd = 'SELECT username FROM users WHERE users.uid = :uid '
  cursor = g.conn.execute(text(cmd),uid=friend_uid )
  friend_usernames= []
  for result in cursor:
    friend_usernames.append(result['username'])
  cursor.close()
  friend_username = friend_usernames[0]

  # Get messages between you and friend
  cmd = 'SELECT users.name AS sender, users2.name AS receiver, messages.content AS message,quotes.content AS quote,quotes.qid AS qid, messages.mtimestamp AS mtimestamp FROM messages LEFT JOIN quotes ON messages.qid = quotes.qid INNER JOIN users ON messages.sender = users.uid INNER JOIN users AS users2 ON messages.receiver = users2.uid WHERE ((messages.sender = :uid and messages.receiver = :friend_uid) or (messages.receiver = :uid and messages.sender = :friend_uid)) ORDER BY mtimestamp DESC'
  cursor = g.conn.execute(text(cmd),friend_uid=friend_uid,uid = uid )

  messages = []
  for result in cursor:
    messages.append((result['sender'],result['receiver'],result['message'],result['quote'],result['qid'],result['mtimestamp']))
  cursor.close()

  # Filter out null quotes
  for i in range(0,len(messages)):
    if(messages[i][3] == None):
      messages[i] = (messages[i][0],messages[i][1],messages[i][2],"",messages[i][4],messages[i][5])

  context = dict(username=username,uid=uid, friend_username = friend_username,friend_uid=friend_uid, messages = messages)
  return render_template("conversation.html", **context)

@app.route('/sendMessage',methods=['POST'])
def sendMessage():
  sender = int(request.form['sender'])
  receiver = int(request.form['receiver'])
  message = request.form['message']
  mtimestamp = request.form['mtimestamp']


  # Insert message into table
  cmd = 'INSERT INTO messages(sender,receiver,qid,content,mtimestamp) VALUES( :sender, :receiver, NULL, :message, :mtimestamp)'
  cursor = g.conn.execute(text(cmd),sender=sender,receiver=receiver, message=message, mtimestamp = mtimestamp)
  cursor.close()
 

  # return redirect('/conversation?uid=' + sender + "&friend_uid=" + receiver)
  return redirect('/conversation?uid='+str(sender) + "&friend_uid=" + str(receiver))

@app.route('/addFriend',methods=['POST'])
def addFriend():

  friend_uid = request.form['friend_uid']

  uid = request.form['uid']


  # Insert friend request into table
  cmd = 'INSERT INTO friend_requests VALUES( :sender, :receiver)'
  cursor = g.conn.execute(text(cmd),sender=uid,receiver=friend_uid)
  cursor.close()


  return redirect('/profile?uid='+friend_uid)

@app.route('/deleteFriend',methods=['POST'])
def deleteFriend():

  friend_uid = request.form['friend_uid']

  uid = request.form['uid']

  # Dlete friend request into table
  uid1 = min(uid,friend_uid)
  uid2 = max(uid,friend_uid)
  cmd = 'DELETE FROM is_friends WHERE uid1= :uid1 and uid2=:uid2'
  cursor = g.conn.execute(text(cmd),uid1=uid1,uid2=uid2)
  cursor.close()

  return redirect('/profile?uid='+friend_uid)

@app.route('/rejectFriendRequest',methods=['POST'])
def rejectFriendRequest():
  uid = request.form['uid']
  friend_uid = request.form['friend_uid']

  # Delete from friend request table
  cmd = 'DELETE FROM friend_requests WHERE friend_requests.sender = :friend_uid and friend_requests.receiver = :uid'
  cursor = g.conn.execute(text(cmd),friend_uid=friend_uid,uid=uid)
  cursor.close()

  return redirect('/profile?uid=' + uid)

@app.route('/acceptFriendRequest',methods =['POST'])
def acceptFriendRequest():
  uid = request.form['uid']
  friend_uid = request.form['friend_uid']

  # Delete from friend request table
  cmd = 'DELETE FROM friend_requests WHERE friend_requests.sender = :friend_uid and friend_requests.receiver = :uid'
  cursor = g.conn.execute(text(cmd),friend_uid=friend_uid,uid=uid)
  cursor.close()

  # Insert into friends table
  uid1 = min(uid,friend_uid)
  uid2 = max(uid,friend_uid)
  cmd = 'INSERT INTO is_friends VALUES( :uid1, :uid2)'
  cursor = g.conn.execute(text(cmd),uid1=uid1,uid2=uid2)
  cursor.close()

  return redirect('/profile?uid=' + uid)

@app.route('/vote')
def vote():
  """
  request is a special object that Flask provides to access web request information:
  request.method:   "GET" or "POST"
  request.form:     if the browser submitted a form, this contains the data in the form
  request.args:     dictionary of URL arguments e.g., {a:1, b:2} for http://localhost?a=1&b=2
  See its API: http://flask.pocoo.org/docs/0.10/api/#incoming-request-data
  """
  uid = request.cookies.get('uid')
  unapproved_cmd = "SELECT q.qid as qid, q.content as content, a.aid as aid, a.name as author, w.wid as wid, w.name as work FROM quotes q INNER JOIN authors a ON q.author = a.aid LEFT JOIN works w on q.work = w.wid WHERE q.num_approvals < 10 ORDER BY q.added_time ASC LIMIT 5"
  unapproved_cursor = g.conn.execute(text(unapproved_cmd))
  unapproved_quotes = []
  for result in unapproved_cursor:
    unapproved_quotes.append((result['qid'],result['content'],result['aid'],result['author'],result['wid'],result['work']))
  unapproved_cursor.close()
  

  cmd = 'SELECT qid FROM user_approves WHERE user_approves.uid = :uid'
  cursor = g.conn.execute(text(cmd),uid = uid)
  user_approved_quotes = []
  for result in cursor:
    user_approved_quotes.append(result[0])
  cursor.close()
  context = dict(unapproved_quotes = unapproved_quotes,uid = uid,user_approved_quotes = user_approved_quotes)

  return render_template("voting.html", **context)
  

@app.route('/approveQuote',methods=['POST'])
def approveQuote():
  qid = request.form['qid']
  uid = request.form['uid']

  # updates user approves table
  cmd = 'INSERT INTO user_approves VALUES(:uid,:qid)'
  cursor = g.conn.execute(text(cmd),qid=qid,uid=uid)
  cursor.close()
 
  # update the quotes table
  cmd = 'UPDATE quotes SET num_approvals = num_approvals + 1 WHERE quotes.qid = :qid'
  cursor = g.conn.execute(text(cmd),qid=qid)
  cursor.close()
  return redirect('/vote')


@app.teardown_request
def teardown_request(exception):
  """
  At the end of the web request, this makes sure to close the database connection.
  If you don't the database could run out of memory!
  """
  try:
    g.conn.close()
  except Exception as e:
    pass

# Main function for running app
if __name__ == "__main__":
  import click

  @click.command()
  @click.option('--debug', is_flag=True)
  @click.option('--threaded', is_flag=True)
  @click.argument('HOST', default='0.0.0.0')
  @click.argument('PORT', default=8111, type=int)
  def run(debug, threaded, host, port):
    """
    This function handles command line parameters.
    Run the server using
        python server.py
    Show the help text using
        python server.py --help
    """

    HOST, PORT = host, port
    print "running on %s:%d" % (HOST, PORT)
    app.run(host=HOST, port=PORT, debug=debug, threaded=threaded)


  run()