import os
import time
import sys
import subprocess
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode

getmail = "echo 's 1 newmail.txt' | mailx"
delmail = "rm -rf newmail.txt"
getLOG_ID = "grep LOG_ID newmail.txt"
getVA_DATE = "grep VA_DATE newmail.txt"
getEMAIL_ADDRESS = "grep EMAIL_ADDRESS newmail.txt"
getNAME_1 = "grep NAME_1 newmail.txt"
getNAME_2 = "grep NAME_2 newmail.txt"
getPHYSADDRESS = "grep PHYS_ADDRESS newmail.txt"
getCITY = "grep CITY newmail.txt"
getNUMBER_TICKETS = "grep NUMBER_TICKETS newmail.txt"

while True:
        try:
                subprocess.check_output(getmail, shell=True)
                print("New order found")
                LOG_ID_TMP = subprocess.check_output(getLOG_ID, shell=True, universal_newlines=True)
                VA_DATE_TMP = subprocess.check_output(getVA_DATE, shell=True, universal_newlines=True)
                EMAIL_ADDRESS_TMP = subprocess.check_output(getEMAIL_ADDRESS, shell=True, universal_newlines=True)
                NAME_1_TMP = subprocess.check_output(getNAME_1, shell=True, universal_newlines=True)
                NAME_2_TMP = subprocess.check_output(getNAME_2, shell=True, universal_newlines=True)
                PHYSADDRESS_TMP = subprocess.check_output(getPHYSADDRESS, shell=True, universal_newlines=True)
                CITY_TMP = subprocess.check_output(getCITY, shell=True, universal_newlines=True)
                NUMBER_TICKETS_TMP = subprocess.check_output(getNUMBER_TICKETS, shell=True, universal_newlines=True)

                LOG_ID = LOG_ID_TMP.split(']')[1]
                VA_DATE = VA_DATE_TMP.split(']')[1]
                EMAIL_ADDRESS = EMAIL_ADDRESS_TMP.split(']')[1]
                NAME_1 = NAME_1_TMP.split(']')[1]
                NAME_2 = NAME_2_TMP.split(']')[1]
                PHYSADDRESS = PHYSADDRESS_TMP.split(']')[1]
                CITY = CITY_TMP.split(']')[1]
                NUMBER_TICKETS = NUMBER_TICKETS_TMP.split(']')[1]
                print("Opening connection to sql server")
                def insertVariblesIntoTable(LOG_ID, VA_DATE, EMAIL_ADDRESS, NAME_1, NAME_2, PHYSADDRESS, CITY, NUMBER_TICKETS):
                    try:
                        connection = mysql.connector.connect(host='localhost',
                                                             database='ksgtickets',
                                                             user='ksgtickets',
                                                             password='12testpassword34')
                        cursor = connection.cursor()
                        mySql_insert_query = """INSERT INTO orders (LOG_ID, VA_DATE, EMAIL_ADDRESS, NAME_1, NAME_2, PHYSADDRESS, CITY, NUMBER_TICKETS)
                                                VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """
                        recordTuple = (LOG_ID, VA_DATE, EMAIL_ADDRESS, NAME_1, NAME_2, PHYSADDRESS, CITY, NUMBER_TICKETS)
                        cursor.execute(mySql_insert_query, recordTuple)
                        connection.commit()
                        print("Record inserted successfully into orders table")
                    except mysql.connector.Error as error:
                        print("Failed to insert into MySQL table {}".format(error))
                    finally:
                        if (connection.is_connected()):
                            cursor.close()
                            connection.close()
                            print("MySQL connection is closed")
                insertVariblesIntoTable(LOG_ID, VA_DATE, EMAIL_ADDRESS, NAME_1, NAME_2, PHYSADDRESS, CITY, NUMBER_TICKETS)
                os.system(delmail)

        except subprocess.CalledProcessError:
                print("Nothing to do, sleep")
        time.sleep(5)