import { Injectable } from '@nestjs/common'
import { DataSource, Repository } from 'typeorm'
import { InjectRepository } from '@nestjs/typeorm'
import { AppLoggerService } from 'src/common/services/logger.service'
import { ReportFieldDef, ProgramZoomLinkRow } from './types/report-field.types'
import { ProgramRegistrationOnlineSession } from 'src/common/entities/program-registration-online-session.entity'
import { SessionProviderType } from 'src/common/enum/session-provider.enum'

@Injectable()
export class ReportsRepository {
  constructor(
    private readonly dataSource: DataSource,
    private readonly logger: AppLoggerService,
    @InjectRepository(ProgramRegistrationOnlineSession)
    private readonly onlineSessionRepository: Repository<ProgramRegistrationOnlineSession>,
  ) {}

  async getAllFieldDefinitions(): Promise<ReportFieldDef[]> {
    return this.dataSource.query<ReportFieldDef[]>('SELECT * FROM fn_get_report_field_definitions()')
  }

  async fetchReportRows(cols: string, filtersJson: string): Promise<Record<string, unknown>[]> {
    const sql = `SELECT ${cols} FROM fn_generate_registration_report($1) ORDER BY "registrationDate" DESC NULLS LAST`
    this.logger.debug('Report SQL', { sql, filters: filtersJson })
    return this.dataSource.query<Record<string, unknown>[]>(sql, [filtersJson])
  }

  async getAllSessionAttendanceFieldDefinitions(): Promise<ReportFieldDef[]> {
    return this.dataSource.query<ReportFieldDef[]>('SELECT * FROM fn_get_session_attendance_report_field_definitions()')
  }

  async fetchSessionAttendanceReportRows(cols: string, filtersJson: string): Promise<Record<string, unknown>[]> {
    const sql = `SELECT ${cols} FROM fn_generate_session_attendance_report($1)`
    this.logger.debug('Session attendance report SQL', { sql, filters: filtersJson })
    return this.dataSource.query<Record<string, unknown>[]>(sql, [filtersJson])
  }

  async fetchSessionDropoffReportRows(filtersJson: string): Promise<Record<string, unknown>[]> {
    const sql = 'SELECT * FROM fn_generate_session_dropoff_report($1)'
    this.logger.debug('Session drop-off report SQL', { sql, filters: filtersJson })
    return this.dataSource.query<Record<string, unknown>[]>(sql, [filtersJson])
  }

  async fetchGeneralAttendeesReportRows(filtersJson: string): Promise<Record<string, unknown>[]> {
    const sql = 'SELECT * FROM fn_generate_general_attendees_report($1)'
    this.logger.debug('General attendees report SQL', { sql, filters: filtersJson })
    return this.dataSource.query<Record<string, unknown>[]>(sql, [filtersJson])
  }

  async fetchGeneralSessionDropoffReportRows(filtersJson: string): Promise<Record<string, unknown>[]> {
    const sql = 'SELECT * FROM fn_generate_general_session_dropoff_report($1)'
    this.logger.debug('General session drop-off report SQL', { sql, filters: filtersJson })
    return this.dataSource.query<Record<string, unknown>[]>(sql, [filtersJson])
  }

  // All Zoom join links generated for a program's registrations, across every session —
  // one row per (registrant, session), with registrant, RM and session context joined in.
  async fetchProgramZoomLinks(programId: number, sessionId?: number): Promise<ProgramZoomLinkRow[]> {
    // Join session context via rosn.onlineSession, not registration.programSession — a
    // registration can be pushed to several online sessions, so the session that owns
    // THIS join link is the one on the link row, not the registration's own pointer.
    const query = this.onlineSessionRepository
      .createQueryBuilder('rosn')
      .innerJoin('rosn.registration', 'registration')
      .innerJoin('rosn.onlineSession', 'onlineSession')
      .innerJoin('onlineSession.programSession', 'session')
      .leftJoin('registration.rmContactUser', 'rm')
      .where('registration.programId = :programId', { programId })
      .andWhere('rosn.provider = :provider', { provider: SessionProviderType.ZOOM })
      .andWhere('registration.deletedAt IS NULL')
      .andWhere('session.deletedAt IS NULL')

    if (sessionId != null) {
      query.andWhere('session.id = :sessionId', { sessionId })
    }

    return query
      .select('session.name', 'sessionName')
      .addSelect('session.startsAt', 'sessionStartsAt')
      .addSelect('session.endsAt', 'sessionEndsAt')
      .addSelect('registration.registrationSeqNumber', 'regId')
      .addSelect('registration.fullName', 'fullName')
      .addSelect('registration.emailAddress', 'email')
      .addSelect('registration.mobileNumber', 'mobileNumber')
      .addSelect('registration.rmContact', 'rmUserId')
      .addSelect('rm.fullName', 'rmName')
      .addSelect('rosn.joinUrl', 'joinUrl')
      .addSelect('rosn.status', 'linkStatus')
      .addSelect('rosn.activationStatus', 'activationStatus')
      .orderBy('session.displayOrder', 'ASC')
      .addOrderBy('registration.fullName', 'ASC')
      .getRawMany<ProgramZoomLinkRow>()
  }
}