# Registration Report — Complete Reference

Base path: `/reports` · All endpoints require **admin** role.

---

## Authentication

Send **one** of the following header sets:

### Firebase Auth (standard)
```
Authorization: Bearer <firebase-id-token>
userId: <user-id>
activeRole: admin
```

### OTP / JWT Auth
```
Authorization: Bearer <jwt-token>custom
userId: <user-id>
activeRole: admin
```

---

## Endpoints

### GET /reports/fields

Returns the list of report fields available for a given program. Fields that require a program feature flag are automatically filtered out if the program does not have that feature enabled.

```
GET /reports/fields?programId=123
```

| Query Param | Type | Required |
|---|---|---|
| `programId` | number | Yes |

**Response**
```json
{
  "success": true,
  "data": [
    { "key": "regId",          "label": "Registration ID", "group": "Registration", "order": 1 },
    { "key": "fullName",       "label": "Full Name",        "group": "Registration", "order": 2 },
    { "key": "approvalStatus", "label": "Approval Status",  "group": "Approval",     "order": 18 }
  ],
  "error": null
}
```

Use `key` values to populate the `fields` array in the generate request. Use `group` to group fields in the UI.

---

### POST /reports/generate

Generates a report with the selected fields and filters.

- **`format: "json"`** — returns `{ data, tableHeaders, total }` in the response body.
- **`format: "excel"`** — uploads to S3 and returns a signed `downloadUrl`.

**Request Body**
```json
{
  "fields": ["regId", "fullName", "email", "registrationStatus"],
  "filters": {
    "programId": 123
  },
  "format": "json",
  "fileName": "my_report"
}
```

| Field | Type | Required | Description |
|---|---|---|---|
| `fields` | `string[]` | Yes | Field keys from `GET /reports/fields` |
| `filters` | object | No | Filter criteria (see Filters section) |
| `format` | `"json"` \| `"excel"` | No | Defaults to `"json"` |
| `fileName` | string | No | Base name for Excel file (no extension). Defaults to `"report"` |

---

## Available Field Keys

### Registration

| Key | Label | Notes |
|---|---|---|
| `regId` | Registration ID | |
| `registrationSeqNumber` | Registration Seq No. | e.g. `HDB25-001` |
| `fullName` | Full Name | |
| `email` | Email | |
| `phone` | Mobile | |
| `alternatePhoneNumber` | Alternate Phone | |
| `phoneNumberType` | Phone Type | |
| `phoneNumberRelation` | Phone Relation | |
| `gender` | Gender | |
| `dob` | Date of Birth | formatted as `DD MMM YYYY` |
| `city` | City | COALESCE(other_city_name, city) |
| `otherCityName` | Other City | auto-included when `city` is selected |
| `country` | Country | |
| `personType` | Person Type | |
| `program` | Program Name | |
| `registrationDate` | Registration Date | formatted as `DD MMM YYYY HH:mm` |
| `registrationStatus` | Registration Status | |
| `registrationMode` | Registration Mode | |
| `seatAllocated` | Seat Allocated | `Yes` / `No` |
| `isFreeSeat` | Free Seat | `Yes` / `No` |
| `numberOfHdbs` | Number of HDBs | |
| `lastHdbAttended` | Last HDB Attended | _(requires: PT\_HDBMSD program type)_ |
| `hdbAssociationSince` | HDB Association Since | _(requires: PT\_HDBMSD program type)_ |
| `isMahatriaChoice` | Mahatria Choice | `Yes` / `No` _(requires: PT\_HDBMSD program type)_ |
| `songPreferences` | Song Preferences | comma-separated _(requires: PT\_HDBMSD program type)_ |
| `rmContact` | RM Contact | COALESCE(other_infinitheism_contact, users.full_name) |

### Seeker

| Key | Label | Notes |
|---|---|---|
| `userType` | User Type | |
| `isDefaulter` | Defaulter | `Yes` / `No` |

### Parental Form _(requires: parentalConsentEnabled)_

| Key | Label | Notes |
|---|---|---|
| `parentalFormStatus` | Parental Form Status | |
| `parentalFormUploadedAt` | Parental Form Uploaded At | formatted as `DD MMM YYYY HH:mm` |
| `parentalFormAdminNotes` | Parental Form Admin Notes | |

### Room _(requires: requiresResidence)_

| Key | Label | Notes |
|---|---|---|
| `preferredRoomMate` | Preferred Roommate | |
| `roomAllocated` | Room Allocated | `Yes` / `No` |

### Waitlist _(requires: waitlistApplicable)_

| Key | Label | Notes |
|---|---|---|
| `waitListFormattedSeqNo` | Waitlist Formatted Seq No. | e.g. `HDB25-WL-001` |
| `reservedLink` | Seat Released | `Yes` / `No` |
| `subStatus` | Sub Status | |

### Cancellation

| Key | Label | Notes |
|---|---|---|
| `cancellationDate` | Cancellation Date | formatted as `DD MMM YYYY HH:mm` |
| `cancelledBy` | Cancelled By | user full name |
| `cancellationReason` | Cancellation Reason | |
| `cancellationComments` | Cancellation Comments | |

### Approval _(requires: requiresApproval)_

| Key | Label | Notes |
|---|---|---|
| `approvalStatus` | Approval Status | |
| `approvalDate` | Date of Blessing | formatted as `DD MMM YYYY HH:mm` |
| `allocatedProgramName` | Allocated Program | _(requires: isGroupedProgram)_ |
| `programPreferences` | Choices | comma-separated _(requires: requiresApproval + isGroupedProgram)_ |

### RM _(requires: requiresApproval)_

| Key | Label | Notes |
|---|---|---|
| `rmReview` | RM Review | |
| `rmRating` | RM Rating | avg number |
| `rmRecommendation` | Recommendation Level | |
| `isRecommended` | Is Recommended | `Yes` / `No` |
| `recommendationComments` | RM Comments | |

### Swap _(requires: isGroupedProgram)_

| Key | Label | Notes |
|---|---|---|
| `swapType` | Swap Type | `SWAP_REQUEST` / `SWAP_DEMAND` |
| `swapStatus` | Swap Status | |
| `swapRequirement` | Swap Requirement | |
| `swapComment` | Swap RM Comments | |
| `swapCurrentProgram` | Current Program (Swap) | |
| `swapRequestedPrograms` | Swap Request To | |

### Payment _(requires: requiresPayment)_

| Key | Label | Notes |
|---|---|---|
| `paymentStatus` | Payment Status | |
| `paymentMode` | Payment Mode | |
| `originalAmount` | Original Amount | currency |
| `paymentSubTotal` | Fee Amount | currency |
| `paymentGstAmount` | GST Amount | currency |
| `paymentTaxAmount` | Tax Amount | currency |
| `paymentTds` | TDS | currency |
| `razorpayId` | Razorpay ID | online payments |
| `paymentDate` | Payment Date | formatted as `DD MMM YYYY HH:mm` |
| `markAsReceivedDate` | Payment Received Date | formatted as `DD MMM YYYY HH:mm` |
| `offlinePaymentMode` | Offline Payment Mode | from `offlineMeta` |
| `offlineBankName` | Bank Name | from `offlineMeta` |
| `offlineChequeNo` | Cheque / DD No. | from `offlineMeta` |
| `offlineChequeDate` | Cheque / DD Date | formatted as `DD MMM YYYY` |
| `offlineUtrNumber` | UTR / Reference No. | from `offlineMeta` |
| `offlineBankTransferDate` | Bank Transfer Date | formatted as `DD MMM YYYY` |

### Proforma _(requires: isProformaAllowed)_

| Key | Label | Notes |
|---|---|---|
| `proformaSeqNumber` | Proforma Number | e.g. `PF-HDB25-001` |
| `proformaInvoiceName` | Proforma Name | |
| `proformaInvoiceAddress` | Proforma Address | |
| `proformaZip` | Proforma Pincode | |
| `proformaIsGstRegistered` | Proforma GST Registered | `Yes` / `No` |
| `proformaGstNumber` | Proforma GST Number | |

### Invoice _(requires: requiresPayment)_

| Key | Label | Notes |
|---|---|---|
| `invoiceNumber` | Invoice Number | |
| `invoiceType` | Invoice Type | |
| `invoiceStatus` | Invoice Status | |
| `invoiceIssuedDate` | Invoice Date | formatted as `DD MMM YYYY` |
| `invoiceName` | Invoice Name | |
| `invoiceEmail` | Invoice Email | |
| `invoiceAddress` | Invoice Address | |
| `invoicePinCode` | Invoice Pincode | |
| `invoiceGstNumber` | GSTIN | |
| `invoicePanNumber` | PAN Number | |
| `invoiceTanNumber` | TAN | |
| `invoiceTdsApplicable` | TDS Applicable | `Yes` / `No` |
| `tdsAmount` | TDS Amount | currency |
| `handoverDate` | Invoice Handover Date | formatted as `DD MMM YYYY HH:mm` |
| `handoverTo` | Invoice Handover To | |
| `einvoiceStatus` | E-Invoice Status | |
| `einvoiceStatusFormatted` | E-Invoice Formatted Status | |
| `einvoiceAckNumber` | E-Invoice Ack Number | |
| `einvoiceAckDate` | E-Invoice Ack Date | |
| `einvoiceQrLink` | E-Invoice QR Link | |
| `einvoiceRefNumber` | E-Invoice Ref Number | |

### Travel Info _(requires: involvesTravel)_

| Key | Label | Notes |
|---|---|---|
| `travelInfoStatus` | Travel Info Status | |
| `idType` | ID Type | Aadhar / Passport / DL etc. |
| `idNumber` | ID Number | |
| `idFrontPicture` | ID Front Picture | URL |
| `idBackPicture` | ID Back Picture | URL |
| `internationalIdPicture` | International ID | URL |
| `passportCopy` | Passport Copy | URL |
| `visaCopy` | Visa Copy | URL |
| `tshirtSize` | T-Shirt Size (Travel) | |

### Travel Plan _(requires: involvesTravel)_

| Key | Label | Notes |
|---|---|---|
| `travelPlanStatus` | Travel Plan Status | |
| `onwardAirline` | Onward Airline | |
| `onwardFlightNumber` | Onward Flight No. | |
| `onwardArrivalDatetime` | Onward Arrival | formatted as `DD MMM YYYY HH:mm` |
| `onwardArrivalFrom` | Coming From | |
| `onwardDepartureLocation` | Departure Location | |
| `onwardTerminal` | Onward Terminal | |
| `onwardAdditionalInfo` | Onward Additional Info | |
| `onwardJourneyTicket` | Onward Ticket URL | URL |
| `returnTravelType` | Return Travel Type | |
| `returnAirline` | Return Airline | |
| `returnFlightNumber` | Return Flight No. | |
| `returnDepartureDatetime` | Return Departure | formatted as `DD MMM YYYY HH:mm` |
| `returnDepartureTo` | Going To | |
| `returnTerminal` | Return Terminal | |
| `returnAdditionalInfo` | Return Additional Info | |
| `returnJourneyTicket` | Return Ticket URL | URL |
| `pickupLocation` | Pickup Location | |
| `pickupTime` | Pickup Time | |
| `checkinAt` | Check-in At | formatted as `DD MMM YYYY HH:mm` |
| `checkinTime` | Check-in Time | |
| `checkinLocation` | Check-in Location | |

### Goodies _(requires: hasGoodies)_

| Key | Label | Notes |
|---|---|---|
| `goodiesStatus` | Goodies Status | |
| `goodiesNotebook` | Notebook | `Yes` / `No` |
| `goodiesFlask` | Flask | `Yes` / `No` |
| `goodiesTshirt` | T-Shirt | `Yes` / `No` |
| `goodiesTshirtSize` | T-Shirt Size | |
| `goodiesJacket` | Jacket | `Yes` / `No` |
| `goodiesJacketSize` | Jacket Size | |
| `goodiesRatriaPillar` | Ratria Pillar | `Yes` / `No` |
| `goodiesRatriaPillarLocation` | Ratria Pillar Location | |
| `goodiesRatriaPillarOtherLoc` | Ratria Pillar Other Location | auto-included when `goodiesRatriaPillarLocation` is selected |

---

## Filters Reference

All filters are passed as a single JSONB object to `fn_generate_registration_report`. All are optional.

### Program / Session

| Key | Type | Notes |
|---|---|---|
| `programId` | int | Program ID |
| `programSessionId` | int | Session ID |

### Registration Status

| Key | Type | Values |
|---|---|---|
| `registrationStatus` | string[] | `pending` `approved` `on_hold` `rejected` `save_as_draft` `waitlisted` `cancelled` `archived` |
| `approvalStatus` | string[] | `pending` `approved` `on_hold` `rejected` |
| `defaulterStatus` | string | `defaulter` `non_defaulter` |

### Demographics

| Key | Type | Values / Notes |
|---|---|---|
| `gender` | string[] | `Male` `Female` |
| `age` | string[] | `0-20` `21-30` `31-50` `51-65` `>65` |
| `location` | string[] | Any city value — matches COALESCE(other_city_name, city) |
| `experienceTags` | string[] | Lookup keys from `lookup_data` where `lookup_status = 'active'` |

### Seat / Allocation

| Key | Type | Values / Notes |
|---|---|---|
| `freeSeat` | string | `yes` `no` |
| `organisation` | string | `yes` `no` — filters by `seeker_user_type = 'ORG'` |
| `seatReleased` | boolean | `true` `false` — filters by `reserved_link` |
| `waitlistCategory` | string | `before` (no seq assigned) · `after` (seq assigned) |
| `allocatedProgramId` | int | Allocated program ID |
| `allocatedSessionId` | int | Allocated session ID |

### Preferences

| Key | Type | Notes |
|---|---|---|
| `preferredProgramId` | int | Single program ID — priority-1 preference |
| `preferredSessionId` | int | Single session ID — priority-1 preference |
| `preferredPrograms` | int[] | Any of these as priority-1 preference |

### HDB Count

Pass as range notation — service converts to `hdbMin`/`hdbMax` before passing to SQL.

| Key | Type | Example Values |
|---|---|---|
| `numberOfHdbs` | string[] | `["0-5"]` `[">10"]` `["=5"]` `["1-3","6-10"]` |

### Payment

| Key | Type | Values |
|---|---|---|
| `paymentStatus` | string[] | `payment_completed` `payment_pending` `no_payment` |
| `paymentMode` | string[] | `online` `offline` |

### Invoice

| Key | Type | Values |
|---|---|---|
| `invoiceStatus` | string[] | `invoice_completed` `invoice_pending` |

### Travel & Goodies

| Key | Type | Values |
|---|---|---|
| `travelStatus` | string[] | `travel_completed` `travel_pending` |
| `travelPlan` | string[] | Travel plan status values from `hdb_registration_travel_plan` |
| `goodiesStatus` | string[] | `goodies_completed` `goodies_pending` |

### RM / Approval

| Key | Type | Notes |
|---|---|---|
| `rmContact` | int[] | Array of RM user IDs |
| `rmRating` | number | Minimum avg rating threshold — filters `>= value` |
| `recommendation` | string[] | Recommendation keys, or `none` for no recommendation on record |

### Swap

| Key | Type | Notes |
|---|---|---|
| `swapRequests` | string[] | `wants_swap` `can_shift` — must have `swapStatus = 'active'` |
| `swapPreferredProgramId` | int | First requested program in any active swap |
| `swapDemandPreferredProgramId` | int | First requested program in latest on-hold SWAP_DEMAND |
| `pendingProgramId` | int | Program where reg is `rejected` (preference) OR `on_hold` (swap demand) |

### Date Range

| Key | Type | Format |
|---|---|---|
| `createdFrom` | string | `YYYY-MM-DD` — inclusive start |
| `createdTo` | string | `YYYY-MM-DD` — inclusive end |

### KPI Shortcuts

Use `kpiCategory` + `kpiFilter` to match dashboard KPI tiles. The backend (and SQL function) translate these into concrete filters.

| `kpiCategory` | `kpiFilter` | Resolves to |
|---|---|---|
| `seats` | `onlineCompleted` | `paymentStatus=[payment_completed]`, `paymentMode=[online]` |
| `seats` | `offlineCompleted` | `paymentStatus=[payment_completed]`, `paymentMode=[offline]` |
| `seats` | `onlinePending` | `paymentStatus=[payment_pending]`, `paymentMode=[online]` |
| `seats` | `offlinePending` | `paymentStatus=[payment_pending]`, `paymentMode=[offline]` |
| `seats` | `organisation` | `organisation=yes` |
| `seats` | `waitingList` | `registrationStatus=[waitlisted]` |
| `seats` | `cancelled` | `registrationStatus=[cancelled]` |
| `seats` | `firstPagePending` | `registrationStatus=[pending, save_as_draft]` |
| `payment` | `paymentComplete` | `paymentStatus=[payment_completed]` |
| `payment` | `paymentPending` | `paymentStatus=[payment_pending]` |
| `payment` | `noPayment` | `freeSeat=yes` |
| `invoice` | `invoiceComplete` | `invoiceStatus=[invoice_completed]` |
| `invoice` | `invoicePending` | `invoiceStatus=[invoice_pending]` |
| `travelAndGoodies` | `travelComplete` | `travelStatus=[travel_completed]` |
| `travelAndGoodies` | `travelPending` | `travelStatus=[travel_pending]` |
| `travelAndGoodies` | `goodiesComplete` | `goodiesStatus=[goodies_completed]` |
| `travelAndGoodies` | `goodiesPending` | `goodiesStatus=[goodies_pending]` |
| `waitlisted` | `waitlisted_before_seq` | `registrationStatus=[waitlisted]`, `waitlistCategory=before` |
| `waitlisted` | `waitlisted_with_seq` | `registrationStatus=[waitlisted]`, `waitlistCategory=after` |
| `waitlisted` | `waitlisted_seat_released` | `registrationStatus=[waitlisted]`, `seatReleased=true` |
| `waitlisted` | `waitlisted_seat_pending` | `registrationStatus=[waitlisted]`, `seatReleased=false`, `waitlistCategory=after` |
| `waitlisted` | _(any / omit)_ | `registrationStatus=[waitlisted]` |
| `swapRequests` | — | `swapRequests=[wants_swap]` |
| `swapDemand` | — | `approvalStatus=[on_hold]` + active SWAP_DEMAND record |
| `regPending` | — | `approvalStatus=[on_hold, rejected]` |
| `registrations` | `approvedSeekers` | `approvalStatus=[approved]` |
| `registrations` | `newSeekersPending` | `approvalStatus=[pending]` |
| `registrations` | `rejected` | `approvalStatus=[rejected]` |
| `registrations` | `onHold` | `approvalStatus=[on_hold]` |
| `registrations` | `regPending` | `approvalStatus=[on_hold, rejected]` |
| `registrations` | `cancelled` | `registrationStatus=[cancelled]` |
| `blessed` | `paymentPending` | `approvalStatus=[approved]`, `paymentStatus=[payment_pending]` |
| `blessed` | `paymentComplete` | `approvalStatus=[approved]`, `paymentStatus=[payment_completed]` |
| `blessed` | `invoicePending` | `approvalStatus=[approved]`, `invoiceStatus=[invoice_pending]` |
| `blessed` | `invoiceComplete` | `approvalStatus=[approved]`, `invoiceStatus=[invoice_completed]` |
| `blessed` | `travelPending` | `approvalStatus=[approved]`, `travelStatus=[travel_pending]` |
| `blessed` | `travelComplete` | `approvalStatus=[approved]`, `travelStatus=[travel_completed]` |
| `blessed` | `swapRequests` | `approvalStatus=[approved]`, `swapRequests=[wants_swap]` |
| `blessed` | `cancelled` | `approvalStatus=[approved]`, `registrationStatus=[cancelled]` |
| `defaulter` | `approvedSeekers` | `defaulterStatus=defaulter`, `approvalStatus=[approved]` |
| `defaulter` | _(others)_ | Same as `registrations` + `defaulterStatus=defaulter` |
| `program_<id>` | `approvedSeekers` | `allocatedProgramId=<id>`, `approvalStatus=[approved]` |

---

## Response — JSON format

```json
{
  "success": true,
  "data": {
    "tableHeaders": [
      { "key": "regId",              "alias": "reg_id",             "label": "Registration ID",    "order": 1, "type": "number",  "sortable": false, "filterable": false },
      { "key": "fullName",           "alias": "fullName",           "label": "Full Name",           "order": 2, "type": "string",  "sortable": false, "filterable": false },
      { "key": "registrationStatus", "alias": "registrationStatus", "label": "Registration Status", "order": 3, "type": "string",  "sortable": false, "filterable": false },
      { "key": "dob",                "alias": "dob",                "label": "Date of Birth",       "order": 4, "type": "date",    "sortable": false, "filterable": false },
      { "key": "paymentSubTotal",    "alias": "paymentSubTotal",    "label": "Fee Amount",          "order": 5, "type": "number",  "sortable": false, "filterable": false }
    ],
    "data": [
      { "reg_id": 1001, "fullName": "Arun Kumar", "registrationStatus": "Approved", "dob": "15 Mar 1990", "paymentSubTotal": 5000 },
      { "reg_id": 1002, "fullName": "Priya S",    "registrationStatus": "Pending",  "dob": null,          "paymentSubTotal": null }
    ],
    "total": 2
  },
  "error": null
}
```

### tableHeaders fields

| Field | Type | Description |
|---|---|---|
| `key` | string | Field key (same as sent in `fields[]`) |
| `alias` | string | Actual key used in each `data` row object |
| `label` | string | Human-readable column header |
| `order` | number | Column display order |
| `type` | `"string"` \| `"number"` \| `"date"` \| `"boolean"` | Data type hint for rendering |

> **Important:** Row keys in `data[]` use the `alias` from `tableHeaders`, not the `key`. e.g. `regId` → `reg_id`.

**Value formatting applied server-side:**

| Format | Output |
|---|---|
| `date` | `DD MMM YYYY` e.g. `15 Mar 1990` |
| `datetime` | `DD MMM YYYY HH:mm` e.g. `01 Jan 2024 10:30` |
| `boolean` | `"Yes"` or `"No"` |
| `number` / `currency` | JavaScript number (not string) |
| `json` / arrays | Comma-separated string e.g. `"Song A, Song B"` |
| `text` | String as-is |
| null / missing | `null` |

---

## Response — Excel format

Returns a JSON object with a signed S3 download URL (not a file stream):

```json
{
  "success": true,
  "data": {
    "downloadUrl": "https://s3.amazonaws.com/bucket/exports/excel/..."
  },
  "error": null
}
```

The URL is pre-signed and expires after a short time.

```javascript
const response = await fetch('/reports/generate', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json', Authorization: 'Bearer ...', userId: '...', activeRole: 'admin' },
  body: JSON.stringify({ fields: [...], filters: { programId: 123 }, format: 'excel', fileName: 'my_report' }),
})
const { data } = await response.json()
window.open(data.downloadUrl, '_blank')
```

---

## Example Requests

### Basic registration list for a program
```json
POST /reports/generate
{
  "fields": ["regId", "fullName", "email", "phone", "gender", "registrationStatus", "registrationDate"],
  "filters": { "programId": 123 },
  "format": "json"
}
```

### Approved seekers with payment details
```json
POST /reports/generate
{
  "fields": ["regId", "fullName", "email", "approvalStatus", "approvalDate", "paymentStatus", "paymentSubTotal", "paymentDate"],
  "filters": { "programId": 123, "approvalStatus": ["approved"], "paymentStatus": ["payment_completed"] },
  "format": "json"
}
```

### Using KPI shortcut — blessed seekers with payment pending
```json
POST /reports/generate
{
  "fields": ["regId", "fullName", "email", "paymentStatus", "paymentSubTotal"],
  "filters": { "programId": 123, "kpiCategory": "blessed", "kpiFilter": "paymentPending" },
  "format": "json"
}
```

### Waitlisted registrations exported to Excel
```json
POST /reports/generate
{
  "fields": ["regId", "fullName", "email", "phone", "registrationStatus", "waitListFormattedSeqNo", "registrationDate"],
  "filters": { "programId": 123, "registrationStatus": ["waitlisted"] },
  "format": "excel",
  "fileName": "waitlist_report"
}
```

### Registrations filtered by city and HDB count
```json
POST /reports/generate
{
  "fields": ["regId", "fullName", "city", "numberOfHdbs", "registrationStatus"],
  "filters": { "programId": 123, "location": ["Chennai", "Bengaluru"], "numberOfHdbs": [">5"] },
  "format": "json"
}
```

---

## Error Responses

```json
{
  "success": false,
  "data": null,
  "error": {
    "code": "INVALID_REPORT_FIELDS",
    "message": "...",
    "details": { "invalidFields": ["unknownKey"] }
  }
}
```

| Scenario | HTTP | Error Code |
|---|---|---|
| Unknown field key in `fields[]` | 400 | `INVALID_REPORT_FIELDS` |
| Field requires a flag the program doesn't have | 400 | `INVALID_REPORT_FIELDS` |
| Program not found | 404 | `PROGRAM_NOT_FOUND` |
| Not authenticated | 401 | — |
| Not admin role | 403 | — |

---

## Database Architecture

### Overview

A PostgreSQL **view** defines all joins and output columns once. A PostgreSQL **function** accepts JSONB filters and applies them. The service does a dynamic `SELECT` over the function — only the requested columns are returned.

### View Architecture

```
hdb_program_registration (reg)
  ├── hdb_registration_approval       → approvalStatus, approvalDate, rmRating, programPreferences
  ├── hdb_registration_payment_detail → paymentStatus, paymentMode, paymentSubTotal, paymentGst, razorpayId
  ├── hdb_registration_invoice_detail → invoiceStatus, invoiceNumber, invoiceName, invoiceEmail, invoiceGst/Tan/Pan
  ├── hdb_registration_travel_info    → travelInfoStatus, idType, idNumber, tshirtSize
  ├── hdb_registration_travel_plan    → travelPlanStatus, onward/return flight details, pickupLocation/Time
  ├── hdb_program_registration_goodies → goodiesStatus, ratriaPillar, flask, notebook, jacketSize
  ├── hdb_program_registration_swap   → swapType, swapStatus, swapComment, swapCurrentProgram
  └── hdb_program_registration_recommendations → rmRecommendation
```

All one-to-many joins use `LEFT LATERAL` to guarantee one row per registration.

**Derived filter-helper columns** (used in WHERE only, not selectable):
- `payment_category` — maps raw `paymentStatus` to `payment_completed` / `payment_pending` / `no_payment`
- `invoice_category` — maps raw `invoiceStatus` to `invoice_completed` / `invoice_pending`
- `travel_overall_status` — `travel_completed` only when both travelInfo AND travelPlan are COMPLETED

### View SQL

```sql
CREATE VIEW vw_hdb_registration_report AS
SELECT
  -- Filter-only 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.waiting_list_seq_number,
  reg.reserved_link,
  reg.dob                             AS dob_raw,
  reg.no_of_hdbs                      AS no_of_hdbs_raw,
  seekerUser.user_type                AS seeker_user_type,

  -- Derived filter-helper columns
  CASE
    WHEN payment.payment_status IN ('ONLINE_COMPLETED', 'OFFLINE_COMPLETED')        THEN 'payment_completed'
    WHEN payment.payment_status IN ('ONLINE_PENDING', 'OFFLINE_PENDING', 'FAILED')  THEN 'payment_pending'
    WHEN reg.is_free_seat = true                                                     THEN 'no_payment'
    ELSE NULL
  END AS payment_category,

  CASE
    WHEN invoice.invoice_status = 'INVOICE_COMPLETED'                               THEN 'invoice_completed'
    WHEN invoice.invoice_status IN ('INVOICE_PENDING', 'DRAFT') OR invoice.id IS NULL THEN 'invoice_pending'
    ELSE NULL
  END AS invoice_category,

  CASE
    WHEN travelInfo.travel_info_status = 'COMPLETED'
     AND travelPlan.travel_plan_status = 'COMPLETED'                                THEN 'travel_completed'
    ELSE 'travel_pending'
  END AS travel_overall_status,

  -- Output columns
  reg.full_name                       AS "fullName",
  reg.email_address                   AS "email",
  reg.mobile_number                   AS "phone",
  reg.gender,
  reg.dob,
  reg.city,
  reg.country_name                    AS "country",
  reg.registration_status             AS "registrationStatus",
  reg.created_at                      AS "createdAt",
  reg.no_of_hdbs                      AS "numberOfHdbs",
  reg.last_hdb_attended               AS "lastHdbAttended",
  reg.is_free_seat                    AS "isFreeSeat",
  reg.parental_form_status            AS "parentalFormStatus",
  reg.pro_forma_invoice_name          AS "proformaInvoiceName",
  reg.pro_forma_invoice_address       AS "proformaInvoiceAddress",
  reg.proforma_invoice_seq_number     AS "proformaSeqNumber",
  reg.pro_forma_gst_number            AS "proformaGstNumber",
  reg.pro_forma_zip                   AS "proformaZip",

  COALESCE(allocProg.name, program.name) AS "allocatedOrProgram",

  approval.approval_status            AS "approvalStatus",
  approval.approval_date              AS "approvalDate",
  rmUser.full_name                    AS "rmContact",
  recommendation.recommendation_key  AS "rmRecommendation",

  (SELECT AVG(r.rating)
   FROM hdb_program_registration_rm_rating r
   WHERE r.program_registration_id = reg.id AND r.deleted_at IS NULL
  )                                   AS "rmRating",

  (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 = reg.id AND pref.deleted_at IS NULL
  )                                   AS "programPreferences",

  payment.payment_status              AS "paymentStatus",
  payment.payment_mode                AS "paymentMode",
  payment.sub_total                   AS "paymentSubTotal",
  payment.gst_amount                  AS "paymentGstAmount",
  payment.tds                         AS "paymentTds",
  payment.razorpay_id                 AS "razorpayId",
  payment.payment_date                AS "paymentDate",

  invoice.invoice_status              AS "invoiceStatus",
  invoice.invoice_sequence_number     AS "invoiceNumber",
  invoice.invoice_name                AS "invoiceName",
  invoice.invoice_email               AS "invoiceEmail",
  invoice.invoice_address             AS "invoiceAddress",
  invoice.invoice_issued_date         AS "invoiceIssuedDate",
  invoice.gst_number                  AS "invoiceGstNumber",
  invoice.tan_number                  AS "invoiceTanNumber",
  invoice.pan_number                  AS "invoicePanNumber",

  travelInfo.travel_info_status       AS "travelInfoStatus",
  travelInfo.id_type                  AS "idType",
  travelInfo.id_number                AS "idNumber",
  travelInfo.tshirt_size              AS "tshirtSize",

  travelPlan.travel_plan_status       AS "travelPlanStatus",
  travelPlan.airline_name             AS "onwardAirline",
  travelPlan.flight_number            AS "onwardFlightNumber",
  travelPlan.arrival_datetime         AS "onwardArrivalDatetime",
  travelPlan.arrival_from             AS "onwardArrivalFrom",
  travelPlan.onward_terminal          AS "onwardTerminal",
  travelPlan.onward_additional_info   AS "onwardAdditionalInfo",
  travelPlan.departure_airline_name   AS "returnAirline",
  travelPlan.departure_flight_number  AS "returnFlightNumber",
  travelPlan.departure_datetime       AS "returnDepartureDatetime",
  travelPlan.departure_to             AS "returnDepartureTo",
  travelPlan.return_terminal          AS "returnTerminal",
  travelPlan.return_additional_info   AS "returnAdditionalInfo",
  travelPlan.pickup_location          AS "pickupLocation",
  travelPlan.pickup_time              AS "pickupTime",

  swap.type                           AS "swapType",
  swap.status                         AS "swapStatus",
  swap.comment                        AS "swapComment",
  swapCurrentProg.name                AS "swapCurrentProgram",

  (SELECT pv.name
   FROM hdb_swap_requested_program srp
   JOIN program_v1 pv ON pv.id = srp.program_id
   WHERE srp.swap_request_id = swap.id
   ORDER BY srp.id ASC LIMIT 1
  )                                   AS "swapRequestedProgram",

  CASE
    WHEN goodies.id IS NOT NULL AND reg.allocated_program_id IS NOT NULL THEN 'goodies_completed'
    ELSE 'goodies_pending'
  END                                 AS "goodiesStatus",
  goodies.ratria_pillar_leonia        AS "ratriaPillarLeonia",
  goodies.ratria_pillar_location      AS "ratriaPillarLocation",
  goodies.flask,
  goodies.notebook,
  goodies.jacket_size                 AS "jacketSize",
  goodies.tshirt_size                 AS "goodiesTshirtSize"

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      rmUser          ON rmUser.id     = reg.rm_contact            AND rmUser.deleted_at     IS NULL
LEFT JOIN users      seekerUser      ON seekerUser.id = reg.user_id               AND seekerUser.deleted_at IS NULL

LEFT JOIN LATERAL (
  SELECT * FROM hdb_registration_approval a
  WHERE a.registration_id = reg.id AND a.deleted_at IS NULL ORDER BY a.id DESC LIMIT 1
) approval ON true

LEFT JOIN LATERAL (
  SELECT * FROM hdb_registration_payment_detail p
  WHERE p.registration_id = reg.id AND p.deleted_at IS NULL ORDER BY p.id DESC LIMIT 1
) payment ON true

LEFT JOIN LATERAL (
  SELECT * FROM hdb_registration_invoice_detail i
  WHERE i.registration_id = reg.id AND i.deleted_at IS NULL ORDER BY i.id DESC LIMIT 1
) invoice ON true

LEFT JOIN LATERAL (
  SELECT * FROM hdb_registration_travel_info ti
  WHERE ti.registration_id = reg.id AND ti.deleted_at IS NULL ORDER BY ti.id DESC LIMIT 1
) travelInfo ON true

LEFT JOIN LATERAL (
  SELECT * FROM hdb_registration_travel_plan tp
  WHERE tp.registration_id = reg.id AND tp.deleted_at IS NULL ORDER BY tp.id DESC LIMIT 1
) travelPlan ON true

LEFT JOIN LATERAL (
  SELECT * FROM hdb_program_registration_goodies g
  WHERE g.registration_id = reg.id AND g.deleted_at IS NULL ORDER BY g.id DESC LIMIT 1
) goodies ON true

LEFT JOIN LATERAL (
  SELECT * FROM hdb_program_registration_swap sw
  WHERE sw.program_registration_id = reg.id AND sw.deleted_at IS NULL ORDER BY sw.id DESC LIMIT 1
) swap ON true

LEFT JOIN program_v1 swapCurrentProg ON swapCurrentProg.id = swap.current_program_id AND swapCurrentProg.deleted_at IS NULL

LEFT JOIN LATERAL (
  SELECT * FROM hdb_program_registration_recommendations r
  WHERE r.registration_id = reg.id AND r.deleted_at IS NULL ORDER BY r.id DESC LIMIT 1
) recommendation ON true

WHERE reg.deleted_at IS NULL;
```

### Function SQL

The function accepts a JSONB filters object, translates KPI shortcuts, and applies all filters.

```sql
CREATE OR REPLACE FUNCTION fn_generate_registration_report(p_filters JSONB DEFAULT '{}')
RETURNS SETOF vw_hdb_registration_report
LANGUAGE plpgsql AS $$
DECLARE
  v_program_id            INT     := (p_filters->>'programId')::INT;
  v_program_session_id    INT     := (p_filters->>'programSessionId')::INT;
  v_registration_status   TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'registrationStatus'));
  v_approval_status       TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'approvalStatus'));
  v_gender                TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'gender'));
  v_organisation          TEXT    := p_filters->>'organisation';
  v_defaulter_status      TEXT    := p_filters->>'defaulterStatus';
  v_payment_status        TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'paymentStatus'));
  v_payment_mode          TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'paymentMode'));
  v_invoice_status        TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'invoiceStatus'));
  v_travel_status         TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'travelStatus'));
  v_travel_plan           TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'travelPlan'));
  v_age_ranges            TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'age'));
  v_hdb_min               INT     := (p_filters->>'hdbMin')::INT;
  v_hdb_max               INT     := (p_filters->>'hdbMax')::INT;
  v_experience_tags       TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'experienceTags'));
  v_location              TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'location'));
  v_free_seat             TEXT    := p_filters->>'freeSeat';
  v_waitlist_category     TEXT    := p_filters->>'waitlistCategory';
  v_seat_released         BOOLEAN := (p_filters->>'seatReleased')::BOOLEAN;
  v_allocated_program_id  INT     := (p_filters->>'allocatedProgramId')::INT;
  v_allocated_session_id  INT     := (p_filters->>'allocatedSessionId')::INT;
  v_preferred_program_id  INT     := (p_filters->>'preferredProgramId')::INT;
  v_preferred_session_id  INT     := (p_filters->>'preferredSessionId')::INT;
  v_preferred_programs    INT[]   := ARRAY(SELECT jsonb_array_elements_text(p_filters->'preferredPrograms')::INT);
  v_swap_types            TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'swapRequests'));
  v_swap_preferred_prog   INT     := (p_filters->>'swapPreferredProgramId')::INT;
  v_swap_demand_prog      INT     := (p_filters->>'swapDemandPreferredProgramId')::INT;
  v_pending_program_id    INT     := (p_filters->>'pendingProgramId')::INT;
  v_rm_contact            INT[]   := ARRAY(SELECT jsonb_array_elements_text(p_filters->'rmContact')::INT);
  v_rm_rating             NUMERIC := (p_filters->>'rmRating')::NUMERIC;
  v_recommendation        TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'recommendation'));
  v_kpi_category          TEXT    := p_filters->>'kpiCategory';
  v_kpi_filter            TEXT    := p_filters->>'kpiFilter';
  v_created_from          DATE    := (p_filters->>'createdFrom')::DATE;
  v_created_to            DATE    := (p_filters->>'createdTo')::DATE;
  v_goodies_status        TEXT[]  := ARRAY(SELECT jsonb_array_elements_text(p_filters->'goodiesStatus'));
BEGIN
  -- Translate kpiCategory + kpiFilter into concrete filter values
  IF v_kpi_category = 'payment' AND v_kpi_filter = 'paymentComplete' THEN
    v_payment_status := ARRAY['payment_completed'];
  ELSIF v_kpi_category = 'payment' AND v_kpi_filter = 'paymentPending' THEN
    v_payment_status := ARRAY['payment_pending'];
  ELSIF v_kpi_category = 'payment' AND v_kpi_filter = 'noPayment' THEN
    v_free_seat := 'yes';
  ELSIF v_kpi_category = 'invoice' AND v_kpi_filter = 'invoiceComplete' THEN
    v_invoice_status := ARRAY['invoice_completed'];
  ELSIF v_kpi_category = 'invoice' AND v_kpi_filter = 'invoicePending' THEN
    v_invoice_status := ARRAY['invoice_pending'];
  ELSIF v_kpi_category = 'travelAndGoodies' AND v_kpi_filter = 'travelComplete' THEN
    v_travel_status := ARRAY['travel_completed'];
  ELSIF v_kpi_category = 'travelAndGoodies' AND v_kpi_filter = 'travelPending' THEN
    v_travel_status := ARRAY['travel_pending'];
  ELSIF v_kpi_category = 'travelAndGoodies' AND v_kpi_filter = 'goodiesComplete' THEN
    v_goodies_status := ARRAY['goodies_completed'];
  ELSIF v_kpi_category = 'travelAndGoodies' AND v_kpi_filter = 'goodiesPending' THEN
    v_goodies_status := ARRAY['goodies_pending'];
  ELSIF v_kpi_category IN ('waitlisted', 'seats') AND v_kpi_filter = 'waitingList' THEN
    v_registration_status := ARRAY['WAITLISTED'];
  ELSIF v_kpi_category = 'seats' AND v_kpi_filter = 'cancelled' THEN
    v_registration_status := ARRAY['CANCELLED'];
  ELSIF v_kpi_category = 'seats' AND v_kpi_filter = 'organisation' THEN
    v_organisation := 'yes';
  ELSIF v_kpi_category = 'seats' AND v_kpi_filter = 'firstPagePending' THEN
    v_registration_status := ARRAY['PENDING', 'SAVE_AS_DRAFT'];
  ELSIF v_kpi_category = 'waitlisted' AND v_kpi_filter = 'waitlisted_before_seq' THEN
    v_registration_status := ARRAY['WAITLISTED']; v_waitlist_category := 'before';
  ELSIF v_kpi_category = 'waitlisted' AND v_kpi_filter = 'waitlisted_with_seq' THEN
    v_registration_status := ARRAY['WAITLISTED']; v_waitlist_category := 'after';
  ELSIF v_kpi_category = 'waitlisted' AND v_kpi_filter = 'waitlisted_seat_released' THEN
    v_registration_status := ARRAY['WAITLISTED']; v_seat_released := true;
  ELSIF v_kpi_category = 'waitlisted' AND v_kpi_filter = 'waitlisted_seat_pending' THEN
    v_registration_status := ARRAY['WAITLISTED']; v_seat_released := false; v_waitlist_category := 'after';
  ELSIF v_kpi_category = 'waitlisted' THEN
    v_registration_status := ARRAY['WAITLISTED'];
  ELSIF v_kpi_category = 'swapRequests' THEN
    v_swap_types := ARRAY['WANTS_SWAP'];
  ELSIF v_kpi_category = 'regPending' THEN
    v_approval_status := ARRAY['ON_HOLD', 'REJECTED'];
  ELSIF v_kpi_category = 'registrations' AND v_kpi_filter = 'approvedSeekers' THEN
    v_approval_status := ARRAY['APPROVED'];
  ELSIF v_kpi_category = 'registrations' AND v_kpi_filter = 'newSeekersPending' THEN
    v_approval_status := ARRAY['PENDING'];
  ELSIF v_kpi_category = 'registrations' AND v_kpi_filter = 'rejected' THEN
    v_approval_status := ARRAY['REJECTED'];
  ELSIF v_kpi_category = 'registrations' AND v_kpi_filter = 'onHold' THEN
    v_approval_status := ARRAY['ON_HOLD'];
  ELSIF v_kpi_category = 'registrations' AND v_kpi_filter = 'regPending' THEN
    v_approval_status := ARRAY['ON_HOLD', 'REJECTED'];
  ELSIF v_kpi_category = 'registrations' AND v_kpi_filter = 'cancelled' THEN
    v_registration_status := ARRAY['CANCELLED'];
  ELSIF v_kpi_category IN ('blessed', 'programs') THEN
    v_approval_status := ARRAY['APPROVED'];
    IF v_kpi_filter = 'paymentPending' THEN v_payment_status := ARRAY['payment_pending'];
    ELSIF v_kpi_filter = 'paymentComplete' THEN v_payment_status := ARRAY['payment_completed'];
    ELSIF v_kpi_filter = 'invoicePending' THEN v_invoice_status := ARRAY['invoice_pending'];
    ELSIF v_kpi_filter = 'invoiceComplete' THEN v_invoice_status := ARRAY['invoice_completed'];
    ELSIF v_kpi_filter = 'travelPending' THEN v_travel_status := ARRAY['travel_pending'];
    ELSIF v_kpi_filter = 'travelComplete' THEN v_travel_status := ARRAY['travel_completed'];
    ELSIF v_kpi_filter = 'swapRequests' THEN v_swap_types := ARRAY['WANTS_SWAP'];
    ELSIF v_kpi_filter = 'cancelled' THEN v_registration_status := ARRAY['CANCELLED'];
    END IF;
  END IF;

  RETURN QUERY
  SELECT * FROM vw_hdb_registration_report v
  WHERE
    (v_program_id           IS NULL OR v.program_id           = v_program_id)
    AND (v_program_session_id   IS NULL OR v.program_session_id   = v_program_session_id)
    AND (array_length(v_registration_status, 1) IS NULL OR v."registrationStatus" = ANY(v_registration_status))
    AND (array_length(v_approval_status, 1)     IS NULL OR v."approvalStatus"     = ANY(v_approval_status))
    AND (array_length(v_gender, 1)              IS NULL OR v.gender               = ANY(v_gender))
    AND (array_length(v_payment_mode, 1)        IS NULL OR v."paymentMode"        = ANY(v_payment_mode))
    AND (array_length(v_travel_plan, 1)         IS NULL OR v."travelPlanStatus"   = ANY(v_travel_plan))
    AND (array_length(v_location, 1)            IS NULL OR v.city                 = ANY(v_location))
    AND (v_seat_released        IS NULL OR v.reserved_link         = v_seat_released)
    AND (v_allocated_program_id IS NULL OR v.allocated_program_id  = v_allocated_program_id)
    AND (v_allocated_session_id IS NULL OR v.allocated_session_id  = v_allocated_session_id)
    AND (v_created_from         IS NULL OR v."createdAt"           >= v_created_from)
    AND (v_created_to           IS NULL OR v."createdAt"           <= v_created_to)
    AND (array_length(v_payment_status, 1) IS NULL OR v.payment_category      = ANY(v_payment_status))
    AND (array_length(v_invoice_status, 1) IS NULL OR v.invoice_category      = ANY(v_invoice_status))
    AND (array_length(v_travel_status, 1)  IS NULL OR v.travel_overall_status = ANY(v_travel_status))
    AND (array_length(v_goodies_status, 1) IS NULL OR v."goodiesStatus"       = ANY(v_goodies_status))
    AND (v_organisation IS NULL
      OR (v_organisation = 'yes' AND v.seeker_user_type = 'ORG')
      OR (v_organisation = 'no'  AND (v.seeker_user_type IS NULL OR v.seeker_user_type != 'ORG')))
    AND (v_free_seat IS NULL
      OR (v_free_seat = 'yes' AND v."isFreeSeat" = true)
      OR (v_free_seat = 'no'  AND v."isFreeSeat" = false))
    AND (v_waitlist_category IS NULL
      OR (v_waitlist_category = 'before' AND v.waiting_list_seq_number IS NULL)
      OR (v_waitlist_category = 'after'  AND v.waiting_list_seq_number IS NOT NULL))
    AND (v_defaulter_status IS NULL
      OR (v_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 (v_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 (array_length(v_swap_types, 1) IS NULL OR (v."swapType" = ANY(v_swap_types) AND v."swapStatus" = 'ACTIVE'))
    AND (v_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 = v_preferred_program_id
            AND pref.priority_order = 1 AND pref.deleted_at IS NULL))
    AND (v_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 = v_preferred_session_id
            AND pref.priority_order = 1 AND pref.deleted_at IS NULL))
    AND (array_length(v_preferred_programs, 1) IS NULL OR EXISTS (
          SELECT 1 FROM hdb_preference pref
          WHERE pref.registration_id = v.reg_id AND pref.preferred_program_id = ANY(v_preferred_programs)
            AND pref.priority_order = 1 AND pref.deleted_at IS NULL))
    AND (array_length(v_experience_tags, 1) 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(v_experience_tags)))
    AND (array_length(v_age_ranges, 1) IS NULL OR (
          v.dob_raw IS NOT NULL AND (
            ('0-20'  = ANY(v_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 0  AND 20)  OR
            ('21-30' = ANY(v_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 21 AND 30) OR
            ('31-50' = ANY(v_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 31 AND 50) OR
            ('51-65' = ANY(v_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) BETWEEN 51 AND 65) OR
            ('>65'   = ANY(v_age_ranges) AND DATE_PART('year', AGE(v.dob_raw)) > 65))))
    AND (v_hdb_min IS NULL OR v.no_of_hdbs_raw >= v_hdb_min)
    AND (v_hdb_max IS NULL OR v.no_of_hdbs_raw <= v_hdb_max)
    AND (array_length(v_rm_contact, 1) IS NULL OR v.reg_id IN (
          SELECT a.registration_id FROM hdb_registration_approval a
          WHERE a.rm_contact = ANY(v_rm_contact) AND a.deleted_at IS NULL))
    AND (v_rm_rating IS NULL OR (
          SELECT AVG(r.rating) FROM hdb_program_registration_rm_rating r
          WHERE r.program_registration_id = v.reg_id AND r.deleted_at IS NULL) >= v_rm_rating)
    AND (array_length(v_recommendation, 1) IS NULL OR (
          ('none' = ANY(v_recommendation) AND NOT EXISTS (
            SELECT 1 FROM hdb_program_registration_recommendations rec
            WHERE rec.registration_id = v.reg_id AND rec.deleted_at IS NULL))
          OR EXISTS (
            SELECT 1 FROM hdb_program_registration_recommendations rec
            WHERE rec.registration_id = v.reg_id AND rec.deleted_at IS NULL
              AND rec.recommendation_key = ANY(v_recommendation))))
    AND (v_swap_preferred_prog IS NULL OR EXISTS (
          SELECT 1 FROM hdb_program_registration_swap sw
          JOIN hdb_swap_requested_program srp ON srp.swap_request_id = sw.id
          WHERE sw.program_registration_id = v.reg_id AND sw.status = 'ACTIVE'
            AND srp.program_id = v_swap_preferred_prog
          ORDER BY srp.id ASC LIMIT 1))
    AND (v_swap_demand_prog IS NULL OR EXISTS (
          SELECT 1 FROM hdb_program_registration_swap sw
          JOIN hdb_swap_requested_program srp ON srp.swap_request_id = sw.id
          WHERE sw.program_registration_id = v.reg_id AND sw.status = 'ON_HOLD'
            AND sw.swap_requirement = 'SWAP_DEMAND'
            AND srp.program_id = v_swap_demand_prog
          ORDER BY sw.id DESC LIMIT 1))
    AND (v_pending_program_id IS NULL OR (
          EXISTS (
            SELECT 1 FROM hdb_preference pref
            WHERE pref.registration_id = v.reg_id AND pref.preferred_program_id = v_pending_program_id
              AND pref.deleted_at IS NULL)
          AND v."approvalStatus" = 'REJECTED')
         OR (EXISTS (
            SELECT 1 FROM hdb_program_registration_swap sw
            JOIN hdb_swap_requested_program srp ON srp.swap_request_id = sw.id
            WHERE sw.program_registration_id = v.reg_id AND sw.status = 'ON_HOLD'
              AND sw.swap_requirement = 'SWAP_DEMAND' AND srp.program_id = v_pending_program_id)
          AND v."approvalStatus" = 'ON_HOLD'));
END;
$$;
```

> **Note on numberOfHdbs:** Range strings (`0-5`, `>10`, `=5`) are parsed in the service layer into `hdbMin`/`hdbMax` integers before building the filters JSON.

### View vs Materialized View

**Decision: Plain view.**

| Factor | Assessment |
|---|---|
| Data freshness | Reports must reflect current data |
| Query frequency | On-demand only — no caching benefit |
| Table size | Filters always narrow the result set significantly |
| Join complexity | LATERAL joins are expensive but acceptable for one-off generation |

---

## Migration

```typescript
// src/migrations/<timestamp>-CreateRegistrationReportViewAndFunction.ts
import { MigrationInterface, QueryRunner } from 'typeorm'

export class CreateRegistrationReportViewAndFunction1749999999999
  implements MigrationInterface
{
  async up(queryRunner: QueryRunner): Promise<void> {
    // View first — function depends on it
    await queryRunner.query(`CREATE VIEW vw_hdb_registration_report AS ...`)
    await queryRunner.query(`CREATE OR REPLACE FUNCTION fn_generate_registration_report(...) ...`)
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    // Function first — view cannot be dropped while function depends on it
    await queryRunner.query(`DROP FUNCTION IF EXISTS fn_generate_registration_report`)
    await queryRunner.query(`DROP VIEW IF EXISTS vw_hdb_registration_report`)
  }
}
```

---

## Module Structure

```text
src/reports/
├── reports.module.ts
├── reports.controller.ts
├── reports.service.ts
├── reports.repository.ts
├── registry/
│   └── report-field.registry.ts
├── types/
│   └── report-field.types.ts
├── utils/
│   └── report-formatter.util.ts
└── dto/
    └── generate-report.dto.ts
```

---

## Implementation Checklist

1. Verify all table names against actual DB schema
2. Write and test view SQL directly in psql before migration
3. Write and test function SQL directly in psql
4. Create migration file manually (no CLI generator)
5. Add `ReportsModule` to `AppModule`
6. Add error code `INVALID_REPORT_FIELDS` to `error-string-constants.ts`
7. Confirm `ProgramModule` exports `ProgramRepository`
8. Any new filter added to `findRegistrations` must also be added to the function SQL + `ReportFilterDto`

### Test each filter type

| Filter | Test value |
|---|---|
| `registrationStatus` | `["approved"]` |
| `approvalStatus` | `["approved"]` |
| `paymentStatus` | `["payment_completed"]` — tests derived column |
| `invoiceStatus` | `["invoice_completed"]` — tests derived column |
| `travelStatus` | `["travel_completed"]` — tests derived column |
| `defaulterStatus` | `"non_defaulter"` — tests EXISTS subquery |
| `experienceTags` | any key — tests EXISTS + join |
| `age` | `["21-30"]` — tests date range |
| `numberOfHdbs` | `["0-5"]` — tests int range parsing |
| `kpiCategory` + `kpiFilter` | `"payment"` + `"paymentComplete"` — tests KPI translation |
| `createdFrom` + `createdTo` | date strings |