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.CertRenewalAlert;
using TMSDAL.Enums;
using TMSDAL.Query.Certificate;
using TMSDAL.Query.ExternalVendor;

namespace TMSDAL.CustomCode.Certificate
{
    public class TrainingCertificateDAL : ITrainingCertificateDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<TrainingCertificateDTO?> GetCertificate(int TrainingCertificateId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TrainingCertificateId = TrainingCertificateId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.QuerySingleAsync<TrainingCertificateDTO>(loginDTO, CertificateQB.GET_CERTIFICATE, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task<string> GetCertificateListByEmployee( LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QueryAsync<TrainingCertificateDTO>(loginDTO, CertificateQB.GET_CERTIFICATE_LIST_BY_EMPLOYEE, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception) { throw; }
        }
        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<dynamic>(
                login, CertificateQB.GET_SELECTLIST_CERTIFICATE,
                new { TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<int> SaveCertificate(TrainingCertificateDTO dto, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(loginDTO, CertificateQB.SAVE_CERTIFICATE, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateCertificate(TrainingCertificateDTO dto, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(loginDTO, CertificateQB.UPDATE_CERTIFICATE, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> RevokeCertificate(int TrainingCertificateId, string reason, int revokedById, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new Dictionary<string, object>
                {
                    ["TrainingCertificateId"] = TrainingCertificateId,
                    ["CertStatus"] = (byte)CertStatus.Revoked,
                    ["RevokedReason"] = reason,
                    ["RevokedOn"] = DateTime.UtcNow,
                    ["RevokedById"] = revokedById,
                    ["ModifiedById"] = revokedById,
                    ["ModifiedOn"] = DateTime.UtcNow,
                    ["TenantId"] = loginDTO.ClientId
                };

                return await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    CertificateQB.REVOKE_CERTIFICATE,
                    parameters
                );
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> MarkCertRenewed(int trainingCertificateId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new Dictionary<string, object>
                {
                    ["TrainingCertificateId"] = trainingCertificateId,
                    ["CertStatus"]            = (byte)CertStatus.Expired,
                    ["ModifiedById"]          = loginDTO.UserId,
                    ["ModifiedOn"]            = DateTime.UtcNow,
                    ["TenantId"]              = loginDTO.ClientId
                };
                return await _QueryExecutor.ExecuteAsync(loginDTO, CertificateQB.MARK_CERT_RENEWED, parameters);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task SaveCertificateBulk(IEnumerable<TrainingCertificateDTO> records, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.BulkInsertAsync(loginDTO, CertificateQB.BULK_INSERT_CERTIFICATE, records);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task UpdateCertificateBulk(IEnumerable<TrainingCertificateDTO> records, LoginDTO loginDTO)
        {
            try
            {
                foreach (var record in records)
                    await _QueryExecutor.ExecuteAsync(loginDTO, CertificateQB.BULK_UPDATE_CERTIFICATE, record);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        /// <summary>Log table — INSERT ONLY, no UPDATE or DELETE.</summary>
        public async Task LogCertRenewalAlert(CertRenewalAlertDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, CertificateQB.LOG_RENEWAL_ALERT, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<CertificateVerificationDTO?> GetCertificateByVerificationToken(string verificationToken, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<CertificateVerificationDTO>(
                    loginDTO, CertificateQB.GET_CERTIFICATE_BY_VERIFICATION_TOKEN,
                    new { VerificationToken = verificationToken },
                    cancellationToken: ct).ConfigureAwait(false);
                return result.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }

        public async Task<int> SetVerificationTokenAndSignatory(int trainingCertificateId, string verificationToken, int signatoryId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO, CertificateQB.SET_CERTIFICATE_VERIFICATION_TOKEN,
                    new { TrainingCertificateId = trainingCertificateId, VerificationToken = verificationToken, SignatoryId = signatoryId, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> SaveCertificateSignature(TrainingCertificateSignatureDTO dto, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO, CertificateQB.SAVE_CERTIFICATE_SIGNATURE, dto,
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> GetAttachmentIdByCertificate(int trainingCertificateId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<int>(
                    loginDTO, CertificateQB.GET_ATTACHMENTID_BY_CERTIFICATE,
                    new { TrainingCertificateId = trainingCertificateId, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
                int attachmentId = result.FirstOrDefault();
                if (attachmentId > 0)
                    return attachmentId;

                // No signature row (crypto-signing was skipped — e.g. no signing cert
                // provisioned yet) doesn't mean no PDF: the upload itself may still have
                // succeeded. Fall back to the latest TATTACHMENT row for this certificate.
                var fallback = await _QueryExecutor.QueryAsync<int>(
                    loginDTO, CertificateQB.GET_LATEST_ATTACHMENTID_BY_OBJECT,
                    new { TrainingCertificateId = trainingCertificateId, ObjectTypeId = GB5Shared.GB5Constant.Constant.EntityConstant.OBJECTTRAININGCERTIFICATE },
                    cancellationToken: ct).ConfigureAwait(false);
                return fallback.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }

        public async Task<AttachmentStorageInfoDTO?> GetAttachmentStorageInfo(int attachmentId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<AttachmentStorageInfoDTO>(
                    loginDTO, CertificateQB.GET_ATTACHMENT_STORAGE_INFO,
                    new { AttachmentId = attachmentId },
                    cancellationToken: ct).ConfigureAwait(false);
                return result.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }
    }
}