import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, SelectQueryBuilder, In, IsNull } from 'typeorm';
import {
  CommunicationTrack,
  ProgramRegistration,
  SessionCommunicationStatus,
  ZoomGeneratedRegistrantLink,
} from 'src/common/entities';
import { handleKnownErrors } from 'src/common/utils/handle-error.util';
import { ERROR_CODES } from 'src/common/constants/error-string-constants';
import { RegistrationStatusEnum } from 'src/common/enum/registration-status.enum';
import { RegistrationOnlineSessionActivationStatus } from 'src/common/enum/registration-online-session-activation-status.enum';
import { GeneratedLinkSourceType } from 'src/common/enum/generated-link-source-type.enum';
import { OnlineSessionRegistrationStatus } from 'src/common/enum/online-session-registration-status.enum';
import { SessionCommunicationStatusEnum } from 'src/common/enum/session-communication-status.enum';
import { SessionCommunicationPurposeEnum } from 'src/common/enum/session-communication-purpose.enum';
import { BulkCommunicationSelectionModeEnum } from 'src/common/enum/bulk-communication-selection-mode.enum';
import { SessionCommunicationSummaryRow } from './session-communication.types';
import {
  ACCESS_KEY_TO_DESCRIPTOR,
  SESSION_COMMUNICATION_ACCESS_KEYS,
} from './session-communication.constants';

/**
 * Registration statuses eligible to receive a session communication. Combined with the
 * seat-allocated guard below: a seeker is eligible when they have been allotted a seat and
 * their registration is still pending or already completed.
 */
export const SESSION_COMMUNICATION_ELIGIBLE_STATUSES: RegistrationStatusEnum[] = [
  RegistrationStatusEnum.PENDING,
  RegistrationStatusEnum.COMPLETED,
];

/**
 * A resolved recipient — only the fields needed to send + track.
 */
export interface SessionCommunicationRecipient {
  id: number;
  emailAddress: string | null;
  mobileNumber: string | null;
  fullName: string | null;
  programId: number | null;
}

/**
 * A common-invite recipient: a staff member who was issued a generated common Zoom link.
 * These have no program registration of their own — contact + the shared link details come
 * from their zoom_generated_registrant_link row (joined to the session's online-session row
 * for the meeting id + passcode).
 */
export interface CommonInviteRecipient {
  userId: number | null;
  displayName: string | null;
  emailAddress: string | null;
  mobileNumber: string | null;
  joinUrl: string | null;
  meetingId: string | null;
  meetingPasscode: string | null;
  /**
   * The recipient's own program_session_id, when the caller needs to thread it into the merge
   * context itself (see getGeneralLinkRecipientById — a single send has no explicit sessionId of
   * its own, so the session-level merge fields must be resolved against THIS row's session, not
   * left to fall back to the program's first session). Not populated by callers that already know
   * the target session from elsewhere (e.g. Common Invite bulk, which gets it from the request).
   */
  programSessionId?: number | null;
}

/** One generated system/placeholder link — the name + join URL listed in the System Links email. */
export interface SystemGeneratedLink {
  displayName: string | null;
  joinUrl: string | null;
}

@Injectable()
export class SessionCommunicationRepository {
  constructor(
    @InjectRepository(CommunicationTrack)
    private readonly trackRepo: Repository<CommunicationTrack>,
    @InjectRepository(ProgramRegistration)
    private readonly registrationRepo: Repository<ProgramRegistration>,
    @InjectRepository(SessionCommunicationStatus)
    private readonly statusRepo: Repository<SessionCommunicationStatus>,
    @InjectRepository(ZoomGeneratedRegistrantLink)
    private readonly generatedLinkRepo: Repository<ZoomGeneratedRegistrantLink>,
  ) {}

  // ==================================================================================
  // Recipient filters — one per communication purpose.
  //
  // Each purpose gets its own function so its audience rule is isolated and can evolve
  // independently. Absent narrows the eligible base set to non-attendees of the target
  // session (via program_user_attendance); the other purposes use the eligible base set
  // (see queryEligibleRecipients). Final-session eligibility (attended all prior sessions)
  // is still TODO — see getInviteRecipients / getAbsentRecipients.
  // ==================================================================================

  /**
   * Base audience: COMPLETED registrations of the program, honoring the bulk selection
   * mode (ALL / SELECTED / EXCLUDED). Soft-deleted rows are auto-excluded by TypeORM.
   * Shared foundation for every purpose's recipient list.
   */
  private async queryEligibleRecipients(
    programId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    try {
      const rows = await this.buildEligibleRecipientsQuery(
        programId,
        selectionMode,
        registrationIds,
      ).getMany();
      return rows.map((r) => this.toRecipient(r));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * Absentees of a session: the eligible base set minus anyone with an attended row for the
   * target session (from program_user_attendance). A registration counts as absent when it
   * has no attendance row for the session, or one explicitly marked is_attended = false.
   */
  private async queryAbsenteeRecipients(
    programId: number,
    sessionId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    try {
      // Source of truth is the link (extension) set for the session; then drop anyone who
      // attended it. No registration-eligibility gate — see buildLinkedRecipientsQuery.
      const qb = this.buildLinkedRecipientsQuery(
        programId,
        sessionId,
        selectionMode,
        registrationIds,
      ).andWhere(
        `NOT EXISTS (
          SELECT 1 FROM program_user_attendance att
          WHERE att.registration_id = registration.id
            AND att.session_id = :sessionId
            AND att.is_attended = true
        )`,
        { sessionId },
      );
      const rows = await qb.getMany();
      return rows.map((r) => this.toRecipient(r));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * Attendees of a session: the eligible base set restricted to registrations with an
   * attended row for the target session (program_user_attendance.is_attended = true).
   * Inverse of queryAbsenteeRecipients — used for the TAT program-completion audience,
   * which only reaches registrants who attended the final session.
   */
  private async queryAttendeeRecipients(
    programId: number,
    sessionId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    try {
      const qb = this.buildEligibleRecipientsQuery(
        programId,
        selectionMode,
        registrationIds,
      ).andWhere(
        `EXISTS (
          SELECT 1 FROM program_user_attendance att
          WHERE att.registration_id = registration.id
            AND att.session_id = :sessionId
            AND att.is_attended = true
        )`,
        { sessionId },
      );
      const rows = await qb.getMany();
      return rows.map((r) => this.toRecipient(r));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * Shared base query for recipient selection: seat-allocated, eligible-status registrations
   * of the program, honoring the bulk selection mode (ALL / SELECTED / EXCLUDED). Also requires
   * the registrant to have an ACTIVE online-session provisioning row — a deactivated registrant
   * (admin/RM/Shoba toggle) is excluded from every bulk communication.
   */
  private buildEligibleRecipientsQuery(
    programId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): SelectQueryBuilder<ProgramRegistration> {
    const qb = this.registrationRepo
      .createQueryBuilder('registration')
      .select([
        'registration.id',
        'registration.emailAddress',
        'registration.mobileNumber',
        'registration.fullName',
        'registration.programId',
      ])
      .where('registration.program_id = :programId', { programId })
      .andWhere('registration.seatAllocated = :seatAllocated', { seatAllocated: true })
      .andWhere('registration.activationStatus = :activationStatus', {
        activationStatus: RegistrationOnlineSessionActivationStatus.ACTIVE,
      })
      .andWhere('registration.registrationStatus IN (:...statuses)', {
        statuses: SESSION_COMMUNICATION_ELIGIBLE_STATUSES,
      });

    if (selectionMode === BulkCommunicationSelectionModeEnum.SELECTED) {
      qb.andWhere('registration.id IN (:...selectedIds)', { selectedIds: registrationIds });
    } else if (selectionMode === BulkCommunicationSelectionModeEnum.EXCLUDED) {
      qb.andWhere('registration.id NOT IN (:...excludedIds)', { excludedIds: registrationIds });
    }
    return qb;
  }

  /**
   * Narrows the query to registrants who have a usable per-registrant join link for the target
   * session — a REGISTERED, non-deleted hdb_program_registration_online_session row with a
   * non-null join_url on that session's online-session. Used by Invite/Absent, whose messages
   * carry the seeker's personal join link (the same row the zoom_join_link merge field reads),
   * so a registrant without a provisioned link is not worth contacting.
   */
  private requireProvisionedLink(
    qb: SelectQueryBuilder<ProgramRegistration>,
    sessionId: number,
  ): SelectQueryBuilder<ProgramRegistration> {
    return qb.andWhere(
      `EXISTS (
        SELECT 1 FROM hdb_program_registration_online_session pros
        INNER JOIN hdb_online_session os ON os.id = pros.online_session_id
        WHERE pros.registration_id = registration.id
          AND os.program_session_id = :linkSessionId
          AND os.deleted_at IS NULL
          AND pros.deleted_at IS NULL
          AND pros.status = :linkStatus
          AND pros.activation_status = :linkActivationStatus
          AND pros.join_url IS NOT NULL
      )`,
      {
        linkSessionId: sessionId,
        linkStatus: OnlineSessionRegistrationStatus.REGISTERED,
        linkActivationStatus: RegistrationOnlineSessionActivationStatus.ACTIVE,
      },
    );
  }

  /**
   * Link-driven base query for Invite/Absent: the source of truth is the online-session extension
   * (hdb_program_registration_online_session), NOT the registration's own eligibility. A recipient
   * qualifies purely by holding a provisioned link for the target session (see
   * requireProvisionedLink); hdb_program_registration is joined only to carry the contact fields
   * (name/email/mobile). The seat/status/ACTIVE gate is intentionally NOT applied here — a
   * deactivated registrant's link is cleared on deactivation, so the link check already excludes
   * them. The bulk selection mode (ALL / SELECTED / EXCLUDED) still applies on registration.id.
   */
  private buildLinkedRecipientsQuery(
    programId: number,
    sessionId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): SelectQueryBuilder<ProgramRegistration> {
    const qb = this.registrationRepo
      .createQueryBuilder('registration')
      .select([
        'registration.id',
        'registration.emailAddress',
        'registration.mobileNumber',
        'registration.fullName',
        'registration.programId',
      ])
      .where('registration.program_id = :programId', { programId });

    if (selectionMode === BulkCommunicationSelectionModeEnum.SELECTED) {
      qb.andWhere('registration.id IN (:...selectedIds)', { selectedIds: registrationIds });
    } else if (selectionMode === BulkCommunicationSelectionModeEnum.EXCLUDED) {
      qb.andWhere('registration.id NOT IN (:...excludedIds)', { excludedIds: registrationIds });
    }

    this.requireProvisionedLink(qb, sessionId);
    return qb;
  }

  /** Map a registration row to the minimal recipient shape used for sending + tracking. */
  private toRecipient(r: ProgramRegistration): SessionCommunicationRecipient {
    return {
      id: Number(r.id),
      emailAddress: r.emailAddress ?? null,
      mobileNumber: r.mobileNumber ?? null,
      fullName: r.fullName ?? null,
      programId: r.programId != null ? Number(r.programId) : null,
    };
  }

  /**
   * Welcome recipients — every eligible registration of the program.
   */
  async getWelcomeRecipients(
    programId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    return this.queryEligibleRecipients(programId, selectionMode, registrationIds);
  }

  /**
   * Common-invite recipients — the staff (ROLE) rows in zoom_generated_registrant_link that
   * successfully got a common Zoom link for the program (or, when `sessionId` is given, for
   * just that one session — the PER_SESSION case, where each session holds its own distinct
   * link). Deduplicated to one row per user (a shared program stores one representative row per
   * recipient), joined to the session's online-session row for the meeting id + passcode. Only
   * rows with a real join_url and a REGISTERED status are returned; placeholders are excluded
   * (source_type = ROLE only).
   */
  async getCommonInviteRecipients(
    programId: number,
    sessionId?: number,
  ): Promise<CommonInviteRecipient[]> {
    try {
      const qb = this.generatedLinkRepo
        .createQueryBuilder('generatedLink')
        .distinctOn(['generatedLink.user_id'])
        .leftJoin(
          'hdb_online_session',
          'os',
          'os.program_session_id = generatedLink.program_session_id AND os.deleted_at IS NULL',
        )
        .select('generatedLink.user_id', 'userId')
        .addSelect('generatedLink.display_name', 'displayName')
        .addSelect('generatedLink.source_email', 'sourceEmail')
        .addSelect('generatedLink.source_mobile', 'sourceMobile')
        .addSelect('generatedLink.join_url', 'joinUrl')
        .addSelect('os.external_id', 'meetingId')
        .addSelect('os.password', 'meetingPasscode')
        .where('generatedLink.program_id = :programId', { programId })
        .andWhere('generatedLink.source_type = :sourceType', { sourceType: GeneratedLinkSourceType.ROLE })
        .andWhere('generatedLink.status = :status', { status: OnlineSessionRegistrationStatus.REGISTERED })
        .andWhere('generatedLink.deleted_at IS NULL')
        .andWhere('generatedLink.join_url IS NOT NULL');
      if (sessionId != null) {
        qb.andWhere('generatedLink.program_session_id = :sessionId', { sessionId });
      }
      const rows = await qb
        .orderBy('generatedLink.user_id', 'ASC')
        .addOrderBy('generatedLink.id', 'ASC')
        .getRawMany();

      return rows.map((row) => ({
        userId: row.userId != null ? Number(row.userId) : null,
        displayName: row.displayName ?? null,
        emailAddress: row.sourceEmail ?? null,
        mobileNumber: row.sourceMobile ?? null,
        joinUrl: row.joinUrl ?? null,
        meetingId: row.meetingId ?? null,
        meetingPasscode: row.meetingPasscode ?? null,
      }));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * The program's generated system/placeholder links (source_type = PLACEHOLDER) — name +
   * join URL — for the System Links email's cumulative table. When `sessionId` is given
   * (the PER_SESSION case), narrowed to just that session's placeholder links instead of the
   * whole program. Deduplicated to one row per (batch, sequence) — a shared program stores one
   * representative row per placeholder — and ordered by batch then sequence so the list reads
   * "system 1, system 2, …".
   */
  async getSystemPlaceholderLinks(
    programId: number,
    sessionId?: number,
  ): Promise<SystemGeneratedLink[]> {
    try {
      const qb = this.generatedLinkRepo
        .createQueryBuilder('generatedLink')
        .distinctOn(['generatedLink.batch_name', 'generatedLink.sequence_number'])
        .select('generatedLink.display_name', 'displayName')
        .addSelect('generatedLink.join_url', 'joinUrl')
        .where('generatedLink.program_id = :programId', { programId })
        .andWhere('generatedLink.source_type = :sourceType', {
          sourceType: GeneratedLinkSourceType.PLACEHOLDER,
        })
        .andWhere('generatedLink.status = :status', { status: OnlineSessionRegistrationStatus.REGISTERED })
        .andWhere('generatedLink.deleted_at IS NULL')
        .andWhere('generatedLink.join_url IS NOT NULL');
      if (sessionId != null) {
        qb.andWhere('generatedLink.program_session_id = :sessionId', { sessionId });
      }
      const rows = await qb
        .orderBy('generatedLink.batch_name', 'ASC')
        .addOrderBy('generatedLink.sequence_number', 'ASC')
        .addOrderBy('generatedLink.id', 'ASC')
        .getRawMany();

      return rows.map((row) => ({
        displayName: row.displayName ?? null,
        joinUrl: row.joinUrl ?? null,
      }));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * EXISTS / NOT EXISTS clause: whether the general-link row's email HAS (`attended: true`) or has
   * no (`attended: false`) attended row for `sessionIdExpr` in zoom_analytics_attendee_summary
   * (registration_id IS NULL — the general-attendee half of that table, matched by session +
   * normalized email, since general-link rows have no registrationId to join on). Gates
   * GENERAL_LINK_VALUE_CARD to real attendees and GENERAL_LINK_ABSENT to real absentees.
   */
  private generalLinkAttendanceClause(sessionIdExpr: string, attended: boolean): string {
    // A general-link recipient has no registration, so attendance can't be read from
    // program_user_attendance (which is keyed by registration_id) the way the seeker sends do.
    // It comes from the general-attendee rows of zoom_analytics_attendee_summary
    // (registration_id IS NULL), matched on email.
    //
    // The outer query builder aliases the table as "generatedLink" (mixed-case) — TypeORM quotes
    // that alias wherever IT builds a reference, but this raw fragment must quote it identically
    // itself, or Postgres case-folds the unquoted text to `generatedlink` and fails to resolve it
    // against the quoted `"generatedLink"` the FROM clause actually declares.
    //
    // Both directions share one definition on purpose: "attended" for the Value Card and "did not
    // attend" for Absent must stay exact complements, so the email-matching and the
    // is_system_attended condition can never drift between them.
    //
    // Matched on registrant_email FIRST, not source_email: for a general row that table stores the
    // uniquely-tagged address the attendee was registered to Zoom with (e.g.
    // "vinod+gen9562p1474@yopmail.com" — see notJoinedGeneralAttendeePayload's `email:
    // link.registrantEmail`, and the analytics read path keying its generated-link map by
    // registrantEmail for exactly this join), never the person's real address. source_email holds
    // the real one and is set on every ROLE link, so COALESCE-ing it first compared the real
    // address against a tagged one and matched nobody — silently emptying the Value Card audience
    // and, in the NOT EXISTS direction, sending Absent to people who had in fact attended.
    // The source_email arm is kept as a second chance for any row stored under the real address
    // (a NULL source_email simply never matches).
    return `${attended ? 'EXISTS' : 'NOT EXISTS'} (
      SELECT 1 FROM zoom_analytics_attendee_summary zas
      WHERE zas.session_id = ${sessionIdExpr}
        AND zas.registration_id IS NULL
        AND (
          LOWER(TRIM(zas.email)) = LOWER(TRIM("generatedLink".registrant_email))
          OR LOWER(TRIM(zas.email)) = LOWER(TRIM("generatedLink".source_email))
        )
        AND zas.is_system_attended = true
    )`;
  }

  /**
   * General-link recipients — every zoom_generated_registrant_link row that has a real user_id
   * (sourceType: ROLE) for the program, narrowed to one session when `sessionId` is given.
   * PLACEHOLDER rows (sourceType: PLACEHOLDER, e.g. anonymous "Staff" x20 batch slots, no
   * user_id) are excluded — there's no real person behind them to email/WhatsApp; they're only
   * ever surfaced to admins as a link list via System Links. These recipients carry no program
   * registration, so eligibility mirrors the seeker rule as closely as a registration-less row
   * allows: status = REGISTERED is always required, and additionally —
   * - GENERAL_LINK_ABSENT: only real absentees of the session (no attended row in
   *   zoom_analytics_attendee_summary for this session, matched by email) — unconditional, same
   *   as getAbsentRecipients never sends Absent to an attendee.
   * GENERAL_LINK_INVITE at the final session is NOT gated by prior-session attendance, unlike
   * the seeker getFinalInviteRecipients rule — these recipients are staff/admin (ROLE-type
   * generated links), not seekers on a structured multi-session journey, so there's no
   * expectation they attended every earlier session before joining the final one. `occurrence`
   * still selects the regular- vs final-session template content; it just doesn't narrow the
   * audience here.
   * A program-level query (no sessionId) can see the same person's row repeated across every
   * session of the program (one generated-link row per session), so results are deduped by user
   * id (falling back to email) in application code — TypeORM's typed distinctOn() doesn't support
   * the coalesced-email expression this would need.
   */
  async getGeneralLinkRecipients(
    programId: number,
    sessionId: number | null,
    purpose: SessionCommunicationPurposeEnum,
  ): Promise<CommonInviteRecipient[]> {
    try {
      const qb = this.generatedLinkRepo
        .createQueryBuilder('generatedLink')
        .leftJoin(
          'hdb_online_session',
          'os',
          'os.program_session_id = generatedLink.program_session_id AND os.deleted_at IS NULL',
        )
        .select('generatedLink.id', 'id')
        .addSelect('generatedLink.user_id', 'userId')
        .addSelect('generatedLink.display_name', 'displayName')
        .addSelect('COALESCE(generatedLink.source_email, generatedLink.registrant_email)', 'emailAddress')
        .addSelect('generatedLink.source_mobile', 'mobileNumber')
        .addSelect('generatedLink.join_url', 'joinUrl')
        .addSelect('os.external_id', 'meetingId')
        .addSelect('os.password', 'meetingPasscode')
        .where('generatedLink.program_id = :programId', { programId })
        .andWhere('generatedLink.deleted_at IS NULL')
        .andWhere('generatedLink.join_url IS NOT NULL')
        .andWhere('generatedLink.user_id IS NOT NULL')
        .andWhere('generatedLink.status = :status', { status: OnlineSessionRegistrationStatus.REGISTERED });

      if (sessionId != null) {
        qb.andWhere('generatedLink.program_session_id = :sessionId', { sessionId });
      }
      // Absent goes to those who did NOT attend; the Value Card goes only to those who DID —
      // it is post-session material, same rule as the seeker value card (see
      // queryAttendeeRecipients).
      if (sessionId != null && purpose === SessionCommunicationPurposeEnum.GENERAL_LINK_ABSENT) {
        qb.andWhere(this.generalLinkAttendanceClause(':sessionId', false), { sessionId });
      }
      if (sessionId != null && purpose === SessionCommunicationPurposeEnum.GENERAL_LINK_VALUE_CARD) {
        qb.andWhere(this.generalLinkAttendanceClause(':sessionId', true), { sessionId });
      }

      const rows = await qb.orderBy('generatedLink.id', 'ASC').getRawMany();

      const byIdentity = new Map<string, CommonInviteRecipient>();
      for (const row of rows) {
        const identity = row.userId != null ? `u:${row.userId}` : `e:${row.emailAddress ?? row.id}`;
        if (byIdentity.has(identity)) {
          continue;
        }
        byIdentity.set(identity, {
          userId: row.userId != null ? Number(row.userId) : null,
          displayName: row.displayName ?? null,
          emailAddress: row.emailAddress ?? null,
          mobileNumber: row.mobileNumber ?? null,
          joinUrl: row.joinUrl ?? null,
          meetingId: row.meetingId ?? null,
          meetingPasscode: row.meetingPasscode ?? null,
        });
      }
      return Array.from(byIdentity.values());
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * One general-link recipient by its own zoom_generated_registrant_link id — for the single-send
   * endpoint. No sessionId is taken here: the row already carries its own program_session_id, so
   * the target session is implicit in generatedLinkId (unlike the bulk endpoint, which has no
   * single row to key off and so needs an explicit sessionId to scope its query). `programId`
   * IS still required and checked against the row (generatedLink.program_id) — otherwise a caller could pass
   * a generatedLinkId from a different program and the send would go out using this program's
   * templates/sender identity for that other program's recipient. Same shape/join as
   * getGeneralLinkRecipients; returns null when the row doesn't exist, belongs to a different
   * program, is soft-deleted, has no join_url (nothing to send), has no user_id (a PLACEHOLDER
   * row — no real person to send to), or fails eligibility. Status = REGISTERED is always
   * required, and additionally —
   * - purpose = GENERAL_LINK_ABSENT: only when the row's email did NOT attend its own session
   *   (zoom_analytics_attendee_summary, matched by email) — unconditional.
   * GENERAL_LINK_INVITE is NOT gated by prior-session attendance at any occurrence (see
   * getGeneralLinkRecipients) — the caller's REGULAR/FINAL occurrence only picks which template
   * to send, resolved separately by the service, so it isn't a parameter here.
   */
  async getGeneralLinkRecipientById(
    programId: number,
    generatedLinkId: number,
    purpose: SessionCommunicationPurposeEnum,
  ): Promise<CommonInviteRecipient | null> {
    try {
      const qb = this.generatedLinkRepo
        .createQueryBuilder('generatedLink')
        .leftJoin(
          'hdb_online_session',
          'os',
          'os.program_session_id = generatedLink.program_session_id AND os.deleted_at IS NULL',
        )
        .select('generatedLink.user_id', 'userId')
        .addSelect('generatedLink.display_name', 'displayName')
        .addSelect('COALESCE(generatedLink.source_email, generatedLink.registrant_email)', 'emailAddress')
        .addSelect('generatedLink.source_mobile', 'mobileNumber')
        .addSelect('generatedLink.join_url', 'joinUrl')
        .addSelect('os.external_id', 'meetingId')
        .addSelect('os.password', 'meetingPasscode')
        .addSelect('generatedLink.program_session_id', 'programSessionId')
        .where('generatedLink.id = :generatedLinkId', { generatedLinkId })
        // Cross-program guard: the row must actually belong to the requested program — without
        // this, a caller could pass a generatedLinkId from a different program and the send
        // would go out using this program's templates/sender identity for that other program's
        // recipient.
        .andWhere('generatedLink.program_id = :programId', { programId })
        .andWhere('generatedLink.deleted_at IS NULL')
        .andWhere('generatedLink.join_url IS NOT NULL')
        .andWhere('generatedLink.user_id IS NOT NULL')
        .andWhere('generatedLink.status = :status', { status: OnlineSessionRegistrationStatus.REGISTERED });

      // Same audience rules as the bulk query, so a single send can never reach someone the bulk
      // run would have excluded: Absent requires non-attendance, the Value Card requires attendance.
      if (purpose === SessionCommunicationPurposeEnum.GENERAL_LINK_ABSENT) {
        qb.andWhere(this.generalLinkAttendanceClause('"generatedLink".program_session_id', false));
      }
      if (purpose === SessionCommunicationPurposeEnum.GENERAL_LINK_VALUE_CARD) {
        qb.andWhere(this.generalLinkAttendanceClause('"generatedLink".program_session_id', true));
      }

      const row = await qb.getRawOne();
      if (!row) {
        return null;
      }
      return {
        userId: row.userId != null ? Number(row.userId) : null,
        displayName: row.displayName ?? null,
        emailAddress: row.emailAddress ?? null,
        mobileNumber: row.mobileNumber ?? null,
        joinUrl: row.joinUrl ?? null,
        meetingId: row.meetingId ?? null,
        meetingPasscode: row.meetingPasscode ?? null,
        programSessionId: row.programSessionId != null ? Number(row.programSessionId) : null,
      };
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * Value Card recipients — only the eligible registrants who ATTENDED the target session
   * (program_user_attendance.is_attended = true). The value card recaps a session the seeker
   * was present for, so it never goes to no-shows.
   */
  async getValueCardRecipients(
    programId: number,
    sessionId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    return this.queryAttendeeRecipients(programId, sessionId, selectionMode, registrationIds);
  }

  /**
   * Program completion recipients. Sent once the program's sessions have concluded.
   *
   * Audience depends on program type (decided by the caller, which passes finalSessionId):
   * - Normal programs (finalSessionId omitted): every eligible registration, mirroring
   *   Welcome / Value Card.
   * - TAT programs (finalSessionId provided): only registrants who attended the final
   *   session (program_user_attendance.is_attended = true).
   */
  async getProgramCompletionRecipients(
    programId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
    finalSessionId?: number,
  ): Promise<SessionCommunicationRecipient[]> {
    if (finalSessionId) {
      return this.queryAttendeeRecipients(
        programId,
        finalSessionId,
        selectionMode,
        registrationIds,
      );
    }
    return this.queryEligibleRecipients(programId, selectionMode, registrationIds);
  }

  /**
   * Invite recipients for a REGULAR (non-final) session — driven purely by the link (extension)
   * set for the target session: every registrant holding a provisioned join link (the invite
   * carries that link). No registration-eligibility gate (see buildLinkedRecipientsQuery). The
   * FINAL session of a multi-session TAT program uses {@link getFinalInviteRecipients} instead,
   * which additionally requires attendance of every earlier session.
   */
  async getInviteRecipients(
    programId: number,
    sessionId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    try {
      const rows = await this.buildLinkedRecipientsQuery(
        programId,
        sessionId,
        selectionMode,
        registrationIds,
      ).getMany();
      return rows.map((r) => this.toRecipient(r));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * FINAL-session Invite recipients (multi-session TAT programs): driven by the link (extension)
   * set for the final session (see buildLinkedRecipientsQuery — no registration-eligibility gate),
   * further restricted to registrants who attended EVERY earlier session of the program (a session
   * that starts before the final one). A registrant absent from any prior session is excluded
   * (same "attended all priors" rule the final-session-confirm block enforces). Attendance is
   * read from program_user_attendance (is_attended = true), consistent with Absent/Value Card.
   *
   * The NOT EXISTS says "no earlier session exists that this registrant failed to attend", so a
   * registrant with an attended row for every prior session qualifies. Prior sessions are those
   * starting before the final session's start; the final session's own attendance is irrelevant
   * (it hasn't happened — this is the invite to it).
   */
  async getFinalInviteRecipients(
    programId: number,
    finalSessionId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    try {
      const qb = this.buildLinkedRecipientsQuery(
        programId,
        finalSessionId,
        selectionMode,
        registrationIds,
      ).andWhere(
        `NOT EXISTS (
          SELECT 1 FROM program_session ps
          WHERE ps.program_id = :programId
            AND ps.id <> :finalSessionId
            AND ps.deleted_at IS NULL
            AND ps.starts_at IS NOT NULL
            AND ps.starts_at < (
              SELECT fs.starts_at FROM program_session fs WHERE fs.id = :finalSessionId
            )
            AND NOT EXISTS (
              SELECT 1 FROM program_user_attendance att
              WHERE att.registration_id = registration.id
                AND att.session_id = ps.id
                AND att.is_attended = true
            )
        )`,
        { programId, finalSessionId },
      );
      const rows = await qb.getMany();
      return rows.map((r) => this.toRecipient(r));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_RECIPIENTS_FAILED, error);
    }
  }

  /**
   * Absent recipients.
   *
   * Restricted to the eligible registrants who did NOT attend the target session
   * (absentees), read from program_user_attendance (is_attended).
   *
   * TODO(final-session): for the FINAL session, additionally restrict to registrants who
   * attended every prior session (absent in none) before checking the final-session miss.
   */
  async getAbsentRecipients(
    programId: number,
    sessionId: number,
    selectionMode: BulkCommunicationSelectionModeEnum,
    registrationIds?: number[],
  ): Promise<SessionCommunicationRecipient[]> {
    return this.queryAbsenteeRecipients(programId, sessionId, selectionMode, registrationIds);
  }

  /**
   * Append one status row for a completed send (program + session + purpose). Both channels
   * are aggregated on the row. Best-effort — the caller must not let a status-write failure
   * break the send, so this maps errors through handleKnownErrors like the rest of the layer.
   */
  /**
   * Which session-scoped purposes actually went out (status TRIGGERED) for each of the
   * given sessions, in one query — the Overall Analytics timeline's "comms checklist" source.
   * Only lists what's real: a purpose never sent (or only ever SKIPPED) for a session is
   * simply absent from its Set, never reported as a "not sent" placeholder.
   */
  async findTriggeredPurposesBySessions(
    programId: number,
    sessionIds: number[],
  ): Promise<Map<number, SessionCommunicationPurposeEnum[]>> {
    if (sessionIds.length === 0) return new Map();
    try {
      const rows = await this.statusRepo.find({
        where: { programId, sessionId: In(sessionIds), status: SessionCommunicationStatusEnum.TRIGGERED },
      });
      const bySession = new Map<number, Set<SessionCommunicationPurposeEnum>>();
      for (const row of rows) {
        if (row.sessionId == null) continue;
        const purposes = bySession.get(row.sessionId) ?? new Set<SessionCommunicationPurposeEnum>();
        purposes.add(row.purpose);
        bySession.set(row.sessionId, purposes);
      }
      return new Map([...bySession].map(([sessionId, purposes]) => [sessionId, [...purposes]]));
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_SUMMARY_FAILED, error);
    }
  }

  /**
   * Whether a bulk send for this program + session + purpose has actually gone out, i.e. a
   * TRIGGERED row exists in the append-only history.
   *
   * TRIGGERED specifically, not "any row": a SKIPPED row means the run resolved recipients but
   * dispatched nothing (no contact details, or no template configured for the program), so it is
   * not evidence the communication ever reached anyone. Gating a single top-up send on a SKIPPED
   * run would let the single API become the FIRST delivery of the session's value card, which is
   * exactly what it must not be.
   */
  async hasTriggeredBulkSend(
    programId: number,
    sessionId: number,
    purpose: SessionCommunicationPurposeEnum,
  ): Promise<boolean> {
    try {
      return await this.statusRepo.exists({
        where: {
          programId,
          sessionId,
          purpose,
          status: SessionCommunicationStatusEnum.TRIGGERED,
        },
      });
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_SUMMARY_FAILED, error);
    }
  }

  /**
   * Same TRIGGERED-only question as hasTriggeredBulkSend, but for purposes that can be sent EITHER
   * program-wide or scoped to one session (Common Invite / System Links).
   *
   * `sessionId: null` means the program-level run specifically — matched with IS NULL, not "any
   * session" — because the two are genuinely different sends: a program-wide Common Invite covers
   * a SHARED link that backs every session, while a per-session run covers that session's own
   * link. Having sent one must not block the other, so the scope is part of the identity of the
   * send, exactly as it is in the status row itself.
   */
  async hasTriggeredScopedBulkSend(
    programId: number,
    sessionId: number | null,
    purpose: SessionCommunicationPurposeEnum,
  ): Promise<boolean> {
    try {
      return await this.statusRepo.exists({
        where: {
          programId,
          sessionId: sessionId == null ? IsNull() : sessionId,
          purpose,
          status: SessionCommunicationStatusEnum.TRIGGERED,
        },
      });
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_SUMMARY_FAILED, error);
    }
  }

  /**
   * The newest status row for one (session, purpose) — the latest outcome of that bulk send,
   * or null when it has never run for the session.
   *
   * Unlike hasTriggeredBulkSend this does NOT filter to TRIGGERED: callers surfacing the status
   * to a user need to see a SKIPPED run too ("we tried, nothing went out"), which is exactly the
   * distinction a "not sent at all" null loses. Ordered by id, not createdAt — the table is
   * append-only with a BIGSERIAL key, so id order is insertion order and can't tie the way two
   * rows written inside the same clock tick can.
   */
  async findLatestSessionStatus(
    sessionId: number,
    purpose: SessionCommunicationPurposeEnum,
  ): Promise<SessionCommunicationStatus | null> {
    try {
      return await this.statusRepo.findOne({
        where: { sessionId, purpose },
        order: { id: 'DESC' },
      });
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_SUMMARY_FAILED, error);
    }
  }

  async recordSendStatus(entry: {
    programId: number;
    sessionId: number | null;
    purpose: SessionCommunicationPurposeEnum;
    occurrence: string | null;
    status: SessionCommunicationStatusEnum;
    requestedCount: number;
    emailEnqueuedCount: number;
    whatsappEnqueuedCount: number;
    skippedCount: number;
    createdBy: number | null;
  }): Promise<void> {
    try {
      const row = this.statusRepo.create({
        programId: entry.programId,
        sessionId: entry.sessionId,
        purpose: entry.purpose,
        occurrence: entry.occurrence,
        status: entry.status,
        requestedCount: entry.requestedCount,
        emailEnqueuedCount: entry.emailEnqueuedCount,
        whatsappEnqueuedCount: entry.whatsappEnqueuedCount,
        skippedCount: entry.skippedCount,
        createdBy: entry.createdBy,
        updatedBy: entry.createdBy,
      });
      await this.statusRepo.save(row);
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_STATUS_SAVE_FAILED, error);
    }
  }

  /**
   * Per-registration counts grouped by purpose and channel for a program.
   *
   * Sourced from the shared hdb_communication_track table: each send recorded by the
   * queue processor carries the resolved template id, and every session-communication
   * template maps 1:1 to a purpose via its access key. We therefore join to
   * hdb_communication_templates, keep only this feature's access keys, and derive the
   * purpose from the template. `typ` is the channel (email/whatsapp).
   */
  async getSummary(
    programId: number,
    registrationIds?: number[],
  ): Promise<SessionCommunicationSummaryRow[]> {
    try {
      const qb = this.trackRepo
        .createQueryBuilder('t')
        .innerJoin('hdb_communication_templates', 'tpl', 'tpl.id = t.template_id')
        .innerJoin('hdb_program_registration', 'r', 'r.id = t.registration_id')
        .select('t.registration_id', 'registrationId')
        .addSelect('tpl.template_access_key', 'accessKey')
        .addSelect('t.typ', 'channel')
        .addSelect('COUNT(*)', 'count')
        .addSelect('MAX(t.created_at)', 'lastSentAt')
        .where('r.program_id = :programId', { programId })
        .andWhere('tpl.template_access_key IN (:...accessKeys)', {
          accessKeys: SESSION_COMMUNICATION_ACCESS_KEYS,
        })
        .groupBy('t.registration_id')
        .addGroupBy('tpl.template_access_key')
        .addGroupBy('t.typ');

      if (registrationIds && registrationIds.length > 0) {
        qb.andWhere('t.registration_id IN (:...registrationIds)', { registrationIds });
      }

      const raw = await qb.getRawMany();
      return raw
        .map((row) => {
          const descriptor = ACCESS_KEY_TO_DESCRIPTOR[row.accessKey];
          if (!descriptor) {
            return null;
          }
          return {
            registrationId: Number(row.registrationId),
            purpose: descriptor.purpose,
            occurrence: descriptor.occurrence,
            channel: row.channel,
            count: Number(row.count),
            lastSentAt: row.lastSentAt,
          };
        })
        .filter((row): row is SessionCommunicationSummaryRow => row !== null);
    } catch (error) {
      handleKnownErrors(ERROR_CODES.SESSION_COMMUNICATION_SUMMARY_FAILED, error);
    }
  }
}