import cx_Oracle
import sys
import random
import datetime
logq = ("select count(*) "
	"from users u "
	"where u.usr =:u_id and u.pwd =:psw");
r1 = (	"select count(*) "
	"from users u "
	"where u.usr =:u_id")
r2 = ("insert into users "
"values (:u_id, :u_psw, :u_name, :u_email, :u_city, :u_timezone)");
re1 = ("insert into retweets "
"values (:u_id, :t_id,:r_date) ");
tweet_menu = ("select distinct t.tid, t.writer, t.tdate, t.text "
"from follows f, tweets t,retweets r "
"where f.flwer =:u_id "
"and (f.flwee = t.writer or (f.flwee = r.usr and r.tid=t.tid)) "
"order by t.tdate desc ");
reply_number=( "select count(*) from tweets where tweets.replyto =:t_id ")
retweets_number=( "select count(*) from retweets where retweets.tid =:t_id ")
c1 = (	"select count(*) "
	"from tweets t "
	"where t.tid =:t_id")
hashq =("select count(*) "
	"from hashtags h "
	"where h.term =:h_t ")
hasha = ("insert into hashtags "
 "values (:h_t ) ")
c2=("insert into tweets "
 "values (:t_id, :u_id, :t_date, :t_text,:r_to)");
m1=("insert into mentions "
 "values (:t_id, :m_term ) ")
search_t=("select distinct t.tid, t.writer, t.tdate, t.text "
	"from tweets t, hashtags h, mentions m "
	"where t.text LIKE :key_word OR (m.term LIKE :key_word and m.tid = t.tid) "
	"order by t.tdate desc " )
search_u_n = ( "SELECT DISTINCT u.usr,u.name,u.city "
		"FROM users u "
		"WHERE u.name LIKE :key_word "
		"ORDER BY length(trim(u.name)) asc ")
search_u_c = ( "SELECT DISTINCT u.usr,u.name,u.city "
		"FROM users u "
		"WHERE u.city LIKE :key_word "
		"ORDER BY length(trim(u.city)) asc ")
u_number = ("SELECT COUNT(distinct t.tid), count(distinct f1.flwee), count(distinct f2.flwer) "
	"FROM tweets t,follows f1, follows f2 "
	"WHERE t.writer =:new_id and f1.flwer =:new_id and f2.flwee =:new_id ")
u_tweets=("SELECT DISTINCT t.tid, t.tdate, t.text "
	"FROM tweets t, users u "
	"WHERE t.writer =:new_id "
	"ORDER BY t.tdate desc ")
u_follow = ("insert into follows values (:u_id,:new_id,:new_date)")
list_follow=("select u.usr,u.name "
	"FROM users u, follows f "
	"where f.flwee =:u_id "
	"and f.flwer =u.usr ")
list_list=("select * "
	"FROM lists "
	"Where lists.owner =:u_id")
list_add=("insert into includes values (:l_name,:new_id) ")
list_del=("delete from includes where member =:de_id and lname =:l_name ")
list_list_in=("SELECT i.lname "
	"FROM includes i "
	"WHERE i.member =:u_id ")
list_create=("insert into lists values (:l_name, :u_id) ")
def login(connection,curs):
	reg=input("Login as register?[Y/N](exit:E):")
	flag=True
	if reg=="E":


		flag=False
	elif reg=="Y":
		u_id=input("Please enter your user id:")
		psw=input("Please enter your psw:")
		para={'u_id':u_id,'psw':psw}

		curs.execute(logq,para);
		vaild=(curs.fetchall())[0][0]
		if(vaild==1):
			print("Aprove")
			flag=menu(connection,curs,u_id)

		else:
			print("Invaild password or u_id")

	elif reg == "N":
		unique=1
		while (unique == 1):
			u_id = random.randint(0,1000000);
			par={'u_id':u_id}
			curs.execute(r1,par)
			unique=(curs.fetchall())[0][0]

		u_name=input("Please enter your name : ")
		u_psw=input("Please enter your password : ")
		u_email=input("Please enter your email : ")
		u_city=input("Please enter your city : ")
		u_timezone=input("Please enter your timezone : ")
		param = {"u_id": u_id, "u_psw": u_psw, "u_name": u_name, "u_email": u_email, "u_city":u_city, "u_timezone": u_timezone};
		curs.execute(r2,param);
		print("Your id is :",u_id);
		connection.commit();

	return flag
def menu(connection,curs,u_id):
	flag=True
	print("The recent tweets:[ID] [Writer] [Time] [TexT]")
	param={'u_id':u_id}
	curs.execute(tweet_menu,param)
	move_on=1
	while (move_on==1):

		for i in range(0,5):
			r=curs.fetchone()


			if (r==None):
				print("Thats all")
				break
			print(r[0],r[1],r[2],r[3])
		if(r==None):
			break
		move_on= int(input("Type 1 to see more:"))
		print("")


	while(flag==True):
		print("")
		action=input("Please select one of the action:1.check the tweet;2.Search for a tweet;"
		"3.Search for user;4.Compose a tweet;5.List followers;6.manage lists;E.exit: ");
		print("")
		if(action=='1'):
			check_tweet(connection,curs,u_id)
		elif(action=='2'):
			search_for_tweet(connection,curs,u_id)
		elif(action=='3'):
			search_for_user(connection,curs,u_id)

		elif(action=='4'):

			compose_tweet(connection,curs,u_id)
		elif(action=='5'):
			list_follower(connection,curs,u_id)
		elif(action=='6'):
			manage_list(connection,curs,u_id)
		elif(action=='E'):
			flag=False
	return flag
def search_for_tweet(connection,curs,u_id):
	keyword=input("Please enter the keyword for search: ")
	keys=keyword.split(" ")
	for key in keys:
		paras={'key_word':'%'+key+'%'}
		curs.execute(search_t,paras)
		move_on=1
		while (move_on==1):

			for i in range(0,5):
				r=curs.fetchone()


				if (r==None):
					print("Thats all")
					break
				print(r[0],r[1],r[2],r[3])
			if(r==None):
				break
			move_on= int(input("Type 1 to see more:"))
			print("")
	check=input("Check any tweet?[Y/N] :")
	if(check=='Y'):
		check_tweet(connection,curs,u_id)
def manage_list(connection,curs,u_id):
	selection=input("Please choose the action: 1.check lists I owned 2.check lists I am in 3.Create a list 4.Manage list: ")
	if(selection=='1'):
		paral={'u_id':u_id}
		curs.execute(list_list,paral)
		rows= curs.fetchall()
		for row in rows:
			print(row[0])
	elif(selection=='2'):
		paral={'u_id':u_id}
		curs.execute(list_list_in,paral)
		rows= curs.fetchall()
		for row in rows:
			print(row[0])
	elif(selection=='3'):
		lname=input("Please input the list name you want to create:")
		parac={'u_id':u_id,'l_name':lname}
		curs.execute(list_create,parac)
		connection.commit()
		print("List created!")
	elif(selection=='4'):
		lname=input("Please input the list name you want to manage:")
		ad=input("Please input the action (D) Delete member (A) Add Member")
		if(ad=='A'):
			new_id=int(input("Please input the Member id:"))
			paraa={'l_name':lname,'new_id':new_id}
			curs.execute(list_add,paraa);
			connection.commit()
			print("Add success")
		elif(ad=='D'):
			de_id=int(input("Please input the Member id:"))
			parad={'l_name':lname,'de_id':de_id}
			curs.execute(list_del,parad)
			connection.commit()
			print("Delete success")
def search_for_user(connection,curs,u_id):
	keyword=input("Please enter the keyword for search: ")
	parasun={'key_word':'%'+keyword+'%'}
	curs.execute(search_u_n,parasun)
	move_on=1
	while (move_on==1):

		for i in range(0,5):
			r=curs.fetchone()


			if (r==None):

				break
			print(r[0],r[1],r[2])
		if(r==None):
			break
		move_on= int(input("Type 1 to see more:"))
	curs.execute(search_u_c,parasun)
	move_on=1
	while (move_on==1):
		for i in range(0,5):
			r=curs.fetchone()

			if (r==None):
				print("Thats all")
				break
			print(r[0],r[1],r[2])
		if(r==None):
			break
		move_on= int(input("Type 1 to see more:"))
	check=input("Check any users?[Y/N] :")
	if(check=='Y'):
		check_user(connection,curs,u_id,1)
def check_user(connection,curs,u_id,mode):
	new_id=input("Please input the id of the user: ");
	param={'new_id':new_id}
	curs.execute(u_number,param)
	r=curs.fetchall();

	print("Tweets: ",r[0][0]," Flowee: ",r[0][1],"Floweer ", r[0][2])
	curs.execute(u_tweets,param);
	print("Resent tweets: ")
	print("")
	move_on=1
	while (move_on==1):
		for i in range(0,3):
			r=curs.fetchone()

			if (r==None):
				print("Thats all")
				break
			print(r[0],r[1],r[2])
		if(r==None):
			break
		move_on= int(input("Type 1 to see more:"))
	if(mode==1):
		follow=input("Follow ther user?[Y/N] :")
		if(follow=='Y'):
			paraf={'u_id':u_id,'new_id':new_id,'new_date':datetime.datetime.now()}
			curs.execute(u_follow,paraf)
			connection.commit()
			print("Follow success! ")
def compose_tweet(connection,curs,u_id):
	unique=1
	while (unique == 1):
		newt_id = random.randint(0,1000000);
		par={'t_id':newt_id}
		curs.execute(c1,par)
		unique=(curs.fetchall())[0][0]
	text=input("Please input the text: ")
	s=text.split(" ")
	hashtags=[]
	for i in s:
		if('#'in i):
			hashtags.append(i);
	for p in hashtags:
		p=p[1:]
		parah={'h_t':p}
		curs.execute(hashq,parah)
		exist=(curs.fetchall())[0][0]
		if(exist==0):
			curs.execute(hasha,parah)
	connection.commit();
	parat={'t_id':newt_id,'u_id':u_id,'t_date':datetime.datetime.now(),'t_text':text,'r_to':None}
	curs.execute(c2,parat);
	connection.commit()
	for m in hashtags:
		m=m[1:]
		param={'t_id':newt_id,'m_term':m}
		curs.execute(m1,param)
	connection.commit()
	print("Compose success in id: ",newt_id)
def list_follower(connection,curs,u_id):
	paralf={'u_id':u_id}
	curs.execute(list_follow,paralf)
	rows= curs.fetchall()
	for row in rows:
		print(row[0],row[1])
	check=input("Check any users?[Y/N] :")
	if(check=='Y'):
		check_user(connection,curs,u_id,0)


def check_tweet(connection,curs,u_id):
	t_id=input("Please input the id of the tweet: ");
	param={'t_id':t_id}
	curs.execute(reply_number,param)
	r=curs.fetchall();
	print("")
	print("replies:",r[0][0])
	curs.execute(retweets_number,param)
	r=curs.fetchall();
	print("retweets:",r[0][0])
	print("")
	selection=input("Do you want reply/retweets it? [retweets[1]/reply[2]]:");
	if(selection=='2'):
		unique=1
		while (unique == 1):
			newt_id = random.randint(0,1000000);
			par={'t_id':newt_id}
			curs.execute(c1,par)
			unique=(curs.fetchall())[0][0]
		text=input("Please input the text: ")
		s=text.split(" ")
		hashtags=[]
		for i in s:
			if('#'in i):
				hashtags.append(i);
		for p in hashtags:
			p=p[1:]
			parah={'h_t':p}
			curs.execute(hashq,parah)
			exist=(curs.fetchall())[0][0]
			if(exist==0):
				curs.execute(hasha,parah)
		connection.commit();
		parat={'t_id':newt_id,'u_id':u_id,'t_date':datetime.datetime.now(),'t_text':text,'r_to':t_id}
		curs.execute(c2,parat);
		connection.commit()
		for m in hashtags:
			m=m[1:]
			param={'t_id':newt_id,'m_term':m}
			curs.execute(m1,param)
		connection.commit()
		print("Reply success in id: ",newt_id)
	elif(selection=='1'):
		parar={'u_id':u_id,'t_id':t_id,'r_date':datetime.datetime.now()}
		curs.execute(re1,parar)
		print("retweets success!")
		connection.commit()
def main():
    # my code here
	name=input("Plesase enter the name for Oracle:")
	pw=input("Please enter the password:")
	connection = cx_Oracle.connect(name, pw, 'gwynne.cs.ualberta.ca:1521/CRS')
	curs = connection.cursor()
	loop=True
	while(loop==True):
		loop = login(connection,curs)
	curs.close
	connection.close()
	sys.exit()

if __name__ == "__main__":
    main()