using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using SQLInsight.Collectors.Databases;
using SQLInsight.Core.Data;
using SQLInsight.Core.Instances;
using SQLInsight.Repository;

namespace SQLInsight.IntegrationTests;

/// <summary>Index usage followed over time, and statistics that were never updated.</summary>
[Collection(SqlServerTestGroup.Name)]
[Trait("Category", "Integration")]
public class IndexUsageTests(SqlServerFixture sql)
{
    [Fact]
    public async Task Usage_between_collections_is_stored_per_day_and_never_updated_statistics_are_seen()
    {
        var table = "Usage_" + Guid.NewGuid().ToString("N")[..6];
        await sql.ExecuteAsSaAsync($"""
            USE [{SqlServerFixture.TestDatabase}];
            CREATE TABLE dbo.{table} (Id int IDENTITY PRIMARY KEY, Code int NOT NULL, Name nvarchar(20));
            CREATE INDEX IX_{table}_Code ON dbo.{table} (Code);
            CREATE STATISTICS ST_{table}_Name ON dbo.{table} (Name) WITH NORECOMPUTE;
            INSERT dbo.{table} (Code, Name) SELECT TOP (500) object_id % 100, N'x' FROM sys.all_objects;
            """);
        var database = "SQLInsightIdx_" + Guid.NewGuid().ToString("N")[..8];
        await sql.ExecuteAsSaAsync($"CREATE DATABASE [{database}];");
        try
        {
            await using var session = await AgentSession.OpenAsync(sql);
            var first = await session.CollectAsync<IndexSnapshot>(new IndexCollector());
            await sql.ExecuteAsSaAsync($"USE [{SqlServerFixture.TestDatabase}]; SELECT COUNT(*) FROM dbo.{table} WITH (INDEX(IX_{table}_Code)) WHERE Code = 7;");
            var second = await session.CollectAsync(new IndexCollector(), first);

            var index = second.Databases.Single(d => d.Name == SqlServerFixture.TestDatabase).Indexes.Single(i => i.Name == $"IX_{table}_Code");
            Assert.True(index.TrackedReads >= 1);
            Assert.NotNull(index.TrackedSince);
            var delta = Assert.Single(second.UsageDeltas, d => d.Index == $"IX_{table}_Code");
            Assert.True(delta.Reads >= 1);
            var never = second.Databases.Single(d => d.Name == SqlServerFixture.TestDatabase).Statistics.Single(s => s.Name == $"ST_{table}_Name");
            Assert.Null(never.LastUpdated);
            Assert.Equal(500, never.Rows); // the table's rows, not the statistic's

            var repository = new InsightRepository(sql.DatabaseConnectionString(database));
            await repository.MigrateAsync();
            var id = await repository.UpsertInstanceAsync("prod-sql01", session.Instance, "SqlClient", false, DateTimeOffset.UtcNow);
            await repository.SaveCollectedAsync(id, "indexes", second);
            var usage = await repository.GetIndexUsageAsync(id, DateTimeOffset.UtcNow.AddDays(-1), DateTimeOffset.UtcNow);
            Assert.Contains(usage, u => u.IndexName == $"IX_{table}_Code" && u.Reads >= 1 && u.Days == 1);

            await using var factory = new WebApplicationFactory<Program>().WithWebHostBuilder(b =>
            {
                b.UseEnvironment("Development");
                b.UseSetting("SQLInsight:Repository:ConnectionString", sql.DatabaseConnectionString(database));
                b.UseSetting("SQLInsight:Web:Authentication", "None");
            });
            var api = await factory.CreateClient().GetStringAsync("/api/v1/instances/prod-sql01/index-usage?days=7");
            Assert.Contains($"IX_{table}_Code", api, StringComparison.Ordinal);
        }
        finally
        {
            await sql.ExecuteAsSaAsync($"ALTER DATABASE [{database}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [{database}];");
            await sql.ExecuteAsSaAsync($"USE [{SqlServerFixture.TestDatabase}]; DROP TABLE dbo.{table};");
        }
    }
}