<?php

class ClubManager {
    private $db;

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

    /**
     * Assign a user as a manager to a club.
     * Note: This method should be called within a transaction along with updating the user's role.
     */
    public function assign($user_id, $club_id, $role_title){
        $this->db->query('INSERT INTO club_managers (user_id, club_id, role_title) VALUES (:user_id, :club_id, :role_title)');
        
        // Bind values
        $this->db->bind(':user_id', $user_id);
        $this->db->bind(':club_id', $club_id);
        $this->db->bind(':role_title', $role_title);

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

    /**
     * Gets the club managed by a specific user.
     * @param int $user_id The ID of the user (manager).
     * @return object|null The club object or null if not found.
     */
    public function getClubByManagerId($user_id) {
        // Check both the dedicated club_managers table and the main manager_id on the clubs table
        $this->db->query('
            SELECT c.* 
            FROM clubs c
            LEFT JOIN club_managers cm ON c.id = cm.club_id
            WHERE cm.user_id = :user_id1 OR c.manager_id = :user_id2
            LIMIT 1
        ');
        $this->db->bind(':user_id1', $user_id);
        $this->db->bind(':user_id2', $user_id);
        $row = $this->db->single();
        return $row ?: null;
    }

    /**
     * Gets all staff members for a given club.
     * This includes club-level managers and team-level managers.
     * @param int $club_id The ID of the club.
     * @return array An array of staff member objects.
     */
    public function getStaffByClubId(int $club_id): array
    {
        $this->db->query("
            (SELECT u.id, u.first_name, u.last_name, u.email, cm.role_title, NULL as team_name
            FROM users u
            JOIN club_managers cm ON u.id = cm.user_id
            WHERE cm.club_id = :club_id1)
            UNION
            (SELECT u.id, u.first_name, u.last_name, u.email, 'Team Manager' as role_title, t.name as team_name
            FROM users u
            JOIN teams t ON u.id = t.manager_id
            WHERE t.club_id = :club_id2)
            UNION
            (SELECT u.id, u.first_name, u.last_name, u.email, 'Club Manager (Primary)' as role_title, NULL as team_name
            FROM users u
            JOIN clubs c ON u.id = c.manager_id
            WHERE c.id = :club_id3)
            ORDER BY role_title, last_name
        ");
        $this->db->bind(':club_id1', $club_id);
        $this->db->bind(':club_id2', $club_id);
        $this->db->bind(':club_id3', $club_id);
        return $this->db->resultSet();
    }

    /**
     * Hires a user as a staff member.
     * @param int $user_id The ID of the user to hire.
     * @param int $club_id The ID of the club.
     * @param string $role_type 'team_manager' or 'club_staff'.
     * @param string|null $position The specific position title (e.g., 'Assistant Coach').
     * @param int|null $team_id The ID of the team (if role is team_manager).
     * @return bool True on success.
     */
    public function hireStaff(int $user_id, int $club_id, string $role_type, ?string $position, ?int $team_id): bool
    {
        $this->db->beginTransaction();
        try {
            if ($role_type === 'team_manager' && $team_id) {
                // Update user role
                $this->db->query("UPDATE users SET role = 'team_manager' WHERE id = :user_id");
                $this->db->bind(':user_id', $user_id);
                $this->db->execute();

                // Assign to team
                $this->db->query("UPDATE teams SET manager_id = :user_id WHERE id = :team_id AND club_id = :club_id");
                $this->db->bind(':user_id', $user_id);
                $this->db->bind(':team_id', $team_id);
                $this->db->bind(':club_id', $club_id);
                $this->db->execute();
            } else {
                // General Club Staff (stored in club_managers table for now, or could be a new staff table)
                // For this implementation, we'll use club_managers but with a custom role title
                // Note: This user won't have the 'club_manager' system role unless specified, 
                // but for simplicity let's keep them as 'user' or upgrade to a specific staff role if you have one.
                // Let's assume we just track them in club_managers for display.
                
                $this->db->query("INSERT INTO club_managers (user_id, club_id, role_title) VALUES (:user_id, :club_id, :role_title)");
                $this->db->bind(':user_id', $user_id);
                $this->db->bind(':club_id', $club_id);
                $this->db->bind(':role_title', $position);
                $this->db->execute();
            }

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

    /**
     * Sacks a staff member.
     * @param int $user_id The ID of the user.
     * @return bool True on success.
     */
    public function sackStaff(int $user_id): bool
    {
        // 1. Reset user role to 'user' (free agent)
        $this->db->query("UPDATE users SET role = 'user' WHERE id = :user_id");
        $this->db->bind(':user_id', $user_id);
        
        // 2. Remove from club_managers and teams (manager_id) is handled by triggers or manual update?
        // Since we might not have triggers, let's manually clear.
        $this->db->query("DELETE FROM club_managers WHERE user_id = :user_id");
        $this->db->bind(':user_id', $user_id);
        $this->db->execute();

        $this->db->query("UPDATE teams SET manager_id = NULL WHERE manager_id = :user_id");
        $this->db->bind(':user_id', $user_id);
        $this->db->execute();

        // Also clear from clubs table if they were the primary manager
        $this->db->query("UPDATE clubs SET manager_id = NULL WHERE manager_id = :user_id");
        $this->db->bind(':user_id', $user_id);
        
        return $this->db->execute();
    }
}
?>
