-- ================================================
-- UPDATE: Register LOGO_URL and BANNER_IMAGE_URL in workflow_variable_master
-- and link them to WF_TAT_TA26_V6
-- Source: program_v1.logo_url / program_v1.banner_image_url (program-level fields)
-- ================================================

-- Step 1: Insert into workflow_variable_master (idempotent)
INSERT INTO workflow_variable_master
  (variable_key, variable_name, source_table, source_column, alias_name, data_type,
   join_type, join_condition, table_alias, execution_order, description, is_active)
VALUES
  ('LOGO_URL', 'Logo URL', 'program_v1', 'logo_url', 'logoUrl', 'string',
   'LEFT', 'p.id = pr.program_id AND p.deleted_at IS NULL', 'p', 80,
   'Logo image URL of the program', true),
  ('BANNER_IMAGE_URL', 'Banner Image URL', 'program_v1', 'banner_image_url', 'bannerImageUrl', 'string',
   'LEFT', 'p.id = pr.program_id AND p.deleted_at IS NULL', 'p', 80,
   'Banner image URL of the program', true)
ON CONFLICT (variable_key) DO NOTHING;

-- Step 2: Link to WF_TAT_TA26_V6
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 ('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 WF_TAT_TA26_V6 (ID: %)', v_workflow_id;
END $$;
