using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.PayConfiguration;
using PayRollDAL.Query.PayConfiguration;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.PayConfiguration
{
    public class PayConfigurationDAL : IPayConfigurationDAL
    {

        private readonly IQueryExecutor queryExecutor;

        public PayConfigurationDAL(IQueryExecutor IQueryExecutor)
        {
            queryExecutor = IQueryExecutor;
        }

        public async Task<PayConfigurationDTO> GetPayConfiguration(int PayConfigurationId, LoginDTO LoginDTO)
        {
            try
            {

                string sql = "";
                if (LoginDTO.DatabaseType == DBTYPE.SQL)
                {
                    sql = PayConfigurationQB.GetPayConfigurationById;
                }
                else if (LoginDTO.DatabaseType == DBTYPE.POSTGRESQL)
                {
                    sql = PayConfigurationQB.GetPayConfigurationById_pg;
                }


                var parameters = new { payconfigurationid = PayConfigurationId };

                // Dictionary ensures parent object is only created once
                var payConfigDict = new Dictionary<int, PayConfigurationDTO>();

                var result = await queryExecutor.QueryAsyncArray<PayConfigurationDTO, PayConfigurationDetailDTO, PayConfigurationDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!payConfigDict.TryGetValue(parent.PayConfigurationId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.PayConfigurationDetailArray = new List<PayConfigurationDetailDTO>();
                            payConfigDict[parent.PayConfigurationId] = existingParent;
                        }

                        // Add child if it exists
                        if (child != null && child.PayConfigurationDetailId != 0)
                        {
                            existingParent.PayConfigurationDetailArray.Add(child);
                        }

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

                return payConfigDict.Values.FirstOrDefault()!;
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetPayConfiguration", ex);
            }
        }

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

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

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

            if (IsCount)
            {
                int count = await queryExecutor.QuerySingleAsync<int>(
                    LoginDTO, PayConfigurationQB.GET_SELECT_LIST_PAYCONFIGURATION_COUNT, new { Code, Name });
                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<PayConfigurationPicklistDTO>(
                LoginDTO,
                PayConfigurationQB.GET_SELECT_LIST_PAYCONFIGURATION,
                new { Code, Name, Offset = offset, PageSize = pageSize }
            )).ToList();

            return JsonConvert.SerializeObject(result);
        }

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

        public async Task<int> SavePayConfiguration(PayConfigurationDTO PayConfigurationDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                return await queryExecutor.ExecuteAsync(LoginDTO, PayConfigurationQB.SAVE_PAYCONFIGURATION, PayConfigurationDTO, DbTransaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in SavePayConfiguration", ex);
            }
        }

        public async Task<int> UpdatePayConfiguration(PayConfigurationDTO PayConfigurationDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                return await queryExecutor.ExecuteAsync(LoginDTO, PayConfigurationQB.UPDATE_PAYCONFIGURATION, PayConfigurationDTO, DbTransaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in UpdatePayConfiguration", ex);
            }
        }

        public async Task DeletePayConfigurationDetail(int PayConfigurationId, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                var parameters = new { payconfigurationid = PayConfigurationId };
                await queryExecutor.ExecuteAsync(LoginDTO, PayConfigurationQB.DELETE_PAYCONFIGURATIONDETAIL_BY_HEADER, parameters, DbTransaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in DeletePayConfigurationDetail", ex);
            }
        }

        public async Task<int> SavePayConfigurationDetail(PayConfigurationDetailDTO PayConfigurationDetailDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                return await queryExecutor.ExecuteAsync(LoginDTO, PayConfigurationQB.SAVE_PAYCONFIGURATIONDETAIL, PayConfigurationDetailDTO, DbTransaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in SavePayConfigurationDetail", ex);
            }
        }

    }
}