using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using TMSDAL.DTO.KpiPosting;
using TMSDAL.Query.KpiPosting;

namespace TMSDAL.CustomCode.KpiPosting
{
    public class TmsKpiPostingDAL : ITmsKpiPostingDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public TmsKpiPostingDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<(int PeriodDetailId, DateTime FromDate, DateTime ToDate)?> ResolveCurrentMonthlyPeriod(LoginDTO login, CancellationToken ct)
        {
            var row = (await _queryExecutor.QueryAsync<PeriodRow>(
                login, TmsKpiPostingQB.RESOLVE_CURRENT_MONTHLY_PERIOD, cancellationToken: ct).ConfigureAwait(false)).FirstOrDefault();
            return row is null ? null : (row.PeriodDetailId, row.FromDate, row.ToDate);
        }

        public async Task<int?> GetPreviousMonthlyPeriodDetailId(int periodDetailId, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.ExecuteScalarAsync<int?>(
                login, TmsKpiPostingQB.GET_PREVIOUS_MONTHLY_PERIOD_DETAIL_ID,
                new { PeriodDetailId = periodDetailId }, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<decimal?> GetPreviousActual(int kpiId, int periodDetailId, int ouId, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_PREVIOUS_ACTUAL,
                new { KPIId = kpiId, PeriodDetailId = periodDetailId, OUId = ouId }, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<List<KpiStatusBandDTO>> GetStatusBands(int kpiId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<KpiStatusBandDTO>(
                login, TmsKpiPostingQB.GET_STATUS_BANDS, new { KPIId = kpiId }, cancellationToken: ct).ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task DeleteExistingValues(int periodDetailId, int ouId, List<int> kpiIds, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login, TmsKpiPostingQB.DELETE_EXISTING_VALUES,
                new { PeriodDetailId = periodDetailId, OUId = ouId, KpiIds = kpiIds }, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task InsertValue(int kpiId, int periodDetailId, int ouId, decimal? actual, byte? trend, int statusId, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login, TmsKpiPostingQB.INSERT_VALUE,
                new { KPIId = kpiId, PeriodDetailId = periodDetailId, OUId = ouId, Actual = actual, Trend = trend, StatusId = statusId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<decimal?> GetComplianceCoveragePct(DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_COMPLIANCE_COVERAGE_PCT, new { TenantId = login.ClientId, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetNominationApprovalTatDays(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_NOMINATION_APPROVAL_TAT_DAYS, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetCostPerTrainedEmployee(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_COST_PER_TRAINED_EMPLOYEE, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetVenueUtilizationPct(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_VENUE_UTILIZATION_PCT, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetPassRatePct(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_PASS_RATE_PCT, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetFirstAttemptPassRatePct(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_FIRST_ATTEMPT_PASS_RATE_PCT, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetSkillUpgradeApprovalRatePct(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_SKILL_UPGRADE_APPROVAL_RATE_PCT, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetCertRenewalCompliancePct(DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_CERT_RENEWAL_COMPLIANCE_PCT, new { TenantId = login.ClientId, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetExpiringCertificates30D(LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_EXPIRING_CERTIFICATES_30D, new { TenantId = login.ClientId }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetFeedbackResponseRatePct(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_FEEDBACK_RESPONSE_RATE_PCT, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<decimal?> GetLearningGainPct(DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
            => await _queryExecutor.ExecuteScalarAsync<decimal?>(
                login, TmsKpiPostingQB.GET_LEARNING_GAIN_PCT, new { TenantId = login.ClientId, FromDate = fromDate, ToDate = toDate }, cancellationToken: ct).ConfigureAwait(false);

        private class PeriodRow
        {
            public int PeriodDetailId { get; set; }
            public DateTime FromDate { get; set; }
            public DateTime ToDate { get; set; }
        }
    }
}