odds/OddsJavaFx/src/objects/SoccerMatchAnalysis.java

package objects;

import data.GuiMysql;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Optional;

public class SoccerMatchAnalysis extends SoccerMatch implements Serializable {

    GuiMysql database;
    private List<TeamStanding> leagueTable;
    private LocalDate leagueTableUpdated;
    private int scoringDiffValue;
    private int scoringTotal;
    private int winCount;
    private int winLossRatio;
    private String percentages;
    Percentages calculatedPercentages;
    private int scoringTotalValue;

    public SoccerMatchAnalysis(SoccerMatch match) {
        setMatchData(match);
        database = GuiMysql.getInstance();
    }

    public int getWinLossRatio() {
        return winLossRatio;
    }

    public String getPercentages() {
        return percentages;
    }

    public void setPercentages(String value) {
        percentages = value;
    }

    public void setWinLossRatio(int winLossRatio) {
        this.winLossRatio = winLossRatio;
    }

    public int getWinCount() {
        return winCount;
    }

    public void setWinCount(int winCount) {
        this.winCount = winCount;
    }

    public int getScoringTotal() {
        return scoringTotal;
    }

    public void setScoringTotal(int scoringTotal) {
        this.scoringTotal = scoringTotal;
    }

    public int getScoringDiffValue() {
        return scoringDiffValue;
    }

    public void setScoringDiffValue(int scoringDiffValue) {
        this.scoringDiffValue = scoringDiffValue;
    }

    /**
     * Antalet mål som hemma laget gjort de senaste "gamesLookback" matcherna -
     * antalet mål för bortalaget under samma period
     *
     * @param gamesLookback - hur många matcher bakåt i tiden som ska kontrolleras
     * @return Float med skillnaden i mål mellan lagen hemma/borta
     */
    public int getScoringDiffLastGames(int gamesLookback) {
        int result = 0;

        String sql = "SELECT * FROM " +
                "(SELECT * FROM SoccerResults WHERE homeTeamId = ? AND leagueId = ? AND DATE" +
                "(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) ht " +
                "UNION " +
                "(SELECT * FROM SoccerResults WHERE " +
                "awayTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? " +
                "ORDER BY gameDate DESC " +
                "LIMIT ?);";

        int homeRes = 0;
        int awayRes = 0;
        try (PreparedStatement stat = database.getDbConnection().prepareStatement(sql)) {
            stat.setInt(1, getHomeTeam().getTeamId());
            stat.setInt(2, getHomeTeam().getTeamLeagueId());
            stat.setString(3, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
            stat.setInt(4, gamesLookback);

            stat.setInt(5, getAwayTeam().getTeamId());
            stat.setInt(6, getAwayTeam().getTeamLeagueId());
            stat.setString(7, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
            stat.setInt(8, gamesLookback);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                if (getHomeTeam().getTeamId() == rs.getInt("homeTeamId")) {
                    homeRes += rs.getInt("homeScore");
                }
                if (getAwayTeam().getTeamId() == rs.getInt("awayTeamId")) {
                    awayRes += rs.getInt("awayScore");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        scoringDiffValue = homeRes - awayRes;
        return scoringDiffValue;
    }

    /**
     * Plocka fram antalet mål som ett specifikt lag gjort under de senaste
     * gamesLookback matcherna
     *
     * @param gamesLookback - hur många matcher bakåt i tiden som ska kontrolleras
     * @param homeTeam      - är det hemma laget som ska kontrolleras i matchen
     * @return antalet mål som är gjorda av bestämt lag.
     */
    public int scoringTotal(int gamesLookback, boolean homeTeam) {
        int result = 0;
        /*
         * String sql = "SELECT * FROM " +
         * "(SELECT * FROM SoccerResults WHERE homeTeamId = ? AND leagueId = ? AND DATE"
         * +
         * "(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) ht " + "UNION DISTINCT " +
         * "(SELECT * FROM " +
         * "SoccerResults WHERE awayTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC "
         * +
         * "LIMIT ?)";
         */

        String sql = "SELECT * FROM ( " +
                "SELECT * FROM SoccerResults " +
                "WHERE (homeTeamId = ? OR awayTeamId = ?) AND leagueId = ? AND gameDate < ? " +
                ") AS CombinedResults " +
                "ORDER BY gameDate DESC " +
                "LIMIT ?";

        try (PreparedStatement stat = database.getDbConnection().prepareStatement(sql)) {
            final Team team;
            if (homeTeam) {
                team = getHomeTeam();
            } else {
                team = getAwayTeam();
            }

            stat.setInt(1, team.getTeamId());
            stat.setInt(2, team.getTeamId());
            stat.setInt(3, team.getTeamLeagueId());
            stat.setString(4, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
            stat.setInt(5, gamesLookback);

            /*
             * stat.setInt(5, team.getTeamId());
             * stat.setInt(6, team.getTeamLeagueId());
             * stat.setString(7, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
             * stat.setInt(8, gamesLookback);
             */
            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                if (rs.getInt("homeTeamId") == team.getTeamId()) {
                    result += rs.getInt("homeScore");
                } else if (rs.getInt("awayTeamId") == team.getTeamId()) {
                    result += rs.getInt("awayScore");
                }
            }

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

    /**
     * Hämta tabell positionen för hemma eller borta laget
     *
     * @param homeTeam - är det hemma laget som ska kontrolleras för matchen?
     * @return position för specifierat lag
     */
    public int getTablePosition(boolean homeTeam) {
        int result = 0;
        updateLeagueTable();
        Optional<TeamStanding> standingOptional = leagueTable.stream().filter(
                p -> p.getTeamName().equals(homeTeam ? getHomeTeam().getTeamName() : getAwayTeam().getTeamName()))
                .findFirst();

        if (standingOptional.isPresent()) {
            TeamStanding standing = standingOptional.get();
            result = leagueTable.indexOf(standing);
        }
        return result;
    }

    private void updateLeagueTable() {
        if (!leagueTableUpdated.isEqual(getGameDate().toLocalDate())) {
            leagueTable = database.getLeagueTable(getHomeTeam().getTeamLeagueId(), getSeason(),
                    getHomeTeam().getCountryId(), getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));

            leagueTableUpdated = getGameDate().toLocalDate();
        }
    }

    /**
     * @return Integer - hur många platser det är mellan hemma och borta laget
     */
    public int diffInStanding() {
        int result = 0;
        updateLeagueTable();

        Optional<TeamStanding> homeTeamStandingOptional = leagueTable.stream()
                .filter(p -> p.getTeamName().equals(getHomeTeam().getTeamName())).findFirst();

        Optional<TeamStanding> awayTeamStandingOptional = leagueTable.stream()
                .filter(p -> p.getTeamName().equals(getAwayTeam().getTeamName())).findFirst();

        if (homeTeamStandingOptional.isPresent() && awayTeamStandingOptional.isPresent()) {
            TeamStanding homeStanding = homeTeamStandingOptional.get();
            TeamStanding awayStanding = awayTeamStandingOptional.get();

            result = leagueTable.indexOf(homeStanding) - leagueTable.indexOf(awayStanding);
        }
        return result;
    }

    /**
     * Vinst förlust ratio för om man är enbart hemma eller bortalag.
     *
     * @param gamesLookback
     * @param homeTeam
     * @return Integer där vinst ger +1 lika ger 0 och förlust get -1
     */
    public int winLossRatio(int gamesLookback, boolean homeTeam) {
        int result = 0;

        Team team = homeTeam ? getHomeTeam() : getAwayTeam();

        String teamSql = homeTeam ? "homeTeamId = ? " : "awayTeamId = ? ";
        String sql = "SELECT * FROM SoccerResults WHERE " + teamSql + "AND leagueId = ? AND DATE(gameDate) < ? ORDER " +
                "BY gameDate DESC LIMIT ?";

        try (PreparedStatement stat = database.getDbConnection().prepareStatement(sql)) {
            stat.setInt(1, team.getTeamId());
            stat.setInt(2, team.getTeamLeagueId());
            stat.setString(3, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
            stat.setInt(4, gamesLookback);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                int homeScore = rs.getInt("homeScore");
                int awayScore = rs.getInt("awayScore");
                if (homeTeam) {
                    if (homeScore > awayScore) {
                        result++;
                    } else if (homeScore < awayScore) {
                        result--;
                    }
                } else {
                    if (homeScore > awayScore) {
                        result--;
                    } else if (homeScore < awayScore) {
                        result++;
                    }
                }
            }

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

    public int winLossRatioHomeAndAway(int gamesLookback, boolean homeTeam) {
        int result = 0;

        String sql = "SELECT * FROM SoccerResults " +
                "WHERE (homeTeamId = ? OR awayTeamId = ?) " +
                "AND leagueId = ? " +
                "AND DATE(gameDate) < ? " +
                "ORDER BY gameDate DESC " +
                "LIMIT ?";
        /*
         * String sql = "SELECT * FROM " +
         * "(SELECT * FROM SoccerResults WHERE homeTeamId = ? AND leagueId = ? AND DATE"
         * +
         * "(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) a " + "UNION DISTINCT " +
         * "(SELECT * FROM " +
         * "SoccerResults WHERE awayTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC "
         * +
         * "LIMIT ?) " + "ORDER BY gameDate DESC " + "LIMIT ?";
         */

        Team team = homeTeam ? getHomeTeam() : getAwayTeam();
        try (PreparedStatement stat = database.getDbConnection().prepareStatement(sql)) {
            stat.setInt(1, team.getTeamId());
            stat.setInt(2, team.getTeamId());
            stat.setInt(3, team.getTeamLeagueId());
            stat.setString(4, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
            stat.setInt(5, gamesLookback);

            /*
             * stat.setInt(1, team.getTeamId());
             * stat.setInt(2, team.getTeamLeagueId());
             * stat.setString(3, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
             * stat.setInt(4, gamesLookback);
             * 
             * stat.setInt(5, team.getTeamId());
             * stat.setInt(6, team.getTeamLeagueId());
             * stat.setString(7, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
             * stat.setInt(8, gamesLookback);
             * 
             * stat.setInt(9, gamesLookback);
             */

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                if (rs.getInt("homeTeamId") == team.getTeamId() && rs.getInt("homeScore") > rs.getInt("awayScore")) {
                    result++;
                } else if (rs.getInt("awayTeamId") == team.getTeamId() && rs.getInt("awayScore") > rs.getInt(
                        "homeScore")) {
                    result++;
                } else {
                    result--;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public float goalsScoredHomeAndAway(boolean homeTeam, int gameLookback) {
        int result = 0;
        Team team = homeTeam ? getHomeTeam() : getAwayTeam();

        String sql = "SELECT * FROM SoccerResults " +
                "WHERE (" + (homeTeam ? "home" : "away") + "TeamId = ?) " +
                "  AND leagueId = ? " +
                "  AND DATE(gameDate) < ? " +
                "ORDER BY gameDate DESC " +
                "LIMIT ?";

        /*
         * String sql = "SELECT * FROM " +
         * "(SELECT * FROM SoccerResults WHERE homeTeamId = ? AND leagueId = ? AND DATE"
         * +
         * "(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) a " + "UNION ALL " +
         * "(SELECT * FROM SoccerResults " +
         * "WHERE awayTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) "
         * +
         * "ORDER BY gameDate DESC " + "LIMIT ?";
         */

        try (PreparedStatement stat = database.getDbConnection().prepareStatement(sql)) {
            stat.setInt(1, team.getTeamId());
            stat.setInt(2, team.getTeamLeagueId());
            stat.setString(3, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
            stat.setInt(4, gameLookback);
            /*
             * stat.setInt(1, team.getTeamId());
             * stat.setInt(2, team.getTeamLeagueId());
             * stat.setString(3, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
             * stat.setInt(4, gameLookback);
             * stat.setInt(5, team.getTeamId());
             * stat.setInt(6, team.getTeamLeagueId());
             * stat.setString(7, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
             * stat.setInt(8, gameLookback);
             * stat.setInt(9, gameLookback);
             */

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                if (rs.getInt("homeTeamId") == team.getTeamId()) {
                    result += rs.getInt("homeScore");
                }
                if (rs.getInt("awayTeamId") == team.getTeamId()) {
                    result += rs.getInt("awayScore");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // TODO Think Should the result be divided by lookback??
        // NEW TEST, devide by lookback (OR SIZE IF LESS THAN LOOKBACK?

        BigDecimal res = BigDecimal.valueOf((result / (float) gameLookback) * 2);
        res = res.setScale(1, RoundingMode.HALF_UP);

        DecimalFormat df = new DecimalFormat("#.00");
        return Float.valueOf(df.format(res.floatValue() / 2.0f).replaceAll(",", "."));
    }

    public int goalsScoredHomeOrAway(boolean homeTeam, int gameLookback) {
        int result = 0;
        Team team = homeTeam ? getHomeTeam() : getAwayTeam();
        String homeOrAway = homeTeam ? "home" : "away";
        String sql = "SELECT SUM(" + homeOrAway + "Score) FROM SoccerResults WHERE " + homeOrAway + "TeamId = ? AND " +
                "leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ? " + "ORDER BY gameDate DESC";

        try (PreparedStatement stat = database.getDbConnection().prepareStatement(sql)) {
            stat.setInt(1, team.getTeamId());
            stat.setInt(2, team.getTeamLeagueId());
            stat.setString(3, getGameDate().format(DateTimeFormatter.ISO_LOCAL_DATE));
            stat.setInt(4, gameLookback);

            ResultSet rs = stat.executeQuery();

            while (rs.next()) {
                result += rs.getInt(homeOrAway + "Score");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public int getScoringTotalValue() {
        return scoringTotalValue;
    }

    public void setScoringTotalValue(int value) {
        scoringTotalValue = value;
    }

    public Percentages calculateWinPercentages() {
        calculatedPercentages = new Percentages();
        League leagueInfo = database.getLeagueInfo(this.getHomeTeam().getTeamLeagueId());
        int homeScore = this.scoringTotal(leagueInfo.getScoringTotal(), true);
        int awayScore = this.scoringTotal(leagueInfo.getScoringTotal(), false);

        database.getTotalGoalStat(calculatedPercentages, leagueInfo.getLeagueId(), leagueInfo.getScoringTotal(),
                homeScore - awayScore);

        database.getScoringDiffLastGames(calculatedPercentages, leagueInfo.getLeagueId(),
                leagueInfo.getScoringDiffLastGame(),
                this.getScoringDiffLastGames(leagueInfo.getScoringDiffLastGame()));

        int winLossHAHome = this.winLossRatioHomeAndAway(leagueInfo.getWinLossRatioHomeAndAway(), true);
        int winLossHAAway = this.winLossRatioHomeAndAway(leagueInfo.getWinLossRatioHomeAndAway(), false);
        database.getWinLossRatioHomeAndAwayStatistics(calculatedPercentages, leagueInfo.getLeagueId(),
                leagueInfo.getWinLossRatioHomeAndAway(),
                winLossHAHome - winLossHAAway);

        int winLossHome = this.winLossRatio(leagueInfo.getWinLossRatio(), true);
        int winLossAway = this.winLossRatio(leagueInfo.getWinLossRatio(), false);
        this.winLossRatio(leagueInfo.getWinLossRatio(), false);
        database.getWinLossRatioStatisticsGames(calculatedPercentages, leagueInfo.getLeagueId(),
                leagueInfo.getWinLossRatio(), winLossHome - winLossAway);

        calculatedPercentages.calculatePercentages();

        return calculatedPercentages;
    }

    public Percentages getCalculatedPercentages() {
        if (calculatedPercentages == null) {
            calculatedPercentages = calculateWinPercentages();
        }
        return calculatedPercentages;
    }

    public void setCalculatedPercentages(Percentages calculatedPercentages) {
        this.calculatedPercentages = calculatedPercentages;
    }
}