-- Create roles table
CREATE TABLE IF NOT EXISTS roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Insert default roles
INSERT INTO roles (role_name) VALUES 
    ('admin'),
    ('user')
ON CONFLICT (role_name) DO NOTHING;

-- Add role_id column to users table
ALTER TABLE users 
    ADD COLUMN IF NOT EXISTS role_id INTEGER;

-- Update existing users to have a default role (user)
UPDATE users 
SET role_id = (SELECT role_id FROM roles WHERE role_name = 
    CASE 
        WHEN role = 'admin' THEN 'admin'
        ELSE 'user'
    END
);

-- 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);

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