import mysql.connector
from mysql.connector import Error

def connect_to_mariadb():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='liquido',
            database='perfumeria'
        )

        if connection.is_connected():
            print("Conexion exitosa a la base de datos")

            cursor = connection.cursor()

            #create_perfume(cursor, "Chance Eau de Parfum", "Chanel", "Chipre floral", 100, 145.9)
            read_all_perfumes(cursor)
            #read_perfume_by_id(cursor, 6)
            #update_perfume(cursor, 7, "Olympéa Eau de Parfum", "Paco Rabanne", "Oriental floral", 80, 105.5)
            #delete_perfume(cursor, 22)

            cursor.close()

    except Error as e:
        print("Error al conectar a la base de datos", e)

    finally:
        if connection.is_connected():
            connection.close()

def create_perfume(cursor, nombre, marca, fragancia, size, precio):
    try:
        cursor.execute("INSERT INTO perfumes (nombre, marca, fragancia, size, precio) VALUES (%s, %s, %s, %s, %s);", (nombre, marca, fragancia, size, precio))
        cursor.execute("COMMIT;")
        print("Perfume creado correctamente")
    except Error as e:
        print("Error al crear perfume:", e)

def read_all_perfumes(cursor):
    try:
        cursor.execute("SELECT * FROM perfumes;")
        perfumes = cursor.fetchall()
        print("Perfumes encontrados:")
        for perfume in perfumes:
            print(perfume)
    except Error as e:
        print("Error al obtener perfumes:", e)

def read_perfume_by_id(cursor, perfume_id):
    try:
        cursor.execute("SELECT * FROM perfumes WHERE id = %s;", (perfume_id,))
        perfume = cursor.fetchone()
        if perfume:
            print("Perfume encontrado:", perfume)
        else:
            print("No se encontro ningun perfume")
    except Error as e:
        print("Error al obtener perfume por ID:", e)

def update_perfume(cursor, perfume_id, nombre, marca, fragancia, size, precio):
    try:
        cursor.execute("UPDATE perfumes SET nombre = %s, marca = %s, fragancia = %s, size = %s, precio = %s WHERE id = %s;", (nombre, marca, fragancia, size, precio, perfume_id))
        cursor.execute("COMMIT;")
        print("Perfume actualizado correctamente")
    except Error as e:
        print("Error al actualizar perfume:", e)

def delete_perfume(cursor, perfume_id):
    try:
        cursor.execute("DELETE FROM perfumes WHERE id = %s;", (perfume_id,))
        cursor.execute("COMMIT;")
        print("Perfume eliminado")
    except Error as e:
        print("Error al eliminar perfume:", e)

connect_to_mariadb()