-- 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 psql and save the output as share-permissions.jsonl:
--   psql -X -q -A -t -v ON_ERROR_STOP=1 -h DBHOST -U READONLY_USER -d JIRADB -f share-permissions.sql -o export/share-permissions.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).

WITH owner_name AS (
  SELECT a.user_key,
         COALESCE((SELECT u.user_name FROM cwd_user u JOIN 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 LIMIT 1),
                  a.lower_user_name) AS user_name
  FROM app_user a
)
SELECT json_build_object(
         'id', sp.id,
         'entityType', sp.entitytype,
         'entityId', sp.entityid,
         'shareType', sp.sharetype,
         'group', CASE WHEN sp.sharetype = 'group' THEN sp.param1 END,
         'projectKey', p.pkey,
         'role', r.name,
         'user', CASE WHEN sp.sharetype = 'user' THEN COALESCE(o.user_name, sp.param1) END)
FROM sharepermissions sp
LEFT JOIN project p ON sp.sharetype = 'project' AND CAST(p.id AS VARCHAR(255)) = sp.param1
LEFT JOIN projectrole r ON sp.sharetype = 'project' AND CAST(r.id AS VARCHAR(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;