using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AccountsDAL.DTO.Insurance;
using AccountsDAL.Query.Insurance;
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.Insurance
{
    public class InsuranceDAL : IInsuranceDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public InsuranceDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        
        public async Task<string> GetInsurance(int InsuranceId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = InsuranceQB.GET_INSURANCE;
                var parameters = new { Insuranceid = InsuranceId };
                InsuranceDTO InsuranceDTOs = await _QueryExecutor.QuerySingleAsync<InsuranceDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(InsuranceDTOs);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListInsurance(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = InsuranceQB.GET_SELECTLIST_INSURANCE;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    var Result = await _QueryExecutor.QueryAsync<InsurancePicklistDTO>(LoginDTO, SQL, parameter);
                    Json = JsonConvert.SerializeObject(Result);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO list (InsuranceDTO, not InsurancePicklistDTO) — same criteria filtering pattern as
        // AccountDAL.GetAccountList/AccountScheduleDAL.GetAccountScheduleList (a plain CriteriaDTO-
        // filtered List call), but no FirstNumber/MaxResult paging.
        public async Task<string> GetInsuranceList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql        = InsuranceQB.GET_INSURANCE_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<InsuranceDTO>(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> SaveInsurance(InsuranceDTO InsuranceDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = InsuranceQB.SAVE_INSURANCE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, InsuranceDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateInsurance(InsuranceDTO InsuranceDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = InsuranceQB.UPDATE_INSURANCE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, InsuranceDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }



        public async Task<string> DeleteInsurance(int InsuranceId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = InsuranceQB.DELETE_INSURANCE;
                var parameter = new { Insuranceid = InsuranceId
                    //TenantId = LoginDTO.ClientId
                };
                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);
            }
        }
    }
}