using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Qualifier;
using GB5Shared.EncryptionHelper;
using GB5Shared.EntityHandler;
using GB5Shared.GB5Library.Qualifier;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.ListQuery;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using SwBLL.ClientDatabase;
using SwBLL.Provisioning;
using SwDAL.CustomCode.DbServer;
using SwDAL.DTO.DbServer;
using SwDAL.Enums;
using SwDAL.Query.DbServer;
using SwSL.EndPoints.ConnectionValidator;
using static GB5Shared.GB5Constant.Constant;

namespace SwBLL.DbServer;

public class DbServerBLL : IDbServerBLL
{
    private readonly IDbServerDAL _DbServerDAL;
    private readonly IDbServerConnectionValidator _connectionValidator;
    private readonly AutoNumber _AutoNumber;
    private readonly IQueryExecutor _QueryExecutor;
    private readonly KeyInvalidate _KeyInvalidate;
    private readonly IListHandler<DbServerListQuery, DbServerListDTO> _ListHandler;
    private readonly BaseEntityAppService<DbServerDTO> _BaseEntityAppService;
    private readonly ITargetDbExecutor _TargetDbExecutor;
    private readonly IClientDatabaseBLL _ClientDatabaseBLL;
    private readonly IConfiguration _Configuration;


    public DbServerBLL(
        IDbServerDAL dbServerDAL,
        IDbServerConnectionValidator connectionValidator,
        AutoNumber autoNumber,
        IQueryExecutor queryExecutor,
        KeyInvalidate keyInvalidate,
        IListHandler<DbServerListQuery, DbServerListDTO> listHandler,
        BaseEntityAppService<DbServerDTO> baseEntityAppService,
        ITargetDbExecutor targetDbExecutor,
        IClientDatabaseBLL clientDatabaseBLL,
        IConfiguration configuration)
    {
        _DbServerDAL = dbServerDAL;
        _connectionValidator = connectionValidator;
        _AutoNumber = autoNumber;
        _QueryExecutor = queryExecutor;
        _KeyInvalidate = keyInvalidate;
        _ListHandler = listHandler;
        _BaseEntityAppService = baseEntityAppService;
        _TargetDbExecutor = targetDbExecutor;
        _ClientDatabaseBLL = clientDatabaseBLL;
        _Configuration = configuration;
    }

    // ============================================================
    // ENCRYPTION KEY
    // ============================================================

    private string GetEncryptionKey()
    {
        return _Configuration["Encryption:DbPasswordKey"]
            ?? throw new InvalidOperationException(
                "DB password encryption key is not configured.");
    }
    public async Task<DbServerDTO?> GetById(int dbServerId, LoginDTO loginDTO, CancellationToken ct)
        => await _DbServerDAL.GetById(dbServerId, loginDTO, ct).ConfigureAwait(false);

    public async Task<DbServerDTO?> GetByIdWithCredentials(int dbServerId, LoginDTO loginDTO, CancellationToken ct)
        => await _DbServerDAL.GetByIdWithCredentials(dbServerId, loginDTO, ct).ConfigureAwait(false);

    public async Task<string> GetList(
        CriteriaDTO criteria, string? searchText, int pageOffset, int pageSize,
        LoginDTO loginDTO, CancellationToken ct = default)
    {
        var merged = CriteriaRouterHelper.WithSearchAndPaging(criteria, searchText, pageOffset, pageSize);
        var c = CriteriaBinder.Bind<DbServerListCriteria>(merged);
        var result = await _ListHandler
            .HandleAsync(new DbServerListQuery(c), loginDTO, ct)
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<string> GetSelectListDbServer(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        => await _DbServerDAL.GetSelectListDbServer(firstNumber, maxResult, loginDTO)
               .ConfigureAwait(false);

    // ── Save (insert or update) ───────────────────────────────────────────

    public async Task<string> Save(DbServerDTO dto, LoginDTO loginDTO, CancellationToken ct)
    {
        if (dto == null) throw new ArgumentNullException(nameof(dto));

        dto.TenantId = loginDTO.ClientId;

        string encryptionKey = GetEncryptionKey();

        bool isNew = dto.DbServerId == 0;

        if (isNew)
        {
            if (string.IsNullOrWhiteSpace(dto.DbPassword))
            {
                throw new ArgumentException(
                    "DB password is required when creating a DB server.");
            }

            // Validate remote SQL Server
            await _connectionValidator.ValidateConnectionAsync(dto.HostName, dto.Port, dto.DbUsername, dto.DbPassword, ct);

            var auto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.SWDBSERVER, loginDTO);

            dto.DbServerId = auto.StartNumber;

            dto.DbPassword = PasswordEncryption.Encrypt(dto.DbPassword, encryptionKey);
        }
        else
        {
            var existing = await _DbServerDAL.GetByIdWithCredentials(dto.DbServerId, loginDTO, ct).ConfigureAwait(false);

            if (existing == null)
            {
                throw new InvalidOperationException($"DB server {dto.DbServerId} not found.");
            }

            bool passwordChanged = !string.IsNullOrWhiteSpace(dto.DbPassword);

            if (passwordChanged)
            {
                dto.DbPassword = PasswordEncryption.Encrypt(
                    dto.DbPassword,
                    encryptionKey);
            }
            else
            {
                dto.DbPassword = existing.DbPassword;
            }

            if (string.IsNullOrWhiteSpace(dto.DbUsername))
            {
                dto.DbUsername = existing.DbUsername;
            }
        }

        // Start transaction only after validation
        var trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);

        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(EntityConstant.OBJECTSWDBSERVER,
                isNew
                    ? EventTypeConstant.SAVEDBSERVEREVENTTYPEID
                    : EventTypeConstant.SAVEDBSERVEREVENTTYPEID,
                dto,
                loginDTO,
                async tx =>
                {
                    if (isNew)
                    {
                        await _DbServerDAL.SaveDbServer(dto, loginDTO, tx, ct);
                    }
                    else
                    {
                        await _DbServerDAL.UpdateDbServer(dto, loginDTO, tx, ct);
                    }

                    return dto.DbServerId;
                },
                null,
                -1,
                -1,
                trans);

            await _QueryExecutor.CommitAsync(trans);

            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                dto.DbServerId,
                EntityConstant.OBJECTSWDBSERVER,
                CacheKeyLevel.CLIENT_LEVEL,
                loginDTO);

            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return isNew
                ? $"{SuccessResponse.SaveSuccessMessage} {dto.DbServerId}"
                : $"{SuccessResponse.UpdateSuccessMessage} {dto.DbServerId}";
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(trans);
            throw;
        }
    }

    // ── GetServerObjects ─────────────────────────────────────────────────

    public async Task<IEnumerable<RemoteDbObjectDTO>> GetServerObjectsAsync(
        int dbServerId, string databaseName, LoginDTO loginDTO, CancellationToken ct)
    {
        var server = await _DbServerDAL.GetById(dbServerId, loginDTO, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"DB server {dbServerId} not found.");

        // Schema browsing is read-only by nature — route through the least-privilege _readonly
        // contained user when this DbServerId+DatabaseName pair is a registered ClientDatabase.
        // Databases browsed before provisioning (or system/unregistered databases) have no
        // contained users to route to, so they keep using the DbServer admin credential.
        var clientDb = await _ClientDatabaseBLL
            .GetByServerAndDatabaseName(dbServerId, databaseName, loginDTO, ct)
            .ConfigureAwait(false);

        string connStr = clientDb is not null
            ? await _TargetDbExecutor
                .BuildConnectionStringForRoleAsync(server, clientDb, ClientDbLoginRole.ReadOnly, ct)
                .ConfigureAwait(false)
            : _TargetDbExecutor.BuildConnectionString(server, databaseName);

        return await _TargetDbExecutor.GetServerObjectsAsync(connStr, ct).ConfigureAwait(false);
    }

    // ── Delete ────────────────────────────────────────────────────────────

    public async Task<string> Delete(int dbServerId, LoginDTO loginDTO, CancellationToken ct)
    {
        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWDBSERVER,
                EventTypeConstant.DELETEDBSERVEREVENTTYPEID,
                new DbServerDTO { DbServerId = dbServerId, TenantId = loginDTO.ClientId },
                loginDTO,
                async tx => await _DbServerDAL.DeleteDbServer(dbServerId, loginDTO, tx, ct),
                null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                dbServerId, EntityConstant.OBJECTSWDBSERVER,
                CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return SuccessResponse.DeleteSuccessMessage;
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    public async Task<string> GetListDBServer(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
          => await _DbServerDAL.GetListDBServer(criteriaDTO, login, ct);

    // GET UNIQUE SERVER HOSTNAMES

    public async Task<List<DbServerDTO>> GetServerHostNameList(LoginDTO loginDTO, CancellationToken ct)
    {
        return await _DbServerDAL.GetServerHostNameList(loginDTO, ct).ConfigureAwait(false);
    }


    // GET DATABASES BY HOSTNAME

    public async Task<List<DbServerDTO>> GetDatabaseListByHostName(string hostName, LoginDTO loginDTO, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(hostName))
        {
            throw new ArgumentException(
                "HostName is required.",
                nameof(hostName));
        }

        return await _DbServerDAL.GetDatabaseListByHostName(hostName, loginDTO, ct).ConfigureAwait(false);
    }
}