import mysql.connector as sql
con=sql.connect(host = "localhost", user = "root", password = "devi")
cur=con.cursor()

def startup():
       cur.execute("CREATE DATABASE IF NOT EXISTS white_lotus_society")
       cur.execute("USE white_lotus_society")
       cur.execute("CREATE TABLE IF NOT EXISTS userdata (srno int, uname varchar(50), UID int PRIMARY KEY, ucountry varchar(30) default 'India',  upass varchar(50), director char(1) default 'N')")
       cur.execute("CREATE TABLE IF NOT EXISTS film (fid int PRIMARY KEY, f_name varchar(100), f_country varchar(10), dir_name varchar(50))")
       cur.execute("CREATE TABLE IF NOT EXISTS ratings (f_name varchar(100),fid int PRIMARY KEY, audience_rating int default 5, increment int default 1 , lastrate varchar(20000) )")
       cur.execute("create table if not exists checker(fid int PRIMARY KEY, ratedby varchar( 65500))")
       
def addfilm():
       f_id=int(input("Enter film id"))
       f_name= input("Give film name: ")
       f_country=input("Enter country of origin: ")
       f_director=input("Enter name of director: ")
       cur.execute("insert into film values({},'{}','{}','{}')".format(f_id,f_name,f_country,f_director))
       cur.execute("insert into ratings values('{}',{},5,1,0)".format(f_name,f_id))
       cur.execute("insert into checker values({},'{}')".format(f_id,""))
       con.commit()
       print("added new film: ", f_name , "\n")
       
def register():
       global regisconfirm
       try:
              name = input("Enter your name: ")
              upass = input("Enter password of choice: ")
              choice = input("Are you a director?: ")
              if choice.lower()=="y":
                     f = open("uid.cnf","r")
                     s= f.readlines()
                     print("Your UID is: ",s[0])
                     uid = s[-1]
                     srno =uid
                     ucountry = "India"
                     cur.execute('insert into userdata values ({},"{}",{},"{}","{}", "Y")'.format(srno,name,uid, ucountry, upass))
                     f = open("uid.cnf","w")
                     nid = str(int(uid)+1)
                     f.write(nid)
                     regisconfirm = 1
                     regisconfirm=2
              else:
                     f = open("uid.cnf","r")
                     s= f.readlines()
                     print("Your UID is: ",s[0], "\n")
                     uid = s[-1]
                     srno =uid
                     ucountry = "India"
                     cur.execute('insert into userdata values ({},"{}",{},"{}","{}", "N")'.format(srno,name,uid, ucountry, upass))
                     con.commit()
                     f.close()
                     f = open("uid.cnf","w")
                     nid = str(int(uid)+1)
                     f.write(nid)
                     regisconfirm = 1
       finally:
              f.close()
              
def increment():
       try:
              f= open("uid.cnf","r")
              print("User IDs exist", "\n")
              f.close()
              
       except FileNotFoundError:
              print("User IDs do not exist")
              cur.execute('insert into film values(342, "Interstellar", "USA","Christopher Nolan")')
              cur.execute('insert into film values(453, "Parasite", "KOR","Bong Joon-ho")')
              cur.execute('insert into film values(124, "Panchatanthiram", "IND","K S Ravikumar")')
              cur.execute('insert into film values(544, "RRR", "IND","S S Rajamouli")')
              cur.execute('insert into film values(706, "Spider-Man", "USA","Sam Raimi")')
              cur.execute('insert into film values(499, "Chakravyuham: The Trap", "IND","Chetkuri Madhusudhan")')
              cur.execute('insert into film values(899, "Spider-Man 2", "USA","Sam Raimi")')
              
              cur.execute('insert into ratings values("Interstellar", 342, 5, 1, NULL)')
              cur.execute('insert into ratings values("Parasite", 453, 5, 1, NULL)')
              cur.execute('insert into ratings values("Panchatanthiram", 124, 5, 1, NULL)')
              cur.execute('insert into ratings values("RRR", 544, 5, 1, NULL)')
              cur.execute('insert into ratings values("Spider-Man", 706, 5, 1, NULL)')
              cur.execute('insert into ratings values("Chakravyuham: The Trap", 499, 5, 1, NULL)')
              cur.execute('insert into ratings values("Spider-Man 2", 899, 5, 1, NULL)')

              cur.execute('insert into checker values(342,"")')
              cur.execute('insert into checker values(453,"")')
              cur.execute('insert into checker values(124,"")')
              cur.execute('insert into checker values(544,"")')
              cur.execute('insert into checker values(706,"")')
              cur.execute('insert into checker values(499,"")')
              cur.execute('insert into checker values(899,"")')
              con.commit()
              f = open("uid.cnf","w")
              f.write("0")
              f.close()
#7-10-23

def rate():
       global userr
       film=input("Enter name of film you wish to rate: ")
       cur.execute("select * from film")
       fetched = cur.fetchall()
       cur.execute("select * from ratings")
       rates = cur.fetchall()
       #print(rates)
       #print(fetched)
       for i in fetched:
              #print(i)
              if i[1].lower() == film.lower():
                     fids = i[0]
                     #print(i)
                     print(i[1], " ", "Film Found.", "\n")
                     rating = int(input("Enter film rating: "))
                     #print(i[1])
                     if rating<=5 and rating>=0:
                              #print(i[0])
                              cur.execute("select audience_rating, increment , lastrate from ratings where fid = {}".format(i[0]))
                              fid = i[0]
                              x=cur.fetchall()
                              cur.execute("select * from checker where fid = {}".format(i[0]))
                              y = cur.fetchall()
                              #print(y)
                              listss =  y[0][1]
                              #print(listss)      
                              #print(x)
                              if str(listss) == "":
                                     val = str(userr)+ ","
                                     rate = int(x[0][0]) + rating
                                     inc = int(x[0][1])+ 1
                                     #print(val)
                                     query = "update ratings set audience_rating={} ,increment = {}, lastrate = '{}' where fid={}".format(int(rate), int(inc),str(val), int(fid))
                                     cur.execute(query)
                                     cur.execute("update checker set ratedby = '{}' where fid = {}".format(val,fid))
                                     con.commit()
                              elif   str(listss) != "":
                                     temp = listss.split(",")
                                     #print(temp)
                                     for i in temp:
                                            if str(userr) == str(i):
                                                   print("User exists in records, cannot rate!")
                                                   raise KeyboardInterrupt
                                            else:
                                                   valt = str(userr) + ","
                                                   valt = valt + str(listss)
                                                   rate = int(x[0][0]) + rating
                                                   inc = int(x[0][1])+ 1
                                                   #print(valt)
                                                   query = "update ratings set audience_rating={} ,increment = {}, lastrate = '{}' where fid={}".format(int(rate), int(inc),str(valt), fid)
                                                   cur.execute(query)
                                                   cur.execute("update checker set ratedby = '{}' where fid = {}".format(valt,fid))
                                                   con.commit()
                     elif rating >5 or rating <0:
                            print("Please use common sense!")
                     else:
                            pass
              

def search():
       name = input("Enter film name: ")
       cur.execute("select * from film")
       x= cur.fetchall()
       for i  in x:
              if i[1].lower()== name.lower():
                     print("Film Found")
                     print("Fetching", "\n")
                     query = "select * from film,ratings where film.fid = ratings.fid and film.fid = {}".format(int(i[0]))
                     cur.execute(query)
                     data = cur.fetchall()
                     #print(data)
                     print("Film Name is: ",data[0][1], "\n", "Film UID (For internal reference only): ", data[0][0])
                     print("Film production in: ",data[0][2])
                     print("Film Director: ",data[0][3])
                     print("White Lotus Rating by Users: ", (int(data[0][6])//int(data[0][7])), "/5" , " by ", int(data[0][7]),  " users", "\n", sep=" ")
                     
def update():
       print("If any value remains un-updated, type previous values.")
       f_id=int(input("Enter film id to be updated: "))
       cur.execute("select * from film where fid = {}".format(int(f_id)))
       x = cur.fetchall()
       print("ID is: ",x[0][0], "Film Name: ",x[0][1], "Country: ",x[0][2], "Director: ",x[0][3])
       f_name= input("Give updated film name: ")
       f_country=input("Enter country of origin: ")
       f_director=input("Enter name of director: ")
       cur.execute("update film set  f_name = '{}', f_country='{}', dir_name = '{}' where fid = {}".format(f_name,f_country,f_director, f_id))
       con.commit()
                
def signin():
       global checker, username, userr
       checker = 0
       username =""
       uid = int(input("Enter UID: "))
       userr = uid
       upass = input("Enter your password: ")
       query = "select * from userdata where uid = {} and upass = '{}'".format(uid,upass)
       #print(query)
       cur.execute(query)
       data = cur.fetchall()
       #print(data)
       try:
              if data ==[]:
                     print("Check UID and password.")
                     checker = 1
                     username = ""
                     raise (KeyboardInterrupt)
              else:
                     for i in data:
                            username = i[1]
                            pwd = i[4]
                            if pwd == upass:
                                   print("Welcome Shri",username,"!", sep = " ")
                            else:
                                   raise KeyboardInterrupt
       except KeyboardInterrupt:
              print("Please sign-in again")
       
              


startup()
increment()
print("-------------------White Lotus Film Rating System-------------------")
try:
       while True:
              choice = input("Would you like to Register/Sign-In: ")
              if choice.lower()=="register":
                     register()
                     if regisconfirm == 1 or regisconfirm ==2:
                            pass
              elif choice.lower()=="signin" or choice.lower()=="sign-in":
                     signin()
                     signedin = 1
                     break
              else:
                     pass
       while True:
              if checker != 1:
                     if signedin == 1:
                            #checking director
                            query = "select director from userdata where uid = {}".format(userr)
                            cur.execute(query)
                            x = cur.fetchall()
                            #print(str(x[0]))
                            if str(x[0])== "('Y',)":
                                   qn = input("Do you wish to add film (add) or update a film(update) or exit (exit)?: ")
                                   if qn.lower() == "add":
                                          addfilm()
                                   elif qn.lower() == "update":
                                          update()
                                   elif qn.lower() == "exit":
                                          raise KeyboardInterrupt
                                   else:
                                          pass
                            elif str(x[0])== "('N',)":
                                   uchoice = input("Do you wish to search (search) or rate(rate) or exit(exit)?: ")
                                   if uchoice.lower()=="search":
                                          search()
                                   elif uchoice.lower() == "rate":
                                          rate()
                                   elif uchoice.lower()== "exit":
                                          raise KeyboardInterrupt
                                   else:
                                          pass
                     
except KeyboardInterrupt:
       print("Exiting.......................")                     
finally:
       print("----------------------------Thank You for using श्वेत कमलम्  ! | வெள்ளை தாமரை பயன்படுத்தியதற்கு நன்றி !----------------------------")