-- Migration Doctor snapshot, SQL export: users (username, user key, display name, e-mail, active) from the first active directory that has them.
-- Read-only: a single SELECT, no writes, no temporary tables. Output: one JSON object per line.
-- Run (read-only) with psql and save the output as users.jsonl:
--   psql -X -q -A -t -v ON_ERROR_STOP=1 -h DBHOST -U READONLY_USER -d JIRADB -f users.sql -o export/users.jsonl
-- If the Jira tables are not in the default search_path, set PGOPTIONS='-c search_path=your_schema' first.
-- Then pass the export directory to the snapshot script: --sql-export-dir export (bash) or -SqlExportDir export (PowerShell).

SELECT json_build_object(
         'key', a.user_key,
         'name', u.user_name,
         'displayName', u.display_name,
         'email', u.email_address,
         'active', u.active)
FROM cwd_user u
JOIN cwd_directory d ON d.id = u.directory_id AND d.active = 1
LEFT JOIN app_user a ON a.lower_user_name = u.lower_user_name
WHERE d.directory_position = (
  SELECT MIN(d2.directory_position)
  FROM cwd_user u2 JOIN cwd_directory d2 ON d2.id = u2.directory_id AND d2.active = 1
  WHERE u2.lower_user_name = u.lower_user_name)
ORDER BY u.lower_user_name;