import mysql.connector
import requests
import os
from dotenv import load_dotenv
load_dotenv()

mydb = mysql.connector.connect(
          host="localhost",
          user=os.getenv("USER"),
          passwd=os.getenv("PASSWORD")
        )
        mycursor = mydb.cursor()
        mycursor.execute("DROP DATABASE food_database")
        print("Création de la base de donnée...")
        mycursor.execute("CREATE DATABASE food_database")
        mycursor.execute("USE food_database")
        mycursor.execute("CREATE TABLE category (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))")
        mycursor.execute("CREATE TABLE food (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), nutriscore_data INT, category_id INT, FOREIGN KEY (category_id) REFERENCES category(id))")

        mycursor.execute("INSERT INTO food (name, nutriscore_data) VALUES ('Nutella', '10')")
        mydb.commit()

        category_link = requests.get('https://fr.openfoodfacts.org/categories.json')
        json_category = category_link.json()
        product = json_category["tags"]
        mycursor.execute("USE food_database")
        for item in product[:50]:
            category_name = str(item["name"])
            add_employee = """INSERT INTO category(name) VALUES(%s)""" % (category_name)
            # Insert new employee
            mycursor.execute(add_employee)
            print("ouais")
            mydb.commit()