using BfiMonitor;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Configuration;

internal sealed record Detection(int Id, string Html, DateTimeOffset DetectedAt);

internal sealed class ScreeningRepository
{
    private readonly string connectionString;

    public ScreeningRepository(IConfiguration configuration)
    {
        connectionString = configuration.GetConnectionString("Screenings") ?? "DataSource=screenings.db";
        Initialize();
    }

    private void Initialize()
    {
        const string sql =
            "CREATE TABLE IF NOT EXISTS DetectedHtml (Id INTEGER PRIMARY KEY AUTOINCREMENT, Html TEXT NOT NULL, DetectedAt TEXT NOT NULL)";
        using var activity = Tracing.StartInitializeScreeningsDatabase(sql);
        using var connection = new SqliteConnection(connectionString);
        connection.Open();
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.ExecuteNonQuery();
    }

    public async Task<string?> LatestHtml(CancellationToken cancellationToken)
    {
        const string sql = "SELECT Html FROM DetectedHtml ORDER BY DetectedAt DESC LIMIT 1";
        using var activity = Tracing.StartLatestHtml(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        return await command.ExecuteScalarAsync(cancellationToken) as string;
    }

    public async Task InsertNewDetection(string html, DateTimeOffset detectedAt)
    {
        const string sql = "INSERT INTO DetectedHtml (Html, DetectedAt) VALUES ($html, $at)";
        using var activity = Tracing.StartInsertNewDetection(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync();
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("$html", html);
        command.Parameters.AddWithValue("$at", detectedAt.ToString("O"));
        await command.ExecuteNonQueryAsync();
    }

    public async Task<IReadOnlyList<Detection>> GetDetectionsAsync(
        int limit,
        CancellationToken cancellationToken = default
    )
    {
        const string sql = "SELECT Id, Html, DetectedAt FROM DetectedHtml ORDER BY DetectedAt DESC LIMIT $limit";
        using var activity = Tracing.StartGetDetections(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("$limit", limit);

        var results = new List<Detection>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add(
                new Detection(reader.GetInt32(0), reader.GetString(1), DateTimeOffset.Parse(reader.GetString(2)))
            );
        }

        return results;
    }

    public async Task<Detection?> GetDetectionByIdAsync(int id, CancellationToken cancellationToken = default)
    {
        const string sql = "SELECT Id, Html, DetectedAt FROM DetectedHtml WHERE Id = $id";
        using var activity = Tracing.StartGetDetectionById(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("$id", id);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        if (!await reader.ReadAsync(cancellationToken))
        {
            return null;
        }

        return new Detection(reader.GetInt32(0), reader.GetString(1), DateTimeOffset.Parse(reader.GetString(2)));
    }

    public async Task<int> CountDetectionsAsync(CancellationToken cancellationToken = default)
    {
        const string sql = "SELECT COUNT(*) FROM DetectedHtml";
        using var activity = Tracing.StartCountDetections(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        return Convert.ToInt32(await command.ExecuteScalarAsync(cancellationToken));
    }
}