<?php

class League {
    private $db;

    public function __construct() {
        $this->db = new Database;
    }

    public function create($data) {
        $this->db->query('INSERT INTO leagues (name, country, season, manager_id, logo, reg_fee_total, reg_deposit_pct, reg_inst2_pct, reg_inst2_games, reg_inst3_pct, reg_inst3_games) VALUES (:name, :country, :season, :manager_id, :logo, :reg_fee_total, :reg_deposit_pct, :reg_inst2_pct, :reg_inst2_games, :reg_inst3_pct, :reg_inst3_games)');
        
        // Bind values
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':country', $data['country']);
        $this->db->bind(':season', $data['season']);
        $this->db->bind(':logo', $data['logo']);
        $this->db->bind(':manager_id', $data['manager_id'] ?? null);
        $this->db->bind(':reg_fee_total', $data['reg_fee_total'] ?? 0);
        $this->db->bind(':reg_deposit_pct', $data['reg_deposit_pct'] ?? 50);
        $this->db->bind(':reg_inst2_pct', $data['reg_inst2_pct'] ?? 30);
        $this->db->bind(':reg_inst2_games', $data['reg_inst2_games'] ?? 5);
        $this->db->bind(':reg_inst3_pct', $data['reg_inst3_pct'] ?? 20);
        $this->db->bind(':reg_inst3_games', $data['reg_inst3_games'] ?? 10);

        // Execute
        if($this->db->execute()){
            return $this->db->lastInsertId();
        } else {
            return false;
        }
    }

    public function getAllForAdmin() {
        $this->db->query('
            SELECT
                l.id,
                l.name,
                l.country,
                l.season,
                l.logo,
                u.first_name as manager_first_name,
                u.last_name as manager_last_name
            FROM leagues l
            LEFT JOIN users u ON l.manager_id = u.id
            ORDER BY l.name ASC
        ');
        return $this->db->resultSet();
    }

    public function getById($id) {
        $this->db->query('SELECT * FROM leagues WHERE id = :id');
        $this->db->bind(':id', $id);
        $row = $this->db->single();

        if ($this->db->rowCount() > 0) {
            return $row;
        } else {
            return false;
        }
    }

    public function update($data) {
        $this->db->query('UPDATE leagues SET name = :name, country = :country, season = :season, manager_id = :manager_id, logo = :logo, reg_fee_total = :reg_fee_total, reg_deposit_pct = :reg_deposit_pct, reg_inst2_pct = :reg_inst2_pct, reg_inst2_games = :reg_inst2_games, reg_inst3_pct = :reg_inst3_pct, reg_inst3_games = :reg_inst3_games WHERE id = :id');
        
        // Bind values
        $this->db->bind(':id', $data['id']);
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':country', $data['country']);
        $this->db->bind(':season', $data['season']);
        $this->db->bind(':logo', $data['logo']);
        // Handle case where no manager is selected
        $this->db->bind(':manager_id', !empty($data['manager_id']) ? $data['manager_id'] : null, PDO::PARAM_INT);
        $this->db->bind(':reg_fee_total', $data['reg_fee_total']);
        $this->db->bind(':reg_deposit_pct', $data['reg_deposit_pct']);
        $this->db->bind(':reg_inst2_pct', $data['reg_inst2_pct']);
        $this->db->bind(':reg_inst2_games', $data['reg_inst2_games']);
        $this->db->bind(':reg_inst3_pct', $data['reg_inst3_pct']);
        $this->db->bind(':reg_inst3_games', $data['reg_inst3_games']);

        // Execute
        return $this->db->execute();
    }

    // Get all leagues for dropdowns
    public function getAll() {
        $this->db->query('SELECT id, name FROM leagues ORDER BY name ASC');
        return $this->db->resultSet();
    }

    // Get all leagues for a specific season
    public function getAllBySeason($season_name) {
        $this->db->query('SELECT id, name FROM leagues WHERE season = :season_name ORDER BY name ASC');
        $this->db->bind(':season_name', $season_name);
        return $this->db->resultSet();
    }

    /**
     * Calculates the league table. Can be filtered for overall, home, or away stats.
     * @param int $league_id The ID of the league.
     * @param string $location 'all', 'home', or 'away'.
     * @param int|null $exclude_fixture_id An optional fixture ID to exclude from calculations (for live tables).
     * @return array The calculated league table.
     */
    public function getTable($league_id, $location = 'all', $exclude_fixture_id = null) {
        // 1. Get all teams in the league
        $this->db->query('SELECT t.id as team_id, c.name as team_name, c.logo FROM teams t JOIN clubs c ON t.club_id = c.id WHERE c.league_id = :league_id');
        $this->db->bind(':league_id', $league_id);
        $teams = $this->db->resultSet();

        // 2. Get all finished fixtures for the league
        $sql = "SELECT * FROM fixtures WHERE league_id = :league_id AND status = 'finished'";
        if ($exclude_fixture_id) {
            $sql .= " AND id != " . (int)$exclude_fixture_id;
        }
        $this->db->query($sql);
        $this->db->bind(':league_id', $league_id);
        $fixtures = $this->db->resultSet();

        // 3. Initialize table data in PHP
        $table = [];
        foreach ($teams as $team) {
            $table[$team->team_id] = (object)[
                'team_id' => $team->team_id,
                'team_name' => $team->team_name,
                'logo' => $team->logo,
                'played' => 0, 'wins' => 0, 'draws' => 0, 'losses' => 0, 'form' => [],
                'goals_for' => 0, 'goals_against' => 0, 'goal_difference' => 0, 'points' => 0
            ];
        }

        // 4. Process fixtures and calculate stats
        foreach ($fixtures as $fixture) {
            $home_id = $fixture->home_team_id;
            $away_id = $fixture->away_team_id;
            $home_score = $fixture->home_team_score;
            $away_score = $fixture->away_team_score;

            // Home Team Stats
            if (isset($table[$home_id]) && ($location === 'all' || $location === 'home')) {
                $table[$home_id]->played++;
                $table[$home_id]->goals_for += $home_score;
                $table[$home_id]->goals_against += $away_score;

                if ($home_score > $away_score) { // Home win
                    $table[$home_id]->wins++;
                    $table[$home_id]->points += 3;
                    $table[$home_id]->form[] = 'W';
                } elseif ($home_score < $away_score) { // Home loss
                    $table[$home_id]->losses++;
                    $table[$home_id]->form[] = 'L';
                } else { // Draw
                    $table[$home_id]->draws++;
                    $table[$home_id]->points += 1;
                    $table[$home_id]->form[] = 'D';
                }
            }

            // Away Team Stats
            if (isset($table[$away_id]) && ($location === 'all' || $location === 'away')) {
                $table[$away_id]->played++;
                $table[$away_id]->goals_for += $away_score;
                $table[$away_id]->goals_against += $home_score;

                if ($away_score > $home_score) { // Away win
                    $table[$away_id]->wins++;
                    $table[$away_id]->points += 3;
                    $table[$away_id]->form[] = 'W';
                } elseif ($away_score < $home_score) { // Away loss
                    $table[$away_id]->losses++;
                    $table[$away_id]->form[] = 'L';
                } else { // Draw
                    $table[$away_id]->draws++;
                    $table[$away_id]->points += 1;
                    $table[$away_id]->form[] = 'D';
                }
            }
        }

        // 5. Calculate GD and convert to array
        $results = array_values($table);
        foreach ($results as $row) {
            $row->goal_difference = $row->goals_for - $row->goals_against;
        }

        // 6. Sort the final table using the dedicated helper method
        return $this->_sortLeagueTable($results, $fixtures);
    }

    public function getLiveTable($fixture_id) {
        // Robustness check: If no valid fixture ID is provided, we can't generate a live table.
        if (empty($fixture_id) || !is_numeric($fixture_id)) {
            // This case should be handled by the calling script, but as a safeguard, return null.
            return null;
        }

        $fixtureModel = new Fixture();
        $fixture = $fixtureModel->findById($fixture_id);

        if (!$fixture || !in_array($fixture->status, ['live', 'finished', 'half_time'])) {
            // If match isn't live or finished, just return the standard table
            return $this->getTable($fixture->league_id);
        }

        // Get the base table based on all *other* finished matches
        $baseTable = $this->getTable($fixture->league_id, 'all', $fixture_id);

        // Find the home and away teams in the table
        $homeTeamRow = null;
        $awayTeamRow = null;
        foreach ($baseTable as $row) {
            if ($row->team_id == $fixture->home_team_id) {
                $homeTeamRow = $row;
            }
            if ($row->team_id == $fixture->away_team_id) {
                $awayTeamRow = $row;
            }
        }

        if (!$homeTeamRow || !$awayTeamRow) {
            return $baseTable; // Should not happen, but as a safeguard
        }

        // Apply the live fixture result
        $homeTeamRow->played++;
        $awayTeamRow->played++;
        $homeTeamRow->goals_for += $fixture->home_team_score;
        $awayTeamRow->goals_for += $fixture->away_team_score;
        $homeTeamRow->goals_against += $fixture->away_team_score;
        $awayTeamRow->goals_against += $fixture->home_team_score;

        if ($fixture->home_team_score > $fixture->away_team_score) {
            $homeTeamRow->wins++;
            $awayTeamRow->losses++;
            $homeTeamRow->points += 3;
        } elseif ($fixture->away_team_score > $fixture->home_team_score) {
            $awayTeamRow->wins++;
            $homeTeamRow->losses++;
            $awayTeamRow->points += 3;
        } else {
            $homeTeamRow->draws++;
            $awayTeamRow->draws++;
            $homeTeamRow->points += 1;
            $awayTeamRow->points += 1;
        }

        $homeTeamRow->goal_difference = $homeTeamRow->goals_for - $homeTeamRow->goals_against;
        $awayTeamRow->goal_difference = $awayTeamRow->goals_for - $awayTeamRow->goals_against;

        // Re-sort the table with the live results
        // Note: For simplicity, we won't calculate head-to-head for live tables.
        // We pass an empty array for fixtures to skip that logic.
        return $this->_sortLeagueTable($baseTable, []);
    }

    /**
     * Private helper method to sort a league table array.
     * Sorts by: Points (desc), Goal Difference (desc), Goals For (desc), Team Name (asc).
     * @param array $table The league table data to sort.
     * @return array The sorted league table.
     */
    private function _sortLeagueTable($table, $fixtures) {
        usort($table, function ($a, $b) use ($fixtures) {
            // 1. Points
            if ($a->points !== $b->points) {
                return $b->points <=> $a->points;
            }
            // 2. Goal Difference (Overall)
            if ($a->goal_difference !== $b->goal_difference) {
                return $b->goal_difference <=> $a->goal_difference;
            }
            // 3. Goals For (Overall)
            if ($a->goals_for !== $b->goals_for) {
                return $b->goals_for <=> $a->goals_for;
            }

            // 4. Head-to-Head Comparison (if fixtures are available)
            if (!empty($fixtures)) {
                $h2h_points_a = 0;
                $h2h_points_b = 0;
                $h2h_gd_a = 0;
                $h2h_gd_b = 0;

                foreach ($fixtures as $fixture) {
                    if (($fixture->home_team_id == $a->team_id && $fixture->away_team_id == $b->team_id)) {
                        // A is home, B is away
                        $h2h_gd_a += ($fixture->home_team_score - $fixture->away_team_score);
                        $h2h_gd_b += ($fixture->away_team_score - $fixture->home_team_score);
                        if ($fixture->home_team_score > $fixture->away_team_score) $h2h_points_a += 3;
                        elseif ($fixture->away_team_score > $fixture->home_team_score) $h2h_points_b += 3;
                        else { $h2h_points_a++; $h2h_points_b++; }
                    } elseif (($fixture->home_team_id == $b->team_id && $fixture->away_team_id == $a->team_id)) {
                        // B is home, A is away
                        $h2h_gd_b += ($fixture->home_team_score - $fixture->away_team_score);
                        $h2h_gd_a += ($fixture->away_team_score - $fixture->home_team_score);
                        if ($fixture->home_team_score > $fixture->away_team_score) $h2h_points_b += 3;
                        elseif ($fixture->away_team_score > $fixture->home_team_score) $h2h_points_a += 3;
                        else { $h2h_points_a++; $h2h_points_b++; }
                    }
                }

                // Compare head-to-head points
                if ($h2h_points_a !== $h2h_points_b) {
                    return $h2h_points_b <=> $h2h_points_a;
                }

                // Compare head-to-head goal difference
                if ($h2h_gd_a !== $h2h_gd_b) {
                    return $h2h_gd_b <=> $h2h_gd_a;
                }
            }

            // 5. Team Name (alphabetical) as final tie-breaker
            return strcmp($a->team_name, $b->team_name);
        });
        return $table;
    }
}
?>