using System.Data;
using Dapper;
using FrameworkDAL.DTO.DiagnosticsCenter;
using FrameworkDAL.DTO.DiagnosticsSession;
using FrameworkDAL.Query.DiagnosticsCenter;
using FrameworkDAL.Query.DiagnosticsSession;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using Npgsql;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkDAL.CustomCode.DiagnosticsCenter;

public class DiagnosticsCenterDAL : IDiagnosticsCenterDAL
{
    private readonly IApplicationConnection         _appConnection;
    private readonly IOptionsMonitor<Gb5SystemDTO>  _systemDto;
    private readonly IQueryExecutor                 _qe;

    public DiagnosticsCenterDAL(
        IApplicationConnection        appConnection,
        IOptionsMonitor<Gb5SystemDTO> systemDto,
        IQueryExecutor                qe)
    {
        _appConnection = appConnection;
        _systemDto     = systemDto;
        _qe            = qe;
    }

    // ── Health Dashboard ──────────────────────────────────────────────────────

    public async Task<DiagnosticsDashboardDTO> GetHealthDashboardAsync(
        int tenantId, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);

        var dashboard = await conn.QuerySingleOrDefaultAsync<DiagnosticsDashboardDTO>(
            new CommandDefinition(
                DiagnosticsCenterQB.GET_HEALTH_DASHBOARD,
                new { TenantId = tenantId },
                cancellationToken: ct)).ConfigureAwait(false)
            ?? new DiagnosticsDashboardDTO();

        dashboard.ActiveDiagnosticSessions = await conn.ExecuteScalarAsync<int>(
            new CommandDefinition(
                DiagnosticsCenterQB.GET_ACTIVE_SESSION_COUNT,
                new { TenantId = tenantId },
                cancellationToken: ct)).ConfigureAwait(false);

        dashboard.GeneratedAt = DateTime.UtcNow;
        return dashboard;
    }

    // ── Slow Transactions ─────────────────────────────────────────────────────

    public async Task<List<SlowTransactionDTO>> GetSlowTransactionsAsync(
        int tenantId, int topN, int hoursBack, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        var rows = await conn.QueryAsync<SlowTransactionDTO>(
            new CommandDefinition(
                DiagnosticsCenterQB.GET_SLOW_TRANSACTIONS,
                new { TenantId = tenantId, TopN = topN, HoursBack = hoursBack },
                cancellationToken: ct)).ConfigureAwait(false);
        return rows.ToList();
    }

    // ── Recent Errors ─────────────────────────────────────────────────────────

    public async Task<List<ErrorSummaryDTO>> GetRecentErrorsAsync(
        int tenantId, int topN, int hoursBack, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        var rows = await conn.QueryAsync<ErrorSummaryDTO>(
            new CommandDefinition(
                DiagnosticsCenterQB.GET_RECENT_ERRORS,
                new { TenantId = tenantId, TopN = topN, HoursBack = hoursBack },
                cancellationToken: ct)).ConfigureAwait(false);
        return rows.ToList();
    }

    // ── Module Health ─────────────────────────────────────────────────────────

    public async Task<List<ModuleHealthDTO>> GetModuleHealthAsync(
        int tenantId, int hoursBack, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        var rows = await conn.QueryAsync<ModuleHealthDTO>(
            new CommandDefinition(
                DiagnosticsCenterQB.GET_MODULE_HEALTH,
                new { TenantId = tenantId, HoursBack = hoursBack },
                cancellationToken: ct)).ConfigureAwait(false);
        return rows.ToList();
    }

    // ── Workflow Diagnostics (per-tenant TEVENTLOG) ───────────────────────────

    public async Task<List<WorkflowDiagnosticsDTO>> GetWorkflowDiagnosticsAsync(
        int topN, int hoursBack, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<WorkflowDiagnosticsDTO>(
            login,
            DiagnosticsCenterQB.GET_WORKFLOW_DIAGNOSTICS,
            new { TopN = topN, HoursBack = hoursBack },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.ToList() ?? [];
    }

    // ── Integration Diagnostics (per-tenant TEVENTLOG) ────────────────────────

    public async Task<List<IntegrationDiagnosticsDTO>> GetIntegrationDiagnosticsAsync(
        int topN, int hoursBack, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<IntegrationDiagnosticsDTO>(
            login,
            DiagnosticsCenterQB.GET_INTEGRATION_DIAGNOSTICS,
            new { TopN = topN, HoursBack = hoursBack },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.ToList() ?? [];
    }

    // ── Session Diagnostics (per-tenant TDIAGNOSTIC_SESSION) ─────────────────

    public async Task<List<DiagnosticsSessionDTO>> GetSessionDiagnosticsAsync(
        int tenantId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<DiagnosticsSessionDTO>(
            login,
            DiagnosticsCenterQB.GET_SESSION_DIAGNOSTICS,
            new { TenantId = tenantId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.ToList() ?? [];
    }

    // ── Helpers ───────────────────────────────────────────────────────────────

    private async Task<IDbConnection> OpenSystemConnectionAsync(CancellationToken ct)
    {
        var connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
        var dbType  = _systemDto.CurrentValue.DataBaseType;

        IDbConnection conn = dbType switch
        {
            DBTYPE.SQL        => new SqlConnection(connStr),
            DBTYPE.POSTGRESQL => new NpgsqlConnection(connStr),
            _ => throw new NotSupportedException($"Unsupported GB5 system DB type: {dbType}")
        };

        conn.Open();
        return conn;
    }
}