import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Database {
    
    private Connection connection; 

    public Database() {
        try {
            Class.forName("org.mariadb.jdbc.Driver");

            this.connection = DriverManager.getConnection(
                "jdbc:mariadb://dwarves.iut-fbleau.fr/baudrier",
                "baudrier",
                "baudrier"
            );
        } catch(ClassNotFoundException exception) {
            System.err.println("Class 'org.mariadb.jdbc.Driver' not found.");
        } catch(SQLException exception) {
            System.err.println("Error while trying to connect to the database");
        }
    }


    public Votes getVotesFromCompetitor(String country) {
        try {
            PreparedStatement query = this.connection.prepareStatement(
                "SELECT VoterVotes.points " +
                "FROM Votes " +
                "JOIN Countries AS Competitors ON Votes.competitor_id = Competitors.id " +
                "JOIN Countries AS Voters ON Votes.voter_id = Voters.id " +
                "WHERE Competitors.name = ?;"
            );
            query.setString(1, country);

            ResultSet result = query.executeQuery();
            ArrayList<Vote> voteList = new ArrayList<>();

            while(result.next()) {
                voteList.add(new Vote(result.getString("name"), result.getInt("points")));
            }

            try {
                query.close();
            } catch(SQLException exception) {
                System.err.println("Error while trying to close the query.");
            }

            return new Votes(country, voteList);
        } catch(SQLException exception) {
            System.err.println("Error while trying to prepare or execute the query (" + exception.getMessage() + ").");
        }

        return null; 
    }

    public void close() {
        try {
            this.connection.close();
        } catch(SQLException exception) {
            System.err.println("Error while trying to close the connection.");
        }
    }
}