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

namespace PayRollDAL.CustomeCode.PayTaxType
{
    public class PayTaxTypeDAL : IPayTaxTypeDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public PayTaxTypeDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }

        public async Task<PayTaxTypeDTO> GetPayTaxType(int PayTaxTypeId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = PayTaxTypeQB.GET_PAYTAXTYPE;
                var parameters = new { paytaxtypeid = PayTaxTypeId };

                var payTaxTypeDict = new Dictionary<int, PayTaxTypeDTO>();

                var result = await _QueryExecutor.QueryAsyncArray<PayTaxTypeDTO, PayTaxTypeDetailDTO, PayTaxTypeDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!payTaxTypeDict.TryGetValue(parent.PayTaxTypeId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.PayTaxTypeDetailArray = new List<PayTaxTypeDetailDTO>();
                            payTaxTypeDict[parent.PayTaxTypeId] = existingParent;
                        }

                        if (child != null && child.PayTaxTypeDetailId != 0)
                        {
                            existingParent.PayTaxTypeDetailArray.Add(child);
                        }

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

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

        public async Task<int> UpdatePayTaxType(int PayTaxTypeId, int PeriodId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = PayTaxTypeQB.UPDATE_PAYTAXTYPE_PERIOD;
                var parameters = new { paytaxtypeid = PayTaxTypeId, periodid = PeriodId };
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception("Error in UpdatePayTaxType", ex);
            }
        }

        // ── Picklist for grid ─────────────────────────────────────────────

        public async Task<string> GetSelectListPayTaxType(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string codeFilter = GetCriteriaStringValue(CriteriaDTO, "Code");
                string nameFilter = GetCriteriaStringValue(CriteriaDTO, "Name");

                var parameters = new DynamicParameters();
                string filterClause = "";

                if (!string.IsNullOrWhiteSpace(codeFilter))
                {
                    filterClause += " AND A.PAYTAXTYPECODE LIKE @code";
                    parameters.Add("code", $"%{codeFilter}%");
                }

                if (!string.IsNullOrWhiteSpace(nameFilter))
                {
                    filterClause += " AND A.PAYTAXTYPENAME LIKE @name";
                    parameters.Add("name", $"%{nameFilter}%");
                }

                if (IsCount)
                {
                    string countSql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => PayTaxTypeQB.GET_SELECTLIST_PAYTAXTYPE_COUNT_SQL,
                        DBType.PostGre => PayTaxTypeQB.GET_SELECTLIST_PAYTAXTYPE_COUNT_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    countSql = countSql.Replace("/*FILTER*/", filterClause);

                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql, parameters)
                        .ConfigureAwait(false);
                    return count.ToString();
                }
                else
                {
                    string sql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => PayTaxTypeQB.GET_SELECTLIST_PAYTAXTYPE_SQL,
                        DBType.PostGre => PayTaxTypeQB.GET_SELECTLIST_PAYTAXTYPE_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    sql = sql.Replace("/*FILTER*/", filterClause);

                    parameters.Add("firstnumber", FirstNumber);
                    parameters.Add("maxresult", MaxResult);

                    var result = await _QueryExecutor.QueryAsync<PayTaxTypePicklistDTO>(LoginDTO, sql, parameters)
                        .ConfigureAwait(false);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetSelectListPayTaxType", ex);
            }
        }

        private string GetCriteriaStringValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is JsonElement je)
                    return je.ValueKind == JsonValueKind.String ? je.GetString() : je.ToString();

                return attr?.FieldValue?.ToString();
            }
            catch { return null; }
        }
    }
}