import { DatabaseError } from "@/lib/error";
import { logError, logInfo } from "@/lib/logger";
import type { RepositoryContext } from "../datastore/repository";
import { BaseRepository } from "../datastore/repository";

/**
 * The console-side half of retiring a member account.
 *
 * Viewpoint owns the account status; everything here mirrors that decision into the
 * member database so Subito stops honouring the account immediately rather than at
 * the next sync. The Viewpoint write happens in the service and must succeed before
 * any of this runs — see account-deletion.service.ts.
 *
 * `members.membership_number` is the Viewpoint AccountID and `members.member_number`
 * is the ContactID; a membership number can carry several member rows (a household),
 * and all of them are retired together because the account is the unit being retired.
 */
/**
 * The Viewpoint status ids an account may be retired to.
 *
 *   95274 — Invalid
 *   95816 — Expired/Cancelled-Subito
 *
 * Held here rather than in configuration: these are not a preference, they are what
 * "deleting an account" means in this business, and an environment that pointed them
 * somewhere else would be applying a different action under the same name.
 */
export const DELETION_STATUS_EXT_IDS = ["95274", "95816"] as const;

export const AccountDeletionRepository = (ctx: RepositoryContext) => {
  /*
   * Two databases, and which is which matters.
   *
   * `datastore` is the console's own database, where the record of what was done
   * lives. `memberDatastore` is the Updot member database holding the accounts
   * being acted on. The deletion history is console data — it is about an operator's
   * action, not about a member — so it stays out of the member database.
   */
  const { datastore: consoleDatastore, memberDatastore: datastore } =
    new BaseRepository(ctx);

  const now = () => new Date().toISOString().replace(/\.\d{3}Z$/, "Z");

  /**
   * The statuses an account may be moved to.
   *
   * Two, and only these two. `member_account_statuses` holds the whole Viewpoint
   * status list — Active, Suspended, Terminated, and the rest — and almost none of
   * them describe a deletion. Offering them all would make this page a general
   * status editor, which is a different tool with different consequences.
   *
   * Matched on `ext_account_status_id`, the Viewpoint id, not on the name. The name
   * is display text and has already been edited once ("Expired/Cancelled-Subito");
   * the Viewpoint id is the thing both systems agree on.
   */
  const FindAccountStatuses = async () => {
    try {
      const all = await datastore
        .selectFrom("member_account_statuses")
        .select(["id", "name", "ext_account_status_id", "is_active"])
        .orderBy("name", "asc")
        .execute();

      /*
       * Narrowed in JavaScript, not in SQL.
       *
       * `ext_account_status_id` is typed as text here but the member database is not
       * ours, and a WHERE ... IN ('95274','95816') either matches nothing or fails
       * outright if the column is actually numeric — and a padded or space-wrapped
       * value misses even when the types line up. The table is a handful of rows, so
       * reading it whole and comparing normalised strings costs nothing and cannot
       * be defeated by either.
       */
      const wanted = new Set<string>(DELETION_STATUS_EXT_IDS);
      const matched = all.filter((status) =>
        wanted.has(String(status.ext_account_status_id ?? "").trim()),
      );

      /*
       * A miss is logged with what was actually there.
       *
       * Silence would leave an empty dropdown and no way to tell whether the status
       * is missing from the database, renumbered, or stored in a form the match does
       * not recognise — which is three different fixes.
       */
      if (matched.length !== wanted.size) {
        logError(
          `[AccountDeletion] Expected Viewpoint status ids ${[...wanted].join(", ")}, matched ${matched.length}. Present: ${all
            .map((status) => `${status.name}=${status.ext_account_status_id}`)
            .join(" | ")}`,
        );
      }

      return matched;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch member account statuses",
      });
    }
  };

  const FindAccountStatusByID = async (statusID: string) => {
    try {
      return await datastore
        .selectFrom("member_account_statuses")
        .select(["id", "name", "ext_account_status_id", "is_active"])
        .where("id", "=", statusID)
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch the member account status",
      });
    }
  };

  /**
   * Everyone on a membership, with the state the deletion is about to change.
   *
   * Deliberately not filtered to active rows: an account that has already been
   * retired must still be findable, or the page could not show an operator that the
   * work was already done and would invite a second attempt.
   */
  const FindMembersByMembershipNumber = async (membershipNumber: string) => {
    try {
      return await baseSelect()
        .where("members.membership_number", "=", membershipNumber)
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to look up the member account",
      });
    }
  };

  // ILIKE rather than "=": Viewpoint and the member database disagree on the case
  // of an address often enough that an exact match would report "no account" for
  // one that is plainly there.
  const FindMembersByEmail = async (email: string) => {
    try {
      return await baseSelect()
        .where("member_user.email", "ilike", email)
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to look up the member account",
      });
    }
  };

  const baseSelect = () =>
    datastore
      .selectFrom("members")
      .innerJoin("users as member_user", "member_user.id", "members.user_id")
      .innerJoin(
        "member_account_types as account_type",
        "account_type.id",
        "members.membership_type_id",
      )
      .innerJoin(
        "member_account_statuses as account_status",
        "account_status.id",
        "members.membership_status_id",
      )
      .leftJoin("member_profiles as profile", "profile.member_id", "members.id")
      .select([
        "members.id as member_id",
        "members.member_number",
        "members.membership_number",
        "members._is_active as is_active",
        "members._is_deleted as is_deleted",
        "members.created_at",
        "members.updated_at",
        "member_user.id as user_id",
        "member_user.email",
        "member_user._is_active as user_is_active",
        "member_user._is_deleted as user_is_deleted",
        "profile.first_name",
        "profile.last_name",
        // Drives which region's deletion letters the account gets.
        "profile.country",
        "profile._is_owner as is_owner",
        "profile._is_family as is_family",
        "account_type.id as account_type_id",
        "account_type.name as account_type_name",
        "account_type.ext_account_type_id as account_type_ext_id",
        "account_status.id as account_status_id",
        "account_status.name as account_status_name",
        "account_status.ext_account_status_id as account_status_ext_id",
      ])
      .orderBy("profile._is_owner", "desc")
      .orderBy("members.member_number", "asc");

  const CountActiveSessions = async (userIDs: string[]) => {
    if (userIDs.length === 0) {
      return 0;
    }
    try {
      const row = await datastore
        .selectFrom("sessions")
        .select((eb) => eb.fn.countAll<string>().as("total"))
        .where("user_id", "in", userIDs)
        .executeTakeFirst();
      return Number(row?.total ?? 0);
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to count member sessions",
      });
    }
  };

  /**
   * Applies the retirement to every member row on the membership.
   *
   * One transaction, so an account cannot end up with its status changed but its
   * login still live. The Viewpoint write is outside it and already committed by the
   * time this runs — if this fails, the two systems disagree and the caller reports
   * that rather than pretending the whole thing succeeded.
   */
  const ApplyAccountRetirement = async (params: {
    memberIDs: string[];
    userIDs: string[];
    statusID: string;
    killLogin: boolean;
    revokeSessions: boolean;
    softDelete: boolean;
  }) => {
    const {
      memberIDs,
      userIDs,
      statusID,
      killLogin,
      revokeSessions,
      softDelete,
    } = params;

    if (memberIDs.length === 0) {
      return { membersUpdated: 0, usersUpdated: 0, sessionsRevoked: 0 };
    }

    const trx = await datastore.startTransaction().execute();
    try {
      const timestamp = now();

      const memberResult = await trx
        .updateTable("members")
        .set({
          membership_status_id: statusID,
          updated_at: timestamp,
          ...(killLogin && { _is_active: false }),
          ...(softDelete && { _is_deleted: true }),
        })
        .where("members.id", "in", memberIDs)
        .executeTakeFirst();

      let usersUpdated = 0;
      if ((killLogin || softDelete) && userIDs.length > 0) {
        const userResult = await trx
          .updateTable("users")
          .set({
            updated_at: timestamp,
            ...(killLogin && { _is_active: false }),
            ...(softDelete && { _is_deleted: true }),
          })
          .where("users.id", "in", userIDs)
          .executeTakeFirst();
        usersUpdated = Number(userResult?.numUpdatedRows ?? 0);
      }

      let sessionsRevoked = 0;
      if (revokeSessions && userIDs.length > 0) {
        const sessionResult = await trx
          .deleteFrom("sessions")
          .where("user_id", "in", userIDs)
          .executeTakeFirst();
        sessionsRevoked = Number(sessionResult?.numDeletedRows ?? 0);
      }

      await trx.commit().execute();
      return {
        membersUpdated: Number(memberResult?.numUpdatedRows ?? 0),
        usersUpdated,
        sessionsRevoked,
      };
    } catch (err) {
      await trx.rollback().execute();
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to apply the account deletion",
      });
    }
  };


  /* ------------------------------------------------------------------ *
   * The console-side record of what was done.
   * ------------------------------------------------------------------ */

  /**
   * Days between retiring an account and the follow-up mail, when nothing says
   * otherwise. Overridable per installation through the `account-deletion` console
   * setting, which the service reads and passes in.
   */
  const DEFAULT_FOLLOWUP_DAYS = 30;

  const InsertDeletionRecord = async (entry: {
    accountNumber: string;
    accountName: string | null;
    statusID: string;
    statusName: string;
    extAccountStatusID: string | null;
    previousStatus: string | null;
    previousStatusID: number | null;
    reason: string;
    killLogin: boolean;
    revokeSessions: boolean;
    softDelete: boolean;
    membersUpdated: number;
    usersUpdated: number;
    sessionsRevoked: number;
    localError: string | null;
    performedBy: string | null;
    performedByID: string | null;
    recipients: string[];
    noticeTemplateID: string | null;
    followupTemplateID: string | null;
    region: string;
    viewpointLogError: string | null;
    /*
     * The window this deletion promised, stored as a due date rather than a count.
     *
     * Changing the setting later must not move mail that has already been promised
     * at the old window — the member was told when to expect it.
     */
    followupDays?: number;
  }) => {
    try {
      const days = entry.followupDays ?? DEFAULT_FOLLOWUP_DAYS;
      const followupDueAt = new Date(Date.now() + days * 24 * 60 * 60 * 1000);
      return await consoleDatastore
        .insertInto("account_deletion_records")
        .values({
          account_number: entry.accountNumber,
          account_name: entry.accountName,
          status_id: entry.statusID,
          status_name: entry.statusName,
          ext_account_status_id: entry.extAccountStatusID,
          previous_status: entry.previousStatus,
          previous_status_id: entry.previousStatusID,
          reason: entry.reason,
          kill_login: entry.killLogin,
          revoke_sessions: entry.revokeSessions,
          soft_delete: entry.softDelete,
          members_updated: entry.membersUpdated,
          users_updated: entry.usersUpdated,
          sessions_revoked: entry.sessionsRevoked,
          local_error: entry.localError,
          performed_by: entry.performedBy,
          performed_by_id: entry.performedByID,
          recipients: JSON.stringify(entry.recipients) as never,
          notice_template_id: entry.noticeTemplateID,
          followup_template_id: entry.followupTemplateID,
          region: entry.region,
          viewpoint_log_status: entry.viewpointLogError ? "failed" : "sent",
          viewpoint_log_error: entry.viewpointLogError,
          followup_days: days,
          followup_due_at: followupDueAt,
        })
        .returningAll()
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to record the account deletion",
      });
    }
  };

  /**
   * Records the outcome of one of the two mails.
   *
   * Written separately from the row itself because the send happens after the row
   * exists — the follow-up 30 days after — and because a failed send must not undo
   * the record of a retirement that did happen.
   */
  const MarkEdmOutcome = async (params: {
    recordID: string;
    kind: "notice" | "followup";
    status: "sent" | "failed" | "skipped";
    error?: string | null;
    recipients?: string[];
  }) => {
    const prefix = params.kind === "notice" ? "notice" : "followup";
    try {
      await consoleDatastore
        .updateTable("account_deletion_records")
        .set({
          [`${prefix}_edm_status`]: params.status,
          [`${prefix}_edm_at`]: new Date(),
          [`${prefix}_edm_error`]: params.error ?? null,
          updated_at: new Date(),
          ...(params.recipients && {
            recipients: JSON.stringify(params.recipients) as never,
          }),
        } as never)
        .where("id", "=", params.recordID)
        .execute();
    } catch (err) {
      // Logged, not thrown: the mail already went (or already failed), and losing
      // the bookkeeping must not turn a delivered mail into a reported error.
      logError(err);
    }
  };

  const ListDeletionRecords = async (params: {
    page: number;
    pageSize: number;
    search?: string;
  }) => {
    const { page, pageSize, search } = params;
    try {
      let query = consoleDatastore.selectFrom("account_deletion_records");
      if (search) {
        const term = `%${search}%`;
        query = query.where((eb) =>
          eb.or([
            eb("account_number", "ilike", term),
            eb("account_name", "ilike", term),
            eb("performed_by", "ilike", term),
            eb("status_name", "ilike", term),
          ]),
        );
      }

      const [rows, total] = await Promise.all([
        query
          .selectAll()
          .orderBy("created_at", "desc")
          .limit(pageSize)
          .offset((page - 1) * pageSize)
          .execute(),
        query
          .select((eb) => eb.fn.countAll<string>().as("total"))
          .executeTakeFirst(),
      ]);

      return { rows, total: Number(total?.total ?? 0) };
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to list account deletion records",
      });
    }
  };


  const FindDeletionRecordByID = async (recordID: string) => {
    try {
      return await consoleDatastore
        .selectFrom("account_deletion_records")
        .selectAll()
        .where("id", "=", recordID)
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to load the account deletion record",
      });
    }
  };

  const FindAccountStatusByExtID = async (extStatusID: string) => {
    try {
      // Compared in JavaScript for the same reason as `FindAccountStatuses` above:
      // the column's storage type and formatting are not ours to rely on.
      const all = await datastore
        .selectFrom("member_account_statuses")
        .select(["id", "name", "ext_account_status_id", "is_active"])
        .execute();
      return all.find(
        (status) =>
          String(status.ext_account_status_id ?? "").trim() ===
          extStatusID.trim(),
      );
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch the member account status",
      });
    }
  };

  /**
   * Puts an account back the way it was.
   *
   * The mirror image of `ApplyAccountRetirement`, and deliberately not a generic
   * "set these flags" call: reactivation always clears both the inactive and the
   * deleted flag on the member and the sign-in account, because leaving either set
   * would produce an account that is active in Viewpoint and still unusable in
   * Subito — the exact divergence the ordering everywhere else exists to avoid.
   *
   * Sessions are not restored. They were deleted, not disabled, and the member signs
   * in again — which is the correct outcome anyway.
   */
  const ApplyAccountReactivation = async (params: {
    memberIDs: string[];
    userIDs: string[];
    statusID: string;
  }) => {
    const { memberIDs, userIDs, statusID } = params;
    if (memberIDs.length === 0) {
      return { membersUpdated: 0, usersUpdated: 0 };
    }

    const trx = await datastore.startTransaction().execute();
    try {
      const timestamp = now();

      const memberResult = await trx
        .updateTable("members")
        .set({
          membership_status_id: statusID,
          _is_active: true,
          _is_deleted: false,
          updated_at: timestamp,
        })
        .where("members.id", "in", memberIDs)
        .executeTakeFirst();

      let usersUpdated = 0;
      if (userIDs.length > 0) {
        const userResult = await trx
          .updateTable("users")
          .set({ _is_active: true, _is_deleted: false, updated_at: timestamp })
          .where("users.id", "in", userIDs)
          .executeTakeFirst();
        usersUpdated = Number(userResult?.numUpdatedRows ?? 0);
      }

      await trx.commit().execute();
      return {
        membersUpdated: Number(memberResult?.numUpdatedRows ?? 0),
        usersUpdated,
      };
    } catch (err) {
      await trx.rollback().execute();
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to reactivate the account",
      });
    }
  };

  /**
   * Marks the record reversed and stands the follow-up mail down.
   *
   * One statement, so a reactivation cannot be recorded while the 30-day mail is
   * still queued to tell the member their account was closed.
   *
   * The follow-up is only cancelled while it is still pending — a mail already sent
   * or already skipped keeps the outcome it had, because rewriting it would claim
   * something about the past that is not true.
   */
  const MarkReactivated = async (params: {
    recordID: string;
    reactivatedBy: string | null;
    restoredStatusName: string;
  }) => {
    try {
      await consoleDatastore
        .updateTable("account_deletion_records")
        .set((eb) => ({
          reactivated_at: new Date(),
          reactivated_by: params.reactivatedBy,
          reactivated_to_status: params.restoredStatusName,
          followup_edm_status: eb
            .case()
            .when("followup_edm_status", "=", "pending")
            .then("cancelled")
            .else(eb.ref("followup_edm_status"))
            .end(),
          updated_at: new Date(),
        }))
        .where("id", "=", params.recordID)
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to record the reactivation",
      });
    }
  };


  /**
   * The console-wide follow-up window, or null when it has never been set.
   *
   * Read straight off `console_settings` rather than through the settings HTTP
   * endpoint: this runs inside a request that is already authenticated, and going
   * back out over HTTP to read our own database would add a hop that can fail.
   */
  const FindFollowupDaysSetting = async (): Promise<number | null> => {
    try {
      const row = await consoleDatastore
        .selectFrom("console_settings" as any)
        .select(["value"] as any)
        .where("key" as any, "=", "account-deletion")
        .executeTakeFirst();
      const value = (row as { value?: unknown } | undefined)?.value;
      if (!value || typeof value !== "object") return null;
      const days = (value as { followupDays?: unknown }).followupDays;
      return typeof days === "number" ? days : null;
    } catch (err) {
      // A missing or unreadable setting is not an error — the caller falls back to
      // the default rather than refusing to retire an account over it.
      logError(err);
      return null;
    }
  };

  /**
   * Saves the follow-up window onto the same `console_settings` row the generic
   * settings endpoint uses.
   *
   * Upserted on the key, and the whole value object is replaced rather than merged.
   * The row holds one field today; a merge would be guessing about a shape that does
   * not exist yet, and this is the only writer.
   */
  const SaveFollowupDaysSetting = async (
    days: number,
    updatedBy: string | null,
  ): Promise<void> => {
    try {
      await consoleDatastore
        .insertInto("console_settings" as any)
        .values({
          key: "account-deletion",
          value: JSON.stringify({ followupDays: days }),
          updated_by: updatedBy,
          updated_at: new Date(),
        } as never)
        .onConflict((oc: any) =>
          oc.column("key").doUpdateSet({
            value: JSON.stringify({ followupDays: days }),
            updated_by: updatedBy,
            updated_at: new Date(),
          }),
        )
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to save the follow-up window",
      });
    }
  };

  /*
   * Whether this deployment has a charge centre table at all.
   *
   * `membership_club_charge_centers` is created by another service, not by a
   * migration in this repo, so an environment can legitimately be without it. Once
   * Postgres has told us the relation does not exist, asking again on every account
   * search is a wasted round trip and a stack trace in the log for a condition that
   * is not an error — so the answer is remembered for the life of the process.
   *
   * Restarting core re-checks, which is the right granularity: the table appearing
   * is a deployment event, not something that happens mid-request.
   */
  let chargeCentresAvailable = true;

  /**
   * The charge centre behind a Viewpoint charge centre id.
   *
   * The id comes off the Viewpoint account (`DefaultChargeCenter`); the name lives in
   * the console's mirror of Viewpoint's charge centre list. Compared as trimmed
   * strings for the same reason the status ids are — the column is text here and a
   * number there, and neither end is ours to rely on.
   *
   * Returns undefined rather than throwing when the table is missing. The caller
   * uses this only to sharpen region detection, and falls back to the account's
   * country; refusing to search an account because an optional reference table is
   * absent would be a much worse failure than the one it is guarding against.
   */
  const FindChargeCentreByViewpointID = async (
    viewpointChargeCentreID: string,
  ) => {
    if (!chargeCentresAvailable) return undefined;
    try {
      const all = await consoleDatastore
        .selectFrom("membership_club_charge_centers")
        .select(["id", "name", "currency", "viewpoint_charge_center_id"])
        .execute();
      return all.find(
        (centre) =>
          String(centre.viewpoint_charge_center_id ?? "").trim() ===
          viewpointChargeCentreID.trim(),
      );
    } catch (err) {
      // 42P01 is Postgres' undefined_table. Anything else is a real fault and is
      // logged as one.
      if ((err as { code?: string })?.code === "42P01") {
        chargeCentresAvailable = false;
        logInfo(
          "[AccountDeletion] No membership_club_charge_centers table in this database — " +
            "region will be resolved from the account's country instead. Not asking again.",
        );
        return undefined;
      }
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to look up the charge centre",
      });
    }
  };


  /**
   * The account's current, unreversed deletion — if it has one.
   *
   * "Current" means not reactivated. An account that was retired, restored and
   * retired again has several records, and only the last unreversed one describes
   * the state it is in now; the earlier ones are closed history and must not be
   * written over.
   */
  const FindActiveRecordForAccount = async (accountNumber: string) => {
    try {
      return await consoleDatastore
        .selectFrom("account_deletion_records")
        .selectAll()
        .where("account_number", "=", accountNumber)
        .where("reactivated_at", "is", null)
        .orderBy("created_at", "desc")
        .executeTakeFirst();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to look up the account's current deletion",
      });
    }
  };

  /**
   * Moves an already-retired account to the other deletion status, in place.
   *
   * An update rather than a second row, because it is the same retirement — the
   * account was closed once and its status is being corrected. Inserting again would
   * present one closure as two on the member's history and, worse, queue a second
   * data-deletion email for a member who is already waiting on the first.
   *
   * `followup_due_at` is deliberately untouched. The member was told when their data
   * would be erased, and correcting the status is not a reason to move that date.
   */
  const UpdateDeletionRecordStatus = async (params: {
    recordID: string;
    statusID: string;
    statusName: string;
    extAccountStatusID: string | null;
    previousStatus: string | null;
    previousStatusID: number | null;
    reason: string;
    region: string;
    membersUpdated: number;
    usersUpdated: number;
    sessionsRevoked: number;
    localError: string | null;
    performedBy: string | null;
    followupTemplateID: string;
    viewpointLogError: string | null;
  }) => {
    try {
      return await consoleDatastore
        .updateTable("account_deletion_records")
        .set({
          status_id: params.statusID,
          status_name: params.statusName,
          ext_account_status_id: params.extAccountStatusID,
          previous_status: params.previousStatus,
          previous_status_id: params.previousStatusID,
          reason: params.reason,
          region: params.region,
          members_updated: params.membersUpdated,
          users_updated: params.usersUpdated,
          sessions_revoked: params.sessionsRevoked,
          local_error: params.localError,
          performed_by: params.performedBy,
          followup_template_id: params.followupTemplateID,
          viewpoint_log_status: params.viewpointLogError ? "failed" : "sent",
          viewpoint_log_error: params.viewpointLogError,
          updated_at: new Date(),
        })
        .where("id", "=", params.recordID)
        .returningAll()
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to update the account deletion record",
      });
    }
  };


  /**
   * Every account currently sitting in one of the deletion statuses.
   *
   * The member database is the source of truth here, not the console's own record
   * of what it did. An account can be retired directly in Viewpoint and synced down
   * without this console ever touching it, and those accounts are exactly the ones
   * an operator needs to see — they are retired, and nothing here says why.
   *
   * Filtered on `membership_status_id`, not on the Viewpoint id: the caller resolves
   * the status rows first (which compares ids as trimmed strings, because the column
   * type is not ours to rely on) and passes the primary keys, which are a plain
   * integer join either database will agree about.
   *
   * Grouped by membership number so a household of four members is one retired
   * account rather than four rows saying the same thing.
   */
  const FindRetiredAccounts = async (params: {
    statusIDs: string[];
    page: number;
    pageSize: number;
    search?: string;
  }) => {
    const { statusIDs, page, pageSize, search } = params;
    if (statusIDs.length === 0) {
      return { rows: [], total: 0 };
    }

    try {
      const base = datastore
        .selectFrom("members")
        .innerJoin(
          "member_account_statuses as account_status",
          "account_status.id",
          "members.membership_status_id",
        )
        .leftJoin("member_profiles as profile", "profile.member_id", "members.id")
        .where("members.membership_status_id", "in", statusIDs)
        .$if(Boolean(search), (qb) =>
          qb.where((eb) =>
            eb.or([
              eb("members.membership_number", "ilike", `%${search}%`),
              eb("profile.first_name", "ilike", `%${search}%`),
              eb("profile.last_name", "ilike", `%${search}%`),
            ]),
          ),
        );

      const [rows, totals] = await Promise.all([
        base
          .select((eb) => [
            "members.membership_number as membership_number",
            eb.fn.max("account_status.name").as("status_name"),
            eb.fn.max("profile.first_name").as("first_name"),
            eb.fn.max("profile.last_name").as("last_name"),
            eb.fn.countAll<string>().as("member_count"),
            eb.fn.max("members.updated_at").as("updated_at"),
          ])
          .groupBy("members.membership_number")
          .orderBy((eb) => eb.fn.max("members.updated_at"), "desc")
          .limit(pageSize)
          .offset((page - 1) * pageSize)
          .execute(),
        /*
         * Counted over the distinct membership numbers, not the member rows.
         *
         * A household of four is one retired account; counting rows would report a
         * total the pager could never reach.
         */
        base
          .select((eb) =>
            eb.fn
              .count<string>("members.membership_number")
              .distinct()
              .as("total"),
          )
          .executeTakeFirst(),
      ]);

      return { rows, total: Number(totals?.total ?? 0) };
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to list retired accounts",
      });
    }
  };

  /**
   * The newest console record for each of the given accounts.
   *
   * One query rather than one per account, and reduced to the first hit per number
   * because the rows arrive newest-first — which is the round that describes the
   * account's current state.
   */
  const FindLatestRecordsForAccounts = async (accountNumbers: string[]) => {
    if (accountNumbers.length === 0) return new Map<string, any>();
    try {
      const rows = await consoleDatastore
        .selectFrom("account_deletion_records")
        .selectAll()
        .where("account_number", "in", accountNumbers)
        .orderBy("created_at", "desc")
        .execute();

      const latest = new Map<string, (typeof rows)[number]>();
      for (const row of rows) {
        if (!latest.has(row.account_number)) latest.set(row.account_number, row);
      }
      return latest;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to load the deletion records for these accounts",
      });
    }
  };

  return {
    FindAccountStatuses,
    FindAccountStatusByID,
    FindChargeCentreByViewpointID,
    FindFollowupDaysSetting,
    SaveFollowupDaysSetting,
    FindMembersByMembershipNumber,
    FindMembersByEmail,
    CountActiveSessions,
    ApplyAccountRetirement,
    InsertDeletionRecord,
    FindActiveRecordForAccount,
    UpdateDeletionRecordStatus,
    MarkEdmOutcome,
    ListDeletionRecords,
    FindRetiredAccounts,
    FindLatestRecordsForAccounts,
    FindDeletionRecordByID,
    FindAccountStatusByExtID,
    ApplyAccountReactivation,
    MarkReactivated,
    DEFAULT_FOLLOWUP_DAYS,
  };
};

export type TAccountDeletionRepository = ReturnType<
  typeof AccountDeletionRepository
>;