import { Injectable } from '@nestjs/common'

import { AppLoggerService } from 'src/common/services/logger.service'
import { ProgramRepository } from 'src/program/program.repository'
import { ReportsRepository } from './reports.repository'
import { ERROR_CODES } from 'src/common/constants/error-string-constants'
import { handleKnownErrors } from 'src/common/utils/handle-error.util'
import InifniBadRequestException from 'src/common/exceptions/infini-badrequest-exception'
import { ReportFieldDef, ReportRow } from './types/report-field.types'
import { formatValue, FormatContext } from './utils/report-formatter.util'
import { ExcelService } from 'src/common/services/excel.service'
import { AwsS3Service } from 'src/common/services/awsS3.service'
import { GenerateReportDto, ReportFilterDto } from './dto/generate-report.dto'
import { GenerateSessionAttendanceReportDto, SessionAttendanceReportFilterDto } from './dto/generate-session-attendance-report.dto'
import {
  SESSION_ATTENDANCE_REPORT_PURPOSE_FIELDS,
  SESSION_ATTENDANCE_REPORT_FEATURE_FLAGS,
  REPORT_DOWNLOAD_FEATURE_FLAG_KEYS,
  SESSION_DROPOFF_FIELD_DEFS,
  GENERAL_ATTENDEE_REPORT_FIELD_DEFS,
  GENERAL_SESSION_REPORT_FIELD_DEFS,
  GENERAL_LATE_COMER_REPORT_FIELD_DEFS,
  GENERAL_SESSION_DROPOFF_FIELD_DEFS,
} from './constants/session-attendance-report.constants'
import { DownloadZoomLinksDto } from './dto/download-zoom-links.dto'
import { normalizeKpiPatterns } from 'src/common/utils/kpi-pattern.util'
import { formatDateTimeIST } from 'src/common/utils/common.util'
import { OnlineSessionRegistrationStatus } from 'src/common/enum/online-session-registration-status.enum'
import { RegistrationOnlineSessionActivationStatus } from 'src/common/enum/registration-online-session-activation-status.enum'
import { FEATURE_FLAG_KEYS } from 'src/common/constants/constants'
import { FeatureFlagService } from 'src/feature-flag/feature-flag.service'
import { ROLE_VALUES } from 'src/common/constants/strings-constants'

export interface ReportTableHeader {
  key: string
  label: string
  alias: string
  order: number
  type: string
  sortable: boolean
  filterable: boolean
}

export interface TransformedReport {
  data: ReportRow[]
  tableHeaders: ReportTableHeader[]
  total: number
  // Only populated by the eligible-registrations-attendance purpose, whose columns (Session
  // 1..N) are dynamic per program and can't come from the static SQL field-defs registry —
  // carried here so the controller's excel branch can pass it to buildSessionAttendanceExcel.
  fieldDefs?: Record<string, ReportFieldDef>
}

export interface ReportUserContext { userRoles: string[]; userId: number | null }

// Field definitions live in the SQL function fn_get_report_field_definitions() and change
// only when that migration is re-run against the DB — never through the app. A short TTL keeps
// the cache warm yet lets a re-run migration surface within TTL_MS without an app restart.
const FIELD_DEFS_CACHE_TTL_MS = 60_000 // 1 minute

const ELIGIBLE_REGISTRATIONS_BASE_FIELDS = ['registrationSeqNumber', 'fullName', 'email', 'phone', 'activationStatus'] as const

interface EligibleRegistrationsAttendanceRow {
  reg_id: number
  session_id: number
  session_display_order: number
  registrationSeqNumber: string | number | null
  fullName: string | null
  email: string | null
  phone: string | null
  activationStatus: string | null
  attendanceStatus: 'Attended' | 'Not Attended' | null
}

@Injectable()
export class ReportsService {
  private fieldDefsCache: Record<string, ReportFieldDef> | null = null
  private fieldDefsCachedAt = 0
  private sessionAttendanceFieldDefsCache: Record<string, ReportFieldDef> | null = null
  private sessionAttendanceFieldDefsCachedAt = 0

  constructor(
    private readonly reportsRepository: ReportsRepository,
    private readonly programRepository: ProgramRepository,
    private readonly logger: AppLoggerService,
    private readonly excelService: ExcelService,
    private readonly awsS3Service: AwsS3Service,
    private readonly featureFlagService: FeatureFlagService,
  ) {}

  // Report-download flags are scoped by program TYPE (program_type_v1 — HDB/MSD/TAT/...), not by
  // individual program, since the same toggle should apply to every program of that type at once.
  private async resolveProgramTypeId(programId: number): Promise<number> {
    const program = await this.programRepository.findOneById(programId)
    if (!program) {
      handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${programId} not found`))
    }
    return program.typeId
  }

  // Throws RPT_BR_004 unless EVERY given feature flag is enabled for this program's type (AND, not
  // OR — currently every purpose maps to exactly one key, but this stays array-based/AND in case a
  // purpose ever needs more than one). A type-scoped row wins over the global one for the same
  // key — see FeatureFlagService.isFeatureEnabledForProgramType.
  private async assertReportFeatureEnabled(featureKeys: readonly string[], programId: number): Promise<void> {
    const programTypeId = await this.resolveProgramTypeId(programId)
    for (const featureKey of featureKeys) {
      const enabled = await this.featureFlagService.isFeatureEnabledForProgramType(featureKey, programTypeId)
      if (!enabled) {
        throw new InifniBadRequestException(ERROR_CODES.REPORT_FEATURE_DISABLED, null, null, featureKey)
      }
    }
  }

  // UI-facing (infinipath-web-admin): "which report-download buttons should this program show?"
  // Returns the enabled state of all 11 gated report features (REPORT_DOWNLOAD_FEATURE_FLAG_KEYS),
  // keyed by their FEATURE_FLAG_KEYS value, resolved through this program's type.
  // enableSingleSessionReport is unrelated to the 4 individual single-session flags — it only
  // reflects isSessionReportEnabled (the Sessions list's own affordance).
  async getReportFeatureFlags(programId: number): Promise<Record<string, boolean>> {
    const programTypeId = await this.resolveProgramTypeId(programId)
    const entries = await Promise.all(
      REPORT_DOWNLOAD_FEATURE_FLAG_KEYS.map(async key =>
        [key, await this.featureFlagService.isFeatureEnabledForProgramType(key, programTypeId)] as const,
      ),
    )
    return Object.fromEntries(entries)
  }

  private async getFieldDefsMap(): Promise<Record<string, ReportFieldDef>> {
    if (this.fieldDefsCache && Date.now() - this.fieldDefsCachedAt < FIELD_DEFS_CACHE_TTL_MS) {
      return this.fieldDefsCache
    }
    const defs = await this.reportsRepository.getAllFieldDefinitions()
    this.logger.debug(`Fetched ${defs.length} report field definitions from database`)
    this.fieldDefsCache = Object.fromEntries(defs.map(d => [d.key, d]))
    this.fieldDefsCachedAt = Date.now()
    return this.fieldDefsCache
  }

  private async getSessionAttendanceFieldDefsMap(): Promise<Record<string, ReportFieldDef>> {
    if (this.sessionAttendanceFieldDefsCache && Date.now() - this.sessionAttendanceFieldDefsCachedAt < FIELD_DEFS_CACHE_TTL_MS) {
      return this.sessionAttendanceFieldDefsCache
    }
    const defs = await this.reportsRepository.getAllSessionAttendanceFieldDefinitions()
    this.logger.debug(`Fetched ${defs.length} session attendance report field definitions from database`)
    this.sessionAttendanceFieldDefsCache = Object.fromEntries(defs.map(d => [d.key, d]))
    this.sessionAttendanceFieldDefsCachedAt = Date.now()
    return this.sessionAttendanceFieldDefsCache
  }

  async getAvailableSessionAttendanceFields(programId: number): Promise<{ key: string; label: string; group: string; order: number }[]> {
    try {
      const program = await this.programRepository.findOneById(programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${programId} not found`))
      }
      const allDefs = await this.getSessionAttendanceFieldDefsMap()
      return Object.values(allDefs).map(def => ({ key: def.key, label: def.label, group: def.group, order: def.order }))
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  async generateSessionAttendanceReport(
    dto: GenerateSessionAttendanceReportDto,
    userContext?: ReportUserContext,
  ): Promise<TransformedReport> {
    const gatingFeatureKeys = dto.purpose ? SESSION_ATTENDANCE_REPORT_FEATURE_FLAGS[dto.purpose] : undefined
    if (gatingFeatureKeys?.length) {
      await this.assertReportFeatureEnabled(gatingFeatureKeys, dto.filters.programId)
    }

    // RM users may only see their own seekers — registrant-based purposes below thread this
    // into the SQL filter; the general-attendee-family purposes have no registration/RM to
    // scope by at all (see generateGeneralAttendeeFamilyReport's own comment), so they ignore it.
    const rmContactId = this.resolveRmContactId(userContext)

    if (dto.purpose === 'eligible-registrations-attendance') {
      return this.generateEligibleRegistrationsAttendanceReport(dto.filters, rmContactId)
    }
    if (dto.purpose === 'session-dropoffs') {
      return this.generateSessionDropoffsReport(dto.filters, rmContactId)
    }
    if (dto.purpose === 'general-attendees') {
      return this.generateGeneralAttendeeFamilyReport(dto.filters, GENERAL_ATTENDEE_REPORT_FIELD_DEFS)
    }
    if (dto.purpose === 'general-session-report') {
      return this.generateGeneralAttendeeFamilyReport(dto.filters, GENERAL_SESSION_REPORT_FIELD_DEFS)
    }
    if (dto.purpose === 'general-late-comer-report') {
      return this.generateGeneralAttendeeFamilyReport(dto.filters, GENERAL_LATE_COMER_REPORT_FIELD_DEFS, true)
    }
    if (dto.purpose === 'general-session-dropoffs') {
      return this.generateGeneralSessionDropoffsReport(dto.filters)
    }

    try {
      const allDefs = await this.getSessionAttendanceFieldDefsMap()
      const fields = this.resolveSessionAttendanceReportFields(dto)

      const invalidFields = fields.filter(f => !allDefs[f])
      if (invalidFields.length) {
        throw new InifniBadRequestException(ERROR_CODES.INVALID_REPORT_FIELDS, null, { invalidFields })
      }

      const filters = dto.filters
      const program = await this.programRepository.findOneById(filters.programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${filters.programId} not found`))
      }

      this.logger.log('Generating session attendance report with filters', { filters, fields, purpose: dto.purpose })

      const cols = fields.map(f => `"${allDefs[f].alias}"`).join(', ')
      const filtersJson = buildFiltersJson({
        programId:          filters.programId,
        sessionId:          filters.sessionId,
        registrationStatus: filters.registrationStatus,
        attended:           filters.attended,
        rmContactId,
      })

      const rows = await this.reportsRepository.fetchSessionAttendanceReportRows(cols, filtersJson)
      // 'late-comer-report' is meant to surface only registrants who actually joined late —
      // the underlying view returns every registrant×session row (lateBy null for on-time/
      // never-joined ones) since other purposes sharing it need those rows too.
      const filteredRows =
        dto.purpose === 'late-comer-report' ? rows.filter(row => row.lateBy !== null && row.lateBy !== undefined) : rows
      const result = this.transformCustomReportData(filteredRows, fields, new Set(), allDefs)

      this.logger.log('Session attendance report generated', { fields, total: result.total })
      return result
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  // `purpose` is a shortcut to a hardcoded field list (see session-attendance-report.constants.ts)
  // so callers don't need to fetch/pick fields for known report types — add a key there per
  // report type instead of a new endpoint. `fields` still works for ad-hoc field selection.
  private resolveSessionAttendanceReportFields(dto: GenerateSessionAttendanceReportDto): string[] {
    if (dto.purpose) {
      return [...SESSION_ATTENDANCE_REPORT_PURPOSE_FIELDS[dto.purpose]]
    }
    if (dto.fields?.length) {
      return dto.fields
    }
    throw new InifniBadRequestException(ERROR_CODES.FIELDS_OR_PURPOSE_REQUIRED)
  }

  async buildSessionAttendanceExcel(
    rows: Record<string, unknown>[],
    fields: string[],
    fileName = 'report',
    defsOverride?: Record<string, ReportFieldDef>,
  ): Promise<string> {
    const defs = defsOverride ?? await this.getSessionAttendanceFieldDefsMap()
    return this.buildExcel(rows, fields, fileName, defs)
  }

  // Single-session drop-off report (purpose: 'session-dropoffs') — reads
  // zoom_analytics_live_event directly via fn_generate_session_dropoff_report, a
  // completely different underlying query/shape than the rest of the session-attendance
  // family, so it's special-cased the same way eligible-registrations-attendance is.
  // Field defs are hardcoded here (not from fn_get_session_attendance_report_field_definitions,
  // which only registers vw_program_session_attendance_report's columns) and carried on
  // result.fieldDefs so the controller's excel branch picks them up instead of the
  // attendance-family registry.
  private async generateSessionDropoffsReport(
    filters: SessionAttendanceReportFilterDto,
    rmContactId?: number,
  ): Promise<TransformedReport> {
    if (!filters.sessionId) {
      throw new InifniBadRequestException(ERROR_CODES.SESSION_ID_REQUIRED_FOR_REPORT)
    }

    try {
      const program = await this.programRepository.findOneById(filters.programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${filters.programId} not found`))
      }

      this.logger.log('Generating session drop-offs report', { filters })

      const filtersJson = buildFiltersJson({ programId: filters.programId, sessionId: filters.sessionId, rmContactId })
      const rows = await this.reportsRepository.fetchSessionDropoffReportRows(filtersJson)
      const fields = Object.keys(SESSION_DROPOFF_FIELD_DEFS)
      const result = this.transformCustomReportData(rows, fields, new Set(), SESSION_DROPOFF_FIELD_DEFS)
      result.fieldDefs = SESSION_DROPOFF_FIELD_DEFS

      this.logger.log('Session drop-offs report generated', { total: result.total })
      return result
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  // General Attendees screen "download" reports (purposes: 'general-attendees',
  // 'general-session-report', 'general-late-comer-report') — all 3 read the same
  // fn_generate_general_attendees_report call (zoom_analytics_attendee_summary joined
  // against zoom_generated_registrant_link), just picking up a different column subset
  // via fieldDefs, the same "one enriched function, purpose picks the columns" shape
  // vw_program_session_attendance_report gives the registered-seeker family — so one
  // shared helper serves all 3 rather than 3 near-identical methods. This is a
  // completely different underlying query/shape than the rest of the session-attendance
  // family, so it's special-cased the same way session-dropoffs is. Field defs are
  // hardcoded here (not from fn_get_session_attendance_report_field_definitions, which
  // only registers vw_program_session_attendance_report's columns) and carried on
  // result.fieldDefs so the controller's excel branch picks them up instead of the
  // attendance-family registry.
  private async generateGeneralAttendeeFamilyReport(
    filters: SessionAttendanceReportFilterDto,
    fieldDefs: Record<string, ReportFieldDef>,
    lateOnly = false,
  ): Promise<TransformedReport> {
    if (!filters.sessionId) {
      throw new InifniBadRequestException(ERROR_CODES.SESSION_ID_REQUIRED_FOR_REPORT)
    }

    try {
      const program = await this.programRepository.findOneById(filters.programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${filters.programId} not found`))
      }

      const fields = Object.keys(fieldDefs)
      this.logger.log('Generating general attendee family report', { filters, fields })

      const filtersJson = buildFiltersJson({ programId: filters.programId, sessionId: filters.sessionId })
      const rows = await this.reportsRepository.fetchGeneralAttendeesReportRows(filtersJson)
      // 'general-late-comer-report' is meant to surface only attendees who actually joined
      // late — see the same reasoning on the registered-seeker 'late-comer-report' path.
      const filteredRows = lateOnly ? rows.filter(row => row.lateBy !== null && row.lateBy !== undefined) : rows
      const result = this.transformCustomReportData(filteredRows, fields, new Set(), fieldDefs)
      result.fieldDefs = fieldDefs

      this.logger.log('General attendee family report generated', { total: result.total })
      return result
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  // General Attendees screen counterpart to 'session-dropoffs' (purpose:
  // 'general-session-dropoffs') — reads zoom_analytics_live_event directly via
  // fn_generate_general_session_dropoff_report, scoped to '+gen'-tagged emails
  // instead of '+reg' (see that function's own header). Field defs are hardcoded
  // here for the same reason generateSessionDropoffsReport's are.
  private async generateGeneralSessionDropoffsReport(filters: SessionAttendanceReportFilterDto): Promise<TransformedReport> {
    if (!filters.sessionId) {
      throw new InifniBadRequestException(ERROR_CODES.SESSION_ID_REQUIRED_FOR_REPORT)
    }

    try {
      const program = await this.programRepository.findOneById(filters.programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${filters.programId} not found`))
      }

      this.logger.log('Generating general session drop-offs report', { filters })

      const filtersJson = buildFiltersJson({ programId: filters.programId, sessionId: filters.sessionId })
      const rows = await this.reportsRepository.fetchGeneralSessionDropoffReportRows(filtersJson)
      const fields = Object.keys(GENERAL_SESSION_DROPOFF_FIELD_DEFS)
      const result = this.transformCustomReportData(rows, fields, new Set(), GENERAL_SESSION_DROPOFF_FIELD_DEFS)
      result.fieldDefs = GENERAL_SESSION_DROPOFF_FIELD_DEFS

      this.logger.log('General session drop-offs report generated', { total: result.total })
      return result
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  // Eligible Registrations screen "download" report — one row per registrant with the
  // per-session attendanceStatus pivoted into dynamic Session 1..N columns (N = the program's
  // session count) instead of one row per registrant × session. Eligibility is collapsed to a
  // single Absent/Eligible column: Absent if the registrant missed ANY conducted session,
  // Eligible otherwise. Reuses the same view/function as the other session-attendance purposes
  // (fn_generate_session_attendance_report) — the pivot happens here because the column count
  // is dynamic per program and doesn't fit the static field-defs-driven column model.
  private async generateEligibleRegistrationsAttendanceReport(
    filters: SessionAttendanceReportFilterDto,
    rmContactId?: number,
  ): Promise<TransformedReport> {
    try {
      const program = await this.programRepository.findOneById(filters.programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${filters.programId} not found`))
      }

      this.logger.log('Generating eligible-registrations-attendance report', { filters })

      const cols = [
        'reg_id', 'session_id', 'session_display_order',
        '"registrationSeqNumber"', '"fullName"', '"email"', '"phone"', '"activationStatus"', '"attendanceStatus"',
      ].join(', ')
      const filtersJson = buildFiltersJson({
        programId:          filters.programId,
        registrationStatus: filters.registrationStatus,
        rmContactId,
      })

      const rows = await this.reportsRepository.fetchSessionAttendanceReportRows(cols, filtersJson) as unknown as EligibleRegistrationsAttendanceRow[]
      const baseDefs = await this.getSessionAttendanceFieldDefsMap()
      const result = this.pivotEligibleRegistrationsAttendance(rows, baseDefs)

      this.logger.log('Eligible registrations attendance report generated', { total: result.total })
      return result
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  private pivotEligibleRegistrationsAttendance(
    rows: EligibleRegistrationsAttendanceRow[],
    baseDefs: Record<string, ReportFieldDef>,
  ): TransformedReport {
    if (!rows.length) {
      return { data: [], tableHeaders: [], total: 0, fieldDefs: {} }
    }

    const byRegistration = new Map<number, EligibleRegistrationsAttendanceRow[]>()
    for (const row of rows) {
      const group = byRegistration.get(row.reg_id) ?? []
      group.push(row)
      byRegistration.set(row.reg_id, group)
    }
    for (const group of byRegistration.values()) {
      group.sort((a, b) => (a.session_display_order - b.session_display_order) || (a.session_id - b.session_id))
    }
    const sessionCount = Math.max(...[...byRegistration.values()].map(group => group.length))

    const data: ReportRow[] = [...byRegistration.values()].map(group => {
      const first = group[0]
      const row: ReportRow = {}
      for (const fieldKey of ELIGIBLE_REGISTRATIONS_BASE_FIELDS) {
        const def = baseDefs[fieldKey]
        row[def.alias] = formatValue(first[fieldKey], this.resolveFormat(def, new Set()), {})
      }

      let absent = false
      group.forEach((sessionRow, index) => {
        row[`session_${index + 1}`] = sessionRow.attendanceStatus
        if (sessionRow.attendanceStatus === 'Not Attended') absent = true
      })
      row.eligibleForFinalSession = absent ? 'Absent' : 'Eligible'

      return row
    })

    const fieldDefs: Record<string, ReportFieldDef> = {}
    const tableHeaders: ReportTableHeader[] = []
    let order = 1
    const pushHeader = (key: string, def: ReportFieldDef): void => {
      fieldDefs[key] = def
      tableHeaders.push({ key, alias: def.alias, label: def.label, order: order++, type: 'string', sortable: false, filterable: false })
    }
    for (const fieldKey of ELIGIBLE_REGISTRATIONS_BASE_FIELDS) {
      pushHeader(fieldKey, baseDefs[fieldKey])
    }
    for (let i = 1; i <= sessionCount; i++) {
      const key = `session_${i}`
      pushHeader(key, {
        key, alias: key, label: `Session ${i}`, group: 'Attendance',
        order: 0, format: null, dependsOn: null, requiredFlags: null, conditionalFormat: null,
      })
    }
    pushHeader('eligibleForFinalSession', {
      key: 'eligibleForFinalSession', alias: 'eligibleForFinalSession', label: 'Eligible for Final Session',
      group: 'Attendance', order: 0, format: null, dependsOn: null, requiredFlags: null, conditionalFormat: null,
    })

    return { data, tableHeaders, total: data.length, fieldDefs }
  }

  async getAvailableFields(programId: number): Promise<{ key: string; label: string; group: string; order: number }[]> {
    try {
      const program = await this.programRepository.findOneById(programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${programId} not found`))
      }
      this.logger.debug(`Building available report fields for program ${programId} (${program.name})`)

      const activeFlags = this.buildActiveFlags(program)
      const allDefs = await this.getFieldDefsMap()

      return Object.values(allDefs)
        .filter(def => {
          if (def.dependsOn) return false
          return !def.requiredFlags || def.requiredFlags.every(f => activeFlags.has(f))
        })
        .map(def => ({ key: def.key, label: def.label, group: def.group, order: def.order }))
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  // RM users may only ever see their own seekers — every report path reading a
  // registration-scoped table must force this, never trust a client-supplied rm filter.
  private resolveRmContactId(userContext?: ReportUserContext): number | undefined {
    const isRMUser = (userContext?.userRoles ?? []).includes(ROLE_VALUES.RELATIONAL_MANAGER)
    return isRMUser && userContext?.userId ? userContext.userId : undefined
  }

  async generateReport(dto: GenerateReportDto, userContext?: ReportUserContext): Promise<TransformedReport> {
    try {
      const allDefs = await this.getFieldDefsMap()

      const invalidFields = dto.fields.filter(f => !allDefs[f])
      if (invalidFields.length) {
        throw new InifniBadRequestException(ERROR_CODES.INVALID_REPORT_FIELDS, null, { invalidFields })
      }

      const rawFilters = (dto.filters ?? {}) as ReportFilterDto
      const normalized = normalizeKpiPatterns(rawFilters.kpiCategory, rawFilters.kpiFilter)
      const filters: ReportFilterDto = { ...rawFilters, ...normalized }
      this.logger.log('Generating report with filters', { filters, fields: dto.fields, userContext });

      // RM users may only see their own seekers. Force the rmContact scope to the
      // logged-in RM's id, overriding any client-supplied rmContact filter.
      const rmContactId = this.resolveRmContactId(userContext)
      if (rmContactId != null) {
        filters.rmContact = [rmContactId]
      }

      let activeFlags = new Set<string>()
      if (filters.programId) {
        const program = await this.programRepository.findOneById(filters.programId)
        if (!program) {
          handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${filters.programId} not found`))
        }
        activeFlags = this.buildActiveFlags(program)
        const unavailableFields = dto.fields.filter(f => {
          const flags = allDefs[f].requiredFlags
          return flags && !flags.every(flag => activeFlags.has(flag))
        })
        if (unavailableFields.length) {
          throw new InifniBadRequestException(ERROR_CODES.INVALID_REPORT_FIELDS, null, { invalidFields: unavailableFields })
        }
      }

      const { hdbMin, hdbMax } = parseHdbRanges(filters.numberOfHdbs)
      const expandedFields = expandWithDependentFields(dto.fields, allDefs)
      const userAliases = new Set(expandedFields.map(f => allDefs[f].alias))
      const CONTEXT_ALIASES = ['allocated_program_id', 'isFreeSeat', 'paymentStatus', 'subStatus', 'approvalStatus', 'reservedLink']
      const extraContextCols = CONTEXT_ALIASES.filter(a => !userAliases.has(a)).map(a => `"${a}"`)
      const cols = [...expandedFields.map(f => `"${allDefs[f].alias}"`), ...extraContextCols].join(', ')

      const filtersJson = buildFiltersJson({
        programId:                   filters.programId,
        programSessionId:            filters.programSessionId,
        registrationStatus:          filters.registrationStatus,
        approvalStatus:              filters.approvalStatus,
        gender:                      filters.gender,
        registrationMode:            filters.registrationMode,
        organisation:                filters.organisation,
        defaulterStatus:             filters.defaulterStatus,
        paymentStatus:               filters.paymentStatus,
        paymentMode:                 filters.paymentMode,
        invoiceStatus:               filters.invoiceStatus,
        travelStatus:                filters.travelStatus,
        travelPlan:                  filters.travelPlan,
        age:                         filters.age,
        hdbMin,
        hdbMax,
        experienceTags:              filters.experienceTags,
        location:                    filters.location,
        freeSeat:                    normalizeFreeSeat(filters.freeSeat),
        waitlistStatus:              filters.waitlistStatus,
        roomAllocationStatus:        filters.roomAllocationStatus,
        waitlistCategory:            filters.waitlistCategory,
        seatReleased:                filters.seatReleased,
        allocatedProgramId:          filters.allocatedProgramId,
        allocatedSessionId:          filters.allocatedSessionId,
        preferredProgramId:          filters.preferredProgramId,
        preferredSessionId:          filters.preferredSessionId,
        swapRequests:                filters.swapRequests,
        kpiCategory:                 filters.kpiCategory,
        kpiFilter:                   filters.kpiFilter,
        createdFrom:                 filters.createdFrom,
        createdTo:                   filters.createdTo,
        goodiesStatus:               filters.goodiesStatus,
        recommendation:              filters.recommendation,
        rmContact:                   filters.rmContact,
        rmRating:                    filters.rmRating,
        preferredPrograms:           filters.preferredPrograms,
        swapPreferredProgramId:      filters.swapPreferredProgramId,
        swapDemandPreferredProgramId: filters.swapDemandPreferredProgramId,
        pendingProgramId:            filters.pendingProgramId,
      })

      const rows = await this.reportsRepository.fetchReportRows(cols, filtersJson)
      const result = this.transformCustomReportData(rows, expandedFields, activeFlags, allDefs)

      this.logger.log('Report generated', { fields: dto.fields, total: result.total })
      return result
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }

  transformCustomReportData(
    rawRows: Record<string, unknown>[],
    orderedFields: string[],
    activeFlags: Set<string> = new Set(),
    defs: Record<string, ReportFieldDef>,
  ): TransformedReport {
    this.logger.debug(`Transforming custom report data with ${rawRows?.length ?? 0} records`)

    if (!rawRows?.length) {
      return { data: [], tableHeaders: this.buildReportTableHeaders(orderedFields, activeFlags, defs), total: 0 }
    }

    const tableHeaders = this.buildReportTableHeaders(orderedFields, activeFlags, defs)

    const data: ReportRow[] = rawRows.map((raw, index) => {
      try {
        const row: ReportRow = {}
        const context: FormatContext = {
          allocatedProgramId: raw['allocated_program_id'] as number | null,
          isFreeSeat: raw['isFreeSeat'] as boolean,
          paymentStatus: raw['paymentStatus'] as string | null,
          subStatus: raw['subStatus'] as string | null,
          approvalStatus: raw['approvalStatus'] as string | null,
          reservedLink: raw['reservedLink'] as boolean | null,
          requiresApproval: activeFlags.has('requiresApproval'),
          waitlistApplicable: activeFlags.has('waitlistApplicable'),
        }
        for (const fieldKey of orderedFields) {
          const def = defs[fieldKey]
          row[def.alias] = formatValue(raw[def.alias], this.resolveFormat(def, activeFlags), context)
        }
        return row
      } catch (itemError) {
        this.logger.error(`Error transforming custom report row at index ${index}`, itemError instanceof Error ? itemError.stack : String(itemError))
        return {}
      }
    })

    return { data, tableHeaders, total: data.length }
  }

  private buildReportTableHeaders(
    orderedFields: string[],
    activeFlags: Set<string>,
    defs: Record<string, ReportFieldDef>,
  ): ReportTableHeader[] {
    return orderedFields.map((fieldKey, index) => {
      const def = defs[fieldKey]
      const fmt = this.resolveFormat(def, activeFlags)
      const type = (fmt === 'date' || fmt === 'datetime') ? 'date'
        : (fmt === 'number' || fmt === 'currency') ? 'number'
        : fmt === 'boolean' ? 'boolean'
        : 'string'
      return {
        key:        fieldKey,
        alias:      def.alias,
        label:      def.label,
        order:      index + 1,
        type,
        sortable:   false,
        filterable: false,
      }
    })
  }

  private resolveFormat(def: ReportFieldDef, activeFlags: Set<string>): string | null {
    if (def.conditionalFormat) {
      for (const [flagKey, fmt] of Object.entries(def.conditionalFormat)) {
        const flags = flagKey.split('+')
        if (flags.every(f => activeFlags.has(f))) return fmt
      }
    }
    return def.format
  }

  private buildActiveFlags(program: {
    requiresApproval: boolean; requiresPayment: boolean; isProformaAllowed: boolean;
    involvesTravel: boolean; hasGoodies: boolean; isGroupedProgram: boolean;
    parentalConsentEnabled: boolean; waitlistApplicable: boolean;
    requiresResidence: boolean; type?: { key: string };
  }): Set<string> {
    return new Set<string>([
      ...(program.requiresApproval       ? ['requiresApproval']       : []),
      ...(program.requiresPayment        ? ['requiresPayment']        : []),
      ...(program.isProformaAllowed      ? ['isProformaAllowed']      : []),
      ...(program.involvesTravel         ? ['involvesTravel']         : []),
      ...(program.hasGoodies             ? ['hasGoodies']             : []),
      ...(program.isGroupedProgram       ? ['isGroupedProgram']       : []),
      ...(program.parentalConsentEnabled ? ['parentalConsentEnabled'] : []),
      ...(program.waitlistApplicable     ? ['waitlistApplicable']     : []),
      ...(program.requiresResidence      ? ['requiresResidence']      : []),
      ...(program.type?.key === 'PT_HDBMSD' ? ['isHdbMsd']           : []),
    ])
  }

  async buildExcel(
    rows: Record<string, unknown>[],
    fields: string[],
    fileName = 'report',
    defsOverride?: Record<string, ReportFieldDef>,
  ): Promise<string> {
    // No rows means there is nothing to export — surface a handled 400 instead of letting the
    // empty Excel build / S3 upload fail as an unmapped 500.
    if (!rows?.length) {
      throw new InifniBadRequestException(
        ERROR_CODES.NO_DATA_FOUND_FOR_REPORT,
        null,
        null,
        'No data available to download for the current filters.',
      )
    }

    const allDefs = defsOverride ?? await this.getFieldDefsMap()
    const columns = fields.map(f => ({
      key:    allDefs[f].alias,
      header: allDefs[f].label,
    }))
    const buffer = await this.excelService.jsonToExcelBuffer(
      rows as any[],
      { sheetName: 'Report', columns, makeHeaderBold: true, autoWidth: true },
    )
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-').slice(0, -5)
    const key = `exports/excel/${timestamp}/${fileName}.xlsx`
    await this.awsS3Service.uploadToS3(key, buffer, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    return this.awsS3Service.getSignedUrl(key)
  }

  // Downloads generated Zoom join links for a program — across all of its sessions, or
  // scoped to a single session when sessionId is given — with registrant basic details,
  // RM and session context. Always all fields, no field picking.
  async downloadZoomLinks(dto: DownloadZoomLinksDto, userContext?: ReportUserContext): Promise<string> {
    try {
      const program = await this.programRepository.findOneById(dto.programId)
      if (!program) {
        handleKnownErrors(ERROR_CODES.PROGRAM_NOT_FOUND, new Error(`Program ${dto.programId} not found`))
      }
      await this.assertReportFeatureEnabled([FEATURE_FLAG_KEYS.ENABLE_ZOOM_LINKS_REPORT], dto.programId)

      const rows = await this.reportsRepository.fetchProgramZoomLinks(dto.programId, dto.sessionId)

      // RM users may only see their own seekers' Zoom links.
      const rmContactId = this.resolveRmContactId(userContext)
      const filteredRows = rmContactId != null
        ? rows.filter(row => row.rmUserId != null && Number(row.rmUserId) === rmContactId)
        : rows

      if (!filteredRows.length) {
        throw new InifniBadRequestException(
          ERROR_CODES.NO_DATA_FOUND_FOR_REPORT,
          null,
          null,
          'No Zoom links found to download for this program.',
        )
      }

      const data = filteredRows.map(row => ({
        'Session Name':      row.sessionName,
        'Session Date':      row.sessionStartsAt ? formatDateTimeIST(row.sessionStartsAt) : '',
        'Registration ID':   row.regId ?? '',
        'Full Name':         row.fullName,
        'Email':             row.email,
        'Phone Number':      row.mobileNumber,
        'RM':                row.rmName ?? '',
        'Join Link':         row.joinUrl ?? '',
        'Link Status':       row.linkStatus === OnlineSessionRegistrationStatus.REGISTERED ? 'Generated' : 'Failed',
        'Activation Status': row.activationStatus === RegistrationOnlineSessionActivationStatus.ACTIVE ? 'Active' : 'Inactive',
      }))

      const fileName = dto.fileName ?? `zoom-links-program-${dto.programId}`
      const buffer = await this.excelService.jsonToExcelBuffer(
        data,
        { sheetName: 'Zoom Links', makeHeaderBold: true, autoWidth: true },
      )
      const timestamp = new Date().toISOString().replace(/[:.]/g, '-').slice(0, -5)
      const key = `exports/excel/${timestamp}/${fileName}.xlsx`
      await this.awsS3Service.uploadToS3(key, buffer, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
      return this.awsS3Service.getSignedUrl(key)
    } catch (err) {
      handleKnownErrors(ERROR_CODES.INTERNAL_SERVER_ERROR, err)
    }
  }
}

function expandWithDependentFields(fields: string[], defs: Record<string, ReportFieldDef>): string[] {
  const selectedSet = new Set(fields)
  const result: string[] = []
  for (const key of fields) {
    result.push(key)
    for (const [depKey, def] of Object.entries(defs)) {
      if (def.dependsOn === key && !selectedSet.has(depKey)) {
        result.push(depKey)
        selectedSet.add(depKey)
      }
    }
  }
  return result
}

// The client sends freeSeat as a multi-select array (e.g. ["no"]) but the SQL
// function reads it as scalar text via ->>'freeSeat' and compares against 'yes'/'no'.
// An array reaches SQL as '["no"]', never matches, and the filter is silently ignored.
// Collapse it to the scalar SQL expects; selecting both yes+no means "all seats" (no filter).
function normalizeFreeSeat(value: unknown): string | undefined {
  const values = (Array.isArray(value) ? value : [value])
    .filter((v): v is string => v === 'yes' || v === 'no')
  const unique = Array.from(new Set(values))
  return unique.length === 1 ? unique[0] : undefined
}

function buildFiltersJson(obj: Record<string, unknown>): string {
  const clean: Record<string, unknown> = {}
  for (const [k, v] of Object.entries(obj)) {
    if (v === null || v === undefined) continue
    if (Array.isArray(v) && v.length === 0) continue
    clean[k] = v
  }
  return JSON.stringify(clean)
}

function parseHdbRanges(ranges?: string[]): { hdbMin: number | null; hdbMax: number | null } {
  if (!ranges?.length) return { hdbMin: null, hdbMax: null }
  let min = Infinity
  let max = -Infinity
  for (const r of ranges) {
    if (r.startsWith('>')) {
      min = Math.min(min, parseInt(r.slice(1)) + 1)
      max = 1000
    } else if (r.startsWith('=')) {
      const v = parseInt(r.slice(1))
      min = Math.min(min, v)
      max = Math.max(max, v)
    } else if (r.includes('-')) {
      const [a, b] = r.split('-').map(Number)
      min = Math.min(min, a)
      max = Math.max(max, b)
    }
  }
  return {
    hdbMin: min === Infinity  ? null : min,
    hdbMax: max === -Infinity ? null : max,
  }
}