using GB5Shared.DTO.Framework.Login;
using Microsoft.Extensions.Logging;
using SwBLL.ClientDatabase;
using SwBLL.DbServer;
using SwBLL.Provisioning;
using SwBLL.UserResourceRole;
using SwDAL.CustomCode.QueryHistory;
using SwDAL.DTO.QueryHistory;
using SwDAL.Enums;
using System.Diagnostics;
using System.Text.Json;

namespace SwBLL.Query;

public class SwQueryBLL : ISwQueryBLL
{
    private readonly IDbServerBLL         _dbServerBLL;
    private readonly IClientDatabaseBLL   _clientDatabaseBLL;
    private readonly ITargetDbExecutor    _targetDbExecutor;
    private readonly IUserResourceRoleBLL _userResourceRoleBLL;
    private readonly IQueryHistoryDAL     _historyDal;
    private readonly ILogger<SwQueryBLL>  _logger;

    public SwQueryBLL(
        IDbServerBLL dbServerBLL,
        IClientDatabaseBLL clientDatabaseBLL,
        ITargetDbExecutor targetDbExecutor,
        IUserResourceRoleBLL userResourceRoleBLL,
        IQueryHistoryDAL historyDal,
        ILogger<SwQueryBLL> logger)
    {
        _dbServerBLL         = dbServerBLL;
        _clientDatabaseBLL   = clientDatabaseBLL;
        _targetDbExecutor    = targetDbExecutor;
        _userResourceRoleBLL = userResourceRoleBLL;
        _historyDal          = historyDal;
        _logger              = logger;
    }

    public async Task<IEnumerable<dynamic>> ExecuteAdHocQuery(
        string queryCode,
        Dictionary<string, object?> parameters,
         int dbServerId,
         string? databaseName,
        LoginDTO loginDTO,
        CancellationToken ct,
        int? savedQueryId = null,
        int? clientDbId = null)
    {
        if (string.IsNullOrWhiteSpace(queryCode))
            throw new ArgumentException("Query code is required.", nameof(queryCode));

        var sw = Stopwatch.StartNew();
        IEnumerable<dynamic> rows = [];
        bool isSuccess = true;
        string? errorMessage = null;

        try
        {
            // Ad-hoc queries always execute against the selected target via ITargetDbExecutor —
            // never against the caller's own tenant DB. They are inherently read-only
            // (ExecuteQueryAsync enforces SELECT-only), so the least-privilege contained
            // "readonly" user is used whenever the target is a registered ClientDatabase.
            //
            // clientDbId is the preferred, unambiguous way to identify the target — a single
            // DbServerId can back several ClientDatabase rows, so falling back to dbServerId
            // alone (or a raw databaseName string) is only for callers that don't have a
            // clientDbId yet (e.g. browsing a database not registered as a ClientDatabase).
            var clientDb = clientDbId.HasValue
                ? await _clientDatabaseBLL.GetById(clientDbId.Value, loginDTO, ct).ConfigureAwait(false)
                : !string.IsNullOrWhiteSpace(databaseName)
                    ? await _clientDatabaseBLL.GetByServerAndDatabaseName(dbServerId, databaseName, loginDTO, ct).ConfigureAwait(false)
                    : null;

            var effectiveDbServerId = clientDb?.DbServerId ?? dbServerId;
            var server = await _dbServerBLL.GetById(effectiveDbServerId, loginDTO, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException($"DB server {effectiveDbServerId} not found.");

            string connStr;
            if (clientDb is not null)
            {
                connStr = await _targetDbExecutor
                    .BuildConnectionStringForRoleAsync(server, clientDb, ClientDbLoginRole.ReadOnly, ct)
                    .ConfigureAwait(false);
            }
            else
            {
                if (string.IsNullOrWhiteSpace(databaseName))
                    throw new InvalidOperationException("Either ClientDbId or DatabaseName must be specified.");

                // Unlike the registered-ClientDatabase branch above, there is no contained-login
                // vault path to route to here at all — this falls straight to the flat DbServer
                // admin credential, the single most dangerous path in this BLL. Deliberately NOT
                // fail-open like the other two enforcement call sites (ChangeRequestBLL/
                // ProvisioningBLL): requires SOME grant to exist for this user against this
                // DbServer (any role — ad-hoc queries are already SELECT-only-enforced downstream
                // by ExecuteQueryAsync, so the role itself doesn't matter here, only that an
                // admin has explicitly granted this user access to this server at all).
                var grant = await _userResourceRoleBLL
                    .GetEffectiveRole(loginDTO.UserId, server.DbServerId, null, loginDTO, ct)
                    .ConfigureAwait(false);
                if (grant is null)
                    throw new UnauthorizedAccessException(
                        $"User {loginDTO.UserId} has no role grant for DB server {server.DbServerId} — ad-hoc queries against an unregistered database require an explicit grant.");

                connStr = _targetDbExecutor.BuildConnectionString(server, databaseName);
            }

            rows = await _targetDbExecutor.ExecuteQueryAsync(connStr, queryCode, parameters, ct)
                .ConfigureAwait(false);

            return rows;
        }
        catch (Exception ex)
        {
            isSuccess    = false;
            errorMessage = ex.Message;
            _logger.LogError(ex, "ExecuteAdHocQuery failed for user {UserId}", loginDTO.UserId);
            throw;
        }
        finally
        {
            sw.Stop();
            await LogQueryAsync(queryCode, savedQueryId, rows.TryGetNonEnumeratedCount(out int c) ? c : 0,
                (int)sw.ElapsedMilliseconds, isSuccess, errorMessage, loginDTO, ct)
                .ConfigureAwait(false);
        }
    }

    public async Task<string> GetQueryHistoryList(QueryHistoryListCriteria criteria, int pageOffset, int pageSize, LoginDTO login, CancellationToken ct)
    {
        try
        {
            var (rows, total) = await _historyDal.GetList(criteria, pageOffset, pageSize, login, ct).ConfigureAwait(false);
            return JsonSerializer.Serialize(new { rows, total });
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "GetQueryHistoryList failed");
            throw;
        }
    }

    private async Task LogQueryAsync(string queryText, int? savedQueryId, int rowsReturned,
        int executionMs, bool isSuccess, string? errorMessage, LoginDTO login, CancellationToken ct)
    {
        try
        {
            var dto = new QueryHistoryInsertDTO
            {
                QueryText    = queryText,
                SavedQueryId = savedQueryId,
                RowsReturned = rowsReturned,
                ExecutionMs  = executionMs,
                IsSuccess    = isSuccess,
                ErrorMessage = errorMessage
            };
            await _historyDal.Insert(dto, login, ct).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            // Logging failures must not surface to the caller
            _logger.LogWarning(ex, "Failed to log query history for user {UserId}", login.UserId);
        }
    }
}