using FrameworkDAL.DTO.Address;
using FrameworkDAL.DTO.Menu;
using FrameworkDAL.DTO.Port;
using FrameworkDAL.DTO.TRModule;
using FrameworkDAL.Query.Address;
using FrameworkDAL.Query.TRModule;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Google.Apis.Json;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.Address
{
    public class AddressDAL : IAddressDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public AddressDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }

        public async Task<string> GetAddress(int AddressId,string LanguageId ,LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = AddressQB.GET_ADDRESS;
                var parameters = new { addressid = AddressId, languageid = LanguageId  };
                AddressDTO AddressDTOs = await _QueryExecutor.QuerySingleAsync<AddressDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(AddressDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<string> GetAddressDetail(int ObjectTypeId, int ObjectId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = AddressQB.GET_ADDRESS_DETAIL;
                var Parameters = new { objecttypeid = ObjectTypeId, objectid = ObjectId };
                AddressDTO AddressDTO = await _QueryExecutor.QuerySingleAsync<AddressDTO>(LoginDTO, Sql, Parameters);
                return JsonConvert.SerializeObject(AddressDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<AddressDTO?> FindAddress(int ObjectTypeId, int ObjectId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var parameters = new { objecttypeid = ObjectTypeId, objectid = ObjectId };
            var results = await _QueryExecutor.QueryAsync<AddressDTO>(loginDTO, AddressQB.GET_ADDRESS_DETAIL, parameters, transaction)
                .ConfigureAwait(false);
            return results.FirstOrDefault();
        }

        public async Task<List<AddressDTO>> FindAddresses(int ObjectTypeId, int ObjectId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            var parameters = new { objecttypeid = ObjectTypeId, objectid = ObjectId };
            var results = await _QueryExecutor.QueryAsync<AddressDTO>(loginDTO, AddressQB.GET_ADDRESS_DETAIL, parameters, transaction)
                .ConfigureAwait(false);
            return results.ToList();
        }

        public async Task<string> GetSelectListAddress(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = AddressQB.GET_SELECTLIST_ADDRESS;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    var Result = await _QueryExecutor.QueryAsync<AddressPicklistDTO>(LoginDTO, SQL, parameter);
                    Json = JsonConvert.SerializeObject(Result);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

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

        public async Task<int> SaveAddress(AddressDTO addressDTO, string LanguageId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string sql = AddressQB.SAVE_ADDRESS;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, addressDTO, transaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while saving Address", ex);
            }
        }

        public async Task<int> CountAddress(AddressDTO addressDTO, string LanguageId, LoginDTO loginDTO)
        {
            try
            {
                string sql = AddressQB.COUNT_OF_TRMADDRESS;
                var parameters = new
                {
                    addressid = addressDTO.AddressId,
                    languageid = LanguageId
                };
                return await _QueryExecutor.TotalCountSql(sql, loginDTO, parameters, true);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while counting Address", ex);
            }
        }

        public async Task<int> UpdateAddress(AddressDTO addressDTO, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string sql = AddressQB.UPDATE_ADDRESS;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, addressDTO, transaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while updating Address", ex);
            }
        }

        public async Task<Result<string>> DeleteAddress(int addressId, LoginDTO loginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>
            {
                (AddressQB.DELETE_ADDRESS, new { addressid = addressId })
            };

                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return Result<string>.Success("Address deleted successfully.");
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting Address.", ex);
            }
        }
    }
}