using System.Data;
using IWS5.Core.Enums;
using IWS5.Core.Models.Processing;
using IWS5.Server.DataAccess;
using IWS5.Server.Enums;
using IWS5.Server.Models.DocumentConversion;
using IWS5.Server.Models.Export;
using IWS5.Server.Utils;
using IWS5.Server.Utils.Converter;
using IWS5.Server.Utils.Merger;
using Microsoft.Data.SqlClient;

namespace IWS5.Server.EventProcessors;

public class ConvertProcessor : EventProcessor
{
    public ConvertProcessor(Serilog.ILogger logger, IConfiguration configuration)
        : base(logger, configuration)
    {
    }

    public override async Task<bool> ProcessEventAsync(EventModel eventData, int processId)
    {
        Logger.Information("Starting convert event processing for WorkItemId: {WorkItemId}, EventId: {EventId}",
            eventData.WorkItemId, eventData.CurrentEventId);

        var resultDocuments = new List<ATDocument>();

        using (var sqlConnection = new SqlConnection(ConnectionString))
        {
            await sqlConnection.OpenAsync();
            using var transaction = sqlConnection.BeginTransaction();

            try
            {
                Logger.Debug("Retrieving convert event templates for EventId: {EventId}", eventData.CurrentEventId);

                const string selectConvertEventSql =
                    "SELECT TemplateId FROM ATConvertEvent WHERE EventId = @currentEventId";
                var selectConvertEventCmd = new SqlCommand(selectConvertEventSql, sqlConnection, transaction);
                selectConvertEventCmd.Parameters.AddWithValue("@currentEventId", eventData.CurrentEventId);
                var selectConvertEventDap = new SqlDataAdapter(selectConvertEventCmd);
                var selectConvertEventDs = new DataSet();
                selectConvertEventDap.Fill(selectConvertEventDs);

                if (selectConvertEventDs.Tables[0].Rows.Count == 0)
                {
                    Logger.Warning(
                        "No convert event templates found for EventId: {EventId}, updating work item reference",
                        eventData.CurrentEventId);
                    await UpdateWorkItemReference(sqlConnection, transaction, eventData);
                    await transaction.CommitAsync();
                    return true;
                }

                var convertTemplates = new List<int>();
                foreach (DataRow dataRow in selectConvertEventDs.Tables[0].Rows)
                {
                    convertTemplates.Add(Convert.ToInt32(dataRow["TemplateId"]));
                }

                Logger.Information("Found {TemplateCount} convert templates: [{Templates}]",
                    convertTemplates.Count, string.Join(", ", convertTemplates));

                var convertDefinitions =
                    DefinitionRepository.GetConvertDefinitionsOrdered(sqlConnection, convertTemplates, transaction);
                if (convertDefinitions.Count == 0)
                {
                    Logger.Warning("No convert definitions found for templates, updating work item reference");
                    await UpdateWorkItemReference(sqlConnection, transaction, eventData);
                    await transaction.CommitAsync();
                    return true;
                }

                Logger.Debug("Retrieved {DefinitionCount} convert definitions", convertDefinitions.Count);

                var originalDocuments = DocumentRepository.GetDocuments(sqlConnection, eventData.WorkItemId, transaction);
                if (originalDocuments.Count == 0)
                {
                    Logger.Warning("No documents found for WorkItemId: {WorkItemId}, updating work item reference",
                        eventData.WorkItemId);
                    await UpdateWorkItemReference(sqlConnection, transaction, eventData);
                    await transaction.CommitAsync();
                    return true;
                }

                Logger.Information("Found {DocumentCount} original documents for WorkItemId: {WorkItemId}",
                    originalDocuments.Count, eventData.WorkItemId);

                resultDocuments.AddRange(originalDocuments);

                var coverSheetTemplateData = new CoverSheetTemplateData();
                if (convertDefinitions.Any(cDefinition => cDefinition.ApplyCoverSheet))
                {
                    Logger.Debug("Preparing cover sheet template data for WorkItemId: {WorkItemId}",
                        eventData.WorkItemId);
                    coverSheetTemplateData = new CoverSheetTemplateData
                    {
                        WorkItemId = eventData.WorkItemId,
                        WorkItemName = eventData.WorkItemName,
                        FolderFields =
                            FolderFieldRepository.GetFolderFields(sqlConnection, eventData.WorkItemId, processId),
                    };
                }

                foreach (var convertDefinition in convertDefinitions)
                {
                    Logger.Debug(
                        "Processing convert definition - SourceType: {SourceType}, TargetType: {TargetType}, " +
                        "ApplyCoverSheet: {ApplyCoverSheet}, ApplyMergeDocs: {ApplyMergeDocs}, ReplaceDocument: {ReplaceDocument}",
                        convertDefinition.SourceDocType, convertDefinition.ExportDocType,
                        convertDefinition.ApplyCoverSheet, convertDefinition.ApplyMergeDocs,
                        convertDefinition.ReplaceDocument);

                    List<ATDocument> documentsToProcess;

                    // Step 5: Check if Source Document Type is null
                    if (string.IsNullOrEmpty(convertDefinition.SourceDocType))
                    {
                        Logger.Debug("Source document type is null, processing all documents");
                        documentsToProcess = new List<ATDocument>(originalDocuments);
                    }
                    else
                    {
                        Logger.Debug("Filtering documents by source type: {SourceType}",
                            convertDefinition.SourceDocType);
                        documentsToProcess = originalDocuments
                            .Where(doc => doc.File != null &&
                                          doc.Name.EndsWith(convertDefinition.SourceDocType,
                                              StringComparison.OrdinalIgnoreCase))
                            .ToList();
                    }

                    if (documentsToProcess.Count == 0)
                    {
                        Logger.Debug("No documents found matching source type filter, skipping definition");
                        continue;
                    }

                    Logger.Information("Processing {DocumentCount} documents with convert definition",
                        documentsToProcess.Count);

                    var convertedDocuments = new List<ATDocument>();
                    var successfullyConvertedDocuments = new List<(ATDocument original, ATDocument converted)>();

                    foreach (var document in documentsToProcess)
                    {
                        Logger.Debug("Converting document: {DocumentName}", document.Name);

                        var sourceExtension = convertDefinition.SourceDocType?.ToLowerInvariant() ??
                                              Path.GetExtension(document.Name)?.TrimStart('.');
                        var targetExtension = convertDefinition.ExportDocType?.ToLowerInvariant();

                        var converter = ConverterFactory.CreateConverter(sourceExtension, targetExtension);
                        if (converter == null)
                        {
                            Logger.Warning(
                                "No converter available for {SourceExt} to {TargetExt}, skipping document: {DocumentName}",
                                sourceExtension, targetExtension, document.Name);
                            continue;
                        }

                        try
                        {
                            var convertedDocument = new ATDocument
                            {
                                File = converter.ConvertAsync(document.File).GetAwaiter().GetResult(),
                                Name = Path.ChangeExtension(document.Name, convertDefinition.ExportDocType),
                                WorkItemId = eventData.WorkItemId,
                                CabinetId = document.CabinetId,
                                CabinetName = document.CabinetName,
                                DisplayOrder = document.DisplayOrder
                            };

                            if (convertedDocument.File != null && convertedDocument.File.Length > 0)
                            {
                                Logger.Debug(
                                    "Successfully converted document: {OriginalName} -> {ConvertedName} ({Size} bytes)",
                                    document.Name, convertedDocument.Name, convertedDocument.File.Length);
                                convertedDocuments.Add(convertedDocument);
                                successfullyConvertedDocuments.Add((document, convertedDocument));
                            }
                            else
                            {
                                Logger.Warning("Document conversion resulted in empty file: {DocumentName}",
                                    document.Name);
                            }
                        }
                        catch (Exception ex)
                        {
                            Logger.Error(ex, "Failed to convert document: {DocumentName}", document.Name);
                            await transaction.RollbackAsync();
                            throw;
                        }
                    }

                    if (convertedDocuments.Count == 0)
                    {
                        Logger.Debug("No documents were successfully converted for this definition, skipping");
                        continue;
                    }

                    Logger.Information("Successfully converted {ConvertedCount} out of {TotalCount} documents",
                        convertedDocuments.Count, documentsToProcess.Count);

                    // Step 7: Add Cover Sheet if enabled
                    if (convertDefinition.ApplyCoverSheet)
                    {
                        Logger.Debug("Applying cover sheet to {DocumentCount} converted documents",
                            convertedDocuments.Count);

                        coverSheetTemplateData.BarcodeFont = convertDefinition.BarcodeFont;
                        var templatePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Assets",
                            "CoverSheet.docx");
                        var coverSheetConverter =
                            ConverterFactory.CreateConverter("docx", convertDefinition.ExportDocType);
                        var merger = MergeFactory.CreateMerger(convertDefinition.ExportDocType);

                        if (coverSheetConverter != null && merger != null)
                        {
                            var coverSheet = CoverSheetProcessor
                                .ProcessTemplateAsync(templatePath, coverSheetTemplateData)
                                .GetAwaiter().GetResult();
                            var coverSheetFile = coverSheetConverter.ConvertAsync(coverSheet).GetAwaiter().GetResult();

                            // Merge cover sheet with each converted document
                            for (int i = 0; i < convertedDocuments.Count; i++)
                            {
                                convertedDocuments[i].File = merger.Merge(coverSheetFile, convertedDocuments[i].File);
                            }

                            Logger.Information("Successfully applied cover sheet to {DocumentCount} documents",
                                convertedDocuments.Count);
                        }
                        else
                        {
                            Logger.Warning("Cover sheet converter or merger not available for type: {ExportType}",
                                convertDefinition.ExportDocType);
                        }
                    }

                    // Step 8: Merge documents if enabled
                    if (convertDefinition.ApplyMergeDocs && convertedDocuments.Count > 1)
                    {
                        Logger.Debug("Merging {DocumentCount} documents with merge order: {MergeOrder}",
                            convertedDocuments.Count, convertDefinition.MergeOrder);

                        var merger = MergeFactory.CreateMerger(convertDefinition.ExportDocType);
                        if (merger != null)
                        {
                            var orderedDocuments = ApplyMergeOrder(convertedDocuments, convertDefinition.MergeOrder);
                            var documentFilesToMerge = orderedDocuments.Select(doc => doc.File).ToArray();
                            var mergedFile = merger.Merge(documentFilesToMerge);

                            var mergedDocument = new ATDocument
                            {
                                File = mergedFile,
                                Name = $"Merged_{eventData.WorkItemName}.{convertDefinition.ExportDocType}",
                                WorkItemId = eventData.WorkItemId,
                                CabinetId = convertedDocuments.FirstOrDefault()?.CabinetId ?? 0,
                                CabinetName = convertedDocuments.FirstOrDefault()?.CabinetName,
                                DisplayOrder = convertedDocuments.Min(d => d.DisplayOrder)
                            };

                            convertedDocuments = new List<ATDocument> { mergedDocument };

                            Logger.Information("Successfully merged {DocumentCount} documents into: {MergedName}",
                                orderedDocuments.Count, mergedDocument.Name);
                        }
                        else
                        {
                            Logger.Warning("Merger not available for type: {ExportType}, skipping merge",
                                convertDefinition.ExportDocType);
                        }
                    }

                    // Step 9: Handle Replace vs Preserve documents
                    if (convertDefinition.ReplaceDocument)
                    {
                        Logger.Debug("Replacing original documents with converted versions");

                        var convertedSourceDocumentIds =
                            successfullyConvertedDocuments.Select(x => x.original.Id).ToHashSet();
                        var removedCount = resultDocuments.RemoveAll(d => convertedSourceDocumentIds.Contains(d.Id));

                        Logger.Debug("Removed {RemovedCount} original documents", removedCount);
                        resultDocuments.AddRange(convertedDocuments);
                    }
                    else
                    {
                        Logger.Debug("Preserving original documents and adding converted versions");
                        resultDocuments.AddRange(convertedDocuments);
                    }
                }

                // Step 10: Save changes to database using SQL merge statements
                Logger.Debug("Saving {TotalDocuments} documents to database (originally had {OriginalCount})",
                    resultDocuments.Count, originalDocuments.Count);

                SaveDocumentsToDatabase(sqlConnection, transaction, resultDocuments, originalDocuments);

                Logger.Debug("Updating work item reference for EventId: {NextEventId}", eventData.NextEventId);

                const string updateWiReferenceSqlStr = @"
                Update ATWorkItemReference
                SET EventID = @eventId,
                    PendEventId = 0,
                    PendQueueId = null,
                    PendExpirationTime = NULL,
                    ServerRequest = @serverRequest,
                    ServerRequestParam = NULL,
                    LastActionDate = @lastActionDate
                WHERE WorkItemID = @workItemId";

                using (var cmd2 = new SqlCommand(updateWiReferenceSqlStr, sqlConnection, transaction))
                {
                    cmd2.Parameters.AddWithValue("@eventId", eventData.NextEventId);
                    cmd2.Parameters.AddWithValue("@workItemId", eventData.WorkItemId);
                    cmd2.Parameters.AddWithValue("@serverRequest", (int)ServerRequest.ForwardExistingWorkItem);
                    cmd2.Parameters.AddWithValue("@lastActionDate", DateTime.Now);
                    await cmd2.ExecuteNonQueryAsync();
                }

                await UpdateWorkItemReference(sqlConnection, transaction, eventData);
                await transaction.CommitAsync();

                Logger.Information("Successfully completed convert event processing for WorkItemId: {WorkItemId}, " +
                                   "processed {DocumentCount} documents", eventData.WorkItemId, resultDocuments.Count);

                return true;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                Logger.Error(ex, "Error processing convert event for WorkItemId: {WorkItemId}, EventId: {EventId}",
                    eventData.WorkItemId, eventData.CurrentEventId);
                throw new InvalidOperationException($"Error processing convert event: {ex.Message}", ex);
            }
        }
    }


    private static List<ATDocument> ApplyMergeOrder(List<ATDocument> documents, MergeOrder mergeOrder)
    {
        return mergeOrder switch
        {
            MergeOrder.DocumentNameAsc => documents.OrderBy(doc => doc.Name).ToList(),
            MergeOrder.DocumentNameDesc => documents.OrderByDescending(doc => doc.Name).ToList(),
            MergeOrder.DocumentOrderAsc => documents.OrderBy(doc => doc.DisplayOrder ?? int.MaxValue)
                .ThenBy(doc => doc.Id).ToList(),
            MergeOrder.DocumentOrderDesc => documents.OrderByDescending(doc => doc.DisplayOrder ?? int.MinValue)
                .ThenByDescending(doc => doc.Id).ToList(),
            _ => documents
        };
    }

    private static void SaveDocumentsToDatabase(SqlConnection sqlConnection, SqlTransaction transaction,
        List<ATDocument> resultDocuments, List<ATDocument> originalDocuments)
    {
        // First, identify which documents need to be deleted (replaced)
        var documentsToDelete = originalDocuments.Where(orig =>
            !resultDocuments.Any(result => result.Id == orig.Id && result.Id > 0)).ToList();

        // Delete replaced documents
        if (documentsToDelete.Count > 0)
        {
            var deleteIds = string.Join(",", documentsToDelete.Select(d => d.Id));
            var deleteSql = $"DELETE FROM ATDocument WHERE DocumentId IN ({deleteIds})";
            var deleteCmd = new SqlCommand(deleteSql, sqlConnection, transaction);
            deleteCmd.ExecuteNonQuery();
        }

        // Insert new documents (those with Id = 0)
        foreach (var document in resultDocuments.Where(d => d.Id == 0))
        {
            var insertSql = @"
            INSERT INTO ATDocument (DocumentName, ReviewStatusId, ReviewNumber, CabinetId, WorkItemId, 
                                   DocumentSize, DisplayOrder, Document, DocumentLink, UserName, SignedBy, LastModified)
            VALUES (@documentName, @reviewStatusId, @reviewNumber, @cabinetId, @workItemId, 
                   @documentSize, @displayOrder, @document, @documentLink, @userName, @signedBy, @lastModified)";

            using var insertCmd = new SqlCommand(insertSql, sqlConnection, transaction);

            insertCmd.Parameters.AddWithValue("@documentName", document.Name ?? (object)DBNull.Value);
            insertCmd.Parameters.AddWithValue("@reviewStatusId", 1);
            insertCmd.Parameters.AddWithValue("@reviewNumber", 1);
            insertCmd.Parameters.AddWithValue("@cabinetId", document.CabinetId);
            insertCmd.Parameters.AddWithValue("@workItemId", document.WorkItemId);
            insertCmd.Parameters.AddWithValue("@documentSize", document.File?.Length ?? 0);
            insertCmd.Parameters.AddWithValue("@displayOrder", document.DisplayOrder ?? (object)DBNull.Value);
            insertCmd.Parameters.AddWithValue("@document", document.File ?? (object)DBNull.Value);
            insertCmd.Parameters.AddWithValue("@documentLink", DBNull.Value);
            insertCmd.Parameters.AddWithValue("@userName", "PDAdmin");
            insertCmd.Parameters.AddWithValue("@signedBy", DBNull.Value);
            insertCmd.Parameters.AddWithValue("@lastModified", DateTime.Now);

            insertCmd.ExecuteNonQuery();
        }
    }

    private static async Task UpdateWorkItemReference(SqlConnection sqlConnection, SqlTransaction transaction,
        EventModel eventData)
    {
        const string updateWiReferenceSqlStr = @"
                    Update ATWorkItemReference 
                    SET EventID = @eventId,
                        PendEventId = 0, 
                        PendQueueId = null, 
                        PendExpirationTime = NULL, 
                        ServerRequest = @serverRequest,
                        ServerRequestParam = NULL, 
                        LastActionDate = @lastActionDate
                    WHERE WorkItemID = @workItemId";

        using (var cmd2 = new SqlCommand(updateWiReferenceSqlStr, sqlConnection, transaction))
        {
            cmd2.Parameters.AddWithValue("@eventId", eventData.NextEventId);
            cmd2.Parameters.AddWithValue("@workItemId", eventData.WorkItemId);
            cmd2.Parameters.AddWithValue("@serverRequest", (int)ServerRequest.ForwardExistingWorkItem);
            cmd2.Parameters.AddWithValue("@lastActionDate", DateTime.Now);
            await cmd2.ExecuteNonQueryAsync();
        }
    }
}