using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class DataQuerySpecTests
{
    [Fact]
    public void Constructor_ValidatesAndOrdersParametersBySqlPlaceholder()
    {
        var code = new DataQueryParameter("code", "005930", DbType.String);
        var enabled = new DataQueryParameter("enabled", true, DbType.Boolean);

        var query = new DataQuerySpec(
            "SELECT name FROM stocks WHERE enabled = :enabled AND code = :code",
            [code, enabled]);

        Assert.Equal([enabled, code], query.Parameters);
        Assert.Equal("Parameterized read query", query.ToString());
        Assert.Equal("Data query parameter", code.ToString());
        query.ValidateFor(DataSourceKind.Oracle);
    }

    [Fact]
    public void Constructor_AllowsMarkersInsideQuotedTextAndComments()
    {
        var query = new DataQuerySpec(
            """
            -- @ignored
            SELECT ':also_ignored', "@quoted_identifier", value
            FROM sample /* :ignored_again */
            WHERE code = :code
            """,
            [new DataQueryParameter("code", 7, DbType.Int32)]);

        query.ValidateFor(DataSourceKind.Oracle);
        Assert.Single(query.Parameters);
    }

    [Fact]
    public void Constructor_AllowsSemicolonsAndKeywordsOnlyWhenTheyAreQuotedOrCommented()
    {
        var query = new DataQuerySpec(
            """
            SELECT 'it''s; UPDATE hidden', value /* ; DELETE hidden */
            FROM sample
            WHERE code = :code -- ; REPLACE hidden
            """,
            [new DataQueryParameter("code", 7, DbType.Int32)]);

        query.ValidateFor(DataSourceKind.Oracle);
        Assert.Single(query.Parameters);
    }

    [Theory]
    [InlineData("")]
    [InlineData("has-dash")]
    [InlineData("@prefixed")]
    [InlineData(":prefixed")]
    [InlineData("한글")]
    [InlineData("9starts_with_digit")]
    public void Parameter_RejectsUnsafeNameWithoutEchoingIt(string unsafeName)
    {
        var exception = Assert.Throws<ArgumentException>(
            () => new DataQueryParameter(unsafeName, "value-sensitive-sentinel"));

        if (unsafeName.Length > 0)
        {
            Assert.DoesNotContain(unsafeName, exception.Message, StringComparison.Ordinal);
        }
        Assert.DoesNotContain("value-sensitive-sentinel", exception.Message, StringComparison.Ordinal);
    }

    [Theory]
    [InlineData("SELECT value INTO OUTFILE '/tmp/x' FROM sample")]
    [InlineData("SELECT value FROM sample;")]
    [InlineData("SELECT 1--1 INTO OUTFILE '/tmp/x'")]
    [InlineData("SELECT 1--\u0085 INTO OUTFILE '/tmp/x'")]
    [InlineData("SELECT 1 /*! INTO OUTFILE '/tmp/x' */")]
    [InlineData("SELECT 1 /*M! INTO OUTFILE '/tmp/x' */")]
    [InlineData("SELECT q'[; DELETE FROM sample]' FROM dual")]
    [InlineData("SELECT 'c:\\temp' FROM dual")]
    [InlineData("SELECT 1\u200B")]
    [InlineData("SELECT 1\v")]
    [InlineData("SELECT ?")]
    [InlineData("SELECT 1 # provider-specific comment")]
    [InlineData("WITH value AS (SELECT 1) REPLACE INTO sample VALUES (1)")]
    [InlineData("WITH FUNCTION f RETURN NUMBER IS BEGIN RETURN 1; END; SELECT f FROM dual")]
    [InlineData("SELECT 'unterminated FROM sample")]
    [InlineData("SELECT value FROM sample /* unterminated")]
    public void Constructor_RejectsUnsafeSqlWithoutEchoingIt(string unsafeSql)
    {
        var exception = Assert.Throws<ArgumentException>(
            () => new DataQuerySpec(unsafeSql));

        Assert.DoesNotContain(unsafeSql, exception.Message, StringComparison.Ordinal);
    }

    [Theory]
    [InlineData("UPDATE sample SET value = :code")]
    [InlineData("SELECT value FROM sample WHERE code = :missing")]
    [InlineData("SELECT value FROM sample WHERE code = :code OR parent = :code")]
    [InlineData("SELECT value FROM sample WHERE code = :code$tail")]
    [InlineData("SELECT value FROM sample WHERE code = prefix:code")]
    [InlineData("SELECT value FROM sample WHERE code = :code\u0301")]
    [InlineData("SELECT value FROM sample WHERE code = :code\u00A0")]
    public void Constructor_RejectsUnsafeParameterizedSqlWithoutEchoingSecrets(string unsafeSql)
    {
        const string parameterName = "code";
        const string parameterValue = "value-sensitive-sentinel";

        var exception = Assert.Throws<ArgumentException>(
            () => new DataQuerySpec(
                unsafeSql,
                [new DataQueryParameter(parameterName, parameterValue)]));

        Assert.DoesNotContain(parameterName, exception.Message, StringComparison.Ordinal);
        Assert.DoesNotContain(parameterValue, exception.Message, StringComparison.Ordinal);
        Assert.DoesNotContain(unsafeSql, exception.Message, StringComparison.Ordinal);
    }

    [Fact]
    public void Constructor_RejectsDuplicateSuppliedNamesCaseInsensitivelyWithoutEchoingValues()
    {
        const string firstValue = "first-value-sensitive-sentinel";
        const string secondValue = "second-value-sensitive-sentinel";

        var exception = Assert.Throws<ArgumentException>(
            () => new DataQuerySpec(
                "SELECT value FROM sample WHERE code = :code",
                [
                    new DataQueryParameter("code", firstValue),
                    new DataQueryParameter("CODE", secondValue)
                ]));

        Assert.DoesNotContain(firstValue, exception.Message, StringComparison.Ordinal);
        Assert.DoesNotContain(secondValue, exception.Message, StringComparison.Ordinal);
    }

    [Fact]
    public void ValidateFor_RejectsMixedProviderMarkers()
    {
        var query = new DataQuerySpec(
            "SELECT value FROM sample WHERE first = :first AND second = @second",
            [
                new DataQueryParameter("first", 1),
                new DataQueryParameter("second", 2)
            ]);

        Assert.Throws<ArgumentException>(() => query.ValidateFor(DataSourceKind.Oracle));
        Assert.Throws<ArgumentException>(() => query.ValidateFor(DataSourceKind.MariaDb));
    }

    [Fact]
    public void ValidateFor_RejectsWrongProviderMarkerWithoutExposingParameterName()
    {
        const string sensitiveName = "account_secret_name";
        var query = new DataQuerySpec(
            $"SELECT value FROM sample WHERE account = :{sensitiveName}",
            [new DataQueryParameter(sensitiveName, "value-sensitive-sentinel")]);

        var exception = Assert.Throws<ArgumentException>(
            () => query.ValidateFor(DataSourceKind.MariaDb));

        Assert.DoesNotContain(sensitiveName, exception.Message, StringComparison.Ordinal);
        Assert.DoesNotContain("value-sensitive-sentinel", exception.Message, StringComparison.Ordinal);
    }

    [Fact]
    public async Task LegacyExecutor_DefaultSpecOverload_ForwardsEmptySpecAsynchronously()
    {
        var executor = new LegacyOnlyExecutor();
        var query = new DataQuerySpec("SELECT 1");
        using var cancellation = new CancellationTokenSource();

        await ((IDataQueryExecutor)executor).ExecuteAsync(
            DataSourceKind.MariaDb,
            "Health",
            query,
            cancellation.Token);

        Assert.Equal(0, executor.SynchronousCallCount);
        Assert.Equal(1, executor.AsynchronousCallCount);
        Assert.Equal("SELECT 1", executor.ObservedSql);
        Assert.Equal(cancellation.Token, executor.ObservedCancellationToken);
    }

    [Fact]
    public async Task LegacyExecutor_DefaultSpecOverload_FailsClosedForParameters()
    {
        var executor = new LegacyOnlyExecutor();
        var query = new DataQuerySpec(
            "SELECT value FROM sample WHERE code = @code",
            [new DataQueryParameter("code", "value-sensitive-sentinel")]);

        var exception = await Assert.ThrowsAsync<NotSupportedException>(
            () => ((IDataQueryExecutor)executor).ExecuteAsync(
                DataSourceKind.MariaDb,
                "Sample",
                query));

        Assert.Equal(0, executor.SynchronousCallCount);
        Assert.Equal(0, executor.AsynchronousCallCount);
        Assert.DoesNotContain("code", exception.Message, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("value-sensitive-sentinel", exception.Message, StringComparison.Ordinal);
    }

    private sealed class LegacyOnlyExecutor : IDataQueryExecutor
    {
        public int SynchronousCallCount { get; private set; }

        public int AsynchronousCallCount { get; private set; }

        public string? ObservedSql { get; private set; }

        public CancellationToken ObservedCancellationToken { get; private set; }

        public DataTable Execute(DataSourceKind source, string tableName, string query)
        {
            SynchronousCallCount++;
            throw new InvalidOperationException("The synchronous path must not be used.");
        }

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default)
        {
            AsynchronousCallCount++;
            ObservedSql = query;
            ObservedCancellationToken = cancellationToken;
            return Task.FromResult(new DataTable());
        }
    }
}
