PersonalAuthMod/DatabaseManager.cs

using System.Security.Cryptography;
using System.Text;
using Npgsql;
using SPTarkov.DI;
using SPTarkov.DI.Annotations;

namespace PersonalAuthMod;

[Injectable]
public class DatabaseManager
{
    private readonly AuthConfig _config;
    private readonly string _connectionString;

    public DatabaseManager()
    {
        _config = AuthConfig.Load();
        _connectionString = $"Host={_config.DbUrl};Port={_config.DbPort};Username={_config.DbUser};Password={_config.DbPassword};Database={_config.DbName}"; 
        Console.WriteLine($"[PersonalAuthMod] DB Manager connecting to: Host={_config.DbUrl}, Port={_config.DbPort}, User={_config.DbUser}, DB={_config.DbName}");
    }

    public void Initialize()
    {
        try
        {
            using var conn = new NpgsqlConnection(_connectionString);
            conn.Open();

            // Create Users Table
            using (var cmd = new NpgsqlCommand(@"
                CREATE TABLE IF NOT EXISTS users (
                    id SERIAL PRIMARY KEY,
                    username VARCHAR(50) UNIQUE NOT NULL,
                    password_hash TEXT NOT NULL,
                    salt TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );", conn))
            {
                cmd.ExecuteNonQuery();
            }

            // Create Sessions Table
            using (var cmd = new NpgsqlCommand(@"
                CREATE TABLE IF NOT EXISTS sessions (
                    session_id VARCHAR(100) PRIMARY KEY,
                    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );", conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            // Log error (using Console for now as we don't have logger injected here yet, or we can use DI)
            Console.WriteLine($"[PersonalAuthMod] Database Initialization Failed: {ex.Message}");
        }
    }

    public bool RegisterUser(string username, string password)
    {
        try
        {
            using var conn = new NpgsqlConnection(_connectionString);
            conn.Open();

            // Check if user exists
            using (var checkCmd = new NpgsqlCommand("SELECT COUNT(*) FROM users WHERE username = @u", conn))
            {
                checkCmd.Parameters.AddWithValue("u", username);
                var count = (long)checkCmd.ExecuteScalar();
                if (count > 0) return false;
            }

            // Hash Password
            var salt = GenerateSalt();
            var hash = HashPassword(password, salt);

            // Insert User
            using (var cmd = new NpgsqlCommand("INSERT INTO users (username, password_hash, salt) VALUES (@u, @p, @s)", conn))
            {
                cmd.Parameters.AddWithValue("u", username);
                cmd.Parameters.AddWithValue("p", hash);
                cmd.Parameters.AddWithValue("s", salt);
                cmd.ExecuteNonQuery();
            }

            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[PersonalAuthMod] RegisterUser Failed: {ex.Message}");
            return false;
        }
    }

    public string? LoginUser(string username, string password)
    {
        try
        {
            using var conn = new NpgsqlConnection(_connectionString);
            conn.Open();

            int userId;
            string storedHash, storedSalt;

            using (var cmd = new NpgsqlCommand("SELECT id, password_hash, salt FROM users WHERE username = @u", conn))
            {
                cmd.Parameters.AddWithValue("u", username);
                using var reader = cmd.ExecuteReader();
                if (!reader.Read()) return null; // User not found

                userId = reader.GetInt32(0);
                storedHash = reader.GetString(1);
                storedSalt = reader.GetString(2);
            }

            var hash = HashPassword(password, storedSalt);
            if (hash != storedHash) return null; // Wrong password

            // Generate Session (Must be 24-character hex for MongoId compatibility)
            var sessionBytes = new byte[12];
            using (var rng = RandomNumberGenerator.Create())
            {
                rng.GetBytes(sessionBytes);
            }
            var sessionId = Convert.ToHexString(sessionBytes).ToLower();

            // Invalidate old sessions for this user? Requirement: "block existing login sessions".
            using (var delCmd = new NpgsqlCommand("DELETE FROM sessions WHERE user_id = @uid", conn))
            {
                delCmd.Parameters.AddWithValue("uid", userId);
                delCmd.ExecuteNonQuery();
            }

            using (var insertCmd = new NpgsqlCommand("INSERT INTO sessions (session_id, user_id) VALUES (@sid, @uid)", conn))
            {
                insertCmd.Parameters.AddWithValue("sid", sessionId);
                insertCmd.Parameters.AddWithValue("uid", userId);
                insertCmd.ExecuteNonQuery();
            }

            return sessionId;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[PersonalAuthMod] LoginUser Failed: {ex.Message}");
            return null;
        }
    }

    public bool ValidateSession(string sessionId)
    {
        if (string.IsNullOrEmpty(sessionId)) return false;

        try
        {
            using var conn = new NpgsqlConnection(_connectionString);
            conn.Open();

            using (var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM sessions WHERE session_id = @sid", conn))
            {
                cmd.Parameters.AddWithValue("sid", sessionId);
                var count = (long)cmd.ExecuteScalar();
                return count > 0;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[PersonalAuthMod] ValidateSession Failed: {ex.Message}");
            return false;
        }
    }

    public string? GetUsernameBySession(string sessionId)
    {
        try
        {
            using var conn = new NpgsqlConnection(_connectionString);
            conn.Open();

            using (var cmd = new NpgsqlCommand(@"
                SELECT u.username 
                FROM sessions s
                JOIN users u ON s.user_id = u.id
                WHERE s.session_id = @sid", conn))
            {
                cmd.Parameters.AddWithValue("sid", sessionId);
                return cmd.ExecuteScalar() as string;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[PersonalAuthMod] GetUsernameBySession Failed: {ex.Message}");
            return null;
        }
    }

    private string GenerateSalt()
    {
        var bytes = new byte[16];
        using (var rng = RandomNumberGenerator.Create())
        {
            rng.GetBytes(bytes);
        }
        return Convert.ToBase64String(bytes);
    }

    private string HashPassword(string password, string salt)
    {
        // Simple SHA256 with salt. Pepper is mentioned in requirements but simplified here to salt.
        // If pepper is strictly required, we can add a hardcoded string constant.
        var pepper = "spt-server-pepper"; 
        using var sha256 = SHA256.Create();
        var combined = password + salt + pepper;
        var bytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(combined));
        return Convert.ToBase64String(bytes);
    }
}