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 } from "#db/db.js";
import { ApiError } from "#exceptions/api-error.js";
import { logger } from "#plugins/logger.js";
import { DatabaseTransactionConnection, sql } from "slonik";
import { z } from "zod";
import { ordersService } from "../shop/orders-service.js";
import { v7 } from "uuid";

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 addDataToActValidators({
    validators,
    activityId,
  }: {
    validators: z.infer<typeof apiTypes.activities.ActValidator>[];
    activityId: string;
  }) {
    return await Promise.all(
      validators.map(
        async (validator) =>
          await cActService.addDataToActValidator(validator, activityId),
      ),
    );
  }

  async getActRegs(userId: string) {
    const actRegsOwner = await selPool.any(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityRegNumber: DbSchema.act.activityRegs.number,
        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.number "activityRegNumber",
        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}
    `);

    const actRegsMember = await selPool.any(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityRegNumber: DbSchema.act.activityRegs.number,
        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.number "activityRegNumber",
        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
          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
          )
    `);

    const actRegsChildrenMember = await selPool.any(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityRegNumber: DbSchema.act.activityRegs.number,
        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 distinct -- DISTINCT нужен для удаления дубликатов, если несколько детей соответствуют условию
  ar.activity_reg_id "activityRegId",
  ar.number "activityRegNumber",
  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", -- ID пользователя из регистрации
  usr.user_id "childUserId", -- ID ребенка, через которого найдена связь
  ar.is_paid "isPaid",
  ar.status_history "statusHistory",
  ar.is_user_reg "isUserReg",
  ar.is_canceled "isCanceled"
from
  act.act_regs_with_status ar
join
  act.pe_members pm on ar.pe_id = pm.pe_id -- Присоединяем участников по pe_id
join
  usr.users usr on pm.user_id = usr.user_id -- Присоединяем пользователей, чтобы проверить, является ли участник ребенком
where
  pm.is_active = true
  and usr.is_child = true
  and usr.parent_id = ${userId}; -- Фильтруем по ID родителя
    `);
    return {
      owner: [...actRegsOwner],
      member: [...actRegsMember],
      childrenMember: [...actRegsChildrenMember],
    };
  }

  async getActRegWithValues(activityRegId: string) {
    const actReg = await selPool.maybeOne(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityRegNumber: DbSchema.act.activityRegs.number,
        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.number "activityRegNumber",
        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,
    tr,
  }: {
    activityRegId: string;
    tr?: DatabaseTransactionConnection;
  }) {
    const db = this.getConnection(tr);
    const actReg = await db.maybeOne(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityRegNumber: DbSchema.act.activityRegs.number,
        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.number "activityRegNumber",
        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.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,
    tr,
  }: {
    activityCode: string;
    tr?: DatabaseTransactionConnection;
  }) {
    const db = this.getConnection(tr);
    return await db.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({
    tr,
    activityRegId,
  }: {
    tr: DatabaseTransactionConnection;
    activityRegId: string;
  }) {
    logger.info(`Обновление статуса оплаты для регистрации ${activityRegId}`);
    const actReg = await this.getActRegForPeMember({ activityRegId, tr });
    if (!actReg) {
      throw ApiError.BadRequest("actRegNotFound", "Не найдена регистрация");
    }

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

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

          await tr.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,
        tr,
      });

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

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

  private getConnection(connection?: DatabaseTransactionConnection) {
    return connection || selPool;
  }

  async checkMembersPayment({
    peId,
    activityRegId,
    tr,
  }: {
    peId: string;
    activityRegId: string;
    tr?: DatabaseTransactionConnection;
  }) {
    const db = this.getConnection(tr);
    const members = await db.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 db.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,
    tr,
  }: {
    activityRegId: string;
    tr?: DatabaseTransactionConnection;
  }) {
    const db = this.getConnection(tr);
    const isPaid = await db.exists(sql.unsafe`
      select 1
      from shop.order_items oi
      where oi.activity_reg_id = ${activityRegId}
      and oi.status = 'PAID'
    `);

    return !!isPaid;
  }

  async checkActRegRole(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";

    const isParent = await selPool.exists(sql.unsafe`
      select 1
      from act.pe_members m
      join usr.users u on
        m.user_id = u.user_id and
        u.parent_id = ${userId} and
        u.is_child = true
      where
        m.pe_id = ${actReg.peId}
        and m.is_active = true
    `);
    if (isParent) return "parent";

    return undefined;
  }

  async getPeForActReg({
    peId,
    activityRegId,
  }: {
    peId: string;
    activityRegId: string;
  }) {
    return await selPool.maybeOne(sql.type(
      z.object({
        peId: DbSchema.act.partEntities.peId,
        peTypeId: DbSchema.act.partEntities.peTypeId,
        peTypeCode: DbSchema.act.peTypes.code,
        peTypeName: DbSchema.act.peTypes.name,
        name: DbSchema.act.partEntities.name,
        eventInstId: DbSchema.act.partEntities.eventInstId,
        ownerId: DbSchema.act.partEntities.ownerId,
        ownerIdentity: z.string(),
        members: z.array(
          z.object({
            peMemberId: DbSchema.act.peMembers.peMemberId,
            userId: DbSchema.act.peMembers.userId,
            identity: z.string(),
            isPaid: z.boolean(),
          }),
        ),
      }),
    )`
      select
        pe.pe_id "peId",
        pt.pe_type_id "peTypeId",
        pt.code "peTypeCode",
        pt.name "peTypeName",
        pe.event_inst_id "eventInstId",
        pe.owner_id "ownerId",
        pe.name,
        ui.identity "ownerIdentity",
        coalesce(m.members, '[]'::jsonb) "members"
      from
		    act.part_entities pe
      left join ev.users_identity ui on
        ui.user_id = pe.owner_id
      left join act.pe_types pt on
        pt.pe_type_id = pe.pe_type_id
      -- members
      left join lateral (
        select
          jsonb_agg(jsonb_build_object(
          'peMemberId', pm.pe_member_id, 
          'userId', pm.user_id, 
          'identity', ui.identity, 
          'isPaid', oi.order_item_id is not null
        )) as members
        from
          act.pe_members pm
        left join ev.users_identity ui on
          ui.user_id = pm.user_id
        -- is_paid
        left join shop.order_items oi on
          oi.pe_member_id = pm.pe_member_id and
          oi.activity_reg_id = ${activityRegId} and
          oi.status = 'PAID'
        where
          pm.pe_id = ${peId} and
          pm.is_active = true
        ) m on true
      where
        pe.pe_id = ${peId}
  `);
  }

  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,
              'orderNumber', af.order_number
            )) 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,
                  'orderNumber', af.order_number
                )) 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 getActRegWithFieldsAndValuesWithValidators(activityRegId: string) {
    return selPool.maybeOne(sql.type(
      z.object({
        activityRegId: DbSchema.act.activityRegs.activityRegId,
        activityRegNumber: DbSchema.act.activityRegs.number,
        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(
          CustomFieldWithValidatorsAndValue.extend({
            arffId: DbSchema.act.activityRegFormFields.arffId,
            isChangeResetStatus:
              DbSchema.act.activityRegFormFields.isChangeResetStatus,
          }),
        ),
        isUserReg: DbSchema.act.activities.isUserReg,
      }),
    )`
      select
        ar.activity_reg_id "activityRegId",
        ar.number "activityRegNumber",
        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",
        coalesce(f.fields "fields", '[]'::jsonb),
	      ar.is_user_reg "isUserReg"
      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,
                  'orderNumber', f_1.order_number
                    )) 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
      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} and
        oi.status = 'PAID'
    `);

      if (!orderItem) {
        logger.error(
          `Order item for activity reg ${actReg.activityRegId} and pe member ${peMember.peMemberId} not found`,
        );
        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,
      }),
    )`
      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);
    }
  }

  async resetAllActivityRegsByPe(
    tr: DatabaseTransactionConnection,
    peid: string,
  ) {
    const actRegs = await this.getActivityRegsByPeId(peid);

    for (const actReg of actRegs) {
      const initialRegStatusId = await this.getInitialRegStatusId(
        actReg.activityId,
      );

      await tr.query(sql.unsafe`
        insert into act.act_reg_status_history
          (act_reg_status_history_id, act_reg_id, act_reg_status_id, note)
        values
          (${v7()}, ${actReg.activityRegId}, ${initialRegStatusId}, 'Изменена сущность участия ${peid}')
      `);
    }
  }

  async getActWithActValidators(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,
          }),
        ),
        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",
              a.is_user_reg "isUserReg"
            from
              act.act_with_validators a
            where a.code = ${activityCode}
          `);
  }
}

export const cActService = new CActService();