-- Add username and NPN columns to users table
ALTER TABLE users
  ADD COLUMN username VARCHAR(100) NULL UNIQUE AFTER email,
  ADD COLUMN npn VARCHAR(20) NULL AFTER username;

-- Auto-generate usernames for existing agents
UPDATE users SET npn = 'NPN1000001' WHERE id = 1 AND npn IS NULL;
UPDATE users SET npn = 'NPN1000002' WHERE id = 2 AND npn IS NULL;
UPDATE users SET npn = 'NPN1000003' WHERE id = 3 AND npn IS NULL;
UPDATE users SET username = LOWER(CONCAT(LEFT(first_name, 1), LEFT(last_name, 1), npn)) WHERE username IS NULL AND role IN ('agent', 'admin');

-- Add verification code columns to password_resets table (dual-code password reset)
ALTER TABLE password_resets
  ADD COLUMN reset_code VARCHAR(6) NULL AFTER token,
  ADD COLUMN phone_code VARCHAR(6) NULL AFTER reset_code,
  ADD COLUMN codes_verified_at DATETIME NULL AFTER expires_at;