import { Injectable, Logger, Inject } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource, In, IsNull, Not } from 'typeorm';
import { MergeInfoAnswerLocationMap, CommunicationTemplates } from 'src/common/entities';
import { CommunicationTemplatesRepository } from '../repositories/communication-templates.repository';
import { CommunicationTemplateAccessKeyEnum } from 'src/common/enum/communication-template-access-key.enum';
import { CommunicationTypeEnum } from 'src/common/enum/communication-type.enum';
import { DataFormatter } from 'src/common/utils/merge-info-data-formatter.util';
import {
  getRegistrationColumnName,
  SEEKER_FE_EDIT_REGISTRATION_PATH,
  SEEKER_FE_REG_PATH,
  SEEKER_FE_SESSION_JOIN_PATH,
} from 'src/common/constants/constants';
import {
  generatePaymentLink,
  generateTravelPlanLink,
  formatDateIST,
  formatTimeIST,
  formatDateTimeIST,
  getWeekName,
  deductDaysFromDate,
  formatTime12HourIST,
  formatTo12HourClock,
} from 'src/common/utils/common.util';
import { PaymentModeEnum } from 'src/common/enum/payment-mode.enum';
import { handleKnownErrors } from 'src/common/utils/handle-error.util';
import { ERROR_CODES } from 'src/common/constants/error-string-constants';
import { PaymentStatusEnum } from 'src/common/enum/payment-status.enum';
import { RegistrationStatusEnum } from 'src/common/enum/registration-status.enum';
import { OnlineSessionRegistrationStatus } from 'src/common/enum/online-session-registration-status.enum';
import type { SessionGuidelinesPdfContract } from './session-guidelines-pdf.types';
import { SESSION_GUIDELINES_PDF_SERVICE } from './session-guidelines-pdf.types';

/**
 * Per-send resolution cache for getTemplateWithMergeInfo.
 *
 * A bulk send calls getTemplateWithMergeInfo once per recipient, and most of that work is
 * identical every time: the template row, its merge-field map, and every field flagged
 * `isCommon` — a flag that means exactly "same value for every recipient of this send". Without
 * a cache, an N-recipient send repeats all of it N times, which is what makes a large send slow:
 * the guidelines-PDF field alone costs a DB read plus a fresh S3 presign per recipient, and if
 * that PDF has never been generated (or generation failed) it re-attempts the whole puppeteer
 * render per recipient.
 *
 * Pass ONE cache per (send, channel) — a channel maps to a single template, so the keys need no
 * further qualification. Never share one across sends: it holds resolved values, not just rows.
 *
 * Per-record fields are never cached, and neither is a common field whose value came from the
 * caller's `common_<keyName>` override — that override IS per-recipient on registration-less
 * sends (Common Invite / System Links / General Link), so it is resolved fresh every time.
 */
export interface SendMergeCache {
  /** undefined = not looked up yet; null = looked up and absent. */
  template?: CommunicationTemplates | null;
  mergeFields?: MergeInfoAnswerLocationMap[];
  commonValues: Map<string, any>;
}

/** A fresh cache for one send + channel. See SendMergeCache. */
export function createSendMergeCache(): SendMergeCache {
  return { commonValues: new Map<string, any>() };
}

/**
 * Context object passed to computed functions
 */
interface ComputedFieldContext {
  programId?: number;
  registrationId?: number;
  userId?: number;
  mergeInfo?: Record<string, any>;
  manager?: any;
  fieldKey?: string; // The field key being computed (e.g., 'payment_online_link', 'hdb_dates')
  functionName?: string; // The full function name from sourceColumn (e.g., 'getProgramField:starts_at')
  extraMergeContext?: Record<string, any>; // Additional context data (e.g., preference arrays, OTP, etc.)
  dataType?: string; // Data type from merge field map (e.g., 'date', 'string', 'number')
  formatType?: string; // Format type from merge field map (e.g., 'weekname', 'custom:DD-MM-YYYY')
}

/**
 * Merge-field keyNames whose registration-sourced value has no registration to read when the
 * recipient is registration-less (e.g. a zoom_generated_registrant_link row sent via Common
 * Invite/System Links/General-Link). When context.registrationId is absent, these keyNames read
 * the caller-supplied `common_<keyName>` value from extraMergeContext instead of hitting the DB —
 * letting a template built for real registrations (reg_name/zoom_join_link/meeting_id/
 * meeting_passcode) serve a registration-less recipient without any change to its merge_field_map.
 * `reg_name`'s alias is `common_user_name` (established by the existing Common Invite/System
 * Links convention); every other keyName defaults to `common_<keyName>`, which already matches
 * zoom_join_link/meeting_id/meeting_passcode.
 */
const REGISTRATION_LESS_FIELD_ALIASES: Record<string, string> = {
  reg_name: 'common_user_name',
};

/**
 * Centralized service for handling template merge info processing
 * Eliminates code duplication across registration, invoice, payment, approval, and scheduler services
 * Following coding standard: DRY principle and Separation of Concerns (AGENTS.md)
 */
@Injectable()
export class CommunicationMergeDataService {
  private readonly logger = new Logger(CommunicationMergeDataService.name);
  
  /**
   * Registry of all computed functions
   * Maps function name (from sourceColumn) to actual function
   */
  private computedFunctionRegistry: Record<string, (context: ComputedFieldContext) => Promise<any>>;

  manager: any;

  constructor(
    private readonly communicationTemplatesRepository: CommunicationTemplatesRepository,
    @InjectRepository(MergeInfoAnswerLocationMap)
    private readonly mergeInfoRepository: Repository<MergeInfoAnswerLocationMap>,
    @Inject(DataSource) private readonly dataSource: DataSource,
    @Inject(SESSION_GUIDELINES_PDF_SERVICE)
    private readonly sessionGuidelinesPdfService: SessionGuidelinesPdfContract,
  ) {
    // Initialize manager from dataSource
    this.manager = this.dataSource.manager;

    // Initialize computed function registry
    // Function names must match sourceColumn values in database/template-merge-infos.json
    this.computedFunctionRegistry = {
      'generatePaymentLink': this.generatePaymentLinkField.bind(this),
      'registrationEditLink': this.generateRegistrationEditLinkField.bind(this),
      'formatProgramDateRange': this.formatProgramDateRangeField.bind(this),
      'calculateTotalPaymentAmount': this.calculateTotalPaymentAmountField.bind(this),
      'lastAllocatedProgram': this.getAllocatedProgramNameField.bind(this),
      'allocatedProgramName': this.getAllocatedProgramNameField.bind(this), // Alias for lastAllocatedProgram
      'formatPreferenceForMessage': this.formatPreferenceForMessageField.bind(this),
      'generateOTP': this.generateOTPField.bind(this),
      'currentDate': this.getCurrentDateField.bind(this),
      'getRmContactUserName': this.getRmContactUserNameField.bind(this),
      'getUserFormattedPhone': this.getUserFormattedPhoneField.bind(this),
      'formatAllocatedProgramDateRange': this.formatAllocatedProgramDateRangeField.bind(this),
      'getCoordinatorName': this.getCoordinatorNameField.bind(this),
      'getProgramField': this.getProgramField.bind(this),
      'getAllocatedProgramField': this.getAllocatedProgramField.bind(this),
      'getProgramSessionField': this.getProgramSessionField.bind(this),
      // Target-session fields — resolve THE session a session-communication is being sent
      // for (extraMergeContext.sessionId), falling back to the program's first session when
      // no target is supplied (program-level uses e.g. Welcome).
      'getTargetSessionField': this.getTargetSessionFieldValue.bind(this),
      'getTargetSessionTime': this.getTargetSessionTimeField.bind(this),
      'getTargetSessionLoginTime': this.getTargetSessionLoginTimeField.bind(this),
      'getTargetSessionLoginLeadMinutes': this.getTargetSessionLoginLeadMinutesField.bind(this),
      'getTargetSessionDate': this.getTargetSessionDateField.bind(this),
      'getTargetSessionDayDate': this.getTargetSessionDayDateField.bind(this),
      'getTargetRemainingProgramDates': this.getTargetRemainingProgramDatesField.bind(this),
      'getProgramFinalSessionDate': this.getProgramFinalSessionDateField.bind(this),
      'getProgramFinalButOneSessionDate': this.getProgramFinalButOneSessionDateField.bind(this),
      'getProgramSessionCount': this.getProgramSessionCountField.bind(this),
      'getAbsentSessionsTable': this.getAbsentSessionsTableField.bind(this),
      'getAbsentSessionsList': this.getAbsentSessionsListField.bind(this),
      'getPortalSessionJoinLink': this.getPortalSessionJoinLinkField.bind(this),
      'getWatiPortalSessionJoinLink': this.getWatiPortalSessionJoinLinkField.bind(this),
      'getWatiSessionJoinUrl': this.getWatiSessionJoinUrlField.bind(this),
      'getSessionGuidelinesPdfUrl': this.getSessionGuidelinesPdfUrl.bind(this),
      'getWelcomeGuidelinesPdfUrl': this.getWelcomeGuidelinesPdfUrl.bind(this),
      'getMergeContextValue': this.getMergeContextValueField.bind(this),
      'getTargetOnlineSessionField': this.getTargetOnlineSessionFieldValue.bind(this),
      'getSessionDays': this.getSessionDaysField.bind(this),
      'formatSessionDateRange': this.formatSessionDateRangeField.bind(this),
      'formatSessionDateAndDaysRange': this.formatSessionDateAndDaysRangeField.bind(this),
      'getSessionTime': this.getSessionTimeField.bind(this),
      'getSessionDaysAndTime': this.getSessionDaysAndTimeField.bind(this),
      'firstPara': this.firstParaField.bind(this),
      'getAllocatedProgramStartsMinus10Days': this.getAllocatedProgramStartsMinus10DaysField.bind(this),
      'getCheckInOut': this.getCheckInOutField.bind(this),
      'countPendingPayments': this.countPendingPaymentsField.bind(this),
      'cashDetails': this.cashDetailsField.bind(this),
      'getUserFirstName': this.getUserFirstNameField.bind(this),
      'getUserLastName': this.getUserLastNameField.bind(this),
      'getUserGender': this.getUserGenderField.bind(this),
      'getUserDOB': this.getUserDOBField.bind(this),
      'getRmContactEmail': this.getRmContactEmailField.bind(this),
      'getRmContactMobile': this.getRmContactMobileField.bind(this),
      'getOrgName': this.getOrgNameField.bind(this),
      'getOrgHelplineNumber': this.getOrgHelplineNumberField.bind(this),
      'getOrgSupportEmail': this.getOrgSupportEmailField.bind(this),
      'generateApproveLink': this.generateApproveLinkField.bind(this),
      'generateRejectLink': this.generateRejectLinkField.bind(this),
      'generateViewRegistrationLink': this.generateViewRegistrationLinkField.bind(this),
      'generateTravelPlanLink': this.generateTravelPlanLinkField.bind(this),
    };
  }
  /**
   * Compute cash_details for blessed/allocated program
   * If basePrice > 150, return empty string, else return the cash message
   * Usage: sourceColumn: 'cashDetails'
   */
  private async cashDetailsField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn('cashDetails: Missing registrationId or manager');
      return '';
    }
    try {
        // Get allocated program base price
      const reg = await manager
        .createQueryBuilder()
        .select(['reg.allocated_program_id'])
        .from('hdb_program_registration', 'reg')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();
      let basePrice = 0;
      if (reg?.allocated_program_id) {
        const prog = await manager
          .createQueryBuilder()
          .select(['prog.base_price'])
          .from('program_v1', 'prog')
          .where('prog.id = :programId', { programId: reg.allocated_program_id })
          .getRawOne();
        basePrice = Number(prog?.base_price) || 0;
      }
      if (basePrice > 150) {
        return '';
      }
      return '4. Cash: If you are paying by cash, after paying the cash, please click on the following link and fill in the details. Please note that this simple update by you will ensure that there is no omission or reconciliation issue owing to human error.';
    } catch (error) {
      this.logger.error('Error in cashDetailsField', error.stack);
      return '';
    }
  }
  /**
     * Generic computed function to fetch any field from program_v1 using registrationId
   * Usage: sourceColumn: 'getProgramField:starts_at'
   */
  private async getProgramField(context: ComputedFieldContext): Promise<any> {
    const { registrationId, manager, functionName } = context;
    if (!manager) {
      this.logger.warn('getProgramField: Missing manager');
      return '';
    }
    
    // Extract field name from functionName: 'getProgramField:starts_at' -> 'starts_at'
    let fieldName = '';
    if (functionName && functionName.includes(':')) {
      fieldName = functionName.split(':')[1];
    }
    
    if (!fieldName) {
      this.logger.warn('getProgramField: No fieldName provided in sourceColumn (expected format: getProgramField:field_name)');
      return '';
    }
    
    try {
      // 1. Resolve the programId — from the registration when present, else the explicit
      //    program on the context (program-level sends such as common invite).
      let programId: number | undefined;
      if (registrationId) {
        const reg = await manager
          .createQueryBuilder()
          .select('reg.program_id', 'programId')
          .from('hdb_program_registration', 'reg')
          .where('reg.id = :registrationId', { registrationId })
          .getRawOne();
        programId = reg?.programId;
      }
      if (!programId) {
        programId = context.programId ?? undefined;
      }

      if (!programId) {
        this.logger.warn(`getProgramField: No programId found for registrationId ${registrationId ?? 'none'}`);
        return '';
      }
      
      // 2. Fetch the requested field from program_v1
      const program = await manager
        .createQueryBuilder()
        .select(`program.${fieldName}`, 'value')
        .from('program_v1', 'program')
        .where('program.id = :programId', { programId })
        .getRawOne();
        
      const rawValue = program?.value;
      
      // 3. Handle special formatting for date and day fields
      if (rawValue) {
        // Get dataType from context (from merge field map)
        const { dataType, formatType } = context;
        
        // If formatType is 'weekname', return day name (e.g., 'Monday', 'Tuesday')
        if (formatType === 'weekname' && dataType === 'date') {
          const date = new Date(rawValue);
          return getWeekName(date);
        }
        
        
        // If dataType is 'date', format as DD-MM-YYYY
        if (dataType === 'date' && (rawValue instanceof Date || (typeof rawValue === 'string' && !isNaN(Date.parse(rawValue))))) {
          return formatDateIST(new Date(rawValue).toISOString());
        }

      }
      
      return rawValue ?? '';
    } catch (error) {
      this.logger.error(`Error in getProgramField for field ${fieldName}`, error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Generic computed function to fetch any field from allocated program_v1 using registrationId
   * Usage: sourceColumn: 'getAllocatedProgramField:starts_at'
   * 
   * IMPORTANT: Supports previousAllocatedProgramId via extraMergeContext
   * When allocated_program_id is NULL (e.g., during swap demand), pass previousAllocatedProgramId in extraContext:
   * extraMergeContext: { previousAllocatedProgramId: 1085 }
   */
  private async getAllocatedProgramField(context: ComputedFieldContext): Promise<any> {
    const { registrationId, manager, functionName, extraMergeContext } = context;
    if (!registrationId || !manager) {
      this.logger.warn('getAllocatedProgramField: Missing registrationId or manager');
      return '';
    }
    
    // Extract field name from functionName: 'getAllocatedProgramField:starts_at' -> 'starts_at'
    let fieldName = '';
    if (functionName && functionName.includes(':')) {
      fieldName = functionName.split(':')[1];
    }
    
    if (!fieldName) {
      this.logger.warn('getAllocatedProgramField: No fieldName provided in sourceColumn (expected format: getAllocatedProgramField:field_name)');
      return '';
    }
    
    try {
      let allocatedProgramId: number | undefined;

      // 1. Check extraMergeContext for previousAllocatedProgramId first (used when allocation is cleared)
      if (extraMergeContext?.previousAllocatedProgramId) {
        allocatedProgramId = extraMergeContext.previousAllocatedProgramId;
        this.logger.debug(
          `getAllocatedProgramField: Using previousAllocatedProgramId ${allocatedProgramId} from extraContext for field ${fieldName}`
        );
      } else {
        // 2. Fall back to querying from registration table
        const reg = await manager
          .createQueryBuilder()
          .select('reg.allocated_program_id', 'allocatedProgramId')
          .from('hdb_program_registration', 'reg')
          .where('reg.id = :registrationId', { registrationId })
          .getRawOne();
          
        allocatedProgramId = reg?.allocatedProgramId;
      }
        
      if (!allocatedProgramId) {
        this.logger.warn(`getAllocatedProgramField: No allocatedProgramId found for registrationId ${registrationId}`);
        return '';
      }
      
      // 3. Fetch the requested field from program_v1 using the resolved allocatedProgramId
      const program = await manager
        .createQueryBuilder()
        .select(`program.${fieldName}`, 'value')
        .from('program_v1', 'program')
        .where('program.id = :programId', { programId: allocatedProgramId })
        .getRawOne();
        
      const rawValue = program?.value;
      
      // 3. Handle special formatting for date and day fields
      if (rawValue) {
        // Get dataType from context (from merge field map)
        const { dataType, formatType } = context;
        
        // If formatType is 'weekname', return day name (e.g., 'Monday', 'Tuesday')
        if (formatType === 'weekname' && dataType === 'date') {
          const date = new Date(rawValue);
          return getWeekName(date);
        }
        
        
        // If dataType is 'date', format as DD-MM-YYYY
        if (dataType === 'date' && (rawValue instanceof Date || (typeof rawValue === 'string' && !isNaN(Date.parse(rawValue))))) {
          return formatDateIST(new Date(rawValue).toISOString());
        }
        
      }
      
      return rawValue ?? '';
    } catch (error) {
      this.logger.error(`Error in getAllocatedProgramField for field ${fieldName}`, error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Resolve all program_session ids belonging to a registration's program.
   * Uses the allocated program when present (allocated_program_id), otherwise the
   * registered program (program_id), and honors previousAllocatedProgramId from
   * extraMergeContext when allocation has been cleared (mirrors getAllocatedProgramField).
   * Returns every non-deleted session id ordered by start date.
   */
  private async resolveSessionIds(context: ComputedFieldContext): Promise<number[]> {
    const { registrationId, manager, extraMergeContext } = context;
    if (!manager) {
      return [];
    }

    // Resolve the program whose sessions we list (allocated takes precedence)
    let programId: number | undefined = extraMergeContext?.previousAllocatedProgramId;
    if (!programId && registrationId) {
      const reg = await manager
        .createQueryBuilder()
        .select('reg.allocated_program_id', 'allocatedProgramId')
        .addSelect('reg.program_id', 'programId')
        .from('hdb_program_registration', 'reg')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();

      programId = reg?.allocatedProgramId ?? reg?.programId ?? undefined;
    }

    // Program-level sends (no registration, e.g. common invite) fall back to the explicit
    // program supplied on the context.
    if (!programId) {
      programId = context.programId ?? undefined;
    }

    if (!programId) {
      this.logger.warn(
        `resolveSessionIds: No programId found for registrationId ${registrationId ?? 'none'}`,
      );
      return [];
    }

    const sessions = await manager
      .createQueryBuilder()
      .select('session.id', 'id')
      .from('program_session', 'session')
      .where('session.program_id = :programId', { programId })
      .andWhere('session.deleted_at IS NULL')
      .orderBy('session.starts_at', 'ASC')
      .addOrderBy('session.display_order', 'ASC')
      .getRawMany();

    return sessions.map((s) => Number(s.id)).filter((id) => !isNaN(id));
  }

  /**
   * Generic computed function to fetch any field from program_session using registrationId.
   * Usage: sourceColumn: 'getProgramSessionField:starts_at'
   * Reads from the first session of the registration's program.
   */
  private async getProgramSessionField(context: ComputedFieldContext): Promise<any> {
    const { manager, functionName } = context;

    // Extract field name: 'getProgramSessionField:starts_at' -> 'starts_at'
    let fieldName = '';
    if (functionName && functionName.includes(':')) {
      fieldName = functionName.split(':')[1];
    }

    if (!fieldName) {
      this.logger.warn(
        'getProgramSessionField: No fieldName provided in sourceColumn (expected format: getProgramSessionField:field_name)',
      );
      return '';
    }

    try {
      const [sessionId] = await this.resolveSessionIds(context);
      if (!sessionId) {
        this.logger.warn('getProgramSessionField: No sessionId found for registration');
        return '';
      }

      const session = await manager
        .createQueryBuilder()
        .select(`session.${fieldName}`, 'value')
        .from('program_session', 'session')
        .where('session.id = :sessionId', { sessionId })
        .getRawOne();

      const rawValue = session?.value;

      // Apply the same date/day formatting conventions as getProgramField
      if (rawValue) {
        const { dataType, formatType } = context;

        if (formatType === 'weekname' && dataType === 'date') {
          return getWeekName(new Date(rawValue));
        }

        if (
          dataType === 'date' &&
          (rawValue instanceof Date ||
            (typeof rawValue === 'string' && !isNaN(Date.parse(rawValue))))
        ) {
          return formatDateIST(new Date(rawValue).toISOString());
        }
      }

      return rawValue ?? '';
    } catch (error) {
      this.logger.error(`Error in getProgramSessionField for field ${fieldName}`, error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  // ==================================================================================
  // Target-session fields (Session Communication feature)
  //
  // These resolve the specific session a communication is being sent for, supplied via
  // extraMergeContext.sessionId. When no target is provided (program-level sends such as
  // Welcome), they fall back to the program's first session so program-level templates
  // still resolve sensibly.
  // ==================================================================================

  /**
   * Resolve the target session id: the explicit extraMergeContext.sessionId when present,
   * otherwise the registration's first session (program-level fallback).
   */
  private async resolveTargetSessionId(context: ComputedFieldContext): Promise<number | null> {
    const fromContext = context.extraMergeContext?.sessionId;
    if (fromContext) {
      return Number(fromContext);
    }
    const [first] = await this.resolveSessionIds(context);
    return first ?? null;
  }

  /**
   * Fetch selected columns of the target session as a raw row (keyed by column name).
   */
  private async fetchTargetSession(
    context: ComputedFieldContext,
    columns: string[],
  ): Promise<Record<string, any> | null> {
    const { manager } = context;
    const sessionId = await this.resolveTargetSessionId(context);
    if (!sessionId || !manager) {
      return null;
    }
    const qb = manager
      .createQueryBuilder()
      .from('program_session', 'session')
      .where('session.id = :sessionId', { sessionId });
    columns.forEach((col, index) => {
      if (index === 0) {
        qb.select(`session.${col}`, col);
      } else {
        qb.addSelect(`session.${col}`, col);
      }
    });
    return qb.getRawOne();
  }

  /**
   * Ordered (ascending by start) list of the registration program's session start dates.
   */
  private async fetchOrderedSessionStartDates(context: ComputedFieldContext): Promise<Date[]> {
    const { manager } = context;
    const sessionIds = await this.resolveSessionIds(context);
    if (!sessionIds.length || !manager) {
      return [];
    }
    const rows = await manager
      .createQueryBuilder()
      .select('session.starts_at', 'startsAt')
      .from('program_session', 'session')
      .where('session.id IN (:...sessionIds)', { sessionIds })
      .andWhere('session.starts_at IS NOT NULL')
      .orderBy('session.starts_at', 'ASC')
      .getRawMany();
    return rows
      .map((row) => new Date(row.startsAt))
      .filter((date) => !isNaN(date.getTime()));
  }

  /** Full weekday + date in IST — e.g. "Sunday, 6 July 2025". */
  private formatSessionDayDate(value: any): string {
    const date = new Date(value);
    if (isNaN(date.getTime())) {
      return '';
    }
    const weekday = new Intl.DateTimeFormat('en-GB', {
      timeZone: 'Asia/Kolkata',
      weekday: 'long',
    }).format(date);
    return `${weekday}, ${this.formatSessionDateLong(value)}`;
  }

  /** Date in IST — e.g. "6 July 2025". */
  private formatSessionDateLong(value: any): string {
    const date = new Date(value);
    if (isNaN(date.getTime())) {
      return '';
    }
    return new Intl.DateTimeFormat('en-GB', {
      timeZone: 'Asia/Kolkata',
      day: 'numeric',
      month: 'long',
      year: 'numeric',
    }).format(date);
  }

  /**
   * Any column of the target session. Usage: getTargetSessionField:meeting_link
   * (also meeting_id, meeting_password, name, starts_at). Applies the same date/day
   * formatting conventions as getProgramSessionField when dataType='date'.
   */
  private async getTargetSessionFieldValue(context: ComputedFieldContext): Promise<any> {
    const { functionName, dataType, formatType } = context;
    const fieldName = functionName?.includes(':') ? functionName.split(':')[1] : '';
    if (!fieldName) {
      this.logger.warn('getTargetSessionField: No fieldName in sourceColumn');
      return '';
    }
    const row = await this.fetchTargetSession(context, [fieldName]);
    const rawValue = row?.[fieldName];
    if (rawValue && dataType === 'date') {
      if (formatType === 'weekname') {
        return getWeekName(new Date(rawValue));
      }
      return formatDateIST(new Date(rawValue).toISOString());
    }
    return rawValue ?? '';
  }

  /**
   * Start-to-end time of the target session in IST 12-hour form — "7:00 p.m. to 9:00 p.m. IST".
   * Used for: session_time. Function name in DB: getTargetSessionTime
   */
  private async getTargetSessionTimeField(context: ComputedFieldContext): Promise<string> {
    const row = await this.fetchTargetSession(context, ['starts_at', 'ends_at']);
    const startTime = this.resolveSessionClockTime(row?.starts_at);
    const endTime = this.resolveSessionClockTime(row?.ends_at);
    if (startTime && endTime) {
      return `${startTime} to ${endTime} IST`;
    }
    const singleTime = startTime || endTime;
    return singleTime ? `${singleTime} IST` : '';
  }

  /**
   * Minutes before the session start that the join window opens, read from the target
   * session's online-session row (hdb_online_session.join_opens_minutes_before); falls back
   * to the platform default (15) when the session has none or isn't provisioned.
   */
  private async resolveTargetLoginLeadMinutes(context: ComputedFieldContext): Promise<number> {
    const { manager } = context;
    const sessionId = await this.resolveTargetSessionId(context);
    if (!sessionId || !manager) {
      return 0;
    }
    const row = await manager
      .createQueryBuilder()
      .select('os.join_opens_minutes_before', 'lead')
      .from('hdb_online_session', 'os')
      .where('os.program_session_id = :sessionId', { sessionId })
      .andWhere('os.deleted_at IS NULL')
      .getRawOne();
    const lead = Number(row?.lead);
    return Number.isFinite(lead) && lead > 0 ? lead : 0;
  }

  /**
   * Recommended log-in clock time for the target session: `lead` minutes before start, IST
   * 12-hour form — e.g. "6:45 p.m. IST". Used for: session_login_time.
   * Function name in DB: getTargetSessionLoginTime
   */
  private async getTargetSessionLoginTimeField(context: ComputedFieldContext): Promise<string> {
    const row = await this.fetchTargetSession(context, ['starts_at']);
    if (!row?.starts_at) {
      return '';
    }
    const start = new Date(row.starts_at);
    if (isNaN(start.getTime())) {
      return '';
    }
    const lead = await this.resolveTargetLoginLeadMinutes(context);
    const login = new Date(start.getTime() - lead * 60 * 1000);
    const formatted = formatTime12HourIST(login.toISOString());
    return formatted ? `${formatted} IST` : '';
  }

  /**
   * Pre-session log-in lead as a human-readable duration — e.g. "15 minutes" or "1 minute".
   * Used for: pre_session_login_time. Function name in DB: getTargetSessionLoginLeadMinutes
   */
  private async getTargetSessionLoginLeadMinutesField(
    context: ComputedFieldContext,
  ): Promise<string> {
    const lead = await this.resolveTargetLoginLeadMinutes(context);
    return `${lead} ${lead === 1 ? 'minute' : 'minutes'}`;
  }

  /** Target session date — "6 July 2025". Function name in DB: getTargetSessionDate */
  private async getTargetSessionDateField(context: ComputedFieldContext): Promise<string> {
    const row = await this.fetchTargetSession(context, ['starts_at']);
    return row?.starts_at ? this.formatSessionDateLong(row.starts_at) : '';
  }

  /** Target session weekday + date — "Sunday, 6 July 2025". Function name: getTargetSessionDayDate */
  private async getTargetSessionDayDateField(context: ComputedFieldContext): Promise<string> {
    const row = await this.fetchTargetSession(context, ['starts_at']);
    return row?.starts_at ? this.formatSessionDayDate(row.starts_at) : '';
  }

  /**
   * Dates of the sessions AFTER the target session, grouped (e.g. "July 8, 10 & 13, 2025").
   * Used for: remaining_program_dates. Function name in DB: getTargetRemainingProgramDates
   */
  private async getTargetRemainingProgramDatesField(
    context: ComputedFieldContext,
  ): Promise<string> {
    const { manager } = context;
    const targetSessionId = await this.resolveTargetSessionId(context);
    const sessionIds = await this.resolveSessionIds(context);
    if (!sessionIds.length || !manager) {
      return '';
    }
    const rows = await manager
      .createQueryBuilder()
      .select('session.id', 'id')
      .addSelect('session.starts_at', 'startsAt')
      .from('program_session', 'session')
      .where('session.id IN (:...sessionIds)', { sessionIds })
      .andWhere('session.starts_at IS NOT NULL')
      .orderBy('session.starts_at', 'ASC')
      .getRawMany();

    const target = rows.find((row) => Number(row.id) === targetSessionId);
    const targetStart = target ? new Date(target.startsAt).getTime() : null;
    const remaining = rows
      .filter((row) =>
        targetStart == null ? true : new Date(row.startsAt).getTime() > targetStart,
      )
      .map((row) => new Date(row.startsAt))
      .filter((date) => !isNaN(date.getTime()));

    return this.formatGroupedSessionDates(remaining);
  }

  /** Last session's date — "6 July 2025". Function name in DB: getProgramFinalSessionDate */
  private async getProgramFinalSessionDateField(context: ComputedFieldContext): Promise<string> {
    const dates = await this.fetchOrderedSessionStartDates(context);
    const last = dates[dates.length - 1];
    return last ? this.formatSessionDateLong(last) : '';
  }

  /** Second-to-last session's date. Function name in DB: getProgramFinalButOneSessionDate */
  private async getProgramFinalButOneSessionDateField(
    context: ComputedFieldContext,
  ): Promise<string> {
    const dates = await this.fetchOrderedSessionStartDates(context);
    const penultimate = dates[dates.length - 2];
    return penultimate ? this.formatSessionDateLong(penultimate) : '';
  }

  /** Number of sessions in the registration's program. Function name: getProgramSessionCount */
  private async getProgramSessionCountField(
    context: ComputedFieldContext,
  ): Promise<number | string> {
    const sessionIds = await this.resolveSessionIds(context);
    return sessionIds.length || '';
  }

  /**
   * The sessions (name + start date) this recipient was absent for, up to and including the
   * target session. "Absent" = an occurred session (starts_at <= the target session's start, or
   * now when no target) with no recorded attendance.
   *
   * For a real registration, attendance is read from program_user_attendance (registrationId).
   * For a registration-less recipient (general-link — no registrationId), that table has nothing
   * to key on, so attendance instead falls back to zoom_analytics_attendee_summary (the
   * general-attendee half of that table, registration_id IS NULL, matched by normalized email) —
   * the SAME source getGeneralLinkRecipients/getGeneralLinkRecipientById already use to decide
   * GENERAL_LINK_ABSENT eligibility in the first place, via `common_user_email` on the merge
   * context (see commonInviteMergeContext). Without either a registrationId or that email, there's
   * nothing to resolve against, so the list comes back empty.
   */
  private async resolveAbsentSessions(
    context: ComputedFieldContext,
  ): Promise<Array<{ name: string; startsAt: any }>> {
    const { manager, registrationId, programId, extraMergeContext } = context;
    const email = extraMergeContext?.common_user_email;
    if (!programId || !manager || (!registrationId && !email)) {
      return [];
    }
    // Cutoff = the target session's start (deterministic), else now.
    let cutoff: Date = new Date();
    const targetSessionId = extraMergeContext?.sessionId;
    if (targetSessionId) {
      const target = await manager
        .createQueryBuilder()
        .select('s.starts_at', 'startsAt')
        .from('program_session', 's')
        .where('s.id = :targetSessionId', { targetSessionId })
        .getRawOne();
      if (target?.startsAt) {
        cutoff = new Date(target.startsAt);
      }
    }
    const query = manager
      .createQueryBuilder()
      .select('s.name', 'name')
      .addSelect('s.starts_at', 'startsAt')
      .from('program_session', 's')
      .where('s.program_id = :programId', { programId })
      .andWhere('s.deleted_at IS NULL')
      .andWhere('s.starts_at IS NOT NULL')
      .andWhere('s.starts_at <= :cutoff', { cutoff });

    if (registrationId) {
      query.andWhere(
        `NOT EXISTS (
          SELECT 1 FROM program_user_attendance att
          WHERE att.session_id = s.id
            AND att.registration_id = :registrationId
            AND att.is_attended = true
        )`,
        { registrationId },
      );
    } else {
      query.andWhere(
        `NOT EXISTS (
          SELECT 1 FROM zoom_analytics_attendee_summary zas
          WHERE zas.session_id = s.id
            AND zas.registration_id IS NULL
            AND LOWER(TRIM(zas.email)) = LOWER(TRIM(:email))
            AND zas.is_system_attended = true
        )`,
        { email },
      );
    }

    const rows = await query.orderBy('s.starts_at', 'ASC').getRawMany();
    return rows.map((r) => ({ name: r.name, startsAt: r.startsAt }));
  }

  /** Absent session start date formatted as "20-Jun-2025" (IST). */
  private formatAbsentSessionDate(value: any): string {
    const date = new Date(value);
    if (isNaN(date.getTime())) {
      return '';
    }
    return new Intl.DateTimeFormat('en-GB', {
      timeZone: 'Asia/Kolkata',
      day: '2-digit',
      month: 'short',
      year: 'numeric',
    })
      .format(date)
      .replace(/ /g, '-');
  }

  private escapeHtml(value: string): string {
    return String(value ?? '')
      .replace(/&/g, '&amp;')
      .replace(/</g, '&lt;')
      .replace(/>/g, '&gt;');
  }

  /**
   * Per-registration HTML table (Session | Date) of the sessions the registrant was absent
   * for. Used for: absentDataTabel (email). Function name in DB: getAbsentSessionsTable
   * Returns '' when the registrant missed no session (so the template can hide the block).
   */
  private async getAbsentSessionsTableField(context: ComputedFieldContext): Promise<string> {
    const sessions = await this.resolveAbsentSessions(context);
    if (!sessions.length) {
      return '';
    }
    const cell = 'border:1px solid #d0d0d0;padding:8px 12px;';
    const header = `${cell}background:#f2f2f2;font-weight:bold;text-align:center;`;
    const rows = sessions
      .map(
        (s) =>
          `<tr><td style="${cell}text-align:center;">${this.escapeHtml(s.name)}</td>` +
          `<td style="${cell}text-align:center;">${this.formatAbsentSessionDate(s.startsAt)}</td></tr>`,
      )
      .join('');
    return (
      `<table style="border-collapse:collapse;width:100%;font-family:Cambria, serif;">` +
      `<thead><tr><th style="${header}">Session</th><th style="${header}">Date</th></tr></thead>` +
      `<tbody>${rows}</tbody></table>`
    );
  }

  /**
   * Plain-text list of the sessions the registrant was absent for, for WhatsApp (which can't
   * render HTML) — e.g. "Session 1 (20-Jun-2025) | Session 2 (21-Jun-2025)".
   * Used for: absent_session (WATI). Function name in DB: getAbsentSessionsList
   */
  private async getAbsentSessionsListField(context: ComputedFieldContext): Promise<string> {
    const sessions = await this.resolveAbsentSessions(context);
    return sessions
      .map((s) => `${s.name} (${this.formatAbsentSessionDate(s.startsAt)})`)
      .join(' | ');
  }

  /**
   * Portal deep-link where the seeker joins the session from the app — the seeker FE
   * base (SEEKER_FE_BASE_URL) + "/myspace/programs".
   * Used for: portal_join_link. Function name in DB: getPortalSessionJoinLink
   */
  private getPortalSessionJoinLinkField(context: ComputedFieldContext): Promise<string> {
    const baseUrl = process.env.SEEKER_FE_BASE_URL || '';
    if (!baseUrl) {
      this.logger.warn(
        `portal_join_link: SEEKER_FE_BASE_URL not set (registration ${context.registrationId ?? 'n/a'}); returning empty`,
      );
      return Promise.resolve('');
    }
    return Promise.resolve(`${baseUrl.replace(/\/+$/, '')}/${SEEKER_FE_SESSION_JOIN_PATH}`);
  }

  /**
   * Portal join deep-link WITHOUT the DNS — the path only ("myspace/programs"), as required by
   * the WhatsApp (WATI) URL-button parameter: the seeker FE domain is baked into the template
   * button, so only the variable path suffix is sent (never the scheme + host).
   * Used for: wati_portal_link. Function name in DB: getWatiPortalSessionJoinLink
   */
  private getWatiPortalSessionJoinLinkField(_context: ComputedFieldContext): Promise<string> {
    return Promise.resolve(SEEKER_FE_SESSION_JOIN_PATH);
  }

  /**
   * The registrant's personalized Zoom join link for the target session WITHOUT the DNS — the
   * path + query only (e.g. "j/8912345678?pwd=..."), as required by the WATI URL-button
   * parameter: the Zoom domain is baked into the template button, so only the variable suffix is
   * sent. Wraps the same personalized join_url used by getTargetOnlineSessionField:join_url.
   * Used for: wati_zoom_link. Function name in DB: getWatiSessionJoinUrl
   */
  private async getWatiSessionJoinUrlField(context: ComputedFieldContext): Promise<string> {
    const sessionId = await this.resolveTargetSessionId(context);
    if (!sessionId) {
      return '';
    }
    const joinUrl = await this.getRegistrationJoinUrl(context, sessionId);
    return this.stripUrlOrigin(joinUrl);
  }

  /**
   * Strip the origin (scheme + host[:port]) from a URL, returning path + query + fragment with no
   * leading slash — the form a WATI URL-button dynamic parameter expects. Empty input yields '';
   * a value that isn't an absolute URL is returned unchanged (minus any leading slash) so an
   * already-relative link passes through untouched.
   */
  private stripUrlOrigin(url: string): string {
    if (!url) {
      return '';
    }
    try {
      const parsed = new URL(url);
      return `${parsed.pathname}${parsed.search}${parsed.hash}`.replace(/^\/+/, '');
    } catch {
      return url.replace(/^\/+/, '');
    }
  }

  /**
   * URL of the session-guidelines PDF used by the WhatsApp templates (a document parameter).
   * Generated lazily and cached by SessionGuidelinesPdfService:
   * - Session-scoped send (Invite): the session-level PDF (adds remaining program dates +
   *   recommended login time), cached on program_session.guidelines_pdf_url.
   * - Program-level send (Welcome): the generic program PDF, cached on program.guidelines_pdf_url.
   * Falls back to SESSION_GUIDELINES_PDF_URL when the program can't be resolved.
   * Used for: guideline_pdf / guidelines_pdf. Function name in DB: getSessionGuidelinesPdfUrl
   */
  private async getSessionGuidelinesPdfUrl(context: ComputedFieldContext): Promise<string> {
    const sessionId = context.extraMergeContext?.sessionId;
    if (sessionId) {
      // Session merge values are computed only on a cache miss (thunk), reusing the same field
      // logic the message templates use, so the PDF and the message stay consistent.
      return this.sessionGuidelinesPdfService.resolveSessionPdfUrl(Number(sessionId), async () => ({
        preSessionLoginTime: await this.resolveTargetLoginLeadMinutes(context),
        sessionLoginTime: await this.getTargetSessionLoginTimeField(context),
      }));
    }
    return '';
  }

  /**
   * URL of the PROGRAM-LEVEL welcome guidelines PDF (the generic SESSION_GUIDELINES template).
   * Used by the Welcome templates — one PDF per program, cached on program.guidelines_pdf_url.
   * Falls back to SESSION_GUIDELINES_PDF_URL when the program can't be resolved.
   * Used for: guideline_pdf. Function name in DB: getWelcomeGuidelinesPdfUrl
   */
  private async getWelcomeGuidelinesPdfUrl(context: ComputedFieldContext): Promise<string> {
    const programId = await this.resolveRegistrationProgramId(context);
    if (!programId) {
      return '';
    }
    return this.sessionGuidelinesPdfService.resolveProgramPdfUrl(programId);
  }

  /**
   * A value supplied on the send's merge context (not from the DB) — e.g. the Value Card
   * `description` typed by the admin. Usage: getMergeContextValue:description reads
   * extraMergeContext.description. Returns '' when absent.
   * Function name in DB: getMergeContextValue
   */
  private getMergeContextValueField(context: ComputedFieldContext): Promise<string> {
    const { functionName, extraMergeContext } = context;
    const fieldName = functionName?.includes(':') ? functionName.split(':')[1] : '';
    if (!fieldName) {
      this.logger.warn('getMergeContextValue: No fieldName in sourceColumn');
      return Promise.resolve('');
    }
    const value = extraMergeContext?.[fieldName];
    return Promise.resolve(value == null ? '' : String(value));
  }

  /** Resolve the program id for the registration in context (hdb_program_registration.program_id). */
  private async resolveRegistrationProgramId(
    context: ComputedFieldContext,
  ): Promise<number | null> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      return null;
    }
    const reg = await manager
      .createQueryBuilder()
      .select('reg.program_id', 'programId')
      .from('hdb_program_registration', 'reg')
      .where('reg.id = :registrationId', { registrationId })
      .getRawOne();
    return reg?.programId ? Number(reg.programId) : null;
  }

  /**
   * A column of the target session's online session. Usage: getTargetOnlineSessionField:join_url —
   * also external_id (the Zoom meeting/webinar id) and password (the Zoom passcode).
   *
   * `join_url` is the per-registrant personalized join link, so it comes from the registrant's
   * own row in hdb_program_registration_online_session. external_id and password are session-level
   * shared values and come from hdb_online_session (linked by program_session_id).
   * Function name in DB: getTargetOnlineSessionField
   */
  private async getTargetOnlineSessionFieldValue(
    context: ComputedFieldContext,
  ): Promise<string> {
    const { manager, functionName } = context;
    const fieldName = functionName?.includes(':') ? functionName.split(':')[1] : '';
    if (!fieldName) {
      this.logger.warn('getTargetOnlineSessionField: No fieldName in sourceColumn');
      return '';
    }
    const sessionId = await this.resolveTargetSessionId(context);
    if (!sessionId || !manager) {
      return '';
    }
    // join_url is personalized per registrant — resolve from the registration's online-session row.
    if (fieldName === 'join_url') {
      return this.getRegistrationJoinUrl(context, sessionId);
    }
    const row = await manager
      .createQueryBuilder()
      .select(`os.${fieldName}`, 'value')
      .from('hdb_online_session', 'os')
      .where('os.program_session_id = :sessionId', { sessionId })
      .andWhere('os.deleted_at IS NULL')
      .getRawOne();
    return row?.value ?? '';
  }

  /**
   * The registrant's personalized join link for the target session, from
   * hdb_program_registration_online_session (keyed by registration + the session's online-session
   * row). Only provisioned (REGISTERED) rows carry a usable link. Returns '' when absent.
   */
  private async getRegistrationJoinUrl(
    context: ComputedFieldContext,
    sessionId: number,
  ): Promise<string> {
    const { manager, registrationId } = context;
    if (!registrationId || !manager) {
      return '';
    }
    const row = await manager
      .createQueryBuilder()
      .select('pros.join_url', 'value')
      .from('hdb_program_registration_online_session', 'pros')
      .innerJoin('hdb_online_session', 'os', 'os.id = pros.online_session_id')
      .where('os.program_session_id = :sessionId', { sessionId })
      .andWhere('os.deleted_at IS NULL')
      .andWhere('pros.registration_id = :registrationId', { registrationId })
      .andWhere('pros.deleted_at IS NULL')
      .andWhere('pros.status = :status', { status: OnlineSessionRegistrationStatus.REGISTERED })
      .orderBy('pros.updated_at', 'DESC')
      .getRawOne();
    return row?.value ?? '';
  }

  /**
   * Weekday names of the registration's program sessions (e.g. "Mondays, Tuesdays, Wednesdays").
   * Used for: session_days. Function name in DB: getSessionDays
   * Maps each session's start date (IST) to its pluralized full weekday name, in
   * chronological order, deduplicated so a weekday is listed once.
   */
  private async getSessionDaysField(context: ComputedFieldContext): Promise<string> {
    const { manager } = context;

    try {
      const sessionIds = await this.resolveSessionIds(context);
      if (!sessionIds.length) {
        this.logger.warn('getSessionDays: No sessions found for registration');
        return '';
      }

      const sessions = await manager
        .createQueryBuilder()
        .select('session.starts_at', 'startsAt')
        .from('program_session', 'session')
        .where('session.id IN (:...sessionIds)', { sessionIds })
        .andWhere('session.starts_at IS NOT NULL')
        .orderBy('session.starts_at', 'ASC')
        .getRawMany();

      const weekdayFormatter = new Intl.DateTimeFormat('en-GB', {
        timeZone: 'Asia/Kolkata',
        weekday: 'long',
      });

      const seen = new Set<string>();
      const days: string[] = [];
      for (const session of sessions) {
        const date = new Date(session.startsAt);
        if (isNaN(date.getTime())) {
          continue;
        }
        const day = `${weekdayFormatter.format(date)}s`;
        if (seen.has(day)) {
          continue;
        }
        seen.add(day);
        days.push(day);
      }

      return days.join(', ');
    } catch (error) {
      this.logger.error('Error in getSessionDays', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * List the start dates of all sessions in the registration's program, grouped
   * by month/year (e.g. "1, 2, 3 June 2026" or "30 June 2026, 1, 2 July 2026").
   * Used for: session_date / session_dates. Function name in DB: formatSessionDateRange
   * Falls back to the program-level date range when no session dates exist.
   */
  private async formatSessionDateRangeField(context: ComputedFieldContext): Promise<string> {
    const { manager } = context;

    try {
      const sessionIds = await this.resolveSessionIds(context);
      if (sessionIds.length) {
        const sessions = await manager
          .createQueryBuilder()
          .select('session.starts_at', 'startsAt')
          .from('program_session', 'session')
          .where('session.id IN (:...sessionIds)', { sessionIds })
          .andWhere('session.starts_at IS NOT NULL')
          .orderBy('session.starts_at', 'ASC')
          .getRawMany();

        const startDates = sessions
          .map((s) => s.startsAt)
          .filter(Boolean)
          .map((value) => new Date(value));

        const formatted = this.formatGroupedSessionDates(startDates);
        if (formatted) {
          this.logger.debug(`formatSessionDateRange: Formatted session dates: ${formatted}`);
          return formatted;
        }
      }
      this.logger.warn(
        'formatSessionDateRange: No valid session start dates found, falling back to program date range',
      );

      // Fall back to the program-level date range for parity with legacy behaviour
      return this.formatProgramDateRangeField(context);
    } catch (error) {
      this.logger.error('Error in formatSessionDateRange', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Grouped session dates for the registration's program
   * (e.g. "August 3, 4, 5, 10, 11, 12, 17, 18, 19, 24, 25 & 26, 2026").
   * Used for: session_dates. Function name in DB: formatSessionDateAndDaysRange
   * Delegates to formatSessionDateRange. The weekday names are no longer appended
   * here — they are carried by the session_days_and_time field (getSessionDaysAndTime).
   */
  private async formatSessionDateAndDaysRangeField(context: ComputedFieldContext): Promise<string> {
    try {
      const dateRange = await this.formatSessionDateRangeField(context);
      this.logger.debug(`formatSessionDateAndDaysRange: ${dateRange}`);
      return dateRange;
    } catch (error) {
      this.logger.error('Error in formatSessionDateAndDaysRange', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Format a list of session start dates into a human-readable string, grouping
   * dates that share a month (and year) under a single full month name and
   * joining the day numbers with commas plus an "&" before the final day.
   *
   * Examples:
   *   single month/year   -> "August 3, 4, 5, 10, 11, 12, 17, 18, 19, 24, 25 & 26, 2026"
   *   two months, 1 year  -> "August 18, 19, 24, 25 & 26, September 1, 3 & 5, 2026"
   *   spanning two years  -> "August 18, 19, 24, 25 & 26, 2026 September 1, 3 & 5, 2027"
   *
   * The year is shown once at the end when all dates fall in the same year;
   * otherwise each group carries its own year and the groups are space-separated.
   */
  private formatGroupedSessionDates(dates: Date[]): string {
    const seen = new Set<string>();
    const parts: { day: number; month: string; year: string; sortKey: number }[] = [];

    for (const date of dates) {
      if (isNaN(date.getTime())) {
        continue;
      }

      const partsMap = new Intl.DateTimeFormat('en-IN', {
        timeZone: 'Asia/Kolkata',
        day: 'numeric',
        month: 'long',
        year: 'numeric',
      })
        .formatToParts(date)
        .reduce(
          (acc, part) => {
            acc[part.type] = part.value;
            return acc;
          },
          {} as Record<string, string>,
        );

      const key = `${partsMap.year}-${partsMap.month}-${partsMap.day}`;
      if (seen.has(key)) {
        continue;
      }
      seen.add(key);

      parts.push({
        day: Number(partsMap.day),
        month: partsMap.month,
        year: partsMap.year,
        sortKey: date.getTime(),
      });
    }

    if (!parts.length) {
      return '';
    }

    parts.sort((a, b) => a.sortKey - b.sortKey);

    // Group consecutive dates that share the same month and year
    const groups: { days: number[]; month: string; year: string }[] = [];
    for (const part of parts) {
      const last = groups[groups.length - 1];
      if (last && last.month === part.month && last.year === part.year) {
        last.days.push(part.day);
      } else {
        groups.push({ days: [part.day], month: part.month, year: part.year });
      }
    }
    this.logger.debug(
      `formatGroupedSessionDates: Grouped session dates: ${JSON.stringify(groups)}`,
    );

    // Join day numbers with commas and an "&" before the final day
    // (e.g. [3, 4, 5] -> "3, 4 & 5").
    const formatDays = (days: number[]): string =>
      days.length <= 1
        ? String(days[0] ?? '')
        : `${days.slice(0, -1).join(', ')} & ${days[days.length - 1]}`;

    // When every group falls in the same year, show the year once at the end
    // (e.g. "August 18, 19 & 26, September 1, 3 & 5, 2026"); otherwise each group
    // carries its own year and the groups are space-separated
    // (e.g. "August 18 & 26, 2026 September 1 & 5, 2027").
    const singleYear = groups.every((group) => group.year === groups[0].year);
    if (singleYear) {
      return `${groups.map((group) => `${group.month} ${formatDays(group.days)}`).join(', ')}, ${groups[0].year}`;
    }

    return groups
      .map((group) => `${group.month} ${formatDays(group.days)}, ${group.year}`)
      .join(' ');
  }

  /**
   * Start-to-end time range of the registration's first session, formatted in IST
   * 12-hour clock — e.g. "07:00 p.m. to 09:00 p.m.".
   * Used for: session_time. Function name in DB: getSessionTime
   * Prefers the session's starts_at/ends_at timestamps, falling back to the
   * default_start_time/default_end_time clock columns.
   */
  private async getSessionTimeField(context: ComputedFieldContext): Promise<string> {
    const { manager } = context;

    try {
      const [sessionId] = await this.resolveSessionIds(context);
      if (!sessionId) {
        this.logger.warn('getSessionTime: No sessionId found for registration');
        return '';
      }

      const session = await manager
        .createQueryBuilder()
        .select(['session.starts_at', 'session.ends_at'])
        .from('program_session', 'session')
        .where('session.id = :sessionId', { sessionId })
        .getRawOne();

      const startTime = this.resolveSessionClockTime(session?.starts_at);
      const endTime = this.resolveSessionClockTime(session?.ends_at);

      if (startTime && endTime) {
        return `${startTime} to ${endTime} IST`;
      }

      const singleTime = startTime || endTime;
      return singleTime ? `${singleTime} IST` : '';
    } catch (error) {
      this.logger.error('Error in getSessionTime', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Resolve a single session clock value to an IST 12-hour string ("07:00 p.m.").
   * Prefers the timestamp (starts_at/ends_at), falling back to the time-only
   * clock column (default_start_time/default_end_time). Returns '' when neither is set.
   */
  private resolveSessionClockTime(timestamp: any): string {
    if (timestamp) {
      const formatted = formatTime12HourIST(timestamp);
      if (formatted) {
        return formatted;
      }
    }

    return '';
  }

  /**
   * Combined session weekday names and start-to-end time for the registration's
   * program — the pluralized weekday names followed by the session time in
   * parentheses (e.g. "Mondays, Tuesdays and Wednesdays (7:00 p.m. to 9:00 p.m. IST)").
   * Used for: session_days_and_time. Function name in DB: getSessionDaysAndTime
   * Reuses getSessionDays + getSessionTime; the weekday list is rejoined with an
   * "and" before the final entry, and a missing piece is dropped so neither an
   * empty parenthesis nor a dangling label is rendered.
   */
  private async getSessionDaysAndTimeField(context: ComputedFieldContext): Promise<string> {
    try {
      const [daysList, time] = await Promise.all([
        this.getSessionDaysField(context),
        this.getSessionTimeField(context),
      ]);

      const days = (daysList || '')
        .split(',')
        .map((day) => day.trim())
        .filter(Boolean);
      const daysText = this.joinWithAnd(days);

      const combined = daysText && time ? `${daysText} (${time})` : daysText || time || '';
      this.logger.debug(`getSessionDaysAndTime: ${combined}`);
      return combined;
    } catch (error) {
      this.logger.error('Error in getSessionDaysAndTime', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Join a list of strings with commas and an "and" before the final entry
   * (e.g. ["Mondays", "Tuesdays", "Wednesdays"] -> "Mondays, Tuesdays and Wednesdays").
   */
  private joinWithAnd(items: string[]): string {
    if (items.length <= 1) {
      return items[0] ?? '';
    }
    return `${items.slice(0, -1).join(', ')} and ${items[items.length - 1]}`;
  }

  /**
   * First paragraph of the invoice email body.
   * Used for: firstPara. Function name in DB: firstPara
   * Returns the seat-confirmation acknowledgement line with the course (program)
   * name resolved from the registration's program_id (same source as prg_coursename).
   */
  private async firstParaField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn('firstPara: Missing registrationId or manager');
      return '';
    }

    try {
      const row = await manager
        .createQueryBuilder()
        .select('program.name', 'courseName')
        .from('hdb_program_registration', 'reg')
        .leftJoin('program_v1', 'program', 'reg.program_id = program.id')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();

      const courseName = row?.courseName || '';
      return `Congratulations, your seat for '${courseName}' is confirmed. Please find enclosed the invoice in acknowledgement of your payment`;
    } catch (error) {
      this.logger.error('Error in firstPara', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Computed function to get allocated program starts_at minus 10 days (for invoice last_date field)
   * This matches the old invoice service logic: deductDaysFromDate(allocatedProgram.startsAt, 10)
   * Usage: sourceColumn: 'getAllocatedProgramStartsMinus10Days'
   */
  private async getAllocatedProgramStartsMinus10DaysField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn('getAllocatedProgramStartsMinus10Days: Missing registrationId or manager');
      return '';
    }
    
    try {
      // 1. Get allocated_program_id from registration
      const reg = await manager
        .createQueryBuilder()
        .select('reg.allocated_program_id', 'allocatedProgramId')
        .from('hdb_program_registration', 'reg')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();
        
      if (!reg?.allocatedProgramId) {
        this.logger.warn(`getAllocatedProgramStartsMinus10Days: No allocatedProgramId found for registrationId ${registrationId}`);
        return '';
      }
      
      // 2. Fetch starts_at from allocated program
      const program = await manager
        .createQueryBuilder()
        .select('program.starts_at', 'startsAt')
        .from('program_v1', 'program')
        .where('program.id = :programId', { programId: reg.allocatedProgramId })
        .getRawOne();
        
      const startsAt = program?.startsAt;
      
      if (!startsAt) {
        this.logger.warn(`getAllocatedProgramStartsMinus10Days: No starts_at found for allocated program ${reg.allocatedProgramId}`);
        return '';
      }
      
      // 3. Deduct 10 days and format
      const lastDate = deductDaysFromDate(new Date(startsAt), 10);
      return formatDateIST(lastDate.toISOString());
    } catch (error) {
      this.logger.error('Error in getAllocatedProgramStartsMinus10Days', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Builds the check-in / check-out display string for ENT email templates.
   * Mirrors old-system format:
   *   `Check-in Time: From {checkin_at} to {checkin_ends_at}.Check-out Time: Latest by {checkout_at}.`
   * Usage: sourceColumn: 'getCheckInOut'
   */
  private async getCheckInOutField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn('getCheckInOut: Missing registrationId or manager');
      return '';
    }

    try {
      const reg = await manager
        .createQueryBuilder()
        .select('reg.allocated_program_id', 'allocatedProgramId')
        .from('hdb_program_registration', 'reg')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();

      if (!reg?.allocatedProgramId) {
        this.logger.warn(`getCheckInOut: No allocatedProgramId for registrationId ${registrationId}`);
        return '';
      }

      const program = await manager
        .createQueryBuilder()
        .select('program.checkin_at', 'checkinAt')
        .addSelect('program.checkin_ends_at', 'checkinEndsAt')
        .addSelect('program.checkout_at', 'checkoutAt')
        .from('program_v1', 'program')
        .where('program.id = :programId', { programId: reg.allocatedProgramId })
        .getRawOne();

      let result = '';
      if (program?.checkinAt) {
        result += `Check-in Time: From ${formatTimeIST(program.checkinAt)}`;
        if (program?.checkinEndsAt) {
          result += ` to ${formatDateTimeIST(program.checkinEndsAt)}`;
        }
        result += '.';
      }
      if (program?.checkoutAt) {
        result += `Check-out Time: Latest by ${formatDateTimeIST(program.checkoutAt)}.`;
      }

      return result;
    } catch (error) {
      this.logger.error('Error in getCheckInOut', (error as Error)?.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Get template with dynamically resolved merge info from database
   * @param programId - Program ID for template lookup
   * @param templateAccessKey - Template access key enum value
   * @param communicationType - Email or WhatsApp
   * @param registrationId - Optional registration ID for per-record merge fields (not needed for scheduler/bulk operations)
   * @param manager - Optional transaction manager for querying registration data
   * @param extraMergeContext - Optional extra context data for computed functions (e.g., preference arrays, OTP)
   * @returns Object with templateKey and dynamically resolved mergeInfo, or null if template not found
   */
  async getTemplateWithMergeInfo(
    programId: number,
    templateAccessKey: CommunicationTemplateAccessKeyEnum,
    communicationType: CommunicationTypeEnum,
    registrationId?: number,
    manager?: any,
    extraMergeContext?: Record<string, any>,
    options?: { resolveAllFieldsAsCommon?: boolean; cache?: SendMergeCache },
  ): Promise<{ templateKey: string; mergeInfo: Record<string, any> } | null> {
    try {
      // Always ensure manager is available
      // Validate required parameters
      if (!programId || !templateAccessKey || !communicationType) {
        this.logger.error('Missing required parameters for getTemplateWithMergeInfo', {
          programId, templateAccessKey, communicationType
        });
        return null;
      }

      const effectiveManager = manager || this.manager;
      if (!effectiveManager) {
        this.logger.warn('No manager available for merge info computation. Some computed fields may fail.');
      }
      this.logger.debug(
        `Fetching template with merge info for programId ${programId}, templateAccessKey ${templateAccessKey}, communicationType ${communicationType}, registrationId ${registrationId ?? 'none'}`
      );
      
      // Get the template from database. Constant for the whole send, so a supplied cache serves
      // every recipient after the first (`undefined` = not looked up; `null` = looked up, absent).
      const cache = options?.cache;
      const template =
        cache?.template !== undefined
          ? cache.template
          : await this.communicationTemplatesRepository.findByProgramAndAccessKey(
              programId,
              templateAccessKey,
              communicationType,
            );
      if (cache) {
        cache.template = template;
      }

      this.logger.debug(`Template fetched: ${template?.templateId ?? 'No template found'}`);
      if (!template?.templateId) {
        this.logger.error(
          `Template not found for program ${programId}, accessKey ${templateAccessKey}, type ${communicationType}. Please configure template in database.`,
          { programId, templateAccessKey, communicationType }
        );
        return null;
      }

      // Send-time gate (rev 11): the clone fires for its native step only if that
      // step is present in `attached_steps`. Legacy clones without a master/step
      // mapping fall through (allowed) for back-compat. Consumers already treat a
      // null return as "skip this communication".
      const masterId = template.masterTemplateId != null ? Number(template.masterTemplateId) : null;
      const nativeStep = template.step ? String(template.step) : null;
      if (masterId != null && nativeStep) {
        const attachedSteps = template.attachedSteps ?? [];
        if (!attachedSteps.includes(nativeStep)) {
          this.logger.log(
            `Template ${template.templateKey} (master ${masterId}) not attached to its native step ${nativeStep} for program ${programId}; skipping send.`,
          );
          return null;
        }
      }

      this.logger.log(`Template found: ${template.templateKey} (ID: ${template.id}, Template ID: ${template.templateId}) for program ${programId}, accessKey ${templateAccessKey}`);
      
      // Determine which template ID will be used based on environment
      const effectiveTemplateId = this.communicationTemplatesRepository.getTemplateIdBasedOnEnvironment(template);
      const useSandbox = process.env.EMAIL_USE_SANDBOX === 'true' && template.templateType === CommunicationTypeEnum.EMAIL && template.sandboxTemplateId;
      
      this.logger.log(
        `Using template ID: ${effectiveTemplateId}` +
        (useSandbox ? ` (sandbox mode, production: ${template.templateId})` : '')
      );

      // Get merge field mappings for this template — also constant for the whole send.
      const mergeFieldMaps =
        cache?.mergeFields ??
        (await this.mergeInfoRepository.find({
          where: { templateId: template.id, isActive: true },
          order: { keyName: 'ASC' },
        }));
      if (cache) {
        cache.mergeFields = mergeFieldMaps;
      }

      this.logger.log(`Found ${mergeFieldMaps.length} merge field mapping(s) for template ${template.id} (${template.templateKey})`);
      
      if (mergeFieldMaps.length === 0) {
        this.logger.warn(`No merge field mappings found for template ${template.id} (${template.templateKey})`);
        return {
          templateKey: this.communicationTemplatesRepository.getTemplateIdBasedOnEnvironment(template),
          mergeInfo: {},
        };
      }
      
      this.logger.debug(`Merge fields: ${mergeFieldMaps.map(f => f.keyName).join(', ')}`);

      // Separate common and per-record fields. Program-level sends with no registration (e.g.
      // common invite) set resolveAllFieldsAsCommon so every field resolves via the common path
      // (programId + extraMergeContext) — this keeps them working even if a clone's merge rows
      // were mis-flagged is_common = false, which would otherwise route them to the per-record
      // pass that is skipped without a registrationId.
      const commonFields = options?.resolveAllFieldsAsCommon
        ? mergeFieldMaps
        : mergeFieldMaps.filter((field) => field.isCommon);
      const perRecordFields = options?.resolveAllFieldsAsCommon
        ? []
        : mergeFieldMaps.filter((field) => !field.isCommon);

      // Create context for computed functions
      const context: ComputedFieldContext = {
        programId,
        registrationId,
        manager: effectiveManager,
        mergeInfo: {}, // Will be populated during processing
        extraMergeContext, // Pass extra context (e.g., preference arrays)
      };

      // Fetch userId if we have a registrationId (needed for payment links)
      if (registrationId && manager) {
        const registration = await manager
          .createQueryBuilder()
          .select('hdb_program_registration.user_id', 'userId')
          .from('hdb_program_registration', 'hdb_program_registration')
          .where('hdb_program_registration.id = :registrationId', { registrationId })
          .getRawOne();
        context.userId = registration?.userId;
      }

      // Process common fields (same for all recipients)
      const commonMergeData = await this.processCommonMergeFields(commonFields, context, cache);

      // Process per-record fields (specific to this registration) - skip if no registrationId
      let perRecordMergeData: Record<string, any> = {};
      if (registrationId) {
        perRecordMergeData = await this.processPerRecordMergeFields(
          perRecordFields,
          context,
        );
      } else {
        this.logger.debug(`Skipping per-record fields for template ${template.id} - no registrationId provided`);
      }

      const mergeInfo = {
        ...commonMergeData,
        ...perRecordMergeData,
      };

      this.logger.log(
        `Prepared merge info for template ${template.id} (${template.templateKey}) with ${Object.keys(mergeInfo).length} fields: ${Object.keys(mergeInfo).join(', ')} | Values: ${Object.values(mergeInfo).slice(0,5).join(', ')}...`
      );

      return {
        templateKey: this.communicationTemplatesRepository.getTemplateIdBasedOnEnvironment(template),
        mergeInfo,
      };
    } catch (error) {
      this.logger.error(
        `Error fetching template with merge info for program ${programId}, accessKey ${templateAccessKey}`,
        error.stack,
      );
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Process common merge fields (shared across all records)
   * @param commonFields - Array of common merge field mappings
   * @param context - Context for computed functions
   * @returns Record of field names to values
   */
  private async processCommonMergeFields(
    commonFields: MergeInfoAnswerLocationMap[],
    context: ComputedFieldContext,
    cache?: SendMergeCache,
  ): Promise<Record<string, any>> {
    const commonMergeData: Record<string, any> = {};

    for (const field of commonFields) {
      try {
        // Registration-less override: when there's no registrationId and the caller supplied a
        // matching common_<keyName> value, use it outright instead of the field's configured
        // resolution (DB column or registrationId-keyed computed function). Never triggers for a
        // real registration send (registrationId is always present there), so this only changes
        // behavior for registration-less recipients.
        const overrideKey = REGISTRATION_LESS_FIELD_ALIASES[field.keyName] ?? `common_${field.keyName}`;
        if (
          !context.registrationId &&
          context.extraMergeContext &&
          Object.prototype.hasOwnProperty.call(context.extraMergeContext, overrideKey)
        ) {
          commonMergeData[field.keyName] = DataFormatter.format(
            context.extraMergeContext[overrideKey],
            field.dataType,
            field.formatType,
            field.isNullable,
            field.defaultValue,
          );
          continue;
        }

        // Reached only for a genuinely send-constant field — the per-recipient override above
        // already returned. Serve it from the send cache when one was supplied: this is what stops
        // an N-recipient send from re-reading the same rows, re-signing the same S3 URL, and (when
        // the guidelines PDF has never been generated) re-running puppeteer N times over.
        if (cache?.commonValues.has(field.keyName)) {
          commonMergeData[field.keyName] = cache.commonValues.get(field.keyName);
          continue;
        }

        // Check if this is a computed field
        if (field.sourceTable === 'computed' && field.sourceColumn) {
          const computedValue = await this.executeComputedFunction(field.sourceColumn, field.keyName, context, field.dataType, field.formatType);
          commonMergeData[field.keyName] = DataFormatter.format(
            computedValue,
            field.dataType,
            field.formatType,
            field.isNullable,
            field.defaultValue,
          );
        } else {
          // Regular database field
          const value = await this.fetchMergeFieldValueFromDB(field, null, context.manager);
          commonMergeData[field.keyName] = DataFormatter.format(
            value,
            field.dataType,
            field.formatType,
            field.isNullable,
            field.defaultValue,
          );
        }
        cache?.commonValues.set(field.keyName, commonMergeData[field.keyName]);
      } catch (fieldError) {
        this.logger.error(
          `[COMMON_FIELD_PROCESSING_ERROR] Failed to process common field ${field.keyName}`,
          fieldError.stack,
          {
            fieldKey: field.keyName,
            sourceTable: field.sourceTable,
            sourceColumn: field.sourceColumn,
          }
        );
        // Use default value to prevent transaction abort
        commonMergeData[field.keyName] = field.defaultValue || '';
      }
    }

    // Update context with current merge info
    context.mergeInfo = { ...context.mergeInfo, ...commonMergeData };
    return commonMergeData;
  }

  /**
   * Process per-record merge fields (unique to each record)
   * @param perRecordFields - Array of per-record merge field mappings
   * @param context - Context for computed functions (must include registrationId)
   * @returns Record of field names to values
   */
  private async processPerRecordMergeFields(
    perRecordFields: MergeInfoAnswerLocationMap[],
    context: ComputedFieldContext,
  ): Promise<Record<string, any>> {
    const recordFields: Record<string, any> = {};

    if (!context.registrationId) {
      this.logger.warn('No registrationId provided for per-record merge fields processing');
      return recordFields;
    }

    for (const field of perRecordFields) {
      try {
        // Handle context-based fields (including per-recipient and flight change fields)
        if (field.sourceTable === 'context') {
          const contextValue = this.resolvePerRecipientField(field, context);
          recordFields[field.keyName] = DataFormatter.format(
            contextValue,
            field.dataType,
            field.formatType,
            field.isNullable,
            field.defaultValue,
          );
        } else if (field.sourceTable === 'computed' && field.sourceColumn) {
          const computedValue = await this.executeComputedFunction(field.sourceColumn, field.keyName, context, field.dataType, field.formatType);
          recordFields[field.keyName] = DataFormatter.format(
            computedValue,
            field.dataType,
            field.formatType,
            field.isNullable,
            field.defaultValue,
          );
        } else {
          // Regular database field
          const value = await this.fetchMergeFieldValueFromDB(field, context.registrationId, context.manager);
          recordFields[field.keyName] = DataFormatter.format(
            value,
            field.dataType,
            field.formatType,
            field.isNullable,
            field.defaultValue,
          );
        }
      } catch (fieldError) {
        this.logger.error(
          `[MERGE_FIELD_PROCESSING_ERROR] Failed to process field ${field.keyName}`,
          fieldError.stack,
          {
            fieldKey: field.keyName,
            sourceTable: field.sourceTable,
            sourceColumn: field.sourceColumn,
            isCommon: field.isCommon,
            registrationId: context.registrationId,
          }
        );
        // Use default value to prevent transaction abort
        recordFields[field.keyName] = field.defaultValue || '';
      }
    }

    // Update context with current merge info
    context.mergeInfo = { ...context.mergeInfo, ...recordFields };
    return recordFields;
  }

  /**
   * Execute a computed function by name
   * @param functionName - Name of the function to execute (from sourceColumn)
   * @param fieldKey - Key name of the field being computed
   * @param context - Context with all necessary data
   * @param dataType - Data type from merge field map
   * @param formatType - Format type from merge field map
   * @returns Computed value
   */
  private async executeComputedFunction(
    functionName: string,
    fieldKey: string,
    context: ComputedFieldContext,
    dataType?: string,
    formatType?: string,
  ): Promise<any> {
    try {
      // Handle pattern: 'getProgramField:starts_at' - extract base function name
      let baseFunctionName = functionName;
      if (functionName.includes(':')) {
        baseFunctionName = functionName.split(':')[0];
      }
      
      const computedFunction = this.computedFunctionRegistry[baseFunctionName];
      
      if (!computedFunction) {
        this.logger.warn(`Computed function '${baseFunctionName}' not found in registry for field '${fieldKey}'`);
        return '';
      }

      this.logger.debug(`Executing computed function '${baseFunctionName}' for field '${fieldKey}'`);
      
      // Add fieldKey, functionName, dataType, and formatType to context
      // For getProgramField:starts_at, this allows the function to extract 'starts_at'
      const contextWithFieldKey = { ...context, fieldKey, functionName, dataType, formatType };
      const result = await computedFunction(contextWithFieldKey);
      
      this.logger.debug(`Computed field '${fieldKey}' = ${result}`);
      
      return result;
    } catch (error) {
      this.logger.error(`Error executing computed function '${functionName}' for field '${fieldKey}': ${error.message}`, error.stack);
      return '';
    }
  }

  /**
   * Resolve per-recipient field value from extraMergeContext
   * Used for fields marked with isPerRecipient=true (e.g., coordinator_name)
   * Also handles context-based fields (e.g., flightChange:oldArrivalAirline)
   * @param field - Merge field configuration
   * @param context - Context with extraMergeContext containing per-recipient data
   * @returns Resolved value from context or default value
   */
  private resolvePerRecipientField(
    field: MergeInfoAnswerLocationMap,
    context: ComputedFieldContext,
  ): any {
    try {
      const { sourceColumn, sourceTable } = field;
      
      // Handle context fields (not just perRecipient)
      if (sourceTable === 'context' && sourceColumn) {
        if (!context.extraMergeContext) {
          this.logger.warn(`No extraMergeContext provided for context field ${field.keyName}`);
          return field.defaultValue || '';
        }

        // Handle perRecipient: prefix
        if (sourceColumn.startsWith('perRecipient:')) {
          const contextPath = sourceColumn.replace('perRecipient:', '');
          const value = this.navigateContextPath(contextPath, context.extraMergeContext);
          
          // Special handling for coordinator_name field
          if (field.keyName === 'coordinator_name' && typeof value === 'object' && value !== null) {
            const coordinator = value as any;
            const coordinatorName = (coordinator?.firstName && coordinator?.lastName)
              ? `${coordinator.firstName} ${coordinator.lastName}`
              : coordinator?.fullName ?? coordinator?.legalFullName ?? 'Coordinator';
            
            this.logger.debug(`Resolved coordinator_name: ${coordinatorName}`);
            return coordinatorName;
          }
          
          return value ?? field.defaultValue ?? '';
        }
        
        // Handle flightChange: prefix
        if (sourceColumn.startsWith('flightChange:')) {
          const fieldPath = sourceColumn.replace('flightChange:', '');
          const value = this.navigateContextPath(fieldPath, context.extraMergeContext);
          return value ?? field.defaultValue ?? '';
        }
        
        // Generic context path navigation
        const value = this.navigateContextPath(sourceColumn, context.extraMergeContext);
        return value ?? field.defaultValue ?? '';
      }

      return field.defaultValue || '';
    } catch (error) {
      this.logger.error(`Error resolving context field ${field.keyName}: ${error.message}`, error.stack);
      return field.defaultValue || '';
    }
  }

  /**
   * Navigate nested object path
   * @param path - Dot-separated path (e.g., "coordinatorInfo.firstName")
   * @param context - Object to navigate
   * @returns Value at path or undefined
   */
  private navigateContextPath(path: string, context: any): any {
    const pathParts = path.split('.');
    let value = context;
    
    for (const part of pathParts) {
      if (value && typeof value === 'object' && part in value) {
        value = value[part];
      } else {
        this.logger.debug(`Path ${path} not found in context`);
        return undefined;
      }
    }
    
    return value;
  }

  /**
   * Fetch merge field value from database based on field configuration
   * @param field - Merge field configuration
   * @param registrationId - Registration ID (null for common fields)
   * @param manager - Optional transaction manager
   * @returns The fetched value or null
   */
  private async fetchMergeFieldValueFromDB(
    field: MergeInfoAnswerLocationMap,
    registrationId: number | null,
    manager?: any,
  ): Promise<any> {
    try {
      const { sourceTable, sourceColumn } = field;
      
      // Skip computed fields - they are handled via executeComputedFunction
      if (sourceTable === 'computed' || !sourceTable || !sourceColumn) {
        return field.defaultValue || '';
      }

      // Use the caller's transaction manager when provided so freshly-inserted rows in an
      // open transaction (e.g. the offline payment_detail row created just before the ack
      // email) are visible; fall back to the repository manager otherwise.
      const queryManager = manager || this.mergeInfoRepository.manager;
      const query = queryManager
        .createQueryBuilder()
        .select(`${sourceTable}.${sourceColumn}`, 'value')
        .from(sourceTable, sourceTable);
      
      let result: any = null;
      try {
        if (field.isCommon) {
          query.limit(1);
          const sql = query.getSql();
          this.logger.debug(`[MERGE_FIELD] Executing common field query for ${field.keyName}: ${sql}`);
          result = await query.getRawOne();
        } else {
          if (!registrationId) {
            return field.defaultValue || '';
          }
          const columnName = getRegistrationColumnName(sourceTable);
          query.where(`${sourceTable}.${columnName} = :registrationId`, { registrationId });
          const sql = query.getSql();
          this.logger.debug(`[MERGE_FIELD] Executing per-record field query for ${field.keyName}: ${sql} [registrationId=${registrationId}]`);
          result = await query.getRawOne();
        }
      } catch (queryError) {
        const attemptedSql = query.getSql();
        this.logger.error(
          `[MERGE_FIELD_ERROR] Failed to fetch field ${field.keyName} from ${sourceTable}.${sourceColumn}`,
          queryError.stack,
          {
            fieldKey: field.keyName,
            sourceTable,
            sourceColumn,
            isCommon: field.isCommon,
            registrationId,
            attemptedSql,
            error: queryError.message,
          }
        );
        // Return default value to prevent transaction abort
        return field.defaultValue || '';
      }
      
      if (result && result.value !== undefined && result.value !== null) {
        return result.value;
      }
      return field.defaultValue || '';
    } catch (error) {
      this.logger.error(`Error fetching field ${field.keyName}: ${error.message}`);
      return field.defaultValue || '';
    }
  }

  // ==================== COMPUTED FIELD FUNCTIONS ====================
  // All functions below are registered in computedFunctionRegistry
  // They are called based on the sourceColumn value in merge_field_map
  
  /**
   * Generate payment link with AES-encrypted registrationId
   * Function name in DB: generatePaymentLink
   */
  private async generatePaymentLinkField(context: ComputedFieldContext): Promise<string> {
    const { programId, registrationId, userId, fieldKey } = context;

    if (!registrationId) {
      this.logger.warn('generatePaymentLink: Missing registrationId');
      return '';
    }
    let paymentMode: PaymentModeEnum | undefined;
    let isBillingEdit = false;

    switch (fieldKey) {
      case 'payment_online_link':
        paymentMode = PaymentModeEnum.ONLINE;
        break;
      case 'payment_link':
      case 'link': {
        const effectiveManager = context.manager || this.manager;
        try {
          const row = await effectiveManager
            .createQueryBuilder()
            .select('payment.payment_mode')
            .from('hdb_registration_payment_detail', 'payment')
            .where('payment.registration_id = :registrationId', { registrationId })
            .getRawOne();
          paymentMode = (row?.payment_mode as PaymentModeEnum) ?? undefined;
        } catch (error) {
          this.logger.warn(`generatePaymentLink: Failed to look up payment mode for registration ${registrationId}, defaulting to ONLINE`);
        }
        break;
      }
      case 'payment_back_transfer_link':
        paymentMode = PaymentModeEnum.DIRECT_BANK_TRANSFER;
        break;
      case 'payment_cheque_link':
        paymentMode = PaymentModeEnum.CHEQUE;
        break;
      case 'payment_cash_link':
        paymentMode = PaymentModeEnum.CASH;
        break;
      case 'billing_edit_link':
        isBillingEdit = true;
        paymentMode = undefined;
        break;
      default:
        this.logger.warn(`Unknown field key for generatePaymentLink: ${fieldKey}`);
        paymentMode = PaymentModeEnum.ONLINE;
    }

    return generatePaymentLink(programId, registrationId, userId, paymentMode, isBillingEdit, true);
  }

  /**
   * Generate registration edit link
   * Used for: reg_edit_link
   * Function name in DB: registrationEditLink
   */
  private async generateRegistrationEditLinkField(context: ComputedFieldContext): Promise<string> {
    const { programId, userId } = context;
    
    if (!programId) {
      this.logger.warn('registrationEditLink: Missing programId');
      return '';
    }

    // Generate registration edit link (no payment, no edit payment mode)
    return this.registrationEditLink(programId, userId);
  }

  private registrationEditLink(programId:number, userId:number | undefined): string {
      const baseUrl = process.env.SEEKER_FE_BASE_URL;
      if (!baseUrl) {
        this.logger.error('SEEKER_FE_BASE_URL not configured in environment');
        return '';
      }
      let url = `${baseUrl}${SEEKER_FE_REG_PATH}${programId}${SEEKER_FE_EDIT_REGISTRATION_PATH}`;
      if (userId) {
        url += `&userId=${userId}`;
      }
      return url;
    }
  /**
   * Format program date range
   * Used for: hdb_dates, hdb_msd_date
   * Function name in DB: formatProgramDateRange
   * Fetches program dates and formats as "DD-MM-YYYY to DD-MM-YYYY"
   */
  private async formatProgramDateRangeField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn('formatProgramDateRange: Missing registrationId or manager');
      return '';
    }

    try {
      // Fetch allocated_program_id and program_id
      const reg = await manager
        .createQueryBuilder()
        .select(['reg.allocated_program_id', 'reg.program_id'])
        .from('hdb_program_registration', 'reg')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();

      let programData;
      if (reg?.allocated_program_id) {
        // Use allocated program dates
        programData = await manager
          .createQueryBuilder()
          .select(['prog.starts_at', 'prog.ends_at'])
          .from('program_v1', 'prog')
          .where('prog.id = :programId', { programId: reg.allocated_program_id })
          .getRawOne();
      } else if (reg?.program_id) {
        // Use main program dates
        programData = await manager
          .createQueryBuilder()
          .select(['prog.starts_at', 'prog.ends_at'])
          .from('program_v1', 'prog')
          .where('prog.id = :programId', { programId: reg.program_id })
          .getRawOne();
      }

      if (!programData?.starts_at || !programData?.ends_at) {
        this.logger.warn('Program dates not found for registration', { registrationId });
        return '';
      }

      return `${formatDateIST(programData.starts_at)} to ${formatDateIST(programData.ends_at)}`;
    } catch (error) {
      this.logger.error(`Error in formatProgramDateRange: ${error.message}`, error.stack);
      return '';
    }
  }

  /**
   * Calculate total payment amount (original + GST)
   * Used for: hdb_msd_amount, pay_amount
   * Function name in DB: calculateTotalPaymentAmount
   */
  private async calculateTotalPaymentAmountField(context: ComputedFieldContext): Promise<number> {
    const { registrationId, manager } = context;
    
    if (!registrationId || !manager) {
      this.logger.warn('calculateTotalPaymentAmount: Missing registrationId or manager');
      return 0;
    }

    try {
      const paymentData = await manager
        .createQueryBuilder()
        .select(['payment.original_amount', 'payment.gst_amount'])
        .from('hdb_registration_payment_detail', 'payment')
        .where('payment.registration_id = :registrationId', { registrationId })
        .getRawOne();

      if (!paymentData) {
        return 0;
      }

      const original = Number(paymentData.original_amount) || 0;
      const gst = Number(paymentData.gst_amount) || 0;
      return original + gst;
    } catch (error) {
      this.logger.error(`Error in calculateTotalPaymentAmount: ${error.message}`, error.stack);
      return 0;
    }
  }

  /**
   * Get allocated program name
   * Used for: last_allocated_hdb_msd
   * Function name in DB: lastAllocatedProgram
   * Logic: current allocated_program_id -> last approved track's allocated program -> 'HDB/MSD'
   */
  private async getAllocatedProgramNameField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    
    if (!registrationId || !manager) {
      this.logger.warn('lastAllocatedProgram: Missing registrationId or manager');
      return 'HDB/MSD';
    }

    try {
      // Mirror service logic: lastApprovedTrack?.allocatedProgram?.name || 'HDB/MSD'
      // Find the last approved registration_approval_track entry and get its allocated program name
      const lastApprovedTrack = await manager
        .createQueryBuilder()
        .select('prog.name', 'allocated_program_name')
        .from('registration_approval_track', 'track')
        .leftJoin('program_v1', 'prog', 'track.allocated_program_id = prog.id')
        .where('track.registration_id = :registrationId', { registrationId })
        .andWhere('track.approval_status = :status', { status: 'approved' })
        .orderBy('track.created_at', 'DESC')
        .limit(1)
        .getRawOne();

      return lastApprovedTrack?.allocated_program_name || 'HDB/MSD';
    } catch (error) {
      this.logger.error(`Error in getAllocatedProgramName: ${error.message}`, error.stack);
      return 'HDB/MSD';
    }
  }

  /**
   * Format preference array for message display
   * Used for: old_pref, new_pref
   * Function name in DB: formatPreferenceForMessage
   * Expects extraMergeContext to contain oldPref or newPref arrays
   */
  private async formatPreferenceForMessageField(context: ComputedFieldContext): Promise<string> {
    const { fieldKey, extraMergeContext } = context;
    
    // Determine which preference array to format based on field key
    let preferences: any[];
    if (fieldKey === 'old_pref' && extraMergeContext?.oldPref) {
      preferences = extraMergeContext.oldPref;
    } else if (fieldKey === 'new_pref' && extraMergeContext?.newPref) {
      preferences = extraMergeContext.newPref;
    } else {
      this.logger.warn(`formatPreferenceForMessage: No preference array found for field '${fieldKey}'`);
      return '';
    }

    // Format preference array: sort by priority and join session names
    if (!preferences || !Array.isArray(preferences) || preferences.length === 0) {
      return 'Any HDB/MSD';
    }
    
    const sorted = [...preferences].sort(
      (a, b) => (a.priorityOrder ?? 0) - (b.priorityOrder ?? 0),
    );
    return sorted.map((p) => p.sessionName).join(', ');
  }

  /**
   * Generate OTP
   * Used for: otp
   * Function name in DB: generateOTP
   */
  private async generateOTPField(context: ComputedFieldContext): Promise<string> {
    // Generate a 6-digit OTP
    const otp = Math.floor(100000 + Math.random() * 900000).toString();
    return otp;
  }

  /**
   * Get current date
   * Used for: currentDate
   * Function name in DB: currentDate
   */
  private async getCurrentDateField(context: ComputedFieldContext): Promise<string> {
    const now = new Date();
    const day = String(now.getDate()).padStart(2, '0');
    const month = String(now.getMonth() + 1).padStart(2, '0');
    const year = now.getFullYear();
    return `${day}-${month}-${year}`;
  }

  /**
   * Get RM contact user full name
   * Used for: rm_name
   * Function name in DB: getRmContactUserName
   * Logic matches old service: orgUsrName || (firstName + lastName) || fullName
   */
  private async getRmContactUserNameField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    this.logger.debug(`getRmContactUserNameField called with registration ${registrationId}`);
    if (!registrationId || !manager) {
      this.logger.warn('getRmContactUserName: Missing registrationId or manager');
      return '';
    }

    try {
      const rmUserData = await manager
        .createQueryBuilder()
        .select('rm_user.org_usr_name', 'orgUsrName')
        .addSelect('rm_user.first_name', 'firstName')
        .addSelect('rm_user.last_name', 'lastName')
        .addSelect('rm_user.full_name', 'fullName')
        .addSelect('reg.other_infinitheism_contact', 'otherInfinitheismContact')
        .from('hdb_program_registration', 'reg')
        .leftJoin('users', 'rm_user', 'reg.rm_contact = rm_user.id')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();

      // Match old service logic: orgUsrName || (firstName + lastName) || fullName
      let resolvedName = '';
      if (rmUserData?.orgUsrName) {
        resolvedName = rmUserData.orgUsrName;
      } else if (rmUserData?.firstName && rmUserData?.lastName) {
        resolvedName = `${rmUserData.firstName} ${rmUserData.lastName}`;
      } else {
        resolvedName = rmUserData?.fullName || '';
      }

      // When the seeker's infinitheism contact is "Other", the RM user record holds the literal
      // "Other"; the actual contact name is captured free-text on the registration instead.
      if (resolvedName.trim().toLowerCase() === 'other') {
        return rmUserData?.otherInfinitheismContact || '';
      }

      return resolvedName;
    } catch (error) {
      this.logger.error('Error in getRmContactUserName', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Look up a single column on the seeker (users) row via the registration FK.
   * Shared by getUserFirstName / getUserLastName / getUserGender / getUserDOB.
   * Returns null when registration, user, or column is missing.
   */
  private async getSeekerUserColumn(
    context: ComputedFieldContext,
    column: 'first_name' | 'last_name' | 'gender' | 'dob',
    fnName: string,
  ): Promise<any> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn(`${fnName}: Missing registrationId or manager`);
      return null;
    }
    try {
      const row = await manager
        .createQueryBuilder()
        .select(`seeker.${column}`, 'value')
        .from('hdb_program_registration', 'reg')
        .leftJoin('users', 'seeker', 'reg.user_id = seeker.id')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();
      return row?.value ?? null;
    } catch (error) {
      this.logger.error(`Error in ${fnName}`, error.stack);
      return null;
    }
  }

  /**
   * Seeker first name. Catalog key: seeker.first_name. Function name in DB: getUserFirstName
   */
  private async getUserFirstNameField(context: ComputedFieldContext): Promise<string> {
    return (await this.getSeekerUserColumn(context, 'first_name', 'getUserFirstName')) || '';
  }

  /**
   * Seeker last name. Catalog key: seeker.last_name. Function name in DB: getUserLastName
   */
  private async getUserLastNameField(context: ComputedFieldContext): Promise<string> {
    return (await this.getSeekerUserColumn(context, 'last_name', 'getUserLastName')) || '';
  }

  /**
   * Seeker gender. Catalog key: seeker.gender. Function name in DB: getUserGender
   */
  private async getUserGenderField(context: ComputedFieldContext): Promise<string> {
    return (await this.getSeekerUserColumn(context, 'gender', 'getUserGender')) || '';
  }

  /**
   * Seeker date of birth, formatted DD-MM-YYYY. Catalog key: seeker.date_of_birth.
   * Function name in DB: getUserDOB
   */
  private async getUserDOBField(context: ComputedFieldContext): Promise<string> {
    const dob = await this.getSeekerUserColumn(context, 'dob', 'getUserDOB');
    if (!dob) {
      return '';
    }
    const d = new Date(dob);
    if (isNaN(d.getTime())) {
      return '';
    }
    const day = String(d.getDate()).padStart(2, '0');
    const month = String(d.getMonth() + 1).padStart(2, '0');
    return `${day}-${month}-${d.getFullYear()}`;
  }

  /**
   * RM contact email. Catalog key: rm.email. Function name in DB: getRmContactEmail
   * Mirrors getRmContactUserName lookup path (reg.rm_contact -> users).
   */
  private async getRmContactEmailField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn('getRmContactEmail: Missing registrationId or manager');
      return '';
    }
    try {
      const row = await manager
        .createQueryBuilder()
        .select('rm_user.email', 'email')
        .from('hdb_program_registration', 'reg')
        .leftJoin('users', 'rm_user', 'reg.rm_contact = rm_user.id')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();
      return row?.email || '';
    } catch (error) {
      this.logger.error('Error in getRmContactEmail', error.stack);
      return '';
    }
  }

  /**
   * RM contact mobile (country code + phone). Catalog key: rm.mobile.
   * Function name in DB: getRmContactMobile
   */
  private async getRmContactMobileField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;
    if (!registrationId || !manager) {
      this.logger.warn('getRmContactMobile: Missing registrationId or manager');
      return '';
    }
    try {
      const row = await manager
        .createQueryBuilder()
        .select('rm_user.country_code', 'countryCode')
        .addSelect('rm_user.phone_number', 'phoneNumber')
        .from('hdb_program_registration', 'reg')
        .leftJoin('users', 'rm_user', 'reg.rm_contact = rm_user.id')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();
      if (row?.countryCode && row?.phoneNumber) {
        return `${row.countryCode} ${row.phoneNumber}`;
      }
      return row?.phoneNumber || '';
    } catch (error) {
      this.logger.error('Error in getRmContactMobile', error.stack);
      return '';
    }
  }

  /**
   * Look up a single column on the program (program_v1) row via context.programId.
   * Shared by org-level fields. Returns null when programId or column is missing.
   */
  private async getProgramColumn(
    context: ComputedFieldContext,
    column: 'email_sender_name' | 'name' | 'helpline_number',
    fnName: string,
  ): Promise<any> {
    const { programId, manager } = context;
    if (!programId || !manager) {
      this.logger.warn(`${fnName}: Missing programId or manager`);
      return null;
    }
    try {
      const row = await manager
        .createQueryBuilder()
        .select(`prog.${column}`, 'value')
        .from('program_v1', 'prog')
        .where('prog.id = :programId', { programId })
        .getRawOne();
      return row?.value ?? null;
    } catch (error) {
      this.logger.error(`Error in ${fnName}`, error.stack);
      return null;
    }
  }

  /**
   * Org name (sender name in emails, falling back to program name). Catalog key: org.name.
   * Function name in DB: getOrgName
   */
  private async getOrgNameField(context: ComputedFieldContext): Promise<string> {
    const sender = await this.getProgramColumn(context, 'email_sender_name', 'getOrgName');
    if (sender) {
      return sender;
    }
    return (await this.getProgramColumn(context, 'name', 'getOrgName')) || '';
  }

  /**
   * Org helpline number. Catalog key: org.helpline_number. Function name in DB: getOrgHelplineNumber
   */
  private async getOrgHelplineNumberField(context: ComputedFieldContext): Promise<string> {
    return (await this.getProgramColumn(context, 'helpline_number', 'getOrgHelplineNumber')) || '';
  }

  /**
   * Org support email. Catalog key: org.support_email. Function name in DB: getOrgSupportEmail
   * No per-program column exists; sourced from env (SUPPORT_EMAIL).
   */
  private async getOrgSupportEmailField(_context: ComputedFieldContext): Promise<string> {
    return process.env.SUPPORT_EMAIL || '';
  }

  /**
   * Approval CTA link. Catalog key: action.approve_link. Function name in DB: generateApproveLink
   */
  private async generateApproveLinkField(context: ComputedFieldContext): Promise<string> {
    return this.buildAdminActionLink(context, 'approve');
  }

  /**
   * Rejection CTA link. Catalog key: action.reject_link. Function name in DB: generateRejectLink
   */
  private async generateRejectLinkField(context: ComputedFieldContext): Promise<string> {
    return this.buildAdminActionLink(context, 'reject');
  }

  /**
   * View-registration CTA link. Catalog key: action.view_registration_link.
   * Function name in DB: generateViewRegistrationLink
   */
  private async generateViewRegistrationLinkField(context: ComputedFieldContext): Promise<string> {
    return this.buildAdminActionLink(context, 'view');
  }

  /**
   * Build an admin-portal CTA link (approve / reject / view) following the
   * generatePaymentLink pattern: env-var base URL + querystring with registrationId/programId.
   */
  private buildAdminActionLink(
    context: ComputedFieldContext,
    action: 'approve' | 'reject' | 'view',
  ): string {
    const { programId, registrationId } = context;
    const baseUrl = process.env.ADMIN_FE_BASE_URL;
    if (!baseUrl) {
      this.logger.error('ADMIN_FE_BASE_URL not configured in environment');
      return '';
    }
    if (!registrationId) {
      this.logger.warn(`generate ${action} link: Missing registrationId`);
      return '';
    }
    const params = new URLSearchParams();
    params.set('registrationId', String(registrationId));
    if (programId) {
      params.set('programId', String(programId));
    }
    params.set('action', action);
    return `${baseUrl}registrations/action?${params.toString()}`;
  }

  /**
   * Get user formatted phone number (country code + phone number)
   * Used for: user_phone in SMS templates
   * Function name in DB: getUserFormattedPhone
   */
  private async getUserFormattedPhoneField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;

    if (!registrationId || !manager) {
      this.logger.warn('getUserFormattedPhone: Missing registrationId or manager');
      return '';
    }

    try {
      // Get user details via registration
      const userData = await manager
        .createQueryBuilder()
        .select('user.country_code', 'countryCode')
        .addSelect('user.phone_number', 'phoneNumber')
        .from('hdb_program_registration', 'reg')
        .leftJoin('users', 'user', 'reg.user_id = user.id')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();

      if (userData?.countryCode && userData?.phoneNumber) {
        return `${userData.countryCode} ${userData.phoneNumber}`;
      }
      
      // Fallback: get from registration mobile_number
      const reg = await manager
        .createQueryBuilder()
        .select('reg.mobile_number', 'mobileNumber')
        .from('hdb_program_registration', 'reg')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();
      
      return reg?.mobileNumber ? '+'.concat(reg.mobileNumber.replace('+', '')) : '';
    } catch (error) {
      this.logger.error('Error in getUserFormattedPhone', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Format allocated program date range (e.g., "HDB MSD 25-26 from 01-01-2026 to 10-01-2026")
   * Used for: program_dates in SMS templates
   * Function name in DB: formatAllocatedProgramDateRange
   */
  private async formatAllocatedProgramDateRangeField(context: ComputedFieldContext): Promise<string> {
    const { registrationId, manager } = context;

    if (!registrationId || !manager) {
      this.logger.warn('formatAllocatedProgramDateRange: Missing registrationId or manager');
      return '';
    }

    try {
      const programData = await manager
        .createQueryBuilder()
        .select('prog.name', 'name')
        .addSelect('prog.starts_at', 'startsAt')
        .addSelect('prog.ends_at', 'endsAt')
        .from('hdb_program_registration', 'reg')
        .leftJoin('program_v1', 'prog', 'reg.allocated_program_id = prog.id')
        .where('reg.id = :registrationId', { registrationId })
        .getRawOne();

      if (!programData?.name || !programData?.startsAt || !programData?.endsAt) {
        return '';
      }

      const startDate = formatDateIST(programData.startsAt.toISOString());
      const endDate = formatDateIST(programData.endsAt.toISOString());
      
      return `${programData.name} from ${startDate} to ${endDate}`;
    } catch (error) {
      this.logger.error('Error in formatAllocatedProgramDateRange', error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Get coordinator (SHOBA role) full name
   * Used for: coordinator_name in travel plan templates
   * Function name in DB: getCoordinatorName
   * Returns the first coordinator's name from the SHOBA role users
   */
  private async getCoordinatorNameField(context: ComputedFieldContext): Promise<string> {
    const { manager } = context;
    this.logger.debug('getCoordinatorNameField called');
    if (!manager) {
      this.logger.warn('getCoordinatorName: Missing manager');
      return 'Coordinator';
    }

    try {
      // Get the first coordinator with SHOBA role
      const coordinatorData = await manager
        .createQueryBuilder()
        .select([
          'COALESCE(NULLIF(users.first_name || \' \' || users.last_name, \' \'), users.full_name, users.legal_full_name, \'Coordinator\') as coordinator_name'
        ])
        .from('users', 'users')
        .innerJoin('user_role_maps', 'urm', 'urm.user = users.id')
        .innerJoin('roles', 'role', 'role.id = urm.role')
        .where('role.name = :roleName', { roleName: 'shoba' })
        .andWhere('users.deleted_at IS NULL')
        .orderBy('users.id', 'ASC')
        .limit(1)
        .getRawOne();

      return coordinatorData?.coordinator_name || 'Coordinator';
    } catch (error) {
      this.logger.error('Error in getCoordinatorName', error.stack);
      return 'Coordinator';
    }
  }

  /**
   * Count pending payments for a specific RM and program
   * Used for: pending_count in payment reminder templates
   * Function name in DB: countPendingPayments
   * Requires: programId and rmContactId in extraMergeContext
   */
  private async countPendingPaymentsField(context: ComputedFieldContext): Promise<number> {
    const { programId, manager, extraMergeContext } = context;
    const rmContactId = extraMergeContext?.rmContactId;
    
    this.logger.debug(`countPendingPaymentsField called for program ${programId}, RM ${rmContactId}`);
    
    if (!programId || !rmContactId || !manager) {
      this.logger.warn('countPendingPayments: Missing programId, rmContactId, or manager');
      return 0;
    }

    try {
      // Query registrations with pending payments for this RM and program
      const result = await manager
        .createQueryBuilder()
        .select('COUNT(DISTINCT reg.id)', 'count')
        .from('hdb_program_registration', 'reg')
        .leftJoin('program_payment_details_v1', 'payment', 'payment.registration_id = reg.id AND payment.deleted_at IS NULL')
        .leftJoin('program_v1', 'program', 'program.id = reg.program_id AND program.deleted_at IS NULL')
        .leftJoin('program_type_v1', 'program_type', 'program_type.id = program.program_type_id')
        .where('reg.rm_contact = :rmContactId', { rmContactId })
        .andWhere('reg.program_id = :programId', { programId })
        .andWhere('reg.is_free_seat = :isFreeSeat', { isFreeSeat: false })
        .andWhere('payment.payment_status IN (:...paymentStatuses)', {
          paymentStatuses: [
            PaymentStatusEnum.ONLINE_PENDING,
            PaymentStatusEnum.OFFLINE_PENDING,
            PaymentStatusEnum.DRAFT,
            PaymentStatusEnum.FAILED,
          ],
        })
        .andWhere('reg.registration_status NOT IN (:...excludedStatuses)', {
          excludedStatuses: [
            RegistrationStatusEnum.SAVE_AS_DRAFT,
            RegistrationStatusEnum.CANCELLED,
          ],
        })
        .andWhere('reg.deleted_at IS NULL')
        .getRawOne();

      const count = parseInt(result?.count || '0', 10);
      this.logger.debug(`Found ${count} pending payments for RM ${rmContactId}, program ${programId}`);
      return count;
    } catch (error) {
      this.logger.error(`Error in countPendingPayments for program ${programId}, RM ${rmContactId}`, error.stack);
      handleKnownErrors(ERROR_CODES.COMMUNICATION_TEMPLATE_MASTER_GET_FAILED, error);
    }
  }

  /**
   * Generate a JWT-signed travel plan link for the seeker.
   * Function name in DB: generateTravelPlanLink
   */
  private async generateTravelPlanLinkField(context: ComputedFieldContext): Promise<string> {
    const { registrationId } = context;
    if (!registrationId) {
      this.logger.warn('generateTravelPlanLink: Missing registrationId');
      return '';
    }
    return generateTravelPlanLink(registrationId, true);
  }
}