-- Migration: 028_staff_profile.sql
-- Purpose:
--   Add staff profile fields required by the CRM Staff Management interface.
--
-- Notes:
--   Columns are nullable so this migration remains compatible with existing
--   staff accounts. The invitation and profile-management flows will validate
--   new values at the application layer.
--
-- Rollback:
--   ALTER TABLE staff_users
--     DROP COLUMN IF EXISTS department,
--     DROP COLUMN IF EXISTS last_name,
--     DROP COLUMN IF EXISTS first_name;

BEGIN;

ALTER TABLE staff_users
  ADD COLUMN IF NOT EXISTS first_name TEXT,
  ADD COLUMN IF NOT EXISTS last_name TEXT,
  ADD COLUMN IF NOT EXISTS department TEXT;

COMMENT ON COLUMN staff_users.first_name IS
  'Staff member given name displayed in the CRM Staff Management interface.';

COMMENT ON COLUMN staff_users.last_name IS
  'Staff member family name displayed in the CRM Staff Management interface.';

COMMENT ON COLUMN staff_users.department IS
  'Staff member organisational department used for CRM administration and access planning.';

COMMIT;
