-- Add role_id column
ALTER TABLE `users` ADD COLUMN `role_id` INT(11) NULL AFTER `password`;

-- Update role_id from existing role name
UPDATE `users` u
JOIN `roles` r ON u.role = r.name
SET u.role_id = r.id;

-- Make role_id not nullable
ALTER TABLE `users` MODIFY `role_id` INT(11) NOT NULL;

-- Add foreign key constraint
ALTER TABLE `users` ADD CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Drop the old role column
ALTER TABLE `users` DROP COLUMN `role`;