import mysql.connector                  #these lines import the necessary modules for this program to work.
from mysql.connector import errorcode
from tabulate import tabulate


try:
    db = mysql.connector.connect(user='ipass',password='ipass123',host='192.168.10.1',database='IPASSDB')
    #The line above sets the database, using the mysql connector it tries to connect to a distant database.
    c = db.cursor()
    while True:
        #The line beneath we are printing what the options are for the user.
        optie = input("Welke actie wilt u uitvoeren (1t/m3) \n 1. Alle product informatie opvragen. \n "
                      "2. Product aanpassen. \n 3. Product toevoegen")
        if optie == "1": #This if statement selects everything in the table producten, the loop puts each row in a tuple.
            c.execute("SELECT * FROM producten")
            rows = c.fetchall()
            product_tuples = []
            for row in rows:
                #print(row)
                product_tuples.append(row)
                #print(product_tuples)
            product_list = list(product_tuples) #with this line we make the tuple into a list
            #Down here we print the table we've made from the list. Because tabulate won't accept tuple.
            print(tabulate(product_list, headers=["typenummer","voorraad","prijs in euro","gewicht in gram",
                                                  "opslag in MB","afmeting lxbxh","fabrikant_id"],
                                                    tablefmt="grid"))
        elif optie == "2":
            pass
        elif optie == "3":
            #after option 3 is chosen, we ask for the details about the product the user wants to add.
            type_in = input("Wat is het typenummer dat u wilt toevoegen?")
            voorraad_in = input("Wat is de begin voorraad voor dit product?")
            gewicht_in = input("Wat is het gewicht van het product? (in gram)")
            prijs_iv = input("Wat is de prijs van dit product? (in euros)")
            afm_in = input("Wat zijn de afmetingen van dit product? (in cm, lxbxh)")
            poort_in = input("Welke poorten heeft dit product? (1 voor Gigabit, 2 voor FastEthernet)")
            fab_in = input("Wat is de fabrikant_id?")
            opslag_in = input ("Wat is de opslag van dit product? (in MB)")
            #add_product is a mysql line we will use.
            add_product = ("INSERT INTO producten"
                           "(typenummer, voorraad, prijs, gewicht, opslag, afmeting, fab_id) "
                            "VALUES(%s, %s, %s, %s, %s, %s, %s)")
            add_product_data = (type_in, voorraad_in, prijs_iv, gewicht_in, opslag_in, afm_in, fab_in)
            c.execute(add_product, add_product_data) #Combines the made variables from the questions with the line.
            db.commit()
            print("Information committed")
            continue

        else:
            print("Dit is geen geldige optie, keuze maken uit 1,2 of 3.")



#if the connection to the database failed, we several print statements the user could use
#for troubleshooting.
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Verkeerde user of wachtwoord")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Verkeerde database opgegeven, of deze bestaat niet")
    else:
        print(err)
else:
    print('sluit')

db.close()