-- 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 sqlcmd and save the output as users.jsonl. JIRA_SCHEMA is the schema of the
-- Jira tables (usually jiraschema, sometimes dbo):
--   sqlcmd -S DBHOST -d JIRADB -E -v JIRA_SCHEMA=jiraschema -y 0 -f 65001 -i users.sql -o export/users.jsonl
-- (-E uses Windows authentication; use -U READONLY_USER and let sqlcmd prompt for the password otherwise.)
-- Then pass the export directory to the snapshot script: --sql-export-dir export (bash) or -SqlExportDir export (PowerShell).

SET NOCOUNT ON;
SELECT (SELECT
         a.user_key AS [key],
         u.user_name AS [name],
         u.display_name AS [displayName],
         u.email_address AS [email],
         u.active AS [active]
       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)
FROM $(JIRA_SCHEMA).cwd_user u
JOIN $(JIRA_SCHEMA).cwd_directory d ON d.id = u.directory_id AND d.active = 1
LEFT JOIN $(JIRA_SCHEMA).app_user a ON a.lower_user_name = u.lower_user_name
WHERE d.directory_position = (
  SELECT MIN(d2.directory_position)
  FROM $(JIRA_SCHEMA).cwd_user u2 JOIN $(JIRA_SCHEMA).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;