using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using MaintenanceDAL.DTO.Activity;
using MaintenanceDAL.Query.Activity;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System.Diagnostics;

namespace MaintenanceDAL.CustomCode.Activity
{
    public class ActivityDAL : IActivityDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly ILogger<ActivityDAL> _logger;
        private readonly IValidation _validation;

        public ActivityDAL(IQueryExecutor queryExecutor, ILogger<ActivityDAL> logger, IValidation validation)
        {
            _queryExecutor = queryExecutor;
            _logger = logger;
            _validation = validation;
        }

        public async Task<string> GetActivity(int activityId, LoginDTO loginDTO)
        {
            try
            {
                string sql = ActivityQB.GET_ACTIVITY;
                var parameters = new
                {
                    ActivityId = activityId
                };

                ActivityDTO ActivityDTO = await _queryExecutor.QuerySingleAsync<ActivityDTO>(loginDTO, sql, parameters);
                return JsonConvert.SerializeObject(ActivityDTO);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetActivity failed");
                throw;
            }
        }

        public async Task<string> GetSelectListActivity(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = ActivityQB.GetSelectListActivity;
                var result = await _queryExecutor.QueryAsync<ActivityPicklistDTO>(loginDTO, sql, null!);
                string json = JsonConvert.SerializeObject(result);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveActivity(ActivityDTO activityDTO, LoginDTO loginDTO)
        {
            try
            {
                const string sqldata = ActivityQB.SAVE_ACTIVITY;
                return await _queryExecutor.ExecuteAsync(loginDTO, sqldata, activityDTO);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "SaveActivity failed for ActivityCode {Code}", activityDTO.ActivityCode);
                // Parse the underlying SQL error into a readable message and log it to GB5_ExceptionLog,
                // so the real cause (bad column / NULL / constraint) surfaces instead of a generic wrapper.
                throw new Exception(await _validation.HandleException(ex, "Error While Saving the Activity"));
            }
        }

        public async Task<int> UpdateActivity(ActivityDTO activityDTO, LoginDTO loginDTO)
        {
            try
            {
                const string sqldata = ActivityQB.UPDATE_ACTIVITY;
                return await _queryExecutor.ExecuteAsync(loginDTO, sqldata, activityDTO);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "UpdateActivity failed for ActivityId {Id}", activityDTO.ActivityId);
                throw new Exception(await _validation.HandleException(ex, "Error While Updating the Activity"));
            }
        }

        public async Task<int> DeleteActivity(int activityId, LoginDTO loginDTO)
        {
            try
            {
                return await _queryExecutor.ExecuteAsync(loginDTO, ActivityQB.DELETE_ACTIVITY, new { ActivityId = activityId });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DeleteActivity failed for ActivityId {Id}", activityId);
                throw new Exception(await _validation.HandleException(ex, "Error While Deleting the Activity"));
            }
        }

    }
}