-- =====================================================
-- PT_ENTRAINMENT — apply rejected-status guard + scenario-aware payment success screen
-- to the LIVE workflow_configuration row, in place (no new row, no key/version already
-- matching this file's target assumed).
--
-- WHY THIS FILE WAS REWRITTEN: the previous version of this file only ran
--   UPDATE program_v1 SET workflow_id = (SELECT id FROM workflow_configuration
--     WHERE workflow_key = 'WF_ENTRAINMENT_V8') ...
-- which is a no-op for permissions/success-screen content — it repoints program_v1, it never
-- touches workflow_stage.prerequisite_rules_config / execution_rules_config / action_buttons,
-- and it hardcoded workflow_key = 'WF_ENTRAINMENT_V8', which does not exist on any environment
-- that only ran 02workflow-refresh.sql's original version (that version inserted the row as
-- 'WF_ENTRAINMENT_V7' / workflow_version '2.0.0' — see that file's git history). So on such an
-- environment this file's old content silently did nothing.
--
-- This version finds the live PT_ENTRAINMENT workflow_configuration row by
-- (program_type_key = 'PT_ENTRAINMENT' AND deleted_at IS NULL) instead of a hardcoded key —
-- correct regardless of whether that row is still keyed 'WF_ENTRAINMENT_V7'/'2.0.0' or was
-- already renamed — renames/bumps it to 'WF_ENTRAINMENT_V8' / '2.0.1', and patches the PAYMENT
-- and TRAVEL stage JSON in place to match the FIXES described in 02workflow-refresh.sql's
-- header (rejected-status guard on PAYMENT/TRAVEL gating, scenario-aware PAYMENT success
-- screen). No program_v1 repoint is needed here: this patches the existing row's id in place,
-- it does not create a new one, so program_v1.workflow_id already points at the right row.
--
-- Also: (1) restores ROLE_SHOBA (Coordinator) to the PAYMENT/TRAVEL prerequisite gate and
-- FS_PAYMENTINVOICE/FS_TRAVELPLAN update permissions — dropped from both stages by the
-- 2026-02-19 v2.0.0 rewrite of entrainment-workflow.ts, though it was never dropped from
-- REGISTRATION's FS_BASICDETAILS update permission, which already grants it unconditionally.
-- (2) drops the {registrationStatus NOT_IN ['waitlisted']} condition from ROLE_VIEWER's
-- FS_BASICDETAILS update permission on REGISTRATION, so a waitlisted seeker can still edit
-- their own basic details while waiting (previously only ROLE_ADMIN/ROLE_SHOBA/
-- ROLE_RELATIONAL_MANAGER could edit basic details once waitlisted).
-- (3) adds ROLE_FINANCE_MANAGER to the PAYMENT/TRAVEL prerequisite gate and
-- FS_PAYMENTINVOICE/FS_TRAVELPLAN update permissions — Finance Manager is a standard
-- payment-stage role in HDB/TAT/PAP workflows but was never included for PT_ENTRAINMENT.
-- (4) adds an unconditional FS_TRAVELPLAN create permission for ROLE_RELATIONAL_MANAGER and
-- ROLE_SHOBA — previously only the seeker (ROLE_VIEWER, gated by the stage's usual conditions)
-- could create the travel plan record; RM/Coordinator can now create it on the seeker's behalf.
--
-- Idempotent: every statement is a plain UPDATE keyed off program_type_key/stage_key, safe to
-- re-run.
-- =====================================================

UPDATE workflow_configuration
SET workflow_key = 'WF_ENTRAINMENT_V8',
    workflow_version = '2.0.1',
    updated_at = NOW(),
    updated_by = -2
WHERE program_type_key = 'PT_ENTRAINMENT'
  AND deleted_at IS NULL;

-- ================================================
-- REGISTRATION stage:
-- (1) drop {registrationStatus NOT_IN ['waitlisted']} from ROLE_VIEWER's FS_BASICDETAILS
--     update condition — a waitlisted seeker should still be able to edit their own basic
--     details.
-- (2) split the stage's single shared prerequisite condition block into a ROLE_VIEWER block
--     (still gated by basicDetailsFilled NOT_EQUALS 'completed') and an unconditional
--     ROLE_ADMIN/ROLE_SHOBA/ROLE_RELATIONAL_MANAGER block — previously all four roles shared
--     one condition, so admin-type roles were blocked from even entering the stage once
--     basicDetailsFilled was 'completed', despite already having unconditional FS_BASICDETAILS
--     update permission in execution_rules_config. action_buttons is unchanged from
--     02workflow-refresh.sql, so it is not rewritten here.
-- ================================================
UPDATE workflow_stage ws
SET prerequisite_rules_config = '{
    "requiresAuth": true,
    "requiredStages": [],
    "conditions": [
      {"roles": ["ROLE_VIEWER"], "condition": {"field": "basicDetailsFilled", "operator": "NOT_EQUALS", "value": "completed"}, "message": "Login required to register"},
      {"roles": ["ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER"], "condition": null, "message": "Login required to register"}
    ]
  }'::jsonb,
    execution_rules_config = '{
    "sections": [
      {
        "sectionKey": "FS_BASICDETAILS",
        "permissions": {
          "create": [{"roles": ["ROLE_VIEWER"], "condition": null}],
          "read": [{"roles": ["ALL"], "condition": null}],
          "update": [
            {"roles": ["ROLE_VIEWER"], "condition": {"field": "basicDetailsFilled", "operator": "NOT_EQUALS", "value": "completed"}},
            {"roles": ["ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER"], "condition": null}
          ],
          "delete": [{"roles": [], "condition": null}]
        }
      }
    ]
  }'::jsonb,
    updated_at = NOW(),
    updated_by = -2
FROM workflow_configuration wc
WHERE ws.workflow_config_id = wc.id
  AND wc.program_type_key = 'PT_ENTRAINMENT'
  AND wc.deleted_at IS NULL
  AND ws.stage_key = 'REGISTRATION'
  AND ws.deleted_at IS NULL;

-- ================================================
-- PAYMENT stage: add {registrationStatus NOT_IN ['rejected']} to prerequisite/section gating,
-- and replace MAKE_PAYMENT's flat successScreen with the scenario-aware successScreenConfig.
-- ================================================
UPDATE workflow_stage ws
SET prerequisite_rules_config = '{
    "requiresAuth": true,
    "requiredStages": ["REGISTRATION"],
    "conditions": [
      {"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
        {"field": "requiresPayment", "operator": "EQUALS", "value": true},
        {"field": "isFreeSeat", "operator": "EQUALS", "value": false},
        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
        {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
        {"operator": "OR", "conditions": [
          {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
          {"field": "reservedLink", "operator": "EQUALS", "value": true}
        ]},
        {
          "operator": "OR",
          "conditions": [
            {"field": "paymentStatus", "operator": "IN", "value": ["online_pending", "failed"]},
            {"field": "paymentStatus", "operator": "IS_NULL"},
            {
              "operator": "AND",
              "conditions": [
                {"field": "paymentStatus", "operator": "EQUALS", "value": "offline_pending"},
                {"field": "editRequestStatus", "operator": "EQUALS", "value": "requested"}
              ]
            }
          ]
        }
      ]}, "message": "Basic details must be completed, not a free seat, registration must not be rejected, seat must be available or admin-released, and payment must be pending or have an active edit request"},
      {"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_SHOBA", "ROLE_FINANCE_MANAGER"], "condition": {"operator": "AND", "conditions": [
        {"field": "requiresPayment", "operator": "EQUALS", "value": true},
        {"field": "isFreeSeat", "operator": "EQUALS", "value": false},
        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
        {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
        {"operator": "OR", "conditions": [
          {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
          {"field": "reservedLink", "operator": "EQUALS", "value": true}
        ]}
      ]}, "message": "Basic details must be completed, not a free seat, registration must not be rejected, and seat must be available or admin-released"}
    ]
  }'::jsonb,
    execution_rules_config = '{
    "sections": [
      {
        "sectionKey": "FS_PAYMENTINVOICE",
        "permissions": {
          "create": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
            {"field": "requiresPayment", "operator": "EQUALS", "value": true},
            {"field": "isFreeSeat", "operator": "EQUALS", "value": false},
            {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
            {"operator": "OR", "conditions": [
              {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
              {"field": "reservedLink", "operator": "EQUALS", "value": true}
            ]}
          ]}}],
          "read": [{"roles": ["ALL"], "condition": null}],
          "update": [
            {
              "roles": ["ROLE_VIEWER"],
              "condition": {
                "operator": "AND",
                "conditions": [
                  {"field": "requiresPayment", "operator": "EQUALS", "value": true},
                  {"field": "isFreeSeat", "operator": "EQUALS", "value": false},
                  {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                  {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
                  {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink", "operator": "EQUALS", "value": true}
                  ]},
                  {
                    "operator": "OR",
                    "conditions": [
                      {"field": "paymentStatus", "operator": "IN", "value": ["online_pending", "failed"]},
                      {"field": "paymentStatus", "operator": "IS_NULL"},
                      {
                        "operator": "AND",
                        "conditions": [
                          {"field": "paymentStatus", "operator": "EQUALS", "value": "offline_pending"},
                          {"field": "editRequestStatus", "operator": "EQUALS", "value": "requested"}
                        ]
                      }
                    ]
                  }
                ]
              }
            },
            {"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_SHOBA", "ROLE_FINANCE_MANAGER"], "condition": null}
          ],
          "delete": [{"roles": [], "condition": null}]
        }
      }
    ]
  }'::jsonb,
    action_buttons = '[
    {
      "actionKey": "CANCEL",
      "label": "Cancel",
      "type": "secondary",
      "icon": "close",
      "apiKey": null,
      "permissions": [{"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_VIEWER"], "condition": null}],
      "onSuccess": [{"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_VIEWER"], "showToast": false, "showSuccessScreen": false}]
    },
    {
      "actionKey": "MAKE_PAYMENT",
      "label": "submit",
      "type": "primary",
      "icon": "payment",
      "apiKey": "MAKE_PAYMENT",
      "permissions": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
        {"field": "requiresPayment", "operator": "EQUALS", "value": true},
        {"field": "isFreeSeat", "operator": "EQUALS", "value": false},
        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
        {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
        {"operator": "OR", "conditions": [
          {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
          {"field": "reservedLink", "operator": "EQUALS", "value": true}
        ]},
        {
          "operator": "OR",
          "conditions": [
            {"field": "paymentStatus", "operator": "IN", "value": ["online_pending", "failed"]},
            {"field": "paymentStatus", "operator": "IS_NULL"},
            {
              "operator": "AND",
              "conditions": [
                {"field": "paymentStatus", "operator": "EQUALS", "value": "offline_pending"},
                {"field": "editRequestStatus", "operator": "EQUALS", "value": "requested"}
              ]
            }
          ]
        }
      ]}}],
      "onSuccess": [{
        "roles": ["ROLE_VIEWER"],
        "showToast": false,
        "showSuccessScreen": true,
        "successScreenConfig": {
          "scenarios": [
            {
              "scenarioKey": "PAYMENT_EDIT_REQUESTED",
              "condition": {
                "operator": "AND",
                "conditions": [
                  {"field": "paymentMode", "operator": "EQUALS", "value": "offline"},
                  {"field": "paymentStatus", "operator": "EQUALS", "value": "offline_pending"},
                  {"field": "editRequestStatus", "operator": "EQUALS", "value": "requested"}
                ]
              },
              "screen": {
                "title": "{{programName}}",
                "message": "Your payment edit request for {{programName}} has been submitted. You can edit your payment details once the request is approved.",
                "icon": "info",
                "showLogo": true,
                "showBanner": true,
                "logoUrl": "{{logoUrl}}",
                "bannerImageUrl": "{{bannerImageUrl}}",
                "actions": [
                  {"actionKey": "HOME", "label": "Go to Home", "type": "secondary", "icon": "home", "apiKey": null, "condition": null},
                  {"actionKey": "UPDATE_PAYMENT", "label": "Update Payment", "type": "secondary", "icon": null, "apiKey": "UPDATE_PAYMENT_DETAILS", "condition": null}
                ]
              }
            },
            {
              "scenarioKey": "ONLINE_PAYMENT_SUCCESS",
              "condition": {
                "operator": "AND",
                "conditions": [
                  {"field": "paymentMode", "operator": "EQUALS", "value": "online"},
                  {"field": "paymentStatus", "operator": "IN", "value": ["online_completed", "completed"]}
                ]
              },
              "screen": {
                "title": "Payment Completed!",
                "message": "Your payment for {{programName}} ({{programStartDate}} to {{programEndDate}}) has been completed successfully. You can proceed with travel details.",
                "icon": "check_circle",
                "showLogo": true,
                "showBanner": true,
                "logoUrl": "{{logoUrl}}",
                "bannerImageUrl": "{{bannerImageUrl}}",
                "actions": [
                  {"actionKey": "PROCEED_TO_TRAVEL", "label": "Proceed to Travel", "type": "primary", "icon": "flight", "apiKey": null, "condition": {"field": "involvesTravel", "operator": "EQUALS", "value": true}},
                  {"actionKey": "DOWNLOAD_INVOICE", "label": "Download Invoice", "type": "secondary", "icon": "download", "apiKey": "DOWNLOAD_INVOICE", "condition": {"field": "paymentStatus", "operator": "EQUALS", "value": "online_completed"}},
                  {"actionKey": "HOME", "label": "Go to Home", "type": "secondary", "icon": "home", "apiKey": null, "condition": null}
                ]
              }
            },
            {
              "scenarioKey": "OFFLINE_PAYMENT_CHEQUE",
              "condition": {
                "operator": "AND",
                "conditions": [
                  {"field": "paymentMode", "operator": "EQUALS", "value": "offline"},
                  {"field": "offlinePaymentMethod", "operator": "EQUALS", "value": "Cheque"}
                ]
              },
              "screen": {
                "title": "Payment Submitted!",
                "message": "We acknowledge your payment towards {{programName}} ({{programStartDate}} to {{programEndDate}}). Note: cheques are subject to realization. You can proceed with travel details.",
                "icon": "check_circle",
                "showLogo": true,
                "showBanner": true,
                "logoUrl": "{{logoUrl}}",
                "bannerImageUrl": "{{bannerImageUrl}}",
                "actions": [
                  {"actionKey": "PROCEED_TO_TRAVEL", "label": "Proceed to Travel", "type": "primary", "icon": "flight", "apiKey": null, "condition": {"field": "involvesTravel", "operator": "EQUALS", "value": true}},
                  {"actionKey": "HOME", "label": "Go to Home", "type": "secondary", "icon": "home", "apiKey": null, "condition": null}
                ]
              }
            },
            {
              "scenarioKey": "OFFLINE_PAYMENT_OTHER",
              "condition": {
                "operator": "AND",
                "conditions": [
                  {"field": "paymentMode", "operator": "EQUALS", "value": "offline"},
                  {"field": "offlinePaymentMethod", "operator": "NOT_EQUALS", "value": "Cheque"}
                ]
              },
              "screen": {
                "title": "Payment Submitted!",
                "message": "We acknowledge your payment towards {{programName}} ({{programStartDate}} to {{programEndDate}}). You can proceed with travel details.",
                "icon": "check_circle",
                "showLogo": true,
                "showBanner": true,
                "logoUrl": "{{logoUrl}}",
                "bannerImageUrl": "{{bannerImageUrl}}",
                "actions": [
                  {"actionKey": "PROCEED_TO_TRAVEL", "label": "Proceed to Travel", "type": "primary", "icon": "flight", "apiKey": null, "condition": {"field": "involvesTravel", "operator": "EQUALS", "value": true}},
                  {"actionKey": "HOME", "label": "Go to Home", "type": "secondary", "icon": "home", "apiKey": null, "condition": null}
                ]
              }
            }
          ],
          "defaultScreen": {
            "title": "Payment Submitted!",
            "message": "Your payment for {{programName}} has been submitted successfully.",
            "icon": "check_circle",
            "showLogo": true,
            "showBanner": true,
            "logoUrl": "{{logoUrl}}",
            "bannerImageUrl": "{{bannerImageUrl}}",
            "actions": [
              {"actionKey": "HOME", "label": "Go to Home", "type": "secondary", "icon": "home", "apiKey": null, "condition": null}
            ]
          }
        }
      }]
    }
  ]'::jsonb,
    updated_at = NOW(),
    updated_by = -2
FROM workflow_configuration wc
WHERE ws.workflow_config_id = wc.id
  AND wc.program_type_key = 'PT_ENTRAINMENT'
  AND wc.deleted_at IS NULL
  AND ws.stage_key = 'PAYMENT'
  AND ws.deleted_at IS NULL;

-- ================================================
-- TRAVEL stage: add {registrationStatus NOT_IN ['rejected']} to prerequisite/section gating.
-- Success-screen copy on SAVE_TRAVEL is unchanged (still the flat successScreen), so
-- action_buttons is rewritten here only to carry the same guard into SAVE_TRAVEL's permission
-- condition — its onSuccess block is untouched.
-- ================================================
UPDATE workflow_stage ws
SET prerequisite_rules_config = '{
    "requiresAuth": true,
    "requiredStages": ["REGISTRATION", "PAYMENT"],
    "conditions": [
      {"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_SHOBA", "ROLE_FINANCE_MANAGER"], "condition": {"operator": "AND", "conditions": [
        {"field": "involvesTravel", "operator": "EQUALS", "value": true},
        {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
        {"operator": "OR", "conditions": [
          {"field": "paymentStatus", "operator": "IN", "value": ["online_completed", "offline_completed", "offline_pending", "completed"]},
          {"field": "isFreeSeat", "operator": "EQUALS", "value": true}
        ]},
        {"operator": "OR", "conditions": [
          {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
          {"field": "reservedLink", "operator": "EQUALS", "value": true}
        ]}
      ]}, "message": "Payment must be completed or seat must be free, registration must not be rejected, and not waitlisted (or admin-released)"}
    ]
  }'::jsonb,
    execution_rules_config = '{
    "sections": [
      {
        "sectionKey": "FS_TRAVELPLAN",
        "permissions": {
          "create": [
            {"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
              {"field": "involvesTravel", "operator": "EQUALS", "value": true},
              {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
              {"operator": "OR", "conditions": [
                {"field": "paymentStatus", "operator": "IN", "value": ["online_completed", "offline_completed", "offline_pending", "completed"]},
                {"field": "isFreeSeat", "operator": "EQUALS", "value": true}
              ]},
              {"operator": "OR", "conditions": [
                {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                {"field": "reservedLink", "operator": "EQUALS", "value": true}
              ]}
            ]}},
            {"roles": ["ROLE_RELATIONAL_MANAGER", "ROLE_SHOBA", "ROLE_ADMIN"], "condition": null}
          ],
          "read": [{"roles": ["ALL"], "condition": null}],
          "update": [
            {"roles": ["ROLE_VIEWER"], "condition": {"field": "travelPlanStatus", "operator": "NOT_EQUALS", "value": "completed"}},
            {"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_SHOBA", "ROLE_FINANCE_MANAGER"], "condition": null}
          ],
          "delete": [{"roles": [], "condition": null}]
        }
      }
    ]
  }'::jsonb,
    action_buttons = '[
    {
      "actionKey": "CANCEL",
      "label": "Cancel",
      "type": "secondary",
      "icon": "close",
      "apiKey": null,
      "permissions": [{"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_VIEWER"], "condition": null}],
      "onSuccess": [{"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER", "ROLE_VIEWER"], "showToast": false, "showSuccessScreen": false}]
    },
    {
      "actionKey": "SAVE_TRAVEL",
      "label": "submit",
      "type": "primary",
      "icon": "send",
      "apiKey": "SAVE_TRAVEL",
      "permissions": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
        {"field": "involvesTravel", "operator": "EQUALS", "value": true},
        {"field": "registrationStatus", "operator": "NOT_IN", "value": ["rejected"]},
        {"operator": "OR", "conditions": [
          {"field": "paymentStatus", "operator": "IN", "value": ["online_completed", "offline_completed", "offline_pending", "completed"]},
          {"field": "isFreeSeat", "operator": "EQUALS", "value": true}
        ]},
        {"operator": "OR", "conditions": [
          {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
          {"field": "reservedLink", "operator": "EQUALS", "value": true}
        ]}
      ]}}],
      "onSuccess": [{
        "roles": ["ROLE_VIEWER"],
        "showToast": true,
        "toast": {"message": "Travel details submitted successfully!", "type": "success"},
        "showSuccessScreen": true,
        "successScreen": {
          "title": "Registration Complete!",
          "message": "Your registration for {{programName}} is now complete. We look forward to seeing you at {{programVenue}} from {{programStartDate}} to {{programEndDate}}!",
          "icon": "check_circle",
          "showLogo": true,
          "showBanner": true,
          "logoUrl": "{{logoUrl}}",
          "bannerImageUrl": "{{bannerImageUrl}}",
          "actions": [
            {"actionKey": "HOME", "label": "Go to Home", "type": "secondary", "icon": "home", "apiKey": null, "condition": null}
          ]
        }
      }]
    }
  ]'::jsonb,
    updated_at = NOW(),
    updated_by = -2
FROM workflow_configuration wc
WHERE ws.workflow_config_id = wc.id
  AND wc.program_type_key = 'PT_ENTRAINMENT'
  AND wc.deleted_at IS NULL
  AND ws.stage_key = 'TRAVEL'
  AND ws.deleted_at IS NULL;

-- ================================================
-- Link LOGO_URL and BANNER_IMAGE_URL variables — the screens patched above now reference
-- {{logoUrl}}/{{bannerImageUrl}}, so the live workflow needs these two variables linked for
-- WorkflowVariableService.buildWorkflowQuery to select them (unlinked variables are never
-- added to the generated query, so the placeholders would otherwise never resolve). Same
-- pattern as workflow-config-tat-ta26-payment-final-update.sql's RESERVED_LINK/LOGO_URL/
-- BANNER_IMAGE_URL linking block.
-- ================================================
DO $$
DECLARE
    v_workflow_id INT;
    v_var_id      INT;
    v_order       INT;
BEGIN
    SELECT id INTO v_workflow_id
    FROM workflow_configuration
    WHERE program_type_key = 'PT_ENTRAINMENT'
      AND deleted_at IS NULL
    LIMIT 1;

    IF v_workflow_id IS NULL THEN
        RAISE NOTICE 'Live PT_ENTRAINMENT workflow_configuration not found — skipping variable linking';
        RETURN;
    END IF;

    SELECT COALESCE(MAX(variable_order), 0) + 1 INTO v_order
    FROM workflow_configuration_variable
    WHERE workflow_config_id = v_workflow_id;

    FOR v_var_id IN
        SELECT id FROM workflow_variable_master
        WHERE variable_key IN ('LOGO_URL', 'BANNER_IMAGE_URL')
        ORDER BY variable_key
    LOOP
        INSERT INTO workflow_configuration_variable
            (workflow_config_id, variable_master_id, variable_order, is_required, created_by, updated_by)
        VALUES
            (v_workflow_id, v_var_id, v_order, false, -2, -2)
        ON CONFLICT DO NOTHING;
        v_order := v_order + 1;
    END LOOP;

    RAISE NOTICE 'Linked LOGO_URL and BANNER_IMAGE_URL to live PT_ENTRAINMENT workflow (ID: %)', v_workflow_id;
END $$;

-- =====================================================
-- VERIFICATION QUERIES (commented out — uncomment to run manually after migration)
-- =====================================================

-- SELECT workflow_key, workflow_version FROM workflow_configuration
-- WHERE program_type_key = 'PT_ENTRAINMENT' AND deleted_at IS NULL;

-- SELECT stage_key, prerequisite_rules_config, execution_rules_config, action_buttons
-- FROM workflow_stage ws
-- JOIN workflow_configuration wc ON wc.id = ws.workflow_config_id
-- WHERE wc.program_type_key = 'PT_ENTRAINMENT' AND wc.deleted_at IS NULL
--   AND ws.stage_key IN ('PAYMENT', 'TRAVEL') AND ws.deleted_at IS NULL;

UPDATE program_v1
SET workflow_id = (SELECT id FROM workflow_configuration WHERE workflow_key = 'WF_ENTRAINMENT_V8')
WHERE program_type_id = (SELECT id FROM program_type_v1 WHERE key = 'PT_ENTRAINMENT');
