-- ============================================================
-- Migration: vw_hdb_registration_report + fn_generate_registration_report
--
-- Architecture:
--   7 domain functions (one per area) + 1 view (laterally joins them) +
--   1 filter function (returns SETOF the view)
--
-- "other" collapsing rules (matches registration.service.ts logic):
--   city            : if city = 'other'  → use other_city_name
--   rmContact       : if org_usr_name = 'other'  → use other_infinitheism_contact
--                     (org_usr_name resolved via org_usr_name → legal_full_name → full_name →
--                      first_name+last_name fallback, matching zoom-registration.repository.ts's
--                      rmDisplayNameSql RM name-resolution chain)
--   ratria location : if loc  = 'other'  → use ratria_pillar_other_location
--
-- Creation order:  domain functions → view → filter function
-- Drop order:      filter function  → view → domain functions
-- ============================================================


-- ── DOWN ──────────────────────────────────────────────────────────────────────
DROP FUNCTION IF EXISTS fn_generate_registration_report;
DROP VIEW      IF EXISTS vw_hdb_registration_report;
DROP FUNCTION  IF EXISTS fn_report_mahatria_choice(BIGINT, BIGINT, TEXT);
DROP FUNCTION  IF EXISTS fn_report_swap(BIGINT);
DROP FUNCTION  IF EXISTS fn_report_goodies(BIGINT, BIGINT);
DROP FUNCTION  IF EXISTS fn_report_travel(BIGINT);
DROP FUNCTION  IF EXISTS fn_report_invoice(BIGINT);
DROP FUNCTION  IF EXISTS fn_report_payment(BIGINT);
DROP FUNCTION  IF EXISTS fn_report_approval(BIGINT, BIGINT);


-- ── DOMAIN FUNCTIONS (UP) ────────────────────────────────────────────────────

-- 1. Approval, RM contact, recommendation, rating, program preferences
CREATE OR REPLACE FUNCTION fn_report_approval(p_reg_id BIGINT, p_rm_contact_id BIGINT)
RETURNS TABLE(
  "approvalStatus"         TEXT,
  "approvalDate"           TIMESTAMPTZ,
  "rmContact"              TEXT,
  "rmRecommendation"       TEXT,
  "isRecommended"          BOOLEAN,
  "recommendationComments" TEXT,
  "rmRating"               NUMERIC,
  "programPreferences"     TEXT,
  "rejectionReason"        TEXT,
  "approvalReason"         TEXT,
  "reviewerComments"       TEXT
)
VOLATILE LANGUAGE sql AS $$
  SELECT
    a.approval_status,
    a.approval_date,
    (SELECT COALESCE(
              NULLIF(TRIM(u.org_usr_name), ''),
              NULLIF(TRIM(u.legal_full_name), ''),
              NULLIF(TRIM(u.full_name), ''),
              NULLIF(TRIM(CONCAT_WS(' ', u.first_name, u.last_name)), '')
            )
       FROM users u WHERE u.id = p_rm_contact_id),
    (SELECT r.recommendation_key::TEXT
       FROM hdb_program_registration_recommendations r
      WHERE r.registration_id = p_reg_id
      ORDER BY r.id DESC LIMIT 1),
    (SELECT r.is_recommended
       FROM hdb_program_registration_recommendations r
      WHERE r.registration_id = p_reg_id
      ORDER BY r.id DESC LIMIT 1),
    (SELECT r.recommendation_text
       FROM hdb_program_registration_recommendations r
      WHERE r.registration_id = p_reg_id
      ORDER BY r.id DESC LIMIT 1),
    (SELECT AVG(rr.rating)
       FROM hdb_program_registration_rm_rating rr
      WHERE rr.program_registration_id = p_reg_id AND rr.deleted_at IS NULL),
    (SELECT STRING_AGG(pv.name, ', ' ORDER BY pref.priority_order)
       FROM hdb_preference pref
       JOIN program_v1 pv ON pv.id = pref.preferred_program_id
      WHERE pref.registration_id = p_reg_id AND pref.deleted_at IS NULL),
    a.rejection_reason,
    a.approval_reason,
    a.reviewer_comments
  FROM (SELECT 1) dummy
  LEFT JOIN (
    SELECT approval_status, approval_date, rejection_reason, approval_reason, reviewer_comments
      FROM hdb_registration_approval
     WHERE registration_id = p_reg_id AND deleted_at IS NULL
     ORDER BY id DESC LIMIT 1
  ) a ON true
$$;


-- 2. Latest payment detail (+ offline meta fields)
CREATE OR REPLACE FUNCTION fn_report_payment(p_reg_id BIGINT)
RETURNS TABLE(
  "paymentStatus"           TEXT,
  "paymentMode"             TEXT,
  "paymentSubTotal"         NUMERIC,
  "paymentGstAmount"        NUMERIC,
  "paymentTaxAmount"        NUMERIC,
  "paymentTds"              NUMERIC,
  "razorpayId"              TEXT,
  "razorpayPaymentId"       TEXT,
  "paymentDate"             TIMESTAMPTZ,
  "markAsReceivedDate"      TIMESTAMPTZ,
  "originalAmount"          NUMERIC,
  "offlinePaymentMode"      TEXT,
  "offlineChequeNo"         TEXT,
  "offlineChequeDate"       DATE,
  "offlineUtrNumber"        TEXT,
  "offlineBankTransferDate" DATE,
  "offlineBankName"         TEXT,
  "gstEighteen"             NUMERIC,
  "amountReceived"          NUMERIC
)
VOLATILE LANGUAGE sql AS $$
  SELECT
    p.payment_status,
    p.payment_mode,
    p.sub_total,
    p.gst_amount,
    p.tax_amount,
    p.tds,
    p.razorpay_id,
    p.offline_meta->>'razorpayPaymentId',
    p.payment_date,
    p.mark_as_received_date,
    p.original_amount,
    p.offline_meta->>'paymentMode',
    COALESCE(p.offline_meta->>'chequeNo', p.offline_meta->>'ddNumber'),
    COALESCE((p.offline_meta->>'chequeDate')::DATE, (p.offline_meta->>'ddDate')::DATE),
    COALESCE(p.offline_meta->>'utrNumber', p.offline_meta->>'referenceNumber', p.offline_meta->>'bankTransferRefNumber'),
    COALESCE((p.offline_meta->>'neftDate')::DATE, (p.offline_meta->>'bankTransferDate')::DATE),
    p.offline_meta->>'bankName',
    (COALESCE(p.original_amount, 0) * 0.18),
    p.sub_total
  FROM (
    SELECT payment_status, payment_mode, sub_total, gst_amount, tax_amount, tds,
           razorpay_id, payment_date, mark_as_received_date, original_amount, offline_meta
      FROM hdb_registration_payment_detail
     WHERE registration_id = p_reg_id AND deleted_at IS NULL
     ORDER BY id DESC LIMIT 1
  ) p
$$;


-- 3. Latest invoice detail (+ e-invoice fields, type, pan, tds applicable)
CREATE OR REPLACE FUNCTION fn_report_invoice(p_reg_id BIGINT)
RETURNS TABLE(
  "invoiceStatus"           TEXT,
  "invoiceNumber"           TEXT,
  "invoiceType"             TEXT,
  "invoiceName"             TEXT,
  "invoiceEmail"            TEXT,
  "invoiceAddress"          TEXT,
  "invoiceIssuedDate"       DATE,
  "invoiceGstNumber"        TEXT,
  "invoicePanNumber"        TEXT,
  "invoiceTanNumber"        TEXT,
  "invoicePinCode"          TEXT,
  "invoiceTdsApplicable"    BOOLEAN,
  "tdsAmount"               NUMERIC,
  "handoverDate"            TIMESTAMPTZ,
  "handoverTo"              TEXT,
  "einvoiceStatus"          TEXT,
  "einvoiceStatusFormatted" TEXT,
  "einvoiceAckNumber"       TEXT,
  "einvoiceAckDate"         TEXT,
  "einvoiceQrLink"          TEXT,
  "einvoiceRefNumber"       TEXT,
  "invoiceModifiedBy"       TEXT,
  "invoiceModifiedTime"     TIMESTAMPTZ
)
VOLATILE LANGUAGE sql AS $$
  SELECT
    i.invoice_status,
    i.invoice_sequence_number,
    i.invoice_type::TEXT,
    i.invoice_name,
    i.invoice_email,
    i.invoice_address,
    i.invoice_issued_date::DATE,
    i.gst_number,
    i.pan_number,
    i.tan_number,
    i.zip,
    i.tds_applicable,
    i.tds_amount,
    i.handover_date,
    i.handover_to,
    i.einvoice_status,
    COALESCE(i.einvoice_status_formatted, i.einvoice_formatted_status),
    i.einvoice_ack_number,
    i.einvoice_ack_date,
    i.einvoice_qr_link,
    i.einvoice_inv_ref_num,
    COALESCE(
      NULLIF(TRIM(u.legal_full_name), ''),
      NULLIF(TRIM(u.org_usr_name), ''),
      NULLIF(TRIM(u.full_name), ''),
      NULLIF(TRIM(CONCAT_WS(' ', u.first_name, u.last_name)), '')
    ),
    i.updated_at
  FROM (
    SELECT invoice_status, invoice_sequence_number, invoice_type, invoice_name, invoice_email,
           invoice_address, invoice_issued_date, gst_number, pan_number, tan_number, zip,
           tds_applicable, tds_amount, handover_date, handover_to,
           einvoice_status, einvoice_status_formatted, einvoice_formatted_status,
           einvoice_ack_number, einvoice_ack_date, einvoice_qr_link, einvoice_inv_ref_num,
           updated_by, updated_at
      FROM hdb_registration_invoice_detail
     WHERE registration_id = p_reg_id AND deleted_at IS NULL
     ORDER BY id DESC LIMIT 1
  ) i
  LEFT JOIN users u ON u.id = i.updated_by
$$;


-- 4. Latest travel info + latest travel plan (combined into one row)
CREATE OR REPLACE FUNCTION fn_report_travel(p_reg_id BIGINT)
RETURNS TABLE(
  "travelInfoStatus"        TEXT,
  "idType"                  TEXT,
  "idNumber"                TEXT,
  "idFrontPicture"          TEXT,
  "idBackPicture"           TEXT,
  "internationalIdPicture"  TEXT,
  "passportCopy"            TEXT,
  "visaCopy"                TEXT,
  "tshirtSize"              TEXT,
  "travelPlanStatus"        TEXT,
  "onwardAirline"           TEXT,
  "onwardFlightNumber"      TEXT,
  "onwardArrivalDatetime"   TIMESTAMP,
  "onwardArrivalFrom"       TEXT,
  "onwardDepartureLocation" TEXT,
  "onwardTerminal"          TEXT,
  "onwardAdditionalInfo"    TEXT,
  "onwardJourneyTicket"     TEXT,
  "returnTravelType"        TEXT,
  "returnAirline"           TEXT,
  "returnFlightNumber"      TEXT,
  "returnDepartureDatetime" TIMESTAMPTZ,
  "returnDepartureTo"       TEXT,
  "returnTerminal"          TEXT,
  "returnAdditionalInfo"    TEXT,
  "returnJourneyTicket"     TEXT,
  "pickupLocation"          TEXT,
  "pickupTime"              TEXT,
  "checkinAt"               TIMESTAMPTZ,
  "checkinTime"             TEXT,
  "checkinLocation"         TEXT
)
VOLATILE LANGUAGE sql AS $$
  SELECT
    ti.travel_info_status,
    ti.id_type,
    ti.id_number,
    ti.id_picture_url,
    ti.id_back_picture_url,
    ti.international_id_picture_url,
    ti.passport_copy_picture_url,
    ti.visa_copy_picture_url,
    ti.tshirt_size,
    tp.travel_plan_status,
    tp.airline_name,
    tp.flight_number,
    tp.arrival_datetime,
    tp.arrival_from,
    tp.departure_location,
    tp.onward_terminal,
    tp.onward_additional_info,
    tp.onward_journey_ticket_url,
    tp.return_travel_type::TEXT,
    tp.departure_airline_name,
    tp.departure_flight_number,
    tp.departure_datetime,
    tp.departure_to,
    tp.return_terminal,
    tp.return_additional_info,
    tp.return_journey_ticket_url,
    tp.pickup_location,
    tp.pickup_time,
    tp.checkin_at,
    tp.checkin_time,
    tp.checkin_location
  FROM (SELECT 1) dummy
  LEFT JOIN (
    SELECT travel_info_status, id_type, id_number, tshirt_size,
           id_picture_url, id_back_picture_url, international_id_picture_url,
           passport_copy_picture_url, visa_copy_picture_url
      FROM hdb_registration_travel_info
     WHERE registration_id = p_reg_id AND deleted_at IS NULL
     ORDER BY id DESC LIMIT 1
  ) ti ON true
  LEFT JOIN (
    SELECT travel_plan_status, airline_name, flight_number, arrival_datetime, arrival_from,
           departure_location, onward_terminal, onward_additional_info, onward_journey_ticket_url,
           return_travel_type, departure_airline_name, departure_flight_number,
           departure_datetime, departure_to, return_terminal, return_additional_info,
           return_journey_ticket_url, pickup_location, pickup_time,
           checkin_at, checkin_time, checkin_location
      FROM hdb_registration_travel_plan
     WHERE registration_id = p_reg_id AND deleted_at IS NULL
     ORDER BY id DESC LIMIT 1
  ) tp ON true
$$;


-- 5. Latest goodies record (all item fields + ratria pillar "other" collapsing)
CREATE OR REPLACE FUNCTION fn_report_goodies(p_reg_id BIGINT, p_allocated_program_id BIGINT)
RETURNS TABLE(
  "goodiesStatus"               TEXT,
  "goodiesNotebook"             BOOLEAN,
  "goodiesFlask"                BOOLEAN,
  "goodiesTshirt"               BOOLEAN,
  "goodiesTshirtSize"           TEXT,
  "goodiesJacket"               BOOLEAN,
  "goodiesJacketSize"           TEXT,
  "goodiesRatriaPillar"         BOOLEAN,
  "goodiesRatriaPillarLocation" TEXT,
  "goodiesRatriaPillarOtherLoc" TEXT
)
VOLATILE LANGUAGE sql AS $$
  SELECT
    CASE
      WHEN g.id IS NOT NULL AND p_allocated_program_id IS NOT NULL THEN 'completed'
      ELSE 'pending'
    END,
    g.notebook,
    g.flask,
    g.tshirt,
    g.tshirt_size,
    g.jacket,
    g.jacket_size,
    g.ratria_pillar_leonia,
    -- if location = 'other', collapse to the free-text other location (matches formatLocation util)
    CASE
      WHEN LOWER(TRIM(g.ratria_pillar_location)) = 'other'
      THEN COALESCE(NULLIF(g.ratria_pillar_other_location, ''), g.ratria_pillar_location)
      ELSE g.ratria_pillar_location
    END,
    g.ratria_pillar_other_location
  FROM (SELECT 1) dummy
  LEFT JOIN (
    SELECT id, notebook, flask, tshirt, tshirt_size, jacket, jacket_size,
           ratria_pillar_leonia, ratria_pillar_location, ratria_pillar_other_location
      FROM hdb_program_registration_goodies
     WHERE registration_id = p_reg_id AND deleted_at IS NULL
     ORDER BY id DESC LIMIT 1
  ) g ON true
$$;


-- 6. Latest swap record + current/requested program names
CREATE OR REPLACE FUNCTION fn_report_swap(p_reg_id BIGINT)
RETURNS TABLE(
  "swapType"              TEXT,
  "swapStatus"            TEXT,
  "swapRequirement"       TEXT,
  "swapComment"           TEXT,
  "swapCurrentProgram"    TEXT,
  "swapRequestedPrograms" TEXT
)
VOLATILE LANGUAGE sql AS $$
  SELECT
    sw.type,
    sw.status,
    sw.swap_requirement,
    sw.comment,
    (SELECT pv.name FROM program_v1 pv WHERE pv.id = sw.current_program_id AND pv.deleted_at IS NULL),
    (SELECT STRING_AGG(pv2.name, ', ' ORDER BY srp.id ASC)
       FROM hdb_swap_requested_program srp
       JOIN program_v1 pv2 ON pv2.id = srp.program_id AND pv2.deleted_at IS NULL
      WHERE srp.swap_request_id = sw.id)
  FROM (
    SELECT id, type, status, swap_requirement, comment, current_program_id
      FROM hdb_program_registration_swap
     WHERE program_registration_id = p_reg_id
     ORDER BY id DESC LIMIT 1
  ) sw
$$;


-- 7. Mahatria-choice flag
CREATE OR REPLACE FUNCTION fn_report_mahatria_choice(p_reg_id BIGINT, p_allocated_program_id BIGINT, p_registration_status TEXT)
RETURNS TABLE("isMahatriaChoice" BOOLEAN)
VOLATILE LANGUAGE sql AS $$
  SELECT
    p_allocated_program_id IS NULL
    AND p_registration_status NOT IN ('save_as_draft', 'cancelled', 'archived')
    AND NOT EXISTS (
      SELECT 1 FROM hdb_preference pref
      WHERE pref.registration_id = p_reg_id AND pref.deleted_at IS NULL
    )
    AND (
      SELECT a.approval_status FROM hdb_registration_approval a
      WHERE a.registration_id = p_reg_id AND a.deleted_at IS NULL
      ORDER BY a.id DESC LIMIT 1
    ) = 'pending'
$$;


-- ── VIEW (UP) ─────────────────────────────────────────────────────────────────
-- "other" collapsing applied in view:
--   city      → CASE WHEN city = 'other' THEN other_city_name ELSE city
--   rmContact → CASE WHEN rmContact (org_usr_name) = 'other' THEN other_infinitheism_contact ELSE rmContact
--   (ratria pillar "other" handled inside fn_report_goodies)
-- Cancellation reason formatted: INITCAP(REPLACE(reason, '_', ' '))

CREATE VIEW vw_hdb_registration_report AS
SELECT
  -- ── Filter-only identifier columns ───────────────────────────────────────
  reg.id                          AS reg_id,
  reg.program_id,
  reg.program_session_id,
  reg.allocated_program_id,
  reg.allocated_session_id,
  reg.user_id,
  reg.rm_contact                  AS rm_contact_id,
  reg.waiting_list_seq_number,
  reg.reserved_link,
  reg.dob                         AS dob_raw,
  reg.no_of_hdbs                  AS no_of_hdbs_raw,
  seekerUser.user_type::TEXT      AS seeker_user_type,

  -- ── Derived filter-helper columns ────────────────────────────────────────
  CASE
    WHEN payment."paymentStatus" IN ('online_completed',  'offline_completed')         THEN 'payment_completed'
    WHEN payment."paymentStatus" IN ('online_pending', 'offline_pending', 'failed')    THEN 'payment_pending'
    -- no payment record yet, but a seat is allocated and it is not free → still pending
    -- (mirrors registration.repository.ts paymentStatus 'payment_pending' bucket)
    WHEN payment."paymentStatus" IS NULL
     AND reg.allocated_program_id IS NOT NULL
     AND reg.is_free_seat = false                                                       THEN 'payment_pending'
    WHEN reg.is_free_seat = true                                                        THEN 'no_payment'
    ELSE NULL
  END AS payment_category,

  -- invoice buckets are payment-gated: only registrations with a completed payment are
  -- classified (mirrors registration.repository.ts invoiceStatus, which inner-joins a
  -- completed payment). pending also covers draft / missing invoice rows.
  CASE
    WHEN payment."paymentStatus" NOT IN ('online_completed', 'offline_completed')
      OR payment."paymentStatus" IS NULL                                                THEN NULL
    WHEN invoice."invoiceStatus" = 'invoice_completed'                                  THEN 'invoice_completed'
    WHEN invoice."invoiceStatus" IN ('invoice_pending', 'draft')
      OR invoice."invoiceStatus" IS NULL                                                THEN 'invoice_pending'
    ELSE NULL
  END AS invoice_category,

  -- travel: completed iff both info + plan completed; else pending only when a program is
  -- allocated (mirrors registration.repository.ts travelStatus); otherwise NULL
  CASE
    WHEN travel."travelInfoStatus" = 'completed'
     AND travel."travelPlanStatus" = 'completed'                                       THEN 'travel_completed'
    WHEN reg.allocated_program_id IS NOT NULL                                           THEN 'travel_pending'
    ELSE NULL
  END AS travel_overall_status,

  -- goodies: only approved + allocated registrations have a goodies status; record
  -- present → completed, absent → pending (mirrors registration.repository.ts goodiesStatus)
  CASE
    WHEN reg.allocated_program_id IS NOT NULL AND approval."approvalStatus" = 'approved'
     AND goodies."goodiesStatus" = 'completed'                                          THEN 'goodies_completed'
    WHEN reg.allocated_program_id IS NOT NULL AND approval."approvalStatus" = 'approved' THEN 'goodies_pending'
    ELSE NULL
  END AS goodies_overall_status,

  -- ── Registration ──────────────────────────────────────────────────────────
  reg.full_name                        AS "fullName",
  reg.email_address                    AS "email",
  reg.mobile_number                    AS "phone",
  reg.alternate_phone_number           AS "alternatePhoneNumber",
  reg.phone_number_type                AS "phoneNumberType",
  reg.phone_number_relation            AS "phoneNumberRelation",
  reg.gender::TEXT                     AS gender,
  reg.dob,
  -- city: if city = 'other' use other_city_name (matches service logic)
  CASE
    WHEN LOWER(TRIM(reg.city)) = 'other' THEN COALESCE(reg.other_city_name, reg.city)
    ELSE reg.city
  END                                  AS city,
  reg.other_city_name                  AS "otherCityName",
  reg.country_name                     AS "country",
  reg.person_type::TEXT                AS "personType",
  reg.registration_seq_number          AS "registrationSeqNumber",
  reg.wait_list_registration_seq_number AS "waitListRegistrationSeqNumber",
  reg.registration_date                AS "registrationDate",
  reg.created_at                       AS "createdAt",
  reg.registration_status::TEXT        AS "registrationStatus",
  reg.registration_mode::TEXT          AS "registrationMode",
  reg.seat_allocated                   AS "seatAllocated",
  reg.is_free_seat                     AS "isFreeSeat",
  COALESCE(allocProg.name, program.name) AS "allocatedOrProgram",
  program.name                        AS "programName",
  -- Program name with period (matches formatProgrammeNameWithPeriod: name (start to end), dates in IST)
  CASE
    WHEN COALESCE(allocProg.name, program.name) IS NULL THEN NULL
    WHEN COALESCE(allocProg.starts_at, program.starts_at) IS NOT NULL
     AND COALESCE(allocProg.ends_at,   program.ends_at)   IS NOT NULL
      THEN COALESCE(allocProg.name, program.name)
        || ' (' || TO_CHAR(COALESCE(allocProg.starts_at, program.starts_at) AT TIME ZONE 'Asia/Kolkata', 'DD-Mon-YYYY')
        || ' to ' || TO_CHAR(COALESCE(allocProg.ends_at, program.ends_at) AT TIME ZONE 'Asia/Kolkata', 'DD-Mon-YYYY') || ')'
    WHEN COALESCE(allocProg.starts_at, program.starts_at) IS NOT NULL
      THEN COALESCE(allocProg.name, program.name)
        || ' (From ' || TO_CHAR(COALESCE(allocProg.starts_at, program.starts_at) AT TIME ZONE 'Asia/Kolkata', 'DD-Mon-YYYY') || ')'
    ELSE COALESCE(allocProg.name, program.name)
  END                                  AS "programNameWithPeriod",
  reg.no_of_hdbs                       AS "numberOfHdbs",
  reg.last_hdb_attended                AS "lastHdbAttended",
  reg.hdb_association_since            AS "hdbAssociationSince",
  reg.notes                            AS "notes",
  reg.song_preferences                 AS "songPreferences",
  seekerUser.user_type::TEXT           AS "userType",

  -- ── Seeker ────────────────────────────────────────────────────────────────
  COALESCE(seekerDefaulter.is_defaulter, false) AS "isDefaulter",

  -- ── Parental Form ─────────────────────────────────────────────────────────
  reg.parental_form_status::TEXT       AS "parentalFormStatus",
  reg.parental_form_uploaded_at        AS "parentalFormUploadedAt",
  reg.parental_form_admin_notes        AS "parentalFormAdminNotes",

  -- ── Room ──────────────────────────────────────────────────────────────────
  reg.preferred_room_mate              AS "preferredRoomMate",
  EXISTS (
    SELECT 1 FROM room_allocation ra
    WHERE ra.registration_id = reg.id AND ra.deleted_at IS NULL
  )                                    AS "roomAllocated",

  -- ── Waitlist ──────────────────────────────────────────────────────────────
  reg.waiting_list_seq_number          AS "waitingListSeqNumber",
  reg.reserved_link                    AS "reservedLink",
  reg.sub_status                       AS "subStatus",

  -- ── Cancellation ─────────────────────────────────────────────────────────
  reg.cancellation_date                AS "cancellationDate",
  cancelledByUser.full_name            AS "cancelledBy",
  -- format: replace underscores + capitalise first letter (matches capitalizeFirstLetter util)
  INITCAP(REPLACE(reg.cancellation_reason, '_', ' ')) AS "cancellationReason",
  reg.cancellation_comments            AS "cancellationComments",

  -- ── Proforma ──────────────────────────────────────────────────────────────
  reg.proforma_invoice_seq_number      AS "proformaSeqNumber",
  reg.pro_forma_invoice_name           AS "proformaInvoiceName",
  reg.pro_forma_invoice_address        AS "proformaInvoiceAddress",
  reg.pro_forma_zip                    AS "proformaZip",
  reg.pro_forma_is_gst_registered      AS "proformaIsGstRegistered",
  reg.pro_forma_gst_number             AS "proformaGstNumber",

  -- ── Approval + RM (from fn_report_approval) ───────────────────────────────
  approval."approvalStatus",
  -- Blessed: Yes when approved (matches approvalStatus === 'approved' ? 'Yes' : 'No')
  CASE WHEN approval."approvalStatus" = 'approved' THEN 'Yes' ELSE 'No' END AS "blessed",
  -- HDB/MSD Offered: allocated program name with period (matches formatProgrammeNameWithPeriod('', allocatedProgram))
  CASE
    WHEN allocProg.name IS NULL THEN NULL
    WHEN allocProg.starts_at IS NOT NULL AND allocProg.ends_at IS NOT NULL
      THEN allocProg.name
        || ' (' || TO_CHAR(allocProg.starts_at AT TIME ZONE 'Asia/Kolkata', 'DD-Mon-YYYY')
        || ' to ' || TO_CHAR(allocProg.ends_at AT TIME ZONE 'Asia/Kolkata', 'DD-Mon-YYYY') || ')'
    WHEN allocProg.starts_at IS NOT NULL
      THEN allocProg.name || ' (From ' || TO_CHAR(allocProg.starts_at AT TIME ZONE 'Asia/Kolkata', 'DD-Mon-YYYY') || ')'
    ELSE allocProg.name
  END                                  AS "offeredWithPeriod",
  approval."approvalDate",
  -- rmContact: if org_usr_name = 'other' use other_infinitheism_contact (matches service logic)
  CASE
    WHEN LOWER(TRIM(approval."rmContact")) = 'other'
    THEN COALESCE(NULLIF(reg.other_infinitheism_contact, ''), approval."rmContact")
    ELSE approval."rmContact"
  END                                  AS "rmContact",
  reg.rm_review                        AS "rmReview",
  approval."rmRecommendation",
  approval."isRecommended",
  approval."recommendationComments",
  approval."rmRating",
  approval."programPreferences",
  allocProg.name                       AS "allocatedProgramName",
  approval."rejectionReason",
  approval."approvalReason",
  approval."reviewerComments",

  -- ── Payment (from fn_report_payment) ──────────────────────────────────────
  payment."paymentStatus",
  payment."paymentMode",
  COALESCE(NULLIF(TRIM(payment."offlinePaymentMode"), ''), payment."paymentMode") AS "paymentModeDisplay",
  payment."paymentSubTotal",
  payment."paymentGstAmount",
  payment."paymentTaxAmount",
  payment."paymentTds",
  payment."razorpayId",
  payment."razorpayPaymentId",
  payment."paymentDate",
  payment."markAsReceivedDate",
  payment."originalAmount",
  payment."offlinePaymentMode",
  payment."offlineChequeNo",
  payment."offlineChequeDate",
  payment."offlineUtrNumber",
  payment."offlineBankTransferDate",
  payment."offlineBankName",
  payment."gstEighteen",
  payment."amountReceived",

  -- ── Invoice (from fn_report_invoice) ──────────────────────────────────────
  invoice."invoiceStatus",
  invoice."invoiceNumber",
  invoice."invoiceType",
  invoice."invoiceName",
  invoice."invoiceEmail",
  invoice."invoiceAddress",
  CASE
    WHEN payment."paymentMode" = 'offline' THEN payment."markAsReceivedDate"
    ELSE payment."paymentDate"
  END AS "invoiceIssuedDate",
  invoice."invoiceGstNumber",
  -- GSTIN Status: Yes when a GSTIN is present (matches gstNumber ? 'Yes' : 'No')
  CASE WHEN COALESCE(NULLIF(TRIM(invoice."invoiceGstNumber"), ''), NULL) IS NOT NULL THEN 'Yes' ELSE 'No' END AS "gstinStatus",
  invoice."invoicePanNumber",
  invoice."invoiceTanNumber",
  invoice."invoicePinCode",
  invoice."invoiceTdsApplicable",
  invoice."tdsAmount",
  invoice."handoverDate",
  invoice."handoverTo",
  invoice."einvoiceStatus",
  invoice."einvoiceStatusFormatted",
  invoice."einvoiceAckNumber",
  invoice."einvoiceAckDate",
  invoice."einvoiceQrLink",
  invoice."einvoiceRefNumber",
  invoice."invoiceModifiedBy",
  invoice."invoiceModifiedTime",

  -- ── Travel (from fn_report_travel) ────────────────────────────────────────
  travel."travelInfoStatus",
  travel."idType",
  travel."idNumber",
  travel."idFrontPicture",
  travel."idBackPicture",
  travel."internationalIdPicture",
  travel."passportCopy",
  travel."visaCopy",
  travel."tshirtSize",
  travel."travelPlanStatus",
  travel."onwardAirline",
  travel."onwardFlightNumber",
  travel."onwardArrivalDatetime",
  travel."onwardArrivalFrom",
  travel."onwardDepartureLocation",
  travel."onwardTerminal",
  travel."onwardAdditionalInfo",
  travel."onwardJourneyTicket",
  travel."returnTravelType",
  travel."returnAirline",
  travel."returnFlightNumber",
  travel."returnDepartureDatetime",
  travel."returnDepartureTo",
  travel."returnTerminal",
  travel."returnAdditionalInfo",
  travel."returnJourneyTicket",
  travel."pickupLocation",
  travel."pickupTime",
  travel."checkinAt",
  travel."checkinTime",
  travel."checkinLocation",

  -- ── Goodies (from fn_report_goodies) ──────────────────────────────────────
  goodies."goodiesStatus",
  goodies."goodiesNotebook",
  goodies."goodiesFlask",
  goodies."goodiesTshirt",
  goodies."goodiesTshirtSize",
  goodies."goodiesJacket",
  goodies."goodiesJacketSize",
  goodies."goodiesRatriaPillar",
  goodies."goodiesRatriaPillarLocation",
  goodies."goodiesRatriaPillarOtherLoc",

  -- ── Swap (from fn_report_swap) ─────────────────────────────────────────────
  swap."swapType",
  swap."swapStatus",
  swap."swapRequirement",
  swap."swapComment",
  swap."swapCurrentProgram",
  swap."swapRequestedPrograms",

  -- ── Mahatria Choice ───────────────────────────────────────────────────────
  mahatria_choice."isMahatriaChoice"

FROM hdb_program_registration reg

LEFT JOIN program_v1 program         ON program.id        = reg.program_id           AND program.deleted_at    IS NULL
LEFT JOIN program_v1 allocProg       ON allocProg.id      = reg.allocated_program_id AND allocProg.deleted_at  IS NULL
LEFT JOIN users      seekerUser      ON seekerUser.id     = reg.user_id
LEFT JOIN users      cancelledByUser ON cancelledByUser.id = reg.cancelled_by

LEFT JOIN LATERAL (
  SELECT is_defaulter FROM seeker_defaulter
  WHERE registration_id = reg.id AND deleted_at IS NULL
  ORDER BY id DESC LIMIT 1
) seekerDefaulter ON true

LEFT JOIN LATERAL fn_report_approval(reg.id, reg.rm_contact)               approval        ON true
LEFT JOIN LATERAL fn_report_payment(reg.id)                                payment         ON true
LEFT JOIN LATERAL fn_report_invoice(reg.id)                                invoice         ON true
LEFT JOIN LATERAL fn_report_travel(reg.id)                                 travel          ON true
LEFT JOIN LATERAL fn_report_goodies(reg.id, reg.allocated_program_id)      goodies         ON true
LEFT JOIN LATERAL fn_report_swap(reg.id)                                   swap            ON true
LEFT JOIN LATERAL fn_report_mahatria_choice(reg.id, reg.allocated_program_id, reg.registration_status::TEXT) mahatria_choice ON true

WHERE reg.deleted_at IS NULL;


-- ── FILTER FUNCTION (UP) ──────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION fn_generate_registration_report(p_filters JSONB DEFAULT '{}')
RETURNS SETOF vw_hdb_registration_report
LANGUAGE plpgsql AS $$
DECLARE
  p_program_id            INT     := (p_filters->>'programId')::INT;
  p_program_session_id    INT     := (p_filters->>'programSessionId')::INT;
  p_organisation          TEXT    := p_filters->>'organisation';
  p_defaulter_status      TEXT    := p_filters->>'defaulterStatus';
  p_free_seat             TEXT    := p_filters->>'freeSeat';
  p_waitlist_category     TEXT    := p_filters->>'waitlistCategory';
  p_seat_released         BOOLEAN := (p_filters->>'seatReleased')::BOOLEAN;
  p_allocated_program_id  INT     := (p_filters->>'allocatedProgramId')::INT;
  p_allocated_session_id  INT     := (p_filters->>'allocatedSessionId')::INT;
  p_preferred_program_id  INT     := (p_filters->>'preferredProgramId')::INT;
  p_preferred_session_id  INT     := (p_filters->>'preferredSessionId')::INT;
  p_hdb_min               INT     := (p_filters->>'hdbMin')::INT;
  p_hdb_max               INT     := (p_filters->>'hdbMax')::INT;
  p_kpi_category          TEXT    := p_filters->>'kpiCategory';
  p_kpi_filter            TEXT    := p_filters->>'kpiFilter';
  p_created_from          DATE    := (p_filters->>'createdFrom')::DATE;
  p_created_to            DATE    := (p_filters->>'createdTo')::DATE;
  p_rm_rating_min         NUMERIC := (p_filters->>'rmRating')::NUMERIC;
  p_registration_status   TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'registrationStatus') v), '{}');
  p_approval_status       TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'approvalStatus')      v), '{}');
  p_gender                TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'gender')              v), '{}');
  p_registration_mode     TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'registrationMode')     v), '{}');
  p_payment_status        TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'paymentStatus')       v), '{}');
  p_payment_mode          TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'paymentMode')         v), '{}');
  p_invoice_status        TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'invoiceStatus')       v), '{}');
  p_travel_status         TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'travelStatus')        v), '{}');
  p_travel_plan           TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'travelPlan')          v), '{}');
  p_age_ranges            TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'age')                 v), '{}');
  p_experience_tags       TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'experienceTags')      v), '{}');
  p_location              TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'location')            v), '{}');
  p_swap_types            TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'swapRequests')        v), '{}');
  p_goodies_status        TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'goodiesStatus')       v), '{}');
  p_recommendation        TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'recommendation')      v), '{}');
  p_rm_contact_ids        BIGINT[] := NULLIF(ARRAY(SELECT v::BIGINT FROM jsonb_array_elements_text(p_filters->'rmContact')   v), '{}');
  p_preferred_program_ids INT[]   := NULLIF(ARRAY(SELECT v::INT   FROM jsonb_array_elements_text(p_filters->'preferredPrograms') v), '{}');
  p_swap_preferred_program_id        INT := (p_filters->>'swapPreferredProgramId')::INT;
  p_swap_demand_preferred_program_id INT := (p_filters->>'swapDemandPreferredProgramId')::INT;
  p_pending_program_id               INT := (p_filters->>'pendingProgramId')::INT;
  p_mahatria_choice               BOOLEAN := (p_filters->>'mahatriaChoice')::BOOLEAN;
  p_waitlist_status               TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'waitlistStatus') v), '{}');
  p_room_allocation_status        TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'roomAllocationStatus') v), '{}');
  -- KPI-derived granular conditions (mirror registration.repository.ts findRegistrations)
  p_raw_payment_status            TEXT[]  := NULL;   -- match raw payment_status (granular)
  p_include_null_payment          BOOLEAN := false;  -- also include regs with no payment record
  p_exclude_cancelled             BOOLEAN := false;  -- exclude cancelled (payment/seats/invoice buckets)
  p_require_payment_completed     BOOLEAN := false;  -- invoice KPIs require a completed payment
  p_exclude_registration_status   TEXT[]  := NULL;   -- statuses to exclude (e.g. firstPagePending)
  -- KPI-derived payment/invoice buckets, kept SEPARATE from the user's paymentStatus / invoiceStatus
  -- side filters (p_payment_status / p_invoice_status) so both AND together instead of the KPI
  -- clobbering the side filter. Mirrors kpiPaymentStatus / kpiInvoiceStatus in registration.repository.ts.
  p_kpi_payment_category          TEXT[]  := NULL;
  p_kpi_invoice_category          TEXT[]  := NULL;
  p_kpi_payment_mode              TEXT[]  := NULL;   -- KPI-forced payment mode, kept SEPARATE from the user's paymentMode side filter (p_payment_mode) so both AND together
  p_require_allocated_program     BOOLEAN := false;  -- blessedSeats/blessedPayment/blessedInvoice require allocated_program_id IS NOT NULL (mirrors their repository methods' outer WHERE)
BEGIN
  IF p_kpi_category = 'seats' AND p_kpi_filter = 'onlineCompleted' THEN
    -- payment_status 'online_completed' already implies online mode; don't force paymentMode so
    -- this matches getSeatsMetrics (counts by payment_status only) and lets the user's Payment mode
    -- side filter still narrow results.
    p_raw_payment_status := ARRAY['online_completed'];
    p_exclude_cancelled  := true;
  ELSIF p_kpi_category = 'seats' AND p_kpi_filter = 'offlineCompleted' THEN
    p_raw_payment_status := ARRAY['offline_completed'];
    p_exclude_cancelled  := true;
  ELSIF p_kpi_category = 'seats' AND p_kpi_filter = 'onlinePending' THEN
    -- online pending = online_pending or failed (failed kept in pending). 'failed' is mode-ambiguous
    -- so getSeatsMetrics.onlinePending counts payment_mode='online' explicitly; keep it here to match.
    p_raw_payment_status := ARRAY['online_pending', 'failed'];
    p_kpi_payment_mode   := ARRAY['online'];
    p_exclude_cancelled  := true;
  ELSIF p_kpi_category = 'seats' AND p_kpi_filter = 'offlinePending' THEN
    p_raw_payment_status := ARRAY['offline_pending'];
    p_exclude_cancelled  := true;
  ELSIF p_kpi_category = 'seats' AND p_kpi_filter = 'organisation' THEN
    p_organisation := 'yes';
  ELSIF p_kpi_category IN ('seats', 'waitlisted') AND p_kpi_filter = 'waitingList' THEN
    p_registration_status := ARRAY['waitlisted'];
  ELSIF p_kpi_category = 'seats' AND p_kpi_filter = 'cancelled' THEN
    p_registration_status := ARRAY['cancelled'];
  ELSIF p_kpi_category = 'seats' AND p_kpi_filter = 'firstPagePending' THEN
    -- still-pending registration that never initiated payment (no payment record); non-free.
    -- Only registration_status = 'pending' qualifies; regs with a payment id, or past 'pending'
    -- (completed, etc.), are excluded. Empty raw status + include_null matches only rows with no
    -- payment record.
    p_raw_payment_status          := ARRAY[]::text[];
    p_include_null_payment        := true;
    p_free_seat                   := 'no';
    p_registration_status         := ARRAY['pending'];
  ELSIF p_kpi_category = 'payment' AND p_kpi_filter = 'all' THEN
    p_kpi_payment_category := ARRAY['payment_completed', 'payment_pending'];
    p_exclude_cancelled    := true;
  ELSIF p_kpi_category = 'payment' AND p_kpi_filter = 'paymentComplete' THEN
    p_kpi_payment_category := ARRAY['payment_completed'];
    p_exclude_cancelled    := true;
  ELSIF p_kpi_category = 'payment' AND p_kpi_filter = 'paymentPending' THEN
    -- Match raw statuses, NOT the payment_category bucket: that bucket also folds in
    -- no-payment-record allocated non-free regs (payment_category CASE null branch), which the
    -- tile count (getPaymentsMetrics.paymentPending = payment_status IN (online_pending,
    -- offline_pending, failed)) and the list drilldown exclude — those are firstPagePending.
    -- Using raw statuses keeps the report row set equal to the KPI count.
    p_raw_payment_status := ARRAY['online_pending', 'offline_pending', 'failed'];
    p_exclude_cancelled  := true;
  ELSIF p_kpi_category = 'payment' AND p_kpi_filter = 'noPayment' THEN
    p_free_seat         := 'yes';
    p_exclude_cancelled := true;
  ELSIF p_kpi_category = 'invoice' AND p_kpi_filter = 'all' THEN
    p_kpi_invoice_category      := ARRAY['invoice_completed', 'invoice_pending'];
    p_require_payment_completed := true;
    p_exclude_cancelled         := true;
  ELSIF p_kpi_category = 'invoice' AND p_kpi_filter = 'invoiceComplete' THEN
    p_kpi_invoice_category      := ARRAY['invoice_completed'];
    p_require_payment_completed := true;
    p_exclude_cancelled         := true;
  ELSIF p_kpi_category = 'invoice' AND p_kpi_filter = 'invoicePending' THEN
    p_kpi_invoice_category      := ARRAY['invoice_pending'];
    p_require_payment_completed := true;
    p_exclude_cancelled         := true;
  ELSIF p_kpi_category = 'travelAndGoodies' AND p_kpi_filter = 'travelComplete' THEN
    p_travel_status := ARRAY['travel_completed'];
  ELSIF p_kpi_category = 'travelAndGoodies' AND p_kpi_filter = 'travelPending' THEN
    p_travel_status := ARRAY['travel_pending'];
  ELSIF p_kpi_category = 'travelAndGoodies' AND p_kpi_filter = 'goodiesComplete' THEN
    p_goodies_status := ARRAY['goodies_completed'];
  ELSIF p_kpi_category = 'travelAndGoodies' AND p_kpi_filter = 'goodiesPending' THEN
    p_goodies_status := ARRAY['goodies_pending'];
  ELSIF p_kpi_category = 'roomAllocation' AND p_kpi_filter = 'roomAllocationComplete' THEN
    p_room_allocation_status := ARRAY['room_allocation_completed'];
  ELSIF p_kpi_category = 'roomAllocation' AND p_kpi_filter = 'roomAllocationPending' THEN
    p_room_allocation_status := ARRAY['room_allocation_pending'];
  ELSIF p_kpi_category = 'waitlisted' AND p_kpi_filter = 'waitlisted_before_seq' THEN
    p_registration_status := ARRAY['waitlisted'];
    p_waitlist_category   := 'before';
  ELSIF p_kpi_category = 'waitlisted' AND p_kpi_filter = 'waitlisted_with_seq' THEN
    p_registration_status := ARRAY['waitlisted'];
    p_waitlist_category   := 'after';
  ELSIF p_kpi_category = 'waitlisted' AND p_kpi_filter = 'waitlisted_seat_released' THEN
    p_registration_status := ARRAY['waitlisted'];
    p_seat_released       := true;
  ELSIF p_kpi_category = 'waitlisted' AND p_kpi_filter = 'waitlisted_seat_pending' THEN
    p_registration_status := ARRAY['waitlisted'];
    p_seat_released       := false;
    p_waitlist_category   := 'after';
  ELSIF p_kpi_category = 'waitlisted' THEN
    p_registration_status := ARRAY['waitlisted'];
  ELSIF p_kpi_category = 'swapRequests' THEN
    p_swap_types := ARRAY['wants_swap'];
  ELSIF p_kpi_category = 'swapDemand' THEN
    p_approval_status := ARRAY['on_hold'];
  ELSIF p_kpi_category = 'regPending' THEN
    p_approval_status := ARRAY['on_hold', 'rejected'];
  ELSIF p_kpi_category IN ('registrations', 'defaulter') THEN
    IF    p_kpi_filter = 'approvedSeekers'   THEN p_approval_status     := ARRAY['approved'];
    ELSIF p_kpi_filter = 'newSeekersPending' THEN p_approval_status     := ARRAY['pending'];
    ELSIF p_kpi_filter = 'rejected'          THEN p_approval_status     := ARRAY['rejected'];
    ELSIF p_kpi_filter = 'onHold'            THEN p_approval_status     := ARRAY['on_hold'];
    ELSIF p_kpi_filter = 'regPending'        THEN p_approval_status     := ARRAY['on_hold', 'rejected'];
    ELSIF p_kpi_filter = 'cancelled'         THEN p_registration_status := ARRAY['cancelled'];
    END IF;
  ELSIF p_kpi_category IN ('blessed', 'programs') THEN
    p_approval_status := ARRAY['approved'];
    IF    p_kpi_filter = 'paymentPending'   THEN p_payment_status      := ARRAY['payment_pending'];
    ELSIF p_kpi_filter = 'paymentComplete'  THEN p_payment_status      := ARRAY['payment_completed'];
    ELSIF p_kpi_filter = 'invoicePending'   THEN p_invoice_status      := ARRAY['invoice_pending'];
    ELSIF p_kpi_filter = 'invoiceComplete'  THEN p_invoice_status      := ARRAY['invoice_completed'];
    ELSIF p_kpi_filter = 'travelPending'    THEN p_travel_status       := ARRAY['travel_pending'];
    ELSIF p_kpi_filter = 'travelComplete'   THEN p_travel_status       := ARRAY['travel_completed'];
    ELSIF p_kpi_filter = 'swapRequests'     THEN p_swap_types          := ARRAY['wants_swap'];
    ELSIF p_kpi_filter = 'cancelled'        THEN p_registration_status := ARRAY['cancelled'];
    END IF;
  -- blessedSeats/blessedPayment/blessedInvoice: PAP's "Offered" tab tiles (see
  -- registration.service.ts getEntrainmentCategoryCount + registration.repository.ts
  -- getBlessedSeatsMetrics/getBlessedPaymentsMetrics/getBlessedInvoicesMetrics). Separate
  -- from 'blessed' above because PAP's Offered tab uses its own kpiCategory per option
  -- (see filter-hierarchy.config.ts FILTER_HIERARCHY_CONFIG_PAP) rather than a single
  -- 'blessed' category with all filters together.
  ELSIF p_kpi_category = 'blessedSeats' THEN
    -- allocated_program_id IS NOT NULL applies to ALL five sub-metrics (outer WHERE in
    -- getBlessedSeatsMetrics); registration_status != cancelled is scoped to only the four
    -- payment-status CASEs there — 'organisation' (allOrgCount) does NOT exclude cancelled.
    p_approval_status           := ARRAY['approved'];
    p_require_allocated_program := true;
    IF p_kpi_filter = 'onlineCompleted' THEN
      p_raw_payment_status := ARRAY['online_completed'];
      p_free_seat          := 'no';
      p_exclude_cancelled  := true;
    ELSIF p_kpi_filter = 'offlineCompleted' THEN
      p_raw_payment_status := ARRAY['offline_completed'];
      p_free_seat          := 'no';
      p_exclude_cancelled  := true;
    ELSIF p_kpi_filter = 'onlinePending' THEN
      -- mirrors getBlessedSeatsMetrics.onlinePending, which also pins payment_mode = 'online'
      p_raw_payment_status := ARRAY['online_pending', 'failed'];
      p_kpi_payment_mode   := ARRAY['online'];
      p_free_seat          := 'no';
      p_exclude_cancelled  := true;
    ELSIF p_kpi_filter = 'offlinePending' THEN
      p_raw_payment_status := ARRAY['offline_pending'];
      p_free_seat          := 'no';
      p_exclude_cancelled  := true;
    ELSIF p_kpi_filter = 'organisation' THEN
      p_organisation := 'yes';
    END IF;
  ELSIF p_kpi_category = 'blessedPayment' THEN
    p_approval_status           := ARRAY['approved'];
    p_require_allocated_program := true;
    IF    p_kpi_filter = 'paymentPending'  THEN p_kpi_payment_category := ARRAY['payment_pending'];
    ELSIF p_kpi_filter = 'paymentComplete' THEN p_kpi_payment_category := ARRAY['payment_completed'];
    ELSIF p_kpi_filter = 'noPayment'       THEN p_kpi_payment_category := ARRAY['no_payment'];
    END IF;
  ELSIF p_kpi_category = 'blessedInvoice' THEN
    -- mirrors getBlessedInvoicesMetrics, which gates on a completed payment rather than
    -- approvalStatus, so no p_approval_status forced here.
    p_require_allocated_program := true;
    IF    p_kpi_filter = 'invoicePending'  THEN p_kpi_invoice_category := ARRAY['invoice_pending'];
    ELSIF p_kpi_filter = 'invoiceComplete' THEN p_kpi_invoice_category := ARRAY['invoice_completed'];
    END IF;
  END IF;

  RETURN QUERY
  SELECT * FROM vw_hdb_registration_report v
  WHERE
        (p_program_id           IS NULL OR v.program_id           = p_program_id)
    AND (p_program_session_id   IS NULL OR v.program_session_id   = p_program_session_id)
    AND (
          (p_registration_status IS NULL     AND v."registrationStatus" NOT IN ('draft', 'save_as_draft'))
       OR (p_registration_status IS NOT NULL AND v."registrationStatus"  = ANY(p_registration_status))
    )
    AND (p_approval_status      IS NULL OR v."approvalStatus"      = ANY(p_approval_status))
    AND (p_gender               IS NULL OR v.gender               = ANY(p_gender))
    AND (p_registration_mode    IS NULL OR v."registrationMode"    = ANY(p_registration_mode))
    AND (p_payment_mode         IS NULL OR v."paymentMode"         = ANY(p_payment_mode))
    AND (p_kpi_payment_mode     IS NULL OR v."paymentMode"         = ANY(p_kpi_payment_mode))
    AND (NOT p_require_allocated_program OR v.allocated_program_id IS NOT NULL)
    -- travelPlan filter sends 'travel_completed'/'travel_pending'; map to the raw plan
    -- status value (mirrors registration.repository.ts travelPlan switch)
    AND (p_travel_plan IS NULL
      OR ('travel_completed' = ANY(p_travel_plan) AND v."travelPlanStatus" = 'completed')
      OR ('travel_pending'   = ANY(p_travel_plan) AND v."travelPlanStatus" = 'pending'))
    AND (p_location             IS NULL OR v.city                  = ANY(p_location))
    AND (p_seat_released        IS NULL OR v.reserved_link         = p_seat_released)
    AND (p_allocated_program_id IS NULL OR v.allocated_program_id  = p_allocated_program_id)
    AND (p_allocated_session_id IS NULL OR v.allocated_session_id  = p_allocated_session_id)
    AND (p_created_from IS NULL OR v."createdAt"::DATE >= p_created_from)
    AND (p_created_to   IS NULL OR v."createdAt"::DATE <= p_created_to)
    AND (p_goodies_status       IS NULL OR v.goodies_overall_status = ANY(p_goodies_status))
    AND (p_rm_contact_ids       IS NULL OR v.rm_contact_id         = ANY(p_rm_contact_ids))
    AND (p_rm_rating_min        IS NULL OR v."rmRating"            >= p_rm_rating_min)
    -- 'failed' is a distinct side-filter value (payment_category buckets it under payment_pending)
    AND (p_payment_status IS NULL
      OR v.payment_category = ANY(p_payment_status)
      OR ('failed' = ANY(p_payment_status) AND v."paymentStatus" = 'failed'))
    AND (p_invoice_status IS NULL OR v.invoice_category      = ANY(p_invoice_status))
    -- KPI payment/invoice buckets applied as a SEPARATE AND so a user paymentStatus / invoiceStatus
    -- side filter still narrows the KPI result instead of being overwritten by it.
    AND (p_kpi_payment_category IS NULL OR v.payment_category = ANY(p_kpi_payment_category))
    AND (p_kpi_invoice_category IS NULL OR v.invoice_category = ANY(p_kpi_invoice_category))
    AND (p_travel_status  IS NULL OR v.travel_overall_status = ANY(p_travel_status))
    -- KPI-derived granular conditions (mirror registration.repository.ts findRegistrations)
    AND (p_raw_payment_status IS NULL
      OR v."paymentStatus" = ANY(p_raw_payment_status)
      OR (p_include_null_payment AND v."paymentStatus" IS NULL))
    -- seats/payment/invoice KPIs exclude cancelled AND archived (these surface only under
    -- All + Cancelled), mirroring registration.repository.ts Not(In([..,ARCHIVED,CANCELLED]))
    AND (NOT p_exclude_cancelled OR v."registrationStatus" NOT IN ('cancelled', 'archived'))
    AND (NOT p_require_payment_completed OR v.payment_category = 'payment_completed')
    AND (p_exclude_registration_status IS NULL
      OR NOT (v."registrationStatus" = ANY(p_exclude_registration_status)))
    -- waitlistStatus side filter: seat_released (reserved_link only) and/or denied (sub_status)
    -- mirrors registration.repository.ts, which gates seat_released on reserved_link = true alone
    AND (p_waitlist_status IS NULL
      OR ('seat_released' = ANY(p_waitlist_status) AND v.reserved_link = true)
      OR ('denied'        = ANY(p_waitlist_status) AND v."subStatus" = 'DENIED'))
    -- roomAllocationStatus side filter: completed = has a room_allocation record, pending = none.
    -- mirrors registration.repository.ts roomAllocationStatus block (ra existence), using the
    -- view's "roomAllocated" boolean (EXISTS room_allocation).
    AND (p_room_allocation_status IS NULL
      OR ('room_allocation_completed' = ANY(p_room_allocation_status) AND v."roomAllocated" = true)
      OR ('room_allocation_pending'   = ANY(p_room_allocation_status) AND v."roomAllocated" = false))
    AND (p_organisation IS NULL
      OR (p_organisation = 'yes' AND v.seeker_user_type = 'ORG')
      OR (p_organisation = 'no'  AND (v.seeker_user_type IS NULL OR v.seeker_user_type != 'ORG'))
    )
    AND (p_free_seat IS NULL
      OR (p_free_seat = 'yes' AND v."isFreeSeat" = true)
      OR (p_free_seat = 'no'  AND v."isFreeSeat" = false)
    )
    AND (p_waitlist_category IS NULL
      OR (p_waitlist_category = 'before' AND v.waiting_list_seq_number IS NULL)
      OR (p_waitlist_category = 'after'  AND v.waiting_list_seq_number IS NOT NULL)
    )
    -- recommendation: 'none' = no actively-recommended row; keys must match a row with
    -- is_recommended = true (mirrors registration.repository.ts, which gates on is_recommended)
    AND (p_recommendation IS NULL
      OR ('none' = ANY(p_recommendation) AND NOT EXISTS (
            SELECT 1 FROM hdb_program_registration_recommendations r
            WHERE r.registration_id = v.reg_id AND r.is_recommended = true))
      OR EXISTS (
            SELECT 1 FROM hdb_program_registration_recommendations r
            WHERE r.registration_id = v.reg_id AND r.is_recommended = true
              AND r.recommendation_key::TEXT = ANY(p_recommendation))
    )
    AND (p_defaulter_status IS NULL
      OR (p_defaulter_status = 'defaulter' AND EXISTS (
            SELECT 1 FROM seeker_defaulter sd
            WHERE sd.registration_id = v.reg_id AND sd.is_defaulter = true AND sd.deleted_at IS NULL
          ))
      OR (p_defaulter_status = 'non_defaulter' AND NOT EXISTS (
            SELECT 1 FROM seeker_defaulter sd
            WHERE sd.registration_id = v.reg_id AND sd.is_defaulter = true AND sd.deleted_at IS NULL
          ))
    )
    AND (p_swap_types IS NULL OR (v."swapType" = ANY(p_swap_types) AND v."swapStatus" = 'active'))
    AND (p_kpi_category IS DISTINCT FROM 'swapDemand' OR EXISTS (
          SELECT 1 FROM hdb_program_registration_swap sw2
          WHERE sw2.program_registration_id = v.reg_id
            AND sw2.status = 'on_hold'
            AND sw2.swap_requirement = 'SWAP_DEMAND'
        ))
    AND (p_preferred_program_id IS NULL OR EXISTS (
          SELECT 1 FROM hdb_preference pref
          WHERE pref.registration_id = v.reg_id
            AND pref.preferred_program_id = p_preferred_program_id
            AND pref.priority_order = 1
            AND pref.deleted_at IS NULL
        ))
    AND (p_preferred_session_id IS NULL OR EXISTS (
          SELECT 1 FROM hdb_preference pref
          WHERE pref.registration_id = v.reg_id
            AND pref.preferred_session_id = p_preferred_session_id
            AND pref.priority_order = 1
            AND pref.deleted_at IS NULL
        ))
    AND (p_preferred_program_ids IS NULL OR EXISTS (
          SELECT 1 FROM hdb_preference pref
          WHERE pref.registration_id = v.reg_id
            AND pref.preferred_program_id = ANY(p_preferred_program_ids)
            AND pref.priority_order = 1
            AND pref.deleted_at IS NULL
        ))
    AND (p_swap_preferred_program_id IS NULL OR EXISTS (
          SELECT 1
          FROM hdb_program_registration_swap sw
          WHERE sw.program_registration_id = v.reg_id
            AND (SELECT srp.program_id
                   FROM hdb_swap_requested_program srp
                  WHERE srp.swap_request_id = sw.id
                  ORDER BY srp.id ASC LIMIT 1) = p_swap_preferred_program_id
        ))
    AND (p_swap_demand_preferred_program_id IS NULL OR EXISTS (
          SELECT 1
          FROM (
            SELECT id FROM hdb_program_registration_swap
            WHERE program_registration_id = v.reg_id
              AND status = 'on_hold'
              AND swap_requirement = 'SWAP_DEMAND'
            ORDER BY id DESC LIMIT 1
          ) latest_sw
          WHERE (SELECT srp.program_id
                   FROM hdb_swap_requested_program srp
                  WHERE srp.swap_request_id = latest_sw.id
                  ORDER BY srp.id ASC LIMIT 1) = p_swap_demand_preferred_program_id
        ))
    AND (p_pending_program_id IS NULL OR (
          (v."approvalStatus" = 'rejected' AND EXISTS (
            SELECT 1 FROM hdb_preference pref
            WHERE pref.registration_id = v.reg_id
              AND pref.preferred_program_id = p_pending_program_id
              AND pref.priority_order = 1
              AND pref.deleted_at IS NULL
          ))
          OR
          (v."approvalStatus" = 'on_hold' AND EXISTS (
            SELECT 1
            FROM (
              SELECT id FROM hdb_program_registration_swap
              WHERE program_registration_id = v.reg_id
                AND status = 'on_hold'
                AND swap_requirement = 'SWAP_DEMAND'
              ORDER BY id DESC LIMIT 1
            ) latest_sw
            WHERE (SELECT srp.program_id
                     FROM hdb_swap_requested_program srp
                    WHERE srp.swap_request_id = latest_sw.id
                    ORDER BY srp.id ASC LIMIT 1) = p_pending_program_id
          ))
        ))
    AND (p_experience_tags IS NULL OR EXISTS (
          SELECT 1
            FROM user_program_experience exp
            JOIN lookup_data lk ON lk.id = exp.lookup_data_id AND lk.lookup_status = 'active'
           WHERE exp.user_id = v.user_id
             AND exp.deleted_at IS NULL
             AND lk.lookup_key = ANY(p_experience_tags)
        ))
    AND (p_age_ranges IS NULL OR (
          v.dob_raw IS NOT NULL AND (
            ('0-20'  = ANY(p_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 0  AND 20) OR
            ('21-30' = ANY(p_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 21 AND 30) OR
            ('31-50' = ANY(p_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 31 AND 50) OR
            ('51-65' = ANY(p_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 51 AND 65) OR
            ('>65'   = ANY(p_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) > 65)
          )
        ))
    AND (p_hdb_min IS NULL OR v.no_of_hdbs_raw >= p_hdb_min)
    AND (p_hdb_max IS NULL OR v.no_of_hdbs_raw <= p_hdb_max)
    AND (p_mahatria_choice IS NULL OR v."isMahatriaChoice" = p_mahatria_choice)
    ;
END;
$$;