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 { normalizeKpiPatterns } from 'src/common/utils/kpi-pattern.util'

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 }

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

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

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

  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
  }

  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)
    }
  }

  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 isRMUser = (userContext?.userRoles ?? []).includes('relational_manager')
      if (isRMUser && userContext?.userId) {
        filters.rmContact = [userContext.userId]
      }

      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'): 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 = 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)
  }
}

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,
  }
}