using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.ItemAlias;
using MMDAL.Query.ItemAlias;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.ItemAlias
{
    public class ItemAliasDAL : IItemAliasDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public ItemAliasDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }

        public async Task<string> GetSelectListItemAlias(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            // Criteria field-name → SQL column mapping (matches GB4 ApplyItemAliasSelectList)
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "ItemId",      "ital.ITEMID"           },
                { "ItemAliasId", "ital.ITEMALIASID"      },
                { "ItemGroupId", "ital.ALIASITEMGROUPID" },
            };

            try
            {
                string template = LoginDTO.DatabaseType switch
                {
                    DBType.SQL => ItemAliasQB.GET_SELECTLIST_ITEMALIAS_SQL,
                    DBType.PostGre => ItemAliasQB.GET_SELECTLIST_ITEMALIAS_PG,
                    _ => throw new Exception("Unsupported database type")
                };
                bool isPostgre = LoginDTO.DatabaseType == DBType.PostGre;

                string additionalConditions = ApplyItemAliasCriteria(CriteriaWithQueryField, CriteriaDTO, out string nameSearch);

                if (!string.IsNullOrWhiteSpace(nameSearch))
                {
                    // GB4 built this filter via raw string concatenation (SQL-injection vector).
                    // GB5 must always send it as a bound parameter — never inline the search text.
                    string nameParam = isPostgre ? ":Name" : "@Name";
                    additionalConditions += $" AND (ital.ALIASITEMNAME LIKE {nameParam} OR ital.ALIASITEMDESCRIPTION LIKE {nameParam})";
                }

                string sql = string.Format(template, additionalConditions);

                var parameters = new DynamicParameters();
                parameters.Add("firstnumber", FirstNumber);
                parameters.Add("maxresult", MaxResult);
                if (!string.IsNullOrWhiteSpace(nameSearch))
                    parameters.Add("Name", $"%{nameSearch}%");

                var result = await _QueryExecutor.QueryAsync<ItemAliasPicklistDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Builds additional AND-conditions from CriteriaDTO's equality attributes (ItemId, ItemAliasId,
        // ItemGroupId), following the same fieldMap + OR-grouping pattern as ItemDAL.ApplyItemCriteria.
        //
        // The special case-insensitive "Name" pseudo-field is deliberately NOT resolved into inline SQL
        // here — its raw search text is handed back via nameSearch so the caller can apply it as a fully
        // parameterized LIKE. GB4's ApplyItemAliasSelectList/ItemAliasBLL concatenated this value directly
        // into the SQL string ("... like '%"+name+"%' ..."), which is a SQL-injection vector; that must
        // never be repeated here.
        private string ApplyItemAliasCriteria(string[,] fieldMap, CriteriaDTO criteriaDTO, out string nameSearch)
        {
            nameSearch = null;

            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return "";

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

            var nameAttr = attrs.FirstOrDefault(a => a.FieldName?.Equals("Name", StringComparison.OrdinalIgnoreCase) == true);
            if (nameAttr != null)
                nameSearch = ExtractStringValue(nameAttr);

            var conditions = new List<(string Cond, CriteriaDTO.AttributeJoinOperationType JoinToNext)>();
            foreach (var attr in attrs)
            {
                if (attr.FieldName?.Equals("Name", StringComparison.OrdinalIgnoreCase) == true)
                    continue;

                string val = ExtractStringValue(attr);
                if (string.IsNullOrEmpty(val)) continue;

                string col = null;
                for (int m = 0; m < fieldMap.GetLength(0); m++)
                {
                    if (fieldMap[m, 0].Equals(attr.FieldName, StringComparison.OrdinalIgnoreCase))
                    {
                        col = fieldMap[m, 1];
                        break;
                    }
                }
                if (col == null) continue;

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

            if (conditions.Count == 0)
                return "";

            var sb = new StringBuilder();
            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 column, AttributesCriteriaDTO attr, string val)
        {
            return attr.OperationType switch
            {
                CriteriaDTO.OperationType.Equal                => $"{column} IN({val})",
                CriteriaDTO.OperationType.In                   => $"{column} IN({val})",
                CriteriaDTO.OperationType.NotIn                => $"{column} NOT IN({val})",
                CriteriaDTO.OperationType.NotEqual             => $"{column} <> {val}",
                CriteriaDTO.OperationType.GreaterThan          => $"{column} > {val}",
                CriteriaDTO.OperationType.LessThan             => $"{column} < {val}",
                CriteriaDTO.OperationType.GreaterThanOrEqualTo => $"{column} >= {val}",
                CriteriaDTO.OperationType.LessThanOrEqualTo    => $"{column} <= {val}",
                _ => null
            };
        }

        private static string ExtractStringValue(AttributesCriteriaDTO attr)
        {
            if (attr?.FieldValue == null) return "";
            if (attr.FieldValue is System.Text.Json.JsonElement je)
                return je.ValueKind == System.Text.Json.JsonValueKind.String
                    ? je.GetString() ?? ""
                    : je.GetRawText() ?? "";
            return attr.FieldValue.ToString() ?? "";
        }
    }
}