using System.Data;
using System.Diagnostics;
using System.Text.Json;
using Microsoft.Data.SqlClient;

// IN (@p0, …) against IN (SELECT … OPENJSON(@json)) for DELETE and SELECT by id lists of several sizes.
var settings = JsonDocument.Parse(File.ReadAllText(args[0])).RootElement.GetProperty("ConnectionStrings");
var cs = settings.EnumerateObject().First().Value.GetString()!;
const string T = "probe_in";

async Task Exec(string sql) { await using var c = new SqlConnection(cs); await c.OpenAsync(); await using var k = new SqlCommand(sql, c) { CommandTimeout = 300 }; await k.ExecuteNonQueryAsync(); }
async Task Create() => await Exec($"IF OBJECT_ID('{T}') IS NOT NULL DROP TABLE {T}; CREATE TABLE {T} (id bigint PRIMARY KEY, name nvarchar(100), amount decimal(18,2));");
async Task Refill() => await Exec($"TRUNCATE TABLE {T}; INSERT INTO {T} SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 1)), N'row', 1.5 FROM sys.all_objects a CROSS JOIN sys.all_objects b;");

SqlCommand Build(SqlConnection c, string verb, List<long> ids, bool json)
{
    var head = verb == "DELETE" ? $"DELETE FROM {T} WHERE id IN " : $"SELECT id, name, amount FROM {T} WHERE id IN ";
    var k = new SqlCommand { Connection = c };
    if (json) { k.CommandText = head + "(SELECT v FROM OPENJSON(@j) WITH (v bigint '$'))"; k.Parameters.Add(new SqlParameter("@j", SqlDbType.NVarChar, -1) { Value = JsonSerializer.Serialize(ids) }); }
    else { k.CommandText = head + "(" + string.Join(", ", ids.Select((_, i) => "@p" + i)) + ")"; for (var i = 0; i < ids.Count; i++) k.Parameters.Add(new SqlParameter("@p" + i, SqlDbType.BigInt) { Value = ids[i] }); }
    return k;
}

await Create();
await Refill();
foreach (var verb in new[] { "SELECT", "DELETE" })
foreach (var n in new[] { 20, 50, 100, 200, 500, 1000, 2000 })
{
    var ids = Enumerable.Range(1, n).Select(x => (long)x * 3).ToList();
    var result = new List<string>();
    foreach (var json in new[] { false, true })
    {
        var times = new List<double>();
        for (var run = 0; run < 12; run++)
        {
            if (verb == "DELETE") await Refill();
            await using var c = new SqlConnection(cs); await c.OpenAsync();
            await using var k = Build(c, verb, ids, json);
            var sw = Stopwatch.StartNew();
            if (verb == "DELETE") await k.ExecuteNonQueryAsync(); else { await using var r = await k.ExecuteReaderAsync(); while (await r.ReadAsync()) { } }
            sw.Stop();
            if (run >= 2) times.Add(sw.Elapsed.TotalMilliseconds);
        }
        times.Sort();
        result.Add($"{(json ? "json" : "in  ")} median {times[times.Count / 2],8:F2} ms");
    }
    Console.WriteLine($"{verb} {n,5}: {string.Join("   ", result)}");
}
await Exec($"DROP TABLE {T}");
