using System.Reflection;
using System.Text.RegularExpressions;
using Microsoft.Extensions.DependencyInjection;
using Omika.Core.Compatibility;
using Omika.Core.Runtime;
using Omika.Data;
using Omika.Data.Configuration;
using Omika.Data.Connection;
using Omika.Data.SchemaDetection;


namespace Omika.Tests.SchemaDetection;

[Collection("ApplicationRuntimeContext")]
public class DatabaseSchemaDetectorTests
{

    // --- Métadonnées simulées -------------------------------------------------

    private static readonly IReadOnlyList<HfsqlColumnMetadata> BatikClientColumns =
    [
        new("Client", "CL_PKID"),
        new("Client", "CL_Nom"),
        new("Client", "CL_IDType")
    ];

    private static readonly IReadOnlyList<HfsqlColumnMetadata> OmikaClientColumns =
    [
        new("Client", "CL_PKID"),
        new("Client", "CL_Nom"),
        new("Client", "CL_IDType"),
        new("Client", "CL_CleType"),
        new("Client", "GUID")
    ];

    private static readonly IReadOnlyList<HfsqlColumnMetadata> RoleColumns =
    [
        new("Role", "R_PKID"),
        new("Role", "R_Libelle")
    ];

    private static readonly IReadOnlyDictionary<string, IReadOnlyList<HfsqlColumnMetadata>> BatikSchema =
        new Dictionary<string, IReadOnlyList<HfsqlColumnMetadata>>
        {
            ["Client"] = BatikClientColumns
        };

    private static readonly IReadOnlyDictionary<string, IReadOnlyList<HfsqlColumnMetadata>> OmikaSchema =
        new Dictionary<string, IReadOnlyList<HfsqlColumnMetadata>>
        {
            ["Client"] = OmikaClientColumns,
            ["Role"] = RoleColumns
        };

    // --- Cas 1 : structure Omika complète => Omika ----------------------------

    [Fact]
    public async Task DetectAsync_OmikaComplete_ReturnsOmika()
    {
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        Assert.True(result.IsSuccess);
        Assert.Equal(DatabaseSchemaVersion.Omika, result.Value);
    }

    // --- Cas 2 : structure Batik complète => Batik ----------------------------

    [Fact]
    public async Task DetectAsync_BatikComplete_ReturnsBatik()
    {
        var connection = new FakeConnection(BatikSchema);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        Assert.True(result.IsSuccess);
        Assert.Equal(DatabaseSchemaVersion.Batik, result.Value);
    }

    // --- Cas 3 : insensibilité à la casse -------------------------------------

    [Fact]
    public async Task DetectAsync_ColumnNamesDifferentCase_IsCaseInsensitive()
    {
        var schema = new Dictionary<string, IReadOnlyList<HfsqlColumnMetadata>>
        {
            ["Client"] =
            [
                new("Client", "cl_pkid"),
                new("Client", "cl_cletype"),
                new("Client", "guid")
            ],
            ["Role"] = [new("Role", "r_pkid")]
        };
        var connection = new FakeConnection(schema);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        Assert.True(result.IsSuccess);
        Assert.Equal(DatabaseSchemaVersion.Omika, result.Value);
    }

    // --- Cas 4 : un seul canari Client présent mais Role présent => Omika -----

    [Fact]
    public async Task DetectAsync_SingleClientCanaryWithRole_ReturnsOmika()
    {
        // Seul CL_CleType présent (GUID absent), mais Role présent => Omika confirmé.
        var schema = new Dictionary<string, IReadOnlyList<HfsqlColumnMetadata>>
        {
            ["Client"] =
            [
                new("Client", "CL_PKID"),
                new("Client", "CL_CleType")
            ],
            ["Role"] = RoleColumns
        };
        var connection = new FakeConnection(schema);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        Assert.True(result.IsSuccess);
        Assert.Equal(DatabaseSchemaVersion.Omika, result.Value);
    }

    // --- Cas 5 : un seul canari Client présent sans Role => échec -------------

    [Fact]
    public async Task DetectAsync_SingleClientCanaryWithoutRole_ReturnsFailure()
    {
        // Seul CL_CleType présent (GUID absent), Role absent => incohérent.
        var schema = new Dictionary<string, IReadOnlyList<HfsqlColumnMetadata>>
        {
            ["Client"] =
            [
                new("Client", "CL_PKID"),
                new("Client", "CL_CleType")
            ]
        };
        var connection = new FakeConnection(schema);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        Assert.True(result.IsFailure);
        Assert.Contains("incohérente", result.Errors[0], StringComparison.OrdinalIgnoreCase);
    }

    // --- Cas 6 : table fondamentale absente => échec --------------------------

    [Fact]
    public async Task DetectAsync_MissingCommonColumn_ReturnsFailure()
    {
        var schema = new Dictionary<string, IReadOnlyList<HfsqlColumnMetadata>>
        {
            ["Client"] = [new("Client", "CL_Nom")]
        };
        var connection = new FakeConnection(schema);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        Assert.True(result.IsFailure);
        Assert.Contains("pas reconnue", result.Errors[0], StringComparison.OrdinalIgnoreCase);
    }

    // --- Cas 7 : erreur de requête sur un canari => comportement contrôlé -----

    [Fact]
    public async Task DetectAsync_QueryError_ReturnsFailureNotBatik()
    {
        var connection = new FakeConnection(throwOnQuery: true);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        // Une erreur de requête ne doit jamais être interprétée comme Batik :
        // le détecteur doit retourner un échec explicite, quel que soit le canari
        // qui a échoué (socle commun ou canari Omika).
        Assert.True(result.IsFailure);
        Assert.False(result.IsSuccess);

    }


    // --- Cas 8 : connexion fermée => ouverture avant détection ---------------

    [Fact]
    public async Task DetectAsync_ClosedConnection_OpensBeforeDetection()
    {
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        await detector.DetectAsync();

        Assert.Equal(1, connection.OpenCount);
        Assert.Equal(1, connection.CloseCount);
    }

    // --- Cas 9 : connexion déjà ouverte => pas de double ouverture -----------

    [Fact]
    public async Task DetectAsync_AlreadyOpenConnection_DoesNotReopen()
    {
        var connection = new FakeConnection(OmikaSchema, initialState: HfsqlConnectionState.Open);
        var detector = new DatabaseSchemaDetector(connection);

        await detector.DetectAsync();

        Assert.Equal(0, connection.OpenCount);
        Assert.Equal(0, connection.CloseCount);
    }

    // --- Cas 10 : cancellation demandée avant détection ----------------------

    [Fact]
    public async Task DetectAsync_CancellationRequested_Throws()
    {
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);
        using var cts = new CancellationTokenSource();
        cts.Cancel();

        await Assert.ThrowsAnyAsync<OperationCanceledException>(
            () => detector.DetectAsync(cts.Token));
    }

    // --- Cas 11 : aucune écriture SQL exécutée --------------------------------

    [Fact]
    public async Task DetectAsync_DoesNotExecuteAnyWriteSql()
    {
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        await detector.DetectAsync();

        Assert.Equal(0, connection.ExecuteNonQueryCount);
        Assert.Equal(0, connection.ExecuteScalarCount);
    }

    // --- Cas 12 : aucune donnée métier lue (requêtes TOP 0 uniquement) --------

    [Fact]
    public async Task DetectAsync_DoesNotReadBusinessData()
    {
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        await detector.DetectAsync();

        Assert.True(connection.QueryCount > 0);
        Assert.All(connection.ExecutedQueries, sql =>
            Assert.True(Regex.IsMatch(sql, @"^SELECT\s+TOP\s+0\s+", RegexOptions.IgnoreCase),
                $"Requête non structurelle exécutée : {sql}"));

    }

    // --- Cas 13 : aucun appel à ApplicationRuntimeContext.SetDetectedVersion ---

    [Fact]
    public async Task DetectAsync_DoesNotTouchApplicationRuntimeContext()
    {
        ApplicationRuntimeContext.Reset();
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        await detector.DetectAsync();

        Assert.False(ApplicationRuntimeContext.IsConfigured);
    }

    // --- Cas 14 : résultat déterministe ---------------------------------------

    [Fact]
    public async Task DetectAsync_IsDeterministic()
    {
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        var first = await detector.DetectAsync();
        var second = await detector.DetectAsync();

        Assert.Equal(first.IsSuccess, second.IsSuccess);
        Assert.Equal(first.Value, second.Value);
    }

    // --- Cas 15 : IDatabaseSchemaDetector enregistré en Scoped ----------------

    [Fact]
    public void AddOmikaData_RegistersDetectorAsScoped()
    {
        var services = new ServiceCollection();
        services.AddOmikaData();

        var descriptor = services.Single(d => d.ServiceType == typeof(IDatabaseSchemaDetector));

        Assert.Equal(ServiceLifetime.Scoped, descriptor.Lifetime);
        Assert.Equal(typeof(DatabaseSchemaDetector), descriptor.ImplementationType);
    }

    // --- Cas 16 : aucune classe ODBC exposée dans la signature publique --------

    [Fact]
    public void DetectorPublicSurface_DoesNotExposeOdbcTypes()
    {
        var detectorType = typeof(DatabaseSchemaDetector);
        var contractType = typeof(IDatabaseSchemaDetector);

        var publicTypes = new[] { detectorType, contractType };

        foreach (var type in publicTypes)
        {
            var members = type.GetMembers(BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static)
                .Select(m => m.DeclaringType)
                .Where(t => t is not null)
                .Cast<Type>();

            var parameters = type.GetMethods(BindingFlags.Public | BindingFlags.Instance)
                .SelectMany(m => m.GetParameters())
                .Select(p => p.ParameterType);

            var returnTypes = type.GetMethods(BindingFlags.Public | BindingFlags.Instance)
                .Select(m => m.ReturnType);

            var referenced = members.Concat(parameters).Concat(returnTypes);

            Assert.DoesNotContain(referenced, t =>
                t.Namespace is not null && t.Namespace.StartsWith("System.Data.Odbc", StringComparison.Ordinal));
        }
    }

    // --- Cas 17 : aucune dépendance au nom de base ----------------------------

    [Fact]
    public async Task DetectAsync_DoesNotDependOnDatabaseName()
    {
        // Le nom de base est totalement différent de BATIK_TEST / OMIKA_TEST.
        // La détection repose uniquement sur la structure (colonnes canaris).
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        var result = await detector.DetectAsync();

        Assert.True(result.IsSuccess);
        Assert.Equal(DatabaseSchemaVersion.Omika, result.Value);
    }

    // --- Cas 18 : aucune dépendance OpenSchema --------------------------------

    [Fact]
    public async Task DetectAsync_DoesNotUseOpenSchema()
    {
        var connection = new FakeConnection(OmikaSchema);
        var detector = new DatabaseSchemaDetector(connection);

        await detector.DetectAsync();

        Assert.Equal(0, connection.GetColumnsCount);
    }

    // --- Fake de connexion -----------------------------------------------------

    private sealed class FakeConnection : IHfsqlConnection
    {
        private readonly IReadOnlyDictionary<string, IReadOnlyList<HfsqlColumnMetadata>>? _schema;
        private readonly bool _throwOnQuery;

        public FakeConnection(
            IReadOnlyDictionary<string, IReadOnlyList<HfsqlColumnMetadata>>? schema = null,
            bool throwOnQuery = false,
            HfsqlConnectionState initialState = HfsqlConnectionState.Closed)
        {
            _schema = schema;
            _throwOnQuery = throwOnQuery;
            State = initialState;
        }

        public HfsqlOptions Options => new();
        public HfsqlConnectionState State { get; private set; }

        public int OpenCount { get; private set; }
        public int CloseCount { get; private set; }
        public int ExecuteNonQueryCount { get; private set; }
        public int ExecuteScalarCount { get; private set; }
        public int QueryCount { get; private set; }
        public int GetColumnsCount { get; private set; }
        public List<string> ExecutedQueries { get; } = new();

        public Task OpenAsync(CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
            OpenCount++;
            State = HfsqlConnectionState.Open;
            return Task.CompletedTask;
        }

        public Task CloseAsync()
        {
            CloseCount++;
            State = HfsqlConnectionState.Closed;
            return Task.CompletedTask;
        }

        public ValueTask DisposeAsync()
        {
            State = HfsqlConnectionState.Closed;
            return ValueTask.CompletedTask;
        }

        public Task<object?> ExecuteScalarAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken cancellationToken = default)
        {
            ExecuteScalarCount++;
            return Task.FromResult<object?>(null);
        }

        public Task<int> ExecuteNonQueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken cancellationToken = default)
        {
            ExecuteNonQueryCount++;
            return Task.FromResult(0);
        }

        public Task<IReadOnlyList<IReadOnlyDictionary<string, object?>>> QueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken cancellationToken = default)
        {
            QueryCount++;
            ExecutedQueries.Add(sql);
            cancellationToken.ThrowIfCancellationRequested();

            if (_throwOnQuery)
                throw new InvalidOperationException("Échec contrôlé de la requête.");

            // Parse "SELECT TOP 0 <col> FROM <table>".
            var match = Regex.Match(sql, @"SELECT\s+TOP\s+0\s+(\w+)\s+FROM\s+(\w+)", RegexOptions.IgnoreCase);
            if (!match.Success)
                return Task.FromResult<IReadOnlyList<IReadOnlyDictionary<string, object?>>>([]);

            var column = match.Groups[1].Value;
            var table = match.Groups[2].Value;

            if (_schema is null || !_schema.TryGetValue(table, out var columns))
                throw new InvalidOperationException($"Table absente : {table}");

            if (!columns.Any(c => c.ColumnName.Equals(column, StringComparison.OrdinalIgnoreCase)))
                throw new InvalidOperationException($"Colonne absente : {column}");

            return Task.FromResult<IReadOnlyList<IReadOnlyDictionary<string, object?>>>([]);
        }


        public Task<IReadOnlyList<HfsqlColumnMetadata>> GetColumnsAsync(
            string tableName,
            CancellationToken cancellationToken = default)
        {
            GetColumnsCount++;
            cancellationToken.ThrowIfCancellationRequested();

            if (_schema is null || !_schema.TryGetValue(tableName, out var columns))
                return Task.FromResult<IReadOnlyList<HfsqlColumnMetadata>>([]);

            return Task.FromResult(columns);
        }
    }
}