-- =====================================================================================
-- CANONICAL CONSOLIDATED UPDATE — TAT TA26 (WF_TAT_TA26_V6) PAYMENT stage
-- =====================================================================================
-- Single idempotent script that brings the PAYMENT stage to its FINAL state. Run this
-- against an ALREADY-DEPLOYED workflow (where the original insert ran before these
-- changes). It is the union of all prior PAYMENT updates and supersedes them all:
--   * workflow-config-tat-ta26-payment-waitlist-release-update.sql  (reservedLink gating)
--   * workflow-config-tat-ta26-payment-ack-screens-update.sql       (ack/confirm screens)
--   * workflow-config-tat-ta26-payment-block-rejected-update.sql    (rejected gating)
--
-- Gating (all four ROLE_VIEWER gates: prerequisite, section create, section update,
-- MAKE_PAYMENT):
--   * top-level AND guard  {registrationStatus NOT_IN ['rejected']}  — rejected
--     registrations can never reach payment (no release path).
--   * OR(registrationStatus NOT_IN ['waitlisted'], reservedLink = true) — admin-released
--     waitlisters (reserved_link = true) can still pay.
--
-- Screens: multi-scenario successScreenConfig —
--   * online  -> "Registration Confirmation"   (variant success, keeps Download Invoice)
--   * offline -> "Registration Acknowledgement" (variant info, "subject to realization")
--   * plus edit-requested, free-seat, and default screens. All home buttons "Home".
--
-- Also links RESERVED_LINK (alias 'reservedLink'), LOGO_URL and BANNER_IMAGE_URL so the
-- reservedLink field resolves and the screens render the logo/banner. Without RESERVED_LINK
-- linked, reservedLink resolves to NULL and released waitlisters stay blocked.
--
-- Safe to re-run: the UPDATE is deterministic and variable links use ON CONFLICT DO NOTHING.
-- =====================================================================================

UPDATE workflow_stage
SET
  prerequisite_rules_config = '{
    "requiresAuth": true,
    "requiredStages": ["REGISTRATION"],
    "conditions": [
      {"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
        {"field": "requiresPayment", "operator": "EQUALS", "value": true},
        {"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, registration must not be rejected or waitlisted, and payment must be pending or have an active edit request"},
      {"roles": ["ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER", "ROLE_FINANCE_MANAGER"], "condition": null, "message": null}
    ]
  }'::jsonb,
  execution_rules_config = '{
    "sections": [
      {
        "sectionKey": "FS_PAYMENTINVOICE",
        "permissions": {
          "create": [
            {"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
              {"field": "requiresPayment", "operator": "EQUALS", "value": true},
              {"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}
              ]}
            ]}},
            {"roles": ["ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER", "ROLE_FINANCE_MANAGER"], "condition": null}
          ],
          "read": [{"roles": ["ALL"], "condition": null}],
          "update": [
            {
              "roles": ["ROLE_VIEWER"],
              "condition": {
                "operator": "AND",
                "conditions": [
                  {"field": "requiresPayment", "operator": "EQUALS", "value": true},
                  {"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_SHOBA", "ROLE_RELATIONAL_MANAGER", "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_SHOBA", "ROLE_RELATIONAL_MANAGER", "ROLE_VIEWER", "ROLE_FINANCE_MANAGER"], "condition": null}],
      "onSuccess": [{"roles": ["ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER", "ROLE_VIEWER", "ROLE_FINANCE_MANAGER"], "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": "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": "{{seekerName}}, your payment edit request for {{programName}} has been submitted.<br>You can edit your payment details once the request is approved.",
                  "variant": "info",
                  "showLogo": true,
                  "showBanner": true,
                  "logoUrl": "{{logoUrl}}",
                  "bannerImageUrl": "{{bannerImageUrl}}",
                  "actions": [
                    {
                      "actionKey": "HOME",
                      "label": "home",
                      "type": "secondary",
                      "apiKey": null
                    },
                    {
                      "actionKey": "UPDATE_PAYMENT",
                      "label": "update payment",
                      "type": "secondary",
                      "apiKey": "UPDATE_PAYMENT_DETAILS"
                    }
                  ]
                }
              },
              {
                "scenarioKey": "ONLINE_PAYMENT_SUCCESS",
                "condition": {
                  "operator": "AND",
                  "conditions": [
                    {"field": "paymentMode", "operator": "EQUALS", "value": "online"},
                    {"field": "paymentStatus", "operator": "IN", "value": ["online_completed", "completed"]}
                  ]
                },
                "screen": {
                  "title": "Registration Confirmation",
                  "message": "{{seekerName}}, congratulations on your successful registration for ''{{programName}}'', a life-transforming 12-session experience with <span style=\"color:#e11d2a;font-weight:600\">Mahatria</span>.<br><strong>Your registration number is: {{registrationNumber}}</strong><br>The access details and recommended digital disciplines will be communicated to you a day before the commencement of the program.<br><strong>Let this ''<span style=\"color:#1e3a8a\">TAT</span>'' magnetise the best of everything in your life.</strong>",
                  "variant": "success",
                  "showLogo": true,
                  "showBanner": true,
                  "logoUrl": "{{logoUrl}}",
                  "bannerImageUrl": "{{bannerImageUrl}}",
                  "actions": [
                    {
                      "actionKey": "DOWNLOAD_INVOICE",
                      "label": "download invoice",
                      "type": "secondary",
                      "apiKey": "DOWNLOAD_INVOICE"
                    },
                    {
                      "actionKey": "HOME",
                      "label": "home",
                      "type": "secondary",
                      "apiKey": null
                    }
                  ]
                }
              },
              {
                "scenarioKey": "OFFLINE_PAYMENT_CHEQUE",
                "condition": {
                  "operator": "AND",
                  "conditions": [
                    {"field": "paymentMode", "operator": "EQUALS", "value": "offline"},
                    {"field": "offlinePaymentMethod", "operator": "EQUALS", "value": "Cheque"}
                  ]
                },
                "screen": {
                  "title": "Registration Acknowledgement",
                  "message": "{{seekerName}}, we acknowledge your registration for ''{{programName}}'', a life-transforming 12-session experience with <span style=\"color:#e11d2a;font-weight:600\">Mahatria</span>.<br><strong>Your provisional registration number is: {{registrationNumber}}</strong><br>The access details and recommended digital disciplines will be communicated to you a day before the commencement of the program.<br><em>Note: Payment is subject to realization. A confirmation email along with the invoice will follow upon payment realization.</em><br><strong>Let this ''<span style=\"color:#1e3a8a\">TAT</span>'' magnetise the best of everything in your life.</strong>",
                  "variant": "info",
                  "showLogo": true,
                  "showBanner": true,
                  "logoUrl": "{{logoUrl}}",
                  "bannerImageUrl": "{{bannerImageUrl}}",
                  "actions": [
                    {
                      "actionKey": "HOME",
                      "label": "home",
                      "type": "secondary",
                      "apiKey": null
                    }
                  ]
                }
              },
              {
                "scenarioKey": "OFFLINE_PAYMENT_OTHER",
                "condition": {
                  "operator": "AND",
                  "conditions": [
                    {"field": "paymentMode", "operator": "EQUALS", "value": "offline"},
                    {"field": "offlinePaymentMethod", "operator": "NOT_EQUALS", "value": "Cheque"}
                  ]
                },
                "screen": {
                  "title": "Registration Acknowledgement",
                  "message": "{{seekerName}}, we acknowledge your registration for ''{{programName}}'', a life-transforming 12-session experience with <span style=\"color:#e11d2a;font-weight:600\">Mahatria</span>.<br><strong>Your provisional registration number is: {{registrationNumber}}</strong><br>The access details and recommended digital disciplines will be communicated to you a day before the commencement of the program.<br><em>Note: Payment is subject to realization. A confirmation email along with the invoice will follow upon payment realization.</em><br><strong>Let this ''<span style=\"color:#1e3a8a\">TAT</span>'' magnetise the best of everything in your life.</strong>",
                  "variant": "info",
                  "showLogo": true,
                  "showBanner": true,
                  "logoUrl": "{{logoUrl}}",
                  "bannerImageUrl": "{{bannerImageUrl}}",
                  "actions": [
                    {
                      "actionKey": "HOME",
                      "label": "home",
                      "type": "secondary",
                      "apiKey": null
                    }
                  ]
                }
              },
              {
                "scenarioKey": "FREE_SEAT_REGISTRATION",
                "condition": {
                  "operator": "AND",
                  "conditions": [
                    {"field": "isFreeSeat", "operator": "EQUALS", "value": true},
                    {"field": "registrationStatus", "operator": "NOT_EQUALS", "value": "cancelled"}
                  ]
                },
                "screen": {
                  "title": "Registration Successful!",
                  "message": "{{seekerName}}, your registration for {{programName}} is successful.<br><strong>Registration Number: {{registrationNumber}}</strong><br>Program Date: {{programStartDate}} to {{programEndDate}}",
                  "variant": "success",
                  "showLogo": true,
                  "showBanner": true,
                  "logoUrl": "{{logoUrl}}",
                  "bannerImageUrl": "{{bannerImageUrl}}",
                  "actions": [
                    {
                      "actionKey": "HOME",
                      "label": "home",
                      "type": "secondary",
                      "apiKey": null
                    },
                    {
                      "actionKey": "VIEW_SUMMARY",
                      "label": "summary",
                      "type": "secondary",
                      "apiKey": "VIEW_REGISTRATION_SUMMARY"
                    }
                  ]
                }
              }
            ],
            "defaultScreen": {
              "title": "Payment Submitted!",
              "message": "{{seekerName}}, your payment for {{programName}} has been submitted successfully.<br><strong>Registration Number: {{registrationNumber}}</strong>",
              "variant": "success",
              "showLogo": true,
              "showBanner": true,
              "logoUrl": "{{logoUrl}}",
              "bannerImageUrl": "{{bannerImageUrl}}",
              "actions": [
                {
                  "actionKey": "HOME",
                  "label": "home",
                  "type": "secondary",
                  "apiKey": null
                }
              ]
            }
          }
        }
      ]
    }
  ]'::jsonb,
  updated_by = -2,
  updated_at = NOW()
WHERE workflow_config_id = (SELECT id FROM workflow_configuration WHERE workflow_key = 'WF_TAT_TA26_V6')
  AND stage_key = 'PAYMENT';

-- ================================================
-- Link RESERVED_LINK, LOGO_URL and BANNER_IMAGE_URL variables
-- ================================================
DO $$
DECLARE
    v_workflow_id INT;
    v_var_id      INT;
    v_order       INT;
BEGIN
    SELECT id INTO v_workflow_id
    FROM workflow_configuration
    WHERE workflow_key = 'WF_TAT_TA26_V6'
    LIMIT 1;

    IF v_workflow_id IS NULL THEN
        RAISE NOTICE 'WF_TAT_TA26_V6 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 ('RESERVED_LINK', '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 RESERVED_LINK, LOGO_URL and BANNER_IMAGE_URL to WF_TAT_TA26_V6 (ID: %)', v_workflow_id;
END $$;

-- ================================================
-- Verification Query (optional)
-- ================================================
-- SELECT stage_key,
--        prerequisite_rules_config -> 'conditions' -> 0 -> 'condition' -> 'conditions' AS prereq_conditions,
--        action_buttons -> 1 -> 'onSuccess' -> 0 -> 'successScreenConfig' -> 'scenarios' AS screens
-- FROM workflow_stage
-- WHERE stage_key = 'PAYMENT'
--   AND workflow_config_id = (SELECT id FROM workflow_configuration WHERE workflow_key = 'WF_TAT_TA26_V6');
