-- ============================================================
-- 39_agent_ords_default_user_display_name.sql
-- Give the seeded verification users human-readable display names.
-- User IDs, roles, bearer keys and VPD authorization remain unchanged.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === Renaming default verification users ===
DECLARE
  PROCEDURE rename_user(
      p_user_id   IN NUMBER,
      p_user_name IN VARCHAR2
  ) IS
  BEGIN
    UPDATE cb_app_user
    SET    user_name = p_user_name
    WHERE  user_id = p_user_id;

    IF SQL%ROWCOUNT <> 1 THEN
      RAISE_APPLICATION_ERROR(-20839, 'Default verification user not found: ' || p_user_id);
    END IF;
  END;
BEGIN
  rename_user(101, '이에이치알'); -- HR_DEPT_ROLE
  rename_user(102, '박파이넨스'); -- FIN_SELF_ROLE
  rename_user(103, '김어드민');   -- ALL_DOC_ROLE
  COMMIT;
END;
/

PROMPT === Verifying renamed users and their roles ===
SELECT u.user_id,
       u.user_name,
       u.employee_no,
       u.dept_code,
       r.role_name
FROM   cb_app_user u
LEFT JOIN cb_user_role ur ON ur.user_id = u.user_id
LEFT JOIN cb_app_role r ON r.role_id = ur.role_id
WHERE  u.user_id IN (101, 102, 103)
ORDER BY u.user_id, r.role_id;

PROMPT === Default verification user display names updated ===
EXIT;
