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

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

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

        public async Task<string> GetSelectListManPower(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string ouIdFilter = GetCriteriaStringValue(CriteriaDTO, "OUId");

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

                if (!string.IsNullOrWhiteSpace(ouIdFilter))
                {
                    filterClause += " AND A.OUID = @ouid";
                    parameters.Add("ouid", Convert.ToInt32(ouIdFilter));
                }

                if (IsCount)
                {
                    string countSql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => ManPowerQB.GET_SELECTLIST_MANPOWER_COUNT_SQL,
                        DBType.PostGre => ManPowerQB.GET_SELECTLIST_MANPOWER_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 => ManPowerQB.GET_SELECTLIST_MANPOWER_SQL,
                        DBType.PostGre => ManPowerQB.GET_SELECTLIST_MANPOWER_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<ManPowerPicklistDTO>(LoginDTO, sql, parameters)
                        .ConfigureAwait(false);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetSelectListManPower", 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; }
        }
    }
}