-- ================================================
-- Custom Program Type + Universal Workflow Seed
-- 2026-05-13
-- No schema changes. Seed data only.
-- Idempotent: all inserts use ON CONFLICT DO NOTHING.
-- ================================================

BEGIN;

-- ================================================
-- 1. program_type_v1 — Insert CUSTOM type
-- Flags default to false. Each program created under
-- CUSTOM sets its own flags in program_v1.
-- ================================================

INSERT INTO program_type_v1 (
    -- NOT NULL columns (required)
    mode_of_operation,
    name,
    key,
    no_of_session,
    has_goodies,
    allow_save_as_draft,
    currency,
    max_proxy_registrations,
    -- Override non-null defaults that don't apply to CUSTOM
    requires_payment,
    requires_attendance_all_sessions,
    has_multiple_sessions,
    max_session_duration_days,
    frequency,
    registration_level,
    gst_percentage,
    is_active
)
VALUES (
    'offline',  -- mode_of_operation (confirm with Product Owner — Open Question 1)
    'Custom Program',
    'CUSTOM',
    0,          -- no_of_session
    false,      -- has_goodies
    false,      -- allow_save_as_draft
    'INR',      -- currency
    0,          -- max_proxy_registrations
    NULL,       -- requires_payment    — not fixed at type level; set per program
    NULL,       -- requires_attendance_all_sessions
    NULL,       -- has_multiple_sessions
    NULL,       -- max_session_duration_days
    'yearly',   -- frequency
    'program',  -- registration_level
    NULL,       -- gst_percentage
    true        -- is_active
)
ON CONFLICT (key) DO NOTHING;


-- ================================================
-- 2. workflow_variable_master — Program flag variables
-- Expose program_v1 flag columns to the workflow engine
-- so that prerequisite_rules_config can reference them.
-- ================================================

INSERT INTO workflow_variable_master (
    variable_key,
    variable_name,
    source_table,
    source_column,
    alias_name,
    data_type,
    join_type,
    join_condition,
    table_alias,
    description,
    is_active,
    is_mandatory_join,
    execution_order,
    variable_scope,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES
(
    'PROGRAM_REQUIRES_PAYMENT',
    'Program Requires Payment',
    'program_v1',
    'requires_payment',
    'requiresPayment',
    'boolean',
    'INNER',
    'pv.id = pr.program_id AND pv.deleted_at IS NULL',
    'pv',
    'Whether this program requires payment. Read from program_v1.',
    true,
    true,
    50,
    'DEFAULT',
    -2, -2, NOW(), NOW()
),
(
    'PROGRAM_INVOLVES_TRAVEL',
    'Program Involves Travel',
    'program_v1',
    'involves_travel',
    'involvesTravel',
    'boolean',
    'INNER',
    'pv.id = pr.program_id AND pv.deleted_at IS NULL',
    'pv',
    'Whether this program involves travel. Read from program_v1.',
    true,
    true,
    51,
    'DEFAULT',
    -2, -2, NOW(), NOW()
),
(
    'PROGRAM_HAS_GOODIES',
    'Program Has Goodies',
    'program_v1',
    'has_goodies',
    'hasGoodies',
    'boolean',
    'INNER',
    'pv.id = pr.program_id AND pv.deleted_at IS NULL',
    'pv',
    'Whether this program has goodies. Read from program_v1.',
    true,
    true,
    52,
    'DEFAULT',
    -2, -2, NOW(), NOW()
),
(
    'PROGRAM_REQUIRES_APPROVAL',
    'Program Requires Approval',
    'program_v1',
    'requires_approval',
    'requiresApproval',
    'boolean',
    'INNER',
    'pv.id = pr.program_id AND pv.deleted_at IS NULL',
    'pv',
    'Whether this program requires admin approval. Read from program_v1.',
    true,
    true,
    53,
    'DEFAULT',
    -2, -2, NOW(), NOW()
)
ON CONFLICT (variable_key) DO NOTHING;

INSERT INTO workflow_variable_master (
    variable_key,
    variable_name,
    source_table,
    source_column,
    alias_name,
    data_type,
    join_type,
    join_condition,
    table_alias,
    description,
    is_active,
    is_mandatory_join,
    execution_order,
    variable_scope,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES (
    'RESERVED_LINK',
    'Reserved Link',
    'hdb_program_registration',
    'reserved_link',
    'reservedLink',
    'boolean',
    NULL,
    NULL,
    NULL,
    'Whether an admin has released this waitlisted registration, allowing it to proceed to payment.',
    true,
    false,
    16,
    'DEFAULT',
    -2, -2, NOW(), NOW()
)
ON CONFLICT (variable_key) DO NOTHING;


-- ================================================
-- 3. workflow_configuration — CUSTOM_TYPE_WORKFLOW
-- Single workflow for all CUSTOM programs.
-- ================================================

INSERT INTO workflow_configuration (
    workflow_key,
    workflow_name,
    program_type_key,
    program_type_id,
    workflow_version,
    lifecycle_status,
    description,
    data_query_template,
    registration_rules,
    initial_stage,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES (
    'CUSTOM_TYPE_WORKFLOW',
    'Universal Program Registration Workflow',
    'CUSTOM',
    (SELECT id FROM program_type_v1 WHERE key = 'CUSTOM'),
    '1.0.0',
    'ACTIVE',
    'Universal workflow for CUSTOM program type. Active stages are determined by the program''s feature flags at runtime.',
    NULL,
    '{
        "criteria": [
            {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"}
        ],
        "message": "Registration is completed when all active stages are done."
    }'::jsonb,
    '{
        "stageKey": "REGISTRATION",
        "stageName": "Basic Details",
        "isOptional": false,
        "description": "Always the first stage"
    }'::jsonb,
    -2,
    -2,
    NOW(),
    NOW()
)
ON CONFLICT DO NOTHING;


-- ================================================
-- 4. workflow_stage — Universal Workflow stages
--
-- Stage gating:
--   REGISTRATION      → always active
--   MAHATRIA_SECTIONS → requiresApproval = true
--                       (before admin approval, so no approvalStatus gate)
--   PAYMENT           → requiresPayment = true
--                       AND (requiresApproval = false OR approvalStatus = approved)
--   TRAVEL            → involvesTravel = true
--                       AND (requiresApproval = false OR approvalStatus = approved)
--   GOODIES           → hasGoodies = true
--                       AND (requiresApproval = false OR approvalStatus = approved)
-- ================================================

-- Stage 1: REGISTRATION — always active

INSERT INTO workflow_stage (
    workflow_config_id,
    stage_key,
    stage_name,
    stage_order,
    stage_category,
    is_optional,
    description,
    prerequisite_rules_config,
    execution_rules_config,
    action_buttons,
    completion_actions_config,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES (
    (SELECT id FROM workflow_configuration WHERE workflow_key = 'CUSTOM_TYPE_WORKFLOW'),
    'REGISTRATION',
    'Basic Details',
    1,
    'FORM',
    false,
    'Seeker fills and submits basic registration details. Always active.',
    '{
        "requiresAuth": true,
        "requiredStages": [],
        "conditions": [
            {
                "roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER"],
                "condition": null,
                "message": "Login required to register"
            }
        ]
    }'::jsonb,
    '{
        "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,
    '[
        {
            "actionKey": "CANCEL",
            "label": "Cancel",
            "type": "secondary",
            "icon": "close",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER"], "condition": null}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        },
        {
            "actionKey": "SAVE_DRAFT",
            "label": "Save Draft",
            "type": "secondary",
            "icon": "save",
            "apiKey": "SAVE_DRAFT",
            "permissions": [
                {"roles": ["ROLE_VIEWER"], "condition": {"field": "basicDetailsFilled", "operator": "NOT_EQUALS", "value": "completed"}},
                {"roles": ["ROLE_RELATIONAL_MANAGER"], "condition": {"operator": "AND", "conditions": [
                    {"field": "allowsProxyRegistration", "operator": "EQUALS", "value": true},
                    {"field": "basicDetailsFilled", "operator": "NOT_EQUALS", "value": "completed"}
                ]}}
            ],
            "onSuccess": [
                {"roles": ["ROLE_VIEWER"], "showToast": true, "toast": {"message": "Draft saved successfully!", "type": "success"}, "showSuccessScreen": false},
                {"roles": ["ROLE_RELATIONAL_MANAGER"], "showToast": true, "toast": {"message": "Draft saved successfully!", "type": "success"}, "showSuccessScreen": false}
            ]
        },
        {
            "actionKey": "SUBMIT_REGISTRATION",
            "label": "Submit",
            "type": "primary",
            "icon": "send",
            "apiKey": "SUBMIT_REGISTRATION",
            "permissions": [{"roles": ["ROLE_VIEWER"], "condition": {"field": "basicDetailsFilled", "operator": "NOT_EQUALS", "value": "completed"}}],
            "onSuccess": [{
                "roles": ["ROLE_VIEWER"],
                "showToast": true,
                "toast": {"message": "Registration submitted successfully!", "type": "success"},
                "showSuccessScreen": true,
                "successScreen": {
                    "title": "Registration Submitted!",
                    "message": "Your registration has been submitted successfully.",
                    "icon": "check_circle",
                    "actions": [{"actionKey": "HOME", "label": "Go to Home", "type": "primary", "icon": "home", "apiKey": null, "condition": null}]
                }
            }]
        },
        {
            "actionKey": "MAKE_PAYMENT",
            "label": "Make Payment",
            "type": "secondary",
            "icon": "arrow_forward",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": {"operator": "AND", "conditions": [
                {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                {"field": "requiresPayment",    "operator": "EQUALS", "value": true},
                {"field": "requiresApproval",   "operator": "EQUALS", "value": false},
                {"field": "isFreeSeat",          "operator": "EQUALS", "value": false},
                {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                ]}
            ]}}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        },
        {
            "actionKey": "PROCEED_TO_TRAVEL",
            "label": "Proceed to Travel",
            "type": "secondary",
            "icon": "arrow_forward",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": {"operator": "AND", "conditions": [
                {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                {"field": "involvesTravel",     "operator": "EQUALS", "value": true},
                {"field": "requiresApproval",   "operator": "EQUALS", "value": false},
                {"field": "requiresPayment",    "operator": "EQUALS", "value": false},
                {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                ]}
            ]}}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        }
    ]'::jsonb,
    '{"conditional": true, "conditions": [{"when": {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"}, "then": {"stageStatus": "completed", "markStageComplete": true}}]}'::jsonb,
    -2, -2, NOW(), NOW()
);


-- Stage 2: MAHATRIA_SECTIONS
-- Active when requiresApproval = true.
-- Seekers fill Mahatria questions so the admin can review and approve.
-- Comes BEFORE admin approval — no approvalStatus gate on this stage.

INSERT INTO workflow_stage (
    workflow_config_id,
    stage_key,
    stage_name,
    stage_order,
    stage_category,
    is_optional,
    description,
    prerequisite_rules_config,
    execution_rules_config,
    action_buttons,
    completion_actions_config,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES (
    (SELECT id FROM workflow_configuration WHERE workflow_key = 'CUSTOM_TYPE_WORKFLOW'),
    'MAHATRIA_SECTIONS',
    'Mahatria Sections',
    2,
    'FORM',
    false,
    'Seeker answers Mahatria-specific questions for admin review. Active only when program requires_approval = true.',
    '{
        "requiresAuth": true,
        "requiredStages": ["REGISTRATION"],
        "conditions": [
            {
                "roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER"],
                "condition": {
                    "operator": "AND",
                    "conditions": [
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"field": "requiresApproval",   "operator": "EQUALS", "value": true},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]}
                    ]
                },
                "message": "Basic details completed, program requires approval, and seeker is not waitlisted."
            }
        ]
    }'::jsonb,
    '{
        "sections": [
            {
                "sectionKey": "FS_MAHATRIA_SECTIONS",
                "permissions": {
                    "create": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"field": "requiresApproval",   "operator": "EQUALS", "value": true},
                        {"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": {"field": "mahatriaSectionsStatus", "operator": "NOT_EQUALS", "value": "completed"}},
                        {"roles": ["ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER"], "condition": null}
                    ],
                    "delete": [{"roles": [], "condition": null}]
                }
            }
        ]
    }'::jsonb,
    '[
        {
            "actionKey": "CANCEL",
            "label": "Cancel",
            "type": "secondary",
            "icon": "close",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_SHOBA", "ROLE_RELATIONAL_MANAGER"], "condition": null}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        },
        {
           "actionKey": "SUBMIT_REGISTRATION",
            "label": "Submit",
            "type": "primary",
            "icon": "send",
            "apiKey": "SUBMIT_REGISTRATION",
            "permissions": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                {"field": "requiresApproval",   "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": "Mahatria sections submitted successfully!", "type": "success"},
                "showSuccessScreen": true,
                "successScreen": {
                    "title": "Submitted!",
                    "message": "Your responses have been submitted.",
                    "icon": "check_circle",
                    "actions": [{"actionKey": "HOME", "label": "Go to Home", "type": "primary", "icon": "home", "apiKey": null, "condition": null}]
                }
            }]
        },
        {
            "actionKey": "MAKE_PAYMENT",
            "label": "Make Payment",
            "type": "secondary",
            "icon": "arrow_forward",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": {"operator": "AND", "conditions": [
                {"field": "mahatriaSectionsStatus", "operator": "EQUALS", "value": "completed"},
                {"field": "approvalStatus",         "operator": "EQUALS", "value": "approved"},
                {"field": "requiresPayment",        "operator": "EQUALS", "value": true},
                {"field": "isFreeSeat",              "operator": "EQUALS", "value": false},
                {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                ]}
            ]}}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        },
        {
            "actionKey": "PROCEED_TO_TRAVEL",
            "label": "Proceed to Travel",
            "type": "secondary",
            "icon": "arrow_forward",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": {"operator": "AND", "conditions": [
                {"field": "mahatriaSectionsStatus", "operator": "EQUALS", "value": "completed"},
                {"field": "approvalStatus",         "operator": "EQUALS", "value": "approved"},
                {"field": "involvesTravel",         "operator": "EQUALS", "value": true},
                {"field": "requiresPayment",        "operator": "EQUALS", "value": false},
                {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                ]}
            ]}}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        }
    ]'::jsonb,
    '{"conditional": true, "conditions": [{"when": {"field": "mahatriaSectionsStatus", "operator": "EQUALS", "value": "completed"}, "then": {"stageStatus": "completed", "markStageComplete": true}}]}'::jsonb,
    -2, -2, NOW(), NOW()
);


-- Stage 3: PAYMENT
-- Active when requiresPayment = true AND (requiresApproval = false OR approvalStatus = approved).

INSERT INTO workflow_stage (
    workflow_config_id,
    stage_key,
    stage_name,
    stage_order,
    stage_category,
    is_optional,
    description,
    prerequisite_rules_config,
    execution_rules_config,
    action_buttons,
    completion_actions_config,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES (
    (SELECT id FROM workflow_configuration WHERE workflow_key = 'CUSTOM_TYPE_WORKFLOW'),
    'PAYMENT',
    'Payment & Invoice',
    3,
    'FORM',
    false,
    'Process payment. Active only when program requires_payment = true.',
    '{
        "requiresAuth": true,
        "requiredStages": ["REGISTRATION"],
        "conditions": [
            {
                "roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"],
                "condition": {
                    "operator": "AND",
                    "conditions": [
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"field": "requiresPayment",    "operator": "EQUALS", "value": true},
                        {"field": "isFreeSeat",          "operator": "EQUALS", "value": false},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]},
                        {"operator": "OR", "conditions": [
                            {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                            {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                        ]}
                    ]
                },
                "message": "Basic details completed, payment required, not free seat, not waitlisted (or reserved), and approved (or program does not require approval)."
            }
        ]
    }'::jsonb,
    '{
        "sections": [
            {
                "sectionKey": "FS_PAYMENTINVOICE",
                "permissions": {
                    "create": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                        {"field": "basicDetailsFilled", "operator": "EQUALS",  "value": "completed"},
                        {"field": "requiresPayment",    "operator": "EQUALS",  "value": true},
                        {"field": "isFreeSeat",          "operator": "EQUALS",  "value": false},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]},
                        {"operator": "OR", "conditions": [
                            {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                            {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                        ]}
                    ]}}],
                    "read":   [{"roles": ["ALL"], "condition": null}],
                    "update": [{"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": null}],
                    "delete": [{"roles": [], "condition": null}]
                }
            }
        ]
    }'::jsonb,
    '[
        {
            "actionKey": "CANCEL",
            "label": "Cancel",
            "type": "secondary",
            "icon": "close",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": null}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        },
        {
            "actionKey": "SUBMIT_PAYMENT",
            "label": "Submit Payment",
            "type": "primary",
            "icon": "payment",
            "apiKey": "SUBMIT_PAYMENT",
            "permissions": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                {"field": "requiresPayment",    "operator": "EQUALS", "value": true},
                {"field": "isFreeSeat",          "operator": "EQUALS", "value": false},
                {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                ]},
                {"operator": "OR", "conditions": [
                    {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                    {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                ]}
            ]}}],
            "onSuccess": [{
                "roles": ["ROLE_VIEWER"],
                "showToast": true,
                "toast": {"message": "Payment submitted successfully!", "type": "success"},
                "showSuccessScreen": true,
                "successScreen": {
                    "title": "Payment Completed!",
                    "message": "Your payment has been processed. You will be notified of next steps.",
                    "icon": "check_circle",
                    "actions": [{"actionKey": "HOME", "label": "Home", "type": "secondary", "icon": "home", "apiKey": null, "condition": null}]
                }
            }]
        },
        {
            "actionKey": "PROCEED_TO_TRAVEL",
            "label": "Proceed to Travel",
            "type": "secondary",
            "icon": "arrow_forward",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": {"operator": "AND", "conditions": [
                {"field": "paymentStatus",  "operator": "IN",     "value": ["online_completed", "offline_completed", "completed"]},
                {"field": "involvesTravel", "operator": "EQUALS", "value": true},
                {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                ]},
                {"operator": "OR", "conditions": [
                    {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                    {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                ]}
            ]}}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        }
    ]'::jsonb,
    '{"conditional": true, "conditions": [{"when": {"field": "paymentStatus", "operator": "IN", "value": ["online_completed", "offline_completed", "completed"]}, "then": {"stageStatus": "completed", "markStageComplete": true}}]}'::jsonb,
    -2, -2, NOW(), NOW()
);


-- Stage 4: TRAVEL
-- Active when involvesTravel = true AND (requiresApproval = false OR approvalStatus = approved).
-- Note: modeOfOperation ONLINE programs cannot have involvesTravel = true by product rule.

INSERT INTO workflow_stage (
    workflow_config_id,
    stage_key,
    stage_name,
    stage_order,
    stage_category,
    is_optional,
    description,
    prerequisite_rules_config,
    execution_rules_config,
    action_buttons,
    completion_actions_config,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES (
    (SELECT id FROM workflow_configuration WHERE workflow_key = 'CUSTOM_TYPE_WORKFLOW'),
    'TRAVEL',
    'Travel',
    4,
    'FORM',
    false,
    'Submit travel info and plan. Active only when program involves_travel = true and mode is not ONLINE.',
    '{
        "requiresAuth": true,
        "requiredStages": ["REGISTRATION"],
        "conditions": [
            {
                "roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"],
                "condition": {
                    "operator": "AND",
                    "conditions": [
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"field": "involvesTravel",     "operator": "EQUALS", "value": true},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]},
                        {"operator": "OR", "conditions": [
                            {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                            {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                        ]}
                    ]
                },
                "message": "Basic details completed, travel enabled, not waitlisted (or reserved), and approved (or program does not require approval)."
            }
        ]
    }'::jsonb,
    '{
        "sections": [
            {
                "sectionKey": "FS_TRAVELINFO",
                "permissions": {
                    "create": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                        {"field": "involvesTravel",     "operator": "EQUALS", "value": true},
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]},
                        {"operator": "OR", "conditions": [
                            {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                            {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                        ]}
                    ]}}],
                    "read":   [{"roles": ["ALL"], "condition": null}],
                    "update": [{"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": null}],
                    "delete": [{"roles": [], "condition": null}]
                }
            },
            {
                "sectionKey": "FS_TRAVELPLAN",
                "permissions": {
                    "create": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                        {"field": "involvesTravel",     "operator": "EQUALS", "value": true},
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]},
                        {"operator": "OR", "conditions": [
                            {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                            {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                        ]}
                    ]}}],
                    "read":   [{"roles": ["ALL"], "condition": null}],
                    "update": [{"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": null}],
                    "delete": [{"roles": [], "condition": null}]
                }
            }
        ]
    }'::jsonb,
    '[
        {
            "actionKey": "CANCEL",
            "label": "Cancel",
            "type": "secondary",
            "icon": "close",
            "apiKey": null,
            "permissions": [{"roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": null}],
            "onSuccess": [{"roles": ["ALL"], "showToast": false, "showSuccessScreen": false}]
        },
        {
            "actionKey": "SUBMIT_TRAVEL",
            "label": "Submit Travel Details",
            "type": "primary",
            "icon": "send",
            "apiKey": "SUBMIT_TRAVEL",
            "permissions": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                {"field": "involvesTravel",     "operator": "EQUALS", "value": true},
                {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                {"operator": "OR", "conditions": [
                    {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                    {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                ]},
                {"operator": "OR", "conditions": [
                    {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                    {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                ]}
            ]}}],
            "onSuccess": [{
                "roles": ["ROLE_VIEWER"],
                "showToast": true,
                "toast": {"message": "Travel details submitted successfully!", "type": "success"},
                "showSuccessScreen": true,
                "successScreen": {
                    "title": "Travel Details Saved!",
                    "message": "Travel details saved. The coordinator will reach out before the program.",
                    "icon": "check_circle",
                    "actions": [{"actionKey": "HOME", "label": "Home", "type": "primary", "icon": "home", "apiKey": null, "condition": null}]
                }
            }]
        }
    ]'::jsonb,
    '{"conditional": true, "conditions": [{"when": {"operator": "AND", "conditions": [
        {"field": "travelInfoStatus",  "operator": "EQUALS", "value": "completed"},
        {"field": "travelPlanStatus",  "operator": "EQUALS", "value": "completed"}
    ]}, "then": {"stageStatus": "completed", "markStageComplete": true}}]}'::jsonb,
    -2, -2, NOW(), NOW()
);


-- Stage 5: GOODIES
-- Active when hasGoodies = true AND (requiresApproval = false OR approvalStatus = approved).

INSERT INTO workflow_stage (
    workflow_config_id,
    stage_key,
    stage_name,
    stage_order,
    stage_category,
    is_optional,
    description,
    prerequisite_rules_config,
    execution_rules_config,
    action_buttons,
    completion_actions_config,
    created_by,
    updated_by,
    created_at,
    updated_at
) VALUES (
    (SELECT id FROM workflow_configuration WHERE workflow_key = 'CUSTOM_TYPE_WORKFLOW'),
    'GOODIES',
    'Goodies',
    5,
    'FORM',
    true,
    'Seeker provides goodies preferences. Active only when program has_goodies = true.',
    '{
        "requiresAuth": true,
        "requiredStages": ["REGISTRATION"],
        "conditions": [
            {
                "roles": ["ROLE_VIEWER", "ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"],
                "condition": {
                    "operator": "AND",
                    "conditions": [
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"field": "hasGoodies",         "operator": "EQUALS", "value": true},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]},
                        {"operator": "OR", "conditions": [
                            {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                            {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                        ]}
                    ]
                },
                "message": "Basic details completed, goodies enabled, not waitlisted (or reserved), and approved (or program does not require approval)."
            }
        ]
    }'::jsonb,
    '{
        "sections": [
            {
                "sectionKey": "FS_GOODIES",
                "permissions": {
                    "create": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                        {"field": "basicDetailsFilled", "operator": "EQUALS", "value": "completed"},
                        {"field": "hasGoodies",         "operator": "EQUALS", "value": true},
                        {"operator": "OR", "conditions": [
                            {"field": "registrationStatus", "operator": "NOT_IN", "value": ["waitlisted"]},
                            {"field": "reservedLink",        "operator": "EQUALS", "value": true}
                        ]},
                        {"operator": "OR", "conditions": [
                            {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                            {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                        ]}
                    ]}}],
                    "read":   [{"roles": ["ALL"], "condition": null}],
                    "update": [
                        {"roles": ["ROLE_VIEWER"],
                         "condition": {"field": "goodiesCutoffPassed", "operator": "EQUALS", "value": false}},
                        {"roles": ["ROLE_ADMIN", "ROLE_RELATIONAL_MANAGER"], "condition": null}
                    ],
                    "delete": [{"roles": [], "condition": null}]
                }
            }
        ]
    }'::jsonb,
    '[
        {
            "actionKey": "SAVE_GOODIES",
            "label": "Save Goodies",
            "type": "primary",
            "icon": "save",
            "apiKey": "SAVE_GOODIES",
            "permissions": [{"roles": ["ROLE_VIEWER"], "condition": {"operator": "AND", "conditions": [
                {"field": "hasGoodies",          "operator": "EQUALS", "value": true},
                {"field": "goodiesCutoffPassed", "operator": "EQUALS", "value": false},
                {"operator": "OR", "conditions": [
                    {"field": "requiresApproval", "operator": "EQUALS", "value": false},
                    {"field": "approvalStatus",   "operator": "EQUALS", "value": "approved"}
                ]}
            ]}}],
            "onSuccess": [{
                "roles": ["ROLE_VIEWER"],
                "showToast": true,
                "toast": {"message": "Goodies preferences saved!", "type": "success"},
                "showSuccessScreen": true,
                "successScreen": {
                    "title": "Goodies Saved!",
                    "message": "Your goodies preferences have been saved!",
                    "icon": "check_circle",
                    "actions": [{"actionKey": "HOME", "label": "Home", "type": "primary", "icon": "home", "apiKey": null, "condition": null}]
                }
            }]
        }
    ]'::jsonb,
    '{"conditional": true, "conditions": [{"when": {"field": "goodiesStatus", "operator": "EQUALS", "value": "completed"}, "then": {"stageStatus": "completed", "markStageComplete": true}}]}'::jsonb,
    -2, -2, NOW(), NOW()
);


-- ================================================
-- 5. workflow_configuration_variable
-- Link program flag variables + standard registration
-- variables to CUSTOM_TYPE_WORKFLOW.
-- ================================================

DO $$
DECLARE
    v_workflow_id INT;
    v_var_id      INT;
    v_order       INT := 1;
BEGIN
    SELECT id INTO v_workflow_id
    FROM workflow_configuration
    WHERE workflow_key = 'CUSTOM_TYPE_WORKFLOW'
    LIMIT 1;

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

    FOR v_var_id IN
        SELECT id FROM workflow_variable_master
        WHERE variable_key IN (
            'REGISTRATION_ID',
            'USER_ID',
            'PROGRAM_ID',
            'REGISTRATION_STATUS',
            'BASIC_DETAILS_STATUS',
            'APPROVAL_STATUS',
            'PAYMENT_STATUS',
            'PAYMENT_MODE',
            'TRAVEL_INFO_STATUS',
            'TRAVEL_PLAN_STATUS',
            'PRO_FORMA_INVOICE_PDF_URL',
            'IS_FREE_SEAT_COMPUTED',
            'IS_WAITLISTED',
            'WAITLIST_POSITION',
            'INVOICE_GENERATED',
            'INVOICE_PDF_URL',
            'INVOICE_STATUS',
            'REGISTRATION_NUMBER',
            'SEEKER_NAME',
            'SEEKER_EMAIL',
            'SEEKER_PHONE',
            -- Program flag variables seeded in step 2
            'PROGRAM_REQUIRES_PAYMENT',
            'PROGRAM_INVOLVES_TRAVEL',
            'PROGRAM_HAS_GOODIES',
            'PROGRAM_REQUIRES_APPROVAL',
            'RESERVED_LINK'
        )
        ORDER BY execution_order, 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 % variables to CUSTOM_TYPE_WORKFLOW (ID: %)', v_order - 1, v_workflow_id;
END $$;

COMMIT;
