odds/OddsJavaFx/src/data/GuiMysql.java

package data;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.AbstractMap;
import java.util.AbstractMap.SimpleEntry;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.eclipse.jetty.util.log.Log;

import com.google.common.base.Strings;
import com.google.common.collect.Lists;

import mysql.Mysql;
import objects.BetDTO;
import objects.Constants;
import objects.League;
import objects.OverUnder;
import objects.SoccerMatch;
import objects.Team;
import objects.TeamResults;
import objects.TeamStanding;
import objects.bets.Bet;
import objects.bets.Bet.Status;

public class GuiMysql extends Mysql {

    private static final String SEASON = "season";
    private static final String AWAY_SCORE = "awayScore";
    private static final String HOME_SCORE = "homeScore";
    private static final String COUNTRY_NAME = "countryName";
    private static final String DRAWS = "draws";
    private static final String DATE_FORMAT = "yyyy-MM-dd";
    private static final String AWAY_TEAM_ID = "awayTeamId";
    private static final String HOME_TEAM_ID = "homeTeamId";
    private static final String LEAGUE_NAME = "leagueName";
    private static final BigDecimal INCREMENT = BigDecimal.valueOf(0.2);
    private static final GuiMysql instance = new GuiMysql();
    private final Connection conn;

    protected GuiMysql() {
        super();
        conn = this.getConnection();
    }

    public static GuiMysql getInstance() {
        return instance;
    }

    public int addAnalysisBet(Bet bet) {
        int newId = -1;
        String sql = "INSERT INTO AnalysisBetTable (matchId, bet, betAmount, betOdds, status) VALUES (?,?,?,?,?)";

        try (PreparedStatement stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stat.setInt(1, bet.getMatch().getMatchId());
            stat.setString(2, bet.getBet());
            stat.setFloat(3, bet.getBetAmount());
            stat.setFloat(4, bet.getBetOdds());
            stat.setString(5, bet.getStatus().name());

            stat.execute();

            ResultSet generatedKeys = stat.getGeneratedKeys();

            while (generatedKeys.next()) {
                newId = generatedKeys.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return newId;
    }

    public void addBetSeries(BetDTO bet) {
        String sql = "INSERT INTO ActiveBets (series, gameId, betType, bet, odds, done) VALUES (?, ?, ?, ?, ?, ?)";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, bet.getBetSeries());
            stat.setInt(2, getGameId(bet.getHomeTeam(), bet.getAwayTeam(), bet.getGameDate(), bet.getCountryId(),
                    bet.getLeagueId()));
            stat.setString(3, bet.getBetType());
            stat.setFloat(4, bet.getBet());
            stat.setFloat(5, bet.getOdds());
            stat.setBoolean(6, false);

            stat.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Bet getAnalysisBet(int betId) {
        Bet result = null;
        String sql = "SELECT * FROM AnalysisBetTable abt "
                + "INNER JOIN SoccerResults sr ON abt.matchId = sr.id "
                + "WHERE abt.id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, betId);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                Team homeTeam = getTeam(rs.getInt(HOME_TEAM_ID));
                Team awayTeam = getTeam(rs.getInt(AWAY_TEAM_ID));
                SoccerMatch match = new SoccerMatch(rs.getInt("matchId"), homeTeam, awayTeam, rs.getFloat("odds1"),
                        rs.getFloat("oddsX"), rs.getFloat("odds2"), rs.getInt(HOME_SCORE), rs.getInt(AWAY_SCORE),
                        LocalDateTime.parse(rs.getString("gameDate")), rs.getString(SEASON));

                match.setLeagueName(homeTeam.getTeamLeague());
                match.setCountryName(homeTeam.getCountryName());

                result = new Bet(rs.getInt("id"), match, rs.getString("bet"), rs.getFloat("betAmount"),
                        rs.getFloat("betOdds"),
                        Status.valueOf(rs.getString("status")), rs.getInt("coveredBetId"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public List<Bet> getAnalysisBets() {
        List<Bet> result = new ArrayList<>();
        String sql = "SELECT * FROM AnalysisBetTable abt "
                + "INNER JOIN SoccerResults sr ON abt.matchId = sr.id "
                + "WHERE status IN ('LOST', 'OPEN', 'COVERED')";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            ResultSet rs = stat.executeQuery();

            while (rs.next()) {

                Team homeTeam = getTeam(rs.getInt(HOME_TEAM_ID));
                Team awayTeam = getTeam(rs.getInt(AWAY_TEAM_ID));
                SoccerMatch match = new SoccerMatch(rs.getInt("matchId"), homeTeam, awayTeam, rs.getFloat("odds1"),
                        rs.getFloat("oddsX"), rs.getFloat("odds2"), rs.getInt(HOME_SCORE), rs.getInt(AWAY_SCORE),
                        LocalDateTime.parse(rs.getString("gameDate")), rs.getString(SEASON));

                match.setLeagueName(homeTeam.getTeamLeague());
                match.setCountryName(homeTeam.getCountryName());

                result.add(new Bet(rs.getInt("id"), match, rs.getString("bet"), rs.getFloat("betAmount"),
                        rs.getFloat("betOdds"),
                        Status.valueOf(rs.getString("status")), rs.getInt("coveredBetId")));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public List<Bet> getAnalysisBetStatistics() {
        List<Bet> result = new ArrayList<>();

        String sql = "SELECT sr.id as soccerMatchId, ht.id as homeTeamId, awt.id as awayTeamId, sr.odds1, sr.oddsX, sr.odds2, "
                + "sr.homeScore, sr.awayScore, sr.gameDate, sr.season, l.name as leagueName, abt.id as betId, abt.bet, abt.betAmount, abt.betOdds "
                + "FROM AnalysisBetTable abt "
                + "INNER JOIN SoccerResults sr ON abt.matchId = sr.id "
                + "INNER JOIN Team ht ON ht.id = sr.homeTeamId "
                + "INNER JOIN Team awt ON awt.id = sr.awayTeamId "
                + "INNER JOIN League l ON l.id = sr.leagueId "
                + "WHERE abt.status != 'OPEN'";

        try (PreparedStatement stat = getConnection().prepareStatement(sql)) {
            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                Team homeTeam = getTeam(rs.getInt(HOME_TEAM_ID));
                Team awayTeam = getTeam(rs.getInt(AWAY_TEAM_ID));
                SoccerMatch match = new SoccerMatch(rs.getInt("soccerMatchId"), homeTeam, awayTeam,
                        rs.getFloat("odds1"), rs.getFloat("oddsX"), rs.getFloat("odds2"), rs.getInt(HOME_SCORE),
                        rs.getInt(AWAY_SCORE), LocalDateTime.parse(rs.getString("gameDate")), rs.getString(SEASON));
                match.setLeagueName(rs.getString(LEAGUE_NAME));

                Bet bet = new Bet(rs.getInt("betId"), match, rs.getString("bet"), rs.getFloat("betAmount"),
                        rs.getFloat("betOdds"));

                result.add(bet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public List<Float> getAvgAwayScore(int teamId) {
        final ArrayList<Float> returnValue = Lists.newArrayList();
        final String sql = "SELECT AVG(homeScore) as avgConceded, AVG(awayScore) as avgScored FROM SoccerResults WHERE awayScore != -1 AND awayTeamId = ?";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, teamId);

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                returnValue.add(rs.getFloat("avgScored"));
                returnValue.add(rs.getFloat("avgConceded"));
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return returnValue;
    }

    public List<Float> getAvgAwayScoreThisSeason(int teamId, int countryId, int leagueId) {
        return getAvgAwayScoreThisSeason(teamId, countryId, leagueId, "");
    }

    public List<Float> getAvgAwayScoreThisSeason(int teamId, int countryId, int leagueId, String gameDate) {
        final ArrayList<Float> returnValue = Lists.newArrayList();
        final String sql = "SELECT AVG(homeScore) as avgConcededSeason, AVG(awayScore) as avgScoredSeason FROM SoccerResults WHERE awayScore != -1 AND awayTeamId = ? AND season = ? AND DATE(gameDate) < ? ORDER BY gameDate ASC";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, teamId);
            if (Strings.isNullOrEmpty(gameDate)
                    || getSeasonFromDate(countryId, leagueId, gameDate).equals(getLastSeason(countryId, leagueId))) {
                stat.setString(2, getLastSeason(countryId, leagueId));
            } else {
                String seasonFromDate = getSeasonFromDate(countryId, leagueId, gameDate);
                stat.setString(2, seasonFromDate);
            }
            if (Strings.isNullOrEmpty(gameDate)) {
                stat.setString(3, new SimpleDateFormat(DATE_FORMAT).format(new Date()));
            } else {
                stat.setString(3, gameDate);
            }

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                returnValue.add(rs.getFloat("avgScoredSeason"));
                returnValue.add(rs.getFloat("avgConcededSeason"));
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return returnValue;
    }

    public List<Float> getAvgHomeScore(int teamId) {
        final ArrayList<Float> returnValue = Lists.newArrayList();
        final String sql = "SELECT AVG(homeScore) as avgScored, AVG(awayScore) as avgConceded FROM SoccerResults WHERE homeScore != -1 AND homeTeamId = ?";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, teamId);

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                returnValue.add(rs.getFloat("avgScored"));
                returnValue.add(rs.getFloat("avgConceded"));
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return returnValue;
    }

    public List<Float> getAvgHomeScoreThisSeason(int teamId, int countryId, int leagueId) {
        return getAvgHomeScoreThisSeason(teamId, countryId, leagueId, "");
    }

    public List<Float> getAvgHomeScoreThisSeason(int teamId, int countryId, int leagueId, String gameDate) {
        final ArrayList<Float> returnValue = Lists.newArrayList();
        final String sql = "SELECT AVG(homeScore) as avgScoredSeason, AVG(awayScore) as avgConcededSeason FROM SoccerResults WHERE homeScore != -1 AND homeTeamId = ? AND season = ? AND DATE(gameDate) < ? ORDER BY gameDate ASC";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, teamId);

            if (Strings.isNullOrEmpty(gameDate)
                    || getSeasonFromDate(countryId, leagueId, gameDate).equals(getLastSeason(countryId, leagueId))) {
                stat.setString(2, getLastSeason(countryId, leagueId));
            } else {
                String seasonFromDate = getSeasonFromDate(countryId, leagueId, gameDate);
                stat.setString(2, seasonFromDate);
            }
            if (Strings.isNullOrEmpty(gameDate)) {
                stat.setString(3, new SimpleDateFormat(DATE_FORMAT).format(new Date()));
            } else {
                stat.setString(3, gameDate);
            }

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                returnValue.add(rs.getFloat("avgScoredSeason"));
                returnValue.add(rs.getFloat("avgConcededSeason"));
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return returnValue;
    }

    public String getBetBaseAmount() {
        String sql = "SELECT value FROM Settings WHERE name = ?";
        String result = "";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setString(1, "BetBaseAmount");

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result = rs.getString("value");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public List<BetDTO> getBetSeries(boolean includeInactive) {
        List<BetDTO> result = new ArrayList<>();
        String sql = "SELECT ab.*, sr.gameDate as gameDate, sr.id as gameId, sr.homeScore as homeScore, sr.awayScore as awayScore, "
                + "ht.name as homeTeam, aw.name as awayTeam " + "FROM ActiveBets ab "
                + "INNER JOIN SoccerResults sr ON ab.gameId = sr.id "
                + "INNER JOIN Team ht ON sr.homeTeamId = ht.id " + "INNER JOIN Team aw ON sr.awayTeamId = aw.id "
                + "WHERE done = ? ORDER BY series ASC, sr.gameDate DESC";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setBoolean(1, includeInactive);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                BetDTO dto = new BetDTO();
                dto.setHomeTeam(rs.getString("homeTeam"));
                dto.setAwayTeam(rs.getString("awayTeam"));
                dto.setBet(rs.getFloat("bet"));
                dto.setBetType(rs.getString("betType"));
                dto.setBetSeries(rs.getInt("series"));
                dto.setGameId(rs.getInt("gameId"));
                dto.setMatch(rs.getString("homeTeam"), rs.getString("awayTeam"));
                dto.setOdds(rs.getFloat("odds"));
                dto.setResult(rs.getInt(HOME_SCORE), rs.getInt("AwayScore"));
                dto.setGameDate(rs.getString("gameDate"));
                dto.setWinAmount(rs.getFloat("odds") * rs.getFloat("bet"));
                result.add(dto);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public int getBetSeriesEndNumber() {
        int result = -1;
        String sql = "SELECT MAX(series) FROM ActiveBets";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public List<SimpleEntry<Integer, String>> getCountries() {

        final ArrayList<AbstractMap.SimpleEntry<Integer, String>> countries = Lists.newArrayList();
        final String sql = "SELECT id, name FROM Country";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                final SimpleEntry<Integer, String> entry = new SimpleEntry<>(rs.getInt(Constants.ID),
                        rs.getString("name"));
                countries.add(entry);
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }
        return countries;
    }

    public List<SimpleEntry<Integer, String>> getCountriesBySport(int sportId, String date) {
        final String sql = "SELECT * FROM Country WHERE id IN (SELECT DISTINCT(countryId) FROM SoccerResults WHERE DATE(gameDate) = ?) ORDER BY prio DESC, name ASC";
        final ArrayList<SimpleEntry<Integer, String>> result = new ArrayList<>();
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            if (date.equals("")) {
                stat.setString(1, new SimpleDateFormat(DATE_FORMAT).format(new Date()));
            } else {
                stat.setString(1, date);
            }

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result.add(new SimpleEntry<>(rs.getInt(Constants.ID), rs.getString("name")));
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public int getGameId(String homeTeam, String awayTeam, String gameDate, int countryId, int leagueId) {
        int result = -1;
        String sql = "SELECT id FROM SoccerResults WHERE homeTeamId = (SELECT id FROM Team WHERE name = ? AND countryId = ? AND leagueId = ?) AND awayTeamId = (SELECT id FROM Team WHERE name = ? AND countryId = ? AND leagueId = ?) AND DATE(gameDate) = DATE(?)";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setString(1, homeTeam);
            stat.setInt(2, countryId);
            stat.setInt(3, leagueId);
            stat.setString(4, awayTeam);
            stat.setInt(5, countryId);
            stat.setInt(6, leagueId);
            stat.setString(7, gameDate);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result = rs.getInt("id");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public Map<Integer, Integer> getGoalAvgThisSeason(int leagueId, int countryId) {
        Map<Integer, Integer> returnValue = new HashMap<>();
        final String goalsSql = "SELECT (homeScore + awayScore) as totalGoals, count(*) as count FROM SoccerResults  WHERE leagueId = ? AND countryId = ? AND season = ? GROUP BY totalGoals ORDER BY totalGoals asc";
        try (PreparedStatement goalStmt = conn.prepareStatement(goalsSql)) {
            goalStmt.setInt(1, leagueId);
            goalStmt.setInt(2, countryId);
            goalStmt.setString(3, getLastSeason(countryId, leagueId));

            final ResultSet goalRs = goalStmt.executeQuery();

            while (goalRs.next()) {
                int tg = goalRs.getInt("totalGoals");
                if (tg < 0) {
                    continue;
                }
                int numGoals = goalRs.getInt("count");

                returnValue.put(tg, numGoals);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return returnValue;
    }

    public BigDecimal getIncrement() {
        return INCREMENT;
    }

    public String getLastSeason(Integer countryId, Integer leagueId) {
        String season = "";
        final String sql = "SELECT season FROM SoccerResults WHERE leagueId = ? AND countryId = ? ORDER BY season DESC limit 1";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, leagueId);
            stmt.setInt(2, countryId);

            final ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                season = rs.getString(Constants.SEASON);
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return season;
    }

    public List<Float> getLeagueAvarages(int leagueId, int countryId, String gameDate) {
        final ArrayList<Float> returnValue = Lists.newArrayList();
        final String sql = "SELECT AVG(homeScore) avgHomeScore, AVG(awayScore) as avgAwayScore"
                + " FROM SoccerResults WHERE leagueId = ? AND countryId = ? AND DATE(gameDate) < DATE(?)";

        try (PreparedStatement stat = conn.prepareStatement(sql);) {
            stat.setInt(1, leagueId);
            stat.setInt(2, countryId);
            stat.setString(3, gameDate);

            final ResultSet rs = stat.executeQuery();
            while (rs.next()) {
                returnValue.add(rs.getFloat("avgHomeScore"));
                returnValue.add(rs.getFloat("avgAwayScore"));
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return returnValue;
    }

    public League getLeagueInfo(int leagueId) {
        final String sql = "SELECT * FROM League WHERE id = ?";
        League result = null;
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, leagueId);
            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result = new League(rs.getInt(Constants.ID), rs.getString("name"), rs.getInt("scoringDiffLastGame"),
                        rs.getInt("scoringTotal"),
                        rs.getInt("winLossRatioHomeAndAway"), rs.getInt("winLossRatio"), rs.getInt("drawDiffHomeAway"),
                        rs.getInt("drawDiffTotalGoals"), rs.getInt("drawWinningForm"),
                        rs.getInt("drawWinningFormHomeAway"));
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public League getLeagueInfo(String teamLeague) {
        final String sql = "SELECT * FROM League WHERE name = ?";
        League result = null;
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setString(1, teamLeague);
            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result = new League(rs.getInt(Constants.ID), rs.getString("name"), rs.getInt("scoringDiffLastGame"),
                        rs.getInt("scoringTotal"),
                        rs.getInt("winLossRatioHomeAndAway"), rs.getInt("winLossRatio"), rs.getInt("drawDiffHomeAway"),
                        rs.getInt("drawDiffTotalGoals"), rs.getInt("drawWinningForm"),
                        rs.getInt("drawWinngingFormHomeAway"));
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public List<SimpleEntry<Integer, String>> getLeagues(int sportId, int countryId) {

        final ArrayList<AbstractMap.SimpleEntry<Integer, String>> leagues = Lists.newArrayList();
        final String sql = "SELECT id, name FROM League WHERE sportId = ? AND countryId = ? ORDER BY name ASC";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, sportId);
            stat.setInt(2, countryId);

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                final SimpleEntry<Integer, String> entry = new SimpleEntry<>(rs.getInt(Constants.ID),
                        rs.getString("name"));
                leagues.add(entry);
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }
        return leagues;
    }

    public List<SimpleEntry<Integer, String>> getLeaguesByDate(int sportId, int countryId, String date) {
        final ArrayList<AbstractMap.SimpleEntry<Integer, String>> leagues = Lists.newArrayList();
        final String sql = "SELECT id, name FROM League WHERE id IN (SELECT leagueId FROM SoccerResults WHERE countryId = ? AND DATE(gameDate) = ?)";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, countryId);
            stat.setString(2, date);

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                final SimpleEntry<Integer, String> entry = new SimpleEntry<>(rs.getInt(Constants.ID),
                        rs.getString("name"));
                leagues.add(entry);
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }
        return leagues;
    }

    public List<TeamStanding> getLeagueTable(int leagueId, String season, int countryId, String date) {
        final ArrayList<TeamStanding> result = Lists.newArrayList();

        final String sql = "SELECT teamName.name as teamName, count(*) played, "
                + "count(case when homeScore > awayScore then 1 end) wins, "
                + "count(case when awayScore> homeScore then 1 end) lost, "
                + "count(case when homeScore = awayScore then 1 end) draws, "
                + "sum(homeScore) homeScore, " + "sum(awayScore) awayScore, "
                + "sum(homeScore) - sum(awayScore) goal_diff, " + "sum("
                + "case when homeScore > awayScore then 3 else 0 end + case "
                + "WHEN homeScore = awayScore then 1 else 0 end) score, "
                + "season FROM "
                + "(select hometeamId team, homeScore, awayScore, season, gameDate, leagueId as league, countryId as country FROM SoccerResults "
                + "union all SELECT awayteamId, awayScore, homeScore, season, gameDate, leagueId as league, countryId as country FROM SoccerResults) a "
                + "INNER JOIN Team teamName ON team = teamName.id " + "WHERE season = ? " + "AND league = ? "
                + "AND country = ? "
                + "AND homeScore != -1 " + "AND awayScore != -1 AND DATE(gameDate) < DATE(?) group by team "
                + "order by score desc, goal_diff desc";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {

            stat.setString(1, season);
            stat.setInt(2, leagueId);
            stat.setInt(3, countryId);
            stat.setString(4, date);

            final ResultSet rs = stat.executeQuery();
            while (rs.next()) {
                final TeamStanding ts = new TeamStanding(rs.getString("teamName"), rs.getInt("wins"), rs.getInt("lost"),
                        rs.getInt(DRAWS),
                        rs.getInt("score"), rs.getFloat(Constants.HOME_SCORE), rs.getFloat(Constants.AWAY_SCORE),
                        rs.getFloat("goal_diff"));
                result.add(ts);
            }

        } catch (final SQLException e) {
            Log.getLog().info("Sql vid fel: %s", sql);
            e.printStackTrace();
        }

        return result;
    }

    public List<SoccerMatch> getMatches(int sportId, Integer countryId, Integer leagueId, String date, String order,
            boolean exactDate,
            boolean onlyPrio) {
        final ArrayList<SoccerMatch> matches = Lists.newArrayList();
        final String dateSql;
        final String orderSql = " ORDER BY gameDate " + order;
        if (date.equals("")) {
            dateSql = "DATE(gameDate) = DATE(NOW()) ";
        } else {
            if (exactDate) {
                dateSql = "DATE(gameDate) = '" + date + "' ";
            } else {
                dateSql = "DATE(gameDate) <= '" + date + "' ";
            }
        }
        final String countrySql;
        if (countryId != null) {
            countrySql = "AND res.countryId = ? ";
        } else {
            countrySql = "";
        }
        final String leagueSql;
        if (leagueId != null) {
            leagueSql = "AND res.leagueId = ? ";
        } else {
            leagueSql = "";
        }
        final String onlyPrioSql;
        if (onlyPrio) {
            onlyPrioSql = "AND l.prio = 1 ";
        } else {
            onlyPrioSql = "";
        }

        final String sql = "SELECT res.*, c.name as countryName, l.name as leagueName, "
                + "hTeam.name as homeTeamName, aTeam.name as awayTeamName "
                + "FROM SoccerResults as res "
                + "INNER Join Team as hTeam ON res.homeTeamId = hTeam.id AND res.leagueId = hTeam.leagueId "
                + "INNER Join Team as aTeam ON res.awayTeamId = aTeam.id AND res.leagueId = hTeam.leagueId "
                + "INNER JOIN League l ON res.leagueId = l.id "
                + "INNER JOIN Country c ON res.countryId = c.id "
                + "WHERE " + dateSql + leagueSql
                + countrySql + onlyPrioSql + orderSql;
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            if (leagueId != null && countryId != null) {
                stat.setInt(1, leagueId);
                stat.setInt(2, countryId);
            } else if (leagueId != null && countryId == null) {
                stat.setInt(1, leagueId);
            } else if (leagueId == null && countryId != null) {
                stat.setInt(1, countryId);
            }

            final ResultSet rs = stat.executeQuery();
            while (rs.next()) {
                final SoccerMatch sm = new SoccerMatch();
                final Team homeTeam = new Team();
                final Team awayTeam = new Team();

                homeTeam.setTeamId(rs.getInt(Constants.HOME_TEAM_ID));
                awayTeam.setTeamId(rs.getInt(Constants.AWAY_TEAM_ID));
                homeTeam.setTeamName(rs.getString(Constants.HOME_TEAM_NAME));
                awayTeam.setTeamName(rs.getString(Constants.AWAY_TEAM_NAME));
                homeTeam.setTeamLeagueId(rs.getInt(Constants.LEAGUE_ID));
                awayTeam.setTeamLeagueId(rs.getInt(Constants.LEAGUE_ID));
                homeTeam.setTeamLeague(rs.getString(LEAGUE_NAME));
                awayTeam.setTeamLeague(rs.getString(LEAGUE_NAME));
                homeTeam.setCountryId(rs.getInt(Constants.COUNTRY_ID));
                awayTeam.setCountryId(rs.getInt(Constants.COUNTRY_ID));
                homeTeam.setCountryName(rs.getString(COUNTRY_NAME));
                awayTeam.setCountryName(rs.getString(COUNTRY_NAME));

                sm.setLeagueName(rs.getString(LEAGUE_NAME));
                sm.setCountryName(rs.getString(COUNTRY_NAME));
                sm.setAwayScore(rs.getInt(Constants.AWAY_SCORE));
                sm.setHomeScore(rs.getInt(Constants.HOME_SCORE));
                sm.setHomeTeam(homeTeam);
                sm.setAwayTeam(awayTeam);
                sm.setMatchId(rs.getInt(Constants.ID));
                sm.setOdds1(rs.getFloat(Constants.ODDS_1));
                sm.setOddsX(rs.getFloat(Constants.ODDS_X));
                sm.setOdds2(rs.getFloat(Constants.ODDS_2));
                sm.setGameDate(LocalDateTime.parse(rs.getString(Constants.GAME_DATE)));
                sm.setSeason(rs.getString(Constants.SEASON));

                sm.setHomeTeamName(homeTeam.getTeamName());
                sm.setAwayTeamName(awayTeam.getTeamName());

                matches.add(sm);
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return matches;
    }

    public List<SoccerMatch> getMatches(int sportId, String dateString, String sortOrder, boolean exactDate) {
        return getMatches(sportId, null, null, dateString, sortOrder, exactDate, true);
    }

    public boolean getParsingStarted(int countryId, int leagueId) {
        boolean returnValue = false;
        final String sql = "SELECT parsedYear FROM League WHERE id = ? AND countryId = ?";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, leagueId);
            stat.setInt(2, countryId);

            final ResultSet rs = stat.executeQuery();
            while (rs.next()) {
                final String parsedYear = rs.getString("parsedYear");
                if (!Strings.isNullOrEmpty(parsedYear)) {
                    returnValue = true;
                }
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return returnValue;
    }

    public Map<String, String> getPreviousMatches(int numberOfMatches, String homeTeamName, String awayTeamName,
            String date, int countryId,
            int leagueId) {
        Map<String, String> result = new HashMap<>();

        String homeTeamSql = "SELECT * FROM SoccerResults sr INNER JOIN Team t ON sr.homeTeamId = t.id WHERE t.name = ? AND sr.leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC limit ?";
        String awayTeamSql = "SELECT * FROM SoccerResults sr INNER JOIN Team t ON sr.awayTeamId = t.id WHERE t.name = ? AND sr.leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC limit ?";
        String combinedSql = "SELECT * FROM SoccerResults sr INNER JOIN Team homeTeam ON homeTeamId = homeTeam.id INNER JOIN Team awayTeam ON sr.awayTeamId = awayTeam.id WHERE homeTeam.name = ? AND awayTeam.name = ? AND sr.leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?";
        String combinedReverseSql = "SELECT * FROM SoccerResults sr INNER JOIN Team homeTeam ON homeTeamId = homeTeam.id INNER JOIN Team awayTeam ON sr.awayTeamId = awayTeam.id WHERE homeTeam.name = ? AND awayTeam.name = ? AND sr.leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?";
        try (PreparedStatement homeTeamStat = conn.prepareStatement(homeTeamSql);
                PreparedStatement awayTeamStat = conn.prepareStatement(awayTeamSql);
                PreparedStatement combinedStat = conn.prepareStatement(combinedSql);
                PreparedStatement combinedReversedStat = conn.prepareStatement(combinedReverseSql);) {

            homeTeamStat.setString(1, homeTeamName);
            homeTeamStat.setInt(2, leagueId);
            homeTeamStat.setString(3, date);
            homeTeamStat.setInt(4, numberOfMatches);

            awayTeamStat.setString(1, awayTeamName);
            awayTeamStat.setInt(2, leagueId);
            awayTeamStat.setString(3, date);
            awayTeamStat.setInt(4, numberOfMatches);

            combinedStat.setString(1, homeTeamName);
            combinedStat.setString(2, awayTeamName);
            combinedStat.setInt(3, leagueId);
            combinedStat.setString(4, date);
            combinedStat.setInt(5, numberOfMatches);

            combinedReversedStat.setString(1, awayTeamName);
            combinedReversedStat.setString(2, homeTeamName);
            combinedReversedStat.setInt(3, leagueId);
            combinedReversedStat.setString(4, date);
            combinedReversedStat.setInt(5, numberOfMatches);

            ResultSet homeTeamRs = homeTeamStat.executeQuery();
            ResultSet awayTeamRs = awayTeamStat.executeQuery();
            ResultSet combinedTeamRs = combinedStat.executeQuery();
            ResultSet combinedReversedTeamRs = combinedReversedStat.executeQuery();

            String homeTeamMeets = "";
            while (homeTeamRs.next()) {
                homeTeamMeets += homeTeamRs.getInt(HOME_SCORE) + "-" + homeTeamRs.getInt(AWAY_SCORE) + ", ";
            }
            String awayTeamMeets = "";
            while (awayTeamRs.next()) {
                awayTeamMeets += awayTeamRs.getInt(HOME_SCORE) + "-" + awayTeamRs.getInt(AWAY_SCORE) + ", ";
            }
            String combinedMeets = "";
            while (combinedTeamRs.next()) {
                combinedMeets += combinedTeamRs.getInt(HOME_SCORE) + "-" + combinedTeamRs.getInt(AWAY_SCORE) + ", ";
            }
            String combinedReversedMeets = "";
            while (combinedReversedTeamRs.next()) {
                combinedReversedMeets += combinedReversedTeamRs.getInt(HOME_SCORE) + "-"
                        + combinedReversedTeamRs.getInt(AWAY_SCORE) + ", ";
            }
            if (homeTeamMeets.length() > 2) {
                result.put("PrevHomeTeam", homeTeamMeets.substring(0, homeTeamMeets.length() - 2));
            } else {
                result.put("PrevHomeTeam", homeTeamMeets);
            }
            if (awayTeamMeets.length() > 2) {
                result.put("PrevAwayTeam", awayTeamMeets.substring(0, awayTeamMeets.length() - 2));
            } else {
                result.put("PrevAwayTeam", awayTeamMeets);
            }
            if (combinedMeets.length() > 2) {
                result.put("PrevCombined", combinedMeets.substring(0, combinedMeets.length() - 2));
            } else {
                result.put("PrevCombined", combinedMeets);
            }

            if (combinedReversedMeets.length() > 2) {
                result.put("PrevReversedCombined",
                        combinedReversedMeets.substring(0, combinedReversedMeets.length() - 2));
            } else {
                result.put("PrevReversedCombined", combinedReversedMeets);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public String getSeasonFromDate(int countryId, int leagueId, String gameDate) {
        String sql = "SELECT season FROM SoccerResults WHERE DATE(gameDate) = ? AND countryId = ? AND leagueId = ? LIMIT 1";
        String returnValue = "";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            if (Strings.isNullOrEmpty(gameDate)) {
                stat.setString(1, new SimpleDateFormat(DATE_FORMAT).format(new Date()));
            } else {
                stat.setString(1, gameDate);
            }
            stat.setInt(2, countryId);
            stat.setInt(3, leagueId);

            ResultSet rs = stat.executeQuery();
            while (rs.next()) {
                returnValue = rs.getString(SEASON);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return returnValue;
    }

    public List<SimpleEntry<Integer, String>> getSports() {

        final ArrayList<AbstractMap.SimpleEntry<Integer, String>> sports = Lists.newArrayList();
        final String sql = "SELECT id, name FROM Sport";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {

            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                final SimpleEntry<Integer, String> entry = new SimpleEntry<>(rs.getInt(Constants.ID),
                        rs.getString("name"));
                sports.add(entry);
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return sports;
    }

    public List<OverUnder> getStatsOverUnder(int leagueId) {
        return getStatsOverUnder(leagueId, "");
    }

    public List<OverUnder> getStatsOverUnder(int leagueId, String gameDate) {

        final DecimalFormat df = new DecimalFormat("##.##");
        df.setRoundingMode(RoundingMode.HALF_DOWN);
        final ArrayList<OverUnder> result = Lists.newArrayList();

        final String sql = "SELECT ((sHome.avgScored * sAway.avgConceded) + (sAway.avgScored * sHome.avgConceded)) as diff, (homeScore + awayScore) as numGoals "
                + "FROM SoccerResults "
                + "INNER JOIN (SELECT homeTeamId, AVG(homeScore) as avgScored, AVG(awayScore) as avgConceded FROM SoccerResults WHERE homeScore != -1 AND homeTeamId = SoccerResults.homeTeamId AND DATE(gameDate) < ? AND season = ? AND leagueId = ? GROUP BY homeTeamId) as sHome ON SoccerResults.homeTeamId = sHome.homeTeamId "
                + "INNER JOIN (SELECT awayTeamId, AVG(homeScore) as avgConceded, AVG(awayScore) as avgScored FROM SoccerResults WHERE awayScore != -1 AND awayTeamId = SoccerResults.awayTeamId AND DATE(gameDate) < ? AND season = ? AND leagueId = ? GROUP BY awayTeamId) as sAway ON SoccerResults.awayTeamId = sAway.awayTeamId "
                + "WHERE homeScore != -1 AND awayScore != -1 AND leagueId = ? AND DATE(gameDate) < ? AND season = ? "
                + "ORDER BY diff ASC";
        List<String> allSeasons = getAllSeasons(leagueId);

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            final String dateString;
            if (Strings.isNullOrEmpty(gameDate)) {
                dateString = new SimpleDateFormat(DATE_FORMAT).format(new Date());
            } else {
                dateString = gameDate;
            }
            for (String season : allSeasons) {
                stat.setString(1, dateString);
                stat.setString(2, season);
                stat.setInt(3, leagueId);
                stat.setString(4, dateString);
                stat.setString(5, season);
                stat.setInt(6, leagueId);

                stat.setInt(7, leagueId);
                stat.setString(8, dateString);
                stat.setString(9, season);

                final ResultSet rs = stat.executeQuery();

                while (rs.next()) {
                    final float diff = rs.getFloat("diff");
                    final int numGoals = rs.getInt("numGoals");
                    final Float formatted = round(BigDecimal.valueOf(diff), INCREMENT, RoundingMode.HALF_UP)
                            .floatValue();

                    final OverUnder entry = result.stream().filter(ou -> ou.getKey().compareTo(formatted) == 0)
                            .findFirst()
                            .orElse(new OverUnder(formatted));
                    entry.addGoalStat(numGoals);
                    result.add(entry);
                }
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public List<OverUnder> getStatsOverUnderWithDrawStats(int leagueId, String gameDate) {
        final DecimalFormat df = new DecimalFormat("##.##");
        df.setRoundingMode(RoundingMode.HALF_DOWN);
        final ArrayList<OverUnder> result = Lists.newArrayList();

        final String sql = "SELECT (homeScore + awayScore) as numGoals, count(case when homeScore = awayScore then 1 end) draws, ROUND((sHome.avgScored * sAway.avgConceded) + (sAway.avgScored * sHome.avgConceded),1) roundedDiff, count(*) as numGames "
                + "FROM SoccerResults "
                + "INNER JOIN (SELECT homeTeamId, AVG(homeScore) as avgScored, AVG(awayScore) as avgConceded FROM SoccerResults WHERE homeScore != -1 AND homeTeamId = SoccerResults.homeTeamId AND DATE(gameDate) < ? AND leagueId = ? GROUP BY homeTeamId) as sHome ON SoccerResults.homeTeamId = sHome.homeTeamId "
                + "INNER JOIN (SELECT awayTeamId, AVG(homeScore) as avgConceded, AVG(awayScore) as avgScored FROM SoccerResults WHERE awayScore != -1 AND awayTeamId = SoccerResults.awayTeamId AND DATE(gameDate) < ? AND leagueId = ? GROUP BY awayTeamId) as sAway ON SoccerResults.awayTeamId = sAway.awayTeamId "
                + "WHERE homeScore != -1 AND awayScore != -1 AND leagueId = ? AND DATE(gameDate) < ? GROUP BY roundedDiff;";

        List<String> allSeasons = getAllSeasons(leagueId);
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            final String dateString;
            if (Strings.isNullOrEmpty(gameDate)) {
                dateString = new SimpleDateFormat(DATE_FORMAT).format(new Date());
            } else {
                dateString = gameDate;
            }
            for (String season : allSeasons) {
                stat.setString(1, dateString);
                stat.setInt(2, leagueId);
                stat.setString(3, dateString);
                stat.setInt(4, leagueId);

                stat.setInt(5, leagueId);
                stat.setString(6, dateString);

                final ResultSet rs = stat.executeQuery();

                while (rs.next()) {
                    final float diff = rs.getFloat("roundedDiff");
                    final int numGoals = rs.getInt("numGoals");

                    final OverUnder entry = result.stream().filter(ou -> ou.getKey().compareTo(diff) == 0)
                            .findFirst()
                            .orElse(new OverUnder(diff));
                    entry.addGoalStat(numGoals);
                    entry.setDraws(rs.getInt(DRAWS));
                    entry.setTotalGames(rs.getInt("numGames"));
                    result.add(entry);
                }
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public Team getTeam(int teamId) {
        Team result = null;
        String sql = "SELECT t.*, c.name as countryName, l.name AS leagueName FROM Team t "
                + "INNER JOIN Country c ON t.countryId = c.id "
                + "INNER JOIN League l ON t.leagueId = l.id "
                + "WHERE t.id = ? ";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, teamId);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result = new Team(rs.getInt("id"), rs.getString("name"), rs.getInt("countryId"),
                        rs.getString(COUNTRY_NAME), rs.getInt("leagueId"), rs.getString(LEAGUE_NAME));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public TeamResults getTeamResults(int teamId, int numResults, boolean isHomeTeam) {
        final String sql;
        final TeamResults tr = new TeamResults();
        if (isHomeTeam) {
            sql = "SELECT count(case when homeScore > awayScore then 1 end) wins, "
                    + "count(case when awayScore > homeScore then 1 end) lost, "
                    + "count(case when homeScore = awayScore then 1 end) draws "
                    + "FROM (SELECT * FROM SoccerResults WHERE homeTeamId = ? AND "
                    + "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < DATE(NOW()) ORDER BY gameDate DESC LIMIT ?) as t";
        } else {
            sql = "SELECT count(case when homeScore < awayScore then 1 end) wins, "
                    + "count(case when awayScore < homeScore then 1 end) lost, "
                    + "count(case when homeScore = awayScore then 1 end) draws "
                    + "FROM (SELECT * FROM SoccerResults WHERE awayTeamId = ? AND "
                    + "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < DATE(NOW()) ORDER BY gameDate DESC LIMIT ?) as t";
        }

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, teamId);
            stat.setInt(2, numResults);
            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                final int draws = rs.getInt(DRAWS);
                final int wins = rs.getInt("wins");
                final int lost = rs.getInt("lost");
                tr.setDraws(draws);
                tr.setWins(wins);
                tr.setLosses(lost);
                tr.setCount(wins + draws + lost);
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return tr;
    }

    public TeamResults getTeamResultsTest(int teamId, int numResults, boolean isHomeTeam, String date) {
        final String sql;
        final TeamResults tr = new TeamResults();
        if (isHomeTeam) {
            sql = "SELECT count(case when homeScore > awayScore then 1 end) wins, "
                    + "count(case when awayScore > homeScore then 1 end) lost, "
                    + "count(case when homeScore = awayScore then 1 end) draws "
                    + "FROM (SELECT * FROM SoccerResults WHERE homeTeamId = ? AND "
                    + "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) as t";
        } else {
            sql = "SELECT count(case when homeScore < awayScore then 1 end) wins, "
                    + "count(case when awayScore < homeScore then 1 end) lost, "
                    + "count(case when homeScore = awayScore then 1 end) draws "
                    + "FROM (SELECT * FROM SoccerResults WHERE awayTeamId = ? AND "
                    + "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) as t";
        }

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, teamId);
            stat.setString(2, date);
            stat.setInt(3, numResults);
            final ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                final int draws = rs.getInt(DRAWS);
                final int wins = rs.getInt("wins");
                final int lost = rs.getInt("lost");
                tr.setDraws(draws);
                tr.setWins(wins);
                tr.setLosses(lost);
                tr.setCount(wins + draws + lost);
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return tr;
    }

    public List<SoccerMatch> getUpcomingMatches(String sportResultTable) {
        final ArrayList<SoccerMatch> matches = Lists.newArrayList();
        final String dateSql;
        dateSql = " AND DATE(gameDate) >= DATE(now())";

        final String sql = "SELECT res.id, homeTeamId, awayTeamId, homeScore, awayScore, overtime, odds1, oddsX, odds2, gameDate, season, res.leagueId, res.countryId, "
                + "hTeam.name as homeTeamName, aTeam.name as awayTeamName, " + "league.name as leagueName, "
                + "country.name as countryName, "
                + "country.prio as prio " + "FROM " + sportResultTable + " as res "
                + "Join Team as hTeam ON res.homeTeamId = hTeam.id "
                + "Join Team as aTeam ON res.awayTeamId = aTeam.id "
                + "Join League as league ON res.leagueId = league.id "
                + "Join Country as country ON res.countryId = country.id " + "WHERE homeScore = -1 " + dateSql
                + "AND league.name NOT LIKE '%cup%' AND league.name NOT LIKE '%group%' AND league.prio = 1 "
                + "ORDER BY country.prio DESC, country.name ASC";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {

            final ResultSet rs = stat.executeQuery();
            while (rs.next()) {
                final SoccerMatch sm = new SoccerMatch();
                final Team homeTeam = new Team();
                final Team awayTeam = new Team();

                homeTeam.setTeamId(rs.getInt(Constants.HOME_TEAM_ID));
                awayTeam.setTeamId(rs.getInt(Constants.AWAY_TEAM_ID));
                homeTeam.setTeamName(rs.getString(Constants.HOME_TEAM_NAME));
                awayTeam.setTeamName(rs.getString(Constants.AWAY_TEAM_NAME));
                homeTeam.setTeamLeagueId(rs.getInt(Constants.LEAGUE_ID));
                awayTeam.setTeamLeagueId(rs.getInt(Constants.LEAGUE_ID));
                homeTeam.setTeamLeague(rs.getString(Constants.LEAGUE_NAME));
                awayTeam.setTeamLeague(rs.getString(Constants.LEAGUE_NAME));
                homeTeam.setCountryId(rs.getInt(Constants.COUNTRY_ID));
                awayTeam.setCountryId(rs.getInt(Constants.COUNTRY_ID));
                homeTeam.setCountryName(rs.getString(Constants.COUNTRY_NAME));
                awayTeam.setCountryName(rs.getString(Constants.COUNTRY_NAME));

                sm.setAwayScore(rs.getInt(Constants.AWAY_SCORE));
                sm.setHomeScore(rs.getInt(Constants.HOME_SCORE));
                sm.setHomeTeam(homeTeam);
                sm.setAwayTeam(awayTeam);
                sm.setMatchId(rs.getInt(Constants.ID));
                sm.setOdds1(rs.getFloat(Constants.ODDS_1));
                sm.setOddsX(rs.getFloat(Constants.ODDS_X));
                sm.setOdds2(rs.getFloat(Constants.ODDS_2));
                sm.setGameDate(LocalDateTime.parse(rs.getString(Constants.GAME_DATE)));
                sm.setCountryPrio(rs.getBoolean("prio"));

                matches.add(sm);
            }

        } catch (final SQLException e) {
            e.printStackTrace();
        }

        return matches;
    }

    public BigDecimal round(BigDecimal value, BigDecimal increment, RoundingMode roundingMode) {
        if (increment.signum() == 0) {
            // 0 increment does not make much sense, but prevent division by 0
            return value;
        } else {
            final BigDecimal divided = value.divide(increment, 0, roundingMode);
            final BigDecimal result = divided.multiply(increment);
            return result.setScale(2, RoundingMode.HALF_UP);
        }
    }

    public void setBetCovered(int id, int coveredBetId) {
        String sql = "UPDATE AnalysisBetTable SET coveredBetId = ? WHERE id = ?";
        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, coveredBetId);
            stat.setInt(2, id);

            stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void setTeamLookbackAway(int teamId, int lookbackAway) {
        final String sql = "UPDATE Team SET lookbackAway = ? WHERE id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, lookbackAway);
            stat.setInt(2, teamId);

            stat.executeUpdate();

        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    public void setTeamLookbackDraw(int teamId, int lookbackDraw) {
        final String sql = "UPDATE Team SET lookbackDraw = ? WHERE id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, lookbackDraw);
            stat.setInt(2, teamId);

            stat.executeUpdate();

        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    public void setTeamLookbackHome(int teamId, int lookbackHome) {
        final String sql = "UPDATE Team SET lookbackHome = ? WHERE id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, lookbackHome);
            stat.setInt(2, teamId);

            stat.executeUpdate();

        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    public void setTeamMarginAway(int teamId, int marginAway) {
        final String sql = "UPDATE Team SET marginAway = ? WHERE id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, marginAway);
            stat.setInt(2, teamId);

            stat.executeUpdate();

        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    public void setTeamMarginDraw(int teamId, int marginDraw) {
        final String sql = "UPDATE Team SET marginDraw = ? WHERE id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, marginDraw);
            stat.setInt(2, teamId);

            stat.executeUpdate();

        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    public void setTeamMarginHome(int teamId, int marginHome) {
        final String sql = "UPDATE Team SET marginHome = ? WHERE id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, marginHome);
            stat.setInt(2, teamId);

            stat.executeUpdate();

        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateBetBaseAmount(String value) {
        String sql = "INSERT INTO Settings (name, value) VALUES (?, ?) ON DUPLICATE KEY UPDATE value = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setString(1, "BetBaseAmount");
            stat.setString(2, value);
            stat.setString(3, value);

            stat.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateBetStatus(int betId, Status newStatus) {
        String sql = "UPDATE AnalysisBetTable SET status = ? WHERE id = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setString(1, newStatus.name());
            stat.setInt(2, betId);

            stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private List<String> getAllSeasons(int leagueId) {
        List<String> returnValue = new ArrayList<>();
        String sql = "SELECT distinct(season) FROM SoccerResults WHERE leagueId = ?";

        try (PreparedStatement stat = conn.prepareStatement(sql)) {
            stat.setInt(1, leagueId);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                returnValue.add(rs.getString(SEASON));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return returnValue;
    }
}