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.Provisioning;
using SwDAL.CustomCode.ClientDatabase;
using SwDAL.CustomCode.DbServer;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using static GB5Shared.GB5Constant.Constant;

namespace SwBLL.ClientDatabase;

public class ClientDatabaseBLL : IClientDatabaseBLL
{
    private readonly IClientDatabaseDAL                                               _ClientDatabaseDAL;
    private readonly AutoNumber                                                       _AutoNumber;
    private readonly IQueryExecutor                                                   _QueryExecutor;
    private readonly KeyInvalidate                                                    _KeyInvalidate;
    private readonly IListHandler<ClientDatabaseListQuery, ClientDatabaseListDTO>     _ListHandler;
    private readonly BaseEntityAppService<ClientDatabaseDTO>                          _BaseEntityAppService;
    private readonly ITargetDbExecutor _TargetDbExecutor;
    private readonly IDbServerDAL _DbServerDAL;
    private readonly IConfiguration _Configuration;

    public object KeyGenerator { get; private set; }

    public ClientDatabaseBLL(
        IClientDatabaseDAL clientDatabaseDAL,
        AutoNumber autoNumber,
        IQueryExecutor queryExecutor,
        KeyInvalidate keyInvalidate,
        IListHandler<ClientDatabaseListQuery, ClientDatabaseListDTO> listHandler,
        BaseEntityAppService<ClientDatabaseDTO> baseEntityAppService,
        ITargetDbExecutor targetDbExecutor,
        IDbServerDAL dbServerDAL,
        IConfiguration configuration)
    {
        _ClientDatabaseDAL    = clientDatabaseDAL;
        _AutoNumber           = autoNumber;
        _QueryExecutor        = queryExecutor;
        _KeyInvalidate        = keyInvalidate;
        _ListHandler          = listHandler;
        _BaseEntityAppService = baseEntityAppService;
        _TargetDbExecutor = targetDbExecutor;
        _DbServerDAL = dbServerDAL;
        _Configuration = configuration;
    }

    // ── Read — typed pass-through ─────────────────────────────────────────

    public async Task<ClientDatabaseDTO?> GetById(int clientDbId, LoginDTO loginDTO, CancellationToken ct)
        => await _ClientDatabaseDAL.GetById(clientDbId, loginDTO, ct).ConfigureAwait(false);

    public async Task<ClientDatabaseDTO?> GetByServerAndDatabaseName(int dbServerId, string databaseName, LoginDTO loginDTO, CancellationToken ct)
        => await _ClientDatabaseDAL.GetByServerAndDatabaseName(dbServerId, databaseName, 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<ClientDatabaseListCriteria>(merged);
        var result = await _ListHandler
            .HandleAsync(new ClientDatabaseListQuery(c), loginDTO, ct)
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

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


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

    private string GetEncryptionKey()
    {
        return _Configuration["Encryption:DbPasswordKey"]
            ?? throw new InvalidOperationException(
                "DB password encryption key is not configured.");
    }

    // ============================================================
    // DECRYPT DB SERVER PASSWORD
    // ============================================================

    private string GetDecryptedDbServerPassword(DbServerDTO server)
    {
        if (string.IsNullOrWhiteSpace(server.DbPassword))
        {
            throw new InvalidOperationException($"DB password is not configured for DB server '{server.DbServerId}'.");
        }

        string encryptionKey = GetEncryptionKey();

        try
        {
            return PasswordEncryption.Decrypt(
                server.DbPassword,
                encryptionKey);
        }
        catch (Exception ex)
        {
            throw new InvalidOperationException(
                $"Unable to decrypt DB password for DB server '{server.DbServerId}'.",
                ex);
        }
    }

    // --------------------------SAVE / UPDATE--------------------------------------------------------

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

        if (string.IsNullOrWhiteSpace(dto.DatabaseName))
        {
            throw new InvalidOperationException("Database name is required.");
        }

        dto.TenantId = loginDTO.ClientId;

        // 1. GET DB SERVER WITH ENCRYPTED CREDENTIALS

        var server = await _DbServerDAL.GetByIdWithCredentials(dto.DbServerId,loginDTO,ct).ConfigureAwait(false);

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

        // 2. RESOLVE USERNAME

        string dbUsername =!string.IsNullOrWhiteSpace(dto.DBUserName)? dto.DBUserName: server.DbUsername;

        if (string.IsNullOrWhiteSpace(dbUsername))
        {
            throw new InvalidOperationException("Database username is not configured in Client Database or DB Server.");
        }

        // 3. RESOLVE PASSWORD

        string dbPassword;

        if (!string.IsNullOrWhiteSpace(dto.DBPassword))
        {
            // ClientDatabase-specific password.
            dbPassword = dto.DBPassword;
        }
        else
        {
            // DBServer password is encrypted in MSWDBSERVER.
            // Decrypt it before creating SQL connection.
            dbPassword = GetDecryptedDbServerPassword(server);
        }

        if (string.IsNullOrWhiteSpace(dbPassword))
        {
            throw new InvalidOperationException("Database password is not configured in Client Database or DB Server.");
        }

        // 4. BUILD ADMIN CONNECTION

        var adminConnectionString =_TargetDbExecutor.BuildConnectionString(server,"master",dbUsername,dbPassword);

        // 5. VERIFY DATABASE EXISTS ON THIS SERVER — skipped for a Pending registration
        // (ClientDbStatus = 0). A Pending row is exactly the "register the intent now, create
        // the physical database later via the ClientProvisioning ChangeRequest's own
        // CreateFromScriptsAsync/CreateFromSnapshotAsync" flow (Entitlement's onboarding
        // orchestrator, tracker §31.11) — the database is EXPECTED not to exist yet at this
        // point, so requiring it here made every brand-new client onboarding fail outright.
        // This check still applies to any other status (e.g. registering an already-existing,
        // Active database directly), which is the scenario it was originally written for.
        if (dto.ClientDbStatus != 0)
        {
            bool databaseExists = await _TargetDbExecutor.DatabaseExistsAsync(adminConnectionString, dto.DatabaseName, ct).ConfigureAwait(false);

            if (!databaseExists)
            {
                throw new InvalidOperationException(
                    $"Database '{dto.DatabaseName}' does not exist on DB server '{server.HostName}'.");
            }
        }

        // 6. BEGIN LOCAL TRANSACTION

        var transaction =
            await _QueryExecutor
                .BeginTransactionAsync(loginDTO);

        try
        {
            bool isNew = dto.ClientDbId == 0;

            // 7. GENERATE CLIENT DATABASE ID

            if (isNew)
            {
                // Was AUTONUMBERCONSTANT.SWDBMODEL — the same copy-paste-wrong-constant bug
                // class already found and fixed 7 times elsewhere in this codebase (tracker
                // §31.10.1). Allocating a new ClientDbId from the DbModel counter risks
                // colliding with a real future DbModel ID; SWCLIENTDATABASE is this entity's
                // own dedicated counter.
                var auto =
                    await _AutoNumber.GetNumberAsync(
                        1,
                        AUTONUMBERCONSTANT.SWCLIENTDATABASE,
                        loginDTO,
                        transaction);

                dto.ClientDbId = auto.StartNumber;
            }

            // 8. SAVE CLIENT DATABASE

            await _BaseEntityAppService.ExecuteSaveAsync(EntityConstant.OBJECTSWCLIENTDATABASE,EventTypeConstant.SAVECLIENTDATABASEEVENTTYPEID,dto,
                loginDTO,
                async tx =>
                {
                    if (isNew)
                    {
                        await _ClientDatabaseDAL.SaveClientDatabase(dto,loginDTO,tx,ct).ConfigureAwait(false);
                    }
                    else
                    {
                        await _ClientDatabaseDAL.UpdateClientDatabase(dto,loginDTO,tx,ct).ConfigureAwait(false);
                    }

                    return dto.ClientDbId;
                },
                null,
                -1,
                -1,
                transaction);

            await _QueryExecutor.CommitAsync(transaction);

            var keyGen = new CacheKeyGeneration();

            var cacheKey =keyGen.KeyGeneration(dto.ClientDbId,
                    EntityConstant.OBJECTSWCLIENTDATABASE,
                    CacheKeyLevel.CLIENT_LEVEL,
                    loginDTO);

            await _KeyInvalidate.AllInvalidateCache(cacheKey);

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

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

    public async Task<string> Delete(int clientDbId, LoginDTO loginDTO, CancellationToken ct)
    {
        var existing = await _ClientDatabaseDAL.GetById(clientDbId, loginDTO, ct).ConfigureAwait(false);
        if (existing?.ClientDbStatus == (byte)SwDAL.Enums.ClientDbStatus.Active)
            throw new InvalidOperationException("Cannot delete an active client database.");

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWCLIENTDATABASE,
                EventTypeConstant.DELETECLIENTDATABASEEVENTTYPEID,
                new ClientDatabaseDTO { ClientDbId = clientDbId, TenantId = loginDTO.ClientId },
                loginDTO,
                async tx =>
                {
                    await _ClientDatabaseDAL.DeleteClientDatabase(clientDbId, loginDTO, tx, ct);
                    return clientDbId; // ✅ return int, not Result<string>
                },
            null, -1, -1, Trans);
            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

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

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

    public async Task<string> SetDeliveryModeAsync(int clientDbId, byte deliveryMode, LoginDTO loginDTO, CancellationToken ct)
    {
        // Real GB5 ClientDbIds are large negative AutoNumber-allocated values (e.g. -1370001001),
        // never small positive ones — a "clientDbId <= 0" guard would reject every genuine
        // client database, the same live-caught bug class already fixed in
        // EntitlementBLL.ClientEntitlementSnapshotBLL.GetSnapshotAsync. Only reject the
        // genuinely-missing/default case.
        if (clientDbId == 0) throw new ArgumentException("ClientDbId is required.", nameof(clientDbId));
        if (deliveryMode > 1) throw new ArgumentException("DeliveryMode must be 0 (Push) or 1 (Offline).", nameof(deliveryMode));

        int rows = await _ClientDatabaseDAL.UpdateDeliveryMode(clientDbId, deliveryMode, loginDTO, ct).ConfigureAwait(false);
        return rows > 0
            ? GB5Shared.Resource.Response.SuccessResponse.UpdateSuccess
            : throw new InvalidOperationException($"ClientDatabase {clientDbId} not found.");
    }

    // ── UpdateStatus (used by ProvisioningBLL) ────────────────────────────

    public async Task<string> UpdateStatusAsync(int clientDbId, byte status, LoginDTO loginDTO, CancellationToken ct)
    {
        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWCLIENTDATABASE,
                EventTypeConstant.UPDATECLIENTDATABASEEVENTTYPEID,
                new ClientDatabaseDTO { ClientDbId = clientDbId, ClientDbStatus = status, TenantId = loginDTO.ClientId },
                loginDTO,
                async tx =>
                {
                    await _ClientDatabaseDAL.UpdateStatus(clientDbId, status, loginDTO, tx, ct);
                    return clientDbId; // ✅ return int
                },
                null, -1, -1, Trans);

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

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

            return $"{SuccessResponse.UpdateSuccessMessage} {clientDbId}";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── UpdateCredentialVaultPaths (used by ClientDatabaseProvisioner) ────

    public async Task<string> UpdateCredentialVaultPathsAsync(
        int clientDbId, string dbaVaultPath, string appVaultPath, string readOnlyVaultPath,
        LoginDTO loginDTO, CancellationToken ct)
    {
        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _ClientDatabaseDAL.UpdateCredentialVaultPaths(
                clientDbId, dbaVaultPath, appVaultPath, readOnlyVaultPath, loginDTO, Trans, ct)
                .ConfigureAwait(false);

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

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

            return $"{SuccessResponse.UpdateSuccessMessage} {clientDbId}";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── UpdateServerConfigId / GetMainServerConfigIdForModel (used by ClientDatabaseProvisioner) ──

    public async Task<string> UpdateServerConfigIdAsync(int clientDbId, int serverConfigId, LoginDTO loginDTO, CancellationToken ct)
    {
        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _ClientDatabaseDAL.UpdateServerConfigId(clientDbId, serverConfigId, loginDTO, Trans, ct)
                .ConfigureAwait(false);

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

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

            return $"{SuccessResponse.UpdateSuccessMessage} {clientDbId}";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    public async Task<int?> GetMainServerConfigIdForModelAsync(int dbModelId, LoginDTO loginDTO, CancellationToken ct)
        => await _ClientDatabaseDAL.GetMainServerConfigIdForModel(dbModelId, loginDTO, ct).ConfigureAwait(false);

    //------------------------------------------------------------------------------------------------------------
    //HELPTER:


}