import type { Kysely } from "kysely";
import { sql } from "kysely";
import { logWarn } from "@/lib/logger";

/**
 * Read-only member lookups against the Updot member DB (`memberDatastore`).
 *
 * Firestore events carry only a member number, so a member-wise report reads as
 * a list of bare digits without this. The names, emails and account details come
 * from the member DB, which is the system of record for them.
 *
 * IMPORTANT: SELECT only. The Updot member DB is production data — no writes
 * from this codebase.
 */

export type EventMemberProfile = {
  /** The number as it appears in the Firestore event, for joining back. */
  memberNumber: string;
  memberId: string | null;
  membershipNumber: string | null;
  fullName: string | null;
  email: string | null;
  mobile: string | null;
  country: string | null;
  nationality: string | null;
  dateOfBirth: string | null;
  accountType: string | null;
  accountStatus: string | null;
  signupPromoCode: string | null;
  memberSince: string | null;
};

/** Postgres `IN` lists get slow long before this; events pages are far smaller. */
const MAX_LOOKUP = 500;

/** Columns the Firestore member number may correspond to. */
const ALLOWED_MATCH_COLUMNS = ["membership_number", "member_number"] as const;

/**
 * Which member-DB column the Firestore `memberNumber` matches.
 *
 * `membership_number`, confirmed with the producer despite the field's name.
 *
 * This used to match both columns with an OR. That was actively wrong: the two
 * are distinct identifiers, so one member's `member_number` can equal a
 * *different* member's `membership_number`, both rows come back, and the last
 * one written wins — showing somebody else's name against the events. A wrong
 * name is worse than no name, so exactly one column is matched.
 *
 * Override with EVENT_TRACKING_MEMBER_COLUMN if the producer changes.
 */
function matchColumn(): (typeof ALLOWED_MATCH_COLUMNS)[number] {
  const raw = process.env.EVENT_TRACKING_MEMBER_COLUMN;
  // Whitelisted, not interpolated: this value reaches a SQL identifier position.
  return ALLOWED_MATCH_COLUMNS.includes(raw as never)
    ? (raw as (typeof ALLOWED_MATCH_COLUMNS)[number])
    : "membership_number";
}

/**
 * Profiles for a set of membership numbers, keyed by the number that was asked
 * for.
 *
 * A number with no match is simply absent from the map — the caller falls back
 * to showing the number, which is better than failing the whole report because
 * one event referenced a member who has since been removed.
 */
export async function lookupMembersByNumber(
  memberDb: Kysely<any>,
  memberNumbers: string[],
): Promise<Map<string, EventMemberProfile>> {
  const numbers = [...new Set(memberNumbers.filter(Boolean))].slice(0, MAX_LOOKUP);
  if (!numbers.length) return new Map();

  const column = matchColumn();

  // `any` on the builder chain, as elsewhere in this repo: the member DB has no
  // generated Kysely types here, so the raw-SQL joins below cannot be expressed
  // against a typed schema.
  const rows = await (memberDb as any)
    .selectFrom("members")
    .leftJoin("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(
      // One profile row per member: `member_profiles` can hold several, and a
      // plain join would multiply the result set. Newest wins, matching how the
      // campaigns repo resolves the same table.
      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"),
    )
    .where(sql<boolean>`members.${sql.raw(column)} = ANY(${numbers})`)
    .select([
      sql<string | null>`members.member_number`.as("member_number"),
      sql<string | null>`members.membership_number`.as("membership_number"),
      sql<string | null>`members.id::text`.as("member_id"),
      sql<string | null>`NULLIF(TRIM(COALESCE(profile.first_name, '') || ' ' || COALESCE(profile.last_name, '')), '')`.as(
        "full_name",
      ),
      sql<string | null>`member_user.email`.as("email"),
      sql<string | null>`profile.mobile`.as("mobile"),
      sql<string | null>`profile.country`.as("country"),
      sql<string | null>`profile.nationality`.as("nationality"),
      sql<string | null>`profile.date_of_birth::text`.as("date_of_birth"),
      sql<string | null>`account_type.name`.as("account_type"),
      sql<string | null>`account_status.name`.as("account_status"),
      sql<string | null>`members.signup_promo_code`.as("signup_promo_code"),
      sql<string | null>`members.created_at::text`.as("member_since"),
    ])
    .execute();

  const byNumber = new Map<string, EventMemberProfile>();
  const ambiguous = new Set<string>();

  for (const row of rows as any[]) {
    // The key is the value of the column that was matched, so it is exactly the
    // string the Firestore document carried.
    const key = column === "membership_number" ? row.membership_number : row.member_number;
    if (!key) continue;

    /*
     * Two members sharing one identifier should be impossible, but the member DB
     * does not enforce it. Rather than pick one and print a name that may belong
     * to the wrong person, drop both and let the UI show the bare number.
     */
    if (byNumber.has(key)) {
      ambiguous.add(key);
      continue;
    }

    const profile: EventMemberProfile = {
      memberNumber: key,
      memberId: row.member_id ?? null,
      membershipNumber: row.membership_number ?? null,
      fullName: row.full_name ?? null,
      email: row.email ?? null,
      mobile: row.mobile ?? null,
      country: row.country ?? null,
      nationality: row.nationality ?? null,
      dateOfBirth: row.date_of_birth ?? null,
      accountType: row.account_type ?? null,
      accountStatus: row.account_status ?? null,
      signupPromoCode: row.signup_promo_code ?? null,
      memberSince: row.member_since ?? null,
    };

    byNumber.set(key, profile);
  }

  for (const key of ambiguous) {
    byNumber.delete(key);
    logWarn(
      `event-analytics: ${key} matched more than one member on ${column}; showing the number instead of a possibly wrong name.`,
    );
  }

  return byNumber;
}