<?php

include_once 'Team.php';
include_once 'SoccerMatch.php';
include_once 'League.php';

class Database
{
    private $conn;
    function openConnection()
    {
        $dbhost = 'nordh.xyz';
        $dbuser = 'OddsNy';
        $dbPass = 'Odds1_Ny_Password';
        $db = 'new_odds';

        try {
            $this->conn = new PDO(
                "mysql:host=$dbhost;dbname=$db",
                $dbuser,
                $dbPass
            );
            $this->conn->setAttribute(
                PDO::ATTR_ERRMODE,
                PDO::ERRMODE_EXCEPTION
            );
        } catch (Exception $e) {
            echo 'Exception ' . $e->getMessage();
        }
    }

    function closeConnection($conn)
    {
        $conn = null;
    }

    function getConnection()
    {
        if (!isset($this->conn)) {
            $this->openConnection();
        }
        return $this->conn;
    }

    function getTodaysPrioMatches()
    {
        $this->openConnection();

        $result = [];
        $sql = 'SELECT sr.*,
            ht.id as homeTeamId, ht.name as homeTeamName, ht.countryId as homeTeamCountryId, ht.leagueId as homeTeamLeagueId,
            awt.id as awayTeamId, awt.name as awayTeamName, awt.countryId as awayTeamCountryId, awt.leagueId as awayTeamLeagueId,
            l.name as leagueName,
            c.name as countryName
            FROM SoccerResults sr
            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
            INNER JOIN Country c ON c.id = sr.countryId
            WHERE sr.leagueId IN (SELECT id FROM League WHERE prio = 1) AND DATE(gameDate) = DATE(NOW())';

        $stat = $this->conn->query($sql);
        $matches = $stat->fetchAll();

        foreach ($matches as $match) {
            $homeTeam = new Team(
                $match['homeTeamId'],
                $match['homeTeamName'],
                $match['homeTeamCountryId'],
                $match['countryName'],
                $match['homeTeamLeagueId'],
                $match['leagueName']
            );
            $awayTeam = new Team(
                $match['awayTeamId'],
                $match['awayTeamName'],
                $match['awayTeamCountryId'],
                $match['countryName'],
                $match['awayTeamLeagueId'],
                $match['leagueName']
            );
            $m = new SoccerMatch(
                $match['id'],
                $homeTeam,
                $awayTeam,
                $match['odds1'],
                $match['oddsX'],
                $match['odds2'],
                $match['homeScore'],
                $match['awayScore'],
                $match['gameDate'],
                $match['season']
            );

            $result[] = $m;
        }

        $this->closeConnection($this->conn);
        return $result;
    }

    public function getLeagueTable($leagueId, $season, $countryId, $date)
    {
        $this->openConnection();
        $result = [];

        $sql = "SELECT teamName.name AS teamName,
        COUNT(*) AS played,
        SUM(CASE WHEN homeScore > awayScore THEN 1 ELSE 0 END) AS wins,
        SUM(CASE WHEN awayScore > homeScore THEN 1 ELSE 0 END) AS lost,
        SUM(CASE WHEN homeScore = awayScore THEN 1 ELSE 0 END) AS draws,
        SUM(homeScore) AS homeScore,
        SUM(awayScore) AS awayScore,
        SUM(homeScore - awayScore) AS goal_diff,
        SUM(CASE WHEN homeScore > awayScore THEN 3 ELSE 0 END + CASE WHEN homeScore = awayScore THEN 1 ELSE 0 END) AS score,
        season
 FROM
   (SELECT hometeamId AS team,
           homeScore,
           awayScore,
           season,
           gameDate,
           leagueId AS league,
           countryId AS country
    FROM SoccerResults
    WHERE homeScore != -1 AND awayScore != -1 AND DATE(gameDate) < DATE(?)
    UNION ALL
    SELECT awayteamId AS team,
           awayScore,
           homeScore,
           season,
           gameDate,
           leagueId AS league,
           countryId AS country
    FROM SoccerResults
    WHERE homeScore != -1 AND awayScore != -1 AND DATE(gameDate) < DATE(?)) a
 INNER JOIN Team AS teamName ON a.team = teamName.id
 WHERE season = ? AND league = ? AND country = ?
 GROUP BY teamName.id
 ORDER BY score DESC, goal_diff DESC;";

        $stmt = $this->conn->prepare($sql);

        $stmt->bindParam(1, $season);
        $stmt->bindParam(2, $leagueId);
        $stmt->bindParam(3, $countryId);
        $stmt->bindParam(4, $date);

        $stmt->execute();

        while ($row = $stmt->fetch()) {
            $ts = [
                'teamName' => $row['teamName'],
                'wins' => $row['wins'],
                'lost' => $row['lost'],
                'draws' => $row['draws'],
                'score' => $row['score'],
                'homeScore' => $row['homeScore'],
                'awayScore' => $row['awayScore'],
                'goal_diff' => $row['goal_diff'],
            ];
            array_push($result, $ts);
        }

        $this->closeConnection($this->conn);
        return $result;
    }

    function getLeagueInfo($leagueId)
    {
        $this->openConnection();
        $sql = 'SELECT * FROM League WHERE id = ?';
        $result = null;
        try {
            $stat = $this->conn->prepare($sql);
            $stat->bindParam(1, $leagueId);
            $stat->execute();

            while ($row = $stat->fetch()) {
                $result = new League(
                    $row['id'],
                    $row['name'],
                    $row['scoringDiffLastGame'],
                    $row['scoringTotal'],
                    $row['winLossRatioHomeAndAway'],
                    $row['winLossRatio'],
                    $row['drawDiffHomeAway'],
                    $row['drawDiffTotalGoals'],
                    $row['drawWinningForm'],
                    $row['drawWinningFormHomeAway']
                );
            }
        } catch (PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();
        }

        $this->closeConnection($this->conn);
        return $result;
    }
}