import sqlite3
import sys
import time
import datetime
#Imports all above ^

try:
    conn = sqlite3.connect("School.db")
    print("Database created / connected")
except:
    print("Database has already been created / an error occured")

cursor = conn.cursor()

try:
    cursor.execute("CREATE TABLE Students"
                   "(StudentID INTEGER PRIMARY KEY NOT NULL,"
                    "Surname TEXT NOT NULL,"
                    "Forename TEXT NOT NULL,"
                    "DateOfBirth DATE,"
                    "HomeAddress TEXT,"
                    "HomePhoneNumber TEXT,"
                    "GENDER TEXT NOT NULL,"
                    "TutorGroup TEXT NOT NULL,"
                    "SchoolEmail TEXT)")

    cursor.execute("CREATE TABLE Students_year"
                   "(StudentsID INTEGER PRIMARY KEY NOT NULL,"
                   "Surname TEXT NOT NULL,"
                   "Forename TEXT NOT NULL,"
                   "Year INTEGER NOT NULL,)")

    print("Table succesfully created")
except:
    print("Table already exists / couldn't be created")


#cursor.execute("INSERT INTO Studentsyear (StudentID, Surname, Forename) SELECT StudentID, Surname, Forename FROM Students")
#INSERT INTO Studentsyear (StudentID, Surname, Forename) SELECT StudentID, Surname, Forename FROM Students
#print("Values moved succesfully")
##except:
##    print("Error values couldn't be moved")
###Creating tables / databases ^

print("\n")

def login():
    tries = 0
    while tries != 3:
        username = input("Please enter your usernname: ")
        password = input("Please enter your password: ")
        username = username.lower()
        if username == "tobias" and password == "shooter":
            print("Access to system granted")
            displaymenu()
            break
        else:
            print("Error username or password incorrect please try again")
            tries = tries + 1
            if tries == 3:
                print("Error too many valid inputs, program will close")
                time.sleep(5)
                quit()

def displaymenu():
    print("\nMENU")
    print("1) Enter and store values into a table?")

    menuchoice()

def validate_choice(num):
    while True:
        try:
            num = int(num)
            break
        except ValueError:
            num = input("Please enter a number: ")
    while num <1 or num >7:
        num = input ("Enter a number valid on the list")
    return num

def validate_number(num):
    while True:
        try:
            num = int(num)
            break
        except ValueError;
        num = input("Please enter a number: ")
    return num

def validate_string(a):
    while a == "" or len(a) < 2:
        a = input("Please enter a correct value: ")
    return a

def validate_gender(g):
    g = g.upper()
    while g != "M" or g != "F":
        g = input("Please enter a gender: ")
    return g

def menuchoice():
    choice = input("Please enter a number for your choice")
    choice = validate_choice(choice)
    if choice == 1:
        enter_values()

def enter_values():
        StudentID = input("StudentID: ")
        StudentID = validate_number(StudentID)

        Surname = input("Surname: ")
        Surname = validate_string(Surname)

        Forename = input("Forename: ")
        Forename = validate_string(Forename)

        print("Enter Date Of Birth")
        DateOfBirth_d = input("Day: ")
        DateOfBirth_d = validate_number(DateOfBirth_d)
        DateOfBirth_m = input("Month: ")
        DateOfBirth_m = validate_number(DateOfBirth_m)
        DateOfBirth_y = input("Year: ")
        DateOfBirth_y = validate_number(DateOfBirth_y)
        DateOfBirth = datetime.date(DateOfBirth_y, DateOfBirth_m, DateOfBirth_d)

        HomeAddress = input("Home Address: ")
        HomeAddress = validate_string(HomeAddress)

        HomeNumber = input("Home Number: ")
        HomeNumber = validate_string(HomeNumber)

        Gender = input("Gender M/F: ")
        Gender = validate_gender(Gender)

login()