using IWS5.Core.Constants;
using IWS5.Core.Enums;
using IWS5.Core.Exceptions;
using IWS5.Core.Models.Request;
using IWS5.DatabaseAccess.Helpers;
using IWS5.DatabaseAccess.Interfaces;
using IWS5.DatabaseAccess.IwsDataContext;
using IWS5.DatabaseAccess.IwsDataModels;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Serilog;
using System.Data;
using IWS5.Core.Models.Processing;

namespace IWS5.DatabaseAccess.Repositories;

public class WorkItemReferenceRepository(
    GreenfieldPd2015Context context,
    ILogger logger)
    : IWSRepository<AtworkItemReference>(context), IWorkItemReferenceRepository
{
    public async Task<List<AtworkItemReference>> GetInboxWorkItems(BaseRequest baseRequest)
    {
        var normalizedUserName = baseRequest.UserName.ToLowerInvariant();

        // Optimized query
        var query = context.AtworkItemReferences
            .AsNoTracking()
            .Where(wir => wir.AssignedTo != null && EF.Functions.Like(wir.AssignedTo.ToLower(), normalizedUserName))
            .Select(x => new AtworkItemReference
            {
                WorkItemReferenceId = x.WorkItemReferenceId,
                WorkItemId = x.WorkItemId,
                QueueId = x.QueueId,
                Queue = x.Queue,
                LastAction = x.LastAction,
                AssignedTo = x.AssignedTo,
                WorkItem = new AtworkItem
                {
                    LockedBy = x.WorkItem.LockedBy,
                    WorkItemName = x.WorkItem.WorkItemName,
                    Created = x.WorkItem.Created,
                    Creator = x.WorkItem.Creator,
                    ProcessId = x.WorkItem.ProcessId,
                    Process = new AtprocessDefinition
                    {
                        ProcessId = x.WorkItem.Process.ProcessId,
                        ProcessName = x.WorkItem.Process.ProcessName,
                        ProcessVersion = x.WorkItem.Process.ProcessVersion
                    }
                }
            });

        var sqlQuery = query.ToQueryString();
        logger.Debug("GetInboxWorkItems SQL query: {SQLQuery}", sqlQuery);

        var workItemReferences = await query.ToListAsync();

        logger.Information("Retrieved {Count} inbox work items for user {UserName}",
            workItemReferences.Count, baseRequest.UserName);

        return workItemReferences;
    }

    public async Task<List<AtworkItemReference>> GetAllowedWorkItems(WorkItemByQueueRequest listWorkItemsByQueueRequest)
    {
        var workItems = await context.AtworkItemReferences
            .AsNoTracking()
            .Where(wiReference =>
                wiReference.Queue.Id == listWorkItemsByQueueRequest.QueueId &&
                wiReference.ServerRequest == 0)
            .OrderBy(wiReference => wiReference.Priority)
            .ThenBy(wiReference => wiReference.WorkItemReferenceId)
            .Select(wiReference => new AtworkItemReference
            {
                WorkItemReferenceId = wiReference.WorkItemReferenceId,
                QueueId = wiReference.QueueId,
                Queue = wiReference.Queue,
                LastAction = wiReference.LastAction,
                WorkItem = new AtworkItem
                {
                    WorkItemId = wiReference.WorkItem.WorkItemId,
                    WorkItemName = wiReference.WorkItem.WorkItemName,
                    LockedBy = wiReference.WorkItem.LockedBy,
                    Created = wiReference.WorkItem.Created,
                    Creator = wiReference.WorkItem.Creator,
                    Atdocuments = wiReference.WorkItem.Atdocuments.Any()
                        ? new List<Atdocument>
                        {
                            new Atdocument
                            {
                                SignedBy = wiReference.WorkItem.Atdocuments
                                    .OrderBy(d => d.DocumentId)
                                    .Select(d => d.SignedBy)
                                    .FirstOrDefault()
                            }
                        }
                        : new List<Atdocument>()
                }
            }).ToListAsync();

        logger.Information("Retrieved {Count} allowed work items for queue {QueueId}",
            workItems.Count, listWorkItemsByQueueRequest.QueueId);

        return workItems;
    }

    public async Task<AtworkItemReference> CreateWorkItemAsync(CreateWorkItemRequest createWorkItemRequest,
        string userName)
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            {
                logger.Information("Starting to create workitem: {WorkItemName}", createWorkItemRequest.WorkItemName);

                var workItem = new AtworkItem
                {
                    WorkItemName = createWorkItemRequest.WorkItemName,
                    Created = DateTime.Now,
                    Creator = userName,
                    Priority = 50,
                    LockedBy = null,
                    ProcessId = createWorkItemRequest.ProcessId
                };

                context.AtworkItems.Add(workItem);
                context.SaveChanges();

                var eventDef = context.AteventDefinitions
                    .FirstOrDefault(eventDef =>
                        eventDef.ProcessId == createWorkItemRequest.ProcessId
                        && eventDef.EventTypeId == (int)ATEventType.Start);

                var workItemReference = new AtworkItemReference
                {
                    WorkItemId = workItem.WorkItemId,
                    Expired = 0, // It hasn't timed out because it is brand new
                    Status = 0, //Always 0, unknown why, maybe it is not used anymore
                    EventId = eventDef.EventId, //
                    ServerRequest = 1, // 1 because it is brand-new work item
                    ServerRequestParam = createWorkItemRequest.ProcessId,
                };

                context.AtworkItemReferences.Add(workItemReference);
                context.SaveChanges();

                foreach (var folderField in createWorkItemRequest.FolderFields)
                {
                    switch (folderField.FolderFieldType)
                    {
                        case FieldInstanceType.Date:
                            var folderFieldDate = new AtfieldInstanceDate
                            {
                                FieldId = folderField.FolderFieldId,
                                FieldName = folderField.FolderFieldName,
                                FieldValue = DateTime.Parse(folderField.FolderFieldValue),
                                WorkItem = workItem,
                                WorkItemId = workItem.WorkItemId
                            };
                            context.AtfieldInstanceDates.Add(folderFieldDate);
                            break;
                        case FieldInstanceType.Number:
                            var folderFieldNumber = new AtfieldInstanceNumber
                            {
                                FieldId = folderField.FolderFieldId,
                                FieldName = folderField.FolderFieldName,
                                FieldValue = decimal.Parse(folderField.FolderFieldValue),
                                WorkItem = workItem,
                                WorkItemId = workItem.WorkItemId
                            };
                            context.AtfieldInstanceNumbers.Add(folderFieldNumber);
                            break;
                        case FieldInstanceType.String:
                            var folderFieldString = new AtfieldInstanceString
                            {
                                FieldId = folderField.FolderFieldId,
                                FieldName = folderField.FolderFieldName,
                                FieldValue = folderField.FolderFieldValue,
                                WorkItem = workItem,
                                WorkItemId = workItem.WorkItemId
                            };
                            context.AtfieldInstanceStrings.Add(folderFieldString);
                            break;
                        case FieldInstanceType.Option:
                            var folderFieldOption = new AtfieldInstanceOption
                            {
                                FieldId = folderField.FolderFieldId,
                                FieldName = folderField.FolderFieldName,
                                FieldValue = folderField.FolderFieldValue,
                                WorkItem = workItem,
                                WorkItemId = workItem.WorkItemId
                            };
                            context.AtfieldInstanceOptions.Add(folderFieldOption);
                            break;
                        default:
                            throw new ArgumentOutOfRangeException(nameof(folderField.FolderFieldType),
                                folderField.FolderFieldType, null);
                    }
                }

                context.SaveChanges();

                foreach (var document in createWorkItemRequest.Documents)
                {
                    var newDocument = new Atdocument
                    {
                        DocumentName = document.DocumentName,
                        Document = Convert.FromBase64String(document.DocumentFile),
                        WorkItemId = workItem.WorkItemId,
                        CabinetId = document.DocumentTemplateId,
                        UserName = userName,
                        ReviewStatusId = 1,
                        LastModified = DateTime.Now,
                        DisplayOrder = document.DisplayOrder ?? 1
                    };
                    context.Atdocuments.Add(newDocument);
                    context.SaveChanges();

                    var template = await context.AtdocumentCabinets
                        .Include(cabinet => cabinet.AtdocumentCabinetDefs)
                        .FirstOrDefaultAsync(cabinet => cabinet.CabinetId == document.DocumentTemplateId);

                    await DocumentTemplateDefTableHelper.InsertRecordIntoTemplateTableAsync(template.CabinetName,
                        newDocument.DocumentId, document.DocumentFields, context);
                }

                foreach (var note in createWorkItemRequest.Notes)
                {
                    var newNote = new Atnote
                    {
                        Note = note.Note,
                        WorkItemId = workItem.WorkItemId,
                        Submitter = userName,
                        SubmitDate = DateTime.Now
                    };
                    context.Atnotes.Add(newNote);
                    context.SaveChanges();
                }

                logger.Information("Successfully created workitem: {WorkItemName}", createWorkItemRequest.WorkItemName);

                await transaction.CommitAsync();
                return workItemReference;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception("Failed to create work item.", ex);
            }
        }
    }

    public async Task<AtworkItemReference> CloneWorkItemAsync(CloneWorkItemRequest cloneWorkItemRequest, string userName, AtworkItemReference originalWorkItemReference)
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            {
                logger.Information("Starting work item clone operation for original work item with WorkItemReferenceId {WorkItemReferenceId}.", cloneWorkItemRequest.WorkItemReferenceId);

                var newWorkItem = new AtworkItem
                {
                    WorkItemName = cloneWorkItemRequest.NewWorkItemName,
                    Creator = userName,
                    Created = DateTime.Now,
                    Priority = originalWorkItemReference.WorkItem.Priority,
                    DueDate = originalWorkItemReference.WorkItem.DueDate,
                    Atcomment = originalWorkItemReference.WorkItem.Atcomment,
                    LockedBy = null,
                    ProcessId = originalWorkItemReference.WorkItem.ProcessId,
                    Expired = originalWorkItemReference.WorkItem.Expired
                };
                context.AtworkItems.Add(newWorkItem);
                context.SaveChanges();

                var queue = await context.Atqueues.FirstOrDefaultAsync(q => q.Id == originalWorkItemReference.QueueId);
                var newWorkItemReference = new AtworkItemReference
                {
                    WorkItemId = newWorkItem.WorkItemId,
                    QueueId = originalWorkItemReference.QueueId,
                    Queue = queue,
                    SourceUser = originalWorkItemReference.SourceUser,
                    SourceQueueId = originalWorkItemReference.SourceQueueId,
                    ArrivalDate = originalWorkItemReference.ArrivalDate,
                    EventId = originalWorkItemReference.EventId,
                    ExpirationTime = originalWorkItemReference.ExpirationTime,
                    Expired = originalWorkItemReference.Expired,
                    Successor = originalWorkItemReference.Successor,
                    ServerRequest = originalWorkItemReference.ServerRequest,
                    ServerRequestParam = originalWorkItemReference.ServerRequestParam,
                    Status = originalWorkItemReference.Status,
                    AssignedTo = originalWorkItemReference.AssignedTo,
                    LastAction = originalWorkItemReference.LastAction,
                    PendEventId = originalWorkItemReference.PendEventId,
                    PendQueueId = originalWorkItemReference.PendQueueId,
                    Draft = originalWorkItemReference.Draft,
                    Priority = originalWorkItemReference.Priority,
                    PendExpirationTime = originalWorkItemReference.PendExpirationTime,
                    LastActionDate = originalWorkItemReference.LastActionDate,
                    LastViewDate = originalWorkItemReference.LastViewDate
                };
                context.AtworkItemReferences.Add(newWorkItemReference);
                context.SaveChanges();

                var folderFieldInstancesDate = await context.AtfieldInstanceDates.Where(fi => fi.WorkItemId == originalWorkItemReference.WorkItemId).ToListAsync();
                foreach (var folderFieldInstance in folderFieldInstancesDate)
                {
                    var newFolderFieldInstance = new AtfieldInstanceDate
                    {
                        FieldId = folderFieldInstance.FieldId,
                        FieldName = folderFieldInstance.FieldName,
                        FieldValue = folderFieldInstance.FieldValue,
                        WorkItem = newWorkItem,
                        WorkItemId = newWorkItem.WorkItemId
                    };
                    context.AtfieldInstanceDates.Add(newFolderFieldInstance);
                }
                var folderFieldInstancesNumber = await context.AtfieldInstanceNumbers.Where(fi => fi.WorkItemId == originalWorkItemReference.WorkItemId).ToListAsync();
                foreach (var folderFieldInstance in folderFieldInstancesNumber)
                {
                    var newFolderField = new AtfieldInstanceNumber
                    {
                        FieldId = folderFieldInstance.FieldId,
                        FieldName = folderFieldInstance.FieldName,
                        FieldValue = folderFieldInstance.FieldValue,
                        WorkItem = newWorkItem,
                        WorkItemId = newWorkItem.WorkItemId
                    };
                    context.AtfieldInstanceNumbers.Add(newFolderField);
                }
                var folderFieldInstancesString = await context.AtfieldInstanceStrings.Where(fi => fi.WorkItemId == originalWorkItemReference.WorkItemId).ToListAsync();
                foreach (var folderFieldInstance in folderFieldInstancesString)
                {
                    var newFolderField = new AtfieldInstanceString
                    {
                        FieldId = folderFieldInstance.FieldId,
                        FieldName = folderFieldInstance.FieldName,
                        FieldValue = folderFieldInstance.FieldValue,
                        WorkItem = newWorkItem,
                        WorkItemId = newWorkItem.WorkItemId
                    };
                    context.AtfieldInstanceStrings.Add(newFolderField);
                }
                var folderFieldInstancesOption = await context.AtfieldInstanceOptions.Where(fi => fi.WorkItemId == originalWorkItemReference.WorkItemId).ToListAsync();
                foreach (var folderFieldInstance in folderFieldInstancesOption)
                {
                    var newFolderField = new AtfieldInstanceOption
                    {
                        FieldId = folderFieldInstance.FieldId,
                        FieldName = folderFieldInstance.FieldName,
                        FieldValue = folderFieldInstance.FieldValue,
                        WorkItem = newWorkItem,
                        WorkItemId = newWorkItem.WorkItemId
                    };
                    context.AtfieldInstanceOptions.Add(newFolderField);
                }
                context.SaveChanges();

                var folderNotes = await context.Atnotes.Where(n => n.WorkItemId == originalWorkItemReference.WorkItemId).ToListAsync();
                foreach (var folderNote in folderNotes)
                {
                    var newNote = new Atnote
                    {
                        WorkItemId = newWorkItem.WorkItemId,
                        Submitter = folderNote.Submitter,
                        SubmitDate = folderNote.SubmitDate,
                        Note = folderNote.Note
                    };
                    context.Atnotes.Add(newNote);
                }
                context.SaveChanges();

                var trackingRecords = await context.AttrackingRecords.Where(tr => tr.WorkItemId == originalWorkItemReference.WorkItemId).ToListAsync();
                foreach (var trackingRecord in trackingRecords)
                {
                    var newTrackingRecord = new AttrackingRecord
                    {
                        TrackingDate = trackingRecord.TrackingDate,
                        WorkItemId = newWorkItem.WorkItemId,
                        UserName = trackingRecord.UserName,
                        TrackingDefinitionId = trackingRecord.TrackingDefinitionId,
                        LastActionId = trackingRecord.LastActionId,
                        Message = trackingRecord.Message
                    };
                    context.AttrackingRecords.Add(newTrackingRecord);
                }
                context.SaveChanges();

                var documents = await context.Atdocuments.Where(d => d.WorkItemId == originalWorkItemReference.WorkItemId).ToListAsync();
                foreach (var document in documents)
                {
                    var newDocument = new Atdocument
                    {
                        DocumentName = document.DocumentName,
                        ReviewStatusId = document.ReviewStatusId,
                        ReviewNumber = document.ReviewNumber,
                        CabinetId = document.CabinetId,
                        WorkItemId = newWorkItem.WorkItemId,
                        DocumentSize = document.DocumentSize,
                        DisplayOrder = document.DisplayOrder,
                        Document = document.Document,
                        DocumentLink = document.DocumentLink,
                        UserName = document.UserName,
                        SignedBy = document.SignedBy,
                        LastModified = document.LastModified
                    };
                    context.Atdocuments.Add(newDocument);
                    context.SaveChanges();

                    var documentNotes = await context.Atnotes.Where(n => n.DocumentId == document.DocumentId).ToListAsync();
                    foreach (var documentNote in documentNotes)
                    {
                        var newNote = new Atnote
                        {
                            DocumentId = newDocument.DocumentId,
                            Submitter = documentNote.Submitter,
                            SubmitDate = documentNote.SubmitDate,
                            Note = documentNote.Note
                        };
                        context.Atnotes.Add(newNote);
                    }
                    context.SaveChanges();

                    var documentTemplate = await context.AtdocumentCabinets.Include(c => c.AtdocumentCabinetDefs).FirstOrDefaultAsync(c => c.CabinetId == document.CabinetId);
                    if (documentTemplate != null)
                    {
                        var documentTemplateTableName = DocumentTemplateTableConstants.GetDocumentTemplateTableName(documentTemplate.CabinetName);
                        var documentTemplateFieldDefs = await context.AtdocumentCabinetDefs.Where(cd => cd.CabinetId == documentTemplate.CabinetId).ToListAsync();
                        var documentTemplateFieldNames = documentTemplateFieldDefs.Select(fd => fd.FieldName);

                        var sqlQuery = $"SELECT {string.Join(", ", documentTemplateFieldNames)} FROM {documentTemplateTableName} WHERE DocumentId = @documentId";
                        var documentTemplateFieldInstances = new Dictionary<string, object>();
                        using (var command = context.Database.GetDbConnection().CreateCommand())
                        {
                            command.Transaction = transaction.GetDbTransaction();
                            command.CommandText = sqlQuery;
                            command.Parameters.Add(new SqlParameter("@documentId", document.DocumentId));

                            await context.Database.OpenConnectionAsync();
                            using (var reader = await command.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    foreach (var documentTemplateFieldName in documentTemplateFieldNames)
                                    {
                                        if (reader[documentTemplateFieldName] != DBNull.Value)
                                        {
                                            documentTemplateFieldInstances[documentTemplateFieldName] = reader[documentTemplateFieldName];
                                        }
                                    }
                                }
                            }
                            await context.Database.CloseConnectionAsync();
                        }

                        var updateDocumentFieldRequests = new List<UpdateDocumentFieldRequest>();
                        foreach (var documentTemplateFieldInstance in documentTemplateFieldInstances)
                        {
                            var documentTemplateFieldDef = documentTemplateFieldDefs.FirstOrDefault(fd => fd.FieldName == documentTemplateFieldInstance.Key);

                            updateDocumentFieldRequests.Add(new UpdateDocumentFieldRequest
                            {
                                FieldName = documentTemplateFieldInstance.Key,
                                FieldType = (ATDocumentFieldType)documentTemplateFieldDef.FieldDataTypeId,
                                FieldValue = documentTemplateFieldInstance.Value.ToString()
                            });
                        }
                        await DocumentTemplateDefTableHelper.InsertRecordIntoTemplateTableAsync(documentTemplate.CabinetName, newDocument.DocumentId, updateDocumentFieldRequests, context);
                    }
                }

                var currentTrackingDefinition = await context.AttrackingDefinitions.FirstOrDefaultAsync(td => td.EventId == queue.EventId);
                if (currentTrackingDefinition != null)
                {
                    var clonedTrackingRecord = new AttrackingRecord
                    {
                        TrackingDate = DateTime.Now,
                        WorkItemId = newWorkItem.WorkItemId,
                        UserName = userName,
                        TrackingDefinitionId = currentTrackingDefinition.Id,
                        Message = cloneWorkItemRequest.CloneTrackingRecordMessage
                    };
                    context.AttrackingRecords.Add(clonedTrackingRecord);
                    context.SaveChanges();
                }

                logger.Information("Successfully cloned work item with WorkItemReferenceId {OldWorkItemReferenceId}. New cloned WorkItemReferenceId: {NewWorkItemReferenceId}.", cloneWorkItemRequest.WorkItemReferenceId, newWorkItemReference.WorkItemReferenceId);

                await transaction.CommitAsync();
                return newWorkItemReference;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception("Failed to clone work item.", ex);
            }
        }
    }

    public async Task<string> LockWorkItem(int workItemId, string userName)
    {
        var workItem = context.AtworkItems.First(wi => wi.WorkItemId == workItemId);

        if (workItem == null)
        {
            return null;
        }

        if (!string.IsNullOrEmpty(workItem.LockedBy))
        {
            return workItem.LockedBy != userName
                ? $"Work item is already locked by another user: {workItem.LockedBy}."
                : $"Work item is already locked by this user: {workItem.LockedBy}.";
        }

        workItem.LockedBy = userName;
        context.AtworkItems.Update(workItem);
        await context.SaveChangesAsync();

        return $"Work item {workItemId} successfully locked by user {userName}.";
    }

    public async Task<AtworkItemReference> ForwardWorkItemAsync(ForwardWorkItemRequest forwardWorkItemRequest,
        string userName)
    {
        var workItemReference = await context.AtworkItemReferences.Include(wiReference => wiReference.WorkItem)
            .FirstOrDefaultAsync(w => w.WorkItemReferenceId == forwardWorkItemRequest.WorkItemReferenceId);

        if (workItemReference == null) { return null; }

        workItemReference.SourceQueueId = workItemReference.QueueId;
        workItemReference.QueueId = null;
        workItemReference.SourceUser = userName;
        workItemReference.ServerRequest = 9; //Ready to move
        workItemReference.ServerRequestParam = 0; //Unknown why it is 0
        workItemReference.LastAction = $"{userName}:{forwardWorkItemRequest.ActionFromDropDown}";
        workItemReference.LastActionDate = DateTime.Now;

        context.AtworkItemReferences.Update(workItemReference);
        context.Entry(workItemReference).State = EntityState.Modified;
        await context.SaveChangesAsync();

        return workItemReference;
    }

    public async Task<AtworkItemReference?> ForwardWorkItemAsync(ForwardWorkItemServerRequest forwardWorkItemRequest)
    {
        AtworkItemReference? reference = await context.AtworkItemReferences
            .SingleOrDefaultAsync(w => w.WorkItemId == forwardWorkItemRequest.WorkItemId);

        if (reference is null)
        {
            return null;
        }
        
        reference.EventId = forwardWorkItemRequest.EventId;
        reference.PendEventId = forwardWorkItemRequest.PendEventId;
        reference.PendQueueId = forwardWorkItemRequest.PendingQueueId ?? null;
        reference.ServerRequest = (int)forwardWorkItemRequest.ServerRequest;
        reference.ServerRequestParam = forwardWorkItemRequest.ServerRequestParam ?? null;
        reference.LastActionDate = DateTime.Now;

        await context.SaveChangesAsync();
        
        return reference;
    }

    public async Task<List<AtworkItemReference>> ForwardWorkItemListAsync(List<AtworkItemReference> atworkItems,
        string action, string userName)
    {
        foreach (var workItemReference in atworkItems)
        {
            workItemReference.SourceQueue = workItemReference.Queue;
            workItemReference.Queue = null;
            workItemReference.SourceUser = userName;
            workItemReference.ServerRequest = 9; //Ready to move
            workItemReference.ServerRequestParam = 0; //Unknown why it is 0
            workItemReference.LastAction = $"{userName}:{action}";
            workItemReference.LastActionDate = DateTime.Now;
            context.AtworkItemReferences.Update(workItemReference);
        }

        return atworkItems;
    }

    public async Task<bool> IsLockedForUserByWorkItemId(int workItemId, string userName)
    {
        var workItem = await context.AtworkItems.AsNoTracking().FirstOrDefaultAsync(w => w.WorkItemId == workItemId);

        if (workItem == null)
        {
            throw new Exception($"WorkItem with work item id {workItemId} was not found.");
        }

        if (string.IsNullOrEmpty(workItem.LockedBy))
        {
            return false;
        }

        return userName != workItem.LockedBy;
    }

    public async Task<bool> IsLockedForUserByWorkItemReferenceId(int workItemReferenceId, string userName)
    {
        var query = context.AtworkItemReferences
            .Where(w => w.WorkItemReferenceId == workItemReferenceId);

        if (!await query.AnyAsync())
        {
            throw new NotFoundException($"WorkItem with work item reference id {workItemReferenceId} was not found.");
        }

        return await query
            .Where(w => !string.IsNullOrEmpty(w.WorkItem.LockedBy) && w.WorkItem.LockedBy != userName)
            .AnyAsync();
    }

    public async Task<bool> UpdateEventId(int workItemId, ATEventType eventType, int processId)
    {
        var startEvent = await context.AteventDefinitions
            .FirstOrDefaultAsync(ed => ed.EventTypeId == (int)eventType && ed.ProcessId == processId);
        var workItemToUpdate = await context.AtworkItems
            .FirstOrDefaultAsync(w => w.WorkItemId == workItemId);
        var workItemReferenceToUpdate = await context.AtworkItemReferences
            .FirstOrDefaultAsync(w => w.WorkItemId == workItemId);

        workItemToUpdate.ProcessId = processId;

        workItemReferenceToUpdate.EventId = startEvent.EventId;
        workItemReferenceToUpdate.QueueId = null;
        workItemReferenceToUpdate.SourceQueueId = null;
        workItemReferenceToUpdate.SourceUser = null;
        workItemReferenceToUpdate.ServerRequest = 9;
        workItemReferenceToUpdate.ServerRequestParam = null;
        workItemReferenceToUpdate.LastActionDate = DateTime.Now;

        var updated = await context.SaveChangesAsync();
        
        return updated > 0;
    }
    
    public async Task<bool> UpdateEventId(int workItemId, int eventId)
    {
        var newQueue = await context.Atqueues
            .FirstOrDefaultAsync(q => q.EventId == eventId);
        var workItemReferenceToUpdate = await context.AtworkItemReferences
            .FirstOrDefaultAsync(w => w.WorkItemId == workItemId);
        
        workItemReferenceToUpdate.EventId = eventId;
        workItemReferenceToUpdate.QueueId = newQueue != null ? newQueue.Id : null;
        workItemReferenceToUpdate.SourceQueueId = null;
        workItemReferenceToUpdate.SourceUser = null;
        workItemReferenceToUpdate.ServerRequest = 9;
        workItemReferenceToUpdate.ServerRequestParam = null;
        workItemReferenceToUpdate.LastActionDate = DateTime.Now;

        var updated = await context.SaveChangesAsync();
        
        return updated > 0;
    }

    public async Task<string> UnlockWorkItem(int workItemReferenceId)
    {
        var workItem = context.AtworkItemReferences.Include(w => w.WorkItem)
            .First(w => w.WorkItemReferenceId == workItemReferenceId).WorkItem;

        if (workItem == null)
        {
            return null;
        }

        workItem.LockedBy = null;
        context.AtworkItems.Update(workItem);
        await context.SaveChangesAsync();

        return $"Work item {workItemReferenceId} successfully unlocked.";
    }

    public async Task<int> DeleteWorkItemsAsync(IEnumerable<int> workItemIds, CancellationToken ct = default)
    {
        var wiIds = workItemIds?.Distinct().ToArray() ?? Array.Empty<int>();
        if (wiIds.Length == 0) return 0;

        var oldAutoDetect = context.ChangeTracker.AutoDetectChangesEnabled;
        context.ChangeTracker.AutoDetectChangesEnabled = false;

        await using var tx = await context.Database.BeginTransactionAsync(ct);
        try
        {
            // Collect docs (+ cabinet names) & refs once
            var docs = await context.Atdocuments
                .AsNoTracking()
                .Where(d => wiIds.Contains(d.WorkItemId))
                .Select(d => new
                {
                    d.DocumentId,
                    d.CabinetId,
                    CabinetName = d.Cabinet != null ? d.Cabinet.CabinetName : null
                })
                .ToListAsync(ct);

            var refIds = await context.AtworkItemReferences
                .AsNoTracking()
                .Where(r => wiIds.Contains(r.WorkItemId))
                .Select(r => r.WorkItemReferenceId)
                .ToListAsync(ct);

            // 0) Dynamic tables (ATD_): delete by DocumentId BEFORE ATDocument
            foreach (var grp in docs
                     .Where(d => !string.IsNullOrWhiteSpace(d.CabinetName))
                     .GroupBy(d => d.CabinetName!))
            {
                var cabinetName = grp.Key.Trim();
                var tableName = DocumentTemplateTableConstants.GetDocumentTemplateTableName(cabinetName); // "ATD_{name}"
                var fqTable = $"[dbo].[{tableName}]";

                var docIds = grp.Select(g => g.DocumentId).Distinct().ToArray();
                await DeleteByInListAsync(context, $"{fqTable}", "DocumentId", docIds, ct);
            }

            // 1) Children of ATWorkItemReference
            if (refIds.Count > 0)
            {
                await DeleteByInListAsync(context, "[dbo].[ATSplit]", "SplitWorkItemRef", refIds, ct);
                await DeleteByInListAsync(context, "[dbo].[ATSplit]", "SourceWorkItemRef", refIds, ct);

                await DeleteByInListAsync(context, "[dbo].[ATTaskInstance]", "WorkItemReferenceID", refIds, ct);
                await DeleteByInListAsync(context, "[dbo].[ATStack]", "WorkItemRef", refIds, ct);
                await DeleteByInListAsync(context, "[dbo].[ATExceptionNote]", "WorkItemReferenceID", refIds, ct);
            }

            // 2) ATNote: by DocumentId then by WorkItemId
            if (docs.Count > 0)
                await DeleteByInListAsync(context, "[dbo].[ATNote]", "DocumentId", docs.Select(d => d.DocumentId), ct);

            await DeleteByInListAsync(context, "[dbo].[ATNote]", "WorkItemId", wiIds, ct);

            // 3) ATDocumentArc then ATDocument
            await DeleteByInListAsync(context, "[dbo].[ATDocumentArc]", "WorkItemId", wiIds, ct);
            await DeleteByInListAsync(context, "[dbo].[ATDocument]", "WorkItemId", wiIds, ct);

            // 4) Field instances
            await DeleteByInListAsync(context, "[dbo].[ATFieldInstanceDate]", "WorkItemID", wiIds, ct);
            await DeleteByInListAsync(context, "[dbo].[ATFieldInstanceNumber]", "WorkItemID", wiIds, ct);
            await DeleteByInListAsync(context, "[dbo].[ATFieldInstanceString]", "WorkItemID", wiIds, ct);
            await DeleteByInListAsync(context, "[dbo].[ATFieldInstanceOption]", "WorkItemID", wiIds, ct);

            // 5) Tracking
            await DeleteByInListAsync(context, "[dbo].[ATTrackingRecordArc]", "WorkItemId", wiIds, ct);
            await DeleteByInListAsync(context, "[dbo].[ATTrackingRecord]", "WorkItemId", wiIds, ct);

            // 6) WorkItemReference
            await DeleteByInListAsync(context, "[dbo].[ATWorkItemReferenceArc]", "WorkItemId", wiIds, ct);
            await DeleteByInListAsync(context, "[dbo].[ATWorkItemReference]", "WorkItemId", wiIds, ct);

            // REDIRECT: WORKITEM_ID is varchar - set null
            await ExecuteUpdateInListAsyncString(
                context,
                """
                UPDATE r SET r.WORKITEM_ID = NULL
                FROM [dbo].[REDIRECT] r
                WHERE r.WORKITEM_ID IN ({0})
                """,
                wiIds.Select(id => id.ToString()),
                ct);

            // POSTXML: set int WorkItemId = NULL
            await ExecuteUpdateInListAsync(
                context,
                """
                UPDATE p SET p.WorkItemId = NULL
                FROM [dbo].[POSTXML] p
                WHERE p.WorkItemId IN ({0})
                """,
                wiIds,
                ct);

            // 7) WorkItemArc then WorkItem
            await DeleteByInListAsync(context, "[dbo].[ATWorkItemArc]", "WorkItemId", wiIds, ct);
            var affected = await DeleteByInListAsync(context, "[dbo].[ATWorkItem]", "WorkItemId", wiIds, ct);

            await tx.CommitAsync(ct);

            return affected; // rows deleted from ATWorkItem
        }
        catch (Exception ex)
        {
            await tx.RollbackAsync(ct);
            logger.Error(ex, "DeleteWorkItemsAsync failed for WIs: {Ids}", string.Join(",", wiIds));
            throw;
        }
        finally
        {
            context.ChangeTracker.AutoDetectChangesEnabled = oldAutoDetect;
        }
    }

    private static async Task<int> DeleteByInListAsync(
        DbContext db,
        string table,
        string keyColumn,
        IEnumerable<int> ids,
        CancellationToken ct)
    {
        var chunks = Chunk(ids, 1000);
        var total = 0;
        foreach (var chunk in chunks)
        {
            var parameters = new List<SqlParameter>();
            var placeholders = new List<string>();
            int i = 0;
            foreach (var id in chunk)
            {
                var p = new SqlParameter($"@p{i++}", SqlDbType.Int) { Value = id };
                parameters.Add(p);
                placeholders.Add(p.ParameterName);
            }

            var sql = $@"
                DELETE FROM {table}
                WHERE {keyColumn} IN ({string.Join(",", placeholders)})
            ";

            total += await db.Database.ExecuteSqlRawAsync(sql, parameters, ct);
        }
        return total;
    }

    private static async Task<int> ExecuteUpdateInListAsyncString(
    DbContext db,
    string templateSqlWithPlaceholder,
    IEnumerable<string> values,
    CancellationToken ct)
    {
        var total = 0;
        var chunks = Chunk(values, 1000);
        foreach (var chunk in chunks)
        {
            var parameters = new List<SqlParameter>();
            var placeholders = new List<string>();
            int i = 0;
            foreach (var v in chunk)
            {
                var p = new SqlParameter($"@p{i++}", SqlDbType.NVarChar, 100) { Value = v };
                parameters.Add(p);
                placeholders.Add(p.ParameterName);
            }

            var sql = string.Format(templateSqlWithPlaceholder, string.Join(",", placeholders));
            total += await db.Database.ExecuteSqlRawAsync(sql, parameters, ct);
        }
        return total;
    }


    private static async Task<int> ExecuteUpdateInListAsync(
        DbContext db,
        string templateSqlWithPlaceholder,
        IEnumerable<int> ids,
        CancellationToken ct)
    {
        var chunks = Chunk(ids, 1000);
        var total = 0;
        foreach (var chunk in chunks)
        {
            var parameters = new List<SqlParameter>();
            var placeholders = new List<string>();
            int i = 0;
            foreach (var id in chunk)
            {
                var p = new SqlParameter($"@p{i++}", SqlDbType.Int) { Value = id };
                parameters.Add(p);
                placeholders.Add(p.ParameterName);
            }

            var sql = string.Format(templateSqlWithPlaceholder, string.Join(",", placeholders));
            total += await db.Database.ExecuteSqlRawAsync(sql, parameters, ct);
        }
        return total;
    }

    private static IEnumerable<List<T>> Chunk<T>(IEnumerable<T> source, int size)
    {
        var bucket = new List<T>(size);
        foreach (var item in source)
        {
            bucket.Add(item);
            if (bucket.Count == size)
            {
                yield return bucket;
                bucket = new List<T>(size);
            }
        }

        if (bucket.Count > 0)
            yield return bucket;
    }
}