using SQLInsight.Core.DynamicsAx;

namespace SQLInsight.Collectors.Tests;

/// <summary>
/// The SQL the AX collectors send, for every version adapter with a schema file: SQL Server 2005 syntax, bounded,
/// read-only, run in the AX database without changing the session's database, and never NOLOCK.
/// </summary>
public sealed class DynamicsAxSqlTests
{
    private static string Directory => Path.Combine(AppContext.BaseDirectory, "DynamicsAx");

    public static TheoryData<string> Adapters() => [.. AxSchemaAdapter.All.Where(a => AxSchemaFile.Find(Directory, a.SchemaFileName) is not null).Select(a => a.SchemaFileName)];

    private static (AxSchemaAdapter Adapter, AxDatabase Database) Load(string fileName)
    {
        var adapter = AxSchemaAdapter.All.First(a => a.SchemaFileName == fileName);
        var schema = AxSchemaFile.Find(Directory, fileName)!;
        var database = AxDetection.Recognise("AXDB", schema.DetectionRows(), ["AXDB", "AXDB_model"])!;
        return (adapter, database with { Isolation = AxReadIsolation.Snapshot });
    }

    [Theory]
    [MemberData(nameof(Adapters))]
    public void Every_ax_query_is_bounded_read_only_sql_server_2005_syntax(string fileName)
    {
        var (adapter, database) = Load(fileName);
        var queries = new[]
        {
            adapter.BatchJobs(database), adapter.BatchHistory(database, null), adapter.BatchHistory(database, 123_456),
            adapter.NumberSequences(database), adapter.RecIds(database), AxSchemaAdapter.Companies(database),
        }.Where(q => q is not null).Select(q => q!).ToList();

        Assert.All(queries, q => Assert.True(q.IsMeasurable, q.NotMeasurable));
        foreach (var sql in queries.Select(q => AxSql.InDatabase(database.Database, q.Sql!, database.Isolation)).Append(AxSql.InDatabase("AXDB", AxDetection.MetadataSql)))
        {
            Assert.DoesNotContain(DiscoveryTests.Sql2008Only, p => p.IsMatch(sql));
            Assert.DoesNotContain("NOLOCK", sql, StringComparison.OrdinalIgnoreCase);
            // Only AX's own standard tables, never anything else.
            var tables = System.Text.RegularExpressions.Regex.Matches(sql, @"\[(?:bmssa|dbo)\]\.\[(?<t>[A-Z_]+)\]").Select(m => m.Groups["t"].Value).ToList();
            Assert.All(tables, t => Assert.Contains(t, AxDetection.KnownTables));
            Assert.DoesNotContain("USE ", sql, StringComparison.Ordinal);
            Assert.DoesNotMatch(@"\b(INSERT|UPDATE|DELETE|MERGE|TRUNCATE|ALTER|CREATE|DROP|EXEC\s*\()\b", sql.Replace("sp_executesql", string.Empty, StringComparison.Ordinal));
            Assert.StartsWith("EXEC [AXDB].sys.sp_executesql", sql, StringComparison.Ordinal);
            Assert.Contains("SET LOCK_TIMEOUT 2000", sql, StringComparison.Ordinal);
        }

        Assert.All(queries, q => Assert.Contains("SELECT TOP (", q.Sql!, StringComparison.Ordinal));
    }

    [Fact]
    public void Reads_use_snapshot_isolation_when_the_database_allows_it_and_read_committed_otherwise()
    {
        Assert.Contains("ISOLATION LEVEL SNAPSHOT", AxSql.InDatabase("AX", "SELECT 1", AxReadIsolation.Snapshot), StringComparison.Ordinal);
        Assert.Contains("ISOLATION LEVEL READ COMMITTED; SELECT 1", AxSql.InDatabase("AX", "SELECT 1", AxReadIsolation.ReadCommittedSnapshot), StringComparison.Ordinal);
        // Afterwards the agent's own session settings again, so the next collector on the shared connection keeps them.
        Assert.EndsWith("SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;", AxSql.InDatabase("AX", "SELECT 1"), StringComparison.Ordinal);
        Assert.Contains(Core.Instances.AgentSession.LockTimeout, Core.Instances.AgentSession.Settings, StringComparison.Ordinal);
        Assert.Contains(Core.Instances.AgentSession.Isolation, Core.Instances.AgentSession.Settings, StringComparison.Ordinal);
    }
}