#!/usr/bin/env python
from circuits import handler, Component , Debugger
from circuits.web import Server, Controller, Sessions
import MySQLdb
from mako.template import Template
home = Template(filename='home.html')
user = Template(filename='user.html')
db = MySQLdb.connect(host="localhost", user="root", passwd="!1#32@",db="twatter")
cursor = db.cursor()
def login(username,password):
    cursor.execute("SELECT id, password FROM login WHERE username=%s", (username))
    result = cursor.fetchall()
    if result:
        if result[0][1] == password:
            return result[0][0]

showfollows = """SELECT updates.datetime, updates.message, login.username
FROM following
INNER JOIN updates ON following.followid = updates.ID
INNER JOIN login ON following.followid = login.ID
WHERE following.ID =%s
ORDER BY updates.datetime DESC
LIMIT 5"""

nametoid = """SELECT ID
FROM login
WHERE username=%s"""

showsuser = """SELECT updates.datetime, updates.message, login.username
FROM updates
INNER JOIN login ON updates.ID = login.ID
WHERE login.username =%s
ORDER BY updates.datetime DESC
LIMIT 5"""

isfollow = """SELECT following.ID, following.followid , login.username
FROM following
INNER JOIN login on following.ID = login.ID
WHERE following.ID =%s AND following.followid = login.ID"""

sqlupdate="INSERT INTO updates (ID, message, datetime) VALUES (%s, %s, CURRENT_TIMESTAMP);"

sqlfollowcheck="""SELECT *
FROM following
WHERE following.ID=%s AND following.followid=%s"""

sqlfollow="""INSERT INTO  following (ID, followid)
VALUES (%s, %s)"""

sqlfollowdel = """DELETE FROM following
WHERE ID=%s AND followid=%s"""
class Root(Controller):
    def index(self, **kwargs):
        if "id" in self.session:
            cursor.execute(showfollows, (self.session["id"]))
            updates = cursor.fetchall()
            return home.render(updates=updates ,name=self.session["name"])
        else:
            return home.render(logged=False)
    def login(self,**kwargs):
        if "name" in self.session:
            return home.render(name=self.session["name"])
        elif kwargs.get("username", None):
            username = kwargs.get("username", None)
            password = kwargs.get("password", None)
            if login(username,password):
                self.session["name"] = username
                self.session["id"] =login(username,password)
                return Template("go home <a href='./index'>a</a>").render()
            else:
                return home.render(name=None,message="Wrong password")

        else:
            return home.render(name=None)
    def user(self , username):
        cursor.execute(showsuser, (username))
        updates = cursor.fetchall()
        if "id" in self.session:
            cursor.execute(nametoid,(username))
            userid= cursor.fetchall()[0][0]
            cursor.execute(sqlfollowcheck, (self.session["id"],userid))
            following = cursor.fetchall()
            return user.render(logged=True,name=username,updates=updates,following=following)
        else:
            return user.render(name=username,updates=updates)

    def update(self , update=None):
        if "id" in self.session and update != None:
            cursor.execute(sqlupdate, (self.session["id"],update))
            return "added"
    def follow(self, username=None):
        if "id" in self.session and user != None:
            cursor.execute(nametoid,(username))
            userid= cursor.fetchall()[0][0]
            if cursor.execute(sqlfollowcheck,(self.session["id"],userid)):
                cursor.execute(sqlfollowdel, (self.session["id"],userid))
                return "Removed from following"
            else:
                cursor.execute(sqlfollow, (self.session["id"],userid))
                return "now following them =D"
        else:
            return "login to follow peeeps gggg"
(Server(("0.0.0.0", 8000)) +Sessions() + Root()).run()