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

namespace PartnerDAL.CustomCode.ClientDomain
{
    // TCLIENTDOMAIN and TPLATFORMSETTINGS live in GB5System ONLY — a domain/CNAME must be
    // resolvable before any tenant/ConnectionName is known (e.g. custom-domain routing at
    // boot), the same reason TPARTNERAPIKEY is centralized. Every method here reads and
    // writes GB5System directly via Gb5SystemConnectionString(), never via the caller's
    // LoginDTO/tenant connection. LoginDTO is kept on the interface only for call-site
    // consistency with the rest of the module — it is not used for routing here.
    public class ClientDomainDAL : IClientDomainDAL
    {
        private readonly IValidation _validation;
        private readonly IApplicationConnection _appConnection;

        public ClientDomainDAL(IValidation validation, IApplicationConnection appConnection)
        {
            _validation = validation;
            _appConnection = appConnection;
        }

        public async Task<string> GetClientDomains(int clientId, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                var result = await conn.QueryAsync<ClientDomainDTO>(
                    ClientDomainQB.GET_CLIENT_DOMAINS,
                    new { ClientId = clientId }).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception) { throw; }
        }

        public async Task<ClientDomainDTO?> GetClientDomainById(int domainId, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                var result = await conn.QueryAsync<ClientDomainDTO>(
                    ClientDomainQB.GET_CLIENT_DOMAIN_BY_ID,
                    new { DomainId = domainId }).ConfigureAwait(false);
                return result.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }

        public async Task<ClientDomainDTO?> GetDomainTenant(string domainName, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                var result = await conn.QueryAsync<ClientDomainDTO>(
                    ClientDomainQB.GET_DOMAIN_TENANT,
                    new { DomainName = domainName.ToLowerInvariant() }).ConfigureAwait(false);
                return result.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<ClientDomainDTO>> GetAllActiveDomains(LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.QueryAsync<ClientDomainDTO>(
                    ClientDomainQB.GET_ALL_ACTIVE_DOMAINS).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<int> SaveClientDomain(ClientDomainDTO dto, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                var newId = await conn.ExecuteScalarAsync<decimal>(
                    ClientDomainQB.SAVE_CLIENT_DOMAIN, dto).ConfigureAwait(false);
                return (int)newId;
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateClientDomain(ClientDomainDTO dto, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteAsync(
                    ClientDomainQB.UPDATE_CLIENT_DOMAIN, dto).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> DeleteClientDomain(int domainId, int modifiedById, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteAsync(
                    ClientDomainQB.DELETE_CLIENT_DOMAIN,
                    new { DomainId = domainId, ModifiedById = modifiedById }).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.DeleteErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> VerifyClientDomain(int domainId, int modifiedById, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteAsync(
                    ClientDomainQB.VERIFY_CLIENT_DOMAIN,
                    new { DomainId = domainId, ModifiedById = modifiedById }).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // ---------------------------------------------------------------
        // Platform-wide settings
        // ---------------------------------------------------------------

        public async Task<string?> GetPlatformSetting(string settingKey, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteScalarAsync<string>(
                    ClientDomainQB.GET_PLATFORM_SETTING,
                    new { SettingKey = settingKey }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<PlatformSettingDTO>> GetAllPlatformSettings(LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.QueryAsync<PlatformSettingDTO>(
                    ClientDomainQB.GET_ALL_PLATFORM_SETTINGS).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<int> SavePlatformSetting(string settingKey, string settingValue, int modifiedById, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteAsync(
                    ClientDomainQB.SAVE_PLATFORM_SETTING,
                    new { SettingKey = settingKey, SettingValue = settingValue, ModifiedById = modifiedById }).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }
    }
}