parr_api/PARR.DAL/Repositories/TemplateRepository.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Npgsql;
using PARR.Core.Repositories.Interfaces;
using PARR.DAL.Context;
using PARR.DAL.Repositories.Base;
using PARR.Domain.Entities;
using PARR.Domain.Entities.Base.History;
using PARR.Domain.Enums;

namespace PARR.DAL.Repositories
{
    internal class TemplateRepository : BaseRepository<Template>, ITemplateRepository
    {
        public TemplateRepository(DataContext dataContext, ILogger<TemplateRepository> logger) : base(logger, dataContext) { }

        public async Task<Template?> GetTemplateByNameAsync(string name)
        {
            _logger.LogDebug("Поиск шаблона по имени: {TemplateName}", name);

            var template = await GetWithIncludes()
                .Include(t => t.RobotConfigurations)
                .FirstOrDefaultAsync(t => t.Name == name);

            if (template != null)
            {
                _logger.LogDebug("Шаблон найден: {TemplateId}, имя: {TemplateName}", template.Id, template.Name);
            }
            else
            {
                _logger.LogDebug("Шаблон с именем {TemplateName} не найден", name);
            }

            return template;
        }

        public IQueryable<Template> GetWithIncludes()
        {
            _logger.LogDebug("Получаю шаблоны с include связями");

            return Get()
                .Include(h => h.Unit)
                    .ThenInclude(t => t!.UnitValues)
                    .ThenInclude(t => t.Value)
                .Include(h => h.Unit)
                    .ThenInclude(t => t!.UnitValues)
                    .ThenInclude(t => t.Field)
                .Include(a => a.Job)
                    .ThenInclude(t => t!.Tnk)
                    .ThenInclude(s => s!.Subprocess)
                    .ThenInclude(p => p!.Process)
                .Include(t => t.Job)
                    .ThenInclude(t => t!.Group)
                        .ThenInclude(t => t!.GroupType)
                .Include(t => t.Job)
                    .ThenInclude(t => t!.Group)
                        .ThenInclude(t => t.DistributionConfig)
                            .ThenInclude(t => t.DistributionPeriod);
        }

        public override Task<bool> CreateAsync(Template obj)
        {
            _logger.LogDebug("Создание шаблона: {TemplateName}", obj.Name);

            // добавление роботов для шаблона
            obj.RobotConfigurations = new List<RobotConfiguration>
            {
                // робот по управлению шаблоном
                new RobotConfiguration
                {
                    Id = Guid.NewGuid(),
                    DateCreated = DateTimeOffset.UtcNow,
                    TemplateId = obj.Id,
                    RobotCode = (int)RobotsEnum.TemplateOrder,
                    TaskStatusCode = (int)TaskStatusEnum.Creating,
                    RobotStatusCode = (int)RobotStatusEnum.Wait,
                    AttemptsNumber = 0,
                    LastRobotStatusUpdated = null
                },
                // робот по управлениею расписанием
                new RobotConfiguration
                {
                    Id = Guid.NewGuid(),
                    DateCreated = DateTimeOffset.UtcNow,
                    TemplateId = obj.Id,
                    RobotCode = (int)RobotsEnum.ScheduleOrder,
                    TaskStatusCode = (int)TaskStatusEnum.Creating,
                    RobotStatusCode = (int)RobotStatusEnum.Wait,
                    AttemptsNumber = 0,
                    LastRobotStatusUpdated = null
                }

            };

            _logger.LogDebug("Добавлены роботы для шаблона {TemplateName}", obj.Name);

            return base.CreateAsync(obj);
        }


        public async Task<Guid?> ReserveUnusedTemplateAsync(Guid newUnitId, HistoryInitiator initiator)
        {
            _logger.LogDebug("Резервирую неиспользуемый шаблон для UnitId: {UnitId}", newUnitId);

            // Явная транзакция гарантирует атомарность UPDATE + подзапроса
            await using var transaction = await EntityContext.Database.BeginTransactionAsync();

            try
            {
                var sql = @"
            UPDATE ""Templates"" 
            SET ""StatusTypeId"" = @NewStatus,
                ""DateModified"" = @DateModified,
                ""InitiatorIp"" = @InitiatorIp,
                ""InitiatorParrComponentId"" = @InitiatorComponent,
                ""InitiatorComment"" = @InitiatorComment
            WHERE ""Id"" = (
                SELECT t.""Id"" 
                FROM ""Templates"" t
                WHERE t.""StatusTypeId"" = @OldStatus 
                  AND t.""UnitId"" != @NewUnitId
                  AND EXISTS (
                      SELECT 1 FROM ""RobotConfigurations"" rc
                      WHERE rc.""TemplateId"" = t.""Id""
                        AND rc.""RobotCode"" = @RobotCode1
                        AND rc.""TaskStatusCode"" = @TaskStatus
                        AND rc.""RobotStatusCode"" = @RobotStatus
                  )
                  AND EXISTS (
                      SELECT 1 FROM ""RobotConfigurations"" rc
                      WHERE rc.""TemplateId"" = t.""Id""
                        AND rc.""RobotCode"" = @RobotCode2
                        AND rc.""TaskStatusCode"" = @TaskStatus
                        AND rc.""RobotStatusCode"" = @RobotStatus
                  )
                ORDER BY t.""DateModified"" ASC NULLS FIRST
                LIMIT 1
                FOR UPDATE SKIP LOCKED
            )
            RETURNING ""Id"";";

                var parameters = new[]
                {
            new NpgsqlParameter("@NewStatus", (int)TemplateStatusTypeEnum.Updating),
            new NpgsqlParameter("@DateModified", DateTimeOffset.UtcNow),
            new NpgsqlParameter("@InitiatorIp", initiator.InitiatorIp ?? (object)DBNull.Value),
            new NpgsqlParameter("@InitiatorComponent",
                initiator.InitiatorParrComponentId.HasValue
                    ? (object)(int)initiator.InitiatorParrComponentId.Value
                    : DBNull.Value),
            new NpgsqlParameter("@InitiatorComment", initiator.InitiatorComment ?? (object)DBNull.Value),
            new NpgsqlParameter("@OldStatus", (int)TemplateStatusTypeEnum.Unused),
            new NpgsqlParameter("@NewUnitId", newUnitId),
            new NpgsqlParameter("@RobotCode1", (int)RobotsEnum.TemplateOrder),
            new NpgsqlParameter("@RobotCode2", (int)RobotsEnum.ScheduleOrder),
            new NpgsqlParameter("@TaskStatus", (int)TaskStatusEnum.Ok),
            new NpgsqlParameter("@RobotStatus", (int)RobotStatusEnum.Complete)
        };

                var result = await EntityContext.Database
                    .SqlQueryRaw<Guid>(sql, parameters)
                    .ToListAsync();

                await transaction.CommitAsync();

                var reservedTemplateId = result.FirstOrDefault();

                if (reservedTemplateId != Guid.Empty)
                {
                    _logger.LogInformation("Успешно зарезервирован шаблон с ID: {TemplateId} для UnitId: {UnitId}",
                        reservedTemplateId, newUnitId);
                    return reservedTemplateId;
                }

                _logger.LogDebug("Не удалось зарезервировать шаблон для UnitId: {UnitId}", newUnitId);
                return null;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                _logger.LogError(ex, "Ошибка при резервировании шаблона для UnitId: {UnitId}", newUnitId);
                throw;
            }
        }
    }
}