alter table users add column country_code character varying(5);
alter table users add column bare_mobile character varying(15);

select * from users where bare_mobile is null order by mobile; -- 3000
select * from users where bare_mobile is not null and length(bare_mobile)!=10 order by mobile;
select mobile, '+91' as country_code, replace(mobile, '+91', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+91' and length(mobile) = 13; -- 2907

update users set country_code = '+91', bare_mobile = replace(mobile, '+91', '') where substring(mobile, 1, 3) = '+91';
select * from users where bare_mobile is not null and length(bare_mobile)!=10 order by mobile;

select mobile, '+44' as country_code, replace(mobile, '+44', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+44'; --3

update users set country_code = '+44', bare_mobile = replace(mobile, '+44', '') where substring(mobile, 1, 3) = '+44'; --3

select mobile, '+1' as country_code, replace(mobile, '+1', '') as bare_mobile from users
where substring(mobile, 1, 2) = '+1'; --23

update users set country_code = '+1', bare_mobile = replace(mobile, '+1', '') where substring(mobile, 1, 2) = '+1';

select mobile, '+971' as country_code, replace(mobile, '+971', '') as bare_mobile from users
where substring(mobile, 1, 4) = '+971'; --41

update users set country_code = '+971', bare_mobile = replace(mobile, '+971', '') where substring(mobile, 1, 4) = '+971';

select mobile, '+61' as country_code, replace(mobile, '+61', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+61';  -- 1


update users set country_code = '+61', bare_mobile = replace(mobile, '+61', '') where substring(mobile, 1, 3) = '+61';

select mobile, '+65' as country_code, replace(mobile, '+65', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+65'; -- 8

update users set country_code = '+65', bare_mobile = replace(mobile, '+65', '') where substring(mobile, 1, 3) = '+65';

select mobile, '+230' as country_code, replace(mobile, '+230', '') as bare_mobile from users
where substring(mobile, 1, 4) = '+230';

update users set country_code = '+230', bare_mobile = replace(mobile, '+230', '') where substring(mobile, 1, 4) = '+230';

select mobile, '+965' as country_code, replace(mobile, '+965', '') as bare_mobile from users
where substring(mobile, 1, 4) = '+965'; --1

update users set country_code = '+965', bare_mobile = replace(mobile, '+965', '') where substring(mobile, 1, 4) = '+965';

select mobile, '+55' as country_code, replace(mobile, '+55', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+55';

update users set country_code = '+55', bare_mobile = replace(mobile, '+55', '') where substring(mobile, 1, 3) = '+55';

select mobile, '+94' as country_code, replace(mobile, '+94', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+94'; --2

update users set country_code = '+94', bare_mobile = replace(mobile, '+94', '') where substring(mobile, 1, 3) = '+94';

select mobile, '+57' as country_code, replace(mobile, '+57', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+57';

update users set country_code = '+57', bare_mobile = replace(mobile, '+57', '') where substring(mobile, 1, 3) = '+57';

select mobile, '+49' as country_code, replace(mobile, '+49', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+49'; --3

update users set country_code = '+49', bare_mobile = replace(mobile, '+49', '') where substring(mobile, 1, 3) = '+49';

select mobile, '+46' as country_code, replace(mobile, '+46', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+46'; --3

update users set country_code = '+46', bare_mobile = replace(mobile, '+46', '') where substring(mobile, 1, 3) = '+46';

select mobile, '+33' as country_code, replace(mobile, '+33', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+33';

update users set country_code = '+33', bare_mobile = replace(mobile, '+33', '') where substring(mobile, 1, 3) = '+33';

select mobile, '+31' as country_code, replace(mobile, '+31', '') as bare_mobile from users
where substring(mobile, 1, 3) = '+31';

update users set country_code = '+31', bare_mobile = replace(mobile, '+31', '') where substring(mobile, 1, 3) = '+31';

select mobile, '+234' as country_code, replace(mobile, '+234', '') as bare_mobile from users
where substring(mobile, 1, 4) = '+234';

update users set country_code = '+234', bare_mobile = replace(mobile, '+234', '') where substring(mobile, 1, 4) = '+234';

select mobile, '+852' as country_code, replace(mobile, '+852', '') as bare_mobile from users
where substring(mobile, 1, 4) = '+852';

update users set country_code = '+852', bare_mobile = replace(mobile, '+852', '') where substring(mobile, 1, 4) = '+852';


-- Preview what will happen
SELECT 
    mobile, 
    '+267' AS country_code, 
    replace(mobile, '+267', '') AS bare_mobile
FROM users
WHERE substring(mobile, 1, 4) = '+267';

-- Update the table
UPDATE users
SET country_code = '+267', 
    bare_mobile = replace(mobile, '+267', '')
WHERE substring(mobile, 1, 4) = '+267';


select * from public.user_address;
select * from public.address; -- city
select * from public.program_user_attendance;
select * from public.seeker_program_tag;
select * from public.seeker_program_tag_mapping;
select * from public.venue; -- program venue
select * from public.program;
select * from public.program_type;
select * from public.program_venue_map;
select * from public.users; -- name, email,phone ---username not null for org people -->check the user role map 
select * from public.user_detail; --dob,gender
select * from public.program_registration; --main
select * from public.program_registration_details; -- rm, comments,rating
select * from public.source_registration; -- user id
select * from public.invoice; -- all
select * from public.invoice_type;
select * from public.payment;
select * from public.payment_gateway_table;
select * from public.payment_history;


SELECT 
    -- Attendance flag
    CASE 
        WHEN COALESCE(pua.is_attended, FALSE) = TRUE 
          OR COALESCE(pua.is_manually_checkedin, FALSE) = TRUE
        THEN 'Attended'
        ELSE 'NotAttended'
    END AS attendance,

    pr.program_registration_id,
    pr.user_id,

    -- User info
    u.full_name AS user_full_name,
    u.bare_mobile AS user_bare_mobile,
    u.country_code AS user_country_code,
    u.email AS user_email,
    u.mobile AS user_mobile,
    d.gender AS user_gender,
    d.dob AS user_dob,

    -- Registration info
    pr.program_id,
    pr.register_status,
    pr.register_sub_status,
    pr.program_registration_seq_number,
    pr.waiting_list_seq_number,
    pr.cancellation_date,
    pr.cancelled_by,
    pr.is_qr_generated,

    -- Registration details (RM)
    prd.registration_status_id,
    prd.rm_user_id,
    rm.full_name AS rm_full_name,
    rm.bare_mobile AS rm_bare_mobile,
    rm.country_code AS rm_country_code,
    rm.email AS rm_email,
    rm.mobile AS rm_mobile,
    rm.username AS rm_username,
    prd.rm_notes,
    prd.prefered_room_mate_name,
    prd.other_rm_name,

    -- Source registration
    sr.registration_for,
    sr.source_user_id,
    sr.source_full_name,
    sr.source_mobile_number,
    sr.source_email,

    -- Payment
    p.payment_id,
    p.paid_amount AS payment_paid_amount,
    p.payment_mode,
    p.payment_type_id,
    p.tax_amount AS payment_tax_amount,
    p.tds AS payment_tds,
    p.subtotal AS payment_subtotal,
    p.total AS payment_total,
    p.payment_status,
    p.razorpay_order_id AS payment_razorpay_order_id,
    p.payment_sub_status,
    p.payment_type_status,
    p.original_amount AS payment_original_amount,
    p.gst_amount AS payment_gst_amount,
    p.invoice_reference_id_ext AS payment_invoice_reference_id_ext,
    p.is_razorpay_captured AS payment_is_razorpay_captured,
    p.razorpay_payment_id AS payment_razorpay_payment_id,
    p.offline_meta AS payment_offline_meta,
    pg.payment_gateway_name,

    -- Invoice
    i.invoice_id,
    i.invoice_type,
    i.program_registration_id AS invoice_program_registration_id,
    i.billing_name AS invoice_billing_name,
    i.billing_address AS invoice_billing_address,
    i.invoice_amount,
    i.pan AS invoice_pan,
    i.tan AS invoice_tan,
    i.tds_amount AS invoice_tds_amount,
    i.sub_total AS invoice_sub_total,
    i.tax_amount AS invoice_tax_amount,
    i.total_invoice_amount AS invoice_total_amount,
    i.status AS invoice_status,
    i.created_at AS invoice_created_at,
    i.updated_at AS invoice_updated_at,
    i.created_by AS invoice_created_by,
    i.updated_by AS invoice_updated_by,
    i.payment_id AS invoice_payment_id,
    i.razorpay_order_id AS invoice_razorpay_order_id,
    i.invoice_number,
    i.shipping_address AS invoice_shipping_address,
    i.einvoice_billing_address AS invoice_einvoice_billing_address,
    i.einvoice_qr_link AS invoice_einvoice_qr_link,
    i.einvoice_ack_date AS invoice_einvoice_ack_date,
    i.einvoice_inv_ref_num AS invoice_einvoice_inv_ref_num,
    i.user_id AS invoice_user_id,
    i.invoice_status AS invoice_einvoice_status,
    i.invoice_sub_status,
    i.email_to_send_invoice AS invoice_email_to_send,
    i.billing_address2 AS invoice_billing_address2,
    i.billing_gst AS invoice_billing_gst,
    i.billing_pin AS invoice_billing_pin,
    i.billing_utc_code AS invoice_billing_utc_code,
    i.einvoice_is_cancellable AS invoice_einvoice_is_cancellable,
    i.einvoice_status_formatted AS invoice_einvoice_status_formatted,
    i.einvoice_ack_number AS invoice_einvoice_ack_number,
    i.einvoice_status AS invoice_einvoice_status_raw,
    i.einvoice_formatted_status AS invoice_einvoice_formatted_status,
    i.invoice_registration_seq_number,
    i.invoice_date,
    i.revised_invoice_date AS invoice_revised_date,
    i.revised_invoice_number AS invoice_revised_number,
    i.revised_invoice_url AS invoice_revised_url,
    i.einvoice_error_message AS invoice_einvoice_error_message,

    -- Transportation
    ut.user_transportation_id AS transport_id,
    ut.from_address AS transport_from_address,
    ut.to_address AS transport_to_address,
    ut.is_arrival AS transport_is_arrival,
    ut.arrival_airline_name AS transport_arrival_airline_name,
    ut.arrival_flight_name AS transport_arrival_flight_name,
    ut.arrival_datetime AS transport_arrival_datetime,
    ut.departure_airline_name AS transport_departure_airline_name,
    ut.departure_flight_name AS transport_departure_flight_name,
    ut.departure_datetime AS transport_departure_datetime,
    ut.full_name AS transport_full_name,
    ut.emergency_contact_name AS transport_emergency_contact_name,
    ut.emergency_contact_relation AS transport_emergency_contact_relation,
    ut.emergency_contact_mobile_number AS transport_emergency_contact_mobile_number,
    ut.emergency_contact_email_address AS transport_emergency_contact_email_address,
    ut.travel_status AS transport_status,
    ut.travel_sub_status AS transport_sub_status,
    ut.logistic_status AS transport_logistic_status,
    ut.logistic_sub_status AS transport_logistic_sub_status,
    ut.room_allocation_status AS transport_room_allocation_status,
    ut.room_allocation_sub_status AS transport_room_allocation_sub_status,
    ut.id_reference_number AS transport_id_reference_number,
    ut.travel_schedule AS transport_schedule,
    ut.other_arrival_airline AS transport_other_arrival_airline,
    ut.other_departure_airline AS transport_other_departure_airline,
    tt.name AS transport_arrival_type,
    rtt.name AS transport_return_type,

    -- User address
    ua.city AS user_city,
    ua.state AS user_state,
    ua.country AS user_country,

    -- Extra profile info
    ut.profile_image,
    ut.shirt_size,
    ut.user_image_url,
    ut.profile_image_back
FROM public.program_registration pr
JOIN public.users u 
    ON pr.user_id = u.user_id
LEFT JOIN public.user_detail d 
    ON u.user_id = d.user_id
LEFT JOIN public.program_registration_details prd
    ON pr.program_registration_id = prd.program_registration_id
LEFT JOIN public.users rm 
    ON prd.rm_user_id = rm.user_id
LEFT JOIN public.source_registration sr
    ON pr.program_registration_id = sr.registration_id
LEFT JOIN public.payment p
    ON pr.program_registration_id = p.program_registration_id
LEFT JOIN public.payment_gateway_table pg
    ON p.payment_gateway_id = pg.id
LEFT JOIN public.invoice i
    ON p.payment_id = i.payment_id
LEFT JOIN public.user_transportation ut
    ON pr.program_registration_id = ut.program_registration_id
LEFT JOIN public.transportation_type tt
    ON ut.transportation_type_id = tt.transportation_type_id
LEFT JOIN public.transportation_type rtt
    ON ut.return_transportation_type_id = rtt.transportation_type_id
LEFT JOIN public.user_address ua
    ON u.user_id = ua.user_id
LEFT JOIN program_user_attendance pua
    ON pua.program_registration_id = pr.program_registration_id;

CREATE TABLE IF NOT EXISTS zz_ref_entrainment_program_registration_info
(
    attendance character varying(255),
    program_registration_id integer,
    user_id integer,

    -- User info
    user_full_name character varying(255),
    user_bare_mobile character varying(20),
    user_country_code character varying(10),
    user_email character varying(255),
    user_mobile character varying(20),
    user_gender character varying(50),
    user_dob date,

    -- Registration info
    program_id integer,
    register_status character varying(255),
    register_sub_status character varying(255),
    program_registration_seq_number character varying(255),
    waiting_list_seq_number character varying(255),
    cancellation_date timestamp with time zone,
    cancelled_by character varying(255),
    is_qr_generated boolean,

    -- Registration details (RM)
    registration_status_id integer,
    rm_user_id integer,
    rm_full_name character varying(255),
    rm_bare_mobile character varying(20),
    rm_country_code character varying(10),
    rm_email character varying(255),
    rm_mobile character varying(20),
    rm_username character varying(255),
    rm_notes text,
    prefered_room_mate_name character varying(255),
    other_rm_name character varying(255),

    -- Source registration
    registration_for character varying(10),
    source_user_id integer,
    source_full_name character varying(255),
    source_mobile_number character varying(20),
    source_email character varying(255),

    -- Payment (prefixed)
    payment_id integer,
    payment_paid_amount numeric(10,2),
    payment_mode character varying(50),
    payment_type_id integer,
    payment_tax_amount numeric(10,2),
    payment_tds numeric(10,2),
    payment_subtotal numeric(10,2),
    payment_total numeric(10,2),
    payment_status character varying(50),
    payment_razorpay_order_id character varying(50),
    payment_sub_status character varying(255),
    payment_type_status character varying(255),
    payment_original_amount double precision,
    payment_gst_amount double precision,
    payment_invoice_reference_id_ext character varying(255),
    payment_is_razorpay_captured character varying(255),
    payment_razorpay_payment_id character varying(255),
    payment_offline_meta jsonb,
    payment_gateway_name character varying(255),

    -- Invoice (prefixed)
    invoice_id integer,
    invoice_type integer,
    invoice_program_registration_id integer,
    invoice_billing_name character varying(255),
    invoice_billing_address text,
    invoice_amount numeric(10,2),
    invoice_pan character varying(20),
    invoice_tan character varying(20),
    invoice_tds_amount numeric(10,2),
    invoice_sub_total numeric(10,2),
    invoice_tax_amount numeric(10,2),
    invoice_total_amount numeric(10,2),
    invoice_status character varying(50),
    invoice_created_at timestamp without time zone,
    invoice_updated_at timestamp without time zone,
    invoice_created_by character varying(255),
    invoice_updated_by character varying(255),
    invoice_payment_id integer,
    invoice_razorpay_order_id character varying(50),
    invoice_number integer,
    invoice_shipping_address json,
    invoice_einvoice_billing_address json,
    invoice_einvoice_qr_link character varying(255),
    invoice_einvoice_ack_date timestamp with time zone,
    invoice_einvoice_inv_ref_num character varying(255),
    invoice_user_id integer,
    invoice_einvoice_status character varying(255),
    invoice_sub_status character varying(255),
    invoice_email_to_send character varying(255),
    invoice_billing_address2 character varying(255),
    invoice_billing_gst character varying(255),
    invoice_billing_pin character varying(10),
    invoice_billing_utc_code character varying(50),
    invoice_einvoice_is_cancellable boolean,
    invoice_einvoice_status_formatted character varying(255),
    invoice_einvoice_ack_number character varying(255),
    invoice_einvoice_status_raw character varying(255),
    invoice_einvoice_formatted_status character varying(255),
    invoice_registration_seq_number character varying(255),
    invoice_date date,
    invoice_revised_date date,
    invoice_revised_number character varying(255),
    invoice_revised_url character varying(255),
    invoice_einvoice_error_message text,

    -- Transportation info (prefixed)
    transport_id integer,
    transport_from_address text,
    transport_to_address text,
    transport_is_arrival boolean,
    transport_arrival_airline_name character varying(255),
    transport_arrival_flight_name character varying(255),
    transport_arrival_datetime timestamp with time zone,
    transport_departure_airline_name character varying(255),
    transport_departure_flight_name character varying(255),
    transport_departure_datetime timestamp with time zone,
    transport_full_name character varying(255),
    transport_emergency_contact_name character varying(50),
    transport_emergency_contact_relation character varying(20),
    transport_emergency_contact_mobile_number character varying(20),
    transport_emergency_contact_email_address character varying(255),
    transport_status character varying(255),
    transport_sub_status character varying(255),
    transport_logistic_status character varying(255),
    transport_logistic_sub_status character varying(255),
    transport_room_allocation_status character varying(255),
    transport_room_allocation_sub_status character varying(255),
    transport_id_reference_number character varying(20),
    transport_schedule boolean,
    transport_other_arrival_airline character varying(255),
    transport_other_departure_airline character varying(255),
    transport_arrival_type character varying(255),
    transport_return_type character varying(255),

    -- User address info
    user_city character varying(255),
    user_state character varying(255),
    user_country character varying(255),

    -- Extra fields
    profile_image text,
    shirt_size integer,
    user_image_url text,
    profile_image_back text
);

-- madhuri@Lap211 ~ % psql -h localhost -U postgres -d prod_dump_01_09_2025 \
-- -c "\copy zz_ref_entrainment_program_registration_info from '/Users/Madhuri/Downloads/entrianment-data-all-in-one-20250909-2042.csv' CSV HEADER NULL 'NULL' ENCODING 'UTF8'"

select * from zz_ref_entrainment_program_registration_info;

SELECT 
    z.program_registration_id,
    z.user_id AS zz_user_id,
    z.full_name AS zz_full_name,
    z.bare_mobile AS zz_bare_mobile,
    u.id AS user_table_id,
    u.full_name AS user_table_full_name,
    u.phone_number AS user_table_mobile
FROM public.zz_ref_entrainment_program_registration_info z
JOIN public.users u
    ON trim(z.bare_mobile) = trim(u.phone_number); --2479 --2480


SELECT 
    z.program_registration_id,
    z.user_id AS zz_user_id,
    z.full_name AS zz_full_name,
    z.bare_mobile AS zz_bare_mobile,
	z.email AS zz_email
FROM public.zz_ref_entrainment_program_registration_info z
LEFT JOIN public.users u
    ON trim(z.bare_mobile) = trim(u.phone_number)
WHERE u.id IS NULL; -- 179 -- 172


select * from zz_ref_entrainment_program_registration_info where trim(bare_mobile) not in (
    select trim(bare_mobile) from zz_ref_entrainment_program_registration_info
    where trim(bare_mobile) in (select trim(phone_number) from users)
) --172
and attendance = 'Attended'; --102
-- 102 users are not there in the portal but they attended

-- 2479 matching

-- 172 not matching
-- 2651 total


select * from zz_ref_entrainment_program_registration_info
where 1 = 1
and trim(bare_mobile) in (select trim(phone_number) from users)
and attendance != 'Attended';
-- 1959 from the attended and existing users
--  520

select * from zz_ref_entrainment_program_registration_info
where 1 = 1
and trim(bare_mobile) not in (select trim(phone_number) from users)
and attendance != 'Attended';

select * from program_v1 where program_type_id = (select id from program_type_v1 where key = 'PT_ENTRAINMENT') ;

select json_agg(t) 
from (
select * from program_v1 where program_type_id = (select id from program_type_v1 where key = 'PT_ENTRAINMENT')
) t;

select json_agg(t) 
from (
select * from program_session 
where program_id = ( select id from program_v1 
                     where program_type_id = ( select id from program_type_v1
					                             where key = 'PT_ENTRAINMENT'))
) t;



INSERT INTO public.program_v1 (
    program, program_type_id, name, description, start_date, end_date,
    no_of_session, registration_start_date, registration_end_date, meta,
    created_at, updated_at, mode_of_operation, online_type,
    max_session_duration_days, has_multiple_sessions, frequency,
    default_start_time, default_end_time, duration, requires_residence,
    involves_travel, has_checkin_checkout, requires_payment,
    requires_attendance_all_sessions, allows_minors, allows_proxy_registration,
    requires_approval, registration_level, waitlist_applicable, limited_seats,
    is_grouped_program, max_capacity, total_seats, waitlist_trigger_count,
    available_seats, filled_seats, program_fee, base_price, gst_percentage,
    currency, venue, status, is_active, code, banner_image_url,
    is_approval_required, is_residence_required, is_travel_involved,
    cgst, sgst, igst, tan_percent, checkin_at, checkout_at,
    starts_at, ends_at, tds_percent, gst_number, tds_applicability,
    invoice_sender_name, invoice_sender_pan, invoice_sender_cin,
    invoice_sender_address, helpline_number, email_sender_name,
    venue_name_in_emails, launch_date, logo_url, checkin_ends_at,
    checkout_ends_at, created_by, updated_by, registration_starts_at,
    registration_ends_at
)
SELECT 
    'PT_ENTRAINMENT',
    pt.id,
    'Entrainment 24',
    'Entrainment 24',
    '2024-09-18', '2024-09-21',
    2,
    '2024-08-07', '2024-08-30',
    '{}'::jsonb,
    NOW(), NOW(),
    'offline', 'NA',
    4, FALSE, 'yearly',
    '09:00:00', '18:00:00', '4 days',
    TRUE, TRUE, TRUE, TRUE, TRUE,
    TRUE, TRUE, FALSE, 'program',
    TRUE, TRUE, FALSE,
    0, 3000, 836, 3000, 0,
    55555.00, 55555.00, 18.00, 'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    'published', TRUE, 'EN24',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/Entrainment.png',
    FALSE, FALSE, FALSE,
    9.00, 9.00, 18.00, 0.00,
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T19:30:00+05:30',
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T19:30:00+05:30',
    10.00,
    '36AACCI7694H1Z5', 'base_only',
    'infinitheism spiritual foundation private limited',
    'AACCI7694H', 'U74900TN2011PTC083263',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91-9841660000', 'events@infinitheism.com',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2024-07-18T12:05:26.67+05:30',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
    '2024-09-19T02:30:00+05:30',
    '2024-09-22T05:29:59+05:30',
    -2, -2,
    '2024-08-07T11:30:00+05:30',
    '2024-08-30T18:00:00+05:30'
FROM public.program_type_v1 pt
WHERE pt.key = 'PT_ENTRAINMENT'
RETURNING id;


INSERT INTO public.program_session (
    program_id, name, description, start_date, end_date,
    registration_start_date, registration_end_date, meta,
    created_at, updated_at, mode_of_operation, online_type,
    max_session_duration_days, has_multiple_sessions, frequency,
    requires_residence, involves_travel, has_checkin_checkout,
    requires_payment, requires_attendance_all_sessions,
    allows_minors, allows_proxy_registration, requires_approval,
    registration_level, waitlist_applicable, limited_seats,
    total_seats, waitlist_trigger_count, available_seats,
    reserved_seats, filled_seats, base_price, gst_percentage,
    currency, venue, status, is_active, code, display_order,
    banner_image_url, created_by, updated_by, cgst, sgst, igst,
    tan_percent, registration_starts_at, registration_ends_at,
    checkin_at, checkout_at, starts_at, ends_at, tds_percent,
    gst_number, tds_applicability, invoice_sender_name,
    invoice_sender_pan, invoice_sender_cin, invoice_sender_address,
    helpline_number, email_sender_name, venue_name_in_emails,
    launch_date, logo_url, checkin_ends_at, checkout_ends_at
)
SELECT 
    pv.id,
    'Entrainment 24',
    'Entrainment 24',
    '2024-09-18', '2024-09-21',
    '2024-08-07', '2024-08-30',
    '{}'::jsonb,
    NOW(), NOW(),
    'offline', 'NA',
    4, TRUE, 'yearly',
    FALSE, FALSE, FALSE,
    TRUE, TRUE,
    FALSE, FALSE, FALSE,
    'session', FALSE, TRUE,
    3000, 836, 3000,
    0, 0,
    55555.00, 18.00, 'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    'scheduled', TRUE, 'EN24', 1,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/Entrainment.png',
    -2, -2,
    9.00, 9.00, 18.00, 0.00,
    '2024-08-07T11:30:00+05:30',
    '2024-08-30T18:00:00+05:30',
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T17:30:00+05:30',
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T19:30:00+05:30',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'infinitheism spiritual foundation private limited',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91-9841660000',
    'events@infinitheism.com',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2024-07-18T12:05:26.67+05:30',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
    '2024-09-19T02:30:00+05:30',
    '2024-09-21T19:30:00+05:30'
FROM program_v1 pv
WHERE pv.code = 'EN24'
RETURNING id;

-- INSERT EN25 PROGRAM
INSERT INTO public.program_v1 (
    program, program_type_id, name, description, start_date, end_date,
    no_of_session, registration_start_date, registration_end_date, meta,
    created_at, updated_at, mode_of_operation, online_type,
    max_session_duration_days, has_multiple_sessions, frequency,
    default_start_time, default_end_time, duration, requires_residence,
    involves_travel, has_checkin_checkout, requires_payment,
    requires_attendance_all_sessions, allows_minors, allows_proxy_registration,
    requires_approval, registration_level, waitlist_applicable, limited_seats,
    is_grouped_program, max_capacity, total_seats, waitlist_trigger_count,
    available_seats, filled_seats, program_fee, base_price, gst_percentage,
    currency, venue, status, is_active, code, banner_image_url,
    is_approval_required, is_residence_required, is_travel_involved,
    cgst, sgst, igst, tan_percent, checkin_at, checkout_at,
    starts_at, ends_at, tds_percent, gst_number, tds_applicability,
    invoice_sender_name, invoice_sender_pan, invoice_sender_cin,
    invoice_sender_address, helpline_number, email_sender_name,
    venue_name_in_emails, launch_date, logo_url, checkin_ends_at,
    checkout_ends_at, created_by, updated_by, registration_starts_at,
    registration_ends_at
)
SELECT 
    'PT_ENTRAINMENT',
    pt.id,
    'Entrainment 25',
    'A residential spiritual retreat with Mahatria',
    '2025-09-04', '2025-09-07',
    2,
    '2025-07-28', '2025-08-31',
    '{}'::jsonb,
    NOW(), NOW(),
    'offline', 'NA',
    8, TRUE, 'yearly',
    '09:00:00', '18:00:00', '4 days',
    TRUE, TRUE, TRUE, TRUE, TRUE,
    TRUE, TRUE, FALSE, 'session',
    TRUE, TRUE, FALSE,
    0, 1200, 10, 1200, 0,
    59000.00, 59000.00, 18.00, 'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    'scheduled', TRUE, 'EN25',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/Entrainment.png',
    FALSE, FALSE, FALSE,
    9.00, 9.00, 18.00, 0.00,
    '2025-09-04T21:30:00+05:30',
    '2025-09-07T19:30:00+05:30',
    '2025-09-04T21:30:00+05:30',
    '2025-09-07T19:30:00+05:30',
    10.00,
    '36AACCI7694H1Z5', 'base_only',
    'infinitheism spiritual foundation private limited',
    'AACCI7694H', 'U74900TN2011PTC083263',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91-9841660000', 'events@infinitheism.com',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-07-18T12:05:26.67+05:30',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
    '2025-09-05T02:30:00+05:30',
    '2025-09-07T19:30:00+05:30',
    -2, -2,
    '2025-07-28T11:30:00+05:30',
    '2025-08-31T18:00:00+05:30'
FROM public.program_type_v1 pt
WHERE pt.key = 'PT_ENTRAINMENT'
RETURNING id;


-- INSERT EN25 PROGRAM SESSION
INSERT INTO public.program_session (
    program_id, name, description, start_date, end_date,
    registration_start_date, registration_end_date, meta,
    created_at, updated_at, mode_of_operation, online_type,
    max_session_duration_days, has_multiple_sessions, frequency,
    requires_residence, involves_travel, has_checkin_checkout,
    requires_payment, requires_attendance_all_sessions,
    allows_minors, allows_proxy_registration, requires_approval,
    registration_level, waitlist_applicable, limited_seats,
    total_seats, waitlist_trigger_count, available_seats,
    reserved_seats, filled_seats, base_price, gst_percentage,
    currency, venue, status, is_active, code, display_order,
    banner_image_url, created_by, updated_by, cgst, sgst, igst,
    tan_percent, registration_starts_at, registration_ends_at,
    checkin_at, checkout_at, starts_at, ends_at, tds_percent,
    gst_number, tds_applicability, invoice_sender_name,
    invoice_sender_pan, invoice_sender_cin, invoice_sender_address,
    helpline_number, email_sender_name, venue_name_in_emails,
    launch_date, logo_url, checkin_ends_at, checkout_ends_at
)
SELECT 
    pv.id,
    'Entrainment 25',
    'A residential spiritual retreat with Mahatria',
    '2025-09-04', '2025-09-07',
    '2025-07-28', '2025-08-31',
    '{}'::jsonb,
    NOW(), NOW(),
    'offline', 'NA',
    8, TRUE, 'yearly',
    FALSE, FALSE, FALSE,
    TRUE, TRUE,
    FALSE, FALSE, FALSE,
    'session', FALSE, TRUE,
    1200, 10, 1200,
    0, 0,
    59000.00, 18.00, 'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    'scheduled', TRUE, 'EN25', 1,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/Entrainment.png',
    -2, -2,
    9.00, 9.00, 18.00, 0.00,
    '2025-07-28T11:30:00+05:30',
    '2025-08-31T18:00:00+05:30',
    '2025-09-04T21:30:00+05:30',
    '2025-09-07T17:30:00+05:30',
    '2025-09-04T21:30:00+05:30',
    '2025-09-07T19:30:00+05:30',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'infinitheism spiritual foundation private limited',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91-9841660000',
    'events@infinitheism.com',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-07-18T12:05:26.67+05:30',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
    '2025-09-05T02:30:00+05:30',
    '2025-09-07T19:30:00+05:30'
FROM program_v1 pv
WHERE pv.code = 'EN25'
RETURNING id;


update public.program_v1
set sub_program_type = 'PST_ENTRAINMENT'
where code in ('EN24', 'EN25');




CREATE TABLE IF NOT EXISTS zz_ref_entrainment_program_registration_info
(
    attendance character varying(255),
    program_registration_id integer,
    user_id integer,

    -- User info
    user_full_name character varying(255),
    user_bare_mobile character varying(20),
    user_country_code character varying(10),
    user_email character varying(255),
    user_mobile character varying(20),
    user_gender character varying(50),
    user_dob date,

    -- Registration info
    program_id integer,
    register_status character varying(255),
    register_sub_status character varying(255),
    program_registration_seq_number character varying(255),
    waiting_list_seq_number character varying(255),
    cancellation_date timestamp with time zone,
    cancelled_by character varying(255),
    is_qr_generated boolean,

    -- Registration details (RM)
    registration_status_id integer,
    rm_user_id integer,
    rm_full_name character varying(255),
    rm_bare_mobile character varying(20),
    rm_country_code character varying(10),
    rm_email character varying(255),
    rm_mobile character varying(20),
    rm_username character varying(255),
    rm_notes text,
    prefered_room_mate_name character varying(255),
    other_rm_name character varying(255),

    -- Source registration
    registration_for character varying(10),
    source_user_id integer,
    source_full_name character varying(255),
    source_mobile_number character varying(20),
    source_email character varying(255),

    -- Payment (prefixed)
    payment_id integer,
    payment_paid_amount numeric(10,2),
    payment_mode character varying(50),
    payment_type_id integer,
    payment_tax_amount numeric(10,2),
    payment_tds numeric(10,2),
    payment_subtotal numeric(10,2),
    payment_total numeric(10,2),
    payment_status character varying(50),
    payment_razorpay_order_id character varying(50),
    payment_sub_status character varying(255),
    payment_type_status character varying(255),
    payment_original_amount double precision,
    payment_gst_amount double precision,
    payment_invoice_reference_id_ext character varying(255),
    payment_is_razorpay_captured character varying(255),
    payment_razorpay_payment_id character varying(255),
    payment_offline_meta jsonb,
    payment_gateway_name character varying(255),

    -- Invoice (prefixed)
    invoice_id integer,
    invoice_type integer,
    invoice_program_registration_id integer,
    invoice_billing_name character varying(255),
    invoice_billing_address text,
    invoice_amount numeric(10,2),
    invoice_pan character varying(20),
    invoice_tan character varying(20),
    invoice_tds_amount numeric(10,2),
    invoice_sub_total numeric(10,2),
    invoice_tax_amount numeric(10,2),
    invoice_total_amount numeric(10,2),
    invoice_status character varying(50),
    invoice_created_at timestamp without time zone,
    invoice_updated_at timestamp without time zone,
    invoice_created_by character varying(255),
    invoice_updated_by character varying(255),
    invoice_payment_id integer,
    invoice_razorpay_order_id character varying(50),
    invoice_number integer,
    invoice_shipping_address json,
    invoice_einvoice_billing_address json,
    invoice_einvoice_qr_link character varying(255),
    invoice_einvoice_ack_date timestamp with time zone,
    invoice_einvoice_inv_ref_num character varying(255),
    invoice_user_id integer,
    invoice_einvoice_status character varying(255),
    invoice_sub_status character varying(255),
    invoice_email_to_send character varying(255),
    invoice_billing_address2 character varying(255),
    invoice_billing_gst character varying(255),
    invoice_billing_pin character varying(10),
    invoice_billing_utc_code character varying(50),
    invoice_einvoice_is_cancellable boolean,
    invoice_einvoice_status_formatted character varying(255),
    invoice_einvoice_ack_number character varying(255),
    invoice_einvoice_status_raw character varying(255),
    invoice_einvoice_formatted_status character varying(255),
    invoice_registration_seq_number character varying(255),
    invoice_date date,
    invoice_revised_date date,
    invoice_revised_number character varying(255),
    invoice_revised_url character varying(255),
    invoice_einvoice_error_message text,

    -- Transportation info (prefixed)
    transport_id integer,
    transport_from_address text,
    transport_to_address text,
    transport_is_arrival boolean,
    transport_arrival_airline_name character varying(255),
    transport_arrival_flight_name character varying(255),
    transport_arrival_datetime timestamp with time zone,
    transport_departure_airline_name character varying(255),
    transport_departure_flight_name character varying(255),
    transport_departure_datetime timestamp with time zone,
    transport_full_name character varying(255),
    transport_emergency_contact_name character varying(50),
    transport_emergency_contact_relation character varying(20),
    transport_emergency_contact_mobile_number character varying(20),
    transport_emergency_contact_email_address character varying(255),
    transport_status character varying(255),
    transport_sub_status character varying(255),
    transport_logistic_status character varying(255),
    transport_logistic_sub_status character varying(255),
    transport_room_allocation_status character varying(255),
    transport_room_allocation_sub_status character varying(255),
    transport_id_reference_number character varying(20),
    transport_schedule boolean,
    transport_other_arrival_airline character varying(255),
    transport_other_departure_airline character varying(255),
    transport_arrival_type character varying(255),
    transport_return_type character varying(255),

    -- User address info
    user_city character varying(255),
    user_state character varying(255),
    user_country character varying(255),

    -- Extra fields
    profile_image text,
    shirt_size integer,
    user_image_url text,
    profile_image_back text
);


select * from program_v1 where program_type_id = (select id from program_type_v1 where key = 'PT_ENTRAINMENT') and code = 'EN25' and deleted_at is null

select * from program_v1 where program_type_id = (select id from program_type_v1 where key = 'PT_ENTRAINMENT') and code = 'EN24' and deleted_at is null


INSERT INTO public.program_v1 (
    program, program_type_id, name, description, start_date, end_date,
    no_of_session, registration_start_date, registration_end_date, meta,
    created_at, updated_at, mode_of_operation, online_type,
    max_session_duration_days, has_multiple_sessions, frequency,
    default_start_time, default_end_time, duration, requires_residence,
    involves_travel, has_checkin_checkout, requires_payment,
    requires_attendance_all_sessions, allows_minors, allows_proxy_registration,
    requires_approval, registration_level, waitlist_applicable, limited_seats,
    is_grouped_program, max_capacity, total_seats, waitlist_trigger_count,
    available_seats, filled_seats, program_fee, base_price, gst_percentage,
    currency, venue, status, is_active, code, banner_image_url,
    is_approval_required, is_residence_required, is_travel_involved,
    cgst, sgst, igst, tan_percent, checkin_at, checkout_at,
    starts_at, ends_at, tds_percent, gst_number, tds_applicability,
    invoice_sender_name, invoice_sender_pan, invoice_sender_cin,
    invoice_sender_address, helpline_number, email_sender_name,
    venue_name_in_emails, launch_date, logo_url, checkin_ends_at,
    checkout_ends_at, created_by, updated_by, registration_starts_at,
    registration_ends_at
)
SELECT 
    'PT_ENTRAINMENT',
    pt.id,
    'Entrainment 24',
    'Entrainment 24',
    '2024-09-18', '2024-09-21',
    2,
    '2024-08-07', '2024-08-30',
    '{}'::jsonb,
    NOW(), NOW(),
    'offline', 'NA',
    4, FALSE, 'yearly',
    '09:00:00', '18:00:00', '4 days',
    TRUE, TRUE, TRUE, TRUE, TRUE,
    TRUE, TRUE, FALSE, 'program',
    TRUE, TRUE, FALSE,
    0, 3000, 836, 3000, 0,
    55555.00, 55555.00, 18.00, 'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    'published', TRUE, 'EN24',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/Entrainment.png',
    FALSE, FALSE, FALSE,
    9.00, 9.00, 18.00, 0.00,
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T19:30:00+05:30',
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T19:30:00+05:30',
    10.00,
    '36AACCI7694H1Z5', 'base_only',
    'infinitheism spiritual foundation private limited',
    'AACCI7694H', 'U74900TN2011PTC083263',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91-9841660000', 'events@infinitheism.com',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2024-07-18T12:05:26.67+05:30',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
    '2024-09-19T02:30:00+05:30',
    '2024-09-22T05:29:59+05:30',
    -2, -2,
    '2024-08-07T11:30:00+05:30',
    '2024-08-30T18:00:00+05:30'
FROM public.program_type_v1 pt
WHERE pt.key = 'PT_ENTRAINMENT'
RETURNING id;


INSERT INTO public.program_session (
    program_id, name, description, start_date, end_date,
    registration_start_date, registration_end_date, meta,
    created_at, updated_at, mode_of_operation, online_type,
    max_session_duration_days, has_multiple_sessions, frequency,
    requires_residence, involves_travel, has_checkin_checkout,
    requires_payment, requires_attendance_all_sessions,
    allows_minors, allows_proxy_registration, requires_approval,
    registration_level, waitlist_applicable, limited_seats,
    total_seats, waitlist_trigger_count, available_seats,
    reserved_seats, filled_seats, base_price, gst_percentage,
    currency, venue, status, is_active, code, display_order,
    banner_image_url, created_by, updated_by, cgst, sgst, igst,
    tan_percent, registration_starts_at, registration_ends_at,
    checkin_at, checkout_at, starts_at, ends_at, tds_percent,
    gst_number, tds_applicability, invoice_sender_name,
    invoice_sender_pan, invoice_sender_cin, invoice_sender_address,
    helpline_number, email_sender_name, venue_name_in_emails,
    launch_date, logo_url, checkin_ends_at, checkout_ends_at
)
SELECT 
    pv.id,
    'Entrainment 24',
    'Entrainment 24',
    '2024-09-18', '2024-09-21',
    '2024-08-07', '2024-08-30',
    '{}'::jsonb,
    NOW(), NOW(),
    'offline', 'NA',
    4, TRUE, 'yearly',
    FALSE, FALSE, FALSE,
    TRUE, TRUE,
    FALSE, FALSE, FALSE,
    'session', FALSE, TRUE,
    3000, 836, 3000,
    0, 0,
    55555.00, 18.00, 'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    'scheduled', TRUE, 'EN24', 1,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/Entrainment.png',
    -2, -2,
    9.00, 9.00, 18.00, 0.00,
    '2024-08-07T11:30:00+05:30',
    '2024-08-30T18:00:00+05:30',
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T17:30:00+05:30',
    '2024-09-18T21:30:00+05:30',
    '2024-09-21T19:30:00+05:30',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'infinitheism spiritual foundation private limited',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91-9841660000',
    'events@infinitheism.com',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2024-07-18T12:05:26.67+05:30',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/entrainment-logo.svg',
    '2024-09-19T02:30:00+05:30',
    '2024-09-21T19:30:00+05:30'
FROM program_v1 pv
WHERE pv.code = 'EN24'
RETURNING id;



INSERT INTO hdb_program_registration (
    program_session_id,
    program_id,
    allocated_program_id,
    allocated_session_id,
    user_id,
    full_name,
    email_address,
    mobile_number,
    gender,
    dob,
    registration_status,
    registration_category,
    registration_seq_number,
    program_registration_seq_number,
    waiting_list_seq_number,
    wait_list_registration_seq_number,
    cancellation_date,
    cancelled_by,
    rm_contact,
    notes,
    preferred_room_mate,
    other_infinitheism_contact,
    registration_date,
    created_by,
    audit_ref_id,
    parent_ref_id,
    created_at,
    updated_at,
    updated_by,
    city,
    other_city_name,
    country_name,
    basic_details_status,
    user_profile_url,
    pro_forma_invoice_name,
    pro_forma_invoice_address,
    proforma_invoice_seq_number,
    pro_forma_gst_number,
    pro_forma_is_gst_registered,
    pro_forma_zip,
    alternate_phone_number,
    comments,
    rm_review,
    terms_accepted,
    no_of_hdbs,
    last_hdb_attended,
    hdb_association_since,
    first_song_preference,
    second_song_preference
)
SELECT 
    -- Get program session ID for EN24
    null AS program_session_id,
    
    -- Program IDs
    (SELECT id FROM program_v1 WHERE code = 'EN24') AS program_id,
    (SELECT id FROM program_v1 WHERE code = 'EN24') AS allocated_program_id,
    null AS allocated_session_id,
    
    -- Get user_id by matching user_bare_mobile with users.phone_number
   CASE 
	    WHEN u.id IS NOT NULL THEN u.id
	    ELSE -2 
	END AS user_id,
    
    -- User details from entrainment data
    TRIM(zz.user_full_name) AS full_name,
    TRIM(LOWER(zz.user_email)) AS email_address,
    TRIM(zz.user_mobile) AS mobile_number,
    
    -- Gender mapping: male/female -> Male/Female
    CASE 
        WHEN TRIM(LOWER(zz.user_gender)) = 'male' THEN 'Male'::gender_enum
        WHEN TRIM(LOWER(zz.user_gender)) = 'female' THEN 'Female'::gender_enum
        ELSE Null
    END AS gender,
    
    -- DOB
    zz.user_dob AS dob,
    
    -- Registration status mapping based on attendance and register_status
    CASE 
        WHEN TRIM(UPPER(zz.attendance)) = 'ATTENDED' THEN 'completed'::registration_status_enum
        WHEN TRIM(LOWER(zz.register_status)) = 'cancelled' THEN 'cancelled'::registration_status_enum
        WHEN TRIM(LOWER(zz.register_status)) = 'denied' THEN 'denied'::registration_status_enum
        WHEN TRIM(LOWER(zz.register_status)) = 'archived' THEN 'archived'::registration_status_enum
        ELSE 'pending'::registration_status_enum
    END AS registration_status,
    
    -- Register status from the data
    TRIM(zz.register_status) AS registration_category,

	TRIM(zz.program_registration_seq_number) AS registration_seq_number,
    -- Registration sequence numbers
   CASE 
        WHEN TRIM(zz.program_registration_seq_number) LIKE 'ENT24%' THEN 
            REPLACE(TRIM(zz.program_registration_seq_number), 'ENT24', '')::integer
        ELSE NULL
    END AS program_registration_seq_number,

   
    
    -- Waiting list sequence number
    CASE 
        WHEN zz.waiting_list_seq_number IS NOT NULL AND zz.waiting_list_seq_number != '' THEN
            CASE 
                WHEN TRIM(zz.waiting_list_seq_number) LIKE 'WL/ENT24%' THEN 
                    REPLACE(TRIM(zz.waiting_list_seq_number), 'WL/ENT24', '')::integer
                WHEN TRIM(zz.waiting_list_seq_number) LIKE 'WL%' THEN 
                    REPLACE(TRIM(zz.waiting_list_seq_number), 'WL', '')::integer
                ELSE NULL
            END
        ELSE NULL 
    END AS waiting_list_seq_number,
    
    TRIM(zz.waiting_list_seq_number) AS wait_list_registration_seq_number,
    
    -- Cancellation details
    zz.cancellation_date AS cancellation_date,
    
    -- Cancelled by - find user by phone number if it's 'sivasankaran' or get admin user
    CASE 
        WHEN TRIM(LOWER(zz.cancelled_by)) = 'sivasankaran' THEN 
            (SELECT id FROM users WHERE TRIM(phone_number) = '8754402031' LIMIT 1)
        WHEN zz.cancelled_by IS NOT NULL AND TRIM(zz.cancelled_by) != '' THEN 
            (SELECT id FROM users u  
             WHERE u.phone_number = '8291673037' LIMIT 1)
        ELSE NULL 
    END AS cancelled_by,
    
    -- RM Contact - find user by rm_bare_mobile
    CASE 
        WHEN UPPER(TRIM(zz.rm_full_name)) = 'OTHER' 
             OR TRIM(zz.rm_bare_mobile) = '9841660000' THEN 
            (SELECT id FROM users WHERE TRIM(phone_number) = '9841660000' LIMIT 1)
        WHEN zz.rm_bare_mobile IS NOT NULL AND TRIM(zz.rm_bare_mobile) != '' THEN 
            (SELECT id FROM users WHERE TRIM(phone_number) = TRIM(zz.rm_bare_mobile) LIMIT 1)
        ELSE NULL 
    END AS rm_contact,
    
    -- RM Notes
    TRIM(zz.rm_notes) AS notes,
    
    -- Preferred room mate logic
    zz.prefered_room_mate_name AS preferred_room_mate,
    
    -- Other RM name if RM is "Other"
    CASE 
        WHEN UPPER(TRIM(zz.rm_full_name)) = 'OTHER' THEN (zz.other_rm_name)
        ELSE NULL
    END AS other_infinitheism_contact,
    
    -- Registration date (current timestamp as we don't have original date)
    NOW() AS registration_date,
    
    -- Created by - if registration_for is 'self' then user_id, else NULL
    CASE 
	    WHEN u.id IS NOT NULL THEN u.id
	    ELSE -2 
	END AS created_by,

    
    -- Audit fields - will be updated after insert
    NULL AS audit_ref_id,
    NULL AS parent_ref_id,
    
    -- Timestamps
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS updated_by,
    
    -- City mapping with predefined Indian cities
    CASE 
        WHEN TRIM(zz.user_city) IN (
            'Agra', 'Ahmedabad', 'Ajmer', 'Aligarh', 'Allahabad', 'Ambattur', 'Amravati', 'Amritsar', 'Asansol', 'Aurangabad', 
            'Bangalore', 'Bareilly', 'Belgaum', 'Bhavnagar', 'Bhilai Nagar', 'Bhiwandi', 'Bhopal', 'Bhubaneswar', 'Bikaner', 
            'Chandigarh', 'Chennai', 'Coimbatore', 'Cuttack', 'Dehradun', 'Delhi', 'Dhanbad', 'Durgapur', 'Faridabad', 'Firozabad', 
            'Gaya', 'Ghaziabad', 'Gorakhpur', 'Gulbarga', 'Guntur', 'Gurgaon', 'Guwahati', 'Gwalior', 'Haora', 'Hubli And Dharwad', 
            'Hyderabad', 'Indore', 'Jabalpur', 'Jaipur', 'Jalandhar', 'Jalgaon', 'Jammu', 'Jamnagar', 'Jamshedpur', 'Jhansi', 
            'Jodhpur', 'Kalyan & Dombivali', 'Kanpur', 'Kochi', 'Kolapur', 'Kolkata', 'Kota', 'Loni', 'Lucknow', 'Ludhiana', 
            'Madurai', 'Maheshtala', 'Malegaon', 'Mangalore', 'Meerut', 'Mira And Bhayander', 'Moradabad', 'Mumbai', 'Nagpur', 
            'Nanded Waghala', 'Nashik', 'Navi Mumbai', 'Nellore', 'Noida', 'Patna', 'Pimpri & Chinchwad', 'Pune', 'Raipur', 
            'Rajkot', 'Ranchi', 'Saharanpur', 'Salem', 'Sangli Miraj Kupwad', 'Siliguri', 'Solapur', 'Srinagar', 'Surat', 
            'Thane', 'Thiruvananthapuram', 'Tiruchirappalli', 'Tirunelveli', 'Udaipur', 'Ujjain', 'Ulhasnagar', 'Vadodara', 
            'Varanasi', 'Vasai Virar', 'Vijayawada', 'Visakhapatnam', 'Warangal'
        ) THEN TRIM(zz.user_city)
        ELSE 'Other'
    END AS city,
    
    -- Other city name - store original city if not in predefined list
    CASE 
        WHEN TRIM(zz.user_city) NOT IN (
            'Agra', 'Ahmedabad', 'Ajmer', 'Aligarh', 'Allahabad', 'Ambattur', 'Amravati', 'Amritsar', 'Asansol', 'Aurangabad', 
            'Bangalore', 'Bareilly', 'Belgaum', 'Bhavnagar', 'Bhilai Nagar', 'Bhiwandi', 'Bhopal', 'Bhubaneswar', 'Bikaner', 
            'Chandigarh', 'Chennai', 'Coimbatore', 'Cuttack', 'Dehradun', 'Delhi', 'Dhanbad', 'Durgapur', 'Faridabad', 'Firozabad', 
            'Gaya', 'Ghaziabad', 'Gorakhpur', 'Gulbarga', 'Guntur', 'Gurgaon', 'Guwahati', 'Gwalior', 'Haora', 'Hubli And Dharwad', 
            'Hyderabad', 'Indore', 'Jabalpur', 'Jaipur', 'Jalandhar', 'Jalgaon', 'Jammu', 'Jamnagar', 'Jamshedpur', 'Jhansi', 
            'Jodhpur', 'Kalyan & Dombivali', 'Kanpur', 'Kochi', 'Kolapur', 'Kolkata', 'Kota', 'Loni', 'Lucknow', 'Ludhiana', 
            'Madurai', 'Maheshtala', 'Malegaon', 'Mangalore', 'Meerut', 'Mira And Bhayander', 'Moradabad', 'Mumbai', 'Nagpur', 
            'Nanded Waghala', 'Nashik', 'Navi Mumbai', 'Nellore', 'Noida', 'Patna', 'Pimpri & Chinchwad', 'Pune', 'Raipur', 
            'Rajkot', 'Ranchi', 'Saharanpur', 'Salem', 'Sangli Miraj Kupwad', 'Siliguri', 'Solapur', 'Srinagar', 'Surat', 
            'Thane', 'Thiruvananthapuram', 'Tiruchirappalli', 'Tirunelveli', 'Udaipur', 'Ujjain', 'Ulhasnagar', 'Vadodara', 
            'Varanasi', 'Vasai Virar', 'Vijayawada', 'Visakhapatnam', 'Warangal'
        ) THEN TRIM(zz.user_city)
        ELSE NULL
    END AS other_city_name,
    
    -- Country name
    UPPER(TRIM(zz.user_country)) AS country_name,
    
    -- Basic details status - completed for all migrated records
    'completed'::basic_details_status_enum AS basic_details_status,
    
    -- User profile URL
    zz.user_image_url AS user_profile_url,
    
    -- Proforma invoice details from billing data
    TRIM(zz.invoice_billing_name) AS pro_forma_invoice_name,
    TRIM(zz.invoice_billing_address) AS pro_forma_invoice_address,
    TRIM(zz.program_registration_seq_number) AS proforma_invoice_seq_number,
    TRIM(zz.invoice_billing_gst) AS pro_forma_gst_number,
    CASE 
        WHEN zz.invoice_billing_gst IS NOT NULL AND TRIM(zz.invoice_billing_gst) != '' THEN TRUE
        ELSE FALSE
    END AS pro_forma_is_gst_registered,
    TRIM(zz.invoice_billing_pin) AS pro_forma_zip,
    
    -- Additional contact info
    TRIM(zz.transport_emergency_contact_mobile_number) AS alternate_phone_number, -- Using mobile as alternate phone
    
    -- Comments from various sources
    NULL AS comments,
    NULL AS rm_review,

    -- Terms accepted - assume true for migrated data
    FALSE AS terms_accepted,

    -- HDB related fields - set defaults for entrainment
    0 AS no_of_hdbs,
    NULL AS last_hdb_attended,
    NULL AS hdb_association_since,
    
    -- Song preferences - set null for entrainment
    NULL AS first_song_preference,
    NULL AS second_song_preference
    
FROM zz_ref_entrainment_program_registration_info zz
-- Join with users table to get user_id by matching user_bare_mobile with phone_number
JOIN users u ON TRIM(zz.user_bare_mobile) = TRIM(u.phone_number)
-- Get the EN24 program session
CROSS JOIN (
    SELECT pv.id 
    FROM program_v1 pv
    WHERE pv.code = 'EN24' 
    LIMIT 1
)
-- Only process records where program_id = 1 (as per your requirement)
WHERE zz.program_id = 1;


UPDATE hdb_program_registration 
SET audit_ref_id = id, parent_ref_id = id 
WHERE audit_ref_id IS NULL 
  AND parent_ref_id IS NULL
  AND updated_by = -2  -- Only update records created by this migration
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN24');


-- Verify the migration results
SELECT 'Entrainment Registration Count' as metric, COUNT(*)::text as count
FROM hdb_program_registration
WHERE updated_by = -2
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN24')
UNION ALL
SELECT 'By Registration Status' as metric, registration_status || ': ' || COUNT(*)::text as count
FROM hdb_program_registration
WHERE updated_by = -2
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN24')
GROUP BY registration_status
UNION ALL
SELECT 'By Attendance Status' as metric, 
       CASE WHEN registration_status = 'completed' THEN 'Attended' ELSE 'Not Attended' END || ': ' || COUNT(*)::text as count
FROM hdb_program_registration
WHERE updated_by = -2
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN24')
GROUP BY CASE WHEN registration_status = 'completed' THEN 'Attended' ELSE 'Not Attended' END;



select * from program_v1 order by id desc;


select * from hdb_program_registration where program_id = (select id from program_v1 order by id desc limit 1);


select * from zz_ref_entrainment_program_registration_info where program_id = 2; --1324
select * from zz_ref_entrainment_program_registration_info where program_id = 2 and source_user_id is not null; --1279
select * from zz_ref_entrainment_program_registration_info where program_id = 2 and source_user_id is null; --45

SELECT z.*, u.id AS matched_user_id
FROM zz_ref_entrainment_program_registration_info z
JOIN users u
  ON trim(z.user_bare_mobile) = trim(u.phone_number)
WHERE z.program_id = 2
  AND z.source_user_id IS NULL;


UPDATE zz_ref_entrainment_program_registration_info z
SET source_user_id = u.id
FROM users u
WHERE z.program_id = 2
  AND z.source_user_id IS NULL
  AND trim(z.user_bare_mobile) = trim(u.phone_number);


INSERT INTO hdb_program_registration (
    program_session_id,
    program_id,
    allocated_program_id,
    allocated_session_id,
    user_id,
    full_name,
    email_address,
    mobile_number,
    gender,
    dob,
    registration_status,
    registration_category,
    registration_seq_number,
    program_registration_seq_number,
    waiting_list_seq_number,
    wait_list_registration_seq_number,
    cancellation_date,
    cancelled_by,
    rm_contact,
    notes,
    preferred_room_mate,
    other_infinitheism_contact,
    registration_date,
    created_by,
    audit_ref_id,
    parent_ref_id,
    created_at,
    updated_at,
    updated_by,
    city,
    other_city_name,
    country_name,
    basic_details_status,
    user_profile_url,
    pro_forma_invoice_name,
    pro_forma_invoice_address,
    proforma_invoice_seq_number,
    pro_forma_gst_number,
    pro_forma_is_gst_registered,
    pro_forma_zip,
    alternate_phone_number,
    comments,
    rm_review,
    terms_accepted,
    no_of_hdbs,
    last_hdb_attended,
    hdb_association_since,
    first_song_preference,
    second_song_preference
)
SELECT 
    -- Get program session ID for EN25
    null AS program_session_id,
    
    -- Program IDs
    (SELECT id FROM program_v1 WHERE code = 'EN25' and deleted_at is null) AS program_id,
    (SELECT id FROM program_v1 WHERE code = 'EN25' and deleted_at is null) AS allocated_program_id,
    null AS allocated_session_id,
    
    -- Get user_id from source_user_id for program 2
    COALESCE(zz.source_user_id, -2) AS user_id,
    
    -- User details from entrainment data
    TRIM(zz.user_full_name) AS full_name,
    TRIM(LOWER(zz.user_email)) AS email_address,
    TRIM(zz.user_mobile) AS mobile_number,
    
    -- Gender mapping: male/female -> Male/Female
    CASE 
        WHEN TRIM(LOWER(zz.user_gender)) = 'male' THEN 'Male'::gender_enum
        WHEN TRIM(LOWER(zz.user_gender)) = 'female' THEN 'Female'::gender_enum
        ELSE Null
    END AS gender,
    
    -- DOB
    zz.user_dob AS dob,
    
    -- Registration status mapping based on attendance and register_status
    CASE 
        WHEN TRIM(UPPER(zz.attendance)) = 'ATTENDED' THEN 'completed'::registration_status_enum
        WHEN TRIM(LOWER(zz.register_status)) = 'cancelled' THEN 'cancelled'::registration_status_enum
        WHEN TRIM(LOWER(zz.register_status)) = 'denied' THEN 'denied'::registration_status_enum
        WHEN TRIM(LOWER(zz.register_status)) = 'archived' THEN 'archived'::registration_status_enum
        ELSE 'pending'::registration_status_enum
    END AS registration_status,
    
    -- Register status from the data
    TRIM(zz.register_status) AS registration_category,

	TRIM(zz.program_registration_seq_number) AS registration_seq_number,
    -- Registration sequence numbers
   CASE 
        WHEN TRIM(zz.program_registration_seq_number) LIKE 'ENT25%' THEN 
            REPLACE(TRIM(zz.program_registration_seq_number), 'ENT25', '')::integer
        ELSE NULL
    END AS program_registration_seq_number,

   
    
    -- Waiting list sequence number
    CASE 
        WHEN zz.waiting_list_seq_number IS NOT NULL AND zz.waiting_list_seq_number != '' THEN
            CASE 
                WHEN TRIM(zz.waiting_list_seq_number) LIKE 'WL/ENT25%' THEN 
                    REPLACE(TRIM(zz.waiting_list_seq_number), 'WL/ENT25', '')::integer
                WHEN TRIM(zz.waiting_list_seq_number) LIKE 'WL%' THEN 
                    REPLACE(TRIM(zz.waiting_list_seq_number), 'WL', '')::integer
                ELSE NULL
            END
        ELSE NULL 
    END AS waiting_list_seq_number,
    
    TRIM(zz.waiting_list_seq_number) AS wait_list_registration_seq_number,
    
    -- Cancellation details
    zz.cancellation_date AS cancellation_date,
    
    -- Cancelled by - find user by phone number if it's 'sivasankaran' or get admin user
    CASE 
        WHEN TRIM(LOWER(zz.cancelled_by)) = 'sivasankaran' THEN 
            (SELECT id FROM users WHERE TRIM(phone_number) = '8754402031' LIMIT 1)
        WHEN zz.cancelled_by IS NOT NULL AND TRIM(zz.cancelled_by) != '' THEN 
            (SELECT id FROM users u  
             WHERE u.phone_number = '8291673037' LIMIT 1)
        ELSE NULL 
    END AS cancelled_by,
    
    -- RM Contact - find user by rm_bare_mobile
    CASE 
        WHEN UPPER(TRIM(zz.rm_full_name)) = 'OTHER' 
             OR TRIM(zz.rm_bare_mobile) = '9841660000' THEN 
            (SELECT id FROM users WHERE TRIM(phone_number) = '9841660000' LIMIT 1)
        WHEN zz.rm_bare_mobile IS NOT NULL AND TRIM(zz.rm_bare_mobile) != '' THEN 
            (SELECT id FROM users WHERE TRIM(phone_number) = TRIM(zz.rm_bare_mobile) LIMIT 1)
        ELSE NULL 
    END AS rm_contact,
    
    -- RM Notes
    TRIM(zz.rm_notes) AS notes,
    
    -- Preferred room mate logic
    zz.prefered_room_mate_name AS preferred_room_mate,
    
    -- Other RM name if RM is "Other"
    CASE 
        WHEN UPPER(TRIM(zz.rm_full_name)) = 'OTHER' THEN (zz.other_rm_name)
        ELSE NULL
    END AS other_infinitheism_contact,
    
    -- Registration date (current timestamp as we don't have original date)
    NOW() AS registration_date,
    
    -- Created by - use source_user_id for program 2
    COALESCE(zz.source_user_id, -2) AS created_by,

    
    -- Audit fields - will be updated after insert
    NULL AS audit_ref_id,
    NULL AS parent_ref_id,
    
    -- Timestamps
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS updated_by,
    
    -- City mapping with predefined Indian cities
    CASE 
        WHEN TRIM(zz.user_city) IN (
            'Agra', 'Ahmedabad', 'Ajmer', 'Aligarh', 'Allahabad', 'Ambattur', 'Amravati', 'Amritsar', 'Asansol', 'Aurangabad', 
            'Bangalore', 'Bareilly', 'Belgaum', 'Bhavnagar', 'Bhilai Nagar', 'Bhiwandi', 'Bhopal', 'Bhubaneswar', 'Bikaner', 
            'Chandigarh', 'Chennai', 'Coimbatore', 'Cuttack', 'Dehradun', 'Delhi', 'Dhanbad', 'Durgapur', 'Faridabad', 'Firozabad', 
            'Gaya', 'Ghaziabad', 'Gorakhpur', 'Gulbarga', 'Guntur', 'Gurgaon', 'Guwahati', 'Gwalior', 'Haora', 'Hubli And Dharwad', 
            'Hyderabad', 'Indore', 'Jabalpur', 'Jaipur', 'Jalandhar', 'Jalgaon', 'Jammu', 'Jamnagar', 'Jamshedpur', 'Jhansi', 
            'Jodhpur', 'Kalyan & Dombivali', 'Kanpur', 'Kochi', 'Kolapur', 'Kolkata', 'Kota', 'Loni', 'Lucknow', 'Ludhiana', 
            'Madurai', 'Maheshtala', 'Malegaon', 'Mangalore', 'Meerut', 'Mira And Bhayander', 'Moradabad', 'Mumbai', 'Nagpur', 
            'Nanded Waghala', 'Nashik', 'Navi Mumbai', 'Nellore', 'Noida', 'Patna', 'Pimpri & Chinchwad', 'Pune', 'Raipur', 
            'Rajkot', 'Ranchi', 'Saharanpur', 'Salem', 'Sangli Miraj Kupwad', 'Siliguri', 'Solapur', 'Srinagar', 'Surat', 
            'Thane', 'Thiruvananthapuram', 'Tiruchirappalli', 'Tirunelveli', 'Udaipur', 'Ujjain', 'Ulhasnagar', 'Vadodara', 
            'Varanasi', 'Vasai Virar', 'Vijayawada', 'Visakhapatnam', 'Warangal'
        ) THEN TRIM(zz.user_city)
        ELSE 'Other'
    END AS city,
    
    -- Other city name - store original city if not in predefined list
    CASE 
        WHEN TRIM(zz.user_city) NOT IN (
            'Agra', 'Ahmedabad', 'Ajmer', 'Aligarh', 'Allahabad', 'Ambattur', 'Amravati', 'Amritsar', 'Asansol', 'Aurangabad', 
            'Bangalore', 'Bareilly', 'Belgaum', 'Bhavnagar', 'Bhilai Nagar', 'Bhiwandi', 'Bhopal', 'Bhubaneswar', 'Bikaner', 
            'Chandigarh', 'Chennai', 'Coimbatore', 'Cuttack', 'Dehradun', 'Delhi', 'Dhanbad', 'Durgapur', 'Faridabad', 'Firozabad', 
            'Gaya', 'Ghaziabad', 'Gorakhpur', 'Gulbarga', 'Guntur', 'Gurgaon', 'Guwahati', 'Gwalior', 'Haora', 'Hubli And Dharwad', 
            'Hyderabad', 'Indore', 'Jabalpur', 'Jaipur', 'Jalandhar', 'Jalgaon', 'Jammu', 'Jamnagar', 'Jamshedpur', 'Jhansi', 
            'Jodhpur', 'Kalyan & Dombivali', 'Kanpur', 'Kochi', 'Kolapur', 'Kolkata', 'Kota', 'Loni', 'Lucknow', 'Ludhiana', 
            'Madurai', 'Maheshtala', 'Malegaon', 'Mangalore', 'Meerut', 'Mira And Bhayander', 'Moradabad', 'Mumbai', 'Nagpur', 
            'Nanded Waghala', 'Nashik', 'Navi Mumbai', 'Nellore', 'Noida', 'Patna', 'Pimpri & Chinchwad', 'Pune', 'Raipur', 
            'Rajkot', 'Ranchi', 'Saharanpur', 'Salem', 'Sangli Miraj Kupwad', 'Siliguri', 'Solapur', 'Srinagar', 'Surat', 
            'Thane', 'Thiruvananthapuram', 'Tiruchirappalli', 'Tirunelveli', 'Udaipur', 'Ujjain', 'Ulhasnagar', 'Vadodara', 
            'Varanasi', 'Vasai Virar', 'Vijayawada', 'Visakhapatnam', 'Warangal'
        ) THEN TRIM(zz.user_city)
        ELSE NULL
    END AS other_city_name,
    
    -- Country name
    UPPER(TRIM(zz.user_country)) AS country_name,
    
    -- Basic details status - completed for all migrated records
    'completed'::basic_details_status_enum AS basic_details_status,
    
    -- User profile URL
    zz.user_image_url AS user_profile_url,
    
    -- Proforma invoice details from billing data
    TRIM(zz.invoice_billing_name) AS pro_forma_invoice_name,
    TRIM(zz.invoice_billing_address) AS pro_forma_invoice_address,
    TRIM(zz.program_registration_seq_number) AS proforma_invoice_seq_number,
    TRIM(zz.invoice_billing_gst) AS pro_forma_gst_number,
    CASE 
        WHEN zz.invoice_billing_gst IS NOT NULL AND TRIM(zz.invoice_billing_gst) != '' THEN TRUE
        ELSE FALSE
    END AS pro_forma_is_gst_registered,
    TRIM(zz.invoice_billing_pin) AS pro_forma_zip,
    
    -- Additional contact info
    TRIM(zz.transport_emergency_contact_mobile_number) AS alternate_phone_number, -- Using mobile as alternate phone
    
    -- Comments from various sources
    NULL AS comments,
    NULL AS rm_review,

    -- Terms accepted - assume true for migrated data
    FALSE AS terms_accepted,

    -- HDB related fields - set defaults for entrainment
    0 AS no_of_hdbs,
    NULL AS last_hdb_attended,
    NULL AS hdb_association_since,
    
    -- Song preferences - set null for entrainment
    NULL AS first_song_preference,
    NULL AS second_song_preference
    
FROM zz_ref_entrainment_program_registration_info zz
-- No JOIN with users table needed for program 2 - use source_user_id directly
-- Get the EN25 program session
CROSS JOIN (
    SELECT ps.id 
    FROM program_session ps 
    JOIN program_v1 pv ON ps.program_id = pv.id 
    WHERE pv.code = 'EN25' 
	and pv.deleted_at is null
    LIMIT 1
) ps
-- Only process records where program_id = 2 (as per your requirement)
WHERE zz.program_id = 2;


UPDATE hdb_program_registration 
SET audit_ref_id = id, parent_ref_id = id 
WHERE audit_ref_id IS NULL 
  AND parent_ref_id IS NULL
  AND updated_by = -2  -- Only update records created by this migration
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN25' and deleted_at is null);


-- Verify the migration results
SELECT 'Entrainment Registration Count' as metric, COUNT(*)::text as count
FROM hdb_program_registration
WHERE updated_by = -2
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN25' and deleted_at is null)
UNION ALL
SELECT 'By Registration Status' as metric, registration_status || ': ' || COUNT(*)::text as count
FROM hdb_program_registration
WHERE updated_by = -2
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN25' and deleted_at is null)
GROUP BY registration_status
UNION ALL
SELECT 'By Attendance Status' as metric, 
       CASE WHEN registration_status = 'completed' THEN 'Attended' ELSE 'Not Attended' END || ': ' || COUNT(*)::text as count
FROM hdb_program_registration
WHERE updated_by = -2
  AND program_id = (SELECT id FROM program_v1 WHERE code = 'EN25' and deleted_at is null)
GROUP BY CASE WHEN registration_status = 'completed' THEN 'Attended' ELSE 'Not Attended' END;




INSERT INTO user_participation_summary (
    user_id,
    program_id,
    program_name,
    sub_program_id,
    sub_program_name,
    session_id,
    session_name,
    program_starts_at,
    program_ends_at,
    created_by,
    updated_by,
    created_at,
    updated_at,
	sub_program_type
)
-- Dynamic data extraction for all programs
SELECT
    reg.user_id,
    reg.program_id,
    prog.name AS program_name,
    reg.allocated_program_id AS sub_program_id,
    CASE 
        WHEN reg.allocated_program_id IS NOT NULL 
        THEN (SELECT name FROM program_v1 WHERE id = reg.allocated_program_id LIMIT 1)
        ELSE NULL
    END AS sub_program_name,
    reg.program_session_id AS session_id,
    ps.name AS session_name,
    COALESCE(prog.starts_at, NULL) AS program_starts_at,
    COALESCE(prog.ends_at, NULL) AS program_ends_at,
    -2 AS created_by,
    -2 AS updated_by,
    NOW() AS created_at,
    NOW() AS updated_at,
	'PST_ENTRAINMENT'::sub_program_type_enum AS sub_program_type
FROM hdb_program_registration reg
JOIN program_v1 prog 
    ON prog.id = reg.program_id
LEFT JOIN program_session ps 
    ON ps.id = reg.program_session_id
WHERE reg.user_id IS NOT NULL
  AND reg.registration_status = 'completed'
  AND reg.updated_by = -2  -- Only include migrated registrations
  AND prog.code IN ('EN24', 'EN25')  -- Only Entrainment programs
ORDER BY reg.id;


select * from program_v1 where name ilike '%ent%';
select count(*) from hdb_program_registration where registration_status = 'completed' and program_id in (
select id from program_v1 where name ilike '%ent%'
);


select * from user_participation_summary where program_id = 52;



