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

namespace SkillManagementDAL.CustomCode.SkillProcessStageMap
{
    public class SkillProcessStageMapDAL : ISkillProcessStageMapDAL
    {
        private readonly IQueryExecutor _qe;

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

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

        public async Task<int> Save(IEnumerable<SkillProcessStageMapDTO> maps, int skillId, LoginDTO login, CancellationToken ct)
        {
            var mapList = maps.ToList();
            if (!mapList.Any())
                return 0;

            var statements = new List<(string sql, object param)>();

            // 1️⃣ Delete maps that are not in incoming list
            var existingIds = mapList.Where(m => m.MapId > 0).Select(m => m.MapId).ToList();
            if (existingIds.Any())
            {
                statements.Add((@"
            DELETE FROM MROUTINGSKILL
            WHERE SKILLID = @SkillId
              AND MAPID NOT IN @MapIds
        ", new { SkillId = skillId, MapIds = existingIds }));
            }
            else
            {
                // No existing maps → delete all
                statements.Add((SkillProcessStageMapQB.DELETE_BY_SKILL, new { SkillId = skillId }));
            }

            // 2️⃣ Insert or Update each map
            foreach (var m in mapList)
            {
                m.SkillId = skillId;

                if (m.MapId > 0)
                {
                    // Update existing
                    statements.Add((SkillProcessStageMapQB.UPDATE, new
                    {
                        m.MapId,
                        m.SkillId,
                        m.SlNo,
                        m.RoutingId,
                        m.RoutingOpCode,
                        m.RoutingOpId,
                        m.WorkCenterId,
                        m.IsPrerequisite,
                        m.MinLevelRequired,
                        m.Remarks
                    }));
                }
                else
                {
                    // Insert new
                    statements.Add((SkillProcessStageMapQB.INSERT, new
                    {
                        m.MapId,
                        m.SkillId,
                        m.SlNo,
                        m.RoutingId,
                        m.RoutingOpCode,
                        m.RoutingOpId,
                        m.WorkCenterId,
                        m.IsPrerequisite,
                        m.MinLevelRequired,
                        m.Remarks
                    }));
                }
            }

            // 3️⃣ Execute transaction
            await _qe.ExecuteInTransactionAsync(login, statements);

            return mapList.Count;
        }

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

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

        // Executes paged query — called by BLL when paged mode
        public async Task<List<SkillProcessStageMapDTO>> GetPagedList(
            int firstNumber,
            int maxResult,
            LoginDTO login,
            CancellationToken ct)
        {
            var result = await _qe.QueryAsync<SkillProcessStageMapDTO>(
                login,
                SkillProcessStageMapQB.GET_ROUTINGSKILL_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<SkillProcessStageMapDTO>> GetAllList(
    LoginDTO login,
    CancellationToken ct)
        {
            var result = await _qe.QueryAsync<SkillProcessStageMapDTO>(
                login,
                SkillProcessStageMapQB.GET_ROUTINGSKILL_LIST_ALL,
                null!,
                null,              // transaction
                false,             // useReadUncommitted
                ct                 // ✅ correct position for cancellationToken
            );

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

        public async Task<byte> GetSlNoBySkillLevelId(int skillLevelId, LoginDTO login)
        {
            var result = await _qe.QuerySingleAsync<byte>(
                login, SkillProcessStageMapQB.GET_SLNO_BY_SLEVELID,
                new { SkillLevelId = skillLevelId });
            return result;
        }

        // Executes count query — called by BLL when count is needed
        public Task<int> GetCount(
            LoginDTO login,
            CancellationToken ct)
        {
            return _qe.ExecuteScalarAsync<int>(
                login,
                SkillProcessStageMapQB.GET_ROUTINGSKILL_COUNT,
                null);
        }

    }
}