import type { Kysely } from "kysely";
import { sql } from "kysely";

/**
 * Read-only queries against the Updot member DB (`memberDatastore`).
 *
 * The Updot `members` table has a `signup_promo_code` text column populated
 * at signup time. We use it to attribute signups to promo codes.
 *
 * IMPORTANT: This repository must only issue SELECT statements. Updot's
 * member DB is production data — no writes from this codebase.
 */
/**
 * Statuses that mean "this booking did not happen". Shared by the internal and
 * curated counts so both columns exclude the same set. `booking_units.status`
 * and `booking_curated_events.status` are both the `booking_status` enum, so
 * one list covers both. HOLD / SYSTEM_PROCESSING are kept — they are bookings
 * in flight, not cancellations.
 */
/**
 * The membership everyone starts on when they sign up with a promo code.
 *
 * Promo-code reporting is about people acquired *by* a promo code and still only
 * holding the guest membership it gave them. Once someone's contract is upgraded to a
 * real membership — Karma Club, Karma Royal Residences, House of Karma — they are a
 * member of that product, and counting them as a promo-code signup overstates what the
 * campaign currently accounts for.
 *
 * Measured on the live member DB: 17,619 of 17,661 promo signups are still on the guest
 * membership and 42 have been upgraded, between them holding 1 booking. So the
 * correction is small — but it is a correction, and it is the difference between "who
 * this campaign brought in" and "who this campaign still holds".
 *
 * Matched by name through a subquery rather than by id. The id is 44 in this database,
 * but nothing guarantees that across environments, whereas the name is the thing the
 * business actually names. No join is needed either, which is why this can be dropped
 * into queries that never look at account types.
 */
/**
 * How a promo code is normalised for comparison.
 *
 * `TRIM()` in Postgres strips **spaces only**. Real data has worse: 12 members carry
 * codes like "\t\r\nSQUARECLUB" and "\nAPARNA17", which survived `TRIM(UPPER(...))`
 * intact and were therefore grouped as promo codes of their own. They matched nothing
 * in the campaign map, so their signups silently fell out of every per-code total —
 * one of the count mismatches on these pages.
 *
 * JavaScript's `.trim()` strips all whitespace, so the console side was already
 * normalising differently from the database. Naming the character set makes both ends
 * agree.
 */
const GUEST_MEMBERSHIP_NAME = "KARMA CLUB GUEST MEMBERSHIP";

/**
 * Restricts a query to members whose contract has not been upgraded.
 *
 * A NULL membership type is kept: it means the type is unknown, not that it changed,
 * and dropping those would quietly remove members on no evidence.
 */
const NOT_UPGRADED = `(
  members.membership_type_id IS NULL
  OR members.membership_type_id IN (
    SELECT id FROM member_account_types WHERE name = '${GUEST_MEMBERSHIP_NAME}'
  )
)`;

const DEAD_BOOKING_STATUSES = [
  "SELF_CANCELLED",
  "SYSTEM_CANCELLED",
  "FAILED",
  "SYSTEM_FAULT",
];

const DEAD_STATUS_LIST = DEAD_BOOKING_STATUSES.map((s) => `'${s}'`).join(", ");

/**
 * Every value of the `booking_status` enum, in the DB's own order.
 *
 * Read from the enum rather than inferred from the data so the bookings page can
 * offer statuses that exist but currently have no rows — otherwise the filter
 * silently shrinks to whatever happens to be present today.
 */
export const ALL_BOOKING_STATUSES = [
  "HOLD",
  "SYSTEM_PROCESSING",
  "UPCOMING",
  "CHECKED_IN",
  "CHECKED_OUT",
  "ENDED",
  "SELF_CANCELLED",
  "SYSTEM_CANCELLED",
  "FAILED",
  "SYSTEM_FAULT",
] as const;

/*
 * Classification predicates that ignore status.
 *
 * The INTERNAL/CURATED predicates above require a non-dead child row, which
 * means a fully cancelled booking is not merely excluded from the totals — it
 * cannot be selected at all. That is right for the campaign dashboard's figures,
 * but it made "show me cancelled bookings" impossible on the bookings page.
 *
 * These decide only *what kind* of booking it is; whether it counts as live is
 * then a separate, explicit filter.
 */
const INTERNAL_ANY_STATUS = `
  booking_entities.type IN ('RESORT', 'INTERNAL_PROPERTY')
  AND EXISTS (SELECT 1 FROM booking_units bu WHERE bu.booking_id = bookings.id)`;

const CURATED_ANY_STATUS = `
  EXISTS (
    SELECT 1 FROM booking_curated_events bce WHERE bce.booking_id = bookings.id
  )`;

/**
 * "Internal" = a stay at a Karma-operated property. Counted via
 * `booking_entities.type`, requiring at least one unit that was not cancelled.
 * A booking can hold several units, hence EXISTS rather than a join (a join
 * would multiply the count by the unit count).
 */
const INTERNAL_BOOKING_PREDICATE = `
  booking_entities.type IN ('RESORT', 'INTERNAL_PROPERTY')
  AND EXISTS (
    SELECT 1 FROM booking_units bu
    WHERE bu.booking_id = bookings.id
      AND bu.status::text NOT IN (${DEAD_STATUS_LIST})
  )`;

/**
 * "Curated" = a curated-event booking, read from `booking_curated_events` (the
 * authoritative curated booking record) rather than inferred from
 * `booking_entities.type = 'CURATED_EVENT'`. The two are 1:1 today, but the
 * curated table is what carries the curated booking's own status.
 */
const CURATED_BOOKING_PREDICATE = `
  EXISTS (
    SELECT 1 FROM booking_curated_events bce
    WHERE bce.booking_id = bookings.id
      AND bce.status::text NOT IN (${DEAD_STATUS_LIST})
  )`;

function getSearchCodes(promoCodes: string[]): string[] {
  const set = new Set<string>();
  for (const c of promoCodes) {
    if (!c) continue;
    const trimmed = c.trim().toUpperCase();
    if (trimmed) set.add(trimmed);
  }
  return Array.from(set);
}

/**
 * A count from the aggregate, or null when it genuinely has none.
 *
 * Exists because `||` cannot tell a real 0 from a missing value, and both appear here:
 * a (promo code, country, area) group where nobody has logged in counts 0, which must
 * not be mistaken for "the aggregate said nothing".
 */
function fromAggregate(value: unknown): number | null {
  if (value === null || value === undefined) return null;
  const n = Number(value);
  return Number.isFinite(n) ? n : null;
}

export class PromoCodeSignupsRepository {
  private db: Kysely<any>;
  /*
   * The console's own DB. Kept on the constructor for callers, but deliberately
   * NOT used for any query in this repository.
   *
   * `members`, `member_profiles`, `member_account_types` and `bookings` exist
   * only in the member DB. Two methods here used to retry against this handle
   * when the member DB returned no rows, which could never work and instead
   * raised `relation "members" does not exist` — turning an empty result into a
   * 500. Do not reintroduce that.
   */
  private mainDb?: Kysely<any>;

  constructor(db: Kysely<any>, mainDb?: Kysely<any>) {
    this.db = db;
    this.mainDb = mainDb;
  }

  private get memberDb(): Kysely<any> {
    return this.db;
  }

  /**
   * Contact completeness per promo code: how many signups have a phone, an
   * email, both, or neither.
   *
   * `users` is LEFT joined deliberately. The members list query inner-joins it,
   * but an inner join here would silently drop any member without a user row and
   * the bucket total would no longer reconcile with `aggregateSignups` — which is
   * the first comparison anyone makes. A missing user row simply means no email.
   *
   * SELECT only: the Updot member DB is production data.
   */
  async aggregateContactCompleteness(opts: {
    promoCodes: string[];
    from?: string;
    to?: string;
    country?: string[];
  }): Promise<
    Array<{
      promo_code: string;
      phone_and_email: number;
      phone_only: number;
      email_only: number;
      neither: number;
      total: number;
    }>
  > {
    const { promoCodes, from, to, country } = opts;
    if (promoCodes.length === 0) return [];

    const searchCodes = getSearchCodes(promoCodes);

    // Treat whitespace-only as absent — the columns hold user-entered values.
    const hasPhone = sql<boolean>`COALESCE(BTRIM(profile.mobile), '') <> ''`;
    const hasEmail = sql<boolean>`COALESCE(BTRIM(member_user.email), '') <> ''`;

    let q = this.memberDb
      .selectFrom("members")
      .leftJoin("member_profiles as profile", "profile.member_id", "members.id")
      .leftJoin("users as member_user", "member_user.id", "members.user_id")
      .select([
        sql<string>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v')`.as("promo_code"),
        sql<number>`COUNT(DISTINCT members.id) FILTER (WHERE ${hasPhone} AND ${hasEmail})`.as(
          "phone_and_email",
        ),
        sql<number>`COUNT(DISTINCT members.id) FILTER (WHERE ${hasPhone} AND NOT ${hasEmail})`.as(
          "phone_only",
        ),
        sql<number>`COUNT(DISTINCT members.id) FILTER (WHERE NOT ${hasPhone} AND ${hasEmail})`.as(
          "email_only",
        ),
        sql<number>`COUNT(DISTINCT members.id) FILTER (WHERE NOT ${hasPhone} AND NOT ${hasEmail})`.as(
          "neither",
        ),
        sql<number>`COUNT(DISTINCT members.id)`.as("total"),
      ])
      .where(
        sql<boolean>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes})`,
      )
      // Upgraded contracts are out of promo scope — see NOT_UPGRADED. Applied to
      // every aggregate as well as the lists, or the totals and the rows they
      // drill into would disagree.
      .where(sql<boolean>`${sql.raw(NOT_UPGRADED)}`)
      .groupBy(sql`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v')`);

    // Same window/country filters as aggregateSignups, so the two reconcile.
    if (from) {
      q = q.where("members.created_at" as any, ">=", new Date(from));
    }
    if (to) {
      const toDate = new Date(to);
      if (!to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
      q = q.where("members.created_at" as any, "<=", toDate);
    }
    if (country && country.length > 0) {
      q = q.where(sql<boolean>`profile.country = ANY(${country})`);
    }

    const rows = await q.execute();
    return rows.map((r: any) => ({
      promo_code: String(r.promo_code ?? ""),
      phone_and_email: Number(r.phone_and_email ?? 0),
      phone_only: Number(r.phone_only ?? 0),
      email_only: Number(r.email_only ?? 0),
      neither: Number(r.neither ?? 0),
      total: Number(r.total ?? 0),
    }));
  }

  /**
   * Returns counts grouped by (signup_promo_code, country, servicing_area)
   * limited to the supplied promo code list and optional date range
   * (`from` / `to` are inclusive ISO date strings, matched against
   * `members.created_at`).
   */
  async aggregateSignups(opts: {
    promoCodes: string[];
    from?: string;
    to?: string;
    // Optional: restrict to signups whose member profile country is in this list.
    country?: string[];
  }): Promise<
    Array<{
      promo_code: string;
      country: string | null;
      area: string | null;
      signups: number;
      logged_in: number;
      internal_bookings: number;
      external_bookings: number;
    }>
  > {
    const { promoCodes, from, to, country } = opts;
    if (promoCodes.length === 0) return [];

    // Case-insensitive match: members.signup_promo_code is stored as
    // whatever was entered at signup. Our promo_code_name comes from
    // user-typed input via the UI, so normalize both sides.
    const searchCodes = getSearchCodes(promoCodes);

    let q = this.memberDb
      .selectFrom("members")
      .leftJoin("member_profiles as profile", "profile.member_id", "members.id")
      .leftJoin(
        "member_account_types as account_type",
        "account_type.id",
        "members.membership_type_id",
      )
      .select([
        sql<string>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v')`.as("promo_code"),
        sql<string | null>`profile.country`.as("country"),
        sql<string | null>`account_type.name`.as("area"),
        sql<number>`COUNT(DISTINCT members.id)`.as("signups"),
        // "logged in" = members who have actually accessed the app at least
        // once. The Updot members table flags this via first_access_attempt.
        sql<number>`COUNT(DISTINCT members.id) FILTER (WHERE members.first_access_attempt IS TRUE)`.as(
          "logged_in",
        ),
      ])
      .where(
        sql<boolean>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes})`,
      )
      // Upgraded contracts are out of promo scope — see NOT_UPGRADED. Applied to
      // every aggregate as well as the lists, or the totals and the rows they
      // drill into would disagree.
      .where(sql<boolean>`${sql.raw(NOT_UPGRADED)}`)
      .groupBy([
        sql`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v')`,
        "profile.country",
        "account_type.name",
      ]);

    if (from) {
      q = q.where("members.created_at" as any, ">=", new Date(from));
    }
    if (to) {
      const toDate = new Date(to);
      if (!to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
      q = q.where("members.created_at" as any, "<=", toDate);
    }
    if (country && country.length > 0) {
      q = q.where(sql<boolean>`profile.country = ANY(${country})`);
    }

    const rows = (await q.execute()) as Array<{
      promo_code: string;
      country: string | null;
      area: string | null;
      signups: number | string;
      logged_in: number | string;
    }>;
    // Booking counts per promo code. `members`, `bookings`, `booking_entities`
    // and `booking_curated_events` all live in the member DB (coredb), and
    // bookings.member_id is a members.id in that same DB — so this must run on
    // memberDb. It previously ran on mainDb (the console's own DB), which has
    // neither table, so every count silently fell back to 0.
    const bookingCountsByCode = new Map<
      string,
      { internal: number; external: number }
    >();
    const mainDbStatsByCode = new Map<
      string,
      { signups: number; logged_in: number }
    >();
    if (searchCodes.length > 0) {
      try {
        let memberQ = this.memberDb
          .selectFrom("members")
          .select([
            "id",
            sql<string>`BTRIM(UPPER(signup_promo_code), E' \t\r\n\f\v')`.as("promo_code"),
            "first_access_attempt",
          ])
          .where(
            sql<boolean>`BTRIM(UPPER(signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes})`,
          )
          /*
           * Same exclusion, written without the `members.` prefix: this subquery
           * selects from `members` directly, so the qualified form would not resolve.
           */
          .where(
            sql<boolean>`(
              membership_type_id IS NULL
              OR membership_type_id IN (
                SELECT id FROM member_account_types
                WHERE name = ${GUEST_MEMBERSHIP_NAME}
              )
            )`,
          );

        if (from)
          memberQ = memberQ.where("created_at" as any, ">=", new Date(from));
        if (to) {
          const toDate = new Date(to);
          if (!to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
          memberQ = memberQ.where("created_at" as any, "<=", toDate);
        }

        const memberPromoRows = await memberQ.execute();
        const memberIds = memberPromoRows
          .map((m: any) => String(m.id))
          .filter(Boolean);
        const memberToCode = new Map<string, string>();
        for (const m of memberPromoRows) {
          if (m.id && m.promo_code)
            memberToCode.set(String(m.id), String(m.promo_code));
          if (m.promo_code) {
            const cur = mainDbStatsByCode.get(m.promo_code) ?? {
              signups: 0,
              logged_in: 0,
            };
            cur.signups += 1;
            if (m.first_access_attempt) cur.logged_in += 1;
            mainDbStatsByCode.set(m.promo_code, cur);
          }
        }

        if (memberIds.length > 0) {
          const bookingRows = await this.memberDb
            .selectFrom("bookings")
            .leftJoin(
              "booking_entities",
              "booking_entities.id",
              "bookings.booking_entity_id",
            )
            .select([
              sql<string>`bookings.member_id::text`.as("member_id"),
              sql<number>`COUNT(DISTINCT CASE WHEN ${sql.raw(INTERNAL_BOOKING_PREDICATE)} THEN bookings.id END)`.as(
                "internal",
              ),
              sql<number>`COUNT(DISTINCT CASE WHEN ${sql.raw(CURATED_BOOKING_PREDICATE)} THEN bookings.id END)`.as(
                "external",
              ),
            ])
            .where(sql`COALESCE(bookings._is_deleted, false)`, "=", false)
            .where(sql<boolean>`bookings.member_id::text = ANY(${memberIds})`)
            .groupBy(sql`bookings.member_id::text`)
            .execute();

          for (const b of bookingRows) {
            const code = memberToCode.get(String(b.member_id));
            if (code) {
              const cur = bookingCountsByCode.get(code) ?? {
                internal: 0,
                external: 0,
              };
              cur.internal += Number(b.internal || 0);
              cur.external += Number(b.external || 0);
              bookingCountsByCode.set(code, cur);
            }
          }
        }
      } catch {
        /* best-effort */
      }
    }

    // Only emit booking counts on the FIRST row per promo code.
    // Rows are grouped by (promo_code, country, area) so there are multiple
    // rows per code. Attaching the total to every row causes it to be summed
    // multiple times in the controller (e.g. 3 internal × 8 rows = 24).
    const seenCodes = new Set<string>();
    const result = rows.map((r) => {
      const code = String(r.promo_code).toUpperCase();
      const bCounts = bookingCountsByCode.get(code) ?? {
        internal: 0,
        external: 0,
      };
      const mStats = mainDbStatsByCode.get(code);
      const isFirst = !seenCodes.has(code);
      seenCodes.add(code);
      /*
       * The fallback fires only when the aggregate has no value at all — never when
       * that value is a real 0.
       *
       * This was `Number(r.logged_in) || mStats?.logged_in`, and `||` cannot tell 0
       * from missing. Rows are grouped by (promo code, country, area), so a group where
       * nobody has logged in legitimately counts 0 — and every one of those groups was
       * instead handed the code's *entire* logged-in total. Summed across groups that
       * reported 20,030 logged-in members against 13,628 real ones, which is how the
       * page came to show more logins than signups.
       *
       * Signups never tripped it: a group only exists because members are in it, so its
       * count is never 0. The bug was invisible on the one figure people sanity-check.
       */
      return {
        ...r,
        signups:
          fromAggregate(r.signups) ?? (isFirst ? mStats?.signups ?? 0 : 0),
        logged_in:
          fromAggregate(r.logged_in) ?? (isFirst ? mStats?.logged_in ?? 0 : 0),
        internal_bookings: isFirst ? bCounts.internal : 0,
        external_bookings: isFirst ? bCounts.external : 0,
      };
    });

    for (const [code, bCounts] of bookingCountsByCode.entries()) {
      if (
        !seenCodes.has(code) &&
        (bCounts.internal > 0 || bCounts.external > 0)
      ) {
        const mStats = mainDbStatsByCode.get(code);
        result.push({
          promo_code: code,
          country: null,
          area: null,
          signups: mStats?.signups || 0,
          logged_in: mStats?.logged_in || 0,
          internal_bookings: bCounts.internal,
          external_bookings: bCounts.external,
        });
      }
    }

    return result;
  }

  /**
   * Per-day signup counts for the given promo codes within an optional date
   * range. `day` is an ISO date string (YYYY-MM-DD) in UTC.
   */
  async aggregateSignupsByDay(opts: {
    promoCodes: string[];
    from?: string;
    to?: string;
  }): Promise<Array<{ day: string; signups: number }>> {
    const { promoCodes, from, to } = opts;
    if (promoCodes.length === 0) return [];

    const searchCodes = getSearchCodes(promoCodes);

    let q = this.memberDb
      .selectFrom("members")
      .select([
        sql<string>`TO_CHAR(members.created_at, 'YYYY-MM-DD')`.as("day"),
        sql<number>`COUNT(DISTINCT members.id)`.as("signups"),
      ])
      .where(
        sql<boolean>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes})`,
      )
      // Upgraded contracts are out of promo scope — see NOT_UPGRADED. Applied to
      // every aggregate as well as the lists, or the totals and the rows they
      // drill into would disagree.
      .where(sql<boolean>`${sql.raw(NOT_UPGRADED)}`)
      .groupBy(sql`TO_CHAR(members.created_at, 'YYYY-MM-DD')`)
      .orderBy(sql`TO_CHAR(members.created_at, 'YYYY-MM-DD')`, "asc");

    if (from) {
      q = q.where("members.created_at" as any, ">=", new Date(from));
    }
    if (to) {
      const toDate = new Date(to);
      if (!to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
      q = q.where("members.created_at" as any, "<=", toDate);
    }

    const rows = (await q.execute()) as Array<{
      day: string;
      signups: number | string;
    }>;
    return rows.map((r) => ({ day: r.day, signups: Number(r.signups) }));
  }

  /**
   * Aggregates for the promo-code bookings page.
   *
   * Server-side on purpose. The page needs charts over every booking, and doing
   * that from a fetched page would repeat the bug the promo-code detail page had:
   * charts describing 100 rows while the tiles describe thousands.
   *
   * Every cut reuses the same member filter, deleted check and internal/curated
   * predicates as `aggregateSignups`, so the tiles here equal the campaign
   * dashboard's Bookings Analytics figures.
   *
   * `byCountry` deliberately returns countries, not areas: the country -> area
   * mapping lives in the console (`regions.ts`) and duplicating it in SQL would
   * give two versions to keep in step.
   *
   * SELECT only: the Updot member DB is production data.
   */
  async aggregateBookings(opts: {
    promoCodes: string[];
    from?: string;
    to?: string;
    statuses?: string[];
    entities?: string[];
  }): Promise<{
    totals: {
      internal: number;
      curated: number;
      members: number;
      entities: number;
    };
    byBookedDay: Array<{ day: string; internal: number; curated: number }>;
    byStayMonth: Array<{ month: string; internal: number; curated: number }>;
    byCountry: Array<{ country: string | null; internal: number; curated: number }>;
    byEntity: Array<{
      name: string | null;
      type: string | null;
      internal: number;
      curated: number;
    }>;
    byStatus: Array<{ status: string | null; count: number }>;
    /** Every enum value, so the filter can offer statuses with no rows yet. */
    allStatuses: string[];
    byPromoCode: Array<{ promo_code: string; internal: number; curated: number }>;
  }> {
    const empty = {
      totals: { internal: 0, curated: 0, members: 0, entities: 0 },
      byBookedDay: [],
      byStayMonth: [],
      byCountry: [],
      byEntity: [],
      byStatus: [],
      allStatuses: [...ALL_BOOKING_STATUSES],
      byPromoCode: [],
    };
    void empty;
    const searchCodes = getSearchCodes(opts.promoCodes);
    /*
     * No codes means "every promo-code signup", matching listUsersByPromoCodes.
     * The controller substitutes a restricted user's allow-list before this, so
     * an empty list here only ever reaches an unrestricted viewer.
     */
    /*
     * The exclusion is ANDed onto both branches, not just the "all codes" one. A
     * per-code count that included upgraded members while the total excluded them
     * would not add up, and the discrepancy would appear only on some campaigns.
     */
    const codeClause =
      searchCodes.length > 0
        ? sql`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes}) AND ${sql.raw(NOT_UPGRADED)}`
        : sql`members.signup_promo_code IS NOT NULL AND members.signup_promo_code <> '' AND ${sql.raw(NOT_UPGRADED)}`;
    // Classification only — whether the booking is live is a separate filter
    // below, so a fully cancelled booking can still be selected by status.
    const INT = sql.raw(INTERNAL_ANY_STATUS);
    const CUR = sql.raw(CURATED_ANY_STATUS);
    const DEAD = sql.raw(DEAD_STATUS_LIST);

    // Window is on the member's signup date, matching every other figure on
    // these pages, so a booking is in scope when its member's signup is.
    const fromClause = opts.from
      ? sql`AND members.created_at >= ${new Date(opts.from)}`
      : sql``;
    let toDate: Date | null = null;
    if (opts.to) {
      toDate = new Date(opts.to);
      if (!opts.to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
    }
    const toClause = toDate ? sql`AND members.created_at <= ${toDate}` : sql``;

    /*
     * One CTE reused by every cut below.
     *
     * `stay_date` and `status` are read from whichever child table applies, so a
     * curated booking contributes its event dates rather than a null.
     */
    const DEAD_SET = new Set<string>(DEAD_BOOKING_STATUSES);

    /*
     * One set-based query, then aggregate in JS.
     *
     * This replaced seven parallel queries that each re-ran the same CTE, and
     * that CTE resolved stay date and status with six *correlated* subqueries per
     * booking. With no index on booking_units.booking_id, bookings.member_id or
     * booking_curated_events.booking_id, every one of those subqueries scanned
     * the whole child table — so the work was roughly 7 x rows x 6 full scans and
     * the endpoint hit the read timeout.
     *
     * Here each child table is reduced to one row per booking *once*, set-based,
     * and joined. The result is one row per booking (a few hundred), which is
     * cheap to fold into the seven cuts in JS.
     *
     * `ARRAY_AGG(... ORDER BY is_dead, ...)` reproduces the previous
     * live-row-preferred semantics: live rows sort first, so a booking reports a
     * cancelled status only when every child row is cancelled.
     */
    const rows = (await sql<{
      booking_id: string;
      member_id: string;
      promo_code: string;
      country: string | null;
      entity_name: string | null;
      entity_type: string | null;
      booked_at: string;
      stay_date: string | null;
      status: string | null;
      unit_count: number;
      event_count: number;
    }>`
      WITH scoped_members AS (
        SELECT members.id,
               BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') AS promo_code
        FROM members
        WHERE ${codeClause}
          ${fromClause}
          ${toClause}
      ),
      scoped_bookings AS (
        SELECT bookings.id, bookings.member_id, bookings.created_at,
               bookings.booking_entity_id
        FROM bookings
        JOIN scoped_members ON scoped_members.id = bookings.member_id
        WHERE COALESCE(bookings._is_deleted, false) = false
      ),
      unit AS (
        SELECT bu.booking_id,
               COUNT(*)::int AS n,
               (ARRAY_AGG(bu.status::text
                 ORDER BY (bu.status::text IN (${DEAD})), bu.check_in_date ASC NULLS LAST, bu.id ASC))[1] AS status,
               (ARRAY_AGG(bu.check_in_date::text
                 ORDER BY (bu.status::text IN (${DEAD})), bu.check_in_date ASC NULLS LAST, bu.id ASC))[1] AS check_in_date
        FROM booking_units bu
        JOIN scoped_bookings ON scoped_bookings.id = bu.booking_id
        GROUP BY bu.booking_id
      ),
      ev AS (
        SELECT bce.booking_id,
               COUNT(*)::int AS n,
               (ARRAY_AGG(bce.status::text
                 ORDER BY (bce.status::text IN (${DEAD})), bce.check_in_date ASC NULLS LAST, bce.id ASC))[1] AS status,
               (ARRAY_AGG(bce.check_in_date::text
                 ORDER BY (bce.status::text IN (${DEAD})), bce.check_in_date ASC NULLS LAST, bce.id ASC))[1] AS check_in_date
        FROM booking_curated_events bce
        JOIN scoped_bookings ON scoped_bookings.id = bce.booking_id
        GROUP BY bce.booking_id
      )
      SELECT
        scoped_bookings.id::text                       AS booking_id,
        scoped_bookings.member_id::text                AS member_id,
        scoped_members.promo_code                      AS promo_code,
        profile.country                                AS country,
        booking_entities.name                          AS entity_name,
        booking_entities.type                          AS entity_type,
        scoped_bookings.created_at::text               AS booked_at,
        COALESCE(unit.check_in_date, ev.check_in_date)  AS stay_date,
        COALESCE(unit.status, ev.status)                AS status,
        COALESCE(unit.n, 0)                            AS unit_count,
        COALESCE(ev.n, 0)                              AS event_count
      FROM scoped_bookings
      JOIN scoped_members ON scoped_members.id = scoped_bookings.member_id
      LEFT JOIN booking_entities ON booking_entities.id = scoped_bookings.booking_entity_id
      LEFT JOIN (
        SELECT DISTINCT ON (member_id) member_id, country
        FROM member_profiles ORDER BY member_id, id DESC
      ) profile ON profile.member_id = scoped_bookings.member_id
      LEFT JOIN unit ON unit.booking_id = scoped_bookings.id
      LEFT JOIN ev ON ev.booking_id = scoped_bookings.id
    `.execute(this.memberDb)).rows;

    const hasStatusFilter = Boolean(opts.statuses && opts.statuses.length > 0);
    const wantStatuses = new Set(opts.statuses ?? []);
    const wantEntities = new Set(opts.entities ?? []);

    const INTERNAL_TYPES = new Set(["RESORT", "INTERNAL_PROPERTY"]);

    // Same rules the SQL used to encode, now in one readable place.
    const kept = rows.filter((r) => {
      const isInternal =
        INTERNAL_TYPES.has(String(r.entity_type ?? "")) &&
        Number(r.unit_count) > 0;
      const isCurated = Number(r.event_count) > 0;
      if (!isInternal && !isCurated) return false;
      const status = r.status ?? "";
      // Liveness is the default; an explicit status selection replaces it.
      if (hasStatusFilter ? !wantStatuses.has(status) : DEAD_SET.has(status)) {
        return false;
      }
      if (wantEntities.size > 0 && !wantEntities.has(String(r.entity_name ?? ""))) {
        return false;
      }
      return true;
    });

    const classify = (r: (typeof rows)[number]) => ({
      internal:
        INTERNAL_TYPES.has(String(r.entity_type ?? "")) &&
        Number(r.unit_count) > 0,
      curated: Number(r.event_count) > 0,
    });

    /** Sums internal/curated into a keyed bucket, skipping null keys. */
    const bucket = (key: (r: (typeof rows)[number]) => string | null) => {
      const m = new Map<string, { internal: number; curated: number }>();
      for (const r of kept) {
        const k = key(r);
        if (k === null) continue;
        const c = classify(r);
        const cur = m.get(k) ?? { internal: 0, curated: 0 };
        if (c.internal) cur.internal += 1;
        if (c.curated) cur.curated += 1;
        m.set(k, cur);
      }
      return m;
    };

    let internal = 0;
    let curated = 0;
    const members = new Set<string>();
    const entities = new Set<string>();
    for (const r of kept) {
      const c = classify(r);
      if (c.internal) internal += 1;
      if (c.curated) curated += 1;
      members.add(r.member_id);
      if (r.entity_name) entities.add(r.entity_name);
    }

    const byDay = bucket((r) => (r.booked_at ?? "").slice(0, 10) || null);
    const byMonth = bucket((r) =>
      r.stay_date ? String(r.stay_date).slice(0, 7) : null,
    );
    const byCountryMap = bucket((r) => r.country ?? "");
    const byCode = bucket((r) => r.promo_code);

    const byEntityMap = new Map<
      string,
      { name: string | null; type: string | null; internal: number; curated: number }
    >();
    for (const r of kept) {
      const k = `${r.entity_name ?? ""}::${r.entity_type ?? ""}`;
      const c = classify(r);
      const cur =
        byEntityMap.get(k) ??
        { name: r.entity_name, type: r.entity_type, internal: 0, curated: 0 };
      if (c.internal) cur.internal += 1;
      if (c.curated) cur.curated += 1;
      byEntityMap.set(k, cur);
    }

    const statusCounts = new Map<string, number>();
    for (const r of kept) {
      const k = r.status ?? "";
      statusCounts.set(k, (statusCounts.get(k) ?? 0) + 1);
    }

    const sorted = (m: Map<string, { internal: number; curated: number }>) =>
      Array.from(m).sort((a, b) => b[1].internal - a[1].internal);

    return {
      totals: { internal, curated, members: members.size, entities: entities.size },
      byBookedDay: Array.from(byDay)
        .sort((a, b) => (a[0] < b[0] ? -1 : 1))
        .map(([day, v]) => ({ day, ...v })),
      byStayMonth: Array.from(byMonth)
        .sort((a, b) => (a[0] < b[0] ? -1 : 1))
        .map(([month, v]) => ({ month, ...v })),
      byCountry: sorted(byCountryMap).map(([country, v]) => ({
        country: country || null,
        ...v,
      })),
      byEntity: Array.from(byEntityMap.values()).sort(
        (a, b) => b.internal - a.internal || b.curated - a.curated,
      ),
      byStatus: Array.from(statusCounts)
        .sort((a, b) => b[1] - a[1])
        .map(([status, count]) => ({ status: status || null, count })),
      allStatuses: [...ALL_BOOKING_STATUSES],
      byPromoCode: sorted(byCode).map(([promo_code, v]) => ({ promo_code, ...v })),
    };
  }

  /**
   * The individual bookings behind the internal/curated counts.
   *
   * Exists so the Bookings Analytics donut can be drilled into: the counts alone
   * don't say *which* bookings they are. The member filter, the deleted check and
   * the internal/curated predicates are the same ones `aggregateSignups` counts
   * with, so `total` here always equals the matching donut slice — verified
   * against the member DB (59 internal / 0 curated at the time of writing).
   *
   * `kind` is required rather than returning both with a type column: a booking
   * at a Karma property that also has a curated-event row satisfies both
   * predicates and is counted in both totals, so a single combined list could not
   * reconcile with either slice.
   *
   * SELECT only: the Updot member DB is production data.
   */
  async listBookingsByPromoCodes(opts: {
    promoCodes: string[];
    kind: "internal" | "curated";
    from?: string;
    to?: string;
    /** Booking statuses to keep, e.g. UPCOMING. Empty means all. */
    statuses?: string[];
    /** Property / event names to keep. Empty means all. */
    entities?: string[];
    page: number;
    pageSize: number;
  }): Promise<{ items: any[]; total: number }> {
    const { promoCodes, kind, from, to, statuses, entities, page, pageSize } =
      opts;
    const searchCodes = getSearchCodes(promoCodes);
    // Empty means every promo-code signup — see aggregateBookings.
    const codeClause =
      searchCodes.length > 0
        ? sql<boolean>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes}) AND ${sql.raw(NOT_UPGRADED)}`
        : sql<boolean>`members.signup_promo_code IS NOT NULL AND members.signup_promo_code <> '' AND ${sql.raw(NOT_UPGRADED)}`;
    const hasStatusFilter = Boolean(statuses && statuses.length > 0);
    /*
     * Classification ignores status once a status filter is in play, so a fully
     * cancelled booking is still recognised as internal or curated and can be
     * listed. Without a filter the live-only predicate keeps this list equal to
     * the dashboard's totals.
     */
    const predicate = hasStatusFilter
      ? kind === "internal"
        ? INTERNAL_ANY_STATUS
        : CURATED_ANY_STATUS
      : kind === "internal"
        ? INTERNAL_BOOKING_PREDICATE
        : CURATED_BOOKING_PREDICATE;
    const offset = (page - 1) * pageSize;

    // Dates, status and reference live on the per-unit row for an internal stay
    // and on the curated-event row for a curated booking, so the detail columns
    // are read from whichever table matches `kind`. Ordered and limited to one
    // row: a booking can hold several units, and the earliest live one is the
    // stay people mean.
    /*
     * The child row the displayed detail comes from.
     *
     * Restricted to live rows by default. When statuses are being filtered the
     * restriction is dropped — otherwise a cancelled booking would match the
     * filter but show empty dates and no status, because the only row carrying
     * them had been excluded here.
     */
    const liveOnly = hasStatusFilter
      ? sql``
      : sql`AND child.status::text NOT IN (${sql.raw(DEAD_STATUS_LIST)})`;
    const detail =
      kind === "internal"
        ? sql`(
            SELECT child FROM booking_units child
            WHERE child.booking_id = bookings.id
              ${liveOnly}
            ORDER BY child.check_in_date ASC NULLS LAST, child.id ASC
            LIMIT 1
          )`
        : sql`(
            SELECT child FROM booking_curated_events child
            WHERE child.booking_id = bookings.id
              ${liveOnly}
            ORDER BY child.check_in_date ASC NULLS LAST, child.id ASC
            LIMIT 1
          )`;

    const base = () => {
      let q: any = this.memberDb
        .selectFrom("members")
        .innerJoin("bookings", "bookings.member_id", "members.id")
        .leftJoin(
          "booking_entities",
          "booking_entities.id",
          "bookings.booking_entity_id",
        )
        .where(codeClause)
        .where(sql`COALESCE(bookings._is_deleted, false)`, "=", false)
        .where(sql<boolean>`${sql.raw(predicate)}`);

      // Windowed on the member's signup date, not the booking date, so the list
      // matches the cohort the rest of the page is describing.
      if (from) {
        q = q.where("members.created_at" as any, ">=", new Date(from));
      }
      if (to) {
        const toDate = new Date(to);
        if (!to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
        q = q.where("members.created_at" as any, "<=", toDate);
      }
      // Entity name is a real column, so it filters directly. Status is a
      // computed value, so it reuses the same `detail` subquery the select does —
      // sharing the expression is what keeps the filter and the displayed status
      // from ever disagreeing.
      if (entities && entities.length > 0) {
        q = q.where(sql<boolean>`booking_entities.name = ANY(${entities})`);
      }
      if (statuses && statuses.length > 0) {
        q = q.where(sql<boolean>`(${detail}).status::text = ANY(${statuses})`);
      }
      return q;
    };

    const [rows, countRow] = await Promise.all([
      base()
        .leftJoin(
          sql`(
            SELECT DISTINCT ON (member_id)
              member_id, first_name, last_name, country
            FROM member_profiles
            ORDER BY member_id, id DESC
          )`.as("profile") as any,
          (join: any) => join.onRef("profile.member_id", "=", "members.id"),
        )
        .select([
          sql<string>`bookings.id::text`.as("booking_id"),
          sql<string>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v')`.as("promo_code"),
          sql<string | null>`members.membership_number`.as(
            "membership_number",
          ),
          sql<string | null>`NULLIF(TRIM(COALESCE(profile.first_name, '') || ' ' || COALESCE(profile.last_name, '')), '')`.as(
            "member_name",
          ),
          sql<string | null>`profile.country`.as("country"),
          sql<string | null>`booking_entities.name`.as("entity_name"),
          sql<string | null>`booking_entities.type`.as("entity_type"),
          sql<string | null>`(${detail}).status::text`.as("status"),
          sql<string | null>`(${detail}).check_in_date::text`.as(
            "check_in_date",
          ),
          sql<string | null>`(${detail}).check_out_date::text`.as(
            "check_out_date",
          ),
          sql<string | null>`(${detail}).viewpoint_booking_number`.as(
            "booking_ref",
          ),
          sql<string>`bookings.created_at::text`.as("booked_at"),
        ])
        // DISTINCT so a booking with several live units appears once, matching
        // the COUNT(DISTINCT bookings.id) the slice is built from.
        .distinctOn(sql`bookings.id`)
        .orderBy(sql`bookings.id`, "desc")
        .limit(pageSize)
        .offset(offset)
        .execute(),
      base()
        .select(sql<number>`COUNT(DISTINCT bookings.id)`.as("total"))
        .executeTakeFirst(),
    ]);

    return { items: rows as any[], total: Number(countRow?.total ?? 0) };
  }

  // Single-code wrapper over listUsersByPromoCodes.
  async listUsersByPromoCode(opts: {
    promoCode: string;
    page: number;
    pageSize: number;
    search?: string;
    from?: string;
    to?: string;
    // Restrict to members who have logged in (first_access_attempt IS TRUE).
    loggedIn?: boolean;
  }): Promise<{ items: any[]; total: number }> {
    return this.listUsersByPromoCodes({
      promoCodes: [opts.promoCode],
      page: opts.page,
      pageSize: opts.pageSize,
      search: opts.search,
      from: opts.from,
      to: opts.to,
      loggedIn: opts.loggedIn,
    });
  }

  // Paginated user list across one or more promo codes. Each row carries its
  // own signup_promo_code so a multi-code report can attribute members.
  private async executeUsersQuery(targetDb: Kysely<any>, opts: any) {
    const offset = (opts.page - 1) * opts.pageSize;
    const upperCodes = (opts.promoCodes as string[]).map((c) =>
      c.toUpperCase(),
    );
    const hasCodes = upperCodes.length > 0;
    const searchCodes = getSearchCodes(opts.promoCodes);

    let baseQuery: any = targetDb
      .selectFrom("members")
      .innerJoin("users as member_user", "member_user.id", "members.user_id")
      .leftJoin(
        "member_account_types as account_type",
        "account_type.id",
        "members.membership_type_id",
      )
      .leftJoin(
        "member_account_statuses as account_status",
        "account_status.id",
        "members.membership_status_id",
      )
      .leftJoin(
        sql`(
          SELECT DISTINCT ON (member_id)
            member_id, first_name, last_name, country, mobile, nationality, date_of_birth
          FROM member_profiles
          ORDER BY member_id, id DESC
        )`.as("profile") as any,
        (join: any) => join.onRef("profile.member_id", "=", "members.id"),
      );

    baseQuery = hasCodes
      ? baseQuery.where(
          sql<boolean>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes})`,
        )
      : baseQuery.where(
          sql<boolean>`members.signup_promo_code IS NOT NULL AND members.signup_promo_code <> ''`,
        );

    /*
     * Members whose contract has been upgraded are out of scope for promo reporting —
     * see NOT_UPGRADED. Applied as its own clause so it holds whether or not specific
     * codes were requested.
     *
     * Skipped when the caller opts in. That is what lets an account-type filter work at
     * all: the exclusion and an explicit account type are two ways of scoping the same
     * column, so applying both can only ever return nothing. Only the ad-hoc report and
     * its filter options ever opt in — every dashboard aggregate keeps the exclusion
     * unconditionally, so campaign figures cannot differ by who is looking.
     */
    if (!opts.includeUpgraded) {
      baseQuery = baseQuery.where(sql<boolean>`${sql.raw(NOT_UPGRADED)}`);
    }

    if (opts.search && opts.search.trim().length > 0) {
      const s = `%${opts.search.trim()}%`;
      baseQuery = baseQuery.where((eb: any) =>
        eb.or([
          eb("profile.first_name", "ilike", s),
          eb("profile.last_name", "ilike", s),
          eb("member_user.email", "ilike", s),
          eb("members.member_number", "ilike", s),
          eb("members.membership_number", "ilike", s),
          eb("profile.nationality", "ilike", s),
          eb("profile.mobile", "ilike", s),
        ]),
      );
    }

    if (opts.from) {
      baseQuery = baseQuery.where(
        "members.created_at" as any,
        ">=",
        new Date(opts.from),
      );
    }
    if (opts.to) {
      const toDate = new Date(opts.to);
      if (!opts.to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
      baseQuery = baseQuery.where("members.created_at" as any, "<=", toDate);
    }

    /*
     * Pending is "not TRUE", not "IS FALSE": the flag is nullable, and a member who
     * has never attempted access has NULL rather than false. IS FALSE would omit
     * them, and they are exactly who the pending bucket is about.
     */
    if (opts.loginStatus === "logged_in") {
      baseQuery = baseQuery.where(
        sql<boolean>`members.first_access_attempt IS TRUE`,
      );
    } else if (opts.loginStatus === "pending") {
      baseQuery = baseQuery.where(
        sql<boolean>`members.first_access_attempt IS NOT TRUE`,
      );
    }

    /*
     * The older boolean, kept for the callers that use it. Skipped when
     * `loginStatus` is present: the two would otherwise AND together, and
     * `loggedIn: true` with `loginStatus: "pending"` would quietly return an empty
     * list instead of honouring the more specific option.
     */
    if (opts.loginStatus === undefined && opts.loggedIn === true) {
      baseQuery = baseQuery.where(
        sql<boolean>`members.first_access_attempt IS TRUE`,
      );
    }

    /*
     * BTRIM mirrors how the aggregate decides a country is absent, so this bucket's
     * rows are exactly the ones the per-country roll-up leaves out — which is what
     * makes the count and the list agree.
     */
    if (opts.countryMissing) {
      baseQuery = baseQuery.where(
        sql<boolean>`COALESCE(BTRIM(profile.country), '') = ''`,
      );
    } else if (opts.country && opts.country.length > 0) {
      baseQuery = baseQuery.where(
        sql<boolean>`profile.country = ANY(${opts.country})`,
      );
    }

    if (opts.status === "active") {
      baseQuery = baseQuery.where(
        sql<boolean>`LOWER(account_status.name) = 'active'`,
      );
    } else if (opts.status === "inactive") {
      baseQuery = baseQuery.where(
        sql<boolean>`COALESCE(LOWER(account_status.name), '') <> 'active'`,
      );
    }

    if (opts.nationality && opts.nationality.length > 0) {
      baseQuery = baseQuery.where(
        sql<boolean>`profile.nationality = ANY(${opts.nationality})`,
      );
    }

    if (opts.accountType && opts.accountType.length > 0) {
      baseQuery = baseQuery.where(
        sql<boolean>`account_type.name = ANY(${opts.accountType})`,
      );
    }

    /*
     * Contact bucket. Uses the same predicates as the aggregate — BTRIM so a field
     * containing only spaces counts as absent, which is what makes the list total
     * match the chart slice.
     */
    if (opts.contact) {
      const hasPhone = sql<boolean>`COALESCE(BTRIM(profile.mobile), '') <> ''`;
      const hasEmail = sql<boolean>`COALESCE(BTRIM(member_user.email), '') <> ''`;
      if (opts.contact === "both") {
        baseQuery = baseQuery.where(sql<boolean>`${hasPhone} AND ${hasEmail}`);
      } else if (opts.contact === "phone") {
        baseQuery = baseQuery.where(sql<boolean>`${hasPhone} AND NOT ${hasEmail}`);
      } else if (opts.contact === "email") {
        baseQuery = baseQuery.where(sql<boolean>`NOT ${hasPhone} AND ${hasEmail}`);
      } else {
        baseQuery = baseQuery.where(
          sql<boolean>`NOT ${hasPhone} AND NOT ${hasEmail}`,
        );
      }
    }

    if (opts.dobFrom) {
      baseQuery = baseQuery.where(
        sql<boolean>`profile.date_of_birth >= ${opts.dobFrom}`,
      );
    }
    if (opts.dobTo) {
      baseQuery = baseQuery.where(
        sql<boolean>`profile.date_of_birth <= ${opts.dobTo}`,
      );
    }

    let itemsQuery = baseQuery.select([
      "members.id as member_id_raw",
      "members.member_number as member_number",
      "members.membership_number as membership_number",
      "members.signup_promo_code as signup_promo_code",
      "members.created_at as commence_date",
      sql<boolean>`members.first_access_attempt`.as("first_access_attempt"),
      sql<string>`profile.first_name`.as("first_name"),
      sql<string>`profile.last_name`.as("last_name"),
      sql<string>`profile.country`.as("country"),
      sql<string | null>`profile.mobile`.as("phone"),
      sql<string>`profile.nationality`.as("nationality"),
      sql<string | null>`profile.date_of_birth`.as("date_of_birth"),
      sql<string>`member_user.email`.as("email"),
      sql<string>`account_type.name`.as("account_type_name"),
      sql<string>`account_status.name`.as("account_status_name"),
    ]);

    if (hasCodes) {
      itemsQuery = itemsQuery.orderBy(
        sql`array_position(${upperCodes}::text[], BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v'))`,
        "asc",
      );
    }
    const items = (await itemsQuery
      .orderBy("members.created_at" as any, "desc")
      .orderBy(sql`LOWER(profile.first_name)`, "asc")
      .orderBy("members.id" as any, "asc")
      .limit(opts.pageSize)
      .offset(offset)
      .execute()) as any[];

    const totalRow = (await baseQuery
      .clearSelect()
      .select(sql<number>`COUNT(DISTINCT members.id)`.as("total"))
      .executeTakeFirst()) as { total: number | string } | undefined;

    return { items, total: Number(totalRow?.total ?? 0) };
  }

  // Paginated user list across one or more promo codes. Each row carries its
  // own signup_promo_code so a multi-code report can attribute members.
  async listUsersByPromoCodes(opts: {
    promoCodes: string[];
    page: number;
    pageSize: number;
    search?: string;
    from?: string;
    to?: string;
    loggedIn?: boolean;
    /**
     * Include members whose contract has been upgraded.
     *
     * Off by default so this list matches the promo scope every dashboard figure uses.
     * On only when the caller supplied an explicit account-type filter — the exclusion
     * and that filter scope the same column, so applying both returns nothing.
     */
    includeUpgraded?: boolean;
    country?: string[];
    status?: "active" | "inactive";
    nationality?: string[];
    accountType?: string[];
    dobFrom?: string;
    dobTo?: string;
    /**
     * Contact-completeness bucket, matching aggregateContactCompleteness.
     *
     * both | phone | email | none — the same has-phone / has-email predicates, so
     * the drill-down list and the chart counts agree.
     */
    contact?: "both" | "phone" | "email" | "none";
    /**
     * Login status, for the Logged In drill-down.
     *
     * `loggedIn: true` above can only ask for members who *have* logged in — there
     * was no way to list the pending ones, which is half of that breakdown.
     */
    loginStatus?: "logged_in" | "pending";
    /**
     * List only members with no country recorded — the "No Country Info" area.
     *
     * Not expressible through `country`: that bucket is defined by the *absence* of
     * a value, so there is no country name to match on. Takes precedence over
     * `country` when both arrive, since the two can only ever contradict.
     */
    countryMissing?: boolean;
  }): Promise<{ items: any[]; total: number }> {
    /*
     * Member DB only — no mainDb fallback.
     *
     * There used to be one, retried whenever this returned zero rows. But
     * `members`, `member_profiles` and `member_account_types` exist only in the
     * member DB (coredb); the console DB (karma_core) has none of them. So the
     * fallback could never succeed — it turned a legitimately empty result into
     * `relation "members" does not exist`, which 500'd the whole Reports page
     * whenever a filter combination matched nobody.
     *
     * An empty result is a valid answer and is now returned as one.
     */
    const { items, total } = await this.executeUsersQuery(this.memberDb, opts);

    if (items.length > 0) {
      const memberIds = items
        .map((u: any) => String(u.member_id_raw || ""))
        .filter(Boolean);
      if (memberIds.length > 0) {
        try {
          // Bookings live in the member DB alongside `members` — see the note in
          // aggregateSignups. Reading them off mainDb returned 0 for every row.
          const bookingRows = await this.memberDb
            .selectFrom("bookings")
            .leftJoin(
              "booking_entities",
              "booking_entities.id",
              "bookings.booking_entity_id",
            )
            .select([
              sql<string>`bookings.member_id::text`.as("member_id"),
              sql<number>`COUNT(DISTINCT CASE WHEN ${sql.raw(INTERNAL_BOOKING_PREDICATE)} THEN bookings.id END)`.as(
                "internal",
              ),
              sql<number>`COUNT(DISTINCT CASE WHEN ${sql.raw(CURATED_BOOKING_PREDICATE)} THEN bookings.id END)`.as(
                "external",
              ),
            ])
            .where(sql`COALESCE(bookings._is_deleted, false)`, "=", false)
            .where(sql<boolean>`bookings.member_id::text = ANY(${memberIds})`)
            .groupBy(sql`bookings.member_id::text`)
            .execute();

          const bookingMap = new Map<
            string,
            { internal: number; external: number }
          >();
          for (const b of bookingRows) {
            bookingMap.set(String(b.member_id), {
              internal: Number(b.internal || 0),
              external: Number(b.external || 0),
            });
          }

          for (const item of items) {
            const mId = String(item.member_id_raw || "");
            const counts = bookingMap.get(mId) ?? { internal: 0, external: 0 };
            item.internal_bookings_count = counts.internal;
            item.external_bookings_count = counts.external;
          }
        } catch {
          /* best-effort */
        }
      }
    }

    return { items, total };
  }

  private async executeFilterOptionsQuery(targetDb: Kysely<any>, opts: any) {
    const hasCodes = opts.promoCodes.length > 0;
    const searchCodes = getSearchCodes(opts.promoCodes);

    let baseQuery: any = targetDb
      .selectFrom("members")
      .leftJoin(
        "member_account_types as account_type",
        "account_type.id",
        "members.membership_type_id",
      )
      .leftJoin(
        sql`(
          SELECT DISTINCT ON (member_id)
            member_id, country, nationality
          FROM member_profiles
          ORDER BY member_id, id DESC
        )`.as("profile") as any,
        (join: any) => join.onRef("profile.member_id", "=", "members.id"),
      );

    baseQuery = hasCodes
      ? baseQuery.where(
          sql<boolean>`BTRIM(UPPER(members.signup_promo_code), E' \t\r\n\f\v') = ANY(${searchCodes})`,
        )
      : baseQuery.where(
          sql<boolean>`members.signup_promo_code IS NOT NULL AND members.signup_promo_code <> ''`,
        );

    /*
     * Members whose contract has been upgraded are out of scope for promo reporting —
     * see NOT_UPGRADED. Applied as its own clause so it holds whether or not specific
     * codes were requested.
     *
     * Skipped when the caller opts in. That is what lets an account-type filter work at
     * all: the exclusion and an explicit account type are two ways of scoping the same
     * column, so applying both can only ever return nothing. Only the ad-hoc report and
     * its filter options ever opt in — every dashboard aggregate keeps the exclusion
     * unconditionally, so campaign figures cannot differ by who is looking.
     */
    if (!opts.includeUpgraded) {
      baseQuery = baseQuery.where(sql<boolean>`${sql.raw(NOT_UPGRADED)}`);
    }

    if (opts.from) {
      baseQuery = baseQuery.where(
        "members.created_at" as any,
        ">=",
        new Date(opts.from),
      );
    }
    if (opts.to) {
      const toDate = new Date(opts.to);
      if (!opts.to.includes("T")) toDate.setUTCHours(23, 59, 59, 999);
      baseQuery = baseQuery.where("members.created_at" as any, "<=", toDate);
    }

    return (await baseQuery
      .select([
        sql<string | null>`profile.country`.as("country"),
        sql<string | null>`profile.nationality`.as("nationality"),
        sql<string | null>`account_type.name`.as("account_type_name"),
      ])
      .distinct()
      .execute()) as Array<{
      country: string | null;
      nationality: string | null;
      account_type_name: string | null;
    }>;
  }

  async getFilterOptions(opts: {
    promoCodes: string[];
    from?: string;
    to?: string;
    /**
     * Include upgraded contracts when building the option lists.
     *
     * On for a caller allowed to filter by account type: with the exclusion applied the
     * account-type list collapses to the single guest membership, since every promo
     * signup still in scope has that type by definition — leaving the picker with one
     * useless option.
     */
    includeUpgraded?: boolean;
  }): Promise<{
    countries: string[];
    nationalities: string[];
    accountTypes: string[];
  }> {
    // Member DB only; the mainDb retry is gone for the reason described in
    // listUsersByPromoCodes — it could only ever throw on an empty result.
    const rows = await this.executeFilterOptionsQuery(this.memberDb, opts);

    const countries = new Set<string>();
    const nationalities = new Set<string>();
    const accountTypes = new Set<string>();
    for (const r of rows) {
      const c = (r.country ?? "").trim();
      if (c) countries.add(c);
      const n = (r.nationality ?? "").trim();
      if (n) nationalities.add(n);
      const a = (r.account_type_name ?? "").trim();
      if (a) accountTypes.add(a);
    }

    return {
      countries: Array.from(countries).sort(),
      nationalities: Array.from(nationalities).sort(),
      accountTypes: Array.from(accountTypes).sort(),
    };
  }
}