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()
    {
        using var connection = new SqliteConnection(connectionString);
        connection.Open();
        using var command = connection.CreateCommand();
        command.CommandText =
            "CREATE TABLE IF NOT EXISTS DetectedHtml (Id INTEGER PRIMARY KEY AUTOINCREMENT, Html TEXT NOT NULL, DetectedAt TEXT NOT NULL)";
        command.ExecuteNonQuery();
    }

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

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