using FrameworkBLL.Engine;
using Xunit;

namespace FrameworkTests;

/// <summary>
/// Covers SyncQueryBuilder's identifier quoting, watermark-clause assembly, and the
/// QUERYCONDITION injection guard (docs/Platform-Architecture-Integration-Reference.md §9.6) —
/// pure string-building logic, no database connection needed. Internal-only class, reached via
/// [InternalsVisibleTo("FrameworkTests")] on FrameworkBLL (see Properties/AssemblyInfo.cs).
/// </summary>
public class SyncQueryBuilderTests
{
    [Theory]
    [InlineData(DatabaseType.SqlServer, "[Col]")]
    [InlineData(DatabaseType.PostgreSQL, "\"Col\"")]
    [InlineData(DatabaseType.MySQL, "`Col`")]
    public void Quote_PerDialect_UsesCorrectDelimiters(DatabaseType dbType, string expected)
    {
        Assert.Equal(expected, SyncQueryBuilder.Quote("Col", dbType));
    }

    [Fact]
    public void Quote_Oracle_UppercasesIdentifier()
    {
        Assert.Equal("\"COL\"", SyncQueryBuilder.Quote("col", DatabaseType.Oracle));
    }

    [Fact]
    public void QuoteTable_WithSchema_ProducesSchemaDotTable()
    {
        Assert.Equal("[dbo].[MyTable]", SyncQueryBuilder.QuoteTable("dbo", "MyTable", DatabaseType.SqlServer));
    }

    [Fact]
    public void QuoteTable_MySqlWithSchemaSupplied_StillProducesSchemaDotTable()
    {
        // QuoteTable itself has no MySQL-specific "no schema" branch — the schema/no-schema
        // decision lives with the caller (DataSyncRunBLL passes an empty schema for MySQL
        // targets); QuoteTable just quotes whatever schema string it's given, per dialect.
        Assert.Equal("`public`.`MyTable`", SyncQueryBuilder.QuoteTable("public", "MyTable", DatabaseType.MySQL));
    }

    [Fact]
    public void QuoteTable_EmptySchema_ReturnsJustTheTable()
    {
        Assert.Equal("[MyTable]", SyncQueryBuilder.QuoteTable("", "MyTable", DatabaseType.SqlServer));
    }

    [Fact]
    public void BuildSelectSql_NoWatermarks_OmitsWhereClause()
    {
        var sql = SyncQueryBuilder.BuildSelectSql(
            "dbo", "MUOM", "UOMID", null, null, null, null, null, DatabaseType.SqlServer);

        Assert.DoesNotContain("WHERE", sql);
        Assert.Contains("ORDER BY [UOMID]", sql);
        Assert.Contains("OFFSET @ChunkOffset ROWS FETCH NEXT @ChunkSize ROWS ONLY", sql);
    }

    [Fact]
    public void BuildSelectSql_BothWatermarksSet_CombinesWithOrNotAnd()
    {
        // A row qualifies if it was EITHER created OR modified since the last run — joining
        // with AND (the original, buggy behavior) meant a pre-existing row's old CreatedOn and
        // new ModifiedOn could never both be true at once, so no update to an existing row
        // could ever match. See SyncQueryBuilder.BuildSelectSql's own comment for the full
        // real-world bug this characterizes.
        var sql = SyncQueryBuilder.BuildSelectSql(
            "dbo", "MUOM", "UOMID", "CREATEDON", "MODIFIEDON",
            new DateTime(2026, 1, 1), new DateTime(2026, 1, 2), null, DatabaseType.SqlServer);

        Assert.Contains("[CREATEDON] >= @LastInsertedTill", sql);
        Assert.Contains("[MODIFIEDON] >= @LastUpdatedTill", sql);
        Assert.Contains("([CREATEDON] >= @LastInsertedTill OR [MODIFIEDON] >= @LastUpdatedTill)", sql);
        Assert.DoesNotContain(" AND ", sql);
    }

    [Fact]
    public void BuildSelectSql_OnlyInsertedWatermarkHasValue_OmitsUpdatedClause()
    {
        var sql = SyncQueryBuilder.BuildSelectSql(
            "dbo", "MUOM", "UOMID", "CREATEDON", "MODIFIEDON",
            new DateTime(2026, 1, 1), null, null, DatabaseType.SqlServer);

        Assert.Contains("[CREATEDON] >= @LastInsertedTill", sql);
        Assert.DoesNotContain("@LastUpdatedTill", sql);
    }

    [Fact]
    public void BuildSelectSql_MySql_UsesLimitOffsetSyntax()
    {
        var sql = SyncQueryBuilder.BuildSelectSql(
            "", "muom", "uomid", null, null, null, null, null, DatabaseType.MySQL);

        Assert.Contains("LIMIT @ChunkSize OFFSET @ChunkOffset", sql);
    }

    [Fact]
    public void BuildSelectSql_ValidExtraWhere_IsIncludedParenthesized()
    {
        var sql = SyncQueryBuilder.BuildSelectSql(
            "dbo", "MUOM", "UOMID", null, null, null, null, "STATUS = 1", DatabaseType.SqlServer);

        Assert.Contains("WHERE (STATUS = 1)", sql);
    }

    [Theory]
    [InlineData("STATUS = 1; DROP TABLE MUOM")]
    [InlineData("1=1 UNION SELECT * FROM MUSER")]
    [InlineData("EXEC sp_who")]
    public void BuildSelectSql_ExtraWhereContainsDisallowedKeyword_Throws(string maliciousCondition)
    {
        Assert.Throws<InvalidOperationException>(() =>
            SyncQueryBuilder.BuildSelectSql(
                "dbo", "MUOM", "UOMID", null, null, null, null, maliciousCondition, DatabaseType.SqlServer));
    }

    [Fact]
    public void BuildCdcSelectSql_ProducesExpectedCdcTableAndOperationFilter()
    {
        var sql = SyncQueryBuilder.BuildCdcSelectSql("MUOM", "UOMID", new DateTime(2026, 1, 1));

        Assert.Contains("cdc.[dbo_MUOM_CT]", sql);
        Assert.Contains("__$operation = 1", sql);
        Assert.Contains("@SinceUtc", sql);
    }
}