-- Migration Doctor snapshot, SQL export: share permissions of filters (SearchRequest) and dashboards (PortalPage).
-- 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 share-permissions.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 share-permissions.sql -o export/share-permissions.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;
WITH owner_name AS (
  SELECT a.user_key,
         COALESCE((SELECT TOP 1 u.user_name FROM $(JIRA_SCHEMA).cwd_user u JOIN $(JIRA_SCHEMA).cwd_directory d ON d.id = u.directory_id AND d.active = 1
           WHERE u.lower_user_name = a.lower_user_name ORDER BY d.directory_position),
                  a.lower_user_name) AS user_name
  FROM $(JIRA_SCHEMA).app_user a
)
SELECT (SELECT
         sp.id AS [id],
         sp.entitytype AS [entityType],
         sp.entityid AS [entityId],
         sp.sharetype AS [shareType],
         CASE WHEN sp.sharetype = 'group' THEN sp.param1 END AS [group],
         p.pkey AS [projectKey],
         r.name AS [role],
         CASE WHEN sp.sharetype = 'user' THEN COALESCE(o.user_name, sp.param1) END AS [user]
       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)
FROM $(JIRA_SCHEMA).sharepermissions sp
LEFT JOIN $(JIRA_SCHEMA).project p ON sp.sharetype = 'project' AND CAST(p.id AS NVARCHAR(255)) = sp.param1
LEFT JOIN $(JIRA_SCHEMA).projectrole r ON sp.sharetype = 'project' AND CAST(r.id AS NVARCHAR(255)) = sp.param2
LEFT JOIN owner_name o ON sp.sharetype = 'user' AND o.user_key = sp.param1
WHERE sp.entitytype IN ('SearchRequest', 'PortalPage')
ORDER BY sp.id;