#!/usr/local/bin/python2.7
# -*- coding: utf-8 -*-


#This file contains helper functions used in app.py to interact with the database tables
#Last Edited: 5/12/2017

import os, sys
import re
import MySQLdb
import dbconn2
from mgaosxu_dsn import DSN
import datetime

DATABASE = 'mgaosxu_db'

def cursor(database=DATABASE):
	'''Returns a cursor to the database'''
	DSN['db'] = database
	conn = dbconn2.connect(DSN)
	return conn.cursor(MySQLdb.cursors.DictCursor)

def lookup_blogID(cursor, bid):
	''' looks up if a blogID exists in the database and returns a boolean'''
	cursor.execute(''' SELECT count(*) as count from blogPost where blogID = %s ''', [bid])
	row = cursor.fetchone()
	return row['count']>0

def insert_blogPost(cursor, uid, title, time, price, extension):
	''' inserts a new post into the database and returns the retusn ID generated by the last insert statement'''
	cursor.execute(''' INSERT INTO blogPost (userID, title, time, price) VALUES (%s, %s, %s, %s) ''',
		          (uid, title, time, price))
	return cursor.lastrowid

def insert_new_file(cursor,blogID, fileNum, extension):
	''' insert a new file into the file table '''
	cursor.execute(''' INSERT INTO file (blogID, fileNum, extension) values (%s, %s, %s) ''', (blogID, fileNum, extension))

def delete_blogPost(cursor, bid):
	''' deletes a blog post and its associated comments in the database given blogID '''
	cursor.execute(''' DELETE from likePosts where blogID = %s''', [bid])
	cursor.execute(''' DELETE from comment where blogID = %s''', [bid])
	cursor.execute(''' DELETE from file where blogID=%s ''', [bid])
	cursor.execute(''' DELETE from blogPost where blogID=%s ''', [bid])

def get_posts(cursor):
	''' get all posts in the database '''
	cursor.execute(''' SELECT blogID, title, datePosted, numoflikes, time, price, user.nm, user.name as author from blogPost inner join user on (user.nm = blogPost.userID) ''')
	rows = cursor.fetchall()
	return rows

def get_postFiles(cursor, blogID):
	''' returns a list of tags given a blogID in the database '''
	cursor.execute(''' SELECT * from file where blogID=%s''', [blogID])
	rows = cursor.fetchall()
	tags = [] # new tags array with all the tags 
	allowed_extensions = ['.jpg', '.png', '.jpeg']
	for row in rows:
		if (row['extension'] in allowed_extensions):
			tags.append('<img src="/files/' + str(blogID) + '_' + str(row['fileNum']) + row['extension'] + '" width="50%">')
		elif (row['extension'] == '.txt'):
			with open('/students/mgaosxu/cs304/beta/files/' + str(blogID) + '_' + str(row['fileNum']) + row['extension'], 'r') as content_file:
				contents = content_file.read()
			tags.append('<pre width="50">' + contents + '</pre>')
		elif (row['extension'] == '.pdf'):
			tags.append('<a href="/files/' + str(blogID) + '_' + str(row['fileNum']) + row['extension'] + '">Click here for PDF</a>')
	return tags

def get_postFilestoDisplay(cursor,blogID):
	''' returns a list of filenums for the given blogID '''
	cursor.execute(''' SELECT fileNum from file where blogID=%s''',(blogID,))
	rows = list(cursor.fetchall())
	fileNums = []
	for row in rows:
		fileNums.append(int(row['fileNum']))
	return fileNums

def get_largestfileNum(cursor,blogID):
	''' returns the highest fileNum for the given blogID '''
	cursor.execute(''' SELECT max(fileNum) as fileNum FROM file WHERE blogID=%s''',(blogID,))
	row = cursor.fetchone()
	print row['fileNum']
	return row['fileNum']


def get_posts_sortby(cursor, tag):
	''' get all posts by date posted/modified or number of likes depending on tag'''
	if (tag == 'date'):
		cursor.execute(''' SELECT blogID, title, datePosted, numoflikes, time, price, user.nm, user.name as author from blogPost inner join user on (user.nm = blogPost.userID) ORDER BY datePosted desc''')
	elif (tag == 'numlikes'):
		cursor.execute(''' SELECT blogID, title, datePosted, numoflikes, time, price, user.nm, user.name as author from blogPost inner join user on (user.nm = blogPost.userID) ORDER BY numoflikes desc ''')
	rows = cursor.fetchall()
	return rows

def get_userPosts(cursor,nm):
	''' get all posts by that user in the database'''
	cursor.execute(' SELECT blogID, title, datePosted, numoflikes, name as author from blogPost inner join user on (user.nm = blogPost.userID) where blogPost.userID =%s ',(int(nm),))
	rows = cursor.fetchall()
	return rows

def get_postsByTitle(cursor, title):
	'''get all posts with titles that match the searched title '''
	searchTitle = "%" + title + "%"
	cursor.execute(''' SELECT blogID,title from blogPost where title like %s''',(searchTitle,))
	rows = cursor.fetchall()
	return rows

def get_blogforBlogpost(cursor, blogID):
	''' returns a blog title given a blogID '''
	cursor.execute( ''' SELECT title, time, numoflikes, price, user.name from blogPost inner join user on (blogPost.userID = user.nm) where blogID = %s''', [blogID])
	row = cursor.fetchone()
	return row

def get_author(cursor, blogID):
	''' get author of a blogpost '''
	cursor.execute(''' SELECT name from user inner join blogPost on (user.nm = blogPost.userID) where blogID=%s''', [blogID])
	row = cursor.fetchone()
	return row['name']
	
def get_postsByTag(cursor, term,tag):
	''' get all posts that match the tag '''
	regExp_time = r'^[-+]?[0-9]+$'
	if tag == "time":
	       	if re.search(regExp_time,term):
	       		term = int(term)
	       		cursor.execute('SELECT blogID,title from blogPost where time = %s',(term,))
	       		rows = cursor.fetchall()
	       		return rows
	       	else:
	       		return "tfail"
	elif tag == "price":
		regExp_price = r'^\$?(\d{1,3},?(\d{3},?)*\d{3}(\.\d{1,3})?|\d{1,3}(\.\d{2})?)$'
		if re.search(regExp_price,term):
			term = int(term.strip('$').strip(','))
			cursor.execute('SELECT blogID,title from blogPost where price <= %s',(term,))
			rows = cursor.fetchall()
			return rows
		else:
			return "pfail"
	elif tag == "name":
		username = "%" + term + "%"
		cursor.execute('SELECT blogID,title from user inner join blogPost on (blogPost.userID = user.nm) where username like %s',(username,))
		rows = cursor.fetchall()
		return rows;

def get_fileNames(cursor, blogID):
	'''Returns a list of file names given the blogID'''
	cursor.execute(''' SELECT * from file where blogID=%s''', [blogID])
	rows = cursor.fetchall()
	filenames = []
	for row in rows:
		filenames.append(str(row['blogID'])+'_'+str(row['fileNum'])+row['extension'])
	return filenames

def get_fileNums(cursor,blogID):
	''' Returns a list of fileNums given the blogID'''
	cursor.execute(''' SELECT fileNum from file where blogID=%s ''', (blogID,))
	rows = cursor.fetchall()
	list(rows)
	return rows

def count_files(cursor,blogID):
	''' Returns the number of files in the file table for the given blogID'''
	cursor.execute(''' SELECT count(*) FROM file WHERE blogID=%s''',(blogID,))
	row = cursor.fetchone()
	counter = int(row['count(*)'])
	return counter

def get_fileNamestoDelete(cursor, blogID, fileNums):
	''' Returns a list of file names using the blogId and fileNum'''
	filenames = []
	for fileNum in fileNums:
		cursor.execute(''' SELECT * from file where blogID=%s and fileNum=%s''',(blogID,int(fileNum),))
		row = cursor.fetchone()
		filenames.append(str(row['blogID'])+'_'+str(row['fileNum'])+row['extension'])
	return filenames

def like_post(cursor, blogID,condition):
	''' Increment numoflikes by one given a blogID'''
	if condition == "true":
		cursor.execute(''' UPDATE blogPost SET numoflikes = numoflikes+1 where blogID = %s ''', [blogID])
	cursor.execute(''' SELECT numoflikes from blogPost where blogID = %s''', [blogID])
	row = cursor.fetchone()
	return str(row['numoflikes'])

def like_comment(cursor, commentID):
	''' Increment numoflikes by one given a commentID, will take care of sessions later...'''
	cursor.execute(''' UPDATE comment SET numoflikes = numoflikes+1 where commentID = %s ''', [commentID])
	cursor.execute(''' SELECT numoflikes from comment where commentID = %s ''', [commentID])
	row = cursor.fetchone()
	return str(row['numoflikes'])

def insert_user(cursor,name,username,password,shortintro):
	'''Insert a new user in the user table'''
	cursor.execute('''INSERT into user(name,username,password,shortintro) values(%s,%s,%s,%s)''',[name,username,password,shortintro,])

def lookup_username(cursor,username):
	'''Checks if a username exists in the db for login'''
	cursor.execute(''' SELECT count(*) as count from user where username=%s''',[username])
	row = cursor.fetchone()
	return row['count'] == 0

def check_username(cursor,username):
	'''Checks if the username already exists'''
	cursor.execute(''' SELECT * from user where username=%s''',(username,))
	row = cursor.fetchone()
	return row

def get_nm(cursor,username):
	'''Gets the nm for the current logged in user'''
	cursor.execute(''' SELECT nm from user where username=%s''',[username])
	return cursor.fetchone()

def get_pw_hash(cursor, username):
	''' Returns the hashed password given a username'''
	cursor.execute(''' SELECT password from user where username = %s''', [username])
	row = cursor.fetchone()
	return row['password']

def get_num_comments(cursor, blogID):
	'''Returns the number of comments a post has '''
	cursor.execute(''' SELECT count(*) as count FROM comment where blogID = %s ''', [blogID])
	row=cursor.fetchone()
	return row['count']

def get_comments(cursor, blogID):
	''' Returns all the comments associated with an ID '''
	cursor.execute(''' SELECT commentContent, numoflikes, userID, datePosted, commentID from comment where blogID=%s ORDER BY datePosted desc''', [blogID])
	rows = cursor.fetchall()
	return rows

def get_username(cursor, userID):
	''' return the username given a userID '''
	cursor.execute(''' SELECT username from user where nm = %s ''', [userID])
	row = cursor.fetchone()
	return row['username']

def compare_userID_blogID(cursor,userID,blogID):
	''' return the tile of the post given userID and blogID '''
	cursor.execute('SELECT title from blogPost where userID=%s and blogID=%s',(userID,blogID,))
	row = cursor.fetchone()
	return row

def insert_new_comment(cursor, blogID, userID, commentContent):
	''' Inserts a new post into comment table given blogID '''
	cursor.execute(''' INSERT into comment(blogID, userID, commentContent) values(%s, %s, %s) ''', (blogID, userID, commentContent))
	lastrowid = cursor.lastrowid
	cursor.execute(''' SELECT blogID, commentContent, numoflikes, datePosted from comment where commentID = %s ''', [lastrowid])
	row = cursor.fetchone()
	row['commentID'] = lastrowid
	return row

def add_new_following(cursor, userID, followingID):
	''' inserts a new following person in the database for given userID '''
	cursor.execute(''' INSERT into following values (%s, %s) ''', (userID, followingID,))

def get_following_IDs(cursor, userID):
	''' given a userID, return the followingIDs that the user is following '''
	cursor.execute(''' SELECT following as followerID from user inner join following on (user.nm = following.user) where user.nm = %s ''', [userID])
	rows = cursor.fetchall()
	return rows

def check_if_following_exists(cursor, userID, followingID):
	''' Checks if the user if already following user with given followingID ''' 
	cursor.execute(''' SELECT count(*) as count FROM following where user = %s and following =%s ''', (userID, followingID,))
	row = cursor.fetchone()
	return row['count']>0

def delete_comment(cursor,commentID):
	''' Deletes the comment that matches the ID '''
	cursor.execute(''' DELETE from comment WHERE commentID = %s''', [commentID])

def update_post(cursor,blogID,title,time,price):
	''' Updates the post that matches the blogID '''
	cursor.execute(''' UPDATE blogPost SET title=%s, time=%s, price=%s WHERE blogID=%s ''', (title,int(time),int(price),blogID,))
	cursor.execute(''' SELECT blogID FROM blogPost WHERE blogID=%s ''', (blogID,))
	row = cursor.fetchone()
	return row

def get_post(cursor,blogID):
	''' Gets a post by its unique blogID '''
	cursor.execute(''' SELECT blogID,title,time,price FROM blogPost WHERE blogID = %s''', (blogID,))
	row = cursor.fetchone()
	return row

def delete_file(cursor,blogID, fileNum):
	''' Deletes a file from the file table '''
	cursor.execute(''' DELETE FROM file where blogID = %s and fileNum = %s ''', (blogID, fileNum,))

def check_if_likeed_post(cursor, userID, blogID):
	''' check if the user with given userID has liked the blogpost already''' 
	cursor.execute(''' SELECT count(*) as count from likePosts where userID = %s and blogID = %s''',(userID, blogID,))
	row = cursor.fetchone()
	return row['count']>0

def insert_new_like_post(cursor, userID, blogID):
	''' inserts a new post like into the database '''
	cursor.execute(''' INSERT into likePosts values (%s, %s) ''', (userID, blogID,))
	
def main(blogID, commentContent):
	conn = cursor(DATABASE)
	insert_new_posts(conn, blogID, commentContent)

if __name__ == '__main__':
	print main(sys.argv[1], sys.argv[2])
