-- Add fields for username, full_name, status, password_hash and login meta
ALTER TABLE users
  ADD COLUMN username VARCHAR(100) NULL UNIQUE AFTER email,
  ADD COLUMN full_name VARCHAR(150) NULL AFTER name,
  ADD COLUMN password_hash VARCHAR(255) NULL AFTER password,
  ADD COLUMN `status` VARCHAR(20) NOT NULL DEFAULT 'active' AFTER password_hash,
  ADD COLUMN last_login_at DATETIME NULL AFTER created_at,
  ADD COLUMN last_login_ip VARCHAR(45) NULL AFTER last_login_at;

-- Ensure existing password values are also available in password_hash for compatibility
UPDATE users SET password_hash = password WHERE password_hash IS NULL AND password IS NOT NULL;
