LEA2/CheckersSpielBot/DatabaseService.cs

using System.IO;
using Microsoft.Data.Sqlite;

namespace CheckersSpielBot
{
    public class DatabaseService
    {
        private readonly string _connectionString;

        public DatabaseService()
        {
            string dbPath = Path.Combine(
                AppDomain.CurrentDomain.BaseDirectory, "checkers.db");

            _connectionString = $"Data Source={dbPath}";

            InitializeSchema();
        }

        private void InitializeSchema()
        {
            using var conn = new SqliteConnection(_connectionString);
            conn.Open();

            var cmd = conn.CreateCommand();
            cmd.CommandText = @"
                CREATE TABLE IF NOT EXISTS players (
                    id         INTEGER PRIMARY KEY AUTOINCREMENT,
                    username   TEXT NOT NULL UNIQUE,
                    password   TEXT NOT NULL,
                    created_at TEXT NOT NULL DEFAULT (datetime('now'))
                );

                CREATE TABLE IF NOT EXISTS gameHistory (
                    id        INTEGER PRIMARY KEY AUTOINCREMENT,
                    playerId  INTEGER NOT NULL,
                    startedAt TEXT NOT NULL DEFAULT (datetime('now')),
                    endedAt   TEXT NULL,
                    winner    TEXT NULL,
                    wentFirst TEXT NOT NULL DEFAULT 'player',
                    FOREIGN KEY (playerId) REFERENCES players(id)
                );";
            cmd.ExecuteNonQuery();
        }

        public bool RegisterPlayer(string username, string password, out string error)
        {
            error = string.Empty;
            try
            {
                using var conn = new SqliteConnection(_connectionString);
                conn.Open();

                var check = conn.CreateCommand();
                check.CommandText = "SELECT COUNT(*) FROM players WHERE username = @u";
                check.Parameters.AddWithValue("@u", username);
                long exists = (long)check.ExecuteScalar()!;

                if (exists > 0)
                {
                    error = "Username already exists.";
                    return false;
                }

                string hashed = BCrypt.Net.BCrypt.HashPassword(password);

                var cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO players (username, password) VALUES (@u, @p)";
                cmd.Parameters.AddWithValue("@u", username);
                cmd.Parameters.AddWithValue("@p", hashed);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
        }

        public bool LoginPlayer(string username, string password,
                                out int playerId, out string error)
        {
            playerId = -1;
            error = string.Empty;
            try
            {
                using var conn = new SqliteConnection(_connectionString);
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT id, password FROM players WHERE username = @u";
                cmd.Parameters.AddWithValue("@u", username);

                using var reader = cmd.ExecuteReader();
                if (!reader.Read())
                {
                    error = "Username not found.";
                    return false;
                }

                int id = reader.GetInt32(0);
                string stored = reader.GetString(1);

                if (!BCrypt.Net.BCrypt.Verify(password, stored))
                {
                    error = "Incorrect password.";
                    return false;
                }

                playerId = id;
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
        }

        public int StartGame(int playerId, string wentFirst)
        {
            try
            {
                using var conn = new SqliteConnection(_connectionString);
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = @"
                    INSERT INTO gameHistory (playerId, startedAt, wentFirst)
                    VALUES (@pid, datetime('now'), @wf);
                    SELECT last_insert_rowid();";
                cmd.Parameters.AddWithValue("@pid", playerId);
                cmd.Parameters.AddWithValue("@wf", wentFirst);

                return Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch { return -1; }
        }

        public void EndGame(int gameId, string winner)
        {
            try
            {
                using var conn = new SqliteConnection(_connectionString);
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = @"
                    UPDATE gameHistory
                    SET endedAt = datetime('now'), winner = @w
                    WHERE id = @id";
                cmd.Parameters.AddWithValue("@w", winner);
                cmd.Parameters.AddWithValue("@id", gameId);
                cmd.ExecuteNonQuery();
            }
            catch { }
        }

        public List<GameHistoryEntry> GetHistory(int playerId)
        {
            var list = new List<GameHistoryEntry>();
            try
            {
                using var conn = new SqliteConnection(_connectionString);
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = @"
                    SELECT id, startedAt, endedAt, winner, wentFirst
                    FROM gameHistory
                    WHERE playerId = @pid
                    ORDER BY startedAt DESC";
                cmd.Parameters.AddWithValue("@pid", playerId);

                using var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var entry = new GameHistoryEntry
                    {
                        Id = reader.GetInt32(0),
                        StartedAt = DateTime.Parse(reader.GetString(1)),
                        EndedAt = reader.IsDBNull(2)
                                        ? null
                                        : DateTime.Parse(reader.GetString(2)),
                        Winner = reader.IsDBNull(3) ? "—" : reader.GetString(3),
                        WentFirst = reader.GetString(4)
                    };
                    list.Add(entry);
                }
            }
            catch { }
            return list;
        }
    }

    public class GameHistoryEntry
    {
        public int Id { get; set; }
        public DateTime StartedAt { get; set; }
        public DateTime? EndedAt { get; set; }
        public string Winner { get; set; } = "—";
        public string WentFirst { get; set; } = "—";

        public string Duration => EndedAt.HasValue
            ? $"{(EndedAt.Value - StartedAt).TotalMinutes:F0} min"
            : "In progress";
    }
}