using Dapper;
using FrameworkDAL.CustomCode.Address;
using FrameworkDAL.DTO.Address;
using FrameworkDAL.Query.Contact;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Google.Apis.Json;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System.Data.Common;
using System.Text;
using System.Text.Json;


namespace FrameworkDAL.CustomCode.Contact
{
    public class ContactDAL : IContactDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IAddressDAL _AddressDAL;
        private readonly IValidation _Validation;
        private readonly ILogger<ContactDAL> _logger;


        public ContactDAL(IQueryExecutor QueryExecutor, IAddressDAL AddressDAL, IValidation IValidation, ILogger<ContactDAL> logger)
        {
            _QueryExecutor = QueryExecutor;
            _AddressDAL = AddressDAL;
            _Validation = IValidation;
            _logger = logger;
        }

        public async Task<ContactDTO> GetContact(int contactId, LoginDTO loginDTO)
        {
            try
            {
                var sql = ContactQB.GET_CONTACT;
                var parameters = new { ContactId = contactId };

                var contact = await _QueryExecutor.QuerySingleAsync<ContactDTO>(
                    loginDTO,
                    sql,
                    parameters
                );

                if (contact != null)
                {
                    contact.AddressDTOArray = await _AddressDAL.FindAddresses(
                        GB5Shared.GB5Constant.Constant.EntityConstant.CONTACT, contact.ContactId, loginDTO);
                }

                return contact!;
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve contact data: {ex.Message}", ex);
            }
        }


        public async Task<int> SaveContact(ContactDTO ContactDTO, LoginDTO LoginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string Sql = ContactQB.SAVE_CONTACT;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, ContactDTO, transaction);
            }
            catch (SqlException ex)
            {
                _logger.LogError(ex, "SaveContact SQL error Number={Number} Line={Line} Procedure={Procedure}", ex.Number, ex.LineNumber, ex.Procedure);
                throw;
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateContact(ContactDTO ContactDTO, LoginDTO LoginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string Sql = ContactQB.UPDATE_CONTACT;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, ContactDTO, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteContact(int ContactId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = ContactQB.DELETE_CONTACT;
                var Parameters = new { ContactId = ContactId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }
        public async Task<string> GetSelectListContact(int FirstNumber, int MaxResult,CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool isCount = false)
        {
            if (isCount)
            {
                var count = await _QueryExecutor.QuerySingleAsync<int>(
                    LoginDTO,
                    ContactQB.GET_CONTACT_COUNT,
                    new { });

                return count.ToString();
            }

            string mainQuery;
            int counter = 1;

            int objectTypeId = 0;
            int objectId = 0;

            if (CriteriaDTO != null)
            {
                foreach (var section in CriteriaDTO.SectionCriteriaList)
                {
                    foreach (var attr in section.AttributesCriteriaList.ToList())
                    {
                        if (attr.FieldName.Equals("counter", StringComparison.OrdinalIgnoreCase))
                        {
                            if (attr.FieldValue is JsonElement je)
                                counter = je.GetInt32();
                            else
                                counter = Convert.ToInt32(attr.FieldValue);
                        }
                        else if (attr.FieldName.Equals("ObjectTypeId", StringComparison.OrdinalIgnoreCase))
                        {
                            if (attr.FieldValue is JsonElement je1)
                                objectTypeId = je1.GetInt32();
                            else
                                objectTypeId = Convert.ToInt32(attr.FieldValue);
                        }
                        else if (attr.FieldName.Equals("ObjectId", StringComparison.OrdinalIgnoreCase))
                        {
                            if (attr.FieldValue is JsonElement je2)
                                objectId = je2.GetInt32();
                            else
                                objectId = Convert.ToInt32(attr.FieldValue);
                        }
                    }


                }
            }


            if (FirstNumber <= 0)
                FirstNumber = 1;

            if (MaxResult <= 0)
                MaxResult = 10;

            //-----------------
            if (objectTypeId != 0 && objectId != 0)
            {
                // GET_SELECTLIST_CONTACT_BY_OBJECT returns PartyCode/ContactId/EntityId-shaped
                // columns that map to ContactDTO, not ContactPicklistDTO — deserialize accordingly
                // and skip the picklist-only city enrichment below.
                var contactsByObject = (await _QueryExecutor.QueryAsync<ContactSelectListByObjectDTO>(
                    LoginDTO,
                    ContactQB.GET_SELECTLIST_CONTACT_BY_OBJECT,
                    new
                    {
                        FirstNumber,
                        MaxResult,
                        ObjectTypeId = objectTypeId,
                        ObjectId = objectId
                    })).ToList();

                return JsonConvert.SerializeObject(contactsByObject);
            }
            else if (counter == 1)
            {

                mainQuery = ContactQB.GET_SELECTLIST_CONTACT;
            }
            else
            {
                mainQuery = ContactQB.GET_CONTACT_SQL_ALLOCATION_EFL;
            }

            var contacts = (await _QueryExecutor.QueryAsync<ContactPicklistDTO>(
                LoginDTO,
                mainQuery,
                new { FirstNumber, MaxResult,
                    ObjectTypeId = objectTypeId,
                    ObjectId = objectId
                })).ToList();

            for (int i = 0; i < contacts.Count; i++)
            {
                var contact = contacts[i];

                var cityList = (await _QueryExecutor.QueryAsync<ContactPicklistDTO>(
                    LoginDTO,
                    ContactQB.GET_CONTACT_CITY,
                    new { contactId = contact.Id })).ToList();

                if (cityList.Count > 0)
                {
                    var city = cityList[0];
                    contact.CityId = city.CityId;
                    contact.CityCode = city.CityCode;
                    contact.CityName = city.CityName;
                }

                contacts[i] = contact; // update list
            }
            return JsonConvert.SerializeObject(contacts);
        }


public async Task<string> GetContactWithImage(int FirstNumber,int MaxResult,CriteriaDTO CriteriaDTO,LoginDTO LoginDTO)
        {
            try
            {
                int ContactId = -1;
                int Sex = -1;
                int Status = -1;
                string Search = "";

                if (CriteriaDTO != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (attr.FieldName.Equals("contact", StringComparison.OrdinalIgnoreCase))
                            {
                                ContactId = attr.FieldValue is JsonElement je1
                                    ? je1.GetInt32()
                                    : Convert.ToInt32(attr.FieldValue);
                            }
                            else if (attr.FieldName.Equals("sex", StringComparison.OrdinalIgnoreCase))
                            {
                                Sex = attr.FieldValue is JsonElement je2
                                    ? je2.GetInt32()
                                    : Convert.ToInt32(attr.FieldValue);
                            }
                            else if (attr.FieldName.Equals("status", StringComparison.OrdinalIgnoreCase))
                            {
                                Status = attr.FieldValue is JsonElement je3
                                    ? je3.GetInt32()
                                    : Convert.ToInt32(attr.FieldValue);
                            }
                            else if (attr.FieldName.Equals("search", StringComparison.OrdinalIgnoreCase))
                            {
                                Search = attr.FieldValue is JsonElement je4
                                    ? je4.GetString() ?? ""
                                    : Convert.ToString(attr.FieldValue) ?? "";
                            }
                        }
                    }
                }

                var Filter = new StringBuilder();
                var Parameters = new DynamicParameters();

                if (ContactId > 0)
                {
                    Filter.Append(" AND a.CONTACTID = @ContactId");
                    Parameters.Add("ContactId", ContactId);
                }

                if (Sex >= 0)
                {
                    Filter.Append(" AND a.SEX = @Sex");
                    Parameters.Add("Sex", Sex);
                }

                if (Status >= 0)
                {
                    Filter.Append(" AND a.STATUS = @Status");
                    Parameters.Add("Status", Status);
                }

                if (!string.IsNullOrWhiteSpace(Search))
                {
                    Filter.Append(@"
                AND (
                    a.NAME LIKE @Search OR
                    a.FIRSTNAME LIKE @Search OR
                    a.MIDDLENAME LIKE @Search OR
                    a.LASTNAME LIKE @Search OR
                    a.MAILID LIKE @Search OR
                    a.PHONENO LIKE @Search OR
                    a.MOBILENO LIKE @Search OR
                    a.DESIGNATION LIKE @Search OR
                    a.COMPANY LIKE @Search
                )");

                    Parameters.Add("Search", $"%{Search}%");
                }

                //// Default paging values
                //if (FirstNumber <= 0)
                //    FirstNumber = 1;

                //if (MaxResult <= 0)
                //    MaxResult = 100; // use a large value to get all records

                Parameters.Add("FirstNumber", FirstNumber);
                Parameters.Add("MaxResult", MaxResult);

                // Insert filters before ORDER BY
                string Sql = ContactQB.GET_CONTACT_FOR_IMAGE.Replace(
           "WHERE 1 = 1",
           "WHERE 1 = 1 " + Filter.ToString());

                var Contacts = (await _QueryExecutor.QueryAsync<ContactDTO>(
                    LoginDTO,
                    Sql,
                    Parameters)).ToList();

                foreach (var contact in Contacts)
                {
                    // List endpoint: addresses are intentionally not loaded per-row here (would be N+1).
                    // Callers needing addresses should use GetContact for the single record.
                    contact.AddressDTOArray = new List<AddressDTO>();

                    contact.ContactImageViewUrl =
                        int.TryParse(contact.ContactDefaultImageId, out int attachmentId) && attachmentId > 0
                            ? $"/File/Get/Attachment/{attachmentId}"
                            : "NONE";
                }

                return JsonConvert.SerializeObject(Contacts);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve contact data: {ex.Message}", ex);
            }
        }



    }
}