-- 1. DECLARE the cursor
DECLARE cursor_name CURSOR FOR
  SELECT col1, col2 FROM some_table WHERE condition;

-- 2. OPEN the cursor (executes the query)
OPEN cursor_name;

-- 3. FETCH rows one by one into variables
FETCH cursor_name INTO var1, var2;

-- 4. CLOSE the cursor when done
CLOSE cursor_name;

DECLARE done INT DEFAULT FALSE;

-- This handler fires when FETCH finds no more rows
DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET done = TRUE;

DELIMITER //

CREATE PROCEDURE process_pending_orders()
BEGIN
  -- 1. Declare variables for the fetched columns
  DECLARE v_order_id    INT;
  DECLARE v_customer_id INT;
  DECLARE v_total       DECIMAL(10,2);
  DECLARE done          INT DEFAULT FALSE;

  -- 2. Declare the cursor
  DECLARE cur_orders CURSOR FOR
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'pending'
    ORDER BY order_date;

  -- 3. Declare NOT FOUND handler
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET done = TRUE;

  -- 4. Open the cursor
  OPEN cur_orders;

  -- 5. Loop
  order_loop: LOOP
    FETCH cur_orders INTO v_order_id, v_customer_id, v_total;

    IF done THEN
      LEAVE order_loop;
    END IF;

    -- 6. Process the current row
    IF v_total > 1000 THEN
      UPDATE orders SET priority = 'high' WHERE order_id = v_order_id;
    ELSE
      UPDATE orders SET priority = 'normal' WHERE order_id = v_order_id;
    END IF;

    -- Log the action
    INSERT INTO order_log (order_id, note, logged_at)
    VALUES (v_order_id, 'Priority assigned', NOW());

  END LOOP order_loop;

  -- 7. Close the cursor
  CLOSE cur_orders;
END //

DELIMITER ;

CALL process_pending_orders();

DELIMITER //

CREATE PROCEDURE sync_customer_tiers()
BEGIN
  DECLARE v_customer_id  INT;
  DECLARE v_total_spent  DECIMAL(10,2);
  DECLARE v_new_tier     VARCHAR(20);
  DECLARE done1          INT DEFAULT FALSE;

  DECLARE cur_customers CURSOR FOR
    SELECT customer_id FROM customers WHERE status = 'active';

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET done1 = TRUE;

  OPEN cur_customers;

  cust_loop: LOOP
    FETCH cur_customers INTO v_customer_id;
    IF done1 THEN LEAVE cust_loop; END IF;

    -- Calculate lifetime value for this customer
    SELECT COALESCE(SUM(total_amount), 0)
    INTO   v_total_spent
    FROM   orders
    WHERE  customer_id = v_customer_id AND status = 'completed';

    -- Assign tier
    SET v_new_tier = CASE
      WHEN v_total_spent = 0     THEN 'New'
      WHEN v_total_spent < 500   THEN 'Bronze'
      WHEN v_total_spent < 2000  THEN 'Silver'
      WHEN v_total_spent < 10000 THEN 'Gold'
      ELSE                            'Platinum'
    END;

    UPDATE customers SET tier = v_new_tier WHERE customer_id = v_customer_id;

  END LOOP cust_loop;

  CLOSE cur_customers;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE safe_process_refunds()
BEGIN
  DECLARE v_order_id   INT;
  DECLARE v_amount     DECIMAL(10,2);
  DECLARE done         INT DEFAULT FALSE;
  DECLARE v_error      INT DEFAULT FALSE;

  DECLARE cur_refunds CURSOR FOR
    SELECT order_id, total_amount FROM orders WHERE status = 'refund_requested';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error = TRUE;

  OPEN cur_refunds;

  refund_loop: LOOP
    SET v_error = FALSE;  -- Reset error flag for each row

    FETCH cur_refunds INTO v_order_id, v_amount;
    IF done THEN LEAVE refund_loop; END IF;

    START TRANSACTION;

    UPDATE orders SET status = 'refunded' WHERE order_id = v_order_id;
    INSERT INTO refund_ledger (order_id, amount, processed_at)
    VALUES (v_order_id, v_amount, NOW());

    IF v_error THEN
      ROLLBACK;
      INSERT INTO error_log (message, created_at)
      VALUES (CONCAT('Refund failed for order ', v_order_id), NOW());
    ELSE
      COMMIT;
    END IF;

  END LOOP refund_loop;

  CLOSE cur_refunds;
END //

DELIMITER ;

-- Set-based version -- NO cursor, single statement
UPDATE customers c
JOIN (
  SELECT customer_id, COALESCE(SUM(total_amount), 0) AS total_spent
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY customer_id
) AS spending ON c.customer_id = spending.customer_id
SET c.tier = CASE
  WHEN spending.total_spent = 0     THEN 'New'
  WHEN spending.total_spent < 500   THEN 'Bronze'
  WHEN spending.total_spent < 2000  THEN 'Silver'
  WHEN spending.total_spent < 10000 THEN 'Gold'
  ELSE                                   'Platinum'
END
WHERE c.status = 'active';

-- This runs as one query -- 100x faster than a row-by-row cursor

-- Real use case where cursor is justified: generating unique slugs
-- Each row needs a slug, and we must check uniqueness per-row
DELIMITER //

CREATE PROCEDURE generate_unique_slugs()
BEGIN
  DECLARE v_id      INT;
  DECLARE v_name    VARCHAR(255);
  DECLARE v_slug    VARCHAR(255);
  DECLARE v_suffix  INT;
  DECLARE v_exists  INT;
  DECLARE done      INT DEFAULT FALSE;

  DECLARE cur CURSOR FOR
    SELECT product_id, product_name FROM products WHERE slug IS NULL;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  slug_loop: LOOP
    FETCH cur INTO v_id, v_name;
    IF done THEN LEAVE slug_loop; END IF;

    -- Generate base slug
    SET v_slug   = LOWER(REGEXP_REPLACE(TRIM(v_name), '[^a-zA-Z0-9]+', '-'));
    SET v_suffix = 1;

    -- Ensure uniqueness
    uniqueness: LOOP
      SELECT COUNT(*) INTO v_exists
      FROM products WHERE slug = v_slug AND product_id <> v_id;

      IF v_exists = 0 THEN LEAVE uniqueness; END IF;

      SET v_slug   = CONCAT(LOWER(REGEXP_REPLACE(TRIM(v_name), '[^a-zA-Z0-9]+', '-')), '-', v_suffix);
      SET v_suffix = v_suffix + 1;
    END LOOP uniqueness;

    UPDATE products SET slug = v_slug WHERE product_id = v_id;

  END LOOP slug_loop;

  CLOSE cur;
END //

DELIMITER ;