using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using System.Linq;
using SkillManagementDAL.DTO.EmployeeSkillProfile;
using SkillManagementDAL.DTO.SkillAssessment;
using SkillManagementDAL.DTO.SkillDomain;
using SkillManagementDAL.Query.EmployeeSkillProfile;
using SkillManagementDAL.Query.SkillAssessment;
using SkillManagementDAL.Query.SkillDomain;
using SkillManagementDAL.Query.SkillProcessStageMap;

namespace SkillManagementDAL.CustomCode.SkillAssessment
{
    public class SkillAssessmentDAL : ISkillAssessmentDAL
    {
        private readonly IQueryExecutor _qe;

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

        public async Task<string> GetHistory(int employeeId, LoginDTO login, CancellationToken ct)
        {
            var result = (await _qe.QueryAsync<SkillAssessmentDTO>(
                login, SkillAssessmentQB.GET_HISTORY,
                new { EmployeeId = employeeId, TenantId = login.ClientId })).ToList();

            // Multi-picklist support: rows saved together via one multi-skill/model
            // Save call share EmployeeId, AssessedById, AssessMethod, AssessedDt.
            // Group them so each row also carries the full sibling SkillIds/SkillNames
            // and ModelIds/ModelNames (joined from MSKILL / MMODEL), not just its own single ID.
            foreach (var group in result.GroupBy(r => new { r.EmployeeId, r.AssessedById, r.AssessMethod, r.AssessedDt }))
            {
                var skillIds = group.Select(r => r.SkillId).Distinct().ToList();
                var skillNames = group.Select(r => r.SkillName ?? 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.SkillIds = skillIds;
                    row.SkillNames = skillNames;
                    row.ModelIds = modelIds;
                    row.ModelNames = modelNames;
                }
            }

            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetSkillAssessment(int AssessmentId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QuerySingleAsync<SkillAssessmentDTO>(
                login, SkillAssessmentQB.GET_BY_ID,
                new { AssessmentId = AssessmentId, TenantId = login.ClientId });

            if (result != null)
            {
                var siblings = await _qe.QueryAsync<SkillAssessmentSiblingDTO>(
                    login, SkillAssessmentQB.GET_SIBLINGS,
                    new
                    {
                        EmployeeId = result.EmployeeId,
                        AssessedById = result.AssessedById,
                        AssessMethod = result.AssessMethod,
                        AssessedDt = result.AssessedDt,
                        TenantId = login.ClientId
                    });

                result.SkillIds = siblings.Select(s => s.SkillId).ToList();
                result.SkillNames = siblings.Select(s => s.SkillName ?? string.Empty).ToList();
                result.ModelIds = siblings.Select(s => s.ModelId).ToList();
                result.ModelNames = siblings.Select(s => s.ModelName ?? string.Empty).ToList();
            }

            return JsonConvert.SerializeObject(result);
        }

        // TASSESSMENT is INSERT-ONLY — no Update/Delete methods exist
        public async Task<int> Insert(
    SkillAssessmentDTO dto,
    EmployeeSkillProfileDTO profile,
    LoginDTO login,
    CancellationToken ct)
        {
            if (dto == null)
                throw new ArgumentNullException(nameof(dto));

            if (profile == null)
                throw new ArgumentNullException(nameof(profile));

            var statements = new List<(string sql, object param)>
    {
        (SkillAssessmentQB.INSERT, dto),
        (EmployeeSkillProfileQB.BULK_UPSERT, profile)
    };

            await _qe.ExecuteInTransactionAsync(login, statements);

            return 1;
        }

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