# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import (connection)
import linecache
import re

cnx = connection.MySQLConnection(user='the_dhan', password = 'password', database='test')
cursor = cnx.cursor(buffered=True)

def Add_User(i):
    cnx = connection.MySQLConnection(user='the_dhan', password = 'password', database='test')
    c = 1
    while i > 0:
        print("Input the name of student %d to add." % c)
        name = raw_input()
        print("Input the key for the user.")
        key = raw_input()
        print("Input the Address of %s." %name)
        Address = raw_input()
        print("Input the Email of %s." %name)
        Email = raw_input()
        print("Input the Pin of %s." %name)
        Pin = raw_input()
        print("Input the Exp_Date for %s." %name)
        Exp = raw_input()
        print("Input the U_Number of %s." %name)
        U_num = raw_input()
        data = [U_num, key, Address, key, Pin, key, Email, key, Exp, key, name]
        curA = cnx.cursor(buffered=True)
        curA.execute("INSERT INTO Student (Name) VALUES ('%s')" %name)
        curA.execute("INSERT INTO Keeys (Name) values ('%s')" %name)
        curA.execute("UPDATE Keeys SET keey = AES_ENCRYPT(%s,'password') where Name = %s", (key,name))
        curA.execute("UPDATE Student SET U_Number = AES_ENCRYPT(%s, %s), Address = AES_ENCRYPT(%s, %s), Pin = AES_ENCRYPT( %s, %s ), Email = AES_ENCRYPT(%s, %s), Exp_Date = AES_ENCRYPT(%s,%s) WHERE Name = %s",(data))
        i=i-1
        c+=1
    cnx.commit()
    cursor.close()
    cnx.close()

def Search(pas):
    cursor.execute("Select Name from Keeys")
    data = cursor.fetchall()
    cursor.execute("Select AES_DECRYPT(keey, '%s') from Keeys" %pas)
    data2 = cursor.fetchall()
    print("Names of all students:")
    for row in data:
        print("%s" % row[0])
    print("The key for each student:")
    for rows in data2:
        print("%s" % rows[0])


def Deliver():
    students = [1, 2, 3]
    for number in students:
        invalid = 1
        while invalid == 1:
            print("Please enter the name of student %d" % number)
            name = raw_input()
            print("Please enter the key for %s" %name)
            key = raw_input()
            curA = cnx.cursor(buffered=True)
            curAb = cnx.cursor(buffered=True)
            curB = cnx.cursor(buffered=True)
            curA.execute("SELECT AES_DECRYPT(Email,%s), AES_DECRYPT(Address,%s) FROM Student WHERE Name = %s", (key,key,name))
            curAb.execute("SELECT AES_DECRYPT(Email,%s), AES_DECRYPT(Address,%s) FROM Student WHERE Name = %s", (key,key,name))
            cursor.execute("SELECT AES_DECRYPT(Address,%s) FROM Student WHERE Name = %s", (key, name))
            curB.execute("SELECT Name FROM Student")
            name1 = curB.fetchall()
            for item in name1:
                x = 0
                if name == item[0]:
                    x = 0
                    if not(curA.fetchone()[0]):
                        print("Key is wrong")
                        break
                    else:
                        print("Email: %s Address: %s" %curAb.fetchone())
                        file= open("/home/pi/Desktop/Recipients/student" + str(number),"w+")
                        file.write(name)
                        file.write('\n')
                        file.write(key)
                        file.write('\n')
                        file.write(str(cursor.fetchone()[0]))
                        file.close()
                        invalid = 0
                        break
                else:
                    x = 1
            if(x==1):
                print("Name:%s is not in the database " %name)

    cursor.close()
    cnx.close()

def Verify(Student):
    for num in Student:
        i = 3
        while i > 0:
            key = linecache.getline("/home/pi/Desktop/Recipients/student" + str(num), 2)
            name = linecache.getline("/home/pi/Desktop/Recipients/student" + str(num), 1)
            key = re.split('\n',key)[0]
            name = re.split('\n',name)[0]
            pin = raw_input("Please input the pin for %s\n" %name)
            cursor.execute("SELECT AES_DECRYPT(Pin,%s) FROM Student WHERE Name = %s", (key,name))
            real_pin = cursor.fetchone()[0]
            if pin == real_pin:
                print("Unlock.")
                break
            else:
                print("Incorrect Pin.")
                i-=1

print(" 1) Input a new users to the database.")
print(" 2) See the database results for the users and their keys.")
print(" 3) Deliver Packages.")
print(" 4) Recieve Package.")
choice = int(raw_input())
if (choice == 1):
    print("Enter the number of Students to add.")
    i = int(raw_input())
    Add_User(i)
elif (choice == 2):
    print("Input key for mailroom.")
    pas = raw_input()
    Search(pas)
elif (choice == 3):
    Deliver()
elif (choice == 4):
    Student = [3,1,2]
    Verify(Student)
else:
    print("Invalid choice")