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

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();
    }
}