using Dapper;
using FrameworkDAL.DTO.BIZTransactionClass;
using FrameworkDAL.Query.BIZTransactionClass;
using GB5Shared.CriteriaHandler;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Identity.Client;
using Newtonsoft.Json;

namespace FrameworkDAL.CustomCode.BIZTransactionClass
{
    public class BIZTransactionClassDAL : IBIZTransactionClassDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        // Maps the UI-facing filter field names to the real MBIZTRANSACTIONCLASS
        // columns — the SELECT list aliases them to Id/Code/Name, but the base
        // table columns are BIZTRANSACTIONCLASSCODE/BIZTRANSACTIONCLASSNAME.
        // Also 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"] = "BIZTRANSACTIONCLASSCODE",
            ["Name"] = "BIZTRANSACTIONCLASSNAME",
            ["ModuleId"] = "MODULEID",
        };

        public BIZTransactionClassDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        public async Task<String> GetBIZTransactionClassData(int BizTransactionClassId, LoginDTO LoginDTO)
        {
            string json = "";
            try
            {
                string sql = BIZTransactionClassQB.GET_BIZTRANSACTIONCLASS;
                var parameters = new { biztransactionclassid = BizTransactionClassId };
                BIZTransactionClassDTO BIZTransactionClassDTOs = await _QueryExecutor.QuerySingleAsync<BIZTransactionClassDTO>(LoginDTO, sql, parameters);
                json = JsonConvert.SerializeObject(BIZTransactionClassDTOs);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                json = null;
            }
        }

        public async Task<string> GetSelectListBIZTransactionClass(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    ApplyFieldNameWhitelist(CriteriaDTO);

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

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

                    List<BIZTransactionClassPicklistDTO> BIZTransactionClassPicklistDTOs = (await _QueryExecutor.QueryAsync<BIZTransactionClassPicklistDTO>(LoginDTO, SQL, parameters)).ToList();
                    Json = JsonConvert.SerializeObject(BIZTransactionClassPicklistDTOs);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // 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];
            }
        }
    }
}