using IntegrationKSSS.WebService.Infrastructure.Data;
using IntegrationKSSS.WebService.Infrastructure.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using ILogger = NLog.ILogger;

namespace IntegrationKSSS.WebService.Infrastructure.Repositories;

/// <summary>
/// A set of repository function implementations
/// </summary>
public class KsssRepository : KsssRepositoryBase, IKsssRepository
{
    #region Constructors
    public KsssRepository(RDnLDbContext rDnLDbContext, ILogger logger) : base(rDnLDbContext, logger) { }
    #endregion

    #region Methods
    /// <summary>
    /// Data recording on the specification infocard
    /// </summary>
    /// <param name="ksssDictionary"></param>
    /// <returns></returns>
    public async Task WriteKsssData(Dictionary<string, string> ksssDictionary)
    {
        try
        {
            decimal specificationId = 0;
            decimal.TryParse(ksssDictionary["SP"], out specificationId);
            if (specificationId == 0)
            {
                Logger.Info(MessageList.SpecificationNotFound);
                return;
            }
            decimal ssDev = 1000012;
            string nameInfoCard = "KSSS_Data";

            var specification = RDnLDbContext.RndtSp.FirstOrDefault(c => c.SP == specificationId &&
                                                                    c.ALLOW_MODIFY == '1' &&
                                                                    c.SS == ssDev);
            if (specification == null)
            {
                Logger.Info(MessageList.SpecificationNotFound);
                return;
            }


            var infoCardKsss = RDnLDbContext.RndtSpIc.FirstOrDefault(infoCard => infoCard.SP == specification.SP &&
                                                                     infoCard.IC_SHORT_DESC == nameInfoCard &&
                                                                     infoCard.SP_VERSION == specification.SP_VERSION);

            if (infoCardKsss == null)
            {
                Logger.Info(MessageList.KsssInfoCardNotFound);
                return;
            }

            var infoFields = RDnLDbContext.RndtSpIi.Where(infoField => infoField.IC == infoCardKsss.IC &&
                                                          infoField.SP == specificationId);


            if (infoFields.Count() == 0)
            {
                Logger.Info(MessageList.KsssInfoCardEmpty);
                return;
            }

            foreach (var ksssValuePair in ksssDictionary)
            {
                if (ksssValuePair.Key == "SP")
                {
                    continue;
                }

                var foundedInfoField = infoFields.FirstOrDefault(infoField => infoField.II_SHORT_DESC == ksssValuePair.Key);
                if (foundedInfoField == null)
                {
                    continue;
                }
                var relationValue = GetRelationValue(foundedInfoField, ksssValuePair.Value);
                if (string.IsNullOrEmpty(relationValue))
                {
                    foundedInfoField.IIVALUE = ksssValuePair.Value;
                }
                else
                {
                    foundedInfoField.IIVALUE = relationValue;
                }
            }
            await RDnLDbContext.SaveChangesAsync();
            Logger.Info(MessageList.SuccessfullyKsssInfoCardRecord);
        }
        catch (Exception ex)
        {

            Logger.Error(ex);
        }
       
    }
    /// <summary>
    /// Getting the value relation
    /// </summary>
    /// <param name="rndtSpIi"></param>
    /// <param name="tableValue"></param>
    /// <returns></returns>
    private string GetRelationValue(RndtSpIi rndtSpIi, string tableValue)
    {
        string result = string.Empty;
        var rndvIpIeAu = RDnLDbContext.RndtIpIeAu.Where(c => c.IE == rndtSpIi.II)
                                                 .OrderByDescending(c => c.VERSION)
                                                 .FirstOrDefault();
        var tableName = rndvIpIeAu?.VALUE;
        if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(tableValue))
        {
            var sqlQuery = $"SELECT Name FROM [RndSuite].[{tableName}] WHERE [Id] = @TableId";
            var tableId = new SqlParameter("@TableId", tableValue);
            result = RDnLDbContext.Database.SqlQueryRaw<string>(sqlQuery, tableId)
                                           .ToList()
                                           .FirstOrDefault();
        }

        return result;
    }
    #endregion
}
