-- 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 sqlcmd and save the output as dashboard-gadgets.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 dashboard-gadgets.sql -o export/dashboard-gadgets.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
         pc.id AS [gadgetId],
         pc.portalpage AS [dashboardId],
         pc.column_number AS [column],
         pc.positionseq AS [row],
         CAST(pc.dashboard_module_complete_key AS NVARCHAR(MAX)) AS [moduleKey],
         CAST(pc.gadget_xml AS NVARCHAR(MAX)) AS [gadgetXml],
         (SELECT MIN(CAST(gp.userprefvalue AS NVARCHAR(MAX))) FROM $(JIRA_SCHEMA).gadgetuserpreference gp
          WHERE gp.portletconfiguration = pc.id AND gp.userprefkey IN ('filterId', 'projectOrFilterId')) AS [filterPref]
       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)
FROM $(JIRA_SCHEMA).portletconfiguration pc
ORDER BY pc.portalpage, pc.column_number, pc.positionseq, pc.id;