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

internal sealed record Monitoring(
    int Id,
    string Name,
    string Url,
    string Selector,
    string[] PhoneNumbers,
    DateTimeOffset CreatedAt,
    DateTimeOffset? PausedAt,
    DateTimeOffset? ArchivedAt
);

internal sealed record MonitoringSummary(
    int Id,
    string Name,
    string Url,
    string Selector,
    string[] PhoneNumbers,
    DateTimeOffset CreatedAt,
    DateTimeOffset? PausedAt,
    DateTimeOffset? ArchivedAt,
    int TotalDetections,
    DateTimeOffset? LatestDetectionAt
);

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

internal sealed class ScreeningRepository
{
    private readonly string connectionString;
    private readonly IConfiguration configuration;

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

    private void Initialize()
    {
        using var connection = new SqliteConnection(connectionString);
        connection.Open();

        Execute(
            connection,
            """
            CREATE TABLE IF NOT EXISTS Monitorings (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL DEFAULT '',
                Url TEXT NOT NULL,
                Selector TEXT NOT NULL,
                CreatedAt TEXT NOT NULL,
                ArchivedAt TEXT
            )
            """
        );

        Execute(
            connection,
            """
            CREATE TABLE IF NOT EXISTS DetectedHtml (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Html TEXT NOT NULL,
                DetectedAt TEXT NOT NULL
            )
            """
        );

        EnsureMonitorIdColumn(connection);
        EnsurePausedAtColumn(connection);
        EnsurePhoneNumbersColumn(connection);
        SeedAppSettings(connection);
        MigrateMonitoringPhoneNumbers(connection);
        SeedDefaultMonitoring(connection);
    }

    private static void Execute(SqliteConnection connection, string sql)
    {
        using var activity = Tracing.StartInitializeScreeningsDatabase(sql);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.ExecuteNonQuery();
    }

    private static bool ColumnExists(SqliteConnection connection, string table, string column)
    {
        using var command = connection.CreateCommand();
        command.CommandText = $"PRAGMA table_info({table})";
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            if (string.Equals(reader.GetString(1), column, StringComparison.OrdinalIgnoreCase))
            {
                return true;
            }
        }

        return false;
    }

    private void EnsureMonitorIdColumn(SqliteConnection connection)
    {
        if (ColumnExists(connection, "DetectedHtml", "MonitorId"))
        {
            return;
        }

        Execute(connection, "ALTER TABLE DetectedHtml ADD COLUMN MonitorId INTEGER");
    }

    private static void EnsurePausedAtColumn(SqliteConnection connection)
    {
        if (ColumnExists(connection, "Monitorings", "PausedAt"))
        {
            return;
        }

        Execute(connection, "ALTER TABLE Monitorings ADD COLUMN PausedAt TEXT");
    }

    private static void EnsurePhoneNumbersColumn(SqliteConnection connection)
    {
        if (ColumnExists(connection, "Monitorings", "PhoneNumbers"))
        {
            return;
        }

        Execute(connection, "ALTER TABLE Monitorings ADD COLUMN PhoneNumbers TEXT NOT NULL DEFAULT '[]'");
    }

    private void SeedAppSettings(SqliteConnection connection)
    {
        Execute(
            connection,
            """
            CREATE TABLE IF NOT EXISTS AppSettings (
                Id INTEGER PRIMARY KEY CHECK (Id = 1),
                IntervalSeconds INTEGER NOT NULL,
                DefaultPhoneNumbers TEXT NOT NULL DEFAULT '[]'
            )
            """
        );

        using var countCommand = connection.CreateCommand();
        countCommand.CommandText = "SELECT COUNT(*) FROM AppSettings";
        if (Convert.ToInt32(countCommand.ExecuteScalar()) > 0)
        {
            return;
        }

        var intervalSeconds = configuration.GetValue("Monitor:IntervalSeconds", 300);
        var defaultPhoneNumbers = PhoneNumbersJson.Serialize(
            configuration.GetSection("Monitor:PhoneNumbers").Get<string[]>() ?? []
        );

        using var insertCommand = connection.CreateCommand();
        insertCommand.CommandText =
            "INSERT INTO AppSettings (Id, IntervalSeconds, DefaultPhoneNumbers) VALUES (1, $interval, $phones)";
        insertCommand.Parameters.AddWithValue("$interval", intervalSeconds);
        insertCommand.Parameters.AddWithValue("$phones", defaultPhoneNumbers);
        insertCommand.ExecuteNonQuery();
    }

    private void MigrateMonitoringPhoneNumbers(SqliteConnection connection)
    {
        var configPhones = PhoneNumbersJson.Serialize(
            configuration.GetSection("Monitor:PhoneNumbers").Get<string[]>() ?? []
        );
        if (configPhones == "[]")
        {
            return;
        }

        using var updateCommand = connection.CreateCommand();
        updateCommand.CommandText = """
            UPDATE Monitorings
            SET PhoneNumbers = $phones
            WHERE PhoneNumbers IS NULL OR PhoneNumbers = '[]'
            """;
        updateCommand.Parameters.AddWithValue("$phones", configPhones);
        updateCommand.ExecuteNonQuery();
    }

    private void SeedDefaultMonitoring(SqliteConnection connection)
    {
        using var countCommand = connection.CreateCommand();
        countCommand.CommandText = "SELECT COUNT(*) FROM Monitorings";
        if (Convert.ToInt32(countCommand.ExecuteScalar()) > 0)
        {
            AssignOrphanDetections(connection);
            return;
        }

        var url = configuration["Monitor:Url"];
        if (string.IsNullOrWhiteSpace(url))
        {
            return;
        }

        var selector = configuration["Monitor:Selector"] ?? ".detailed-search-results";
        var name = configuration["Monitor:Name"] ?? "";
        var createdAt = DateTimeOffset.UtcNow.ToString("O");
        var phoneNumbers = PhoneNumbersJson.Serialize(
            configuration.GetSection("Monitor:PhoneNumbers").Get<string[]>() ?? []
        );

        using var insertCommand = connection.CreateCommand();
        insertCommand.CommandText = """
            INSERT INTO Monitorings (Name, Url, Selector, PhoneNumbers, CreatedAt)
            VALUES ($name, $url, $selector, $phones, $createdAt)
            """;
        insertCommand.Parameters.AddWithValue("$name", name);
        insertCommand.Parameters.AddWithValue("$url", url);
        insertCommand.Parameters.AddWithValue("$selector", selector);
        insertCommand.Parameters.AddWithValue("$phones", phoneNumbers);
        insertCommand.Parameters.AddWithValue("$createdAt", createdAt);
        insertCommand.ExecuteNonQuery();

        AssignOrphanDetections(connection);
    }

    private static void AssignOrphanDetections(SqliteConnection connection)
    {
        using var monitorCommand = connection.CreateCommand();
        monitorCommand.CommandText = "SELECT Id FROM Monitorings ORDER BY Id LIMIT 1";
        var firstMonitorId = monitorCommand.ExecuteScalar();
        if (firstMonitorId is null)
        {
            return;
        }

        using var updateCommand = connection.CreateCommand();
        updateCommand.CommandText = "UPDATE DetectedHtml SET MonitorId = $monitorId WHERE MonitorId IS NULL";
        updateCommand.Parameters.AddWithValue("$monitorId", firstMonitorId);
        updateCommand.ExecuteNonQuery();
    }

    public async Task<IReadOnlyList<MonitoringSummary>> GetMonitoringsAsync(
        bool includeArchived,
        CancellationToken cancellationToken = default
    )
    {
        var sql = """
            SELECT
                m.Id,
                m.Name,
                m.Url,
                m.Selector,
                m.PhoneNumbers,
                m.CreatedAt,
                m.PausedAt,
                m.ArchivedAt,
                (SELECT COUNT(*) FROM DetectedHtml d WHERE d.MonitorId = m.Id) AS TotalDetections,
                (SELECT MAX(d.DetectedAt) FROM DetectedHtml d WHERE d.MonitorId = m.Id) AS LatestDetectionAt
            FROM Monitorings m
            WHERE ($includeArchived = 1 OR m.ArchivedAt IS NULL)
            ORDER BY m.ArchivedAt IS NOT NULL, m.PausedAt IS NOT NULL, m.CreatedAt DESC
            """;

        using var activity = Tracing.StartGetMonitorings(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("$includeArchived", includeArchived ? 1 : 0);

        var results = new List<MonitoringSummary>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add(ReadMonitoringSummary(reader));
        }

        return results;
    }

    public async Task<IReadOnlyList<Monitoring>> GetActiveMonitoringsAsync(
        CancellationToken cancellationToken = default
    )
    {
        const string sql = """
            SELECT Id, Name, Url, Selector, PhoneNumbers, CreatedAt, PausedAt, ArchivedAt
            FROM Monitorings
            WHERE ArchivedAt IS NULL AND PausedAt IS NULL
            ORDER BY CreatedAt
            """;

        using var activity = Tracing.StartGetMonitorings(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        var results = new List<Monitoring>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add(ReadMonitoring(reader));
        }

        return results;
    }

    public async Task<IReadOnlyList<Monitoring>> GetNonArchivedMonitoringsAsync(
        CancellationToken cancellationToken = default
    )
    {
        const string sql = """
            SELECT Id, Name, Url, Selector, PhoneNumbers, CreatedAt, PausedAt, ArchivedAt
            FROM Monitorings
            WHERE ArchivedAt IS NULL
            ORDER BY CreatedAt
            """;

        using var activity = Tracing.StartGetMonitorings(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        var results = new List<Monitoring>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add(ReadMonitoring(reader));
        }

        return results;
    }

    public async Task<Monitoring?> GetMonitoringByIdAsync(int id, CancellationToken cancellationToken = default)
    {
        const string sql =
            "SELECT Id, Name, Url, Selector, PhoneNumbers, CreatedAt, PausedAt, ArchivedAt FROM Monitorings WHERE Id = $id";
        using var activity = Tracing.StartGetMonitoringById(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);
        return await reader.ReadAsync(cancellationToken) ? ReadMonitoring(reader) : null;
    }

    public async Task<Monitoring> CreateMonitoringAsync(
        string url,
        string selector,
        string name,
        string[] phoneNumbers,
        DateTimeOffset createdAt,
        CancellationToken cancellationToken = default
    )
    {
        const string sql = """
            INSERT INTO Monitorings (Name, Url, Selector, PhoneNumbers, CreatedAt)
            VALUES ($name, $url, $selector, $phones, $createdAt)
            RETURNING Id, Name, Url, Selector, PhoneNumbers, CreatedAt, PausedAt, ArchivedAt
            """;

        using var activity = Tracing.StartCreateMonitoring(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("$name", name);
        command.Parameters.AddWithValue("$url", url);
        command.Parameters.AddWithValue("$selector", selector);
        command.Parameters.AddWithValue("$phones", PhoneNumbersJson.Serialize(phoneNumbers));
        command.Parameters.AddWithValue("$createdAt", createdAt.ToString("O"));

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        await reader.ReadAsync(cancellationToken);
        return ReadMonitoring(reader);
    }

    public async Task<Monitoring?> UpdateMonitoringAsync(
        int id,
        string url,
        string selector,
        string name,
        string[] phoneNumbers,
        CancellationToken cancellationToken = default
    )
    {
        const string sql = """
            UPDATE Monitorings
            SET Name = $name, Url = $url, Selector = $selector, PhoneNumbers = $phones
            WHERE Id = $id AND ArchivedAt IS NULL
            RETURNING Id, Name, Url, Selector, PhoneNumbers, CreatedAt, PausedAt, ArchivedAt
            """;

        using var activity = Tracing.StartUpdateMonitoring(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);
        command.Parameters.AddWithValue("$name", name);
        command.Parameters.AddWithValue("$url", url);
        command.Parameters.AddWithValue("$selector", selector);
        command.Parameters.AddWithValue("$phones", PhoneNumbersJson.Serialize(phoneNumbers));

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        return await reader.ReadAsync(cancellationToken) ? ReadMonitoring(reader) : null;
    }

    public async Task<bool> ArchiveMonitoringAsync(
        int id,
        DateTimeOffset archivedAt,
        CancellationToken cancellationToken = default
    )
    {
        const string sql = "UPDATE Monitorings SET ArchivedAt = $archivedAt WHERE Id = $id AND ArchivedAt IS NULL";
        using var activity = Tracing.StartArchiveMonitoring(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);
        command.Parameters.AddWithValue("$archivedAt", archivedAt.ToString("O"));
        return await command.ExecuteNonQueryAsync(cancellationToken) > 0;
    }

    public async Task<bool> PauseMonitoringAsync(
        int id,
        DateTimeOffset pausedAt,
        CancellationToken cancellationToken = default
    )
    {
        const string sql = """
            UPDATE Monitorings
            SET PausedAt = $pausedAt
            WHERE Id = $id AND ArchivedAt IS NULL AND PausedAt IS NULL
            """;
        using var activity = Tracing.StartPauseMonitoring(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);
        command.Parameters.AddWithValue("$pausedAt", pausedAt.ToString("O"));
        return await command.ExecuteNonQueryAsync(cancellationToken) > 0;
    }

    public async Task<bool> ResumeMonitoringAsync(int id, CancellationToken cancellationToken = default)
    {
        const string sql = """
            UPDATE Monitorings
            SET PausedAt = NULL
            WHERE Id = $id AND ArchivedAt IS NULL AND PausedAt IS NOT NULL
            """;
        using var activity = Tracing.StartResumeMonitoring(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);
        return await command.ExecuteNonQueryAsync(cancellationToken) > 0;
    }

    public async Task<string?> LatestHtml(int monitorId, CancellationToken cancellationToken)
    {
        const string sql =
            "SELECT Html FROM DetectedHtml WHERE MonitorId = $monitorId 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;
        command.Parameters.AddWithValue("$monitorId", monitorId);
        return await command.ExecuteScalarAsync(cancellationToken) as string;
    }

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

    public async Task<IReadOnlyList<Detection>> GetDetectionsAsync(
        int? monitorId,
        int limit,
        CancellationToken cancellationToken = default
    )
    {
        var sql = monitorId is null
            ? """
                SELECT Id, MonitorId, Html, DetectedAt
                FROM DetectedHtml
                ORDER BY DetectedAt DESC
                LIMIT $limit
                """
            : """
                SELECT Id, MonitorId, Html, DetectedAt
                FROM DetectedHtml
                WHERE MonitorId = $monitorId
                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);
        if (monitorId is not null)
        {
            command.Parameters.AddWithValue("$monitorId", monitorId.Value);
        }

        var results = new List<Detection>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add(ReadDetection(reader));
        }

        return results;
    }

    public async Task<Detection?> GetDetectionByIdAsync(int id, CancellationToken cancellationToken = default)
    {
        const string sql = "SELECT Id, MonitorId, 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);
        return await reader.ReadAsync(cancellationToken) ? ReadDetection(reader) : null;
    }

    public async Task<int> CountRunningMonitoringsAsync(CancellationToken cancellationToken = default)
    {
        const string sql = "SELECT COUNT(*) FROM Monitorings WHERE ArchivedAt IS NULL AND PausedAt IS NULL";
        using var activity = Tracing.StartCountMonitorings(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));
    }

    public async Task<int> CountActiveMonitoringsAsync(CancellationToken cancellationToken = default)
    {
        const string sql = "SELECT COUNT(*) FROM Monitorings WHERE ArchivedAt IS NULL";
        using var activity = Tracing.StartCountMonitorings(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));
    }

    public async Task<AppSettings> GetAppSettingsAsync(CancellationToken cancellationToken = default)
    {
        const string sql = "SELECT IntervalSeconds, DefaultPhoneNumbers FROM AppSettings WHERE Id = 1";
        using var activity = Tracing.StartGetAppSettings(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        if (!await reader.ReadAsync(cancellationToken))
        {
            return new AppSettings(300, []);
        }

        return new AppSettings(reader.GetInt32(0), PhoneNumbersJson.Deserialize(reader.GetString(1)));
    }

    public async Task<AppSettings> UpdateAppSettingsAsync(
        int intervalSeconds,
        string[] defaultPhoneNumbers,
        CancellationToken cancellationToken = default
    )
    {
        const string sql = """
            UPDATE AppSettings
            SET IntervalSeconds = $interval, DefaultPhoneNumbers = $phones
            WHERE Id = 1
            RETURNING IntervalSeconds, DefaultPhoneNumbers
            """;

        using var activity = Tracing.StartUpdateAppSettings(sql);
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(cancellationToken);
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("$interval", Math.Max(60, intervalSeconds));
        command.Parameters.AddWithValue("$phones", PhoneNumbersJson.Serialize(defaultPhoneNumbers));

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        await reader.ReadAsync(cancellationToken);
        return new AppSettings(reader.GetInt32(0), PhoneNumbersJson.Deserialize(reader.GetString(1)));
    }

    private static Monitoring ReadMonitoring(SqliteDataReader reader) =>
        new(
            reader.GetInt32(0),
            reader.GetString(1),
            reader.GetString(2),
            reader.GetString(3),
            PhoneNumbersJson.Deserialize(reader.GetString(4)),
            DateTimeOffset.Parse(reader.GetString(5)),
            reader.IsDBNull(6) ? null : DateTimeOffset.Parse(reader.GetString(6)),
            reader.IsDBNull(7) ? null : DateTimeOffset.Parse(reader.GetString(7))
        );

    private static MonitoringSummary ReadMonitoringSummary(SqliteDataReader reader) =>
        new(
            reader.GetInt32(0),
            reader.GetString(1),
            reader.GetString(2),
            reader.GetString(3),
            PhoneNumbersJson.Deserialize(reader.GetString(4)),
            DateTimeOffset.Parse(reader.GetString(5)),
            reader.IsDBNull(6) ? null : DateTimeOffset.Parse(reader.GetString(6)),
            reader.IsDBNull(7) ? null : DateTimeOffset.Parse(reader.GetString(7)),
            reader.GetInt32(8),
            reader.IsDBNull(9) ? null : DateTimeOffset.Parse(reader.GetString(9))
        );

    private static Detection ReadDetection(SqliteDataReader reader) =>
        new(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), DateTimeOffset.Parse(reader.GetString(3)));
}