using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.ServiceRule;
using PayRollDAL.Query.ServiceRule;
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.ServiceRule
{
    public class ServiceRuleDAL : IServiceRuleDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Picklist ──────────────────────────────────────────────────────

        public async Task<string> GetSelectListServiceRule(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            var parameters = new DynamicParameters();
            string filter = BuildFilter(CriteriaDTO, parameters);

            if (IsCount)
            {
                string countSql = ServiceRuleQB.GET_SELECTLIST_SERVICERULE_COUNT_SQL
                    + filter
                    + ServiceRuleQB.GET_SELECTLIST_SERVICERULE_COUNT_FOOTER;

                int total = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql, parameters)
                    .ConfigureAwait(false);
                return total.ToString();
            }

            string body = LoginDTO.DatabaseType switch
            {
                DBType.SQL => ServiceRuleQB.GET_SELECTLIST_SERVICERULE_SQL,
                DBType.PostGre => ServiceRuleQB.GET_SELECTLIST_SERVICERULE_PG,
                _ => throw new Exception("Unsupported database type")
            };

            string footer = LoginDTO.DatabaseType switch
            {
                DBType.SQL => ServiceRuleQB.GET_SELECTLIST_SERVICERULE_SQL_FOOTER,
                DBType.PostGre => ServiceRuleQB.GET_SELECTLIST_SERVICERULE_PG_FOOTER,
                _ => throw new Exception("Unsupported database type")
            };

            string sql = body + filter + footer;

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

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

        // ── Criteria → parameterized filter clause ─────────────────────────

        private string BuildFilter(CriteriaDTO CriteriaDTO, DynamicParameters parameters)
        {
            var sb = new System.Text.StringBuilder();

            int? partyId = GetCriteriaIntValue(CriteriaDTO, "PartyId");
            if (partyId.HasValue)
            {
                sb.Append("\r\n AND a.PARTYID = @partyid");
                parameters.Add("partyid", partyId.Value);
            }

            int? partyBranchId = GetCriteriaIntValue(CriteriaDTO, "PartyBranchId");
            if (partyBranchId.HasValue)
            {
                sb.Append("\r\n AND a.PARTYBRANCHID = @partybranchid");
                parameters.Add("partybranchid", partyBranchId.Value);
            }

            int? workTypeId = GetCriteriaIntValue(CriteriaDTO, "WorkTypeId");
            if (workTypeId.HasValue)
            {
                sb.Append("\r\n AND a.WORKTYPEID = @worktypeid");
                parameters.Add("worktypeid", workTypeId.Value);
            }

            int? status = GetCriteriaIntValue(CriteriaDTO, "Status");
            if (status.HasValue)
            {
                sb.Append("\r\n AND a.STATUS = @status");
                parameters.Add("status", status.Value);
            }

            int? id = GetCriteriaIntValue(CriteriaDTO, "Id");
            if (id.HasValue)
            {
                sb.Append("\r\n AND a.SERVICERULEID = @serviceruleid");
                parameters.Add("serviceruleid", id.Value);
            }

            string code = GetCriteriaStringValue(CriteriaDTO, "code");
            string name = GetCriteriaStringValue(CriteriaDTO, "name");
            if (!string.IsNullOrWhiteSpace(code) || !string.IsNullOrWhiteSpace(name))
            {
                sb.Append("\r\n AND (a.SERVICERULECODE LIKE @code OR a.SERVICERULENAME LIKE @name)");
                parameters.Add("code", $"%{(string.IsNullOrWhiteSpace(code) ? name : code)}%");
                parameters.Add("name", $"%{(string.IsNullOrWhiteSpace(name) ? code : name)}%");
            }

            return sb.ToString();
        }

        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 : (int?)null;
        }
    }
}