using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using SkillManagementDAL.DTO.SkillGroup;
using SkillManagementDAL.Query.SkillGroup;
using SkillManagementDAL.Query.SkillLevel;
using System.Text.RegularExpressions;

namespace SkillManagementDAL.CustomCode.SkillGroup
{
    public class SkillGroupDAL : ISkillGroupDAL
    {
        private readonly IQueryExecutor _qe;
        private readonly IValidation _Validation;

        public SkillGroupDAL(IQueryExecutor qe, IValidation validation)
        {
            _qe = qe;
            _Validation = validation;
        }

        public async Task<string> GetList(int domainId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<SkillGroupDTO>(
                login, SkillGroupQB.GET_LIST,
                new { TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        // Executes paged query — called by BLL when paged mode
        public async Task<List<SkillGroupDTO>> GetPagedList(
            int firstNumber,
            int maxResult,
            LoginDTO login,
            CancellationToken ct)
        {
            var result = await _qe.QueryAsync<SkillGroupDTO>(
                login,
                SkillGroupQB.GET_SKILLGROUP_LIST_PAGED,
                new { FirstNumber = firstNumber, MaxResult = maxResult },
                null,
                false,
                ct
            );

            return result.ToList(); // ✅ FIX
        }


        // Executes all-data query — called by BLL when all-data mode
        public async Task<List<SkillGroupDTO>> GetAllList(
    LoginDTO login,
    CancellationToken ct)
        {
            var result = await _qe.QueryAsync<SkillGroupDTO>(
                login,
                SkillGroupQB.GET_SKILLGROUP_LIST_ALL,
                null!,
                null,              // transaction
                false,             // useReadUncommitted
                ct                 // ✅ correct position for cancellationToken
            );

            return result.ToList(); // ✅ convert IEnumerable → List
        }

        // Executes count query — called by BLL when count is needed
        public Task<int> GetCount(
            LoginDTO login,
            CancellationToken ct)
        {
            return _qe.ExecuteScalarAsync<int>(
                login,
                SkillGroupQB.GET_SKILLGROUP_COUNT,
                null);
        }
    
        public async Task<string> GetById(int groupId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QuerySingleAsync<SkillGroupDTO>(
                login, SkillGroupQB.GET_BY_ID,
                new { GroupId = groupId, TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

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

        public async Task<bool> DomainExists(int domainId, int tenantId, LoginDTO login)
        {
            var count = await _qe.ExecuteScalarAsync<int>(
                login,
                @"SELECT COUNT(1)
          FROM MSKILLDOMAIN
          WHERE DOMAINID = @DomainId
          AND TENANTID = @TenantId",
                new { DomainId = domainId, TenantId = tenantId }
            );

            return count > 0;
        }

        public async Task<int> Insert(SkillGroupDTO dto, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(login, SkillGroupQB.INSERT, dto, cancellationToken: ct);
        }

        public async Task<int> Update(SkillGroupDTO dto, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(login, SkillGroupQB.UPDATE, dto, cancellationToken: ct);
        }

        public async Task<string> DeleteSkillGroup(int groupId, LoginDTO loginDTO)
        {
            try
            {
                string sql = SkillGroupQB.DELETE_SKILL_GROUP;

                var parameter = new { GroupId = groupId }; // must match SQL parameter name

                var result = await _qe.ExecuteAsync(loginDTO, sql, parameter);

                if (result > 0)
                    return SuccessResponse.DeleteSuccessMessage;
                else
                    return ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }
    }
}