const db = require('../db/models');

const { Op } = db.Sequelize;

const PROVIDER_LABELS = {
  booking_com: 'Booking.com',
  expedia: 'Expedia',
  airbnb: 'Airbnb',
  agoda: 'Agoda',
  other: 'Other OTA',
};

const RESERVATION_STATUSES = [
  'pending',
  'confirmed',
  'checked_in',
  'checked_out',
  'cancelled',
  'no_show',
];

const isUuid = (value) =>
  typeof value === 'string' &&
  /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i.test(value);

const createHttpError = (message, code = 400) => {
  const error = new Error(message);
  error.code = code;
  return error;
};

const stringValue = (value) => {
  if (value === undefined || value === null) return null;
  const normalized = String(value).trim();
  return normalized || null;
};

const numberValue = (value, fallback = 0) => {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : fallback;
};

const dateValue = (value) => {
  if (!value) return null;
  const date = new Date(value);
  if (Number.isNaN(date.getTime())) return null;
  return date;
};

const addDays = (date, days) => {
  const next = new Date(date);
  next.setDate(next.getDate() + days);
  return next;
};

const eachDate = (startDate, days) =>
  Array.from({ length: days }, (_, index) => addDays(startDate, index).toISOString().slice(0, 10));

const getPath = (source, path) => {
  if (!source || !path) return undefined;

  return path.split('.').reduce((current, key) => {
    if (current === undefined || current === null) return undefined;
    return current[key];
  }, source);
};

const firstDefined = (source, paths) => {
  for (const path of paths) {
    const value = getPath(source, path);
    if (value !== undefined && value !== null && value !== '') return value;
  }

  return undefined;
};

const splitName = (rawName) => {
  const name = stringValue(rawName);
  if (!name) return { firstName: 'OTA', lastName: 'Guest' };

  const parts = name.split(/\s+/).filter(Boolean);

  if (parts.length === 1) return { firstName: parts[0], lastName: 'Guest' };

  return {
    firstName: parts.slice(0, -1).join(' '),
    lastName: parts.slice(-1).join(' '),
  };
};

const normalizeProviderLabel = (provider) => PROVIDER_LABELS[provider] || 'OTA';

const normalizeStatus = (value) => {
  const status = stringValue(value)?.toLowerCase().replace(/[\s-]+/g, '_');

  if (!status) return 'confirmed';
  if (RESERVATION_STATUSES.includes(status)) return status;

  if (['booked', 'booking', 'accepted', 'active', 'reserved', 'commit'].includes(status)) {
    return 'confirmed';
  }

  if (['modified', 'amended', 'updated'].includes(status)) return 'confirmed';
  if (['canceled', 'cancelled_by_guest', 'cancelled_by_host', 'void'].includes(status)) return 'cancelled';
  if (['noshow', 'no_showed'].includes(status)) return 'no_show';

  return 'confirmed';
};

const normalizeExternalReservation = (rawReservation, connection) => {
  const externalReservationId = stringValue(
    firstDefined(rawReservation, [
      'externalReservationId',
      'external_reservation_id',
      'reservationId',
      'reservation_id',
      'confirmationCode',
      'confirmation_code',
      'confirmation',
      'itineraryId',
      'itinerary_id',
      'bookingId',
      'booking_id',
      'id',
    ]),
  );

  if (!externalReservationId) {
    throw createHttpError('OTA reservation import failed: external reservation id is required.');
  }

  const checkIn = dateValue(
    firstDefined(rawReservation, [
      'checkIn',
      'check_in',
      'check_in_at',
      'arrivalDate',
      'arrival_date',
      'startDate',
      'start_date',
      'stayDateRange.start',
      'dates.start',
      'dates.start_date',
    ]),
  );

  const checkOut = dateValue(
    firstDefined(rawReservation, [
      'checkOut',
      'check_out',
      'check_out_at',
      'departureDate',
      'departure_date',
      'endDate',
      'end_date',
      'stayDateRange.end',
      'dates.end',
      'dates.end_date',
    ]),
  );

  if (!checkIn || !checkOut || checkOut <= checkIn) {
    throw createHttpError(
      `OTA reservation ${externalReservationId} import failed: valid check-in and check-out dates are required.`,
    );
  }

  const fullName = firstDefined(rawReservation, [
    'guestName',
    'guest_name',
    'guest.name',
    'guest.fullName',
    'guest.full_name',
    'booker.name',
    'primaryGuest.name',
  ]);
  const splitGuestName = splitName(fullName);

  const firstName = stringValue(
    firstDefined(rawReservation, [
      'guestFirstName',
      'guest_first_name',
      'guest.firstName',
      'guest.first_name',
      'booker.firstName',
      'booker.first_name',
      'primaryGuest.firstName',
      'primaryGuest.first_name',
    ]),
  ) || splitGuestName.firstName;

  const lastName = stringValue(
    firstDefined(rawReservation, [
      'guestLastName',
      'guest_last_name',
      'guest.lastName',
      'guest.last_name',
      'booker.lastName',
      'booker.last_name',
      'primaryGuest.lastName',
      'primaryGuest.last_name',
    ]),
  ) || splitGuestName.lastName;

  return {
    externalReservationId,
    reservationNumber:
      stringValue(firstDefined(rawReservation, ['reservationNumber', 'reservation_number', 'code', 'reference'])) ||
      `${connection.provider || 'OTA'}-${externalReservationId}`,
    status: normalizeStatus(firstDefined(rawReservation, ['status', 'bookingStatus', 'booking_status', 'state'])),
    checkIn,
    checkOut,
    adults: Math.max(1, numberValue(firstDefined(rawReservation, ['adults', 'adultCount', 'adult_count', 'guestCounts.adults']), 1)),
    children: Math.max(0, numberValue(firstDefined(rawReservation, ['children', 'childCount', 'child_count', 'guestCounts.children']), 0)),
    totalAmount: numberValue(firstDefined(rawReservation, ['totalAmount', 'total_amount', 'amount', 'price', 'totalPrice.amount', 'pricing.total', 'financials.total']), 0),
    taxAmount: numberValue(firstDefined(rawReservation, ['taxAmount', 'tax_amount', 'tax', 'totalPrice.tax', 'pricing.tax']), 0),
    depositAmount: numberValue(firstDefined(rawReservation, ['depositAmount', 'deposit_amount', 'deposit', 'prepaidAmount']), 0),
    specialRequests: stringValue(firstDefined(rawReservation, ['specialRequests', 'special_requests', 'notes', 'guest.notes'])),
    cancellationReason: stringValue(firstDefined(rawReservation, ['cancellationReason', 'cancellation_reason', 'cancelReason'])),
    guest: {
      externalGuestId: stringValue(firstDefined(rawReservation, ['guest.id', 'guestId', 'guest_id', 'booker.id', 'primaryGuest.id'])),
      firstName,
      lastName,
      email: stringValue(firstDefined(rawReservation, ['guest.email', 'booker.email', 'primaryGuest.email', 'email'])),
      phone: stringValue(firstDefined(rawReservation, ['guest.phone', 'booker.phone', 'primaryGuest.phone', 'phone'])),
      country: stringValue(firstDefined(rawReservation, ['guest.country', 'booker.country', 'primaryGuest.country', 'country'])),
    },
    room: {
      id: stringValue(firstDefined(rawReservation, ['roomId', 'room_id', 'room.id'])),
      number: stringValue(firstDefined(rawReservation, ['roomNumber', 'room_number', 'room.number', 'unitName', 'unit_name', 'listingId', 'listing_id'])),
    },
    roomType: {
      id: stringValue(firstDefined(rawReservation, ['roomTypeId', 'room_type_id', 'roomType.id', 'room_type.id'])),
      name: stringValue(firstDefined(rawReservation, ['roomTypeName', 'room_type_name', 'roomType.name', 'room_type.name', 'unitTypeName'])),
    },
    ratePlanId: stringValue(firstDefined(rawReservation, ['ratePlanId', 'rate_plan_id', 'ratePlan.id', 'rate_plan.id'])),
    hotelId: stringValue(firstDefined(rawReservation, ['hotelId', 'hotel_id', 'hotel.id'])),
    raw: rawReservation,
  };
};

const normalizeInboundReservations = (payload) => {
  if (!payload) return [];
  if (Array.isArray(payload)) return payload;
  if (Array.isArray(payload.reservations)) return payload.reservations;
  if (Array.isArray(payload.bookings)) return payload.bookings;
  if (Array.isArray(payload.data)) return payload.data;
  if (payload.reservation) return [payload.reservation];
  if (payload.booking) return [payload.booking];
  return [];
};

const currentOrganizationId = (currentUser, connection) =>
  currentUser?.organizationsId ||
  currentUser?.organizationId ||
  currentUser?.organizations?.id ||
  connection.organizationsId ||
  null;

const scopedWhere = (baseWhere, connection, currentUser) => {
  const where = { ...baseWhere };
  const organizationId = currentOrganizationId(currentUser, connection);

  if (connection.hotelId) where.hotelId = connection.hotelId;
  if (organizationId) where.organizationsId = organizationId;

  return where;
};

const getConnection = async (connectionId, transaction) => {
  if (!isUuid(connectionId)) throw createHttpError('Valid OTA connection id is required.');

  const connection = await db.ota_connections.findByPk(connectionId, { transaction });

  if (!connection) throw createHttpError('OTA connection was not found.', 404);

  return connection;
};

const ensureSalesChannel = async (connection, currentUser, transaction) => {
  if (connection.sales_channelId) {
    const existingChannel = await db.sales_channels.findByPk(connection.sales_channelId, { transaction });
    if (existingChannel) return existingChannel;
  }

  const providerName = normalizeProviderLabel(connection.provider);
  const externalReference = `ota:${connection.provider || 'other'}:${connection.account_identifier || connection.id}`;
  const organizationId = currentOrganizationId(currentUser, connection);
  const where = { external_reference: externalReference };

  if (organizationId) where.organizationsId = organizationId;

  let channel = await db.sales_channels.findOne({ where, transaction });

  if (!channel) {
    channel = await db.sales_channels.create(
      {
        channel_type: 'ota',
        name: providerName,
        external_reference: externalReference,
        active: true,
        hotelId: connection.hotelId || null,
        organizationsId: organizationId,
        createdById: currentUser?.id || null,
        updatedById: currentUser?.id || null,
      },
      { transaction },
    );
  }

  await connection.update({ sales_channelId: channel.id, updatedById: currentUser?.id || null }, { transaction });

  return channel;
};

const ensureGuest = async (reservation, connection, currentUser, transaction) => {
  const organizationId = currentOrganizationId(currentUser, connection);
  const guestImportHash = `ota-guest:${connection.id}:${reservation.guest.externalGuestId || reservation.guest.email || reservation.externalReservationId}`;
  const guestWhere = reservation.guest.email
    ? {
        [Op.or]: [{ importHash: guestImportHash }, { email: reservation.guest.email }],
      }
    : { importHash: guestImportHash };

  let guest = await db.guests.findOne({ where: guestWhere, transaction });
  const guestPayload = {
    first_name: reservation.guest.firstName,
    last_name: reservation.guest.lastName,
    email: reservation.guest.email,
    phone: reservation.guest.phone,
    country: reservation.guest.country,
    importHash: guestImportHash,
    hotelId: reservation.hotelId || connection.hotelId || null,
    organizationsId: organizationId,
    updatedById: currentUser?.id || null,
  };

  if (guest) {
    await guest.update(guestPayload, { transaction });
    return guest;
  }

  guest = await db.guests.create(
    {
      ...guestPayload,
      createdById: currentUser?.id || null,
    },
    { transaction },
  );

  return guest;
};

const findRoomType = async (reservation, connection, currentUser, transaction) => {
  if (isUuid(reservation.roomType.id)) {
    const roomType = await db.room_types.findByPk(reservation.roomType.id, { transaction });
    if (roomType) return roomType;
  }

  if (!reservation.roomType.name) return null;

  return db.room_types.findOne({
    where: scopedWhere(
      {
        name: { [Op.iLike]: reservation.roomType.name },
      },
      connection,
      currentUser,
    ),
    transaction,
  });
};

const findExplicitRoom = async (reservation, connection, currentUser, transaction) => {
  if (isUuid(reservation.room.id)) {
    const room = await db.rooms.findByPk(reservation.room.id, { transaction });
    if (room) return room;
  }

  if (!reservation.room.number) return null;

  return db.rooms.findOne({
    where: scopedWhere(
      {
        room_number: reservation.room.number,
      },
      connection,
      currentUser,
    ),
    transaction,
  });
};

const reservationOverlaps = (reservation, checkIn, checkOut) => {
  const existingCheckIn = dateValue(reservation.check_in_at);
  const existingCheckOut = dateValue(reservation.check_out_at);

  if (!existingCheckIn || !existingCheckOut) return false;

  return existingCheckIn < checkOut && existingCheckOut > checkIn;
};

const allocateRoom = async (reservation, roomType, connection, currentUser, transaction) => {
  const explicitRoom = await findExplicitRoom(reservation, connection, currentUser, transaction);
  if (explicitRoom) return explicitRoom;

  const where = scopedWhere(
    {
      active: true,
      status: { [Op.notIn]: ['out_of_service'] },
    },
    connection,
    currentUser,
  );

  if (roomType) where.room_typeId = roomType.id;

  const candidateRooms = await db.rooms.findAll({ where, transaction, order: [['room_number', 'ASC']] });

  if (!candidateRooms.length) return null;

  const roomIds = candidateRooms.map((room) => room.id);
  const overlappingReservations = await db.reservations.findAll({
    where: {
      roomId: { [Op.in]: roomIds },
      status: { [Op.notIn]: ['cancelled', 'no_show', 'checked_out'] },
      check_in_at: { [Op.lt]: reservation.checkOut },
      check_out_at: { [Op.gt]: reservation.checkIn },
    },
    transaction,
  });
  const blockedRoomIds = new Set(
    overlappingReservations
      .filter((existingReservation) => reservationOverlaps(existingReservation, reservation.checkIn, reservation.checkOut))
      .map((existingReservation) => existingReservation.roomId),
  );

  return candidateRooms.find((room) => !blockedRoomIds.has(room.id)) || null;
};

const upsertReservation = async (rawReservation, connection, currentUser, transaction) => {
  const normalized = normalizeExternalReservation(rawReservation, connection);
  const providerLabel = normalizeProviderLabel(connection.provider);
  const importHash = `ota-reservation:${connection.id}:${normalized.externalReservationId}`;
  const salesChannel = await ensureSalesChannel(connection, currentUser, transaction);
  const guest = await ensureGuest(normalized, connection, currentUser, transaction);
  const roomType = await findRoomType(normalized, connection, currentUser, transaction);
  const room = await allocateRoom(normalized, roomType, connection, currentUser, transaction);
  const organizationId = currentOrganizationId(currentUser, connection);
  const existingReservation = await db.reservations.findOne({ where: { importHash }, transaction });
  const status = normalizeStatus(normalized.status);
  const isCancelled = status === 'cancelled';
  const payload = {
    reservation_number: normalized.reservationNumber,
    check_in_at: normalized.checkIn,
    check_out_at: normalized.checkOut,
    adults: normalized.adults,
    children: normalized.children,
    status,
    total_amount: normalized.totalAmount,
    tax_amount: normalized.taxAmount,
    deposit_amount: normalized.depositAmount,
    special_requests: normalized.specialRequests,
    internal_notes: `Imported from ${providerLabel}. External reservation: ${normalized.externalReservationId}.`,
    cancelled_at: isCancelled ? new Date() : null,
    cancellation_reason: isCancelled ? normalized.cancellationReason || `Cancelled by ${providerLabel}` : null,
    importHash,
    hotelId: normalized.hotelId || connection.hotelId || null,
    guestId: guest?.id || null,
    roomId: room?.id || null,
    room_typeId: roomType?.id || room?.room_typeId || null,
    rate_planId: isUuid(normalized.ratePlanId) ? normalized.ratePlanId : null,
    sales_channelId: salesChannel?.id || null,
    organizationsId: organizationId,
    updatedById: currentUser?.id || null,
  };

  let reservationRecord;
  let action = 'created';

  if (existingReservation) {
    await existingReservation.update(payload, { transaction });
    reservationRecord = existingReservation;
    action = 'updated';
  } else {
    reservationRecord = await db.reservations.create(
      {
        ...payload,
        createdById: currentUser?.id || null,
      },
      { transaction },
    );
  }

  if (status === 'checked_in' && room) {
    await room.update({ status: 'occupied', updatedById: currentUser?.id || null }, { transaction });
  }

  return {
    action,
    status,
    id: reservationRecord.id,
    reservation_number: reservationRecord.reservation_number,
    room_id: room?.id || null,
    room_number: room?.room_number || null,
    guest_id: guest?.id || null,
  };
};

const importReservations = async (connection, payload, currentUser, transaction) => {
  const inboundReservations = normalizeInboundReservations(payload);
  const results = {
    received: inboundReservations.length,
    created: 0,
    updated: 0,
    cancelled: 0,
    skipped: 0,
    errors: [],
    reservations: [],
  };

  for (const rawReservation of inboundReservations) {
    try {
      const result = await upsertReservation(rawReservation, connection, currentUser, transaction);
      results.reservations.push(result);

      if (result.action === 'created') results.created += 1;
      if (result.action === 'updated') results.updated += 1;
      if (result.status === 'cancelled') results.cancelled += 1;
    } catch (error) {
      console.error('OTA reservation import failed', error);
      results.skipped += 1;
      results.errors.push(error.message || 'Reservation import failed.');
    }
  }

  return results;
};

const roomStatusExport = (status) => {
  if (status === 'out_of_service') return 'blocked';
  if (status === 'occupied') return 'occupied';
  if (['dirty', 'cleaning'].includes(status)) return 'available_needs_housekeeping';
  return 'available';
};

const buildExportSnapshot = async (connection, payload, currentUser, transaction) => {
  const startDate = dateValue(payload?.startDate || payload?.start_date) || new Date();
  const days = Math.min(Math.max(numberValue(payload?.days, 30), 1), 180);
  const dateRange = eachDate(startDate, days);
  const roomWhere = scopedWhere({ active: true }, connection, currentUser);
  const roomTypeWhere = scopedWhere({ active: true }, connection, currentUser);

  const [rooms, roomTypes, reservations, ratePlans, pricingRules] = await Promise.all([
    db.rooms.findAll({
      where: roomWhere,
      include: [{ model: db.room_types, as: 'room_type' }],
      transaction,
      order: [['room_number', 'ASC']],
    }),
    db.room_types.findAll({ where: roomTypeWhere, transaction, order: [['name', 'ASC']] }),
    db.reservations.findAll({
      where: scopedWhere(
        {
          status: { [Op.notIn]: ['cancelled', 'no_show'] },
          check_in_at: { [Op.lt]: addDays(startDate, days) },
          check_out_at: { [Op.gt]: startDate },
        },
        connection,
        currentUser,
      ),
      transaction,
    }),
    db.rate_plans.findAll({
      where: scopedWhere({ active: true }, connection, currentUser),
      transaction,
      order: [['name', 'ASC']],
    }),
    db.dynamic_pricing_rules.findAll({
      where: scopedWhere({ active: true }, connection, currentUser),
      transaction,
      order: [['name', 'ASC']],
    }),
  ]);

  const activeRooms = rooms.filter((room) => room.status !== 'out_of_service');
  const roomsByType = activeRooms.reduce((accumulator, room) => {
    const roomTypeId = room.room_typeId || 'unassigned';
    accumulator[roomTypeId] = accumulator[roomTypeId] || [];
    accumulator[roomTypeId].push(room);
    return accumulator;
  }, {});

  const inventory = [];

  for (const roomType of roomTypes) {
    const typedRooms = roomsByType[roomType.id] || [];

    for (const date of dateRange) {
      const currentDate = new Date(`${date}T12:00:00Z`);
      const bookedRoomIds = new Set(
        reservations
          .filter(
            (reservation) =>
              reservation.room_typeId === roomType.id &&
              dateValue(reservation.check_in_at) <= currentDate &&
              dateValue(reservation.check_out_at) > currentDate,
          )
          .map((reservation) => reservation.roomId)
          .filter(Boolean),
      );

      inventory.push({
        date,
        room_type_id: roomType.id,
        room_type_name: roomType.name,
        rooms_total: typedRooms.length,
        rooms_booked: bookedRoomIds.size,
        rooms_available: Math.max(typedRooms.length - bookedRoomIds.size, 0),
      });
    }
  }

  return {
    generated_at: new Date().toISOString(),
    provider: connection.provider,
    account_identifier: connection.account_identifier,
    inventory_sync_enabled: Boolean(connection.inventory_sync_enabled),
    rate_sync_enabled: Boolean(connection.rate_sync_enabled),
    rooms: rooms.map((room) => ({
      id: room.id,
      room_number: room.room_number,
      room_type_id: room.room_typeId,
      room_type_name: room.room_type?.name || null,
      status: roomStatusExport(room.status),
      active: room.active,
    })),
    inventory,
    rates: roomTypes.map((roomType) => ({
      room_type_id: roomType.id,
      room_type_name: roomType.name,
      base_rate: numberValue(roomType.base_rate, 0),
      currency: 'USD',
      active_rate_plans: ratePlans.map((ratePlan) => ({
        id: ratePlan.id,
        name: ratePlan.name,
        plan_type: ratePlan.plan_type,
        refundable: ratePlan.refundable,
        deposit_percent: numberValue(ratePlan.deposit_percent, 0),
      })),
      dynamic_pricing_rules: pricingRules
        .filter((rule) => !rule.room_typeId || rule.room_typeId === roomType.id)
        .map((rule) => ({
          id: rule.id,
          name: rule.name,
          rule_type: rule.rule_type,
          adjustment_percent: numberValue(rule.adjustment_percent, 0),
          adjustment_amount: numberValue(rule.adjustment_amount, 0),
        })),
    })),
  };
};

module.exports = class OtaIntegrationService {
  static async syncConnection(connectionId, payload = {}, currentUser) {
    const transaction = await db.sequelize.transaction();

    try {
      const connection = await getConnection(connectionId, transaction);
      const mode = payload.mode || 'full';
      const shouldImport = ['full', 'import', 'reservations'].includes(mode);
      const shouldExport = ['full', 'export', 'inventory', 'rates'].includes(mode);
      const inbound = shouldImport
        ? await importReservations(connection, payload, currentUser, transaction)
        : { received: 0, created: 0, updated: 0, cancelled: 0, skipped: 0, errors: [], reservations: [] };
      const outbound = shouldExport
        ? await buildExportSnapshot(connection, payload, currentUser, transaction)
        : null;
      const nextStatus = inbound.errors.length ? 'warning' : 'ok';

      if (!payload.dryRun) {
        await connection.update(
          {
            last_sync_at: new Date(),
            sync_status: nextStatus,
            updatedById: currentUser?.id || null,
          },
          { transaction },
        );
      }

      if (payload.dryRun) {
        await transaction.rollback();
      } else {
        await transaction.commit();
      }

      return {
        connection: {
          id: connection.id,
          provider: connection.provider,
          account_identifier: connection.account_identifier,
          sync_status: payload.dryRun ? connection.sync_status : nextStatus,
          last_sync_at: payload.dryRun ? connection.last_sync_at : new Date().toISOString(),
        },
        inbound,
        outbound,
        dryRun: Boolean(payload.dryRun),
        message: inbound.errors.length
          ? 'OTA sync completed with warnings. Review skipped reservations.'
          : 'OTA sync completed successfully.',
      };
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }

  static async syncAll(payload = {}, currentUser) {
    const connections = await db.ota_connections.findAll({
      where: {
        [Op.or]: [
          { inventory_sync_enabled: true },
          { rate_sync_enabled: true },
          { sync_status: { [Op.ne]: 'disabled' } },
        ],
      },
      order: [['provider', 'ASC']],
    });
    const results = [];

    for (const connection of connections) {
      try {
        results.push(await this.syncConnection(connection.id, payload, currentUser));
      } catch (error) {
        console.error(`OTA sync failed for connection ${connection.id}`, error);
        await connection.update({ sync_status: 'error', updatedById: currentUser?.id || null });
        results.push({
          connection: {
            id: connection.id,
            provider: connection.provider,
            account_identifier: connection.account_identifier,
            sync_status: 'error',
          },
          error: error.message || 'OTA sync failed.',
        });
      }
    }

    return {
      count: results.length,
      ok: results.filter((result) => !result.error && !result.inbound?.errors?.length).length,
      warnings: results.filter((result) => result.inbound?.errors?.length).length,
      errors: results.filter((result) => result.error).length,
      results,
    };
  }

  static async exportConnection(connectionId, payload = {}, currentUser) {
    const transaction = await db.sequelize.transaction();

    try {
      const connection = await getConnection(connectionId, transaction);
      const snapshot = await buildExportSnapshot(connection, payload, currentUser, transaction);
      await transaction.commit();
      return snapshot;
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }
};
