using AdminDAL.DTO.BIZTransactionType;
using AdminDAL.DTO.OrganizationUnit;
using AdminDAL.DTO.Reason;
using AdminDAL.Query.BIZTransaction;
using AdminDAL.Query.OrganizationUnit;
using Dapper;
using GB5Shared.CriteriaHandler;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace AdminDAL.CustomCode.BIZTransaction
{
    public class BIZTransactionDAL : IBIZTransactionDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        // Maps the UI-facing filter field names to the real MBIZTRANSACTION
        // columns, and doubles as a whitelist: any FieldName not listed here
        // is dropped before CriteriaBuilder runs, since it interpolates
        // FieldName directly into the SQL column reference (not parameterized).
        private static readonly Dictionary<string, string> _filterableFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["Code"] = "BIZTRANSACTIONCODE",
            ["Name"] = "BIZTRANSACTIONNAME",
            ["SubClassId"] = "BIZTRANSACTIONSUBCLASSID",
            ["BIZTransactionSubClassId"] = "BIZTRANSACTIONSUBCLASSID",
            ["ModuleId"] = "MODULEID",
        };


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

        public async Task<string> GetBIZTransaction(int BIZTransactionId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = BIZTransactionQB.GET_BIZTRANSACTION;
                var Parameters = new { biztransactionid = BIZTransactionId };
                BIZTransactionDTO BIZTransactionDTO = await _QueryExecutor.QuerySingleAsync<BIZTransactionDTO>(LoginDTO, Sql, Parameters);
                return JsonConvert.SerializeObject(BIZTransactionDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<BIZTransactionPicklistDTO>> GetSelectListBIZTransaction(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            ApplyFieldNameWhitelist(CriteriaDTO);

            var parameters = new DynamicParameters();
            parameters.Add("firstnumber", FirstNumber);
            parameters.Add("maxresult", MaxResult);

            string baseSql = LoginDTO.DatabaseType == DBType.PostGre
                ? BIZTransactionQB.GET_SELECTLIST_BIZTRANSACTION_PG
                : BIZTransactionQB.GET_SELECTLIST_BIZTRANSACTION;

            var (whereClause, _) = CriteriaBuilder.Build(CriteriaDTO, parameters, baseSql);
            string SQL = baseSql.Replace("/*CRITERIA*/", whereClause);

            return await _QueryExecutor.QueryAsync<BIZTransactionPicklistDTO>(LoginDTO, SQL, parameters).ConfigureAwait(false);
        }

        // Drops any AttributesCriteriaList entry whose FieldName isn't a known
        // filterable column, then rewrites the surviving entries' FieldName to
        // the real column name CriteriaBuilder should emit.
        private static void ApplyFieldNameWhitelist(CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;

                section.AttributesCriteriaList = section.AttributesCriteriaList
                    .Where(attr => attr?.FieldName != null && _filterableFields.ContainsKey(attr.FieldName))
                    .ToList();

                foreach (var attr in section.AttributesCriteriaList)
                    attr.FieldName = _filterableFields[attr.FieldName];
            }
        }

        public async Task<int> GetSelectListBIZTransactionCount(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
            => await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, BIZTransactionQB.GET_SELECTLIST_BIZTRANSACTION_COUNT, null!).ConfigureAwait(false);

        public async Task<IEnumerable<BIZTransactionReportDTO>> GetBIZTransactionDetail(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string SQL = BIZTransactionQB.GET_BIZTRANSACTION_DETAIL;
            var parameters = new { firstnumber = FirstNumber, maxresult = MaxResult };
            return await _QueryExecutor.QueryAsync<BIZTransactionReportDTO>(LoginDTO, SQL, parameters).ConfigureAwait(false);
        }

        public async Task<int> GetBIZTransactionDetailCount(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
            => await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, BIZTransactionQB.GET_BIZTRANSACTION_DETAIL_COUNT, null!).ConfigureAwait(false);


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

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

        public async Task<String> DeleteBIZTransaction (int BIZTransactionId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = BIZTransactionQB.DELETE_BIZTRANSACTION;
                var parameter = new { biztransactionid = BIZTransactionId };
                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);
            }
        }
    }
}