-- Drop the old role column if it exists
ALTER TABLE users DROP COLUMN IF EXISTS role;

-- Add role_id column if it doesn't exist
ALTER TABLE users ADD COLUMN IF NOT EXISTS role_id INTEGER;

-- Update existing users to have a default role (2 for user role)
UPDATE users SET role_id = 2 WHERE role_id IS NULL;

-- Make role_id NOT NULL and add foreign key constraint
ALTER TABLE users 
    ALTER COLUMN role_id SET NOT NULL,
    ADD CONSTRAINT fk_user_role 
    FOREIGN KEY (role_id) 
    REFERENCES roles(role_id);
