-- TAT_3 (program 45) participant data corrections (WhatsApp request, 2026-07-31)
--   1) Chithra Senthil Kumar — TAT26-457 — mobile number -> 99401-99688
--   2) Paripoorani K — TAT26-194 — email -> paripooranikannan@gmail.com

-- Preview current values before touching anything
SELECT registration_seq_number, full_name, mobile_number, email_address
FROM hdb_program_registration
WHERE program_id = 45 AND registration_seq_number IN ('TAT26-457', 'TAT26-194');

SELECT r.registration_seq_number, q.binding_key, qa.question_id, qa.answer_value
FROM hdb_registration_question_answer qa
JOIN hdb_program_registration r ON r.id = qa.registration_id
JOIN hdb_program_question pq ON pq.program_id = r.program_id
JOIN hdb_question q ON q.id = pq.question_id AND q.binding_key IN ('email', 'mobileNumber')
WHERE qa.question_id = q.id
  AND r.program_id = 45
  AND r.registration_seq_number IN ('TAT26-457', 'TAT26-194');

BEGIN;

-- 1) Mobile number (keeps existing country-code prefix, replaces the 10-digit number)
WITH target AS (
  SELECT id, mobile_number FROM hdb_program_registration
  WHERE program_id = 45 AND registration_seq_number = 'TAT26-457'
)
UPDATE hdb_program_registration r
SET mobile_number = SUBSTRING(target.mobile_number FROM 1 FOR GREATEST(LENGTH(target.mobile_number) - 10, 0)) || '9940199688',
    updated_at = NOW()
FROM target
WHERE r.id = target.id;

-- 2) Email
UPDATE hdb_program_registration
SET email_address = 'paripooranikannan@gmail.com',
    updated_at = NOW()
WHERE program_id = 45 AND registration_seq_number = 'TAT26-194';

-- Sync the FS_BASICDETAILS question answers to match (resolved via binding_key)
UPDATE hdb_registration_question_answer qa
SET answer_value = to_jsonb(to_jsonb(r) ->> CASE q.binding_key
      WHEN 'mobileNumber' THEN 'mobile_number'
      WHEN 'email' THEN 'email_address'
    END),
    updated_at = NOW()
FROM hdb_program_registration r
JOIN hdb_program_question pq ON pq.program_id = r.program_id
JOIN hdb_question q ON q.id = pq.question_id AND q.binding_key IN ('email', 'mobileNumber')
WHERE qa.registration_id = r.id
  AND qa.question_id = q.id
  AND r.program_id = 45
  AND r.registration_seq_number IN ('TAT26-457', 'TAT26-194');

SELECT registration_seq_number, full_name, mobile_number, email_address
FROM hdb_program_registration
WHERE program_id = 45 AND registration_seq_number IN ('TAT26-457', 'TAT26-194');

-- Review above, then COMMIT; or ROLLBACK;
COMMIT;
