using System.Data;
using Microsoft.Data.SqlClient;

namespace ZA.CoreService.ESBCertificateManager.Services;

/// <summary>
/// Stellt sicher, dass optionale Schema-Patches auf bestehenden Datenbanken
/// angewendet wurden (z. B. SonicContainer.CertificateCheckUrl).
/// </summary>
public static class DatabaseSchemaPatcher
{
    private static readonly string[] LegacyCertificateCheckUrlColumns =
    [
        "vertifiaktechecurl",
        "VerifyCertificateCheckCurl",
        "CertificateCheckCurl",
        "ZertifikatCheckCurl"
    ];

    private static bool? _hasCertificateCheckUrlColumn;

    public static async Task EnsureCertificateCheckUrlColumnAsync(
        string connectionString,
        CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(connectionString))
        {
            return;
        }

        await using SqlConnection connection = new(connectionString);
        await connection.OpenAsync(cancellationToken);

        if (await ColumnExistsAsync(
                connection,
                "CertificateCheckUrl",
                cancellationToken))
        {
            _hasCertificateCheckUrlColumn = true;
            return;
        }

        foreach (string legacyColumn in LegacyCertificateCheckUrlColumns)
        {
            if (!await ColumnExistsAsync(
                    connection,
                    legacyColumn,
                    cancellationToken))
            {
                continue;
            }

            await using SqlCommand renameCommand = new(
                """
                EXEC sp_rename
                    N'dbo.SonicContainer.LEGACY_COLUMN',
                    N'CertificateCheckUrl',
                    N'COLUMN';
                """
                    .Replace(
                        "LEGACY_COLUMN",
                        legacyColumn,
                        StringComparison.Ordinal),
                connection);

            await renameCommand.ExecuteNonQueryAsync(cancellationToken);
            _hasCertificateCheckUrlColumn = true;
            return;
        }

        const string addColumnSql = """
            ALTER TABLE [dbo].[SonicContainer]
            ADD [CertificateCheckUrl] [nvarchar](500) NULL;
            """;

        await using SqlCommand addCommand = new(addColumnSql, connection);
        await addCommand.ExecuteNonQueryAsync(cancellationToken);
        _hasCertificateCheckUrlColumn = true;
    }

    public static async Task<bool> HasCertificateCheckUrlColumnAsync(
        string connectionString,
        CancellationToken cancellationToken = default)
    {
        if (_hasCertificateCheckUrlColumn is bool cached)
        {
            return cached;
        }

        if (string.IsNullOrWhiteSpace(connectionString))
        {
            return false;
        }

        await using SqlConnection connection = new(connectionString);
        await connection.OpenAsync(cancellationToken);

        _hasCertificateCheckUrlColumn = await ColumnExistsAsync(
            connection,
            "CertificateCheckUrl",
            cancellationToken);

        return _hasCertificateCheckUrlColumn.Value;
    }

    private static async Task<bool> ColumnExistsAsync(
        SqlConnection connection,
        string columnName,
        CancellationToken cancellationToken)
    {
        await using SqlCommand command = new(
            "SELECT COL_LENGTH(N'dbo.SonicContainer', @ColumnName);",
            connection);

        command.Parameters.Add(
            new SqlParameter("@ColumnName", SqlDbType.NVarChar, 128)
            {
                Value = columnName
            });

        object? result =
            await command.ExecuteScalarAsync(cancellationToken);

        return result is not null and not DBNull;
    }
}