-- Update users table to support multi-role authentication
-- Add new roles: admin, instructor, student, guest

ALTER TABLE users MODIFY COLUMN role ENUM('admin', 'instructor', 'student', 'guest') DEFAULT 'student';

-- Update existing users to appropriate roles
UPDATE users SET role = 'admin' WHERE role = 'admin';
UPDATE users SET role = 'instructor' WHERE role = 'author';

-- Add additional columns for enhanced authentication
ALTER TABLE users
ADD COLUMN email_verified BOOLEAN DEFAULT FALSE,
ADD COLUMN email_verification_token VARCHAR(255) NULL,
ADD COLUMN password_reset_token VARCHAR(255) NULL,
ADD COLUMN password_reset_expires TIMESTAMP NULL,
ADD COLUMN last_login TIMESTAMP NULL,
ADD COLUMN failed_login_attempts INT DEFAULT 0,
ADD COLUMN account_locked_until TIMESTAMP NULL,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Create index for email verification
CREATE INDEX idx_email_verification_token ON users(email_verification_token);
CREATE INDEX idx_password_reset_token ON users(password_reset_token);