using IWS5.Core;
using IWS5.Core.Enums;
using IWS5.Core.Helpers;
using IWS5.Core.Models;
using IWS5.Core.Models.Processing;
using IWS5.Server.Configuration;
using IWS5.Server.DataAccess;
using IWS5.Server.Helpers;
using IWS5.Server.Helpers.TriggerProcessors;
using IWS5.Server.Models;
using IWS5.Server.Models.TriggerActionModels;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using PDServer.Helpers.TriggerProcessors;
using System.Data;
using System.Text.RegularExpressions;
using ILogger = Serilog.ILogger;

namespace IWS5.Server;

public class Worker : BackgroundService
{
    private readonly ILogger _logger;
    private readonly IConfiguration _configuration;
    private readonly ProcessAgent _agentConfig;
    private readonly IServiceProvider _serviceProvider;
    private readonly string _connectionString = string.Empty;

    public Worker(
        ILogger logger,
        IConfiguration configuration,
        IServiceProvider serviceProvider,
        IOptions<ProcessAgent> agentConfig)
    {
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
        _serviceProvider = serviceProvider ?? throw new ArgumentNullException(nameof(serviceProvider));
        _agentConfig = agentConfig?.Value ?? throw new ArgumentNullException(nameof(agentConfig));
        _connectionString = _configuration.GetConnectionString("GreenfieldPD") ??
                            throw new InvalidOperationException("Connection string 'GreenfieldPD' not found.");
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        await using (var sqlConnection = new SqlConnection(_connectionString))
        {
            await sqlConnection.OpenAsync(stoppingToken);

            const string sql = "Update ATServer Set Status = @agentStatus Where HostName = @processAgent";
            await using (var cmd = new SqlCommand(sql, sqlConnection))
            {
                cmd.Parameters.AddWithValue("@processAgent", _agentConfig.HostName);
                cmd.Parameters.AddWithValue("@agentStatus", nameof(ATServerStatus.Running));
                await cmd.ExecuteNonQueryAsync(stoppingToken);
            }
        }

        while (!stoppingToken.IsCancellationRequested)
        {
            _logger.Information("Worker running at: {time}", DateTimeOffset.Now);

            await ProcessWorkItemsAsync(ServerRequest.NewWorkItem);
            await ProcessWorkItemsAsync(ServerRequest.ForwardExistingWorkItem);
            await ProcessPendingWorkItemsAsync();

            await Task.Delay(5000, stoppingToken);
        }

        await using (var sqlConnection = new SqlConnection(_connectionString))
        {
            await sqlConnection.OpenAsync(stoppingToken);

            const string sql = "Update ATServer Set Status = @agentStatus Where HostName = @processAgent";
            await using (var cmd = new SqlCommand(sql, sqlConnection))
            {
                cmd.Parameters.AddWithValue("@processAgent", _agentConfig.HostName);
                cmd.Parameters.AddWithValue("@agentStatus", nameof(ATServerStatus.Stopped));
                await cmd.ExecuteNonQueryAsync(stoppingToken);
            }

            await sqlConnection.CloseAsync();
        }
    }

    private async Task<bool> ProcessWorkItemsAsync(ServerRequest serverRequest)
    {
        var sqlStr =
            @$"SELECT TOP({_agentConfig.ProcessLimit}) wi.WorkItemId, wi.WorkItemName, wir.SourceUser, wir.QueueId, wir.SourceQueueId, wir.EventID, wir.LastAction, ed.EventTypeID, ed.EventName, ed.NextEventID, ed.ProcessID
                    FROM ATWorkItem wi
                    JOIN ATWorkItemReference wir ON wir.WorkItemId = wi.WorkItemId
                    JOIN ATEventDefinition ed on wir.EventID = ed.EventID
                    Where ed.server = @processAgent And ServerRequest = @serverRequest";

        var workItemsToProcess = new List<EventModel>();

        await using (var sqlConnection = new SqlConnection(_connectionString))
        {
            await sqlConnection.OpenAsync();
            await using (var cmd = new SqlCommand(sqlStr, sqlConnection))
            {
                cmd.Parameters.AddWithValue("@processAgent", _agentConfig.HostName);
                cmd.Parameters.AddWithValue("@serverRequest", (int)serverRequest);

                await using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        workItemsToProcess.Add(new EventModel
                        {
                            WorkItemId = reader.GetInt32(reader.GetOrdinal("WorkItemId")),
                            WorkItemName = reader.GetString(reader.GetOrdinal("WorkItemName")),
                            SourceUser = reader.IsDBNull(reader.GetOrdinal("SourceUser"))
                                ? null
                                : reader.GetString(reader.GetOrdinal("SourceUser")),
                            QueueId = reader.IsDBNull(reader.GetOrdinal("QueueId"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("QueueId")),
                            SourceQueueId = reader.IsDBNull(reader.GetOrdinal("SourceQueueId"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("SourceQueueId")),
                            CurrentEventId = reader.GetInt32(reader.GetOrdinal("EventID")),
                            LastAction = reader.IsDBNull(reader.GetOrdinal("LastAction"))
                                ? null
                                : reader.GetString(reader.GetOrdinal("LastAction")),
                            ServerRequest = serverRequest,
                            CurrentEventType = (ATEventType)reader.GetInt32(reader.GetOrdinal("EventTypeID")),
                            CurrentEventName = reader.GetString(reader.GetOrdinal("EventName")),
                            NextEventId = reader.IsDBNull(reader.GetOrdinal("NextEventID"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("NextEventID")),
                            CurrentProcessId = reader.IsDBNull(reader.GetOrdinal("ProcessID"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("ProcessID")),
                        });
                    }
                }
            }

            await sqlConnection.CloseAsync();
        }

        if (workItemsToProcess.Count == 0)
        {
            _logger.Information("No work items to process for request: {serverRequest}", serverRequest);
            return true;
        }

        if (workItemsToProcess.Count > 0 && serverRequest == ServerRequest.NewWorkItem)
        {
            foreach (var workItem in workItemsToProcess)
            {
                string sql = $@"
                            WITH ProcessInfo AS (
                                SELECT pd.ProcessName
                                FROM ATEventDefinition ed
                                INNER JOIN ATProcessDefinition pd ON ed.ProcessId = pd.ProcessId
                                WHERE ed.EventId = @eventId
                            ),
                            LatestProcess AS (
                                SELECT pd.ProcessId
                                FROM ATProcessDefinition pd
                                INNER JOIN ProcessInfo pi ON pd.ProcessName = pi.ProcessName
                                WHERE pd.Status = 1
                                AND pd.ProcessVersion = (
                                    SELECT MAX(ProcessVersion)
                                    FROM ATProcessDefinition pd2
                                    WHERE pd2.ProcessName = pi.ProcessName
                                    AND pd2.Status = 1
                                )
                            )
                            SELECT ed.EventId
                            FROM ATEventDefinition ed
                            INNER JOIN LatestProcess lp ON ed.ProcessId = lp.ProcessId
                            WHERE ed.EventName = '{nameof(ATEventType.Start)}'";

                workItem.CurrentEventId = Convert.ToInt32(await GetRecordValueAsync(sql,
                    new Dictionary<string, object> { { "@eventId", workItem.CurrentEventId } }));
            }
        }

        foreach (var eventModel in workItemsToProcess)
        {
            try
            {
                var result = await ProcessWorkItemAsync(eventModel);
                if (!result)
                {
                    _logger.Error("Failed to process work item {WorkItemId}", eventModel.WorkItemId);
                }

                _logger.Information("Processed work item {WorkItemId}", eventModel.WorkItemId);
            }
            catch (Exception e)
            {
                _logger.Error("Error processing work item {WorkItemId}: {ErrorMessage}", eventModel.WorkItemId,
                    e.Message);
                return false;
            }
        }

        return true;
    }

    private async Task<bool> ProcessWorkItemAsync(EventModel eventModel)
    {
        var triggerDefinitions = new List<ATTriggerDefinition>();
        try
        {
            await using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                triggerDefinitions = DefinitionRepository.GetTriggerDefinitions(connection, eventModel.CurrentEventId);
            }

            if (eventModel.ServerRequest == ServerRequest.NewWorkItem)
            {
                //Process new workItems that have been Imported or created by iDesktop and have a ServerRequest = 1
                //There is no queue assigned so the workitem. LockBy does not need to be used for the import
                await using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var sqlStr =
                        $"Update ATWorkItem SET ProcessId = @currentProcessId, Created = '{DateTime.Now}' WHERE WorkItemId = @workItemId";
                    await using (var command = new SqlCommand(sqlStr, connection))
                    {
                        command.Parameters.AddWithValue("@currentProcessId", eventModel.CurrentProcessId);
                        command.Parameters.AddWithValue("@workItemId", eventModel.WorkItemId);
                        await command.ExecuteNonQueryAsync();
                    }
                }
            }

            await WriteTracking(eventModel);

            foreach (var triggerDefinition in triggerDefinitions.Where(trigger => trigger.Hook == ATTriggerHook.OnArrival))
            {
                var processed = await HandleTriggerAction(triggerDefinition, eventModel.CurrentEventType, eventModel.CurrentEventId,
                    eventModel.WorkItemId, eventModel.SourceUser, eventModel.QueueId.GetValueOrDefault(), eventModel.SourceQueueId.GetValueOrDefault(), eventModel.LastAction);

                if (processed && (triggerDefinition.Action == ATTriggerAction.SendToEvent || triggerDefinition.Action == ATTriggerAction.SendToProcess))
                {
                    return true;
                }
            }

            await using var scope = _serviceProvider.CreateAsyncScope();
            var processor = scope.ServiceProvider.GetKeyedService<IEventProcessor>(eventModel.CurrentEventType);

            if (processor == null)
            {
                _logger.Error("No processor found for event type: {EventType}, WorkItemId: {WorkItemId}",
                    eventModel.CurrentEventType, eventModel.WorkItemId);
                return false;
            }

            var result = await processor.ProcessEventAsync(eventModel, eventModel.CurrentProcessId.GetValueOrDefault());

            if (!result)
            {
                _logger.Error("Processor failed for event type: {EventType}, WorkItemId: {WorkItemId}",
                    eventModel.CurrentEventType, eventModel.WorkItemId);
                return false;
            }

            return true;
        }
        catch (Exception ex)
        {
            _logger.Error(ex,
                "Error while processing WorkItemId={WorkItemId}, WorkItemReferenceId={WorkItemReferenceId}",
                eventModel.WorkItemId);
            await WriteErrorAsync(eventModel.WorkItemId, ex);

            foreach (var triggerDefinition in triggerDefinitions.Where(trigger => trigger.Hook == ATTriggerHook.OnError))
            {
                await HandleTriggerAction(triggerDefinition, eventModel.CurrentEventType, eventModel.CurrentEventId,
                                  eventModel.WorkItemId, eventModel.SourceUser, eventModel.QueueId.GetValueOrDefault(), eventModel.SourceQueueId.GetValueOrDefault(), eventModel.LastAction);
            }

            throw ex;
        }
    }

    private async Task ProcessPendingWorkItemsAsync()
    {
        var sqlStr =
            @$"SELECT TOP({_agentConfig.ProcessLimit}) wi.WorkItemId, wi.WorkItemName, wir.SourceUser, wir.QueueId, wir.SourceQueueId, wir.EventID, wir.LastAction, ed.EventTypeID, ed.EventName, ed.NextEventID, ed.ProcessID
                    FROM ATPendingWorkItemReference pwir
                    JOIN ATWorkItemReference wir ON pwir.PendingWorkItemReferenceId = wir.WorkItemReferenceId
                    JOIN ATWorkItem wi ON wir.WorkItemId = wi.WorkItemId
                    JOIN ATEventDefinition ed on wir.EventID = ed.EventID
                    JOIN ATQueue q on wir.QueueId = q.Id
                    Where ed.server = @processAgent AND pwir.ExpirationTime < GETUTCDATE()";

        var workItemsToProcess = new List<EventModel>();

        await using (var sqlConnection = new SqlConnection(_connectionString))
        {
            await sqlConnection.OpenAsync();
            await using (var cmd = new SqlCommand(sqlStr, sqlConnection))
            {
                cmd.Parameters.AddWithValue("@processAgent", _agentConfig.HostName);

                await using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        workItemsToProcess.Add(new EventModel
                        {
                            WorkItemId = reader.GetInt32(reader.GetOrdinal("WorkItemId")),
                            WorkItemName = reader.GetString(reader.GetOrdinal("WorkItemName")),
                            SourceUser = reader.IsDBNull(reader.GetOrdinal("SourceUser"))
                                ? null
                                : reader.GetString(reader.GetOrdinal("SourceUser")),
                            QueueId = reader.IsDBNull(reader.GetOrdinal("QueueId"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("QueueId")),
                            SourceQueueId = reader.IsDBNull(reader.GetOrdinal("SourceQueueId"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("SourceQueueId")),
                            CurrentEventId = reader.GetInt32(reader.GetOrdinal("EventID")),
                            LastAction = reader.IsDBNull(reader.GetOrdinal("LastAction"))
                                ? null
                                : reader.GetString(reader.GetOrdinal("LastAction")),
                            CurrentEventType = (ATEventType)reader.GetInt32(reader.GetOrdinal("EventTypeID")),
                            CurrentEventName = reader.GetString(reader.GetOrdinal("EventName")),
                            NextEventId = reader.IsDBNull(reader.GetOrdinal("NextEventID"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("NextEventID")),
                            CurrentProcessId = reader.IsDBNull(reader.GetOrdinal("ProcessID"))
                                ? null
                                : reader.GetInt32(reader.GetOrdinal("ProcessID")),
                        });
                    }
                }
            }

            await sqlConnection.CloseAsync();
        }

        if (workItemsToProcess.Count == 0)
        {
            _logger.Information("No expired work items to process");
            return;
        }

        foreach (var eventModel in workItemsToProcess)
        {
            await ProcessPendingWorkItemAsync(eventModel);
        }
    }

    private async Task ProcessPendingWorkItemAsync(EventModel expiredWorkIteModel)
    {
        var triggerDefinitions = new List<ATTriggerDefinition>();
        await using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            triggerDefinitions = DefinitionRepository.GetTriggerDefinitions(connection,
                expiredWorkIteModel.CurrentEventId,
                ATTriggerHook.OnExpireWorkItem);
        }

        foreach (var triggerDefinition in triggerDefinitions.Where(trigger => trigger.Hook == ATTriggerHook.OnExpireWorkItem))
        {
            await HandleTriggerAction(triggerDefinition, expiredWorkIteModel.CurrentEventType,
                expiredWorkIteModel.CurrentEventId,
                expiredWorkIteModel.WorkItemId, expiredWorkIteModel.SourceUser,
                expiredWorkIteModel.QueueId.GetValueOrDefault(), expiredWorkIteModel.SourceQueueId.GetValueOrDefault(),
                expiredWorkIteModel.LastAction);
        }
    }

    private async Task WriteTracking(EventModel eventModel)
    {
        try
        {
            await using var scope = _serviceProvider.CreateAsyncScope();
            var messageProcessor = scope.ServiceProvider.GetRequiredService<IMessageProcessor>();

            await using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();

                string sqlStr = "";
                string buttonId = "";

                var trackingDefinition = TrackingRepository.GetTrackingDefinition(connection,
                    eventModel.CurrentProcessId.GetValueOrDefault(), eventModel.CurrentEventId);
                if (trackingDefinition == null)
                {
                    return;
                }

                if (string.IsNullOrEmpty(trackingDefinition.Message))
                {
                    return;
                }

                int? knownQueueId = null;
                if (eventModel.CurrentEventType == ATEventType.Queue || eventModel.CurrentEventType == ATEventType.User)
                {
                    knownQueueId = Repository.GetQueueId(connection, eventModel.CurrentEventId);
                }

                var processedMessage = await messageProcessor.ProcessMessage(new MessageModel {
                    Message = trackingDefinition.Message,
                    WorkItemId = eventModel.WorkItemId,
                    SourceUser = eventModel.SourceUser,
                    SourceQueueId = eventModel.SourceQueueId.GetValueOrDefault(),
                    EventId = eventModel.CurrentEventId,
                    LastAction = eventModel.LastAction,
                    KnownQueueId = knownQueueId,
                });

                if (!string.IsNullOrEmpty(eventModel.LastAction))
                {
                    var actionButtonName = "";
                    int colonIndex = eventModel.LastAction.IndexOf(':');
                    if (colonIndex > 0 && colonIndex < eventModel.LastAction.Length - 1)
                    {
                        actionButtonName = eventModel.LastAction.Substring(colonIndex + 1);
                    }

                    var getButtonIndexQuery = $"SELECT [Id] FROM [ATButton] WHERE [ButtonName] = @actionButtonName";
                    buttonId = await GetRecordValueAsync(getButtonIndexQuery,
                        new Dictionary<string, object> { { "@actionButtonName", actionButtonName } });
                }

                //Write the tracking record
                string recCnt = "0";

                if (eventModel.CurrentEventType == ATEventType.Timer)
                {
                    sqlStr =
                        $"Select count(1) as recCnt from ATTimer WHERE workItemId = @workItemId AND ProcessId = @processId AND EventId = @eventId";
                    recCnt = await GetRecordValueAsync(sqlStr, new Dictionary<string, object>
                    {
                        { "@workItemId", eventModel.WorkItemId },
                        { "@processId", eventModel.CurrentProcessId.GetValueOrDefault() },
                        { "@eventId", eventModel.CurrentEventId }
                    });
                }

                if (recCnt == "0")
                {
                    TrackingRepository.InsertTrackingRecord(connection, trackingDefinition.Id, eventModel.WorkItemId,
                        eventModel.LastAction, eventModel.SourceQueueId.GetValueOrDefault(), eventModel.SourceUser,
                        buttonId,
                        processedMessage);
                    sqlStr =
                        @"Insert Into ATTrackingRecord (TrackingDate, WorkItemId, UserName, TrackingDefinitionId, LastActionId, Message) 
                                                Values (@trackingDate, @workItemId,@userName, @trackingDefinitionId, @lastActionid, @message)";

                    if (trackingDefinition.EnabledExternal)
                    {
                        var externalTrackingTemplates =
                            TrackingRepository.GetExternalTrackingData(connection, trackingDefinition.Id);
                        foreach (var externalTrackingTemplate in externalTrackingTemplates)
                        {
                            using (var externalConnection =
                                   new SqlConnection(externalTrackingTemplate.ConnectionString))
                            {
                                var tableToInsertDataTo =
                                    externalTrackingTemplate.ExternalTrackingDefinitions.First().DbTableName;
                                var stringColumns = externalTrackingTemplate.ExternalTrackingDefinitions
                                    .Select(def => def.DbColumnName.Replace("[", "").Replace("]", ""))
                                    .ToList();
                                var externalSqlStr =
                                    $"INSERT INTO {tableToInsertDataTo} ({string.Join(", ", stringColumns)}) VALUES ({string.Join(", ", stringColumns.Select(c => "@" + c))})";
                                var command = new SqlCommand(externalSqlStr, externalConnection);
                                foreach (var externalTrackingExternalTrackingDefinition in externalTrackingTemplate
                                             .ExternalTrackingDefinitions)
                                {
                                    string value = string.Empty;
                                    switch (externalTrackingExternalTrackingDefinition.SourceType)
                                    {
                                        case ATExternalTrackingSourceType.PDSystemValue:
                                            value = await messageProcessor.ProcessMessage(new MessageModel {
                                                Message = "{{" + externalTrackingExternalTrackingDefinition.SourceValue + "}}",
                                                WorkItemId = eventModel.WorkItemId,
                                                SourceUser = eventModel.SourceUser,
                                                SourceQueueId = eventModel.SourceQueueId.GetValueOrDefault(),
                                                EventId = eventModel.CurrentEventId,
                                                LastAction = eventModel.LastAction
                                            });
                                            break;
                                        case ATExternalTrackingSourceType.FolderField:
                                            value = FolderFieldRepository.GetFolderFieldInstanceValue(connection,
                                                eventModel.WorkItemId,
                                                externalTrackingExternalTrackingDefinition.SourceValue,
                                                eventModel.CurrentProcessId.GetValueOrDefault());
                                            break;
                                        case ATExternalTrackingSourceType.TextEntry:
                                            value = externalTrackingExternalTrackingDefinition.SourceValue;
                                            break;
                                    }

                                    var valueToInsert = ParseValueBySqlDbType(value,
                                        externalTrackingExternalTrackingDefinition.DbDataType);
                                    var parameterName = externalTrackingExternalTrackingDefinition.DbColumnName
                                        .Replace("[", "")
                                        .Replace("]", "");
                                    command.Parameters.AddWithValue("@" + parameterName, valueToInsert);
                                }

                                externalConnection.Open();
                                command.ExecuteNonQuery();
                                externalConnection.Close();
                            }
                        }
                    }
                }

                await connection.CloseAsync();
            }
        }
        catch (Exception ex)
        {
            _logger.Error("Error in WriteTracking. {message}", ex.Message);
        }
    }

    private async Task<string> GetRecordValueAsync(string sqlStatement, Dictionary<string, object> parameters = null)
    {
        string returnValue = "";
        await using (var connection = new SqlConnection(_connectionString))
        {
            try
            {
                await connection.OpenAsync();

                var sqlCommand = new SqlCommand(sqlStatement, connection);
                if (parameters?.Count > 0)
                {
                    foreach (var parameter in parameters)
                    {
                        sqlCommand.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }

                using var reader = await sqlCommand.ExecuteReaderAsync();
                if (await reader.ReadAsync())
                {
                    returnValue = reader[0]?.ToString() ?? "";
                }

                if (string.IsNullOrEmpty(returnValue))
                {
                    _logger.Warning("No value retrieved in GetRecordValueAsync for statement {SqlStatement}",
                        sqlStatement);
                }

                return returnValue;
            }
            catch (Exception ex)
            {
                await connection.CloseAsync();
                _logger.Error("Error for statement {SqlStatement}: {ErrorMessage}", sqlStatement, ex.Message);
                return "";
            }
        }
    }

    private static object ParseValueBySqlDbType(string value, string sqlDbType)
    {
        if (string.IsNullOrEmpty(value))
            return null;

        // Match type and size, e.g. "numeric(18, 0)", "char(10)"
        var typeMatch = Regex.Match(
            sqlDbType, @"^(\w+)\s*(?:\((\d+)(?:,\s*(\d+))?\))?$");
        var baseType = typeMatch.Groups[1].Value.ToLower();
        var precision = typeMatch.Groups[2].Success ? int.Parse(typeMatch.Groups[2].Value) : (int?)null;
        var scale = typeMatch.Groups[3].Success ? int.Parse(typeMatch.Groups[3].Value) : (int?)null;

        switch (baseType)
        {
            case "int":
            case "smallint":
            case "tinyint":
                if (int.TryParse(value, out int intResult))
                    return intResult;
                break;
            case "bigint":
                if (long.TryParse(value, out long bigIntResult))
                    return bigIntResult;
                break;
            case "float":
            case "real":
                if (double.TryParse(value, out double doubleResult))
                    return doubleResult;
                break;
            case "decimal":
            case "numeric":
            case "money":
            case "smallmoney":
                if (scale.HasValue && scale.Value == 0)
                {
                    if (long.TryParse(value, out long longResult))
                        return longResult;
                }

                if (decimal.TryParse(value, out decimal decimalResult))
                {
                    if (scale.HasValue)
                        decimalResult = Math.Round(decimalResult, scale.Value);
                    return decimalResult;
                }

                break;
            case "bit":
                if (value == "1" || value.ToLower() == "true")
                    return true;
                if (value == "0" || value.ToLower() == "false")
                    return false;
                break;
            case "datetime":
            case "smalldatetime":
            case "date":
            case "time":
            case "datetime2":
            case "datetimeoffset":
                if (DateTime.TryParse(value, out DateTime dateResult))
                    return dateResult;
                break;
            case "char":
            case "nchar":
                if (precision.HasValue)
                {
                    if (value.Length > precision.Value)
                        return value.Substring(0, precision.Value);
                    else
                        return value.PadRight(precision.Value, ' ');
                }

                return value;
            case "varchar":
            case "nvarchar":
                if (precision.HasValue && value.Length > precision.Value)
                    return value.Substring(0, precision.Value);
                return value;
            case "text":
            case "ntext":
            default:
                return value;
        }

        // If parsing fails, return the original string
        return value;
    }

    private async Task<bool> HandleTriggerAction(ATTriggerDefinition triggerDefinition, ATEventType eventType, int eventId,
        int workItemId, string sourceUser, int queueId, int sourceQueueId, string lastAction)
    {
        await using var scope = _serviceProvider.CreateAsyncScope();
        var messageProcessor = scope.ServiceProvider.GetRequiredService<IMessageProcessor>();
        var triggerProcessor = TriggerProcessorFactory.GetTriggerProcessor(triggerDefinition.Action, messageProcessor);

        using (var connection = new SqlConnection(_connectionString))
        {
            var eventIsQueue = eventType == ATEventType.Queue || eventType == ATEventType.User;

            bool triggerProcessed;
            if (triggerProcessor is EmailTriggerProcessor emailTriggerProcessor && eventIsQueue)
            {
                int? knownQueueId = Repository.GetQueueId(connection, eventId);
                triggerProcessed = await emailTriggerProcessor.ProcessTrigger(connection, triggerDefinition.TriggerAction,
                    workItemId, sourceUser, sourceQueueId, eventId, lastAction, knownQueueId);
            }
            else if (triggerProcessor is MoveToEventTriggerProcessor moveToEventTriggerProcessor && eventIsQueue)
            {
                int? knownQueueId = Repository.GetQueueId(connection, eventId);
                triggerProcessed = await moveToEventTriggerProcessor.ProcessTrigger(connection, triggerDefinition.TriggerAction, workItemId, knownQueueId);
            }
            else
            {
                triggerProcessed =
                    await triggerProcessor.ProcessTrigger(connection, triggerDefinition.TriggerAction, workItemId);
            }

            if (!triggerProcessed)
            {
                _logger.Error(
                    $"Trigger processing failed for trigger type {triggerDefinition.Action} in ProcessWorkItem for work item {workItemId}");
            }

            return triggerProcessed;
        }
    }

    private async Task WriteErrorAsync(int workItemId, Exception ex)
    {
        string message = ex.ToString();
        if (message.Length > 2000) message = message[..1999];

        await using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        const string selectContextSql = @"
                SELECT TOP (1)
                    wir.WorkItemReferenceId,
                    wir.EventID,
                    wi.WorkItemName,
                    pg.ProcessGroup,
                    pd.ProcessName
                FROM ATWorkItemReference wir
                JOIN ATWorkItem wi ON wi.WorkItemId = wir.WorkItemId
                JOIN ATProcessDefinition pd ON pd.ProcessId = wi.ProcessId
                JOIN ATProcessGroup pg ON pg.ProcessGroupId = pd.ProcessGroupId
                WHERE wir.WorkItemId = @workItemId
                ORDER BY 
                    wir.Expired ASC,
                    wir.WorkItemReferenceId DESC;";

        int workItemReferenceId = 0;
        int? eventId = null;
        string workItemName = "(unknown)";
        string processGroup = "(unknown)";
        string processName = "(unknown)";

        await using (var cmd = new SqlCommand(selectContextSql, connection))
        {
            cmd.Parameters.Add("@workItemId", SqlDbType.Int).Value = workItemId;

            await using var reader = await cmd.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                workItemReferenceId = reader.GetInt32(0);
                eventId = reader.IsDBNull(1) ? null : reader.GetInt32(1);
                workItemName = reader.IsDBNull(2) ? "(unknown)" : reader.GetString(2);
                processGroup = reader.IsDBNull(3) ? "(unknown)" : reader.GetString(3);
                processName = reader.IsDBNull(4) ? "(unknown)" : reader.GetString(4);
            }
        }

        string eventName = "(unknown)";
        if (eventId.HasValue && eventId.Value > 0)
        {
            const string selectEventNameSql = @"SELECT EventName FROM ATEventDefinition WHERE EventId = @eventId;";
            await using var cmd = new SqlCommand(selectEventNameSql, connection);
            cmd.Parameters.Add("@eventId", SqlDbType.Int).Value = eventId.Value;

            eventName = (string?)await cmd.ExecuteScalarAsync() ?? "(unknown)";
        }

        if (workItemReferenceId != 0)
        {
            const string updateWirSql = @"
                UPDATE ATWorkItemReference
                SET ServerRequest = 999,
                    LastActionDate = GETUTCDATE()
                WHERE WorkItemReferenceId = @workItemReferenceId;";

            await using var cmd = new SqlCommand(updateWirSql, connection);
            cmd.Parameters.Add("@workItemReferenceId", SqlDbType.Int).Value = workItemReferenceId;
            await cmd.ExecuteNonQueryAsync();
        }

        const string insertErrorSql = @"
                INSERT INTO ATErrors
                (
                    ErrorDate,
                    ProcessGroup,
                    ProcessName,
                    WorkItemName,
                    WorkItemId,
                    WorkItemReferenceId,
                    Event,
                    Message
                )
                VALUES
                (
                    GETUTCDATE(),
                    @processGroup,
                    @processName,
                    @workItemName,
                    @workItemId,
                    @workItemReferenceId,
                    @eventName,
                    @message
                );";

        await using (var cmd = new SqlCommand(insertErrorSql, connection))
        {
            cmd.Parameters.Add("@processGroup", SqlDbType.NVarChar, 255).Value = processGroup;
            cmd.Parameters.Add("@processName", SqlDbType.NVarChar, 255).Value = processName;
            cmd.Parameters.Add("@workItemName", SqlDbType.NVarChar, 255).Value = workItemName;

            cmd.Parameters.Add("@workItemId", SqlDbType.Int).Value = workItemId;
            cmd.Parameters.Add("@workItemReferenceId", SqlDbType.Int).Value = workItemReferenceId;

            cmd.Parameters.Add("@eventName", SqlDbType.NVarChar, 255).Value = eventName;
            cmd.Parameters.Add("@message", SqlDbType.NVarChar, 2000).Value = (object?)message ?? DBNull.Value;

            await cmd.ExecuteNonQueryAsync();
        }
    }
}