import sqlite3 as lite
import sys
import datetime
from datetime import datetime

con = lite.connect('test.db')
#Title Screen asking for Login, Register or Exit
while True :
    TitleScreen = raw_input("Login, Register or Exit? ")
#Login Module
    if TitleScreen in ["Login", "login"] :
        while True:
            with con:
                cur = con.cursor()
                cur.execute("CREATE TABLE IF NOT EXISTS Users(user_id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, password TEXT NOT NULL)")
            LoginAns = raw_input('Login to existing user?')
            if LoginAns in ['Yes', 'y', 'yes', 'Y', "YES"]:
                LoginUser = raw_input('Username: ')
                LoginPassword = raw_input('Password: ')
                params = (LoginUser, LoginPassword)
                cur.execute("SELECT count(*) FROM Users WHERE username = ?", (LoginUser,))
                dataLogin = cur.fetchone()[0]
                if dataLogin == 1:
                    cur.execute("SELECT count(*) FROM Users WHERE password =?", (LoginPassword,))
                    dataPass = cur.fetchone()[0]
                    if dataPass == 1:
                        print('Successful login')
                        #Timetable Creation
                        cur.execute("SELECT user_id FROM Users WHERE username = ? ", (LoginUser,))

                        UserTuple = cur.fetchone()
                        CurrentUserID = UserTuple[0]

                        while True:
							cur.execute("CREATE TABLE IF NOT EXISTS Events(event_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, timetable_id INTEGER, user_id INTEGER NOT NULL, event_name TEXT NOT NULL, event_descript TEXT, location_id INTEGER, datetime_id INTEGER)")

							cur.execute("CREATE TABLE IF NOT EXISTS location(location_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, timetable_id INTEGER, user_id INTEGER NOT NULL, location_name TEXT NOT NULL)")

							cur.execute("CREATE TABLE IF NOT EXISTS DateTable(datetime_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, timetable_id INTEGER, user_id INTEGER NOT NULL, EventDateTime DATE NOT NULL)")


							TimetableAction = raw_input('Add new event, view existing event, delete existing event or exit?')
							if TimetableAction in ['Add new event', 'ADD NEW EVENT', 'add new event', 'add', 'new', 'new event', 'Add']:

								NewEventName = raw_input('Name of event: ')
								NewEventDescript = raw_input('Description of event: ')
								NewEventLocation = raw_input("Location of event: ")

								NewEventDateTime = str(raw_input('Input Year, Month, Day, Hour, Minute (Format = "y, m, d, H, M") '))
								try:
									dt_start = datetime.strptime(NewEventDateTime, '%Y, %m, %d, %H, %M')
								except ValueError:
									print "Incorrect Format"

								params = (CurrentUserID, NewEventLocation)
								cur.execute("INSERT INTO location VALUES(NULL, NULL, ?, ?)", params)
								LocationID = cur.lastrowid

								params = (CurrentUserID, dt_start)
								cur.execute("INSERT INTO DateTable VALUES(NULL, NULL, ?, ?)", params)
								DateTimeID = cur.lastrowid

								params = (CurrentUserID, NewEventName, NewEventDescript, LocationID, DateTimeID)
								cur.execute("INSERT INTO Events VALUES(NULL, NULL, ?, ?, ?, ?, ?)", params)

							elif TimetableAction in ['View existing event', 'View', 'view', 'existing', 'Existing', 'event', 'Event', 'VIEW EXISTING EVENT', 'view existing event']:

								SearchBy = raw_input('Search by Name or Date?')
								if SearchBy in ['Name', 'name', 'NAME']:

									SearchName = raw_input('Input Name ')

									cur.execute("SELECT event_name FROM Events WHERE event_name =?", (SearchName,))
									EventNameTuple = cur.fetchone()
									SearchedEventName = EventNameTuple[0]

									print SearchedEventName

									cur.execute("SELECT event_descript FROM Events WHERE event_name =?", (SearchName,))
									EventDescriptTuple = cur.fetchone()
									SearchedEventDescript = EventDescriptTuple[0]

									print SearchedEventDescript

									cur.execute("SELECT location_id FROM Events WHERE event_name =?", (SearchName,))
									LocationIDValueTuple = cur.fetchone()
									SearchedLocationID = LocationIDValueTuple[0]

									cur.execute("SELECT location_name FROM location WHERE location_id =?", (SearchedLocationID,))
									LocationNameTuple = cur.fetchone()
									SearchedLocationName = LocationNameTuple[0]

									print SearchedLocationName

									cur.execute("SELECT datetime_id FROM Events WHERE event_name =?", (SearchName,))
									DateTimeIDValueTuple = cur.fetchone()
									SearchedDateTimeID = DateTimeIDValueTuple[0]

									cur.execute("SELECT EventDateTime FROM DateTable WHERE datetime_id =?", (SearchedDateTimeID,))
									DateTimeTuple = cur.fetchone()
									SearchedDateTime = DateTimeTuple[0]

									print SearchedDateTime
									#PROBLEM
								if SearchBy in ['Date', 'date', 'DATE']:

									SearchDate = str(raw_input('Input Year, Month, Day, Hour, Minute (Format = "y, m, d, H, M") '))
								try:
									dt_end = datetime.strptime(SearchDate, '%Y, %m, %d, %H, %M')
								except ValueError:
									print "Incorrect Format"

									cur.execute("SELECT datetime_id FROM DateTable where EventDateTime =?", (dt_end,))

									print(cur.fetchone())

									DateTimeID2Value = cur.fetchone()
									SearchedDateTimeID2 = DateTimeID2Value[0]

									print SearchedDateTimeID2

							elif TimetableAction in ["Delete", "delete", "delete existing", "delete existing event", "Delete existing event", "Delete existing"]:

								DeleteEventName = raw_input('Input name of event to be delete ')

								cur.execute("Select location_id FROM Events WHERE event_name =?", (DeleteEventName,))
								DeleteEventLocationIDTuple = cur.fetchone()
								DeleteEventLocationID = DeleteEventLocationIDTuple[0]

								cur.execute("Select datetime_id FROM Events WHERE event_name =?", (DeleteEventName,))
								DeleteEventDateTimeIDTuple = cur.fetchone()
								DeleteEventDateTimeID = DeleteEventDateTimeIDTuple[0]

								cur.execute("Delete FROM Events WHERE event_name =?", (DeleteEventName,))
								cur.execute("Delete FROM location WHERE location_id =?", (DeleteEventLocationID,))
								cur.execute("Delete FROM DateTable WHERE datetime_id =?", (DeleteEventDateTimeID,))

								print "Successfully deleted"

							elif TimeTableAction in ["exit", "Exit", "EXIT"]:
								break
                    else:
                        print('Wrong password')
                else:
                    print('No such user exists')
            elif LoginAns in ['No', 'no', 'N', 'n', "NO"]:
                break
            else:
                print "Invalid answer"
#Register Module
    elif TitleScreen in ["Register", "register"] :
        with con:

            cur = con.cursor()
            cur.execute("CREATE TABLE IF NOT EXISTS Users(user_id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, password TEXT NOT NULL)")
            while True:
                RegisterAns = raw_input('Create new User? ')
                if RegisterAns in ["Yes", "Y", "yes", "y", "YES"]:
                    Newusername = raw_input('Create Username: ')
                    password = raw_input('Create password: ')
                    params = (Newusername, password)
                    cur.execute("SELECT count(*) FROM Users WHERE username = ?", (Newusername,))
                    dataRegister = cur.fetchone()[0]
                    if dataRegister == 1:
                        print('User already exists')
                    else:
                        cur.execute("INSERT INTO Users VALUES(NULL, ?, ?)", params)
                        print('Successfully registered')
                elif RegisterAns in ["No", "N", "n", "no", "NO"]:
                    break
                else:
                    print "Invalid input"
#Exit module
    elif TitleScreen in ["Exit", "exit"] :
        sys.exit()
    else :
        print "Invalid input"

cur.execute("CREATE TABLE Events(event_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL UNIQUE, event_name TEXT NOT NULL, event_descript TEXT, location_id INTEGER, time_id INTEGER, date_id INTEGER)")

cur.execute("CREATE TABLE location(location_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL UNIQUE, location_name TEXT NOT NULL, location_descript TEXT, )")

cur.execute("CREATE TABLE timetable(timetable_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, user_id INTEGER NOT NULL UNIQUE, event_name TEXT NOT NULL, date_id INTEGER, time_id INTEGER)")

cur.execute("CREATE TABLE DateTable(date_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, user_id INTEGER NOT NULL UNIQUE, EventDate DATE NOT NULL)")

cur.execute("CREATE TABLE time(time_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL UNIQUE, time datetime NOT NULL, event_id INT NOT NULL, date_id INT NOT NULL)")

B = raw_input("user ID")
C = raw_input("event name")
D = raw_input("event description")
E = raw_input("location id")
F = raw_input("time id")
G = raw_input("date id")


params = (B, C, D, E, F, G)


cur.execute("INSERT INTO Events VALUES(NULL, ?, ?, ?, ?, ?, ?)", params)