using System.Data;
using IWS5.Server.Models;
using Microsoft.Data.SqlClient;

namespace IWS5.Server.DataAccess;

internal static class FolderFieldRepository
{
    internal static List<FolderField> GetFolderFields(SqlConnection connection, int workItemId, int processId)
    {
        var folderFields = new List<FolderField>();

        const string folderFieldsQuery = @"
           SELECT 
             df.FieldName, 
             COALESCE(
               CONVERT(nvarchar(1000), fid.FieldValue), 
               CONVERT(nvarchar(1000), fin.FieldValue), 
               CONVERT(nvarchar(1000), fis.FieldValue), 
               CONVERT(nvarchar(1000), df.InitialValue)
             ) AS FieldValue
           FROM [dbo].[ATFieldDefinition] df
           LEFT JOIN ATFieldInstanceDate fid ON df.FieldID = fid.FieldID
           LEFT JOIN ATFieldInstanceNumber fin ON df.FieldID = fin.FieldID
           LEFT JOIN ATFieldInstanceString fis ON df.FieldID = fis.FieldID
           LEFT JOIN ATFieldInstanceOption fio ON df.FieldID = fio.FieldID
           WHERE df.ProcessID = @processId AND (fid.WorkItemID IS NULL OR fid.WorkItemID = @workItemId) AND (fin.WorkItemID IS NULL OR fin.WorkItemID = @workItemId) AND (fin.WorkItemID IS NULL OR fin.WorkItemID = @workItemId)";

        using var command = new SqlCommand(folderFieldsQuery, connection);
        command.Parameters.AddWithValue("@processId", processId);
        command.Parameters.AddWithValue("@workItemId", workItemId);
        var dataAdapter = new SqlDataAdapter(command);
        var dataSet = new DataSet();
        dataAdapter.Fill(dataSet);

        if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
        {
            return folderFields;
        }

        foreach (DataRow row in dataSet.Tables[0].Rows)
        {
            var fieldName = row["FieldName"].ToString();
            var fieldValue = row["FieldValue"].ToString();

            if (!string.IsNullOrEmpty(fieldName) && !string.IsNullOrEmpty(fieldValue))
            {
                folderFields.Add(new FolderField(fieldName, fieldValue));
            }
        }

        return folderFields;
    }

    internal static string GetFolderFieldInstanceValue(SqlConnection connection, int workItemId, string fieldName, int processId)
    {
        const string sqlStr = @"
            SELECT COALESCE(
                CONVERT(nvarchar(1000), fid.FieldValue), 
                CONVERT(nvarchar(1000), fin.FieldValue), 
                CONVERT(nvarchar(1000), fis.FieldValue),
                CONVERT(nvarchar(1000), df.InitialValue)
            ) AS FieldValue
            FROM [dbo].[ATFieldDefinition] df
            LEFT JOIN ATFieldInstanceDate fid ON df.FieldID = fid.FieldID AND fid.WorkItemID = @workItemId
            LEFT JOIN ATFieldInstanceNumber fin ON df.FieldID = fin.FieldID AND fin.WorkItemID = @workItemId
            LEFT JOIN ATFieldInstanceString fis ON df.FieldID = fis.FieldID AND fis.WorkItemID = @workItemId
            LEFT JOIN ATFieldInstanceOption fio ON df.FieldID = fio.FieldID AND fio.WorkItemID = @workItemId
            WHERE df.ProcessID = @processId AND df.FieldName = @fieldName";

        using var command = new SqlCommand(sqlStr, connection);
        command.Parameters.AddWithValue("@processId", processId);
        command.Parameters.AddWithValue("@workItemId", workItemId);
        command.Parameters.AddWithValue("@fieldName", fieldName);

        return command.ExecuteScalar()?.ToString();
    }
}   