using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using TMSDAL.DTO.TrainingEnrollment;
using TMSDAL.Enums;
using TMSDAL.Query.TrainingEnrollment;

namespace TMSDAL.CustomCode.TrainingEnrollment
{
    public class TrainingEnrollmentDAL : ITrainingEnrollmentDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public TrainingEnrollmentDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation = validation;
        }

        // ════════════════════════════════════════════════════════════════════
        // READ
        // ════════════════════════════════════════════════════════════════════

        /// <summary>Fetch full enrollment record by its primary key.</summary>
        public async Task<TrainingEnrollmentDTO?> GetTrainingEnrollment(int enrollmentId, LoginDTO loginDTO)
        {
            var parameters = new { EnrollmentId = enrollmentId, TenantId = loginDTO.ClientId };
            return await _QueryExecutor.QuerySingleAsync<TrainingEnrollmentDTO>(
                loginDTO, TrainingEnrollmentQB.GET_ENROLLMENT, parameters);
        }

        /// <summary>
        /// Fetch an ACTIVE enrollment by unique constraint columns
        /// (InstanceId + EmployeeId + TenantId).
        /// Used by BLL for duplicate-enrollment detection before insert.
        /// Returns null when no active enrollment exists.
        /// Withdrawn / Rejected rows are excluded — matching the filtered
        /// unique index — so re-enrollment after those states is permitted.
        /// </summary>
        public async Task<TrainingEnrollmentDTO?> GetEnrollment(int instanceId, int employeeId, LoginDTO loginDTO)
        {
            var parameters = new
            {
                InstanceId = instanceId,
                EmployeeId = employeeId,
                TenantId = loginDTO.ClientId
            };
            return await _QueryExecutor.QuerySingleAsync<TrainingEnrollmentDTO>(
                loginDTO, TrainingEnrollmentQB.GET_ENROLLMENT_BY_INSTANCE_EMPLOYEE, parameters);
        }

        /// <summary>All enrollments for a given training instance (JSON).</summary>
        public async Task<string> GetEnrollmentsByInstance(int instanceId, LoginDTO loginDTO)
        {
            var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
            var result = await _QueryExecutor.QueryAsync<TrainingEnrollmentListDTO>(
                loginDTO, TrainingEnrollmentQB.GET_ENROLLMENTS_BY_INSTANCE, parameters);
            return JsonConvert.SerializeObject(result);
        }

        /// <summary>All enrollments for a given employee (JSON).</summary>
        public async Task<string> GetEnrollmentsByEmployee(int employeeId, LoginDTO loginDTO)
        {
            var parameters = new { EmployeeId = employeeId, TenantId = loginDTO.ClientId };
            var result = await _QueryExecutor.QueryAsync<TrainingEnrollmentListDTO>(
                loginDTO, TrainingEnrollmentQB.GET_ENROLLMENTS_BY_EMPLOYEE, parameters);
            return JsonConvert.SerializeObject(result);
        }

        /// <summary>Full enrollment list for the tenant (JSON).</summary>
        public async Task<string> GetEnrollmentList(LoginDTO loginDTO)
        {
            var parameters = new { TenantId = loginDTO.ClientId };
            var result = await _QueryExecutor.QueryAsync<TrainingEnrollmentListDTO>(
                loginDTO, TrainingEnrollmentQB.GET_ENROLLMENT_LIST, parameters);
            return JsonConvert.SerializeObject(result);
        }

        /// <summary>Paged / filtered select-list for dropdowns (JSON).</summary>
        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryWithCriteriaAsync<dynamic>(
                loginDTO,
                TrainingEnrollmentQB.GET_SELECTLIST_TRAINING_ENROLLMENT,
                new { TenantId = loginDTO.ClientId },
                criteriaDTO,
                ct);
            return JsonConvert.SerializeObject(result);
        }

        /// <summary>
        /// Next available waitlist position for a given instance.
        /// Returns MAX(WaitlistPosition) + 1, or 1 if no waitlisted rows exist.
        /// </summary>
        public async Task<int> GetNextWaitlistPosition(int instanceId, LoginDTO loginDTO)
        {
            var parameters = new
            {
                InstanceId = instanceId,
                WaitlistedStatus = (byte)EnrollmentStatus.Waitlisted,
                TenantId = loginDTO.ClientId
            };
            return await _QueryExecutor.ExecuteScalarAsync<int>(
                loginDTO, TrainingEnrollmentQB.GET_NEXT_WAITLIST_POSITION, parameters);
        }

        // ════════════════════════════════════════════════════════════════════
        // WRITE
        // ════════════════════════════════════════════════════════════════════

        /// <summary>
        /// Insert a new enrollment record.
        /// Returns Task (not Task&lt;int&gt;) — BLL never used rows-affected.
        /// Throws a formatted exception on DB error.
        /// </summary>
        public async Task SaveTrainingEnrollment(TrainingEnrollmentDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingEnrollmentQB.SAVE_ENROLLMENT, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        /// <summary>
        /// Unified Manager + HR approval / rejection.
        /// ApprovalType on the DTO determines which query and column set is used.
        /// Remarks are written to tier-specific columns (MANAGERREMARKS / HRREMARKS)
        /// so each approval tier has its own audit trail.
        /// </summary>
        public async Task<int> UpdateApproval(ApprovalUpdateDTO approvalDto, LoginDTO loginDTO)
        {
            try
            {
                var now = DateTime.UtcNow;

                (object parameters, string query) = approvalDto.ApprovalType switch
                {
                    ApprovalType.Manager => (
                        (object)new
                        {
                            EnrollmentId = approvalDto.EnrollmentId,
                            EnrollmentStatus = (byte)approvalDto.Status,
                            ManagerApprovalId = approvalDto.ApprovalId,
                            ManagerRemarks = approvalDto.Remarks,
                            ManagerApprovedOn = now,
                            ModifiedById = loginDTO.UserId,
                            ModifiedOn = now,
                            TenantId = loginDTO.ClientId
                        },
                        TrainingEnrollmentQB.UPDATE_MANAGER_APPROVAL
                    ),

                    ApprovalType.HR => (
                        (object)new
                        {
                            EnrollmentId = approvalDto.EnrollmentId,
                            EnrollmentStatus = (byte)approvalDto.Status,
                            HRApprovalId = approvalDto.ApprovalId,
                            HRRemarks = approvalDto.Remarks,
                            HRApprovedOn = now,
                            ModifiedById = loginDTO.UserId,
                            ModifiedOn = now,
                            TenantId = loginDTO.ClientId
                        },
                        TrainingEnrollmentQB.UPDATE_HR_APPROVAL
                    ),

                    _ => throw new ArgumentOutOfRangeException(
                             nameof(approvalDto.ApprovalType),
                             $"Unsupported ApprovalType: {approvalDto.ApprovalType}")
                };

                return await _QueryExecutor.ExecuteAsync(loginDTO, query, parameters);
            }
            catch (ArgumentOutOfRangeException)
            {
                // Programming error — not a DB error; bypass _Validation.HandleException
                throw;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        /// <summary>
        /// Withdraw an enrollment.
        /// Status is always EnrollmentStatus.Withdrawn — enforced by BLL.
        /// Records WithdrawalReason and WithdrawnOn timestamp.
        /// </summary>
        public async Task<int> WithdrawEnrollment(int enrollmentId, byte status, string? reason, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new
                {
                    EnrollmentId = enrollmentId,
                    EnrollmentStatus = status,
                    WithdrawalReason = reason,
                    WithdrawnOn = DateTime.UtcNow,
                    ModifiedById = loginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = loginDTO.ClientId
                };
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO, TrainingEnrollmentQB.WITHDRAW_ENROLLMENT, parameters);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        /// <summary>
        /// Soft-delete: sets STATUS = 0.
        /// Hard delete not permitted — enrollment history must be preserved.
        /// </summary>
        public async Task<int> DeleteTrainingEnrollment(int enrollmentId, LoginDTO loginDTO)
        {
            try
            {
                // FIX: ModifiedById / ModifiedOn now passed for audit trail on soft-delete
                var parameters = new
                {
                    EnrollmentId = enrollmentId,
                    ModifiedById = loginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = loginDTO.ClientId
                };
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO, TrainingEnrollmentQB.DELETE_ENROLLMENT, parameters);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }
    }
}