-- Migration Doctor snapshot, SQL export: gadgets on dashboards with their module key and filter preference.
-- 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 dashboard-gadgets.jsonl:
--   psql -X -q -A -t -v ON_ERROR_STOP=1 -h DBHOST -U READONLY_USER -d JIRADB -f dashboard-gadgets.sql -o export/dashboard-gadgets.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(
         'gadgetId', pc.id,
         'dashboardId', pc.portalpage,
         'column', pc.column_number,
         'row', pc.positionseq,
         'moduleKey', pc.dashboard_module_complete_key,
         'gadgetXml', pc.gadget_xml,
         'filterPref', (SELECT MIN(gp.userprefvalue) FROM gadgetuserpreference gp
          WHERE gp.portletconfiguration = pc.id AND gp.userprefkey IN ('filterId', 'projectOrFilterId')))
FROM portletconfiguration pc
ORDER BY pc.portalpage, pc.column_number, pc.positionseq, pc.id;