-- =====================================================
-- SCRIPT: Pre-template setup (run before 04_*_template scripts)
-- Purpose:
--   1) Add conditional_config columns (if missing)
--   2) Add registrationForWhom in master_question (if missing)
-- =====================================================

BEGIN;

DO $$
DECLARE
    v_basic_details_section_id BIGINT;
    v_registration_for_whom_id BIGINT;
    v_next_question_code TEXT;
BEGIN
    -- Add conditional_config column to master_question
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'master_question'
          AND column_name = 'conditional_config'
    ) THEN
        ALTER TABLE master_question ADD COLUMN conditional_config JSONB;
    END IF;

    -- Add conditional_config column to template_question
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'template_question'
          AND column_name = 'conditional_config'
    ) THEN
        ALTER TABLE template_question ADD COLUMN conditional_config JSONB;
    END IF;

    -- Add registrationForWhom only once
    SELECT id INTO v_registration_for_whom_id
    FROM master_question
    WHERE binding_key = 'registrationForWhom'
      AND deleted_at IS NULL
    LIMIT 1;

    IF v_registration_for_whom_id IS NULL THEN
        SELECT id INTO v_basic_details_section_id
        FROM master_form_section
        WHERE section_key = 'FS_BASICDETAILS'
          AND deleted_at IS NULL
        LIMIT 1;

        IF v_basic_details_section_id IS NULL THEN
            RAISE EXCEPTION 'FS_BASICDETAILS not found in master_form_section';
        END IF;

        SELECT 'Q_' || LPAD((COALESCE(MAX(SUBSTRING(question_code FROM 3)::INTEGER), 0) + 1)::TEXT, 10, '0')
        INTO v_next_question_code
        FROM master_question
        WHERE question_code ~ '^Q_[0-9]+$';

        INSERT INTO master_question (
            question_code,
            binding_key,
            master_form_section_id,
            question_text,
            question_type,
            answer_type,
            answer_location,
            option_config,
            config,
            conditional_config,
            is_active,
            created_at,
            updated_at,
            created_by,
            updated_by
        )
        VALUES (
            v_next_question_code,
            'registrationForWhom',
            v_basic_details_section_id,
            'Who are you registering for?',
            'radio',
            'string',
            'hdb_program_registration.registration_mode',
            '[
                {"name":"Register for Myself","value":"self","type":"string","order":0},
                {"name":"Register for Other","value":"other","type":"string","order":1}
            ]'::jsonb,
            '{"isRequired": true}'::jsonb,
            '[{"metaKey":"allowsProxyRegistration","operator":"equals","value":[true]}]'::jsonb,
            TRUE,
            NOW(),
            NOW(),
            -2,
            -2
        );
    END IF;
END $$;

-- Add conditional_config column to hdb_question
ALTER TABLE hdb_question
ADD COLUMN IF NOT EXISTS conditional_config JSONB NULL;

COMMENT ON COLUMN hdb_question.conditional_config IS 'Program/field based conditional configuration. Reserved for future business logic usage.';

-- Add conditional_config column to master_form_section (for section-level conditional rendering)
ALTER TABLE master_form_section
ADD COLUMN IF NOT EXISTS conditional_config JSONB NULL;

-- Add has_goodies column to program_v1 (program table)
ALTER TABLE program_v1
ADD COLUMN IF NOT EXISTS has_goodies BOOLEAN NOT NULL DEFAULT FALSE;

-- Add has_goodies column to program_type_v1
ALTER TABLE program_type_v1
ADD COLUMN IF NOT EXISTS has_goodies BOOLEAN NOT NULL DEFAULT FALSE;

-- Set conditional_config on master sections based on program fields
UPDATE master_form_section
SET conditional_config = '[{"metaKey":"requiresPayment","operator":"equals","value":[true]}]'::jsonb
WHERE section_key = 'FS_PAYMENTINVOICE'
  AND deleted_at IS NULL;

UPDATE master_form_section
SET conditional_config = '[{"metaKey":"involvesTravel","operator":"equals","value":[true]}]'::jsonb
WHERE section_key = 'FS_TRAVELPLAN'
  AND deleted_at IS NULL;

UPDATE master_form_section
SET conditional_config = '[{"metaKey":"hasGoodies","operator":"equals","value":[true]}]'::jsonb
WHERE section_key = 'FS_GOODIES'
  AND deleted_at IS NULL;

COMMIT;

BEGIN;

  INSERT INTO public.master_question (
    question_code,
    binding_key,
    master_form_section_id,
    question_text,
    question_type,
    answer_type,
    answer_location,
    option_config,
    config,
    is_active,
    created_at,
    updated_at,
    deleted_at,
    created_by,
    updated_by
  )
  SELECT
    q.question_code || 'DEP_RETURN',
    'travelPlanDependReturn',
    q.master_form_section_id,
    q.question_text,
    q.question_type,
    q.answer_type,
    q.answer_location,
    q.option_config,
    jsonb_set(
      COALESCE(q.config, '{}'::jsonb),
      '{dependsOn}',
      COALESCE(q.config->'dependsOn', '[]'::jsonb) ||
      jsonb_build_array(
        jsonb_build_object(
          'value', jsonb_build_array('I want to update now'),
          'questionBindingKey', 'travelDetails',
          'questionId', td.id
        )
      ),
      true
    ),
    q.is_active,
    NOW(),
    NOW(),
    NULL,
    q.created_by,
    q.updated_by
  FROM public.master_question q
  JOIN public.master_question td
    ON td.binding_key = 'travelDetails'
    AND td.deleted_at IS NULL
  WHERE q.binding_key = 'travelPlanReturn'
    AND q.deleted_at IS NULL
    AND NOT EXISTS (
      SELECT 1 FROM public.master_question mq
      WHERE mq.question_code = q.question_code || 'DEP_RETURN'
        AND mq.deleted_at IS NULL
    );




  INSERT INTO public.master_question (
    question_code,
    binding_key,
    master_form_section_id,
    question_text,
    question_type,
    answer_type,
    answer_location,
    option_config,
    config,
    is_active,
    created_at,
    updated_at,
    deleted_at,
    created_by,
    updated_by
  )
  SELECT
    q.question_code || 'DEP_ONWARD',
    'travelPlanDependOnward',
    q.master_form_section_id,
    q.question_text,
    q.question_type,
    q.answer_type,
    q.answer_location,
    q.option_config,
    jsonb_set(
      COALESCE(q.config, '{}'::jsonb),
      '{dependsOn}',
      COALESCE(q.config->'dependsOn', '[]'::jsonb) ||
      jsonb_build_array(
        jsonb_build_object(
          'value', jsonb_build_array('I want to update now'),
          'questionBindingKey', 'travelDetails',
          'questionId', td.id
        )
      ),
      true
    ),
    q.is_active,
    NOW(),
    NOW(),
    NULL,
    q.created_by,
    q.updated_by
  FROM public.master_question q
  JOIN public.master_question td
    ON td.binding_key = 'travelDetails'
    AND td.deleted_at IS NULL
  WHERE q.binding_key = 'travelPlanOnward'
    AND q.deleted_at IS NULL
    AND NOT EXISTS (
      SELECT 1 FROM public.master_question mq
      WHERE mq.question_code = q.question_code || 'DEP_ONWARD'
        AND mq.deleted_at IS NULL
    );


DO $$
DECLARE
  dep_onward_id bigint;
  q RECORD;
BEGIN
  -- Get the questionId for travelPlanDependOnward
  SELECT id INTO dep_onward_id FROM master_question WHERE binding_key = 'travelPlanDependOnward' AND deleted_at IS NULL LIMIT 1;

  FOR q IN
    SELECT id, config
    FROM master_question
    WHERE binding_key IN (
      'comingFrom',
      'uploadOnwardJourneyTicket',
      'onwardTerminal',
      'airlineNameOnward',
      'flightNumberOnward',
      'arrivalDateTime'
    )
    AND deleted_at IS NULL
  LOOP
    UPDATE master_question
    SET config = jsonb_set(
      COALESCE(config, '{}'::jsonb),
      '{dependsOn}',
      (
        SELECT
          COALESCE(config->'dependsOn', '[]'::jsonb) ||
          jsonb_build_array(
            jsonb_build_object(
              'value', jsonb_build_array('Flight'),
              'questionId', dep_onward_id,
              'questionBindingKey', 'travelPlanDependOnward'
            )
          )
        FROM master_question WHERE id = q.id
      ),
      true
    )
    WHERE id = q.id;
  END LOOP;
END $$;

DO $$
DECLARE
  dep_return_id bigint;
  q RECORD;
BEGIN
  -- Get the questionId for travelPlanDependReturn
  SELECT id INTO dep_return_id FROM master_question WHERE binding_key = 'travelPlanDependReturn' AND deleted_at IS NULL LIMIT 1;

  FOR q IN
    SELECT id, config
    FROM master_question
    WHERE binding_key IN (
      'uploadReturnJourneyTicket', 'returnTerminal', 'airlineNameReturn', 'flightNumberReturn', 'goingTo', 'departureDateTime'

    )
    AND deleted_at IS NULL
  LOOP
    UPDATE master_question
    SET config = jsonb_set(
      COALESCE(config, '{}'::jsonb),
      '{dependsOn}',
      (
        SELECT
          COALESCE(config->'dependsOn', '[]'::jsonb) ||
          jsonb_build_array(
            jsonb_build_object(
              'value', jsonb_build_array('Flight'),
              'questionId', dep_return_id,
              'questionBindingKey', 'travelPlanDependReturn'
            )
          )
        FROM master_question WHERE id = q.id
      ),
      true
    )
    WHERE id = q.id;
  END LOOP;
END $$;

COMMIT;

BEGIN;

DO $$
DECLARE
  reg_for_whom_id BIGINT;
BEGIN
  SELECT id INTO reg_for_whom_id
  FROM master_question
  WHERE binding_key = 'registrationForWhom'
    AND deleted_at IS NULL
  LIMIT 1;

  IF reg_for_whom_id IS NULL THEN
    RAISE EXCEPTION 'registrationForWhom question not found in master_question';
  END IF;

  -- Add dependsOn registrationForWhom (both values) to config of FS_BASICDETAILS questions
  UPDATE master_question
  SET config = jsonb_set(
    COALESCE(config, '{}'::jsonb),
    '{dependsOn}',
    COALESCE(config->'dependsOn', '[]'::jsonb) ||
    jsonb_build_array(
      jsonb_build_object(
        'value', jsonb_build_array('self', 'other'),
        'questionBindingKey', 'registrationForWhom',
        'questionId', reg_for_whom_id
      )
    ),
    true
  )
  WHERE binding_key = ANY(ARRAY[
      'notes',
      'preference',
      'countryName',
      'profileUrl',
      'name',
      'QK_HDB_ASSOCIATION_SINCE',
      'rmContact',
      'gender',
      'proFormaBillingAddress',
      'roommate',
      'pro_forma_invoice_name',
      'dob',
      'email',
      'mobileNumber',
      'proFormaIsGstRegistered',
      'city',
      'no_of_hdbs'
    ])
    AND deleted_at IS NULL;
END $$;

COMMIT;
