using Npgsql;

namespace Azaion.Missions.E2E.Helpers;

/// <summary>
/// Loads named fixture SQL files (e.g. <c>fixture_cascade_F3.sql</c> from
/// <c>_docs/00_problem/input_data/expected_results/</c>) and applies them to
/// the test database via Npgsql side-channel.
/// </summary>
public static class FixtureSql
{
    /// <summary>
    /// Resolves a fixture by its base name (without <c>.sql</c>). The lookup
    /// path is rooted at <c>FIXTURE_SQL_DIR</c> when set, otherwise at the
    /// well-known repo path. Throws when the fixture is missing — silent
    /// fallbacks would mask test setup bugs.
    /// </summary>
    public static void Apply(string fixtureName)
    {
        var sql = Load(fixtureName);
        using var conn = new NpgsqlConnection(TestEnvironment.DbSideChannel);
        conn.Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
    }

    public static string Load(string fixtureName)
    {
        var dir = Environment.GetEnvironmentVariable("FIXTURE_SQL_DIR")
                  ?? "/app/fixtures";
        var path = Path.Combine(dir, fixtureName + ".sql");
        if (!File.Exists(path))
            throw new FileNotFoundException(
                $"fixture SQL not found: {path}. " +
                "Set FIXTURE_SQL_DIR or mount fixtures into /app/fixtures.",
                path);
        return File.ReadAllText(path);
    }
}