import mysql.connector

import datetime

import constants

connection = mysql.connector.connect(user=constants.USERNAME,
                                     password=constants.PASSWORD,
                                     host=constants.HOST,
                                     database=constants.DB)

cursor = connection.cursor()

def vehicle_check(vehicle_no):
    global cursor
    cursor.execute("SELECT * FROM VEHICLES WHERE car_no='%s' " % (vehicle_no))
    result = len(cursor.fetchall())
    if(result==0):
        vehicle_add(vehicle_no)

def vehicle_add(vehicle_no):
    global cursor, connection
    company = input("Enter company name: ")
    model = input("Enter vehicle model: ")
    color = input("Enter vehicle color: ")
    cursor.execute("INSERT INTO vehicles VALUES('%s', '%s', '%s', '%s')" % (vehicle_no, company, model, color))
    connection.commit()

def employee_check(vehicle_no):
    global cursor
    cursor.execute("SELECT * FROM EMPLOYEES WHERE car_no='%s'" % (vehicle_no))
    result = len(cursor.fetchall())
    if(result==0):
        print("You are not registered as an employee.")
        non_employee_check(vehicle_no)

def non_employee_check(vehicle_no):
    global cursor
    cursor.execute("SELECT * FROM NONEMPLOYEES WHERE car_no='%s'" % (vehicle_no))
    result = len(cursor.fetchall())
    if(result==0):
        print("You are not registered in this parking lot yet.")
        non_employee_add(vehicle_no)
    else:
        pass # Print data

def non_employee_add(vehicle_no):
    global cursor, connection
    name = input("Enter your name: ")
    address = input("Enter your address: ")
    dob_inp = input("Enter your DOB(dd/mm/yyyy): ")
    dob = datetime.datetime.strptime(dob_inp, '%d/%m/%Y').date()
    cursor.execute("INSERT INTO NONEMPLOYEES (name,address, dob, car_no) VALUES('%s', '%s', '%s', '%s')" % (name, address, dob, vehicle_no))
    connection.commit()

def entry_vehicle(vehicle_no):
    global cursor, connection
    vehicle_check(vehicle_no)
    employee_check(vehicle_no)
    cursor.execute("SELECT spot from avail where available=true")
    spot = cursor.fetchall()
    if(spot!=None):
        spot = spot[0][0]
        print("You have been assigned spot %s" % spot)
        cursor.execute("UPDATE AVAIL SET AVAILABLE=false WHERE spot='%s'" % spot)
        connection.commit()
        t = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute("INSERT INTO PARKERS (intime, car_no, spot) VALUES('%s', '%s', '%s')" % (t, vehicle_no, spot))
        connection.commit()
    else:
        print("No parking spot available")

def exit_vehicle(vehicle_no):
    global cursor, connection
    cursor.execute("SELECT * from PARKERS WHERE car_no='%s'" % vehicle_no)
    result = cursor.fetchall()
    if(len(result)!=0):
        result = result[0]
        t = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute("UPDATE PARKERS SET OUTTIME='%s' WHERE log_no='%s'" % (t, result[0]))
        connection.commit()
        cursor.execute("UPDATE AVAIL SET AVAILABLE=true WHERE SPOT='%s'" % result[-1])
        connection.commit()
    print(result)

entry_vehicle('CG07LT2850')
exit_vehicle('CG07LT2850')