using Dapper;
using GB5Shared.Connection;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Microsoft.Data.SqlClient;
using PartnerDAL.DTO.ClientDetail;
using PartnerDAL.Query.ClientDetail;
using System.Linq;

namespace PartnerDAL.CustomCode.ClientDetail
{
    // MCLIENTDETAILS lives in GB5System — same reasoning as PartnerApiKeyDAL/ClientDomainDAL:
    // routing decisions must resolve before any tenant/ConnectionName context exists, so this
    // reads and writes GB5System directly via Gb5SystemConnectionString(), never a LoginDTO.
    // No LoginDTO parameter here at all — unlike ClientDomainDAL, this DAL is only ever called
    // from endpoints that don't need a tenant-routed session (admin assigns by ClientId directly).
    public class ClientDetailDAL : IClientDetailDAL
    {
        private readonly IValidation _validation;
        private readonly IApplicationConnection _appConnection;

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

        public async Task<IEnumerable<ClientDetailDTO>> GetClientDetailList(int clientId)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.QueryAsync<ClientDetailDTO>(
                    ClientDetailQB.GET_CLIENT_DETAIL_LIST,
                    new { ClientId = clientId }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<ClientDetailDTO?> GetClientDetailBySite(int clientId, int clientSiteId)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                var result = await conn.QueryAsync<ClientDetailDTO>(
                    ClientDetailQB.GET_CLIENT_DETAIL_BY_SITE,
                    new { ClientId = clientId, ClientSiteId = clientSiteId }).ConfigureAwait(false);
                return result.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }

        public async Task<int> AssignPartnerProduct(int clientId, int clientSiteId, int partnerProductId)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteAsync(
                    ClientDetailQB.ASSIGN_PARTNER_PRODUCT,
                    new { ClientId = clientId, ClientSiteId = clientSiteId, PartnerProductId = partnerProductId }).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> CreateClientDetail(int clientId, int clientSiteId)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteScalarAsync<int>(
                    ClientDetailQB.CREATE_CLIENT_DETAIL,
                    new { ClientId = clientId, ClientSiteId = clientSiteId }).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // Registers a new ConnectionName against an EXISTING physical database. Wrapped in
        // its own transaction because this does several dependent reads (uniqueness check,
        // next-safe-id, existing-database lookup) before the actual insert — all must see a
        // consistent snapshot, and nothing here should partially apply.
        public async Task<int> RegisterConnection(RegisterConnectionDTO dto, int createdById)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                await conn.OpenAsync().ConfigureAwait(false);
                await using var txn = await conn.BeginTransactionAsync().ConfigureAwait(false);

                var exists = await conn.ExecuteScalarAsync<int>(
                    ClientDetailQB.CONNECTION_NAME_EXISTS,
                    new { dto.ConnectionName }, txn).ConfigureAwait(false);
                if (exists > 0)
                    throw new InvalidOperationException($"ConnectionName '{dto.ConnectionName}' already exists.");

                var existingConnection = (await conn.QueryAsync<ExistingConnectionInfo>(
                    ClientDetailQB.DATABASE_NAME_IN_USE,
                    new { dto.DatabaseName }, txn).ConfigureAwait(false)).FirstOrDefault();
                if (existingConnection == null)
                    throw new InvalidOperationException(
                        $"DatabaseName '{dto.DatabaseName}' is not used by any existing active connection — " +
                        "RegisterConnection only attaches a new name to an already-provisioned database.");

                // Self-heal onto the same SERVICESERVERID as the existing sibling connection —
                // never caller-supplied, same as ServerId above. Verified active first so a
                // corrupted sibling row can't silently propagate a bad assignment forward.
                var serviceServerIsActive = await conn.ExecuteScalarAsync<int>(
                    ClientDetailQB.SERVICE_SERVER_IS_ACTIVE,
                    new { existingConnection.ServiceServerId }, txn).ConfigureAwait(false);
                if (existingConnection.ServiceServerId == null || serviceServerIsActive == 0)
                    throw new InvalidOperationException(
                        $"DatabaseName '{dto.DatabaseName}' has no active service-server assignment on its " +
                        "existing connection(s) — fix that connection's SERVICESERVERID before registering a new name against it.");

                var newServerConfigId = await conn.ExecuteScalarAsync<int>(
                    ClientDetailQB.GET_NEXT_SERVER_CONFIG_ID, transaction: txn).ConfigureAwait(false);

                await conn.ExecuteAsync(ClientDetailQB.CREATE_SERVER_CONFIG, new
                {
                    ServerConfigId = newServerConfigId,
                    dto.ConnectionName,
                    existingConnection.ServerId,
                    dto.DatabaseName,
                    dto.ClientId,
                    dto.ClientSiteId,
                    CreatedById = createdById,
                    ModifiedById = createdById,
                    existingConnection.ServiceServerId
                }, txn).ConfigureAwait(false);

                await txn.CommitAsync().ConfigureAwait(false);
                return newServerConfigId;
            }
            catch (InvalidOperationException) { throw; }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // DAL-internal projection for ClientDetailQB.DATABASE_NAME_IN_USE — never crosses
        // into BLL/SL, exists only to carry both columns out of one query.
        private sealed class ExistingConnectionInfo
        {
            public int ServerId { get; set; }
            public int? ServiceServerId { get; set; }
        }
    }
}