using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AccountsDAL.DTO.CostCenterType;
using AccountsDAL.Query.CostCenterType;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace AccountsDAL.CustomCode.CostCenterType
{
    public class CostCenterTypeDAL : ICostCenterTypeDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;


        public CostCenterTypeDAL(IQueryExecutor IQueryExecutor, IValidation Validation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = Validation;

        }

        public async Task<string> GetCostCenterType(int CostCenterTypeId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = CostCenterTypeQB.GET_COSTCENTERTYPE;
                var parameters = new { costcentertypeid = CostCenterTypeId };
                CostCenterTypeDTO CostCenterTypeDTOs = await _QueryExecutor.QuerySingleAsync<CostCenterTypeDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(CostCenterTypeDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<string> GetSelectListCostCenterType(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = CostCenterTypeQB.GET_SELECTLIST_COSTCENTERTYPE;
                var Result = await _QueryExecutor.QueryAsync<CostCenterTypePicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO list (CostCenterTypeDTO, not CostCenterTypePicklistDTO) — same criteria filtering
        // as AccountDAL.GetAccountList, but for CostCenterType. No FirstNumber/MaxResult paging.
        public async Task<string> GetCostCenterTypeList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql = CostCenterTypeQB.GET_COSTCENTERTYPE_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<CostCenterTypeDTO>(LoginDTO, sql, parameter)
                                                 .ConfigureAwait(false)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Picklist search-as-you-type sends the typed text as a Like-operation attribute on each
        // searchable field with the same FieldValue repeated per field — take the first non-empty
        // one found.
        private static string? ExtractLikeSearchText(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return null;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.OperationType == CriteriaDTO.OperationType.Like && attr.FieldValue != null)
                    {
                        var text = attr.FieldValue.ToString();
                        if (!string.IsNullOrWhiteSpace(text)) return text;
                    }
                }
            }
            return null;
        }

        // Picklist also sends a Status attribute (e.g. NotEqual 5 = exclude Archived) alongside the
        // search text — extract it as (Equals, NotEquals) so both directions are honored; whichever
        // one wasn't sent stays null and its corresponding SQL condition is a no-op.
        private static (int? EqualTo, int? NotEqualTo) ExtractStatusFilter(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return (null, null);
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr == null || !string.Equals(attr.FieldName, "Status", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (attr.FieldValue == null || !int.TryParse(attr.FieldValue.ToString(), out int value))
                        continue;

                    if (attr.OperationType == CriteriaDTO.OperationType.Equal)
                        return (value, null);
                    if (attr.OperationType == CriteriaDTO.OperationType.NotEqual)
                        return (null, value);
                }
            }
            return (null, null);
        }

        public async Task<int> SaveCostCenterType(CostCenterTypeDTO CostCenterTypeDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = CostCenterTypeQB.SAVE_COSTCENTERTYPE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, CostCenterTypeDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateCostCenterType(CostCenterTypeDTO CostCenterTypeDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string sql = CostCenterTypeQB.UPDATE_COSTCENTERTYPE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, CostCenterTypeDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteCostCenterType(int CostCenterTypeId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = CostCenterTypeQB.DELETE_COSTCENTERTYPE;
                var parameter = new { costcentertypeid = CostCenterTypeId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameter);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }
    }
}