using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using SkillManagementDAL.DTO.RoleSkillRequirement;
using SkillManagementDAL.DTO.SkillGroup;
using SkillManagementDAL.Query.RoleSkillRequirement;
using SkillManagementDAL.Query.SkillGroup;
using SkillManagementDAL.Query.SkillProcessStageMap;

namespace SkillManagementDAL.CustomCode.RoleSkillRequirement
{
    public class RoleSkillRequirementDAL : IRoleSkillRequirementDAL
    {
        private readonly IQueryExecutor _qe;

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

        public async Task<string> GetByRole(int RoleId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<RoleSkillRequirementDTO>(
                login, RoleSkillRequirementQB.GET_BY_ROLE,
                new { RoleId = RoleId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetBySkill(int SkillId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<RoleSkillRequirementDTO>(
                login, RoleSkillRequirementQB.GET_BY_SKILL,
                new { SkillId = SkillId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetByReq(int ReqId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<RoleSkillRequirementDTO>(
                login, RoleSkillRequirementQB.GET_BY_ID,
                new { ReqId = ReqId });
            return JsonConvert.SerializeObject(result);
        }


        public async Task<int> Save(IEnumerable<RoleSkillRequirementDTO> reqs, int roleId, LoginDTO login, CancellationToken ct)
        {
            var statements = new List<(string sql, object param)>();

            // Step 1: Delete all existing skills if role exists
            if (roleId != 0)
            {
                statements.Add((RoleSkillRequirementQB.DELETE_BY_ROLE, new { RoleId = roleId }));
            }

            // Step 2: Insert all provided skills
            foreach (var r in reqs)
            {
                statements.Add((RoleSkillRequirementQB.INSERT, r));
            }

            // Step 3: Execute all statements in a single transaction
            await _qe.ExecuteInTransactionAsync(login, statements);

            return roleId;
        }

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

        public async Task<int> DeleteById(int reqId, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(
                login, RoleSkillRequirementQB.DELETE_BY_ID,
                new { ReqId = reqId });
        }

        // Executes paged query — called by BLL when paged mode
        public async Task<List<RoleSkillRequirementDTO>> GetPagedList(
            int firstNumber,
            int maxResult,
            LoginDTO login,
            CancellationToken ct)
        {
            var result = await _qe.QueryAsync<RoleSkillRequirementDTO>(
                login,
                RoleSkillRequirementQB.GET_JOBROLE_SKILL_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<RoleSkillRequirementDTO>> GetAllList(
    LoginDTO login,
    CancellationToken ct)
        {
            var result = await _qe.QueryAsync<RoleSkillRequirementDTO>(
                login,
                RoleSkillRequirementQB.GET_JOBROLE_SKILL_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,
                RoleSkillRequirementQB.GET_JOBROLE_SKILL_COUNT,
                null);
        }
    }
}