psql -h localhost -U postgres -d prod_dump_01_09_2025 \
-c "\copy zz_load_tat_2025_data_20250902_clean from '/Users/Madhuri/Downloads/zz_load_tat_2025_data_20250902_clean.csv' CSV HEADER NULL 'NULL' ENCODING 'UTF8'"

-------------------------------------------------------------------------------
create table zz_load_tat_2025_data_20250902_clean
(
    batch character varying (255),
    full_name character varying (255),
    user_group character varying (255),
    full_name_2 character varying (255),
    mobile character varying (255),
    email character varying (255),
    gender character varying (255),
    dob date,
    city character varying (255),
    infinitheism_contact character varying (255),
    country_code character varying(5),
    bare_mobile character varying(15),
    country character varying(15),
    city_clean character varying(55)
);

select * from program_type_v1 where key in ('PT_HDBMSD','PT_TAT');

select json_agg(t) from (
select * from program_v1 where sub_program_type = 'PST_HDB' limit 1
)t;



INSERT INTO program_type_v1 (
    mode_of_operation,
    name,
    description,
    created_at,
    updated_at,
    deleted_at,
    online_type,
    max_session_duration_days,
    has_multiple_sessions,
    frequency,
    default_start_time,
    default_end_time,
    default_duration,
    requires_residence,
    involves_travel,
    has_checkin_checkout,
    requires_payment,
    requires_attendance_all_sessions,
    allows_minors,
    allows_proxy_registration,
    requires_approval,
    registration_level,
    is_active,
    created_by,
    updated_by,
    no_of_session,
    waitlist_applicable,
    max_capacity,
    venue,
    limited_seats,
    is_grouped_program,
    logo_url,
    banner_url,
    key,
    igst,
    cgst,
    sgst,
    gst_percentage,
    tds_percent,
    gst_number,
    tds_applicability,
    invoice_sender_name,
    invoice_sender_pan,
    invoice_sender_cin,
    invoice_sender_address,
    helpline_number,
    email_sender_name,
    venue_name_in_emails,
    is_special_program
)
VALUES (
    'online',
    'TAT',
    'Online Webinar',
    '2025-06-11 16:01:28.575211+00',
    '2025-06-11 16:01:28.575211+00',
    NULL,
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90',
    FALSE,
    FALSE,
    FALSE,
    TRUE,   -- requires_payment
    TRUE,   -- requires_attendance_all_sessions
    TRUE,
    FALSE,
    FALSE,
    'session',
    TRUE,
    -2,
    -2,
    12,
    FALSE,
    500,
    'Online Webinar',
    FALSE,
    FALSE,
    NULL,
    NULL,
    'PT_TAT',
    18.00,
    9.00,
    9.00,
    18.00,
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'infinitheism spiritual foundation private limited',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally,
Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar',
    TRUE   -- is_special_program
);

-- ================================================================================================
-- INSERT INTO program_v1 - Create TAT Program 2024-2025
-- ================================================================================================

INSERT INTO program_v1 (
    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,
    base_price,
    gst_percentage,
    currency,
    venue,
    status,
    is_active,
    code,
    banner_image_url,
    meta,
    created_at,
    updated_at,
    deleted_at,
    created_by,
    updated_by,
    cgst,
    sgst,
    igst,
    tds_percent,
    gst_number,
    tds_applicability,
    invoice_sender_name,
    invoice_sender_pan,
    invoice_sender_cin,
    invoice_sender_address,
    helpline_number,
    email_sender_name,
    venue_name_in_emails,
    registration_starts_at,
    registration_ends_at,
    starts_at,
    ends_at,
    launch_date,
    logo_url
) VALUES (
    '2025',                              -- program
    (SELECT id FROM program_type_v1 WHERE key = 'PT_TAT'),  -- program_type_id
    'TAT 2025',   -- name
    'Online webinar with Mahatria',  -- description
    'online',                                   -- mode_of_operation
    'webinar',                                  -- online_type
    1,                                          -- max_session_duration_days
    TRUE,                                       -- has_multiple_sessions
    'yearly',                                   -- frequency
    '18:00:00',                                 -- default_start_time
    '19:30:00',                                 -- default_end_time
    '90 minutes',                               -- duration
    FALSE,                                      -- requires_residence
    FALSE,                                      -- involves_travel
    FALSE,                                      -- has_checkin_checkout
    TRUE,                                       -- requires_payment
    TRUE,                                       -- requires_attendance_all_sessions
    TRUE,                                       -- allows_minors
    FALSE,                                      -- allows_proxy_registration
    FALSE,                                      -- requires_approval
    'session',                                  -- registration_level
    FALSE,                                      -- waitlist_applicable
    FALSE,                                      -- limited_seats
    FALSE,                                      -- is_grouped_program
    12,                                         -- no_of_session
    500,                                        -- max_capacity
    500,                                        -- total_seats
    0,                                          -- waitlist_trigger_count
    500,                                        -- available_seats
    0,                                          -- filled_seats
    508.47,                                     -- base_price (600/1.18)
    18.00,                                      -- gst_percentage
    'INR',                                      -- currency
    'Online Webinar',                  -- venue
    'completed',                                    -- status
    TRUE,                                       -- is_active
    'TAT2025',                                  -- code
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/banner/TAT.png',  -- banner_image_url
    '{}',  -- meta
    NOW(),                                      -- created_at
    NOW(),                                      -- updated_at
    NULL,                                       -- deleted_at
    -2,                                         -- created_by
    -2,                                         -- updated_by
    9.00,                                       -- cgst
    9.00,                                       -- sgst
    18.00,                                      -- igst
    10.00,                                      -- tds_percent
    '36AACCI7694H1Z5',                          -- gst_number
    'base_only',                                -- tds_applicability
    'TAT 2024-2025 - Online Webinar Series',   -- invoice_sender_name
    'AACCI7694H',                               -- invoice_sender_pan
    'U74900TN2011PTC083263',                    -- invoice_sender_cin
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',  -- invoice_sender_address
    '+91 98416 70000',                          -- helpline_number
    'Team infinitheism',                        -- email_sender_name
    'Online Webinar Platform',                  -- venue_name_in_emails
    '2024-09-01 00:00:00+05:30',              -- registration_starts_at
    '2024-09-30 23:59:59+05:30',              -- registration_ends_at
    '2024-10-15 18:00:00+05:30',              -- starts_at
    '2025-01-31 19:30:00+05:30',              -- ends_at
    '2024-09-15 10:00:00+05:30',              -- launch_date
    'https://infinitheism-serverless.s3.ap-south-1.amazonaws.com/static-images/logo/tat-logo.svg'  -- logo_url
);

ALTER TYPE sub_program_type_enum ADD VALUE 'PST_TAT';

UPDATE program_v1 
SET sub_program_type = CASE 
    WHEN LOWER(name) LIKE '%tat%' THEN 'PST_TAT'::sub_program_type_enum
    ELSE sub_program_type
END
WHERE sub_program_type IS NULL and code = 'TAT2025';
-- ================================================================================================
-- Verification Queries
-- ================================================================================================

-- Check if TAT program type was created successfully
SELECT 
    id, name, key, mode_of_operation, online_type, 
    max_capacity, no_of_session, is_active
FROM program_type_v1 
WHERE key = 'PT_TAT';

-- Check if TAT program was created successfully  
SELECT 
    id, name, code, mode_of_operation, online_type, 
    max_capacity, no_of_session, status, base_price
FROM program_v1 
WHERE code = 'TAT2025';

-- Show TAT program with its type
SELECT 
    p.id as program_id,
    p.name as program_name,
    p.code as program_code,
    p.mode_of_operation,
    p.online_type,
    p.base_price,
    p.status,
    pt.name as program_type_name,
    pt.key as program_type_key
FROM program_v1 p
JOIN program_type_v1 pt ON p.program_type_id = pt.id
WHERE pt.key = 'PT_TAT';

-- ================================================================================================
-- INSERT INTO program_session - Create TAT Program Sessions
-- ================================================================================================


INSERT INTO program_session (
    program_id,
    name,
    description,
    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,
    max_capacity,
    total_seats,
    available_seats,
    filled_seats,
    waitlist_trigger_count,
    venue,
    session_fee,
    base_price,
    gst_percentage,
    cgst,
    sgst,
    igst,
    currency,
    status,
    is_active,
    created_at,
    updated_at,
    created_by,
    updated_by,
    meta,
    tds_percent,
    gst_number,
    tds_applicability,
    invoice_sender_name,
    invoice_sender_pan,
    invoice_sender_cin,
    invoice_sender_address,
    helpline_number,
    email_sender_name,
    venue_name_in_emails
) VALUES
-- Session 1
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 1',
    'Online TAT session',
    '2024-10-15 18:00:00+05:30',
    '2024-10-15 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 1}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 2
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 2',
    'Online TAT session',
    '2024-10-22 18:00:00+05:30',
    '2024-10-22 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 2}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 3
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 3',
    'Online TAT session',
    '2024-10-29 18:00:00+05:30',
    '2024-10-29 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 3}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 4
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 4',
    'Online TAT session',
    '2024-11-05 18:00:00+05:30',
    '2024-11-05 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 4}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 5
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 5',
    'Online TAT session',
    '2024-11-12 18:00:00+05:30',
    '2024-11-12 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 5}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 6
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 6',
    'Online TAT session',
    '2024-11-19 18:00:00+05:30',
    '2024-11-19 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 6}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 7
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 7',
    'Online TAT session',
    '2024-11-26 18:00:00+05:30',
    '2024-11-26 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 7}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 8
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 8',
    'Online TAT session',
    '2024-12-03 18:00:00+05:30',
    '2024-12-03 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 8}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 9
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 9',
    'Online TAT session',
    '2024-12-10 18:00:00+05:30',
    '2024-12-10 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 9}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 10
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 10',
    'Online TAT session',
    '2024-12-17 18:00:00+05:30',
    '2024-12-17 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 10}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 11
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 11',
    'Online TAT session',
    '2025-01-07 18:00:00+05:30',
    '2025-01-07 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 11}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
),
-- Session 12 (Final Session)
(
    (SELECT id FROM program_v1 WHERE code = 'TAT2025'),
    'TAT 2025 - Session 12',
    'Online TAT session',
    '2025-01-14 18:00:00+05:30',
    '2025-01-14 19:30:00+05:30',
    'online',
    'webinar',
    1,
    TRUE,
    'yearly',
    '18:00:00',
    '19:30:00',
    '90 minutes',
    FALSE,
    FALSE,
    FALSE,
    TRUE,
    TRUE,
    TRUE,
    FALSE,
    FALSE,
    'session',
    FALSE,
    FALSE,
    FALSE,
    500,
    500,
    500,
    0,
    0,
    'Online Webinar',
    600.00,
    508.47,
    18.00,
    9.00,
    9.00,
    18.00,
    'INR',
    'active',
    TRUE,
    NOW(),
    NOW(),
    -2,
    -2,
    '{"webinar_link": "TBD", "recording_available": true, "session_number": 12, "final_session": true}',
    10.00,
    '36AACCI7694H1Z5',
    'base_only',
    'TAT 2025',
    'AACCI7694H',
    'U74900TN2011PTC083263',
    'No.8-5-248/1, Syndicate Bank Colony, Old Bowenpally, Secunderabad - 500011, Telangana, India',
    '+91 98416 70000',
    'Team infinitheism',
    'Online Webinar Platform'
);

-- ================================================================================================
-- Verification Queries for Program Sessions
-- ================================================================================================

-- Check all TAT program sessions
SELECT 
    id, name, start_time, end_time, 
    max_capacity, session_fee, status
FROM program_session 
WHERE program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025');

-- Count total sessions created
SELECT COUNT(*) as total_sessions
FROM program_session 
WHERE program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025');


select * from zz_load_tat_2025_data_20250902_clean;

select distinct infinitheism_contact from zz_load_tat_2025_data_20250902_clean;



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 code = 'TAT2025') AS program_id,
    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'
);

-- Insert program registrations for TAT 2025
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,
    registration_status,
    basic_details_status,
    registration_seq_number,
    program_registration_seq_number,
    registration_date,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    NULL as program_session_id,
    
    -- Program ID - TAT 2025 program
    (SELECT id FROM program_v1 WHERE code = 'TAT2025') AS program_id,

    -- Allocated Program ID - same as program_id for TAT (no sub-programs)
    (SELECT id FROM program_v1 WHERE code = 'TAT2025') AS allocated_program_id,
    
    -- User ID - find user by matching bare_mobile with users.phone_number
    (SELECT u.id 
     FROM users u 
     WHERE TRIM(u.phone_number) = TRIM(tat.bare_mobile)
     LIMIT 1) AS user_id,
    
    -- Basic details mapping
    TRIM(tat.full_name) AS full_name,
    TRIM(LOWER(tat.email)) AS email_address,
    TRIM(tat.mobile) AS mobile_number,
    
    -- Gender mapping (ensure it matches enum values)
    CASE 
        WHEN LOWER(TRIM(tat.gender)) IN ('male', 'm') THEN 'Male'::gender_enum
        WHEN LOWER(TRIM(tat.gender)) IN ('female', 'f') THEN 'Female'::gender_enum
        WHEN LOWER(TRIM(tat.gender)) IN ('other', 'o') THEN 'Other'::gender_enum
        ELSE 'Male'::gender_enum
    END AS gender,
    
    -- Date of birth conversion
    CASE 
        WHEN tat.dob IS NOT NULL THEN tat.dob
        ELSE NULL
    END AS dob,
    
    -- City mapping
    CASE 
        WHEN TRIM(tat.city_clean) 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(tat.city_clean)
        ELSE 'Other'
    END AS city,
    
    -- Other city name
    CASE 
        WHEN TRIM(tat.city_clean) 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(tat.city_clean)
        ELSE NULL
    END AS other_city_name,
    
    -- Country mapping
    CASE 
        WHEN UPPER(TRIM(tat.country)) IN ('USA', 'US') THEN 'United States'
        WHEN UPPER(TRIM(tat.country)) IN ('UK', 'GB') THEN 'United Kingdom'  
        WHEN UPPER(TRIM(tat.country)) IN ('UAE') THEN 'United Arab Emirates'
        WHEN UPPER(TRIM(tat.country)) = 'IN' THEN 'India'
        WHEN UPPER(TRIM(tat.country)) = 'INDIA' THEN 'India'
        ELSE COALESCE(NULLIF(TRIM(tat.country), ''), NULL)
    END AS country_name,

    CASE 
        WHEN UPPER(TRIM(tat.infinitheism_contact)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION')
            OR (CASE UPPER(TRIM(tat.infinitheism_contact))
                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(tat.infinitheism_contact))
                    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(tat.infinitheism_contact)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION')
            OR (CASE UPPER(TRIM(tat.infinitheism_contact))
                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(tat.infinitheism_contact)) = 'OTHER' THEN 'OtherInfinitheismContact'
            ELSE TRIM(tat.infinitheism_contact)
        END
        ELSE NULL
    END AS other_infinitheism_contact,
    
    -- Status fields
    'completed'::registration_status_enum AS registration_status,
    'completed'::basic_details_status_enum AS basic_details_status,
    
    -- Generate registration sequence number for all registrations (following insert order)
    CONCAT(
        'TAT2025',
        LPAD(
            (ROW_NUMBER() OVER (
                ORDER BY 
                    tat.full_name
            ))::TEXT,
            5,
            '0'
        )
    ) AS registration_seq_number,
    
    -- Generate program registration sequence number (integer, following insert order)
    (ROW_NUMBER() OVER (
        ORDER BY 
            tat.full_name
    ))::INTEGER AS program_registration_seq_number,
    
    -- Registration date - use current timestamp
    NOW() AS registration_date,
    
    -- Audit fields
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by

FROM zz_load_tat_2025_data_20250902_clean tat
JOIN users u ON TRIM(u.phone_number) = TRIM(tat.bare_mobile)
WHERE tat.bare_mobile IS NOT NULL
  AND TRIM(tat.bare_mobile) != '';


SELECT COUNT(*) AS total_registrations
FROM hdb_program_registration
WHERE program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025');


-- Insert question answers for all TAT registration fields
INSERT INTO hdb_registration_question_answer (
    registration_id,
    question_id,
    answer_value,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    reg.id AS registration_id,
    q.id AS question_id,
    
    -- Map answer values based on binding_key
    CASE q.binding_key
        WHEN 'name' THEN to_jsonb(COALESCE(TRIM(tat.full_name), ''))
        WHEN 'email' THEN to_jsonb(COALESCE(TRIM(LOWER(tat.email)), ''))
        WHEN 'mobileNumber' THEN to_jsonb(COALESCE(TRIM(tat.mobile), ''))
        WHEN 'gender' THEN to_jsonb(
            CASE 
                WHEN LOWER(TRIM(tat.gender)) IN ('male', 'm') THEN 'Male'
                WHEN LOWER(TRIM(tat.gender)) IN ('female', 'f') THEN 'Female'
                WHEN LOWER(TRIM(tat.gender)) IN ('other', 'o') THEN 'Other'
                ELSE 'Male'
            END
        )
        WHEN 'dob' THEN 
            CASE 
                WHEN tat.dob IS NOT NULL THEN to_jsonb(tat.dob::text)
                ELSE to_jsonb(''::text)
            END
        WHEN 'city' THEN to_jsonb(
            CASE 
                WHEN TRIM(tat.city_clean) 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(tat.city_clean)
                ELSE 'Other'
            END
        )
        WHEN 'otherCityName' THEN to_jsonb(
            CASE 
                WHEN TRIM(tat.city_clean) 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(tat.city_clean)
                ELSE ''::text
            END
        )
        WHEN 'countryName' THEN to_jsonb(
            CASE 
                WHEN UPPER(TRIM(tat.country)) IN ('USA', 'US') THEN 'United States'
                WHEN UPPER(TRIM(tat.country)) IN ('UK', 'GB') THEN 'United Kingdom'
                WHEN UPPER(TRIM(tat.country)) IN ('UAE') THEN 'United Arab Emirates'
                WHEN UPPER(TRIM(tat.country)) = 'IN' THEN 'India'
                WHEN UPPER(TRIM(tat.country)) = 'INDIA' THEN 'India'
                ELSE COALESCE(NULLIF(TRIM(tat.country), ''), 'India')
            END
        )
        WHEN 'rmContact' THEN to_jsonb(
            CASE 
                WHEN UPPER(TRIM(tat.infinitheism_contact)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION', 'NO CONTACT', '')
                    OR (CASE UPPER(TRIM(tat.infinitheism_contact))
                        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'
                        WHEN 'NO CONTACT' THEN '9841660000'
                        ELSE NULL
                    END) = '9841660000' 
                THEN 'Generic Contact'
                ELSE COALESCE(NULLIF(TRIM(tat.infinitheism_contact), ''), 'No Contact')
            END
        )
        WHEN 'otherInfinitheismContact' THEN to_jsonb(
            CASE 
                WHEN UPPER(TRIM(tat.infinitheism_contact)) IN ('RAMEL', 'GOPINATH', 'ATHIAMAN', 'OTHER', 'SAKTHI', 'JAYA', 'ORGANIZATION', 'NO CONTACT', '')
                    OR (CASE UPPER(TRIM(tat.infinitheism_contact))
                        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'
                        WHEN 'NO CONTACT' THEN '9841660000'
                        ELSE NULL
                    END) = '9841660000' 
                THEN CASE 
                    WHEN UPPER(TRIM(tat.infinitheism_contact)) = 'OTHER' THEN 'OtherInfinitheismContact'
                    WHEN UPPER(TRIM(tat.infinitheism_contact)) = 'NO CONTACT' THEN 'NoInfinitheismContact'
                    WHEN TRIM(tat.infinitheism_contact) = '' THEN 'EmptyInfinitheismContact'
                    ELSE TRIM(tat.infinitheism_contact)
                END
                ELSE ''::text
            END
        )
        ELSE to_jsonb(''::text)
    END AS answer_value,
    
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by

FROM hdb_program_registration reg
JOIN zz_load_tat_2025_data_20250902_clean tat 
    ON TRIM(reg.mobile_number) = TRIM(tat.mobile)
    AND TRIM(LOWER(reg.email_address)) = TRIM(LOWER(tat.email))
JOIN hdb_question q ON q.created_by = -2 
    AND q.binding_key IN (
        'name',
        'email',
        'mobileNumber',
        'gender', 
        'dob',
        'city',
        'otherCityName',
        'countryName',
        'rmContact',
        'otherInfinitheismContact'
    )
WHERE reg.created_by = -2
  AND reg.program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025');


-- Insert user participation summary records for TAT 2025
INSERT INTO user_participation_summary (
    user_id,
    program_id,
    program_name,
    sub_program_id,
    sub_program_name,
    session_id,
    session_name,
    sub_program_type,
    program_starts_at,
    program_ends_at,
    created_by,
    updated_by,
    created_at,
    updated_at
)
SELECT
    reg.user_id,
    reg.program_id,
    prog.name AS program_name,
    reg.allocated_program_id AS sub_program_id,
    CASE 
        WHEN reg.allocated_program_id IS NOT NULL 
        THEN (SELECT name FROM program_v1 WHERE id = reg.allocated_program_id LIMIT 1)
        ELSE NULL
    END AS sub_program_name,
    reg.program_session_id AS session_id,
    ps.name AS session_name,
    'PST_TAT'::sub_program_type_enum AS sub_program_type,
    COALESCE(prog.starts_at, prog.registration_starts_at) AS program_starts_at,
    COALESCE(prog.ends_at, prog.registration_ends_at) AS program_ends_at,
    -2 AS created_by,
    -2 AS updated_by,
    NOW() AS created_at,
    NOW() AS updated_at

FROM hdb_program_registration reg
JOIN program_v1 prog ON prog.id = reg.program_id
LEFT JOIN program_session ps ON ps.id = reg.program_session_id
WHERE reg.registration_status = 'completed'
  AND reg.user_id IS NOT NULL
  AND reg.program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025')
  AND reg.created_by = -2;


-- Check TAT program registrations
SELECT 
    'TAT Program Registration Count' as metric, 
    COUNT(*) as count
FROM hdb_program_registration 
WHERE program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025')
AND created_by = -2

UNION ALL

SELECT 
    'TAT Question Answer Count' as metric, 
    COUNT(*) as count
FROM hdb_registration_question_answer rqa
JOIN hdb_program_registration reg ON reg.id = rqa.registration_id
WHERE reg.program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025')
AND reg.created_by = -2

UNION ALL

SELECT 
    'TAT User Participation Summary Count' as metric, 
    COUNT(*) as count
FROM user_participation_summary ups
WHERE ups.program_id = (SELECT id FROM program_v1 WHERE code = 'TAT2025')
AND ups.created_by = -2;
