using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using System.Linq;
using SkillManagementDAL.DTO.SkillGroup;
using SkillManagementDAL.DTO.SkillProductMap;
using SkillManagementDAL.Query.SkillDomain;
using SkillManagementDAL.Query.SkillGroup;
using SkillManagementDAL.Query.SkillProductMap;

namespace SkillManagementDAL.CustomCode.SkillProductMap
{
    public class SkillProductMapDAL : ISkillProductMapDAL
    {
        private readonly IQueryExecutor _qe;

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

        public async Task<string> GetBySkill(int skillId, LoginDTO login, CancellationToken ct)
        {
            var result = (await _qe.QueryAsync<SkillProductMapDTO>(
                login, SkillProductMapQB.GET_BY_SKILL,
                new { SkillId = skillId })).ToList();

            ApplyMultiPicklistFields(result);

            return JsonConvert.SerializeObject(result);
        }

        // Multi-picklist support without a schema change: rows sharing the same
        // SkillId are siblings of one multi-select save — collect their
        // ProductFamilyIds/ModelIds (and names) onto every row for display.
        private static void ApplyMultiPicklistFields(List<SkillProductMapDTO> rows)
        {
            foreach (var group in rows.GroupBy(r => r.SkillId))
            {
                var productFamilyIds = group.Select(r => r.ProductFamilyId).Distinct().ToList();
                var productFamilyNames = group.Select(r => r.ProductFamilyName ?? string.Empty).Distinct().ToList();
                var modelIds = group.Select(r => r.ModelId).Distinct().ToList();
                var modelNames = group.Select(r => r.ModelName ?? string.Empty).Distinct().ToList();

                foreach (var row in group)
                {
                    row.ProductFamilyIds = productFamilyIds;
                    row.ProductFamilyNames = productFamilyNames;
                    row.ModelIds = modelIds;
                    row.ModelNames = modelNames;
                }
            }
        }
        public async Task<string> GetByMapId(int MapId, LoginDTO login, CancellationToken ct)
        {
            var result = (await _qe.QueryAsync<SkillProductMapDTO>(
                login, SkillProductMapQB.GET_BY_ID,
                new { MapId = MapId })).ToList();
            ApplyMultiPicklistFields(result);
            return JsonConvert.SerializeObject(result);
        }
        public async Task<string> GetByProductFamily(int ProductFamilyId, LoginDTO login, CancellationToken ct)
        {
            var result = (await _qe.QueryAsync<SkillProductMapDTO>(
                login, SkillProductMapQB.GET_BY_PRODUCT_FAMILY,
                new { ProductFamilyId = ProductFamilyId })).ToList();
            ApplyMultiPicklistFields(result);
            return JsonConvert.SerializeObject(result);
        }
        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryWithCriteriaAsync<dynamic>(
                login, SkillProductMapQB.GET_SELECT_LIST,
                new { TenantId = login.ClientId }, criteriaDTO, ct);
            return JsonConvert.SerializeObject(result);
        }
        public async Task<int> SaveOrUpdate(IEnumerable<SkillProductMapDTO> maps, int skillId, LoginDTO login, CancellationToken ct)
        {
            var mapList = maps?.ToList() ?? new List<SkillProductMapDTO>();
            if (!mapList.Any())
                return 0;

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

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

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

                if (m.MapId > 0)
                {
                    // Update existing
                    statements.Add((SkillProductMapQB.UPDATE, new
                    {
                        m.MapId,
                        m.SkillId,
                        m.SlNo,
                        m.ProductFamilyId,
                        m.ModelId,
                        m.SpecificParameters,
                        m.IsActive,
                        m.Remarks
                    }));
                }
                else
                {
                    // Insert new
                    statements.Add((SkillProductMapQB.INSERT, new
                    {
                        m.MapId,
                        m.SkillId,
                        m.SlNo,
                        m.ProductFamilyId,
                        m.ModelId,
                        m.SpecificParameters,
                        m.IsActive,
                        m.Remarks
                    }));
                }
            }

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

            return mapList.Count;
        }

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

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

            ApplyMultiPicklistFields(result);

            return result; // ✅ FIX
        }


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

            ApplyMultiPicklistFields(result);

            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,
                SkillProductMapQB.GET_SKILLPRODUCT_COUNT,
                null);
        }

    }
}