-- ================================================
-- Migration: Seed missing workflow variables + link to ALL workflows
-- Date: 2026-06-10
-- ================================================

-- ------------------------------------------------
-- PART 1: Seed any missing variables into workflow_variable_master
-- All ON CONFLICT DO NOTHING — safe to re-run.
-- ------------------------------------------------

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_HAS_TERMS', 'Program Has Terms',
    'program_v1', 'should_send_terms', 'shouldSendTerms',
    'boolean', 'INNER', 'pv.id = pr.program_id AND pv.deleted_at IS NULL', 'pv',
    'Whether this program requires the participant to accept T&C. Read from program_v1.should_send_terms.',
    true, true, 54, '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
(
    '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()
),
(
    '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 proxy (on-behalf) registration. Read from program_v1.',
    true, true, 55, '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;

-- ------------------------------------------------
-- PART 2: Link ALL 7 variables to EVERY workflow in workflow_configuration
-- Skips pairs that already exist. Safe to re-run.
-- ------------------------------------------------

DO $$
DECLARE
    v_var     RECORD;
    v_wf      RECORD;
    v_var_id  INT;
    v_next    INT;
    linked    INT := 0;
    skipped   INT := 0;
BEGIN
    FOR v_wf IN
        SELECT id, workflow_key FROM workflow_configuration ORDER BY id
    LOOP
        FOR v_var IN
            SELECT id, variable_key FROM workflow_variable_master
            WHERE variable_key IN (
                'PROGRAM_REQUIRES_PAYMENT',
                'PROGRAM_INVOLVES_TRAVEL',
                'PROGRAM_HAS_GOODIES',
                'PROGRAM_REQUIRES_APPROVAL',
                'PROGRAM_ALLOWS_PROXY_REGISTRATION',
                'PROGRAM_HAS_TERMS',
                'RESERVED_LINK'
            )
            AND is_active = true
            ORDER BY execution_order, variable_key
        LOOP
            IF EXISTS (
                SELECT 1 FROM workflow_configuration_variable
                WHERE workflow_config_id = v_wf.id AND variable_master_id = v_var.id
            ) THEN
                skipped := skipped + 1;
                CONTINUE;
            END IF;

            SELECT COALESCE(MAX(variable_order), 0) + 1 INTO v_next
            FROM workflow_configuration_variable
            WHERE workflow_config_id = v_wf.id;

            INSERT INTO workflow_configuration_variable
                (workflow_config_id, variable_master_id, variable_order, is_required, created_by, updated_by)
            VALUES
                (v_wf.id, v_var.id, v_next, false, -2, -2);

            linked := linked + 1;
            RAISE NOTICE 'Linked % → % (wf_id=%, order=%)',
                v_var.variable_key, v_wf.workflow_key, v_wf.id, v_next;
        END LOOP;
    END LOOP;

    RAISE NOTICE '==============================';
    RAISE NOTICE 'Done. Linked: %, Already existed: %', linked, skipped;
    RAISE NOTICE '==============================';
END $$;

-- ------------------------------------------------
-- Verification: all 7 variables × ALL workflows
-- Every row should show OK when this script is complete.
-- ------------------------------------------------
SELECT
    wc.workflow_key,
    v.variable_key,
    CASE
        WHEN vm.id  IS NULL THEN 'VARIABLE NOT IN MASTER'
        WHEN wcv.id IS NULL THEN 'NOT LINKED'
        ELSE                     'OK'
    END AS status
FROM (VALUES
    ('PROGRAM_REQUIRES_PAYMENT'),
    ('PROGRAM_INVOLVES_TRAVEL'),
    ('PROGRAM_HAS_GOODIES'),
    ('PROGRAM_REQUIRES_APPROVAL'),
    ('PROGRAM_ALLOWS_PROXY_REGISTRATION'),
    ('PROGRAM_HAS_TERMS'),
    ('RESERVED_LINK')
) AS v(variable_key)
CROSS JOIN workflow_configuration wc
LEFT JOIN workflow_variable_master vm
    ON vm.variable_key = v.variable_key
LEFT JOIN workflow_configuration_variable wcv
    ON wcv.workflow_config_id = wc.id AND wcv.variable_master_id = vm.id
ORDER BY v.variable_key, wc.workflow_key;
