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;


ALTER TYPE registration_status_enum ADD VALUE IF NOT EXISTS 'denied';
ALTER TYPE registration_status_enum ADD VALUE IF NOT EXISTS 'archived';

-- Add registration_category column to hdb_program_registration if it doesn't exist
ALTER TABLE hdb_program_registration 
ADD COLUMN IF NOT EXISTS registration_category character varying(255);



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
) as ps
-- 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 by matching user_bare_mobile with users.phone_number (like EN24)
    CASE 
        WHEN u.id IS NOT NULL THEN u.id
        ELSE NULL
    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 '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 matched user_id (like EN24)
    CASE 
        WHEN u.id IS NOT NULL THEN u.id
        ELSE NULL 
    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 (like EN24)
JOIN users u ON TRIM(zz.user_bare_mobile) = TRIM(u.phone_number)
-- 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;


ALTER TYPE sub_program_type_enum ADD VALUE 'PST_ENTRAINMENT';

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 user_participation_summary;


select * from hdb_program_registration where program_id in (select id from program_v1 where program ilike '%PT_ENT%')order by id desc;
