using System.Text.Json;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.CustomeCode.Employee;
using PayRollDAL.DTO.Employee;
using PayRollDAL.DTO.PayPeriod;
using PayRollDAL.Query.PayProcess;

namespace PayRollDAL.CustomeCode.PayPeriod
{
    public class PayPeriodDAL : IPayPeriodDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IEmployeeDAL _employeeDAL;
        private readonly IPickListExecutor _pickListExecutor;

        public PayPeriodDAL(
            IQueryExecutor queryExecutor,
            IEmployeeDAL employeeDAL,
            IPickListExecutor pickListExecutor)
        {
            _queryExecutor = queryExecutor;
            _employeeDAL = employeeDAL;
            _pickListExecutor = pickListExecutor;
        }

        public async Task<string> GetSelectListPayPeriodApplicableOu(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO CriteriaDTO,
    LoginDTO LoginDTO)
        {
            if (CriteriaDTO == null)
                throw new NotFoundException("Criteria must be supplied");

            int OuId = LoginDTO.WorkOUId;
            int Type = -1;
            int EmployeeId = -1;
            int PayConfigurationId = -1;

            string Code = null!;
            string Name = null!;
            string DisplayName = null!;

            #region Read Criteria
            foreach (var section in CriteriaDTO.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList
         ?? new List<AttributesCriteriaDTO>())
                {
                    var field = attr.FieldName?.Trim().ToLower();

                    switch (field)
                    {
                        case "ouid":
                            OuId = GetInt(attr.FieldValue);
                            break;

                        case "type":
                            Type = GetInt(attr.FieldValue);
                            break;

                        case "employeeid":
                            EmployeeId = GetInt(attr.FieldValue);
                            break;

                        case "payconfigurationid":
                            PayConfigurationId = GetInt(attr.FieldValue);
                            break;

                        case "code":
                            Code = GetString(attr.FieldValue);
                            break;

                        case "name":
                            Name = GetString(attr.FieldValue);
                            break;

                        case "displayname":
                            DisplayName = GetString(attr.FieldValue);
                            break;
                    }
                }

            }
            #endregion

            // 🔥 Normalize strings (VERY IMPORTANT)
            Code = string.IsNullOrWhiteSpace(Code) ? null! : Code;
            Name = string.IsNullOrWhiteSpace(Name) ? null! : Name;
            DisplayName = string.IsNullOrWhiteSpace(DisplayName) ? null! : DisplayName;

            int offset = FirstNumber <= 0 ? 0 : FirstNumber;
            int pageSize = MaxResult <= 0 ? 10 : MaxResult;

            string sql;

            #region Build SQL by Type
            switch (Type)
            {
                case -1:
                    sql = PayProcessQB.GET_SELECT_LIST_PAYPERIOD;
                    break;

                case 1:
                    if (EmployeeId <= 0)
                        throw new MethodNotAllowedException("Employee filter is required");

                    sql = PayProcessQB.GET_SELECT_LIST_PAYPERIOD_EMPLOYEE;
                    var emp1 = await GetEmployee(EmployeeId, LoginDTO);
                    PayConfigurationId = emp1.PayConfigurationId;
                    break;

                case 2:
                    if (PayConfigurationId <= 0)
                        throw new MethodNotAllowedException("PayConfiguration filter is required");

                    sql = PayProcessQB.GET_SELECT_LIST_PAYPERIOD_EMPLOYEE;
                    break;

                case 3:
                    if (EmployeeId <= 0)
                        throw new MethodNotAllowedException("Employee filter is required");

                    sql = PayProcessQB.GET_SELECT_LIST_PAYPERIOD_WITH_ARREAR_EMPLOYEE;
                    var emp3 = await GetEmployee(EmployeeId, LoginDTO);
                    PayConfigurationId = emp3.PayConfigurationId;
                    break;

                case 4:
                    if (EmployeeId == -1)
                        throw new MethodNotAllowedException("Employee filter is required");

                    sql = PayProcessQB.GET_SELECT_LIST_PAYPERIOD_FROM_TPAYPROCESS_EMPLOYEE_PAGED;
                    break;

                default:
                    throw new MethodNotAllowedException("Type not supported");
            }
            #endregion
 

            var result = (await _queryExecutor.QueryAsync<PayPeriodPicklistDTO>(
                LoginDTO,
                sql,
                new
                {
                    OuId,
                    PayConfigurationId,
                    EmployeeId,
                    PeriodId = LoginDTO.WorkPeriodId,
                    Code,
                    Name,
                    DisplayName,
                    Offset = offset,
                    PageSize = pageSize
                }
            )).ToList();

            return JsonConvert.SerializeObject(result);
        }


        #region Helpers

        private async Task<EmployeeDTO> GetEmployee(int employeeId, LoginDTO login)
        {
            var json = await _employeeDAL.GetEmployee(employeeId, login.LanguageId, login);
            return JsonConvert.DeserializeObject<EmployeeDTO>(json)!;
        }

        private async Task<string> ApplyCriteria(string sql, CriteriaDTO criteria, LoginDTO login)
        {
            string[,] map =
            {
            { "Code", "Mpayperiod.PayPeriodCode" },
            { "Name", "Mpayperiod.PayPeriodName" },
            { "PeriodId", "Mpayperiod.PeriodId" },
            { "PayperiodId", "Mpayperiod.PayperiodId" },
            { "PeriodType", "Mpayperiod.PeriodType" }
        };
            return await _pickListExecutor.ApplyCriteria(sql, map, criteria, login);
        }

        private async Task<string> ApplyCriteriaType(string sql, CriteriaDTO criteria, LoginDTO login)
        {
            string[,] map =
            {
            { "PeriodId", "Mpayperiod.PeriodId" },
            { "PayperiodId", "Mpayperiod.PayperiodId" },
            { "PeriodType", "Mpayperiod.PeriodType" }
        };
            return await _pickListExecutor.ApplyCriteria(sql, map, criteria, login);
        }

        private static int GetInt(object value, int def = -1)
        {
            if (value == null) return def;
            if (value is JsonElement je && je.TryGetInt32(out int i)) return i;
            return Convert.ToInt32(value);
        }

        private static string GetString(object value)
        {
            if (value == null) return string.Empty;
            if (value is JsonElement je) return je.ToString();
            return Convert.ToString(value) ?? string.Empty;
        }

        #endregion


        // ── PayPeriod/SelectList/Descending ──────────────────────────────────
        // GB4 parity: PayPeriodBLL.GetSelectListPayPeriodDescendingOrder

        public async Task<string> GetSelectListPayPeriodDescendingOrder(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            string Code = null!;
            string Name = null!;
            string DisplayName = null!;

            foreach (var section in CriteriaDTO?.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    var field = attr.FieldName?.Trim().ToLower();
                    switch (field)
                    {
                        case "code": Code = GetString(attr.FieldValue); break;
                        case "name": Name = GetString(attr.FieldValue); break;
                        case "displayname": DisplayName = GetString(attr.FieldValue); break;
                    }
                }
            }

            Code = string.IsNullOrWhiteSpace(Code) ? null! : Code;
            Name = string.IsNullOrWhiteSpace(Name) ? null! : Name;
            DisplayName = string.IsNullOrWhiteSpace(DisplayName) ? null! : DisplayName;

            if (IsCount)
            {
                int count = await _queryExecutor.QuerySingleAsync<int>(
                    LoginDTO,
                    PayProcessQB.GET_SELECT_LIST_PAYPERIOD_PLAIN_COUNT,
                    new { Code, Name, DisplayName });
                return count.ToString();
            }

            int offset = FirstNumber <= 0 ? (FirstNumber == 0 ? 0 : -1) : FirstNumber;
            int pageSize = MaxResult <= 0 ? (MaxResult == 0 ? 10 : -1) : MaxResult;

            var result = (await _queryExecutor.QueryAsync<PayPeriodPicklistDTO>(
                LoginDTO,
                PayProcessQB.GET_SELECT_LIST_PAYPERIOD_DESCENDING,
                new { Code, Name, DisplayName, Offset = offset, PageSize = pageSize }
            )).ToList();

            return JsonConvert.SerializeObject(result);
        }

        // ── PayPeriod/SelectList/PayPeriod/ApplicableOu/DependMultiPayConfig ─
        // GB4 parity: PayPeriodBLL.GetSelectListPayPeriodApplicableOuDependMultiPayConfig

        public async Task<string> GetSelectListPayPeriodApplicableOuDependMultiPayConfig(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            if (CriteriaDTO == null)
                throw new NotFoundException("Criteria must be supplied");

            int OuId = LoginDTO.WorkOUId;
            string PayConfigurationIds = null!;
            string Code = null!;
            string Name = null!;
            string DisplayName = null!;

            foreach (var section in CriteriaDTO.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    var field = attr.FieldName?.Trim().ToLower();
                    switch (field)
                    {
                        case "ouid": OuId = GetInt(attr.FieldValue, LoginDTO.WorkOUId); break;
                        case "payconfigurationid": PayConfigurationIds = GetString(attr.FieldValue); break;
                        case "code": Code = GetString(attr.FieldValue); break;
                        case "name": Name = GetString(attr.FieldValue); break;
                        case "displayname": DisplayName = GetString(attr.FieldValue); break;
                    }
                }
            }

            Code = string.IsNullOrWhiteSpace(Code) ? null! : Code;
            Name = string.IsNullOrWhiteSpace(Name) ? null! : Name;
            DisplayName = string.IsNullOrWhiteSpace(DisplayName) ? null! : DisplayName;
            PayConfigurationIds = string.IsNullOrWhiteSpace(PayConfigurationIds) ? "-1" : PayConfigurationIds;

            int offset = FirstNumber <= 0 ? (FirstNumber == 0 ? 0 : -1) : FirstNumber;
            int pageSize = MaxResult <= 0 ? (MaxResult == 0 ? 10 : -1) : MaxResult;

            var result = (await _queryExecutor.QueryAsync<PayPeriodPicklistDTO>(
                LoginDTO,
                PayProcessQB.GET_SELECT_LIST_PAYPERIOD_DEPENDS_MULTI_PAYCONFIG,
                new { OuId, PayConfigurationIds, Code, Name, DisplayName, Offset = offset, PageSize = pageSize }
            )).ToList();

            return JsonConvert.SerializeObject(result);
        }

        public async Task<PayPeriodDTO> GetPeriod(int PayPeriodId, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = PayProcessQB.GET_PAYPERIOD;
                var para = new
                {
                    payperiodid = PayPeriodId
                };
                PayPeriodDTO PayPeriodDTO = await _queryExecutor.QuerySingleAsync<PayPeriodDTO>(LoginDTO , SQL, para);
                return PayPeriodDTO;
            }
            catch(Exception)
            {
                throw;
            }

        }

        public async Task<string> GetSelectListPayPeriod(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount)
        {
            string Code = null!;
            string Name = null!;
            string DisplayName = null!;
            int? PeriodId = null;

            foreach (var section in CriteriaDTO?.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    var field = attr.FieldName?.Trim().ToLower();
                    switch (field)
                    {
                        case "code": Code = GetString(attr.FieldValue); break;
                        case "name": Name = GetString(attr.FieldValue); break;
                        case "displayname": DisplayName = GetString(attr.FieldValue); break;
                        case "periodid": PeriodId = GetInt(attr.FieldValue); break;
                    }
                }
            }

            Code = string.IsNullOrWhiteSpace(Code) ? null! : Code;
            Name = string.IsNullOrWhiteSpace(Name) ? null! : Name;
            DisplayName = string.IsNullOrWhiteSpace(DisplayName) ? null! : DisplayName;
            PeriodId = PeriodId is null or -1 ? null : PeriodId;

            if (IsCount)
            {
                int count = await _queryExecutor.QuerySingleAsync<int>(
                    LoginDTO,
                    PayProcessQB.GET_SELECT_LIST_PAYPERIOD_PLAIN_COUNT,
                    new { PeriodId, Code, Name, DisplayName });
                return count.ToString();
            }

            int offset = FirstNumber <= 0 ? (FirstNumber == 0 ? 0 : -1) : FirstNumber;
            int pageSize = MaxResult <= 0 ? (MaxResult == 0 ? 10 : -1) : MaxResult;

            var result = (await _queryExecutor.QueryAsync<PayPeriodPicklistDTO>(
                LoginDTO,
                PayProcessQB.GET_SELECT_LIST_PAYPERIOD_PLAIN,
                new { PeriodId, Code, Name, DisplayName, Offset = offset, PageSize = pageSize }
            )).ToList();

            return JsonConvert.SerializeObject(result);
        }

        public async Task<PayPeriodDTO> GetPayPeriod(int PayPeriodId, LoginDTO LoginDTO)
        {
            try
            {
                PayPeriodDTO PayPeriodDTO = await GetPeriod(PayPeriodId, LoginDTO);
                if (PayPeriodDTO == null)
                    return PayPeriodDTO!;

                if (!string.IsNullOrWhiteSpace(PayPeriodDTO.PayPeriodApplicableOuIds) && PayPeriodDTO.PayPeriodApplicableOuIds != "-1")
                {
                    var ouIds = PayPeriodDTO.PayPeriodApplicableOuIds
                        .Split(',', StringSplitOptions.RemoveEmptyEntries)
                        .Select(id => Convert.ToInt32(id.Trim()))
                        .ToList();

                    if (ouIds.Count > 0)
                    {
                        var ouNames = (await _queryExecutor.QueryAsync<IdNamePair>(
                            LoginDTO, PayProcessQB.GET_ORGANIZATIONUNIT_NAMES_BY_IDS, new { Ids = ouIds }))
                            .ToDictionary(x => x.Id, x => x.Name);

                        PayPeriodDTO.PayPeriodApplicableOuNames = string.Join(",",
                            ouIds.Select(id => ouNames.TryGetValue(id, out var n) ? n : string.Empty));
                    }
                }

                if (!string.IsNullOrWhiteSpace(PayPeriodDTO.PayPeriodApplicablePayConfigIds) && PayPeriodDTO.PayPeriodApplicablePayConfigIds != "-1")
                {
                    var payConfigIds = PayPeriodDTO.PayPeriodApplicablePayConfigIds
                        .Split(',', StringSplitOptions.RemoveEmptyEntries)
                        .Select(id => Convert.ToInt32(id.Trim()))
                        .ToList();

                    if (payConfigIds.Count > 0)
                    {
                        var payConfigNames = (await _queryExecutor.QueryAsync<IdNamePair>(
                            LoginDTO, PayProcessQB.GET_PAYCONFIGURATION_NAMES_BY_IDS, new { Ids = payConfigIds }))
                            .ToDictionary(x => x.Id, x => x.Name);

                        PayPeriodDTO.PayPeriodApplicablePayConfigNames = string.Join(",",
                            payConfigIds.Select(id => payConfigNames.TryGetValue(id, out var n) ? n : string.Empty));
                    }
                }

                int processCount = await _queryExecutor.QuerySingleAsync<int>(
                    LoginDTO, PayProcessQB.CHECK_TPAYPROCESS_EXISTS_FOR_PAYPERIOD, new { PayPeriodId });
                PayPeriodDTO.UpdateDeleteRequired = processCount > 0 ? 1 : 0;

                return PayPeriodDTO;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> CheckPayPeriodExists(int PayPeriodId, LoginDTO LoginDTO)
        {
            int count = await _queryExecutor.QuerySingleAsync<int>(LoginDTO, PayProcessQB.CHECK_PAYPERIOD_EXISTS, new { PayPeriodId });
            return count > 0;
        }

        public async Task<bool> CheckPayProcessExistsForPayPeriod(int PayPeriodId, LoginDTO LoginDTO)
        {
            int count = await _queryExecutor.QuerySingleAsync<int>(LoginDTO, PayProcessQB.CHECK_TPAYPROCESS_EXISTS_FOR_PAYPERIOD, new { PayPeriodId });
            return count > 0;
        }

        public async Task<int> SavePayPeriod(PayPeriodDTO PayPeriodDTO, LoginDTO LoginDTO)
        {
            return await _queryExecutor.ExecuteAsync(LoginDTO, PayProcessQB.SAVE_PAYPERIOD, PayPeriodDTO);
        }

        public async Task<int> UpdatePayPeriod(PayPeriodDTO PayPeriodDTO, LoginDTO LoginDTO)
        {
            return await _queryExecutor.ExecuteAsync(LoginDTO, PayProcessQB.UPDATE_PAYPERIOD, PayPeriodDTO);
        }

        private class IdNamePair
        {
            public int Id { get; set; }
            public string Name { get; set; } = string.Empty;
        }
    }

}