-- -----------------------------------------------------
-- Enterprise E-commerce Triggers & Procedures (MySQL)
-- -----------------------------------------------------

DELIMITER //

-- -----------------------------------------------------
-- UUID Generation Triggers
-- Ensures that if the application doesn't provide a UUID, MySQL generates one.
-- -----------------------------------------------------

CREATE TRIGGER `trg_users_uuid` BEFORE INSERT ON `users`
FOR EACH ROW
BEGIN
  IF NEW.id IS NULL OR NEW.id = '' THEN
    SET NEW.id = UUID();
  END IF;
END//

CREATE TRIGGER `trg_tenants_uuid` BEFORE INSERT ON `tenants`
FOR EACH ROW
BEGIN
  IF NEW.id IS NULL OR NEW.id = '' THEN
    SET NEW.id = UUID();
  END IF;
END//

CREATE TRIGGER `trg_categories_uuid` BEFORE INSERT ON `categories`
FOR EACH ROW
BEGIN
  IF NEW.id IS NULL OR NEW.id = '' THEN
    SET NEW.id = UUID();
  END IF;
END//

CREATE TRIGGER `trg_products_uuid` BEFORE INSERT ON `products`
FOR EACH ROW
BEGIN
  IF NEW.id IS NULL OR NEW.id = '' THEN
    SET NEW.id = UUID();
  END IF;
END//

CREATE TRIGGER `trg_orders_uuid` BEFORE INSERT ON `orders`
FOR EACH ROW
BEGIN
  IF NEW.id IS NULL OR NEW.id = '' THEN
    SET NEW.id = UUID();
  END IF;
END//

-- -----------------------------------------------------
-- Order Status & Inventory Management Procedures
-- -----------------------------------------------------

-- Procedure to safely update stock when an order is paid
CREATE PROCEDURE `sp_process_order_payment` (IN p_order_id VARCHAR(36))
BEGIN
  DECLARE v_status VARCHAR(50);
  
  -- Check current status
  SELECT status INTO v_status FROM orders WHERE id = p_order_id FOR UPDATE;
  
  IF v_status = 'pendente' THEN
    -- Update Order
    UPDATE orders SET status = 'pago' WHERE id = p_order_id;
    
    -- Update Stock for each item
    UPDATE products p
    INNER JOIN order_items oi ON p.id = oi.product_id
    SET 
        p.stock = p.stock - oi.quantity,
        p.stock_sold = p.stock_sold + oi.quantity
    WHERE oi.order_id = p_order_id;
    
    -- Register Financial Transaction
    INSERT INTO financial_transactions (id, tenant_id, type, amount, order_id, description)
    SELECT UUID(), tenant_id, 'entrada', total_amount, id, CONCAT('Pagamento da encomenda ', id)
    FROM orders WHERE id = p_order_id;
    
  END IF;
END//

-- -----------------------------------------------------
-- Trigger to protect User roles (Security Hardening)
-- Equivalent to the prevent_role_escalation trigger in Postgres
-- -----------------------------------------------------
CREATE TRIGGER `trg_prevent_role_escalation` BEFORE UPDATE ON `users`
FOR EACH ROW
BEGIN
  -- In MySQL, since we don't have RLS or native "current_user" mapping to app user,
  -- we rely on the backend. However, as an extra layer of defense, we can enforce
  -- that role changes must be done via a dedicated procedure or require a specific context.
  -- This is a placeholder showing the intent.
  -- If you want absolute DB level protection, you would set a session variable in the backend.
  IF @admin_context IS NULL AND NEW.role != OLD.role THEN
    -- Silently revert the role change
    SET NEW.role = OLD.role;
  END IF;
END//

DELIMITER ;