using System.Data;
using IWS5.Core.Enums;
using IWS5.Core.Models.Processing;
using IWS5.Server.DataAccess;
using IWS5.Server.Models.Export;
using Microsoft.Data.SqlClient;

namespace IWS5.Server.EventProcessors;

public class ExportProcessor(Serilog.ILogger logger, IConfiguration configuration)
    : EventProcessor(logger, configuration)
{
    public override async Task<bool> ProcessEventAsync(EventModel eventData, int processId)
    {
        Logger.Information(
            "Starting export process for WorkItemId: {WorkItemId}, ProcessId: {ProcessId}, EventId: {EventId}",
            eventData.WorkItemId, processId, eventData.CurrentEventId);

        try
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                await sqlConnection.OpenAsync();
                Logger.Debug("Database connection opened for export processing");

                Logger.Debug("Retrieving documents and work item information for WorkItemId: {WorkItemId}",
                    eventData.WorkItemId);

                const string selectDocumentsToExportQuery =
                    @"SELECT DocumentId, DocumentName, Document
                FROM ATDocument
                Where ATDocument.WorkItemId = @workItemId;

                SELECT ATWorkItem.WorkItemName, ATProcessDefinition.ProcessName
                FROM ATWorkItem
                JOIN ATProcessDefinition ON ATWorkItem.ProcessID = ATProcessDefinition.ProcessID
                WHERE WorkItemId = @workItemId;";

                var selectDocumentsToExportCmd = new SqlCommand(selectDocumentsToExportQuery, sqlConnection);
                selectDocumentsToExportCmd.Parameters.AddWithValue("@workItemId", eventData.WorkItemId);
                var selectDocumentsToExportDap = new SqlDataAdapter(selectDocumentsToExportCmd);
                var selectDocumentsToExportDs = new DataSet();
                selectDocumentsToExportDap.Fill(selectDocumentsToExportDs);

                if (selectDocumentsToExportDs.Tables[0].Rows.Count == 0)
                {
                    Logger.Warning(
                        "No documents found to export for WorkItemId: {WorkItemId}, updating work item reference",
                        eventData.WorkItemId);
                    await UpdateWorkItemReference(sqlConnection, eventData);
                    return true;
                }

                Logger.Information("Found {DocumentCount} documents to export for WorkItemId: {WorkItemId}",
                    selectDocumentsToExportDs.Tables[0].Rows.Count, eventData.WorkItemId);

                var documentsToExport = new List<ATDocument>();
                foreach (DataRow dataRow in selectDocumentsToExportDs.Tables[0].Rows)
                {
                    var document = new ATDocument
                    {
                        Id = (int)dataRow["DocumentId"],
                        Name = dataRow["DocumentName"].ToString(),
                        WorkItemId = eventData.WorkItemId,
                        File = dataRow["Document"] as byte[]
                    };
                    documentsToExport.Add(document);
                    Logger.Debug("Loaded document for export: {DocumentName} (Id: {DocumentId}, Size: {Size} bytes)",
                        document.Name, document.Id, document.File?.Length ?? 0);
                }

                var processName = selectDocumentsToExportDs.Tables[1].Rows[0]["ProcessName"].ToString();
                Logger.Debug("Process name for export: {ProcessName}", processName);

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

                const string selectExpEventSql =
                    "Select TemplateId from ATExportEvent where EventId = @currentEventId";
                var selectExpEventCmd = new SqlCommand(selectExpEventSql, sqlConnection);
                selectExpEventCmd.Parameters.AddWithValue("@currentEventId", eventData.CurrentEventId);
                var selectExpEventDap = new SqlDataAdapter(selectExpEventCmd);
                var selectExpEventDs = new DataSet();
                selectExpEventDap.Fill(selectExpEventDs);

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

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

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

                var exportDefinitions = DefinitionRepository.GetExportDefinitions(sqlConnection, exportTemplates);
                if (exportDefinitions.Count == 0)
                {
                    await UpdateWorkItemReference(sqlConnection, eventData);
                    Logger.Warning(
                        "No export definitions found for templates: {Templates}, updating work item reference",
                        string.Join(", ", exportTemplates));
                    return true;
                }

                Logger.Debug("Retrieved {DefinitionCount} export definitions", exportDefinitions.Count);

                var exportDefinitionIds = exportDefinitions.Select(eDefinition => eDefinition.Id).ToArray();

                Logger.Debug("Retrieving export locations for definition IDs: [{DefinitionIds}]",
                    string.Join(", ", exportDefinitionIds));

                var selectExpLocationsSql =
                    $@"SELECT ExportDefinitionId, ATExportLocation.Name AS ExportLocation
                   FROM ATExportDefinition_ExportLocation
                   JOIN ATExportLocation ON ATExportDefinition_ExportLocation.ExportLocationId = ATExportLocation.Id
                   WHERE ExportDefinitionId IN ({string.Join(", ", exportDefinitionIds)})";

                var selectExpLocCmd = new SqlCommand(selectExpLocationsSql, sqlConnection);
                var selectExpLocDap = new SqlDataAdapter(selectExpLocCmd);
                var selectExpLocDs = new DataSet();
                selectExpLocDap.Fill(selectExpLocDs);

                if (selectExpLocDs.Tables[0].Rows.Count == 0)
                {
                    await UpdateWorkItemReference(sqlConnection, eventData);
                    Logger.Warning(
                        "No export locations found for definitions: {DefinitionIds}, updating work item reference",
                        string.Join(", ", exportDefinitionIds));
                    return true;
                }

                Logger.Debug("Found {LocationCount} export location mappings", selectExpLocDs.Tables[0].Rows.Count);

                foreach (DataRow dataRow in selectExpLocDs.Tables[0].Rows)
                {
                    var exportDefinitionId = (int)dataRow["ExportDefinitionId"];
                    var exportLocation = dataRow["ExportLocation"].ToString();

                    var exportDefinition = exportDefinitions.FirstOrDefault(ed => ed.Id == exportDefinitionId);
                    exportDefinition?.ExportLocations.Add(exportLocation);

                    Logger.Debug("Mapped export location '{Location}' to definition ID: {DefinitionId}",
                        exportLocation, exportDefinitionId);
                }

                var activeExportDefinitions = exportDefinitions.Where(ed => ed.ExportLocations.Count > 0).ToList();
                Logger.Information("Processing {ActiveDefinitionCount} export definitions with locations",
                    activeExportDefinitions.Count);

                foreach (var exportDefinition in activeExportDefinitions)
                {
                    Logger.Debug(
                        "Processing export definition ID: {DefinitionId}, FileFormat: {FileFormat}, Locations: {LocationCount}",
                        exportDefinition.Id, exportDefinition.ExportFileFormat ?? "ALL",
                        exportDefinition.ExportLocations.Count);

                    foreach (var location in exportDefinition.ExportLocations)
                    {
                        var targetDirectory = Path.Combine(location, processName, eventData.WorkItemName);

                        Logger.Debug("Creating target directory: {TargetDirectory}", targetDirectory);
                        Directory.CreateDirectory(targetDirectory);
                        Logger.Information("Created target directory: {TargetDirectory}", targetDirectory);

                        var filteredDocuments =
                            exportDefinition.ExportFileFormat != null
                                ? documentsToExport.Where(doc =>
                                        doc.File != null && doc.Name.EndsWith(exportDefinition.ExportFileFormat,
                                            StringComparison.OrdinalIgnoreCase))
                                    .ToList()
                                : documentsToExport;

                        Logger.Debug(
                            "Filtered {FilteredCount} documents for export (out of {TotalCount}) based on format filter: {Filter}",
                            filteredDocuments.Count, documentsToExport.Count,
                            exportDefinition.ExportFileFormat ?? "NONE");

                        foreach (var document in filteredDocuments)
                        {
                            var filePath = Path.Combine(targetDirectory, document.Name);
                            File.WriteAllBytes(filePath, document.File);
                            Logger.Information("Exported document: {DocumentName} to {FilePath} ({Size} bytes)",
                                document.Name, filePath, document.File.Length);
                        }

                        if (!string.IsNullOrEmpty(exportDefinition.DoneFlag))
                        {
                            var doneFilePath = Path.Combine(targetDirectory, exportDefinition.DoneFlag);
                            File.WriteAllText(doneFilePath, string.Empty);
                            Logger.Information("Created done flag file: {DoneFilePath}", doneFilePath);
                        }

                        Logger.Debug("Completed export to location: {Location}, exported {DocumentCount} documents",
                            location, filteredDocuments.Count);
                    }
                }

                Logger.Debug("Updating work item reference to next event: {NextEventId}", eventData.NextEventId);
                await UpdateWorkItemReference(sqlConnection, eventData);

                Logger.Information(
                    "Export process completed successfully for WorkItemId: {WorkItemId}, processed {DefinitionCount} export definitions",
                    eventData.WorkItemId, activeExportDefinitions.Count);
                return true;
            }
        }
        catch (Exception ex)
        {
            Logger.Error(ex, "Error in export process for WorkItemId: {WorkItemId}, EventId: {EventId}",
                eventData.WorkItemId, eventData.CurrentEventId);
            return false;
        }
    }


    private static async Task UpdateWorkItemReference(SqlConnection sqlConnection, 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))
        {
            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();
        }
    }
}