using IWS5.Core.Constants;
using IWS5.Core.Enums;
using IWS5.Core.Models.Request;
using IWS5.DatabaseAccess.IwsDataContext;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace IWS5.DatabaseAccess.Helpers;

internal static class DocumentTemplateDefTableHelper
{
    internal static async Task InsertRecordIntoTemplateTableAsync(
        string documentTemplateName,
        int documentId,
        IEnumerable<UpdateDocumentFieldRequest> fieldsToUpdate,
        GreenfieldPd2015Context context)
    {
        // Create fields to insert, including the "DocumentId" field
        var fieldsToInsert = new List<UpdateDocumentFieldRequest>
        {
            new()
            {
                FieldName = "DocumentId",
                FieldValue = documentId.ToString(),
                FieldType = ATDocumentFieldType.Integer
            }
        };

        fieldsToInsert.AddRange(fieldsToUpdate.Select(field => new UpdateDocumentFieldRequest
        {
            FieldName = field.FieldName,
            FieldValue = field.FieldValue
        }));

        // Build the insert query dynamically
        var insertQuery = $"INSERT INTO {DocumentTemplateTableConstants.GetDocumentTemplateTableName(documentTemplateName)} ({string.Join(", ", fieldsToInsert.Select(f => f.FieldName))}) VALUES ({string.Join(", ", fieldsToInsert.Select(f => "@" + f.FieldName))})";
        var parameters = fieldsToInsert.Select(f => new SqlParameter("@" + f.FieldName, f.FieldValue)).ToArray();

        // Execute the insert query
        await context.Database.ExecuteSqlRawAsync(insertQuery, parameters);
    }
}