using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.Item;
using MMDAL.Query.Party;
using System.Text;

namespace MMDAL.CustomCode.Party
{
    public class PartyDAL : IPartyDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public PartyDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<IEnumerable<ItemDetailDTO>> GetPartyDetailsQualityParameter(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                string innerSql = ApplyPartyQualityCriteria(
                    PartyQB.GET_PARTYDETAILS_QUALITY_INNER, criteriaDTO);

                string fullSql = $"WITH PagedParty AS ({innerSql}) {PartyQB.GET_PARTYDETAILS_QUALITY_OUTER}";

                var parameters = new { firstnumber = firstNumber, maxresult = maxResult };
                var result = await _queryExecutor.QueryAsync<ItemDetailDTO>(loginDTO, fullSql, parameters);
                return result ?? Enumerable.Empty<ItemDetailDTO>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static string ApplyPartyQualityCriteria(string baseSql, CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return baseSql;

            var fieldMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                { "PartyId",        "pa.PARTYID"              },
                { "Party.Id",       "pa.PARTYID"              },
                { "ParameterSetId", "paramset.PARAMETERSETID" },
            };

            var sectionAttrs = criteriaDTO.SectionCriteriaList[0]?.AttributesCriteriaList
                               ?? new List<AttributesCriteriaDTO>();

            var conditions = new List<(string Cond, CriteriaDTO.AttributeJoinOperationType JoinToNext)>();
            foreach (var attr in sectionAttrs)
            {
                if (!fieldMap.TryGetValue(attr.FieldName ?? "", out string col)) continue;

                string val = attr.FieldValue?.ToString() ?? "";
                if (string.IsNullOrWhiteSpace(val)) continue;

                string cond = BuildConditionSql(col, attr, val);
                if (cond != null)
                    conditions.Add((cond, attr.JoinType));
            }

            var sb = new StringBuilder(baseSql);
            int idx = 0;
            while (idx < conditions.Count)
            {
                var orGroup = new List<string> { conditions[idx].Cond };
                while (idx < conditions.Count - 1 &&
                       conditions[idx].JoinToNext == CriteriaDTO.AttributeJoinOperationType.Or)
                { idx++; orGroup.Add(conditions[idx].Cond); }

                sb.Append(orGroup.Count > 1
                    ? $" AND ({string.Join(" OR ", orGroup)})"
                    : $" AND {orGroup[0]}");
                idx++;
            }

            return sb.ToString();
        }

        private static string BuildConditionSql(string col, AttributesCriteriaDTO attr, string val)
        {
            return attr.OperationType switch
            {
                CriteriaDTO.OperationType.Equal               => $"{col} = {val}",
                CriteriaDTO.OperationType.NotEqual            => $"{col} <> {val}",
                CriteriaDTO.OperationType.Like                => $"{col} LIKE '%{val}%'",
                CriteriaDTO.OperationType.StartWith           => $"{col} LIKE '{val}%'",
                CriteriaDTO.OperationType.EndsWith            => $"{col} LIKE '%{val}'",
                CriteriaDTO.OperationType.GreaterThan         => $"{col} > {val}",
                CriteriaDTO.OperationType.GreaterThanOrEqualTo => $"{col} >= {val}",
                CriteriaDTO.OperationType.LessThan            => $"{col} < {val}",
                CriteriaDTO.OperationType.LessThanOrEqualTo   => $"{col} <= {val}",
                _ => null
            };
        }
    }
}