using Npgsql;
using Xunit;

namespace Azaion.Missions.E2E.Helpers;

/// <summary>
/// Side-channel database assertions. Used to verify state the API does not
/// expose directly (default-vehicle invariants, mission row counts after
/// cascade-delete, audit-table side effects).
/// </summary>
/// <remarks>
/// Marked with <c>[Trait("db_access","seed-or-assert-only")]</c> at the
/// consumer-test level — this helper itself is a pure utility.
/// </remarks>
public static class DbAssertions
{
    public static long ScalarCount(string sql, params (string Name, object Value)[] parameters)
    {
        using var conn = new NpgsqlConnection(TestEnvironment.DbSideChannel);
        conn.Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        foreach (var (name, value) in parameters)
            cmd.Parameters.AddWithValue(name, value);
        var result = cmd.ExecuteScalar();
        if (result is null || result is DBNull)
            throw new InvalidOperationException($"Scalar query '{sql}' returned NULL");
        return Convert.ToInt64(result, System.Globalization.CultureInfo.InvariantCulture);
    }

    public static void AssertExactlyOneDefaultVehicle()
    {
        var count = ScalarCount("SELECT COUNT(*) FROM vehicles WHERE is_default = TRUE");
        Assert.True(count <= 1, $"default-vehicle invariant violated: {count} vehicles flagged is_default=TRUE");
    }

    public static long TableRowCount(string table)
    {
        if (!IsValidIdentifier(table))
            throw new ArgumentException($"Invalid table identifier '{table}'", nameof(table));
        return ScalarCount($"SELECT COUNT(*) FROM {table}");
    }

    private static bool IsValidIdentifier(string s)
    {
        if (string.IsNullOrEmpty(s) || s.Length > 63) return false;
        foreach (var c in s)
        {
            if (!(char.IsLetterOrDigit(c) || c == '_'))
                return false;
        }
        return true;
    }
}