event-admin-back/src/modules/client/activities/c-act-service.ts

import { apiTypes } from "#api/current-api.js";
import {
  CustomFieldWithUserCopyValue,
  CustomFieldWithValidators,
  CustomFieldWithValidatorsAndValue,
  CustomFieldWithValue,
} from "#api/v_0.1.0/types/custom-fields-types.js";
import { DbSchema } from "#db/db-schema.js";
import { selPool, updPool } from "#db/db.js";
import { ApiError } from "#exceptions/api-error.js";
import { logger } from "#plugins/logger.js";
import { sql } from "slonik";
import { z } from "zod";
import { ordersService } from "../shop/orders-service.js";

class CActService {
  async addDataToActValidator(
    validator: z.infer<typeof apiTypes.activities.ActValidator>,
    activityId: string,
  ): Promise<z.infer<typeof apiTypes.activities.ActValidatorWithData>> {
    switch (validator.code) {
      case "max-regs": {
        const currentRegs = await selPool.oneFirst(sql.type(
          z.object({
            count: z.number(),
          }),
        )`
          select
              count(*)
          from
              act.activity_regs
          where
              activity_id = ${activityId}
        `);

        return {
          ...validator,
          serverData: {
            currentRegs,
          },
        };
      }

      default: {
        return validator;
      }
    }
  }

  async getActRegs(userId: string) {
    const actRegs = await selPool.any(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityId: DbSchema.act.activityRegs.activityId,
        activityCode: DbSchema.act.activities.code,
        activityPublicName: DbSchema.act.activities.publicName,
        peId: DbSchema.act.activityRegs.peId,
        peName: DbSchema.act.partEntities.name.nullable(),
        peOwnerId: DbSchema.act.partEntities.ownerId.nullable(),
        userId: DbSchema.act.activityRegs.userId.nullable(),
        isPaid: DbSchema.act.activityRegs.isPaid,
        isCanceled: DbSchema.act.activityRegs.isCanceled,
        statusHistory: z.array(
          z.object({
            statusHistoryId: DbSchema.act.actRegStatusHistory.statusHistoryId,
            name: DbSchema.act.actRegStatuses.name,
            code: DbSchema.act.actRegStatuses.code,
            note: DbSchema.act.actRegStatusHistory.note,
            setDate: DbSchema.act.actRegStatusHistory.setDate,
            actRegStatusId: DbSchema.act.actRegStatuses.actRegStatusId,
            color: DbSchema.act.actRegStatuses.color,
            isPaymentOpen: DbSchema.act.actRegStatuses.isPaymentOpen,
          }),
        ),
        isUserReg: DbSchema.act.activities.isUserReg,
      }),
    )`
      select
        ar.activity_reg_id "activityRegId",
        ar.activity_id "activityId",
        ar.activity_code "activityCode",
        ar.activity_public_name "activityPublicName",
        ar.pe_id "peId",
        ar.pe_name "peName",
        ar.pe_owner_id "peOwnerId",
        ar.user_id "userId",
        ar.is_paid "isPaid",
        ar.status_history "statusHistory",
        ar.is_user_reg "isUserReg",
        ar.is_canceled "isCanceled"
      from
        act.act_regs_with_status ar
      where
        ar.user_id = ${userId}
      or 
          ar.pe_owner_id = ${userId}
      or 
          EXISTS (
              select 1
              from act.pe_members pm_check
              where pm_check.pe_id = ar.pe_id
              and pm_check.user_id = ${userId}
              and pm_check.is_active = true
          );
    `);
    return actRegs;
  }

  async getActRegWithValues(activityRegId: string) {
    const actReg = await selPool.maybeOne(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityId: DbSchema.act.activityRegs.activityId,
        activityCode: DbSchema.act.activities.code,
        activityPublicName: DbSchema.act.activities.publicName,
        peId: DbSchema.act.activityRegs.peId,
        peName: DbSchema.act.partEntities.name.nullable(),
        peOwnerId: DbSchema.act.partEntities.ownerId.nullable(),
        userId: DbSchema.act.activityRegs.userId.nullable(),
        isPaid: DbSchema.act.activityRegs.isPaid,
        isCanceled: DbSchema.act.activityRegs.isCanceled,
        statusHistory: z.array(
          z.object({
            statusHistoryId: DbSchema.act.actRegStatusHistory.statusHistoryId,
            name: DbSchema.act.actRegStatuses.name,
            code: DbSchema.act.actRegStatuses.code,
            note: DbSchema.act.actRegStatusHistory.note,
            setDate: DbSchema.act.actRegStatusHistory.setDate,
            actRegStatusId: DbSchema.act.actRegStatuses.actRegStatusId,
            color: DbSchema.act.actRegStatuses.color,
            isPaymentOpen: DbSchema.act.actRegStatuses.isPaymentOpen,
          }),
        ),
        fields: z.array(
          CustomFieldWithValue.extend({
            arffId: DbSchema.act.activityRegFormFields.arffId,
            isChangeResetStatus:
              DbSchema.act.activityRegFormFields.isChangeResetStatus,
          }),
        ),
        isUserReg: DbSchema.act.activities.isUserReg,
      }),
    )`
      select
        ar.activity_reg_id "activityRegId",
        ar.activity_id "activityId",
        ar.activity_code "activityCode",
        ar.activity_public_name "activityPublicName",
        ar.pe_id "peId",
        ar.pe_name "peName",
        ar.pe_owner_id "peOwnerId",
        ar.user_id "userId",
        ar.is_paid "isPaid",
        ar.status_history "statusHistory",
        ar.fields "fields",
        ar.is_user_reg "isUserReg",
        ar.is_canceled "isCanceled"
      from
        act.act_regs_with_values ar
      where
        ar.activity_reg_id = ${activityRegId}
    `);
    return actReg;
  }

  async getActRegForPeMember(activityRegId: string) {
    const actReg = await selPool.maybeOne(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityId: DbSchema.act.activityRegs.activityId,
        activityCode: DbSchema.act.activities.code,
        activityPublicName: DbSchema.act.activities.publicName,
        peId: DbSchema.act.activityRegs.peId,
        peName: DbSchema.act.partEntities.name.nullable(),
        peOwnerId: DbSchema.act.partEntities.ownerId.nullable(),
        userId: DbSchema.act.activityRegs.userId.nullable(),
        isPaid: DbSchema.act.activityRegs.isPaid,
        isCanceled: DbSchema.act.activityRegs.isCanceled,
        statusHistory: z.array(
          z.object({
            statusHistoryId: DbSchema.act.actRegStatusHistory.statusHistoryId,
            name: DbSchema.act.actRegStatuses.name,
            code: DbSchema.act.actRegStatuses.code,
            note: DbSchema.act.actRegStatusHistory.note,
            setDate: DbSchema.act.actRegStatusHistory.setDate,
            actRegStatusId: DbSchema.act.actRegStatuses.actRegStatusId,
            color: DbSchema.act.actRegStatuses.color,
            isPaymentOpen: DbSchema.act.actRegStatuses.isPaymentOpen,
          }),
        ),
        isUserReg: DbSchema.act.activities.isUserReg,
      }),
    )`
      select
        ar.activity_reg_id "activityRegId",
        ar.activity_id "activityId",
        ar.activity_code "activityCode",
        ar.activity_public_name "activityPublicName",
        ar.pe_id "peId",
        ar.pe_name "peName",
        ar.pe_owner_id "peOwnerId",
        ar.user_id "userId",
        ar.is_paid "isPaid",
        ar.status_history "statusHistory",
        ar.is_user_reg "isUserReg",
        ar.is_canceled "isCanceled"
      from
        act.act_regs_with_values ar
      where
        ar.activity_reg_id = ${activityRegId}
    `);
    return actReg;
  }

  async getInitialRegStatusId(activityId: string) {
    const initialRegStatusId = await selPool.oneFirst(sql.type(
      z.object({
        initialRegStatusId: DbSchema.act.activities.initialRegStatusId,
      }),
    )`
      select
        initial_reg_status_id "initialRegStatusId"
      from
        act.activities
      where
        activity_id = ${activityId}
    `);
    return initialRegStatusId;
  }

  async getActivity(activityCode: string) {
    return await selPool.maybeOne(sql.type(
      z.object({
        activityId: DbSchema.act.activities.activityId,
        code: DbSchema.act.activities.code,
        publicName: DbSchema.act.activities.publicName,
        eventInstId: DbSchema.act.activities.eventInstId,
        categoryId: DbSchema.act.activities.categoryId,
        isUserReg: DbSchema.act.activities.isUserReg,
        paymentConfig: DbSchema.act.activities.paymentConfig,
        registrationProductId: DbSchema.act.activities.registrationProductId,
        participantProductId: DbSchema.act.activities.participantProductId,
        nextRegStatusIdAfterPayment:
          DbSchema.act.activities.nextRegStatusIdAfterPayment,
      }),
    )`
      select
        a.activity_id "activityId",
        a.code "code",
        a.public_name "publicName",
        a.event_inst_id "eventInstId",
        a.category_id "categoryId",
        a.is_user_reg "isUserReg",
        a.payment_config "paymentConfig",
        a.registration_product_id "registrationProductId",
        a.participant_product_id "participantProductId",
        a.next_reg_status_id_after_payment "nextRegStatusIdAfterPayment"
      from
        act.activities a
      where
        a.code = ${activityCode}
    `);
  }

  async checkActivityRegNumber(activityRegNumber: string) {
    const isExist = await selPool.exists(sql.unsafe`
      select
        number
      from
        act.activity_regs
      where
        number = ${activityRegNumber}
    `);
    return !!isExist;
  }

  async updateActRegPaymentStatus(activityRegId: string) {
    const actReg = await this.getActRegForPeMember(activityRegId);
    if (!actReg) {
      throw ApiError.BadRequest("actRegNotFound", "Не найдена регистрация");
    }

    const activity = await this.getActivity(actReg.activityCode);
    if (!activity) {
      throw ApiError.BadRequest("activityNotFound", "Не найдена активность");
    }

    // оплата за всю регистрацию
    if (activity.paymentConfig === "PER_REGISTRATION") {
      const isPaid = await this.checkActivityRegPayment({
        activityRegId,
      });
      // если надо поменять
      if (actReg.isPaid !== isPaid) {
        if (isPaid) {
          await updPool.query(sql.unsafe`
          update act.activity_regs
          set is_paid = true
          where activity_reg_id = ${activityRegId}
        `);

          await updPool.query(sql.unsafe`
          insert into act.act_reg_status_history (
            activity_reg_id,
            act_reg_status_id,
            note
          )
          values (
            ${activityRegId},
            ${activity.nextRegStatusIdAfterPayment},
            'Оплачено'
          )
        `);
        } else {
          // такого по идее не должно быть
          logger.error("isPaid !== actReg.isPaid", {
            isPaid,
            actReg,
            activity,
          });
        }
      }

      // TODO: QR
    }

    // оплата за каждого участника
    if (activity.paymentConfig === "PER_PARTICIPANT") {
      if (!actReg.peId) {
        throw Error("peId not found");
      }

      const isAllPaid = await this.checkMembersPayment({
        activityRegId,
        peId: actReg.peId,
      });

      // если надо поменять
      if (isAllPaid !== actReg.isPaid) {
        if (!isAllPaid) {
          await updPool.query(sql.unsafe`
          update act.activity_regs
          set is_paid = false
          where activity_reg_id = ${activityRegId}
        `);

          // TODO: Возможно стоит добавить в act.activities поле payment_status_id
          await updPool.query(sql.unsafe`
          insert into act.act_reg_status_history (
            activity_reg_id,
            act_reg_status_id,
            note
          )
          values (
            ${activityRegId},
            'd6d27702-cded-4625-be07-e339c4003c2f',
            'Оплачены не все участники'
          )
        `);
        } else {
          await updPool.query(sql.unsafe`
          update act.activity_regs
          set is_paid = true
          where activity_reg_id = ${activityRegId}
        `);
          await updPool.query(sql.unsafe`
          insert into act.act_reg_status_history (
            activity_reg_id,
            act_reg_status_id,
            note
          )
          values (
            ${activityRegId},
            ${activity.nextRegStatusIdAfterPayment},
            'Оплачены все участники'
          )
        `);
        }
      }
    }
  }

  async checkMembersPayment({
    peId,
    activityRegId,
  }: {
    peId: string;
    activityRegId: string;
  }) {
    const members = await selPool.any(sql.type(
      z.object({
        peMemberId: DbSchema.act.peMembers.peMemberId,
        userId: DbSchema.act.peMembers.userId,
      }),
    )`
      select
        pm.pe_member_id "peMemberId",
        pm.user_id "userId"
      from
        act.pe_members pm
      where
        pm.pe_id = ${peId}
        and pm.is_active = true
    `);

    const memberIds = members.map((member) => member.peMemberId);

    const paidMemberRows = await selPool.any(sql.unsafe`
      select distinct
        oi.pe_member_id  -- Выбираем ID тех, кто заплатил
      from
        shop.order_items oi
      where
        oi.pe_member_id = ANY(${sql.array(memberIds, "uuid")})
        and oi.status = 'PAID'
        and oi.activity_reg_id = ${activityRegId}
    `);

    return memberIds.length === paidMemberRows.length;
  }

  async checkActivityRegPayment({ activityRegId }: { activityRegId: string }) {
    const isPaid = await selPool.exists(sql.unsafe`
      select 1
      from shop.order_items oi
      where oi.activity_reg_id = ${activityRegId}
      and oi.status = 'PAID'
    `);

    return !!isPaid;
  }

  async checkActRegOwner(userId: string, activityRegId: string) {
    const actReg = await this.getActRegWithValues(activityRegId);
    if (!actReg) {
      throw ApiError.BadRequest("actRegNotFound", "Не найдена регистрация");
    }

    if (actReg.peOwnerId === userId || actReg.userId === userId) {
      return "owner";
    }

    const isMemeber = await selPool.exists(sql.unsafe`
      select 1
      from act.pe_members
      where pe_id = ${actReg.peId}
      and user_id = ${userId}
      and is_active = true
    `);

    if (isMemeber) return "member";

    return undefined;
  }

  async checkPeMemberActivityRegPayment({
    peMemberId,
    activityRegId,
  }: {
    peMemberId: string;
    activityRegId: string;
  }) {
    const isPaid = await selPool.exists(sql.unsafe`
      select 1
      from shop.order_items oi
      where oi.pe_member_id = ${peMemberId}
      and oi.activity_reg_id = ${activityRegId}
      and oi.status = 'PAID'
    `);

    return !!isPaid;
  }

  async getActRegDataWithUserCopyValuesAndActValidators(
    userId: string,
    eventId: string,
    activityCode: string,
  ) {
    return await selPool.maybeOne(sql.type(
      z.object({
        activityId: DbSchema.act.activities.activityId,
        code: DbSchema.act.activities.code,
        publicName: DbSchema.act.activities.publicName,
        eventInstId: DbSchema.act.activities.eventInstId,
        categoryId: DbSchema.act.activities.categoryId,
        categoryCode: DbSchema.act.activityCategories.code,
        validators: z.array(apiTypes.activities.ActValidator),
        peTypes: z.array(
          z.object({
            peTypeId: DbSchema.act.peTypes.peTypeId,
            code: DbSchema.act.peTypes.code,
            name: DbSchema.act.peTypes.name,
          }),
        ),
        fields: z.array(
          CustomFieldWithUserCopyValue.extend({ arffId: z.string() }),
        ),
        isUserReg: DbSchema.act.activities.isUserReg,
      }),
    )`
        select
          a.activity_id "activityId",
          a.code,
          a.public_name "publicName",
          a.event_inst_id "eventInstId",
          a.category_id "categoryId",
          a.category_code "categoryCode",
          a.validators,
          a.pe_types "peTypes",
          coalesce(f.fields, '[]'::jsonb) "fields",
          a.is_user_reg "isUserReg"
        from
          act.act_with_validators a
        left join lateral (
          select
            jsonb_agg(jsonb_build_object(
              'fieldDefinitionId', f.field_definition_id,
              'arffId', af.arff_id,
              'isCopyUserValue', af.is_copy_user_value,
              'code', f.code,
              'userCopyValue', ufwv.value,
              'fieldTypeCode', f.field_type_code,
              'title', COALESCE(af.field_title_override, f.title),
              'mask', f.mask,
              'options', f."options",
              'validators', f.validators
            )) as fields
          from
            act.activity_reg_form_fields af
          left join cf.custom_fields_with_validators f on
            f.field_definition_id = af.field_definition_id
              -- значение из профиля юзера
          left join ev.user_fields_with_values ufwv on
              af.field_definition_id = ufwv.field_definition_id
              and ufwv.user_id = ${userId}
              and ufwv.event_id = ${eventId}
              and ufwv.value is not null
              -- только если нужно копировать
              and af.is_copy_user_value = true
          where
            af.activity_id = a.activity_id
        ) f on true
          where a.code = ${activityCode}
      `);
  }

  async getActRegDataWithFieldsAndValidatorsAndActValidators(
    activityCode: string,
  ) {
    return await selPool.maybeOne(sql.type(
      z.object({
        activityId: DbSchema.act.activities.activityId,
        code: DbSchema.act.activities.code,
        publicName: DbSchema.act.activities.publicName,
        eventInstId: DbSchema.act.activities.eventInstId,
        categoryId: DbSchema.act.activities.categoryId,
        categoryCode: DbSchema.act.activityCategories.code,
        validators: z.array(apiTypes.activities.ActValidator),
        peTypes: z.array(
          z.object({
            peTypeId: DbSchema.act.peTypes.peTypeId,
            code: DbSchema.act.peTypes.code,
            name: DbSchema.act.peTypes.name,
          }),
        ),
        fields: z.array(
          CustomFieldWithValidators.extend({ arffId: z.string() }),
        ),
        isUserReg: DbSchema.act.activities.isUserReg,
      }),
    )`
            select
              a.activity_id "activityId",
              a.code,
              a.public_name "publicName",
              a.event_inst_id "eventInstId",
              a.category_id "categoryId",
              a.category_code "categoryCode",
              a.validators,
              a.pe_types "peTypes",
              coalesce(f.fields, '[]'::jsonb) "fields",
              a.is_user_reg "isUserReg"
            from
              act.act_with_validators a
            left join lateral (
              select
                jsonb_agg(jsonb_build_object(
                  'fieldDefinitionId', f.field_definition_id,
                  'arffId', af.arff_id,
                  'isCopyUserValue', af.is_copy_user_value,
                  'code', f.code,
                  'fieldTypeCode', f.field_type_code,
                  'title', COALESCE(af.field_title_override, f.title),
                  'mask', f.mask,
                  'options', f."options",
                  'validators', f.validators
                )) as fields
              from
                act.activity_reg_form_fields af
              left join cf.custom_fields_with_validators f on
                f.field_definition_id = af.field_definition_id
              where
                af.activity_id = a.activity_id
            ) f on true
              where a.code = ${activityCode}
          `);
  }

  async getActRegWithFieldsAndValidatorsAndValuesAndActValidators(
    activityRegId: string,
  ) {
    return selPool.maybeOne(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityId: DbSchema.act.activityRegs.activityId,
        activityCode: DbSchema.act.activities.code,
        activityPublicName: DbSchema.act.activities.publicName,
        peId: DbSchema.act.activityRegs.peId,
        peName: DbSchema.act.partEntities.name.nullable(),
        peOwnerId: DbSchema.act.partEntities.ownerId.nullable(),
        userId: DbSchema.act.activityRegs.userId.nullable(),
        isPaid: DbSchema.act.activityRegs.isPaid,
        isCanceled: DbSchema.act.activityRegs.isCanceled,
        validators: z.array(apiTypes.activities.ActValidator),
        statusHistory: z.array(
          z.object({
            statusHistoryId: DbSchema.act.actRegStatusHistory.statusHistoryId,
            name: DbSchema.act.actRegStatuses.name,
            code: DbSchema.act.actRegStatuses.code,
            note: DbSchema.act.actRegStatusHistory.note,
            setDate: DbSchema.act.actRegStatusHistory.setDate,
            actRegStatusId: DbSchema.act.actRegStatuses.actRegStatusId,
            color: DbSchema.act.actRegStatuses.color,
            isPaymentOpen: DbSchema.act.actRegStatuses.isPaymentOpen,
          }),
        ),
        fields: z.array(
          CustomFieldWithValidatorsAndValue.extend({
            arffId: DbSchema.act.activityRegFormFields.arffId,
            isChangeResetStatus:
              DbSchema.act.activityRegFormFields.isChangeResetStatus,
          }),
        ),
        isUserReg: DbSchema.act.activities.isUserReg,
        peTypes: z.array(
          z.object({
            peTypeId: DbSchema.act.peTypes.peTypeId,
            code: DbSchema.act.peTypes.code,
            name: DbSchema.act.peTypes.name,
          }),
        ),
      }),
    )`
      select
        ar.activity_reg_id "activityRegId",
        ar.activity_id "activityId",
        ar.activity_code "activityCode",
        ar.activity_public_name "activityPublicName",
        ar.pe_id "peId",
        ar.pe_name "peName",
        ar.pe_owner_id "peOwnerId",
        ar.user_id "userId",
        ar.is_paid "isPaid",
        ar.is_canceled "isCanceled",
        ar.status_history "statusHistory",
        f.fields "fields",
        awv.validators,
	      ar.is_user_reg "isUserReg",
        awv.pe_types "peTypes"
      from
        act.act_regs_with_status ar
      left join lateral (
        select
          jsonb_agg(jsonb_build_object(
                  'fieldDefinitionId', cfd.field_definition_id, 
                  'arffId', f_1.arff_id, 
                  'isCopyUserValue', f_1.is_copy_user_value, 
                  'code', cfd.code, 
                  'value', afv.value, 
                  'fieldTypeCode', ft.code, 
                  'title', coalesce(f_1.field_title_override, cfd.title), 
                  'mask', cfd.mask, 
                  'options', cfd.options, 
                  'isChangeResetStatus', f_1.is_change_reset_status,
                      'validators', cfwv.validators
                    )) as fields
        from
          act.activity_reg_form_fields f_1
        left join cf.custom_field_definitions cfd on
          f_1.field_definition_id = cfd.field_definition_id
        left join cf.field_types ft on
          cfd.field_type_id = ft.field_type_id
        left join act.ar_field_values afv on
          f_1.arff_id = afv.arff_id
          and afv.activity_reg_id = ar.activity_reg_id
        left join cf.custom_fields_with_validators cfwv on
          cfwv.field_definition_id = cfd.field_definition_id
        where
          f_1.activity_id = ar.activity_id) f on
        true
      left join act.act_with_validators awv on
        awv.activity_id = ar.activity_id
      where ar.activity_reg_id = ${activityRegId}
    `);
  }

  async refundByPeMemberId(peMember: {
    peMemberId: string;
    peId: string;
    userId: string;
  }) {
    const actRegs = await selPool.any(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityId: DbSchema.act.activityRegs.activityId,
      }),
    )`
      select
        r.activity_reg_id "activityRegId",
        r.activity_id "activityId"
      from
        act.activity_regs r
      left join act.activities a on
        a.activity_id = r.activity_id and
        a.payment_config = 'PER_PARTICIPANT'
      where
        pe_id = ${peMember.peId}
    `);

    for (const actReg of actRegs) {
      const orderItem = await selPool.maybeOne(sql.type(
        z.object({
          orderItemId: DbSchema.shop.orderItems.orderItemId,
          orderId: DbSchema.shop.orderItems.orderId,
          productId: DbSchema.shop.orderItems.productId,
          unitPrice: DbSchema.shop.orderItems.unitPrice,
        }),
      )`
      select
        oi.order_item_id "orderItemId",
        oi.order_id "orderId",
        oi.product_id "productId",
        oi.unit_price::float "unitPrice"
      from
        shop.order_items oi
      where
        oi.activity_reg_id = ${actReg.activityRegId} and
        oi.pe_member_id = ${peMember.peMemberId}
    `);

      if (!orderItem) {
        throw new Error("Order item not found");
      }

      await ordersService.refundOrderItem(orderItem.orderItemId);
    }
  }

  async getActivityRegsByPeId(peId: string) {
    return await selPool.any(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityId: DbSchema.act.activityRegs.activityId,
        activityRegNumber: DbSchema.act.activityRegs.number,
      }),
    )`
      select
        r.activity_reg_id "activityRegId",
        r.activity_id "activityId",
        r.number "activityRegNumber"
      from
        act.activity_regs r
      where
        pe_id = ${peId}
    `);
  }

  async refundByActivityRegId(activityRegId: string) {
    const activityId = await selPool.maybeOneFirst(sql.type(
      z.object({
        activityId: DbSchema.act.activityRegs.activityId,
      }),
    )`
      select
        r.activity_id "activityId"
      from
        act.activity_regs r
      left join act.activities a on
        a.activity_id = r.activity_id
      where
        r.activity_reg_id = ${activityRegId}
    `);

    if (!activityId) {
      throw new Error("Activity reg not found");
    }

    const orderItems = await selPool.any(sql.type(
      z.object({
        orderItemId: DbSchema.shop.orderItems.orderItemId,
        orderId: DbSchema.shop.orderItems.orderId,
        paymentId: DbSchema.shop.payments.paymentId,
        paymentExternalTransactionId:
          DbSchema.shop.payments.externalTransactionId,
      }),
    )`
      select
        oi.order_item_id "orderItemId",
        oi.order_id "orderId"
      from
        shop.order_items oi
      where
        oi.activity_reg_id = ${activityRegId}
    `);

    for (const orderItem of orderItems) {
      await ordersService.refundOrderItem(orderItem.orderItemId);
    }
  }
}

export const cActService = new CActService();