using AccountsDAL.DTO.WarehouseMeasure;
using AccountsDAL.Query.WarehouseMeasure;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace AccountsDAL.CustomCode.WarehouseMeasure
{
    public class WarehouseMeasureDAL : IWarehouseMeasureDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public WarehouseMeasureDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        public async Task<string> GetWarehouseMeasure(int MeasureId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = WarehouseMeasureQB.GET_WAREHOUSEMEASURE;
                var parameters = new { measureid = MeasureId };
                var result = await _QueryExecutor.QuerySingleAsync<WarehouseMeasureDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetWarehouseMeasureList(int? FactId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = WarehouseMeasureQB.GET_WAREHOUSEMEASURE_LIST;
                var parameters = new { FactId };
                var list = (await _QueryExecutor.QueryAsync<WarehouseMeasureDTO>(LoginDTO, sql, parameters)
                                                 .ConfigureAwait(false)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int?> GetExistingMeasureIdForColumn(int FactId, string ColumnName, LoginDTO LoginDTO)
        {
            try
            {
                string sql = WarehouseMeasureQB.GET_EXISTING_MEASURE_FOR_COLUMN;
                var parameters = new { FactId, ColumnName };
                var results = (await _QueryExecutor.QueryAsync<int>(LoginDTO, sql, parameters)
                                                    .ConfigureAwait(false)).ToList();
                return results.Count > 0 ? results[0] : (int?)null;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string?> GetFactTableNameForMeasureCheck(int FactId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = WarehouseMeasureQB.GET_FACT_TABLE_NAME_FOR_MEASURE_CHECK;
                var parameters = new { FactId };
                return await _QueryExecutor.ExecuteScalarAsync<string?>(LoginDTO, sql, parameters).ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> CheckColumnExistsOnTable(string TableName, string ColumnName, LoginDTO LoginDTO)
        {
            try
            {
                string sql = WarehouseMeasureQB.CHECK_COLUMN_EXISTS_ON_TABLE;
                var parameters = new { TableName, ColumnName };
                int count = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, parameters).ConfigureAwait(false);
                return count > 0;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveWarehouseMeasure(WarehouseMeasureDTO WarehouseMeasureDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string sql = WarehouseMeasureQB.SAVE_WAREHOUSEMEASURE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, WarehouseMeasureDTO);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateWarehouseMeasure(WarehouseMeasureDTO WarehouseMeasureDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string sql = WarehouseMeasureQB.UPDATE_WAREHOUSEMEASURE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, WarehouseMeasureDTO);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task<string> DeleteWarehouseMeasure(int MeasureId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = WarehouseMeasureQB.DELETE_WAREHOUSEMEASURE;
                var parameters = new { measureid = MeasureId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameters);

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