-- https://docs.google.com/document/d/1ZMMuWcPT_w1nxxOYXpqBAEoiTAH3ZWbN6MUKwlsD7ng/edit?tab=t.0
--
--2025-12-02
-- Migration: Add total_bed_count column to program_v1
ALTER TABLE program_v1 ADD COLUMN total_bed_count INT DEFAULT 0;

update program_v1
set total_bed_count = 194
where program_type_id = (select id from program_type_v1 where key = 'PT_HDBMSD');

--2025-11-27
-- Create a new template for HOLD

--2025-11-26

SELECT h.id,
       h.registration_id,
       h.approval_date,
       h.updated_at,
       s.status AS swap_status
FROM public.hdb_registration_approval h
INNER JOIN public.hdb_program_registration_swap s
    ON s.program_registration_id = h.registration_id
WHERE h.approval_status = 'approved'
  AND s.status = 'accepted';


UPDATE public.hdb_registration_approval h
SET approval_date = h.updated_at
FROM public.hdb_program_registration_swap s
WHERE s.program_registration_id = h.registration_id
  AND s.status = 'accepted'
  AND h.approval_status = 'approved';

--2025-11-25
CREATE TYPE public.room_category_enum AS ENUM (
    'general',
    'physically_challenged',
    'senior_citizens',
    'children'
);

ALTER TABLE program_room_inventory_map
ADD COLUMN IF NOT EXISTS room_category room_category_enum DEFAULT 'general'::room_category_enum NOT NULL;

--2025-11-24
CREATE TABLE seeker_program_experience (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    registration_id INTEGER REFERENCES hdb_program_registration(id) ON DELETE SET NULL,
    record_type VARCHAR(100) NOT NULL,
    message TEXT,
    media_url TEXT,
    is_viewed BOOLEAN DEFAULT FALSE,
    is_approved BOOLEAN DEFAULT FALSE,
    is_private BOOLEAN DEFAULT FALSE,
    deleted_reason TEXT,
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    deleted_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

ALTER TABLE program_type_v1
ADD COLUMN seeker_can_share_experience BOOLEAN DEFAULT FALSE;

ALTER TABLE program_v1
ADD COLUMN seeker_can_share_experience BOOLEAN DEFAULT FALSE;

select id, name,seeker_can_share_experience from program_type_v1 where key = 'PT_HDBMSD';

update program_type_v1
set seeker_can_share_experience = TRUE
where key = 'PT_HDBMSD';

select id, name,seeker_can_share_experience from program_type_v1 where key = 'PT_HDBMSD';

select id, name, seeker_can_share_experience from program_v1 where program_type_id IN (
    SELECT id FROM program_type_v1 WHERE seeker_can_share_experience = TRUE
);
UPDATE program_v1 
SET seeker_can_share_experience = TRUE
WHERE program_type_id IN (
    SELECT id FROM program_type_v1 WHERE seeker_can_share_experience = TRUE
);

select id, name, seeker_can_share_experience from program_v1 where program_type_id IN (
    SELECT id FROM program_type_v1 WHERE seeker_can_share_experience = TRUE
);



--2025-11-06
select * from hdb_question where created_by = -2 and form_section_id = 3 and binding_key = 'travelInfoType';

UPDATE public.hdb_question q
SET config = jsonb_set(
        q.config,
        '{dependsOn}',
        jsonb_build_array(
            jsonb_build_object(
                'value', 'yes',
                'questionId',
                    (SELECT id
                     FROM public.hdb_question
                     WHERE binding_key = 'internationalId'
                       AND created_by = -2
                     LIMIT 1),
                'questionBindingKey', 'internationalId'
            ),
            jsonb_build_object(
                'value', 'no',
                'questionId',
                    (SELECT id
                     FROM public.hdb_question
                     WHERE binding_key = 'internationalId'
                       AND created_by = -2
                     LIMIT 1),
                'questionBindingKey', 'internationalId'
            )
        )
    ),
    updated_at = NOW()
WHERE q.id = (
    SELECT id
    FROM public.hdb_question
    WHERE binding_key = 'travelInfoType'
      AND created_by = -2
    LIMIT 1
);

WITH intl AS (
    SELECT id
    FROM public.hdb_question
    WHERE binding_key = 'internationalId'
      AND created_by = -2
    LIMIT 1
),
target AS (
    SELECT id
    FROM public.hdb_question
    WHERE binding_key = 'travelInfoType'
      AND created_by = -2
    LIMIT 1
)
UPDATE public.hdb_question q
SET 
    config = jsonb_set(
        q.config,
        '{prefill}',
        jsonb_build_object(
            'prefillIf',
                jsonb_build_array(
                    jsonb_build_object(
                        'value', 'yes',
                        'operator', 'equals',
                        'questionId', (SELECT id FROM intl),
                        'questionBindingKey', 'internationalId'
                    )
                ),
            'prefillType', 'static',
            'prefillValue', 'Passport'
        )::jsonb
    ),
    updated_at = NOW()
FROM target
WHERE q.id = target.id;


select * from hdb_question where created_by = -2 and form_section_id = 3 and binding_key = 'pictureUrl';


UPDATE public.hdb_question q
SET config = jsonb_set(
        q.config,
        '{dependsOn}',
        jsonb_build_array(
            jsonb_build_object(
                'value', 'no',
                'questionId',
                    (SELECT id
                     FROM public.hdb_question
                     WHERE binding_key = 'internationalId'
                       AND created_by = -2
                     LIMIT 1),
                'questionBindingKey', 'internationalId'
            )
        )
    ),
    updated_at = NOW()
WHERE q.id = (
    SELECT id
    FROM public.hdb_question
    WHERE binding_key = 'pictureUrl'
      AND created_by = -2
    LIMIT 1
);


select * from hdb_question where created_by = -2 and form_section_id = 3 and binding_key = 'idBack';


UPDATE public.hdb_question q
SET config = jsonb_set(
        q.config,
        '{dependsOn}',
        jsonb_build_array(
            jsonb_build_object(
                'value', 'no',
                'questionId',
                    (SELECT id
                     FROM public.hdb_question
                     WHERE binding_key = 'internationalId'
                       AND created_by = -2
                     LIMIT 1),
                'questionBindingKey', 'internationalId'
            )
        )
    ),
    updated_at = NOW()
WHERE q.id = (
    SELECT id
    FROM public.hdb_question
    WHERE binding_key = 'idBack'
      AND created_by = -2
    LIMIT 1
);

UPDATE hdb_program_question
SET display_order = CASE question_id
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoType') THEN 4
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'pictureUrl') THEN 5
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'idBack') THEN 6
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoNumber') THEN 3
	WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'internationalId') THEN 1
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'passport') THEN 2
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'visa') THEN 3
    ELSE display_order
END
WHERE form_section_id = (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2)
  AND question_id IN (
      SELECT id FROM public.hdb_question
      WHERE binding_key IN (
          'pictureUrl','idBack','travelInfoType','travelInfoNumber','internationalId','passport','visa')
  );

select * from lookup_data where lookup_category = 'ID_TYPE' and lookup_key = 'Pan card';

update lookup_data
set lookup_status = 'inactive'
where lookup_category = 'ID_TYPE' and lookup_key = 'Pan card';

select * from hdb_question where created_by = -2 and form_section_id = 3 and binding_key = 'internationalId';

UPDATE hdb_question
SET config = config || '{"isRequired": false}'::jsonb
WHERE created_by = -2
  AND form_section_id = 3
  AND binding_key = 'internationalId';
  
--2025-10-31
INSERT INTO user_role (id, name, role_key, priority, is_system_role)
VALUES (9, 'rm_support', 'ROLE_RM_SUPPORT', 5, true)

UPDATE user_role
SET priority = priority+1
WHERE role_key IN ('ROLE_OPERATIONAL_MANAGER','ROLE_RELATIONAL_MANAGER','ROLE_VIEWER')

--2025-10-30
-- Add swap_requirement column to hdb_program_registration_swap table
ALTER TABLE hdb_program_registration_swap
ADD COLUMN swap_requirement VARCHAR(50) DEFAULT 'SWAP_REQUEST';

-- Optionally, update existing rows to set the default value explicitly
UPDATE hdb_program_registration_swap
SET swap_requirement = 'SWAP_REQUEST'
WHERE swap_requirement IS NULL;

-- 2025-10-29
ALTER TYPE address_type_enum ADD VALUE 'venue_address';
CREATE TYPE room_status_enum AS ENUM (
        'AVAILABLE',
        'PARTIALLY_ALLOTTED', 
        'ALLOTTED',
        'RESERVED'
);
ALTER TABLE program_room_inventory_map
ALTER COLUMN room_status SET DEFAULT 'AVAILABLE'::room_status_enum;
-- 2025-10-28

INSERT INTO public.feature_flag 
(feature_key, label, description, flag, status, created_at, updated_at, created_by, updated_by)
VALUES
  (
    'sendPaymentRemindereToRM',
    'Send Payment Reminders to RM',
    'Toggle Payment Reminders feature',
    FALSE,
    'active',
    NOW(),
    NOW(),
    -2,
    -2
  )
ON CONFLICT (feature_key) DO NOTHING;

--2025-10-23
/***
need to add #scheduler config in env
*/
-- Migration script for scheduler_logs table
-- This table tracks all scheduler job executions with their status and details

-- Create scheduler_logs table
CREATE TABLE IF NOT EXISTS portal_scheduler_logs (
    id BIGSERIAL PRIMARY KEY,
    job_name VARCHAR(100) NOT NULL,
    job_type VARCHAR(100) NOT NULL,
    request_payload JSONB,
    response_data JSONB,
    execution_status VARCHAR(50) NOT NULL DEFAULT 'pending',
    attempt_number INTEGER NOT NULL DEFAULT 1,
    total_attempts INTEGER NOT NULL DEFAULT 1,
    error_message TEXT,
    error_stack TEXT,
    execution_time_ms INTEGER,
    program_id BIGINT,
    registration_id BIGINT,
    triggered_by_user_id BIGINT NOT NULL,
    triggered_type VARCHAR(50) NOT NULL DEFAULT 'scheduled',
    completed_at TIMESTAMP WITH TIME ZONE,
    failed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
    
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_job_name ON portal_scheduler_logs(job_name);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_job_type ON portal_scheduler_logs(job_type);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_execution_status ON portal_scheduler_logs(execution_status);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_triggered_type ON portal_scheduler_logs(triggered_type);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_program_id ON portal_scheduler_logs(program_id);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_registration_id ON portal_scheduler_logs(registration_id);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_triggered_by_user_id ON portal_scheduler_logs(triggered_by_user_id);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_created_at ON portal_scheduler_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_completed_at ON portal_scheduler_logs(completed_at DESC);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_failed_at ON portal_scheduler_logs(failed_at DESC);

-- Composite indexes for common queries
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_job_status ON portal_scheduler_logs(job_type, execution_status);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_date_status ON portal_scheduler_logs(created_at DESC, execution_status);
CREATE INDEX IF NOT EXISTS idx_scheduler_logs_program_status ON portal_scheduler_logs(program_id, execution_status) WHERE program_id IS NOT NULL;

-- 2025-10-22
-- 1. Add signed URL columns to hdb_program_registration table
ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS user_profile_signed_url TEXT,
ADD COLUMN IF NOT EXISTS user_video_signed_url TEXT;

COMMENT ON COLUMN hdb_program_registration.user_profile_signed_url IS 'Signed S3 URL for user profile picture';
COMMENT ON COLUMN hdb_program_registration.user_video_signed_url IS 'Signed S3 URL for user video';

-- 2. Add signed URL columns to hdb_registration_travel_info table
ALTER TABLE hdb_registration_travel_info 
ADD COLUMN IF NOT EXISTS id_picture_signed_url TEXT,
ADD COLUMN IF NOT EXISTS id_back_picture_signed_url TEXT,
ADD COLUMN IF NOT EXISTS passport_copy_picture_signed_url TEXT,
ADD COLUMN IF NOT EXISTS visa_copy_picture_signed_url TEXT;

COMMENT ON COLUMN hdb_registration_travel_info.id_picture_signed_url IS 'Signed S3 URL for ID front picture';
COMMENT ON COLUMN hdb_registration_travel_info.id_back_picture_signed_url IS 'Signed S3 URL for ID back picture';
COMMENT ON COLUMN hdb_registration_travel_info.passport_copy_picture_signed_url IS 'Signed S3 URL for passport copy';
COMMENT ON COLUMN hdb_registration_travel_info.visa_copy_picture_signed_url IS 'Signed S3 URL for visa copy';

-- 3. Add journey ticket URL columns and their signed URL columns to hdb_registration_travel_plan table
ALTER TABLE hdb_registration_travel_plan 
ADD COLUMN IF NOT EXISTS onward_journey_ticket_url TEXT,
ADD COLUMN IF NOT EXISTS onward_journey_ticket_signed_url TEXT,
ADD COLUMN IF NOT EXISTS return_journey_ticket_url TEXT,
ADD COLUMN IF NOT EXISTS return_journey_ticket_signed_url TEXT;

COMMENT ON COLUMN hdb_registration_travel_plan.onward_journey_ticket_url IS 'Plain S3 URL for onward journey ticket';
COMMENT ON COLUMN hdb_registration_travel_plan.onward_journey_ticket_signed_url IS 'Signed S3 URL for onward journey ticket';
COMMENT ON COLUMN hdb_registration_travel_plan.return_journey_ticket_url IS 'Plain S3 URL for return journey ticket';
COMMENT ON COLUMN hdb_registration_travel_plan.return_journey_ticket_signed_url IS 'Signed S3 URL for return journey ticket';

-- 4. Update hdb_question table to add answer_location for ticket upload questions
UPDATE hdb_question 
SET answer_location = 'hdb_registration_travel_plan.onward_journey_ticket_url'
WHERE binding_key = 'uploadOnwardJourneyTicket';

UPDATE hdb_question 
SET answer_location = 'hdb_registration_travel_plan.return_journey_ticket_url'
WHERE binding_key = 'uploadReturnJourneyTicket';

--2025-10-17
select * from lookup_data where lookup_category = 'SEEKER_PROGRAM_EXPERIENCE' order by lookup_order;

WITH count_cte AS (
    SELECT COUNT(*) AS total_count
    FROM lookup_data
    WHERE lookup_category = 'SEEKER_PROGRAM_EXPERIENCE'
),
ordered AS (
    SELECT 
        id,
        lookup_key,
        ROW_NUMBER() OVER (
            ORDER BY 
                lookup_key
        ) AS new_order
    FROM lookup_data
    WHERE lookup_category = 'SEEKER_PROGRAM_EXPERIENCE'
)
UPDATE lookup_data ld
SET lookup_order = ordered.new_order
FROM ordered
WHERE ld.id = ordered.id;

--2025-01-15
-- Make ID number optional in travel info section
UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{isRequired}',
    'false'::jsonb,
    true
)
WHERE binding_key = 'travelInfoNumber' AND created_by = -2;

-- Reorder goodies section: T-shirt, Jacket, Flask, Ratria Pillars, Notebook
UPDATE hdb_program_question
SET display_order = CASE question_id
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtApplicable') THEN 1
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtSize') THEN 2
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketApplicable') THEN 3
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketSize') THEN 4
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesRatriaPillarLeonia') THEN 5
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarLocation') THEN 6
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarOtherLocation') THEN 7
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesFlask') THEN 8
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesNotebook') THEN 9
  ELSE display_order
END
WHERE question_id IN (
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtApplicable'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtSize'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketApplicable'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketSize'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesFlask'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesRatriaPillarLeonia'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarLocation'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarOtherLocation'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesNotebook')
);

-- Verify the changes
SELECT 
    binding_key,
    config->'isRequired' as is_required
FROM hdb_question 
WHERE binding_key = 'travelInfoNumber' AND created_by = -2;

SELECT 
    q.binding_key,
    pq.display_order
FROM hdb_program_question pq
JOIN hdb_question q ON pq.question_id = q.id
WHERE q.binding_key IN (
    'goodiesTshirtApplicable', 'goodiesTshirtSize', 
    'goodiesJacketApplicable', 'goodiesJacketSize',
    'goodiesFlask', 'goodiesRatriaPillarLeonia',
    'ratriaPillarLocation', 'ratriaPillarOtherLocation',
    'goodiesNotebook'
)
AND q.created_by = -2
ORDER BY pq.display_order;

select * from hdb_question where created_by = -2 and form_section_id in (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	) and binding_key = 'travelInfoNumber';


delete from hdb_program_question
where question_id in (
	select id from hdb_question 
	where binding_key = 'travelInfoNumber' 
	and form_section_id in (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
	and created_by = -2
);

-- 2025-10-15
-- Insert alphanumeric pair codes
INSERT INTO public.lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES
('PAIR_CODE', 'Pair Code', 'D', '2', 'active', 1),
('PAIR_CODE', 'Pair Code', 'T', '3', 'active', 2),
('PAIR_CODE', 'Pair Code', 'Q', '4', 'active', 3),
('PAIR_CODE', 'Pair Code', 'P', '5', 'active', 4);

-- 2025-10-14
-- 1.Venue table creation
CREATE TABLE venue (
    id BIGSERIAL PRIMARY KEY,
    label VARCHAR(255) NOT NULL,
    description TEXT,
    address_id BIGINT NOT NULL,
    type VARCHAR(50),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT,
    updated_by BIGINT,

    CONSTRAINT fk_venue_address
        FOREIGN KEY (address_id)
        REFERENCES hdb_address(address_id)
);

CREATE INDEX idx_venue_address ON venue(address_id);
CREATE INDEX idx_venue_deleted ON venue(deleted_at);

--- 2.Create block table

CREATE TABLE IF NOT EXISTS block (
  id BIGSERIAL PRIMARY KEY,
  label VARCHAR(255) NOT NULL,
  description TEXT,
  venue_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP,
  created_by BIGINT NOT NULL,
  updated_by BIGINT,
  CONSTRAINT fk_block_venue FOREIGN KEY (venue_id) REFERENCES venue(id)
);

CREATE INDEX idx_block_venue ON block(venue_id);
CREATE INDEX idx_block_deleted ON block(deleted_at);



--- 3.Table to store floor-level details within each block
CREATE TABLE IF NOT EXISTS floor (
    id BIGSERIAL PRIMARY KEY,
    label VARCHAR(255) NOT NULL,
    description TEXT,
    block_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_floor_block FOREIGN KEY (block_id) REFERENCES block(id)
);

CREATE INDEX idx_floor_block ON floor(block_id);
CREATE INDEX idx_floor_deleted ON floor(deleted_at);


--- 4.Table to store room details within each floor
CREATE TABLE IF NOT EXISTS room (
    id BIGSERIAL PRIMARY KEY,
    label VARCHAR(255) NOT NULL,
    description TEXT,
    floor_id BIGINT NOT NULL,
    room_number VARCHAR(50) NOT NULL,
    occupancy INTEGER NOT NULL,
    room_status VARCHAR(50) NOT NULL,
    room_type VARCHAR(50) NOT NULL,
    bed_type VARCHAR(50),
    remarks TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_room_floor FOREIGN KEY (floor_id) REFERENCES floor(id)
);

CREATE INDEX idx_room_floor ON room(floor_id);
CREATE INDEX idx_room_status ON room(room_status);
CREATE INDEX idx_room_type ON room(room_type);
CREATE INDEX idx_room_deleted ON room(deleted_at);


--- 5.Table to map program inventory to rooms
CREATE TABLE IF NOT EXISTS program_room_inventory_map (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL,
    program_id BIGINT NOT NULL,
    sub_program_id BIGINT,
    remaining_occupancy INTEGER NOT NULL,
    is_reserved BOOLEAN NOT NULL,
    reserved_for VARCHAR(255),
    room_status VARCHAR(50) NOT NULL,
    occupied_starts_at TIMESTAMP,
    occupied_ends_at TIMESTAMP,
    remarks TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_prim_room FOREIGN KEY (room_id) REFERENCES room(id),
    CONSTRAINT fk_prim_program FOREIGN KEY (program_id) REFERENCES program_v1(id),
    CONSTRAINT fk_prim_sub_program FOREIGN KEY (sub_program_id) REFERENCES program_v1(id)
);

CREATE INDEX idx_prim_room ON program_room_inventory_map(room_id);
CREATE INDEX idx_prim_program ON program_room_inventory_map(program_id);
CREATE INDEX idx_prim_subprogram ON program_room_inventory_map(sub_program_id);
CREATE INDEX idx_prim_status ON program_room_inventory_map(room_status);
CREATE INDEX idx_prim_deleted ON program_room_inventory_map(deleted_at);

--- 6.Table to store allocation of rooms to program registrations
CREATE TABLE IF NOT EXISTS room_allocation (
    id BIGSERIAL PRIMARY KEY,
    program_room_inventory_map_id BIGINT NOT NULL,
    registration_id BIGINT NOT NULL,
    remarks TEXT,
    bed_position INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_ra_prim FOREIGN KEY (program_room_inventory_map_id) REFERENCES program_room_inventory_map(id),
    CONSTRAINT fk_ra_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id)
);

CREATE INDEX idx_ra_inventory ON room_allocation(program_room_inventory_map_id);
CREATE INDEX idx_ra_registration ON room_allocation(registration_id);
CREATE INDEX idx_ra_deleted ON room_allocation(deleted_at);

--- 7.Table to track history of room allocations
CREATE TABLE IF NOT EXISTS room_allocation_history (
    id BIGSERIAL PRIMARY KEY,
    room_allocation_id BIGINT NOT NULL,
    program_room_inventory_map_id BIGINT NOT NULL,
    registration_id BIGINT NOT NULL,
    remarks TEXT,
    bed_position INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_rah_allocation FOREIGN KEY (room_allocation_id) REFERENCES room_allocation(id),
    CONSTRAINT fk_rah_inventory FOREIGN KEY (program_room_inventory_map_id) REFERENCES program_room_inventory_map(id),
    CONSTRAINT fk_rah_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id)
);

CREATE INDEX idx_rah_allocation ON room_allocation_history(room_allocation_id);
CREATE INDEX idx_rah_inventory ON room_allocation_history(room_inventory_id);
CREATE INDEX idx_rah_registration ON room_allocation_history(program_registration_id);
CREATE INDEX idx_rah_created ON room_allocation_history(created_at);


--- 8.Table to store registration pairs for programs and sub-programs
CREATE TABLE IF NOT EXISTS registration_pair (
    id BIGSERIAL PRIMARY KEY,
    pair_code VARCHAR(10) NOT NULL,
    seq_number BIGINT NOT NULL,
    program_id BIGINT NOT NULL,
    sub_program_id BIGINT,
    remarks TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_rp_program FOREIGN KEY (program_id) REFERENCES program_v1(id),
    CONSTRAINT fk_rp_subprogram FOREIGN KEY (sub_program_id) REFERENCES program_v1(id)
);

CREATE INDEX idx_rp_program ON registration_pair(program_id);
CREATE INDEX idx_rp_subprogram ON registration_pair(sub_program_id);
CREATE INDEX idx_rp_code ON registration_pair(pair_code);
CREATE INDEX idx_rp_deleted ON registration_pair(deleted_at);


--- 9.Table to map program registrations to registration pairs
CREATE TABLE IF NOT EXISTS registration_pair_map (
    id BIGSERIAL PRIMARY KEY,
    registration_pair_id BIGINT NOT NULL,
    registration_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_rpm_pair FOREIGN KEY (registration_pair_id) REFERENCES registration_pair(id),
    CONSTRAINT fk_rgm_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id)
);

CREATE INDEX idx_rpm_pair ON registration_pair_map(registration_pair_id);
CREATE INDEX idx_rpm_registration ON registration_pair_map(registration_id);
CREATE INDEX idx_rpm_deleted ON registration_pair_map(deleted_at);


--- 10.Table to store registration groups for programs and sub-programs
CREATE TABLE IF NOT EXISTS registration_group (
    id BIGSERIAL PRIMARY KEY,
    group_name VARCHAR(255) NOT NULL,
    program_id BIGINT NOT NULL,
    sub_program_id BIGINT,
    remarks TEXT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_rg_program FOREIGN KEY (program_id) REFERENCES program_v1(id),
    CONSTRAINT fk_rg_sub_program FOREIGN KEY (sub_program_id) REFERENCES program_v1(id)
);

CREATE INDEX idx_rg_program ON registration_group(program_id);
CREATE INDEX idx_rg_subprogram ON registration_group(sub_program_id);
CREATE INDEX idx_rg_name ON registration_group(group_name);
CREATE INDEX idx_rg_deleted ON registration_group(deleted_at);


--- 11.Table to map program registrations to registration groups
CREATE TABLE IF NOT EXISTS registration_group_map (
    id BIGSERIAL PRIMARY KEY,
    registration_group_id BIGINT NOT NULL,
    registration_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    created_by BIGINT NOT NULL,
    updated_by BIGINT,
    
    CONSTRAINT fk_rgm_group FOREIGN KEY (registration_group_id) REFERENCES registration_group(id),
    CONSTRAINT fk_rgm_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id)
);

CREATE INDEX idx_rgm_group ON registration_group_map(registration_group_id);
CREATE INDEX idx_rgm_registration ON registration_group_map(registration_id);
CREATE INDEX idx_rgm_deleted ON registration_group_map(deleted_at);




--2025-10-13
SELECT * FROM lookup_data
WHERE lookup_category = 'RATRIA_PILLAR_LOCATION';


INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Chennai', 'Chennai', 'active', 1),
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Bangalore', 'Bangalore', 'active', 2),
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Kolkata', 'Kolkata', 'active', 3),
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Coimbatore', 'Coimbatore', 'active', 4),
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Hyderabad', 'Hyderabad', 'active', 5),
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Mumbai', 'Mumbai', 'active', 6),
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Pune', 'Pune', 'active', 7),
('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Location', 'Other', 'Other', 'active', 8);

SELECT * FROM lookup_data
WHERE lookup_category = 'RATRIA_PILLAR_LOCATION';

WITH count_cte AS (
    SELECT COUNT(*) AS total_count
    FROM lookup_data
    WHERE lookup_category = 'RATRIA_PILLAR_LOCATION'
),
ordered AS (
    SELECT 
        id,
        lookup_key,
        ROW_NUMBER() OVER (
            ORDER BY 
                CASE 
                    WHEN lookup_key = 'LEONIA' THEN 0
                    WHEN lookup_key = 'Other' THEN (SELECT total_count FROM count_cte)
                    ELSE 1
                END,
                lookup_key
        ) AS new_order
    FROM lookup_data
    WHERE lookup_category = 'RATRIA_PILLAR_LOCATION'
)
UPDATE lookup_data ld
SET lookup_order = ordered.new_order
FROM ordered
WHERE ld.id = ordered.id;

select * from hdb_form_section WHERE key = 'FS_GOODIES' AND created_by = -2;

UPDATE hdb_form_section
SET name = 'Goodies and Ratria Pillars'
WHERE key = 'FS_GOODIES' AND created_by = -2;


select * from hdb_form_section WHERE key = 'FS_GOODIES' AND created_by = -2;

select * from hdb_question 
WHERE binding_key IN ('goodiesFlask', 'goodiesNotebook', 'goodiesRatriaPillarLeonia', 'goodiesJacketApplicable','goodiesTshirtApplicable');

UPDATE hdb_question
SET config = jsonb_set(config::jsonb, '{isRequired}', 'true'::jsonb, true)
WHERE binding_key IN ('goodiesFlask', 'goodiesNotebook', 'goodiesRatriaPillarLeonia', 'goodiesJacketApplicable','goodiesTshirtApplicable');

-- Add terminal columns to travel plan table
ALTER TABLE hdb_registration_travel_plan
ADD COLUMN onward_terminal VARCHAR(100),
ADD COLUMN return_terminal VARCHAR(100);

-- Step 1: Create option category for airport terminals
WITH inserted_category AS (
  INSERT INTO hdb_option_category (
    name, created_at, updated_at, created_by, updated_by
  )
  VALUES (
    'Airport Terminal', NOW(), NOW(), -2, -2
  )
  RETURNING id
),

-- Step 2: Insert options using returned category id
inserted_options AS (
  INSERT INTO hdb_option (
    name, category_id, created_at, updated_at, created_by, updated_by, typ, stats
  )
  SELECT name, inserted_category.id, NOW(), NOW(), -2, -2, 'string', 'published'
  FROM inserted_category,
  (VALUES 
    ('Domestic'),
    ('International')
  ) AS option_names(name)
  RETURNING id, name
),

-- Step 3: Insert onward terminal question
inserted_onward_question AS (
  INSERT INTO hdb_question (
    label, 
    config, 
    stats, 
    typ, 
    created_at, 
    updated_at, 
    created_by, 
    updated_by, 
    form_section_id, 
    answer_location, 
    binding_key
  )
  VALUES (
    'Domestic / International',
    jsonb_build_object(
        'isRequired', true,
        'dependsOn', jsonb_build_array(
          jsonb_build_object(
              'value', 'Flight',
              'questionId', (SELECT id FROM public.hdb_question 
                              WHERE binding_key = 'travelPlanOnward' AND created_by = -2),
              'questionBindingKey', 'travelPlanOnward'
          )
        )
    ),
    'published',
    'radio',
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id 
     FROM hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' 
       AND created_by = -2 
     LIMIT 1),
    'hdb_registration_travel_plan.onward_terminal',
    'onwardTerminal'
  )
  RETURNING id
)

-- Step 4: Create question-option mappings for onward terminal
INSERT INTO hdb_question_option_map (
  question_id, option_id, created_at, updated_at, created_by, updated_by
)
SELECT 
  q.id, o.id, NOW(), NOW(), -2, -2
FROM inserted_onward_question q
CROSS JOIN inserted_options o;


INSERT INTO hdb_question (
    label, 
    config, 
    stats, 
    typ, 
    created_at, 
    updated_at, 
    created_by, 
    updated_by, 
    form_section_id, 
    answer_location, 
    binding_key
)
VALUES (
    'Domestic / International',
    jsonb_build_object(
        'isRequired', true,
        'dependsOn', jsonb_build_array(
          jsonb_build_object(
              'value', 'Flight',
              'questionId', (SELECT id FROM public.hdb_question 
                              WHERE binding_key = 'travelPlanReturn' AND created_by = -2),
              'questionBindingKey', 'travelPlanReturn'
          )
        )
    ),
    'published',
    'radio',
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id 
     FROM hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' 
       AND created_by = -2 
     LIMIT 1),
    'hdb_registration_travel_plan.return_terminal',
    'returnTerminal'
);

-- Step 4: Create question-option mappings for return terminal
INSERT INTO hdb_question_option_map (
  question_id, option_id, created_at, updated_at, created_by, updated_by
)
SELECT 
  (SELECT id FROM hdb_question WHERE binding_key = 'returnTerminal' AND created_by = -2),
  o.id, NOW(), NOW(), -2, -2
FROM hdb_option o
JOIN hdb_option_category oc ON o.category_id = oc.id
WHERE oc.name = 'Airport Terminal' 
  AND oc.created_by = -2
  AND o.created_by = -2;

INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    1082,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'onwardTerminal' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    0,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' AND created_by = -2)
);

-- otherDeparture
INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    1082,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'returnTerminal' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    1,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' AND created_by = -2)
);


UPDATE hdb_program_question
SET display_order = CASE question_id
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoType') THEN 0
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'pictureUrl') THEN 1
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'idBack') THEN 2
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoNumber') THEN 3
	  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'internationalId') THEN 4
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'passport') THEN 5
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'visa') THEN 6
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelPlanOnward') THEN 7
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'uploadOnwardJourneyTicket') THEN 8
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'onwardTerminal') THEN 9
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameOnward') THEN 10
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherAirlineNameOnward') THEN 11
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'flightNumberOnward') THEN 12
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'comingFrom') THEN 13
	  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherArrival') THEN 14
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'arrivalDateTime') THEN 15
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'onwardInfo') THEN 16
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelPlanReturn') THEN 17
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'uploadReturnJourneyTicket') THEN 18
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'returnTerminal') THEN 19
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameReturn') THEN 20
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherAirlineNameReturn') THEN 21
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'flightNumberReturn') THEN 22
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goingTo') THEN 23
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherDeparture') THEN 24
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'departureDateTime') THEN 25
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'returnInfo') THEN 26
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'alternatePhoneNumber') THEN 27
    ELSE display_order
END
WHERE form_section_id = (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2)
  AND question_id IN (
      SELECT id FROM public.hdb_question
      WHERE binding_key IN (
          'pictureUrl','idBack','travelInfoType','travelInfoNumber','internationalId','passport','visa','travelPlanOnward','uploadOnwardJourneyTicket','airlineNameOnward','flightNumberOnward',
          'comingFrom','arrivalDateTime','onwardInfo','travelPlanReturn','uploadReturnJourneyTicket',
          'airlineNameReturn','flightNumberReturn','goingTo','departureDateTime','returnInfo',
          'alternatePhoneNumber','otherAirlineNameOnward','otherAirlineNameReturn', 'otherDeparture','otherArrival', 'onwardTerminal','returnTerminal'
      )
  );


SELECT 
    *
FROM hdb_question
WHERE binding_key IN (
    'goodiesNotebook',
    'goodiesTshirtApplicable',
    'goodiesJacketApplicable',
    'goodiesRatriaPillarLeonia',
    'goodiesFlask'
)
ORDER BY binding_key;



-- 1. Update Notebook (questionBindingKey: goodiesNotebook)
UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{defaultValue}',
    '{
      "conditions": [],
      "fallback": "yes"
    }'::jsonb
)
WHERE binding_key = 'goodiesNotebook';

-- 2. Update T-shirt (questionBindingKey: goodiesTshirtApplicable)
UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{defaultValue}',
    '{
      "conditions": [
        {
          "priority": 1,
          "criteria": {
            "no_of_hdbs": { "greaterThan": 0 },
            "QK_LAST_HDB": { "greaterThanOrEqual": "2019-2020" }
          },
          "value": "no"
        }
      ],
      "fallback": "yes"
    }'::jsonb
)
WHERE binding_key = 'goodiesTshirtApplicable';


-- 3. Update Jacket (questionBindingKey: goodiesJacketApplicable)
UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{defaultValue}',
    '{
      "conditions": [
        {
          "priority": 1,
          "criteria": {
            "no_of_hdbs": { "greaterThan": 0 },
            "QK_LAST_HDB": { "greaterThanOrEqual": "2019-2020" }
          },
          "value": "no"
        }
      ],
      "fallback": "yes"
    }'::jsonb
)
WHERE binding_key = 'goodiesJacketApplicable';

-- 4. Update Ratria Pillar (questionBindingKey: ratriaPillarLocation)
UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{defaultValue}',
    '{
      "conditions": [
        {
          "priority": 1,
          "criteria": {
            "no_of_hdbs": { "equals": 0 }
          },
          "value": "yes"
        },
        {
          "priority": 2,
          "criteria": {
            "no_of_hdbs": { "greaterThan": 0 },
            "QK_LAST_HDB": { "lessThan": "2016-2017" }
          },
          "value": "yes"
        },
        {
          "priority": 3,
          "criteria": {
            "no_of_hdbs": { "greaterThan": 0 },
            "QK_LAST_HDB": { "greaterThanOrEqual": "2016-2017" }
          },
          "value": "no"
        }
      ],
      "fallback": "no"
    }'::jsonb
)
WHERE binding_key = 'goodiesRatriaPillarLeonia';

-- 5. Update Flask (questionBindingKey: goodiesFlask)
UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{defaultValue}',
    '{
      "conditions": [
        {
          "priority": 1,
          "criteria": {
            "no_of_hdbs": { "greaterThan": 0 },
            "QK_LAST_HDB": { "greaterThanOrEqual": "2019-2020" }
          },
          "value": "no"
        }
      ],
      "fallback": "yes"
    }'::jsonb
)
WHERE binding_key = 'goodiesFlask';

SELECT 
    *
FROM hdb_question
WHERE binding_key IN (
    'goodiesNotebook',
    'goodiesTshirtApplicable',
    'goodiesJacketApplicable',
    'goodiesRatriaPillarLeonia',
    'goodiesFlask'
)
ORDER BY binding_key;

-- 2025-10-07
UPDATE hdb_question
SET config = jsonb_set(config::jsonb, '{isRequired}', 'true'::jsonb, true)
WHERE binding_key IN ('goodiesTshirtSize', 'goodiesJacketSize', 'ratriaPillarLocation', 'ratriaPillarOtherLocation');

SELECT 
    id,
    binding_key,
    config
FROM hdb_question 
WHERE binding_key = 'travelInfoNumber';

-- Update the Aadhar pattern
WITH aadhar_index AS (
    SELECT 
        id,
        (idx - 1) as array_index
    FROM hdb_question,
         jsonb_array_elements(config::jsonb->'validationConfig'->'validationPattern') WITH ORDINALITY arr(elem, idx)
    WHERE binding_key = 'travelInfoNumber'
      AND elem->>'validateif' = 'Aadhar'
)
UPDATE hdb_question hq
SET config = jsonb_set(
    hq.config::jsonb,
    array['validationConfig', 'validationPattern', ai.array_index::text, 'pattern'],
    '"^(?:[2-9][0-9]{11}|(?:XXXXXXXX|xxxxxxxx|\\*{8})[0-9]{4})$"')
FROM aadhar_index ai
WHERE hq.id = ai.id;

-- Verify the update
SELECT 
    id,
    binding_key,
    config
FROM hdb_question 
WHERE binding_key = 'travelInfoNumber';

-- 2025-10-06
-- To find the registrations which are moved from save_as_draft to pending for program_id 931
select distinct ahd.*, ah.created_at as save_as_draft_to_pending_moved_date, ah.parent_ref_id as regId, pr_inner.registration_date as registration_date_from_reg_table from public.audit_history_log_detail ahd
join audit_history_log ah on ah.id = ahd.audit_log_id
join public.hdb_program_registration pr_inner 
        on pr_inner.id = ah.parent_ref_id::int
where 
field_name = 'registrationStatus' and
old_value::text like '%save_as_draft%' and 
new_value::text like '%pending%' and
audit_log_id in (
	select distinct id from public.audit_history_log where 
	parent_ref_id::int in (
		select id from public.hdb_program_registration where program_id = 931 --- use the program_id here
	)
)
order by audit_log_id;

--- Update the registration_date in hdb_program_registration table with the created_at date from audit_history_log table
UPDATE public.hdb_program_registration AS pr
SET registration_date = t.created_at
FROM (
    SELECT DISTINCT ON (pr_inner.id)
        pr_inner.id AS reg_id,
        ah.created_at
    FROM public.audit_history_log_detail ahd
    JOIN public.audit_history_log ah 
        ON ah.id = ahd.audit_log_id
    JOIN public.hdb_program_registration pr_inner 
        ON pr_inner.id = ah.parent_ref_id::int
    WHERE 
        ahd.field_name = 'registrationStatus'
        AND ahd.old_value::text ILIKE '%save_as_draft%'
        AND ahd.new_value::text ILIKE '%pending%'
        AND pr_inner.program_id = 931 -- use the program_id here 
    ORDER BY pr_inner.id, ah.created_at
) AS t
WHERE pr.id = t.reg_id
  AND pr.program_id = 931; -- use the program_id here


--2025-10-01
select * from hdb_question where binding_key in ('airlineNameOnward','airlineNameReturn');

UPDATE public.hdb_question
SET config = config - 'validationPattern',
    updated_at = NOW(),
    updated_by = -2
where binding_key in ('airlineNameOnward','airlineNameReturn')
  AND created_by = -2;


UPDATE program_v1
SET checkin_at = checkin_at::date + TIME '09:30:00',
checkout_at = checkout_at::date + TIME '01:30:00',
checkin_ends_at = checkin_ends_at::date + TIME '14:30:00';

select * from hdb_question where created_by = -2 and form_section_id = 3 and binding_key in ('flightNumberOnward','flightNumberReturn');

UPDATE hdb_question
SET config = jsonb_set(
               jsonb_set(
                 jsonb_set(
                   config::jsonb,
                   '{minCharacter}',
                   '5'::jsonb
                 ),
                 '{maxCharacters}',
                 '10'::jsonb
               ),
               '{validationPattern}',
               '"^[A-Z0-9\\-]+$"'::jsonb
             )
WHERE created_by = -2
  AND form_section_id = 3
  AND binding_key IN ('flightNumberOnward', 'flightNumberReturn');


INSERT INTO public.lookup_data 
  (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES
  ('AIRLINE', 'Airline', 'IX', 'Air India Express', 'active', 12),
  ('AIRLINE', 'Airline', 'QP', 'Akasa Air', 'active', 13),
  ('AIRLINE', 'Airline', '9I', 'Alliance Air', 'active', 14),
  ('AIRLINE', 'Airline', 'S5', 'Star Air', 'active', 15);

select * from lookup_data where lookup_category = 'AIRLINE';

UPDATE public.lookup_data
SET lookup_status = 'inactive'
WHERE lookup_category = 'AIRLINE'
  AND lookup_key IN ('I5', 'G8', 'UK');


select * from lookup_data where lookup_category = 'AIRLINE';


-- Step 1: Assign temporary order for the category 'AIRLINE'
WITH ordered AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY lookup_category 
            ORDER BY CASE WHEN lookup_label = 'Other' THEN 1 ELSE 0 END ASC, lookup_label ASC
        ) AS temp_order
    FROM lookup_data
    WHERE lookup_category = 'AIRLINE'
)
UPDATE lookup_data ld
SET lookup_order = o.temp_order
FROM ordered o
WHERE ld.id = o.id;

-- Step 2: Push 'Other' to the last position for 'AIRLINE'
WITH max_order AS (
    SELECT lookup_category, MAX(lookup_order) AS max_order
    FROM lookup_data
    WHERE lookup_category = 'AIRLINE'
      AND lookup_label <> 'Other'
    GROUP BY lookup_category
)
UPDATE lookup_data ld
SET lookup_order = mo.max_order + 1
FROM max_order mo
WHERE ld.lookup_category = mo.lookup_category
  AND ld.lookup_label = 'Other';


select * from lookup_data where lookup_category = 'AIRLINE';


-- 2025-09-30
-- Prefill config for flightNumberOnward
UPDATE hdb_question
SET config = config || jsonb_build_object(
  'prefill', jsonb_build_object(
    'addToPrefill', '-',
    'prefillFrom', (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameOnward' AND created_by = -2),
    'prefillType', 'fieldDependent',
    'prefillFromBindingKey', 'airlineNameOnward'
  )
)
WHERE binding_key = 'flightNumberOnward' AND created_by = -2;

-- Prefill config for flightNumberReturn
UPDATE hdb_question
SET config = config || jsonb_build_object(
  'prefill', jsonb_build_object(
    'addToPrefill', '-',
    'prefillFrom', (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameReturn' AND created_by = -2),
    'prefillType', 'fieldDependent',
    'prefillFromBindingKey', 'airlineNameReturn'
  )
)
WHERE binding_key = 'flightNumberReturn' AND created_by = -2;

select * from hdb_question where created_by = -2 and binding_key IN ('goingTo', 'comingFrom');


UPDATE hdb_question
SET typ = 'apicall',
    config = config || '{
      "type": "select",
      "apiUrl": "lookup-data/all",
      "category": "CITY_NAME"
    }'::jsonb
WHERE binding_key IN ('goingTo', 'comingFrom')
  AND form_section_id IN (
        SELECT id
        FROM hdb_form_section
        WHERE key = 'FS_TRAVELPLAN'
          AND created_by = -2
      )
	AND created_by = -2;


ALTER TABLE hdb_registration_travel_plan
ADD COLUMN other_departure VARCHAR(100),
ADD COLUMN other_arrival VARCHAR(100);

INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
    binding_key,
    stats,
    answer_location
)
VALUES (
    'Other',
    'text',
    jsonb_build_object(
        'isRequired', true,
        'minCharacter', 1,
        'maxCharacters', 100,
        'dependsOn', jsonb_build_array(
          jsonb_build_object(
              'value', 'Other',
              'questionId', (SELECT id FROM public.hdb_question 
                              WHERE binding_key = 'goingTo' AND created_by = -2),
              'questionBindingKey', 'goingTo'
          )
        )
    ),
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2),
    'otherDeparture',
    'published',
    'hdb_registration_travel_plan.other_departure'
);


INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
    binding_key,
    stats,
    answer_location
)
VALUES (
    'Other',
    'text',
    jsonb_build_object(
        'isRequired', true,
        'minCharacter', 1,
        'maxCharacters', 100,
        'dependsOn', jsonb_build_array(
          jsonb_build_object(
              'value', 'Other',
              'questionId', (SELECT id FROM public.hdb_question 
                              WHERE binding_key = 'comingFrom' AND created_by = -2),
              'questionBindingKey', 'comingFrom'
          )
        )
    ),
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2),
    'otherArrival',
    'published',
    'hdb_registration_travel_plan.other_arrival'
);



-- otherArrival
INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    883,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'otherArrival' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    0,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' AND created_by = -2)
);

-- otherDeparture
INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    883,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'otherDeparture' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    1,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' AND created_by = -2)
);

UPDATE hdb_program_question
SET display_order = CASE question_id
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoType') THEN 0
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'pictureUrl') THEN 1
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'idBack') THEN 2
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoNumber') THEN 3
	  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'internationalId') THEN 4
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'passport') THEN 5
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'visa') THEN 6
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelPlanOnward') THEN 7
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'uploadOnwardJourneyTicket') THEN 8
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameOnward') THEN 9
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherAirlineNameOnward') THEN 10
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'flightNumberOnward') THEN 11
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'comingFrom') THEN 12
	  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherArrival') THEN 13
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'arrivalDateTime') THEN 14
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'onwardInfo') THEN 15
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelPlanReturn') THEN 16
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'uploadReturnJourneyTicket') THEN 17
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameReturn') THEN 18
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherAirlineNameReturn') THEN 19
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'flightNumberReturn') THEN 20
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goingTo') THEN 21
	  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherDeparture') THEN 22
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'departureDateTime') THEN 23
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'returnInfo') THEN 24
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'alternatePhoneNumber') THEN 25
    ELSE display_order
END
WHERE form_section_id = (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2)
  AND question_id IN (
      SELECT id FROM public.hdb_question
      WHERE binding_key IN (
          'pictureUrl','idBack','travelInfoType','travelInfoNumber','internationalId','passport','visa','travelPlanOnward','uploadOnwardJourneyTicket','airlineNameOnward','flightNumberOnward',
          'comingFrom','arrivalDateTime','onwardInfo','travelPlanReturn','uploadReturnJourneyTicket',
          'airlineNameReturn','flightNumberReturn','goingTo','departureDateTime','returnInfo',
          'alternatePhoneNumber','otherAirlineNameOnward','otherAirlineNameReturn', 'otherDeparture','otherArrival'
      )
  );



-- Add new question: Where do you want to give the Ratria Pillars?

ALTER TABLE hdb_program_registration_goodies
ADD COLUMN ratria_pillar_location VARCHAR(100),
ADD COLUMN ratria_pillar_other_location VARCHAR(100);

INSERT INTO public.hdb_question (
  label,
  typ,
  config,
  created_at,
  updated_at,
  created_by,
  updated_by,
  form_section_id,
  binding_key,
  stats,
  answer_location
)
VALUES (
  'Where do you want to give the Ratria Pillars?',
  'apicall',
  jsonb_build_object(
    'type', 'select',
    'apiUrl', 'lookup-data/all',
    'category', 'RATRIA_PILLAR_LOCATION',
    'isRequired', true,
    'dependsOn', jsonb_build_array(
          jsonb_build_object(
              'value', 'yes',
              'questionId', (SELECT id FROM public.hdb_question 
                              WHERE binding_key = 'goodiesRatriaPillarLeonia' AND created_by = -2),
              'questionBindingKey', 'goodiesRatriaPillarLeonia'
          )
        )
  ),
  NOW(),
  NOW(),
  -2,
  -2,
  (SELECT id FROM public.hdb_form_section WHERE key = 'FS_GOODIES' and created_by = -2),
  'ratriaPillarLocation',
  'published',
  'hdb_program_registration_goodies.ratria_pillar_location'
);

INSERT INTO public.hdb_question (
  label,
  typ,
  config,
  created_at,
  updated_at,
  created_by,
  updated_by,
  form_section_id,
  binding_key,
  stats,
  answer_location
)
VALUES (
  'Other location',
  'text',
  jsonb_build_object(
    'isRequired', true,
    'minCharacter', 1,
    'maxCharacters', 100,
    'dependsOn', jsonb_build_array(
          jsonb_build_object(
              'value', 'Other',
              'questionId', (SELECT id FROM public.hdb_question 
                              WHERE binding_key = 'ratriaPillarLocation' AND created_by = -2),
              'questionBindingKey', 'ratriaPillarLocation'
          )
        )
  ),
  NOW(),
  NOW(),
  -2,
  -2,
  (SELECT id FROM public.hdb_form_section WHERE key = 'FS_GOODIES' and created_by = -2),
  'ratriaPillarOtherLocation',
  'published',
  'hdb_program_registration_goodies.ratria_pillar_other_location'
);

-- Update label for Ratria Pillars Yes/No question
UPDATE hdb_question
SET label = 'Ratria Pillars'
WHERE binding_key = 'goodiesRatriaPillarLeonia' AND created_by = -2;


-- ratriaPillarLocation
INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    1082,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'ratriaPillarLocation' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    0,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_GOODIES' AND created_by = -2)
);

-- ratriaPillarOtherLocation
INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    1082,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'ratriaPillarOtherLocation' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    1,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_GOODIES' AND created_by = -2)
);

-- Set display_order for goodies and Ratria Pillar related questions
UPDATE hdb_program_question
SET display_order = CASE question_id
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtApplicable') THEN 1
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtSize') THEN 2
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketApplicable') THEN 3
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketSize') THEN 4
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesNotebook') THEN 5
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesFlask') THEN 6
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesRatriaPillarLeonia') THEN 7
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarLocation') THEN 8
  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarOtherLocation') THEN 9
  ELSE display_order
END
WHERE question_id IN (
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtApplicable'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesTshirtSize'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketApplicable'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesJacketSize'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesNotebook'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesFlask'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarLocation'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'ratriaPillarOtherLocation'),
  (SELECT id FROM public.hdb_question WHERE binding_key = 'goodiesRatriaPillarLeonia')
);

-- Lookup data for RATRIA_PILLAR_LOCATION (Leonia at top)
INSERT INTO public.lookup_data 
  (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES
  ('RATRIA_PILLAR_LOCATION', 'Ratria Pillar Locations', 'LEONIA', 'Leonia', 'active', 1);

--2025-09-26 -- here we are using the static program_id for HDB/MSD program which is 1058
--- add other airline name columns in hdb_registration_travel_plan table

ALTER TABLE hdb_registration_travel_plan
ADD COLUMN arrival_other_airline_name VARCHAR(100),
ADD COLUMN departure_other_airline_name VARCHAR(100);


-- Change airlineNameReturn and airlineNameOnward question type to apicall and add config for apiUrl and category
UPDATE hdb_question
SET typ = 'apicall',
    config = config || '{
      "type": "select",
      "apiUrl": "lookup-data/all",
      "category": "AIRLINE"
    }'::jsonb
WHERE binding_key IN ('airlineNameReturn', 'airlineNameOnward')
  AND form_section_id IN (
        SELECT id
        FROM hdb_form_section
        WHERE key = 'FS_TRAVELPLAN'
          AND created_by = -2
      )
	AND created_by = -2;



--- Add other arrival airline question for travel plan section

INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
    binding_key,
    stats,
    answer_location
)
VALUES (
    'Other airline name',
    'text',
    jsonb_build_object(
        'isRequired', true,
        'minCharacter', 1,
        'maxCharacters', 100,
        'dependsOn', jsonb_build_array(
          jsonb_build_object(
              'value', 'OTHER',
              'questionId', (SELECT id FROM public.hdb_question 
                              WHERE binding_key = 'airlineNameOnward' AND created_by = -2),
              'questionBindingKey', 'airlineNameOnward'
          )
        )
    ),
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2),
    'otherAirlineNameOnward',
    'published',
    'hdb_registration_travel_plan.arrival_other_airline_name'
);

--- Add other return airline question for travel plan section

INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
    binding_key,
    stats,
    answer_location
)
VALUES (
  'Other airline name',
  'text',
  jsonb_build_object(
      'isRequired', true,
      'minCharacter', 1,
      'maxCharacters', 100,
      'dependsOn', jsonb_build_array(
        jsonb_build_object(
          'value', 'OTHER',
          'questionId', (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameReturn' and created_by = -2),
          'questionBindingKey', 'airlineNameReturn'
        )
      )
  ),
  NOW(),
  NOW(),
  -2,
  -2,
  (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2),
  'otherAirlineNameReturn',
  'published',
  'hdb_registration_travel_plan.departure_other_airline_name'
);


delete from hdb_program_question
where question_id in (
	select id from hdb_question 
	where binding_key = 'registrationComments' 
	and form_section_id in (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
	and created_by = -2
);

UPDATE hdb_question
SET label = 'Upload International ID card'
WHERE binding_key = 'internationalId' AND
created_by = -2 AND
form_section_id = (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' AND created_by = -2);



select * from hdb_question
where form_section_id = (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
and created_by = -2 
and binding_key in ('travelPlanReturn','travelPlanOnward');

UPDATE hdb_question
SET config = jsonb_set(config::jsonb, '{isRequired}', 'false'::jsonb)
WHERE form_section_id =(
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
  AND created_by = -2
  AND binding_key IN ('travelPlanReturn','travelPlanOnward');


select * from hdb_question
where form_section_id = (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
and created_by = -2 
and binding_key in ('travelPlanReturn','travelPlanOnward');

UPDATE hdb_question
SET config = config - 'dependsOn'
WHERE form_section_id = (
    select id from hdb_form_section 
    where key = 'FS_TRAVELPLAN'
    and created_by = -2
  )
  AND created_by = -2
  AND binding_key IN ('travelPlanReturn','travelPlanOnward');

select * from hdb_question
where form_section_id = (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
and created_by = -2 
and binding_key in ('travelPlanReturn','travelPlanOnward');


delete from hdb_program_question
where question_id in (
	select id from hdb_question 
	where binding_key = 'travelDetails' 
	and form_section_id in (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
	and created_by = -2
);

UPDATE hdb_question
SET config = config - 'dependsOn'
WHERE form_section_id = (
		select id from hdb_form_section 
		where key = 'FS_TRAVELPLAN'
		and created_by = -2
	)
  AND created_by = -2
  AND binding_key IN ('returnInfo','onwardInfo');

select * from hdb_question
where created_by = -2 
and binding_key in 
('returnInfo','onwardInfo');

-- Onward airline
INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    883,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'otherAirlineNameOnward' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    0,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' AND created_by = -2)
);

-- Return airline
INSERT INTO public.hdb_program_question (
    id,
    program_id,
    question_id,
    created_at,
    updated_at,
    deleted_at,
    program_session_id,
    registration_level,
    created_by,
    updated_by,
    display_order,
    form_section_id
)
VALUES (
    nextval('hdb_program_question_id_seq'),  -- dynamic id
    883,  -- replace with your program_id
    (SELECT id FROM public.hdb_question 
     WHERE binding_key = 'otherAirlineNameReturn' AND created_by = -2),
    NOW(),
    NOW(),
    NULL,
    NULL,
    'program',
    -2,
    -2,
    1,
    (SELECT id FROM public.hdb_form_section 
     WHERE key = 'FS_TRAVELPLAN' AND created_by = -2)
);

UPDATE hdb_program_question
SET display_order = CASE question_id
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'pictureUrl') THEN 0
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'idBack') THEN 1
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoType') THEN 2
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelInfoNumber') THEN 3
	  WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'internationalId') THEN 4
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'passport') THEN 5
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'visa') THEN 6
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelPlanOnward') THEN 7
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'uploadOnwardJourneyTicket') THEN 8
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameOnward') THEN 9
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherAirlineNameOnward') THEN 10
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'flightNumberOnward') THEN 11
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'comingFrom') THEN 12
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'arrivalDateTime') THEN 13
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'onwardInfo') THEN 14
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'travelPlanReturn') THEN 15
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'uploadReturnJourneyTicket') THEN 16
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'airlineNameReturn') THEN 17
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'otherAirlineNameReturn') THEN 18
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'flightNumberReturn') THEN 19
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'goingTo') THEN 20
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'departureDateTime') THEN 21
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'returnInfo') THEN 22
    WHEN (SELECT id FROM public.hdb_question WHERE binding_key = 'alternatePhoneNumber') THEN 23
    ELSE display_order
END
WHERE form_section_id = (SELECT id FROM public.hdb_form_section WHERE key = 'FS_TRAVELPLAN' and created_by = -2)
  AND question_id IN (
      SELECT id FROM public.hdb_question
      WHERE binding_key IN (
          'pictureUrl','idBack','travelInfoType','travelInfoNumber','internationalId','passport','visa','travelPlanOnward','uploadOnwardJourneyTicket','airlineNameOnward','flightNumberOnward',
          'comingFrom','arrivalDateTime','onwardInfo','travelPlanReturn','uploadReturnJourneyTicket',
          'airlineNameReturn','flightNumberReturn','goingTo','departureDateTime','returnInfo',
          'alternatePhoneNumber','otherAirlineNameOnward','otherAirlineNameReturn'
      )
  );



--2025-09-24

ALTER TABLE user_role 
ADD COLUMN priority INTEGER;

ALTER TABLE user_role 
ADD COLUMN is_system_role BOOLEAN NOT NULL DEFAULT false;

update user_role set is_system_role = true;

-- Update priority for user roles (lower value = higher priority)
UPDATE user_role SET priority = 1 WHERE role_key = 'ROLE_MAHATRIA';
UPDATE user_role SET priority = 2 WHERE role_key = 'ROLE_SHOBA';
UPDATE user_role SET priority = 3 WHERE role_key = 'ROLE_ADMIN';
UPDATE user_role SET priority = 4 WHERE role_key = 'ROLE_FINANCE_MANAGER';
UPDATE user_role SET priority = 5 WHERE role_key = 'ROLE_OPERATIONAL_MANAGER';
UPDATE user_role SET priority = 6 WHERE role_key = 'ROLE_RELATIONAL_MANAGER';
UPDATE user_role SET priority = 7 WHERE role_key = 'ROLE_VIEWER';

select * from hdb_question where created_by = -2 and binding_key = 'videoUrl';

update hdb_question
set label = 'First timers : Share a video to Mahatria - introduce yourself, your infinitheism journey, and why do you want to be in HDB/MSD.'
where created_by = -2 and binding_key = 'videoUrl';

--2025-09-24

select * from hdb_question where binding_key in ('flightNumberOnward', 'flightNumberReturn') and created_by = -2;

update hdb_question
set label = case
when binding_key = 'flightNumberOnward' then 'Onward flight number'
when binding_key = 'flightNumberReturn' then 'Return flight number'
else label
end
where binding_key in ('flightNumberOnward', 'flightNumberReturn') and created_by = -2;


--2025-09-20

-- Change endPoint key to apiUrl in phoneNumberRelation question

select * from hdb_question where binding_key = 'phoneNumberRelation' and created_by = -2;
UPDATE hdb_question
SET config = jsonb_set(
   config - 'endPoint',              -- remove old key first
   '{apiUrl}',                       -- insert new key
   config -> 'endPoint',             -- reuse old value
   true
)
WHERE binding_key = 'phoneNumberRelation'
  AND created_by = -2;

-- Add validation pattern for phoneNumberRelation question
UPDATE hdb_question
SET config = jsonb_set(
	config,
	'{validationPattern}',
	to_jsonb('^(?:[A-Za-z0-9 .,-/()#]+(?:\r?\n|$))+$'::text),
	true
)
WHERE created_by = -2
  AND binding_key IN ('invoiceAddress','proFormaBillingAddress');


--2025-09-19
select * from hdb_question where created_by = -2 and binding_key in ('invoiceAddress','proFormaBillingAddress');
UPDATE hdb_question
SET config = jsonb_set(
    jsonb_set(
        config,
        '{validationPattern}',
        to_jsonb('^[A-Za-z0-9 .,\-\/()#]+$'::text),
        true
    ),
    '{patternErrorMsg}',
    to_jsonb('Please use only letters, numbers, commas (,), hyphens (-), forward slashes (/), and spaces in the address field'::text),
    true
)
WHERE created_by = -2
  AND binding_key IN ('invoiceAddress', 'proFormaBillingAddress');

--2025-09-18
CREATE TABLE user_registration_map (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    registration_id BIGINT NOT NULL,
    summary_id BIGINT,
    created_by BIGINT,
    updated_by BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    
    -- Foreign key constraints
    CONSTRAINT fk_user_registration_map_user 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_registration_map_registration 
        FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_registration_map_summary 
        FOREIGN KEY (summary_id) REFERENCES user_participation_summary(id) ON DELETE SET NULL,
    CONSTRAINT fk_user_registration_map_created_by 
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_user_registration_map_updated_by 
        FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Create indexes for better query performance
CREATE INDEX idx_user_registration_map_user_id ON user_registration_map(user_id);
CREATE INDEX idx_user_registration_map_registration_id ON user_registration_map(registration_id);
CREATE INDEX idx_user_registration_map_summary_id ON user_registration_map(summary_id);
CREATE INDEX idx_user_registration_map_created_at ON user_registration_map(created_at);
CREATE INDEX idx_user_registration_map_deleted_at ON user_registration_map(deleted_at);

-- Create the type
CREATE TYPE origin_type_enum AS ENUM ('migrated', 'code_inserted');

ALTER TYPE origin_type_enum ADD VALUE IF NOT EXISTS 'migrated';
ALTER TYPE origin_type_enum ADD VALUE IF NOT EXISTS 'code_inserted';
ALTER TABLE user_participation_summary
ADD COLUMN IF NOT EXISTS origin_type origin_type_enum NOT NULL DEFAULT 'code_inserted';
ALTER TABLE user_registration_map
ADD COLUMN IF NOT EXISTS origin_type origin_type_enum NOT NULL DEFAULT 'code_inserted';


INSERT INTO public.lookup_data 
(lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'ENTRAINMENT_25', 'Entrainment 25', 'active', 33),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'MY_Z_AXIS_FORUM', 'My Z Axis - Forum', 'active', 34),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2025', 'TAT 2025', 'active', 35);

-- Add the song_preferences column
ALTER TABLE hdb_program_registration 
ADD COLUMN song_preferences JSONB NULL;

select * from hdb_question where binding_key = 'QK_HDB_MSD_SONG_PREFERENCES' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_program_registration.song_preferences'
where binding_key = 'QK_HDB_MSD_SONG_PREFERENCES' and created_by = -2;

-- Change upload video question label
UPDATE public.hdb_question
SET label = 'Share your video expression with Mahatria (applicable for first timers):'
WHERE binding_key = 'videoUrl' and created_by = -2;

-- Make song preferences question not required
UPDATE public.hdb_question
SET config = jsonb_set(
              config, '{isRequired}', 'false'::jsonb, false
            )
WHERE binding_key = 'QK_HDB_MSD_SONG_PREFERENCES'
  AND config ? 'isRequired'
  AND created_by = -2;


--2025-09-17

-- Update dob date validation
UPDATE public.hdb_question
SET config = jsonb_set(
                jsonb_set(config, '{pastValidation,minValue}', '0'::jsonb, false),
                '{pastValidation,maxValue}', '150'::jsonb, false
            )
WHERE binding_key = 'dob'
  AND config ? 'pastValidation'
  AND (config->'pastValidation') ? 'minValue'
  AND (config->'pastValidation') ? 'maxValue'
  AND created_by = -2;

INSERT INTO public.lookup_data 
(lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', '11_WAYS_2016', '11 Ways 2016', 'active', 1),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', '11_WAYS_2017', '11 Ways 2017', 'active', 2),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'BIRTH_OF_INFINITHEISM', 'Birth of infinitheism', 'active', 3),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'EYES_2022', 'EYES 2022', 'active', 4),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'ENTRAINMENT_23', 'Entrainment 23', 'active', 5),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'ENTRAINMENT_24', 'Entrainment 24', 'active', 6),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'HDB', 'HDB', 'active', 7),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'HOLISTIC_COURSE', 'Holistic course', 'active', 8),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'MSD', 'MSD', 'active', 9),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'MAHATRIA''S_PUBLIC_DISCOURSES', 'Mahatria''s Public Discourses', 'active', 10),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'MY_Z_AXIS', 'My Z Axis', 'active', 11),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'PAP', 'PAP', 'active', 12),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'R_TAT_2018', 'R-TAT 2018', 'active', 13),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'SR._CITIZEN_HDB', 'Sr. Citizen HDB', 'active', 14),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2014', 'TAT 2014', 'active', 15),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2015', 'TAT 2015', 'active', 16),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2019', 'TAT 2019', 'active', 17),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2020_ONLINE', 'TAT 2020 Online', 'active', 18),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2021_ONLINE', 'TAT 2021 Online', 'active', 19),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2022_ONLINE', 'TAT 2022 Online', 'active', 20),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2023', 'TAT 2023', 'active', 21),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'TAT_2024', 'TAT 2024', 'active', 22),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'AAAHA', 'aaaha', 'active', 23),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINI', 'infini', 'active', 24),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINI11', 'infini11', 'active', 25),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINI14', 'infini14', 'active', 26),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINIACADEMY', 'infiniAcademy', 'active', 27),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINIDANCE', 'infinidance', 'active', 28),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINIPATH', 'infinipath', 'active', 29),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINIPOWER', 'infinipower', 'active', 30),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINITHOUGHTS', 'infinithoughts', 'active', 31),
('SEEKER_PROGRAM_EXPERIENCE', 'Seeker Program Experiences', 'INFINIWAVES', 'infiniwaves', 'active', 32);


CREATE TABLE user_program_experience(
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    registration_id INTEGER,
    lookup_data_id INTEGER NOT NULL,
    created_by INTEGER,
    updated_by INTEGER,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted_at TIMESTAMPTZ,
    
    -- Foreign key constraints
    CONSTRAINT fk_user_program_experience_user 
        FOREIGN KEY (user_id) REFERENCES "users"(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_user_program_experience_registration 
        FOREIGN KEY (registration_id) REFERENCES "hdb_program_registration"(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_user_program_experience_lookup_data 
        FOREIGN KEY (lookup_data_id) REFERENCES "lookup_data"(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_user_program_experience_created_by 
        FOREIGN KEY (created_by) REFERENCES "users"(id) ON DELETE SET NULL,
    
    CONSTRAINT fk_user_program_experience_updated_by 
        FOREIGN KEY (updated_by) REFERENCES "users"(id) ON DELETE SET NULL
);

CREATE INDEX idx_user_program_experience_user_id ON "user_program_experience"(user_id);
CREATE INDEX idx_user_program_experience_registration_id ON "user_program_experience"(registration_id);
CREATE INDEX idx_user_program_experience_lookup_data_id ON "user_program_experience"(lookup_data_id);

INSERT INTO user_program_experience (user_id, lookup_data_id)
SELECT
  upe.user_id,
  ld.id
FROM user_profile_extension AS upe
CROSS JOIN LATERAL unnest(string_to_array(upe.which_of_the_following_have_you_experienced, ',')) AS x(raw_token)
CROSS JOIN LATERAL (
  SELECT trim(both from regexp_replace(raw_token, '\s+', ' ', 'g')) AS token -- trim & collapse inner whitespace
) t
JOIN lookup_data AS ld
  ON lower(ld.lookup_label) = lower(t.token)     -- case-insensitive match
WHERE coalesce(upe.which_of_the_following_have_you_experienced, '') <> ''
  AND t.token <> ''                               -- ignore empty tokens like trailing commas
ON CONFLICT DO NOTHING;

--- 2025-09-17

UPDATE public.hdb_question
SET config = jsonb_set(
    config,
    '{dependsOn}',
    (
      SELECT jsonb_agg(
               CASE
                 WHEN elem->>'value' = 'Different number'
                 THEN jsonb_set(elem, '{value}', '"Family member''s number"'::jsonb, false)
                 ELSE elem
               END
             )
      FROM jsonb_array_elements(config->'dependsOn') elem
    ),
    false
)
WHERE binding_key = 'mobileNumber'
  AND config ? 'dependsOn'
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements(config->'dependsOn') elem
      WHERE elem->>'value' = 'Different number'
  )
  AND created_by = -2;
  

-- Date: 2025-09-16
-- Add "overall" rating type to RM rating system
CREATE SEQUENCE hdb_program_registration_rm_rating_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE hdb_program_registration_rm_rating_type
    ALTER COLUMN id SET DEFAULT nextval('hdb_program_registration_rm_rating_type_id_seq');

ALTER SEQUENCE public.hdb_program_registration_rm_rating_type_id_seq OWNED BY public.hdb_program_registration_rm_rating_type.id;

INSERT INTO hdb_program_registration_rm_rating_type (id, rating_key, name, created_at, updated_at, created_by, updated_by) 
VALUES ((SELECT COALESCE(MAX(id), 0) + 1 FROM hdb_program_registration_rm_rating_type), 'overall', 'Overall', NOW(), NOW(), -1, -1);

-- Update the sequence to reflect the new ID
SELECT setval('hdb_program_registration_rm_rating_type_id_seq', (SELECT MAX(id) FROM hdb_program_registration_rm_rating_type), true);

--2025-09-16
ALTER TABLE users
	ADD COLUMN 
		org_usr_name VARCHAR(255);

-- This script is to update the org_usr_name column based on phone_number in the development and qa environment
UPDATE 
	users 
		SET org_usr_name = 
			CASE phone_number  
				WHEN '2817603626' THEN 'Bhupendhar' 
				WHEN '4345351236' THEN 'Swaroop'
				WHEN '4573994937' THEN 'Gopal'
				WHEN '4573994942' THEN 'Vijisubbu'
				WHEN '2242864639' THEN 'Vinod'
				WHEN '4123269028' THEN 'Manohar'
				WHEN '3014532713' THEN 'Mohini'
				WHEN '2092841144' THEN 'Charuta'
				WHEN '7146968342' THEN 'Mukundan'
				WHEN '5182612621' THEN 'Arun'
				WHEN '2562690950' THEN 'Subbu'
				WHEN '4106354732' THEN 'Mahesh'
				WHEN '4573994944' THEN 'Chithambaran'
				WHEN '4573994943' THEN 'Sridhar'
				WHEN '4573994941' THEN 'Sabari'
				WHEN '3312762635' THEN 'Other'
				WHEN '9999999999' THEN 'Kishan'
				WHEN '7659950042' THEN 'Harsha Vardhan Reddy'
				WHEN '8328316842' THEN 'Dudi vinay venkata sai durga avinash'
END
WHERE role = 'relational_manager';

-- for uat and prod need to change for prod
UPDATE users
    SET org_usr_name = CASE phone_number
      WHEN '9841021495' THEN 'Gopal'
      WHEN '9841727022' THEN 'Sridhar'
      WHEN '9841280188' THEN 'Sabari'
      WHEN '9011240808' THEN 'Charuta'
      WHEN '9884874161' THEN 'Mukundan'
      WHEN '9841149060' THEN 'Bhupendhar'
      WHEN '8094444402' THEN 'Vinod'
      WHEN '9657152211' THEN 'Swaroop'
      WHEN '9845191961' THEN 'Arun'
      WHEN '9841042289' THEN 'Manohar'
      WHEN '9841670000' THEN 'Other'
      WHEN '9841405117' THEN 'Subbu'
      WHEN '9880702700' THEN 'Mohini'
      WHEN '9791140214' THEN 'Mahesh'
      WHEN '9790768202' THEN 'Chithambaran'
      WHEN '8939910100' THEN 'Vijisubbu'
      WHEN '9999999999' THEN 'Kishan'
    END
WHERE role = 'relational_manager';


--2025-09-16
-- Change phone number question label
UPDATE hdb_question
SET label = 'Do you want to give your phone number or a family member''s number?'
WHERE binding_key = 'phoneNumberType' and created_by = -2;

-- Update option name from Different number to Family member's number
UPDATE hdb_option
SET name = 'Family member''s number'
WHERE name = 'Different number'
  AND category_id = (
    SELECT id
    FROM hdb_option_category
    WHERE name = 'Phone Number Type' and created_by = -2
  ) and created_by = -2;

-- Update dependsOn value in phoneNumberRelation question

UPDATE public.hdb_question
SET config = jsonb_set(
    config,
    '{dependsOn}',
    (
      SELECT jsonb_agg(
               CASE
                 WHEN elem->>'value' = 'Different number'
                 THEN jsonb_set(elem, '{value}', '"Family member''s number"'::jsonb, false)
                 ELSE elem
               END
             )
      FROM jsonb_array_elements(config->'dependsOn') elem
    ),
    false
)
WHERE binding_key = 'phoneNumberRelation' and created_by = -2;

-- Add Mahatria Questions

-- 1
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'What about life (personally yours or life as a whole) do you admire, wonder, in awe with…',
    'textarea',
    '{
        "isRequired": true,
		"minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	'QK_LIFE_ADMIRATION_AWE',
    'published'
);
--2
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'If one wish of yours can be granted, what would you wish for the world?',
    'textarea',
    '{
        "isRequired": true,
		"minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	'QK_ONE_WISH_FOR_WORLD',
    'published'
);
--3
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'Which is/are your most dominant deep positive emotion/s?',
    'textarea',
    '{
        "isRequired": true,
		"minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	'QK_DOM_POSITIVE_EMOTION',
    'published'
);
--4
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'Which is/are your most dominant deep negative emotion/s?',
    'textarea',
    '{
        "isRequired": true,
		"minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	'QK_DOM_NEGATIVE_EMOTION',
    'published'
);

--5 
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'Describe one ''Golden Moment'' from your last 365 days.',
    'textarea',
    '{
        "isRequired": true,
		"minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	'QK_PREVIOUS_GOLDEN_MOMENT',
    'published'
);
--6

INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'Which 3 aspects will be your primary focus in the next 365 days?',
    'multiQuestion',
    '{
        "isRequired": true,
        "displayLabelType": "textarea",
        "questions": [
             {"key": "aspect_1", "label": "Aspect 1", "minCharacter": 1, "maxCharacters": 111,"displayLabelType": "textarea", "displayOrder":1},
            {"key": "aspect_2", "label": "Aspect 2", "minCharacter": 1, "maxCharacters": 111,"displayLabelType": "textarea", "displayOrder":2},
            {"key": "aspect_3", "label": "Aspect 3", "minCharacter": 1, "maxCharacters": 111,"displayLabelType": "textarea", "displayOrder":3}
        ]
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	'QK_3PRIMARY_ASPECTS_FOR_NEXT_365DAYS',
    'published'
);

-- 7

INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'What is the first thought or first feeling that comes to you, when you think of Mahatria?',
    'textarea',
    '{
      "isRequired": true,
		  "minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	  'QK_FIRST_THOUGHT_ABOUT_MAHATRIA',
    'published'
);

--8
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'Of all the multidimensional experiences, which aspect of HDB/MSD do you most look forward to? (For first timers - What do you want from HDB/MSD?)',
    'textarea',
    '{
      "isRequired": true,
		  "minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	  'QK_HDB_MSD_ASPECT_LOOK_FORWARD',
    'published'
);

--9
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'If Mahatria can customise one session in HDB/MSD for you, what would your request be?',
    'textarea',
    '{
      "isRequired": true,
		  "minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	  'QK_HDB_MSD_CUSTOM_SESSION_REQUEST',
    'published'
);

--10

INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'Please provide your song preferences for HDB/MSD (furnish album/language details and spell them right):',
    'multiQuestion',
    '{
        "isRequired": true,
        "displayLabelType": "textarea",
        "questions": [
             {"key": "song_1", "label": "Song 1", "minCharacter": 1, "maxCharacters": 111,"displayLabelType": "textarea", "displayOrder":1},
            {"key": "song_2", "label": "Song 2", "minCharacter": 1, "maxCharacters": 111,"displayLabelType": "textarea", "displayOrder":2},
            {"key": "song_3", "label": "Song 3", "minCharacter": 1, "maxCharacters": 111,"displayLabelType": "textarea", "displayOrder":3}
        ]
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	'QK_HDB_MSD_SONG_PREFERENCES',
    'published'
);

--11
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
	binding_key,
    stats
)
VALUES (
    'Your personal expression to your ''My Mahatria'':',
    'textarea',
    '{
      "isRequired": true,
		  "minCharacter": 1,
	  	"maxCharacters": 111
    }'::jsonb,
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'), -- form_section_id
	  'QK_MY_EXPRESSION_TO_MAHATRIA',
    'published'
);
-- 12
INSERT INTO public.hdb_question (
    label,
    typ,
    config,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
    binding_key,
    stats
)
VALUES (
    'What do you want from HDB/MSD?',
    'textarea',
    jsonb_build_object(
        'isRequired', true,
        'minCharacter', 1,
        'maxCharacters', 111,
        'dependsOn', jsonb_build_array(
            jsonb_build_object(
                'value', 0,
                'operator', 'equals',
                'questionId', (SELECT id FROM public.hdb_question WHERE binding_key = 'no_of_hdbs' AND created_by = -2),
                'questionBindingKey', 'no_of_hdbs'
            )
        )
    ),
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM public.hdb_form_section WHERE key = 'FS_MAHATRIAQUESTIONS'),
    'HDB_MSD_EXPECTATION',
    'published'
);

UPDATE hdb_question
SET label = 'Of all the multidimensional experiences, which aspect of HDB/MSD do you most look forward to?'
WHERE binding_key = 'QK_HDB_MSD_ASPECT_LOOK_FORWARD';

UPDATE public.hdb_question
SET config = 
  CASE 
      WHEN config ? 'dependsOn' THEN
        jsonb_set(
            config,
            '{dependsOn}',
            (
                (config->'dependsOn')::jsonb || 
                jsonb_build_array(
                    jsonb_build_object(
                        'value', 0,
                        'operator', 'greaterThan',
                        'questionId', (
                            SELECT id 
                            FROM public.hdb_question 
                            WHERE binding_key = 'no_of_hdbs' 
                              AND created_by = -2
                            LIMIT 1
                        ),
                        'questionBindingKey', 'no_of_hdbs'
                    )
                )
            )
        )
      ELSE
        jsonb_set(
            config,
            '{dependsOn}',
            jsonb_build_array(
                jsonb_build_object(
                    'value', 0,
                    'operator', 'greaterThan',
                    'questionId', (
                        SELECT id 
                        FROM public.hdb_question 
                        WHERE binding_key = 'no_of_hdbs' 
                          AND created_by = -2
                        LIMIT 1
                    ),
                    'questionBindingKey', 'no_of_hdbs'
                )
            )
        )
  END
WHERE binding_key = 'QK_HDB_MSD_ASPECT_LOOK_FORWARD'
  AND created_by = -2;



--2025-09-15

ALTER TYPE parental_form_status_enum
ADD VALUE 'not_applicable';

SELECT *
FROM hdb_question where created_by = -2 and form_section_id = 4 and binding_key in ('QK_HDB_SONG_1','QK_HDB_SONG_2');


UPDATE hdb_question
SET label = TRIM(TRAILING '.' FROM label)
WHERE created_by = -2
  AND form_section_id = 4
  AND binding_key IN ('QK_HDB_SONG_1','QK_HDB_SONG_2');


SELECT *
FROM hdb_question where created_by = -2 and form_section_id = 1 and binding_key in ('QK_LAST_HDB');


UPDATE hdb_question
SET config = 
    CASE 
        WHEN config ? 'dependsOn' THEN
            jsonb_set(
                config,
                '{dependsOn}',
                (
                    (config->'dependsOn')::jsonb || 
                    jsonb_build_array(
                        jsonb_build_object(
                            'value', 0,
                            'operator', 'greaterThan',
                            'questionId', (
                                SELECT id 
                                FROM hdb_question 
                                WHERE binding_key = 'no_of_hdbs' 
                                  AND created_by = -2
                            ),
                            'questionBindingKey', 'no_of_hdbs'
                        )
                    )
                )
            )
        ELSE
            jsonb_set(
                config,
                '{dependsOn}',
                jsonb_build_array(
                    jsonb_build_object(
                        'value', 0,
                        'operator', 'greaterThan',
                        'questionId', (
                            SELECT id 
                            FROM hdb_question 
                            WHERE binding_key = 'no_of_hdbs' 
                              AND created_by = -2
                        ),
                        'questionBindingKey', 'no_of_hdbs'
                    )
                )
            )
    END
WHERE id = (
    SELECT id 
    FROM hdb_question 
    WHERE binding_key = 'QK_LAST_HDB' AND created_by = -2
);

--2025-09-11

select * from hdb_question where binding_key = 'mobileNumber' and created_by = -2 ;


UPDATE hdb_question 
SET 
    config = CASE 
        WHEN config ? 'dependsOn' THEN
            -- If dependsOn exists, append to it
            jsonb_set(
                config,
                '{dependsOn}',
                COALESCE(config->'dependsOn', '[]'::jsonb) || jsonb_build_object(
                    'type', 'disable',
                    'value', 'Different number',
                    'questionId', (SELECT id FROM hdb_question WHERE binding_key = 'phoneNumberType' AND created_by = -2),
                    'questionBindingKey', 'phoneNumberType'
                )
            )
        ELSE
            -- If dependsOn doesn't exist, create it
            config || jsonb_build_object(
                'dependsOn', 
                jsonb_build_array(
                    jsonb_build_object(
                        'type', 'disable',
                        'value', 'Different number',
                        'questionId', (SELECT id FROM hdb_question WHERE binding_key = 'phoneNumberType' AND created_by = -2),
                        'questionBindingKey', 'phoneNumberType'
                    )
                )
            )
    END,
    updated_at = NOW()
WHERE binding_key = 'mobileNumber';
--2025-09-10
UPDATE hdb_question 
SET 
    config = CASE 
        WHEN config ? 'dependsOn' THEN
            -- If dependsOn exists, append to it
            jsonb_set(
                config,
                '{dependsOn}',
                COALESCE(config->'dependsOn', '[]'::jsonb) || jsonb_build_object(
                    'transformedMaxValue', 'elderMinAge',
                    'transformedMinValue', 'childMaxAge',
                    'questionId', (SELECT id FROM hdb_question WHERE binding_key = 'dob' AND created_by = -2),
                    'questionBindingKey', 'dob'
                )
            )
        ELSE
            -- If dependsOn doesn't exist, create it
            config || jsonb_build_object(
                'dependsOn', 
                jsonb_build_object(
                    'transformedMaxValue', 'elderMinAge',
                    'transformedMinValue', 'childMaxAge',
                    'questionId', (SELECT id FROM hdb_question WHERE binding_key = 'dob' AND created_by = -2),
                    'questionBindingKey', 'dob'
                )
            )
    END,
    updated_at = NOW()
WHERE binding_key = 'phoneNumberType';

select * from hdb_question where created_by = -2 and form_section_id = 1 and binding_key = 'phoneNumberType';

--2025-09-08
select * from hdb_question where created_by = -2 and binding_key in ('proFormaGstNumber','proFormaZip', 'zip', 'gstNumber','tanNumber');

UPDATE hdb_question
SET config = config || jsonb_build_object(
    'minCharacter', 6,
    'maxCharacters', 6
)
WHERE created_by = -2
  AND binding_key IN ('proFormaZip', 'zip');


UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{validationPattern}',
    '"^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z]{1}[1-9A-Z]{1}Z[0-9A-Z]{1}$"'::jsonb,
    true
)
WHERE created_by = -2
  AND binding_key = 'gstNumber';



UPDATE hdb_question
SET config = jsonb_set(
    config::jsonb,
    '{validationPattern}',
    '"^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z]{1}[1-9A-Z]{1}Z[0-9A-Z]{1}$"'::jsonb,
    true
)
WHERE created_by = -2
  AND binding_key = 'proFormaGstNumber';

--2025-09-06

-- Add elder_min_age and child_max_age columns to program_v1 table
ALTER TABLE program_v1
ADD COLUMN elder_min_age INT,
ADD COLUMN child_max_age INT;

CREATE TYPE parental_form_status_enum AS ENUM ('pending', 'completed');

ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS parental_form_pdf_url TEXT;

ALTER TABLE hdb_program_registration
ADD COLUMN IF NOT EXISTS parental_form_status parental_form_status_enum;


COMMENT ON COLUMN hdb_program_registration.parental_form_pdf_url IS 'URL of the uploaded parental consent form PDF';

COMMENT ON COLUMN hdb_program_registration.parental_form_status IS 'Status of the parental consent form';


CREATE TYPE person_type_enum AS ENUM ('child', 'adult', 'elder');

ALTER TABLE hdb_program_registration
ADD COLUMN person_type person_type_enum;

--2025-09-05

-- Phone Number Question Setup
-- Add phone_number column to registration table if not exists
ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS phone_number_type VARCHAR(50);

-- Add comment for the new column
COMMENT ON COLUMN hdb_program_registration.phone_number_type IS 'Type of phone number for registration';

-- Step 1: Create option category and get its ID
WITH inserted_category AS (
  INSERT INTO hdb_option_category (
    name, created_at, updated_at, created_by, updated_by
  )
  VALUES (
    'Phone Number Type', NOW(), NOW(), -2, -2
  )
  RETURNING id
),

-- Step 2: Insert options using returned category id
inserted_options AS (
  INSERT INTO hdb_option (
    name, category_id, created_at, updated_at, created_by, updated_by, typ, stats
  )
  SELECT name, inserted_category.id, NOW(), NOW(), -2, -2, 'string', 'published'
  FROM inserted_category,
  (VALUES 
    ('My phone number'),
    ('Different number')
  ) AS option_names(name)
  RETURNING id, name
),

-- Step 3: Insert question and get its ID
inserted_question AS (
  INSERT INTO hdb_question (
    label, 
    config, 
    stats, 
    typ, 
    created_at, 
    updated_at, 
    created_by, 
    updated_by, 
    form_section_id, 
    answer_location, 
    binding_key
  )
  VALUES (
    'Do you want to give your phone number or a different number?',
    '{"isRequired": true, "dependsOn": [], "conditionalFields": []}',
    'published',
    'radio',
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id FROM hdb_form_section WHERE key = 'FS_BASICDETAILS' and created_by = -2 LIMIT 1),
    'hdb_program_registration.phone_number_type',
    'phoneNumberType'
  )
  RETURNING id
)

-- Step 4: Create question-option mappings
INSERT INTO hdb_question_option_map (
  question_id, option_id, created_by, updated_by, created_at, updated_at
)
SELECT 
  q.id, o.id, -2, -2, NOW(), NOW()
FROM inserted_question q
CROSS JOIN inserted_options o;



SELECT 
    q.id as question_id,
    q.label,
    q.binding_key,
    q.answer_location,
    oc.name as category_name,
    o.name as option_name,
    qom.id as mapping_id
FROM hdb_question q
JOIN hdb_question_option_map qom ON q.id = qom.question_id
JOIN hdb_option o ON qom.option_id = o.id
JOIN hdb_option_category oc ON o.category_id = oc.id
WHERE q.binding_key = 'phoneNumberType'
AND q.created_by = -2
ORDER BY o.id;



select * from lookup_data;





ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS phone_number_relation VARCHAR(50);

COMMENT ON COLUMN hdb_program_registration.phone_number_relation IS 'Relationship with the person associated with the entered phone number';


-- Insert relationship data into lookup_data table
INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES
('RELATION', 'Relation', 'FATHER', 'Father', 'active', 1),
('RELATION', 'Relation', 'MOTHER', 'Mother', 'active', 2),
('RELATION', 'Relation', 'BROTHER', 'Brother', 'active', 3),
('RELATION', 'Relation', 'SISTER', 'Sister', 'active', 4),
('RELATION', 'Relation', 'OTHER', 'Other', 'active', 5);



INSERT INTO hdb_question (
    label, 
    config, 
    stats, 
    typ, 
    created_at, 
    updated_at, 
    created_by, 
    updated_by, 
    form_section_id, 
    answer_location, 
    binding_key
)
VALUES (
    'What is the relationship with the person associated with the entered phone number?',
    jsonb_build_object(
        'type', 'select',
        'endPoint', 'lookup-data/all',
        'isRequired', true,
        'category', 'RELATION',
        'dependsOn', jsonb_build_array(
            jsonb_build_object(
                'value', 'Different number',
                'questionId', (
                    SELECT id 
                    FROM hdb_question 
                    WHERE binding_key = 'phoneNumberType' 
                      AND created_by = -2
                    LIMIT 1
                ),
                'questionBindingKey', 'phoneNumberType'
            )
        )
    ),
    'published',
    'apicall',
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id 
     FROM hdb_form_section 
     WHERE key = 'FS_BASICDETAILS' 
       AND created_by = -2 
     LIMIT 1),
    'hdb_program_registration.phone_number_relation',
    'phoneNumberRelation'
);


-- Add column for other relation
ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS other_relation VARCHAR(100);

COMMENT ON COLUMN hdb_program_registration.other_relation IS 'other relationship type when OTHER is selected';

-- Create "Other Relation" question
INSERT INTO hdb_question (
    label, 
    config, 
    stats, 
    typ, 
    created_at, 
    updated_at, 
    created_by, 
    updated_by, 
    form_section_id, 
    answer_location, 
    binding_key
)
VALUES (
    'Other relation',
    jsonb_build_object(
        'isRequired', true,
        'minCharacter', 2,
        'maxCharacters', 50,
        'validationPattern', '^[a-zA-Z\\s\\.]+$',
        'dependsOn', jsonb_build_array(
            jsonb_build_object(
                'value', 'OTHER',
                'questionId', (
                    SELECT id 
                    FROM hdb_question 
                    WHERE binding_key = 'phoneNumberRelation' 
                      AND created_by = -2
                    LIMIT 1
                ),
                'questionBindingKey', 'phoneNumberRelation'
            )
        )
    ),
    'published',
    'text',
    NOW(),
    NOW(),
    -2,
    -2,
    (SELECT id 
     FROM hdb_form_section 
     WHERE key = 'FS_BASICDETAILS' 
       AND created_by = -2 
     LIMIT 1),
    'hdb_program_registration.other_relation',
    'otherRelation'
);


-- Add banner_animation_url to program_v1 table
ALTER TABLE program_v1 
ADD COLUMN IF NOT EXISTS banner_animation_url VARCHAR(1024);

-- Add banner_animation_url to program_session table  
ALTER TABLE program_session 
ADD COLUMN IF NOT EXISTS banner_animation_url VARCHAR(1024);

-- Add comment for documentation
COMMENT ON COLUMN program_v1.banner_animation_url IS 'URL for banner animation video or GIF';
COMMENT ON COLUMN program_session.banner_animation_url IS 'URL for banner animation video or GIF';

select * from hdb_program_question where question_id in (
select id from hdb_question where created_by = -2 and binding_key in ('city','otherCityName','countryName')
);

UPDATE hdb_program_question pq
SET display_order = CASE q.binding_key
    WHEN 'countryName' THEN 5
    WHEN 'city' THEN 6
    WHEN 'otherCityName' THEN 7
END
FROM hdb_question q
WHERE pq.question_id = q.id
  AND q.created_by = -2
  AND q.binding_key IN ('city','otherCityName','countryName');


select id, config from hdb_question where created_by = -2 and binding_key = 'travelInfoNumber';


UPDATE hdb_question
SET config = config || jsonb_build_object(
    'validationConfig',
    jsonb_build_object(
        'dependentBindingKey', 'travelInfoType',
        'validationPattern', jsonb_build_array(
            jsonb_build_object(
                'validateif', 'Aadhar',
                'length', 12,
                'pattern', '^[2-9]{1}[0-9]{11}$'
            ),
            jsonb_build_object(
                'validateif', 'Pan card',
                'length', 10,
                'pattern', '^[A-Z]{5}[0-9]{4}[A-Z]{1}$'
            )
        )
    )
)
WHERE created_by = -2
  AND binding_key = 'travelInfoNumber';

--2025-09-04
select * from hdb_question where created_by = -2 and binding_key = 'city';

UPDATE hdb_question
SET config = jsonb_set(
    COALESCE(config::jsonb, '{}'::jsonb),
    '{validationPattern}',
    '"^[a-zA-Z\\s&.''-]+$"'::jsonb,
    true
)
WHERE binding_key = 'city' and created_by = -2;

UPDATE hdb_question
SET config = jsonb_set(
    COALESCE(config::jsonb, '{}'::jsonb),
    '{validationPattern}',
    $pattern$"^[0-9]+$"$pattern$::jsonb,
    true
)
WHERE binding_key = 'no_of_hdbs' and created_by = -2;

-- Verification query
SELECT binding_key, 
       config
FROM hdb_question 
WHERE binding_key = 'no_of_hdbs' and created_by = -2;

-- 2025-09-03
ALTER TABLE users 
ALTER COLUMN is_infipath_user_active SET DEFAULT true;
--
--
-- Need to run the user extension migrations
--
-- 

ALTER TABLE hdb_registration_travel_info 
ADD COLUMN id_back_picture_url VARCHAR(500) NULL;

ALTER TABLE hdb_registration_travel_info 
ADD COLUMN international_id_picture_url VARCHAR(500) NULL;

ALTER TABLE hdb_registration_travel_info 
ADD COLUMN passport_copy_picture_url VARCHAR(500) NULL;

ALTER TABLE hdb_registration_travel_info 
ADD COLUMN visa_copy_picture_url VARCHAR(500) NULL;

select * from hdb_question where binding_key = 'idBack' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_registration_travel_info.id_back_picture_url'
where binding_key = 'idBack' and created_by = -2;

select * from hdb_question where binding_key = 'internationalId' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_registration_travel_info.international_id_picture_url'
where binding_key = 'internationalId' and created_by = -2;

select * from hdb_question where binding_key = 'passport' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_registration_travel_info.passport_copy_picture_url'
where binding_key = 'passport' and created_by = -2;

select * from hdb_question where binding_key = 'visa' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_registration_travel_info.visa_copy_picture_url'
where binding_key = 'visa' and created_by = -2;

ALTER TABLE hdb_program_registration 
ADD COLUMN first_song_preference VARCHAR(255) NULL;

ALTER TABLE hdb_program_registration 
ADD COLUMN second_song_preference VARCHAR(255) NULL;

select * from hdb_question where binding_key = 'QK_HDB_SONG_1' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_program_registration.first_song_preference'
where binding_key = 'QK_HDB_SONG_1' and created_by = -2;

select * from hdb_question where binding_key = 'QK_HDB_SONG_2' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_program_registration.second_song_preference'
where binding_key = 'QK_HDB_SONG_2' and created_by = -2;

ALTER TABLE hdb_registration_travel_plan 
ADD COLUMN onward_additional_info TEXT NULL;

ALTER TABLE hdb_registration_travel_plan 
ADD COLUMN return_additional_info TEXT NULL;

select * from hdb_question where binding_key = 'onwardInfo' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_registration_travel_plan.onward_additional_info'
where binding_key = 'onwardInfo' and created_by = -2;

select * from hdb_question where binding_key = 'returnInfo' and created_by = -2; --1
update hdb_question 
set answer_location = 'hdb_registration_travel_plan.return_additional_info'
where binding_key = 'returnInfo' and created_by = -2;

--2025-09-03
-- Add follow_up_count column to hdb_program_registration_recommendations table
ALTER TABLE hdb_program_registration_recommendations 
ADD COLUMN IF NOT EXISTS follow_up_count INTEGER DEFAULT 0;

-- Add comment for the new column
COMMENT ON COLUMN hdb_program_registration_recommendations.follow_up_count IS 'Number of follow-ups made for this recommendation';

-- Create index for better query performance if needed
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_recommendations_follow_up_count 
ON hdb_program_registration_recommendations(follow_up_count);

-- 2025-09-01
select * from hdb_option where id in(
select option_id from hdb_question_option_map where question_id in (
select id from hdb_question where created_by = -2 and binding_key = 'gender'
));


UPDATE hdb_option
SET name = 'Male'
WHERE LOWER(name) = 'male'
  AND id IN (
    SELECT option_id 
    FROM hdb_question_option_map 
    WHERE question_id IN (
      SELECT id 
      FROM hdb_question 
      WHERE created_by = -2 
        AND binding_key = 'gender'
    )
  );

UPDATE hdb_option
SET name = 'Female'
WHERE LOWER(name) = 'female'
  AND id IN (
    SELECT option_id 
    FROM hdb_question_option_map 
    WHERE question_id IN (
      SELECT id 
      FROM hdb_question 
      WHERE created_by = -2 
        AND binding_key = 'gender'
    )
  );


SELECT id, gender
FROM hdb_program_registration
WHERE gender = 'male';



SELECT id, gender
FROM hdb_program_registration
WHERE gender ='female';


ALTER TYPE gender_enum RENAME VALUE 'male' TO 'Male';
ALTER TYPE gender_enum RENAME VALUE 'female' TO 'Female';
ALTER TYPE gender_enum RENAME VALUE 'other' TO 'Other';
ALTER TYPE gender_enum RENAME VALUE 'prefer_not_to_say' TO 'Prefer Not To Say';


SELECT id, gender
FROM hdb_program_registration
WHERE gender = 'Male';


SELECT id, gender
FROM hdb_program_registration
WHERE gender ='Female';

SELECT id, gender
FROM users
WHERE gender = 'male';

UPDATE users
SET gender = 'Male'
WHERE gender = 'male';

SELECT id, gender
FROM users
WHERE gender ='female';

UPDATE users
SET gender = 'Female'
WHERE gender = 'female';
select * from hdb_question where created_by = -2 and binding_key in ('city','countryName', 'invoiceName');

UPDATE hdb_question 
SET config = jsonb_set(
    config,
    '{prefill}',
    jsonb_build_object(
        'prefillIf', jsonb_build_array(
            jsonb_build_object(
                'value', 'INDIA',
                'operator', 'not_equals',
                'questionId', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'countryName'),
                'questionBindingKey', 'countryName'
            )
        ),
        'prefillValue', 'Other',
        'prefillType', 'static'
    )
)
WHERE created_by = -2 AND binding_key = 'city';


select * from hdb_question where created_by = -2 and binding_key in ('chequeNo','videoUrl','no_of_hdbs');

select * from hdb_question where created_by = -2 and binding_key in ('videoUrl');

UPDATE hdb_question 
SET config = jsonb_set(
    config,
    '{dependsOn}',
    jsonb_build_array(
        jsonb_build_object(
            'value', 0,
            'operator', 'equals',
            'questionId', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'no_of_hdbs'),
            'questionBindingKey', 'no_of_hdbs'
        )
    )
)
WHERE created_by = -2 and binding_key = 'videoUrl';



  
--2025-08-28

select * from hdb_question where created_by = -2 and binding_key IN 
('gstNumber','zip','isGstRegistered','proFormaIsGstRegistered','proFormaGstNumber','proFormaZip');

INSERT INTO hdb_question 
(label, config, stats, typ, created_at, updated_at, deleted_at, created_by, updated_by, form_section_id, answer_location, binding_key)
VALUES 
(
  'Registered under Indian GST (pro-forma)', 
  '{
    "maxValue": null,
    "minValue": null,
    "dependsOn": [],
    "isRequired": true,
    "showDialog": {
      "type": "popup",
      "isShow": "no",
      "imageName": null,
      "contentType": "text",
      "dialogueContent": "If you do not provide your GSTIN, this transaction will be filed under \"unregistered\" category in our GST return."
    },
    "minCharacter": null,
    "maxCharacters": null,
    "conditionalFields": [],
    "validationPattern": null
  }',
  'published',
  'radio',
  NOW(),
  NOW(),
  NULL,
  -2,
  -2,
  (SELECT id FROM hdb_form_section WHERE key = 'FS_BASICDETAILS'),
  'hdb_program_registration.pro_forma_is_gst_registered',
  'proFormaIsGstRegistered'
);

SELECT 
    q2.id AS proforma_question_id,
    q2.binding_key AS proforma_binding_key,
    qom.option_id,
    q1.id AS source_question_id,
    q1.binding_key AS source_binding_key
FROM hdb_question q1
JOIN hdb_question_option_map qom ON qom.question_id = q1.id
JOIN hdb_question q2 ON q2.binding_key = 'proFormaIsGstRegistered' AND q2.created_by = -2
WHERE q1.binding_key = 'isGstRegistered'
  AND q1.created_by = -2;


-- Insert the same options from isGstRegistered → proFormaIsGstRegistered
INSERT INTO hdb_question_option_map (question_id, option_id, created_by, updated_by, created_at, updated_at)
SELECT q2.id, qom.option_id, -2, -2, NOW(), NOW()
FROM hdb_question q1
JOIN hdb_question_option_map qom ON qom.question_id = q1.id
JOIN hdb_question q2 ON q2.binding_key = 'proFormaIsGstRegistered' AND q2.created_by = -2
WHERE q1.binding_key = 'isGstRegistered'
  AND q1.created_by = -2;

-- Pro-forma GST Number
INSERT INTO hdb_question 
(label, config, stats, typ, created_at, updated_at, deleted_at, created_by, updated_by, form_section_id, answer_location, binding_key)
VALUES 
(
  'GST No. (pro-forma)',
  json_build_object(
    'dependsOn', json_build_array(
      json_build_object(
        'value', 'yes',
        'questionId', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'proFormaIsGstRegistered'),
        'questionBindingKey', 'proFormaIsGstRegistered'
      )
    ),
    'isRequired', true
  )::jsonb,
  'published',
  'text',
  NOW(),
  NOW(),
  NULL,
  -2,
  -2,
  (SELECT id FROM hdb_form_section WHERE key = 'FS_BASICDETAILS'),
  'hdb_program_registration.pro_forma_gst_number',
  'proFormaGstNumber'
);

-- Pro-forma ZIP Code
INSERT INTO hdb_question 
(label, config, stats, typ, created_at, updated_at, deleted_at, created_by, updated_by, form_section_id, answer_location, binding_key)
VALUES 
(
  'PIN code (pro-forma)',
  json_build_object(
    'dependsOn', json_build_array(
      json_build_object(
        'value', 'yes',
        'questionId', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'proFormaIsGstRegistered'),
        'questionBindingKey', 'proFormaIsGstRegistered'
      )
    ),
    'isRequired', true
  )::jsonb,
  'published',
  'text',
  NOW(),
  NOW(),
  NULL,
  -2,
  -2,
  (SELECT id FROM hdb_form_section WHERE key = 'FS_BASICDETAILS'),
  'hdb_program_registration.pro_forma_zip',
  'proFormaZip'
);



ALTER TABLE hdb_program_registration
ADD COLUMN pro_forma_is_gst_registered BOOLEAN,
ADD COLUMN pro_forma_gst_number VARCHAR(50),
ADD COLUMN pro_forma_zip VARCHAR(10);

UPDATE hdb_question q
SET config = jsonb_set(
    q.config::jsonb,
    '{prefill}',
    jsonb_build_object(
        'prefillIf', jsonb_build_array(
            jsonb_build_object(
                'value', 'yes',
                'questionId', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'existingProformaInvoice'),
                'questionBindingKey', 'existingProformaInvoice'
            )
        ),
        'prefillFrom', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'proFormaGstNumber'),
        'prefillType', 'fieldDependent',
        'prefillFromBindingKey', 'proFormaGstNumber'
    )
)
WHERE q.binding_key = 'gstNumber'
  AND q.created_by = -2;

UPDATE hdb_question q
SET config = jsonb_set(
    q.config::jsonb,
    '{prefill}',
    jsonb_build_object(
        'prefillIf', jsonb_build_array(
            jsonb_build_object(
                'value', 'yes',
                'questionId', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'existingProformaInvoice'),
                'questionBindingKey', 'existingProformaInvoice'
            )
        ),
        'prefillFrom', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'proFormaZip'),
        'prefillType', 'fieldDependent',
        'prefillFromBindingKey', 'proFormaZip'
    )
)
WHERE q.binding_key = 'zip'
  AND q.created_by = -2;



UPDATE hdb_question q
SET config = jsonb_set(
    q.config::jsonb,
    '{prefill}',
    jsonb_build_object(
        'prefillIf', jsonb_build_array(
            jsonb_build_object(
                'value', 'yes',
                'questionId', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'existingProformaInvoice'),
                'questionBindingKey', 'existingProformaInvoice'
            )
        ),
        'prefillFrom', (SELECT id FROM hdb_question WHERE created_by = -2 AND binding_key = 'proFormaIsGstRegistered'),
        'prefillType', 'fieldDependent',
        'prefillFromBindingKey', 'proFormaIsGstRegistered'
    )
)
WHERE q.binding_key = 'isGstRegistered'
  AND q.created_by = -2;

select * from hdb_question where created_by = -2 and binding_key IN 
('gstNumber','zip','isGstRegistered', 'invoiceEmail','invoiceName', 'existingProformaInvoice', 'pro_forma_invoice_name', 'proFormaBillingAddress','proFormaIsGstRegistered','proFormaGstNumber','proFormaZip');

--2025-08-25
INSERT INTO public.lookup_data(lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order) VALUES 
('CITY_NAME', 'City', 'Ajmer', 'Ajmer', 'active', 26),
('CITY_NAME', 'City', 'Aligarh', 'Aligarh', 'active', 27),
('CITY_NAME', 'City', 'Allahabad', 'Allahabad', 'active', 28),
('CITY_NAME', 'City', 'Ambattur', 'Ambattur', 'active', 29),
('CITY_NAME', 'City', 'Amravati', 'Amravati', 'active', 30),
('CITY_NAME', 'City', 'Asansol', 'Asansol', 'active', 31),
('CITY_NAME', 'City', 'Aurangabad', 'Aurangabad', 'active', 32),
('CITY_NAME', 'City', 'Bareilly', 'Bareilly', 'active', 33),
('CITY_NAME', 'City', 'Belgaum', 'Belgaum', 'active', 34),
('CITY_NAME', 'City', 'Bhavnagar', 'Bhavnagar', 'active', 35),
('CITY_NAME', 'City', 'Bhilai Nagar', 'Bhilai Nagar', 'active', 36),
('CITY_NAME', 'City', 'Bhiwandi', 'Bhiwandi', 'active', 37),
('CITY_NAME', 'City', 'Bhubaneswar', 'Bhubaneswar', 'active', 38),
('CITY_NAME', 'City', 'Bikaner', 'Bikaner', 'active', 39),
('CITY_NAME', 'City', 'Chandigarh', 'Chandigarh', 'active', 40),
('CITY_NAME', 'City', 'Cuttack', 'Cuttack', 'active', 41),
('CITY_NAME', 'City', 'Dehradun', 'Dehradun', 'active', 42),
('CITY_NAME', 'City', 'Dhanbad', 'Dhanbad', 'active', 43),
('CITY_NAME', 'City', 'Durgapur', 'Durgapur', 'active', 44),
('CITY_NAME', 'City', 'Faridabad', 'Faridabad', 'active', 45),
('CITY_NAME', 'City', 'Firozabad', 'Firozabad', 'active', 46),
('CITY_NAME', 'City', 'Gaya', 'Gaya', 'active', 47),
('CITY_NAME', 'City', 'Ghaziabad', 'Ghaziabad', 'active', 48),
('CITY_NAME', 'City', 'Gorakhpur', 'Gorakhpur', 'active', 49),
('CITY_NAME', 'City', 'Gulbarga', 'Gulbarga', 'active', 50),
('CITY_NAME', 'City', 'Guntur', 'Guntur', 'active', 51),
('CITY_NAME', 'City', 'Gurgaon', 'Gurgaon', 'active', 52),
('CITY_NAME', 'City', 'Gwalior', 'Gwalior', 'active', 53),
('CITY_NAME', 'City', 'Haora', 'Haora', 'active', 54),
('CITY_NAME', 'City', 'Hubli And Dharwad', 'Hubli And Dharwad', 'active', 55),
('CITY_NAME', 'City', 'Jabalpur', 'Jabalpur', 'active', 56),
('CITY_NAME', 'City', 'Jalandhar', 'Jalandhar', 'active', 57),
('CITY_NAME', 'City', 'Jalgaon', 'Jalgaon', 'active', 58),
('CITY_NAME', 'City', 'Jammu', 'Jammu', 'active', 59),
('CITY_NAME', 'City', 'Jamnagar', 'Jamnagar', 'active', 60),
('CITY_NAME', 'City', 'Jamshedpur', 'Jamshedpur', 'active', 61),
('CITY_NAME', 'City', 'Jhansi', 'Jhansi', 'active', 62),
('CITY_NAME', 'City', 'Jodhpur', 'Jodhpur', 'active', 63),
('CITY_NAME', 'City', 'Kalyan & Dombivali', 'Kalyan & Dombivali', 'active', 64),
('CITY_NAME', 'City', 'Kochi', 'Kochi', 'active', 65),
('CITY_NAME', 'City', 'Kolapur', 'Kolapur', 'active', 66),
('CITY_NAME', 'City', 'Kota', 'Kota', 'active', 67),
('CITY_NAME', 'City', 'Loni', 'Loni', 'active', 68),
('CITY_NAME', 'City', 'Madurai', 'Madurai', 'active', 69),
('CITY_NAME', 'City', 'Maheshtala', 'Maheshtala', 'active', 70),
('CITY_NAME', 'City', 'Malegaon', 'Malegaon', 'active', 71),
('CITY_NAME', 'City', 'Mangalore', 'Mangalore', 'active', 72),
('CITY_NAME', 'City', 'Meerut', 'Meerut', 'active', 73),
('CITY_NAME', 'City', 'Mira And Bhayander', 'Mira And Bhayander', 'active', 74),
('CITY_NAME', 'City', 'Moradabad', 'Moradabad', 'active', 75),
('CITY_NAME', 'City', 'Nanded Waghala', 'Nanded Waghala', 'active', 76),
('CITY_NAME', 'City', 'Nashik', 'Nashik', 'active', 77),
('CITY_NAME', 'City', 'Navi Mumbai', 'Navi Mumbai', 'active', 78),
('CITY_NAME', 'City', 'Nellore', 'Nellore', 'active', 79),
('CITY_NAME', 'City', 'Noida', 'Noida', 'active', 80),
('CITY_NAME', 'City', 'Pimpri & Chinchwad', 'Pimpri & Chinchwad', 'active', 81),
('CITY_NAME', 'City', 'Raipur', 'Raipur', 'active', 82),
('CITY_NAME', 'City', 'Rajkot', 'Rajkot', 'active', 83),
('CITY_NAME', 'City', 'Saharanpur', 'Saharanpur', 'active', 84),
('CITY_NAME', 'City', 'Salem', 'Salem', 'active', 85),
('CITY_NAME', 'City', 'Sangli Miraj Kupwad', 'Sangli Miraj Kupwad', 'active', 86),
('CITY_NAME', 'City', 'Siliguri', 'Siliguri', 'active', 87),
('CITY_NAME', 'City', 'Solapur', 'Solapur', 'active', 88),
('CITY_NAME', 'City', 'Srinagar', 'Srinagar', 'active', 89),
('CITY_NAME', 'City', 'Thane', 'Thane', 'active', 90),
('CITY_NAME', 'City', 'Tiruchirappalli', 'Tiruchirappalli', 'active', 91),
('CITY_NAME', 'City', 'Tirunelveli', 'Tirunelveli', 'active', 92),
('CITY_NAME', 'City', 'Udaipur', 'Udaipur', 'active', 93),
('CITY_NAME', 'City', 'Ujjain', 'Ujjain', 'active', 94),
('CITY_NAME', 'City', 'Ulhasnagar', 'Ulhasnagar', 'active', 95),
('CITY_NAME', 'City', 'Vasai Virar', 'Vasai Virar', 'active', 96),
('CITY_NAME', 'City', 'Vijayawada', 'Vijayawada', 'active', 97),
('CITY_NAME', 'City', 'Visakhapatnam', 'Visakhapatnam', 'active', 98),
('CITY_NAME', 'City', 'Warangal', 'Warangal', 'active', 99);



WITH ordered AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY lookup_category 
            ORDER BY CASE WHEN lookup_label = 'Other' THEN 1 ELSE 0 END ASC, lookup_label ASC
        ) AS temp_order
    FROM lookup_data
)
UPDATE lookup_data ld
SET lookup_order = o.temp_order
FROM ordered o
WHERE ld.id = o.id;

-- Step 2: Push 'Other' to the last position
WITH max_order AS (
    SELECT lookup_category, MAX(lookup_order) AS max_order
    FROM lookup_data
    WHERE lookup_label <> 'Other'
    GROUP BY lookup_category
)
UPDATE lookup_data ld
SET lookup_order = mo.max_order + 1
FROM max_order mo
WHERE ld.lookup_category = mo.lookup_category
  AND ld.lookup_label = 'Other';



UPDATE lookup_data
SET lookup_order = CASE lookup_key
    WHEN 'XS' THEN 1
    WHEN 'S' THEN 2
    WHEN 'M' THEN 3
    WHEN 'L' THEN 4
    WHEN 'XL' THEN 5
    WHEN 'XXL' THEN 6
    WHEN 'XXXL' THEN 7
    WHEN 'XXXXL' THEN 8
    ELSE lookup_order
END
WHERE lookup_category = 'TSHIRT_SIZE';

SELECT setval('lookup_data_id_seq', (SELECT COALESCE(MAX(id), 0) + 2 FROM lookup_data));

select * from public.lookup_data where lookup_category = 'CITY_NAME' order by lookup_order asc ;



-- Step 1: Soft delete existing options
UPDATE public.hdb_option
SET deleted_at = NOW()
WHERE id IN (
    SELECT option_id
    FROM public.hdb_question_option_map
    WHERE question_id IN (
        SELECT id
        FROM public.hdb_question
        WHERE created_by = -2
          AND binding_key = 'paymentMode'
    )
);

-- Step 2: Soft delete existing mappings (to avoid conflicts)
UPDATE public.hdb_question_option_map
SET deleted_at = NOW()
WHERE question_id IN (
    SELECT id
    FROM public.hdb_question
    WHERE created_by = -2
      AND binding_key = 'paymentMode'
);

-- Step 3: Insert fresh options in required order
INSERT INTO public.hdb_option (typ, stats, created_at, updated_at, created_by, updated_by, name)
VALUES 
('string', 'published', NOW(), NOW(), -2, -2, 'Online (Through Razorpay)'),
('string', 'published', NOW(), NOW(), -2, -2, 'Direct bank transfer (NEFT/RTGS)'),
('string', 'published', NOW(), NOW(), -2, -2, 'Cheque'),
('string', 'published', NOW(), NOW(), -2, -2, 'Cash');

-- Step 4: Insert new mappings (link new options with the paymentMode question)
INSERT INTO public.hdb_question_option_map (question_id, option_id, created_at, updated_at, created_by, updated_by)
SELECT q.id, o.id, NOW(), NOW(), -2, -2
FROM public.hdb_question q
JOIN public.hdb_option o ON o.name = 'Online (Through Razorpay)' AND o.deleted_at IS NULL AND o.created_by = -2
WHERE q.created_by = -2 AND q.binding_key = 'paymentMode';

INSERT INTO public.hdb_question_option_map (question_id, option_id, created_at, updated_at, created_by, updated_by)
SELECT q.id, o.id, NOW(), NOW(), -2, -2
FROM public.hdb_question q
JOIN public.hdb_option o ON o.name = 'Direct bank transfer (NEFT/RTGS)' AND o.deleted_at IS NULL AND o.created_by = -2
WHERE q.created_by = -2 AND q.binding_key = 'paymentMode';

INSERT INTO public.hdb_question_option_map (question_id, option_id, created_at, updated_at, created_by, updated_by)
SELECT q.id, o.id, NOW(), NOW(), -2, -2
FROM public.hdb_question q
JOIN public.hdb_option o ON o.name = 'Cheque' AND o.deleted_at IS NULL AND o.created_by = -2
WHERE q.created_by = -2 AND q.binding_key = 'paymentMode';

INSERT INTO public.hdb_question_option_map (question_id, option_id, created_at, updated_at, created_by, updated_by)
SELECT q.id, o.id, NOW(), NOW(), -2, -2
FROM public.hdb_question q
JOIN public.hdb_option o ON o.name = 'Cash' AND o.deleted_at IS NULL AND o.created_by = -2
WHERE q.created_by = -2 AND q.binding_key = 'paymentMode';


select * from public.hdb_question where created_by = -2 and binding_key in ('invoiceName','pro_forma_invoice_name');


UPDATE public.hdb_question
SET config = config - 'validationPattern'
WHERE created_by = -2
  AND binding_key IN ('invoiceName', 'pro_forma_invoice_name');


ALTER TABLE public.hdb_program_registration
ADD COLUMN last_hdb_attended VARCHAR(255),
ADD COLUMN hdb_association_since VARCHAR(255),
ADD COLUMN other_infinitheism_contact VARCHAR(255),
ADD COLUMN other_city_name VARCHAR(255);

UPDATE public.hdb_question
SET answer_location = 'hdb_program_registration.last_hdb_attended'
WHERE created_by = -2 AND binding_key = 'QK_LAST_HDB';

UPDATE public.hdb_question
SET answer_location = 'hdb_program_registration.hdb_association_since'
WHERE created_by = -2 AND binding_key = 'QK_HDB_ASSOCIATION_SINCE';

UPDATE public.hdb_question
SET answer_location = 'hdb_program_registration.other_infinitheism_contact'
WHERE created_by = -2 AND binding_key = 'otherInfinitheismContact';

UPDATE public.hdb_question
SET answer_location = 'hdb_program_registration.other_city_name'
WHERE created_by = -2 AND binding_key = 'otherCityName';



-- 2025-08-22
CREATE TABLE hdb_registration_invoice_basic_history (
  id SERIAL PRIMARY KEY,
  invoice_id bigint,
  invoice_type invoice_type_enum,
  invoice_name varchar(255),
  invoice_email varchar(255),
  invoice_address text,
  pan_number varchar(20),
  tds_amount numeric(10,2),
  tan_number varchar(20),
  zip varchar(10),
  gst_number varchar(50),
  created_at timestamp,
  updated_at timestamp,
  created_by bigint,
  updated_by bigint,

  CONSTRAINT fk_invoice FOREIGN KEY (invoice_id) REFERENCES hdb_registration_invoice_detail(id),
  CONSTRAINT fk_history_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_history_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE hdb_registration_invoice_einvoice_history (
  id SERIAL PRIMARY KEY,
  invoice_id bigint,
  invoice_type invoice_type_enum,
  zip varchar(10),
  gst_number varchar(50),
  pan_number varchar(20),
  invoice_sequence_number varchar(50),
  invoice_issued_date timestamp,
  einvoice_qr_link varchar(1024),
  einvoice_ack_date varchar(500),
  einvoice_inv_ref_num varchar(1024),
  einvoice_is_cancellable boolean,
  einvoice_status_formatted varchar(1024),
  einvoice_formatted_status varchar(500),
  einvoice_ack_number varchar(500),
  einvoice_status varchar(500),
  einvoice_pdf_url varchar(2048),
  created_at timestamp,
  updated_at timestamp,
  created_by bigint,
  updated_by bigint,
  CONSTRAINT fk_invoice FOREIGN KEY (invoice_id) REFERENCES hdb_registration_invoice_detail(id),
  CONSTRAINT fk_einvoice_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_einvoice_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

select * from public.hdb_form_section where key = 'FS_MAHATRIAQUESTIONS';

update hdb_form_section
set name = 'Questions and Answers'
where key = 'FS_MAHATRIAQUESTIONS';


select id, label, form_section_id from public.hdb_question where created_by = -2 and binding_key = 'videoUrl';


select id,key from public.hdb_form_section where key = 'FS_MAHATRIAQUESTIONS';

UPDATE public.hdb_question q
SET form_section_id = (
    SELECT id
    FROM public.hdb_form_section
    WHERE key = 'FS_MAHATRIAQUESTIONS'
)
WHERE created_by = -2
  AND binding_key = 'videoUrl';

select id, label, form_section_id from public.hdb_question where created_by = -2 and binding_key = 'videoUrl';


select id,key from public.hdb_form_section where key = 'FS_MAHATRIAQUESTIONS';

UPDATE public.hdb_question q
SET form_section_id = (
    SELECT id
    FROM public.hdb_form_section
    WHERE key = 'FS_MAHATRIAQUESTIONS'
)
WHERE created_by = -2
  AND binding_key = 'videoUrl';

-- 2025-08-21
ALTER TABLE hdb_program_registration 
ADD COLUMN country_name VARCHAR(255);

-- 2025-08-21
select id, lookup_category, lookup_key, lookup_label from lookup_data where lookup_category in ('ID_TYPE','CITY_NAME','BANK_NAME');--64
UPDATE lookup_data
SET lookup_key = lookup_label
WHERE lookup_category IN ('ID_TYPE', 'CITY_NAME', 'BANK_NAME');

select id, lookup_category, lookup_key, lookup_label from lookup_data where lookup_category in ('ID_TYPE','CITY_NAME','BANK_NAME');

INSERT INTO public.lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status) VALUES
('CITY_NAME', 'City', 'Chennai', 'Chennai', 'active');

update hdb_question_option_map
set deleted_at = now()
where question_id in (
  select id 
  from hdb_question 
  where created_by = -2 
    and binding_key in (
      'rmContact','handOverTo','travelInfoType',
      'countryName','city','bankName',
      'goodiesTshirtSize','goodiesJacketSize'
    )
);

-- 2025-08-20

-- ENUM for request status
CREATE TYPE request_status_enum AS ENUM ('requested', 'closed', 'completed');

-- Table for hdb_payment_change_request
CREATE TABLE hdb_payment_change_request (
    id SERIAL PRIMARY KEY,
    payment_id BIGINT NOT NULL,
    request_status request_status_enum NOT NULL,
    requested_by BIGINT NOT NULL,
    updated_by BIGINT,
    completed_by BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,

    CONSTRAINT fk_payment FOREIGN KEY (payment_id) REFERENCES hdb_registration_payment_detail(id),
    CONSTRAINT fk_requested_by FOREIGN KEY (requested_by) REFERENCES users(id),
    CONSTRAINT fk_updated_by FOREIGN KEY (updated_by) REFERENCES users(id),
    CONSTRAINT fk_completed_by FOREIGN KEY (completed_by) REFERENCES users(id)
);



update hdb_question
set label = 'HDB/MSD Preference'
where created_by = -2 and binding_key = 'preference';

select * from lookup_data;

ALTER TABLE public.lookup_data 
ADD CONSTRAINT lookup_data_category_key_unique UNIQUE (lookup_category, lookup_key);


INSERT INTO public.lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status) VALUES
('COUNTRY', 'Country', 'AFGHANISTAN', 'AFGHANISTAN', 'active'),
('COUNTRY', 'Country', 'ALBANIA', 'ALBANIA', 'active'),
('COUNTRY', 'Country', 'ALGERIA', 'ALGERIA', 'active'),
('COUNTRY', 'Country', 'AMERICAN_SAMOA', 'AMERICAN SAMOA', 'active'),
('COUNTRY', 'Country', 'ANDORRA', 'ANDORRA', 'active'),
('COUNTRY', 'Country', 'ANGOLA', 'ANGOLA', 'active'),
('COUNTRY', 'Country', 'ARGENTINA', 'ARGENTINA', 'active'),
('COUNTRY', 'Country', 'ARMENIA', 'ARMENIA', 'active'),
('COUNTRY', 'Country', 'AUSTRALIA', 'AUSTRALIA', 'active'),
('COUNTRY', 'Country', 'AUSTRIA', 'AUSTRIA', 'active'),
('COUNTRY', 'Country', 'AZERBAIJAN', 'AZERBAIJAN', 'active'),
('COUNTRY', 'Country', 'BAHAMAS', 'BAHAMAS', 'active'),
('COUNTRY', 'Country', 'BAHRAIN', 'BAHRAIN', 'active'),
('COUNTRY', 'Country', 'BANGLADESH', 'BANGLADESH', 'active'),
('COUNTRY', 'Country', 'BARBADOS', 'BARBADOS', 'active'),
('COUNTRY', 'Country', 'BELARUS', 'BELARUS', 'active'),
('COUNTRY', 'Country', 'BELGIUM', 'BELGIUM', 'active'),
('COUNTRY', 'Country', 'BELIZE', 'BELIZE', 'active'),
('COUNTRY', 'Country', 'BENIN', 'BENIN', 'active'),
('COUNTRY', 'Country', 'BERMUDA', 'BERMUDA', 'active'),
('COUNTRY', 'Country', 'BHUTAN', 'BHUTAN', 'active'),
('COUNTRY', 'Country', 'BOLIVIA', 'BOLIVIA', 'active'),
('COUNTRY', 'Country', 'BOSNIA_AND_HERZEGOVINA', 'BOSNIA AND HERZEGOVINA', 'active'),
('COUNTRY', 'Country', 'BOTSWANA', 'BOTSWANA', 'active'),
('COUNTRY', 'Country', 'BRAZIL', 'BRAZIL', 'active'),
('COUNTRY', 'Country', 'BULGARIA', 'BULGARIA', 'active'),
('COUNTRY', 'Country', 'BURKINA_FASO', 'BURKINA FASO', 'active'),
('COUNTRY', 'Country', 'BURUNDI', 'BURUNDI', 'active'),
('COUNTRY', 'Country', 'CAMBODIA', 'CAMBODIA', 'active'),
('COUNTRY', 'Country', 'CAMEROON', 'CAMEROON', 'active'),
('COUNTRY', 'Country', 'CANADA', 'CANADA', 'active'),
('COUNTRY', 'Country', 'CAPE_VERDE_ISLANDS', 'CAPE VERDE ISLANDS', 'active'),
('COUNTRY', 'Country', 'CHAD', 'CHAD', 'active'),
('COUNTRY', 'Country', 'CHILE', 'CHILE', 'active'),
('COUNTRY', 'Country', 'CHINA', 'CHINA', 'active'),
('COUNTRY', 'Country', 'COLOMBIA', 'COLOMBIA', 'active'),
('COUNTRY', 'Country', 'CONGO', 'CONGO', 'active'),
('COUNTRY', 'Country', 'COSTA_RICA', 'COSTA RICA', 'active'),
('COUNTRY', 'Country', 'CROATIA', 'CROATIA', 'active'),
('COUNTRY', 'Country', 'CUBA', 'CUBA', 'active'),
('COUNTRY', 'Country', 'CYPRUS', 'CYPRUS', 'active'),
('COUNTRY', 'Country', 'CZECH_REPUBLIC', 'CZECH REPUBLIC', 'active'),
('COUNTRY', 'Country', 'DENMARK', 'DENMARK', 'active'),
('COUNTRY', 'Country', 'DJIBOUTI', 'DJIBOUTI', 'active'),
('COUNTRY', 'Country', 'DOMINICA', 'DOMINICA', 'active'),
('COUNTRY', 'Country', 'DOMINICAN_REPUBLIC', 'DOMINICAN REPUBLIC', 'active'),
('COUNTRY', 'Country', 'ECUADOR', 'ECUADOR', 'active'),
('COUNTRY', 'Country', 'EGYPT', 'EGYPT', 'active'),
('COUNTRY', 'Country', 'EL_SALVADOR', 'EL SALVADOR', 'active'),
('COUNTRY', 'Country', 'ERITREA', 'ERITREA', 'active'),
('COUNTRY', 'Country', 'ESTONIA', 'ESTONIA', 'active'),
('COUNTRY', 'Country', 'ETHIOPIA', 'ETHIOPIA', 'active'),
('COUNTRY', 'Country', 'FIJI', 'FIJI', 'active'),
('COUNTRY', 'Country', 'FINLAND', 'FINLAND', 'active'),
('COUNTRY', 'Country', 'FRANCE', 'FRANCE', 'active'),
('COUNTRY', 'Country', 'FRENCH_POLYNESIA', 'FRENCH POLYNESIA', 'active'),
('COUNTRY', 'Country', 'GABON', 'GABON', 'active'),
('COUNTRY', 'Country', 'GAMBIA', 'GAMBIA', 'active'),
('COUNTRY', 'Country', 'GEORGIA', 'GEORGIA', 'active'),
('COUNTRY', 'Country', 'GERMANY', 'GERMANY', 'active'),
('COUNTRY', 'Country', 'GHANA', 'GHANA', 'active'),
('COUNTRY', 'Country', 'GREECE', 'GREECE', 'active'),
('COUNTRY', 'Country', 'GRENADA', 'GRENADA', 'active'),
('COUNTRY', 'Country', 'GUATEMALA', 'GUATEMALA', 'active'),
('COUNTRY', 'Country', 'GUINEA', 'GUINEA', 'active'),
('COUNTRY', 'Country', 'GUYANA', 'GUYANA', 'active'),
('COUNTRY', 'Country', 'HAITI', 'HAITI', 'active'),
('COUNTRY', 'Country', 'HONDURAS', 'HONDURAS', 'active'),
('COUNTRY', 'Country', 'HUNGARY', 'HUNGARY', 'active'),
('COUNTRY', 'Country', 'ICELAND', 'ICELAND', 'active'),
('COUNTRY', 'Country', 'INDIA', 'INDIA', 'active'),
('COUNTRY', 'Country', 'INDONESIA', 'INDONESIA', 'active'),
('COUNTRY', 'Country', 'IRAN', 'IRAN', 'active'),
('COUNTRY', 'Country', 'IRAQ', 'IRAQ', 'active'),
('COUNTRY', 'Country', 'IRELAND', 'IRELAND', 'active'),
('COUNTRY', 'Country', 'ISRAEL', 'ISRAEL', 'active'),
('COUNTRY', 'Country', 'ITALY', 'ITALY', 'active'),
('COUNTRY', 'Country', 'JAMAICA', 'JAMAICA', 'active'),
('COUNTRY', 'Country', 'JAPAN', 'JAPAN', 'active'),
('COUNTRY', 'Country', 'JORDAN', 'JORDAN', 'active'),
('COUNTRY', 'Country', 'KAZAKHSTAN', 'KAZAKHSTAN', 'active'),
('COUNTRY', 'Country', 'KENYA', 'KENYA', 'active'),
('COUNTRY', 'Country', 'KOREA', 'KOREA', 'active'),
('COUNTRY', 'Country', 'KUWAIT', 'KUWAIT', 'active'),
('COUNTRY', 'Country', 'LATVIA', 'LATVIA', 'active'),
('COUNTRY', 'Country', 'LEBANON', 'LEBANON', 'active'),
('COUNTRY', 'Country', 'LIBERIA', 'LIBERIA', 'active'),
('COUNTRY', 'Country', 'LIBYA', 'LIBYA', 'active'),
('COUNTRY', 'Country', 'LITHUANIA', 'LITHUANIA', 'active'),
('COUNTRY', 'Country', 'LUXEMBOURG', 'LUXEMBOURG', 'active'),
('COUNTRY', 'Country', 'MADAGASCAR', 'MADAGASCAR', 'active'),
('COUNTRY', 'Country', 'MALAWI', 'MALAWI', 'active'),
('COUNTRY', 'Country', 'MALAYSIA', 'MALAYSIA', 'active'),
('COUNTRY', 'Country', 'MALDIVES', 'MALDIVES', 'active'),
('COUNTRY', 'Country', 'MALI', 'MALI', 'active'),
('COUNTRY', 'Country', 'MALTA', 'MALTA', 'active'),
('COUNTRY', 'Country', 'MAURITANIA', 'MAURITANIA', 'active'),
('COUNTRY', 'Country', 'MAURITIUS', 'MAURITIUS', 'active'),
('COUNTRY', 'Country', 'MEXICO', 'MEXICO', 'active'),
('COUNTRY', 'Country', 'MONACO', 'MONACO', 'active'),
('COUNTRY', 'Country', 'MONGOLIA', 'MONGOLIA', 'active'),
('COUNTRY', 'Country', 'MONTENEGRO', 'MONTENEGRO', 'active'),
('COUNTRY', 'Country', 'MOROCCO', 'MOROCCO', 'active'),
('COUNTRY', 'Country', 'MOZAMBIQUE', 'MOZAMBIQUE', 'active'),
('COUNTRY', 'Country', 'NAMIBIA', 'NAMIBIA', 'active'),
('COUNTRY', 'Country', 'NEPAL', 'NEPAL', 'active'),
('COUNTRY', 'Country', 'NETHERLANDS', 'NETHERLANDS', 'active'),
('COUNTRY', 'Country', 'NEW_ZEALAND', 'NEW ZEALAND', 'active'),
('COUNTRY', 'Country', 'NICARAGUA', 'NICARAGUA', 'active'),
('COUNTRY', 'Country', 'NIGER', 'NIGER', 'active'),
('COUNTRY', 'Country', 'NIGERIA', 'NIGERIA', 'active'),
('COUNTRY', 'Country', 'NORWAY', 'NORWAY', 'active'),
('COUNTRY', 'Country', 'OMAN', 'OMAN', 'active'),
('COUNTRY', 'Country', 'PAKISTAN', 'PAKISTAN', 'active'),
('COUNTRY', 'Country', 'PANAMA', 'PANAMA', 'active'),
('COUNTRY', 'Country', 'PAPUA_NEW_GUINEA', 'PAPUA NEW GUINEA', 'active'),
('COUNTRY', 'Country', 'PARAGUAY', 'PARAGUAY', 'active'),
('COUNTRY', 'Country', 'PERU', 'PERU', 'active'),
('COUNTRY', 'Country', 'PHILIPPINES', 'PHILIPPINES', 'active'),
('COUNTRY', 'Country', 'POLAND', 'POLAND', 'active'),
('COUNTRY', 'Country', 'PORTUGAL', 'PORTUGAL', 'active'),
('COUNTRY', 'Country', 'QATAR', 'QATAR', 'active'),
('COUNTRY', 'Country', 'ROMANIA', 'ROMANIA', 'active'),
('COUNTRY', 'Country', 'RWANDA', 'RWANDA', 'active'),
('COUNTRY', 'Country', 'SAUDI_ARABIA', 'SAUDI ARABIA', 'active'),
('COUNTRY', 'Country', 'SENEGAL', 'SENEGAL', 'active'),
('COUNTRY', 'Country', 'SERBIA', 'SERBIA', 'active'),
('COUNTRY', 'Country', 'SIERRA_LEONE', 'SIERRA LEONE', 'active'),
('COUNTRY', 'Country', 'SINGAPORE', 'SINGAPORE', 'active'),
('COUNTRY', 'Country', 'SLOVAKIA', 'SLOVAKIA', 'active'),
('COUNTRY', 'Country', 'SLOVENIA', 'SLOVENIA', 'active'),
('COUNTRY', 'Country', 'SOLOMON_ISLANDS', 'SOLOMON ISLANDS', 'active'),
('COUNTRY', 'Country', 'SOMALIA', 'SOMALIA', 'active'),
('COUNTRY', 'Country', 'SOUTH_AFRICA', 'SOUTH AFRICA', 'active'),
('COUNTRY', 'Country', 'SPAIN', 'SPAIN', 'active'),
('COUNTRY', 'Country', 'SRI_LANKA', 'SRI LANKA', 'active'),
('COUNTRY', 'Country', 'SUDAN', 'SUDAN', 'active'),
('COUNTRY', 'Country', 'SURINAME', 'SURINAME', 'active'),
('COUNTRY', 'Country', 'SWAZILAND', 'SWAZILAND', 'active'),
('COUNTRY', 'Country', 'SWEDEN', 'SWEDEN', 'active'),
('COUNTRY', 'Country', 'SWITZERLAND', 'SWITZERLAND', 'active'),
('COUNTRY', 'Country', 'TAIWAN', 'TAIWAN', 'active'),
('COUNTRY', 'Country', 'TAJIKISTAN', 'TAJIKISTAN', 'active'),
('COUNTRY', 'Country', 'THAILAND', 'THAILAND', 'active'),
('COUNTRY', 'Country', 'TOGO', 'TOGO', 'active'),
('COUNTRY', 'Country', 'TRINIDAD_AND_TOBAGO', 'TRINIDAD AND TOBAGO', 'active'),
('COUNTRY', 'Country', 'TUNISIA', 'TUNISIA', 'active'),
('COUNTRY', 'Country', 'TURKEY', 'TURKEY', 'active'),
('COUNTRY', 'Country', 'TURKMENISTAN', 'TURKMENISTAN', 'active'),
('COUNTRY', 'Country', 'TUVALU', 'TUVALU', 'active'),
('COUNTRY', 'Country', 'UGANDA', 'UGANDA', 'active'),
('COUNTRY', 'Country', 'UKRAINE', 'UKRAINE', 'active'),
('COUNTRY', 'Country', 'UNITED_ARAB_EMIRATES', 'UNITED ARAB EMIRATES', 'active'),
('COUNTRY', 'Country', 'UNITED_KINGDOM', 'UNITED KINGDOM', 'active'),
('COUNTRY', 'Country', 'UNITED_STATES', 'UNITED STATES', 'active'),
('COUNTRY', 'Country', 'URUGUAY', 'URUGUAY', 'active'),
('COUNTRY', 'Country', 'UZBEKISTAN', 'UZBEKISTAN', 'active'),
('COUNTRY', 'Country', 'VANUATU', 'VANUATU', 'active'),
('COUNTRY', 'Country', 'VENEZUELA', 'VENEZUELA', 'active'),
('COUNTRY', 'Country', 'VIET_NAM', 'VIET NAM', 'active'),
('COUNTRY', 'Country', 'YEMEN', 'YEMEN', 'active'),
('COUNTRY', 'Country', 'ZAMBIA', 'ZAMBIA', 'active');


ALTER TABLE public.lookup_data
  ADD COLUMN IF NOT EXISTS lookup_order INTEGER DEFAULT 0;

UPDATE public.lookup_data
SET lookup_order = 0
WHERE lookup_order IS NULL;

ALTER TABLE public.lookup_data
  ALTER COLUMN lookup_order SET NOT NULL;

-- Update lookup_order for all unique lookup_category values based on alphabetical order of lookup_label
-- Step 1: Recalculate lookup_order for each category alphabetically excluding 'OTHER'
WITH ordered AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY lookup_category 
            ORDER BY CASE WHEN lookup_label = 'Other' THEN 1 ELSE 0 END ASC, lookup_label ASC
        ) AS temp_order
    FROM lookup_data
)
UPDATE lookup_data ld
SET lookup_order = o.temp_order
FROM ordered o
WHERE ld.id = o.id;

-- Step 2: Push 'Other' to the last position
WITH max_order AS (
    SELECT lookup_category, MAX(lookup_order) AS max_order
    FROM lookup_data
    WHERE lookup_label <> 'Other'
    GROUP BY lookup_category
)
UPDATE lookup_data ld
SET lookup_order = mo.max_order + 1
FROM max_order mo
WHERE ld.lookup_category = mo.lookup_category
  AND ld.lookup_label = 'Other';


UPDATE lookup_data
SET lookup_status = 'inactive'
WHERE lookup_category IN ('PAYMENT_MODE', 'GENDER', 'TRAVEL_PLAN');


INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label, lookup_status, lookup_order)
VALUES ('TSHIRT_SIZE', 'Tshirt size', 'XXXXL', 'XXXXL', 'active', 8);


UPDATE lookup_data
SET lookup_order = CASE lookup_key
    WHEN 'XS' THEN 1
    WHEN 'S' THEN 2
    WHEN 'M' THEN 3
    WHEN 'L' THEN 4
    WHEN 'XL' THEN 5
    WHEN 'XXL' THEN 6
    WHEN 'XXXL' THEN 7
    WHEN 'XXXXL' THEN 8
    ELSE lookup_order
END
WHERE lookup_category = 'TSHIRT_SIZE';

INSERT INTO public.hdb_question (
  label, 
  config, 
  stats, 
  typ, 
  created_at, 
  updated_at, 
  created_by, 
  updated_by,
  form_section_id,
  answer_location,
  binding_key
) VALUES (
  'Country',
  '{
    "type": "select",
    "apiUrl": "lookup-data/all",
    "category": "COUNTRY", 
    "isRequired": true
  }'::jsonb,
  'published',
  'apicall',
  NOW(),
  NOW(),
  -2,
  -2,
  1,
 'hdb_program_registration.country_name',
 'countryName'
);


UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'COUNTRY',
        'isRequired', true
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'countryName';

-- Jacket size
UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'TSHIRT_SIZE',
        'isRequired', false
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'goodiesJacketSize';

-- T-shirt size
UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'TSHIRT_SIZE',
        'isRequired', false
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'goodiesTshirtSize';

-- Bank name
UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'BANK_NAME',
        'isRequired', true
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'bankName';

-- City
UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'CITY_NAME',
        'isRequired', true
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'city';

-- ID type
UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'ID_TYPE',
        'isRequired', true
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'travelInfoType';

-- Handover to
UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'RM',
        'isRequired', true
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'handOverTo';

-- Contact person
UPDATE public.hdb_question
SET 
    config = config || jsonb_build_object(
        'type', 'select',
        'apiUrl', 'lookup-data/all',
        'category', 'RM',
        'isRequired', true
    ),
    typ = 'apicall',
    updated_at = NOW(),
    updated_by = -2
WHERE binding_key = 'rmContact';

-- 2025-08-19
ALTER TYPE public.tshirt_size_enum 
ADD VALUE 'XXXXL' AFTER 'XXXL';


WITH ref_option AS (
    -- Pick any one existing goodies option to copy category_id
    SELECT category_id 
    FROM hdb_option o
    JOIN hdb_question_option_map qm ON o.id = qm.option_id
    JOIN hdb_question q ON q.id = qm.question_id
    WHERE q.binding_key IN ('goodiesJacketSize', 'goodiesTshirtSize')
    LIMIT 1
),
ins_option AS (
    -- Insert new option XXXXL with same category_id
    INSERT INTO hdb_option (name, category_id, created_by, updated_by, created_at, updated_at,stats,typ)
    SELECT 'XXXXL', r.category_id, -2, -2, NOW(), NOW(), 'published','string'
    FROM ref_option r
    RETURNING id
),
q_ids AS (
    -- Get both goodies question ids
    SELECT id 
    FROM hdb_question 
    WHERE created_by = -2 
      AND binding_key IN ('goodiesJacketSize', 'goodiesTshirtSize')
)
-- Map new option to both questions
INSERT INTO hdb_question_option_map (question_id, option_id, created_by, updated_by, created_at, updated_at)
SELECT q.id, o.id, -2, -2, NOW(), NOW()
FROM q_ids q CROSS JOIN ins_option o;


-- 2025-08-18
-- Add average_rating column to hdb_program_registration
ALTER TABLE hdb_program_registration
ADD COLUMN average_rating NUMERIC;

-- Update average_rating with the average from hdb_program_registration_rm_rating
UPDATE hdb_program_registration pr
SET average_rating = sub.avg_rating
FROM (
    SELECT program_registration_id, AVG(rating)::NUMERIC(5,2) AS avg_rating
    FROM hdb_program_registration_rm_rating
    GROUP BY program_registration_id
) sub
WHERE pr.id = sub.program_registration_id;



-- 2025-08-14
-- Add api_endpoint column
ALTER TABLE audit_history_log 
ADD COLUMN api_endpoint VARCHAR(255) NULL;

-- Add http_method column  
ALTER TABLE audit_history_log 
ADD COLUMN http_method VARCHAR(10) NULL;
select * from hdb_question where (binding_key = 'roommate' or  binding_key='preference' ) and created_by = -2;
-- Add helperText for binding_key = 'roommate'
UPDATE hdb_question
SET config = jsonb_set(
    config,
    '{helperText}',
    to_jsonb('(roommate preference not guaranteed)'::text),
    true
)
WHERE binding_key = 'roommate';

-- Remove existing keys and add mahatriachoiceconfig
UPDATE hdb_question
SET config = jsonb_set(
    config - 'allowMahatriaChoice' - 'mahatriachoicetext',
    '{mahatriaChoiceConfig}',
    to_jsonb(json_build_object(
        'allowMahatriaChoice', true,
        'mahatriaChoicetText', 'Any HDB/MSD'
    )),
    true
)
WHERE binding_key = 'preference';

ALTER TABLE hdb_program_registration
ALTER COLUMN full_name TYPE citext;


ALTER TABLE users 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE users 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN users.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN users.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

ALTER TABLE hdb_communication_track 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_communication_track 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_communication_track.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_communication_track.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';


ALTER TABLE hdb_preference 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_preference 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_preference.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_preference.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';



ALTER TABLE hdb_program_registration_rm_rating 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_program_registration_rm_rating 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_program_registration_rm_rating.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_program_registration_rm_rating.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';



ALTER TABLE hdb_program_registration_swap 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_program_registration_swap 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_program_registration_swap.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_program_registration_swap.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';


ALTER TABLE hdb_registration_approval 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_registration_approval 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_registration_approval.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_registration_approval.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';


ALTER TABLE hdb_registration_invoice_detail 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_registration_invoice_detail 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_registration_invoice_detail.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_registration_invoice_detail.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

ALTER TABLE hdb_registration_payment_detail 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_registration_payment_detail 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_registration_payment_detail.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_registration_payment_detail.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';



ALTER TABLE hdb_registration_travel_info 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_registration_travel_info 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_registration_travel_info.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_registration_travel_info.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';


ALTER TABLE hdb_registration_travel_plan 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_registration_travel_plan 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_registration_travel_plan.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_registration_travel_plan.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

ALTER TABLE user_role_map 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE user_role_map 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN user_role_map.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN user_role_map.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Users table
CREATE INDEX IF NOT EXISTS idx_users_audit_ref_id ON users(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_users_parent_ref_id ON users(parent_ref_id);

-- hdb_communication_track table
CREATE INDEX IF NOT EXISTS idx_hdb_communication_track_audit_ref_id ON hdb_communication_track(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_communication_track_parent_ref_id ON hdb_communication_track(parent_ref_id);

-- hdb_preference table
CREATE INDEX IF NOT EXISTS idx_hdb_preference_audit_ref_id ON hdb_preference(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_preference_parent_ref_id ON hdb_preference(parent_ref_id);

-- hdb_program_registration_rm_rating table
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_rm_rating_audit_ref_id ON hdb_program_registration_rm_rating(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_rm_rating_parent_ref_id ON hdb_program_registration_rm_rating(parent_ref_id);

-- hdb_program_registration_swap table
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_swap_audit_ref_id ON hdb_program_registration_swap(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_swap_parent_ref_id ON hdb_program_registration_swap(parent_ref_id);

-- hdb_registration_approval table
CREATE INDEX IF NOT EXISTS idx_hdb_registration_approval_audit_ref_id ON hdb_registration_approval(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_registration_approval_parent_ref_id ON hdb_registration_approval(parent_ref_id);

-- hdb_registration_invoice_detail table
CREATE INDEX IF NOT EXISTS idx_hdb_registration_invoice_detail_audit_ref_id ON hdb_registration_invoice_detail(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_registration_invoice_detail_parent_ref_id ON hdb_registration_invoice_detail(parent_ref_id);

-- hdb_registration_payment_detail table
CREATE INDEX IF NOT EXISTS idx_hdb_registration_payment_detail_audit_ref_id ON hdb_registration_payment_detail(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_registration_payment_detail_parent_ref_id ON hdb_registration_payment_detail(parent_ref_id);

-- hdb_registration_travel_info table
CREATE INDEX IF NOT EXISTS idx_hdb_registration_travel_info_audit_ref_id ON hdb_registration_travel_info(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_registration_travel_info_parent_ref_id ON hdb_registration_travel_info(parent_ref_id);

-- hdb_registration_travel_plan table
CREATE INDEX IF NOT EXISTS idx_hdb_registration_travel_plan_audit_ref_id ON hdb_registration_travel_plan(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_registration_travel_plan_parent_ref_id ON hdb_registration_travel_plan(parent_ref_id);

-- user_role_map table
CREATE INDEX IF NOT EXISTS idx_user_role_map_audit_ref_id ON user_role_map(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_user_role_map_parent_ref_id ON user_role_map(parent_ref_id);


-- 2025-08-14
-- Add audit_ref_id and parent_ref_id columns to hdb_program_registration table
ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments to describe the purpose of these columns
COMMENT ON COLUMN hdb_program_registration.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_program_registration.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Add indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_audit_ref_id ON hdb_program_registration(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_parent_ref_id ON hdb_program_registration(parent_ref_id);

-- hdb_registration_question_answer table
ALTER TABLE hdb_registration_question_answer 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_registration_question_answer 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_registration_question_answer.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_registration_question_answer.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_hdb_registration_question_answer_audit_ref_id ON hdb_registration_question_answer(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_registration_question_answer_parent_ref_id ON hdb_registration_question_answer(parent_ref_id);

-- program_message table
ALTER TABLE program_message 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE program_message 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN program_message.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN program_message.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_program_message_audit_ref_id ON program_message(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_program_message_parent_ref_id ON program_message(parent_ref_id);

-- hdb_program_registration_recommendations table
ALTER TABLE hdb_program_registration_recommendations 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_program_registration_recommendations 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_program_registration_recommendations.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_program_registration_recommendations.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_recommendations_audit_ref_id ON hdb_program_registration_recommendations(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_recommendations_parent_ref_id ON hdb_program_registration_recommendations(parent_ref_id);

-- program_registration_failures table
ALTER TABLE program_registration_failures 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE program_registration_failures 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN program_registration_failures.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN program_registration_failures.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_program_registration_failures_audit_ref_id ON program_registration_failures(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_program_registration_failures_parent_ref_id ON program_registration_failures(parent_ref_id);

-- hdb_program_registration_goodies table
ALTER TABLE hdb_program_registration_goodies 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_program_registration_goodies 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_program_registration_goodies.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_program_registration_goodies.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_goodies_audit_ref_id ON hdb_program_registration_goodies(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_program_registration_goodies_parent_ref_id ON hdb_program_registration_goodies(parent_ref_id);

-- hdb_registration_custom_response table
ALTER TABLE hdb_registration_custom_response 
ADD COLUMN IF NOT EXISTS audit_ref_id BIGINT NULL;

ALTER TABLE hdb_registration_custom_response 
ADD COLUMN IF NOT EXISTS parent_ref_id BIGINT NULL;

-- Add comments
COMMENT ON COLUMN hdb_registration_custom_response.audit_ref_id IS 'Reference ID for audit tracking, typically the entity ID itself';
COMMENT ON COLUMN hdb_registration_custom_response.parent_ref_id IS 'Parent reference ID for audit tracking, typically the entity ID itself';

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_hdb_registration_custom_response_audit_ref_id ON hdb_registration_custom_response(audit_ref_id);
CREATE INDEX IF NOT EXISTS idx_hdb_registration_custom_response_parent_ref_id ON hdb_registration_custom_response(parent_ref_id);

-- 2025-08-14
-- Add parent_ref_id column to audit_history_log for registration tracking
ALTER TABLE public.audit_history_log
ADD COLUMN parent_ref_id VARCHAR(100);
CREATE INDEX idx_audit_history_log_parent_ref_id ON public.audit_history_log USING btree (parent_ref_id);
-- 2025-08-12
SELECT qa.id, qa.full_name, qa.email AS old_email, dev_data.email AS new_email,
       qa.phone_number AS old_phone, dev_data.phone_number AS new_phone,
       qa.country_code AS old_country, dev_data.country_code AS new_country
FROM users AS qa
JOIN (
    VALUES
        ('Charuta',     'charuta@yopmail.com',     '2092841144', '+1'),
        ('Vinod',       'vinod@yopmail.com',       '2242864639', '+1'),
        ('Mahesh',      'mahesh@yopmail.com',      '4106354732', '+1'),
        ('Other',       'shobs@yopmail.com',       '3312762635', '+1'),
        ('Kishan',      'kishan@yopmail.com',      '9999999999', '+91'),
        ('Swaroop',     'swaroop@yopmail.com',     '4345351236', '+1'),
        ('Subbu',       'subbu@yopmail.com',       '2562690950', '+1'),
        ('Arun',        'arun@yopmail.com',        '5182612621', '+1'),
        ('Bhupendhar',  'bhupendhar@yopmail.com',  '2817603626', '+1'),
        ('Chithambaran','chithambaran@yopmail.com','4573994944', '+358'),
        ('Gopal',       'gopal@yopmail.com',       '4573994937', '+358'),
        ('Sridhar',     'rsridhar@yopmail.com',    '4573994943', '+358'),
        ('Vijisubbu',   'vijisubbu@yopmail.com',   '4573994942', '+358'),
        ('Sabari',      'sabari@yopmail.com',      '4573994941', '+358'),
        ('Mukundan',    'mukundan@yopmail.com',    '7146968342', '+1'),
        ('Manohar',     'manohar@yopmail.com',     '4123269028', '+1'),
        ('Mohini',      'mohini@yopmail.com',      '3014532713', '+1')
) AS dev_data(full_name, email, phone_number, country_code)
  ON qa.full_name = dev_data.full_name
WHERE qa.role = 'relational_manager'
  AND qa.user_type = 'Org';




UPDATE users AS qa
SET email = dev_data.email,
    phone_number = dev_data.phone_number,
    country_code = dev_data.country_code
FROM (
    VALUES
        ('Charuta',     'charuta@yopmail.com',     '2092841144', '+1'),
        ('Vinod',       'vinod@yopmail.com',       '2242864639', '+1'),
        ('Mahesh',      'mahesh@yopmail.com',      '4106354732', '+1'),
        ('Other',       'shobs@yopmail.com',       '3312762635', '+1'),
        ('Kishan',      'kishan@yopmail.com',      '9999999999', '+91'),
        ('Swaroop',     'swaroop@yopmail.com',     '4345351236', '+1'),
        ('Subbu',       'subbu@yopmail.com',       '2562690950', '+1'),
        ('Arun',        'arun@yopmail.com',        '5182612621', '+1'),
        ('Bhupendhar',  'bhupendhar@yopmail.com',  '2817603626', '+1'),
        ('Chithambaran','chithambaran@yopmail.com','4573994944', '+358'),
        ('Gopal',       'gopal@yopmail.com',       '4573994937', '+358'),
        ('Sridhar',     'rsridhar@yopmail.com',    '4573994943', '+358'),
        ('Vijisubbu',   'vijisubbu@yopmail.com',   '4573994942', '+358'),
        ('Sabari',      'sabari@yopmail.com',      '4573994941', '+358'),
        ('Mukundan',    'mukundan@yopmail.com',    '7146968342', '+1'),
        ('Manohar',     'manohar@yopmail.com',     '4123269028', '+1'),
        ('Mohini',      'mohini@yopmail.com',      '3014532713', '+1')
) AS dev_data(full_name, email, phone_number, country_code)
WHERE qa.full_name = dev_data.full_name
  AND qa.role = 'relational_manager'
  AND qa.user_type = 'Org';

SELECT id, full_name, email, phone_number , country_code
FROM users
WHERE role = 'relational_manager'
  AND user_type = 'Org'
  AND full_name = 'Other';



UPDATE users
SET email = 'hdb@infinitheism.com',
    phone_number = '9841670000',
    country_code = '+91'
WHERE role = 'relational_manager'
  AND user_type = 'Org'
  AND full_name = 'Other';


-- Audit logging tables
-- Create audit_history_log table
CREATE TABLE public.audit_history_log (
    id SERIAL PRIMARY KEY,
    entity_type VARCHAR(100) NOT NULL,
    entity_name VARCHAR(100) NOT NULL,
    entity_id VARCHAR(100) NOT NULL,
    action VARCHAR(10) NOT NULL,
    user_id VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    request_id VARCHAR(100),
    metadata JSONB,
    type VARCHAR(10) NOT NULL DEFAULT 'AUDIT',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for audit_history_log
CREATE INDEX idx_audit_history_log_entity_type_id ON public.audit_history_log USING btree (entity_type, entity_id);
CREATE INDEX idx_audit_history_log_user_id ON public.audit_history_log USING btree (user_id);
CREATE INDEX idx_audit_history_log_created_at ON public.audit_history_log USING btree (created_at);

-- Create audit_history_log_detail table
CREATE TABLE public.audit_history_log_detail (
    id SERIAL PRIMARY KEY,
    audit_log_id INTEGER NOT NULL,
    field_name VARCHAR(100) NOT NULL,
    old_value JSONB,
    new_value JSONB,
    field_type VARCHAR(50) NOT NULL,
    CONSTRAINT fk_audit_history_log_detail_audit_log
        FOREIGN KEY (audit_log_id)
        REFERENCES public.audit_history_log(id)
        ON DELETE CASCADE
);

-- Create index for audit_history_log_detail
CREATE INDEX idx_audit_history_log_detail_audit_log ON public.audit_history_log_detail USING btree (audit_log_id);

-- 2025-08-09
-- Create a new enum for gateway payment status
CREATE TYPE gateway_payment_status_enum AS ENUM ('failed', 'captured', 'authorized');

ALTER TYPE payment_status_enum
ADD VALUE IF NOT EXISTS 'failed';

-- Add new columns for gateway payment status
ALTER TABLE hdb_registration_payment_detail
ADD COLUMN gateway_online_payment_status gateway_payment_status_enum,
ADD COLUMN gateway_online_payment_attempt_status JSON,
ADD COLUMN portal_online_payment_status gateway_payment_status_enum,
ADD COLUMN webhook_online_payment_status gateway_payment_status_enum;


ALTER TABLE hdb_registration_payment_details_history
ADD COLUMN gateway_online_payment_status gateway_payment_status_enum,
ADD COLUMN gateway_online_payment_attempt_status JSON,
ADD COLUMN portal_online_payment_status gateway_payment_status_enum,
ADD COLUMN webhook_online_payment_status gateway_payment_status_enum;

-- 2025-08-07

ALTER TABLE hdb_registration_payment_detail
ADD COLUMN portal_online_payment_attempt_status JSONB;

ALTER TABLE hdb_registration_payment_detail
ADD COLUMN webhook_online_payment_attempt_status JSONB;

-- Rename the existing column to use 'portal' prefix
ALTER TABLE hdb_registration_payment_details_history
RENAME COLUMN payment_attempt_status TO portal_online_payment_attempt_status;

-- Add a new column for webhook-specific status
ALTER TABLE hdb_registration_payment_details_history
ADD COLUMN webhook_online_payment_attempt_status JSONB;

-- 2025-08-06
ALTER TABLE hdb_registration_payment_details_history
ADD COLUMN payment_attempt_status JSON;

-- 2025-08-09

-- Add allocated program and session to approval track
ALTER TABLE hdb_registration_approval_track
  ADD COLUMN IF NOT EXISTS allocated_program_id BIGINT,
  ADD COLUMN IF NOT EXISTS allocated_session_id BIGINT;

ALTER TABLE hdb_registration_approval_track
  ADD CONSTRAINT fk_registration_approval_track_allocated_program FOREIGN KEY (allocated_program_id) REFERENCES program_v1(id) ON DELETE SET NULL;

ALTER TABLE hdb_registration_approval_track
  ADD CONSTRAINT fk_registration_approval_track_allocated_session FOREIGN KEY (allocated_session_id) REFERENCES program_session(id) ON DELETE SET NULL;
  
-- Cancellation reason master table and column update

ALTER TYPE public.approval_status_enum ADD VALUE 'cancelled';

CREATE TABLE IF NOT EXISTS public.hdb_cancellation_reason (
  id SERIAL PRIMARY KEY,
  reason_key VARCHAR(255) UNIQUE NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  description TEXT,
  status hdb_common_status_enum DEFAULT 'active',
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ
);

ALTER TABLE public.hdb_program_registration
ADD COLUMN IF NOT EXISTS cancellation_reason VARCHAR(255) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS cancellation_comments TEXT;

-- Insert common cancellation reasons for program registration
INSERT INTO public.hdb_cancellation_reason (reason_key, display_name, description, status, created_by, updated_by) VALUES
('PERSONAL_EMERGENCY', 'Personal Emergency', 'Cancelled due to unexpected personal or family emergency', 'active', -2, -2),
('MEDICAL_REASONS', 'Medical Reasons', 'Cancelled due to health issues or medical concerns', 'active', -2, -2),
('TRAVEL_RESTRICTIONS', 'Travel Restrictions', 'Cancelled due to travel restrictions or transportation issues', 'active', -2, -2),
('SCHEDULE_CONFLICT', 'Schedule Conflict', 'Cancelled due to conflicting personal or professional schedule', 'active', -2, -2),
('OTHER', 'Other', 'Other reasons not covered in the predefined categories', 'active', -2, -2);

-- 2025-08-08

ALTER TABLE public.hdb_program_registration_goodies
ADD COLUMN tshirt BOOLEAN DEFAULT false,
ADD COLUMN jacket BOOLEAN DEFAULT false;

select * from hdb_question where created_by = -2 order by id desc;

-- Insert "Do you want T-shirt?" and "Do you want Jacket?"
INSERT INTO public.hdb_question (
  label, config, stats, typ, created_at, updated_at, deleted_at, created_by, updated_by,
  form_section_id, answer_location, binding_key
)
VALUES 
('T-shirt', '{"isRequired": false}'::jsonb, 'published', 'radio', NOW(), NULL, NULL, -2, -2, (SELECT id FROM hdb_form_section WHERE key = 'FS_GOODIES'), 'hdb_program_registration_goodies.tshirt', 'goodiesTshirtApplicable'),
('Jacket',  '{"isRequired": false}'::jsonb, 'published', 'radio', NOW(), NULL, NULL, -2, -2, (SELECT id FROM hdb_form_section WHERE key = 'FS_GOODIES'), 'hdb_program_registration_goodies.jacket', 'goodiesJacketApplicable');
-- Dynamically insert option mappings for new radio questions
DO $$
DECLARE
    r RECORD;
BEGIN
  FOR r IN
    SELECT 
      new_q.id AS question_id,
      o.option_id
    FROM hdb_question new_q
    CROSS JOIN (
      SELECT DISTINCT option_id
      FROM hdb_question_option_map o
      JOIN hdb_question q ON q.id = o.question_id
      WHERE q.binding_key = 'isGstRegistered' and q.created_by = -2
    ) o
    WHERE new_q.binding_key IN ('goodiesTshirtApplicable', 'goodiesJacketApplicable')  
      AND new_q.created_by = -2
      AND new_q.typ = 'radio'
  LOOP
    EXECUTE format(
      'INSERT INTO hdb_question_option_map (question_id, option_id, created_at, updated_at, created_by, updated_by)
       VALUES (%s, %s, NOW(), NOW(), -2, -2);',
       r.question_id, r.option_id
    );
  END LOOP;
END$$;

SELECT id, binding_key
FROM hdb_question
WHERE binding_key IN ('goodiesTshirtApplicable', 'goodiesJacketApplicable') 
  AND created_by = -2;

-- Step 1: Dynamically fetch dependency question IDs
WITH dependency_questions AS (
  SELECT id, binding_key
  FROM hdb_question
  WHERE binding_key IN ('goodiesTshirtApplicable', 'goodiesJacketApplicable') and created_by = -2
),

-- Step 2: Build and apply the `dependsOn` config
update_tshirt AS (
  UPDATE hdb_question
  SET config = jsonb_set(
    config,
    '{dependsOn}',
    jsonb_build_array(jsonb_build_object(
      'value', 'yes',
      'questionId', (SELECT id FROM dependency_questions WHERE binding_key = 'goodiesTshirtApplicable' and created_by = -2),
      'questionBindingKey', 'goodiesTshirtApplicable'
    ))
  )
  WHERE binding_key = 'goodiesTshirtSize' and created_by = -2
  RETURNING id
),

update_jacket AS (
  UPDATE hdb_question
  SET config = jsonb_set(
    config,
    '{dependsOn}',
    jsonb_build_array(jsonb_build_object(
      'value', 'yes',
      'questionId', (SELECT id FROM dependency_questions WHERE binding_key = 'goodiesJacketApplicable'),
      'questionBindingKey', 'goodiesJacketApplicable'
    ))
  )
  WHERE binding_key = 'goodiesJacketSize' and created_by = -2
  RETURNING id
)

SELECT * FROM update_tshirt
UNION ALL
SELECT * FROM update_jacket;


-- 2025-08-07
select * from hdb_question where created_by = -2 and typ = 'file'; -- 9

UPDATE hdb_question
SET config = jsonb_set(
  config,
  '{filetype}',
  '"image"'::jsonb
)
WHERE binding_key = 'pictureUrl' AND created_by = -2;

-- 1. Image/Profile Uploads
UPDATE hdb_question
SET config = jsonb_set(
  config,
  '{fileConfig}',
  jsonb_build_object(
    'type', config->>'filetype',
    'accepts', '["image/jpeg", "image/jpg", "image/png", "image/webp"]'::jsonb
  )
)
WHERE binding_key IN (
  'userPictureUrl', 
  'passport', 
  'idBack', 
  'profileUrl', 
  'pictureUrl', 
  'visa'
) AND created_by = -2;

-- 2. Video Upload
UPDATE hdb_question
SET config = jsonb_set(
  config,
  '{fileConfig}',
  '{
    "type": "video",
    "accepts": ["video/mp4", "video/webm"],
    "timeLimit": {
      "unit": "sec",
      "maxValue": 111,
      "minValue": 1
    },
    "sizeLimit": {
      "unit": "mb",
      "maxValue": 50,
      "minValue": 0
    }
  }'::jsonb
)
WHERE binding_key = 'videoUrl' and created_by = -2;

-- 3. Ticket Uploads
UPDATE hdb_question
SET config = jsonb_set(
  config,
  '{fileConfig}',
  '{
    "type": "image",
    "accepts": ["image/jpeg", "image/jpg", "image/png", "image/webp", "application/pdf"]
  }'::jsonb
)
WHERE binding_key IN (
  'uploadOnwardJourneyTicket', 
  'uploadReturnJourneyTicket'
) AND created_by = -2;


UPDATE hdb_question
SET config = jsonb_set(
  config,
  '{filetype}',
  '"image"'::jsonb
)
WHERE binding_key = 'pictureUrl' AND created_by = -2;



-- 2025-08-06

SELECT hqom.*
FROM hdb_question_option_map hqom
JOIN hdb_question hq ON hqom.question_id = hq.id
JOIN hdb_option ho ON hqom.option_id = ho.id
WHERE hq.created_by = -2
  AND hq.binding_key = 'paymentMode'
  AND ho.name ILIKE '%card swipe%';


UPDATE hdb_question_option_map hqom
SET deleted_at = now()
FROM hdb_question hq, hdb_option ho
WHERE hqom.question_id = hq.id
  AND hqom.option_id = ho.id
  AND hq.created_by = -2
  AND hq.binding_key = 'paymentMode'
  AND ho.name ILIKE '%card swipe%';

select * from public.feature_flag WHERE feature_key = 'showInfiniminutePrayer';

UPDATE feature_flag
SET FLAG = true
WHERE feature_key = 'showInfiniminutePrayer';

UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanOnward"') WHERE binding_key = 'airlineNameOnward' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'originalPayment' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,1,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'originalPayment' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,2,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'originalPayment' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,3,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'originalPayment' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'swipeDate' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanOnward"') WHERE binding_key = 'arrivalDateTime' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{prefill,prefillIf,0,questionBindingKey}', '"existingProformaInvoice"') WHERE binding_key = 'invoiceName' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{prefill,prefillFromBindingKey}', '"pro_forma_invoice_name"') WHERE binding_key = 'invoiceName' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'chequeDate' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'bankName' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,1,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'bankName' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'tidReferenceNo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanOnward"') WHERE binding_key = 'flightNumberOnward' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"internationalId"') WHERE binding_key = 'passport' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"isGstRegistered"') WHERE binding_key = 'zip' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"bankName"') WHERE binding_key = 'otherBankName' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"rmContact"') WHERE binding_key = 'otherInfinitheismContact' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"isGstRegistered"') WHERE binding_key = 'gstNumber' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"internationalId"') WHERE binding_key = 'visa' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"city"') WHERE binding_key = 'otherCityName' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanOnward"') WHERE binding_key = 'uploadOnwardJourneyTicket' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanReturn"') WHERE binding_key = 'uploadReturnJourneyTicket' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'handOverDate' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,1,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'handOverDate' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelDetails"') WHERE binding_key = 'onwardInfo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelDetails"') WHERE binding_key = 'returnInfo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"handOverTo"') WHERE binding_key = 'otherRM' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'chequeNo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"tdsAmount"') WHERE binding_key = 'tanNumber' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'utrNumber' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'handOverTo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,1,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'handOverTo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,2,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'handOverTo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"paymentMode"') WHERE binding_key = 'bankTransferDate' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanReturn"') WHERE binding_key = 'departureDateTime' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelDetails"') WHERE binding_key = 'travelPlanReturn' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelDetails"') WHERE binding_key = 'travelPlanOnward' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanReturn"') WHERE binding_key = 'goingTo' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanOnward"') WHERE binding_key = 'comingFrom' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanReturn"') WHERE binding_key = 'flightNumberReturn' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{dependsOn,0,questionBindingKey}', '"travelPlanReturn"') WHERE binding_key = 'airlineNameReturn' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{prefill,prefillIf,0,questionBindingKey}', '"existingProformaInvoice"') WHERE binding_key = 'invoiceAddress' AND created_by = -2;
UPDATE hdb_question SET config = jsonb_set(config, '{prefill,prefillFromBindingKey}', '"proFormaBillingAddress"') WHERE binding_key = 'invoiceAddress' AND created_by = -2;


-- 2025-08-04
---- I have reviewed and confirm that my invoice and payment information is correct.

ALTER TABLE hdb_program_registration
ADD COLUMN invoice_confirmation BOOLEAN DEFAULT FALSE;

-- Step 1: Insert new question and capture the new ID
WITH inserted_question AS (
  INSERT INTO hdb_question (
    label,
    config,
    stats,
    typ,
    created_at,
    updated_at,
    created_by,
    updated_by,
    form_section_id,
    answer_location,
    binding_key
  )
  SELECT
    'I have reviewed and confirm that my invoice and payment information is correct.',
    config,
    stats,
    typ,
    NOW(),
    NOW(),
    created_by,
    updated_by,
    2,  -- new form_section_id
    'hdb_program_registration.invoice_confirmation',
    'invoiceConfirmation'
  FROM hdb_question
  WHERE binding_key = 'terms'
    AND deleted_at IS NULL
  LIMIT 1
  RETURNING id
)

-- Step 2: Use that ID to insert option mappings
INSERT INTO hdb_question_option_map (
  question_id,
  option_id,
  created_at,
  updated_at,
  created_by,
  updated_by
)
SELECT
  inserted_question.id,
  hqo.option_id,
  NOW(),
  NOW(),
  -2,
  -2
FROM hdb_question_option_map hqo
JOIN hdb_question hq ON hqo.question_id = hq.id
JOIN inserted_question ON TRUE
WHERE hq.binding_key = 'terms'
  AND hq.deleted_at IS NULL;


-- 2025-07-30
-- To indicate whether a seat is free or not.
-- It also sets the default value of this column to false.
ALTER TABLE hdb_program_registration
ADD COLUMN is_free_seat boolean DEFAULT false;


-- 2025-07-29
--goodies section insert
UPDATE hdb_form_section
SET name = 'Travel Plan'
WHERE key = 'FS_TRAVELPLAN';


INSERT INTO public.hdb_form_section (name, description, created_at, updated_at, deleted_at, created_by, updated_by, display_order, key) VALUES ('Goodies', 'Section for goodies details', NOW(), NOW(), NULL, -2, -2, 5, 'FS_GOODIES');



CREATE TABLE public.hdb_program_registration_goodies (
  id SERIAL PRIMARY KEY,
  registration_id INTEGER NOT NULL,
  notebook BOOLEAN,
  ratria_pillar_leonia BOOLEAN,
  flask BOOLEAN,
  tshirt_size tshirt_size_enum,
  jacket_size tshirt_size_enum,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  deleted_at TIMESTAMP WITH TIME ZONE,
  created_by INTEGER,
  updated_by INTEGER
);



--update master question

select * from hdb_question where created_by = -2 and binding_key = 'tShirtSize';

DELETE FROM hdb_question
WHERE created_by = -2
  AND binding_key = 'tShirtSize';

select * from hdb_question where binding_key = 'departureDateTime';


UPDATE public.hdb_question
SET config = '{
  "prefill": {
    "prefillType": "programDependent",
    "prefillDateFrom": "endsAt"
  },
  "dependsOn": [
    {
      "value": "Flight",
      "questionId": 989
    }
  ],
  "isRequired": true
}'
WHERE binding_key = 'departureDateTime';



select * from hdb_question where binding_key = 'arrivalDateTime';


UPDATE public.hdb_question
SET config = '{
  "prefill": {
    "prefillType": "programDependent",
    "prefillDateFrom": "startsAt"
  },
  "dependsOn": [
    {
      "value": "Flight",
      "questionId": 988
    }
  ],
  "isRequired": true
}'
WHERE binding_key = 'arrivalDateTime';



--inserting new questions dynamically

INSERT INTO public.hdb_question (
    label, 
    config, 
    stats, 
    typ, 
    created_at, 
    updated_at, 
    deleted_at, 
    created_by, 
    updated_by, 
    form_section_id, 
    answer_location, 
    binding_key
) VALUES 
(
    'Note book', 
    '{"isRequired": false}', 
    'published', 
    'radio', 
    NOW(), 
    NULL, 
    NULL, 
    -2, 
    -2, 
    (SELECT id FROM hdb_form_section WHERE key = 'FS_GOODIES'), 
    'hdb_program_registration_goodies.notebook', 
    'goodiesNotebook'
),
(
    'Ratria pillar in leonia', 
    '{"isRequired": false}', 
    'published', 
    'radio', 
    NOW(), 
    NULL, 
    NULL, 
    -2, 
    -2, 
    (SELECT id FROM hdb_form_section WHERE key = 'FS_GOODIES'), 
    'hdb_program_registration_goodies.ratria_pillar_leonia', 
    'goodiesRatriaPillarLeonia'
),
(
    'Flask', 
    '{"isRequired": false}', 
    'published', 
    'radio', 
    NOW(), 
    NULL, 
    NULL, 
    -2, 
    -2, 
    (SELECT id FROM hdb_form_section WHERE key = 'FS_GOODIES'), 
    'hdb_program_registration_goodies.flask', 
    'goodiesFlask'
);

SELECT 
  q.id AS question_id,
  q.label AS question_label,
  q.binding_key,
  q.typ,
  o.option_id AS option_id
FROM hdb_question q
LEFT JOIN hdb_question_option_map o ON q.id = o.question_id
WHERE q.created_by = -2
  AND q.typ = 'radio'
  AND q.binding_key = 'isGstRegistered';



  SELECT 
  q.id AS question_id,
  q.binding_key,
  o.option_id
FROM hdb_question q
LEFT JOIN hdb_question_option_map o ON q.id = o.question_id
WHERE q.created_by = -2
  AND q.typ = 'radio'
  AND q.binding_key IN (
    'goodiesNotebook',
    'goodiesRatriaPillarLeonia',
    'goodiesFlask'
  );



SELECT 
  'INSERT INTO hdb_question_option_map (question_id, option_id, created_at, updated_at, created_by, updated_by) VALUES (' 
  || new_q.id || ', ' 
  || o.option_id || ', NOW(), NOW(), -2, -2);'
AS insert_stmt
FROM hdb_question new_q
CROSS JOIN (
  SELECT DISTINCT option_id
  FROM hdb_question_option_map o
  JOIN hdb_question q ON q.id = o.question_id
  WHERE q.binding_key = 'isGstRegistered'
) o
WHERE new_q.binding_key IN (
  'goodiesNotebook',
  'goodiesRatriaPillarLeonia',
  'goodiesFlask'
)
AND new_q.created_by = -2
AND new_q.typ = 'radio';




DO $$
DECLARE
    r RECORD;
BEGIN
  FOR r IN
    SELECT 
      new_q.id AS question_id,
      o.option_id AS option_id
    FROM hdb_question new_q
    CROSS JOIN (
      SELECT DISTINCT option_id
      FROM hdb_question_option_map o
      JOIN hdb_question q ON q.id = o.question_id
      WHERE q.binding_key = 'isGstRegistered'
    ) o
    WHERE new_q.binding_key IN (
      'goodiesNotebook',
      'goodiesRatriaPillarLeonia',
      'goodiesFlask'
    )
    AND new_q.created_by = -2
    AND new_q.typ = 'radio'
  LOOP
    EXECUTE format(
      'INSERT INTO hdb_question_option_map (question_id, option_id, created_at, updated_at, created_by, updated_by)
       VALUES (%s, %s, NOW(), NOW(), -2, -2);',
       r.question_id, r.option_id
    );
  END LOOP;
END$$;


  SELECT 
  q.id AS question_id,
  q.binding_key,
  o.option_id
FROM hdb_question q
LEFT JOIN hdb_question_option_map o ON q.id = o.question_id
WHERE q.created_by = -2
  AND q.typ = 'radio'
  AND q.binding_key IN (
    'goodiesNotebook',
    'goodiesRatriaPillarLeonia',
    'goodiesFlask'
  );





-- Step 1: Insert category and get its ID
WITH inserted_category AS (
  INSERT INTO hdb_option_category (
    name, created_at, updated_at, created_by, updated_by
  )
  VALUES (
    'T-Shirt Size', NOW(), NOW(), -2, -2
  )
  RETURNING id
),

-- Step 2: Insert options using returned category id
inserted_options AS (
  INSERT INTO hdb_option (
    name, category_id, created_at, updated_at, created_by, updated_by, typ
  )
  SELECT name, inserted_category.id, NOW(), NOW(), -2, -2, 'string'
  FROM inserted_category,
       (VALUES 
          ('XS'), ('S'), ('M'), ('L'), ('XL'), ('XXL'), ('XXXL')
       ) AS sizes(name)
  RETURNING id, name
),

-- Step 3: Insert new questions using enumName
inserted_questions AS (
  INSERT INTO public.hdb_question (label, config, stats, typ, created_at, updated_at, deleted_at, created_by, updated_by, form_section_id, answer_location, binding_key) VALUES 
    ('T-shirt size', '{"isRequired": false}', 'published', 'select', NOW(), NULL, NULL, -2, -2,  (SELECT id FROM hdb_form_section WHERE key = 'FS_GOODIES'), 'hdb_program_registration_goodies.tshirt_size', 'goodiesTshirtSize'),
    ('Jacket size', '{"isRequired": false}', 'published', 'select', NOW(), NULL, NULL, -2, -2,  (SELECT id FROM hdb_form_section WHERE key = 'FS_GOODIES'), 'hdb_program_registration_goodies.jacket_size', 'goodiesJacketSize')
  RETURNING id, label
)

-- Step 4: Insert into hdb_question_option_map
INSERT INTO hdb_question_option_map (
  question_id, option_id, created_at, updated_at, created_by, updated_by
)
SELECT q.id, o.id, NOW(), NOW(), -2, -2
FROM inserted_questions q
CROSS JOIN inserted_options o;
-- Step 5: Update the config for the new questions
select * from hdb_question where created_by = -2 and binding_key = 'rmContact';

select * from hdb_question where created_by = -2 and binding_key = 'otherInfinitheismContact';


-- Update label for binding_key = 'rmContact'
UPDATE hdb_question
SET label = 'Contact person'
WHERE created_by = -2 AND binding_key = 'rmContact';

-- Update label for binding_key = 'otherInfinitheismContact'
UPDATE hdb_question
SET label = 'Other contact person'
WHERE created_by = -2 AND binding_key = 'otherInfinitheismContact';


INSERT INTO public.hdb_question (
  label, 
  config, 
  stats, 
  typ, 
  created_at, 
  updated_at, 
  deleted_at, 
  created_by, 
  updated_by, 
  form_section_id, 
  answer_location, 
  binding_key
) VALUES 
('Alternate phone number', '{"isRequired": false}', 'published', 'tel', NOW(), NULL, NULL, -2, -2, 3, 'hdb_program_registration.alternate_phone_number', 'alternatePhoneNumber'),
('Comments', '{"isRequired": false}', 'published', 'textarea', NOW(), NULL, NULL, -2, -2, 3, 'hdb_program_registration.comments', 'registrationComments');


ALTER TABLE public.hdb_program_registration
ADD COLUMN alternate_phone_number VARCHAR(20),
ADD COLUMN comments TEXT;

CREATE TYPE template_key_enum AS ENUM ('hdb_first_timer', 'hdb_checklist', 'hdb_preparatory');

CREATE TABLE hdb_communication_templates (
    id SERIAL PRIMARY KEY,
    template_id TEXT NOT NULL,
    template_key template_key_enum NOT NULL,
    template_content TEXT NOT NULL,
    template_type communication_type_enum NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TYPE communication_category_enum AS ENUM (
    'hdb_blessed',
    'hdb_seeker_pending',
    'hdb_hold',
    'hdb_reject'
);

ALTER TABLE hdb_communication_templates
ADD COLUMN category communication_category_enum;

ALTER TABLE hdb_communication_templates
ADD COLUMN program_key VARCHAR(100);

ALTER TABLE program_type_v1
ADD CONSTRAINT program_type_v1_key UNIQUE (key);

ALTER TABLE hdb_communication_templates
ADD CONSTRAINT fk_program_key
FOREIGN KEY (program_key)
REFERENCES program_type_v1(key);


INSERT INTO public.hdb_communication_templates ( template_id, template_key, template_content, template_type, created_at, updated_at, category, program_key)
VALUES (
    '2d6f.2a60732b5ed84938.k1.601b0f30-77fe-11f0-b704-86f7e6aa0425.198a1b27fa3',
    'hdb_first_timer', '', 'email', NOW(), NOW(), 'hdb_blessed', 'PT_HDBMSD'
), (
    '2d6f.2a60732b5ed84938.k1.498bc2a0-77fe-11f0-b704-86f7e6aa0425.198a1b1ebca',
    'hdb_checklist', '', 'email', NOW(), NOW(), 'hdb_blessed', 'PT_HDBMSD'
), (
    '2d6f.2a60732b5ed84938.k1.30440cd1-77fe-11f0-b704-86f7e6aa0425.198a1b1461d',
    'hdb_preparatory', '', 'email', NOW(), NOW(), 'hdb_blessed', 'PT_HDBMSD'
);


-- 2025-07-24
ALTER TABLE public.program_session
ALTER COLUMN program_id SET NOT NULL;

ALTER TABLE public.program_session
ADD COLUMN start_date date,
ADD COLUMN start_time time with time zone,
ADD COLUMN end_date date,
ADD COLUMN end_time time with time zone,
ADD COLUMN registration_start_date date,
ADD COLUMN registration_start_time time with time zone,
ADD COLUMN registration_end_date date,
ADD COLUMN registration_end_time time with time zone;


/* *********** need to update in entity
 * Rename type_id to program_type_id
 */
ALTER TABLE public.program_v1
RENAME COLUMN type_id TO program_type_id;

ALTER TABLE public.program_v1
ALTER COLUMN program_type_id SET NOT NULL;

ALTER TABLE public.program_v1
ADD COLUMN start_date date,
ADD COLUMN start_time time with time zone,
ADD COLUMN end_date date,
ADD COLUMN end_time time with time zone,
ADD COLUMN registration_start_date date,
ADD COLUMN registration_start_time time with time zone,
ADD COLUMN registration_end_date date,
ADD COLUMN registration_end_time time with time zone;

ALTER TABLE public.users
ALTER COLUMN is_infiminute SET DEFAULT true;

ALTER TABLE public.users
ALTER COLUMN is_infipath SET DEFAULT true;

ALTER TABLE public.users
ALTER COLUMN is_infiminute_user_active SET DEFAULT true;

ALTER TABLE public.users
ALTER COLUMN country_code TYPE character varying(7);

ALTER TABLE public.users
ALTER COLUMN status_updated_at TYPE timestamp without time zone;

ALTER TABLE public.webinar_registration
ALTER COLUMN created_at TYPE timestamp with time zone;

ALTER TABLE public.webinar_registration
ALTER COLUMN updated_at TYPE timestamp with time zone;

ALTER TABLE public.webinar_registration
ALTER COLUMN deleted_at TYPE timestamp with time zone;

ALTER TABLE public.webinar
ALTER COLUMN deleted_at TYPE timestamp with time zone;

ALTER TABLE public.webinar_analytics
ADD COLUMN country_code character varying(255);


CREATE SEQUENCE hdb_program_registration_rm_rating_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE hdb_program_registration_rm_rating_type
    ALTER COLUMN id SET DEFAULT nextval('hdb_program_registration_rm_rating_type_id_seq');

ALTER SEQUENCE public.hdb_program_registration_rm_rating_type_id_seq OWNED BY public.hdb_program_registration_rm_rating_type.id;


-- 2025-07-23
CREATE TYPE recommendation_level AS ENUM (
    'Wholeheartedly',
    'Affirmatively',
    'Supportively'
);

CREATE TABLE hdb_program_registration_recommendations (
    id SERIAL PRIMARY KEY,
    registration_id INTEGER NOT NULL,
    recommended_by INTEGER NOT NULL,
    recommendation_key recommendation_level,
    is_recommended BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    recommendation_text TEXT,
    CONSTRAINT fk_hprr_registration_id
        FOREIGN KEY (registration_id)
        REFERENCES hdb_program_registration(id),
    CONSTRAINT fk_hprr_recommended_by
        FOREIGN KEY (recommended_by)
        REFERENCES users(id)
);



-----------------THESE ARE OLD SCRIPTS WHICH ALREADY THERE IN BASE SCRIPTS -------------------------
-- 2025-06-21
-- This script holds the messages table schema and initial data.
CREATE TABLE IF NOT EXISTS program_message (
    id SERIAL PRIMARY KEY,
    program_id INT NOT NULL,
    seeker_id INT NOT NULL,
    sender_id INT NOT NULL,
    receiver_id INT,
    receiver_role_flag BOOLEAN DEFAULT FALSE,
    cc_role_id INT,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (program_id) REFERENCES program_v1(id),
    FOREIGN KEY (seeker_id) REFERENCES users(id),
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (receiver_id) REFERENCES users(id),
    FOREIGN KEY (cc_role_id) REFERENCES user_role(id)
);

-- Add user_type column to differentiate users
CREATE TYPE user_type_enum AS ENUM ('Seeker', 'Org', 'AI', 'System');
ALTER TABLE IF EXISTS users
    ADD COLUMN IF NOT EXISTS user_type user_type_enum DEFAULT 'Seeker';

-- 2025-06-29
-- V1__create_lookup_data_table.sql

-- Create the lookup_data table to store various lookup values.
CREATE TABLE lookup_data (
    id SERIAL PRIMARY KEY,
    lookup_category VARCHAR(255) NOT NULL,
    lookup_category_name VARCHAR(255),
    lookup_key VARCHAR(255) NOT NULL,
    lookup_label VARCHAR(255) NOT NULL,
    lookup_status hdb_common_status_enum NOT NULL DEFAULT 'active',
    UNIQUE (lookup_category, lookup_key)
);

// Create index on lookup_category and lookup_key for faster lookups
CREATE INDEX idx_lookup_category_key ON lookup_data (lookup_category, lookup_key);

-- Comments for columns:
-- id: The unique identifier for each lookup entry.
-- lookup_category: The category for a set of lookups (e.g., 'USER_ROLES', 'DOCUMENT_TYPES').
-- lookup_key: The machine-readable key for a lookup value (e.g., 'ADMIN', 'USER_AGREEMENT').
-- lookup_label: The human-readable label for a lookup value (e.g., 'Administrator', 'User Agreement').
-- lookup_status: The status of the lookup value, using the hdb_common_status_enum type.


INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('BANK_NAME', 'Bank Name', 'BANK_OF_BARODA', 'Bank of Baroda'),
('BANK_NAME', 'Bank Name', 'BANK_OF_INDIA', 'Bank of India'),
('BANK_NAME', 'Bank Name', 'BANK_OF_MAHARASHTRA', 'Bank of Maharashtra'),
('BANK_NAME', 'Bank Name', 'CANARA_BANK', 'Canara Bank'),
('BANK_NAME', 'Bank Name', 'CENTRAL_BANK_OF_INDIA', 'Central Bank of India'),
('BANK_NAME', 'Bank Name', 'INDIAN_BANK', 'Indian Bank'),
('BANK_NAME', 'Bank Name', 'INDIAN_OVERSEAS_BANK', 'Indian Overseas Bank'),
('BANK_NAME', 'Bank Name', 'PUNJAB_AND_SIND_BANK', 'Punjab and Sind Bank'),
('BANK_NAME', 'Bank Name', 'PUNJAB_NATIONAL_BANK', 'Punjab National Bank'),
('BANK_NAME', 'Bank Name', 'STATE_BANK_OF_INDIA', 'State Bank of India'),
('BANK_NAME', 'Bank Name', 'UCO_BANK', 'UCO Bank'),
('BANK_NAME', 'Bank Name', 'UNION_BANK_OF_INDIA', 'Union Bank of India'),
('BANK_NAME', 'Bank Name', 'AXIS_BANK', 'Axis Bank'),
('BANK_NAME', 'Bank Name', 'BANDHAN_BANK', 'Bandhan Bank'),
('BANK_NAME', 'Bank Name', 'CSB_BANK', 'CSB Bank'),
('BANK_NAME', 'Bank Name', 'CITY_UNION_BANK', 'City Union Bank'),
('BANK_NAME', 'Bank Name', 'DCB_BANK', 'DCB Bank'),
('BANK_NAME', 'Bank Name', 'DHANLAXMI_BANK', 'Dhanlaxmi Bank'),
('BANK_NAME', 'Bank Name', 'FEDERAL_BANK', 'Federal Bank'),
('BANK_NAME', 'Bank Name', 'HDFC_BANK', 'HDFC Bank'),
('BANK_NAME', 'Bank Name', 'ICICI_BANK', 'ICICI Bank'),
('BANK_NAME', 'Bank Name', 'IDBI_BANK', 'IDBI Bank'),
('BANK_NAME', 'Bank Name', 'IDFC_FIRST_BANK', 'IDFC First Bank'),
('BANK_NAME', 'Bank Name', 'INDUSIND_BANK', 'IndusInd Bank'),
('BANK_NAME', 'Bank Name', 'KARNATAKA_BANK', 'Karnataka Bank'),
('BANK_NAME', 'Bank Name', 'KARUR_VYSYA_BANK', 'Karur Vysya Bank'),
('BANK_NAME', 'Bank Name', 'KOTAK_MAHINDRA_BANK', 'Kotak Mahindra Bank'),
('BANK_NAME', 'Bank Name', 'NAINITAL_BANK', 'Nainital Bank'),
('BANK_NAME', 'Bank Name', 'RBL_BANK', 'RBL Bank'),
('BANK_NAME', 'Bank Name', 'SOUTH_INDIAN_BANK', 'South Indian Bank'),
('BANK_NAME', 'Bank Name', 'TAMILNAD_MERCANTILE_BANK','Tamilnad Mercantile Bank'),
('BANK_NAME', 'Bank Name', 'YES_BANK','Yes Bank'),
('BANK_NAME', 'Bank Name', 'JAMMU_AND_KASHMIR_BANK_LTD','Jammu & Kashmir Bank Ltd'),
('BANK_NAME', 'Bank Name', 'OTHER', 'Other');

INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('CITY_NAME', 'City', 'HYDERABAD', 'Hyderabad'),
('CITY_NAME', 'City', 'MUMBAI', 'Mumbai'),
('CITY_NAME', 'City', 'DELHI', 'Delhi'),
('CITY_NAME', 'City', 'BANGALORE', 'Bangalore'),
('CITY_NAME', 'City', 'KOLKATA', 'Kolkata'),
('CITY_NAME', 'City', 'PUNE', 'Pune'),
('CITY_NAME', 'City', 'AHMEDABAD', 'Ahmedabad'),
('CITY_NAME', 'City', 'JAIPUR', 'Jaipur'),
('CITY_NAME', 'City', 'SURAT', 'Surat'),
('CITY_NAME', 'City', 'LUCKNOW', 'Lucknow'),
('CITY_NAME', 'City', 'KANPUR', 'Kanpur'),
('CITY_NAME', 'City', 'NAGPUR', 'Nagpur'),
('CITY_NAME', 'City', 'PATNA', 'Patna'),
('CITY_NAME', 'City', 'INDORE', 'Indore'),
('CITY_NAME', 'City', 'BHOPAL', 'Bhopal'),
('CITY_NAME', 'City', 'VADODARA', 'Vadodara'),
('CITY_NAME', 'City', 'LUDHIANA', 'Ludhiana'),
('CITY_NAME', 'City', 'AGRA', 'Agra'),
('CITY_NAME', 'City', 'VARANASI', 'Varanasi'),
('CITY_NAME', 'City', 'AMRITSAR', 'Amritsar'),
('CITY_NAME', 'City', 'COIMBATORE', 'Coimbatore'),
('CITY_NAME', 'City', 'THIRUVANANTHAPURAM', 'Thiruvananthapuram'),
('CITY_NAME', 'City', 'GUWAHATI', 'Guwahati'),
('CITY_NAME', 'City', 'RANCHI', 'Ranchi'),
('CITY_NAME', 'City', 'OTHER', 'Other');

INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('GENDER', 'Gender', 'OTHER', 'Other'),
('GENDER', 'Gender', 'MALE', 'Male'),
('GENDER', 'Gender', 'FEMALE', 'Female'),
('GENDER', 'Gender', 'PREFER_NOT_TO_SAY', 'Prefer not to say'),

('PAYMENT_MODE', 'Payment mode', 'ONLINE', 'Online'),
('PAYMENT_MODE', 'Payment mode', 'OFFLINE', 'Offline'),
('PAYMENT_MODE', 'Payment mode', 'CHEQUE', 'Cheque'),
('PAYMENT_MODE', 'Payment mode', 'CARD_SWIPE', 'Card swipe'),
('PAYMENT_MODE', 'Payment mode', 'CASH', 'Cash'),
('PAYMENT_MODE', 'Payment mode', 'ONLINE_RAZORPAY', 'Online (Through Razorpay)'),
('PAYMENT_MODE', 'Payment mode', 'DIRECT_BANK_TRANSFER', 'Direct bank transfer (NEFT/RTGS)'),

('TSHIRT_SIZE', 'Tshirt size', 'XS', 'XS'),
('TSHIRT_SIZE', 'Tshirt size', 'S', 'S'),
('TSHIRT_SIZE', 'Tshirt size', 'M', 'M'),
('TSHIRT_SIZE', 'Tshirt size', 'L', 'L'),
('TSHIRT_SIZE', 'Tshirt size', 'XL', 'XL'),
('TSHIRT_SIZE', 'Tshirt size', 'XXL', 'XXL'),
('TSHIRT_SIZE', 'Tshirt size', 'XXXL', 'XXXL'),

('TRAVEL_PLAN', 'Travel Plan', 'FLIGHT', 'Flight'),
('TRAVEL_PLAN', 'Travel Plan', 'OWN_TRANSPORT', 'Own transport'),
('TRAVEL_PLAN', 'Travel Plan', 'CITY_DROP', 'City drop'),
('TRAVEL_PLAN', 'Travel Plan', 'CITY_PICK_UP', 'City pick up'),

('ID_TYPE', 'Id type', 'PASSPORT', 'Passport'),
('ID_TYPE', 'Id type', 'AADHAR', 'Aadhar'),
('ID_TYPE', 'Id type', 'DRIVING_LICENSE', 'Driving license'),
('ID_TYPE', 'Id type', 'VOTER_ID', 'Voter id'),
('ID_TYPE', 'Id type', 'PAN_CARD', 'Pan card');

INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('AIRLINE', 'Airline', 'EK', 'Emirates'),
('AIRLINE', 'Airline', 'EY', 'Etihad Airways'),
('AIRLINE', 'Airline', 'QR', 'Qatar Airways'),
('AIRLINE', 'Airline', 'SQ', 'Singapore Air'),
('AIRLINE', 'Airline', 'I5', 'Air Asia'),
('AIRLINE', 'Airline', 'AI', 'Air India'),
('AIRLINE', 'Airline', 'G8', 'Go First'),
('AIRLINE', 'Airline', '6E', 'IndiGo'),
('AIRLINE', 'Airline', 'SG', 'SpiceJet'),
('AIRLINE', 'Airline', 'UK', 'Vistara'),
('AIRLINE', 'Airline', 'OTHER', 'Other');


-- This script creates the program_type_section table.
-- This table stores sections and their configurations for a specific program type.

CREATE TABLE IF NOT EXISTS program_type_section (
    id SERIAL PRIMARY KEY,
    program_type_id INT NOT NULL,
    section_key VARCHAR(255) NOT NULL,
    section_label VARCHAR(1024) NOT NULL,
    display_order INT DEFAULT 0,
    dependency_key VARCHAR(255),
    section_status hdb_common_status_enum NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    created_by INT,
    updated_by INT,

    CONSTRAINT fk_pts_program_type
        FOREIGN KEY (program_type_id)
        REFERENCES program_type_v1(id) ON DELETE CASCADE,

    CONSTRAINT fk_pts_created_by
        FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT fk_pts_updated_by
        FOREIGN KEY (updated_by)
        REFERENCES users(id) ON DELETE SET NULL,

    UNIQUE (program_type_id, section_key)
);

-- Add comments for columns for better understanding
COMMENT ON TABLE program_type_section IS 'Stores sections and their configurations for a specific program type.';
COMMENT ON COLUMN program_type_section.id IS 'Unique identifier for each program type section.';
COMMENT ON COLUMN program_type_section.program_type_id IS 'Foreign key to the program_type_v1 table.';
COMMENT ON COLUMN program_type_section.section_key IS 'A machine-readable key for the section, unique per program type.';
COMMENT ON COLUMN program_type_section.section_label IS 'The human-readable label for the section.';
COMMENT ON COLUMN program_type_section.display_order IS 'The order in which the section should be displayed.';
COMMENT ON COLUMN program_type_section.dependency_key IS 'Registration status key, foreign key to lookup_data(key).';
COMMENT ON COLUMN program_type_section.section_status IS 'The status of the section (e.g., draft, active, inactive).';
COMMENT ON COLUMN program_type_section.created_at IS 'Timestamp when the record was created.';
COMMENT ON COLUMN program_type_section.updated_at IS 'Timestamp when the record was last updated.';
COMMENT ON COLUMN program_type_section.deleted_at IS 'Timestamp for soft deletion.';
COMMENT ON COLUMN program_type_section.created_by IS 'Foreign key to the users table for the creator.';
COMMENT ON COLUMN program_type_section.updated_by IS 'Foreign key to the users table for the last updater.';

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_pts_program_type_id ON program_type_section(program_type_id, section_key);

-- This script creates the program_type_question table.
-- This table stores questions and their configurations for a specific program type.

CREATE TABLE IF NOT EXISTS program_type_question (
    id SERIAL PRIMARY KEY,
    program_type_id INT NOT NULL,
    question_key VARCHAR(255) NOT NULL,
    question_label VARCHAR(1024) NOT NULL,
    display_order INT DEFAULT 0,
    section_key VARCHAR(255),
    question_type VARCHAR(50) NOT NULL,
    question_config JSONB,
    question_status hdb_common_status_enum NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    created_by INT,
    updated_by INT,

    CONSTRAINT fk_program_type
        FOREIGN KEY (program_type_id)
        REFERENCES program_type_v1(id) ON DELETE CASCADE,

    CONSTRAINT fk_created_by
        FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT fk_updated_by
        FOREIGN KEY (updated_by)
        REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT fk_section_key
        FOREIGN KEY (program_type_id, section_key)
        REFERENCES program_type_section(program_type_id, section_key) ON DELETE CASCADE,

    UNIQUE (program_type_id, question_key)
);

-- Add comments for columns for better understanding
COMMENT ON TABLE program_type_question IS 'Stores questions and their configurations for a specific program type.';
COMMENT ON COLUMN program_type_question.id IS 'Unique identifier for each program type question.';
COMMENT ON COLUMN program_type_question.program_type_id IS 'Foreign key to the program_type_v1 table.';
COMMENT ON COLUMN program_type_question.question_key IS 'A machine-readable key for the question, unique per program type.';
COMMENT ON COLUMN program_type_question.question_label IS 'The human-readable label for the question.';
COMMENT ON COLUMN program_type_question.display_order IS 'The order in which the question should be displayed.';
COMMENT ON COLUMN program_type_question.section_key IS 'A key to group questions into sections.';
COMMENT ON COLUMN program_type_question.question_type IS 'The type of the question (e.g., text, radio, checkbox).';
COMMENT ON COLUMN program_type_question.question_config IS 'JSONB field to store question-specific configurations (e.g., options for a radio button).';
COMMENT ON COLUMN program_type_question.question_status IS 'The status of the question (e.g., draft, active, inactive).';
COMMENT ON COLUMN program_type_question.created_at IS 'Timestamp when the record was created.';
COMMENT ON COLUMN program_type_question.updated_at IS 'Timestamp when the record was last updated.';
COMMENT ON COLUMN program_type_question.deleted_at IS 'Timestamp for soft deletion.';
COMMENT ON COLUMN program_type_question.created_by IS 'Foreign key to the users table for the creator.';
COMMENT ON COLUMN program_type_question.updated_by IS 'Foreign key to the users table for the last updater.';

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_ptq_program_type_id ON program_type_question(program_type_id, question_key);
CREATE INDEX IF NOT EXISTS idx_ptq_section_key ON program_type_question(program_type_id, section_key);

INSERT INTO program_type_section (id, program_type_id, section_key, section_label, display_order, section_status)
VALUES 
(1, 1, 'FS_BASICDETAILS', 'Basic Details', 1, 'active'),
(2, 1, 'FS_MAHATRIAQUESTIONS', 'Mahatria''s Questions', 2, 'active'),
(3, 1, 'FS_PAYMENTINVOICE', 'Payment & Invoice', 3, 'active'),
(4, 1, 'FS_TRAVELPLAN', 'Travel Plan & Goodies', 4, 'active');

INSERT INTO program_type_question (
  program_type_id,
  question_key,
  question_label,
  display_order,
  section_key,
  question_type,
  question_config,
  question_status
)
SELECT
  1                      AS program_type_id,
  q.binding_key          AS question_key,
  q.label                AS question_label,
  pq.display_order       AS display_order,
  fs.key                 AS section_key,
  q.typ                  AS question_type,
  q.config::jsonb        AS question_config,
  'active'::hdb_common_status_enum AS question_status
FROM hdb_program_question pq
JOIN hdb_question       q  ON pq.question_id      = q.id
JOIN hdb_form_section   fs ON pq.form_section_id  = fs.id
WHERE pq.program_id = 567
ORDER BY pq.form_section_id, pq.display_order;



-- 2025-07-04

ALTER TABLE public.hdb_registration_travel_plan 
ALTER COLUMN travel_plan_status SET DEFAULT 'pending'::travel_status_enum;

ALTER TABLE public.hdb_registration_travel_info 
ALTER COLUMN travel_info_status SET DEFAULT 'pending'::travel_status_enum;

-- 2025-07-05

-- This script creates the user_session table to manage user login sessions.

-- Create an ENUM type for session status to ensure data consistency.
CREATE TYPE user_session_status_enum AS ENUM (
  'initiated',
  'validated',
  'logged_out',
  'expired',
  'failed'
);

-- Create the user_session table.
-- This table stores session information for each user login.
CREATE TABLE IF NOT EXISTS user_session (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    phone_number VARCHAR(255),
    country_code VARCHAR(5),
    email VARCHAR(320),
    login_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    otp VARCHAR(6),
    attempt_count SMALLINT DEFAULT 0,
    jwt_token TEXT,
    session_status user_session_status_enum NOT NULL DEFAULT 'initiated',
    user_agent VARCHAR(1024),
    ip_address VARCHAR(45),
    expires_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_user_session_user
        FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

-- Add comments for columns for better understanding.
COMMENT ON TABLE user_session IS 'Stores user session information, including JWTs and OTPs.';
COMMENT ON COLUMN user_session.id IS 'Unique identifier for the user session.';
COMMENT ON COLUMN user_session.user_id IS 'Foreign key to the users table, identifying the user.';
COMMENT ON COLUMN user_session.phone_number IS 'The phone number used to initiate this session, if applicable.';
COMMENT ON COLUMN user_session.country_code IS 'The country code for the phone number used, if applicable.';
COMMENT ON COLUMN user_session.email IS 'The email address used to initiate this session, if applicable.';
COMMENT ON COLUMN user_session.login_at IS 'Timestamp of when the user logged in and the session was created.';
COMMENT ON COLUMN user_session.otp IS 'The one-time password sent for login, if applicable (6-digit alphanumeric).';
COMMENT ON COLUMN user_session.jwt_token IS 'The JWT bearer token for this session. Populated after successful validation.';
COMMENT ON COLUMN user_session.last_activity_at IS 'Timestamp of the last user activity for this session.';
COMMENT ON COLUMN user_session.session_status IS 'The current status of the session (e.g., initiated, validated, logged_out, expired, failed).';
COMMENT ON COLUMN user_session.expires_at IS 'Timestamp when the session is set to expire.';
COMMENT ON COLUMN user_session.attempt_count IS 'Number of OTP validation attempts for this session.';
COMMENT ON COLUMN user_session.user_agent IS 'Information about the user''s device, such as User-Agent, OS, etc.';
COMMENT ON COLUMN user_session.ip_address IS 'The IP address from which the session was initiated.';

-- Create indexes for better query performance.
CREATE INDEX IF NOT EXISTS idx_user_session_user_id ON user_session(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_user_session_phone_number ON user_session(country_code, phone_number) WHERE phone_number IS NOT NULL AND country_code IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_user_session_email ON user_session(email) WHERE email IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_session_jwt_token ON user_session(jwt_token) WHERE jwt_token IS NOT NULL;

-- 2025-07-07

-- This SQL script updates the display order of questions in the registration form section for program_id 599
-- with program_id 599 for UAT
-- This is to update the display order of questions in the registration form section for program_id 599
UPDATE hdb_program_question SET display_order = 0  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 328;
UPDATE hdb_program_question SET display_order = 1  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 329;
UPDATE hdb_program_question SET display_order = 2  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 330;
UPDATE hdb_program_question SET display_order = 3  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 331;
UPDATE hdb_program_question SET display_order = 4  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 332;
UPDATE hdb_program_question SET display_order = 5  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 381;
UPDATE hdb_program_question SET display_order = 6  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1123;
UPDATE hdb_program_question SET display_order = 7  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1079;
UPDATE hdb_program_question SET display_order = 8  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 333;
UPDATE hdb_program_question SET display_order = 9  WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1129;
UPDATE hdb_program_question SET display_order = 10 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1097;
UPDATE hdb_program_question SET display_order = 11 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1098;
UPDATE hdb_program_question SET display_order = 12 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1099;
UPDATE hdb_program_question SET display_order = 13 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 335;
UPDATE hdb_program_question SET display_order = 14 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1085;
UPDATE hdb_program_question SET display_order = 15 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1086;
UPDATE hdb_program_question SET display_order = 16 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 575;
UPDATE hdb_program_question SET display_order = 17 WHERE program_id = 599 AND form_section_id = 1 AND question_id = 1080;


-- without program_id for dev
-- This is to update the display order of questions in the registration form section
UPDATE hdb_program_question SET display_order = 0 WHERE form_section_id = 1 AND question_id = 328;
UPDATE hdb_program_question SET display_order = 1 WHERE form_section_id = 1 AND question_id = 329;
UPDATE hdb_program_question SET display_order = 2 WHERE form_section_id = 1 AND question_id = 330;
UPDATE hdb_program_question SET display_order = 3 WHERE form_section_id = 1 AND question_id = 331;
UPDATE hdb_program_question SET display_order = 4 WHERE form_section_id = 1 AND question_id = 332;
UPDATE hdb_program_question SET display_order = 8 WHERE form_section_id = 1 AND question_id = 333;
UPDATE hdb_program_question SET display_order = 9 WHERE form_section_id = 1 AND question_id = 1158;
UPDATE hdb_program_question SET display_order = 5 WHERE  form_section_id = 1 AND question_id = 381;
UPDATE hdb_program_question SET display_order = 6 WHERE  form_section_id = 1 AND question_id = 1125;
UPDATE hdb_program_question SET display_order = 13 WHERE form_section_id = 1 AND question_id = 335;
UPDATE hdb_program_question SET display_order = 16 WHERE form_section_id = 1 AND question_id = 575;
UPDATE hdb_program_question SET display_order = 7 WHERE  form_section_id = 1 AND question_id = 1079;
UPDATE hdb_program_question SET display_order = 17 WHERE form_section_id = 1 AND question_id = 1080;
UPDATE hdb_program_question SET display_order = 14 WHERE form_section_id = 1 AND question_id = 1085;
UPDATE hdb_program_question SET display_order = 15 WHERE form_section_id = 1 AND question_id = 1086;
UPDATE hdb_program_question SET display_order = 10 WHERE form_section_id = 1 AND question_id = 1097;
UPDATE hdb_program_question SET display_order = 11 WHERE form_section_id = 1 AND question_id = 1098;
UPDATE hdb_program_question SET display_order = 12 WHERE form_section_id = 1 AND question_id = 1099;

-- This SQL script updates the display order of questions in the registration form section for program_id 759
-- with program_id 759 for DEV
UPDATE hdb_program_question SET display_order = 0 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 328;
UPDATE hdb_program_question SET display_order = 1 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 329;
UPDATE hdb_program_question SET display_order = 2 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 330;
UPDATE hdb_program_question SET display_order = 3 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 331;
UPDATE hdb_program_question SET display_order = 4 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 332;
UPDATE hdb_program_question SET display_order = 8 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 333;
UPDATE hdb_program_question SET display_order = 9 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1158;
UPDATE hdb_program_question SET display_order = 5 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 381;
UPDATE hdb_program_question SET display_order = 6 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1125;
UPDATE hdb_program_question SET display_order = 13 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 335;
UPDATE hdb_program_question SET display_order = 16 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 575;
UPDATE hdb_program_question SET display_order = 7 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1079;
UPDATE hdb_program_question SET display_order = 17 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1080;
UPDATE hdb_program_question SET display_order = 14 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1085;
UPDATE hdb_program_question SET display_order = 15 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1086;
UPDATE hdb_program_question SET display_order = 10 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1097;
UPDATE hdb_program_question SET display_order = 11 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1098;
UPDATE hdb_program_question SET display_order = 12 WHERE program_id = 759 AND form_section_id = 1 AND question_id = 1099;

-- add isRequired to the question
select * from public.hdb_question where created_by =-2 and form_Section_id = (3) and id in (1058,1057,357,356,354,355,1096,1059,353)


-- 2025-07-15

CREATE TABLE hdb_registration_approval_track (
    id BIGSERIAL PRIMARY KEY,
    approval_id BIGINT NOT NULL REFERENCES hdb_registration_approval(id),
    registration_id BIGINT NOT NULL REFERENCES hdb_program_registration(id),
    approval_status approval_status_enum NOT NULL DEFAULT 'pending',
    approval_date TIMESTAMPTZ NULL,
    approved_by BIGINT NULL REFERENCES users(id),
    rejection_reason TEXT NULL,
    reviewer_comments TEXT NULL,
    auto_approved BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
    updated_by BIGINT NULL REFERENCES users(id) ON DELETE SET NULL
);


  ALTER TABLE hdb_registration_approval_track
    ADD COLUMN type VARCHAR(255) DEFAULT 'registration',
    ADD COLUMN swap_request_id BIGINT;


-- 2025-07-21

ALTER TABLE hdb_registration_travel_plan
  ADD COLUMN IF NOT EXISTS departure_datetime TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS departure_flight_number VARCHAR(20),
  ADD COLUMN IF NOT EXISTS departure_airline_name VARCHAR(100),
  ADD COLUMN IF NOT EXISTS arrival_from VARCHAR(255),
  ADD COLUMN IF NOT EXISTS departure_to VARCHAR(255);


—-------------------
CREATE TABLE hdb_program_registration_rm_rating_type(
	id BIGINT PRIMARY KEY,
	rating_key VARCHAR(255) UNIQUE NOT NULL,
	name VARCHAR(255) NOT NULL,
	created_at TIMESTAMP WITHOUT TIME ZONE,
	updated_at TIMESTAMP WITHOUT TIME ZONE,
	created_by BIGINT,
	updated_by BIGINT,
	FOREIGN KEY (created_by) REFERENCES users(id),
	FOREIGN KEY (updated_by) REFERENCES users(id)
);

INSERT INTO hdb_program_registration_rm_rating_type VALUES
	(1, 'passion', 'Passion', '2025-06-14 12:02:58.831501'),
	(2, 'growth', 'Growth', '2025-06-14 12:02:58.831501'),
	(3, 'infinitheist', 'Infinitheist', '2025-06-14 12:02:58.831501'),
	(4, 'continuity', 'Continuity', '2025-06-14 12:02:58.831501');

CREATE TABLE hdb_program_registration_rm_rating(
	id SERIAL PRIMARY KEY,
	program_registration_id BIGINT, 
	rm_id BIGINT NOT NULL,
	rating_key VARCHAR(255) NOT NULL,
	rating DECIMAL DEFAULT 0,
	created_at TIMESTAMP WITHOUT TIME ZONE,
	updated_at TIMESTAMP WITHOUT TIME ZONE,
	deleted_at TIMESTAMP WITHOUT TIME ZONE,
	created_by BIGINT,
	updated_by BIGINT,
	FOREIGN KEY (program_registration_id) REFERENCES hdb_program_registration(id),
	FOREIGN KEY (rm_id) REFERENCES users(id),
	FOREIGN KEY (rating_key) REFERENCES hdb_program_registration_rm_rating_type(rating_key),
	FOREIGN KEY (created_by) REFERENCES users(id),
	FOREIGN KEY (updated_by) REFERENCES users(id)
);

ALTER TABLE hdb_program_registration
	ADD COLUMN rm_review TEXT;


CREATE TABLE hdb_program_registration_swap(
	id SERIAL PRIMARY KEY,
	program_registration_id BIGINT,
	type VARCHAR(255),
	status VARCHAR(255),
	comment TEXT
	created_at TIMESTAMP WITHOUT TIME ZONE,
	updated_at TIMESTAMP WITHOUT TIME ZONE,
	created_by BIGINT,
	updated_by BIGINT,
	target_program_id BIGINT,
	FOREIGN KEY (created_by) REFERENCES users(id),
	FOREIGN KEY (updated_by) REFERENCES users(id),
);

ALTER TABLE public.hdb_program_registration_swap
	ADD COLUMN allocated_program_id BIGINT,
	ADD COLUMN current_program_id BIGINT,
	ADD CONSTRAINT fk_current_program FOREIGN KEY (current_program_id)
    REFERENCES program_v1(id),
	ADD CONSTRAINT fk_allocated_program FOREIGN KEY (allocated_program_id)
    REFERENCES program_v1(id);


CREATE TABLE hdb_swap_requested_program(
	id SERIAL PRIMARY KEY,
	swap_request_id BIGINT,
	program_id BIGINT,
	created_at TIMESTAMP WITHOUT TIME ZONE,
	updated_at TIMESTAMP WITHOUT TIME ZONE,
	created_by BIGINT,
	updated_by BIGINT,
	FOREIGN KEY (swap_request_id) REFERENCES hdb_program_registration_swap(id),
	FOREIGN KEY (program_id) REFERENCES program_v1(id),
	FOREIGN KEY (created_by) REFERENCES users(id),
	FOREIGN KEY (updated_by) REFERENCES users(id)
)


-- Create enum types
CREATE TYPE IF NOT EXISTS address_type_enum AS ENUM ('billing_address', 'invoice_address', 'user_address');
CREATE TYPE IF NOT EXISTS tds_applicability_enum AS ENUM ('base_only', 'base_plus_tax');

-- Address table
CREATE TABLE IF NOT EXISTS public.address (
    address_id SERIAL PRIMARY KEY,
    location_id INTEGER,
    addr_1 VARCHAR(255),
    addr_2 VARCHAR(255),
    landmark VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    pincode VARCHAR(20),
    lat NUMERIC(9,6),
    "long" NUMERIC(9,6),
    type address_type_enum,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255),
    updated_by VARCHAR(255)
);

-- Program table alterations
ALTER TABLE program_v1
  ADD COLUMN IF NOT EXISTS tds_applicability tds_applicability_enum,
  ADD COLUMN IF NOT EXISTS invoice_sender_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS invoice_sender_pan VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_cin VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_address_id INTEGER;

ALTER TABLE program_v1
  ADD CONSTRAINT IF NOT EXISTS fk_program_invoice_address FOREIGN KEY (invoice_sender_address_id)
    REFERENCES address(address_id);

-- Program session table alterations
ALTER TABLE program_session
  ADD COLUMN IF NOT EXISTS tds_applicability tds_applicability_enum,
  ADD COLUMN IF NOT EXISTS invoice_sender_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS invoice_sender_pan VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_cin VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_address_id INTEGER;

ALTER TABLE program_session
  ADD CONSTRAINT IF NOT EXISTS fk_session_invoice_address FOREIGN KEY (invoice_sender_address_id)
    REFERENCES address(address_id);

ALTER TABLE program_type_v1
    ADD COLUMN  igst numeric(5,2) DEFAULT 0,
    ADD COLUMN  cgst numeric(5,2) DEFAULT 0,
    ADD COLUMN  sgst numeric(5,2) DEFAULT 0,
    ADD COLUMN  gst_percentage numeric(5,2) DEFAULT 18.00,
    ADD COLUMN  tds_percent numeric(5,2) DEFAULT 0,
    ADD COLUMN  gst_number character varying(50) COLLATE pg_catalog."default",
    ADD COLUMN  tds_applicability tds_applicability_enum,
    ADD COLUMN  invoice_sender_name character varying(255) COLLATE pg_catalog."default",
    ADD COLUMN  invoice_sender_pan character varying(50) COLLATE pg_catalog."default",
    ADD COLUMN  invoice_sender_cin character varying(50) COLLATE pg_catalog."default",
    ADD COLUMN  invoice_sender_address text COLLATE pg_catalog."default",
    ADD COLUMN  helpline_number text COLLATE pg_catalog."default",
    ADD COLUMN  email_sender_name text COLLATE pg_catalog."default",
    ADD COLUMN  venue_name_in_emails text COLLATE pg_catalog."default";


-- ENUM for hdb_option.type
CREATE TYPE hdb_option_type_enum AS ENUM (
  'string',
  'number',
  'boolean',
  'date',
  'timestamp',
  'image',
  'file',
  'object',
  'enum',
  'color'
);

-- Common ENUM for status
CREATE TYPE hdb_common_status_enum AS ENUM (
  'draft',
  'active',
  'inactive',
  'published',
  'archived'
);

-- hdb_form_section Table
CREATE TABLE hdb_form_section (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ DEFAULT NULL,

  created_by INTEGER,
  updated_by INTEGER,

  CONSTRAINT fk_created_by FOREIGN KEY (created_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);


INSERT INTO hdb_form_section (name, description, created_by, updated_by)
VALUES
  ('Basic Details', 'Section for basic user details', 8319, 8319),
  ('Invoice', 'Section for invoice information', 8319, 8319),
  ('Travel', 'Section for travel-related details', 8319, 8319);

-- hdb_question Table
CREATE TABLE hdb_question (
    id SERIAL PRIMARY KEY,
    label VARCHAR(1024) NOT NULL,
    config JSONB,
    stats hdb_common_status_enum NOT NULL DEFAULT 'draft', -- Use common status ENUM
    typ VARCHAR(50) NOT NULL,

    form_section_id INTEGER, -- Nullable, links to hdb_form_section

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,

    created_by INTEGER,
    updated_by INTEGER,

    CONSTRAINT fk_form_section FOREIGN KEY (form_section_id)
        REFERENCES public.hdb_form_section (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_created_by FOREIGN KEY (created_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- hdb_option_category Table
CREATE TABLE hdb_option_category (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ DEFAULT NULL, -- Soft delete column

  created_by INTEGER,
  updated_by INTEGER,

  CONSTRAINT fk_created_by FOREIGN KEY (created_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- hdb_option Table
CREATE TABLE hdb_option (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  typ hdb_option_type_enum NOT NULL,
  category_id INTEGER,

  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ, -- Soft delete column

  stats hdb_common_status_enum DEFAULT 'draft', -- Use common status ENUM

  created_by INTEGER,
  updated_by INTEGER,

  CONSTRAINT fk_category FOREIGN KEY (category_id)
      REFERENCES public.hdb_option_category (id) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_created_by FOREIGN KEY (created_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
      REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- hdb_question_option_map Table
CREATE TABLE hdb_question_option_map (
    id SERIAL PRIMARY KEY,
    
    question_id INTEGER NOT NULL,
    option_id INTEGER NOT NULL,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,

    created_by INTEGER,
    updated_by INTEGER,

    CONSTRAINT fk_question FOREIGN KEY (question_id)
        REFERENCES public.hdb_question (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_option FOREIGN KEY (option_id)
        REFERENCES public.hdb_option (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_created_by FOREIGN KEY (created_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);


-- hdb_program_question Table
CREATE TABLE hdb_program_question (
    id SERIAL PRIMARY KEY,
    
    program_id INTEGER NOT NULL,
    question_id INTEGER NOT NULL,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,

    created_by INTEGER,
    updated_by INTEGER,

    CONSTRAINT fk_program FOREIGN KEY (program_id)
        REFERENCES public.program_v1 (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_question FOREIGN KEY (question_id)
        REFERENCES public.hdb_question (id) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_created_by FOREIGN KEY (created_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT fk_updated_by FOREIGN KEY (updated_by)
        REFERENCES public.users (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- drop table hdb_question,hdb_option_category,hdb_option,hdb_question_option_map,hdb_program_question

-- Alter commands for program_v1 table
ALTER TABLE program_v1
ADD COLUMN program_fee DECIMAL(10, 2),
ADD COLUMN is_approval_required BOOLEAN DEFAULT FALSE,
ADD COLUMN is_residence_required BOOLEAN DEFAULT FALSE,
ADD COLUMN is_travel_involved BOOLEAN DEFAULT FALSE,
ADD COLUMN cgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN sgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN igst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN max_capacity INT,
ADD COLUMN created_by INT,
ADD COLUMN updated_by INT,
ADD COLUMN deleted_at TIMESTAMP,
ADD COLUMN tds_percent DECIMAL(5,2) DEFAULT 0, -- Added
ADD COLUMN gst_number VARCHAR(50),             -- Added
ADD COLUMN checkin_ends_at TIMESTAMPTZ,
ADD COLUMN checkout_ends_at TIMESTAMPTZ;
ADD COLUMN logo_url VARCHAR(1024); -- Added logo URL column

ALTER TABLE program_session
ADD COLUMN created_by INT,
ADD COLUMN updated_by INT,
ADD COLUMN deleted_at TIMESTAMP,
ADD COLUMN cgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN sgst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN igst DECIMAL(5,2) DEFAULT 0,
ADD COLUMN tds_percent DECIMAL(5,2) DEFAULT 0, -- Added
ADD COLUMN gst_number VARCHAR(50),             -- Added
ADD COLUMN checkin_ends_at TIMESTAMPTZ,
ADD COLUMN checkout_ends_at TIMESTAMPTZ;
ADD COLUMN logo_url VARCHAR(1024);

ALTER TABLE program_type_v1
ADD COLUMN deleted_at TIMESTAMP;


-- Create ENUM types
CREATE TYPE gender_enum AS ENUM ('male', 'female', 'other', 'prefer_not_to_say');
CREATE TYPE invoice_type_enum AS ENUM ('online', 'offline');
CREATE TYPE payment_mode_enum AS ENUM ('online', 'offline');
CREATE TYPE id_type_enum AS ENUM ('passport', 'aadhar', 'driving_license', 'voter_id', 'pan_card');
CREATE TYPE tshirt_size_enum AS ENUM ('XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL');
CREATE TYPE travel_type_enum AS ENUM ('flight', 'own_transport', 'city_pick_up', 'city_drop');
CREATE TYPE approval_status_enum AS ENUM ('pending', 'approved', 'rejected', 'under_review');
CREATE TYPE registration_status_enum AS ENUM ('draft', 'pending_approval', 'waitlisted', 'completed', 'cancelled');
CREATE TYPE basic_details_status_enum AS ENUM ('draft', 'approved', 'waitlisted');
CREATE TYPE payment_status_enum AS ENUM ('draft', 'online_pending', 'online_completed', 'offline_pending', 'offline_completed');
CREATE TYPE invoice_status_enum AS ENUM ('draft', 'invoice_pending', 'invoice_completed');
CREATE TYPE travel_status_enum AS ENUM ('draft', 'pending', 'completed');

-- hdb_program_registration Table (Main Registration Table)
CREATE TABLE hdb_program_registration (
    id BIGSERIAL PRIMARY KEY,
    program_session_id BIGINT NOT NULL,
    user_id BIGINT NULL,
    program_registration_seq_number INTEGER NULL,
    registration_number VARCHAR(50) NULL,
    waiting_list_seq_number INTEGER NULL,
    registration_status registration_status_enum NOT NULL DEFAULT 'draft',
    basic_details_status basic_details_status_enum NOT NULL DEFAULT 'draft',
    registration_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cancellation_date TIMESTAMP NULL,
    cancelled_by BIGINT NULL,
    rm_contact BIGINT NULL,
    preferred_room_mate VARCHAR(255) NULL,
    
    -- Basic Details
    full_name VARCHAR(255) NOT NULL,
    gender gender_enum NOT NULL,
    mobile_number VARCHAR(20) NOT NULL,
    email_address VARCHAR(255) NOT NULL,
    dob DATE NULL,
    city VARCHAR(100) NULL,
    notes TEXT NULL,
    terms_accepted BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_invoice_detail Table (Fixed table name)
CREATE TABLE hdb_registration_invoice_detail (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    invoice_type invoice_type_enum NOT NULL,
    invoice_name VARCHAR(255) NOT NULL,
    invoice_email VARCHAR(255) NOT NULL,
    pan_number VARCHAR(20) NULL,
    is_gst_registered BOOLEAN NOT NULL DEFAULT FALSE,
    invoice_address TEXT NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    invoice_sequence_number VARCHAR(50) NULL,
    razorpay_id VARCHAR(100) NULL,
    invoice_issued_date TIMESTAMP NULL,
    invoice_status invoice_status_enum NOT NULL DEFAULT 'draft',
    tds_applicable BOOLEAN NOT NULL DEFAULT FALSE,
    tan_number VARCHAR(20) NULL,
    handover_date TIMESTAMP NULL,
    handover_to VARCHAR(255) NULL,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_payment_detail Table (Fixed table name)
CREATE TABLE hdb_registration_payment_detail (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    program_session_id BIGINT NOT NULL,
    payment_mode payment_mode_enum NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tds DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    sub_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    razorpay_id VARCHAR(100) NULL,
    offline_meta JSONB NULL,
    original_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    gst_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_status payment_status_enum NOT NULL DEFAULT 'draft',
    payment_date TIMESTAMP NULL,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_travel_info Table
CREATE TABLE hdb_registration_travel_info (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    id_type id_type_enum NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    id_picture_url VARCHAR(500) NULL,
    user_picture_url VARCHAR(500) NULL,
    tshirt_size tshirt_size_enum NULL,
    travel_info_status travel_status_enum NOT NULL DEFAULT 'draft',
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_travel_plan Table
CREATE TABLE hdb_registration_travel_plan (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    travel_type travel_type_enum NOT NULL,
    airline_name VARCHAR(100) NULL,
    flight_number VARCHAR(20) NULL,
    arrival_datetime TIMESTAMP NULL,
    departure_location VARCHAR(255) NULL,
    pickup_time TIME NULL,
    pickup_location VARCHAR(255) NULL,
    checkin_at TIME NULL,
    checkin_location VARCHAR(255) NULL,
    travel_plan_status travel_status_enum NOT NULL DEFAULT 'draft',
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_custom_response Table
CREATE TABLE hdb_registration_custom_response (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    question_id BIGINT NOT NULL,
    response_value JSONB NULL,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_question_answer Table
CREATE TABLE hdb_registration_question_answer (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    question_id BIGINT NOT NULL,
    answer_value JSONB NULL,

    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- hdb_registration_approval Table
CREATE TABLE hdb_registration_approval (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    approval_status approval_status_enum NOT NULL DEFAULT 'pending',
    approval_date TIMESTAMP NULL,
    approved_by BIGINT NULL,
    rejection_reason TEXT NULL,
    reviewer_comments TEXT NULL,
    auto_approved BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL
);

-- Create Indexes for hdb_program_registration
CREATE INDEX idx_hdb_program_session ON hdb_program_registration(program_session_id);
CREATE INDEX idx_hdb_user ON hdb_program_registration(user_id);
CREATE INDEX idx_hdb_registration_date ON hdb_program_registration(registration_date);
CREATE INDEX idx_hdb_rm_contact ON hdb_program_registration(rm_contact);
CREATE INDEX idx_hdb_cancelled_by ON hdb_program_registration(cancelled_by);
CREATE INDEX idx_hdb_created_by ON hdb_program_registration(created_by);
CREATE INDEX idx_hdb_updated_by ON hdb_program_registration(updated_by);

-- Create Indexes for hdb_registration_invoice_detail
CREATE INDEX idx_hdb_invoice_registration ON hdb_registration_invoice_detail(registration_id);
CREATE INDEX idx_hdb_invoice_type ON hdb_registration_invoice_detail(invoice_type);
CREATE INDEX idx_hdb_invoice_sequence ON hdb_registration_invoice_detail(invoice_sequence_number);
CREATE INDEX idx_hdb_invoice_razorpay_id ON hdb_registration_invoice_detail(razorpay_id);
CREATE INDEX idx_hdb_invoice_issued_date ON hdb_registration_invoice_detail(invoice_issued_date);

-- Create Indexes for hdb_registration_payment_detail
CREATE INDEX idx_hdb_payment_registration ON hdb_registration_payment_detail(registration_id);
CREATE INDEX idx_hdb_payment_program_session ON hdb_registration_payment_detail(program_session_id);
CREATE INDEX idx_hdb_payment_mode ON hdb_registration_payment_detail(payment_mode);
CREATE INDEX idx_hdb_payment_razorpay_id ON hdb_registration_payment_detail(razorpay_id);
CREATE INDEX idx_hdb_payment_date ON hdb_registration_payment_detail(payment_date);

-- Create Indexes for hdb_registration_travel_info
CREATE INDEX idx_hdb_travel_info_registration ON hdb_registration_travel_info(registration_id);
CREATE INDEX idx_hdb_travel_info_id_type ON hdb_registration_travel_info(id_type);

-- Create Indexes for hdb_registration_travel_plan
CREATE INDEX idx_hdb_travel_plan_registration ON hdb_registration_travel_plan(registration_id);
CREATE INDEX idx_hdb_travel_plan_type ON hdb_registration_travel_plan(travel_type);
CREATE INDEX idx_hdb_travel_plan_arrival ON hdb_registration_travel_plan(arrival_datetime);

-- Create Indexes for hdb_registration_custom_response
CREATE INDEX idx_hdb_custom_response_registration ON hdb_registration_custom_response(registration_id);
CREATE INDEX idx_hdb_custom_response_question ON hdb_registration_custom_response(question_id);

-- Create Indexes for hdb_registration_question_answer
CREATE INDEX idx_hdb_question_answer_registration ON hdb_registration_question_answer(registration_id);
CREATE INDEX idx_hdb_question_answer_question ON hdb_registration_question_answer(question_id);

-- Create Indexes for hdb_registration_approval
CREATE INDEX idx_hdb_approval_registration ON hdb_registration_approval(registration_id);
CREATE INDEX idx_hdb_approval_status ON hdb_registration_approval(approval_status);
CREATE INDEX idx_hdb_approval_approved_by ON hdb_registration_approval(approved_by);
CREATE INDEX idx_hdb_approval_date ON hdb_registration_approval(approval_date);

-- Add Foreign Key Constraints for hdb_program_registration
-- Note: Assuming these reference tables exist: program_session, users
ALTER TABLE hdb_program_registration
ADD CONSTRAINT fk_hdb_program_session FOREIGN KEY (program_session_id) REFERENCES program_session(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_hdb_rm_contact FOREIGN KEY (rm_contact) REFERENCES users(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_hdb_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_hdb_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- Add Foreign Key Constraints for hdb_registration_invoice_detail
ALTER TABLE hdb_registration_invoice_detail
ADD CONSTRAINT fk_hdb_invoice_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_payment_detail
ALTER TABLE hdb_registration_payment_detail
ADD CONSTRAINT fk_hdb_payment_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_payment_program_session FOREIGN KEY (program_session_id) REFERENCES program_session(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_travel_info
ALTER TABLE hdb_registration_travel_info
ADD CONSTRAINT fk_hdb_travel_info_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_travel_plan
ALTER TABLE hdb_registration_travel_plan
ADD CONSTRAINT fk_hdb_travel_plan_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_custom_response
-- Note: Assuming hdb_question table exists (referenced as 'question' in entity)
ALTER TABLE hdb_registration_custom_response
ADD CONSTRAINT fk_hdb_custom_response_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_custom_response_question FOREIGN KEY (question_id) REFERENCES hdb_question(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_question_answer
ALTER TABLE hdb_registration_question_answer
ADD CONSTRAINT fk_hdb_question_answer_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_question_answer_question FOREIGN KEY (question_id) REFERENCES hdb_question(id) ON DELETE CASCADE;

-- Add Foreign Key Constraints for hdb_registration_approval
ALTER TABLE hdb_registration_approval
ADD CONSTRAINT fk_hdb_approval_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_hdb_approval_approved_by FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL;

-- Add Unique Constraints
ALTER TABLE hdb_program_registration
ADD CONSTRAINT unique_hdb_registration_seq UNIQUE (program_session_id, program_registration_seq_number),
ADD CONSTRAINT unique_hdb_waitlist_seq UNIQUE (program_session_id, waiting_list_seq_number);

ALTER TABLE hdb_registration_invoice_detail
ADD CONSTRAINT unique_hdb_invoice_sequence UNIQUE (invoice_sequence_number);

ALTER TABLE hdb_registration_custom_response
ADD CONSTRAINT unique_hdb_registration_question UNIQUE (registration_id, question_id);

ALTER TABLE hdb_registration_question_answer
ADD CONSTRAINT unique_hdb_question_answer UNIQUE (registration_id, question_id);


-- PROGRAM REGISTRATION
-- hdb_program_registration
ALTER TABLE hdb_program_registration
  ADD COLUMN registration_status registration_status_enum DEFAULT 'draft',
  ADD COLUMN basic_details_status basic_details_status_enum DEFAULT 'draft';
-- hdb_registration_invoice_detail
ALTER TABLE hdb_registration_invoice_detail
  ADD COLUMN invoice_status invoice_status_enum DEFAULT 'draft';
-- hdb_registration_payment_detail
ALTER TABLE hdb_registration_payment_detail
  ADD COLUMN payment_status payment_status_enum DEFAULT 'draft';

-- hdb_registration_travel_info
ALTER TABLE hdb_registration_travel_info
  ADD COLUMN travel_info_status travel_status_enum DEFAULT 'draft';
-- hdb_registration_travel_plan
ALTER TABLE hdb_registration_travel_plan
  ADD COLUMN travel_plan_status travel_status_enum DEFAULT 'draft';







CREATE TYPE approval_status_enum AS ENUM ('pending', 'approved', 'rejected', 'under_review');
CREATE TYPE registration_status_enum AS ENUM ('draft', 'pending_approval','pending', 'waitlisted', 'completed', 'cancelled');
CREATE TYPE basic_details_status_enum AS ENUM ('draft', 'pending', 'waitlisted', 'completed', 'rejected');
CREATE TYPE payment_status_enum AS ENUM ('draft', 'online_pending', 'online_completed', 'offline_pending', 'offline_completed');
CREATE TYPE invoice_status_enum AS ENUM ('draft', 'invoice_pending', 'invoice_completed');
CREATE TYPE travel_status_enum AS ENUM ('draft', 'pending', 'completed');



SELECT e.enumlabel AS value
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'approval_status_enum';


 CREATE TABLE user_role (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_role_map (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES user_role(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_role (name) VALUES
  ('viewer'),
  ('admin'),
  ('mahatria'),
  ('rm');

ALTER TYPE program_status_enum ADD VALUE 'published';

ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Cheque';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Direct bank transfer (NEFT/RTGS)';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Card swipe';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Cash';
ALTER TYPE payment_mode_enum ADD VALUE IF NOT EXISTS 'Online (Through Razorpay)';

-- hdb_registration_payment_details_history Table
CREATE TABLE hdb_registration_payment_details_history ( 
    id BIGSERIAL PRIMARY KEY,
    payment_details_id BIGINT NOT NULL REFERENCES hdb_registration_payment_detail(id),
    payment_mode payment_mode_enum,
    razorpay_id VARCHAR(100),
    offline_meta JSONB,
    payment_status payment_status_enum DEFAULT 'draft'::payment_status_enum,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by BIGINT,
    updated_by BIGINT
);

ALTER TABLE hdb_form_section ADD COLUMN display_order INTEGER DEFAULT 0;

-- hdb_preference Table
CREATE TABLE hdb_preference (
    id BIGSERIAL PRIMARY KEY,
    session_id BIGINT NOT NULL,
    program_id INT NOT NULL,
    registration_id BIGINT NOT NULL,
    priority_order INT DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ DEFAULT NULL,

    created_by INT,
    updated_by INT
);

-- Add Foreign Key Constraints for hdb_preference
ALTER TABLE hdb_preference
    ADD CONSTRAINT fk_preference_session FOREIGN KEY (session_id) REFERENCES program_session_v1(id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_preference_program FOREIGN KEY (program_id) REFERENCES program_v2(id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_preference_registration FOREIGN KEY (registration_id) REFERENCES hdb_program_registration(id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_preference_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_preference_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- communication_type_enum and communication_status_enum
create type communication_type_enum AS ENUM ('whatsapp','email');
create type communication_status_enum AS ENUM ('initiated','success','failed');


-- hdb_communication_track Table
CREATE TABLE hdb_communication_track (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    typ communication_type_enum,
    stats communication_status_enum not null default 'initiated',
	failed_reason text,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT,
    updated_by BIGINT,

    CONSTRAINT fk_hdb_communication_track
        FOREIGN KEY (registration_id)
        REFERENCES public.hdb_program_registration (id)
        ON UPDATE NO ACTION
        ON DELETE CASCADE,

    CONSTRAINT fk_hdb_communication_track_created_by 
        FOREIGN KEY (created_by)
        REFERENCES public.users (id)
        ON UPDATE NO ACTION
        ON DELETE SET NULL,

    CONSTRAINT fk_hdb_communication_track_updated_by 
        FOREIGN KEY (updated_by)
        REFERENCES public.users (id)
        ON UPDATE NO ACTION
        ON DELETE SET NULL
);


ALTER TABLE program_v1
  RENAME COLUMN invoice_sender_address_id TO venue_address_id;

ALTER TABLE program_v1
  DROP CONSTRAINT IF EXISTS fk_program_invoice_address;

ALTER TABLE program_v1
  ADD CONSTRAINT fk_program_venue_address FOREIGN KEY (venue_address_id)
    REFERENCES hdb_address(address_id);

ALTER TABLE program_v1
  ADD COLUMN invoice_sender_address TEXT;

ALTER TABLE program_session
  RENAME COLUMN invoice_sender_address_id TO venue_address_id;

ALTER TABLE program_session
  DROP CONSTRAINT IF EXISTS fk_session_invoice_address;

ALTER TABLE program_session
  ADD CONSTRAINT fk_session_venue_address FOREIGN KEY (venue_address_id)
    REFERENCES hdb_address(address_id);

ALTER TABLE program_session
  ADD COLUMN invoice_sender_address TEXT;




-- Add SAVE_AS_DRAFT and ATTEMPTED states
ALTER TYPE registration_status_enum ADD VALUE IF NOT EXISTS 'save_as_draft';
ALTER TYPE registration_status_enum ADD VALUE IF NOT EXISTS 'attempted';

-- Add SAVE_AS_DRAFT for basic details, payment, invoice and travel statuses
ALTER TYPE basic_details_status_enum ADD VALUE IF NOT EXISTS 'save_as_draft';
ALTER TYPE payment_status_enum ADD VALUE IF NOT EXISTS 'save_as_draft';
ALTER TYPE invoice_status_enum ADD VALUE IF NOT EXISTS 'save_as_draft';
ALTER TYPE travel_status_enum ADD VALUE IF NOT EXISTS 'save_as_draft';


ALTER TABLE program_v1
  DROP COLUMN IF EXISTS checkin_date,
  DROP COLUMN IF EXISTS checkin_time,
  DROP COLUMN IF EXISTS checkout_date,
  DROP COLUMN IF EXISTS checkout_time,
  ADD COLUMN IF NOT EXISTS checkin_at TIMESTAMPTZ NULL,
  ADD COLUMN IF NOT EXISTS checkout_at TIMESTAMPTZ NULL,
  ADD COLUMN IF NOT EXISTS tds_percent DECIMAL(5,2) DEFAULT 0, -- Added
  ADD COLUMN IF NOT EXISTS gst_number VARCHAR(50);             -- Added

ALTER TABLE program_session
  DROP COLUMN IF EXISTS checkin_time,
  DROP COLUMN IF EXISTS checkout_time,
  ADD COLUMN IF NOT EXISTS checkin_at TIMESTAMPTZ NULL,
  ADD COLUMN IF NOT EXISTS checkout_at TIMESTAMPTZ NULL,
  ADD COLUMN IF NOT EXISTS tds_percent DECIMAL(5,2) DEFAULT 0, -- Added
  ADD COLUMN IF NOT EXISTS gst_number VARCHAR(50);             -- Added

-- program_v1 table changes
ALTER TABLE program_v1
  DROP COLUMN IF EXISTS registration_start_date,
  DROP COLUMN IF EXISTS registration_start_time,
  DROP COLUMN IF EXISTS registration_end_date,
  DROP COLUMN IF EXISTS registration_end_time,
  DROP COLUMN IF EXISTS checkin_date,
  DROP COLUMN IF EXISTS checkin_time,
  DROP COLUMN IF EXISTS checkout_date,
  DROP COLUMN IF EXISTS checkout_time,
  DROP COLUMN IF EXISTS start_date,
  DROP COLUMN IF EXISTS start_time,
  DROP COLUMN IF EXISTS end_date,
  DROP COLUMN IF EXISTS end_time,
  ADD COLUMN registration_starts_at TIMESTAMPTZ,
  ADD COLUMN registration_ends_at TIMESTAMPTZ,
  ADD COLUMN checkin_at TIMESTAMPTZ,
  ADD COLUMN checkout_at TIMESTAMPTZ,
  ADD COLUMN starts_at TIMESTAMPTZ,
  ADD COLUMN ends_at TIMESTAMPTZ;

-- program_session table changes
ALTER TABLE program_session
  DROP COLUMN IF EXISTS registration_start_date,
  DROP COLUMN IF EXISTS registration_start_time,
  DROP COLUMN IF EXISTS registration_end_date,
  DROP COLUMN IF EXISTS registration_end_time,
  DROP COLUMN IF EXISTS checkin_date,
  DROP COLUMN IF EXISTS checkin_time,
  DROP COLUMN IF EXISTS checkout_date,
  DROP COLUMN IF EXISTS checkout_time,
  DROP COLUMN IF EXISTS start_date,
  DROP COLUMN IF EXISTS start_time,
  DROP COLUMN IF EXISTS end_date,
  DROP COLUMN IF EXISTS end_time,
  ADD COLUMN registration_starts_at TIMESTAMPTZ,
  ADD COLUMN registration_ends_at TIMESTAMPTZ,
  ADD COLUMN checkin_at TIMESTAMPTZ,
  ADD COLUMN checkout_at TIMESTAMPTZ,
  ADD COLUMN starts_at TIMESTAMPTZ,
  ADD COLUMN ends_at TIMESTAMPTZ;

ALTER TABLE hdb_registration_travel_plan
  ADD COLUMN checkin_at TIMESTAMPTZ

ALTER TABLE hdb_registration_invoice_detail
  ADD COLUMN tds_amount numeric(10,2);


ALTER TABLE hdb_registration_invoice_detail
ADD COLUMN einvoice_qr_link VARCHAR(1024),
ADD COLUMN einvoice_ack_date VARCHAR(500),
ADD COLUMN einvoice_inv_ref_num VARCHAR(1024),
ADD COLUMN einvoice_is_cancellable BOOLEAN,
ADD COLUMN einvoice_status_formatted VARCHAR(1024),
ADD COLUMN einvoice_formatted_status VARCHAR(500),
ADD COLUMN einvoice_ack_number VARCHAR(500),
ADD COLUMN einvoice_status VARCHAR(500);

ALTER TABLE public.hdb_registration_invoice_detail
ADD COLUMN invoice_pdf_url VARCHAR(2048);

ALTER TABLE program_v1
  ADD COLUMN IF NOT EXISTS helpline_number TEXT,
  ADD COLUMN IF NOT EXISTS email_sender_name TEXT,
  ADD COLUMN IF NOT EXISTS venue_name_in_emails TEXT,
  ADD COLUMN IF NOT EXISTS launch_date TIMESTAMPTZ;

ALTER TABLE program_session
  ADD COLUMN IF NOT EXISTS helpline_number TEXT,
  ADD COLUMN IF NOT EXISTS email_sender_name TEXT,
  ADD COLUMN IF NOT EXISTS venue_name_in_emails TEXT,
  ADD COLUMN IF NOT EXISTS launch_date TIMESTAMPTZ;


CREATE TABLE IF NOT EXISTS feature_flag (
  id SERIAL PRIMARY KEY,
  feature_key VARCHAR(255) UNIQUE NOT NULL,
  label VARCHAR(255) NOT NULL,
  description VARCHAR(512),
  flag BOOLEAN DEFAULT FALSE NOT NULL,
  status VARCHAR(50) DEFAULT 'active' NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
  deleted_at TIMESTAMPTZ,
  created_by INT,
  updated_by INT
);


INSERT INTO feature_flag (feature_key, label, description, flag, status)
VALUES
  ('showInfiniminutePrayer', 'Show Infiniminute Prayer', 'Toggle Infiniminute Prayer feature', TRUE, 'active'),
  ('showInfinipath', 'Show Infinipath', 'Toggle Infinipath feature', TRUE, 'active')
ON CONFLICT (feature_key) DO NOTHING;
