package com.bltzteams.infrastructure.adapter.out; import com.bltzteams.domain.model.*; import com.bltzteams.domain.repository.TeamRepository; import java.math.BigDecimal; import java.sql.*; import java.time.Instant; import java.util.*; import java.util.concurrent.CompletableFuture; import java.util.concurrent.ConcurrentHashMap; public class SqliteTeamRepository implements TeamRepository { private final String jdbcUrl; private final Map cache = new ConcurrentHashMap<>(); public SqliteTeamRepository(String filePath) { this.jdbcUrl = "jdbc:sqlite:" + filePath; initializeDatabase(); } private void initializeDatabase() { try (Connection conn = getConnection(); Statement stmt = conn.createStatement()) { stmt.execute(""" CREATE TABLE IF NOT EXISTS teams ( id TEXT PRIMARY KEY, name TEXT UNIQUE NOT NULL, tag TEXT NOT NULL, color TEXT NOT NULL, prefix TEXT NOT NULL, description TEXT NOT NULL, motd TEXT NOT NULL, created_at INTEGER NOT NULL, balance REAL NOT NULL, is_public INTEGER NOT NULL, friendly_fire INTEGER NOT NULL, ally_fire INTEGER NOT NULL, password TEXT, max_members INTEGER NOT NULL, max_warps INTEGER NOT NULL, max_allies INTEGER NOT NULL ) """); stmt.execute(""" CREATE TABLE IF NOT EXISTS team_members ( team_id TEXT NOT NULL, player_id TEXT NOT NULL, role TEXT NOT NULL, PRIMARY KEY (team_id, player_id), FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE ) """); stmt.execute(""" CREATE TABLE IF NOT EXISTS team_allies ( team_id TEXT NOT NULL, ally_id TEXT NOT NULL, PRIMARY KEY (team_id, ally_id), FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE ) """); stmt.execute(""" CREATE TABLE IF NOT EXISTS team_enemies ( team_id TEXT NOT NULL, enemy_id TEXT NOT NULL, PRIMARY KEY (team_id, enemy_id), FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE ) """); stmt.execute(""" CREATE TABLE IF NOT EXISTS team_warps ( team_id TEXT NOT NULL, name TEXT NOT NULL, world TEXT NOT NULL, x REAL NOT NULL, y REAL NOT NULL, z REAL NOT NULL, yaw REAL NOT NULL, pitch REAL NOT NULL, password TEXT, PRIMARY KEY (team_id, name), FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE ) """); stmt.execute(""" CREATE TABLE IF NOT EXISTS team_homes ( team_id TEXT PRIMARY KEY, world TEXT NOT NULL, x REAL NOT NULL, y REAL NOT NULL, z REAL NOT NULL, yaw REAL NOT NULL, pitch REAL NOT NULL, FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE ) """); } catch (SQLException e) { throw new RuntimeException("Failed to initialize database", e); } } private Connection getConnection() throws SQLException { return DriverManager.getConnection(jdbcUrl); } @Override public CompletableFuture> findById(TeamId id) { return CompletableFuture.supplyAsync(() -> { if (cache.containsKey(id)) { return Optional.of(cache.get(id)); } try (Connection conn = getConnection()) { return loadTeam(conn, id); } catch (SQLException e) { throw new RuntimeException("Failed to find team by id", e); } }); } @Override public CompletableFuture> findByName(String name) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement( "SELECT id FROM teams WHERE name = ?")) { ps.setString(1, name); ResultSet rs = ps.executeQuery(); if (rs.next()) { TeamId teamId = TeamId.from(rs.getString("id")); return loadTeam(conn, teamId); } return Optional.empty(); } catch (SQLException e) { throw new RuntimeException("Failed to find team by name", e); } }); } @Override public CompletableFuture> findByPlayer(PlayerId playerId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement( "SELECT team_id FROM team_members WHERE player_id = ?")) { ps.setString(1, playerId.value().toString()); ResultSet rs = ps.executeQuery(); if (rs.next()) { TeamId teamId = TeamId.from(rs.getString("team_id")); return loadTeam(conn, teamId); } return Optional.empty(); } catch (SQLException e) { throw new RuntimeException("Failed to find team by player", e); } }); } @Override public CompletableFuture> findAll() { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement("SELECT id FROM teams")) { List teams = new ArrayList<>(); ResultSet rs = ps.executeQuery(); while (rs.next()) { TeamId teamId = TeamId.from(rs.getString("id")); loadTeam(conn, teamId).ifPresent(teams::add); } return teams; } catch (SQLException e) { throw new RuntimeException("Failed to find all teams", e); } }); } @Override public CompletableFuture save(Team team) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection()) { conn.setAutoCommit(false); try { saveTeamData(conn, team); saveMembers(conn, team); saveAllies(conn, team); saveEnemies(conn, team); saveWarps(conn, team); saveHome(conn, team); conn.commit(); cache.put(team.getId(), team); } catch (SQLException e) { conn.rollback(); throw e; } } catch (SQLException e) { throw new RuntimeException("Failed to save team", e); } }); } @Override public CompletableFuture delete(TeamId id) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement("DELETE FROM teams WHERE id = ?")) { ps.setString(1, id.value().toString()); ps.executeUpdate(); cache.remove(id); } catch (SQLException e) { throw new RuntimeException("Failed to delete team", e); } }); } @Override public CompletableFuture existsByName(String name) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement( "SELECT 1 FROM teams WHERE name = ?")) { ps.setString(1, name); ResultSet rs = ps.executeQuery(); return rs.next(); } catch (SQLException e) { throw new RuntimeException("Failed to check team existence", e); } }); } @Override public CompletableFuture> findTopByBalance(int limit) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement( "SELECT id FROM teams ORDER BY balance DESC LIMIT ?")) { ps.setInt(1, limit); List teams = new ArrayList<>(); ResultSet rs = ps.executeQuery(); while (rs.next()) { TeamId teamId = TeamId.from(rs.getString("id")); loadTeam(conn, teamId).ifPresent(teams::add); } return teams; } catch (SQLException e) { throw new RuntimeException("Failed to find top teams", e); } }); } private Optional loadTeam(Connection conn, TeamId id) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( "SELECT * FROM teams WHERE id = ?")) { ps.setString(1, id.value().toString()); ResultSet rs = ps.executeQuery(); if (!rs.next()) { return Optional.empty(); } PlayerId owner = loadOwner(conn, id); Team team = new Team(id, rs.getString("name"), rs.getString("tag"), owner); team.setColor(rs.getString("color")); team.setPrefix(rs.getString("prefix")); team.setDescription(rs.getString("description")); team.setMotd(rs.getString("motd")); team.deposit(Money.of(rs.getDouble("balance"))); team.setPublic(rs.getBoolean("is_public")); team.setFriendlyFire(rs.getBoolean("friendly_fire")); team.setAllyFire(rs.getBoolean("ally_fire")); String password = rs.getString("password"); team.setPassword(Optional.ofNullable(password)); team.setMaxMembers(rs.getInt("max_members")); team.setMaxWarps(rs.getInt("max_warps")); team.setMaxAllies(rs.getInt("max_allies")); loadMembers(conn, team); loadAllies(conn, team); loadEnemies(conn, team); loadWarps(conn, team); loadHome(conn, team); return Optional.of(team); } } private PlayerId loadOwner(Connection conn, TeamId teamId) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( "SELECT player_id FROM team_members WHERE team_id = ? AND role = ?")) { ps.setString(1, teamId.value().toString()); ps.setString(2, TeamRole.OWNER.name()); ResultSet rs = ps.executeQuery(); if (rs.next()) { return PlayerId.from(rs.getString("player_id")); } throw new IllegalStateException("Team has no owner"); } } private void loadMembers(Connection conn, Team team) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( "SELECT player_id, role FROM team_members WHERE team_id = ?")) { ps.setString(1, team.getId().value().toString()); ResultSet rs = ps.executeQuery(); while (rs.next()) { PlayerId playerId = PlayerId.from(rs.getString("player_id")); TeamRole role = TeamRole.valueOf(rs.getString("role")); if (role != TeamRole.OWNER) { team.addMember(playerId, role); } } } } private void loadAllies(Connection conn, Team team) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( "SELECT ally_id FROM team_allies WHERE team_id = ?")) { ps.setString(1, team.getId().value().toString()); ResultSet rs = ps.executeQuery(); while (rs.next()) { team.addAlly(TeamId.from(rs.getString("ally_id"))); } } } private void loadEnemies(Connection conn, Team team) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( "SELECT enemy_id FROM team_enemies WHERE team_id = ?")) { ps.setString(1, team.getId().value().toString()); ResultSet rs = ps.executeQuery(); while (rs.next()) { team.addEnemy(TeamId.from(rs.getString("enemy_id"))); } } } private void loadWarps(Connection conn, Team team) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( "SELECT * FROM team_warps WHERE team_id = ?")) { ps.setString(1, team.getId().value().toString()); ResultSet rs = ps.executeQuery(); while (rs.next()) { TeamLocation location = new TeamLocation( rs.getString("world"), rs.getDouble("x"), rs.getDouble("y"), rs.getDouble("z"), rs.getFloat("yaw"), rs.getFloat("pitch") ); String password = rs.getString("password"); TeamWarp warp = new TeamWarp( rs.getString("name"), location, Optional.ofNullable(password) ); team.addWarp(warp); } } } private void loadHome(Connection conn, Team team) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( "SELECT * FROM team_homes WHERE team_id = ?")) { ps.setString(1, team.getId().value().toString()); ResultSet rs = ps.executeQuery(); if (rs.next()) { TeamLocation home = new TeamLocation( rs.getString("world"), rs.getDouble("x"), rs.getDouble("y"), rs.getDouble("z"), rs.getFloat("yaw"), rs.getFloat("pitch") ); team.setHome(home); } } } private void saveTeamData(Connection conn, Team team) throws SQLException { try (PreparedStatement ps = conn.prepareStatement( """ INSERT OR REPLACE INTO teams (id, name, tag, color, prefix, description, motd, created_at, balance, is_public, friendly_fire, ally_fire, password, max_members, max_warps, max_allies) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """)) { ps.setString(1, team.getId().value().toString()); ps.setString(2, team.getName()); ps.setString(3, team.getTag()); ps.setString(4, team.getColor()); ps.setString(5, team.getPrefix()); ps.setString(6, team.getDescription()); ps.setString(7, team.getMotd()); ps.setLong(8, team.getCreatedAt().getEpochSecond()); ps.setDouble(9, team.getBalance().doubleValue()); ps.setBoolean(10, team.isPublic()); ps.setBoolean(11, team.isFriendlyFire()); ps.setBoolean(12, team.isAllyFire()); ps.setString(13, team.getPassword().orElse(null)); ps.setInt(14, team.getMaxMembers()); ps.setInt(15, team.getMaxWarps()); ps.setInt(16, team.getMaxAllies()); ps.executeUpdate(); } } private void saveMembers(Connection conn, Team team) throws SQLException { try (PreparedStatement delete = conn.prepareStatement( "DELETE FROM team_members WHERE team_id = ?"); PreparedStatement insert = conn.prepareStatement( "INSERT INTO team_members (team_id, player_id, role) VALUES (?, ?, ?)")) { delete.setString(1, team.getId().value().toString()); delete.executeUpdate(); for (Map.Entry entry : team.getAllMembers()) { insert.setString(1, team.getId().value().toString()); insert.setString(2, entry.getKey().value().toString()); insert.setString(3, entry.getValue().name()); insert.addBatch(); } insert.executeBatch(); } } private void saveAllies(Connection conn, Team team) throws SQLException { try (PreparedStatement delete = conn.prepareStatement( "DELETE FROM team_allies WHERE team_id = ?"); PreparedStatement insert = conn.prepareStatement( "INSERT INTO team_allies (team_id, ally_id) VALUES (?, ?)")) { delete.setString(1, team.getId().value().toString()); delete.executeUpdate(); for (TeamId allyId : team.getAllAllies()) { insert.setString(1, team.getId().value().toString()); insert.setString(2, allyId.value().toString()); insert.addBatch(); } insert.executeBatch(); } } private void saveEnemies(Connection conn, Team team) throws SQLException { try (PreparedStatement delete = conn.prepareStatement( "DELETE FROM team_enemies WHERE team_id = ?"); PreparedStatement insert = conn.prepareStatement( "INSERT INTO team_enemies (team_id, enemy_id) VALUES (?, ?)")) { delete.setString(1, team.getId().value().toString()); delete.executeUpdate(); for (TeamId enemyId : team.getAllEnemies()) { insert.setString(1, team.getId().value().toString()); insert.setString(2, enemyId.value().toString()); insert.addBatch(); } insert.executeBatch(); } } private void saveWarps(Connection conn, Team team) throws SQLException { try (PreparedStatement delete = conn.prepareStatement( "DELETE FROM team_warps WHERE team_id = ?"); PreparedStatement insert = conn.prepareStatement( """ INSERT INTO team_warps (team_id, name, world, x, y, z, yaw, pitch, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) """)) { delete.setString(1, team.getId().value().toString()); delete.executeUpdate(); for (TeamWarp warp : team.getAllWarps()) { insert.setString(1, team.getId().value().toString()); insert.setString(2, warp.name()); insert.setString(3, warp.location().world()); insert.setDouble(4, warp.location().x()); insert.setDouble(5, warp.location().y()); insert.setDouble(6, warp.location().z()); insert.setFloat(7, warp.location().yaw()); insert.setFloat(8, warp.location().pitch()); insert.setString(9, warp.password().orElse(null)); insert.addBatch(); } insert.executeBatch(); } } private void saveHome(Connection conn, Team team) throws SQLException { try (PreparedStatement delete = conn.prepareStatement( "DELETE FROM team_homes WHERE team_id = ?"); PreparedStatement insert = conn.prepareStatement( "INSERT INTO team_homes (team_id, world, x, y, z, yaw, pitch) VALUES (?, ?, ?, ?, ?, ?, ?)")) { delete.setString(1, team.getId().value().toString()); delete.executeUpdate(); team.getHome().ifPresent(home -> { try { insert.setString(1, team.getId().value().toString()); insert.setString(2, home.world()); insert.setDouble(3, home.x()); insert.setDouble(4, home.y()); insert.setDouble(5, home.z()); insert.setFloat(6, home.yaw()); insert.setFloat(7, home.pitch()); insert.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } }); } } }