board_investigator

board_investigator.sql

WITH board_base AS (
    SELECT
        rv."ID" AS board_id,
        rv."NAME" AS board_name,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM "AO_60DB71_SPRINT" s
                WHERE s."RAPID_VIEW_ID" = rv."ID"
            )
            THEN 'scrum'
            ELSE 'kanban'
        END AS board_type,
        sr.id AS filter_id,
        sr.filtername AS filter_name,
        sr.reqcontent AS jql,
        au.lower_user_name AS filter_owner
    FROM "AO_60DB71_RAPIDVIEW" rv
    LEFT JOIN searchrequest sr
        ON rv."SAVED_FILTER_ID" = sr.id
    LEFT JOIN app_user au
        ON sr.username = au.user_key
),
jql_projects AS (
    SELECT
        bb.*,
        regexp_matches(bb.jql, $$project\s+in\s*\(([^)]+)\)|project\s*=\s*'([^']+)'$$, 'gi') AS proj_match
    FROM board_base bb
)
SELECT
    board_id,
    board_name,
    board_type,
    filter_id,
    filter_name,
    jql,
    filter_owner,
    STRING_AGG(DISTINCT p.pkey, ',') AS project_keys,
    STRING_AGG(DISTINCT p.pname, ',') AS project_names
FROM jql_projects jp
LEFT JOIN project p
    ON p.pkey = ANY (
        string_to_array(
            regexp_replace(
                COALESCE(jp.proj_match[1], jp.proj_match[2], ''),
                '''', '', 'g'
            ), ','
        )
    )
GROUP BY
    board_id,
    board_name,
    board_type,
    filter_id,
    filter_name,
    jql,
    filter_owner
ORDER BY board_name;