import mysql.connector as mc
import os

cnx = mc.connect(host="localhost", user="alen", password="alen")
cur = cnx.cursor()

def sql_connected():
    if cnx.is_connected():
        return 1

def login(username, passwd):
    cur.execute("SELECT * FROM USER_LOGIN WHERE USERNAME='' AND PASSWORD=''".format(username, passwd))

    data = cur.fetchall()

    if any(data):
        return 1

    else:
        return 0

while True:
    print("1. Check the connection status of the program")
    print("2. Setting up the database and tables")
    print("3. Login to the Ebin Automobiles")
    print("4. Exit the program")

    ch = int(input("Enter your selection: "))

    if ch == 1:
        print("1. You selected to check the connection status of the program")
        c = sql_connected()

        if c == 1:
            print("Program successfully connected with the sql server")
            continue

        else:
            print("Connection to the sql server failed. Pls fix the bug and retry")
            break

    elif ch == 2:
        print("Set up the database and tables")
        cur.execute("CREATE DATABASE IF NOT EXISTS AUTO_MOBILE_SERVICE_STATION")
        cur.execute("USE AUTO_MOBILE_SERVICE_STATION")
        cur.execute("CREATE TABLE IF NOT EXISTS CUSTOMER_DETAILS(SNO INT PRIMARY KEY, CNAME VARCHAR(25), CDETAILS VARCHAR(30), CADDRESS VARCHAR(30), CPINCODE INT, CPURAMT INT, CDISK FLOAT)")

    elif ch == 3:
        user = input("Enter your login username: ")
        passwd = input("Enter your password: ")

        d = login(user, passwd)

        if d == 1:
            print("You successfully logged into the store")
            continue

        else:
            print("Invalid combination of username or password")
            print("Please try again")
            break

        while True:
            print("Welcome TO EK Automobile Services")
            print()
            print("1. Service station")
            print("2. Go back to previous menu")

            cn = int(input("Enter your selection: "))

            if cn == 1:
                print("Add a new customer to EK Services")

                sno = int(input("Enter the serial number: "))
                cname = input("Enter customer name: ")
                cdetails = input("Enter customer details: ")
                caddress = input("Enter customer address: ")
                cpincode = int(input("Enter customer address pincode"))
                puramt = int(input("Enter customer purchase amount: "))
                cdisc = float("Enter the discount amount: ")

                cur.execute("INSERT INTO CUSTOMER_DETAILS VALUES (str(sno), cname, cdetails, caddress, cpincode, puramt, cdisk)")

                cnx.commit()
                print("New customer added to store")


    elif ch == 4:
        print("Thank you for using me")
        quit()