website/database_migrations/add_user_roles_status.sql

-- Add user role and status management
-- Run this migration to enhance the users table

ALTER TABLE users 
ADD COLUMN role ENUM('admin', 'user') NOT NULL DEFAULT 'user' AFTER auth_type,
ADD COLUMN status ENUM('active', 'inactive') NOT NULL DEFAULT 'active' AFTER role,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

-- Create index for better performance
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_status ON users(status);

-- Update existing users to have default role and status
UPDATE users SET role = 'admin' WHERE id = 1; -- Make first user admin
UPDATE users SET status = 'active' WHERE status IS NULL;