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

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

        // ── Picklist for grid ─────────────────────────────────────────────
        // Mirrors GB4 ObjectiveDAL.GetSelectListObjective filters (ObjectiveId,
        // ObjectiveCode, ObjectiveName, ForEmployeeId) narrowed to the fields the
        // picklist DTO (Id/Name/Description) actually supports: Name and Description.

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

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

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

                if (!string.IsNullOrWhiteSpace(descriptionFilter))
                {
                    filterClause += " AND obj.DESCRIPTION LIKE @description";
                    parameters.Add("description", $"%{descriptionFilter}%");
                }

                if (IsCount)
                {
                    string countSql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => ObjectiveQB.GET_SELECTLIST_OBJECTIVE_COUNT_SQL,
                        DBType.PostGre => ObjectiveQB.GET_SELECTLIST_OBJECTIVE_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 => ObjectiveQB.GET_SELECTLIST_OBJECTIVE_SQL,
                        DBType.PostGre => ObjectiveQB.GET_SELECTLIST_OBJECTIVE_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<ObjectivePicklistDTO>(LoginDTO, sql, parameters)
                        .ConfigureAwait(false);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetSelectListObjective", 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; }
        }
    }
}