MySQL Stored Procedures

-- Change the delimiter so MySQL doesn't interpret ; inside the procedure
DELIMITER $$

-- Simple procedure: get all orders for a customer
CREATE PROCEDURE GetCustomerOrders(IN p_customer_id INT)
BEGIN
    SELECT
        o.order_id,
        o.total,
        o.status,
        o.ordered_at
    FROM orders o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.ordered_at DESC;
END$$

-- Procedure with IN, OUT, and INOUT parameters
CREATE PROCEDURE GetOrderStats(
    IN  p_customer_id INT,
    OUT p_order_count INT,
    OUT p_total_spent DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total), 0)
    INTO p_order_count, p_total_spent
    FROM orders
    WHERE customer_id = p_customer_id;
END$$

DELIMITER ;

-- Call the procedures
CALL GetCustomerOrders(1);

CALL GetOrderStats(1, @count, @spent);
SELECT @count AS order_count, @spent AS total_spent;

-- Show all stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'shop';

-- Drop a procedure
DROP PROCEDURE IF EXISTS GetCustomerOrders;

DELIMITER $$

CREATE PROCEDURE ClassifyCustomer(IN p_customer_id INT, OUT p_tier VARCHAR(20))
BEGIN
    DECLARE v_total_spent DECIMAL(10,2) DEFAULT 0;

    -- Get total spent by this customer
    SELECT COALESCE(SUM(total), 0)
    INTO v_total_spent
    FROM orders
    WHERE customer_id = p_customer_id AND status = 'delivered';

    -- Classify based on spending
    IF v_total_spent >= 1000 THEN
        SET p_tier = 'Platinum';
    ELSEIF v_total_spent >= 500 THEN
        SET p_tier = 'Gold';
    ELSEIF v_total_spent >= 100 THEN
        SET p_tier = 'Silver';
    ELSE
        SET p_tier = 'Bronze';
    END IF;
END$$

-- Procedure with WHILE loop: apply bulk discount
CREATE PROCEDURE ApplyCategoryDiscount(IN p_category VARCHAR(100), IN p_discount DECIMAL(5,2))
BEGIN
    DECLARE v_done    INT DEFAULT 0;
    DECLARE v_prod_id INT;
    DECLARE v_price   DECIMAL(10,2);

    -- Cursor to iterate over products in the category
    DECLARE cur CURSOR FOR
        SELECT product_id, price FROM products WHERE category = p_category;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_prod_id, v_price;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        UPDATE products
        SET price = ROUND(v_price * (1 - p_discount / 100), 2)
        WHERE product_id = v_prod_id;
    END LOOP;
    CLOSE cur;
END$$

DELIMITER ;

-- Call the procedures
CALL ClassifyCustomer(1, @tier);
SELECT @tier;

CALL ApplyCategoryDiscount('Electronics', 10.00);

DELIMITER $$

-- Function: calculate discounted price
CREATE FUNCTION DiscountedPrice(p_price DECIMAL(10,2), p_discount_pct DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(p_price * (1 - p_discount_pct / 100), 2);
END$$

-- Function: get customer full name
CREATE FUNCTION GetFullName(p_customer_id INT)
RETURNS VARCHAR(101)
READS SQL DATA
BEGIN
    DECLARE v_name VARCHAR(101);
    SELECT CONCAT(first_name, ' ', last_name)
    INTO v_name
    FROM customers
    WHERE customer_id = p_customer_id;
    RETURN COALESCE(v_name, 'Unknown');
END$$

DELIMITER ;

-- Use functions in queries (just like built-in functions)
SELECT
    name,
    price,
    DiscountedPrice(price, 15) AS sale_price
FROM products
WHERE category = 'Electronics';

SELECT order_id, GetFullName(customer_id) AS customer_name, total
FROM orders
ORDER BY ordered_at DESC
LIMIT 5;

-- Drop a function
DROP FUNCTION IF EXISTS DiscountedPrice;

-- Create an audit log table
CREATE TABLE order_audit_log (
    log_id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   INT UNSIGNED NOT NULL,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id)
);

DELIMITER $$

-- AFTER UPDATE trigger: log order status changes
CREATE TRIGGER trg_orders_status_change
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_audit_log (order_id, old_status, new_status)
        VALUES (NEW.order_id, OLD.status, NEW.status);
    END IF;
END$$

-- BEFORE INSERT trigger: ensure price is not negative
CREATE TRIGGER trg_products_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SET NEW.price = 0;
    END IF;
END$$

DELIMITER ;

-- Test the trigger
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
SELECT * FROM order_audit_log;

-- Show all triggers
SHOW TRIGGERS FROM shop;

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_orders_status_change;

-- Enable the event scheduler
SET GLOBAL event_scheduler = ON;

DELIMITER $$

-- One-time event: run once at a specific time
CREATE EVENT evt_one_time_cleanup
ON SCHEDULE AT '2025-01-01 00:00:00'
DO
BEGIN
    DELETE FROM order_audit_log WHERE changed_at < '2024-01-01';
END$$

-- Recurring event: run every day at midnight
CREATE EVENT evt_daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- Purge soft-deleted customers older than 90 days
    DELETE FROM customers
    WHERE deleted_at IS NOT NULL
      AND deleted_at < NOW() - INTERVAL 90 DAY;
END$$

DELIMITER ;

-- Show all events
SHOW EVENTS FROM shop;

-- Disable an event
ALTER EVENT evt_daily_cleanup DISABLE;

-- Drop an event
DROP EVENT IF EXISTS evt_one_time_cleanup;