event-admin-back/src/modules/client/activities/participant-entities/c-pe-service.ts

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 { sql } from "slonik";
import { z } from "zod";

class CPeService {
  async checkPeOwner(userId: string, peId: string) {
    return await selPool.exists(sql.unsafe`
            select
                pe_id
            from
                act.part_entities pe
            where
                pe.owner_id = ${userId}
                and pe.pe_id = ${peId}
        `);
  }

  async getPeWithValues(peId: string) {
    return await selPool.maybeOne(sql.type(
      z.object({
        peId: z.string().uuid(),
        peTypeId: z.string().uuid(),
        peTypeCode: z.string(),
        peTypeName: z.string(),
        eventInstId: z.string().uuid(),
        ownerId: z.string().uuid(),
        name: z.string(),
        fields: z.array(
          CustomFieldWithValue.extend({
            peFfId: z.string().uuid(),
          }),
        ),
      }),
    )`
      select
        pe_id "peId",
        pe_type_id "peTypeId",
        pe_type_code "peTypeCode",
        pe_type_name "peTypeName",
        event_inst_id "eventInstId",
        name,
        owner_id "ownerId",
        fields
      from
        act.pe_with_fields_and_values
      where
        pe_id = ${peId}
    `);
  }

  async getPeWithValidatorsAndValues(peId: string) {
    return await selPool.maybeOne(sql.type(
      z.object({
        peId: z.string().uuid(),
        peTypeId: z.string().uuid(),
        peTypeCode: z.string(),
        peTypeName: z.string(),
        eventInstId: z.string().uuid(),
        ownerId: z.string().uuid(),
        name: z.string(),
        fields: z.array(
          CustomFieldWithValidatorsAndValue.extend({
            peFfId: z.string().uuid(),
          }),
        ),
      }),
    )`
      select
        pe.pe_id "peId",
        pt.pe_type_id "peTypeId",
        pt.code "peTypeCode",
        pt.name "peTypeName",
        pt.event_inst_id "eventInstId",
        pe.owner_id "ownerId",
        pe.name,
        coalesce(f.fields, '[]'::jsonb) as fields
      from
        act.part_entities pe
      left join act.pe_types pt on
        pt.pe_type_id = pe.pe_type_id
      left join lateral (
        select
          jsonb_agg(jsonb_build_object(
            'fieldDefinitionId', cfd.field_definition_id, 
            'peFfId', pff.pe_ff_id, 
            'isCopyUserValue', pff.is_copy_user_value, 
            'code', cfd.code, 'value', pfv.value, 
            'fieldTypeCode', ft.code, 
            'title', coalesce(pff.field_title_override, cfd.title), 
            'mask', cfd.mask, 
            'options', cfd.options,
            'validators', cfwv.validators
          )) as fields
        from
          act.pe_form_fields pff
        left join cf.custom_field_definitions cfd on
          pff.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.pe_field_values pfv on
          pff.pe_ff_id = pfv.pe_ff_id
          and pe.pe_id = pfv.pe_id
        left join cf.custom_fields_with_validators cfwv on
          cfwv.field_definition_id = pff.field_definition_id
        where
          pff.pe_type_id = pe.pe_type_id) f on
        true
      where
        pe.pe_id = ${peId}
    `);
  }

  async getPeForMember(peId: 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,
      }),
    )`
      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
      from
		act.part_entities pe
      left join act.pe_types pt on
        pt.pe_type_id = pe.pe_type_id
      where
        pe.pe_id = ${peId}
  `);
  }

  async getInvites(peId: string) {
    return await selPool.any(sql.type(
      z.object({
        peInviteId: DbSchema.act.peInvites.peInviteId,
        peInviteUuid: DbSchema.act.peInvites.peInviteUuid,
        name: DbSchema.act.peInvites.name,
        limitVal: DbSchema.act.peInvites.limitVal,
        countVal: DbSchema.act.peInvites.countVal,
        expirationDate: DbSchema.act.peInvites.expirationDate,
      }),
    )`
      select
        pe_invite_id "peInviteId",
        pe_invite_uuid "peInviteUuid",
        name,
        limit_val "limitVal",
        count_val "countVal",
        expiration_date "expirationDate"
      from
        act.pe_invites
      where
        pe_id = ${peId}
    `);
  }

  async getMembers(peId: string) {
    return await selPool.any(sql.type(
      z.object({
        peMemberId: z.string().uuid(),
        userId: z.string().uuid(),
        email: z.string().email(),
        fields: z.array(
          CustomFieldWithValue.extend({
            userEfId: z.string().uuid(),
          }),
        ),
      }),
    )`
      select
        pe_member_id "peMemberId",
        user_id "userId",
        email,
        fields
      from
        act.pe_members_with_fields_and_values
      where
        pe_id = ${peId}
    `);
  }

  async getInviteInfo(peInviteUuid: string) {
    return await selPool.maybeOne(sql.type(
      z.object({
        peInviteId: DbSchema.act.peInvites.peInviteId,
        peInviteUuid: DbSchema.act.peInvites.peInviteUuid,
        peId: DbSchema.act.peInvites.peId,
        peName: DbSchema.act.partEntities.name,
        peOwnerId: DbSchema.act.partEntities.ownerId,
        limitVal: DbSchema.act.peInvites.limitVal,
        countVal: DbSchema.act.peInvites.countVal,
        expirationDate: DbSchema.act.peInvites.expirationDate,
      }),
    )`
      select
        i.pe_invite_id "peInviteId",
        i.pe_invite_uuid "peInviteUuid",
        i.pe_id "peId",
        pe."name" "peName",
        pe.owner_id "peOwnerId",
        i.limit_val "limitVal",
        i.count_val "countVal",
        i.expiration_date "expirationDate"
      from
        act.pe_invites i
      join act.part_entities pe on
        pe.pe_id = i.pe_id
      where
        pe_invite_uuid = ${peInviteUuid}
    `);
  }

  async getPeTypeWithFieldsAndUserCopyValues(
    userId: string,
    peTypeCode: string,
    eventId: string,
  ) {
    return await selPool.maybeOne(sql.type(
      z.object({
        peTypeId: DbSchema.act.peTypes.peTypeId,
        code: DbSchema.act.peTypes.code,
        name: DbSchema.act.peTypes.name,
        eventInstId: DbSchema.act.peTypes.eventInstId,
        fields: z.array(
          CustomFieldWithUserCopyValue.extend({ peFfId: z.string() }),
        ),
      }),
    )`
          select
            pt.pe_type_id as "peTypeId",
            pt.code,
            pt.name,
            pt.event_inst_id as "eventInstId",
            coalesce(jsonb_agg(jsonb_build_object(
                'fieldDefinitionId',
                cfwv.field_definition_id,
                'peFfId',
                pff.pe_ff_id,
                'isCopyUserValue',
                pff.is_copy_user_value,
                'code',
                cfwv.code,
                'userCopyValue',
                ufwv.value,
                'fieldTypeCode',
                cfwv.field_type_code,
                'title',
                COALESCE(pff.field_title_override, cfwv.title),
                'mask',
                cfwv.mask,
                'options',
                cfwv.options,
                'validators',
                cfwv.validators
            )) filter (
          where
            cfwv.field_definition_id is not null),
            '[]'::jsonb) as fields
          from
            act.pe_types pt
            --	необходимые поля
          left join act.pe_form_fields pff on
            pff.pe_type_id = pt.pe_type_id
            -- значение из профиля юзера
          left join ev.user_fields_with_values ufwv on
            pff.field_definition_id = ufwv.field_definition_id
            and ufwv.user_id = ${userId}
            and ufwv.event_id = ${eventId}
            and ufwv.value is not null
            -- только если нужно копировать
            and pff.is_copy_user_value = true
          left join cf.custom_fields_with_validators cfwv on
            pff.field_definition_id = cfwv.field_definition_id
          where 
            pt.code = ${peTypeCode}
          group by
            pt.pe_type_id,
            pt.code,
            pt.name,
            pt.event_inst_id
        `);
  }

  async getPeTypeWithFields(peTypeCode: string) {
    return await selPool.maybeOne(sql.type(
      z.object({
        peTypeId: DbSchema.act.peTypes.peTypeId,
        code: DbSchema.act.peTypes.code,
        name: DbSchema.act.peTypes.name,
        eventInstId: DbSchema.act.peTypes.eventInstId,
        fields: z.array(
          CustomFieldWithValidators.extend({ peFfId: z.string() }),
        ),
      }),
    )`
          select
            pt.pe_type_id as "peTypeId",
            pt.code,
            pt.name,
            pt.event_inst_id as "eventInstId",
            coalesce(jsonb_agg(jsonb_build_object(
              'fieldDefinitionId',
              cfwv.field_definition_id,
              'peFfId',
              pff.pe_ff_id,
              'code',
              cfwv.code,
              'fieldTypeCode',
              cfwv.field_type_code,
              'title',
              COALESCE(pff.field_title_override, cfwv.title),
              'mask',
              cfwv.mask,
              'options',
              cfwv.options,
              'validators',
              cfwv.validators
            )) filter (
          where
            cfwv.field_definition_id is not null),
            '[]'::jsonb) as fields
          from
            act.pe_types pt
            --	необходимые поля
          left join act.pe_form_fields pff on
            pff.pe_type_id = pt.pe_type_id 
          left join cf.custom_fields_with_validators cfwv on
            pff.field_definition_id = cfwv.field_definition_id
          where 
            pt.code = ${peTypeCode}
          group by
            pt.pe_type_id,
            pt.code,
            pt.name,
            pt.event_inst_id
        `);
  }
}

export const cPeService = new CPeService();