using RecruitmentDAL.DTO.ManpowerBudget;
using RecruitmentDAL.Query.ManpowerBudget;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace RecruitmentDAL.CustomeCode.ManpowerBudget
{
    public class ManpowerBudgetDAL : IManpowerBudgetDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetManpowerBudget(int ManpowerBudgetId, LoginDTO LoginDTO, CancellationToken ct)
        {
            string json = "";
            try
            {
                string sql = ManpowerBudgetQB.GET_MANPOWERBUDGET;
                var parameters = new { ManpowerBudgetId = ManpowerBudgetId, TenantId = LoginDTO.ClientId };
                ManpowerBudgetDTO ManpowerBudgetDTOs = await _QueryExecutor
                    .QuerySingleAsync<ManpowerBudgetDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
                json = JsonConvert.SerializeObject(ManpowerBudgetDTOs);
                return json;
            }
            catch
            {
                throw;
            }
        }

        // Resolution lookup used only when the calling JobRequisition's own BudgetReferenceId is
        // unset (-1) — see ManpowerBudgetQB.GET_ACTIVE_MANPOWERBUDGET_FOR_DEPARTMENT_PERIOD.
        // Returns JSON "null" when no active budget row exists for this Department/FiscalYear.
        public async Task<string> GetActiveManpowerBudgetForDepartmentPeriod(int DepartmentId, short FiscalYear, byte FiscalQuarter, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ManpowerBudgetQB.GET_ACTIVE_MANPOWERBUDGET_FOR_DEPARTMENT_PERIOD;
                var parameters = new
                {
                    DepartmentId = DepartmentId,
                    FiscalYear = FiscalYear,
                    FiscalQuarter = FiscalQuarter,
                    TenantId = LoginDTO.ClientId
                };

                ManpowerBudgetDTO result = await _QueryExecutor
                    .QuerySingleAsync<ManpowerBudgetDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Select List

        public async Task<string> GetSelectListManpowerBudget(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ManpowerBudgetQB.GET_SELECTLIST_MANPOWERBUDGET;

                var parameters = new
                {
                    TenantId = LoginDTO.ClientId
                };

                var result = await _QueryExecutor
                    .QueryAsync<ManpowerBudgetDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Save

        // Called from BaseEntityAppService.ExecuteSaveAsync's persistFunc — must run on the
        // pipeline's own transaction (tx) so validation/workflow/event-publish/outbox all commit
        // or roll back atomically with the insert.
        public async Task<int> SaveManpowerBudget(ManpowerBudgetDTO ManpowerBudgetDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = ManpowerBudgetQB.SAVE_MANPOWERBUDGET;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, ManpowerBudgetDTO, tx, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.SaveErrorMessage);

                throw new Exception(error);
            }
        }

        #endregion

        #region Update

        public async Task<int> UpdateManpowerBudget(ManpowerBudgetDTO ManpowerBudgetDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = ManpowerBudgetQB.UPDATE_MANPOWERBUDGET;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, ManpowerBudgetDTO, tx, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.UpdateErrorMessage);

                throw new Exception(error);
            }
        }

        // Standalone follow-up call — NOT part of any ExecuteSaveAsync transaction. See
        // ManpowerBudgetQB.INCREMENT_MANPOWERBUDGET_CONSUMED doc comment for why.
        public async Task IncrementManpowerBudgetConsumed(int ManpowerBudgetId, short HeadcountDelta, decimal BudgetAmountDelta, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ManpowerBudgetQB.INCREMENT_MANPOWERBUDGET_CONSUMED;
                var parameters = new
                {
                    ManpowerBudgetId = ManpowerBudgetId,
                    HeadcountDelta = HeadcountDelta,
                    BudgetAmountDelta = BudgetAmountDelta,
                    ModifiedById = LoginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = LoginDTO.ClientId
                };

                await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.UpdateErrorMessage)
                    .ConfigureAwait(false);

                throw new Exception(error);
            }
        }

        #endregion

        #region Delete

        public async Task<string> DeleteManpowerBudget(int ManpowerBudgetId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ManpowerBudgetQB.DELETE_MANPOWERBUDGET;
                var parameter = new
                {
                    ManpowerBudgetId = ManpowerBudgetId,
                    TenantId = LoginDTO.ClientId,
                    ModifiedById = LoginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow
                };

                int result = await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, parameter, cancellationToken: ct)
                    .ConfigureAwait(false);

                return result > 0
                    ? SuccessResponse.DeleteSuccessMessage
                    : ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.DeleteErrorMessage);

                throw new Exception(error);
            }
        }

        #endregion
    }
}