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

namespace IWS5.Server.EventProcessors;

public class InboxProcessor(Serilog.ILogger logger, IConfiguration configuration)
    : EventProcessor(logger, configuration)
{
    public override async Task<bool> ProcessEventAsync(EventModel eventData, int processId)
    {
        Logger.Information(
            "Starting inbox event processing for WorkItemId: {WorkItemId}, EventId: {EventId}, SourceQueueId: {SourceQueueId}",
            eventData.WorkItemId, eventData.CurrentEventId, eventData.SourceQueueId);

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

                Logger.Debug("Retrieving queue ID for EventId: {EventId}", eventData.CurrentEventId);
                int queueId = Repository.GetQueueId(sqlConnection, eventData.CurrentEventId);
                Logger.Debug("Found queue ID: {QueueId} for EventId: {EventId}", queueId, eventData.CurrentEventId);

                string assignedTo = null;

                if (eventData.SourceQueueId == queueId)
                {
                    Logger.Information(
                        "Work item already in target queue (SourceQueueId: {SourceQueueId} == QueueId: {QueueId}), moving to next event",
                        eventData.SourceQueueId, queueId);

                    await using (var command = new SqlCommand("", sqlConnection))
                    {
                        command.CommandText =
                            "Update ATWorkItemReference SET QueueId = NULL, EventID = @nextEventId, ServerRequestParam = NULL, LastActionDate = @currentDate, AssignedTo = null WHERE WorkItemID = @workItemId";
                        command.Parameters.AddWithValue("@nextEventId", eventData.NextEventId);
                        command.Parameters.AddWithValue("@currentDate", DateTime.Now);
                        command.Parameters.AddWithValue("@workItemId", eventData.WorkItemId);
                        command.ExecuteNonQuery();
                        Logger.Debug("Updated work item reference to move to next event: {NextEventId}",
                            eventData.NextEventId);
                        return true;
                    }
                }

                Logger.Debug("Retrieving inbox assignment configuration for EventId: {EventId}",
                    eventData.CurrentEventId);
                var assignment = GetInboxAssignment(sqlConnection, eventData.CurrentEventId);
                if (assignment == null)
                {
                    Logger.Error("No inbox assignment found for event ID: {EventId}, WorkItemId: {WorkItemId}",
                        eventData.CurrentEventId, eventData.WorkItemId);
                    return false;
                }

                Logger.Debug("Found inbox assignment - Type: {AssignmentType}, Value: {AssignmentValue}",
                    assignment.AtAssigmentType, assignment.AssigmentValue ?? "NULL");

                switch (assignment.AtAssigmentType)
                {
                    case ATAssignmentType.NoneSelected:
                        Logger.Debug("No assignment type selected, processing as queue event only");
                        break;

                    case ATAssignmentType.FolderField:
                        Logger.Debug("Processing folder field assignment with field name: {FieldName}",
                            assignment.AssigmentValue);
                        assignedTo = ProcessFolderFieldAssignment(sqlConnection, eventData.WorkItemId,
                            assignment.AssigmentValue, eventData.CurrentProcessId.GetValueOrDefault());
                        Logger.Information("Folder field assignment result: {AssignedTo}", assignedTo ?? "NULL");
                        break;

                    case ATAssignmentType.SystemVariable:
                        Logger.Debug("Processing system variable assignment with variable: {SystemVariable}",
                            assignment.AssigmentValue);
                        assignedTo = ProcessSystemVariableAssignment(sqlConnection, eventData.WorkItemId,
                            assignment.AssigmentValue);
                        Logger.Information("System variable assignment result: {AssignedTo}", assignedTo ?? "NULL");
                        break;

                    case ATAssignmentType.AssignedName:
                        Logger.Debug("Using direct name assignment");
                        assignedTo = assignment.AssigmentValue;
                        Logger.Information("Direct assignment result: {AssignedTo}", assignedTo);
                        break;

                    default:
                        Logger.Error("Unknown assignment type: {AssignmentType}, WorkItemId: {WorkItemId}",
                            assignment.AtAssigmentType, eventData.WorkItemId);
                        return false;
                }

                if (string.IsNullOrWhiteSpace(assignedTo))
                {
                    Logger.Error(
                        "No assigned user determined for inbox assignment. WorkItemId: {WorkItemId}, AssignmentType: {AssignmentType}",
                        eventData.WorkItemId, assignment.AtAssigmentType);
                    return false;
                }

                Logger.Debug("Updating work item reference - QueueId: {QueueId}, AssignedTo: {AssignedTo}", queueId,
                    assignedTo);
                UpdateWorkItemReference(sqlConnection, eventData.WorkItemId, eventData.CurrentEventId, queueId,
                    assignedTo);

                Logger.Information(
                    "Successfully completed inbox processing for WorkItemId: {WorkItemId}, assigned to: {AssignedTo}",
                    eventData.WorkItemId, assignedTo);
                return true;
            }
        }
        catch (Exception ex)
        {
            Logger.Error(ex, "Error in ProcessInbox for WorkItemId: {WorkItemId}, EventId: {EventId}",
                eventData.WorkItemId, eventData.CurrentEventId);
            return false;
        }
    }

    private InboxAssignment GetInboxAssignment(SqlConnection sqlConnection, int eventId)
    {
        Logger.Debug("Querying inbox assignment for EventId: {EventId}", eventId);

        var assignment = new InboxAssignment();
        string sqlQuery =
            $"SELECT [EventID], [AssignmentTypeId], [InboxAssignment] FROM [ATInboxEvent] WHERE [EventID] = {eventId}";

        SqlDataAdapter dap = new SqlDataAdapter(sqlQuery, sqlConnection);
        DataSet ds = new DataSet();
        dap.Fill(ds);

        if (ds.Tables[0].Rows.Count == 0)
        {
            Logger.Warning("No inbox assignment configuration found for EventId: {EventId}", eventId);
            return null;
        }

        foreach (DataRow tr in ds.Tables[0].Rows)
        {
            assignment.EventId = (int)tr[0];
            assignment.AtAssigmentType = (ATAssignmentType)tr[1];
            assignment.AssigmentValue = tr[2].ToString();
        }

        Logger.Debug("Retrieved inbox assignment configuration successfully");
        return assignment;
    }

    private string ProcessFolderFieldAssignment(SqlConnection connection, int workItemId, string fieldName,
        int processId)
    {
        Logger.Debug("Processing folder field assignment - FieldName: {FieldName}, ProcessId: {ProcessId}", fieldName,
            processId);

        string sqlQuery =
            $"SELECT [FieldID] FROM [ATFieldDefinition] WHERE [FieldName] = N'{fieldName}' AND [ProcessID] = {processId}";

        var fieldIdString = Repository.GetRecordValue(connection, sqlQuery);
        if (string.IsNullOrEmpty(fieldIdString))
        {
            Logger.Warning("Field definition not found for FieldName: {FieldName}, ProcessId: {ProcessId}", fieldName,
                processId);
            return null;
        }

        var fieldId = int.Parse(fieldIdString);
        Logger.Debug("Found field definition with FieldId: {FieldId}", fieldId);

        sqlQuery =
            $"SELECT [DataType] FROM [ATFieldDefinition] WHERE [FieldName] = N'{fieldName}' AND [ProcessID] = {processId}";
        var dataType = (FieldInstanceType)Convert.ToByte(int.Parse(Repository.GetRecordValue(connection, sqlQuery)));

        switch (dataType)
        {
            case FieldInstanceType.String:
                sqlQuery = $"SELECT [FieldValue] FROM [ATFieldInstanceString] WHERE [WorkItemID] = {workItemId} AND [FieldID] = {fieldId}";
                break;
            case FieldInstanceType.Option:
                sqlQuery = $"SELECT [FieldValue] FROM [ATFieldInstanceOption] WHERE [WorkItemID] = {workItemId} AND [FieldID] = {fieldId}";
                break;
        }

        var fieldValue = Repository.GetRecordValue(connection, sqlQuery);
        Logger.Debug("Retrieved field value: {FieldValue} for WorkItemId: {WorkItemId}, FieldId: {FieldId}",
            fieldValue ?? "NULL", workItemId, fieldId);

        return fieldValue;
    }

    private string ProcessSystemVariableAssignment(SqlConnection connection, int workItemId, string variableStringId)
    {
        Logger.Debug("Processing system variable assignment - VariableId: {VariableId}, WorkItemId: {WorkItemId}",
            variableStringId, workItemId);

        var userSystemVariable = (ATUserSystemVariable)int.Parse(variableStringId);
        Logger.Debug("Parsed system variable: {SystemVariable}", userSystemVariable);

        string getVariableValueQuery = string.Empty;
        if (userSystemVariable == ATUserSystemVariable.creator ||
            userSystemVariable == ATUserSystemVariable.lockedBy)
        {
            getVariableValueQuery =
                $"SELECT [{userSystemVariable.ToString()}] FROM [ATWorkItem] WHERE [WorkItemID] = @workItemId";
            Logger.Debug("Using ATWorkItem table for system variable lookup");
        }
        else if (userSystemVariable == ATUserSystemVariable.lastAction ||
                 userSystemVariable == ATUserSystemVariable.sourceUser)
        {
            getVariableValueQuery =
                $"SELECT [{userSystemVariable.ToString()}] FROM [ATWorkItemReference] WHERE [WorkItemID] = @workItemId";
            Logger.Debug("Using ATWorkItemReference table for system variable lookup");
        }
        else
        {
            Logger.Error("Unsupported system variable for assignment: {SystemVariable}, WorkItemId: {WorkItemId}",
                userSystemVariable, workItemId);
            return null;
        }

        var result = Repository.GetRecordValue(connection, getVariableValueQuery,
            new Dictionary<string, object>() { { "@workItemId", workItemId } });
        Logger.Debug("Retrieved system variable raw value: {RawValue}", result ?? "NULL");

        if (!string.IsNullOrWhiteSpace(result) && userSystemVariable == ATUserSystemVariable.lastAction)
        {
            var colonIndex = result.IndexOf(':');
            if (colonIndex > 0 && colonIndex < result.Length - 1)
            {
                var originalResult = result;
                result = result.Substring(0, colonIndex);
                Logger.Debug("Processed lastAction value: {OriginalValue} -> {ProcessedValue}", originalResult, result);
            }
            else
            {
                Logger.Debug("LastAction value has no valid colon separator, setting to empty");
                result = string.Empty;
            }
        }

        return result;
    }

    private void UpdateWorkItemReference(SqlConnection connection, int workItemId, int eventId, int queueId,
        string assignedTo)
    {
        Logger.Debug(
            "Updating work item reference - WorkItemId: {WorkItemId}, EventId: {EventId}, QueueId: {QueueId}, AssignedTo: {AssignedTo}",
            workItemId, eventId, queueId, assignedTo);

        SqlCommand cmd = new SqlCommand("", connection);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.Append("UPDATE [ATWorkItemReference] SET ");

        if (!(queueId <= 0))
        {
            sqlBuilder.Append("[QueueId] = @queueId, ");
            cmd.Parameters.AddWithValue("@queueId", queueId);
            Logger.Debug("Added QueueId parameter: {QueueId}", queueId);
        }

        sqlBuilder.Append($"[EventID] = {eventId}, ");

        if (!string.IsNullOrEmpty(assignedTo))
        {
            sqlBuilder.Append("[AssignedTo] = @assignedTo, ");
            cmd.Parameters.AddWithValue("@assignedTo", assignedTo);
            Logger.Debug("Added AssignedTo parameter: {AssignedTo}", assignedTo);
        }

        sqlBuilder.Append("[ArrivalDate] = @arrivalDate, ");
        sqlBuilder.Append("[LastActionDate] = @lastActionDate, ");
        sqlBuilder.Append("[ServerRequest] = 0, ");
        sqlBuilder.Append("[ServerRequestParam] = NULL ");
        sqlBuilder.Append("WHERE [WorkItemID] = @workItemID");

        cmd.CommandText = sqlBuilder.ToString();
        cmd.Parameters.AddWithValue("@arrivalDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@lastActionDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@workItemID", workItemId);

        Logger.Debug("Executing work item reference update query");
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();

        Logger.Information("Successfully updated work item reference for WorkItemId: {WorkItemId}", workItemId);
    }
}