import mysql.connector

sql = mysql.connector.connect(
  host="localhost",
  user="root",
  password="***"
)

sqlcursor = sql.cursor()

def database():
    #add options to create a table, alter a table, select all columns from a table and show tables
    choice = input("select option: \n[1]Create Table \n[2]Alter Table \n[3]View Table \n[4]Insert into Table \n[5]Update Table \n[6]Exit")
    if choice == "1":
        table_name = input("Enter table name: ")
        columns = []
        while True:
            column_name = input("Enter column name (type 'done' to finish): ")
            if column_name.lower() == 'done':
                break
            else:
                data_type = input("Enter data type for the column: ")
            columns.append({column_name} + ' ' + {data_type})
        create_table_query = "CREATE TABLE {} ({})".format(table_name , ', '.join(columns))
        try:
            sqlcursor.execute(create_table_query)
            print("Table " + {table_name} + " created successfully.")
        except:
            print("\nError Encountered try again \n")
        finally:
            database()

    elif choice == "2":
        table_name = input("Enter table name to alter: ")
        alter_query = input("Enter the ALTER TABLE query: ")
        try:
            sqlcursor.execute(f"ALTER TABLE {table_name} {alter_query}")
            print("Table " + {table_name} + " altered successfully.")
        except:
            print("\nError Encountered try again \n")
        finally:
            database()

    elif choice == "3":
        table_name = input("Enter table name to select from: ")
        try:
            sqlcursor.execute("SELECT * FROM " + {table_name})
            result = sqlcursor.fetchall()
            if result:
                print("Data from table " + {table_name} + ":")
                for row in result:
                    print(row)
            else:
                print("No data found in table " + {table_name} + " .")
        except:
            print("\nError Encountered try again \n")
        finally:
            database()
    elif choice == "4":
        table_name = input("Enter table name to insert data into: ")
        query = "INSERT INTO {} VALUES (".format(table_name)
        values = []
    
        while True:
            value = input("Enter values seperated by comma for a column (type 'done' to finish): ")
            if value.lower() == 'done':
              break
            else:
                value_split = value.split(",")
                values.append(value_split)
        
        query = query + ', '.join(['%s' for _ in values])
        query = query + ")"
        
        try:
            print(query,values)
            sqlcursor.executemany(query, values)
            print(f"Data inserted into '{table_name}' successfully.")
        except:
                print("Error:")
        else:
            sql.commit()
        finally:
            database()
        
    elif choice == "5":
        table_name = input("Enter table name to update data: ")
        query = "UPDATE customers SET %s = %s WHERE %s = %s"
        val = []
        val = val.append(input("Enter Column to set value into:\n"))
        val = val.append(input("Enter value to set:\n"))
        val = val.append(input("Enter Column where value meets certain condition:\n"))
        val = val.append(input("Enter value:\n"))

        sqlcursor.execute(query,val)

        sql.commit()

    elif choice == "6":
        main()

    else:   
        print("Please Input a Valid Option")
        database()


    pass


def main():
    option = int(input("Select an option: \n[1]use database \n[2]create database \n[3]show databases \n[4]Exit"))
    while option == 1:
        db = input("\n Enter database to use: \n")
        try:
            sqlcursor.execute("USE {}".format(db))
        except:
            print("\n Invalid Database \n")
        else:
            print("\n {} database successfully connected \n".format(db))
            database()
            break
    while option == 2:
        db = input("\n Create database name: \n")
        try:
            sqlcursor.execute("CREATE DATABASE " + db)
        except:
            print("\n Error Occured \n")
        else:
            print("\n Database Created \n")
            database()
            break
    if option == 3:
        sqlcursor.execute("SHOW DATABASES")
        for output in sqlcursor:
            print(output)
        main()

    if option == 4:
        pass

    main()
    
    
    

main()
print("Complete")