1
public interface ConnectionProvider {
2
    DataSource dataSource();
3

4
    static Properties loadProperties(String db) {
5
        Properties fitxer = new Properties();
6
        try (FileInputStream fis = new FileInputStream("db/config.properties")) {
7
            fitxer.load(fis);
8
        } catch (IOException e) {
9
            throw new RuntimeException("No s'ha pogut carregar db/config.properties: " + e.getMessage());
10
        }
11
        Properties props = new Properties();
12
        props.setProperty("url", fitxer.getProperty("db.url." + db));
13
        props.setProperty("user", fitxer.getProperty("db.user." + db));
14
        props.setProperty("password", fitxer.getProperty("db.password." + db));
15
        return props;
16
    }
17
}

1
public DataSource dataSource() {
2
    Properties props = DAO.ConnectionProvider.loadProperties("postgresql");
3
    HikariConfig cfg = new HikariConfig();
4
    cfg.setJdbcUrl(props.getProperty("url"));
5
    cfg.setUsername(props.getProperty("user"));
6
    cfg.setPassword(props.getProperty("password"));
7
    cfg.setMaximumPoolSize(5);
8
    cfg.setPoolName("EscaladaPool");
9
    return new HikariDataSource(cfg);
10
}

1
public interface CRUD<T> {
2
    void create(T obj) throws DAOException;
3
    T read(int id) throws DAOException;
4
    void update(T obj) throws DAOException;
5
    void delete(int id) throws DAOException;
6
    List<T> getAll() throws DAOException;
7
}

1
public class DAOException extends Exception {
2
    public DAOException(String message) { super(message); }
3
    public DAOException(String message, Throwable cause) { super(message, cause); }
4
}

1
public void create(Escola escola) throws DAOException {
2
    String sql = "INSERT INTO escola (nom, lloc, aproximacio, popularitat) VALUES (?, ?, ?, ?)";
3
    try (Connection conn = ds.getConnection();
4
         PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
5
        ps.setString(1, escola.getNom());
6
        ps.setString(2, escola.getLloc());
7
        ps.setString(3, escola.getAproximacio());
8
        ps.setString(4, escola.getPopularitat());
9
        ps.executeUpdate();
10

11
        try (ResultSet keys = ps.getGeneratedKeys()) {
12
            if (keys.next()) escola.setId(keys.getInt(1));
13
        }
14
    } catch (SQLException e) {
15
        throw new DAOException("Error al crear l'escola: " + e.getMessage(), e);
16
    }
17
}

1
public Escola read(int id) throws DAOException {
2
    String sql = "SELECT id, nom, lloc, aproximacio, popularitat FROM escola WHERE id = ?";
3
    try (Connection conn = ds.getConnection();
4
         PreparedStatement ps = conn.prepareStatement(sql)) {
5
        ps.setInt(1, id);
6
        try (ResultSet rs = ps.executeQuery()) {
7
            if (rs.next()) return mapRow(rs);
8
            return null;
9
        }
10
    } catch (SQLException e) {
11
        throw new DAOException("Error al llegir l'escola amb id " + id + ": " + e.getMessage(), e);
12
    }
13
}

1
private Escola mapRow(ResultSet rs) throws SQLException {
2
    return new Escola(
3
            rs.getInt("id"),
4
            rs.getString("nom"),
5
            rs.getString("lloc"),
6
            rs.getString("aproximacio"),
7
            rs.getString("popularitat")
8
    );
9
}

1
public int countVies(int escolaId) throws DAOException {
2
    String sql = "SELECT COUNT(*) FROM via v "
3
               + "JOIN sector s ON v.sector_id = s.id "
4
               + "WHERE s.escola_id = ?";
5
    // ... ejecutar y devolver rs.getInt(1)
6
}

1
private static void setTimestamp(PreparedStatement ps, int idx, String value) throws SQLException {
2
    if (value == null || value.isBlank()) {
3
        ps.setNull(idx, Types.TIMESTAMP);
4
    } else {
5
        ps.setTimestamp(idx, Timestamp.valueOf(value));
6
    }
7
}

1
String sql = "UPDATE via SET estat = 'Apte' "
2
           + "WHERE estat IN ('Construccio', 'Tancada') AND data_fi_estat < NOW()";

1
String sql = "SELECT MAX(v.grau_dificultat) FROM ascensio a "
2
           + "JOIN via v ON a.via_id = v.id "
3
           + "WHERE a.escalador_id = ?";

1
String sql = "SELECT COALESCE(SUM(llargada), 0) FROM llarg WHERE via_id = ?";

1
public record SectorAmbViesDisponibles(int id, String sectorNom, String escolaNom, int viesDisponibles) {}

1
private static final String GRAUS_SQL_ARRAY =
2
    "ARRAY['3','3+','4','4+','5','5+','6a','6a+','6b','6b+','6c','6c+',"
3
  + "'7a','7a+','7b','7b+','7c','7c+','8a','8a+','8b','8b+','8c','8c+',"
4
  + "'9a','9a+','9b','9b+','9c','9c+']::text[]";

1
public static void executar(DataSource ds) {
2
    EscolaDAO escolaDAO = new EscolaDAO(ds);
3
    EscaladorDAO escaladorDAO = new EscaladorDAO(ds);
4
    SectorDAO sectorDAO = new SectorDAO(ds);
5
    ViaDAO viaDAO = new ViaDAO(ds);
6
    LlargDAO llargDAO = new LlargDAO(ds);
7
    AscensioDAO ascensioDAO = new AscensioDAO(ds);
8
    ConsultesDAO consultesDAO = new ConsultesDAO(ds);
9

10
    boolean sortir = false;
11
    while (!sortir) {
12
        View.mostrarMenuPrincipal();
13
        int opcio = View.llegirEnter("Escull una opció: ");
14
        View.netejarConsola();
15
        switch (opcio) {
16
            case 1 -> MenuEscola.executar(escolaDAO, consultesDAO);
17
            case 2 -> MenuEscalador.executar(escaladorDAO, ascensioDAO, consultesDAO);
18
            // ...
19
            case 0 -> sortir = true;
20
            default -> View.mostrarOpcioIncorrecta();
21
        }
22
    }
23
}

1
public static void executar(EscolaDAO dao) {
2
    String nom = View.llegirLiniaNoBuida("Nom de l'escola: ");
3
    String lloc = View.llegirLinia("Lloc: ");
4
    String aproximacio = View.llegirLinia("Aproximació (descripció com arribar): ");
5
    String popularitat;
6
    while (true) {
7
        String entrada = View.llegirLinia("Popularitat (baixa / mitjana / alta): ").trim();
8
        if (entrada.equalsIgnoreCase("baixa") || entrada.equalsIgnoreCase("mitjana") || entrada.equalsIgnoreCase("alta")) {
9
            popularitat = entrada.toLowerCase();
10
            break;
11
        }
12
        View.missatge("Valor no vàlid. Opcions: baixa, mitjana, alta");
13
    }
14

15
    Escola escola = new Escola(nom, lloc, aproximacio, popularitat);
16
    try {
17
        dao.create(escola);
18
        View.missatge("Escola creada amb id " + escola.getId());
19
    } catch (DAOException e) {
20
        View.missatge(e.getMessage());
21
    }
22
}

1
String nom = View.llegirLinia("Nou nom [" + escola.getNom() + "]: ");
2
if (!nom.isBlank()) escola.setNom(nom);

1
public class ValorantApiClient {
2
    private static final String BASE = "https://valorant-api.com/v1";
3
    private final HttpClient client = HttpClient.newHttpClient();
4

5
    public String fetchAgents() { return get(BASE + "/agents?isPlayableCharacter=true"); }
6
    public String fetchMaps() { return get(BASE + "/maps"); }
7
    public String fetchAgentByUuid(String uuid) { return get(BASE + "/agents/" + uuid); }
8
    public String fetchMapByUuid(String uuid) { return get(BASE + "/maps/" + uuid); }
9

10
    private String get(String url) {
11
        try {
12
            HttpRequest req = HttpRequest.newBuilder()
13
                    .uri(URI.create(url))
14
                    .GET()
15
                    .build();
16
            HttpResponse<String> resp = client.send(req, HttpResponse.BodyHandlers.ofString());
17
            if (resp.statusCode() != 200) {
18
                throw new RuntimeException("L'API ha retornat codi " + resp.statusCode() + " per a: " + url);
19
            }
20
            return resp.body();
21
        } catch (IOException e) {
22
            throw new RuntimeException("Error de connexió amb l'API: " + e.getMessage(), e);
23
        } catch (InterruptedException e) {
24
            Thread.currentThread().interrupt();
25
            throw new RuntimeException("Connexió interrompuda: " + e.getMessage(), e);
26
        }
27
    }
28
}

1
private String read(String filePath) {
2
    try {
3
        return Files.readString(Paths.get(filePath));
4
    } catch (IOException e) {
5
        throw new RuntimeException("No s'ha pogut llegir el fitxer JSON: " + e.getMessage(), e);
6
    }
7
}

1
public List<Agent> parseAgents(String json) {
2
    try {
3
        JsonObject root = JsonParser.parseString(json).getAsJsonObject();
4
        JsonArray data = root.getAsJsonArray("data");
5
        List<Agent> agents = new ArrayList<>();
6
        for (JsonElement el : data) {
7
            Agent agent = parseAgentObject(el.getAsJsonObject());
8
            if (agent != null) agents.add(agent);
9
        }
10
        return agents;
11
    } catch (JsonSyntaxException | IllegalStateException e) {
12
        throw new RuntimeException("L'estructura del JSON d'agents ha canviat o és invàlida: " + e.getMessage(), e);
13
    }
14
}

1
private String getString(JsonObject obj, String key) {
2
    if (!obj.has(key) || obj.get(key).isJsonNull()) return null;
3
    return obj.get(key).getAsString();
4
}

1
public interface CRUD<T> {
2
    void create(T obj) throws DAOException;
3
    T read(String uuid) throws DAOException;
4
    void update(T obj) throws DAOException;
5
    void delete(String uuid) throws DAOException;
6
    List<T> getAll() throws DAOException;
7
}

1
String sql = """
2
        SELECT a.*, r.display_name AS role_name
3
        FROM agent a
4
        LEFT JOIN role r ON a.role_uuid = r.uuid
5
        WHERE a.uuid = ?
6
        """;

1
public boolean existsByUuid(String uuid) throws DAOException {
2
    String sql = "SELECT 1 FROM agent WHERE uuid = ?";
3
    try (Connection conn = ds.getConnection();
4
         PreparedStatement ps = conn.prepareStatement(sql)) {
5
        ps.setString(1, uuid);
6
        try (ResultSet rs = ps.executeQuery()) {
7
            return rs.next();
8
        }
9
    } catch (SQLException e) {
10
        throw new DAOException("Error al verificar l'agent: " + e.getMessage(), e);
11
    }
12
}

1
String sql = """
2
        INSERT INTO agent (uuid, display_name, description, developer_name, release_date,
3
                           is_playable_character, is_available_for_test, is_base_content,
4
                           role_uuid, last_updated)
5
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
6
        ON CONFLICT (uuid) DO UPDATE
7
          SET display_name          = EXCLUDED.display_name,
8
              description           = EXCLUDED.description,
9
              developer_name        = EXCLUDED.developer_name,
10
              release_date          = EXCLUDED.release_date,
11
              is_playable_character = EXCLUDED.is_playable_character,
12
              is_available_for_test = EXCLUDED.is_available_for_test,
13
              is_base_content       = EXCLUDED.is_base_content,
14
              role_uuid             = EXCLUDED.role_uuid,
15
              last_updated          = NOW()
16
        """;

1
private Timestamp parseReleaseDate(String date) {
2
    if (date == null || date.isBlank()) return null;
3
    try {
4
        return Timestamp.from(OffsetDateTime.parse(date).toInstant());
5
    } catch (Exception e) {
6
        return null;
7
    }
8
}

1
AgentDAO agentDAO = new AgentDAO(ds);
2
RoleDAO roleDAO = new RoleDAO(ds);
3
AbilityDAO abilityDAO = new AbilityDAO(ds);
4
MapDAO mapDAO = new MapDAO(ds);
5
ValorantApiClient client = new ValorantApiClient();
6
ValorantJsonLoader loader = new ValorantJsonLoader();
7
ValorantMapper mapper = new ValorantMapper();

1
public static void executar(AgentDAO agentDAO, RoleDAO roleDAO, AbilityDAO abilityDAO,
2
                            ValorantApiClient client, ValorantJsonLoader loader, ValorantMapper mapper) {
3
    View.mostrarMenuFontExterna();
4
    int font = View.llegirEnter("Font: ");
5
    if (font == 0) return;
6

7
    View.mostrarMenuSincronitzacio();
8
    int tipus = View.llegirEnter("Tipus: ");
9
    if (tipus == 0) return;
10

11
    String json;
12
    try {
13
        if (font == 1) {
14
            json = client.fetchAgents();
15
        } else if (font == 2) {
16
            String path = View.llegirLiniaNoBuida("Ruta del fitxer JSON: ");
17
            json = loader.loadAgents(path);
18
        } else { return; }
19
    } catch (RuntimeException e) {
20
        View.missatge("Error en obtenir les dades: " + e.getMessage());
21
        return;
22
    }
23

24
    List<Agent> agents = mapper.parseAgents(json);
25
    List<Role> roles = mapper.parseRolesFromAgentsJson(json);
26

27
    int afegits = 0, actualitzats = 0;
28

29
    try {
30
        // Roles primero — agent tiene FK a role
31
        for (Role role : roles) roleDAO.upsert(role);
32

33
        for (Agent agent : agents) {
34
            if (tipus == 1) {
35
                // Còpia parcial: ignora els agents que ja existeixen
36
                if (!agentDAO.existsByUuid(agent.getUuid())) {
37
                    agentDAO.create(agent);
38
                    if (agent.getAbilities() != null) {
39
                        for (Ability ab : agent.getAbilities()) abilityDAO.create(ab);
40
                    }
41
                    afegits++;
42
                }
43
            } else if (tipus == 2) {
44
                // Còpia completa: upsert i reemplaça abilities
45
                agentDAO.upsert(agent);
46
                abilityDAO.deleteByAgent(agent.getUuid());
47
                if (agent.getAbilities() != null) {
48
                    for (Ability ab : agent.getAbilities()) abilityDAO.create(ab);
49
                }
50
                actualitzats++;
51
            }
52
        }
53
    } catch (DAOException e) {
54
        View.missatge("Error durant la sincronització: " + e.getMessage());
55
    }
56
}