using Dapper;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using PartnerDAL.DTO.PartnerBrand;
using PartnerDAL.Query.PartnerBrand;

namespace PartnerDAL.CustomCode.PartnerBrand
{
    public class PartnerBrandDAL : IPartnerBrandDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _validation;
        private readonly IApplicationConnection _appConnection;

        public PartnerBrandDAL(IQueryExecutor queryExecutor, IValidation validation, IApplicationConnection appConnection)
        {
            _queryExecutor = queryExecutor;
            _validation = validation;
            _appConnection = appConnection;
        }

        public async Task<string> GetPartnerBrand(int PartnerBrandId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _queryExecutor.QuerySingleAsync<PartnerBrandDTO>(
                    loginDTO, PartnerBrandQB.GET_PARTNER_BRAND,
                    new { PartnerBrandId = PartnerBrandId }).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListPartnerBrand(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _queryExecutor.QueryWithCriteriaAsync<dynamic>(
                    loginDTO, PartnerBrandQB.GET_SELECTLIST_PARTNER_BRAND,
                    null, criteriaDTO, ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SavePartnerBrand(PartnerBrandDTO partnerBrandDTO, LoginDTO loginDTO)
        {
            try
            {
                return await _queryExecutor.ExecuteIdentityAsync(
                    loginDTO, PartnerBrandQB.SAVE_PARTNER_BRAND, partnerBrandDTO).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> UpdatePartnerBrand(PartnerBrandDTO partnerBrandDTO, LoginDTO loginDTO)
        {
            try
            {
                return await _queryExecutor.ExecuteAsync(
                    loginDTO, PartnerBrandQB.UPDATE_PARTNER_BRAND, partnerBrandDTO).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.UpdateErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> DeletePartnerBrand(int partnerBrandId, LoginDTO loginDTO)
        {
            try
            {
                return await _queryExecutor.ExecuteAsync(
                    loginDTO, PartnerBrandQB.DELETE_PARTNER_BRAND,
                    new { PartnerBrandId = partnerBrandId }).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.DeleteErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<string> GetPartnerBrandListByPartner(int partnerId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _queryExecutor.QueryAsync<PartnerBrandDTO>(
                    loginDTO, PartnerBrandQB.GET_PARTNER_BRAND_LIST_BY_PARTNER,
                    new { PartnerId = partnerId }).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetPartnerProductBrand(int partnerProductId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _queryExecutor.QuerySingleAsync<PartnerProductBrandDTO>(
                    loginDTO, PartnerBrandQB.GET_PARTNER_PRODUCT_BRAND,
                    new { PartnerProductId = partnerProductId }).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SavePartnerProductBrand(PartnerProductBrandDTO partnerProductBrandDTO, LoginDTO loginDTO)
        {
            try
            {
                return await _queryExecutor.ExecuteIdentityAsync(
                    loginDTO, PartnerBrandQB.SAVE_PARTNER_PRODUCT_BRAND, partnerProductBrandDTO).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> UpdatePartnerProductBrand(PartnerProductBrandDTO partnerProductBrandDTO, LoginDTO loginDTO)
        {
            try
            {
                return await _queryExecutor.ExecuteAsync(
                    loginDTO, PartnerBrandQB.UPDATE_PARTNER_PRODUCT_BRAND, partnerProductBrandDTO).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.UpdateErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // Two-hop: Hop 1 resolves PartnerProductId from GB5System (the only place
        // MCLIENTDETAILS lives). Hop 2 reuses the caller's own post-login LoginDTO —
        // already pointed at this client's own ProductionDB — to read the real
        // TPARTNERBRAND/MFILE data via GET_TENANT_LOCAL_PUBLIC_BRAND. No ConnectionName
        // re-lookup needed: LoginDTO already carries it.
        public async Task<string> GetResolvedBrandForClient(int clientId, string baseUri, LoginDTO loginDTO)
        {
            try
            {
                string systemConnStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                using var systemConn = new SqlConnection(systemConnStr);
                var routing = await systemConn.QueryFirstOrDefaultAsync(
                    PartnerBrandQB.GET_ROUTING_BY_CLIENT_ID,
                    new { ClientId = clientId }).ConfigureAwait(false);

                int partnerProductId = routing != null ? (int)routing.PartnerProductId : -1;
                if (partnerProductId <= 0)
                    return JsonConvert.SerializeObject(new { AppName = "GoodBooks", PrimaryColor = "#003399" });

                // dynamic, not PartnerBrandDTO — GET_TENANT_LOCAL_PUBLIC_BRAND's column
                // list (PartnerProductId, ProductName, PartnerCode, etc.) doesn't match
                // PartnerBrandDTO's shape (that DTO is for the admin CRUD queries above).
                // A fixed-DTO mapping here would silently drop the columns that don't
                // match and backfill the ones that do with meaningless C# defaults.
                var brand = await _queryExecutor.QuerySingleAsync<dynamic>(
                    loginDTO, PartnerBrandQB.GET_TENANT_LOCAL_PUBLIC_BRAND,
                    new { PartnerProductId = partnerProductId, BaseUri = baseUri ?? "" }).ConfigureAwait(false);
                return JsonConvert.SerializeObject(brand);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}