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

export type EventMemberProfile = {
  memberNumber: string;
  memberId: string | null;
  membershipNumber: string | null;
  fullName: string | null;
  firstName: string | null;
  lastName: string | null;
  title: string | null;
  email: string | null;
  mobile: string | null;
  city: string | null;
  state: string | null;
  country: string | null;
  nationality: string | null;
  dateOfBirth: string | null;
  relationship: string | null;
  isOwner: boolean | null;
  accountType: string | null;
  accountStatus: string | null;
  signupPromoCode: string | null;
  memberSince: string | null;
  sharedBy: number | null;
  ownerCount: number;
};

const MAX_LOOKUP = 500;

const ALLOWED_MATCH_COLUMNS = ["membership_number", "member_number"] as const;

function matchColumn(): (typeof ALLOWED_MATCH_COLUMNS)[number] {
  const raw = process.env.EVENT_TRACKING_MEMBER_COLUMN;
  return ALLOWED_MATCH_COLUMNS.includes(raw as never)
    ? (raw as (typeof ALLOWED_MATCH_COLUMNS)[number])
    : "membership_number";
}

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();

  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(
      sql`(
        SELECT DISTINCT ON (member_id)
          member_id, first_name, last_name, title, country, state, city,
          mobile, nationality, date_of_birth, relationship,
          _is_owner, _is_family
        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>`profile.first_name`.as("first_name"),
      sql<string | null>`profile.last_name`.as("last_name"),
      sql<string | null>`profile.title`.as("title"),
      sql<string | null>`profile.city`.as("city"),
      sql<string | null>`profile.state`.as("state"),
      sql<string | null>`profile.relationship`.as("relationship"),
      sql<boolean | null>`profile._is_owner`.as("is_owner"),
      sql<boolean | null>`profile._is_family`.as("is_family"),
      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 grouped = new Map<string, any[]>();
  for (const row of rows as any[]) {
    const key = column === "membership_number" ? row.membership_number : row.member_number;
    if (!key) continue;
    (grouped.get(key) ?? grouped.set(key, []).get(key)!).push(row);
  }

  for (const [key, matches] of grouped) {
    const owners = matches
      .filter((r) => r.is_owner)
      .sort(
        (a, b) =>
          String(a.member_since ?? "").localeCompare(String(b.member_since ?? "")) ||
          Number(a.member_id ?? 0) - Number(b.member_id ?? 0),
      );
    const chosen = matches.length === 1 ? matches[0] : (owners[0] ?? null);

    if (!chosen) {
      logWarn(
        `event-analytics: ${key} matched ${matches.length} members on ${column} ` +
        `and none is flagged as owner; showing the number alone.`,
      );
    }

    byNumber.set(key, {
      memberNumber: key,
      memberId: chosen?.member_id ?? null,
      membershipNumber: matches[0]?.membership_number ?? key,
      fullName: chosen?.full_name ?? null,
      firstName: chosen?.first_name ?? null,
      lastName: chosen?.last_name ?? null,
      title: chosen?.title ?? null,
      email: chosen?.email ?? null,
      mobile: chosen?.mobile ?? null,
      city: chosen?.city ?? null,
      state: chosen?.state ?? null,
      country: chosen?.country ?? null,
      nationality: chosen?.nationality ?? null,
      dateOfBirth: chosen?.date_of_birth ?? null,
      relationship: chosen?.relationship ?? null,
      isOwner: chosen ? Boolean(chosen.is_owner) : null,
      accountType: chosen?.account_type ?? null,
      accountStatus: chosen?.account_status ?? null,
      signupPromoCode: chosen?.signup_promo_code ?? null,
      memberSince: chosen?.member_since ?? null,
      sharedBy: matches.length > 1 ? matches.length : null,
      ownerCount: owners.length,
    });
  }

  return byNumber;
}


export async function membershipNumbersForEmail(
  memberDb: Kysely<any>,
  email: string,
): Promise<string[]> {
  const rows = await (memberDb as any)
    .selectFrom("members")
    .leftJoin("users as member_user", "member_user.id", "members.user_id")
    .where(sql<boolean>`lower(member_user.email) = lower(${email.trim()})`)
    .select([
      sql<string | null>`members.membership_number`.as("membership_number"),
    ])
    .execute();

  return [
    ...new Set(
      (rows as any[])
        .map((row) => (row.membership_number ? String(row.membership_number) : null))
        .filter((value): value is string => Boolean(value)),
    ),
  ];
}
