using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using SkillManagementDAL.DTO.SkillMatrixConfig;
using SkillManagementDAL.Query.SkillMatrixConfig;

namespace SkillManagementDAL.CustomCode.SkillMatrixConfig
{
    public class SkillMatrixConfigDAL : ISkillMatrixConfigDAL
    {
        private readonly IQueryExecutor _qe;

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

        // ========================================================
        // LIST
        // ========================================================
        public async Task<string> GetList(LoginDTO login, CancellationToken ct)
        {
            var rows = await _qe.QueryAsync<SkillMatrixConfigDTO>(
                login,
                SkillMatrixConfigQB.GET_LIST,
                new { TenantId = login.ClientId });

            var result = rows.Select(MapFromDto).ToList();
            return JsonConvert.SerializeObject(result);
        }

        // ========================================================
        // GET ALL LIST
        // ========================================================
        public async Task<List<SkillMatrixConfigDTO>> GetAllList(LoginDTO login, CancellationToken ct)
        {
            var rows = await _qe.QueryAsync<SkillMatrixConfigDTO>(
                login,
                SkillMatrixConfigQB.GET_SKILLMATRIXCONFIG_LIST_ALL,
                new { TenantId = login.ClientId },
                null, false, ct);

            return rows.Select(MapFromDto).ToList();
        }

        // ========================================================
        // GET PAGED LIST
        // ========================================================
        public async Task<List<SkillMatrixConfigDTO>> GetPagedList(int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            var rows = await _qe.QueryAsync<SkillMatrixConfigDTO>(
                login,
                SkillMatrixConfigQB.GET_SKILLMATRIXCONFIG_LIST_PAGED,
                new { TenantId = login.ClientId, FirstNumber = firstNumber, MaxResult = maxResult },
                null, false, ct);

            return rows.Select(MapFromDto).ToList();
        }

        // ========================================================
        // GET BY ID
        // ========================================================
        public async Task<SkillMatrixConfigDTO?> GetById(
            int configId,
            LoginDTO login,
            CancellationToken ct)
        {
            var dto = await _qe.QuerySingleAsync<SkillMatrixConfigDTO>(
                login,
                SkillMatrixConfigQB.GET_BY_ID,
                new
                {
                    ConfigId = configId,
                    TenantId = login.ClientId
                },
                cancellationToken: ct);

            return dto is null ? null : MapFromDto(dto);
        }
        public async Task<string> GetSelectList(
            CriteriaDTO criteriaDTO,
            LoginDTO login,
            CancellationToken ct)
        {
            var result = await _qe.QueryWithCriteriaAsync<dynamic>(
                login,
                SkillMatrixConfigQB.GET_SELECT_LIST,
                new { TenantId = login.ClientId },
                criteriaDTO,
                ct);

            return JsonConvert.SerializeObject(result);
        }
        // ========================================================
        // MAP DTO (FIXED - was missing)
        // ========================================================
        private static SkillMatrixConfigDTO MapFromDto(SkillMatrixConfigDTO dto)
        {
            dto.TargetRoleIds = ParseJsonList(dto.TargetRoleIdsJson);
            dto.ModelIds = ParseJsonList(dto.ModelIdsJson);
            dto.IncludeSkillIds = ParseJsonList(dto.IncludeSkillIdsJson);

            return dto;
        }

        private static List<int> ParseJsonList(string? value)
        {
            if (string.IsNullOrWhiteSpace(value))
                return new List<int>();

            value = value.Trim();

            try
            {
                // JSON array format  e.g. [-1499999971,-1499999970]
                if (value.StartsWith("["))
                {
                    return System.Text.Json.JsonSerializer.Deserialize<List<int>>(value)
                           ?? new List<int>();
                }

                // CSV fallback — x != 0 preserves negative GoodBooks AutoNumber IDs
                return value
                    .Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                    .Select(x => int.TryParse(x, out var id) ? id : 0)
                    .Where(x => x != 0)
                    .Distinct()
                    .ToList();
            }
            catch
            {
                return new List<int>();
            }
        }

        // ========================================================
        // INSERT
        // ========================================================
        public async Task<int> Insert(SkillMatrixConfigDTO dto, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(login, SkillMatrixConfigQB.INSERT, new
            {
                dto.ConfigId,
                dto.MatrixCode,
                dto.MatrixTitle,
                dto.DomainId,
                dto.TargetDept,

                TargetRoleIds = dto.TargetRoleIdsJson,
                dto.ProductFamilyId,
                ModelIds = dto.ModelIdsJson,
                IncludeSkillIds = dto.IncludeSkillIdsJson,

                dto.ShowSummaryRow,
                dto.ShowMinLevel,
                dto.UpdateFrequency,
                dto.IsActive,
                dto.Status,
                dto.SortOrder,
                dto.CreatedById,
                dto.ModifiedById,
                dto.SourceType,
                dto.TenantId
            });
        }

        // ========================================================
        // UPDATE
        // ========================================================
        public async Task<int> Update(SkillMatrixConfigDTO dto, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(login, SkillMatrixConfigQB.UPDATE, new
            {
                dto.ConfigId,
                dto.MatrixCode,
                dto.MatrixTitle,
                dto.DomainId,
                dto.TargetDept,

                TargetRoleIds = dto.TargetRoleIdsJson,
                dto.ProductFamilyId,
                ModelIds = dto.ModelIdsJson,
                IncludeSkillIds = dto.IncludeSkillIdsJson,

                dto.ShowSummaryRow,
                dto.ShowMinLevel,
                dto.UpdateFrequency,
                dto.IsActive,
                dto.Status,
                dto.SortOrder,
                dto.ModifiedById,
                dto.TenantId
            });
        }

        // ========================================================
        // VALIDATE SKILLS
        // ========================================================
        public async Task<bool> ValidateSkills(List<int> skillIds, LoginDTO login)
        {
            var count = await _qe.ExecuteScalarAsync<int>(login, @"
                SELECT COUNT(1)
                FROM MSKILL
                WHERE SKILLID IN @SkillIds
                  AND TENANTID = @TenantId
                  AND STATUS = 1
            ", new
            {
                SkillIds = skillIds,
                TenantId = login.ClientId
            });

            return count == skillIds.Count;
        }

        // ========================================================
        // EXISTS CHECK
        // ========================================================
        public async Task<bool> ConfigExists(int configId, LoginDTO login)
        {
            var count = await _qe.ExecuteScalarAsync<int>(login, @"
                SELECT COUNT(1)
                FROM MSKILLMATRIX
                WHERE CONFIGID = @ConfigId
                  AND TENANTID = @TenantId
            ", new
            {
                ConfigId = configId,
                TenantId = login.ClientId
            });

            return count > 0;
        }

        // ========================================================
        // SOFT DELETE
        // ========================================================
        public async Task<int> SoftDelete(int configId, int modifiedById, LoginDTO login, CancellationToken ct)
        {
            return await _qe.ExecuteAsync(
                login,
                SkillMatrixConfigQB.SOFT_DELETE,
                new
                {
                    ConfigId = configId,
                    ModifiedById = modifiedById,
                    TenantId = login.ClientId
                });
        }

    }
}