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

namespace AccountsDAL.CustomCode.CostCategory
{
    public class CostCategoryDAL : ICostCategoryDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetCostCategory(int CostCategoryId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = CostCategoryQB.GET_COSTCATEGORY;
                var parameters = new { costcategoryid = CostCategoryId };

                var CostCategoryDict = new Dictionary<int, CostCategoryDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<CostCategoryDTO, CostCategoryConditionDTO, CostCategoryDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!CostCategoryDict.TryGetValue(parent.CostCategoryId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.CostCategoryConditionArray = new List<CostCategoryConditionDTO>();
                            CostCategoryDict[parent.CostCategoryId] = existingParent;
                        }

                        if (child != null && child.CostCategoryConditionId != 0)
                        {
                            existingParent.CostCategoryConditionArray.Add(child);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "CostCategoryConditionId"
                );

                var finalResult = CostCategoryDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve CostCategory: {ex.Message}", ex);
            }
        }


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

        // Full-DTO list (CostCategoryDTO, not CostCategoryPicklistDTO) — same criteria filtering
        // shape as AccountDAL.GetAccountList, but no FirstNumber/MaxResult paging.
        public async Task<string> GetCostCategoryList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql = CostCategoryQB.GET_COSTCATEGORY_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<CostCategoryDTO>(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. Mirrors AccountDAL.ExtractLikeSearchText.
        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;
        }

        // Extract a Status attribute (Equal/NotEqual) as (Equals, NotEquals) so both directions are
        // honored; whichever one wasn't sent stays null and its corresponding SQL condition is a
        // no-op. Mirrors AccountDAL.ExtractStatusFilter.
        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> SaveCostCategory(CostCategoryDTO CostCategoryDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string CostCategorySql = CostCategoryQB.SAVE_COSTCATEGORY;
                string conditionSql = CostCategoryQB.SAVE_COSTCATEOGRY_CONDITION;
                string countSql = CostCategoryQB.GET_COSTCATEGORY_COUNT;

                // DUPLICATE CHECK
                int existingCount = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql,
                    new
                    {
                        CostCategoryDTO.CostCategoryId
                    });

                if (existingCount > 0)
                {
                    throw new MethodNotAllowedException(
                        "Duplicate CostCategory record exists."
                    );
                }

                // TRANSACTION BUILD
                var statements = new List<(string sql, object param)>
        {
            (CostCategorySql, CostCategoryDTO)
        };

                if (CostCategoryDTO.CostCategoryConditionArray?.Count > 0)
                {
                    foreach (var condition in CostCategoryDTO.CostCategoryConditionArray)
                    {
                        condition.CostCategoryId = CostCategoryDTO.CostCategoryId;
                        statements.Add((conditionSql, condition));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

                return CostCategoryDTO.CostCategoryId;
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"{ErrorResponse.SaveErrorMessage}: {ex.Message}", ex);
            }
        }



        public async Task<int> UpdateCostCategory(
      CostCategoryDTO CostCategoryDTO,
      LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();

            // Update Header
            statements.Add(
                (
                    CostCategoryQB.UPDATE_COSTCATEGORY,
                    CostCategoryDTO
                ));

            // Delete Existing Details
            statements.Add(
                (
                    CostCategoryQB.DELETE_COSTCATEGORY_CONDITION,
                    new
                    {
                        CostCategoryDTO.CostCategoryId
                    }
                ));

            // Reinsert Details
            if (CostCategoryDTO.CostCategoryConditionArray?.Any() == true)
            {
                foreach (var detail in CostCategoryDTO.CostCategoryConditionArray)
                {
                    detail.CostCategoryId =
                        CostCategoryDTO.CostCategoryId;

                    statements.Add(
                        (
                            CostCategoryQB.SAVE_COSTCATEOGRY_CONDITION,
                            detail
                        ));
                }
            }

            await _QueryExecutor.ExecuteInTransactionAsync(
                login,
                statements);

            return CostCategoryDTO.CostCategoryId;
        }
        public async Task<int> DeleteCostCategoryCondition(int CostCategoryId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    CostCategoryQB.DELETE_COSTCATEGORY_CONDITION,
                    new { costcategoryid = CostCategoryId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting CostCategory detail for ID {CostCategoryId}", ex);
            }
        }


        public async Task<int> DeleteCostCategory(int CostCategoryId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    CostCategoryQB.DELETE_COSTCATEGORY,
                    new { costcategoryid = CostCategoryId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting CostCategory with ID {CostCategoryId}", ex);
            }
        }

    }
}