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.Skill;
using SkillManagementDAL.DTO.SkillGroup;
using SkillManagementDAL.Query.Skill;
using SkillManagementDAL.Query.SkillGroup;

namespace SkillManagementDAL.CustomCode.Skill
{
    public class SkillDAL : ISkillDAL
    {
        private readonly IQueryExecutor _qe;
        private readonly IValidation _Validation;
        public SkillDAL(IQueryExecutor qe, IValidation validation)
        {
            _qe = qe;
            _Validation = validation;
        }

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

        public async Task<string> GetById(int skillId, LoginDTO login, CancellationToken ct)
        {
            var skill = await _qe.QuerySingleAsync<SkillDTO>(
                login, SkillQB.GET_BY_ID,
                new { SkillId = skillId, TenantId = login.ClientId });

            if (skill != null)
            {
                var levels = await _qe.QueryAsync<DTO.SkillLevel.SkillLevelDTO>(
                    login, SkillQB.GET_LEVELS,
                    new { SkillId = skillId });
                skill.Levels = levels.ToList();
            }

            return JsonConvert.SerializeObject(skill);
        }

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

        // ─────────────────────────────────────────────────────────────
        // SAVE
        // ─────────────────────────────────────────────────────────────
        // Purpose:
        //      Inserts or updates Skill Master (MSKILL)
        //      and Skill Levels (MSKILLLEVEL)
        //
        // Features:
        //      • Transaction based save
        //      • Insert / Update support
        //      • Child level refresh handling
        //      • Full DDL alignment
        //      • LEVELCOUNT support added
        //      • NOTES support added
        //
        // Child Handling Strategy:
        //      UPDATE:
        //          DELETE existing levels
        //          INSERT latest levels
        //
        //      INSERT:
        //          Direct INSERT levels
        //
        // IMPORTANT:
        //      MSKILLLEVEL is a PURE CHILD TABLE.
        //      It does NOT contain audit fields.
        //
        // Tables:
        //      • MSKILL
        //      • MSKILLLEVEL
        // ─────────────────────────────────────────────────────────────
        public async Task<int> Save(
            SkillDTO dto,
            LoginDTO login,
            CancellationToken ct,
            bool isNew)
        {
            // =========================================================
            // Transaction Statement Collection
            // =========================================================
            var statements = new List<(string sql, object param)>();

            // =========================================================
            // Current Server Time
            // =========================================================
            var now = DateTime.UtcNow;

            // =========================================================
            // SKILL MASTER SAVE
            // =========================================================
            if (isNew)
            {
                // =====================================================
                // INSERT — MSKILL
                // =====================================================
                statements.Add((SkillQB.INSERT, new
                {
                    // -------------------------------------------------
                    // Primary / Relationship Fields
                    // -------------------------------------------------
                    dto.SkillId,
                    dto.GroupId,
                    dto.DomainId,

                    // -------------------------------------------------
                    // Business Fields
                    // -------------------------------------------------
                    dto.SkillCode,
                    dto.SkillName,
                    dto.SkillNameFull,

                    dto.SkillScope,

                    dto.LevelCount,

                    IsSpecialChar = (byte)dto.IsSpecialChar,

                    dto.MatrixSeqNo,

                    IsActive = (byte)dto.IsActive,

                    Remarks = dto.Remarks ?? "",

                    Notes = dto.Notes ?? "",

                    // -------------------------------------------------
                    // Standard Fields
                    // -------------------------------------------------
                    dto.SortOrder,

                    dto.SourceType,

                    dto.Status,

                    CreatedById = login.UserId,
                    CreatedOn = now,

                    ModifiedById = login.UserId,
                    ModifiedOn = now,

                    TenantId = login.ClientId
                }));
            }
            else
            {
                // =====================================================
                // UPDATE — MSKILL
                // =====================================================
                statements.Add((SkillQB.UPDATE, new
                {
                    // -------------------------------------------------
                    // Primary / Relationship Fields
                    // -------------------------------------------------
                    dto.SkillId,
                    dto.GroupId,
                    dto.DomainId,

                    // -------------------------------------------------
                    // Business Fields
                    // -------------------------------------------------
                    dto.SkillCode,
                    dto.SkillName,
                    dto.SkillNameFull,

                    dto.SkillScope,

                    dto.LevelCount,

                    IsSpecialChar = (byte)dto.IsSpecialChar,

                    dto.MatrixSeqNo,

                    IsActive = (byte)dto.IsActive,

                    Remarks = dto.Remarks ?? "",

                    Notes = dto.Notes ?? "",

                    // -------------------------------------------------
                    // Standard Fields
                    // -------------------------------------------------
                    dto.Status,

                    dto.SortOrder,

                    dto.SourceType,

                    ModifiedById = login.UserId,
                    ModifiedOn = now,

                    TenantId = login.ClientId
                }));

                // =====================================================
                // DELETE EXISTING LEVELS
                // =====================================================
                // Child rows are refreshed completely.
                // =====================================================
                statements.Add((SkillQB.DELETE_LEVELS, new
                {
                    SkillId = dto.SkillId
                }));
            }

            // =========================================================
            // INSERT SKILL LEVELS
            // =========================================================
            if (dto.Levels != null && dto.Levels.Count > 0)
            {
                foreach (var level in dto.Levels)
                {
                    statements.Add((SkillQB.INSERT_LEVEL, new
                    {
                        // -------------------------------------------------
                        // Primary / Relationship Fields
                        // -------------------------------------------------
                        level.SLevelId,

                        SkillId = dto.SkillId,

                        // -------------------------------------------------
                        // Business Fields
                        // -------------------------------------------------
                        level.SlNo,

                        level.LevelLabel,

                        level.LevelCount,

                        level.LevelDescriptor,

                        IsMinRequired = (byte)level.IsMinRequired,

                        CanTrainOthers = (byte)level.CanTrainOthers,

                        Remarks = level.Remarks ?? ""
                    }));
                }
            }

            // =========================================================
            // EXECUTE TRANSACTION
            // =========================================================
            await _qe.ExecuteInTransactionAsync(
                login,
                statements);

            // =========================================================
            // RETURN PK
            // =========================================================
            return dto.SkillId;
        }
        public async Task<bool> GroupExists(int groupId, LoginDTO login)
        {
            var count = await _qe.ExecuteScalarAsync<int>(
                login,
                @"SELECT COUNT(1)
          FROM MSKILLGROUP
          WHERE GROUPID = @GroupId",
                new { GroupId = groupId }
            );

            return count > 0;
        }
        public async Task<bool> IsGroupInDomain(int groupId, int domainId, LoginDTO login)
        {
            var count = await _qe.ExecuteScalarAsync<int>(
                login,
                @"SELECT COUNT(1)
          FROM MSKILLGROUP
          WHERE GROUPID = @GroupId
          AND DOMAINID = @DomainId",
                new { GroupId = groupId, DomainId = domainId }
            );

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

            return count > 0;
        }
        public async Task<string> DeleteSkill(int skillId, LoginDTO loginDTO)
        {
            try
            {
                string sql = SkillQB.DELETE_SKILL;

                var parameter = new { SkillId = skillId }; // 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);
            }
        }

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