import { DatabaseError } from "@/lib/error"; import { logError } from "@/lib/logger"; import { sql } from "kysely"; import type { RepositoryContext } from "../datastore/repository"; import { BaseRepository } from "../datastore/repository"; export const MemberRepository = (ctx: RepositoryContext) => { // Member tables live in the Updot member database. const { memberDatastore: datastore } = new BaseRepository(ctx); const FindMemberRecordsByUserID = async (userID: string) => { try { const records = await datastore .selectFrom("members") .innerJoin( "member_account_types", "member_account_types.id", "members.membership_type_id", ) .select([ "members.id", "members.member_number", "members.membership_number", "member_account_types.id as account_type_id", "member_account_types.ext_account_type_id", "member_account_types.is_active as is_active_account_type", "member_account_types.name as account_type_name", ]) .where("user_id", "=", userID) .execute(); return records; } catch (error) { throw new DatabaseError({ error, message: "Find member by user ID" }); } }; const FindMemberRecordByID = async (id: string) => { try { const record = await datastore .selectFrom("members") .innerJoin( "member_account_types", "member_account_types.id", "members.membership_type_id", ) .select([ "members.id", "members.member_number", "members.membership_number", "member_account_types.id as account_type_id", "member_account_types.ext_account_type_id", "member_account_types.is_active as is_active_account_type", "member_account_types.name as account_type_name", "members.created_at", "members.updated_at", "members.user_id", ]) .where("members.id", "=", id) .executeTakeFirstOrThrow(); return record; } catch (error) { throw new DatabaseError({ error, message: "Find member by user ID" }); } }; const FindMemberProfile = async (memberRecordID: string) => { try { const record = await datastore .selectFrom("member_profiles") .selectAll() .where("member_profiles.member_id", "=", memberRecordID) .executeTakeFirstOrThrow(); return record; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to find member profile", }); } }; const UpdateMember = async ( memberRecordID: string, entry: { membershipTypeID?: string }, ) => { try { const updatedRecord = await datastore .updateTable("members") .set(() => { const updateRecord: Record = { updated_at: new Date().toISOString().replace(/\.\d{3}Z$/, "Z"), ...(entry.membershipTypeID && { membership_type_id: entry.membershipTypeID, }), }; return updateRecord; }) .where("members.id", "=", memberRecordID) .executeTakeFirstOrThrow(); return updatedRecord; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to update the member records", }); } }; const UpdateMemberProfile = async ( memberRecordID: string, profile: { avatar?: string; first_name?: string; last_name?: string; mobile?: string; address_line_one?: string; address_line_two?: string; city?: string; state?: string; country?: string; postcode?: string; nationality?: string; date_of_birth?: string; is_owner?: boolean; is_family?: boolean; date_of_anniversary?: string; }, ) => { try { const updatedRecord = await datastore .updateTable("member_profiles") .set(() => { const updateRecord: Record = { updated_at: new Date().toISOString().replace(/\.\d{3}Z$/, "Z"), ...(profile.avatar && { avatar: profile.avatar }), ...(profile.first_name && { first_name: profile.first_name }), ...(profile.last_name && { last_name: profile.last_name }), ...(profile.address_line_one && { address_line_one: profile.address_line_one, }), ...(profile.address_line_two && { address_line_two: profile.address_line_two, }), ...(profile.city && { city: profile.city }), ...(profile.state && { state: profile.state }), ...(profile.country && { country: profile.country }), ...(profile.postcode && { postcode: profile.postcode }), ...(profile.nationality && { nationality: profile.nationality }), ...(profile.date_of_birth && { date_of_birth: new Date(profile.date_of_birth), }), ...(profile.is_owner && { _is_owner: profile.is_owner }), ...(profile.is_family && { _is_family: profile.is_family }), ...(profile.date_of_anniversary && { date_of_anniversary: new Date(profile.date_of_anniversary), }), }; return updateRecord; }) .where("member_profiles.member_id", "=", memberRecordID) .executeTakeFirstOrThrow(); return updatedRecord; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to update member profile", }); } }; const FindPaginatedMemberships = async (query: { page: number; limit: number; searchTerm?: string; accountTypeIds?: string[]; accountStatusIds?: string[]; isActive?: boolean; createdFrom?: string; createdTo?: string; scopeTypeIds?: string[]; scopeStatusIds?: string[]; }) => { try { const { limit, page, searchTerm, accountTypeIds, accountStatusIds, isActive, createdFrom, createdTo, scopeTypeIds, scopeStatusIds, } = query; const offset = (page - 1) * limit; const baseQuery = await datastore .selectFrom("members") .innerJoin("users as member_user", "member_user.id", "members.user_id") .innerJoin( "member_account_types as account_type", "account_type.id", "members.membership_type_id", ) .innerJoin( "member_account_statuses as account_status", "account_status.id", "members.membership_status_id", ) .innerJoin( "member_profiles as profile", "profile.member_id", "members.id", ) .select([ "members.id as member_id", "members.member_number", "members.membership_number", "members._is_active as is_active", "members._is_deleted as is_deleted", "members.created_at", "members.updated_at", "member_user.id as user_id", "member_user.email", "profile.first_name", "profile.last_name", "profile._is_family as is_family", "profile._is_owner as is_owner", "member_user._is_active as user_is_active", "member_user._is_deleted as user_is_deleted", "account_type.id as account_type_id", "account_type.name as account_type_name", "account_type.ext_account_type_id as account_type_ext_id", "account_type.is_active as account_type_is_active", "account_status.id as account_status_id", "account_status.name as account_status_name", "account_status.ext_account_status_id as account_status_ext_id", "account_status.is_active as account_status_is_active", ]) .where((eb) => { const clauses = []; if (searchTerm) { const search = `%${searchTerm}%`; clauses.push( eb.or([ eb("profile.first_name", "ilike", search), eb("profile.last_name", "ilike", search), eb("account_status.name", "ilike", search), eb("account_type.name", "ilike", search), eb("member_user.email", "ilike", search), eb("members.member_number", "ilike", search), eb("members.membership_number", "ilike", search), ]), ); } if (accountTypeIds?.length) { clauses.push(eb("account_type.id", "in", accountTypeIds)); } if (accountStatusIds?.length) { clauses.push(eb("account_status.id", "in", accountStatusIds)); } // Access scope, applied on top of the caller's own filters so a // scoped user cannot widen past their allow-list by filtering. if (scopeTypeIds?.length) { clauses.push(eb("account_type.id", "in", scopeTypeIds)); } if (scopeStatusIds?.length) { clauses.push(eb("account_status.id", "in", scopeStatusIds)); } if (typeof isActive === "boolean") { clauses.push(eb("members._is_active", "=", isActive)); } if (createdFrom) { clauses.push(eb("members.created_at", ">=", new Date(createdFrom))); } if (createdTo) { clauses.push(eb("members.created_at", "<=", new Date(createdTo))); } // No filters: a true clause so nothing gets filtered out. if (clauses.length === 0) { return eb.val(true); } return eb.and(clauses); }); /* * Two stages, because the row shown per account must be the *owner*, not * whichever member happened to match. * * Filtering by a contact's member number previously left only that * contact's row in the set, so DISTINCT ON returned the contact and the * table showed a child as the account's primary contact. Stage 1 finds the * matching membership numbers; stage 2 picks the representative row for * those accounts from *all* their members, preferring owner, then family, * then the earliest record. */ const matchingPage = await baseQuery .clearSelect() .select("members.membership_number") .distinct() .orderBy("members.membership_number", "asc") .limit(limit) .offset(offset) .execute(); const membershipNumbers = matchingPage.map((row) => String(row.membership_number), ); const members = membershipNumbers.length ? await datastore .selectFrom("members") .innerJoin( "users as member_user", "member_user.id", "members.user_id", ) .innerJoin( "member_account_types as account_type", "account_type.id", "members.membership_type_id", ) .innerJoin( "member_account_statuses as account_status", "account_status.id", "members.membership_status_id", ) .innerJoin( "member_profiles as profile", "profile.member_id", "members.id", ) .select([ "members.id as member_id", "members.member_number", "members.membership_number", "members._is_active as is_active", "members._is_deleted as is_deleted", "members.created_at", "members.updated_at", "member_user.id as user_id", "member_user.email", "profile.first_name", "profile.last_name", "profile._is_family as is_family", "profile._is_owner as is_owner", "member_user._is_active as user_is_active", "member_user._is_deleted as user_is_deleted", "account_type.id as account_type_id", "account_type.name as account_type_name", "account_type.ext_account_type_id as account_type_ext_id", "account_type.is_active as account_type_is_active", "account_status.id as account_status_id", "account_status.name as account_status_name", "account_status.ext_account_status_id as account_status_ext_id", "account_status.is_active as account_status_is_active", ]) .where("members.membership_number", "in", membershipNumbers) .distinctOn("members.membership_number") .orderBy("members.membership_number", "asc") /* * Which member represents the account: owner, else family, else any * other contact — then the most recently synced record. * * One CASE rather than two boolean sorts. Sorting `_is_owner DESC` * then `_is_family DESC` looks equivalent but isn't: a row flagged * both owner *and* family then outranks a pure owner, which is how * a 2025 contact (owner+family) beat the account's current owner. * * The flags themselves are the app's, not Viewpoint's, and Viewpoint * is the authority on ownership — this list can't call it per row, * so the newest sync is the closest available proxy. */ .orderBy( sql`CASE WHEN profile._is_owner IS TRUE THEN 0 WHEN profile._is_family IS TRUE THEN 1 ELSE 2 END`, ) .orderBy("members.created_at", "desc") .orderBy("members.member_number", "asc") .execute() : []; const totalResult = await baseQuery .clearSelect() .select((eb) => eb .fn("count", [ eb.fn("distinct", [eb.ref("members.membership_number")]), ]) .as("total"), ) .executeTakeFirst(); const total = Number(totalResult?.total ?? 0); const totalPages = Math.ceil(total / limit); return { members, pagination: { page, limit, total, totalPages, hasNextPage: page < totalPages, hasPrevPage: page > 1, }, }; } catch (err) { throw new DatabaseError({ error: err, message: "Failed to find all memberships", }); } }; const FindMembersByMembershipNumber = async (membershipNumber: string) => { try { const records = await datastore .selectFrom("members") .innerJoin("users as member_user", "member_user.id", "members.user_id") .innerJoin( "member_account_types as account_type", "account_type.id", "members.membership_status_id", ) .innerJoin( "member_account_statuses as account_status", "account_status.id", "members.membership_status_id", ) .innerJoin( "member_profiles as profile", "profile.member_id", "members.id", ) .select([ "members.id as member_id", "members.member_number", "members.membership_number", "members._is_active as is_active", "members._is_deleted as is_deleted", "members.created_at", "members.updated_at", "member_user.id as user_id", "member_user.email", "profile.first_name", "profile.last_name", "profile._is_family as is_family", "profile._is_owner as is_owner", "profile.address_line_one", "profile.address_line_two", "profile.city", "profile.country", "profile.postcode", "profile.mobile", "profile.nationality", "profile.state", "profile.date_of_birth", "profile.avatar", "member_user._is_active as user_is_active", "member_user._is_deleted as user_is_deleted", "account_type.id as account_type_id", "account_type.name as account_type_name", "account_type.ext_account_type_id as account_type_ext_id", "account_type.is_active as account_type_is_active", "account_status.id as account_status_id", "account_status.name as account_status_name", "account_status.ext_account_status_id as account_status_ext_id", "account_status.is_active as account_status_is_active", ]) .where("members.membership_number", "=", membershipNumber) .execute(); return records; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to find members by membership number", }); } }; const FindMembersByMemberNumber = async (memberNumber: string) => { try { const records = await datastore .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( "member_profiles as profile", "profile.member_id", "members.id", ) .leftJoin( "member_preferences", "member_preferences.member_id", "members.id", ) .select([ "members.id as member_id", "members.member_number", "members.membership_number", "members._is_active as is_active", "members._is_deleted as is_deleted", "members.created_at", "members.updated_at", "member_user.id as user_id", "member_user.email", "profile.first_name", "profile.last_name", "profile._is_family as is_family", "profile._is_owner as is_owner", "profile.address_line_one", "profile.address_line_two", "profile.city", "profile.country", "profile.postcode", "profile.mobile", "profile.nationality", "profile.state", "profile.avatar", "profile.date_of_birth", "profile.date_of_anniversary", "profile.id as id", "member_user._is_active as user_is_active", "member_user._is_deleted as user_is_deleted", "account_type.id as account_type_id", "account_type.name as account_type_name", "account_type.ext_account_type_id as account_type_ext_id", "account_type.is_active as account_type_is_active", "account_status.id as account_status_id", "account_status.name as account_status_name", "account_status.ext_account_status_id as account_status_ext_id", "account_status.is_active as account_status_is_active", "member_preferences.personal_interests", "member_preferences.id as preference_id", ]) .where("members.member_number", "=", memberNumber) .execute(); return records; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to find member by member number", }); } }; /** * Every accounts-dashboard figure in one round trip. * * Was four separate queries (totals, by type, by status, by country), each * re-scanning ~49k members — the endpoint took seconds and the page sat on a * skeleton. One MATERIALIZED CTE is scanned once and reused by four cheap * aggregates, unioned into a single result the service folds apart. * * `count(DISTINCT email)` and the `users` join are gone with it: the Unique * Emails tile is parked, and that distinct was the most expensive column here. */ /** * Filters for the analytics queries. * * The same shape the accounts list takes, plus the caller's access slice. Both * are applied: the aggregates must describe the rows the caller can actually * see, otherwise a scoped user reads global figures and a filtered table sits * under an unfiltered chart. */ type AnalyticsFilters = { searchTerm?: string; accountTypeIds?: string[]; accountStatusIds?: string[]; isActive?: boolean; createdFrom?: string; createdTo?: string; scopeTypeIds?: string[]; scopeStatusIds?: string[]; }; /** WHERE fragment shared by the member aggregate and the booking totals. */ const analyticsPredicates = (filters: AnalyticsFilters) => { const clauses = [sql`TRUE`]; if (filters.searchTerm) { const term = `%${filters.searchTerm}%`; clauses.push(sql`( p.first_name ILIKE ${term} OR p.last_name ILIKE ${term} OR u.email ILIKE ${term} OR m.member_number ILIKE ${term} OR m.membership_number ILIKE ${term} OR at.name ILIKE ${term} OR st.name ILIKE ${term} )`); } if (filters.accountTypeIds?.length) { clauses.push(sql`at.id::text = ANY(${filters.accountTypeIds})`); } if (filters.accountStatusIds?.length) { clauses.push(sql`st.id::text = ANY(${filters.accountStatusIds})`); } // Access slice, applied on top so a filter can't widen past the allow-list. if (filters.scopeTypeIds?.length) { clauses.push(sql`at.id::text = ANY(${filters.scopeTypeIds})`); } if (filters.scopeStatusIds?.length) { clauses.push(sql`st.id::text = ANY(${filters.scopeStatusIds})`); } if (typeof filters.isActive === "boolean") { clauses.push( filters.isActive ? sql`m._is_active IS TRUE` : sql`m._is_active IS NOT TRUE`, ); } if (filters.createdFrom) { clauses.push(sql`m.created_at >= ${new Date(filters.createdFrom)}`); } if (filters.createdTo) { clauses.push(sql`m.created_at <= ${new Date(filters.createdTo)}`); } return sql.join(clauses, sql` AND `); }; const FindMembershipAnalytics = async ( filters: AnalyticsFilters = {}, countryLimit = 200, ) => { try { const result = await sql<{ kind: "total" | "type" | "status" | "country"; id: string | null; name: string | null; country: string | null; count: string; accounts: string | null; active: string | null; inactive: string | null; }>` WITH base AS MATERIALIZED ( SELECT m.id, m.membership_number, m._is_active, at.id AS type_id, at.name AS type_name, st.id AS status_id, st.name AS status_name FROM members m JOIN member_account_types at ON at.id = m.membership_type_id JOIN member_account_statuses st ON st.id = m.membership_status_id LEFT JOIN LATERAL ( SELECT first_name, last_name FROM member_profiles WHERE member_id = m.id ORDER BY id DESC LIMIT 1 ) p ON TRUE LEFT JOIN users u ON u.id = m.user_id WHERE ${analyticsPredicates(filters)} ) SELECT 'total' AS kind, NULL::text AS id, NULL::text AS name, NULL::text AS country, COUNT(*)::text AS count, COUNT(DISTINCT membership_number)::text AS accounts, COUNT(*) FILTER (WHERE _is_active IS TRUE)::text AS active, COUNT(*) FILTER (WHERE _is_active IS NOT TRUE)::text AS inactive FROM base UNION ALL SELECT 'type', type_id::text, type_name, NULL::text, COUNT(*)::text, NULL, NULL, NULL FROM base GROUP BY type_id, type_name UNION ALL SELECT 'status', status_id::text, status_name, NULL::text, COUNT(*)::text, NULL, NULL, NULL FROM base GROUP BY status_id, status_name UNION ALL SELECT * FROM ( SELECT 'country' AS kind, base.type_id::text AS id, base.type_name AS name, p.country AS country, COUNT(*)::text AS count, NULL::text, NULL::text, NULL::text FROM base JOIN member_profiles p ON p.member_id = base.id WHERE p.country IS NOT NULL AND p.country <> '' GROUP BY base.type_id, base.type_name, p.country ORDER BY COUNT(*) DESC LIMIT ${countryLimit} ) country_rows `.execute(datastore); return result.rows; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to fetch membership analytics", }); } }; /** * Booking totals across the whole member base, split by kind. * * Classification matches the promo campaign repo so a figure means the same * thing on both dashboards: internal = a stay at a Karma property, external = * an RCI exchange/rental, curated = has a `booking_curated_events` row. * * Its own query rather than part of the members union: it scans a different * table, and folding it in would force a join across both. */ const CountBookingTotals = async (filters: AnalyticsFilters = {}) => { try { const result = await sql<{ total: string; internal: string; external: string; curated: string; }>` WITH scoped_members AS ( SELECT m.id FROM members m JOIN member_account_types at ON at.id = m.membership_type_id JOIN member_account_statuses st ON st.id = m.membership_status_id LEFT JOIN LATERAL ( SELECT first_name, last_name FROM member_profiles WHERE member_id = m.id ORDER BY id DESC LIMIT 1 ) p ON TRUE LEFT JOIN users u ON u.id = m.user_id WHERE ${analyticsPredicates(filters)} ), live AS ( SELECT bookings.id, bookings.booking_entity_id FROM bookings JOIN scoped_members ON scoped_members.id = bookings.member_id WHERE COALESCE(bookings._is_deleted, false) = false ), curated AS ( SELECT DISTINCT booking_id FROM booking_curated_events ) SELECT COUNT(*)::text AS total, COUNT(*) FILTER ( WHERE entity.type IN ('RESORT', 'INTERNAL_PROPERTY') )::text AS internal, COUNT(*) FILTER (WHERE entity.type ILIKE 'RCI%')::text AS external, COUNT(*) FILTER (WHERE curated.booking_id IS NOT NULL)::text AS curated FROM live LEFT JOIN booking_entities entity ON entity.id = live.booking_entity_id LEFT JOIN curated ON curated.booking_id = live.id `.execute(datastore); const row = result.rows[0]; return { total: Number(row?.total ?? 0), internal: Number(row?.internal ?? 0), external: Number(row?.external ?? 0), curated: Number(row?.curated ?? 0), }; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to count booking totals", }); } }; /** * Per-account overview for the member detail page: booking mix, last login, * emails on the account and the signup promo codes its members used. * * Booking classification mirrors the promo campaign queries — internal is a * `RESORT`/`INTERNAL_PROPERTY` entity with unit rows, curated is read from * `booking_curated_events` (the authoritative curated record), external is * an RCI/external entity. */ const FindAccountOverview = async (membershipNumber: string) => { try { const bookings = await sql<{ internal: number; external: number; curated: number; }>` WITH acct AS ( SELECT id FROM members WHERE membership_number = ${membershipNumber} ), b AS ( SELECT bookings.id, booking_entities.type AS etype FROM bookings JOIN acct ON acct.id = bookings.member_id LEFT JOIN booking_entities ON booking_entities.id = bookings.booking_entity_id WHERE COALESCE(bookings._is_deleted, false) = false ) SELECT COUNT(*) FILTER ( WHERE etype IN ('RESORT', 'INTERNAL_PROPERTY') AND EXISTS ( SELECT 1 FROM booking_units bu WHERE bu.booking_id = b.id ) )::int AS internal, COUNT(*) FILTER ( WHERE etype ILIKE 'RCI%' OR etype ILIKE '%EXTERNAL%' )::int AS external, COUNT(*) FILTER ( WHERE EXISTS ( SELECT 1 FROM booking_curated_events bce WHERE bce.booking_id = b.id ) )::int AS curated FROM b `.execute(datastore); // Most-booked resorts/entities for this account, per booking kind, so the // detail page can show where the account actually stays rather than just // how many times. const topEntities = await sql<{ kind: "internal" | "external" | "curated"; name: string | null; bookings: string; }>` WITH acct AS ( SELECT id FROM members WHERE membership_number = ${membershipNumber} ), b AS ( SELECT bookings.id, booking_entities.type AS etype, booking_entities.name AS ename FROM bookings JOIN acct ON acct.id = bookings.member_id LEFT JOIN booking_entities ON booking_entities.id = bookings.booking_entity_id WHERE COALESCE(bookings._is_deleted, false) = false ), classified AS ( SELECT CASE WHEN EXISTS ( SELECT 1 FROM booking_curated_events bce WHERE bce.booking_id = b.id ) THEN 'curated' WHEN b.etype ILIKE 'RCI%' THEN 'external' ELSE 'internal' END AS kind, COALESCE(b.ename, 'Unknown') AS name FROM b ) SELECT kind, name, COUNT(*)::text AS bookings FROM classified GROUP BY kind, name ORDER BY COUNT(*) DESC LIMIT 30 `.execute(datastore); const lastSession = await datastore .selectFrom("sessions") .innerJoin("members", "members.user_id", "sessions.user_id") .select([ "sessions.id", "sessions.created_at", "sessions.application_type", "sessions.application_version", "sessions.os", "sessions.browser", "sessions.device_type", "sessions.ip_address", "sessions.location", "members.member_number", ]) .where("members.membership_number", "=", membershipNumber) .orderBy("sessions.created_at", "desc") .limit(1) .executeTakeFirst(); const contacts = await datastore .selectFrom("members") .innerJoin("users as member_user", "member_user.id", "members.user_id") .select([ "members.member_number", "member_user.email", "members.signup_promo_code", ]) .where("members.membership_number", "=", membershipNumber) .execute(); const emails = contacts .map((contact) => contact.email) .filter((email): email is string => Boolean(email)); return { bookings: { internal: Number(bookings.rows[0]?.internal ?? 0), external: Number(bookings.rows[0]?.external ?? 0), curated: Number(bookings.rows[0]?.curated ?? 0), }, topEntities: topEntities.rows.map((row) => ({ kind: row.kind, name: row.name ?? "Unknown", bookings: Number(row.bookings), })), lastSession: lastSession ?? null, emails: { // `contacts` doubles as the account's member count: one row per member // record on the membership. contacts: contacts.length, withEmail: emails.length, unique: Array.from(new Set(emails.map((e) => e.toLowerCase()))), }, promoCodes: Array.from( new Set( contacts .map((contact) => contact.signup_promo_code?.trim()) .filter((code): code is string => Boolean(code)), ), ), }; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to fetch account overview", }); } }; // Filter option lists for the accounts table, straight from the member DB so // the ids line up with what FindPaginatedMemberships filters on. const FindAccountTypes = async () => { try { return await datastore .selectFrom("member_account_types") .select(["id", "name", "is_active"]) .orderBy("name", "asc") .execute(); } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to fetch member account types", }); } }; const FindAccountStatuses = async () => { try { return await datastore .selectFrom("member_account_statuses") .select(["id", "name", "is_active"]) .orderBy("name", "asc") .execute(); } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to fetch member account statuses", }); } }; /** * The account type and status behind a membership or member number. * * Used to decide whether a scoped console user may open this record at all, * so it deliberately ignores the scope itself. */ const FindMembershipScopeKeys = async (params: { membershipNumber?: string; memberNumber?: string; }) => { try { let query = datastore .selectFrom("members") .innerJoin( "member_account_types as account_type", "account_type.id", "members.membership_type_id", ) .innerJoin( "member_account_statuses as account_status", "account_status.id", "members.membership_status_id", ) .select([ "members.membership_number", "account_type.id as account_type_id", "account_status.id as account_status_id", ]); if (params.membershipNumber) { query = query.where( "members.membership_number", "=", params.membershipNumber, ); } else if (params.memberNumber) { query = query.where("members.member_number", "=", params.memberNumber); } else { return null; } const record = await query.executeTakeFirst(); return record ?? null; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to resolve membership scope", }); } }; // Saved searches the member created in the app (member DB). const FindSavedSearchesByMemberID = async (memberID: string) => { try { const records = await datastore .selectFrom("member_saved_searches as ss") .select([ "ss.id", "ss.label", "ss.search_term", "ss.parameters", "ss.source", "ss.frequency", "ss.last_used", "ss.created_at", "ss.updated_at", ]) .where("ss.member_id", "=", memberID) .orderBy("ss.frequency", "desc") .orderBy("ss.last_used", "desc") .execute(); return records; } catch (err) { logError(err); throw new DatabaseError({ error: err, message: "Failed to fetch member saved searches", }); } }; return { FindMemberRecordsByUserID, FindSavedSearchesByMemberID, FindMembershipAnalytics, CountBookingTotals, FindAccountOverview, FindMembershipScopeKeys, FindAccountTypes, FindAccountStatuses, UpdateMemberProfile, FindMemberProfile, FindPaginatedMemberships, FindMembersByMembershipNumber, FindMembersByMemberNumber, FindMemberRecordByID, UpdateMember, }; }; export type TMemberRepository = ReturnType;