server/Database.java - develop - ledart/RRP_LedNik

server/Database.java
283 строки10 KB
Darya
обновлены комментарии
18 дек 2025, 14:46
18 дек 2025, 14:4695c0a7f
package server;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Database {
    private static final String DB_URL = "jdbc:sqlite:database/schema.db";
    private Connection connection;

    public void connect() {
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection(DB_URL);
            System.out.println("[Database] Connected to SQLite");
        } catch (ClassNotFoundException e) {
            System.err.println("[Database] SQLite JDBC driver not found: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("[Database] Connection failed: " + e.getMessage());
        }
    }

    public void init() {
        try {
            connect();
            initTables(); // Создание таблиц при старте
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void initTables() {
        String createPlayersTable = """
            CREATE TABLE IF NOT EXISTS players (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                wins INTEGER DEFAULT 0,
                losses INTEGER DEFAULT 0,
                score INTEGER DEFAULT 0,
                games_played INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """;
        
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(createPlayersTable);
            System.out.println("[Database] Players table initialized");
        } catch (SQLException e) {
            System.err.println("[Database] Error creating tables: " + e.getMessage());
        }
    }

    // Регистрация пользователя
    public boolean registerUser(String username, String password) {
        if (username == null || password == null) {
            return false;
        }
        
        username = username.trim();
        
        if (username.isEmpty() || password.isEmpty()) {
            return false;
        }
        
        // Хэшируем пароль
        String passwordHash = hashPassword(password);
        if (passwordHash == null) {
            return false;
        }
        
        String sql = "INSERT INTO players (username, password_hash) VALUES (?, ?)";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, username);
            pstmt.setString(2, passwordHash);
            pstmt.executeUpdate();
            System.out.println("[Database] User registered: " + username);
            return true;
        } catch (SQLException e) {
            System.err.println("[Database] Error registering user: " + e.getMessage());
            return false;
        }
    }

    // Аутентификация пользователя
    public boolean authenticateUser(String username, String password) {
        if (username == null || password == null) {
            return false;
        }
        
        username = username.trim();
        
        String sql = "SELECT password_hash FROM players WHERE username = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                String storedHash = rs.getString("password_hash");
                return checkPassword(password, storedHash);
            }
            return false; // Пользователь не найден
            
        } catch (SQLException e) {
            System.err.println("[Database] Error authenticating user: " + e.getMessage());
            return false;
        }
    }

    // Проверка существования пользователя
    public boolean userExists(String username) {
        if (username == null) {
            return false;
        }
        
        username = username.trim();
        String sql = "SELECT COUNT(*) FROM players WHERE username = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
            
        } catch (SQLException e) {
            System.err.println("[Database] Error checking user existence: " + e.getMessage());
            return false;
        }
    }


    // Приватные методы для хэширования паролей
    private String hashPassword(String password) {
        try {
            // Используем SHA-256 
            java.security.MessageDigest md = java.security.MessageDigest.getInstance("SHA-256");
            byte[] hash = md.digest(password.getBytes());
            StringBuilder hexString = new StringBuilder();
            for (byte b : hash) {
                String hex = Integer.toHexString(0xff & b);
                if (hex.length() == 1) hexString.append('0');
                hexString.append(hex);
            }
            return hexString.toString();
        } catch (java.security.NoSuchAlgorithmException e) {
            System.err.println("[Database] SHA-256 algorithm not available: " + e.getMessage());
            return null;
        }
    }

    private boolean checkPassword(String password, String storedHash) {
        String hashedPassword = hashPassword(password);
        if (hashedPassword == null) {
            return false;
        }
        return hashedPassword.equals(storedHash);
    }

    public void updatePlayerStats(String winner, String loser) {
        try {
            connection.setAutoCommit(false);
            
            String winSql = """
                UPDATE players 
                SET wins = wins + 1, 
                    games_played = games_played + 1,
                    score = score + 1
                WHERE username = ?
            """;
            
            try (PreparedStatement pstmt = connection.prepareStatement(winSql)) {
                pstmt.setString(1, winner);
                pstmt.executeUpdate();
            }
            
            if (loser != null && !loser.isEmpty() && !loser.startsWith("Bot")) {
                String loseSql = """
                    UPDATE players 
                    SET losses = losses + 1, 
                        games_played = games_played + 1
                    WHERE username = ?
                """;
                
                try (PreparedStatement pstmt = connection.prepareStatement(loseSql)) {
                    pstmt.setString(1, loser);
                    pstmt.executeUpdate();
                }
            }
            
            connection.commit();
            System.out.println("[Database] " + winner + " +1 point");
            
        } catch (SQLException e) {
            try { connection.rollback(); } catch (SQLException ex) {}
            System.err.println("[Database] Error updating stats: " + e.getMessage());
        } finally {
            try { connection.setAutoCommit(true); } catch (SQLException e) {}
        }
    }
    
    public void updatePlayerStatsDirect(String username, int newScore) {
        String sql = """
            UPDATE players 
            SET score = ?
            WHERE username = ? AND ? > score
        """;
        // Если текущий score в БД уже больше - обновления не будет. 
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, newScore);
            pstmt.setString(2, username);
            pstmt.setInt(3, newScore);
            int rows = pstmt.executeUpdate();
            
            if (rows > 0) {
                System.out.println("[Database] Updated best score for " + username + " to " + newScore);
            }
        } catch (SQLException e) {
            System.err.println("[Database] Error updating direct score: " + e.getMessage());
        }
    }
    
    public List<String[]> getTopScores(int limit) {
        List<String[]> scores = new ArrayList<>();
        String sql = """
            SELECT username, score,
                   strftime('%Y-%m-%d %H:%M', created_at) as date
            FROM players 
            WHERE score > 0
            ORDER BY score DESC 
            LIMIT ?
        """;
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, limit);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                String[] row = {
                    rs.getString("username"),
                    String.valueOf(rs.getInt("score")),
                    rs.getString("date")
                };
                scores.add(row);
            }
        } catch (SQLException e) {
            System.err.println("[Database] Error getting top scores: " + e.getMessage());
        }
        return scores;
    }

    public Connection getConnection() {
        return connection;
    }

    public void close() {
        try {
            if (connection != null) connection.close();
            System.out.println("[Database] Connection closed");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int getPlayerScore(String username) {
        String sql = "SELECT score FROM players WHERE username = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("score");
            }
        } catch (SQLException e) {
            System.err.println("[Database] Error getting player score: " + e.getMessage());
        }
        return 0;
    }
}