from flask import Flask, jsonify, abort, flash, session, escape, make_response, render_template, request, g, redirect, url_for
from functools import wraps
from contextlib import closing
import MySQLdb
import MySQLdb.cursors
app = Flask(__name__)

@app.before_request
def before_request():
    g.db = MySQLdb.connect(host='localhost',
                    port=3306,
                     user='pstoica_4400_2',
                     passwd='lolwut',
                     db='pstoica_4400_2',
                     cursorclass=MySQLdb.cursors.DictCursor)
    if session.get('user'):
        cursor = g.db.cursor()
        cursor.execute("SELECT `Name` FROM `User` WHERE Email=%s",
                       session.get('user'))
        g.user = cursor.fetchone()

@app.teardown_request
def teardown_request(exception):
    g.db.close()

def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if session.get('user'):
            return f(*args, **kwargs)
        else:
            flash('Please log in first.', 'error')
            next_url = request.url
            login_url = '%s?next=%s' % (url_for('login'), next_url)
            return redirect(login_url)
    return decorated_function

@app.route('/')
@login_required
def index():
    cursor = g.db.cursor()
    cursor.execute("""SELECT CorkBoard.ID, CorkBoard.Title, User.Name, CorkBoard.Private, MAX(DateAndTime) AS LastUpdate
                FROM `CBWithPrivate` AS CorkBoard
                INNER JOIN `User`
                USING (Email)
                INNER JOIN `PushPin`
                ON CorkBoard.ID = PushPin.CorkBoard
                LEFT JOIN `Follow`
                ON Follower = %s AND Followed = CorkBoard.Email
                LEFT JOIN `Watch`
                ON Watch.Email = %s AND Watch.CorkBoard = CorkBoard.ID
                WHERE Follow.Followed IS NOT NULL
                    OR Watch.CorkBoard IS NOT NULL
                    OR CorkBoard.Email = %s
                GROUP BY CorkBoard.Title, CorkBoard.Email
                ORDER BY LastUpdate DESC
                LIMIT 4""",
                [session.get('user')] * 3)
    recent_updates = cursor.fetchall()
    cursor.execute("""SELECT CorkBoard.ID, CorkBoard.Title, COUNT(PushPin.ID) as PushPins, CorkBoard.Private
                FROM `CBWithPrivate` as `CorkBoard`
                LEFT JOIN `PushPin`
                ON CorkBoard.ID = PushPin.CorkBoard
                WHERE CorkBoard.Email=%s
                GROUP BY CorkBoard.Title
                ORDER BY CorkBoard.Title ASC""", (session.get('user')))
    my_corkboards = cursor.fetchall()
    return render_template('index.html',
                           recent_updates = recent_updates,
                           my_corkboards = my_corkboards)
    

@app.route('/login', methods=['POST', 'GET'])
def login():
    cursor = g.db.cursor()
    if request.method == 'POST':
        cursor.execute("""SELECT *
                        FROM `User`
                        WHERE Email=%s

                        AND PIN=%s""",
                        (request.form['username'],
                        request.form['password']))
        result = cursor.fetchone()
        if result is None:
            flash("Invalid username or password.", "error")
        else:
            session['user'] = request.form['username']
            flash("You've successfully logged in!", "success")
            return redirect(url_for('index'))
    return render_template('login.html')
    
@app.route('/logout')
@login_required
def logout():
    session.clear()
    flash("You've been logged out!", "success")
    return redirect(url_for('login'))

@app.route('/corkboard/add', methods=['POST', 'GET'])
@login_required
def add_corkboard():
    cursor = g.db.cursor()
    cursor.execute("""SELECT *
                    FROM Category""")
    categories = cursor.fetchall()
    if request.method == 'POST':
        match = len([category for category in categories if category['Name'] == request.form['category']])
        if match > 0 and len(request.form['title']) > 0:
            try:
                cursor.execute("""INSERT INTO CorkBoard
                                (`Title`, `Email`, `Category`)
                                VALUES (%s, %s, %s)""",
                                (request.form['title'],
                                session.get('user'),
                                request.form['category']))
            except MySQLdb.IntegrityError:
                flash("You already have a CorkBoard with that title!", "error")
                return render_template('add_corkboard.html', categories = categories)
            corkboard_id = cursor.lastrowid
            if len(request.form['password']) > 0:
                cursor.execute("""INSERT INTO PrivateCB
                                (`ID`, `Password`)
                                VALUES (%s, %s)""",
                                (corkboard_id,
                                 request.form['password']))
            else:
                cursor.execute("""INSERT INTO PublicCB
                                (`ID`)
                                VALUES (%s)""",
                                (corkboard_id))
            g.db.commit()
            flash("CorkBoard has successfully been added!", "success")
            return redirect(url_for('index'))
        else:
            flash("Some of your form data was invalid.", "error")
    return render_template('add_corkboard.html', categories = categories)

@app.route('/corkboard/<int:cb_id>', methods=['POST', 'GET'])
@login_required
def view_corkboard(cb_id):
    cursor = g.db.cursor()
    cursor.execute("""SELECT CorkBoard.ID, CorkBoard.Title, CorkBoard.Category, Password, User.Name, User.Email, CorkBoard.Private, MAX(PushPin.DateAndTime) AS LastUpdate, COUNT(DISTINCT(Watch.Email)) AS Watchers, COUNT(Follow.Follower) AS IsFollowing,
                    IF(EXISTS(SELECT *
                        FROM `Watch`
                        WHERE CorkBoard = %s
                        AND Email = %s), 1, 0) AS IsWatching
                    FROM `CBWithPrivate` AS CorkBoard
                    INNER JOIN `User`
                    USING (Email)
                    LEFT JOIN `Watch`
                    ON CorkBoard.ID = Watch.CorkBoard
                    LEFT JOIN `PrivateCB`
                    USING (`ID`)
                    LEFT JOIN `Follow`
                    ON Follow.Follower = %s AND Follow.Followed = CorkBoard.Email
                    LEFT JOIN `PushPin`
                    ON CorkBoard.ID = PushPin.CorkBoard
                    WHERE CorkBoard.ID = %s""",
                    (cb_id,
                     session.get('user'),
                     session.get('user'),
                     cb_id))
    corkboard = cursor.fetchone()
    if corkboard['ID'] is None:
        abort(404)
    elif corkboard['Private'] > 0 and not session.get('cb_' + str(cb_id)):
        if request.method == 'POST':
            if request.form['password'] != corkboard['Password']:
                flash("Invalid password.", "error")
                return render_template('private_corkboard.html', corkboard = corkboard)
            else:
                session['cb_' + str(cb_id)] = True
        else:
            return render_template('private_corkboard.html', corkboard = corkboard)
    cursor.execute("""SELECT *
                    FROM `PushPin`
                    WHERE CorkBoard = %s""",
                    (cb_id))
    pushpins = cursor.fetchall()
    return render_template('view_corkboard.html',
                           corkboard = corkboard,
                           pushpins = pushpins) 

@app.route('/pushpin/add', methods=['POST', 'GET'])
@login_required
def add_pushpin(cb_id = None):
    cursor = g.db.cursor()
    cursor.execute("""SELECT *
                    FROM CorkBoard
                    WHERE Email = %s""",
                    (session.get('user')))
    corkboards = cursor.fetchall()
    if cursor.rowcount == 0:
        flash('Please add a CorkBoard first!', 'error')
        return redirect(url_for('add_corkboard'))
    if request.method == 'POST':
        match = len([corkboard for corkboard in corkboards if corkboard['ID'] == long(request.form['corkboard'])])
        if match > 0 and len(request.form['image_url']) > 0 and len(request.form['description']) > 0:
            try:
                cursor.execute("""INSERT INTO PushPin
                                (`CorkBoard`, `Link`, `Description`)
                                VALUES (%s, %s, %s)""",
                                (request.form['corkboard'],
                                request.form['image_url'],
                                request.form['description']))
                pushpin_id = cursor.lastrowid
                if len(request.form['tags']) > 0:
                    tags = [x.strip() for x in request.form['tags'].split(',')]
                    for tag in list(set(tags)):
                        cursor.execute("""INSERT INTO Tag
                                        (`PushPin`, `Tag`)
                                        VALUES (%s, %s)""",
                                        (pushpin_id,
                                        tag))
                g.db.commit()
                flash("PushPin has successfully been added!", "success")
                return redirect(url_for('index'))
            except MySQLdb.IntegrityError:
                flash("You've already posted that image in this CorkBoard!", "error")
        else:
            flash("Some of your form data was invalid.", "error")
    return render_template('add_pushpin.html', corkboards = corkboards, cb_id = cb_id)

@app.route('/pushpin/add/<int:cb_id>', methods=['POST', 'GET'])
@login_required
def add_pushpin_corkboard(cb_id):
    return add_pushpin(cb_id)

@app.route('/pushpin/<int:pp_id>', methods=['POST', 'GET'])
@login_required
def view_pushpin(pp_id):
    cursor = g.db.cursor()
    cursor.execute("""SELECT PushPin.ID, CorkBoard.Title AS CBTitle, CorkBoard.Private, Description, Link, DateAndTime, CorkBoard, Email, Name, COUNT(Follow.Follower) AS IsFollowing,
		IF(EXISTS(SELECT *
		FROM `Like`
		WHERE PushPin = %s 
		AND Email = %s), 1, 0) AS IsLiked
		FROM `PushPin`
		INNER JOIN `CBWithPrivate` as CorkBoard
		ON PushPin.CorkBoard = CorkBoard.ID
		INNER JOIN `User`
		USING(Email)
		LEFT JOIN `Follow`
		ON Follow.Follower = %s AND Follow.Followed = CorkBoard.Email
		WHERE PushPin.ID = %s""",
		((pp_id,
		session.get('user'),
		session.get('user'),
		pp_id)))
    pushpin = cursor.fetchone()
    
    if request.method == 'POST':
        comment = request.form['comment']
        if len(comment) > 0 and 'ID' in pushpin:
            print 'sup'
            try:
                cursor.execute("""INSERT INTO `Comment` (PushPin, Email, Text)
                                VALUES (%s, %s, %s)""",
                                (pushpin['ID'],
                                 session.get('user'),
                                 comment))
                flash("Your comment was successfully posted!", "success")
            except MySQLdb.IntegrityError:
                flash("Oops, you made a duplicate comment!", "error")
        else:
            print 'nope'
            flash("Please fill out the comment form!", "error")

    cursor.execute("""SELECT Tag
		FROM `Tag`
		WHERE Tag.PushPin = %s
		ORDER BY Tag ASC""", (pp_id))
    tags = cursor.fetchall()

    cursor.execute( """ SELECT Text, Name, DateAndTime
		FROM `Comment`
		INNER JOIN `User`
		USING (Email)
		WHERE PushPin = %s
		ORDER BY DateAndTime DESC""", (pp_id))
    comments = cursor.fetchall()

    cursor.execute( """SELECT Name
		FROM `Like`
		INNER JOIN `User`
		USING(Email)
		WHERE PushPin = %s
		AND Email != %s""",
        (pp_id,
         session.get('user')))
    likes = []
    if pushpin['IsLiked']:
        likes.append(dict(Name = 'You'))
    if cursor.rowcount > 0:
        likes.append(cursor.fetchall()[0])
    
    g.db.commit()

    if pushpin['ID'] is None:
        abort(404)
    elif pushpin['Private'] > 0 and not session.get('cb_' + str(pushpin['CorkBoard'])):
        return redirect(url_for('view_corkboard', cb_id = pushpin['CorkBoard']))
    else:
        return render_template('view_pushpin.html', pushpin = pushpin, tags = tags, comments = comments, likes = likes)

@app.route('/populartags')
@login_required
def popular_tags():
    cursor = g.db.cursor()
    cursor.execute(""" SELECT `Tag`, COUNT(PushPin) AS `PushPins`, COUNT(DISTINCT PushPin.CorkBoard) AS `UniqueCorkBoards`
                    FROM `Tag`
                    LEFT JOIN `PushPin`
                    ON Tag.PushPin = PushPin.ID
                    GROUP BY `Tag`
                    ORDER BY `PushPins` DESC, `UniqueCorkBoards` DESC
                    LIMIT 5 """)
    tags = cursor.fetchall()
    return render_template('popular_tags.html', tags = tags)

@app.route('/search', methods=['POST', 'GET'])
@login_required
def search():
    cursor = g.db.cursor()
    if request.method == 'GET':
        results = []
        term = request.args.get('terms')
        if term:
            cursor.execute("""SELECT PushPin.ID as PP_ID, CorkBoard.ID as CB_ID, Description AS Description, Link AS URL, CorkBoard.Title AS CorkBoard, User.Name AS Owner
    			FROM `PushPin`
    			LEFT JOIN `CBWithPrivate` as CorkBoard
    			ON CorkBoard = CorkBoard.ID
    			LEFT JOIN `Tag`
    			ON Tag.PushPin = PushPin.ID
    			INNER JOIN `User`
    			ON CorkBoard.Email = User.Email
    			WHERE (PushPin.Description LIKE %s
    			OR CorkBoard.Category LIKE %s
    			OR Tag.Tag LIKE %s)
    			AND CorkBoard.Private = 0
    			GROUP BY PushPin.Description, PushPin.Link, PushPin.CorkBoard
    			ORDER BY PushPin.Description ASC""",
                ['%' + term + '%'] * 3)
            results.append(cursor.fetchall())
    return render_template('search.html', results = results, terms = term)

@app.route('/_like', methods=['POST'])
@login_required
def like():
    pp_id = request.form['pp_id']
    cursor = g.db.cursor()
    cursor.execute("""SELECT * FROM `Like`
                    WHERE Email = %s
                    AND PushPin = %s""",
                    (session.get('user'),
                     pp_id))
    liked = cursor.rowcount > 0
    if liked:
        cursor.execute("""DELETE FROM `Like`
                        WHERE PushPin = %s
                        AND Email = %s""",
                        (pp_id,
                         session.get('user')))
        liked = False
    else:
        cursor.execute("""INSERT INTO `Like` (PushPin, Email)
                    VALUES (%s, %s)""",
                    (pp_id,
                     session.get('user')))
        liked = True
    g.db.commit()
    return jsonify(dict(liked = liked))

@app.route('/_follow', methods=['POST'])
def follow():
    email = request.form['email']
    cursor = g.db.cursor()
    cursor.execute("""SELECT *
                    FROM `Follow`
                    WHERE Follower = %s
                    AND Followed = %s""",
                    (session.get('user'),
                     email))
    followed = cursor.rowcount > 0
    if followed:
        cursor.execute("""DELETE FROM `Follow`
                        WHERE Follower = %s
                        AND Followed = %s""",
                        (session.get('user'),
                         email))
        followed = False
    else:
        cursor.execute("""INSERT INTO `Follow` (Follower, Followed)
                    VALUES (%s, %s)""",
                    (session.get('user'),
                     email))
        followed = True
    g.db.commit()
    return jsonify(dict(followed = followed))

@app.route('/_watch', methods=['POST'])
@login_required
def watch():
    cb_id = request.form['cb_id']
    cursor = g.db.cursor()
    cursor.execute("""SELECT *
                    FROM Watch
                    WHERE Email = %s
                    AND CorkBoard = %s""",
                    (session.get('user'),
                     cb_id))
    watched = cursor.rowcount > 0
    if watched:
        cursor.execute("""DELETE FROM `Watch`
                        WHERE Email = %s
                        AND CorkBoard = %s""",
                        (session.get('user'),
                         cb_id))
        watched = False
    else:
        cursor.execute("""INSERT INTO `Watch` (Email, CorkBoard)
                    VALUES (%s, %s)""",
                    (session.get('user'),
                     cb_id))
        watched = True
    g.db.commit()
    return jsonify(dict(watched = watched))

@app.errorhandler(404)
def not_found(error):
    return render_template('error.html'), 404

app.url_map.strict_slashes = False
app.debug = True
app.secret_key = 'A0Zr98j/3yX R~XHH!jmN]LWX/,?RT'


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