import { logError } from "@/lib/logger"; import { jsonArrayFrom } from "kysely/helpers/postgres"; import type { RepositoryContext } from "../datastore/repository"; import { BaseRepository } from "../datastore/repository"; import type { TMemberAccessPolicy } from "../member-access-policies/member-access-policy.types"; export const MembershipClubRepository = (ctx: RepositoryContext) => { const repo = new BaseRepository(ctx); const CreateEntry = async (entry: { name: string; club_id: string; account_types: { id: string }[]; charge_centers: { id: string }[]; statuses: { id: string; access_policies: TMemberAccessPolicy; }[]; note?: string; }) => { const trx = await repo.datastore.startTransaction().execute(); try { const newMembershipClub = await trx .insertInto("membership_clubs") .values({ name: entry.name, club_id: entry.club_id, note: entry?.note, }) .onConflict((oc) => oc.doNothing()) .returningAll() .executeTakeFirstOrThrow(); for (const { id } of entry.account_types) { await trx .insertInto("membership_account_type_clubs") .values({ membership_account_type_id: id, membership_club_id: newMembershipClub.id, }) .onConflict((oc) => oc.doNothing()) .returningAll() .executeTakeFirstOrThrow(); } for (const { id, access_policies } of entry.statuses) { const exisitingRecord = await trx .selectFrom("membership_club_status_policies") .where("membership_club_id", "=", newMembershipClub.id) .where("member_account_status_id", "=", id) .executeTakeFirst(); if (exisitingRecord) { continue; } const newPolicy = await trx .insertInto("member_access_role_policies") .values({ city_stopovers: access_policies.city_stopovers, curated_events: access_policies.curated_events, internal_properties: access_policies.internal_properties, karma_alliance: access_policies.karma_alliance, karma_alliance_offers: access_policies.karma_alliance_offers, karma_getaway: access_policies.karma_getaway, karma_getaway_offers: access_policies.karma_getaway_offers, karma_nomad: access_policies.karma_nomad, karma_registry_collection: access_policies.karma_registry_collection, login: access_policies.login, member_privileges_travel: access_policies.member_privileges_travel, profile: access_policies.profile, rci_exchange: access_policies.rci_exchange, rci_rental: access_policies.rci_rental, reciprocal_partners: access_policies.reciprocal_partners, twoforone: access_policies.twoforone, hot_deals: access_policies.hot_deals, // @ts-ignore restrictions: access_policies.restrictions ? access_policies.restrictions : null, }) .returning(["id"]) .executeTakeFirstOrThrow(); await trx .insertInto("membership_club_status_policies") .values({ membership_club_id: newMembershipClub.id, member_account_status_id: id, member_access_role_policy_id: newPolicy.id, }) .onConflict((oc) => oc.doNothing()) .returningAll() .executeTakeFirstOrThrow(); } await trx.commit().execute(); return newMembershipClub; } catch (err) { await trx.rollback().execute(); logError(err); throw new Error("Failed to create club"); } }; const FindEntries = async () => { try { const res = await repo.datastore .selectFrom("membership_clubs as mc") .select((eb) => [ "mc.id", "mc.club_id", "mc.name", jsonArrayFrom( eb .selectFrom("membership_account_type_clubs as matc") .innerJoin( "member_account_types as mat", "mat.id", "matc.membership_account_type_id", ) .select(["mat.name", "mat.ext_account_type_id", "mat.is_active"]) .whereRef("matc.membership_club_id", "=", "mc.id"), ).as("account_types"), jsonArrayFrom( eb .selectFrom("membership_club_status_policies as mcsp") .innerJoin( "member_account_statuses as mas", "mas.id", "mcsp.member_account_status_id", ) .innerJoin( "member_access_role_policies as marp", "marp.id", "mcsp.member_access_role_policy_id", ) .whereRef("mcsp.membership_club_id", "=", "mc.id") .select([ "mas.id", "mas.name", "mas.ext_account_status_id", "marp.city_stopovers", "marp.hot_deals", "marp.karma_alliance", "marp.karma_alliance_offers", "karma_getaway", "marp.karma_getaway_offers", "marp.karma_nomad", "marp.karma_registry_collection", "marp.login", "marp.member_privileges_travel", "marp.profile", "marp.rci_exchange", "marp.rci_rental", "marp.reciprocal_partners", "marp.twoforone", "marp.internal_properties", "marp.curated_events", "marp.restrictions", ]), ).as("statuses"), ]) .execute(); return res; } catch (err) { logError(err); throw new Error("Failed to fetch clubs"); } }; const FindEntry = async (id: string) => { try { const result = await repo.datastore .selectFrom("membership_clubs as mc") .where("mc.id", "=", id) .select((eb) => [ "mc.id", "mc.club_id", "mc.name", "mc.note", jsonArrayFrom( eb .selectFrom("membership_account_type_clubs as matc") .innerJoin( "member_account_types as mat", "mat.id", "matc.membership_account_type_id", ) .select([ "mat.name", "mat.ext_account_type_id", "mat.is_active", "mat.id", ]) .whereRef("matc.membership_club_id", "=", "mc.id"), ).as("account_types"), jsonArrayFrom( eb .selectFrom("membership_club_status_policies as mcsp") .innerJoin( "member_account_statuses as mas", "mas.id", "mcsp.member_account_status_id", ) .innerJoin( "member_access_role_policies as marp", "marp.id", "mcsp.member_access_role_policy_id", ) .whereRef("mcsp.membership_club_id", "=", "mc.id") .select([ "mas.id", "mas.name", "mas.ext_account_status_id", "marp.city_stopovers", "marp.hot_deals", "marp.karma_alliance", "marp.karma_alliance_offers", "karma_getaway", "marp.karma_getaway_offers", "marp.karma_nomad", "marp.karma_registry_collection", "marp.login", "marp.member_privileges_travel", "marp.profile", "marp.rci_exchange", "marp.rci_rental", "marp.reciprocal_partners", "marp.twoforone", "marp.internal_properties", "marp.curated_events", "marp.restrictions", ]), ).as("statuses"), ]) .executeTakeFirstOrThrow(); return result; } catch (err) { logError(err); throw new Error("Failed to fetch club"); } }; const UpdateEntry = async ( id: string, entry: { name?: string; club_id?: string; note?: string; account_types?: { id: string }[]; charge_centers?: { id: string }[]; statuses?: { id: string; access_policies: TMemberAccessPolicy; }[]; }, ) => { const trx = await repo.datastore.startTransaction().execute(); try { const existingClubRecord = await trx .selectFrom("membership_clubs") .select(["id"]) .where("id", "=", id) .executeTakeFirstOrThrow(); if (entry.account_types) { await trx .deleteFrom("membership_account_type_clubs") .where( "membership_account_type_clubs.membership_account_type_id", "not in", entry.account_types.map((a) => a.id), ) .execute(); for (const accountType of entry.account_types) { await trx .insertInto("membership_account_type_clubs") .values({ membership_account_type_id: accountType.id, membership_club_id: id, }) .onConflict((oc) => oc.doNothing()) .returningAll() .executeTakeFirst(); } } const updatedEntry = await trx .updateTable("membership_clubs") .set(() => { const updates: Record = { updated_at: new Date().toISOString().replace(/\.\d{3}Z$/, "Z"), }; if (entry.club_id) updates.club_id = entry.club_id; if (entry.name) updates.name = entry.name; if (entry.note) updates.note = entry.note; return updates; }) .where("id", "=", existingClubRecord.id) .returningAll() .executeTakeFirstOrThrow(); // Status policy handling if (entry.statuses) { const clubStatusPolicies = await trx .selectFrom("membership_club_status_policies") .selectAll() .where( "membership_club_status_policies.member_account_status_id", "in", entry.statuses.map((s) => s.id), ) .where( "membership_club_status_policies.membership_club_id", "=", existingClubRecord.id, ) .execute(); if (clubStatusPolicies.length > 0) { const removedClubStatusPolicies = await trx .deleteFrom("membership_club_status_policies") .where( "membership_club_status_policies.id", "not in", clubStatusPolicies.map((e) => e.id), ) .returningAll() .execute(); if (removedClubStatusPolicies.length > 0) { await trx .deleteFrom("member_access_role_policies") .where( "id", "in", removedClubStatusPolicies.map( (csp) => csp.member_access_role_policy_id, ), ) .execute(); } } for (const { id, access_policies } of entry.statuses) { const status = clubStatusPolicies.find( (e) => e.member_account_status_id === id, ); const newPolicy = await trx .insertInto("member_access_role_policies") .values({ ...(status && { id: status.member_access_role_policy_id }), city_stopovers: access_policies.city_stopovers, curated_events: access_policies.curated_events, internal_properties: access_policies.internal_properties, karma_alliance: access_policies.karma_alliance, karma_alliance_offers: access_policies.karma_alliance_offers, karma_getaway: access_policies.karma_getaway, karma_getaway_offers: access_policies.karma_getaway_offers, karma_nomad: access_policies.karma_nomad, karma_registry_collection: access_policies.karma_registry_collection, login: access_policies.login, member_privileges_travel: access_policies.member_privileges_travel, profile: access_policies.profile, rci_exchange: access_policies.rci_exchange, rci_rental: access_policies.rci_rental, hot_deals: access_policies.hot_deals, reciprocal_partners: access_policies.reciprocal_partners, twoforone: access_policies.twoforone, // @ts-ignore restrictions: access_policies.restrictions ? access_policies.restrictions : null, }) .onConflict((oc) => oc.column("id").doUpdateSet((eb) => ({ city_stopovers: eb.ref("excluded.city_stopovers"), curated_events: eb.ref("excluded.curated_events"), internal_properties: eb.ref("excluded.internal_properties"), karma_alliance: eb.ref("excluded.karma_alliance"), karma_alliance_offers: eb.ref("excluded.karma_alliance_offers"), karma_getaway: eb.ref("excluded.karma_getaway"), karma_getaway_offers: eb.ref("excluded.karma_getaway_offers"), karma_nomad: eb.ref("excluded.karma_nomad"), karma_registry_collection: eb.ref( "excluded.karma_registry_collection", ), login: eb.ref("excluded.login"), member_privileges_travel: eb.ref( "excluded.member_privileges_travel", ), profile: eb.ref("excluded.profile"), rci_exchange: eb.ref("excluded.rci_exchange"), rci_rental: eb.ref("excluded.rci_rental"), reciprocal_partners: eb.ref("excluded.reciprocal_partners"), twoforone: eb.ref("excluded.twoforone"), hot_deals: eb.ref("excluded.hot_deals"), restrictions: eb.ref("excluded.restrictions"), })), ) .returning(["id"]) .executeTakeFirstOrThrow(); await trx .insertInto("membership_club_status_policies") .values({ membership_club_id: existingClubRecord.id, member_account_status_id: id, member_access_role_policy_id: newPolicy.id, }) .onConflict((oc) => oc.doNothing()) .returningAll() .executeTakeFirst(); } } await trx.commit().execute(); return updatedEntry; } catch (err) { await trx.rollback().execute(); logError(err); throw new Error("Failed to update club"); } }; const RemoveClubByID = async (id: string) => { const trx = await repo.datastore.startTransaction().execute(); try { const clubStatuses = await trx .deleteFrom("membership_club_status_policies") .where("membership_club_status_policies.membership_club_id", "=", id) .returningAll() .execute(); await trx .deleteFrom("member_access_role_policies") .where( "id", "in", clubStatuses.map((e) => e.member_access_role_policy_id), ) .execute(); await trx .deleteFrom("membership_clubs") .where("id", "=", id) .executeTakeFirstOrThrow(); await trx.commit().execute(); } catch (err) { await trx.rollback().execute(); logError(err); throw new Error("Failed to remove club"); } }; const FindClubStatusPolicy = async ( id: string, ex_account_status_id: string, ) => { const trx = await repo.datastore.startTransaction().execute(); try { const club = await trx .selectFrom("membership_clubs") .where("membership_clubs.id", "=", id) .selectAll() .executeTakeFirstOrThrow(); const accountStatus = await trx .selectFrom("member_account_statuses") .where( "member_account_statuses.ext_account_status_id", "=", ex_account_status_id, ) .selectAll() .executeTakeFirstOrThrow(); const clubStatusPolicy = await trx .selectFrom("membership_club_status_policies") .where( "membership_club_status_policies.member_account_status_id", "=", accountStatus.id, ) .where( "membership_club_status_policies.membership_club_id", "=", club.id, ) .selectAll() .executeTakeFirstOrThrow(); const policy = await trx .selectFrom("member_access_role_policies as marp") .where("id", "=", clubStatusPolicy.member_access_role_policy_id) .select([ "marp.city_stopovers", "marp.hot_deals", "marp.karma_alliance", "marp.karma_alliance_offers", "karma_getaway", "marp.karma_getaway_offers", "marp.karma_nomad", "marp.karma_registry_collection", "marp.login", "marp.member_privileges_travel", "marp.profile", "marp.rci_exchange", "marp.rci_rental", "marp.reciprocal_partners", "marp.twoforone", "marp.internal_properties", "marp.curated_events", "marp.restrictions", ]) .executeTakeFirstOrThrow(); await trx.commit().execute(); return { id: club.id, name: club.name, club_id: club.club_id, status: { id: accountStatus.id, name: accountStatus.name, ext_account_status_id: accountStatus.ext_account_status_id, ...policy, }, }; } catch (err) { await trx.rollback().execute(); logError(err); throw new Error("Failed to find club status policy"); } }; const FindClubStatusPolicyByClubID = async ( clubID: string, ex_account_status_id: string, ) => { const trx = await repo.datastore.startTransaction().execute(); try { const club = await trx .selectFrom("membership_clubs") .where("membership_clubs.club_id", "=", clubID) .selectAll() .executeTakeFirstOrThrow(); const accountStatus = await trx .selectFrom("member_account_statuses") .where( "member_account_statuses.ext_account_status_id", "=", ex_account_status_id, ) .selectAll() .executeTakeFirstOrThrow(); const clubStatusPolicy = await trx .selectFrom("membership_club_status_policies") .where( "membership_club_status_policies.member_account_status_id", "=", accountStatus.id, ) .where( "membership_club_status_policies.membership_club_id", "=", club.id, ) .selectAll() .executeTakeFirstOrThrow(); const policy = await trx .selectFrom("member_access_role_policies as marp") .where("id", "=", clubStatusPolicy.member_access_role_policy_id) .select([ "marp.city_stopovers", "marp.hot_deals", "marp.karma_alliance", "marp.karma_alliance_offers", "karma_getaway", "marp.karma_getaway_offers", "marp.karma_nomad", "marp.karma_registry_collection", "marp.login", "marp.member_privileges_travel", "marp.profile", "marp.rci_exchange", "marp.rci_rental", "marp.reciprocal_partners", "marp.twoforone", "marp.internal_properties", "marp.curated_events", "marp.restrictions", ]) .executeTakeFirstOrThrow(); await trx.commit().execute(); return { id: club.id, name: club.name, club_id: club.club_id, status: { id: accountStatus.id, name: accountStatus.name, ext_account_status_id: accountStatus.ext_account_status_id, ...policy, }, }; } catch (err) { await trx.rollback().execute(); logError(err); throw new Error("Failed to find club status policy"); } }; return { CreateEntry, FindEntries, FindEntry, UpdateEntry, RemoveClubByID, FindClubStatusPolicy, FindClubStatusPolicyByClubID, }; }; export type TMembershipClubRepository = ReturnType< typeof MembershipClubRepository >;