using FrameworkDAL.DTO.Address;
using FrameworkDAL.DTO.EIPConversation;
using FrameworkDAL.DTO.Party;
using FrameworkDAL.Query.Contact;
using FrameworkDAL.Query.EIPConversation;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.EIPConversation
{
    public class MessageHubConversationDAL : IConceptDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly ILogger<MessageHubConversationDAL> _logger;

        public MessageHubConversationDAL(IQueryExecutor queryExecutor, ILogger<MessageHubConversationDAL> logger)
        {
            _queryExecutor = queryExecutor ?? throw new ArgumentNullException(nameof(queryExecutor));
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        }

        #region Conversation – Read

        public async Task<MessageHubConversationDTO?> GetConversationSessionAsync(LoginDTO login, string sessionToken, CancellationToken ct = default)
        {
            sessionToken ??= string.Empty;

            try
            {
                var session = await _queryExecutor.QuerySingleAsync<MessageHubConversationDTO>(
                    login,
                    EIPConceptQB.GET_CONVERSATION,
                    new
                    {
                        TenantId = -1,
                        ClientId = login?.ClientId ?? 0,
                        SessionId = sessionToken,
                    });

                _logger.LogInformation("[GetConversationSession] SessionToken={SessionToken} | Found={Found} | ClientId={ClientId}",
                    sessionToken, session != null, login?.ClientId ?? 0);

                return session;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[GetConversationSession] Failed | SessionToken={SessionToken} | ClientId={ClientId}", sessionToken, login?.ClientId ?? 0);
                return null;
            }
        }

        #endregion

        #region Conversation – Create

        public async Task<long> CreateConversationSessionAsync(LoginDTO login, MessageHubConversationDTO dto, CancellationToken ct = default)
        {
            if (login == null) throw new ArgumentNullException(nameof(login));
            dto ??= new MessageHubConversationDTO();
            dto.SessionId ??= Guid.NewGuid().ToString();
            var now = DateTime.UtcNow;

            try
            {
                // Check for existing session
                var existingConversationId = await _queryExecutor.ExecuteScalarAsync<long>(
                    login,
                    @"SELECT TOP 1 CONVERSATIONID 
                      FROM TEIPCONVERSATION 
                      WHERE SESSIONTOKEN = @SessionId AND CLIENTID = @ClientId",
                    new { SessionId = dto.SessionId, ClientId = login.ClientId });

                if (existingConversationId > 0)
                {
                    _logger.LogInformation("[CreateConversationSession] Existing session | SessionToken={SessionToken} | ConversationId={ConversationId}",
                        dto.SessionId, existingConversationId);
                    return existingConversationId;
                }

                // Insert new session
                var conversationId = await _queryExecutor.ExecuteScalarAsync<long>(
                    login,
                    EIPConceptQB.INSERT_CONVERSATION,
                    new
                    {
                        TenantId = -1,
                        ClientId = login.ClientId,
                        PlatformTypeId = dto.PlatformTypeId,
                        SessionId = dto.SessionId,
                        Status = dto.StatusId,
                        CreatedById = login.UserId,
                        CreatedOn = now,
                        ModifiedById = login.UserId,
                        ModifiedOn = now,
                        LastActivityOn = now
                    });

                _logger.LogInformation("[CreateConversationSession] New session created | SessionToken={SessionToken} | ConversationId={ConversationId}",
                    dto.SessionId, conversationId);

                return conversationId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[CreateConversationSession] Failed | SessionToken={SessionToken}", dto.SessionId);
                throw;
            }
        }

        #endregion

        #region Conversation – Update

        public async Task UpdateConversationStatusAsync(LoginDTO login, string sessionToken, int status, CancellationToken ct = default)
        {
            sessionToken ??= string.Empty;
            var now = DateTime.UtcNow;

            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    EIPConceptQB.UPDATE_STATUS_AND_ACTIVITY,
                    new
                    {
                        TenantId = -1,
                        ClientId = login.ClientId,
                        SessionId = sessionToken,
                        Status = status,
                        LastActivityOn = now,
                        ModifiedById = login.UserId,
                        ModifiedOn = now,
                    });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[UpdateConversationStatus] Failed | SessionToken={SessionToken}", sessionToken);
            }
        }

        public async Task UpdateConversationLastActivityAsync(LoginDTO login, string sessionToken, CancellationToken ct = default)
        {
            sessionToken ??= string.Empty;
            var now = DateTime.UtcNow;

            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    EIPConceptQB.UPDATE_LAST_ACTIVITY_ONLY,
                    new
                    {
                        TenantId = -1,
                        ClientId = login.ClientId,
                        SessionId = sessionToken,
                        LastActivityOn = now,
                        ModifiedById = login.UserId,
                        ModifiedOn = now,
                    });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[UpdateConversationLastActivity] Failed | SessionToken={SessionToken}", sessionToken);
            }
        }

        public async Task CloseConversationSessionAsync(LoginDTO login, string sessionToken, CancellationToken ct = default)
        {
            sessionToken ??= string.Empty;
            var now = DateTime.UtcNow;

            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    EIPConceptQB.CLOSE_CONVERSATION,
                    new
                    {
                        TenantId = -1,
                        ClientId = login.ClientId,
                        SessionId = sessionToken,
                        ModifiedById = login.UserId,
                        ModifiedOn = now,
                    });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[CloseConversationSession] Failed | SessionToken={SessionToken}", sessionToken);
            }
        }

        #endregion

        #region Conversation Detail – Messages

        public async Task<long> AddConversationMessageAsync(LoginDTO login, long conversationId, byte messageDirection, string messageContent, string? providerMessageId, CancellationToken ct = default)
        {
            messageContent ??= string.Empty;
            if (messageDirection != 0 && messageDirection != 1) messageDirection = 0;
            var now = DateTime.UtcNow;

            try
            {
                var detailId = await _queryExecutor.ExecuteScalarAsync<long>(
                    login,
                    EIPConceptQB.INSERT_CONVERSATION_DETAIL,
                    new
                    {
                        TenantId = -1,
                        ConversationId = conversationId,
                        Direction = messageDirection,
                        Message = messageContent,
                        ProviderMessageId = providerMessageId,
                        Status = 1,
                        CreatedById = login.UserId,
                        CreatedOn = now,
                        ModifiedById = login.UserId,
                        ModifiedOn = now
                    });

                return detailId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[AddConversationMessage] Failed | ConversationId={ConversationId}", conversationId);
                throw;
            }
        }

        #endregion

        #region OTP – Verification

        public async Task<long> AddConversationOtpAsync(LoginDTO login, long conversationId, string otp, DateTime expiresOn, CancellationToken ct = default)
        {
            var now = DateTime.UtcNow;

            try
            {
                var otpId = await _queryExecutor.ExecuteScalarAsync<long>(
                    login,
                    EIPConceptQB.INSERT_CONVERSATION_OTP,
                    new
                    {
                        TenantId = -1,
                        ConversationId = conversationId,
                        OTP = otp,
                        ExpiresOn = expiresOn,
                        Status = 0,
                        CreatedById = login.UserId,
                        CreatedOn = now,
                        ModifiedById = login.UserId,
                        ModifiedOn = now
                    });

                return otpId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[AddConversationOtp] Failed | ConversationId={ConversationId}", conversationId);
                throw;
            }
        }

        #endregion

        #region Support Request

        public async Task SaveSupportRequestAsync(LoginDTO login, SupportRequestDTO dto, CancellationToken ct = default)
        {
            dto ??= new SupportRequestDTO();
            dto.SessionId ??= Guid.NewGuid().ToString();
            var now = DateTime.UtcNow;

            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    EIPConceptQB.INSERT_SUPPORT_REQUEST,
                    new
                    {
                        TenantId = -1,
                        ClientId = login.ClientId,
                        SessionId = dto.SessionId,
                        PhoneNumber = dto.PhoneNumber ?? string.Empty,
                        Remarks = dto.Remarks ?? string.Empty,
                        CreatedById = login.UserId,
                        CreatedOn = now,
                        ModifiedById = login.UserId,
                        ModifiedOn = now
                    });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[SaveSupportRequest] Failed | SessionToken={SessionToken}", dto.SessionId);
            }
        }

        #endregion

        #region Restricted Operations

        public Task DeleteConversationSessionAsync(string sessionToken, CancellationToken ct = default)
        {
            _logger.LogWarning("[DeleteConversationSession] Operation not allowed | SessionToken={SessionToken}", sessionToken);
            return Task.CompletedTask;
        }

        public Task ClearAllConversationsAsync(CancellationToken ct = default)
        {
            _logger.LogWarning("[ClearAllConversations] Operation not allowed");
            return Task.CompletedTask;
        }

        #endregion

        public async Task<ContactDTO?> GetContact(
LoginDTO login,
string mobileNo,
CancellationToken ct = default)
        {
            mobileNo ??= string.Empty;

            try
            {
                var contact = await _queryExecutor.QuerySingleAsync<ContactDTO>(
                    login,
                    EIPConceptQB.GET_CONTACT_BY_MOBILE,
                    new
                    {
                        TenantId = -1,
                        ClientId = login?.ClientId ?? 0,
                        MobileNo = mobileNo
                    });

                _logger.LogInformation(
                    "[GetContact] MobileNo={MobileNo} | Found={Found} | ClientId={ClientId}",
                    mobileNo,
                    contact != null,
                    login?.ClientId ?? 0);

                return contact;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex,
                    "[GetContact] Failed | MobileNo={MobileNo} | ClientId={ClientId}",
                    mobileNo,
                    login?.ClientId ?? 0);

                return null;
            }
        }




        public async Task<int> SaveContact(
    LoginDTO login,
    ContactDTO dto,
    CancellationToken ct = default)
        {
            dto ??= new ContactDTO();
            var now = DateTime.UtcNow;

            // 🔹 Normalize Mobile Number (Client sends 10 digits only)
            var cleanNumber = NormalizeToTenDigits(dto.ContactMobileNo);

            if (string.IsNullOrWhiteSpace(cleanNumber) || cleanNumber.Length != 10)
                throw new Exception("Mobile number must contain exactly 10 digits.");

            // Phone → 10 digits
            dto.ContactPhoneNo = cleanNumber;

            // Mobile → 91 + 10 digits
            dto.ContactMobileNo = AddDefaultCountryCode(cleanNumber);

            // 🔹 Auto split name
            SplitName(dto.ContactName, dto);

            dto.ContactCreatedById = login?.UserId ?? 0;
            dto.ContactCreatedOn = now;
            dto.ContactModifiedById = login?.UserId ?? 0;
            dto.ContactModifiedOn = now;
            dto.ContactStatus = 1;
            dto.ContactSourceType = 1;

            try
            {
                var contactId = await _queryExecutor.ExecuteScalarAsync<int>(
                    login,
                    EIPConceptQB.INSERT_CONTACT,
                    new
                    {
                        TenantId = -1,
                        ClientId = login?.ClientId ?? 0,
                        Salutation = dto.ContactSalutation ?? "Mr",
                        Name = dto.ContactName,
                        FirstName = dto.ContactFirstName,
                        MiddleName = dto.ContactMiddleName,
                        LastName = dto.ContactLastName,
                        MobileNo = dto.ContactMobileNo,   // 919876543210
                        PhoneNo = dto.ContactPhoneNo,     // 9876543210
                        MailId = dto.ContactMailId,
                        Dob = dto.ContactDob,
                        Status = dto.ContactStatus,
                        SourceType = dto.ContactSourceType,
                        CreatedById = dto.ContactCreatedById,
                        CreatedOn = dto.ContactCreatedOn,
                        ModifiedById = dto.ContactModifiedById,
                        ModifiedOn = dto.ContactModifiedOn
                    });

                _logger.LogInformation(
                    "[SaveContact] Success | ContactId={ContactId} | Mobile={MobileNo} | ClientId={ClientId}",
                    contactId,
                    dto.ContactMobileNo,
                    login?.ClientId ?? 0);

                return contactId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex,
                    "[SaveContact] Failed | Mobile={MobileNo} | ClientId={ClientId}",
                    dto.ContactMobileNo,
                    login?.ClientId ?? 0);

                throw;
            }
        }
        private string NormalizeToTenDigits(string number)
        {
            if (string.IsNullOrWhiteSpace(number))
                return string.Empty;

            var digits = new string(number.Where(char.IsDigit).ToArray());

            // If number contains country code or extra digits → take last 10
            if (digits.Length > 10)
                digits = digits[^10..];

            return digits;
        }

        private string AddDefaultCountryCode(string tenDigitNumber)
        {
            if (string.IsNullOrWhiteSpace(tenDigitNumber))
                return string.Empty;

            const string defaultCountryCode = "91";
            return defaultCountryCode + tenDigitNumber;
        }





        public async Task<List<PartyProductDTO>> GetPartyProductAsync(
    LoginDTO login,
    long partyId,
    CancellationToken ct = default)
        {
            try
            {
                var products = await _queryExecutor.QueryAsync<PartyProductDTO>(
                    login,
                    EIPConceptQB.GET_PARTY_PRODUCTS,
                    new
                    {
                        TenantId = -1,
                        ClientId = login?.ClientId ?? 0,
                        PartyId = partyId
                    });

                _logger.LogInformation(
                    "[GetPartyProduct] PartyId={PartyId} | Count={Count} | ClientId={ClientId} | Timestamp={Time}",
                    partyId,
                    products?.Count() ?? 0,
                    login?.ClientId ?? 0,
                    DateTime.UtcNow
                );


                return products?.ToList() ?? new List<PartyProductDTO>();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[GetPartyProduct] Failed | PartyId={PartyId} | ClientId={ClientId}",
                    partyId, login?.ClientId ?? 0);

                return new List<PartyProductDTO>();
            }
        }
        public async Task<int> SavePartyProduct(
            LoginDTO login,
            PartyProductDTO dto,
            CancellationToken ct = default)
        {
            dto ??= new PartyProductDTO();
            var now = DateTime.UtcNow;

            dto.CreatedById = login?.UserId ?? 0;
            dto.CreatedOn = now;

            // 🔹 Minimal mandatory fields
            dto.Quantity = dto.Quantity == 0 ? 1 : dto.Quantity;
            dto.Particulars = string.IsNullOrWhiteSpace(dto.Particulars) ? dto.ItemName : dto.Particulars;

            try
            {
                // 🔹 Convert ExtraFields dictionary to dynamic parameters for QueryExecutor
                var parameters = new Dictionary<string, object>
    {
        { "TenantId", -1 },
        { "ClientId", login?.ClientId ?? 0 },
        { "PartyId", dto.PartyId },
        { "PartyBranchId", dto.PartyBranchId },
        { "ItemName", dto.ItemName },
        { "Quantity", dto.Quantity },
        { "Particulars", dto.Particulars },
        { "CreatedById", dto.CreatedById },
        { "CreatedOn", dto.CreatedOn }
    };

                // Add any extra fields dynamically
                foreach (var kvp in dto.ExtraFields)
                {
                    parameters[kvp.Key] = kvp.Value;
                }

                // Execute Insert and get PartyProductId
                var partyProductId = await _queryExecutor.ExecuteScalarAsync<int>(
                    login,
                    EIPConceptQB.INSERT_PARTYPRODUCT, // Your generic insert query
                    parameters
                );

                _logger.LogInformation(
                    "[SavePartyProduct] PartyProductId={PartyProductId} | ItemName={ItemName} | ClientId={ClientId}",
                    partyProductId,
                    dto.ItemName,
                    login?.ClientId ?? 0
                );

                return partyProductId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex,
                    "[SavePartyProduct] Failed | ItemName={ItemName} | ClientId={ClientId}",
                    dto.ItemName,
                    login?.ClientId ?? 0
                );
                throw;
            }
        }

        private void SplitName(string fullName, ContactDTO dto)
        {
            if (string.IsNullOrWhiteSpace(fullName))
                return;

            var parts = fullName.Trim()
                                .Split(' ', StringSplitOptions.RemoveEmptyEntries);

            dto.ContactName = fullName.Trim();

            if (parts.Length == 1)
            {
                dto.ContactFirstName = parts[0];
                dto.ContactMiddleName = "NONE";
                dto.ContactLastName = "NONE";
            }
            else if (parts.Length == 2)
            {
                dto.ContactFirstName = parts[0];
                dto.ContactMiddleName = "NONE";
                dto.ContactLastName = parts[1];
            }
            else
            {
                dto.ContactFirstName = parts[0];
                dto.ContactLastName = parts[^1];
                dto.ContactMiddleName = string.Join(" ", parts.Skip(1).Take(parts.Length - 2));
            }
        }
    }
}