using FrameworkDAL.DTO.MessageHub;
using FrameworkDAL.DTO.MessageHub.MessageHubGenerator;
using FrameworkDAL.Query.MessageHub;
using FrameworkDAL.Query.RegionType;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace FrameworkDAL.CustomCode.MessageHub
{
    public class MessageHubDAL : IMessageHubDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        // 🔹 Get by Id
        public async Task<string> GetMessageHub(int MessageHubId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<string>(
                    LoginDTO,
                    MessageHubQB.GET_MESSAGEHUB,
                    new { MessageHubId }
                );
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, "Error while fetching MessageHub details.");
                throw new Exception(error);
            }
        }

        // 🔹 Insert
        public async Task<string> SaveMessageHub(MessageHubOldDTO dto, LoginDTO LoginDTO)
        {
            try
            {
                // ✅ Ensure defaults for all required SQL columns
                var param = new
                {
                    dto.Mode,
                    dto.Recipient,
                    dto.MessageText,
                    dto.AuthToken,
                    dto.PhoneNumberId,
                    dto.WebhookUrl,
                    SortOrder = dto.SortOrder == 0 ? (short)9999 : dto.SortOrder,
                    Status = dto.Status == 0 ? (byte)1 : dto.Status,   // default 1
                    Version = dto.Version == 0 ? (short)1 : dto.Version,
                    SourceType = dto.SourceType == 0 ? (byte)0 : dto.SourceType,
                    CreatedById = LoginDTO.UserId,
                    ModifiedById = LoginDTO.UserId,
                    TenantId = -1 // hardcoded
                };

                var result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    MessageHubQB.INSERT_MESSAGEHUB,
                    param
                );

                return result > 0
                    ? "MessageHub record inserted successfully."
                    : "Insert failed.";
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, "Error while saving MessageHub record.");
                throw new Exception(error);
            }
        }


        // 🔹 Update
        public async Task<string> UpdateMessageHub(MessageHubOldDTO dto, LoginDTO LoginDTO)
        {
            try
            {
                var param = new
                {
                    dto.MessageHubId,
                    dto.Mode,
                    dto.Recipient,
                    dto.MessageText,
                    dto.AuthToken,
                    dto.PhoneNumberId,
                    dto.WebhookUrl,

                    ModifiedById = LoginDTO.UserId,
                    TenantId = -1 // ✅ HARDCODED
                };

                var result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    MessageHubQB.UPDATE_MESSAGEHUB,
                    param
                );

                return result > 0
                    ? "MessageHub record updated successfully."
                    : "No record found to update.";
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, "Error while updating MessageHub record.");
                throw new Exception(error);
            }
        }

        // 🔹 Delete
        public async Task<string> DeleteMessageHub(int MessageHubId, LoginDTO LoginDTO)
        {
            try
            {
                var result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    MessageHubQB.DELETE_MESSAGEHUB,
                    new
                    {
                        MessageHubId,
                        TenantId = -1 // ✅ HARDCODED
                    }
                );

                return result > 0
                    ? "MessageHub record deleted successfully."
                    : "No record found to delete.";
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, "Error while deleting MessageHub record.");
                throw new Exception(error);
            }
        }


        public async Task<string> GetSelectListMessageHub(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = MessageHubQB.GET_SELECTLIST_MESSAGEHUB;
                var Result = await _QueryExecutor.QueryAsync<MessageHubPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetMessageHubTemplate(int MailTemplateId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = RegionTypeQB.GET_REGIONTYPE;
                var Parameters = new { mailtemplateid = MailTemplateId };
                MMailTemplateDTO MMailTemplateDTO = await _QueryExecutor.QuerySingleAsync<MMailTemplateDTO>(LoginDTO, Sql, Parameters);
                return JsonConvert.SerializeObject(MMailTemplateDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}