---------------------
select * from program_v1;
SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD';
select * from program_type_v1;
INSERT INTO program_v1 (id, program, program_type_id, name, description, 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, no_of_session, 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, meta, created_at, updated_at, deleted_at, created_by, updated_by, is_approval_required, is_residence_required, is_travel_involved, group_id, is_primary_program, primary_program_id, group_display_order, 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, venue_address_id, invoice_sender_address, helpline_number, email_sender_name, venue_name_in_emails, launch_date, logo_url, checkin_ends_at, checkout_ends_at, start_date, start_time, end_date, end_time, registration_start_date, registration_start_time, registration_end_date, registration_end_time) VALUES
(nextval('program_v1_id_seq'), '2024 - 2025', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB/MSD 2024-25', 'A residential spiritual retreat with Mahatria', 'offline', 'NA', 8,TRUE, 'yearly', '09:00:00', '18:00:00', '8 days',TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE, 'program',FALSE,TRUE,TRUE, 0, 1000,1000, 0,0,1000, NULL, 1.00, 18.00, 'INR','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', 'completed',TRUE, 'HDB', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png','{"price":[{"HDB":2},{"MSD":1}]}', '2024-07-29 15:03:56.323064+05:30', '2024-07-29 15:03:56.323064+05:30', NULL,-2,-2,FALSE,FALSE,FALSE, 'b2e3f4a5-6789-41bc-8def-2345678901bc',TRUE, NULL, 1, 9.00, 9.00, 18.00, 0.00, '2024-08-15 05:30:00+05:30', '2024-08-31 05:29:59+05:30', '2024-11-27 10:30:00+05:30', '2025-02-07 02:30:00+05:30', '2024-11-28 08:30:00+05:30', '2025-02-06 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2024-25', 'AACCI7694H', 'U74900TN2011PTC083263', NULL,'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2024-25','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2025-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2024-11-27 22:30:00+05:30', '2025-02-07 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(nextval('program_v1_id_seq'), '2024 - 2025', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB 1 - 28-Nov-2024 to 06-Dec-2024', 'A residential spiritual retreat with Mahatria', 'offline', 'NA', 6,TRUE, 'yearly', '09:00:00', '18:00:00', '6 days',TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE, 'program',FALSE,TRUE,TRUE, 0, 1000,202, 0, 0,202, NULL, 1.00, 18.00, 'INR','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', 'completed',TRUE, 'HDB', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png', '{}', '2024-07-29 15:03:56.323064+05:30', '2024-07-29 15:03:56.323064+05:30', NULL,-2,-2,FALSE,FALSE,FALSE, 'b2e3f4a5-6789-41bc-8def-2345678901bc',FALSE,NULL, 2, 9.00, 9.00, 18.00, 0.00, '2024-08-15 05:30:00+05:30', '2024-08-31 05:29:59+05:30', '2024-11-27 10:30:00+05:30', '2024-12-07 02:30:00+05:30', '2024-11-28 08:30:00+05:30', '2024-12-06 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2024-25', 'AACCI7694H', 'U74900TN2011PTC083263', NULL,'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2024-25','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2025-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2024-11-27 22:30:00+05:30', '2024-12-07 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(nextval('program_v1_id_seq'), '2024 - 2025', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB 2 - 19-Dec-2024 to 27-Dec-2024', 'A residential spiritual retreat with Mahatria', 'offline', 'NA', 6,TRUE, 'yearly', '09:00:00', '18:00:00', '9 days',TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE, 'program',FALSE,TRUE,TRUE, 0, 1000,204, 0, 0,204, NULL, 2.00, 18.00, 'INR','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', 'completed',TRUE, 'HDB', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png', '{}', '2024-07-29 15:03:56.323064+05:30', '2024-07-29 15:03:56.323064+05:30', NULL,-2,-2,FALSE,FALSE,FALSE, 'b2e3f4a5-6789-41bc-8def-2345678901bc',FALSE,NULL, 3, 9.00, 9.00, 18.00, 0.00, '2024-08-15 05:30:00+05:30', '2024-08-31 05:29:59+05:30', '2024-12-18 10:30:00+05:30', '2024-12-28 02:30:00+05:30', '2024-12-19 08:30:00+05:30', '2024-12-27 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2024-25', 'AACCI7694H', 'U74900TN2011PTC083263', NULL,'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2024-25','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2025-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2024-12-18 22:30:00+05:30', '2024-12-28 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(nextval('program_v1_id_seq'), '2024 - 2025', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB 3 - 9-Jan-2025 to 17-Jan-2025', 'A residential spiritual retreat with Mahatria', 'offline', 'NA', 6,TRUE, 'yearly', '09:00:00', '18:00:00', '6 days',TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE, 'program',FALSE,TRUE,TRUE, 0, 1000,201, 0, 0,201, NULL, 1.00, 18.00, 'INR','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', 'completed',TRUE, 'HDB', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png', '{}', '2024-07-29 15:03:56.323064+05:30', '2024-07-29 15:03:56.323064+05:30', NULL,-2,-2,FALSE,FALSE,FALSE, 'b2e3f4a5-6789-41bc-8def-2345678901bc',FALSE,NULL, 4, 9.00, 9.00, 18.00, 0.00, '2024-08-15 05:30:00+05:30', '2024-08-31 05:29:59+05:30', '2025-01-08 10:30:00+05:30', '2025-01-18 02:30:00+05:30', '2025-01-09 08:30:00+05:30', '2025-01-17 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2024-25', 'AACCI7694H', 'U74900TN2011PTC083263', NULL,'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2024-25','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2025-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2025-01-08 22:30:00+05:30', '2025-01-18 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(nextval('program_v1_id_seq'), '2024 - 2025', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'MSD - 27-Jan-2025 to 01-Feb-2025', 'A residential spiritual retreat with Mahatria', 'offline', 'NA', 6,TRUE, 'yearly', '09:00:00', '18:00:00', '9 days',TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE, 'program',FALSE,TRUE,TRUE, 0, 1000,200, 0, 0,200, NULL, 2.00, 18.00, 'INR','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', 'completed',TRUE, 'MSD', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png', '{}', '2024-07-29 15:03:56.323064+05:30', '2024-07-29 15:03:56.323064+05:30', NULL,-2,-2,FALSE,FALSE,FALSE, 'b2e3f4a5-6789-41bc-8def-2345678901bc',FALSE,NULL, 5, 9.00, 9.00, 18.00, 0.00, '2024-08-15 05:30:00+05:30', '2024-08-31 05:29:59+05:30', '2025-01-26 10:30:00+05:30', '2025-02-02 02:30:00+05:30', '2025-01-27 08:30:00+05:30', '2025-02-01 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2024-25', 'AACCI7694H', 'U74900TN2011PTC083263', NULL,'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2024-25','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2025-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2025-01-26 22:30:00+05:30', '2025-02-02 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(nextval('program_v1_id_seq'), '2024 - 2025', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'MSD 2 - 01-Feb-2025 to 06-Feb-2025', 'A residential spiritual retreat with Mahatria', 'offline', 'NA', 6,TRUE, 'yearly', '09:00:00', '18:00:00', '9 days',TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE, 'program',FALSE,TRUE,TRUE, 0, 1000,196, 0, 0,196, NULL, 2.00, 18.00, 'INR','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', 'completed',TRUE, 'MSD', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png', '{}', '2024-07-29 15:03:56.323064+05:30', '2024-07-29 15:03:56.323064+05:30', NULL,-2,-2,FALSE,FALSE,FALSE, 'b2e3f4a5-6789-41bc-8def-2345678901bc',FALSE,NULL, 6, 9.00, 9.00, 18.00, 0.00, '2024-08-15 05:30:00+05:30', '2024-08-31 05:29:59+05:30', '2025-01-31 10:30:00+05:30', '2025-02-07 02:30:00+05:30', '2025-02-01 08:30:00+05:30', '2025-02-06 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2024-25', 'AACCI7694H', 'U74900TN2011PTC083263', NULL,'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2024-25','Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2025-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2025-01-31 22:30:00+05:30', '2025-02-07 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);



-- Program Session INSERT statements for HDB/MSD 2024-25 programs
-- These sessions correspond to the programs created above

INSERT INTO program_session (
    program_id,
    name,
    description,
    registration_starts_at,
    registration_ends_at,
    checkin_at,
    checkout_at,
    starts_at,
    ends_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,
    total_seats,
    waitlist_trigger_count,
    available_seats,
    filled_seats,
    max_capacity,
    base_price,
    gst_percentage,
    cgst,
    sgst,
    igst,
    tds_percent,
    gst_number,
    tds_applicability,
    invoice_sender_name,
    invoice_sender_pan,
    invoice_sender_cin,
    invoice_sender_address,
    venue_address_id,
    currency,
    venue,
    helpline_number,
    email_sender_name,
    venue_name_in_emails,
    launch_date,
    status,
    is_active,
    code,
    display_order,
    banner_image_url,
    meta,
    logo_url,
    created_at,
    updated_at,
    created_by,
    updated_by,
    checkin_ends_at,
    checkout_ends_at
) VALUES
-- Main HDB/MSD 2024-25 Session (parent program)
(
    (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2024-25'),
    'HDB/MSD 2024-25 Main Session',
    'Main HDB/MSD 2024-25 program session',
    '2024-08-15 05:30:00+05:30',
    '2024-08-31 05:29:59+05:30',
    '2024-11-27 10:30:00+05:30',
    '2025-02-07 02:30:00+05:30',
    '2024-11-28 08:30:00+05:30',
    '2025-02-06 22:30:00+05:30',
    'offline',
    'NA',
    8,
    true,
    'yearly',
    '09:00:00',
    '18:00:00',
    '8 days',
    true,
    true,
    true,
    true,
    true,
    false,
    false,
    true,
    'session',
    false,
    true,
    true,
    1000,
    0,
    0,
    1000,
    1000,
    1.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2024-25',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
    NULL,
    'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91 98416 70000',
    'HDB/MSD 2024-25',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-11-17 14:30:00+05:30',
    'active',
    true,
    'HDB2425',
    0,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png',
    '{"price":[{"HDB":2},{"MSD":1}]}',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
    '2024-07-29 15:03:56.323064+05:30',
    '2024-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2024-11-27 22:30:00+05:30',
    '2025-02-07 10:30:00+05:30'
),
-- HDB 1 Session (program_id from HDB 1 program)
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 1 - 28-Nov-2024 to 06-Dec-2024'),
    'HDB 1 Session - Nov 2024',
    'First HDB session for Nov-Dec 2024',
    '2024-08-15 05:30:00+05:30',
    '2024-08-31 05:29:59+05:30',
    '2024-11-27 10:30:00+05:30',
    '2024-12-07 02:30:00+05:30',
    '2024-11-28 08:30:00+05:30',
    '2024-12-06 22:30:00+05:30',
    'offline',
    'NA',
    6,
    true,
    'yearly',
    '09:00:00',
    '18:00:00',
    '6 days',
    true,
    true,
    true,
    true,
    true,
    false,
    false,
    true,
    'session',
    false,
    true,
    true,
    202,
    0,
    202,
    0,
    202,
    1.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2024-25',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
    NULL,
    'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91 98416 70000',
    'HDB/MSD 2024-25',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-11-17 14:30:00+05:30',
    'active',
    true,
    'HDB1',
    1,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png',
    '{}',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
    '2024-07-29 15:03:56.323064+05:30',
    '2024-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2024-11-27 22:30:00+05:30',
    '2024-12-07 10:30:00+05:30'
),
-- HDB 2 Session (program_id from HDB 2 program)
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 2 - 19-Dec-2024 to 27-Dec-2024'),
    'HDB 2 Session - Dec 2024',
    'Second HDB session for Dec 2024',
    '2024-08-15 05:30:00+05:30',
    '2024-08-31 05:29:59+05:30',
    '2024-12-18 10:30:00+05:30',
    '2024-12-28 02:30:00+05:30',
    '2024-12-19 08:30:00+05:30',
    '2024-12-27 22:30:00+05:30',
    'offline',
    'NA',
    6,
    true,
    'yearly',
    '09:00:00',
    '18:00:00',
    '9 days',
    true,
    true,
    true,
    true,
    true,
    false,
    false,
    true,
    'session',
    false,
    true,
    true,
    204,
    0,
    204,
    0,
    204,
    2.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2024-25',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
    NULL,
    'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91 98416 70000',
    'HDB/MSD 2024-25',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-11-17 14:30:00+05:30',
    'active',
    true,
    'HDB2',
    2,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png',
    '{}',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
    '2024-07-29 15:03:56.323064+05:30',
    '2024-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2024-12-18 22:30:00+05:30',
    '2024-12-28 10:30:00+05:30'
),
-- HDB 3 Session (program_id from HDB 3 program)
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 3 - 9-Jan-2025 to 17-Jan-2025'),
    'HDB 3 Session - Jan 2025',
    'Third HDB session for Jan 2025',
    '2024-08-15 05:30:00+05:30',
    '2024-08-31 05:29:59+05:30',
    '2025-01-08 10:30:00+05:30',
    '2025-01-18 02:30:00+05:30',
    '2025-01-09 08:30:00+05:30',
    '2025-01-17 22:30:00+05:30',
    'offline',
    'NA',
    6,
    true,
    'yearly',
    '09:00:00',
    '18:00:00',
    '6 days',
    true,
    true,
    true,
    true,
    true,
    false,
    false,
    true,
    'session',
    false,
    true,
    true,
    201,
    0,
    201,
    0,
    201,
    1.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2024-25',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
    NULL,
    'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91 98416 70000',
    'HDB/MSD 2024-25',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-11-17 14:30:00+05:30',
    'active',
    true,
    'HDB3',
    3,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png',
    '{}',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
    '2024-07-29 15:03:56.323064+05:30',
    '2024-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2025-01-08 22:30:00+05:30',
    '2025-01-18 10:30:00+05:30'
),
-- MSD Session (program_id from MSD program)
(
    (SELECT id FROM program_v1 WHERE name = 'MSD - 27-Jan-2025 to 01-Feb-2025'),
    'MSD Session - Jan-Feb 2025',
    'MSD session for Jan-Feb 2025',
    '2024-08-15 05:30:00+05:30',
    '2024-08-31 05:29:59+05:30',
    '2025-01-26 10:30:00+05:30',
    '2025-02-02 02:30:00+05:30',
    '2025-01-27 08:30:00+05:30',
    '2025-02-01 22:30:00+05:30',
    'offline',
    'NA',
    6,
    true,
    'yearly',
    '09:00:00',
    '18:00:00',
    '9 days',
    true,
    true,
    true,
    true,
    true,
    false,
    false,
    true,
    'session',
    false,
    true,
    true,
    200,
    0,
    200,
    0,
    200,
    2.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2024-25',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
    NULL,
    'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91 98416 70000',
    'HDB/MSD 2024-25',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-11-17 14:30:00+05:30',
    'active',
    true,
    'MSD1',
    4,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png',
    '{}',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
    '2024-07-29 15:03:56.323064+05:30',
    '2024-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2025-01-26 22:30:00+05:30',
    '2025-02-02 10:30:00+05:30'
),
-- MSD 2 Session (program_id from MSD 2 program)
(
    (SELECT id FROM program_v1 WHERE name = 'MSD 2 - 01-Feb-2025 to 06-Feb-2025'),
    'MSD 2 Session - Feb 2025',
    'Second MSD session for Feb 2025',
    '2024-08-15 05:30:00+05:30',
    '2024-08-31 05:29:59+05:30',
    '2025-01-31 10:30:00+05:30',
    '2025-02-07 02:30:00+05:30',
    '2025-02-01 08:30:00+05:30',
    '2025-02-06 22:30:00+05:30',
    'offline',
    'NA',
    6,
    true,
    'yearly',
    '09:00:00',
    '18:00:00',
    '9 days',
    true,
    true,
    true,
    true,
    true,
    false,
    false,
    true,
    'session',
    false,
    true,
    true,
    196,
    0,
    196,
    0,
    196,
    2.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2024-25',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
    NULL,
    'INR',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '+91 98416 70000',
    'HDB/MSD 2024-25',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2025-11-17 14:30:00+05:30',
    'active',
    true,
    'MSD2',
    5,
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png',
    '{}',
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
    '2024-07-29 15:03:56.323064+05:30',
    '2024-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2025-01-31 22:30:00+05:30',
    '2025-02-07 10:30:00+05:30'
);

-- Verification queries
SELECT 
    p.name as program_name,
    ps.name as session_name,
    ps.code as session_code,
    ps.display_order,
    ps.total_seats,
    ps.available_seats,
    ps.starts_at,
    ps.ends_at
FROM program_session ps
JOIN program_v1 p ON ps.program_id = p.id
WHERE p.program LIKE '%2024 - 2025%'
AND ps.created_by = -2
ORDER BY ps.display_order;


select * from program_session;
select json_agg(t) from(
select * from hdb_question where created_by = -2
)t;



select id from program_v1 where name = 'HDB/MSD 2024-25';
--2024-25
SELECT 
    id,
    binding_key,
    label
FROM hdb_question
WHERE created_by = -2 
  AND binding_key IN (
    'name',
    'email',
    'mobileNumber',
    'gender',
    'dob',
    'city',
    'countryName',
    'rmContact',
    'QK_LAST_HDB',
    'QK_HDB_ASSOCIATION_SINCE',
    'no_of_hdbs',
    'whichOfTheFollowingHaveYouExperienced',
    'QK_HDB_SONG_1',
    'QK_HDB_SONG_2',
    'invoiceEmail',
    'invoiceName',
    'invoiceAddress',
    'tanNumber',
    'gstNumber',
    'isGstRegistered',
    'pictureUrl',
    'idBack'
)
ORDER BY binding_key; -- 22
select * from program_v1 where deleted_at is null order by id desc;
--create program question map


-- Insert mapping for 2024-25 program using binding_key + form_section_id from hdb_question
INSERT INTO hdb_program_question (
    program_id,
    form_section_id,
    question_id,
    display_order,
    registration_level,
    created_at,
    updated_at,
    created_by,
    updated_by,
    deleted_at
)
SELECT 
    (select id from program_v1 where name = 'HDB/MSD 2024-25'),                -- replace with actual program_id for 2024-25
    q.form_section_id,             -- take section id from hdb_question
    q.id AS question_id,
    ROW_NUMBER() OVER (PARTITION BY q.form_section_id ORDER BY q.binding_key) - 1 AS display_order,
    'program' AS registration_level,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by,
    NULL AS deleted_at
FROM hdb_question q
WHERE q.created_by = -2 
  AND q.binding_key IN (
    'name',
    'email',
    'mobileNumber',
    'gender',
    'dob',
    'city',
    'otherCityName',
    'countryName',
    'rmContact',
    'otherInfinitheismContact',
    'QK_LAST_HDB',
    'QK_HDB_ASSOCIATION_SINCE',
    'no_of_hdbs',
    'whichOfTheFollowingHaveYouExperienced',
    'QK_HDB_SONG_1',
    'QK_HDB_SONG_2',
    'invoiceEmail',
    'invoiceName',
    'invoiceAddress',
    'tanNumber',
    'gstNumber',
    'isGstRegistered',
    'pictureUrl',
    'idBack'
)
ORDER BY q.form_section_id, q.binding_key;

select * from hdb_program_question;

select * from public.hdb_registration_question_answer;

----------------------
select city, other_city_name from hdb_program_registration where city = 'Other';
 
INSERT INTO hdb_program_registration (
    program_session_id,
    program_id,
    allocated_program_id,
    user_id,
    full_name,
    email_address,
    mobile_number,
    gender,
    dob,
    city,
    other_city_name,
    country_name,
    rm_contact,
    other_infinitheism_contact,
    no_of_hdbs,
    last_hdb_attended,
    hdb_association_since,
    first_song_preference,
    second_song_preference,
    pro_forma_invoice_name,
    pro_forma_invoice_address,
    pro_forma_gst_number,
    pro_forma_is_gst_registered,
    proforma_invoice_seq_number,
    registration_status,
    basic_details_status,
    registration_seq_number,
    program_registration_seq_number,
    registration_date,
    audit_ref_id,
    parent_ref_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    -- Determine program_session_id based on hdb_allocation using program_v1 names
    NULL as program_session_id,
    
    -- Program ID - always the main HDB/MSD 2024-25 program
    (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2024-25') AS program_id,

    -- Allocated Program ID - determine based on hdb_allocation
    CASE 
        WHEN zz.hdb_allocation ILIKE '%HDB 1%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'HDB 1 - 28-Nov-2024 to 06-Dec-2024')
        WHEN zz.hdb_allocation ILIKE '%HDB 2%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'HDB 2 - 19-Dec-2024 to 27-Dec-2024')
        WHEN zz.hdb_allocation ILIKE '%HDB 3%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'HDB 3 - 9-Jan-2025 to 17-Jan-2025')
        WHEN zz.hdb_allocation ILIKE '%MSD 1%' OR zz.hdb_allocation ILIKE '%MSD%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'MSD - 27-Jan-2025 to 01-Feb-2025')
        WHEN zz.hdb_allocation ILIKE '%MSD 2%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'MSD 2 - 01-Feb-2025 to 06-Feb-2025')
        ELSE 
            NULL -- Default to main program
    END AS allocated_program_id,
    
    -- User ID - find user by matching bare_mobile with users.phone_number
    (SELECT u.id 
     FROM users u 
     WHERE TRIM(u.phone_number) = TRIM(zz.bare_mobile)
     LIMIT 1) AS user_id,
    
    -- Basic details mapping
    TRIM(zz.name) AS full_name,
    TRIM(LOWER(zz.email)) AS email_address,
    TRIM(zz.mobile) AS mobile_number,
    
    -- Gender mapping (ensure it matches enum values)
    CASE 
        WHEN LOWER(TRIM(zz.gender)) IN ('male', 'm') THEN 'Male'::gender_enum
        WHEN LOWER(TRIM(zz.gender)) IN ('female', 'f') THEN 'Female'::gender_enum
        WHEN LOWER(TRIM(zz.gender)) IN ('other', 'o') THEN 'Other'::gender_enum
        ELSE 'Male'::gender_enum -- Default fallback
    END AS gender,
    
    -- Date of birth conversion - handle DD-MMM-YYYY format and convert to YYYY-MM-DD
    CASE 
        WHEN zz.date_of_birth IS NOT NULL AND zz.date_of_birth != '' THEN 
            CASE
                -- Try YYYY-MM-DD format first
                WHEN zz.date_of_birth ~ '^\d{4}-\d{2}-\d{2}$' THEN 
                    TO_DATE(zz.date_of_birth, 'YYYY-MM-DD')
                -- Try DD-MM-YYYY format
                WHEN zz.date_of_birth ~ '^\d{2}-\d{2}-\d{4}$' THEN 
                    TO_DATE(zz.date_of_birth, 'DD-MM-YYYY')
                -- Try DD-MMM-YYYY format (e.g., 15-Dec-1990)
                WHEN zz.date_of_birth ~ '^\d{1,2}-[A-Za-z]{3}-\d{4}$' THEN 
                    TO_DATE(zz.date_of_birth, 'DD-Mon-YYYY')
                -- Try other common formats or skip if unparseable
                ELSE NULL
            END
        ELSE NULL
    END AS dob,
    
    -- City handling - use from cities list or set to 'Other'
    CASE 
        WHEN TRIM(zz.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.city)
        ELSE 'Other'
    END AS city,
    
    -- Other city name - store original city if not in predefined list
    CASE 
        WHEN TRIM(zz.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.city)
        ELSE NULL
    END AS other_city_name,
    
    TRIM(zz.country) AS country_name,
    
    -- RM Contact - find user by phone number, handle special case for 9841660000
    CASE 
        WHEN UPPER(TRIM(zz.contact_person)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION')
            OR (CASE UPPER(TRIM(zz.contact_person))
                WHEN 'CHARUTA' THEN '9011240808'
                WHEN 'CHITHAMBARAN' THEN '9790768202'
                WHEN 'SRIDHAR' THEN '9841727022'
                WHEN 'MUKUNDAN' THEN '9884874161'
                WHEN 'VINOD' THEN '8094444402'
                WHEN 'ARUN' THEN '9845191961'                
                WHEN 'RAMEL' THEN '9841660000'
                WHEN 'GOPINATH' THEN '9841660000'
                WHEN 'ATHIAMAN' THEN '9841660000'
                WHEN 'OTHER' THEN '9841660000'
                WHEN 'GOPAL' THEN '9841021495'
                WHEN 'BHUPENDHAR' THEN '9841149060'
                WHEN 'SAKTHI' THEN '9841660000'
                WHEN 'JAYA' THEN '9841660000'
                WHEN 'MANOHAR' THEN '9841042289'
                WHEN 'KISHAN' THEN '9966279944'
                WHEN 'SWAROOP' THEN '9657152211'
                WHEN 'VIJISUBBU' THEN '8939910100'
                WHEN 'ORGANIZATION' THEN '9841660000'
                WHEN 'SUBBU' THEN '9841405117'
                WHEN 'MAHESH' THEN '9791140214'
                WHEN 'MOHINI' THEN '9880702700'
                ELSE NULL
            END) = '9841660000' 
        THEN NULL  -- Set to NULL for generic contact
        ELSE 
            (SELECT u.id 
             FROM users u 
             WHERE TRIM(u.phone_number) = (
                CASE UPPER(TRIM(zz.contact_person))
                    WHEN 'CHARUTA' THEN '9011240808'
                    WHEN 'CHITHAMBARAN' THEN '9790768202'
                    WHEN 'SRIDHAR' THEN '9841727022'
                    WHEN 'MUKUNDAN' THEN '9884874161'
                    WHEN 'VINOD' THEN '8094444402'
                    WHEN 'ARUN' THEN '9845191961'                    WHEN 'GOPAL' THEN '9841021495'
                    WHEN 'BHUPENDHAR' THEN '9841149060'
                    WHEN 'MANOHAR' THEN '9841042289'
                    WHEN 'KISHAN' THEN '9966279944'
                    WHEN 'SWAROOP' THEN '9657152211'
                    WHEN 'VIJISUBBU' THEN '8939910100'
                   WHEN 'SUBBU' THEN '9841405117'
                    WHEN 'MAHESH' THEN '9791140214'
                    WHEN 'MOHINI' THEN '9880702700'
                    ELSE NULL
                END
             )
             LIMIT 1)
    END AS rm_contact,
    
    -- Other Infinitheism Contact - store contact person name for generic contact (9841660000)
    CASE 
        WHEN UPPER(TRIM(zz.contact_person)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION')
            OR (CASE UPPER(TRIM(zz.contact_person))
                WHEN 'CHARUTA' THEN '9011240808'
                WHEN 'CHITHAMBARAN' THEN '9790768202'
                WHEN 'SRIDHAR' THEN '9841727022'
                WHEN 'MUKUNDAN' THEN '9884874161'
                WHEN 'VINOD' THEN '8094444402'
                WHEN 'ARUN' THEN '9845191961'                
                WHEN 'RAMEL' THEN '9841660000'
                WHEN 'GOPINATH' THEN '9841660000'
                WHEN 'ATHIAMAN' THEN '9841660000'
                WHEN 'OTHER' THEN '9841660000'
                WHEN 'GOPAL' THEN '9841021495'
                WHEN 'BHUPENDHAR' THEN '9841149060'
                WHEN 'SAKTHI' THEN '9841660000'
                WHEN 'JAYA' THEN '9841660000'
                WHEN 'MANOHAR' THEN '9841042289'
                WHEN 'KISHAN' THEN '9966279944'
                WHEN 'SWAROOP' THEN '9657152211'
                WHEN 'VIJISUBBU' THEN '8939910100'
                WHEN 'ORGANIZATION' THEN '9841660000'
               WHEN 'SUBBU' THEN '9841405117'
                WHEN 'MAHESH' THEN '9791140214'
                WHEN 'MOHINI' THEN '9880702700'
                ELSE NULL
            END) = '9841660000' 
        THEN CASE 
            WHEN UPPER(TRIM(zz.contact_person)) = 'OTHER' THEN 'OtherInfinitheismContact'
            ELSE TRIM(zz.contact_person)
        END
        ELSE NULL
    END AS other_infinitheism_contact,
    
    -- Number of HDBs
    CASE 
        WHEN zz.how_many_hdb_msd_have_you_done ~ '^[0-9]+$' THEN 
            zz.how_many_hdb_msd_have_you_done::INTEGER
        ELSE 0
    END AS no_of_hdbs,
    
    TRIM(zz.when_was_your_last_hdb_msd) AS last_hdb_attended,
    TRIM(zz.since_when_have_you_been_associated_with_this_path) AS hdb_association_since,
    TRIM(zz.song_preference_1) AS first_song_preference,
    TRIM(zz.song_preference_2) AS second_song_preference,
    
    -- Invoice details
    TRIM(zz.name_in_which_invoice_to_be_issued) AS pro_forma_invoice_name,
    TRIM(zz.invoice_address) AS pro_forma_invoice_address,
    TRIM(zz.gstin) AS pro_forma_gst_number,
    
    -- GST registration status
    CASE 
        WHEN LOWER(TRIM(zz.are_you_registered_under_gst)) IN ('yes', 'y', 'true', '1') THEN true
        ELSE false
    END AS pro_forma_is_gst_registered,
    
    -- Generate proforma invoice sequence only for confirmed registrations
    CASE 
        WHEN LOWER(TRIM(zz.status)) = 'confirmed' THEN 
            CONCAT(
                'HDB2425',
                LPAD(
                    (ROW_NUMBER() OVER (
                        PARTITION BY CASE WHEN LOWER(TRIM(zz.status)) = 'confirmed' THEN 1 ELSE 0 END 
                        ORDER BY zz.name
                    ))::TEXT,
                    5,
                    '0'
                )
            )
        ELSE NULL -- NULL for Hold and Registered records
    END AS proforma_invoice_seq_number,
    
    -- Registration status based on status field
    CASE 
        WHEN LOWER(TRIM(zz.status)) = 'confirmed' THEN 'completed'::registration_status_enum
        WHEN LOWER(TRIM(zz.status)) = 'hold' THEN 'rejected'::registration_status_enum
        WHEN LOWER(TRIM(zz.status)) = 'registered' THEN 'pending'::registration_status_enum
        ELSE 'pending'::registration_status_enum
    END AS registration_status,
    
    -- Basic details status
    CASE 
        WHEN LOWER(TRIM(zz.status)) IN ('confirmed', 'hold', 'registered') THEN 'completed'::basic_details_status_enum
        ELSE 'completed'::basic_details_status_enum
    END AS basic_details_status,
    
    -- Generate registration sequence number for all registrations (following insert order)
    CONCAT(
        'HDB2425',
        LPAD(
            (ROW_NUMBER() OVER (
                ORDER BY 
                    CASE LOWER(TRIM(zz.status))
                        WHEN 'confirmed' THEN 1
                        WHEN 'registered' THEN 2
                        WHEN 'hold' THEN 3
                        ELSE 4
                    END,
                    zz.name
            ))::TEXT,
            5,
            '0'
        )
    ) AS registration_seq_number,
    
    -- Generate program registration sequence number (integer, following insert order)
    (ROW_NUMBER() OVER (
        ORDER BY 
            CASE LOWER(TRIM(zz.status))
                WHEN 'confirmed' THEN 1
                WHEN 'registered' THEN 2
                WHEN 'hold' THEN 3
                ELSE 4
            END,
            zz.name
    ))::INTEGER AS program_registration_seq_number,
    
    -- Registration date
    NOW() AS registration_date,
    
    -- Audit ref ID and parent ref ID will be set to the registration ID after insert
    NULL AS audit_ref_id,
    NULL AS parent_ref_id,
    
    NOW() AS created_at,
    NOW() AS updated_at,
    (SELECT u.id 
     FROM users u 
     WHERE TRIM(u.phone_number) = TRIM(zz.bare_mobile)
     LIMIT 1) AS created_by,
    -2 AS updated_by
    
FROM zz_ref_20250714_hdb_info_asis_trimmed zz
-- WHERE EXISTS (
--     SELECT 1 FROM users u 
--     WHERE TRIM(u.phone_number) = TRIM(zz.bare_mobile)
-- )
ORDER BY 
    CASE LOWER(TRIM(zz.status))
        WHEN 'confirmed' THEN 1  -- Process confirmed first for invoice sequence
        WHEN 'registered' THEN 2
        WHEN 'hold' THEN 3
        ELSE 4
    END;

-- ================================================================================================
-- 1.1. UPDATE audit_ref_id and parent_ref_id to match registration ID
-- ================================================================================================

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;




-- ================================================================================================
-- 2. INSERT INTO hdb_registration_question_answer
-- ================================================================================================
select * from hdb_registration_question_answer where registration_id = 1043;
-- Insert question answers for all fields (both with and without answer_location)
INSERT INTO hdb_registration_question_answer (
    registration_id,
    question_id,
    answer_value,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    q.id AS question_id,
    
    -- Map answer values based on binding_key
    CASE q.binding_key
        WHEN 'name' THEN to_jsonb(COALESCE(TRIM(zz.name), ''))
        WHEN 'email' THEN to_jsonb(COALESCE(TRIM(LOWER(zz.email)), ''))
        WHEN 'mobileNumber' THEN to_jsonb(COALESCE(TRIM(zz.mobile), ''))
        WHEN 'gender' THEN to_jsonb(
            CASE 
                WHEN LOWER(TRIM(zz.gender)) IN ('male', 'm') THEN 'Male'
                WHEN LOWER(TRIM(zz.gender)) IN ('female', 'f') THEN 'Female'
                WHEN LOWER(TRIM(zz.gender)) IN ('other', 'o') THEN 'Other'
                ELSE 'Male'
            END
        )
        WHEN 'dob' THEN 
            to_jsonb(
                CASE 
                    WHEN zz.date_of_birth IS NOT NULL AND zz.date_of_birth != '' THEN 
                        CASE
                            -- Try YYYY-MM-DD format first
                            WHEN zz.date_of_birth ~ '^\d{4}-\d{2}-\d{2}$' THEN 
                                zz.date_of_birth
                            -- Try DD-MM-YYYY format and convert to YYYY-MM-DD
                            WHEN zz.date_of_birth ~ '^\d{2}-\d{2}-\d{4}$' THEN 
                                TO_CHAR(TO_DATE(zz.date_of_birth, 'DD-MM-YYYY'), 'YYYY-MM-DD')
                            -- Try DD-MMM-YYYY format (e.g., 15-Dec-1990) and convert to YYYY-MM-DD
                            WHEN zz.date_of_birth ~ '^\d{1,2}-[A-Za-z]{3}-\d{4}$' THEN 
                                TO_CHAR(TO_DATE(zz.date_of_birth, 'DD-Mon-YYYY'), 'YYYY-MM-DD')
                            -- Return original if can't parse
                            ELSE COALESCE(zz.date_of_birth, '')
                        END
                    ELSE ''  -- Store empty string for empty dates
                END
            )
        WHEN 'city' THEN 
            to_jsonb(
                CASE 
                    WHEN TRIM(zz.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.city)
                    ELSE 'Other'
                END
            )
        WHEN 'otherCityName' THEN 
            to_jsonb(
                CASE 
                    WHEN TRIM(zz.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.city)
                    ELSE ''
                END
            )
        WHEN 'countryName' THEN to_jsonb(COALESCE(TRIM(zz.country), ''))
        WHEN 'rmContact' THEN 
            to_jsonb(
                CASE 
                    WHEN UPPER(TRIM(zz.contact_person)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION')
                        OR (CASE UPPER(TRIM(zz.contact_person))
                            WHEN 'CHARUTA' THEN '9011240808'
                            WHEN 'CHITHAMBARAN' THEN '9790768202'
                            WHEN 'SRIDHAR' THEN '9841727022'
                            WHEN 'MUKUNDAN' THEN '9884874161'
                            WHEN 'VINOD' THEN '8094444402'
                            WHEN 'ARUN' THEN '9845191961'                            
                            WHEN 'RAMEL' THEN '9841660000'
                            WHEN 'GOPINATH' THEN '9841660000'
                            WHEN 'ATHIAMAN' THEN '9841660000'
                            WHEN 'OTHER' THEN '9841660000'
                            WHEN 'GOPAL' THEN '9841021495'
                            WHEN 'BHUPENDHAR' THEN '9841149060'
                            WHEN 'SAKTHI' THEN '9841660000'
                            WHEN 'JAYA' THEN '9841660000'
                            WHEN 'MANOHAR' THEN '9841042289'
                            WHEN 'KISHAN' THEN '9966279944'
                            WHEN 'SWAROOP' THEN '9657152211'
                            WHEN 'VIJISUBBU' THEN '8939910100'
                            WHEN 'ORGANIZATION' THEN '9841660000'
                           WHEN 'SUBBU' THEN '9841405117'
                            WHEN 'MAHESH' THEN '9791140214'
                            WHEN 'MOHINI' THEN '9880702700'
                            ELSE NULL
                        END) = '9841660000' 
                    THEN ''  -- Set to empty for generic contact
                    ELSE COALESCE(TRIM(zz.contact_person), '')
                END
            )
        WHEN 'otherInfinitheismContact' THEN 
            to_jsonb(
                CASE 
                    WHEN UPPER(TRIM(zz.contact_person)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION')
                        OR (CASE UPPER(TRIM(zz.contact_person))
                            WHEN 'CHARUTA' THEN '9011240808'
                            WHEN 'CHITHAMBARAN' THEN '9790768202'
                            WHEN 'SRIDHAR' THEN '9841727022'
                            WHEN 'MUKUNDAN' THEN '9884874161'
                            WHEN 'VINOD' THEN '8094444402'
                            WHEN 'ARUN' THEN '9845191961'                            
                            WHEN 'RAMEL' THEN '9841660000'
                            WHEN 'GOPINATH' THEN '9841660000'
                            WHEN 'ATHIAMAN' THEN '9841660000'
                            WHEN 'OTHER' THEN '9841660000'
                            WHEN 'GOPAL' THEN '9841021495'
                            WHEN 'BHUPENDHAR' THEN '9841149060'
                            WHEN 'SAKTHI' THEN '9841660000'
                            WHEN 'JAYA' THEN '9841660000'
                            WHEN 'MANOHAR' THEN '9841042289'
                            WHEN 'KISHAN' THEN '9966279944'
                            WHEN 'SWAROOP' THEN '9657152211'
                            WHEN 'VIJISUBBU' THEN '8939910100'
                            WHEN 'ORGANIZATION' THEN '9841660000'
                           WHEN 'SUBBU' THEN '9841405117'
                            WHEN 'MAHESH' THEN '9791140214'
                            WHEN 'MOHINI' THEN '9880702700'
                            ELSE NULL
                        END) = '9841660000' 
                    THEN CASE 
                        WHEN UPPER(TRIM(zz.contact_person)) = 'OTHER' THEN 'OtherInfinitheismContact'
                        ELSE TRIM(zz.contact_person)
                    END
                    ELSE ''
                END
            )
        WHEN 'QK_LAST_HDB' THEN to_jsonb(COALESCE(TRIM(zz.when_was_your_last_hdb_msd), ''))
        WHEN 'QK_HDB_ASSOCIATION_SINCE' THEN to_jsonb(COALESCE(TRIM(zz.since_when_have_you_been_associated_with_this_path), ''))
        WHEN 'no_of_hdbs' THEN 
            to_jsonb(
                CASE 
                    WHEN zz.how_many_hdb_msd_have_you_done ~ '^[0-9]+$' THEN 
                        zz.how_many_hdb_msd_have_you_done::INTEGER
                    ELSE 0
                END
            )
        WHEN 'whichOfTheFollowingHaveYouExperienced' THEN to_jsonb(COALESCE(TRIM(zz.which_of_the_following_have_you_experienced), ''))
        WHEN 'QK_HDB_SONG_1' THEN to_jsonb(COALESCE(TRIM(zz.song_preference_1), ''))
        WHEN 'QK_HDB_SONG_2' THEN to_jsonb(COALESCE(TRIM(zz.song_preference_2), ''))
        WHEN 'invoiceEmail' THEN to_jsonb(COALESCE(TRIM(LOWER(zz.email_to_which_invoice_to_be_sent)), ''))
        WHEN 'invoiceName' THEN to_jsonb(COALESCE(TRIM(zz.name_in_which_invoice_to_be_issued), ''))
        WHEN 'invoiceAddress' THEN to_jsonb(COALESCE(TRIM(zz.invoice_address), ''))
        WHEN 'tanNumber' THEN to_jsonb(COALESCE(TRIM(zz.tan), ''))
        WHEN 'gstNumber' THEN to_jsonb(COALESCE(TRIM(zz.gstin), ''))
        WHEN 'isGstRegistered' THEN 
            to_jsonb(
                CASE 
                    WHEN LOWER(TRIM(COALESCE(zz.are_you_registered_under_gst, ''))) IN ('yes', 'y', 'true', '1') THEN true
                    ELSE false
                END
            )
        WHEN 'pictureUrl' THEN to_jsonb(COALESCE(TRIM(zz.id_front_back_only_from_download_link), ''))
        WHEN 'idBack' THEN to_jsonb(COALESCE(TRIM(zz.id_back_only_download_link), ''))
        ELSE to_jsonb(NULL::text)
    END AS answer_value,
    
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM zz_ref_20250714_hdb_info_asis_trimmed zz
JOIN hdb_program_registration reg ON (
    TRIM(reg.mobile_number) = TRIM(zz.mobile)
    AND TRIM(LOWER(reg.email_address)) = TRIM(LOWER(zz.email))
	AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2024-25')
)
JOIN hdb_question q ON q.created_by = -2 
  AND q.binding_key IN (
    'name',
    'email', 
    'mobileNumber',
    'gender',
    'dob',
    'city',
    'otherCityName',
    'countryName',
    'rmContact',
    'otherInfinitheismContact',
    'QK_LAST_HDB',
    'QK_HDB_ASSOCIATION_SINCE',
    'no_of_hdbs',
    'whichOfTheFollowingHaveYouExperienced',
    'QK_HDB_SONG_1',
    'QK_HDB_SONG_2',
    'invoiceEmail',
    'invoiceName',
    'invoiceAddress', 
    'tanNumber',
    'gstNumber',
    'isGstRegistered',
    'pictureUrl',
    'idBack'
);

UPDATE hdb_registration_question_answer 
SET audit_ref_id = id, 
    parent_ref_id = registration_id
WHERE audit_ref_id IS NULL;  -- Only update records created by this migration

-- ================================================================================================
-- 3. INSERT INTO hdb_registration_approval
-- ================================================================================================
select * from hdb_registration_approval;
INSERT INTO hdb_registration_approval (
    registration_id,
    approval_status,
    approval_date,
    approved_by,
    auto_approved,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    
    -- Approval status based on original status
    CASE 
        WHEN LOWER(TRIM(zz.status)) = 'confirmed' THEN 'approved'::approval_status_enum
        WHEN LOWER(TRIM(zz.status)) = 'hold' THEN 'rejected'::approval_status_enum
        WHEN LOWER(TRIM(zz.status)) = 'registered' THEN 'pending'::approval_status_enum
        ELSE 'pending'::approval_status_enum
    END AS approval_status,
    
    -- Approval date (set to current time for approved/rejected statuses)
    CASE 
        WHEN LOWER(TRIM(zz.status)) IN ('confirmed') THEN NOW()
        ELSE NULL
    END AS approval_date,
    
    -- Approved by (system user for migration)
    CASE 
        WHEN LOWER(TRIM(zz.status)) IN ('confirmed', 'hold') THEN -2
        ELSE NULL
    END AS approved_by,
    
    false AS auto_approved, -- Manual migration
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM zz_ref_20250714_hdb_info_asis_trimmed zz
JOIN hdb_program_registration reg ON (
    TRIM(reg.mobile_number) = TRIM(zz.mobile)
    AND TRIM(LOWER(reg.email_address)) = TRIM(LOWER(zz.email))
	AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2024-25')
);

-- ================================================================================================
-- 3.1. UPDATE hdb_registration_approval audit_ref_id to match record ID
-- ================================================================================================

UPDATE hdb_registration_approval 
SET audit_ref_id = id, 
    parent_ref_id = registration_id
WHERE audit_ref_id IS NULL;  -- Only update records created by this migration

------approval track--------
INSERT INTO hdb_registration_approval_track (
    approval_id,
    registration_id,
    approval_status,
    approval_date,
    approved_by,
    rejection_reason,
    reviewer_comments,
    auto_approved,
    type,
    swap_request_id,
    allocated_program_id,
    allocated_session_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    app.id AS approval_id,
    app.registration_id,
    app.approval_status,
    app.approval_date,
    app.approved_by,
    app.rejection_reason,
    app.reviewer_comments,
    app.auto_approved,
    'registration' AS type,  -- Default type for registration approvals
    NULL AS swap_request_id, -- No swap request for initial migration
    reg.allocated_program_id,
    reg.allocated_session_id AS allocated_session_id,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM hdb_registration_approval app
JOIN hdb_program_registration reg ON reg.id = app.registration_id;


------------------------------------- travel ------------------------------
-- ================================================================================================
-- 5. INSERT INTO hdb_registration_travel_info
-- ================================================================================================
select * from hdb_registration_travel_info;
INSERT INTO hdb_registration_travel_info (
    registration_id,
    id_picture_url,
    id_back_picture_url,
    travel_info_status,
    audit_ref_id,
    parent_ref_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    TRIM(zz.id_front_back_only_from_download_link) AS id_picture_url,  -- Map from pictureUrl
    TRIM(zz.id_back_only_download_link) AS id_back_picture_url,        -- Map from idBack
    'completed'::travel_status_enum AS travel_info_status,
    null AS audit_ref_id,  -- Set to ID for audit trail
    reg.id AS parent_ref_id, -- Set to registration ID for reference
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM hdb_program_registration reg
JOIN program_v1 p ON p.id = reg.program_id
JOIN zz_ref_20250714_hdb_info_asis_trimmed zz ON (
    TRIM(reg.mobile_number) = TRIM(zz.mobile)
    AND TRIM(LOWER(reg.email_address)) = TRIM(LOWER(zz.email))
)
WHERE p.name = 'HDB/MSD 2024-25'  -- Only for 2024-25 program
  AND reg.registration_status = 'completed';  -- Only for completed registrations

-- ================================================================================================
-- 5.1. UPDATE hdb_registration_travel_info audit_ref_id to match record ID
-- ================================================================================================

UPDATE hdb_registration_travel_info 
SET audit_ref_id = id 
WHERE audit_ref_id IS NULL;  -- Only update records created by this migration

-- ================================================================================================
-- 6. INSERT INTO hdb_registration_travel_plan
-- ================================================================================================
select * from hdb_registration_travel_plan;
INSERT INTO hdb_registration_travel_plan (
    registration_id,
    travel_plan_status,
    audit_ref_id,
    parent_ref_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    'completed'::travel_status_enum AS travel_plan_status,
    null AS audit_ref_id,  -- Set to ID for audit trail
    reg.id AS parent_ref_id, -- Set to registration ID for reference
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM hdb_program_registration reg
JOIN program_v1 p ON p.id = reg.program_id
WHERE p.name = 'HDB/MSD 2024-25'  -- Only for 2024-25 program
  AND reg.registration_status = 'completed';  -- Only for completed registrations

-- ================================================================================================
-- 6.1. UPDATE hdb_registration_travel_plan audit_ref_id to match record ID
-- ================================================================================================

UPDATE hdb_registration_travel_plan 
SET audit_ref_id = id 
WHERE audit_ref_id IS NULL;  -- Only update records created by this migration

-- ================================================================================================
-- 7. UPDATE hdb_program_registration with RM Review and Rating
-- ================================================================================================

UPDATE hdb_program_registration 
SET 
    rm_review = TRIM(zz.rm_review),
    average_rating = CASE 
        WHEN zz.rm_rating ~ '^[0-9]+(\.[0-9]+)?$' THEN 
            zz.rm_rating::NUMERIC
        ELSE NULL
    END
FROM zz_ref_20250714_hdb_info_asis_trimmed zz
WHERE TRIM(hdb_program_registration.mobile_number) = TRIM(zz.mobile)
  AND TRIM(LOWER(hdb_program_registration.email_address)) = TRIM(LOWER(zz.email))
  AND hdb_program_registration.program_id = (SELECT id FROM program_v1 WHERE name = TRIM('HDB/MSD 2024-25'));

-----------------------------------------------------------------------------------------------------------------------------------------
-- ================================================================================================
-- 8. INSERT INTO hdb_registration_payment_detail
-- ================================================================================================
INSERT INTO hdb_registration_payment_detail (
    registration_id,
    program_session_id,
    payment_mode,
    tax_amount,
    tds,
    sub_total,
    razorpay_id,
    offline_meta,
    original_amount,
    gst_amount,
    payment_status,
    mark_as_received_date,
    payment_date,
    audit_ref_id,
    parent_ref_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    NULL AS program_session_id,
    NULL AS payment_mode,
    NULL AS tax_amount,
    NULL AS tds,
    NULL AS sub_total,
    NULL AS razorpay_id,
    -- offline_meta with only these 6 binding key-value pairs
    jsonb_build_object(
	 	'isGstRegistered', CASE 
            WHEN LOWER(TRIM(COALESCE(zz.are_you_registered_under_gst, ''))) IN ('yes', 'y', 'true', '1') THEN true
            ELSE false
        END,
        'gstNumber', COALESCE(TRIM(zz.gstin), ''),
        'tanNumber', COALESCE(TRIM(zz.tan), ''),
        'invoiceEmail', COALESCE(TRIM(LOWER(zz.email_to_which_invoice_to_be_sent)), ''),
        'invoiceName', COALESCE(TRIM(zz.name_in_which_invoice_to_be_issued), ''),
        'invoiceAddress', COALESCE(TRIM(zz.invoice_address), '')
    ) AS offline_meta,
    NULL AS original_amount,
    NULL AS gst_amount,
    NULL AS payment_status,
    NULL AS mark_as_received_date,
    NULL AS payment_date,
    NULL AS audit_ref_id,
    NULL AS parent_ref_id,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM hdb_program_registration reg
JOIN zz_ref_20250714_hdb_info_asis_trimmed zz ON (
    TRIM(reg.mobile_number) = TRIM(zz.mobile)
    AND TRIM(LOWER(reg.email_address)) = TRIM(LOWER(zz.email))
    AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2024-25')
)
WHERE reg.registration_status = 'completed';  -- Only for completed registrations

-- ================================================================================================
-- 8.1. UPDATE hdb_registration_payment_detail audit_ref_id and parent_ref_id
-- ================================================================================================
UPDATE hdb_registration_payment_detail 
SET audit_ref_id = id, parent_ref_id = registration_id
WHERE audit_ref_id IS NULL 
  AND parent_ref_id IS NULL;  -- Only update records created by this migration


select * from hdb_registration_payment_detail;

-- ================================================================================================
-- 8.2. INSERT INTO hdb_registration_payment_details_history
-- ================================================================================================
INSERT INTO hdb_registration_payment_details_history (
    payment_details_id,
    payment_mode,
    razorpay_id,
    offline_meta,
    payment_status,
    portal_online_payment_status,
    portal_online_payment_attempt_status,
    webhook_online_payment_status,
    webhook_online_payment_attempt_status,
    gateway_online_payment_status,
    gateway_online_payment_attempt_status,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    pd.id AS payment_details_id,
    pd.payment_mode,
    pd.razorpay_id,
    pd.offline_meta,
    pd.payment_status,
    NULL AS portal_online_payment_status,
    NULL AS portal_online_payment_attempt_status,
    NULL AS webhook_online_payment_status,
    NULL AS webhook_online_payment_attempt_status,
    NULL AS gateway_online_payment_status,
    NULL AS gateway_online_payment_attempt_status,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM hdb_registration_payment_detail pd
JOIN hdb_program_registration reg ON reg.id = pd.registration_id
JOIN program_v1 p ON p.id = reg.program_id
WHERE p.name = 'HDB/MSD 2024-25'  -- Only for specific program
  AND reg.registration_status = 'completed';  -- Only for completed registrations

-- ================================================================================================
-- 9. INSERT INTO hdb_registration_invoice_detail
-- ================================================================================================
INSERT INTO hdb_registration_invoice_detail (
    registration_id,
    invoice_type,
    invoice_name,
    invoice_email,
    pan_number,
    is_gst_registered,
    invoice_address,
    tax_amount,
    invoice_sequence_number,
    razorpay_id,
    invoice_issued_date,
    invoice_status,
    tds_applicable,
    tan_number,
    handover_date,
    handover_to,
    audit_ref_id,
    parent_ref_id,
    created_at,
    updated_at,
    created_by,
    updated_by,
    gst_number,
    zip,
    tds_amount,
    einvoice_qr_link,
    einvoice_ack_date,
    einvoice_inv_ref_num,
    einvoice_is_cancellable,
    einvoice_status_formatted,
    einvoice_formatted_status,
    einvoice_ack_number,
    einvoice_status,
    invoice_pdf_url
)
SELECT 
    reg.id AS registration_id,
    NULL AS invoice_type,
    zz.name_in_which_invoice_to_be_issued AS invoice_name,
    zz.email_to_which_invoice_to_be_sent AS invoice_email,
    NULL AS pan_number,
    CASE 
        WHEN LOWER(TRIM(COALESCE(zz.are_you_registered_under_gst, ''))) IN ('yes', 'y', 'true', '1') THEN true
        ELSE false
    END AS is_gst_registered,
    zz.invoice_address AS invoice_address,
    NULL AS tax_amount,
    NULL AS invoice_sequence_number,
    NULL AS razorpay_id,
    NULL AS invoice_issued_date,
    'invoice_completed'::invoice_status_enum AS invoice_status,
    NULL AS tds_applicable,
    zz.tan AS tan_number,
    NULL AS handover_date,
    NULL AS handover_to,
    NULL AS audit_ref_id,
    NULL AS parent_ref_id,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by,
    zz.gstin AS gst_number,
    NULL AS zip,
    NULL AS tds_amount,
    NULL AS einvoice_qr_link,
    NULL AS einvoice_ack_date,
    NULL AS einvoice_inv_ref_num,
    NULL AS einvoice_is_cancellable,
    NULL AS einvoice_status_formatted,
    NULL AS einvoice_formatted_status,
    NULL AS einvoice_ack_number,
    NULL AS einvoice_status,
    NULL AS invoice_pdf_url
    
FROM hdb_program_registration reg
JOIN zz_ref_20250714_hdb_info_asis_trimmed zz ON (
    TRIM(reg.mobile_number) = TRIM(zz.mobile)
    AND TRIM(LOWER(reg.email_address)) = TRIM(LOWER(zz.email))
    AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2024-25')
)
WHERE reg.registration_status = 'completed'  -- Only for completed registrations
  AND reg.proforma_invoice_seq_number IS NOT NULL;  -- Only if invoice sequence is available

select * from hdb_registration_invoice_detail;
-- ================================================================================================
-- 9.1. UPDATE hdb_registration_invoice_detail audit_ref_id and parent_ref_id
-- ================================================================================================
UPDATE hdb_registration_invoice_detail 
SET audit_ref_id = id, parent_ref_id = registration_id
WHERE audit_ref_id IS NULL 
  AND parent_ref_id IS NULL;  -- Only update records created by this migration

------------------------------------------------------------------------------------------------------------------------------------
select * from program_v1 where name in ('HDB/MSD 2024-25','HDB 1 - 28-Nov-2024 to 06-Dec-2024','HDB 2 - 19-Dec-2024 to 27-Dec-2024',
'HDB 3 - 9-Jan-2025 to 17-Jan-2025',
'MSD - 27-Jan-2025 to 01-Feb-2025',
'MSD 2 - 01-Feb-2025 to 06-Feb-2025');

update program_v1
set status = 'completed'
where  name in ('HDB/MSD 2024-25','HDB 1 - 28-Nov-2024 to 06-Dec-2024','HDB 2 - 19-Dec-2024 to 27-Dec-2024',
'HDB 3 - 9-Jan-2025 to 17-Jan-2025',
'MSD - 27-Jan-2025 to 01-Feb-2025',
'MSD 2 - 01-Feb-2025 to 06-Feb-2025');

-- ================================================================================================
-- 10. VERIFICATION QUERIES
-- ================================================================================================

-- Verify the migration results
SELECT 'Registration Count' as metric, COUNT(*) as count
FROM hdb_program_registration
UNION ALL
SELECT 'Question Answer Count' as metric, COUNT(*) as count
FROM hdb_registration_question_answer
UNION ALL
SELECT 'Approval Count' as metric, COUNT(*) as count
FROM hdb_registration_approval
UNION ALL
SELECT 'Approval Track Count' as metric, COUNT(*) as count
FROM hdb_registration_approval_track
UNION ALL
SELECT 'Travel Info Count' as metric, COUNT(*) as count
FROM hdb_registration_travel_info
UNION ALL
SELECT 'Travel Plan Count' as metric, COUNT(*) as count
FROM hdb_registration_travel_plan;



select * from zz_ref_20250714_hdb_info_asis_trimmed; --1232, --1231
-- Check registration status distribution
SELECT 
    registration_status,
    COUNT(*) as count
FROM hdb_program_registration
GROUP BY registration_status
ORDER BY registration_status;

-- Check approval status distribution
SELECT 
    approval_status,
    COUNT(*) as count
FROM hdb_registration_approval
GROUP BY approval_status
ORDER BY approval_status;

-- Check approval track status distribution
SELECT 
    approval_status,
    type,
    COUNT(*) as count
FROM hdb_registration_approval_track
GROUP BY approval_status, type
ORDER BY type, approval_status;

-- Check travel status distribution
SELECT 
    'Travel Info' as table_name,
    travel_info_status as status,
    COUNT(*) as count
FROM hdb_registration_travel_info
GROUP BY travel_info_status
UNION ALL
SELECT 
    'Travel Plan' as table_name,
    travel_plan_status as status,
    COUNT(*) as count
FROM hdb_registration_travel_plan
GROUP BY travel_plan_status
ORDER BY table_name, status;

-- Check program session distribution
SELECT 
    p.name as program_name,
    ps.name as session_name,
    ps.code as session_code,
    COUNT(reg.id) as registration_count
FROM program_v1 p
LEFT JOIN program_session ps ON ps.program_id = p.id
LEFT JOIN hdb_program_registration reg ON reg.program_session_id = ps.id
WHERE p.program LIKE '2024 - 2025%'
GROUP BY p.id, p.name, ps.id, ps.name, ps.code
ORDER BY p.name, ps.display_order;

-- Sample of migrated data
SELECT 
    reg.id,
    reg.full_name,
    reg.email_address,
    reg.registration_status,
    p.name as program_name,
    ps.name as session_name,
    app.approval_status,
    COUNT(DISTINCT track.id) as approval_track_count,
    COUNT(DISTINCT ti.id) as travel_info_count,
    COUNT(DISTINCT tp.id) as travel_plan_count,
    ti.travel_info_status,
    tp.travel_plan_status
FROM hdb_program_registration reg
LEFT JOIN program_session ps ON ps.id = reg.program_session_id
LEFT JOIN program_v1 p ON p.id = reg.program_id
LEFT JOIN hdb_registration_approval app ON app.registration_id = reg.id
LEFT JOIN hdb_registration_approval_track track ON track.approval_id = app.id
LEFT JOIN hdb_registration_travel_info ti ON ti.registration_id = reg.id AND ti.created_by = -2
LEFT JOIN hdb_registration_travel_plan tp ON tp.registration_id = reg.id AND tp.created_by = -2
GROUP BY reg.id, reg.full_name, reg.email_address, reg.registration_status, 
         p.name, ps.name, app.approval_status, ti.travel_info_status, tp.travel_plan_status
ORDER BY reg.id
LIMIT 10;




-- UPDATE public.zz_ref_20250714_hdb_info_asis_trimmed
-- SET id_front_back_only_from_download_link = REPLACE(id_front_back_only_from_download_link, 'creatorapp.zohocom', 'creatorapp.zoho.public.com'),
--     id_back_only_download_link = REPLACE(id_back_only_download_link, 'creatorapp.zohocom', 'creatorapp.zoho.public.com')
-- WHERE id_front_back_only_from_download_link LIKE '%creatorapp.zohocom%'
--    OR id_back_only_download_link LIKE '%creatorapp.zohocom%';


-- select * from public.zz_ref_20250717_hdb_2021_2022_info_asis_trimmed;
-- select id_front_back_only_from_download_link,id_back_only_download_link from public.zz_ref_20250714_hdb_info_asis_trimmed where id_front_back_only_from_download_link!= '' or id_front_back_only_from_download_link is null or id_back_only_download_link!= '' or id_back_only_download_link is null;
-- UPDATE public.zz_ref_20250714_hdb_info_asis_trimmed
-- SET id_front_back_only_from_download_link = REPLACE(id_front_back_only_from_download_link, 'creatorapp.zohocom', 'creatorapp.zohopublic.com'),
--     id_back_only_download_link = REPLACE(id_back_only_download_link, 'creatorapp.zohocom', 'creatorapp.zohopublic.com')
-- WHERE id_front_back_only_from_download_link LIKE '%creatorapp.zohocom%'
--    OR id_back_only_download_link LIKE '%creatorapp.zohocom%';
   
-- select id_1_download_link,id_2_download_link from public.zz_ref_20250717_hdb_2022_2023_info_asis_trimmed;
-- UPDATE public.zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
-- SET id_1_download_link = REPLACE(id_1_download_link, 'creatorapp.zohocom', 'creatorapp.zohopublic.com'),
--     id_2_download_link = REPLACE(id_2_download_link, 'creatorapp.zohocom', 'creatorapp.zohopublic.com')
-- WHERE id_1_download_link LIKE '%creatorapp.zohocom%'
--    OR id_2_download_link LIKE '%creatorapp.zohocom%';

-- select id_1_download_link,id_2_download_link from  public.zz_ref_20250717_hdb_2023_2024_info_asis_trimmed;
-- UPDATE public.zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
-- SET id_1_download_link = REPLACE(id_1_download_link, 'creatorapp.zohocom', 'creatorapp.zohopublic.com'),
--     id_2_download_link = REPLACE(id_2_download_link, 'creatorapp.zohocom', 'creatorapp.zohopublic.com')
-- WHERE id_1_download_link LIKE '%creatorapp.zohocom%'
--    OR id_2_download_link LIKE '%creatorapp.zohocom%';



-- select * from public.zz_ref_20250717_hdb_2023_2024_info_asis_trimmed where email in ('natra66@gmail.com',
-- 'asiddharth.singhvi@gmail.com',
-- 'jaijaifibrenpith@gmail.com',
-- 'bjayakanth@gmail.com',
-- 'priyaathia@gmail.com');


-- UPDATE public.zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
-- SET contact_person = CASE email
--     WHEN 'natra66@gmail.com'            THEN 'Manohar'
--     WHEN 'asiddharth.singhvi@gmail.com' THEN 'Manohar'
--     WHEN 'jaijaifibrenpith@gmail.com'   THEN 'Subbu'
--     WHEN 'bjayakanth@gmail.com'         THEN 'Yet to assign'
--     WHEN 'priyaathia@gmail.com'         THEN 'Yet to assign'
-- END
-- WHERE email IN (
--     'natra66@gmail.com',
--     'asiddharth.singhvi@gmail.com',
--     'jaijaifibrenpith@gmail.com',
--     'bjayakanth@gmail.com',
--     'priyaathia@gmail.com'
-- );



-- select count(*),email from zz_ref_20250717_hdb_2022_2023_info_asis_trimmed where email IN (
--     'manish_1687@yahoo.com','vinodg@me.com','shivmith@gmail.com','jsampath@gmail.com','cavvignesh@gmail.com',
--     'keerthana.kumar@gmail.com','rizwanaopto@gmail.com','gothiabhisha@gmail.com','aravind.dm@gmail.com',
--     'aswathy.dilip@itdp.org','cartick.infi@gmail.com','debesh2014@gmail.com','sudhakmr189@gmail.com',
--     'kavita@alphabet.school','askask1991@gmail.com','sampath.kiran@gmail.com','smileynagaraj@gmail.com',
--     'nagarajan@stonemartcladding.com','naravisva22@gmail.com','pooranikumar@gmail.com','wevolvelabs@gmail.com',
--     'san_sbv@yahoo.co.in','shashichordia1111@gmail.com','asiddharth.singhvi@gmail.com','sridhar_r61@yahoo.com',
--     'sarafstuti2000@gmail.com','sukanya.1392@gmail.com','suranasuyash777@gmail.com','jvjsurana@gmail.com',
--     'gothivineet@gmail.com','vishweshwaransridhar@gmail.com','yashohi@gmail.com','hardtoolsyusuf@gmail.com',
--     'anitasurana1975@gmail.com','dilip@nkefloors.com','dseshaasai@gmail.com','jeevikathiru@gmail.com',
--     'aadhya127@gmail.com','sanjaybalaji@yahoo.com','vidukp@yahoo.com','abirami@spinoslifescience.com',
--     'adyasaran8126@gmail.com','ashateet@gmail.com','saran.ashutosh@gmail.com','menonpcl@gmail.com',
--     'devipandian@innovativeinteriors.in','dhanushreepandian07@gmail.com','gayathri.viswanathan@gmail.com',
--     'gauthamans@yahoo.com','kavitha@tenet.res.in','vkrishnacumar@gmail.com','vestabuildpr@yahoo.co.in',
--     'meet_lali@yahoo.com','ichoudhury.nandini@hotmail.com','pandian@innovativeinteriors.in','prasadmen@gmail.com',
--     'rasmitasaran@gmail.com','shanvel@ncssoft.in','kheerthi.agrathaa@gmail.com','netsrihari@yahoo.com',
--     'sudhakarperumal72@gmail.com','venkatbabuks@gmail.com','tas.banu@gmail.com','utsav.unibreakers@gmail.com',
--     'arun@flosil.com','gogv09@gmail.com','vishva.venki71@gmail.com','shekar@anovacorporate.co.in',
--     'chavijain282@gmail.com','darshan@deepakagencies.co.in','utharikha@hotmail.com','kashyap@agrid.in',
--     'malaezhil@gmail.com','kumaralatha@rediffmail.com','mithaliagarwal28@gmail.com','cjnara@gmail.com',
--     'nats@voicesnap.net','priyaathia@gmail.com','brgpal@gmail.com','rrajesh_91@rediff.com','raji29july@gmail.com',
--     'notifyrashmi@gmail.com','sachmittal@gmail.com','sjalan22@gmail.com','vsk1992@gmail.com',
--     'vijay@gvrgroups.com','vikasholdingtheworld@gmail.com'
-- )
-- group by email
-- having count(*)>1;


-- UPDATE public.zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
-- SET contact_person = CASE email
--     WHEN 'manish_1687@yahoo.com'         THEN 'Bhupendhar'
--     WHEN 'vinodg@me.com'                 THEN 'Gopal'
--     WHEN 'shivmith@gmail.com'            THEN 'Gopal'
--     WHEN 'jsampath@gmail.com'            THEN 'Mahesh'
--     WHEN 'cavvignesh@gmail.com'          THEN 'Mahesh'
--     WHEN 'keerthana.kumar@gmail.com'     THEN 'Manohar'
--     WHEN 'rizwanaopto@gmail.com'         THEN 'Manohar'
--     WHEN 'gothiabhisha@gmail.com'        THEN 'Manohar'
--     WHEN 'aravind.dm@gmail.com'          THEN 'Manohar'
--     WHEN 'aswathy.dilip@itdp.org'        THEN 'Manohar'
--     WHEN 'cartick.infi@gmail.com'        THEN 'Manohar'
--     WHEN 'debesh2014@gmail.com'          THEN 'Manohar'
--     WHEN 'sudhakmr189@gmail.com'         THEN 'Manohar'
--     WHEN 'kavita@alphabet.school'        THEN 'Manohar'
--     WHEN 'askask1991@gmail.com'          THEN 'Manohar'
--     WHEN 'sampath.kiran@gmail.com'       THEN 'Manohar'
--     WHEN 'smileynagaraj@gmail.com'       THEN 'Manohar'
--     WHEN 'nagarajan@stonemartcladding.com' THEN 'Manohar'
--     WHEN 'naravisva22@gmail.com'         THEN 'Manohar'
--     WHEN 'pooranikumar@gmail.com'        THEN 'Manohar'
--     WHEN 'wevolvelabs@gmail.com'         THEN 'Manohar'
--     WHEN 'san_sbv@yahoo.co.in'           THEN 'Manohar'
--     WHEN 'shashichordia1111@gmail.com'   THEN 'Manohar'
--     WHEN 'asiddharth.singhvi@gmail.com'  THEN 'Manohar'
--     WHEN 'sridhar_r61@yahoo.com'         THEN 'Manohar'
--     WHEN 'sarafstuti2000@gmail.com'      THEN 'Manohar'
--     WHEN 'sukanya.1392@gmail.com'        THEN 'Manohar'
--     WHEN 'suranasuyash777@gmail.com'     THEN 'Manohar'
--     WHEN 'jvjsurana@gmail.com'           THEN 'Manohar'
--     WHEN 'gothivineet@gmail.com'         THEN 'Manohar'
--     WHEN 'vishweshwaransridhar@gmail.com' THEN 'Manohar'
--     WHEN 'yashohi@gmail.com'             THEN 'Manohar'
--     WHEN 'hardtoolsyusuf@gmail.com'      THEN 'Manohar'
--     WHEN 'anitasurana1975@gmail.com'     THEN 'Manohar'
--     WHEN 'dilip@nkefloors.com'           THEN 'Manohar'
--     WHEN 'dseshaasai@gmail.com'          THEN 'Manohar'
--     WHEN 'jeevikathiru@gmail.com'        THEN 'Manohar'
--     WHEN 'aadhya127@gmail.com'           THEN 'Organization'
--     WHEN 'sanjaybalaji@yahoo.com'        THEN 'Subbu'
--     WHEN 'vidukp@yahoo.com'              THEN 'Subbu'
--     WHEN 'abirami@spinoslifescience.com' THEN 'Subbu'
--     WHEN 'adyasaran8126@gmail.com'       THEN 'Subbu'
--     WHEN 'ashateet@gmail.com'            THEN 'Subbu'
--     WHEN 'saran.ashutosh@gmail.com'      THEN 'Subbu'
--     WHEN 'menonpcl@gmail.com'            THEN 'Subbu'
--     WHEN 'devipandian@innovativeinteriors.in' THEN 'Subbu'
--     WHEN 'dhanushreepandian07@gmail.com' THEN 'Subbu'
--     WHEN 'gayathri.viswanathan@gmail.com' THEN 'Subbu'
--     WHEN 'gauthamans@yahoo.com'          THEN 'Subbu'
--     WHEN 'kavitha@tenet.res.in'          THEN 'Subbu'
--     WHEN 'vkrishnacumar@gmail.com'       THEN 'Subbu'
--     WHEN 'vestabuildpr@yahoo.co.in'      THEN 'Subbu'
--     WHEN 'meet_lali@yahoo.com'           THEN 'Subbu'
--     WHEN 'ichoudhury.nandini@hotmail.com' THEN 'Subbu'
--     WHEN 'pandian@innovativeinteriors.in' THEN 'Subbu'
--     WHEN 'prasadmen@gmail.com'           THEN 'Subbu'
--     WHEN 'rasmitasaran@gmail.com'        THEN 'Subbu'
--     WHEN 'shanvel@ncssoft.in'            THEN 'Subbu'
--     WHEN 'kheerthi.agrathaa@gmail.com'   THEN 'Subbu'
--     WHEN 'netsrihari@yahoo.com'          THEN 'Subbu'
--     WHEN 'sudhakarperumal72@gmail.com'   THEN 'Subbu'
--     WHEN 'venkatbabuks@gmail.com'        THEN 'Subbu'
--     WHEN 'tas.banu@gmail.com'            THEN 'Subbu'
--     WHEN 'utsav.unibreakers@gmail.com'   THEN 'Subbu'
--     WHEN 'arun@flosil.com'               THEN 'Subbu'
--     WHEN 'gogv09@gmail.com'              THEN 'Yet to assign'
--     WHEN 'vishva.venki71@gmail.com'      THEN 'Yet to assign'
--     WHEN 'shekar@anovacorporate.co.in'   THEN 'Yet to assign'
--     WHEN 'chavijain282@gmail.com'        THEN 'Yet to assign'
--     WHEN 'darshan@deepakagencies.co.in'  THEN 'Yet to assign'
--     WHEN 'utharikha@hotmail.com'         THEN 'Yet to assign'
--     WHEN 'kashyap@agrid.in'              THEN 'Yet to assign'
--     WHEN 'malaezhil@gmail.com'           THEN 'Yet to assign'
--     WHEN 'kumaralatha@rediffmail.com'    THEN 'Yet to assign'
--     WHEN 'mithaliagarwal28@gmail.com'    THEN 'Yet to assign'
--     WHEN 'cjnara@gmail.com'              THEN 'Yet to assign'
--     WHEN 'nats@voicesnap.net'            THEN 'Yet to assign'
--     WHEN 'priyaathia@gmail.com'          THEN 'Yet to assign'
--     WHEN 'brgpal@gmail.com'              THEN 'Yet to assign'
--     WHEN 'rrajesh_91@rediff.com'         THEN 'Yet to assign'
--     WHEN 'raji29july@gmail.com'          THEN 'Yet to assign'
--     WHEN 'notifyrashmi@gmail.com'        THEN 'Yet to assign'
--     WHEN 'sachmittal@gmail.com'          THEN 'Yet to assign'
--     WHEN 'sjalan22@gmail.com'            THEN 'Yet to assign'
--     WHEN 'vsk1992@gmail.com'             THEN 'Yet to assign'
--     WHEN 'vijay@gvrgroups.com'           THEN 'Yet to assign'
--     WHEN 'vikasholdingtheworld@gmail.com' THEN 'Yet to assign'
-- END
-- WHERE email IN (
--     'manish_1687@yahoo.com','vinodg@me.com','shivmith@gmail.com','jsampath@gmail.com','cavvignesh@gmail.com',
--     'keerthana.kumar@gmail.com','rizwanaopto@gmail.com','gothiabhisha@gmail.com','aravind.dm@gmail.com',
--     'aswathy.dilip@itdp.org','cartick.infi@gmail.com','debesh2014@gmail.com','sudhakmr189@gmail.com',
--     'kavita@alphabet.school','askask1991@gmail.com','sampath.kiran@gmail.com','smileynagaraj@gmail.com',
--     'nagarajan@stonemartcladding.com','naravisva22@gmail.com','pooranikumar@gmail.com','wevolvelabs@gmail.com',
--     'san_sbv@yahoo.co.in','shashichordia1111@gmail.com','asiddharth.singhvi@gmail.com','sridhar_r61@yahoo.com',
--     'sarafstuti2000@gmail.com','sukanya.1392@gmail.com','suranasuyash777@gmail.com','jvjsurana@gmail.com',
--     'gothivineet@gmail.com','vishweshwaransridhar@gmail.com','yashohi@gmail.com','hardtoolsyusuf@gmail.com',
--     'anitasurana1975@gmail.com','dilip@nkefloors.com','dseshaasai@gmail.com','jeevikathiru@gmail.com',
--     'aadhya127@gmail.com','sanjaybalaji@yahoo.com','vidukp@yahoo.com','abirami@spinoslifescience.com',
--     'adyasaran8126@gmail.com','ashateet@gmail.com','saran.ashutosh@gmail.com','menonpcl@gmail.com',
--     'devipandian@innovativeinteriors.in','dhanushreepandian07@gmail.com','gayathri.viswanathan@gmail.com',
--     'gauthamans@yahoo.com','kavitha@tenet.res.in','vkrishnacumar@gmail.com','vestabuildpr@yahoo.co.in',
--     'meet_lali@yahoo.com','ichoudhury.nandini@hotmail.com','pandian@innovativeinteriors.in','prasadmen@gmail.com',
--     'rasmitasaran@gmail.com','shanvel@ncssoft.in','kheerthi.agrathaa@gmail.com','netsrihari@yahoo.com',
--     'sudhakarperumal72@gmail.com','venkatbabuks@gmail.com','tas.banu@gmail.com','utsav.unibreakers@gmail.com',
--     'arun@flosil.com','gogv09@gmail.com','vishva.venki71@gmail.com','shekar@anovacorporate.co.in',
--     'chavijain282@gmail.com','darshan@deepakagencies.co.in','utharikha@hotmail.com','kashyap@agrid.in',
--     'malaezhil@gmail.com','kumaralatha@rediffmail.com','mithaliagarwal28@gmail.com','cjnara@gmail.com',
--     'nats@voicesnap.net','priyaathia@gmail.com','brgpal@gmail.com','rrajesh_91@rediff.com','raji29july@gmail.com',
--     'notifyrashmi@gmail.com','sachmittal@gmail.com','sjalan22@gmail.com','vsk1992@gmail.com',
--     'vijay@gvrgroups.com','vikasholdingtheworld@gmail.com'
-- );



-- SELECT 
--     LOWER(TRIM(email)) AS normalized_email,
--     COUNT(*) AS duplicate_count
-- FROM public.zz_ref_20250717_hdb_2021_2022_info_asis_trimmed
-- WHERE email IS NOT NULL
-- GROUP BY LOWER(TRIM(email))
-- HAVING COUNT(*) > 1
-- ORDER BY duplicate_count DESC;



-- UPDATE public.zz_ref_20250717_hdb_2021_2022_info_asis_trimmed
-- SET contact_person = CASE 
--     WHEN email = 'mailanirudhramesh@gmail.com' THEN 'Bhupendhar'
--     WHEN email = 'mr.krish.murali.k@gmail.com' THEN 'Bhupendhar'
--     WHEN email = 'manish_1687@yahoo.com' THEN 'Bhupendhar'
--     WHEN email = 'aravind.dm@gmail.com' THEN 'Manohar'
--     WHEN email = 'aswathy.dilip@itdp.org' THEN 'Manohar'
--     WHEN email = 'chitrasamichetty@gmail.com' THEN 'Manohar'
--     WHEN email = 'sampath.kiran@gmail.com' THEN 'Manohar'
--     WHEN email = 'nagarajan@stonemartcladding.com' THEN 'Manohar'
--     WHEN email = 'rishabsanklecha10@gmail.com' THEN 'Manohar'
--     WHEN email = 'san_sbv@yahoo.co.in' THEN 'Manohar'
--     WHEN email = 'shashichordia1111@gmail.com' THEN 'Manohar'
--     WHEN email = 'sridhar_r61@yahoo.com' THEN 'Manohar'
--     WHEN email = 'sunil@nutsnspices.in' THEN 'Manohar'
--     WHEN email = 'sanklecha.sunitha@gmail.com' THEN 'Manohar'
--     WHEN email = 'suranasuyash777@gmail.com' THEN 'Manohar'
--     WHEN email = 'vinay423@hotmail.com' THEN 'Manohar'
--     WHEN email = 'hardtoolsyusuf@gmail.com' THEN 'Manohar'
--     WHEN email = 'lokeshrajca@outlook.com' THEN 'Mukundan'
--     WHEN email = 'sai@successshipping.in' THEN 'Mukundan'
--     WHEN email = 'abirami@spinoslifescience.com' THEN 'Subbu'
--     WHEN email = 'sanjaybalaji@yahoo.com' THEN 'Subbu'
--     WHEN email = 'vidukp@yahoo.com' THEN 'Subbu'
--     WHEN email = 'lunked@yahoo.com' THEN 'Subbu'
--     WHEN email = 'infinideepak@gmail.com' THEN 'Subbu'
--     WHEN email = 'gauthamans@yahoo.com' THEN 'Subbu'
--     WHEN email = 'kavya.horse@gmail.com' THEN 'Subbu'
--     WHEN email = 'kavitha@tenet.res.in' THEN 'Subbu'
--     WHEN email = 'Pavan@happimobiles.com' THEN 'Subbu'
--     WHEN email = 'Kritilunked01@gmail.com' THEN 'Subbu'
--     WHEN email = 'vestabuildpr@yahoo.co.in' THEN 'Subbu'
--     WHEN email = 'ichoudhury.nandini@hotmail.com' THEN 'Subbu'
--     WHEN email = 'neetulunked79@gmail.com' THEN 'Subbu'
--     WHEN email = 'vahinid@yahoo.co.in' THEN 'Subbu'
--     WHEN email = 'yamunaramasekar@gmail.com' THEN 'Subbu'
--     WHEN email = 'startupp19@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'aditya@thepenworld.com' THEN 'Yet to assign'
--     WHEN email = 'anitasurana1975@gmail.com' THEN 'Manohar'
--     WHEN email = 'arun@flosil.com' THEN 'Subbu'
--     WHEN email = 'ashokkumar@indarelectricals.com' THEN 'Mahesh'
--     WHEN email = 'chavi.jain@flame.edu.in' THEN 'Yet to assign'
--     WHEN email = 'chithra311@yahoo.co.in' THEN 'Yet to assign'
--     WHEN email = 'guru@celekt.com' THEN 'Yet to assign'
--     WHEN email = 'jyothi_architect@yahoo.com' THEN 'Yet to assign'
--     WHEN email = 'krishnacpati@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'kumaralatha@rediffmail.com' THEN 'Yet to assign'
--     WHEN email = 'amsreenika@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'nandhusubasri@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'cjnara@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'priyaathia@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'raji29july@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'sachmittal@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'dseshaasai@gmail.com' THEN 'Manohar'
--     WHEN email = 'vikasholdingtheworld@gmail.com' THEN 'Yet to assign'
--     WHEN email = 'venkaji@yahoo.com' THEN 'Yet to assign'
--     WHEN email = 'vikashmutha05@gmail.com' THEN 'Yet to assign'
--     ELSE contact_person
-- END
-- WHERE email IN (
--     'mailanirudhramesh@gmail.com','mr.krish.murali.k@gmail.com','manish_1687@yahoo.com','aravind.dm@gmail.com',
--     'aswathy.dilip@itdp.org','chitrasamichetty@gmail.com','sampath.kiran@gmail.com','nagarajan@stonemartcladding.com',
--     'rishabsanklecha10@gmail.com','san_sbv@yahoo.co.in','shashichordia1111@gmail.com','sridhar_r61@yahoo.com',
--     'sunil@nutsnspices.in','sanklecha.sunitha@gmail.com','suranasuyash777@gmail.com','vinay423@hotmail.com',
--     'hardtoolsyusuf@gmail.com','lokeshrajca@outlook.com','sai@successshipping.in','abirami@spinoslifescience.com',
--     'sanjaybalaji@yahoo.com','vidukp@yahoo.com','lunked@yahoo.com','infinideepak@gmail.com','gauthamans@yahoo.com',
--     'kavya.horse@gmail.com','kavitha@tenet.res.in','Pavan@happimobiles.com','Kritilunked01@gmail.com',
--     'vestabuildpr@yahoo.co.in','ichoudhury.nandini@hotmail.com','neetulunked79@gmail.com','vahinid@yahoo.co.in',
--     'yamunaramasekar@gmail.com','startupp19@gmail.com','aditya@thepenworld.com','anitasurana1975@gmail.com',
--     'arun@flosil.com','ashokkumar@indarelectricals.com','chavi.jain@flame.edu.in','chithra311@yahoo.co.in',
--     'guru@celekt.com','jyothi_architect@yahoo.com','krishnacpati@gmail.com','kumaralatha@rediffmail.com',
--     'amsreenika@gmail.com','nandhusubasri@gmail.com','cjnara@gmail.com','priyaathia@gmail.com','raji29july@gmail.com',
--     'sachmittal@gmail.com','dseshaasai@gmail.com','vikasholdingtheworld@gmail.com','venkaji@yahoo.com',
--     'vikashmutha05@gmail.com'
-- );

-- SELECT *
-- FROM zz_ref_20250714_hdb_info_asis_trimmed
-- WHERE contact_person IN ('Ramel', 'Gopinath', 'Athiaman', 'Sakthi', 'Jaya');
-- SELECT *
-- FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
-- WHERE contact_person IN ('Ramel', 'Gopinath', 'Athiaman', 'Sakthi', 'Jaya');
-- SELECT *
-- FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
-- WHERE contact_person IN ('Ramel', 'Gopinath', 'Athiaman', 'Sakthi', 'Jaya');
-- SELECT *
-- FROM zz_ref_20250717_hdb_2021_2022_info_asis_trimmed
-- WHERE contact_person IN ('Ramel', 'Gopinath', 'Athiaman', 'Sakthi', 'Jaya');

-- SELECT *
-- FROM zz_ref_20250714_hdb_info_asis_trimmed where bare_mobile in ('9380090783');

-- select * from zz_ref_20250717_hdb_2023_2024_info_asis_trimmed where mobile ilike '%9380090783%';
-- select * from zz_ref_20250717_hdb_2022_2023_info_asis_trimmed where mobile ilike '%9380090783%';
-- select * from zz_ref_20250717_hdb_2021_2022_info_asis_trimmed where mobile ilike '%9380090783%';
-- select * from zz_ref_20250714_hdb_info_asis_trimmed where mobile ilike '%9380090783%';

-- SELECT *
-- FROM zz_ref_20250714_hdb_info_asis_trimmed where name ilike '%Rajesh Kumar Dayalan%';
-- select * from zz_ref_20250717_hdb_2023_2024_info_asis_trimmed where name ilike '%Rajesh Kumar Dayalan%';

-- select * from zz_ref_20250717_hdb_2021_2022_info_asis_trimmed where name ilike '%Rajesh Kumar Dayalan%';
