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

namespace PayRollDAL.CustomeCode.PaySlipIssue
{
    public class PaySlipIssueDAL : IPaySlipIssueDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Picklist for grid ─────────────────────────────────────────────
        // GB4 parity: PaySlipIssueDAL.GetSelectListPaySlipIssuePaymentNumber

        public async Task<string> GetSelectListPaySlipIssuePaymentNumber(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string paymentNumber = GetCriteriaStringValue(CriteriaDTO, "PaymentNumber");
            int bizTransactionTypeId = GetCriteriaIntValue(CriteriaDTO, "BizTransactionTypeId") ?? -1;

            var parameters = new DynamicParameters();
            parameters.Add("paymentnumber", string.IsNullOrWhiteSpace(paymentNumber) ? null : $"%{paymentNumber}%");
            parameters.Add("biztransactiontypeid", bizTransactionTypeId);

            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => PaySlipIssueQB.GET_SELECTLIST_PAYMENTNUMBER_SQL,
                DBType.PostGre => PaySlipIssueQB.GET_SELECTLIST_PAYMENTNUMBER_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var result = await _QueryExecutor.QueryAsync<PaySlipIssuePaymentNumberPicklistDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

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

        private int? GetCriteriaIntValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            string value = GetCriteriaStringValue(criteriaDTO, fieldName);
            return int.TryParse(value, out int result) ? result : null;
        }
    }
}