using RecruitmentDAL.DTO.ProfileGenerator;
using RecruitmentDAL.Query.ProfileGenerator;
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.ProfileGenerator
{
    public class ProfileGeneratorDAL : IProfileGeneratorDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetProfileGenerator(int ProfileGeneratorId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ProfileGeneratorQB.GET_PROFILEGENERATOR;

                var parameters = new { profileGeneratorId = ProfileGeneratorId };

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

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

        #endregion


        #region Select List

        public async Task<string> GetSelectListProfileGenerator(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ProfileGeneratorQB.GET_SELECTLIST_PROFILEGENERATOR;

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

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

        #endregion


        #region Save

        // Returns the real IDENTITY value the DB assigned to PROFILEGENERATORID.
        // SAVE_PROFILEGENERATOR's INSERT column list omits PROFILEGENERATORID entirely and the
        // SQL ends with SELECT SCOPE_IDENTITY() — ExecuteIdentityAsync runs it as a scalar query
        // and hands back the generated id. No AutoNumber involvement — the column is IDENTITY(1,1).
        public async Task<int> SaveProfileGenerator(
            ProfileGeneratorDTO ProfileGeneratorDTO, LoginDTO LoginDTO, DbTransaction transaction, CancellationToken ct)
        {
            try
            {
                const string sql = ProfileGeneratorQB.SAVE_PROFILEGENERATOR;

                // ExecuteIdentityAsync has no CancellationToken parameter (SQL must end with
                // SELECT SCOPE_IDENTITY(), executed via ExecuteScalarAsync) — see IQueryExecutor.
                return await _QueryExecutor
                    .ExecuteIdentityAsync(LoginDTO, sql, ProfileGeneratorDTO, transaction)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.SaveErrorMessage)
                    .ConfigureAwait(false);

                throw new Exception(error);
            }
        }

        #endregion


        #region Update

        public async Task<int> UpdateProfileGenerator(
            ProfileGeneratorDTO ProfileGeneratorDTO, LoginDTO LoginDTO, DbTransaction transaction, CancellationToken ct)
        {
            try
            {
                const string sql = ProfileGeneratorQB.UPDATE_PROFILEGENERATOR;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, ProfileGeneratorDTO, transaction, 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<int> DeleteProfileGenerator(
            int ProfileGeneratorId, LoginDTO LoginDTO, DbTransaction transaction, CancellationToken ct)
        {
            try
            {
                string sql = ProfileGeneratorQB.DELETE_PROFILEGENERATOR;

                var parameter = new { profileGeneratorId = ProfileGeneratorId };

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

                throw new Exception(error);
            }
        }

        #endregion
    }
}