<?php

class Club {
    private $db;

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

    // Create a new club and assign it to a league
    public function create($data){
        $this->db->query('INSERT INTO clubs (name, stadium, league_id, logo) VALUES (:name, :stadium, :league_id, :logo)');
        
        // Bind values
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':stadium', $data['stadium']);
        $this->db->bind(':league_id', $data['league_id']);
        $this->db->bind(':logo', $data['logo']);

        // Execute and return new club ID on success
        if($this->db->execute()){
            return $this->db->lastInsertId();
        } else {
            return false;
        }
    }

    // Get all clubs
    public function getAll() {
        $this->db->query('SELECT * FROM clubs ORDER BY name ASC');
        return $this->db->resultSet();
    }

    // Get all clubs for a specific league (public)
    public function getAllByLeague($league_id){
        $this->db->query('SELECT id, name, logo, stadium FROM clubs WHERE league_id = :league_id ORDER BY name ASC');
        $this->db->bind(':league_id', $league_id);
        return $this->db->resultSet();
    }

    // Find club by name to prevent duplicates
    public function findByName($name){
        $this->db->query('SELECT id FROM clubs WHERE name = :name');
        $this->db->bind(':name', $name);
        $this->db->single();

        return $this->db->rowCount() > 0;
    }

    // Get club by ID
    public function findById($id) {
        $this->db->query('SELECT * FROM clubs WHERE id = :id');
        $this->db->bind(':id', $id);
        $row = $this->db->single();

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

    // Get club details including the primary team ID
    public function getClubDetails($club_id, $current_user_id = null) {
        $this->db->query('
            SELECT c.id, c.name, c.logo, c.stadium, t.id as team_id,
                   ' . ($current_user_id ? '(SELECT COUNT(*) FROM user_follows_clubs WHERE user_id = :current_user_id AND club_id = c.id)' : '0') . ' as is_following
            FROM clubs c
            LEFT JOIN teams t ON c.id = t.club_id
            WHERE c.id = :club_id
            LIMIT 1
        '); // Assuming one main team per club for simplicity
        $this->db->bind(':club_id', $club_id);
        if ($current_user_id) {
            $this->db->bind(':current_user_id', $current_user_id);
        }
        return $this->db->single();
    }

    // Get aggregated stats for a club for the season
    public function getClubStats($club_id) {
        $this->db->query("
            SELECT
                IFNULL(SUM(CASE WHEN me.event_type = 'goal' OR me.event_type = 'penalty_scored' THEN 1 ELSE 0 END), 0) as goals_scored,
                IFNULL((SELECT COUNT(*) FROM match_events me_conceded JOIN fixtures f_conceded ON me_conceded.fixture_id = f_conceded.id WHERE (f_conceded.home_team_id IN (SELECT id FROM teams WHERE club_id = :club_id) AND me_conceded.team_id != f_conceded.home_team_id) OR (f_conceded.away_team_id IN (SELECT id FROM teams WHERE club_id = :club_id) AND me_conceded.team_id != f_conceded.away_team_id)), 0) as goals_conceded,
                IFNULL(SUM(CASE WHEN me.event_type = 'shot_on_target' THEN 1 ELSE 0 END), 0) as shots_on_target,
                IFNULL(SUM(CASE WHEN me.event_type = 'corner' THEN 1 ELSE 0 END), 0) as corners,
                IFNULL(SUM(CASE WHEN me.event_type = 'foul' THEN 1 ELSE 0 END), 0) as fouls,
                IFNULL(SUM(CASE WHEN me.event_type = 'yellow_card' THEN 1 ELSE 0 END), 0) as yellow_cards,
                IFNULL(SUM(CASE WHEN me.event_type = 'red_card' THEN 1 ELSE 0 END), 0) as red_cards,
                IFNULL(SUM(CASE WHEN me.event_type = 'offside' THEN 1 ELSE 0 END), 0) as offsides
            FROM match_events me
            JOIN teams t ON me.team_id = t.id
            WHERE t.club_id = :club_id
        ");
        $this->db->bind(':club_id', $club_id);
        return $this->db->single();
    }

    // Search clubs by name
    public function searchByName($query) {
        $this->db->query("
            SELECT id, name, logo, stadium
            FROM clubs
            WHERE name LIKE :query ORDER BY name ASC LIMIT 5
        ");
        $this->db->bind(':query', '%' . $query . '%');
        return $this->db->resultSet();
    }

    // Get all clubs with league details for admin panel
    public function getAllForAdmin() {
        $this->db->query('
            SELECT c.id, c.name, c.stadium, l.name as league_name, CONCAT(u.first_name, " ", u.last_name) as manager_name
            FROM clubs c
            LEFT JOIN leagues l ON c.league_id = l.id
            LEFT JOIN users u ON c.manager_id = u.id
            ORDER BY l.name, c.name ASC
        ');
        return $this->db->resultSet();
    }

    // Update an existing club
    public function update($data) {
        $this->db->query('UPDATE clubs SET name = :name, stadium = :stadium, league_id = :league_id, manager_id = :manager_id, logo = :logo WHERE id = :id');
        
        // Bind values
        $this->db->bind(':id', $data['id']);
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':stadium', $data['stadium']);
        $this->db->bind(':league_id', $data['league_id']);
        $this->db->bind(':logo', $data['logo']);
        $this->db->bind(':manager_id', !empty($data['manager_id']) ? $data['manager_id'] : null, PDO::PARAM_INT);

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

    /**
     * Gets all members for a given club.
     * @param int $club_id The ID of the club.
     * @return array An array of member objects.
     */
    public function getClubMembers(int $club_id): array
    {
        $this->db->query("
            SELECT u.id, u.first_name, u.last_name, u.email, m.membership_type, m.start_date, m.expiry_date
            FROM users u
            JOIN memberships m ON u.id = m.user_id
            WHERE m.club_id = :club_id
            ORDER BY u.last_name, u.first_name
        ");
        $this->db->bind(':club_id', $club_id);
        return $this->db->resultSet();
    }

    /**
     * Calculates the total revenue from memberships for a club.
     * @param int $club_id The ID of the club.
     * @return float The total revenue.
     */
    public function getMembershipRevenueByClub(int $club_id): float
    {
        $this->db->query("
            SELECT SUM(mp.price) as total_revenue
            FROM memberships m
            JOIN membership_packages mp ON m.membership_type = mp.name AND m.club_id = mp.club_id
            WHERE m.club_id = :club_id
        ");
        $this->db->bind(':club_id', $club_id);
        $row = $this->db->single();
        return $row && $row->total_revenue ? (float)$row->total_revenue : 0.0;
    }

    public function getMembershipPackagesByClub(int $club_id): array
    {
        $this->db->query("SELECT * FROM membership_packages WHERE club_id = :club_id ORDER BY price ASC");
        $this->db->bind(':club_id', $club_id);
        return $this->db->resultSet();
    }

    /**
     * Gets a monthly summary of revenue and fines for a club.
     * @param int $club_id The ID of the club.
     * @return array An array of objects, each with month, revenue, and fines.
     */
    public function getFinancialsOverTime(int $club_id): array
    {
        // 1. Get Membership Revenue by month
        $this->db->query("
            SELECT 
                DATE_FORMAT(m.created_at, '%Y-%m') as month, 
                SUM(mp.price) as total_revenue
            FROM memberships m
            JOIN membership_packages mp ON m.membership_type = mp.name AND m.club_id = mp.club_id
            WHERE m.club_id = :club_id
            GROUP BY DATE_FORMAT(m.created_at, '%Y-%m')
        ");
        $this->db->bind(':club_id', $club_id);
        $revenue_data = $this->db->resultSet();
        $revenue_by_month = array_column($revenue_data, 'total_revenue', 'month');

        // 2. Get Fines Issued by month
        $this->db->query("
            SELECT DATE_FORMAT(f.issued_date, '%Y-%m') as month, SUM(f.amount) as total_fines
            FROM fines f
            LEFT JOIN users u ON f.entity_id = u.id AND f.entity_type = 'player'
            LEFT JOIN players p ON u.id = p.user_id
            LEFT JOIN teams t ON p.team_id = t.id
            WHERE (f.entity_type = 'club' AND f.entity_id = :club_id1) OR (f.entity_type = 'player' AND t.club_id = :club_id2)
            GROUP BY DATE_FORMAT(f.issued_date, '%Y-%m')
        ");
        $this->db->bind(':club_id1', $club_id);
        $this->db->bind(':club_id2', $club_id);
        $fines_data = $this->db->resultSet();
        $fines_by_month = array_column($fines_data, 'total_fines', 'month');

        // 3. Merge data
        $all_months = array_unique(array_merge(array_keys($revenue_by_month), array_keys($fines_by_month)));
        sort($all_months);

        return array_map(fn($month) => [
            'month' => date('M Y', strtotime($month . '-01')),
            'revenue' => (float)($revenue_by_month[$month] ?? 0),
            'fines' => (float)($fines_by_month[$month] ?? 0)
        ], $all_months);
    }

    public function getMembershipPackageById(int $id): ?object
    {
        $this->db->query("SELECT * FROM membership_packages WHERE id = :id");
        $this->db->bind(':id', $id);
        $row = $this->db->single();
        return $row ?: null;
    }

    public function createMembershipPackage(array $data): bool
    {
        $this->db->query("INSERT INTO membership_packages (club_id, name, description, price, duration_type) VALUES (:club_id, :name, :description, :price, :duration_type)");
        $this->db->bind(':club_id', $data['club_id']);
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':description', $data['description']);
        $this->db->bind(':price', $data['price']);
        $this->db->bind(':duration_type', $data['duration_type']);
        return $this->db->execute();
    }

    public function updateMembershipPackage(array $data): bool
    {
        $this->db->query("UPDATE membership_packages SET name = :name, description = :description, price = :price, duration_type = :duration_type, is_active = :is_active WHERE id = :id AND club_id = :club_id");
        $this->db->bind(':id', $data['id']);
        $this->db->bind(':club_id', $data['club_id']);
        $this->db->bind(':name', $data['name']);
        $this->db->bind(':description', $data['description']);
        $this->db->bind(':price', $data['price']);
        $this->db->bind(':duration_type', $data['duration_type']);
        $this->db->bind(':is_active', $data['is_active']);
        return $this->db->execute();
    }

    public function deleteMembershipPackage(int $id, int $club_id): bool
    {
        $this->db->query("DELETE FROM membership_packages WHERE id = :id AND club_id = :club_id");
        $this->db->bind(':id', $id);
        $this->db->bind(':club_id', $club_id);
        return $this->db->execute();
    }

    public function getAllActivePackagesByLeague(int $league_id): array
    {
        $this->db->query("
            SELECT mp.* 
            FROM membership_packages mp
            JOIN clubs c ON mp.club_id = c.id
            WHERE c.league_id = :league_id AND mp.is_active = 1
            ORDER BY mp.club_id, mp.price ASC
        ");
        $this->db->bind(':league_id', $league_id);
        $results = $this->db->resultSet();

        // Group by club_id
        $packages_by_club = [];
        foreach ($results as $package) {
            $packages_by_club[$package->club_id][] = $package;
        }
        return $packages_by_club;
    }

    /**
     * Creates a new membership record for a user and a club.
     * @param array $data Contains user_id, club_id, type, start_date, expiry_date.
     * @return bool True on success, false on failure.
     */
    public function createMembership(array $data): bool
    {
        $this->db->query(
            'INSERT INTO memberships (user_id, club_id, membership_type, start_date, expiry_date) 
             VALUES (:user_id, :club_id, :membership_type, :start_date, :expiry_date)'
        );

        $this->db->bind(':user_id', $data['user_id']);
        $this->db->bind(':club_id', $data['club_id']);
        $this->db->bind(':membership_type', $data['membership_type']);
        $this->db->bind(':start_date', $data['start_date']);
        $this->db->bind(':expiry_date', $data['expiry_date']);

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

    // Delete a club
    public function delete($id) {
        $this->db->query('DELETE FROM clubs WHERE id = :id');
        $this->db->bind(':id', $id);

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