website/Services/SqliteGameService.cs

using System.Data.Common;
using Microsoft.Data.Sqlite;
using Phenix.Models;

namespace Phenix.Services;

public class SqliteGameService : IGameService
{
    private readonly string _connectionString;

    public SqliteGameService(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task Init()
    {
        await using var conn = new SqliteConnection(_connectionString);
        await conn.OpenAsync();
        var cmd = conn.CreateCommand();
        cmd.CommandText = """
            CREATE TABLE IF NOT EXISTS games (
                id TEXT PRIMARY KEY,
                date TEXT NOT NULL,
                time TEXT NOT NULL,
                home TEXT NOT NULL,
                away TEXT NOT NULL,
                year INTEGER NOT NULL,
                home_score INTEGER,
                away_score INTEGER,
                practice INTEGER NOT NULL DEFAULT 0,
                rain INTEGER NOT NULL DEFAULT 0,
                UNIQUE(date, time)
            )
        """;
        await cmd.ExecuteNonQueryAsync();
    }

    public async Task<List<Game>> GetAllGames()
    {
        var games = new List<Game>();
        await using var conn = new SqliteConnection(_connectionString);
        await conn.OpenAsync();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT * FROM games ORDER BY date, time";
        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
            games.Add(ReadGame(reader));
        return games;
    }

    public async Task<Game?> GetGameById(string id)
    {
        await using var conn = new SqliteConnection(_connectionString);
        await conn.OpenAsync();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT * FROM games WHERE id = @id";
        cmd.Parameters.AddWithValue("@id", id);
        await using var reader = await cmd.ExecuteReaderAsync();
        if (await reader.ReadAsync())
            return ReadGame(reader);
        return null;
    }

    public async Task<(Game? Game, string Error)> CreateNewGame(string date, string time, string home, string away)
    {
        date = CleanDate(date);
        time = CleanTime(time);

        if (!DateOnly.TryParse(date, out _))
            return (null, $"Invalide date format {date}");
        if (!TimeOnly.TryParse(time, out _))
            return (null, $"Invalide time format {time}");
        if (!ValidTeams.Contains(away))
            return (null, $"Invalide away format {away}");
        if (!ValidTeams.Contains(home))
            return (null, $"Invalide home format {home}");
        if (home == away)
            return (null, $"Home and away teams must be different (received {home} for both)");

        var year = DateOnly.Parse(date).Year;
        var game = new Game
        {
            Id = Guid.NewGuid().ToString(),
            Date = date,
            Time = time,
            Home = home,
            Away = away,
            Year = year,
        };

        try
        {
            await using var conn = new SqliteConnection(_connectionString);
            await conn.OpenAsync();
            var cmd = conn.CreateCommand();
            cmd.CommandText = """
                INSERT INTO games (id, date, time, home, away, year)
                VALUES (@id, @date, @time, @home, @away, @year)
            """;
            cmd.Parameters.AddWithValue("@id", game.Id);
            cmd.Parameters.AddWithValue("@date", game.Date);
            cmd.Parameters.AddWithValue("@time", game.Time);
            cmd.Parameters.AddWithValue("@home", game.Home);
            cmd.Parameters.AddWithValue("@away", game.Away);
            cmd.Parameters.AddWithValue("@year", game.Year);
            await cmd.ExecuteNonQueryAsync();
        }
        catch (SqliteException ex) when (ex.SqliteErrorCode == 19)
        {
            return (null, "game already exists at this date/time");
        }

        return (game, "");
    }

    public async Task<bool> UpdateGame(string id, int? homeScore, int? awayScore, bool? rain, bool? practice)
    {
        await using var conn = new SqliteConnection(_connectionString);
        await conn.OpenAsync();
        var cmd = conn.CreateCommand();
        cmd.CommandText = """
            UPDATE games SET
                home_score = COALESCE(@homeScore, home_score),
                away_score = COALESCE(@awayScore, away_score),
                rain = COALESCE(@rain, rain),
                practice = COALESCE(@practice, practice)
            WHERE id = @id
        """;
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@homeScore", (object?)homeScore ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@awayScore", (object?)awayScore ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@rain", rain ?? (object?)DBNull.Value);
        cmd.Parameters.AddWithValue("@practice", practice ?? (object?)DBNull.Value);
        return await cmd.ExecuteNonQueryAsync() > 0;
    }

    public async Task<bool> DeleteGameById(string id)
    {
        await using var conn = new SqliteConnection(_connectionString);
        await conn.OpenAsync();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "DELETE FROM games WHERE id = @id";
        cmd.Parameters.AddWithValue("@id", id);
        return await cmd.ExecuteNonQueryAsync() > 0;
    }

    public async Task ClearAllGames()
    {
        await using var conn = new SqliteConnection(_connectionString);
        await conn.OpenAsync();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "DELETE FROM games";
        await cmd.ExecuteNonQueryAsync();
    }

    public async Task<bool> SwitchVisitors(string id)
    {
        var game = await GetGameById(id);
        if (game is null) return false;

        await using var conn = new SqliteConnection(_connectionString);
        await conn.OpenAsync();
        var cmd = conn.CreateCommand();
        cmd.CommandText = """
            UPDATE games SET home = @home, away = @away,
                home_score = @homeScore, away_score = @awayScore
            WHERE id = @id
        """;
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@home", game.Away);
        cmd.Parameters.AddWithValue("@away", game.Home);
        cmd.Parameters.AddWithValue("@homeScore", (object?)game.AwayScore ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@awayScore", (object?)game.HomeScore ?? DBNull.Value);
        return await cmd.ExecuteNonQueryAsync() > 0;
    }

    private static Game ReadGame(DbDataReader r) => new()
    {
        Id = r.GetString(r.GetOrdinal("id")),
        Date = r.GetString(r.GetOrdinal("date")),
        Time = r.GetString(r.GetOrdinal("time")),
        Home = r.GetString(r.GetOrdinal("home")),
        Away = r.GetString(r.GetOrdinal("away")),
        Year = r.GetInt32(r.GetOrdinal("year")),
        HomeScore = r.IsDBNull(r.GetOrdinal("home_score")) ? null : r.GetInt32(r.GetOrdinal("home_score")),
        AwayScore = r.IsDBNull(r.GetOrdinal("away_score")) ? null : r.GetInt32(r.GetOrdinal("away_score")),
        Practice = r.GetBoolean(r.GetOrdinal("practice")),
        Rain = r.GetBoolean(r.GetOrdinal("rain")),
    };

    private static readonly string[] ValidTeams =
        ["jaune", "rouge", "bleu", "gris", "vert", "noir", "orange", "mauve"];

    private static string CleanDate(string raw)
    {
        var parts = raw.Split('-');
        if (parts.Length != 3) return raw;
        return $"{parts[0]}-{parts[1].PadLeft(2, '0')}-{parts[2].PadLeft(2, '0')}";
    }

    private static string CleanTime(string raw)
    {
        var sep = raw.Contains('h') ? 'h' : ':';
        var parts = raw.Split(sep);
        if (parts.Length != 2) return raw;
        return $"{parts[0].PadLeft(2, '0')}:{parts[1].PadLeft(2, '0')}";
    }
}