-- 创建存储过程
DELIMITER //
CREATE PROCEDURE sp_get_user_orders(IN user_id BIGINT)
BEGIN
    SELECT order_no, total_amount, status, created_at
    FROM orders
    WHERE user_id = user_id
    ORDER BY created_at DESC;
END //
DELIMITER ;

-- 调用
CALL sp_get_user_orders(1);

-- 删除
DROP PROCEDURE IF EXISTS sp_get_user_orders;

DELIMITER //
CREATE PROCEDURE sp_user_stats(
    IN p_user_id BIGINT,
    OUT p_order_count INT,
    OUT p_total_spent DECIMAL(12,2),
    OUT p_avg_order DECIMAL(12,2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(pay_amount), 0), COALESCE(AVG(pay_amount), 0)
    INTO p_order_count, p_total_spent, p_avg_order
    FROM orders
    WHERE user_id = p_user_id AND status IN (1, 2, 3);
END //
DELIMITER ;

-- 调用
CALL sp_user_stats(1, @cnt, @total, @avg);
SELECT @cnt AS order_count, @total AS total_spent, @avg AS avg_order;

DELIMITER //
CREATE PROCEDURE sp_process_order(IN p_order_id BIGINT)
BEGIN
    -- 声明变量
    DECLARE v_status TINYINT;
    DECLARE v_amount DECIMAL(12,2);
    DECLARE v_user_id BIGINT;
    
    -- 获取订单信息
    SELECT status, pay_amount, user_id 
    INTO v_status, v_amount, v_user_id
    FROM orders WHERE id = p_order_id;
    
    -- IF 条件
    IF v_status = 0 THEN
        UPDATE orders SET status = 1, pay_time = NOW() WHERE id = p_order_id;
        UPDATE users SET balance = balance - v_amount WHERE id = v_user_id;
    ELSEIF v_status = 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单已支付';
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单状态异常';
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_archive_old_orders()
BEGIN
    DECLARE v_id BIGINT;
    DECLARE v_done INT DEFAULT FALSE;
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT id FROM orders WHERE status IN (3, 4) AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
    
    -- 结束处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    OPEN cur;
    
    archive_loop: LOOP
        FETCH cur INTO v_id;
        IF v_done THEN
            LEAVE archive_loop;
        END IF;
        
        -- 迁移到归档表
        INSERT INTO orders_archive SELECT * FROM orders WHERE id = v_id;
        DELETE FROM orders WHERE id = v_id;
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_generate_test_data(IN count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= count DO
        INSERT INTO users (username, email, password, balance)
        VALUES (
            CONCAT('testuser_', LPAD(i, 6, '0')),
            CONCAT('test', i, '@example.com'),
            SHA2(CONCAT('password', i), 256),
            ROUND(RAND() * 10000, 2)
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- REPEAT 循环
DELIMITER //
CREATE PROCEDURE sp_repeat_example()
BEGIN
    DECLARE i INT DEFAULT 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO logs (message) VALUES (CONCAT('Entry ', i));
    UNTIL i >= 10
    END REPEAT;
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION fn_age(birth_date DATE) RETURNS INT DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END //
DELIMITER ;

-- 使用
SELECT username, fn_age(birth_date) AS age FROM users;

-- 订单状态文本
DELIMITER //
CREATE FUNCTION fn_order_status(status_code TINYINT) RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
    RETURN CASE status_code
        WHEN 0 THEN '待支付'
        WHEN 1 THEN '已支付'
        WHEN 2 THEN '已发货'
        WHEN 3 THEN '已完成'
        WHEN 4 THEN '已取消'
        WHEN 5 THEN '已退款'
        ELSE '未知'
    END;
END //
DELIMITER ;

SELECT order_no, fn_order_status(status) AS status_text FROM orders;

-- 创建审计日志触发器
DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO audit_log (table_name, record_id, field_name, old_value, new_value, changed_at)
        VALUES ('users', NEW.id, 'email', OLD.email, NEW.email, NOW());
    END IF;
    IF OLD.status != NEW.status THEN
        INSERT INTO audit_log (table_name, record_id, field_name, old_value, new_value, changed_at)
        VALUES ('users', NEW.id, 'status', OLD.status, NEW.status, NOW());
    END IF;
END //
DELIMITER ;

-- 库存扣减触发器
DELIMITER //
CREATE TRIGGER trg_order_items_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock - NEW.quantity,
        sales = sales + NEW.quantity
    WHERE id = NEW.product_id;
    
    IF (SELECT stock FROM products WHERE id = NEW.product_id) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
    END IF;
END //
DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'myapp';

-- 删除触发器
DROP TRIGGER IF EXISTS trg_users_after_update;

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建定时事件：每天清理过期数据
DELIMITER //
CREATE EVENT evt_cleanup_expired
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    DELETE FROM sessions WHERE expires_at < NOW();
    DELETE FROM verification_codes WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);
END //
DELIMITER ;

-- 创建一次性事件
DELIMITER //
CREATE EVENT evt_one_time
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
BEGIN
    INSERT INTO logs (message) VALUES ('One hour has passed');
END //
DELIMITER ;

-- 查看事件
SHOW EVENTS;
SELECT * FROM information_schema.EVENTS;

-- 修改事件
ALTER EVENT evt_cleanup_expired DISABLE;
ALTER EVENT evt_cleanup_expired ENABLE;
ALTER EVENT evt_cleanup_expired ON SCHEDULE EVERY 6 HOUR;

-- 删除事件
DROP EVENT IF EXISTS evt_cleanup_expired;

DELIMITER //
CREATE PROCEDURE sp_transfer(
    IN from_id BIGINT,
    IN to_id BIGINT,
    IN amount DECIMAL(12,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1 @err_msg = MESSAGE_TEXT;
        SELECT CONCAT('Error: ', @err_msg) AS error;
    END;
    
    START TRANSACTION;
    
    -- 检查余额
    IF (SELECT balance FROM accounts WHERE id = from_id) < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
    
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    
    COMMIT;
    SELECT 'Transfer successful' AS result;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_daily_report(IN report_date DATE)
BEGIN
    INSERT INTO daily_summary (report_date, order_count, revenue, new_users, avg_order_amount)
    SELECT 
        report_date,
        COUNT(*),
        SUM(pay_amount),
        (SELECT COUNT(*) FROM users WHERE DATE(created_at) = report_date),
        AVG(pay_amount)
    FROM orders
    WHERE DATE(created_at) = report_date AND status IN (1, 2, 3);
END //
DELIMITER ;

-- 配合事件调度器
CREATE EVENT evt_daily_report
ON SCHEDULE EVERY 1 DAY
STARTS '2026-05-11 02:00:00'
DO CALL sp_daily_report(CURDATE() - INTERVAL 1 DAY);