

-- ============================================================================
-- Stored Procedures
-- ============================================================================

DELIMITER $$

-- Check rate limit for an API key
CREATE PROCEDURE IF NOT EXISTS check_rate_limit(
    IN p_api_key_id VARCHAR(64),
    IN p_bucket_type ENUM('minute', 'hour', 'day'),
    IN p_quota INT,
    OUT p_allowed BOOLEAN,
    OUT p_current_count INT,
    OUT p_reset_at TIMESTAMP
)
BEGIN
    DECLARE v_window_start TIMESTAMP;
    DECLARE v_count INT DEFAULT 0;

    -- Calculate window start based on bucket type
    IF p_bucket_type = 'minute' THEN
        SET v_window_start = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00');
    ELSEIF p_bucket_type = 'hour' THEN
        SET v_window_start = DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00');
    ELSE -- day
        SET v_window_start = DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00');
    END IF;

    SET p_current_count = v_count;
    SET p_allowed = (v_count <= p_quota);

    -- Calculate reset time
    IF p_bucket_type = 'minute' THEN
        SET p_reset_at = DATE_ADD(v_window_start, INTERVAL 1 MINUTE);
    ELSEIF p_bucket_type = 'hour' THEN
        SET p_reset_at = DATE_ADD(v_window_start, INTERVAL 1 HOUR);
    ELSE
        SET p_reset_at = DATE_ADD(v_window_start, INTERVAL 1 DAY);
    END IF;
END$$

    SELECT ROW_COUNT() as deleted_rows;
END$$

    SELECT ROW_COUNT() as deleted_rows;
END$$

DELIMITER ;

-- ============================================================================
-- Events for automatic cleanup (if MySQL Event Scheduler is enabled)
-- ============================================================================

-- Daily cleanup of rate limit buckets
CREATE EVENT IF NOT EXISTS evt_cleanup_rate_limits
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 3 HOUR)
DO
    CALL cleanup_rate_limit_buckets();

-- Weekly cleanup of old telemetry (keep 90 days)
CREATE EVENT IF NOT EXISTS evt_cleanup_telemetry
ON SCHEDULE EVERY 1 WEEK
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 WEEK + INTERVAL 4 HOUR)
DO
    CALL cleanup_old_telemetry(90);

-- ============================================================================
-- Grant permissions (adjust as needed for your environment)
-- ============================================================================
-- GRANT SELECT, INSERT, UPDATE ON api_keys TO 'orchestrator_user'@'localhost';
-- GRANT SELECT, INSERT, UPDATE ON rate_limit_buckets TO 'orchestrator_user'@'localhost';
-- GRANT SELECT, INSERT ON api_telemetry TO 'orchestrator_user'@'localhost';
-- GRANT SELECT, INSERT, UPDATE ON telemetry_daily_summary TO 'orchestrator_user'@'localhost';
-- GRANT EXECUTE ON PROCEDURE check_rate_limit TO 'orchestrator_user'@'localhost';

-- ============================================================================
-- Migration complete
-- ============================================================================
SELECT 'Sprint 4 migration completed successfully' AS status;
