using System.Data.Common;
using Dapper;
using FrameworkBLL.Engine;
using Microsoft.Data.Sqlite;
using Xunit;

namespace FrameworkTests;

/// <summary>
/// Covers ConflictResolver's three conflict policies (docs/Platform-Architecture-Integration-Reference.md
/// §5.1/§9.6) against a real in-memory SQLite database — not mocks. ConflictResolver's own SQL
/// (identifier-quoted DELETE/INSERT/SELECT-by-PK/UPDATE) needs no OFFSET/FETCH or other
/// SQL-Server/Postgres-specific syntax, and SQLite genuinely accepts bracket-quoted identifiers
/// ([Col]) for compatibility — so this is real behavioral proof, not a syntax-compatible fiction.
/// Internal-only class, reached via [InternalsVisibleTo("FrameworkTests")] (see
/// FrameworkBLL/Properties/AssemblyInfo.cs).
/// </summary>
public class ConflictResolverTests : IAsyncLifetime
{
    private SqliteConnection _conn = null!;

    public async Task InitializeAsync()
    {
        _conn = new SqliteConnection("Data Source=:memory:");
        await _conn.OpenAsync();
        // ModifiedOn is declared DATETIME (not TEXT) so Microsoft.Data.Sqlite's type-affinity
        // mapping returns a real CLR DateTime for dynamic/object reads, matching what a genuine
        // SQL Server/Postgres native datetime column would give ConflictResolver in production.
        await _conn.ExecuteAsync("CREATE TABLE TestTable (Id INTEGER PRIMARY KEY, Value TEXT, ModifiedOn DATETIME)");
    }

    public async Task DisposeAsync() => await _conn.DisposeAsync();

    private static Dictionary<string, object?> Row(int id, string value, DateTime? modifiedOn = null) =>
        new() { ["Id"] = id, ["Value"] = value, ["ModifiedOn"] = modifiedOn };

    private async Task SeedAsync(int id, string value, DateTime? modifiedOn = null) =>
        await _conn.ExecuteAsync(
            "INSERT INTO TestTable (Id, Value, ModifiedOn) VALUES (@Id, @Value, @ModifiedOn)",
            new { Id = id, Value = value, ModifiedOn = modifiedOn });

    private async Task<string?> GetValueAsync(int id) =>
        await _conn.QuerySingleOrDefaultAsync<string?>(
            "SELECT Value FROM TestTable WHERE Id = @Id", new { Id = id });

    [Fact]
    public async Task Overwrite_ExistingRow_IsDeletedThenReinsertedWithNewValue()
    {
        await SeedAsync(1, "old");

        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            new[] { Row(1, "new") }, "Id",
            ConflictPolicy.Overwrite, null,
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        Assert.Equal(1, written);
        Assert.Equal("new", await GetValueAsync(1));
    }

    [Fact]
    public async Task Overwrite_NewRow_IsInserted()
    {
        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            new[] { Row(1, "brand-new") }, "Id",
            ConflictPolicy.Overwrite, null,
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        Assert.Equal(1, written);
        Assert.Equal("brand-new", await GetValueAsync(1));
    }

    [Fact]
    public async Task Skip_ExistingRow_IsLeftUntouched_NewRowIsInserted()
    {
        await SeedAsync(1, "old");

        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            new[] { Row(1, "attempted-overwrite"), Row(2, "brand-new") }, "Id",
            ConflictPolicy.Skip, null,
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        // Only row 2 was genuinely new — row 1 already existed and must be untouched.
        Assert.Equal(1, written);
        Assert.Equal("old", await GetValueAsync(1));
        Assert.Equal("brand-new", await GetValueAsync(2));
    }

    [Fact]
    public async Task LastWriteWins_SourceNewerThanTarget_Updates()
    {
        var older = new DateTime(2026, 1, 1);
        var newer = new DateTime(2026, 1, 2);
        await SeedAsync(1, "old", older);

        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            new[] { Row(1, "new", newer) }, "Id",
            ConflictPolicy.LastWriteWins, "ModifiedOn",
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        Assert.Equal(1, written);
        Assert.Equal("new", await GetValueAsync(1));
    }

    [Fact]
    public async Task LastWriteWins_SourceOlderThanTarget_LeavesTargetUntouched()
    {
        var older = new DateTime(2026, 1, 1);
        var newer = new DateTime(2026, 1, 2);
        await SeedAsync(1, "authoritative", newer);

        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            new[] { Row(1, "stale-source-value", older) }, "Id",
            ConflictPolicy.LastWriteWins, "ModifiedOn",
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        // Source is older than target — must be skipped, not overwritten.
        Assert.Equal(0, written);
        Assert.Equal("authoritative", await GetValueAsync(1));
    }

    [Fact]
    public async Task LastWriteWins_RowAbsentFromTarget_IsInserted()
    {
        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            new[] { Row(1, "brand-new", DateTime.UtcNow) }, "Id",
            ConflictPolicy.LastWriteWins, "ModifiedOn",
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        Assert.Equal(1, written);
        Assert.Equal("brand-new", await GetValueAsync(1));
    }

    [Fact]
    public async Task LastWriteWins_NoModifiedDateColumnConfigured_FallsBackToOverwrite()
    {
        await SeedAsync(1, "old");

        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            new[] { Row(1, "new") }, "Id",
            ConflictPolicy.LastWriteWins, modifiedDateColumn: null,
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        Assert.Equal(1, written);
        Assert.Equal("new", await GetValueAsync(1));
    }

    [Fact]
    public async Task ApplyAsync_EmptyChunk_ReturnsZeroWithoutTouchingTarget()
    {
        await SeedAsync(1, "untouched");

        int written = await ConflictResolver.ApplyAsync(
            (DbConnection)_conn, "", "TestTable",
            Array.Empty<Dictionary<string, object?>>(), "Id",
            ConflictPolicy.Overwrite, null,
            DatabaseType.SqlServer, DatabaseType.SqlServer, default);

        Assert.Equal(0, written);
        Assert.Equal("untouched", await GetValueAsync(1));
    }
}