<?php

class Fantasy extends Model {

    public function getTeamByUserIdAndLeague($user_id, $season, $league_id) {
        $this->db->query("SELECT ft.*, 
                          (SELECT COALESCE(SUM(p.fantasy_price), 0) 
                           FROM fantasy_squads fs 
                           JOIN players p ON fs.player_id = p.user_id 
                           WHERE fs.fantasy_team_id = ft.id) as squad_value
                          FROM fantasy_teams ft 
                          WHERE ft.user_id = :user_id AND ft.season = :season AND ft.league_id = :league_id");
        $this->db->bind(':user_id', $user_id);
        $this->db->bind(':season', $season);
        $this->db->bind(':league_id', $league_id);
        return $this->db->single();
    }

    public function getTeamById($id) {
        $this->db->query("SELECT * FROM fantasy_teams WHERE id = :id");
        $this->db->bind(':id', $id);
        return $this->db->single();
    }

    public function create($data) {
        $this->db->query("INSERT INTO fantasy_teams (user_id, name, season, league_id, bank) VALUES (:user_id, :name, :season, :league_id, 100.0)");
        $this->db->bind(':user_id', $data['user_id']);
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':season', $data['season']);
        $this->db->bind(':league_id', $data['league_id']);
        if ($this->db->execute()) {
            return $this->db->lastInsertId();
        }
        return false;
    }

    public function updateBank($team_id, $amount) {
        $this->db->query("UPDATE fantasy_teams SET bank = :bank WHERE id = :id");
        $this->db->bind(':bank', $amount);
        $this->db->bind(':id', $team_id);
        return $this->db->execute();
    }

    public function getGlobalRank($team_id, $league_id) {
        // Simple rank based on total points
        $this->db->query("SELECT COUNT(*) + 1 as rank FROM fantasy_teams WHERE league_id = :league_id AND total_points > (SELECT total_points FROM fantasy_teams WHERE id = :id)");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':id', $team_id);
        $row = $this->db->single();
        return $row ? $row->rank : '-';
    }

    public function processTransfers($team_id, $transfers, $cost, $new_bank, $gameweek, $chip = null) {
        try {
            $this->db->beginTransaction();

            // Apply Wildcard Logic: If wildcard is played, transfers are free
            if ($chip === 'wildcard') {
                $cost = 0;
                // Logic to record chip usage could go here (e.g., insert into a chips_history table)
            }

            // Update Bank
            $this->updateBank($team_id, $new_bank);

            // Process Swaps
            foreach ($transfers as $t) {
                // 1. Get current status of the player leaving to preserve slot/role
                $this->db->query("SELECT is_starter, bench_order, formation, is_captain, is_vice_captain FROM fantasy_squads WHERE fantasy_team_id = :tid AND player_id = :pid");
                $this->db->bind(':tid', $team_id);
                $this->db->bind(':pid', $t['out']);
                $existing = $this->db->single();

                // 2. Remove old player
                $this->db->query("DELETE FROM fantasy_squads WHERE fantasy_team_id = :tid AND player_id = :pid");
                $this->db->bind(':tid', $team_id);
                $this->db->bind(':pid', $t['out']);
                $this->db->execute();

                // 3. Insert new player with preserved details
                $this->db->query("INSERT INTO fantasy_squads (fantasy_team_id, player_id, is_starter, bench_order, formation, is_captain, is_vice_captain, gameweek_added) 
                                  VALUES (:tid, :pid, :is_starter, :bench_order, :formation, :is_captain, :is_vice_captain, :gameweek)");
                $this->db->bind(':tid', $team_id);
                $this->db->bind(':pid', $t['in']);
                $this->db->bind(':is_starter', $existing->is_starter ?? 0);
                $this->db->bind(':bench_order', $existing->bench_order);
                $this->db->bind(':formation', $existing->formation);
                $this->db->bind(':is_captain', $existing->is_captain ?? 0);
                $this->db->bind(':is_vice_captain', $existing->is_vice_captain ?? 0);
                $this->db->bind(':gameweek', $gameweek);
                $this->db->execute();
            }

            // Log Transfer Cost (Deduct points) if > 0
            if ($cost > 0) {
                // Deduct points from total score
                $this->db->query("UPDATE fantasy_teams SET total_points = total_points - :cost WHERE id = :id");
                $this->db->bind(':cost', $cost);
                $this->db->bind(':id', $team_id);
                $this->db->execute();
            }

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollBack();
            return false;
        }
    }

    public function getAllTeamsWithDetails() {
        $this->db->query("SELECT ft.*, u.username, CONCAT(u.first_name, ' ', u.last_name) as manager_name 
                          FROM fantasy_teams ft 
                          JOIN users u ON ft.user_id = u.id 
                          ORDER BY ft.total_points DESC");
        return $this->db->resultSet();
    }

    public function delete($id) {
        $this->db->query("DELETE FROM fantasy_teams WHERE id = :id");
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    public function performSeasonRollover($season) {
        // Archive logic would go here. For now, we just delete old teams to reset.
        $this->db->query("DELETE FROM fantasy_teams WHERE season = :season");
        $this->db->bind(':season', $season);
        return $this->db->execute();
    }

    public function getTotalTeamsCount($league_id) {
        $this->db->query("SELECT COUNT(*) as count FROM fantasy_teams WHERE league_id = :league_id");
        $this->db->bind(':league_id', $league_id);
        return $this->db->single()->count;
    }

    public function getGlobalStandingsPaginated($league_id, $limit, $offset) {
        $this->db->query("SELECT ft.id as fantasy_team_id, ft.name as team_name, 
                                 CONCAT(u.first_name, ' ', u.last_name) as manager_name,
                                 ft.gameweek_points, ft.total_points
                          FROM fantasy_teams ft
                          JOIN users u ON ft.user_id = u.id
                          WHERE ft.league_id = :league_id
                          ORDER BY ft.total_points DESC, ft.gameweek_points DESC
                          LIMIT :limit OFFSET :offset");
        $this->db->bind(':league_id', $league_id);
        $this->db->bind(':limit', $limit);
        $this->db->bind(':offset', $offset);
        return $this->db->resultSet();
    }
}