using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using MMDAL.DTO.Store;
using MMDAL.Query.Store;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.Store
{
    public class StoreDAL : IStoreDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public StoreDAL(IQueryExecutor IQueryExecutor, IValidation validation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = validation;
        }

        public async Task<string> GetSelectListStore(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                int bizTransactionTypeId = -1;

                // 1. Extract BizTransactionTypeId from CriteriaDTO
                if (criteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (string.Equals(attr.FieldName, "biztransactiontypeid", StringComparison.OrdinalIgnoreCase))
                            {
                                bizTransactionTypeId = ExtractIntValue(attr.FieldValue);
                            }
                        }
                    }
                }

                string sql;
                var parameters = new Dictionary<string, object>();

                // 2. Decide query based on BizTransactionTypeId
                if (bizTransactionTypeId == -1)
                {
                    sql = StoreQB.GET_SELECTLIST_STORE;
                }
                else
                {
                    sql = StoreQB.GET_STORE_BASED_ON_APPLICABLE_STORE_BIZTYPE;
                    parameters.Add("@BizTransactionTypeId", bizTransactionTypeId);
                }

                // 3. Apply criteria (GB5 way)
                sql = ApplyStoreCriteria(sql, criteriaDTO, parameters);

                // 4. Execute query
                var result = await _QueryExecutor.QueryAsync<StorePicklistDTO>(
                    loginDTO,
                    sql,
                    parameters
                );

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        private string ApplyStoreCriteria(
    string sql,
    CriteriaDTO criteriaDTO,
    IDictionary<string, object> parameters)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return sql;

            var whereConditions = new List<string>();

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (string.IsNullOrWhiteSpace(attr.FieldName) || attr.FieldValue == null)
                        continue;

                    switch (attr.FieldName.ToLower())
                    {
                        case "organizationunit.id":
                            whereConditions.Add("a.OUID = @OUId");
                            parameters["@OUId"] = ExtractIntValue(attr.FieldValue);
                            break;

                        case "name":
                            whereConditions.Add("a.STORENAME LIKE @StoreName");
                            parameters["@StoreName"] = "%" + attr.FieldValue + "%";
                            break;
                    }
                }
            }

            if (whereConditions.Count > 0)
            {
                sql += sql.Contains("WHERE", StringComparison.OrdinalIgnoreCase)
                    ? " AND " + string.Join(" AND ", whereConditions)
                    : " WHERE " + string.Join(" AND ", whereConditions);
            }

            return sql;
        }

        // FieldValue may be a JsonElement (System.Text.Json deserialization via FastEndpoints) —
        // JsonElement doesn't implement IConvertible, so Convert.ToInt32 throws on it directly.
        private static int ExtractIntValue(object fieldValue)
        {
            if (fieldValue is System.Text.Json.JsonElement je)
                return je.ValueKind switch
                {
                    System.Text.Json.JsonValueKind.Number => je.GetInt32(),
                    System.Text.Json.JsonValueKind.String => int.TryParse(je.GetString(), out int s) ? s : 0,
                    _ => 0
                };

            return Convert.ToInt32(fieldValue);
        }

    }
}