import { sql, type Kysely } from "kysely";
import {
  VIEWPOINT_CONFLICT_KEY,
  VIEWPOINT_FIELDS,
  VIEWPOINT_TABLE,
  type FieldKind,
  type ViewpointReportType,
} from "./viewpoint-fields";

/**
 * Imported Viewpoint reports: storing them, querying them, and refreshing them.
 *
 * The `viewpoint_*` tables postdate the last kysely-codegen run, so this works
 * against an untyped `Kysely<any>` — the same approach the other newer
 * repositories here take. `db` is the console DB (`datastore`) and is written to;
 * `memberDb` is the Updot member DB and is only ever read, since it is production
 * data this codebase must not modify.
 */

/** Which of the first two numbers in a slash-separated date is the day. */
export type DateOrder = "dmy" | "mdy";

export interface DateOrderDetection {
  order: DateOrder;
  /**
   * True when the values themselves settled it — some value had a component above
   * 12, which only the day can be.
   */
  certain: boolean;
  /** How many values were inspected, for the caller to report. */
  sampled: number;
}

/**
 * Infers whether a column of dates is day-first or month-first.
 *
 * Decided by the one thing that is not ambiguous: a component above 12 can only be a
 * day. If the first component ever exceeds 12 the column is day-first; if the second
 * ever does, it is month-first.
 *
 * Three outcomes matter:
 *
 *   one side exceeds 12   — certain, and that is the order
 *   both sides exceed 12  — the column mixes conventions, which no single order can
 *                           read correctly. Reported as uncertain so the caller can
 *                           warn rather than quietly halving the damage.
 *   neither exceeds 12    — every value is genuinely ambiguous (a column of dates all
 *                           within the first twelve days of a month). Reported as
 *                           uncertain; the default is day-first only because it has to
 *                           be something.
 *
 * ISO values are ignored: they are already unambiguous and would otherwise dilute the
 * evidence.
 */
export function detectDateOrder(values: unknown[]): DateOrderDetection {
  let firstOver12 = 0;
  let secondOver12 = 0;
  let sampled = 0;

  for (const value of values) {
    const text = String(value ?? "").trim();
    if (text === "") continue;
    // Skip anything already ISO or with a spelt-out month.
    if (/^\d{4}-\d{2}-\d{2}/.test(text) || /[a-z]{3}/i.test(text)) continue;
    const m = /^(\d{1,2})[/\-.](\d{1,2})[/\-.](\d{4}|\d{2})/.exec(text);
    if (!m) continue;
    sampled += 1;
    if (Number(m[1]) > 12) firstOver12 += 1;
    if (Number(m[2]) > 12) secondOver12 += 1;
  }

  if (firstOver12 > 0 && secondOver12 === 0) {
    return { order: "dmy", certain: true, sampled };
  }
  if (secondOver12 > 0 && firstOver12 === 0) {
    return { order: "mdy", certain: true, sampled };
  }
  /*
   * Both, or neither. "Both" means the file is internally inconsistent and cannot be
   * read correctly either way; "neither" means there is no evidence at all. Either
   * way the caller must be told rather than handed a confident-looking answer.
   */
  return { order: "dmy", certain: false, sampled };
}

export interface CoercedRow {
  values: Record<string, unknown>;
  raw: Record<string, unknown>;
}

/**
 * Turns one spreadsheet value into something the column will accept.
 *
 * Returns `undefined` for anything unusable rather than throwing or substituting a
 * zero: a blank cell means "Viewpoint didn't tell us", and writing 0 or 1970-01-01
 * would turn a gap into a fact. The caller leaves those columns out of the row so
 * an upsert doesn't overwrite a good value with a blank one.
 */
export function coerce(
  kind: FieldKind,
  input: unknown,
  /**
   * Which of the first two numbers in a slash-separated date is the day.
   *
   * Required rather than assumed. A spreadsheet gives no locale, so "6/12/2026" is
   * genuinely either 6 December or 12 June, and picking one silently produces wrong
   * dates rather than an error. `detectDateOrder` infers it from the column as a
   * whole; the caller passes the result.
   */
  dateOrder: DateOrder = "dmy",
): unknown | undefined {
  if (input === null || input === undefined) return undefined;
  const text = String(input).trim();
  if (text === "") return undefined;

  if (kind === "text") return text;

  if (kind === "int" || kind === "decimal") {
    /*
     * Strips thousands separators, currency symbols and stray spaces, which report
     * exports carry routinely ("1,234", "£1,234.00"). A parenthesised figure is
     * accounting notation for a negative.
     */
    const negative = /^\(.*\)$/.test(text);
    const cleaned = text.replace(/[()]/g, "").replace(/[^0-9.\-]/g, "");
    if (cleaned === "" || cleaned === "-" || cleaned === ".") return undefined;
    const n = Number(cleaned);
    if (!Number.isFinite(n)) return undefined;
    const signed = negative ? -Math.abs(n) : n;
    return kind === "int" ? Math.trunc(signed) : signed;
  }

  // date | timestamp
  const parsed = parseSpreadsheetDate(text, dateOrder);
  if (!parsed) return undefined;
  return kind === "date" ? parsed.slice(0, 10) : parsed;
}

/**
 * Parses the date shapes a Viewpoint export actually produces.
 *
 * `new Date(string)` alone is not safe here. It reads an unqualified "01/02/2026"
 * as *January* 2nd, which silently mangles the day-first format these reports use —
 * a booking's arrival would land in the wrong month with no error anywhere. So
 * day-first strings are matched explicitly and assembled by hand, and only formats
 * that are already unambiguous (ISO, or a spelt-out month) are handed to `Date`.
 *
 * Returns an ISO string, or null when the value isn't a date at all.
 */
function parseSpreadsheetDate(
  text: string,
  dateOrder: DateOrder,
): string | null {
  // Already ISO-ish: 2026-08-13, optionally with a time.
  const iso = /^(\d{4})-(\d{2})-(\d{2})([T ].*)?$/.exec(text);
  if (iso) {
    const time = iso[4] ? iso[4].replace(" ", "T") : "T00:00:00";
    /*
     * `Z` is appended unless the value already carries a zone. Without it
     * `new Date` reads the text as *local* time and `toISOString` then shifts it,
     * which moved a date across midnight on any machine not running in UTC — the
     * console runs in IST, so a bare `2026-08-13T00:00` came back as 2026-08-12.
     * A spreadsheet has no timezone, so its wall clock is taken as UTC.
     */
    const zoned = /[Zz]|[+-]\d{2}:?\d{2}$/.test(time);
    const d = new Date(
      `${iso[1]}-${iso[2]}-${iso[3]}${time}${zoned ? "" : "Z"}`,
    );
    return Number.isNaN(d.getTime()) ? null : d.toISOString();
  }

  /*
   * Day-first with any of / - . as the separator, 2- or 4-digit year.
   *
   * The year alternation is `\d{4}` FIRST and deliberately so. Regex alternation is
   * ordered, not longest-match: with `(\d{2}|\d{4})` the year "2026" matched the
   * two-digit branch as "20", was read as 2020, and the trailing "26" was left
   * unconsumed — so every four-digit year silently became 2000 plus its first two
   * digits. Four digits must be tried before two.
   */
  const dmy = /^(\d{1,2})[/\-.](\d{1,2})[/\-.](\d{4}|\d{2})(?:[ ,]+(\d{1,2}):(\d{2})(?::(\d{2}))?)?/.exec(
    text,
  );
  if (dmy) {
    const first = Number(dmy[1]);
    const second = Number(dmy[2]);
    /*
     * Which is the day comes from the column, not from this row.
     *
     * A real Viewpoint export turned out to be month-first ("7/17/2026" = 17 July),
     * while this parser assumed day-first. The visible damage was 70 rejected rows;
     * the invisible damage was worse — every value where both numbers were 12 or
     * under, like "6/12/2026", parsed successfully as the wrong date. Deciding per
     * column and passing the answer in is what removes the guess.
     */
    const day = dateOrder === "mdy" ? second : first;
    const month = dateOrder === "mdy" ? first : second;
    if (month > 12 || day > 31 || month < 1 || day < 1) return null;
    const rawYear = Number(dmy[3]);
    const year = dmy[3].length === 2 ? 2000 + rawYear : rawYear;
    const hh = Number(dmy[4] ?? 0);
    const mm = Number(dmy[5] ?? 0);
    const ss = Number(dmy[6] ?? 0);
    const d = new Date(Date.UTC(year, month - 1, day, hh, mm, ss));
    // Rejects impossible dates like 31/02 — Date would roll them into March.
    if (
      d.getUTCFullYear() !== year ||
      d.getUTCMonth() !== month - 1 ||
      d.getUTCDate() !== day
    ) {
      return null;
    }
    return d.toISOString();
  }

  // A spelt-out month ("13 Aug 2026", "Aug 13, 2026") is unambiguous.
  if (/[a-z]{3}/i.test(text)) {
    const d = new Date(text);
    if (Number.isNaN(d.getTime())) return null;
    /*
     * Re-assembled from the local parts into UTC rather than returned directly.
     * `new Date("13 Aug 2026")` is midnight *local*, and `toISOString` would move it
     * to the previous day anywhere east of UTC. Reading the calendar fields back and
     * declaring them UTC keeps the date the sheet actually said, and matches how the
     * day-first branch above treats its values.
     */
    return new Date(
      Date.UTC(
        d.getFullYear(),
        d.getMonth(),
        d.getDate(),
        d.getHours(),
        d.getMinutes(),
        d.getSeconds(),
      ),
    ).toISOString();
  }

  return null;
}

export class ViewpointImportRepository {
  private db: Kysely<any>;
  private memberDb: Kysely<any> | null;

  constructor(db: unknown, memberDb?: unknown) {
    this.db = db as Kysely<any>;
    this.memberDb = (memberDb as Kysely<any>) ?? null;
  }

  /**
   * Coerces raw sheet rows into column values using the operator's mapping.
   *
   * Pure and side-effect free so the console can preview exactly what would be
   * written before anything is. Rows missing a required field are reported rather
   * than written: without its natural key a row could never be updated by a later
   * import or sync, so it would duplicate on every upload.
   */
  prepareRows(
    reportType: ViewpointReportType,
    /** Sheet header -> canonical field key. Unmapped headers are simply absent. */
    columnMap: Record<string, string>,
    rows: Array<Record<string, unknown>>,
    /**
     * Forces the day/month order instead of inferring it.
     *
     * For the case detection cannot settle — a column whose every value has both
     * components under 13 — where only the operator knows the answer.
     */
    dateOrderOverride?: DateOrder,
  ): {
    prepared: CoercedRow[];
    skipped: Array<{ row: number; reason: string }>;
    /** What was inferred per date column, so the caller can report or warn. */
    dateOrders: Record<string, DateOrderDetection>;
  } {
    const fields = VIEWPOINT_FIELDS[reportType];
    const byKey = new Map(fields.map((f) => [f.key, f]));
    const required = fields.filter((f) => f.required).map((f) => f.key);

    /*
     * Date order is decided per column, before any row is coerced.
     *
     * It cannot be decided per value: "6/12/2026" is readable either way, and the
     * only evidence is elsewhere in the same column — a sibling value like
     * "7/17/2026" proves the second component is the day. So the whole column is
     * inspected first, then every value in it is read with that one answer.
     */
    const dateOrders: Record<string, DateOrderDetection> = {};
    for (const [header, fieldKey] of Object.entries(columnMap)) {
      const field = byKey.get(fieldKey);
      if (!field || (field.kind !== "date" && field.kind !== "timestamp")) {
        continue;
      }
      const detected = detectDateOrder(rows.map((r) => r[header]));
      dateOrders[field.key] = dateOrderOverride
        ? { ...detected, order: dateOrderOverride, certain: true }
        : detected;
    }

    const prepared: CoercedRow[] = [];
    const skipped: Array<{ row: number; reason: string }> = [];

    rows.forEach((row, i) => {
      const values: Record<string, unknown> = {};
      for (const [header, fieldKey] of Object.entries(columnMap)) {
        const field = byKey.get(fieldKey);
        if (!field) continue;
        const value = coerce(
          field.kind,
          row[header],
          dateOrders[field.key]?.order ?? dateOrderOverride ?? "dmy",
        );
        if (value !== undefined) values[field.key] = value;
      }

      const missing = required.filter((k) => values[k] === undefined);
      if (missing.length > 0) {
        // 1-indexed and offset by the header row, so the number matches what the
        // operator sees in Excel rather than a zero-based array position.
        skipped.push({
          row: i + 2,
          reason: `missing ${missing.join(", ")}`,
        });
        return;
      }

      // The whole original row travels with it, including columns nobody mapped,
      // so a mapping can be corrected later without asking for the file again.
      prepared.push({ values, raw: row });
    });

    return { prepared, skipped, dateOrders };
  }

  /** Records the upload itself. Written before the rows so a failure mid-insert still leaves a trace. */
  async createImport(input: {
    reportType: ViewpointReportType;
    filename: string;
    sheetName: string | null;
    uploadedBy: string | null;
    uploadedById: string | null;
    sheetColumns: string[];
    columnMap: Record<string, string>;
    rowCount: number;
  }): Promise<{ id: string }> {
    const row = await this.db
      .insertInto("viewpoint_imports")
      .values({
        report_type: input.reportType,
        filename: input.filename,
        sheet_name: input.sheetName,
        uploaded_by: input.uploadedBy,
        uploaded_by_id: input.uploadedById,
        sheet_columns: JSON.stringify(input.sheetColumns),
        column_map: JSON.stringify(input.columnMap),
        row_count: input.rowCount,
      })
      .returning(["id"])
      .executeTakeFirstOrThrow();
    return { id: String(row.id) };
  }

  async finishImport(
    id: string,
    counts: { inserted: number; updated: number; skipped: number },
  ): Promise<void> {
    await this.db
      .updateTable("viewpoint_imports")
      .set({
        inserted_count: counts.inserted,
        updated_count: counts.updated,
        skipped_count: counts.skipped,
      })
      .where("id", "=", id)
      .execute();
  }

  /**
   * Writes prepared rows, updating any that already exist.
   *
   * ON CONFLICT on the natural key is what makes a daily import an update rather
   * than a duplicate. Two details matter:
   *
   * - Only the columns present in a row are overwritten. A field the operator did
   *   not map this time keeps whatever it already had, instead of being nulled out
   *   by an import that simply didn't carry it.
   * - `xmax = 0` distinguishes an insert from an update in the same statement,
   *   which is how the inserted/updated counts are reported honestly. Postgres has
   *   no other way to tell from RETURNING alone.
   *
   * Chunked because a single statement with thousands of rows and dozens of
   * parameters each will exceed Postgres' 65535-parameter limit.
   */
  async upsertRows(
    reportType: ViewpointReportType,
    importId: string,
    prepared: CoercedRow[],
  ): Promise<{ inserted: number; updated: number }> {
    const table = VIEWPOINT_TABLE[reportType];
    const conflictKey = VIEWPOINT_CONFLICT_KEY[reportType];
    const CHUNK = 250;

    let inserted = 0;
    let updated = 0;

    for (let start = 0; start < prepared.length; start += CHUNK) {
      const chunk = prepared.slice(start, start + CHUNK);
      /*
       * One column set per chunk, covering every column any row in it touches.
       * Rows that lack a column send undefined, which Kysely writes as NULL — and
       * the conflict clause below then skips it, so a NULL from a short row can't
       * clobber a stored value.
       */
      const columns = Array.from(
        new Set(chunk.flatMap((r) => Object.keys(r.values))),
      );

      const rows = chunk.map((r) => {
        const record: Record<string, unknown> = {
          import_id: importId,
          raw: JSON.stringify(r.raw),
          source_of_truth: "import",
          updated_at: new Date(),
        };
        for (const col of columns) record[col] = r.values[col] ?? null;
        return record;
      });

      const result = await this.db
        .insertInto(table)
        .values(rows)
        .onConflict((oc: any) =>
          oc.column(conflictKey).doUpdateSet((eb: any) => {
            const set: Record<string, unknown> = {
              import_id: eb.ref("excluded.import_id"),
              raw: eb.ref("excluded.raw"),
              source_of_truth: sql`'import'`,
              updated_at: eb.ref("excluded.updated_at"),
            };
            for (const col of columns) {
              if (col === conflictKey) continue;
              // COALESCE, not a plain assignment: a column the incoming row did
              // not carry arrives as NULL and must leave the stored value alone.
              set[col] = sql`COALESCE(${eb.ref(`excluded.${col}`)}, ${eb.ref(
                `${table}.${col}`,
              )})`;
            }
            return set;
          }),
        )
        .returning(sql<boolean>`xmax = 0`.as("was_insert"))
        .execute();

      for (const r of result as Array<{ was_insert: boolean }>) {
        if (r.was_insert) inserted += 1;
        else updated += 1;
      }
    }

    return { inserted, updated };
  }

  /** Import history, newest first. */
  async listImports(reportType?: ViewpointReportType, limit = 25) {
    let q: any = this.db
      .selectFrom("viewpoint_imports")
      .selectAll()
      .orderBy("created_at", "desc")
      .limit(limit);
    if (reportType) q = q.where("report_type", "=", reportType);
    return (await q.execute()) as any[];
  }

  /**
   * Bookings by source and by status, plus the headline counts.
   *
   * One grouped query rather than several: the page shows both roll-ups and the
   * totals together, and a set of separate counts would drift from each other as
   * rows change between them.
   */
  async bookingAggregates(filters: BookingFilters): Promise<{
    /** Subito vs Viewpoint-direct — the booking's source in business terms. */
    byOrigin: Array<{ origin: BookingOrigin; bookings: number }>;
    /** The report's own source column, which is not the same thing. */
    bySource: Array<{ source: string; bookings: number }>;
    byStatus: Array<{ status: string; bookings: number }>;
    total: number;
    unmatched: number;
    lastSyncedAt: string | null;
  }> {
    const rows = (await this.applyBookingFilters(
      this.db
        .selectFrom("viewpoint_bookings")
        .select([
          sql<string>`COALESCE(NULLIF(BTRIM(booking_source), ''), 'Unknown')`.as(
            "source",
          ),
          sql<string>`COALESCE(NULLIF(BTRIM(status), ''), 'Unknown')`.as(
            "status",
          ),
          /*
           * Grouped in SQL rather than derived in JS afterwards, so the origin
           * roll-up respects the same filters as every other figure in this query —
           * a filtered view must not report unfiltered origin counts.
           */
          sql<string>`CASE
            WHEN core_booking_id IS NOT NULL THEN 'subito'
            WHEN core_matched_at IS NOT NULL THEN 'viewpoint'
            ELSE 'unchecked'
          END`.as("origin"),
          sql<number>`COUNT(*)::int`.as("bookings"),
          sql<number>`COUNT(*) FILTER (WHERE COALESCE(BTRIM(membership_number), '') = '')::int`.as(
            "no_membership",
          ),
          sql<string | null>`MAX(last_synced_at)`.as("last_synced_at"),
        ])
        .groupBy(["source", "status", "origin"]),
      filters,
    ).execute()) as Array<{
      source: string;
      status: string;
      origin: string;
      bookings: number;
      no_membership: number;
      last_synced_at: string | null;
    }>;

    const bySource = new Map<string, number>();
    const byStatus = new Map<string, number>();
    const byOrigin = new Map<string, number>();
    let total = 0;
    let unmatched = 0;
    let lastSyncedAt: string | null = null;

    for (const r of rows) {
      bySource.set(r.source, (bySource.get(r.source) ?? 0) + r.bookings);
      byStatus.set(r.status, (byStatus.get(r.status) ?? 0) + r.bookings);
      byOrigin.set(r.origin, (byOrigin.get(r.origin) ?? 0) + r.bookings);
      total += r.bookings;
      unmatched += r.no_membership;
      if (r.last_synced_at && (!lastSyncedAt || r.last_synced_at > lastSyncedAt)) {
        lastSyncedAt = r.last_synced_at;
      }
    }

    const sorted = (m: Map<string, number>) =>
      Array.from(m, ([k, v]) => [k, v] as const).sort((a, b) => b[1] - a[1]);

    return {
      byOrigin: sorted(byOrigin).map(([origin, bookings]) => ({
        origin: origin as BookingOrigin,
        bookings,
      })),
      bySource: sorted(bySource).map(([source, bookings]) => ({
        source,
        bookings,
      })),
      byStatus: sorted(byStatus).map(([status, bookings]) => ({
        status,
        bookings,
      })),
      total,
      unmatched,
      lastSyncedAt,
    };
  }

  /**
   * The timing, geography and destination roll-ups behind the chart group.
   *
   * Separate grouped queries rather than extra columns on `bookingAggregates`. That one
   * groups by (source, status, origin) — adding a date, a country and a resort to the
   * same GROUP BY would multiply the result set by every distinct combination and ship
   * thousands of rows to compute four small charts.
   *
   * Run in parallel and each capped, since a chart can only show so many bars anyway:
   * a 300-resort list is a table, not a bar chart.
   */
  async bookingDimensions(filters: BookingFilters): Promise<{
    byArrival: Array<{ day: string; bookings: number }>;
    byConfirmed: Array<{ day: string; bookings: number }>;
    byCountry: Array<{ country: string; bookings: number }>;
    byResort: Array<{ resort: string; bookings: number }>;
  }> {
    /** Bookings per day for a date column, chronological. */
    const byDay = async (column: "arrival" | "confirmed_date") => {
      const rows = (await this.applyBookingFilters(
        this.db
          .selectFrom("viewpoint_bookings")
          .select([
            sql<string>`to_char(${sql.ref(column)}, 'YYYY-MM-DD')`.as("day"),
            sql<number>`COUNT(*)::int`.as("bookings"),
          ])
          .where(sql<boolean>`${sql.ref(column)} IS NOT NULL`)
          .groupBy(sql`1`)
          // Ascending: this is a time axis, and ordering by volume would destroy it.
          .orderBy(sql`1`, "asc"),
        filters,
      ).execute()) as Array<{ day: string; bookings: number }>;
      return rows.map((r) => ({ day: r.day, bookings: Number(r.bookings) }));
    };

    /** Top-N of a text column by volume, blanks folded into one bucket. */
    const byText = async (
      column: "guest_country" | "resort_name",
      limit: number,
    ) => {
      const rows = (await this.applyBookingFilters(
        this.db
          .selectFrom("viewpoint_bookings")
          .select([
            sql<string>`COALESCE(NULLIF(BTRIM(${sql.ref(column)}), ''), 'Unknown')`.as(
              "label",
            ),
            sql<number>`COUNT(*)::int`.as("bookings"),
          ])
          .groupBy(sql`1`)
          .orderBy(sql`2`, "desc")
          .limit(limit),
        filters,
      ).execute()) as Array<{ label: string; bookings: number }>;
      return rows.map((r) => ({
        label: r.label,
        bookings: Number(r.bookings),
      }));
    };

    const [byArrival, byConfirmed, countries, resorts] = await Promise.all([
      byDay("arrival"),
      byDay("confirmed_date"),
      // Every country: the console folds them into areas, so truncating here would
      // silently shrink an area's total.
      byText("guest_country", 300),
      byText("resort_name", 12),
    ]);

    return {
      byArrival,
      byConfirmed,
      byCountry: countries.map((r) => ({ country: r.label, bookings: r.bookings })),
      byResort: resorts.map((r) => ({ resort: r.label, bookings: r.bookings })),
    };
  }

  /**
   * Viewpoint bookings attributed to promo codes, for the campaigns dashboard.
   *
   * The chain is `viewpoint_bookings.membership_number` -> `members.membership_number`
   * -> `members.signup_promo_code`. Measured on the live import: 145 of 155 membership
   * numbers resolve, and 138 of those resolve to exactly one promo code with none
   * resolving to more than one — so the attribution is unambiguous in practice, even
   * though a membership can cover several members.
   *
   * ONLY VIEWPOINT-DIRECT BOOKINGS ARE COUNTED
   *
   * A booking that also exists in core is already counted by the dashboard's own
   * booking figures, so including it here would double-count it. `core_booking_id IS
   * NULL` is what makes this additive rather than overlapping — these are the bookings
   * console2 could not previously see at all.
   *
   * Rows never reconciled are excluded too: whether core has them is unknown, and
   * counting them as new would be asserting they are not duplicates.
   */
  async viewpointDirectByPromoCode(): Promise<
    Array<{ promoCode: string; bookings: number }>
  > {
    if (!this.memberDb) return [];

    const rows = (await this.db
      .selectFrom("viewpoint_bookings")
      .select([
        sql<string>`UPPER(BTRIM(membership_number, ${WS}))`.as("membership"),
        sql<number>`COUNT(*)::int`.as("bookings"),
      ])
      .where(sql<boolean>`COALESCE(BTRIM(membership_number, ${WS}), '') <> ''`)
      // Viewpoint-direct only, and only where that has actually been established.
      .where("core_booking_id", "is", null)
      .where("core_matched_at", "is not", null)
      .groupBy(sql`1`)
      .execute()) as Array<{ membership: string; bookings: number }>;

    if (rows.length === 0) return [];

    const memberships = rows.map((r) => r.membership);
    const CHUNK = 500;
    /** Membership number -> the promo code its members signed up with. */
    const codeByMembership = new Map<string, string>();

    for (let start = 0; start < memberships.length; start += CHUNK) {
      const chunk = memberships.slice(start, start + CHUNK);
      const found = (await sql<{ membership: string; code: string }>`
        SELECT UPPER(BTRIM(membership_number, ${WS})) AS membership,
               UPPER(BTRIM(signup_promo_code, ${WS})) AS code
        FROM members
        WHERE UPPER(BTRIM(membership_number, ${WS})) = ANY(${chunk})
          AND COALESCE(BTRIM(signup_promo_code, ${WS}), '') <> ''
        GROUP BY 1, 2
      `.execute(this.memberDb)) as unknown as {
        rows: Array<{ membership: string; code: string }>;
      };

      for (const r of found.rows) {
        const existing = codeByMembership.get(r.membership);
        /*
         * A membership resolving to two different codes is dropped rather than
         * attributed to whichever came back first. It does not occur in the current
         * data — 0 of 145 — but guessing between two campaigns would put bookings on
         * the wrong one silently, and an unattributed booking is the honest outcome.
         */
        if (existing && existing !== r.code) {
          codeByMembership.set(r.membership, "");
          continue;
        }
        if (!existing) codeByMembership.set(r.membership, r.code);
      }
    }

    const byCode = new Map<string, number>();
    for (const r of rows) {
      const code = codeByMembership.get(r.membership);
      if (!code) continue;
      byCode.set(code, (byCode.get(code) ?? 0) + r.bookings);
    }

    return Array.from(byCode, ([promoCode, bookings]) => ({
      promoCode,
      bookings,
    })).sort((a, b) => b.bookings - a.bookings);
  }

  /** Distinct sources and statuses present, for the filter controls. */
  async bookingFilterOptions(): Promise<{
    sources: string[];
    statuses: string[];
  }> {
    const rows = (await this.db
      .selectFrom("viewpoint_bookings")
      .select([
        sql<string | null>`NULLIF(BTRIM(booking_source), '')`.as("source"),
        sql<string | null>`NULLIF(BTRIM(status), '')`.as("status"),
      ])
      .distinct()
      .execute()) as Array<{ source: string | null; status: string | null }>;

    return {
      sources: Array.from(
        new Set(rows.map((r) => r.source).filter(Boolean) as string[]),
      ).sort(),
      statuses: Array.from(
        new Set(rows.map((r) => r.status).filter(Boolean) as string[]),
      ).sort(),
    };
  }

  /**
   * A page of bookings, enriched from the member DB.
   *
   * The join is done in two steps — page the console DB, then look those
   * membership numbers up in the member DB — because the two live on separate
   * servers and cannot be joined in one statement. Doing it this way also bounds
   * the member query to at most one page of numbers.
   */
  async listBookings(filters: BookingFilters & { page: number; pageSize: number }) {
    const offset = (filters.page - 1) * filters.pageSize;

    const items = (await this.applyBookingFilters(
      this.db.selectFrom("viewpoint_bookings").selectAll(),
      filters,
    )
      // Newest arrivals first, with a stable tiebreak so paging can't repeat or
      // skip a row when several share an arrival date.
      .orderBy("arrival", "desc")
      .orderBy("booking_no", "asc")
      .limit(filters.pageSize)
      .offset(offset)
      .execute()) as any[];

    const totalRow = (await this.applyBookingFilters(
      this.db
        .selectFrom("viewpoint_bookings")
        .select(sql<number>`COUNT(*)::int`.as("total")),
      filters,
    ).executeTakeFirst()) as { total: number } | undefined;

    const matches = await this.resolveMembers(
      items.map((r) => String(r.membership_number ?? "")).filter(Boolean),
    );

    return {
      items: items.map((r) => decorateWithMember(r, matches)),
      total: Number(totalRow?.total ?? 0),
    };
  }

  private applyBookingFilters(query: any, filters: BookingFilters) {
    let q = query;
    if (filters.sources?.length) {
      q = q.where(
        sql<boolean>`COALESCE(NULLIF(BTRIM(booking_source), ''), 'Unknown') = ANY(${filters.sources})`,
      );
    }
    if (filters.statuses?.length) {
      q = q.where(
        sql<boolean>`COALESCE(NULLIF(BTRIM(status), ''), 'Unknown') = ANY(${filters.statuses})`,
      );
    }
    if (filters.from) q = q.where("arrival", ">=", filters.from);
    if (filters.to) q = q.where("arrival", "<=", filters.to);
    if (filters.membershipNumbers?.length) {
      q = q.where(
        sql<boolean>`UPPER(BTRIM(membership_number)) = ANY(${filters.membershipNumbers.map(
          (m) => m.trim().toUpperCase(),
        )})`,
      );
    }
    /*
     * Origin is derived, so it filters on the reconciliation columns rather than on a
     * stored label. Built as an OR of the selected origins so picking two behaves like
     * every other multi-select here.
     */
    if (filters.origins?.length) {
      const wanted = new Set(filters.origins);
      const clauses: string[] = [];
      if (wanted.has("subito")) clauses.push("core_booking_id IS NOT NULL");
      if (wanted.has("viewpoint")) {
        clauses.push("(core_booking_id IS NULL AND core_matched_at IS NOT NULL)");
      }
      if (wanted.has("unchecked")) clauses.push("core_matched_at IS NULL");
      // Selecting nothing recognisable must match nothing, not everything.
      q = q.where(sql<boolean>`${sql.raw(clauses.join(" OR ") || "FALSE")}`);
    }

    if (filters.onlyUnmatched) {
      q = q.where(
        sql<boolean>`COALESCE(BTRIM(membership_number), '') = ''`,
      );
    }
    if (filters.search?.trim()) {
      const s = `%${filters.search.trim()}%`;
      q = q.where((eb: any) =>
        eb.or([
          eb("booking_no", "ilike", s),
          eb("guest_name", "ilike", s),
          eb("membership_number", "ilike", s),
          eb("resort_name", "ilike", s),
        ]),
      );
    }
    return q;
  }

  /**
   * Resolves report numbers against the member DB.
   *
   * WHY THIS IS NOT A SIMPLE LOOKUP
   *
   * The two candidate columns mean different things, measured against the live
   * member DB (77,368 members):
   *
   *   `member_number`     — unique, 0 collisions. Identifies one person.
   *   `membership_number` — one-to-many. 36,815 numbers cover a single member, but
   *                         9,392 cover two, 2,579 cover three, and 371 cover six
   *                         or more. It identifies a *membership*, and everyone on
   *                         that membership shares it.
   *
   * They are also not interchangeable: only one row in the whole table has the same
   * value in both, and 3,489 values are one member's `membership_number` while
   * simultaneously being a different member's `member_number`.
   *
   * So the two are looked up separately and `member_number` wins, because it is the
   * only one that can name a person. A `membership_number` hit that covers several
   * members is reported as `membership` with all of them attached and no single
   * member chosen — picking the first would attach a booking to whichever household
   * member the query happened to return, silently and wrongly.
   */
  async resolveMembers(numbers: string[]): Promise<Map<string, MemberMatch>> {
    const out = new Map<string, MemberMatch>();
    if (!this.memberDb) return out;

    const keys = Array.from(
      new Set(numbers.map(normaliseMembershipNumber).filter(Boolean) as string[]),
    );
    if (keys.length === 0) return out;

    /*
     * The joined builder is captured as `any` before the select list is added.
     * Annotating the whole chain is not enough — the initializer is still checked
     * first, and the cast `sql` fragment in the join leaves the row type unusable.
     * Same reason promo-code-signups.repo.ts works untyped against this database.
     */
    const joined: any = this.memberDb
      .selectFrom("members")
      .innerJoin("users as member_user", "member_user.id", "members.user_id")
      .leftJoin(
        sql`(
          SELECT DISTINCT ON (member_id) member_id, first_name, last_name, country, mobile
          FROM member_profiles
          ORDER BY member_id, id DESC
        )`.as("profile") as any,
        (join: any) => join.onRef("profile.member_id", "=", "members.id"),
      );

    const rows = await joined
      .select([
        "members.id as id",
        "members.membership_number as membership_number",
        "members.member_number as member_number",
        "members.signup_promo_code as signup_promo_code",
        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>`member_user.email`.as("email"),
      ])
      .where(
        sql<boolean>`UPPER(BTRIM(members.membership_number)) = ANY(${keys})
          OR UPPER(BTRIM(members.member_number)) = ANY(${keys})`,
      )
      .execute();

    const wanted = new Set(keys);
    // Collected separately so a membership hit can never mask an individual one.
    const byMemberNumber = new Map<string, MemberSummary>();
    const byMembershipNumber = new Map<string, MemberSummary[]>();

    for (const row of rows as any[]) {
      const summary: MemberSummary = {
        id: String(row.id),
        membershipNumber: row.membership_number ?? null,
        memberNumber: row.member_number ?? null,
        firstName: row.first_name ?? null,
        lastName: row.last_name ?? null,
        email: row.email ?? null,
        phone: row.phone ?? null,
        country: row.country ?? null,
        signupPromoCode: row.signup_promo_code ?? null,
      };

      const individual = normaliseMembershipNumber(row.member_number);
      if (individual && wanted.has(individual)) {
        byMemberNumber.set(individual, summary);
      }

      const membership = normaliseMembershipNumber(row.membership_number);
      if (membership && wanted.has(membership)) {
        const list = byMembershipNumber.get(membership);
        if (list) list.push(summary);
        else byMembershipNumber.set(membership, [summary]);
      }
    }

    for (const key of keys) {
      const individual = byMemberNumber.get(key);
      if (individual) {
        out.set(key, {
          matchedOn: "member_number",
          member: individual,
          members: [individual],
        });
        continue;
      }
      const household = byMembershipNumber.get(key);
      if (!household || household.length === 0) continue;
      out.set(key, {
        matchedOn: "membership_number",
        // Only named when the membership holds exactly one member; otherwise the
        // number does not identify a person and the caller must say so.
        member: household.length === 1 ? household[0] : null,
        members: household,
      });
    }

    return out;
  }

  /**
   * How many stored rows a given import last wrote.
   *
   * Read before a delete so the confirmation can state a real number rather than
   * "some rows". Note "last wrote", not "created": `import_id` is overwritten by
   * whichever import most recently touched a row, so a re-import moves rows onto
   * itself and away from the batch that first inserted them. That is the honest
   * meaning of the count and the delete alike.
   */
  async countRowsForImport(
    reportType: ViewpointReportType,
    importId: string,
  ): Promise<number> {
    const row = (await this.db
      .selectFrom(VIEWPOINT_TABLE[reportType])
      .select(sql<number>`COUNT(*)::int`.as("total"))
      .where("import_id", "=", importId)
      .executeTakeFirst()) as { total: number } | undefined;
    return Number(row?.total ?? 0);
  }

  /**
   * Removes the rows an import last wrote, from the console database only.
   *
   * Nothing is sent to Viewpoint. This deletes what console2 stored, and Viewpoint
   * remains the system of record — a booking removed here is still in Viewpoint and
   * will come back if the same report is imported again. That is the intended
   * behaviour: this is an undo for a bad import, not a way to cancel a booking.
   *
   * The `viewpoint_imports` row is deliberately kept. It records who uploaded which
   * file, when, and how it was mapped, which is the audit trail for the data that
   * was just removed — deleting it would erase the evidence of the thing being
   * undone.
   */
  async deleteRowsForImport(
    reportType: ViewpointReportType,
    importId: string,
  ): Promise<number> {
    const result = await this.db
      .deleteFrom(VIEWPOINT_TABLE[reportType])
      .where("import_id", "=", importId)
      .executeTakeFirst();
    return Number(result?.numDeletedRows ?? 0);
  }

  /**
   * Removes specific bookings by number, console database only.
   *
   * Its own method rather than routing through `deleteBookingsByFilters` with a
   * search term: `search` is an ILIKE across several columns, so deleting "3193074"
   * that way could also match a booking whose guest name or resort happened to
   * contain it. Row-level delete has to hit exactly the row that was clicked, which
   * means matching the natural key exactly.
   *
   * Viewpoint is not contacted. The booking still exists there and returns on the
   * next import of the same report.
   */
  async deleteBookingsByNumbers(bookingNumbers: string[]): Promise<number> {
    const numbers = Array.from(
      new Set(bookingNumbers.map((n) => String(n).trim()).filter(Boolean)),
    );
    // Guarded because `IN ()` on an empty list is a syntax error in some builders,
    // and "delete nothing" must never widen into "delete everything".
    if (numbers.length === 0) return 0;

    const result = await this.db
      .deleteFrom("viewpoint_bookings")
      .where("booking_no", "in", numbers)
      .executeTakeFirst();
    return Number(result?.numDeletedRows ?? 0);
  }

  /**
   * Removes the bookings matching a set of filters, console database only.
   *
   * Built through the same `applyBookingFilters` the list and the aggregates use, so
   * "delete what is on screen" deletes exactly what was on screen. A second
   * hand-written WHERE clause here would be the obvious way to get that wrong — the
   * table would show one set and the delete would remove another, and nobody would
   * find out until rows were already gone.
   *
   * Viewpoint is not contacted. Deleted rows still exist there and return on the next
   * import of the same report.
   */
  async deleteBookingsByFilters(filters: BookingFilters): Promise<number> {
    const result = await this.applyBookingFilters(
      this.db.deleteFrom("viewpoint_bookings"),
      filters,
    ).executeTakeFirst();
    return Number(result?.numDeletedRows ?? 0);
  }

  /**
   * How many bookings those filters match.
   *
   * Read before deleting so the confirmation states a real number. Uses the same
   * filter builder again, for the same reason.
   */
  async countBookingsByFilters(filters: BookingFilters): Promise<number> {
    const row = (await this.applyBookingFilters(
      this.db
        .selectFrom("viewpoint_bookings")
        .select(sql<number>`COUNT(*)::int`.as("total")),
      filters,
    ).executeTakeFirst()) as { total: number } | undefined;
    return Number(row?.total ?? 0);
  }

  /**
   * Empties a report's table in the console database.
   *
   * Again console-only: Viewpoint is untouched and re-importing restores everything.
   * Import records are kept for the same audit reason as above.
   */
  async deleteAllRows(reportType: ViewpointReportType): Promise<number> {
    const result = await this.db
      .deleteFrom(VIEWPOINT_TABLE[reportType])
      .executeTakeFirst();
    return Number(result?.numDeletedRows ?? 0);
  }

  /**
   * Reconciles imported bookings against core's own booking records.
   *
   * Core stores Viewpoint's booking number on `booking_units.viewpoint_booking_number`
   * (and the same column on `booking_curated_events`) for bookings it created, so that
   * is the link between the two systems. A match means the booking came through Subito
   * and exists in both; no match means it was made directly in Viewpoint.
   *
   * Done in chunks because the member DB is a separate server: the numbers have to be
   * shipped to it rather than joined against. Chunking also keeps the parameter list
   * well inside Postgres' limit as the imported set grows.
   *
   * `core_matched_at` is stamped on every row the run covers, matched or not — without
   * that, "checked and not in core" is indistinguishable from "never checked", and the
   * dashboard would have to present a guess as a fact.
   *
   * Read-only against the member DB, as everything here is.
   */
  async matchAgainstCore(): Promise<{
    checked: number;
    matched: number;
    unmatched: number;
  }> {
    if (!this.memberDb) {
      throw new Error("Member database is not configured");
    }

    const rows = (await this.db
      .selectFrom("viewpoint_bookings")
      .select(["booking_no"])
      .execute()) as Array<{ booking_no: string }>;
    const numbers = rows
      .map((r) => String(r.booking_no ?? "").trim())
      .filter(Boolean);
    if (numbers.length === 0) {
      return { checked: 0, matched: 0, unmatched: 0 };
    }

    const CHUNK = 500;
    /** Viewpoint booking number -> what core knows about it. */
    const found = new Map<
      string,
      { bookingId: string; status: string | null; kind: "unit" | "curated" }
    >();

    for (let start = 0; start < numbers.length; start += CHUNK) {
      const chunk = numbers.slice(start, start + CHUNK);
      /*
       * Both child tables in one statement, tagged by kind.
       *
       * `booking_units` is by far the larger population, so it is listed first and
       * wins a tie below — a booking recorded as both a stay and a curated event is
       * a stay for reconciliation purposes.
       */
      const matches = (await sql<{
        viewpoint_booking_number: string;
        booking_id: string;
        status: string | null;
        kind: string;
      }>`
        SELECT bu.viewpoint_booking_number, bu.booking_id, bu.status::text AS status,
               'unit' AS kind
        FROM booking_units bu
        WHERE BTRIM(bu.viewpoint_booking_number) = ANY(${chunk})
        UNION ALL
        SELECT bce.viewpoint_booking_number, bce.booking_id, bce.status::text AS status,
               'curated' AS kind
        FROM booking_curated_events bce
        WHERE BTRIM(bce.viewpoint_booking_number) = ANY(${chunk})
      `.execute(this.memberDb)) as unknown as {
        rows: Array<{
          viewpoint_booking_number: string;
          booking_id: string;
          status: string | null;
          kind: string;
        }>;
      };

      for (const m of matches.rows) {
        const key = String(m.viewpoint_booking_number ?? "").trim();
        if (!key) continue;
        // First writer wins, and units are selected first — see the note above.
        if (found.has(key)) continue;
        found.set(key, {
          bookingId: String(m.booking_id),
          status: m.status ?? null,
          kind: m.kind === "curated" ? "curated" : "unit",
        });
      }
    }

    const now = new Date();

    /*
     * Matched rows are written individually; the unmatched are cleared in one
     * statement. Writing per row for the matches is unavoidable — each carries
     * different values — but the far larger unmatched set needs only a timestamp, and
     * doing that as one UPDATE keeps a reconciliation over thousands of rows to a
     * couple of statements rather than thousands.
     */
    for (const [bookingNo, match] of found) {
      await this.db
        .updateTable("viewpoint_bookings")
        .set({
          core_booking_id: match.bookingId,
          core_booking_status: match.status,
          core_booking_kind: match.kind,
          core_matched_at: now,
          updated_at: now,
        })
        .where("booking_no", "=", bookingNo)
        .execute();
    }

    const matchedNumbers = Array.from(found.keys());
    let cleared: any = this.db.updateTable("viewpoint_bookings").set({
      /*
       * Cleared, not left alone. A booking that used to match and no longer does —
       * deleted in core, or its Viewpoint number corrected — must stop claiming a core
       * record that isn't there.
       */
      core_booking_id: null,
      core_booking_status: null,
      core_booking_kind: null,
      core_matched_at: now,
      updated_at: now,
    });
    if (matchedNumbers.length > 0) {
      cleared = cleared.where("booking_no", "not in", matchedNumbers);
    }
    await cleared.execute();

    return {
      checked: numbers.length,
      matched: found.size,
      unmatched: numbers.length - found.size,
    };
  }

  /**
   * Where the stored bookings came from, and when that was last established.
   *
   * `never_checked` is its own bucket rather than folded into Viewpoint-only. Before a
   * reconciliation has run, nothing is known about overlap, and reporting that as
   * "made directly in Viewpoint" would be inventing a finding.
   */
  async originBreakdown(): Promise<{
    subito: number;
    viewpointOnly: number;
    neverChecked: number;
    lastMatchedAt: string | null;
  }> {
    const row = (await this.db
      .selectFrom("viewpoint_bookings")
      .select([
        sql<number>`COUNT(*) FILTER (WHERE core_booking_id IS NOT NULL)::int`.as(
          "subito",
        ),
        sql<number>`COUNT(*) FILTER (WHERE core_booking_id IS NULL AND core_matched_at IS NOT NULL)::int`.as(
          "viewpoint_only",
        ),
        sql<number>`COUNT(*) FILTER (WHERE core_matched_at IS NULL)::int`.as(
          "never_checked",
        ),
        sql<string | null>`MAX(core_matched_at)`.as("last_matched_at"),
      ])
      .executeTakeFirst()) as any;

    return {
      subito: Number(row?.subito ?? 0),
      viewpointOnly: Number(row?.viewpoint_only ?? 0),
      neverChecked: Number(row?.never_checked ?? 0),
      lastMatchedAt: row?.last_matched_at ?? null,
    };
  }

  /** Booking numbers due a sync, oldest-synced first so a capped run makes progress. */
  async bookingNumbersToSync(limit: number): Promise<string[]> {
    const rows = (await this.db
      .selectFrom("viewpoint_bookings")
      .select(["booking_no"])
      // NULLS FIRST: never-synced rows are the ones with nothing but a
      // spreadsheet snapshot behind them, so they are worth the most.
      .orderBy(sql`last_synced_at ASC NULLS FIRST`)
      .limit(limit)
      .execute()) as Array<{ booking_no: string }>;
    return rows.map((r) => String(r.booking_no));
  }

  /**
   * Applies a Viewpoint API response to a stored booking.
   *
   * `source_of_truth` becomes `sync` because these values are current, whereas an
   * imported status is only true as of the moment the report was exported.
   */
  async applySyncedBooking(
    bookingNo: string,
    values: Record<string, unknown>,
  ): Promise<void> {
    await this.db
      .updateTable("viewpoint_bookings")
      .set({
        ...values,
        source_of_truth: "sync",
        last_synced_at: new Date(),
        sync_error: null,
        updated_at: new Date(),
      })
      .where("booking_no", "=", bookingNo)
      .execute();
  }

  /**
   * Records that a sync attempt failed.
   *
   * `last_synced_at` is still stamped so a booking that always fails — deleted in
   * Viewpoint, or a number the report mangled — moves to the back of the queue
   * instead of being retried first on every run and starving the rest.
   */
  async recordSyncFailure(bookingNo: string, message: string): Promise<void> {
    await this.db
      .updateTable("viewpoint_bookings")
      .set({
        sync_error: message.slice(0, 500),
        last_synced_at: new Date(),
        updated_at: new Date(),
      })
      .where("booking_no", "=", bookingNo)
      .execute();
  }

  /** Member contact rows, with the same member-DB enrichment as bookings. */
  async listMemberContacts(opts: {
    page: number;
    pageSize: number;
    search?: string;
    onlyUnmatched?: boolean;
  }) {
    const offset = (opts.page - 1) * opts.pageSize;
    const base = () => {
      let q: any = this.db.selectFrom("viewpoint_member_contacts");
      if (opts.search?.trim()) {
        const s = `%${opts.search.trim()}%`;
        q = q.where((eb: any) =>
          eb.or([
            eb("membership_number", "ilike", s),
            eb("first_name", "ilike", s),
            eb("last_name", "ilike", s),
            eb("email", "ilike", s),
          ]),
        );
      }
      return q;
    };

    const items = (await base()
      .selectAll()
      .orderBy("membership_number", "asc")
      .limit(opts.pageSize)
      .offset(offset)
      .execute()) as any[];

    const totalRow = (await base()
      .select(sql<number>`COUNT(*)::int`.as("total"))
      .executeTakeFirst()) as { total: number } | undefined;

    const matches = await this.resolveMembers(
      items.map((r) => String(r.membership_number ?? "")),
    );

    const enriched = items.map((r) => decorateWithMember(r, matches));

    return {
      // Filtered after enrichment because "unmatched" is a fact about the member
      // DB, which the console DB cannot express in its own WHERE clause.
      items: opts.onlyUnmatched
        ? enriched.filter((r) => !r.member_matched)
        : enriched,
      total: Number(totalRow?.total ?? 0),
    };
  }
}

/**
 * Where a booking came from, decided by whether core has it.
 *
 * This is the booking's *source* as the business means it: a booking present in core
 * was made through Subito, and one that is not was made directly in Viewpoint. It is
 * deliberately not the report's own `BookingSourceDesc` column — that says "KCGM" for
 * almost every row and describes which report produced the file, not which system took
 * the booking.
 *
 * `unchecked` is a real third value, not a gap: before a reconciliation has run,
 * nothing is known either way, and calling those Viewpoint-direct would be asserting a
 * result nobody has established.
 */
/**
 * The whitespace characters to strip when comparing keys.
 *
 * Postgres' bare `BTRIM(x)` removes **spaces only** — not tabs, carriage returns or
 * newlines. Real data has all three: `members.signup_promo_code` holds
 * "\t\r\nSQUARECLUB" for one member, which bare BTRIM left intact, so it became its
 * own promo code, matched no campaign, and its bookings silently fell out of the
 * dashboard's total. JavaScript's `.trim()` strips all of them, which is how the two
 * sides of the same comparison came to disagree.
 */
const WS = " \t\r\n\f\v";

export type BookingOrigin = "subito" | "viewpoint" | "unchecked";

export interface BookingFilters {
  origins?: BookingOrigin[];
  sources?: string[];
  statuses?: string[];
  from?: string;
  to?: string;
  membershipNumbers?: string[];
  search?: string;
  /** Only rows carrying no membership number at all. */
  onlyUnmatched?: boolean;
}

export interface MemberSummary {
  id: string;
  membershipNumber: string | null;
  memberNumber: string | null;
  firstName: string | null;
  lastName: string | null;
  email: string | null;
  phone: string | null;
  country: string | null;
  signupPromoCode: string | null;
}

/**
 * Attaches the member-DB resolution to one imported row.
 *
 * Shared by both listings so bookings and contacts describe a match identically —
 * the dashboard distinguishes three states, and conflating any two of them hides a
 * different problem:
 *
 *   no number at all      — the report did not carry one
 *   number, no match      — the person is not in the member DB
 *   number, ambiguous     — it is a membership shared by several members
 */
function decorateWithMember(
  row: any,
  matches: Map<string, MemberMatch>,
): any {
  const key = normaliseMembershipNumber(row.membership_number);
  const match = key ? matches.get(key) : undefined;

  /*
   * A membership shared by several members is narrowed by the booking's guest name.
   *
   * The membership number identifies a household, not a person, so on its own it can
   * only report "4 members" — which is true and useless. But the booking says who
   * actually stayed, and that name is almost always one of the household: membership
   * 104092 holds four Savages, and the guest is "Andrew James Savage".
   *
   * Measured on the live import: 9 of 159 rows were ambiguous, and the guest name
   * resolved every one of them to exactly one member.
   *
   * Only a *unique* hit counts. If the name matches two household members — twins, a
   * junior, a duplicated record — the row stays ambiguous rather than picking one,
   * because a wrong member is worse than no member.
   */
  const resolved =
    match && !match.member ? resolveByGuestName(row.guest_name, match.members) : null;

  const member = match?.member ?? resolved ?? null;
  const ambiguous = Boolean(match && !member);

  return {
    ...row,
    member,
    member_candidates: ambiguous ? match!.members : [],
    member_matched: Boolean(match),
    // Records that the guest name did the narrowing, so the basis of the match is not
    // misreported as the membership number alone.
    member_matched_on: resolved ? "guest_name" : (match?.matchedOn ?? null),
    member_ambiguous: ambiguous,
    has_membership_number: Boolean(key),
  };
}

/** Letters only, lower-cased — so punctuation and spacing don't defeat a match. */
function nameKey(value: unknown): string {
  return String(value ?? "")
    .toLowerCase()
    .replace(/[^a-z]/g, "");
}

/**
 * The one household member whose name matches the booking's guest, if exactly one does.
 *
 * Exact match is tried before containment, and containment only counts when it is
 * unique. Trying them in that order matters: "Andrew Savage" is contained in nothing
 * else, but a bare "Foo Bar" repeated across a household would match several — and
 * returning null there is the correct answer.
 */
function resolveByGuestName(
  guestName: unknown,
  candidates: MemberSummary[],
): MemberSummary | null {
  const guest = nameKey(guestName);
  if (!guest || candidates.length === 0) return null;

  const keyed = candidates.map((m) => ({
    member: m,
    key: nameKey(`${m.firstName ?? ""}${m.lastName ?? ""}`),
  }));

  const exact = keyed.filter((c) => c.key && c.key === guest);
  if (exact.length === 1) return exact[0].member;
  // Several members with the identical name: nothing distinguishes them, so nothing
  // is claimed.
  if (exact.length > 1) return null;

  const partial = keyed.filter(
    (c) => c.key && (guest.includes(c.key) || c.key.includes(guest)),
  );
  return partial.length === 1 ? partial[0].member : null;
}

/**
 * The outcome of resolving one report number.
 *
 * `member` is null when the number resolved to a membership covering several
 * people — the match is real but does not name an individual, and the dashboard
 * reports that instead of showing an arbitrary one of them.
 */
export interface MemberMatch {
  matchedOn: "member_number" | "membership_number" | "guest_name";
  member: MemberSummary | null;
  members: MemberSummary[];
}

/** The form membership numbers are compared in — trimmed and upper-cased. */
export function normaliseMembershipNumber(
  value: unknown,
): string | null {
  const text = String(value ?? "").trim().toUpperCase();
  return text === "" ? null : text;
}