using IWS5.Core.Enums;
using IWS5.Core.Models.TriggerAction;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using PDServer.Helpers.TriggerProcessors;
using System.Data;

namespace IWS5.Server.Helpers.TriggerProcessors;

public class AssignFolderFieldValueFromQueryTriggerProcessor : ITriggerProcessor
{
    public async Task<bool> ProcessTrigger(SqlConnection sqlConnection, string triggerAction, int workItemId)
    {
        var model = JsonConvert.DeserializeObject<AssignFolderFieldValueFromQueryTriggerAction>(triggerAction);
        
        const string getFieldTypeQuery =
            @"SELECT TOP 1 [DataType]
              FROM [dbo].[ATFieldDefinition] 
              WHERE [FieldID] = @fieldId AND [ProcessID] = @processId";

        // Get the field data type
        using var fieldTypeCmd = new SqlCommand(getFieldTypeQuery, sqlConnection);
        fieldTypeCmd.Parameters.AddWithValue("@fieldId", model.FieldId);
        fieldTypeCmd.Parameters.AddWithValue("@processId", model.ProcessId);

        if (sqlConnection.State != ConnectionState.Open)
            sqlConnection.Open();

        var dataTypeResult = fieldTypeCmd.ExecuteScalar();
        
        using var cmd = new SqlCommand(model.SqlQuery, sqlConnection);
        cmd.Parameters.AddWithValue("@workItemId", workItemId);

        var valueToAssign = cmd.ExecuteScalar();

        var dataType = (FieldInstanceType)Convert.ToByte(dataTypeResult);
        var newFieldValue = valueToAssign?.ToString();

        if (string.IsNullOrWhiteSpace(newFieldValue)) 
            return true;
        
        switch (dataType)
        {
            case FieldInstanceType.Date:
                if(DateTime.TryParse(newFieldValue, out var date))
                    UpdateDateFieldValue(sqlConnection, workItemId, model.FieldId, date);
                else
                    return false;
                break;
            case FieldInstanceType.Number:
                if (decimal.TryParse(newFieldValue, out var number))
                    UpdateNumberFieldValue(sqlConnection, workItemId, model.FieldId, number);
                else
                    return false;
                break;
            case FieldInstanceType.String:
                UpdateStringFieldValue(sqlConnection, workItemId, model.FieldId, newFieldValue);
                break;
            case FieldInstanceType.Option:
                UpdateOptionFieldValue(sqlConnection, workItemId, model.FieldId, newFieldValue);
                break;
        }

        return true;
    }
    
    private void UpdateDateFieldValue(SqlConnection sqlConnection, int workItemId, int fieldId, DateTime newFieldValue)
    {
        const string mergeQuery = @"
        MERGE [dbo].[ATFieldInstanceDate] AS target
        USING (SELECT @workItemId AS WorkItemID, @fieldId AS FieldID) AS source
        ON target.WorkItemID = source.WorkItemID AND target.FieldID = source.FieldID
        WHEN MATCHED THEN 
            UPDATE SET FieldValue = @newFieldValue
        WHEN NOT MATCHED THEN
            INSERT (WorkItemID, FieldID, FieldValue)
            VALUES (@workItemId, @fieldId, @newFieldValue);";

        using var cmd = new SqlCommand(mergeQuery, sqlConnection);
        cmd.Parameters.AddWithValue("@workItemId", workItemId);
        cmd.Parameters.AddWithValue("@fieldId", fieldId);
        cmd.Parameters.AddWithValue("@newFieldValue", newFieldValue);
    
        cmd.ExecuteNonQuery();
    }
    
    private void UpdateNumberFieldValue(SqlConnection sqlConnection, int workItemId, int fieldId, decimal newFieldValue)
    {
        const string mergeQuery = @"
        MERGE [dbo].[ATFieldInstanceNumber] AS target
        USING (SELECT @workItemId AS WorkItemID, @fieldId AS FieldID) AS source
        ON target.WorkItemID = source.WorkItemID AND target.FieldID = source.FieldID
        WHEN MATCHED THEN 
            UPDATE SET FieldValue = @newFieldValue
        WHEN NOT MATCHED THEN
            INSERT (WorkItemID, FieldID, FieldValue)
            VALUES (@workItemId, @fieldId, @newFieldValue);";

        using var cmd = new SqlCommand(mergeQuery, sqlConnection);
        cmd.Parameters.AddWithValue("@workItemId", workItemId);
        cmd.Parameters.AddWithValue("@fieldId", fieldId);
        cmd.Parameters.AddWithValue("@newFieldValue", newFieldValue);
    
        cmd.ExecuteNonQuery();
    }

    private void UpdateStringFieldValue(SqlConnection sqlConnection, int workItemId, int fieldId, string newFieldValue)
    {
        const string mergeQuery = @"
        MERGE [dbo].[ATFieldInstanceString] AS target
        USING (SELECT @workItemId AS WorkItemID, @fieldId AS FieldID) AS source
        ON target.WorkItemID = source.WorkItemID AND target.FieldID = source.FieldID
        WHEN MATCHED THEN 
            UPDATE SET FieldValue = @newFieldValue
        WHEN NOT MATCHED THEN
            INSERT (WorkItemID, FieldID, FieldValue)
            VALUES (@workItemId, @fieldId, @newFieldValue);";

        using var cmd = new SqlCommand(mergeQuery, sqlConnection);
        cmd.Parameters.AddWithValue("@workItemId", workItemId);
        cmd.Parameters.AddWithValue("@fieldId", fieldId);
        cmd.Parameters.AddWithValue("@newFieldValue", newFieldValue);
    
        cmd.ExecuteNonQuery();
    }

    private void UpdateOptionFieldValue(SqlConnection sqlConnection, int workItemId, int fieldId, string newFieldValue)
    {
        const string mergeQuery = @"
        MERGE [dbo].[ATFieldInstanceOption] AS target
        USING (SELECT @workItemId AS WorkItemID, @fieldId AS FieldID) AS source
        ON target.WorkItemID = source.WorkItemID AND target.FieldID = source.FieldID
        WHEN MATCHED THEN 
            UPDATE SET FieldValue = @newFieldValue
        WHEN NOT MATCHED THEN
            INSERT (WorkItemID, FieldID, FieldValue)
            VALUES (@workItemId, @fieldId, @newFieldValue);";

        using var cmd = new SqlCommand(mergeQuery, sqlConnection);
        cmd.Parameters.AddWithValue("@workItemId", workItemId);
        cmd.Parameters.AddWithValue("@fieldId", fieldId);
        cmd.Parameters.AddWithValue("@newFieldValue", newFieldValue);

        cmd.ExecuteNonQuery();
    }
}