-- =============================================================================
-- TAT_3 (program 1544) Org-participant batch registration — READ-ONLY QUERIES
--
-- Companion file: 2026-07-28-tat3-org-participants-inserts-updates.sql (all
-- writes: staging table, manual fill-in UPDATEs, and the actual registration
-- INSERT transaction).
--
-- Run these in order:
--   A) FS_BASICDETAILS questions for the program (reference only).
--   B) Org users eligible for registration vs already registered.
--   C) Staging-table completeness check. Requires the staging table to
--      already exist (run Section A of the companion file first). Re-run
--      this after every manual UPDATE until every "needs_*" column reads
--      false.
--   D) Full preview of exactly what the insert transaction would write, with
--      zero writes. Run this last, right before the real insert.
--
-- Nothing in this file writes to the database. Edit only the program_id
-- literal below.
-- =============================================================================

CREATE TEMP TABLE script_params AS
SELECT 1544::integer AS program_id;

-- =============================================================================
-- SECTION A — FS_BASICDETAILS questions for this program (question_id,
-- binding_key, target column on hdb_program_registration). Payment/invoice
-- questions (FS_PAYMENTINVOICE) are excluded — not needed for a free-seat
-- batch. The insert transaction resolves question_answer rows dynamically
-- from this same shape; shown here purely for review.
-- =============================================================================
SELECT
  pq.question_id,
  q.binding_key,
  q.label,
  q.typ                  AS question_type,
  q.answer_location,
  split_part(q.answer_location, '.', 1) AS target_table,
  split_part(q.answer_location, '.', 2) AS target_column,
  q.option_config,
  pq.registration_level,
  pq.display_order
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
ORDER BY pq.display_order NULLS LAST, pq.id;

-- =============================================================================
-- SECTION B — organisation (user_type = 'Org') users who should be
-- registered, scoped to the explicit phone-number list below, and (for
-- reference) those already registered. No role-based exclusion — this batch
-- registers everyone on the list regardless of role.
-- =============================================================================
SELECT
  u.id AS user_id,
  u.full_name,
  u.email,
  u.phone_number,
  ROW_NUMBER() OVER (ORDER BY u.id) AS batch_row_number
FROM users u
JOIN script_params sp ON true
WHERE u.user_type = 'Org'
  -- Batch scoped to only this explicit phone-number list — same list used
  -- throughout the companion writes file.
  AND u.phone_number IN (
    '9841021495', '9840020078', '7299903879', '9840250199', '9962077899',
    '9842988268', '9790768202', '9789050061', '9841679090', '9003234356',
    '9841280188', '9042159132', '9841285650', '9962894630', '9003020686',
    '9841301020', '8754402031', '9841727022', '9003249142', '9657152211',
    '9841825155', '9841819377', '8939910100', '9845191961', '9841405117'
  )
  AND NOT EXISTS (
    SELECT 1 FROM hdb_program_registration r
    WHERE r.program_id = sp.program_id AND r.user_id = u.id
  )
ORDER BY u.id;

SELECT u.id AS user_id, u.full_name, r.id AS existing_registration_id, r.registration_status
FROM users u
JOIN script_params sp ON true
JOIN hdb_program_registration r ON r.user_id = u.id AND r.program_id = sp.program_id
WHERE u.user_type = 'Org';

-- =============================================================================
-- SECTION C — staging-table completeness check. Shows which of the 13
-- FS_BASICDETAILS fields still need manual fill-in on
-- stg_tat3_org_participants (see the companion file's Section B for the
-- UPDATE template). Also flags any pre-filled column that's unexpectedly
-- empty, in case production data has gaps.
-- =============================================================================
SELECT user_id, full_name,
       (registration_for_whom IS NULL)      AS needs_registration_for_whom,
       (other_city_name IS NULL)            AS needs_other_city_name,
       (rm_contact IS NULL)                 AS needs_rm_contact,
       (other_infinitheism_contact IS NULL) AS needs_other_infinitheism_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,
       (profile_url IS NULL)   AS profile_url_missing_in_source
FROM stg_tat3_org_participants
ORDER BY user_id;

-- =============================================================================
-- SECTION D — zero-write preview of exactly what the insert transaction (in
-- the companion file) would write: same participant selection (sourced from
-- stg_tat3_org_participants), same sequence-number math, same column values,
-- same dynamic (non-hardcoded) question-answer derivation via answer_location.
-- =============================================================================

-- D.1 Program row & seat capacity
SELECT id, name, code, requires_payment, requires_approval, limited_seats,
       total_seats, filled_seats, (total_seats - filled_seats) AS seats_remaining,
       registration_level
FROM program_v1 p
JOIN script_params sp ON p.id = sp.program_id;

-- D.2 Participants who would be newly registered, with the exact staged
-- values that would be written (already-registered Org users are excluded,
-- shown separately in Section B).
-- Excludes anyone missing full_name/mobile_number/email — those are NOT
-- NULL on hdb_program_registration, so the insert transaction excludes them
-- too (with a warning); see the two diagnostic queries below.
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 stg ON stg.user_id = u.id
CROSS JOIN script_params sp
WHERE u.user_type = 'Org'
  -- Same explicit phone-number scoping as the companion writes file.
  AND u.phone_number IN (
    '9841021495', '9840020078', '7299903879', '9840250199', '9962077899',
    '9842988268', '9790768202', '9789050061', '9841679090', '9003234356',
    '9841280188', '9042159132', '9841285650', '9962894630', '9003020686',
    '9841301020', '8754402031', '9841727022', '9003249142', '9657152211',
    '9841825155', '9841819377', '8939910100', '9845191961', '9841405117'
  )
  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;

-- Org users eligible for this batch but with NO staging row at all. The
-- insert transaction in the companion file raises an exception (not just a
-- warning) if this isn't empty — treat any row here as a blocker.
SELECT u.id AS user_id, u.full_name
FROM users u
CROSS JOIN script_params sp
WHERE u.user_type = 'Org'
  AND u.phone_number IN (
    '9841021495', '9840020078', '7299903879', '9840250199', '9962077899',
    '9842988268', '9790768202', '9789050061', '9841679090', '9003234356',
    '9841280188', '9042159132', '9841285650', '9962894630', '9003020686',
    '9841301020', '8754402031', '9841727022', '9003249142', '9657152211',
    '9841825155', '9841819377', '8939910100', '9845191961', '9841405117'
  )
  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 stg WHERE stg.user_id = u.id);

-- Org users with a staging row that would be EXCLUDED from this batch for
-- missing full_name/mobile_number/email (no source data available). The
-- insert transaction proceeds without them and only prints a warning — this
-- is the query to check who that will be, before you run it.
SELECT u.id AS user_id, stg.full_name, stg.mobile_number, stg.email
FROM users u
JOIN stg_tat3_org_participants stg ON stg.user_id = u.id
CROSS JOIN script_params sp
WHERE u.user_type = 'Org'
  AND u.phone_number IN (
    '9841021495', '9840020078', '7299903879', '9840250199', '9962077899',
    '9842988268', '9790768202', '9789050061', '9841679090', '9003234356',
    '9841280188', '9042159132', '9841285650', '9962894630', '9003020686',
    '9841301020', '8754402031', '9841727022', '9003249142', '9657152211',
    '9841825155', '9841819377', '8939910100', '9845191961', '9841405117'
  )
  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);

-- D.3 Capacity check — would this batch fit?
SELECT
  p.total_seats, p.filled_seats, p.limited_seats,
  (SELECT COUNT(*) FROM batch_participants) AS batch_size,
  NOT p.limited_seats
    OR (p.filled_seats + (SELECT COUNT(*) FROM batch_participants)) <= p.total_seats
    AS fits_within_capacity
FROM program_v1 p JOIN script_params sp ON p.id = sp.program_id;

-- D.4 Sequence numbers this batch would get
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;

-- D.5 Preview of the exact hdb_program_registration row values the insert
-- transaction would write (as JSON, one object per participant).
CREATE TEMP TABLE batch_preview_rows AS
SELECT bp.user_id,
       jsonb_build_object(
         'program_id', sp.program_id,
         'allocated_program_id', sp.program_id,
         'program_session_id', NULL,
         'user_id', bp.user_id,
         'owner_user_id', bp.user_id,
         'registration_mode', bp.registration_for_whom,
         'registration_status', 'completed',
         'basic_details_status', 'completed',
         'full_name', bp.full_name,
         'gender', bp.gender,
         'country_name', bp.country_name,
         'city', bp.city,
         'other_city_name', bp.other_city_name,
         'mobile_number', bp.mobile_number,
         'email_address', bp.email,
         'dob', bp.dob,
         'rm_contact', bp.rm_contact,
         'other_infinitheism_contact', bp.other_infinitheism_contact,
         'person_type', 'adult',
         'is_free_seat', true,
         'seat_allocated', true,
         'terms_accepted', bp.terms_accepted,
         'invoice_confirmation', false,
         'program_registration_seq_number', (bs.base_seq + bp.rn),
         'registration_seq_number', (pg.code || '-' || LPAD((bs.base_seq + bp.rn)::text, 3, '0')),
         'activation_status', 'active',
         'user_profile_url', bp.profile_url
       ) AS preview_row
FROM batch_participants bp
CROSS JOIN batch_seq bs
CROSS JOIN (SELECT code FROM program_v1 p JOIN script_params sp ON p.id = sp.program_id) pg
CROSS JOIN script_params sp;

SELECT user_id, preview_row FROM batch_preview_rows ORDER BY user_id;

-- D.6 Preview of the exact hdb_registration_question_answer rows the insert
-- transaction would write — derived the SAME dynamic way (answer_location →
-- column → jsonb lookup), just against the preview JSON instead of a real
-- row. Cast to text then re-wrapped in jsonb, same as the insert transaction
-- — answer_value must always be a JSON string (see that file's Section C
-- comment) or registration.controller.ts's .trim() call throws.
SELECT bpr.user_id, bqc.question_id,
       to_jsonb(bpr.preview_row ->> bqc.target_column) AS would_be_answer_value
FROM batch_preview_rows bpr
JOIN (
  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'
) bqc ON true
WHERE (bpr.preview_row -> bqc.target_column) IS NOT NULL
  AND (bpr.preview_row -> bqc.target_column) <> 'null'::jsonb
ORDER BY bpr.user_id, bqc.question_id;

-- D.7 Totals summary
SELECT
  (SELECT COUNT(*) FROM batch_participants) AS registrations_would_be_inserted,
  (SELECT COUNT(*) FROM batch_participants) AS approval_rows_would_be_inserted,
  (SELECT COUNT(*)
     FROM batch_preview_rows bpr,
          (SELECT pq.question_id, 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 split_part(q.answer_location, '.', 1) = 'hdb_program_registration') bqc
    WHERE (bpr.preview_row -> bqc.target_column) IS NOT NULL
      AND (bpr.preview_row -> bqc.target_column) <> 'null'::jsonb
  ) AS question_answer_rows_would_be_inserted;

DROP TABLE script_params;
DROP TABLE batch_participants;
DROP TABLE batch_seq;
DROP TABLE batch_preview_rows;
