-- ============================================================
-- Migration: fn_get_report_field_definitions
--
-- Returns metadata for every selectable report field:
--   key, alias (view column), label, group, display order,
--   format, dependsOn, requiredFlags, conditionalFormat
--
-- Used by GET /reports/fields and POST /reports/generate
-- instead of the hardcoded TypeScript registry.
-- ============================================================

DROP FUNCTION IF EXISTS fn_get_report_field_definitions;

CREATE OR REPLACE FUNCTION fn_get_report_field_definitions()
RETURNS TABLE(
  key                TEXT,
  alias              TEXT,
  label              TEXT,
  "group"            TEXT,
  "order"            INT,
  format             TEXT,
  "dependsOn"        TEXT,
  "requiredFlags"    TEXT[],
  "conditionalFormat" JSONB
)
STABLE LANGUAGE sql AS $$
  SELECT
    t.key, t.alias, t.label, t.grp, t.ord, t.fmt, t.dep, t.flags, t.cond_fmt
  FROM (VALUES

    -- ── Registration ──────────────────────────────────────────────────────────
    ('regId',                  'reg_id',                        'Registration ID',              'Registration', 1,   'number',    NULL::TEXT, NULL::TEXT[],                                       NULL::JSONB),
    ('fullName',               'fullName',                      'Full Name',                    'Registration', 2,   NULL,        NULL,       NULL,                                               NULL),
    ('email',                  'email',                         'Email',                        'Registration', 3,   NULL,        NULL,       NULL,                                               NULL),
    ('phone',                  'phone',                         'Mobile',                       'Registration', 4,   NULL,        NULL,       NULL,                                               NULL),
    ('alternatePhoneNumber',   'alternatePhoneNumber',          'Alternate Phone',              'Registration', 5,   NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('phoneNumberType',        'phoneNumberType',               'Phone Type',                   'Registration', 6,   'title-case',NULL,       ARRAY['isHdbMsd'],                                  NULL),
    ('phoneNumberRelation',    'phoneNumberRelation',           'Phone Relation',               'Registration', 7,   'title-case',NULL,       ARRAY['isHdbMsd'],                                  NULL),
    ('gender',                 'gender',                        'Gender',                       'Registration', 8,   'title-case',NULL,       NULL,                                               NULL),
    ('dob',                    'dob',                           'Date of Birth',                'Registration', 9,   'date',      NULL,       NULL,                                               NULL),
    ('city',                   'city',                          'City',                         'Registration', 10,  NULL,        NULL,       NULL,                                               NULL),
    ('otherCityName',          'otherCityName',                 'Other City',                   'Registration', 11,  NULL,        'city',     NULL,                                               NULL),
    ('country',                'country',                       'Country',                      'Registration', 12,  'title-case',NULL,       NULL,                                               NULL),
    ('personType',             'personType',                    'Person Type',                  'Registration', 13,  'title-case',NULL,       NULL,                                               NULL),
    ('program',                'programName',                   'Program Name',                 'Registration', 14,  NULL,        NULL,       NULL,                                               NULL),
    ('registrationSeqNumber',  'registrationSeqNumber',         'Registration Seq No.',         'Registration', 15,  NULL,        NULL,       NULL,                                               NULL),
    ('registrationDate',       'registrationDate',              'Registration Date',            'Registration', 16,  'datetime',  NULL,       NULL,                                               NULL),
    ('registrationStatus',     'registrationStatus',            'Registration Status',          'Registration', 17,  'seeker-status', NULL, NULL,                                                NULL),
    ('registrationMode',       'registrationMode',              'Registration Mode',            'Registration', 18,  'title-case',NULL,       NULL,                                               NULL),
    ('seatAllocated',          'seatAllocated',                 'Seat Allocated',               'Registration', 19,  'boolean',   NULL,       NULL,                                               NULL),
    ('isFreeSeat',             'isFreeSeat',                    'Free Seat',                    'Registration', 20,  'boolean',   NULL,       NULL,                                               NULL),
    ('numberOfHdbs',           'numberOfHdbs',                  'Number of HDBs',               'Registration', 21,  'number',    NULL,       ARRAY['isHdbMsd'],                                  NULL),
    ('lastHdbAttended',        'lastHdbAttended',               'Last HDB Attended',            'Registration', 22,  NULL,        NULL,       ARRAY['isHdbMsd'],                                  NULL),
    ('hdbAssociationSince',    'hdbAssociationSince',           'HDB Association Since',        'Registration', 23,  NULL,        NULL,       ARRAY['isHdbMsd'],                                  NULL),
    ('isMahatriaChoice',       'isMahatriaChoice',              'Mahatria Choice',              'Registration', 24,  'boolean',   NULL,       ARRAY['isHdbMsd'],                                  NULL),
    ('songPreferences',        'songPreferences',               'Song Preferences',             'Registration', 25,  'json',      NULL,       ARRAY['isHdbMsd'],                                  NULL),
    ('rmContact',              'rmContact',                     'RM Contact',                   'Registration', 26,  NULL,        NULL,       NULL,                                               NULL),

    -- ── Seeker ────────────────────────────────────────────────────────────────
    ('userType',               'userType',                      'User Type',                    'Seeker',       27,  'title-case',NULL,       NULL,                                               NULL),
    ('isDefaulter',            'isDefaulter',                   'Defaulter',                    'Seeker',       28,  'boolean',   NULL,       NULL,                                               NULL),

    -- ── Parental Form ─────────────────────────────────────────────────────────
    ('parentalFormStatus',     'parentalFormStatus',            'Parental Form Status',         'Parental Form',29,  'title-case',NULL,       ARRAY['parentalConsentEnabled'],                     NULL),
    ('parentalFormUploadedAt', 'parentalFormUploadedAt',        'Parental Form Uploaded At',    'Parental Form',30,  'datetime',  NULL,       ARRAY['parentalConsentEnabled'],                     NULL),
    ('parentalFormAdminNotes', 'parentalFormAdminNotes',        'Parental Form Admin Notes',    'Parental Form',31,  NULL,        NULL,       ARRAY['parentalConsentEnabled'],                     NULL),

    -- ── Room ──────────────────────────────────────────────────────────────────
    ('preferredRoomMate',      'preferredRoomMate',             'Preferred Roommate',           'Room',         32,  NULL,        NULL,       ARRAY['requiresResidence'],                          NULL),
    ('roomAllocated',          'roomAllocated',                 'Room Allocated',               'Room',         33,  'boolean',   NULL,       ARRAY['requiresResidence'],                          NULL),

    -- ── Waitlist ──────────────────────────────────────────────────────────────
    ('waitListFormattedSeqNo', 'waitListRegistrationSeqNumber', 'Waitlist Formatted Seq No.',   'Waitlist',     34,  NULL,        NULL,       ARRAY['waitlistApplicable'],                         NULL),
    ('reservedLink',           'reservedLink',                  'Seat Released',                'Waitlist',     35,  'boolean',   NULL,       ARRAY['waitlistApplicable'],                         NULL),
    ('subStatus',              'subStatus',                     'Sub Status',                   'Waitlist',     36,  'title-case',NULL,       ARRAY['waitlistApplicable'],                         NULL),

    -- ── Cancellation ──────────────────────────────────────────────────────────
    ('cancellationDate',       'cancellationDate',              'Cancellation Date',            'Cancellation', 37,  'datetime',  NULL,       NULL,                                               NULL),
    ('cancelledBy',            'cancelledBy',                   'Cancelled By',                 'Cancellation', 38,  NULL,        NULL,       NULL,                                               NULL),
    ('cancellationReason',     'cancellationReason',            'Cancellation Reason',          'Cancellation', 39,  'title-case',NULL,       NULL,                                               NULL),
    ('cancellationComments',   'cancellationComments',          'Cancellation Comments',        'Cancellation', 40,  NULL,        NULL,       NULL,                                               NULL),

    -- ── Approval ──────────────────────────────────────────────────────────────
    ('approvalDate',           'approvalDate',                  'Date of Blessing',             'Approval',     42,  'datetime',  NULL,       ARRAY['requiresApproval'],                          NULL),
    ('allocatedProgramName',   'allocatedProgramName',          'Allocated Program',            'Approval',     43,  NULL,        NULL,       ARRAY['requiresApproval'],                          NULL),
    ('programPreferences',     'programPreferences',            'Choices',                      'Approval',     44,  'json',      NULL,       ARRAY['requiresApproval','isGroupedProgram'],        NULL),

    -- ── RM ────────────────────────────────────────────────────────────────────
    ('rmReview',               'rmReview',                      'RM Review',                    'RM',           45,  NULL,        NULL,       ARRAY['requiresApproval'],                          NULL),
    ('rmRating',               'rmRating',                      'RM Rating',                    'RM',           46,  'number',    NULL,       ARRAY['requiresApproval'],                          NULL),
    ('rmRecommendation',       'rmRecommendation',              'Recommendation Level',         'RM',           47,  'title-case',NULL,       ARRAY['requiresApproval'],                          NULL),
    ('isRecommended',          'isRecommended',                 'Is Recommended',               'RM',           48,  'boolean',   NULL,       ARRAY['requiresApproval'],                          NULL),
    ('recommendationComments', 'recommendationComments',        'RM Comments',                  'RM',           49,  NULL,        NULL,       ARRAY['requiresApproval'],                          NULL),

    -- ── Swap ──────────────────────────────────────────────────────────────────
    ('swapType',               'swapType',                      'Swap Type',                    'Swap',         50,  'title-case',NULL,       ARRAY['isGroupedProgram'],                          NULL),
    ('swapStatus',             'swapStatus',                    'Swap Status',                  'Swap',         51,  'swap-status',NULL,      ARRAY['isGroupedProgram'],                          NULL),
    ('swapRequirement',        'swapRequirement',               'Swap Requirement',             'Swap',         52,  'title-case',NULL,       ARRAY['isGroupedProgram'],                          NULL),
    ('swapComment',            'swapComment',                   'Swap RM Comments',             'Swap',         53,  NULL,        NULL,       ARRAY['isGroupedProgram'],                          NULL),
    ('swapCurrentProgram',     'swapCurrentProgram',            'Current Program (Swap)',       'Swap',         54,  NULL,        NULL,       ARRAY['isGroupedProgram'],                          NULL),
    ('swapRequestedPrograms',  'swapRequestedPrograms',         'Swap Request To',              'Swap',         55,  NULL,        NULL,       ARRAY['isGroupedProgram'],                          NULL),

    -- ── Payment ───────────────────────────────────────────────────────────────
    ('originalAmount',         'originalAmount',                'Fee Amount',                   'Payment',      56,  'currency',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('gstEighteen',            'gstEighteen',                   'GST 18%',                      'Payment',      57,  'currency',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('paymentSubTotal',        'paymentSubTotal',               'Total amount',                 'Payment',      58,  'currency',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('paymentTds',             'paymentTds',                    'TDS',                          'Payment',      59,  'currency',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('amountReceived',         'amountReceived',                'Amount Received',              'Payment',      60,  'currency',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('paymentStatus',          'paymentStatus',                 'Payment Status',               'Payment',      61,  'payment-status', NULL,  ARRAY['requiresPayment'],                           NULL),
    ('paymentMode',            'paymentModeDisplay',            'Payment Mode',                 'Payment',      62,  'title-case',NULL,       ARRAY['requiresPayment'],                           NULL),
    ('razorpayId',             'razorpayId',                    'Order ID (Razor pay)',         'Payment',      63,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('razorpayPaymentId',      'razorpayPaymentId',             'Payment ID (Razor pay)',       'Payment',      64,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('offlineChequeNo',        'offlineChequeNo',               'Cheque / DD No.',              'Payment',      65,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('offlineChequeDate',      'offlineChequeDate',             'Cheque / DD Date',             'Payment',      66,  'date',      NULL,       ARRAY['requiresPayment'],                           NULL),
    ('offlineUtrNumber',       'offlineUtrNumber',              'UTR / Reference No.',          'Payment',      67,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('offlineBankTransferDate','offlineBankTransferDate',        'Bank Transfer Date',           'Payment',      68,  'date',      NULL,       ARRAY['requiresPayment'],                           NULL),
    ('paymentDate',            'paymentDate',                   'Payment Date',                 'Payment',      69,  'datetime',  NULL,       ARRAY['requiresPayment'],                           NULL),
    -- Payment fields beyond the legacy finance export, kept after the section:
    ('markAsReceivedDate',     'markAsReceivedDate',            'Payment Received Date',        'Payment',      70,  'date',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('offlineBankName',        'offlineBankName',               'Bank Name',                    'Payment',      71,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),

    -- ── Proforma ──────────────────────────────────────────────────────────────
    ('proformaSeqNumber',      'proformaSeqNumber',             'Proforma Number',              'Proforma',     73,  NULL,        NULL,       ARRAY['isProformaAllowed'],                          NULL),
    ('proformaInvoiceName',    'proformaInvoiceName',           'Proforma Name',                'Proforma',     74,  NULL,        NULL,       ARRAY['isProformaAllowed'],                          NULL),
    ('proformaInvoiceAddress', 'proformaInvoiceAddress',        'Proforma Address',             'Proforma',     75,  NULL,        NULL,       ARRAY['isProformaAllowed'],                          NULL),
    ('proformaZip',            'proformaZip',                   'Proforma Pincode',             'Proforma',     76,  NULL,        NULL,       ARRAY['isProformaAllowed'],                          NULL),
    ('proformaIsGstRegistered','proformaIsGstRegistered',       'Proforma GST Registered',      'Proforma',     77,  'boolean',   NULL,       ARRAY['isProformaAllowed'],                          NULL),
    ('proformaGstNumber',      'proformaGstNumber',             'Proforma GST Number',          'Proforma',     78,  NULL,        NULL,       ARRAY['isProformaAllowed'],                          NULL),

    -- ── Invoice ───────────────────────────────────────────────────────────────
    ('invoiceGstNumber',       'invoiceGstNumber',              'GSTIN',                        'Invoice',      79,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceTanNumber',       'invoiceTanNumber',              'TAN',                          'Invoice',      80,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceNumber',          'invoiceNumber',                 'Invoice Number',               'Invoice',      81,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceIssuedDate',      'invoiceIssuedDate',             'Invoice Date',                 'Invoice',      82,  'date',      NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceName',            'invoiceName',                   'Invoice Name',                 'Invoice',      83,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceEmail',           'invoiceEmail',                  'Invoice Email',                'Invoice',      84,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceAddress',         'invoiceAddress',                'Invoice Address',              'Invoice',      85,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoicePinCode',         'invoicePinCode',                'Invoice Pincode',              'Invoice',      86,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('gstinStatus',            'gstinStatus',                   'GSTIN Status',                 'Invoice',      87,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('einvoiceAckNumber',      'einvoiceAckNumber',             'E-Invoice Ack Number',         'Invoice',      88,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceStatus',          'invoiceStatus',                 'Invoice Status',               'Invoice',      89,  'invoice-status', NULL,  ARRAY['requiresPayment'],                           NULL),
    ('einvoiceAckDate',        'einvoiceAckDate',               'E-Invoice Ack Date',           'Invoice',      90,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceModifiedBy',      'invoiceModifiedBy',             'Modified User',                'Invoice',      91,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceModifiedTime',    'invoiceModifiedTime',           'Modified Time',                'Invoice',      92,  'datetime',  NULL,       ARRAY['requiresPayment'],                           NULL),
    -- Invoice fields beyond the legacy finance export, kept after the section:
    ('invoiceType',            'invoiceType',                   'Invoice Type',                 'Invoice',      93,  'title-case',NULL,       ARRAY['requiresPayment'],                           NULL),
    ('invoiceTdsApplicable',   'invoiceTdsApplicable',          'TDS Applicable',               'Invoice',      94,  'boolean',   NULL,       ARRAY['requiresPayment'],                           NULL),
    ('tdsAmount',              'tdsAmount',                     'TDS Amount',                   'Invoice',      95,  'currency',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('handoverDate',           'handoverDate',                  'Invoice Handover Date',        'Invoice',      96,  'datetime',  NULL,       ARRAY['requiresPayment'],                           NULL),
    ('handoverTo',             'handoverTo',                    'Invoice Handover To',          'Invoice',      97,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('einvoiceStatus',         'einvoiceStatus',                'E-Invoice Status',             'Invoice',      98,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('einvoiceStatusFormatted','einvoiceStatusFormatted',       'E-Invoice Formatted Status',   'Invoice',      99,  NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('einvoiceQrLink',         'einvoiceQrLink',                'E-Invoice QR Link',            'Invoice',      100, NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),
    ('einvoiceRefNumber',      'einvoiceRefNumber',             'E-Invoice Ref Number',         'Invoice',      101, NULL,        NULL,       ARRAY['requiresPayment'],                           NULL),

    -- ── Travel Info ───────────────────────────────────────────────────────────
    ('travelInfoStatus',       'travelInfoStatus',              'Travel Info Status',           'Travel Info',  103, 'title-case',NULL,       ARRAY['involvesTravel'],                            NULL),
    ('idType',                 'idType',                        'ID Type',                      'Travel Info',  104, 'title-case',NULL,       ARRAY['involvesTravel'],                            NULL),
    ('idNumber',               'idNumber',                      'ID Number',                    'Travel Info',  105, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('idFrontPicture',         'idFrontPicture',                'ID Front Picture',             'Travel Info',  106, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('idBackPicture',          'idBackPicture',                 'ID Back Picture',              'Travel Info',  107, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('internationalIdPicture', 'internationalIdPicture',        'International ID',             'Travel Info',  108, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('passportCopy',           'passportCopy',                  'Passport Copy',                'Travel Info',  109, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('visaCopy',               'visaCopy',                      'Visa Copy',                    'Travel Info',  110, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('tshirtSize',             'tshirtSize',                    'T-Shirt Size (Travel)',        'Travel Info',  111, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),

    -- ── Travel Plan ───────────────────────────────────────────────────────────
    ('travelPlanStatus',       'travelPlanStatus',              'Travel Plan Status',           'Travel Plan',  112, 'title-case',NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardAirline',          'onwardAirline',                 'Onward Airline',               'Travel Plan',  113, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardFlightNumber',     'onwardFlightNumber',            'Onward Flight No.',            'Travel Plan',  114, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardArrivalDatetime',  'onwardArrivalDatetime',         'Onward Arrival',               'Travel Plan',  115, 'datetime',  NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardArrivalFrom',      'onwardArrivalFrom',             'Coming From',                  'Travel Plan',  116, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardDepartureLocation','onwardDepartureLocation',       'Departure Location',           'Travel Plan',  117, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardTerminal',         'onwardTerminal',                'Onward Terminal',              'Travel Plan',  118, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardAdditionalInfo',   'onwardAdditionalInfo',          'Onward Additional Info',       'Travel Plan',  119, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('onwardJourneyTicket',    'onwardJourneyTicket',           'Onward Ticket URL',            'Travel Plan',  120, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnTravelType',       'returnTravelType',              'Return Travel Type',           'Travel Plan',  121, 'title-case',NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnAirline',          'returnAirline',                 'Return Airline',               'Travel Plan',  122, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnFlightNumber',     'returnFlightNumber',            'Return Flight No.',            'Travel Plan',  123, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnDepartureDatetime','returnDepartureDatetime',       'Return Departure',             'Travel Plan',  124, 'datetime',  NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnDepartureTo',      'returnDepartureTo',             'Going To',                     'Travel Plan',  125, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnTerminal',         'returnTerminal',                'Return Terminal',              'Travel Plan',  126, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnAdditionalInfo',   'returnAdditionalInfo',          'Return Additional Info',       'Travel Plan',  127, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('returnJourneyTicket',    'returnJourneyTicket',           'Return Ticket URL',            'Travel Plan',  128, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('pickupLocation',         'pickupLocation',                'Pickup Location',              'Travel Plan',  129, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('pickupTime',             'pickupTime',                    'Pickup Time',                  'Travel Plan',  130, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('checkinAt',              'checkinAt',                     'Check-in At',                  'Travel Plan',  131, 'datetime',  NULL,       ARRAY['involvesTravel'],                            NULL),
    ('checkinTime',            'checkinTime',                   'Check-in Time',                'Travel Plan',  132, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),
    ('checkinLocation',        'checkinLocation',               'Check-in Location',            'Travel Plan',  133, NULL,        NULL,       ARRAY['involvesTravel'],                            NULL),

    -- ── Goodies ───────────────────────────────────────────────────────────────
    ('goodiesStatus',              'goodiesStatus',             'Goodies Status',               'Goodies',      134, 'title-case',NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesNotebook',            'goodiesNotebook',           'Notebook',                     'Goodies',      135, 'boolean',   NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesFlask',               'goodiesFlask',              'Flask',                        'Goodies',      136, 'boolean',   NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesTshirt',              'goodiesTshirt',             'T-Shirt',                      'Goodies',      137, 'boolean',   NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesTshirtSize',          'goodiesTshirtSize',         'T-Shirt Size',                 'Goodies',      138, NULL,        NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesJacket',              'goodiesJacket',             'Jacket',                       'Goodies',      139, 'boolean',   NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesJacketSize',          'goodiesJacketSize',         'Jacket Size',                  'Goodies',      140, NULL,        NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesRatriaPillar',        'goodiesRatriaPillar',       'Ratria Pillar',                'Goodies',      141, 'boolean',   NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesRatriaPillarLocation','goodiesRatriaPillarLocation','Ratria Pillar Location',      'Goodies',      142, NULL,        NULL,       ARRAY['hasGoodies'],                                NULL),
    ('goodiesRatriaPillarOtherLoc','goodiesRatriaPillarOtherLoc','Ratria Pillar Other Location','Goodies',     143, NULL,        'goodiesRatriaPillarLocation', ARRAY['hasGoodies'],             NULL),

    -- ── Approval / Registration columns from the legacy finance export ─────────
    ('blessed',                'blessed',                       'Blessed',                      'Approval',     144, NULL,        NULL,       ARRAY['requiresApproval'],                          NULL),
    ('offeredWithPeriod',      'offeredWithPeriod',             'HDB / MSD Offered',            'Approval',     145, NULL,        NULL,       ARRAY['requiresApproval'],                          NULL),
    ('programNameWithPeriod',  'programNameWithPeriod',         'Programme Name with period',   'Registration', 146, NULL,        NULL,       NULL,                                               NULL)

  ) AS t(key, alias, label, grp, ord, fmt, dep, flags, cond_fmt)
  ORDER BY t.ord
$$;
