<?php
mysqli_report( MYSQLI_REPORT_STRICT );

require_once __DIR__ . '/dbFunctions.php';

class WebDbConnection {
    private $dbCon;
    private $analyzerConnection;
    private $today;
    private $leagueName;
    public function __construct() {
        $this->dbCon = new dbFunctions();
    }
 
    private function getSqlAsArray($sql) {
        $mysql = $this->dbCon->getConnection();
        $result = $mysql->query( $sql );
        if ($result === false) {
            echo $mysql->error . " from sql " . $sql;
            return false;
        }
        $returnArray = array ();
        if (strpos( $sql, "UPDATE" ) === false) {
            foreach ( $result as $key => $value ) {
                $returnArray [$key] = $value;
            }
        }
        
        return $returnArray;
    }
  
    public function getCountries() {
        $mysql = $this->dbCon->getConnection();
        $sql = "SELECT id, name FROM Country WHERE id IN (SELECT countryId FROM SoccerResults WHERE DATE(gameDate) >= DATE(NOW())) GROUP BY name ORDER BY prio DESC, name ASC";

//         $result = $mysql->query( $sql );
//         if ($result === false) {
//             echo $result->error;
//         }
        
        return $this->getSqlAsArray($sql);
    }
    
    public function getLeagues() {
        $mysql = $this->dbCon->getConnection();
        $sql = "SELECT id, name, countryId FROM League WHERE id IN (SELECT leagueId FROM SoccerResults WHERE DATE(gameDate) >= DATE(NOW())) GROUP BY name ORDER BY prio DESC, name ASC";
        
//         $result = $mysql->query( $sql );
//         if ($result === false) {
//             echo $result->error;
//         }
        
        return $this->getSqlAsArray($sql);
    }
    
    public function getMatches($countryId, $leagueId ) {
        $mysql = $this->dbCon->getConnection();

        $sql = "SELECT res.*, " .
        "hTeam.name as homeTeamName, aTeam.name as awayTeamName " .
        "FROM SoccerResults as res " .
        "Join Team as hTeam ON res.homeTeam = hTeam.id " .
        "Join Team as aTeam ON res.awayTeam = aTeam.id " .
        "WHERE " .
        "DATE(gameDate) >= DATE(NOW()) " .
        "AND res.leagueId = " . $leagueId .
        " AND res.countryId = " . $countryId .
        " ORDER BY gameDate ASC";
        
        
        $result = $this->getSqlAsArray($sql);

        return $this->prepareMatches($result, $leagueId, $countryId);
    }
    
    private function prepareMatches($matches, $leagueId, $countryId) {
        for ($i = 0; $i < count($matches); $i++) {

            $match = $matches[$i];
            $league = $this->getLeagueInfo($leagueId);

            $lookback = $league[0]['lookback'];
            $betMargin = $league[0]['betMargin'];
            
            $homeTeamResults = $this->getTeamResults($match['homeTeam'], $lookback, true)[0];
            $awayTeamResults = $this->getTeamResults($match['awayTeam'], $lookback, false)[0];
            
            $homeTeamCount = $homeTeamResults['wins'] + $homeTeamResults['draws'] + $homeTeamResults['lost'];
            $awayTeamCount = $awayTeamResults['wins'] + $awayTeamResults['draws'] + $awayTeamResults['lost'];
            
            $homeWinPercent = round((($homeTeamResults['wins'] + $awayTeamResults['lost']) / ($homeTeamCount + $awayTeamCount)) * 100,2);
            $drawPercent = round((($homeTeamResults['draws'] + $awayTeamResults['draws']) / ($homeTeamCount + $awayTeamCount)) * 100, 2);
            $awayWinPercent = round((($awayTeamResults['wins'] + $homeTeamResults['lost']) / ($homeTeamCount + $awayTeamCount)) * 100, 2);
            
            $match['homeWinPercent'] = $homeWinPercent;
            $match['drawPercent'] = $drawPercent;
            $match['awayWinPercent'] = $awayWinPercent;
            $matches[$i] = $match;
        }
        return $matches;
    }
    
    public function getLeagueInfo($leagueId) {
        $sql = "SELECT * FROM League WHERE id = " . $leagueId;
        $result = $this->getSqlAsArray($sql);
        
        return $result;
    }
    
    public function getTeamResults($teamId, $numResults, $isHomeTeam) {
        $sql = "";
        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 homeTeam = " . $teamId . " AND " .
            "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < DATE(NOW()) ORDER BY gameDate DESC LIMIT " . $numResults . ") 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 awayTeam = " . $teamId . " AND " .
            "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < DATE(NOW()) ORDER BY gameDate DESC LIMIT " . $numResults . ") as t";
        }
        
        return $this->getSqlAsArray($sql);
    }
    
    public function addBet($matchId, $betOdds, $betAmount, $isBetOnHome) {
        $betHome = (strcmp($isBetOnHome, "on") == 0)?1:0;
        
        $sql = "INSERT INTO bets (matchId, odds, amount, isbetOnHomeTeam) VALUES ($matchId, $betOdds, $betAmount, $betHome)";
        
        $this->getSqlAsArray($sql);
    }
}