-- 1. Creación de la Base de Datos
CREATE DATABASE IF NOT EXISTS minitienda;
USE minitienda;
DROP DATABASE IF EXISTS minitienda; -- Added to ensure a clean database on each run

-- 2. Tabla Productos
CREATE TABLE IF NOT EXISTS productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL
);

-- 3. Tabla Ventas
CREATE TABLE IF NOT EXISTS ventas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    producto_id INT NOT NULL,
    cantidad INT NOT NULL,
    fecha DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);

-- 4. Creación de Índice para Optimización de Consultas por Nombre
CREATE INDEX idx_nombre_producto ON productos(nombre);

-- 5. Crear Usuario Específico con Permisos Limitados (no root)
CREATE USER IF NOT EXISTS 'usuario_tienda'@'localhost' IDENTIFIED BY 'Password123!';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON minitienda.* TO 'usuario_tienda'@'localhost';
FLUSH PRIVILEGES;

-- 6. Trigger para Validar Stock Antes de Insertar en Ventas
DELIMITER //
CREATE TRIGGER trg_validar_stock_before_insert
BEFORE INSERT ON ventas
FOR EACH ROW
BEGIN
    DECLARE stock_actual INT;
    SELECT stock INTO stock_actual FROM productos WHERE id = NEW.producto_id;
    IF stock_actual IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El producto especificado no existe.';
    ELSE IF stock_actual < NEW.cantidad THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock insuficiente para realizar la venta.';
    END IF ;
END;
//
DELIMITER ;

-- 7. Procedimiento Almacenado para Registrar Venta
DELIMITER //
CREATE PROCEDURE sp_registrar_venta(
    IN p_producto_id INT,
    IN p_cantidad INT
)
BEGIN
    INSERT INTO ventas(producto_id, cantidad) VALUES (p_producto_id, p_cantidad);
    UPDATE productos SET stock = stock - p_cantidad WHERE id = p_producto_id;
END;
//
DELIMITER ;

-- 8. Insertar Datos a Tabla Productos
INSERT INTO productos (nombre, precio, stock) VALUES
('Café Molido 500g', 85.50, 20),
('Leche Entera 1L', 24.00, 50),
('Pan Integral', 35.00, 15),
('Galletas de Chocolate', 18.50, 5);
"""