-- =====================================================
-- SCRIPT: 16 — Conditional FS_BASICDETAILS questions
--
-- 1. Adds workflow variable master entries for:
--       requiresResidence, allowsProxyRegistration, subProgramType
-- 2. Links new variables to CUSTOM_WORKFLOW
-- 3. Sets master_question.conditional_config for all 12
--    dynamic basicdetails fields used in non-HDB templates
--
-- Idempotent: ON CONFLICT DO NOTHING on inserts;
--             UPDATE conditional_config is safe to re-run.
-- Run after: 2026-05-13-custom-program-type-universal-workflow.sql
-- =====================================================

BEGIN;

-- ================================================
-- 1. workflow_variable_master — new program flags
-- ================================================

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_RESIDENCE',
    'Program Requires Residence',
    'program_v1',
    'requires_residence',
    'requiresResidence',
    'boolean',
    'INNER',
    'pv.id = pr.program_id AND pv.deleted_at IS NULL',
    'pv',
    'Whether this program requires residential stay. Read from program_v1.',
    true, true, 54, 'DEFAULT',
    -2, -2, NOW(), NOW()
),
(
    'PROGRAM_ALLOWS_PROXY_REGISTRATION',
    'Program Allows Proxy Registration',
    'program_v1',
    'allows_proxy_registration',
    'allowsProxyRegistration',
    'boolean',
    'INNER',
    'pv.id = pr.program_id AND pv.deleted_at IS NULL',
    'pv',
    'Whether this program allows RM/proxy registration on behalf of a seeker. Read from program_v1.',
    true, true, 55, 'DEFAULT',
    -2, -2, NOW(), NOW()
),
(
    'PROGRAM_SUB_PROGRAM_TYPE',
    'Program Sub-Program Type',
    'program_v1',
    'sub_program_type',
    'subProgramType',
    'string',
    'INNER',
    'pv.id = pr.program_id AND pv.deleted_at IS NULL',
    'pv',
    'Sub-type key for this program (e.g. PST_HDB, PST_MSD). Read from program_v1.',
    true, true, 56, 'DEFAULT',
    -2, -2, NOW(), NOW()
)
ON CONFLICT (variable_key) DO NOTHING;


-- ================================================
-- 2. Link new variables to CUSTOM_WORKFLOW
-- ================================================

DO $$
DECLARE
    v_workflow_id INT;
    v_var_id      INT;
    v_max_order   INT;
BEGIN
    SELECT id INTO v_workflow_id
    FROM workflow_configuration
    WHERE workflow_key = 'CUSTOM_WORKFLOW'
    LIMIT 1;

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

    SELECT COALESCE(MAX(variable_order), 0) INTO v_max_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 (
            'PROGRAM_REQUIRES_RESIDENCE',
            'PROGRAM_ALLOWS_PROXY_REGISTRATION',
            'PROGRAM_SUB_PROGRAM_TYPE'
        )
        ORDER BY execution_order
    LOOP
        v_max_order := v_max_order + 1;
        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_max_order, false, -2, -2)
        ON CONFLICT DO NOTHING;
    END LOOP;

    RAISE NOTICE 'New workflow variables linked to CUSTOM_WORKFLOW';
END $$;


-- ================================================
-- 3. master_question.conditional_config updates
--
-- Format: [{"metaKey":"<alias>","operator":"<op>","value":[...]}]
--   metaKey  → alias_name from workflow_variable_master
--   operator → "equals" | "in"
--   value    → array of accepted values
-- ================================================

-- roommate — show only when program requires residential stay
UPDATE master_question
SET conditional_config = '[{"metaKey":"requiresResidence","operator":"equals","value":[true]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'roommate'
  AND deleted_at IS NULL;

-- proFormaZip — show only when program requires payment
UPDATE master_question
SET conditional_config = '[{"metaKey":"requiresPayment","operator":"equals","value":[true]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'proFormaZip'
  AND deleted_at IS NULL;

-- proFormaBillingAddress — show only when program requires payment
UPDATE master_question
SET conditional_config = '[{"metaKey":"requiresPayment","operator":"equals","value":[true]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'proFormaBillingAddress'
  AND deleted_at IS NULL;

-- pro_forma_invoice_name — show only when program requires payment
UPDATE master_question
SET conditional_config = '[{"metaKey":"requiresPayment","operator":"equals","value":[true]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'pro_forma_invoice_name'
  AND deleted_at IS NULL;

-- proFormaIsGstRegistered — show only when program requires payment
UPDATE master_question
SET conditional_config = '[{"metaKey":"requiresPayment","operator":"equals","value":[true]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'proFormaIsGstRegistered'
  AND deleted_at IS NULL;

-- proFormaGstNumber — show only when program requires payment
UPDATE master_question
SET conditional_config = '[{"metaKey":"requiresPayment","operator":"equals","value":[true]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'proFormaGstNumber'
  AND deleted_at IS NULL;

-- registrationForWhom — show only when program allows proxy registration
UPDATE master_question
SET conditional_config = '[{"metaKey":"allowsProxyRegistration","operator":"equals","value":[true]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'registrationForWhom'
  AND deleted_at IS NULL;

-- preference — show only for HDB / MSD sub-program types
UPDATE master_question
SET conditional_config = '[{"metaKey":"subProgramType","operator":"in","value":["PST_MSD","PST_HDB"]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'preference'
  AND deleted_at IS NULL;

-- QK_HDB_ASSOCIATION_SINCE — show only for HDB / MSD sub-program types
UPDATE master_question
SET conditional_config = '[{"metaKey":"subProgramType","operator":"in","value":["PST_MSD","PST_HDB"]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'QK_HDB_ASSOCIATION_SINCE'
  AND deleted_at IS NULL;

-- no_of_hdbs — show only for HDB / MSD sub-program types
UPDATE master_question
SET conditional_config = '[{"metaKey":"subProgramType","operator":"in","value":["PST_MSD","PST_HDB"]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'no_of_hdbs'
  AND deleted_at IS NULL;

-- QK_LAST_HDB — show only for HDB / MSD sub-program types
UPDATE master_question
SET conditional_config = '[{"metaKey":"subProgramType","operator":"in","value":["PST_MSD","PST_HDB"]}]'::jsonb,
    updated_at = NOW()
WHERE binding_key = 'QK_LAST_HDB'
  AND deleted_at IS NULL;



-- ================================================
-- Verify
-- ================================================

SELECT
    binding_key,
    conditional_config
FROM master_question
WHERE binding_key IN (
    'roommate',
    'proFormaZip',
    'proFormaBillingAddress',
    'pro_forma_invoice_name',
    'proFormaIsGstRegistered',
    'proFormaGstNumber',
    'registrationForWhom',
    'preference',
    'QK_HDB_ASSOCIATION_SINCE',
    'no_of_hdbs',
    'QK_LAST_HDB',
    'QK_HDB_MSD_ASPECT_LOOK_FORWARD',
    'terms'
)
  AND deleted_at IS NULL
ORDER BY binding_key;

COMMIT;
