using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MathNet.Numerics.Distributions;
using MMDAL.DTO.ItemParty;
using MMDAL.Query.ItemParty;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.ItemParty
{
    public class ItemPartyDAL : IItemPartyDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public ItemPartyDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        public async Task<string> GetSelectListItemParty(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var attributes = CriteriaDTO?.SectionCriteriaList?
                    .Where(section => section?.AttributesCriteriaList != null)
                    .SelectMany(section => section.AttributesCriteriaList)
                    .ToList() ?? new List<AttributesCriteriaDTO>();

                int? itemid = GetCriteriaInt(attributes, "ItemId", "Item.Id");
                int? partyid = GetCriteriaInt(attributes, "PartyId", "Party.Id");
                int? processid = GetCriteriaInt(attributes, "ProcessId", "Process.Id");
                int? status = GetCriteriaInt(attributes, "Status");
                string code = GetCriteriaString(attributes, "ITEMPARTYCODE");
                string name = GetCriteriaString(attributes, "ITEMPARTYNAME");

                var parameters = new
                {
                    itemid,
                    partyid,
                    processid,
                    status,
                    code = string.IsNullOrWhiteSpace(code) ? null : code,
                    name = string.IsNullOrWhiteSpace(name) ? null : name,
                    // FirstNumber is 1-based; -1 is the "no paging" sentinel and passes through unchanged.
                    skip = FirstNumber == -1 ? -1 : FirstNumber - 1,
                    take = MaxResult
                };

                string SQL = ItemPartyQB.GET_SELECTLIST_ITEMPARTY;
                var Result = await _QueryExecutor.QueryAsync<ItemPartyPicklistDTO>(LoginDTO, SQL, parameters);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static int? GetCriteriaInt(List<AttributesCriteriaDTO> attributes, params string[] fieldNames)
        {
            var value = NormalizeCriteriaValue(attributes
                .FirstOrDefault(attr => fieldNames.Any(fieldName => string.Equals(attr.FieldName, fieldName, StringComparison.OrdinalIgnoreCase)))
                ?.FieldValue);

            if (value == null) return null;
            if (value is string s && string.IsNullOrWhiteSpace(s)) return null;

            return Convert.ToInt32(value);
        }

        private static string GetCriteriaString(List<AttributesCriteriaDTO> attributes, string fieldName)
        {
            var value = NormalizeCriteriaValue(attributes
                .FirstOrDefault(attr => string.Equals(attr.FieldName, fieldName, StringComparison.OrdinalIgnoreCase))
                ?.FieldValue)?.ToString();

            return string.IsNullOrWhiteSpace(value) ? null : value;
        }

        // CriteriaDTO.FieldValue deserializes as a boxed System.Text.Json.JsonElement — unwrap it
        // to a native CLR value before using it, otherwise Convert.ToInt32/Dapper binding fails.
        private static object NormalizeCriteriaValue(object value)
        {
            if (value is not System.Text.Json.JsonElement je)
                return value;

            return je.ValueKind switch
            {
                System.Text.Json.JsonValueKind.String => je.GetString(),
                System.Text.Json.JsonValueKind.Number => je.TryGetInt32(out var i) ? i : je.GetDouble(),
                System.Text.Json.JsonValueKind.True => true,
                System.Text.Json.JsonValueKind.False => false,
                System.Text.Json.JsonValueKind.Null => null,
                _ => je.ToString()
            };
        }
    }
}