---------------------

---------------------

-- ================================================================================================
-- 1. INSERT PROGRAM_V1 RECORDS FOR HDB/MSD 2021-22
-- ================================================================================================
select * from program_v1;
SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD';
select * from program_type_v1;

-- corrected inserts for 2021-2022 (session total_seats changed to 1000;
-- preserved session filled_seats values)
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'),
  '2021 - 2022',
  (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
  'HDB/MSD 2021-22',
  '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}]}',
  '2021-07-29 15:03:56.323064+05:30',
  '2021-07-29 15:03:56.323064+05:30',
  NULL,
  -2,
  -2,
  FALSE,
  FALSE,
  FALSE,
  'cf5d08b9-c637-449f-b590-b96927a05cf1',
  TRUE,
  NULL,
  1,
  9.00,
  9.00,
  18.00,
  0.00,
  '2021-08-15 05:30:00+05:30',
  '2021-08-31 05:29:59+05:30',
  '2021-12-02 10:30:00+05:30',
  '2022-02-05 02:30:00+05:30',
  '2021-12-02 08:30:00+05:30',
  '2022-02-05 22:30:00+05:30',
  10.00,
  '36AACCI7694H1Z5',
  'base_only',
  'HDB/MSD 2021-22',
  'AACCI7694H',
  'U74900TN2011PTC083263',
  NULL,
  'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
  '+91 98416 70000',
  'HDB/MSD 2021-22',
  'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
  '2022-11-17 14:30:00+05:30',
  'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
  '2021-12-02 22:30:00+05:30',
  '2022-02-05 10:30:00+05:30',
  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
),
(
  nextval('program_v1_id_seq'),
  '2021 - 2022',
  (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
  'HDB 1 - 02-Dec-2021 to 10-Dec-2021',
  '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,
  -- changed total_seats -> 1000 (was 202)
  1000,
  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',
  '{}',
  '2021-07-29 15:03:56.323064+05:30',
  '2021-07-29 15:03:56.323064+05:30',
  NULL,
  -2,
  -2,
  FALSE,
  FALSE,
  FALSE,
  'cf5d08b9-c637-449f-b590-b96927a05cf1',
  FALSE,
  NULL,
  2,
  9.00,
  9.00,
  18.00,
  0.00,
  '2021-08-15 05:30:00+05:30',
  '2021-08-31 05:29:59+05:30',
  '2021-12-01 10:30:00+05:30',
  '2021-12-11 02:30:00+05:30',
  '2021-12-02 08:30:00+05:30',
  '2021-12-10 22:30:00+05:30',
  10.00,
  '36AACCI7694H1Z5',
  'base_only',
  'HDB/MSD 2021-22',
  'AACCI7694H',
  'U74900TN2011PTC083263',
  NULL,
  'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
  '+91 98416 70000',
  'HDB/MSD 2021-22',
  'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
  '2022-11-17 14:30:00+05:30',
  'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
  '2021-12-01 22:30:00+05:30',
  '2021-12-11 10:30:00+05:30',
  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
),
(
  nextval('program_v1_id_seq'),
  '2021 - 2022',
  (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
  'HDB 2 - 23-Dec-2021 to 31-Dec-2021',
  '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,
  -- changed total_seats -> 1000 (was 204)
  1000,
  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',
  '{}',
  '2021-07-29 15:03:56.323064+05:30',
  '2021-07-29 15:03:56.323064+05:30',
  NULL,
  -2,
  -2,
  FALSE,
  FALSE,
  FALSE,
  'cf5d08b9-c637-449f-b590-b96927a05cf1',
  FALSE,
  NULL,
  3,
  9.00,
  9.00,
  18.00,
  0.00,
  '2021-08-15 05:30:00+05:30',
  '2021-08-31 05:29:59+05:30',
  '2021-12-22 10:30:00+05:30',
  '2022-01-01 02:30:00+05:30',
  '2021-12-23 08:30:00+05:30',
  '2021-12-31 22:30:00+05:30',
  10.00,
  '36AACCI7694H1Z5',
  'base_only',
  'HDB/MSD 2021-22',
  'AACCI7694H',
  'U74900TN2011PTC083263',
  NULL,
  'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
  '+91 98416 70000',
  'HDB/MSD 2021-22',
  'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
  '2022-11-17 14:30:00+05:30',
  'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
  '2021-12-22 22:30:00+05:30',
  '2022-01-01 10:30:00+05:30',
  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
),
(
  nextval('program_v1_id_seq'),
  '2021 - 2022',
  (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
  'HDB 3 - 13-Jan-2022 to 21-Jan-2022',
  '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,
  -- changed total_seats -> 1000 (was 201)
  1000,
  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',
  '{}',
  '2021-07-29 15:03:56.323064+05:30',
  '2021-07-29 15:03:56.323064+05:30',
  NULL,
  -2,
  -2,
  FALSE,
  FALSE,
  FALSE,
  'cf5d08b9-c637-449f-b590-b96927a05cf1',
  FALSE,
  NULL,
  4,
  9.00,
  9.00,
  18.00,
  0.00,
  '2021-08-15 05:30:00+05:30',
  '2021-08-31 05:29:59+05:30',
  '2022-01-12 10:30:00+05:30',
  '2022-01-22 02:30:00+05:30',
  '2022-01-13 08:30:00+05:30',
  '2022-01-21 22:30:00+05:30',
  10.00,
  '36AACCI7694H1Z5',
  'base_only',
  'HDB/MSD 2021-22',
  'AACCI7694H',
  'U74900TN2011PTC083263',
  NULL,
  'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
  '+91 98416 70000',
  'HDB/MSD 2021-22',
  'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
  '2022-11-17 14:30:00+05:30',
  'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
  '2022-01-12 22:30:00+05:30',
  '2022-01-22 10:30:00+05:30',
  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
),
(
  nextval('program_v1_id_seq'),
  '2021 - 2022',
  (SELECT id FROM program_type_v1 WHERE key = 'PT_HDBMSD'),
  'MSD - 31-Jan-2022 to 05-Feb-2022',
  '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,
  -- changed total_seats -> 1000 (was 200)
  1000,
  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',
  '{}',
  '2021-07-29 15:03:56.323064+05:30',
  '2021-07-29 15:03:56.323064+05:30',
  NULL,
  -2,
  -2,
  FALSE,
  FALSE,
  FALSE,
  'cf5d08b9-c637-449f-b590-b96927a05cf1',
  FALSE,
  NULL,
  5,
  9.00,
  9.00,
  18.00,
  0.00,
  '2021-08-15 05:30:00+05:30',
  '2021-08-31 05:29:59+05:30',
  '2022-01-30 10:30:00+05:30',
  '2022-02-06 02:30:00+05:30',
  '2022-01-31 08:30:00+05:30',
  '2022-02-05 22:30:00+05:30',
  10.00,
  '36AACCI7694H1Z5',
  'base_only',
  'HDB/MSD 2021-22',
  'AACCI7694H',
  'U74900TN2011PTC083263',
  NULL,
  'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India.',
  '+91 98416 70000',
  'HDB/MSD 2021-22',
  'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
  '2022-11-17 14:30:00+05:30',
  'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/hdb-logo.svg',
  '2022-01-30 22:30:00+05:30',
  '2022-02-06 10:30:00+05:30',
  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
);


-- Program Session INSERT statements for HDB/MSD 2021-22 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 2021-22 Session (parent program)
(
    (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22'),
    'HDB/MSD 2021-22 Main Session',
    'Main HDB/MSD 2021-22 program session',
    '2021-08-15 05:30:00+05:30',
    '2021-08-31 05:29:59+05:30',
    '2021-12-02 10:30:00+05:30',
    '2022-02-05 02:30:00+05:30',
    '2021-12-02 08:30:00+05:30',
    '2022-02-05 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 2021-22',
    '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 2021-22',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2022-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB2122',
    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',
    '2021-07-29 15:03:56.323064+05:30',
    '2021-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2021-12-02 22:30:00+05:30',
    '2022-02-05 10:30:00+05:30'
),
-- HDB 1 Session
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 1 - 02-Dec-2021 to 10-Dec-2021'),
    'HDB 1 Session - Dec 2021',
    'First HDB session for Dec 2021',
    '2021-08-15 05:30:00+05:30',
    '2021-08-31 05:29:59+05:30',
    '2021-12-01 10:30:00+05:30',
    '2021-12-11 02:30:00+05:30',
    '2021-12-02 08:30:00+05:30',
    '2021-12-10 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,
    0,
    202,
    202,
    1.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2021-22',
    '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 2021-22',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2022-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB1_2122',
    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',
    '2021-07-29 15:03:56.323064+05:30',
    '2021-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2021-12-01 22:30:00+05:30',
    '2021-12-11 10:30:00+05:30'
),
-- HDB 2 Session
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 2 - 23-Dec-2021 to 31-Dec-2021'),
    'HDB 2 Session - Dec 2021',
    'Second HDB session for Dec 2021',
    '2021-08-15 05:30:00+05:30',
    '2021-08-31 05:29:59+05:30',
    '2021-12-22 10:30:00+05:30',
    '2022-01-01 02:30:00+05:30',
    '2021-12-23 08:30:00+05:30',
    '2021-12-31 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,
    0,
    204,
    204,
    2.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2021-22',
    '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 2021-22',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2022-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB2_2122',
    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',
    '2021-07-29 15:03:56.323064+05:30',
    '2021-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2021-12-22 22:30:00+05:30',
    '2022-01-01 10:30:00+05:30'
),
-- HDB 3 Session
(
    (SELECT id FROM program_v1 WHERE name = 'HDB 3 - 13-Jan-2022 to 21-Jan-2022'),
    'HDB 3 Session - Jan 2022',
    'Third HDB session for Jan 2022',
    '2021-08-15 05:30:00+05:30',
    '2021-08-31 05:29:59+05:30',
    '2022-01-12 10:30:00+05:30',
    '2022-01-22 02:30:00+05:30',
    '2022-01-13 08:30:00+05:30',
    '2022-01-21 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,
    0,
    201,
    201,
    1.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2021-22',
    '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 2021-22',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2022-11-17 14:30:00+05:30',
    'completed',
    true,
    'HDB3_2122',
    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',
    '2021-07-29 15:03:56.323064+05:30',
    '2021-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2022-01-12 22:30:00+05:30',
    '2022-01-22 10:30:00+05:30'
),
-- MSD Session
(
    (SELECT id FROM program_v1 WHERE name = 'MSD - 31-Jan-2022 to 05-Feb-2022'),
    'MSD Session - Jan 2022',
    'MSD session for Jan-Feb 2022',
    '2021-08-15 05:30:00+05:30',
    '2021-08-31 05:29:59+05:30',
    '2022-01-30 10:30:00+05:30',
    '2022-02-06 02:30:00+05:30',
    '2022-01-31 08:30:00+05:30',
    '2022-02-05 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,
    0,
    200,
    200,
    2.00,
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'HDB/MSD 2021-22',
    '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 2021-22',
    'Leonia Holistic Destination, Bommarasipet, Shamirpet Mandal, Medchal-Malkajgiri District, Hyderabad - 500078.',
    '2022-11-17 14:30:00+05:30',
    'completed',
    true,
    'MSD_2122',
    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',
    '2021-07-29 15:03:56.323064+05:30',
    '2021-07-29 15:03:56.323064+05:30',
    -2,
    -2,
    '2022-01-30 22:30:00+05:30',
    '2022-02-06 10:30:00+05:30'
);


select id from program_v1 where name = 'HDB/MSD 2021-22';
select * from hdb_program_question where program_id = 962;
-- Insert mapping for 2021-22 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 2021-22'),                -- replace with actual program_id for 2021-22
    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'
)
ORDER BY q.form_section_id, q.binding_key;

-- ================================================================================================
-- 1. 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,
    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 parent program
    (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22') as program_id,

    -- Allocated Program ID based on hdb_allocation
    (SELECT id FROM program_v1 WHERE name = 
        CASE TRIM(zz.hdb_allocation)
            WHEN 'HDB1' THEN 'HDB 1 - 02-Dec-2021 to 10-Dec-2021'
            WHEN 'HDB2' THEN 'HDB 2 - 23-Dec-2021 to 31-Dec-2021'
            WHEN 'HDB3' THEN 'HDB 3 - 13-Jan-2022 to 21-Jan-2022'
            WHEN 'MSD' THEN 'MSD - 31-Jan-2022 to 05-Feb-2022'
            ELSE 'HDB/MSD 2021-22' -- Default to main program
        END
     LIMIT 1) AS allocated_program_id,

    -- User ID - find user by matching mobile with users.phone_number
    
    (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,
    NULL AS first_song_preference,
    NULL AS second_song_preference,
    
    
    -- Registration status based on status field
    CASE LOWER(TRIM(zz.status))
        WHEN 'confirmed' THEN 'completed'::registration_status_enum
        WHEN 'ytd' THEN 'pending'::registration_status_enum
        ELSE 'pending'::registration_status_enum -- Default for 2021-22 historical data
    END AS registration_status,

    -- Basic details status
    'completed'::basic_details_status_enum 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,
    (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_20250717_hdb_2021_2022_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 'participated' THEN 1
        WHEN 'approved' THEN 2
        WHEN 'approved-waitlist' THEN 3
        WHEN 'rejected' THEN 4
        WHEN 'not participated' THEN 5
        WHEN 'withdrawn' THEN 6
        WHEN 'cancelled' THEN 7
        ELSE 8
    END;


select * from zz_ref_20250717_hdb_2021_2022_info_asis_trimmed;
-- ================================================================================================
-- 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
  AND program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22');

-- ================================================================================================
-- 2. INSERT INTO hdb_registration_question_answer
-- ================================================================================================

-- Insert question answers for required fields based on available data
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), ''))
        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_2021_2022_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 2021-22')
)
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'
);

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;

-- ================================================================================================
-- 3. INSERT INTO hdb_registration_approval
-- ================================================================================================

INSERT INTO hdb_registration_approval (
    registration_id,
    approval_status,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    'approved'::approval_status_enum AS approval_status, -- All 2021-22 are completed/approved
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
FROM hdb_program_registration reg
WHERE reg.created_by = -2
  AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22');

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

-- ================================================================================================
-- 4. INSERT INTO hdb_registration_approval_track
-- ================================================================================================

INSERT INTO hdb_registration_approval_track (
    approval_id,
	registration_id,
    approval_status,
    type,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    app.id AS approval_id,
	app.registration_id,
    'approved'::approval_status_enum AS approval_status,
    'registration'AS type,
    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 = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22');

-- ================================================================================================
-- 5. INSERT INTO hdb_registration_travel_info
-- ================================================================================================

INSERT INTO hdb_registration_travel_info (
    registration_id,
    travel_info_status,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    'completed'::travel_status_enum AS travel_info_status,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
FROM hdb_program_registration reg
WHERE reg.created_by = -2
  AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22')
  AND reg.registration_status = 'completed';

-- ================================================================================================
-- 5.1. UPDATE hdb_registration_travel_info audit_ref_id
-- ================================================================================================

UPDATE hdb_registration_travel_info 
SET audit_ref_id = id 
WHERE audit_ref_id IS NULL 
  AND created_by = -2;

-- ================================================================================================
-- 6. INSERT INTO hdb_registration_travel_plan
-- ================================================================================================

INSERT INTO hdb_registration_travel_plan (
    registration_id,
    travel_plan_status,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    'completed'::travel_status_enum AS travel_plan_status,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
FROM hdb_program_registration reg
WHERE reg.created_by = -2
  AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22')
  AND reg.registration_status = 'completed';

-- ================================================================================================
-- 6.1. UPDATE hdb_registration_travel_plan audit_ref_id
-- ================================================================================================

UPDATE hdb_registration_travel_plan 
SET audit_ref_id = id 
WHERE audit_ref_id IS NULL 
  AND created_by = -2;

-- ================================================================================================
-- 7. INSERT INTO hdb_registration_payment_detail
-- ================================================================================================

INSERT INTO hdb_registration_payment_detail (
    registration_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
FROM hdb_program_registration reg
WHERE reg.created_by = -2
  AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22')
  AND reg.registration_status = 'completed';

-- ================================================================================================
-- 7.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
  AND created_by = -2;

-- ================================================================================================
-- 8. INSERT INTO hdb_registration_payment_details_history
-- ================================================================================================

INSERT INTO hdb_registration_payment_details_history (
    payment_details_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    pd.id AS payment_details_id,
    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
WHERE pd.created_by = -2
  AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22')
  AND reg.registration_status = 'completed';

-- ================================================================================================
-- 9. INSERT INTO hdb_registration_invoice_detail
-- ================================================================================================

INSERT INTO hdb_registration_invoice_detail (
    registration_id,
    invoice_pdf_url,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    NULL AS invoice_pdf_url,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
FROM hdb_program_registration reg
WHERE reg.created_by = -2
  AND reg.program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22')
  AND reg.registration_status = 'completed'
  AND reg.proforma_invoice_seq_number IS NOT NULL;

-- ================================================================================================
-- 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
  AND created_by = -2;  -- Only update records created by this migration

------------------------------------------------------------------------------------------------------------------------------------
-- Update program status to completed for all 2021-22 programs
UPDATE program_v1
SET status = 'completed'
WHERE name IN ('HDB/MSD 2021-22','HDB 1 - 02-Dec-2021 to 10-Dec-2021','HDB 2 - 23-Dec-2021 to 31-Dec-2021',
'HDB 3 - 13-Jan-2022 to 21-Jan-2022',
'MSD - 31-Jan-2022 to 05-Feb-2022');

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

-- Verify the migration results
SELECT 'Registration Count (2021-22)' as metric, COUNT(*) as count
FROM hdb_program_registration
WHERE created_by = -2 AND program_id = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22')
UNION ALL
SELECT 'Question Answer Count (2021-22)' 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 = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22'))
UNION ALL
SELECT 'Approval Count (2021-22)' 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 = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22'))
UNION ALL
SELECT 'Travel Info Count (2021-22)' 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 = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22'))
UNION ALL
SELECT 'Travel Plan Count (2021-22)' 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 = (SELECT id FROM program_v1 WHERE name = 'HDB/MSD 2021-22'));