using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using SkillManagementDAL.DTO.EmployeeSkillProfile;
using SkillManagementDAL.DTO.SkillDomain;
using SkillManagementDAL.Query.EmployeeSkillProfile;
using SkillManagementDAL.Query.RoleSkillRequirement;
using SkillManagementDAL.Query.SkillDomain;

namespace SkillManagementDAL.CustomCode.EmployeeSkillProfile
{
    public class EmployeeSkillProfileDAL : IEmployeeSkillProfileDAL
    {
        private readonly IQueryExecutor _qe;

        public EmployeeSkillProfileDAL(IQueryExecutor qe) => _qe = qe;

        public async Task<string> GetByEmployee(int employeeId, int profileid, int skillid, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<EmployeeSkillProfileDTO>(
                login, EmployeeSkillProfileQB.GET_BY_EMPLOYEE,
                new { EmployeeId = employeeId, ProfileId = profileid, SkillId = skillid, TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        // All profile rows for one employee + one skill — used for cell detail lightbox.
        // Returns every saved level row ordered by CURRENTLEVNO ASC.
        public async Task<string> GetCellDetail(int employeeId, int skillId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<EmployeeSkillProfileDTO>(
                login, EmployeeSkillProfileQB.GET_BY_EMPLOYEE,
                new { EmployeeId = employeeId, ProfileId = (int?)null, SkillId = skillId, TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        // Full skill list for one employee — no ProfileId/SkillId filter.
        // Reuses GET_BY_EMPLOYEE's own optional-filter design (ProfileId/SkillId are
        // "@Param IS NULL OR ..." checks), passing them as null so every skill row for
        // the employee comes back instead of just one profile/skill match.
        public async Task<string> GetByEmployeeId(int employeeId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<EmployeeSkillProfileDTO>(
                login, EmployeeSkillProfileQB.GET_BY_EMPLOYEE,
                new { EmployeeId = employeeId, ProfileId = (int?)null, SkillId = (int?)null, TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }
        public async Task<int> Insert(EmployeeSkillProfileDTO dto, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(login, EmployeeSkillProfileQB.INSERT, dto);
        }
        public async Task<int> Upsert(EmployeeSkillProfileDTO dto, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(login, EmployeeSkillProfileQB.UPSERT, dto);
        }

        public async Task<int> BulkUpsert(EmployeeSkillProfileDTO dto, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(login, EmployeeSkillProfileQB.BULK_UPSERT, dto);
        }

        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryWithCriteriaAsync<dynamic>(
                login, EmployeeSkillProfileQB.GET_SELECT_LIST,
                new { TenantId = login.ClientId },criteriaDTO, ct);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<byte?> GetCurrentLevel(int employeeId, int skillId, int productFamilyId, int modelId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<byte>(
                login, EmployeeSkillProfileQB.GET_CURRENT_LEVEL,
                new { EmployeeId = employeeId, SkillId = skillId, ProductFamilyId = productFamilyId, ModelId = modelId, TenantId = login.ClientId });
            return result.FirstOrDefault();
        }

        public async Task<byte> GetSlNoBySlevelId(int slevelId, int skillId, LoginDTO login)
        {
            var results = await _qe.QueryAsync<byte>(
                login, EmployeeSkillProfileQB.GET_SLNO_BY_SLEVELID,
                new { SlevelId = slevelId, SkillId = skillId });
            return results.FirstOrDefault();
        }

        public async Task<byte> GetSlNoByLevelLabel(int skillId, string levelLabel, LoginDTO login)
        {
            var results = await _qe.QueryAsync<byte>(
                login, EmployeeSkillProfileQB.GET_SLNO_BY_LEVEL_LABEL,
                new { SkillId = skillId, LevelLabel = levelLabel });
            return results.FirstOrDefault();
        }

        public async Task<string?> GetEmployeeName(int employeeId, LoginDTO login)
        {
            var results = await _qe.QueryAsync<string>(
                login, EmployeeSkillProfileQB.GET_EMPLOYEE_NAME,
                new { EmployeeId = employeeId });
            return results.FirstOrDefault();
        }

        public async Task<string> GetEmployeeSkillProfileList(LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<dynamic>(
                login, EmployeeSkillProfileQB.GET_LIST,
                new { TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }
}
}