-- =============================================================================
-- TAT_3 (program 45) Org-participant batch registration — BATCH 2
--
-- Split out of 2026-07-28-tat3-org-participants-inserts-updates.sql — same
-- script, scoped to the phone numbers held back from the main batch, to be
-- run separately later.
--
-- Phone list below is the original 3 held-back numbers with 9791140214
-- swapped out (moved to user_type = 'Seeker' — see
-- ../2026-08-01-tat3-batch2-user-corrections.sql, run that first) for
-- Parthiban Anthonyraj (9789102500) and Mano (9841042289).
--
-- Run in order:
--   A) Create + pre-fill the staging table (stg_tat3_org_participants_batch2).
--   B) Manually fill in the fields with no data source (see the TODOs) using
--      the UPDATE template below.
--   C) Run the insert transaction. It does NOT auto-commit — review the
--      summary at the bottom, then COMMIT; or ROLLBACK; in the same session.
--   D) Once committed, drop the staging table (bottom of this file).
-- =============================================================================


-- =============================================================================
-- SECTION A — create and pre-fill the staging table.
-- =============================================================================

CREATE TABLE stg_tat3_org_participants_batch2 (
  user_id                    bigint PRIMARY KEY REFERENCES users(id),

  -- (1) registrationForWhom -> hdb_program_registration.registration_mode
  registration_for_whom      varchar(20),    -- TODO: fill in — 'self' | 'other'

  -- (2) name -> hdb_program_registration.full_name
  full_name                  varchar(255),   -- pre-filled from users.legal_full_name (existing fact)

  -- (3) gender -> hdb_program_registration.gender
  gender                     varchar(20),    -- pre-filled from users.gender (existing fact)

  -- (4) countryName -> hdb_program_registration.country_name
  country_name               varchar(255),   -- pre-filled from users.country_name (existing fact)

  -- (5) city -> hdb_program_registration.city
  city                       varchar(100),   -- pre-filled from users.address (existing fact — confirmed address=city)

  -- (6) otherCityName -> hdb_program_registration.other_city_name
  other_city_name            varchar(255),   -- TODO: fill in ONLY if city = 'Other'

  -- (7) mobileNumber -> hdb_program_registration.mobile_number
  mobile_number              varchar(20),    -- pre-filled from users.country_code || phone_number (existing fact)

  -- (8) email -> hdb_program_registration.email_address
  email                      varchar(255),   -- pre-filled from users.email (existing fact)

  -- (9) dob -> hdb_program_registration.dob
  dob                        date,           -- pre-filled from users.dob (existing fact)

  -- (10) rmContact -> hdb_program_registration.rm_contact
  rm_contact                 bigint,         -- TODO: fill in — a users.id of the RM contact person

  -- (11) otherInfinitheismContact -> hdb_program_registration.other_infinitheism_contact
  other_infinitheism_contact varchar(255),   -- TODO: fill in ONLY if rm_contact refers to 'other'

  -- (12) profileUrl -> hdb_program_registration.user_profile_url
  profile_url                text,           -- pre-filled from users.profile_url where present (existing fact)

  -- (13) terms -> hdb_program_registration.terms_accepted
  terms_accepted             boolean,        -- TODO: fill in — true | false

  notes                      text            -- optional: any notes while gathering data
);

INSERT INTO stg_tat3_org_participants_batch2 (
  user_id, full_name, gender, country_name, city, mobile_number, email, dob, profile_url
)
SELECT
  u.id,
  u.legal_full_name,
  u.gender,
  u.country_name,
  u.address,
  (COALESCE(u.country_code, '') || u.phone_number),
  NULLIF(u.email, ''),
  u.dob,
  NULLIF(u.profile_url, '')
FROM users u
WHERE u.user_type = 'Org'
  -- Batch 2 — held back from the main batch, scoped to just these numbers.
  AND u.phone_number IN (
    '8939720011', '9789102500', '9841042289'
  )
  AND NOT EXISTS (
    SELECT 1 FROM hdb_program_registration r WHERE r.program_id = 45 AND r.user_id = u.id
  );


-- =============================================================================
-- SECTION B — manual fill-in.
--
-- This is an admin-driven batch registration (not individual self-service
-- signup), so registration_for_whom = 'self' and terms_accepted = true apply
-- uniformly to the whole batch — bulk-filled below rather than decided per
-- user.
-- =============================================================================

UPDATE stg_tat3_org_participants_batch2
SET registration_for_whom = 'self',
    terms_accepted = true
WHERE registration_for_whom IS NULL
   OR terms_accepted IS NULL;

-- rm_contact — same RM contact user_id used for the main batch, resolved
-- dynamically from users.org_usr_name + phone_number (never a hardcoded
-- user_id). Aborts if the lookup doesn't match exactly one user, rather than
-- silently guessing.
DO $$
DECLARE
  v_match_count integer;
BEGIN
  SELECT COUNT(*) INTO v_match_count FROM users WHERE org_usr_name ILIKE '%other%' AND phone_number = '8939910100';
  IF v_match_count <> 1 THEN
    RAISE EXCEPTION 'Expected exactly 1 user with org_usr_name ILIKE ''%%other%%'' and phone_number = ''8939910100'', found % — resolve the RM contact user_id manually', v_match_count;
  END IF;
END $$;

UPDATE stg_tat3_org_participants_batch2
SET rm_contact = (SELECT id FROM users WHERE org_usr_name ILIKE '%other%' AND phone_number = '8939910100'),
    other_infinitheism_contact = 'Organization'
WHERE rm_contact IS NULL;

-- Check completeness before running Section C, e.g.:
--
--   SELECT user_id, full_name,
--          (registration_for_whom IS NULL)      AS needs_registration_for_whom,
--          (rm_contact IS NULL)                 AS needs_rm_contact,
--          (terms_accepted IS NULL)             AS needs_terms_accepted,
--          (gender IS NULL)        AS gender_missing_in_source,
--          (country_name IS NULL)  AS country_name_missing_in_source,
--          (city IS NULL)          AS city_missing_in_source,
--          (dob IS NULL)           AS dob_missing_in_source,
--          (email IS NULL)         AS email_missing_in_source,
--          (full_name IS NULL)     AS full_name_missing_in_source,
--          (mobile_number IS NULL) AS mobile_number_missing_in_source
--   FROM stg_tat3_org_participants_batch2
--   ORDER BY user_id;
--
-- Fill any gaps individually, e.g.:
--
--   UPDATE stg_tat3_org_participants_batch2
--   SET full_name = 'Some Name'
--   WHERE user_id = 9531;


-- =============================================================================
-- SECTION C — insert the actual registrations. Does NOT auto-commit: runs
-- inside one transaction and stops with a summary for review. Type COMMIT;
-- to make it permanent, or ROLLBACK; to discard everything, in the same
-- session.
-- =============================================================================

BEGIN;

-- ---- Parameters: edit these two values, nothing else in this section ----
CREATE TEMP TABLE script_params AS
SELECT
  45::integer   AS program_id,
  -2::bigint     AS operator_user_id;  -- stamped as created_by/updated_by/approved_by
-- ------------------------------------------------------------------------

-- Serializes concurrent runs of THIS script for this program (does not block
-- the live application's own registration flow).
DO $$
DECLARE
  v_program_id integer;
BEGIN
  SELECT program_id INTO v_program_id FROM script_params;
  PERFORM pg_advisory_xact_lock(v_program_id);
END $$;

-- Lock the program row so filled_seats can't shift under us mid-transaction.
CREATE TEMP TABLE batch_program AS
SELECT p.total_seats, p.filled_seats, p.limited_seats, p.code
FROM program_v1 p
JOIN script_params sp ON p.id = sp.program_id
FOR UPDATE OF p;

-- Every FS_BASICDETAILS field sourced from stg_tat3_org_participants_batch2 —
-- that table is the single source of truth for this batch (see Sections
-- A/B). Participants whose staging row is missing full_name/mobile_number/
-- email are EXCLUDED here (see the warning below) — those columns are NOT
-- NULL on hdb_program_registration, so there's no way to insert them without
-- genuinely-unavailable source data; register those people separately once
-- their real data is known.
CREATE TEMP TABLE batch_participants AS
SELECT
  u.id AS user_id,
  stg.registration_for_whom,
  stg.full_name,
  stg.gender,
  stg.country_name,
  stg.city,
  stg.other_city_name,
  stg.mobile_number,
  stg.email,
  stg.dob,
  stg.rm_contact,
  stg.other_infinitheism_contact,
  stg.profile_url,
  stg.terms_accepted,
  ROW_NUMBER() OVER (ORDER BY u.id) AS rn
FROM users u
JOIN stg_tat3_org_participants_batch2 stg ON stg.user_id = u.id
CROSS JOIN script_params sp
WHERE u.user_type = 'Org'
  -- Batch 2 — same numbers as Section A.
  AND u.phone_number IN (
    '8939720011', '9789102500', '9841042289'
  )
  AND NOT EXISTS (
    SELECT 1 FROM hdb_program_registration r
    WHERE r.program_id = sp.program_id AND r.user_id = u.id
  )
  AND stg.full_name IS NOT NULL
  AND stg.mobile_number IS NOT NULL
  AND stg.email IS NOT NULL;

SELECT * FROM batch_participants ORDER BY rn;

-- Warn (do not block the rest of the batch) about anyone excluded above for
-- missing full_name/mobile_number/email.
DO $$
DECLARE
  v_excluded text;
BEGIN
  SELECT string_agg(u.id::text, ', ') INTO v_excluded
  FROM users u
  JOIN stg_tat3_org_participants_batch2 stg ON stg.user_id = u.id
  CROSS JOIN script_params sp
  WHERE u.user_type = 'Org'
    AND u.phone_number IN (
      '8939720011', '9789102500', '9841042289'
    )
    AND NOT EXISTS (SELECT 1 FROM hdb_program_registration r WHERE r.program_id = sp.program_id AND r.user_id = u.id)
    AND (stg.full_name IS NULL OR stg.mobile_number IS NULL OR stg.email IS NULL);

  IF v_excluded IS NOT NULL THEN
    RAISE WARNING 'Excluding user_id(s) % from this batch — missing full_name/mobile_number/email with no source data available. Register these separately once their data is known.', v_excluded;
  END IF;
END $$;

-- Abort (RAISE EXCEPTION rolls back automatically, unlike the warning above)
-- if any eligible Org user has no staging row at all, or any REMAINING
-- staged row is missing registration_for_whom/terms_accepted — those two
-- are a uniform business-rule default (Section B) with no legitimate reason
-- to still be missing at this point.
DO $$
DECLARE
  v_missing_staging text;
  v_incomplete text;
BEGIN
  SELECT string_agg(u.id::text, ', ') INTO v_missing_staging
  FROM users u
  CROSS JOIN script_params sp
  WHERE u.user_type = 'Org'
    AND u.phone_number IN (
      '8939720011', '9789102500', '9841042289'
    )
    AND NOT EXISTS (SELECT 1 FROM hdb_program_registration r WHERE r.program_id = sp.program_id AND r.user_id = u.id)
    AND NOT EXISTS (SELECT 1 FROM stg_tat3_org_participants_batch2 stg WHERE stg.user_id = u.id);

  IF v_missing_staging IS NOT NULL THEN
    RAISE EXCEPTION 'Org user(s) % have no row in stg_tat3_org_participants_batch2 — run Section A''s populate INSERT for them first', v_missing_staging;
  END IF;

  SELECT string_agg(user_id::text, ', ') INTO v_incomplete
  FROM batch_participants
  WHERE registration_for_whom IS NULL OR terms_accepted IS NULL;

  IF v_incomplete IS NOT NULL THEN
    RAISE EXCEPTION 'Staging row(s) for user_id(s) % are missing registration_for_whom/terms_accepted — fill these in via Section B first', v_incomplete;
  END IF;
END $$;

-- Abort if there isn't enough room. Mirrors the same total_seats guard
-- incrementSeatCounts uses.
DO $$
DECLARE
  v_total_seats integer;
  v_filled_seats integer;
  v_limited_seats boolean;
  v_batch_size integer;
BEGIN
  SELECT total_seats, filled_seats, limited_seats INTO v_total_seats, v_filled_seats, v_limited_seats FROM batch_program;
  SELECT COUNT(*) INTO v_batch_size FROM batch_participants;

  IF v_batch_size = 0 THEN
    RAISE EXCEPTION 'No new Org participants to register — everyone is already registered to this program';
  END IF;

  IF v_limited_seats AND (v_filled_seats + v_batch_size) > v_total_seats THEN
    RAISE EXCEPTION 'Not enough seats: batch needs %, only % of % total available',
      v_batch_size, v_total_seats - v_filled_seats, v_total_seats;
  END IF;
END $$;

-- Base sequence number this batch continues from.
CREATE TEMP TABLE batch_seq AS
SELECT COALESCE(MAX(r.program_registration_seq_number), 0) AS base_seq
FROM hdb_program_registration r, script_params sp
WHERE r.program_id = sp.program_id;

WITH inserted AS (
  INSERT INTO hdb_program_registration (
    program_id, allocated_program_id, program_session_id, user_id, owner_user_id,
    registration_mode, registration_status, basic_details_status,
    full_name, gender, country_name, city, other_city_name,
    mobile_number, email_address, dob, rm_contact, other_infinitheism_contact,
    person_type, is_free_seat, seat_allocated, terms_accepted, invoice_confirmation,
    registration_date, program_registration_seq_number, registration_seq_number,
    activation_status, created_by, updated_by, created_at, updated_at,
    user_profile_url
  )
  SELECT
    sp.program_id, sp.program_id, NULL, bp.user_id, bp.user_id,
    bp.registration_for_whom::registration_mode_enum, 'completed', 'completed',
    bp.full_name, bp.gender::gender_enum, bp.country_name, bp.city, bp.other_city_name,
    bp.mobile_number, bp.email, bp.dob, bp.rm_contact, bp.other_infinitheism_contact,
    'adult', true, true, bp.terms_accepted, false,
    NOW(), (bs.base_seq + bp.rn), (pg.code || '-' || LPAD((bs.base_seq + bp.rn)::text, 3, '0')),
    'active', sp.operator_user_id, sp.operator_user_id, NOW(), NOW(),
    bp.profile_url
  FROM batch_participants bp, batch_seq bs, batch_program pg, script_params sp
  RETURNING id, user_id, registration_seq_number
)
SELECT * INTO TEMP TABLE batch_registrations FROM inserted;

SELECT * FROM batch_registrations ORDER BY id;

-- Resolve each FS_BASICDETAILS question's target column from answer_location
-- (no hardcoded question_ids, no hardcoded binding_key-to-field mapping).
CREATE TEMP TABLE batch_question_columns AS
SELECT pq.question_id,
       split_part(q.answer_location, '.', 1) AS target_table,
       split_part(q.answer_location, '.', 2) AS target_column
FROM hdb_program_question pq
JOIN hdb_question q ON q.id = pq.question_id
JOIN hdb_form_section fs ON fs.id = pq.form_section_id
JOIN script_params sp ON pq.program_id = sp.program_id
WHERE fs.key = 'FS_BASICDETAILS'
  AND pq.deleted_at IS NULL
  AND q.deleted_at IS NULL
  AND q.answer_location IS NOT NULL
  AND split_part(q.answer_location, '.', 1) = 'hdb_program_registration';

-- One hdb_registration_question_answer row per question whose target column
-- actually has a value on the row we just inserted. answer_value is ALWAYS
-- stored as a JSON string here — matches what the real app writes
-- (RegistrationAnswerDto.answer is @IsString(), and
-- registration.controller.ts's getQuestionResponses calls .trim() on it
-- directly, which throws on a non-string JSON value).
INSERT INTO hdb_registration_question_answer (
  registration_id, question_id, answer_value, created_by, updated_by, created_at, updated_at
)
SELECT r.id, bqc.question_id, to_jsonb(to_jsonb(r) ->> bqc.target_column),
       sp.operator_user_id, sp.operator_user_id, NOW(), NOW()
FROM hdb_program_registration r
JOIN batch_registrations br ON br.id = r.id
JOIN batch_question_columns bqc ON true
CROSS JOIN script_params sp
WHERE (to_jsonb(r) -> bqc.target_column) IS NOT NULL
  AND (to_jsonb(r) -> bqc.target_column) <> 'null'::jsonb;

-- One approval row per registration, auto-approved (payment/approval not required for these).
INSERT INTO hdb_registration_approval (
  registration_id, approval_status, approval_date, approved_by, auto_approved,
  approval_reason, created_by, updated_by, created_at, updated_at
)
SELECT br.id, 'approved', NOW(), sp.operator_user_id, true,
       'Free seat allocation - organisation participant batch registration',
       sp.operator_user_id, sp.operator_user_id, NOW(), NOW()
FROM batch_registrations br, script_params sp;

-- Seat count update (mirrors incrementSeatCounts; guard already checked above).
UPDATE program_v1
SET filled_seats = filled_seats + (SELECT COUNT(*) FROM batch_registrations),
    updated_at = NOW()
WHERE id = (SELECT program_id FROM script_params);

-- Defensive sequence sync for the id columns touched above. Not strictly
-- needed here since every insert used DEFAULT nextval() (not explicit ids),
-- so the sequences are already correct — this is a no-op confirmation, safe
-- to run.
SELECT setval(pg_get_serial_sequence('hdb_program_registration', 'id'),
              (SELECT MAX(id) FROM hdb_program_registration));
SELECT setval(pg_get_serial_sequence('hdb_registration_approval', 'id'),
              (SELECT MAX(id) FROM hdb_registration_approval));

-- SUMMARY — review before deciding COMMIT or ROLLBACK
SELECT
  (SELECT COUNT(*) FROM batch_registrations) AS registrations_inserted,
  (SELECT total_seats FROM program_v1 WHERE id = (SELECT program_id FROM script_params)) AS total_seats,
  (SELECT filled_seats FROM program_v1 WHERE id = (SELECT program_id FROM script_params)) AS filled_seats_after;

-- Transaction is OPEN. Run COMMIT; to save, or ROLLBACK; to discard.

COMMIT;

-- =============================================================================
-- SECTION D — cleanup. Run after the transaction above has been either
-- COMMIT;-ed or ROLLBACK;-ed — these are scratch tables, safe to drop either
-- way (IF EXISTS handles a fresh session where they were never created).
-- =============================================================================

-- Only drop the staging table after a real COMMIT — if you ROLLBACK;-ed to
-- fix and retry, stg_tat3_org_participants_batch2 still holds your filled-in
-- data and you don't want to lose it.
DROP TABLE stg_tat3_org_participants_batch2;
