-- ================================================================================================
-- HDB DATA MIGRATION SCRIPT FOR 2022-2023
-- Migration from zz_ref_20250717_hdb_2022_2023_info_asis_trimmed to program registration system
-- ================================================================================================

-- ================================================================================================
-- 1. INSERT PROGRAMS AND SESSIONS FOR 2022-2023
-- ================================================================================================

-- Insert the main program and individual sessions for 2022-2023
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
-- Main program for 2022-2023
(nextval('program_v1_id_seq'), '2022 - 2023', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB/MSD 2022-23', '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, 'HDB2223', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png', '{"price":[{"HDB":2},{"MSD":1}]}', '2022-07-29 15:03:56.323064+05:30', '2022-07-29 15:03:56.323064+05:30', NULL, -2, -2, FALSE, FALSE, FALSE, '9f8d9a3b-1234-5678-9abc-def012345678', TRUE, NULL, 1, 9.00, 9.00, 18.00, 0.00, '2022-08-15 05:30:00+05:30', '2022-08-31 05:29:59+05:30', '2022-12-01 10:30:00+05:30', '2023-02-04 02:30:00+05:30', '2022-12-01 08:30:00+05:30', '2023-02-04 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2022-23', 'AACCI7694H', 'U74900TN2011PTC083263', NULL, 'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2022-23', 'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2023-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2022-12-01 22:30:00+05:30', '2023-02-04 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

-- HDB 1 - 01-Dec-2022 to 09-Dec-2022
(nextval('program_v1_id_seq'), '2022 - 2023', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB 1 - 01-Dec-2022 to 09-Dec-2022', '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', '{}', '2022-07-29 15:03:56.323064+05:30', '2022-07-29 15:03:56.323064+05:30', NULL, -2, -2, FALSE, FALSE, FALSE, '9f8d9a3b-1234-5678-9abc-def012345678', FALSE, NULL, 2, 9.00, 9.00, 18.00, 0.00, '2022-08-15 05:30:00+05:30', '2022-08-31 05:29:59+05:30', '2022-12-01 10:30:00+05:30', '2022-12-09 02:30:00+05:30', '2022-12-01 08:30:00+05:30', '2022-12-09 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2022-23', 'AACCI7694H', 'U74900TN2011PTC083263', NULL, 'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2022-23', 'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2023-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2022-12-01 22:30:00+05:30', '2022-12-09 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

-- HDB 2 - 22-Dec-2022 to 30-Dec-2022
(nextval('program_v1_id_seq'), '2022 - 2023', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB 2 - 22-Dec-2022 to 30-Dec-2022', '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', '{}', '2022-07-29 15:03:56.323064+05:30', '2022-07-29 15:03:56.323064+05:30', NULL, -2, -2, FALSE, FALSE, FALSE, '9f8d9a3b-1234-5678-9abc-def012345678', FALSE, NULL, 3, 9.00, 9.00, 18.00, 0.00, '2022-08-15 05:30:00+05:30', '2022-08-31 05:29:59+05:30', '2022-12-22 10:30:00+05:30', '2022-12-30 02:30:00+05:30', '2022-12-22 08:30:00+05:30', '2022-12-30 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2022-23', 'AACCI7694H', 'U74900TN2011PTC083263', NULL, 'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2022-23', 'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2023-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2022-12-22 22:30:00+05:30', '2022-12-30 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

-- HDB 3 - 12-Jan-2023 to 20-Jan-2023
(nextval('program_v1_id_seq'), '2022 - 2023', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'HDB 3 - 12-Jan-2023 to 20-Jan-2023', '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', '{}', '2022-07-29 15:03:56.323064+05:30', '2022-07-29 15:03:56.323064+05:30', NULL, -2, -2, FALSE, FALSE, FALSE, '9f8d9a3b-1234-5678-9abc-def012345678', FALSE, NULL, 4, 9.00, 9.00, 18.00, 0.00, '2022-08-15 05:30:00+05:30', '2022-08-31 05:29:59+05:30', '2023-01-12 10:30:00+05:30', '2023-01-20 02:30:00+05:30', '2023-01-12 08:30:00+05:30', '2023-01-20 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2022-23', 'AACCI7694H', 'U74900TN2011PTC083263', NULL, 'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2022-23', 'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2023-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2023-01-12 22:30:00+05:30', '2023-01-20 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

-- MSD - 30-Jan-2023 to 04-Feb-2023
(nextval('program_v1_id_seq'), '2022 - 2023', (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'), 'MSD - 30-Jan-2023 to 04-Feb-2023', '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, 'MSD', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/MSD.png', '{}', '2022-07-29 15:03:56.323064+05:30', '2022-07-29 15:03:56.323064+05:30', NULL, -2, -2, FALSE, FALSE, FALSE, '9f8d9a3b-1234-5678-9abc-def012345678', FALSE, NULL, 5, 9.00, 9.00, 18.00, 0.00, '2022-08-15 05:30:00+05:30', '2022-08-31 05:29:59+05:30', '2023-01-30 10:30:00+05:30', '2023-02-04 02:30:00+05:30', '2023-01-30 08:30:00+05:30', '2023-02-04 22:30:00+05:30', 10.00, '36AACCI7694H1Z5', 'base_only', 'HDB/MSD 2022-23', 'AACCI7694H', 'U74900TN2011PTC083263', NULL, 'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.', '+91 98416 70000', 'HDB/MSD 2022-23', 'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.', '2023-11-17 14:30:00+05:30', 'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg', '2023-01-30 22:30:00+05:30', '2023-02-04 10:30:00+05:30', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- Insert the main program session and individual sessions
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 2022-23 Session (parent program)
(
    (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2022-23'),
    'HDB/MSD 2022-23 Main Session',
    'Main HDB/MSD 2022-23 program session',
    '2022-08-15 05:30:00+05:30',
    '2022-08-31 05:29:59+05:30',
    '2022-12-01 10:30:00+05:30',
    '2023-02-04 02:30:00+05:30',
    '2022-12-01 08:30:00+05:30',
    '2023-02-04 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,
    'program',
    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 2022-23',
    '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 2022-23',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2023-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB2223',
    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',
    '2022-07-29 15:03:56.323064+05:30',
    '2022-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2022-12-01 22:30:00+05:30',
    '2023-02-04 10:30:00+05:30'
),

-- HDB 1 Session
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 1 - 01-Dec-2022 to 09-Dec-2022'),
    'HDB 1 - 01-Dec-2022 to 09-Dec-2022 Session',
    'HDB 1 program session for 2022-23',
    '2022-08-15 05:30:00+05:30',
    '2022-08-31 05:29:59+05:30',
    '2022-12-01 10:30:00+05:30',
    '2022-12-09 02:30:00+05:30',
    '2022-12-01 08:30:00+05:30',
    '2022-12-09 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 1 - 01-Dec-2022 to 09-Dec-2022',
    '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 1 - 01-Dec-2022 to 09-Dec-2022',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2023-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB2223',
    2,
    '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',
    '2022-07-29 15:03:56.323064+05:30',
    '2022-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2022-12-01 22:30:00+05:30',
    '2022-12-09 10:30:00+05:30'
),
-- HDB 2 Session
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 2 - 22-Dec-2022 to 30-Dec-2022'),
    'HDB 2 - 22-Dec-2022 to 30-Dec-2022 Session',
    'HDB 2 program session for 2022-23',
    '2022-08-15 05:30:00+05:30',
    '2022-08-31 05:29:59+05:30',
    '2022-12-22 10:30:00+05:30',
    '2022-12-30 02:30:00+05:30',
    '2022-12-22 08:30:00+05:30',
    '2022-12-30 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 2 - 22-Dec-2022 to 30-Dec-2022',
    '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 2 - 22-Dec-2022 to 30-Dec-2022',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2023-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB2223',
    3,
    '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',
    '2022-07-29 15:03:56.323064+05:30',
    '2022-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2022-12-22 22:30:00+05:30',
    '2022-12-30 10:30:00+05:30'
),
-- HDB 3 Session
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 3 - 12-Jan-2023 to 20-Jan-2023'),
    'HDB 3 - 12-Jan-2023 to 20-Jan-2023 Session',
    'HDB 3 program session for 2022-23',
    '2022-08-15 05:30:00+05:30',
    '2022-08-31 05:29:59+05:30',
    '2023-01-12 10:30:00+05:30',
    '2023-01-20 02:30:00+05:30',
    '2023-01-12 08:30:00+05:30',
    '2023-01-20 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 3 - 12-Jan-2023 to 20-Jan-2023',
    '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 3 - 12-Jan-2023 to 20-Jan-2023',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2023-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB2223',
    4,
    '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',
    '2022-07-29 15:03:56.323064+05:30',
    '2022-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2023-01-12 22:30:00+05:30',
    '2023-01-20 10:30:00+05:30'
),
-- MSD Session
(
    (SELECT id FROM program_v1 WHERE name = 'MSD - 30-Jan-2023 to 04-Feb-2023'),
    'MSD - 30-Jan-2023 to 04-Feb-2023 Session',
    'MSD program session for 2022-23',
    '2022-08-15 05:30:00+05:30',
    '2022-08-31 05:29:59+05:30',
    '2023-01-30 10:30:00+05:30',
    '2023-02-04 02:30:00+05:30',
    '2023-01-30 08:30:00+05:30',
    '2023-02-04 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',
    'MSD - 30-Jan-2023 to 04-Feb-2023',
    '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',
    'MSD - 30-Jan-2023 to 04-Feb-2023',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2023-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB2223',
    5,
    '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',
    '2022-07-29 15:03:56.323064+05:30',
    '2022-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2023-01-30 22:30:00+05:30',
    '2023-02-04 10:30:00+05:30'
);

-- ================================================================================================
-- 1.5. INSERT INTO hdb_program_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 2022-23'),                -- Only main program
    q.form_section_id,
    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;

-- ================================================================================================
-- 2. INSERT INTO hdb_program_registration
-- ================================================================================================


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 
    -- Program_session_id - NULL as we're not using sessions for allocation
    NULL as program_session_id,
    
    -- Program ID based on hdb_allocation (using LIKE for partial matches)
    (SELECT id FROM program_v1 WHERE name IN (
        'HDB/MSD 2022-23'
    )) as program_id,

    -- Allocated Program ID - same as program_id (using LIKE for partial matches)
    CASE 
        WHEN UPPER(TRIM(zz.hdb_allocation)) LIKE '%HDB 1%' OR UPPER(TRIM(zz.hdb_allocation)) LIKE '%HDB1%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'HDB 1 - 01-Dec-2022 to 09-Dec-2022')
        WHEN UPPER(TRIM(zz.hdb_allocation)) LIKE '%HDB 2%' OR UPPER(TRIM(zz.hdb_allocation)) LIKE '%HDB2%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'HDB 2 - 22-Dec-2022 to 30-Dec-2022')
        WHEN UPPER(TRIM(zz.hdb_allocation)) LIKE '%HDB 3%' OR UPPER(TRIM(zz.hdb_allocation)) LIKE '%HDB3%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'HDB 3 - 12-Jan-2023 to 20-Jan-2023')
        WHEN UPPER(TRIM(zz.hdb_allocation)) LIKE '%MSD%' THEN 
            (SELECT id FROM program_v1 WHERE name = 'MSD - 30-Jan-2023 to 04-Feb-2023')
        ELSE 
            NULL
    END AS allocated_program_id,
    
    -- User ID - find user by matching mobile with users.phone_number
    COALESCE(
        (SELECT u.id 
         FROM users u 
         WHERE TRIM(LOWER(u.email)) = TRIM(LOWER(zz.email))
         LIMIT 1),
        (SELECT u.id 
         FROM users u 
         WHERE TRIM(u.phone_number) = TRIM(zz.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(
                'HDB2223',
                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,
    
    -- Registration sequence numbers
    NULL AS registration_seq_number,
    NULL AS program_registration_seq_number,
    
    NOW() AS registration_date,
    NULL AS audit_ref_id,  -- Set to ID for audit trail
    NULL AS parent_ref_id, -- Set to ID for reference
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
    
FROM zz_ref_20250717_hdb_2022_2023_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
        WHEN 'registered' THEN 2
        WHEN 'hold' THEN 3
        ELSE 4
    END;

select * from hdb_program_registration where program_id in (SELECT id FROM program_v1 WHERE name IN (
        'HDB/MSD 2022-23'
    ));

select * from zz_ref_20250717_hdb_2022_2023_info_asis_trimmed; --988
-- ================================================================================================
-- 2.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
  AND created_by = -2
  AND program_id IN (
    SELECT id FROM program_v1 WHERE name IN (
        'HDB/MSD 2022-23'
    )
  );

-- ================================================================================================
-- 3. INSERT INTO hdb_registration_question_answer
-- ================================================================================================

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 answers based on question binding key
    CASE q.binding_key
        WHEN 'name' THEN to_jsonb(TRIM(zz.name))
        WHEN 'email' THEN to_jsonb(TRIM(LOWER(zz.email)))
        WHEN 'mobileNumber' THEN to_jsonb(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'
                    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
            )
        -- Map 2022-2023 specific ID fields
        WHEN 'pictureUrl' THEN to_jsonb(COALESCE(TRIM(zz.id_1_download_link), ''))  -- id_1_download_link maps to ID front
        WHEN 'idBack' THEN to_jsonb(COALESCE(TRIM(zz.id_2_download_link), ''))      -- id_2_download_link maps to ID back
        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_20250717_hdb_2022_2023_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 2022-23')
)
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
  AND created_by = -2
  AND registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2022-23'));

-- ================================================================================================
-- 4. INSERT INTO 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_20250717_hdb_2022_2023_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 IN (
        SELECT id FROM program_v1 WHERE name IN (
            'HDB/MSD 2022-23'
        )
    )
);

-- Update audit references
UPDATE hdb_registration_approval 
SET audit_ref_id = id, 
    parent_ref_id = registration_id
WHERE audit_ref_id IS NULL
  AND created_by = -2;

-- ================================================================================================
-- 5. INSERT INTO hdb_registration_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,
    NULL 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
WHERE app.created_by = -2
  AND reg.program_id IN (
    SELECT id FROM program_v1 WHERE name IN (
        'HDB/MSD 2022-23'
    )
  );

-- ================================================================================================
-- 6. INSERT INTO hdb_registration_travel_info
-- ================================================================================================
select * from zz_ref_20250717_hdb_2022_2023_info_asis_trimmed where status = 'Confirmed';
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_1_download_link) AS id_picture_url,        -- Map from id_1_download_link (ID front)
    TRIM(zz.id_2_download_link) AS id_back_picture_url,   -- Map from id_2_download_link (ID back)
    '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_20250717_hdb_2022_2023_info_asis_trimmed zz ON (
    TRIM(reg.mobile_number) = TRIM(zz.mobile)
    AND TRIM(LOWER(reg.email_address)) = TRIM(LOWER(zz.email))
)
WHERE reg.created_by = -2  -- Only process records created by this migration
  AND p.name IN (
    'HDB/MSD 2022-23'
  )  -- Only for 2022-23 programs
  AND reg.registration_status = 'completed';  -- Only for completed registrations

-- Update audit references
UPDATE hdb_registration_travel_info 
SET audit_ref_id = id 
WHERE audit_ref_id IS NULL 
  AND created_by = -2
  AND registration_id IN (
    SELECT id FROM hdb_program_registration WHERE program_id IN (
        SELECT id FROM program_v1 WHERE name IN (
            'HDB/MSD 2022-23'
        )
    )
  );

-- ================================================================================================
-- 7. INSERT INTO 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 reg.created_by = -2  -- Only process records created by this migration
  AND p.name IN (
    'HDB/MSD 2022-23'
  )  -- Only for 2022-23 programs
  AND reg.registration_status = 'completed';  -- Only for completed registrations

-- Update audit references
UPDATE hdb_registration_travel_plan 
SET audit_ref_id = id 
WHERE audit_ref_id IS NULL 
  AND created_by = -2
  AND registration_id IN (
    SELECT id FROM hdb_program_registration WHERE program_id IN (
        SELECT id FROM program_v1 WHERE name IN (
           'HDB/MSD 2022-23'
        )
    )
  );

-- ================================================================================================
-- 8. 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_20250717_hdb_2022_2023_info_asis_trimmed zz
WHERE hdb_program_registration.created_by = -2  -- Only update records created by this migration
  AND 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 IN (
    SELECT id FROM program_v1 WHERE name IN (
        'HDB/MSD 2022-23'
    )
  );

-- ================================================================================================
-- 9. 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 payment-related information
    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_20250717_hdb_2022_2023_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 IN (
        SELECT id FROM program_v1 WHERE name IN (
            'HDB/MSD 2022-23'
        )
    )
)
WHERE reg.created_by = -2  -- Only process records created by this migration
  AND reg.registration_status = 'completed';  -- Only for completed registrations

-- Update audit references
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
  AND created_by = -2;

-- ================================================================================================
-- 10. 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 pd.created_by = -2  -- Only process records created by this migration
  AND p.name IN (
    'HDB/MSD 2022-23'
  )  -- Only for specific programs
  AND reg.registration_status = 'completed';  -- Only for completed registrations

-- ================================================================================================
-- 11. 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_20250717_hdb_2022_2023_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 IN (
        SELECT id FROM program_v1 WHERE name IN (
            'HDB/MSD 2022-23'
        )
    )
)
WHERE reg.created_by = -2  -- Only process records created by this migration
  AND reg.registration_status = 'completed'  -- Only for completed registrations
  AND reg.proforma_invoice_seq_number IS NOT NULL;  -- Only if invoice sequence is available

-- Update audit references
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
  AND created_by = -2;

-- ================================================================================================
-- 12. VERIFICATION QUERIES
-- ================================================================================================

-- Verify the migration results
SELECT 'Registration Count (2022-23)' as metric, COUNT(*) as count
FROM hdb_program_registration
WHERE created_by = -2 AND program_id IN (
    SELECT id FROM program_v1 WHERE name IN (
        'HDB/MSD 2022-23'
    )
)
UNION ALL
SELECT 'Question Answer Count (2022-23)' as metric, COUNT(*) as count
FROM hdb_registration_question_answer
WHERE created_by = -2 AND registration_id IN (
    SELECT id FROM hdb_program_registration WHERE program_id IN (
        SELECT id FROM program_v1 WHERE name IN (
            'HDB/MSD 2022-23'
        )
    )
)
UNION ALL
SELECT 'Approval Count (2022-23)' as metric, COUNT(*) as count
FROM hdb_registration_approval
WHERE created_by = -2 AND registration_id IN (
    SELECT id FROM hdb_program_registration WHERE program_id IN (
        SELECT id FROM program_v1 WHERE name IN (
           'HDB/MSD 2022-23'
        )
    )
)
UNION ALL
SELECT 'Travel Info Count (2022-23)' as metric, COUNT(*) as count
FROM hdb_registration_travel_info
WHERE created_by = -2 AND registration_id IN (
    SELECT id FROM hdb_program_registration WHERE program_id IN (
        SELECT id FROM program_v1 WHERE name IN (
            'HDB/MSD 2022-23'
        )
    )
)
UNION ALL
SELECT 'Travel Plan Count (2022-23)' as metric, COUNT(*) as count
FROM hdb_registration_travel_plan
WHERE created_by = -2 AND registration_id IN (
    SELECT id FROM hdb_program_registration WHERE program_id IN (
        SELECT id FROM program_v1 WHERE name IN (
    'HDB/MSD 2022-23'
        )
    )
);

-- Check registration status distribution
SELECT 
    '2022-23' as program_year,
    registration_status,
    COUNT(*) as count
FROM hdb_program_registration
WHERE created_by = -2 AND program_id IN (
    SELECT id FROM program_v1 WHERE name IN (
        'HDB/MSD 2022-23'
    )
)
GROUP BY registration_status
ORDER BY registration_status;

-- Sample of migrated data
SELECT 
    reg.id,
    reg.full_name,
    reg.email_address,
    reg.registration_status,
    p.name as program_name,
    app.approval_status,
    ti.travel_info_status,
    tp.travel_plan_status
FROM hdb_program_registration reg
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_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
WHERE reg.created_by = -2 AND p.name IN (
'HDB/MSD 2022-23'
)
ORDER BY reg.id
LIMIT 10;
