<?php

class Fine extends Model
{
    protected $table = 'fines';
    /**
     * Creates a new fine record.
     * @param array $data Associative array containing fine details.
     * @return bool True on success, false on failure.
     */
    public function create(array $data): bool {
        $this->db->query('INSERT INTO fines (entity_type, entity_id, reason, amount, status) VALUES (:entity_type, :entity_id, :reason, :amount, :status)');

        $this->db->bind(':entity_type', $data['entity_type']);
        $this->db->bind(':entity_id', $data['entity_id']);
        $this->db->bind(':reason', $data['reason']);
        $this->db->bind(':amount', $data['amount']);
        $this->db->bind(':status', $data['status'] ?? 'unpaid');

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

    /**
     * Gets all fines from the database with associated names.
     * @return array An array of fine objects.
     */
    public function getAllFinesWithDetails(int $limit = 20, int $offset = 0, string $search = ''): array
    {
        $sql = "
            SELECT 
                f.id, f.entity_type, f.entity_id, f.reason, f.amount, f.status, f.issued_date,
                CASE
                    WHEN f.entity_type = 'club' THEN c.name
                    WHEN f.entity_type = 'player' THEN CONCAT(u.first_name, ' ', u.last_name)
                    ELSE 'N/A'
                END as entity_name
            FROM fines f
            LEFT JOIN clubs c ON f.entity_id = c.id AND f.entity_type = 'club'
            LEFT JOIN users u ON f.entity_id = u.id AND f.entity_type = 'player'";

        $params = [];
        if (!empty($search)) {
            $sql .= " WHERE (c.name LIKE :search OR u.first_name LIKE :search OR u.last_name LIKE :search OR f.reason LIKE :search)";
            $params[':search'] = '%' . $search . '%';
        }

        $sql .= " ORDER BY f.issued_date DESC LIMIT :limit OFFSET :offset";
        
        $this->db->query($sql);
        
        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        $this->db->bind(':offset', $offset, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    /**
     * Gets the total count of all fines.
     * @return int The total number of fines.
     */
    public function getTotalFinesCount(string $search = ''): int
    {
        $sql = "SELECT COUNT(f.id) as total FROM fines f
                LEFT JOIN clubs c ON f.entity_id = c.id AND f.entity_type = 'club'
                LEFT JOIN users u ON f.entity_id = u.id AND f.entity_type = 'player'";
        $params = [];
        if (!empty($search)) {
            $sql .= " WHERE (c.name LIKE :search OR u.first_name LIKE :search OR u.last_name LIKE :search OR f.reason LIKE :search)";
            $params[':search'] = '%' . $search . '%';
        }
        $this->db->query($sql);
        if (!empty($search)) {
            $this->db->bind(':search', $params[':search']);
        }
        return (int)$this->db->single()->total;
    }

    /**
     * Deletes a fine based on the event that triggered it.
     * This is used to undo automatically generated fines.
     * It finds the most recent unpaid fine matching the criteria.
     * @param int $player_id The ID of the player who was fined.
     * @param int $fixture_id The ID of the fixture where the event occurred.
     * @param string $reason The specific reason string to match.
     * @return bool True on success, false on failure.
     */
    public function deleteByEventDetails(int $player_id, int $fixture_id, string $reason): bool
    {
        // The reason string contains the fixture ID, which is our primary link.
        $this->db->query(
            "DELETE FROM fines 
             WHERE entity_type = 'player' AND entity_id = :player_id AND reason = :reason AND status = 'unpaid'
             ORDER BY issued_date DESC LIMIT 1"
        );
        $this->db->bind(':player_id', $player_id);
        $this->db->bind(':reason', $reason);
        return $this->db->execute();
    }

    /**
     * Deletes a fine by its ID.
     * @param int $id The ID of the fine to delete.
     * @return bool True on success, false on failure.
     */
    public function delete(int $id): bool {
        $this->db->query('DELETE FROM fines WHERE id = :id');
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    /**
     * Gets all unpaid fines for a club and its players.
     * @param int $club_id The ID of the club.
     * @return array An array of fine objects.
     */
    public function getUnpaidFinesForTeam(int $club_id): array {
        $this->db->query("
            SELECT 
                f.id,
                f.entity_type,
                f.entity_id,
                f.reason,
                f.amount,
                f.status,
                f.issued_date,
                u.first_name,
                u.id as player_id,
                u.last_name,
                c_fined.name as club_name_fined
            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 -- This finds the player record
            LEFT JOIN teams t ON p.team_id = t.id -- This finds the player's team
            LEFT JOIN clubs c_player_club ON t.club_id = c_player_club.id
            LEFT JOIN clubs c_fined ON f.entity_id = c_fined.id AND f.entity_type = 'club' -- This finds the club if the fine is for the club itself
            WHERE 
                f.status = 'unpaid'
                AND 
                (
                    -- Case 1: The fine is directly for the club we are looking for.
                    (f.entity_type = 'club' AND f.entity_id = :club_id) 
                    OR 
                    -- Case 2: The fine is for a player who belongs to a team that is part of the club we are looking for.
                    (f.entity_type = 'player' AND t.club_id = :club_id2)
                )
            ORDER BY f.issued_date DESC;
        ");
        $this->db->bind(':club_id', $club_id);
        $this->db->bind(':club_id2', $club_id);
        return $this->db->resultSet();
    }

    /**
     * Gets an array of player IDs who have unpaid fines for a given club.
     * @param int $club_id The ID of the club.
     * @return array An array of player user IDs.
     */
    public function getFinedPlayerIdsForTeam(int $club_id): array {
        $this->db->query("
            SELECT DISTINCT f.entity_id
            FROM fines f
            JOIN users u ON f.entity_id = u.id
            JOIN players p ON u.id = p.user_id
            JOIN teams t ON p.team_id = t.id
            WHERE f.entity_type = 'player' 
              AND f.status = 'unpaid' 
              AND t.club_id = :club_id
        ");
        $this->db->bind(':club_id', $club_id);
        $results = $this->db->resultSet();
        // Return a flat array of IDs
        return array_map(fn($row) => $row->entity_id, $results);
    }

    /**
     * Marks an array of fines as paid.
     * @param array $fine_ids An array of fine IDs to update.
     * @return bool True on success, false on failure.
     */
    public function markAsPaid(array $fine_ids): bool {
        if (empty($fine_ids)) {
            return true;
        }

        $placeholders = implode(',', array_fill(0, count($fine_ids), '?'));

        $this->db->query("UPDATE fines SET status = 'paid', paid_date = NOW() WHERE id IN ($placeholders)");
        
        // The execute method in your Database class can handle an array of parameters for 'IN' clauses
        return $this->db->execute($fine_ids);
    }

    /**
     * Gets details for specific fines by their IDs.
     * @param array $fine_ids An array of fine IDs.
     * @return array An array of fine objects.
     */
    public function getFinesByIds(array $fine_ids): array {
        if (empty($fine_ids)) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($fine_ids), '?'));

        $this->db->query("
            SELECT 
                f.id, f.entity_type, f.entity_id, f.reason, f.amount, f.status, f.issued_date, f.paid_date,
                CASE
                    WHEN f.entity_type = 'club' THEN c.name
                    WHEN f.entity_type = 'player' THEN CONCAT(u.first_name, ' ', u.last_name)
                    ELSE 'N/A'
                END as entity_name,
                COALESCE(c.name, c_player.name) as club_name
            FROM fines f
            LEFT JOIN clubs c ON f.entity_type = 'club' AND f.entity_id = c.id
            LEFT JOIN users u ON f.entity_type = 'player' AND f.entity_id = u.id
            LEFT JOIN players p ON u.id = p.user_id
            LEFT JOIN teams t ON p.team_id = t.id
            LEFT JOIN clubs c_player ON t.club_id = c_player.id
            WHERE f.id IN ($placeholders)
        ");
        
        return $this->db->resultSet($fine_ids);
    }

    /**
     * Gets fine details by ID, ensuring it belongs to a specific club.
     * This is used for security checks before processing payments.
     * @param int $fine_id The ID of the fine.
     * @param int $club_id The ID of the club to check against.
     * @return object|null The fine object with relevant details, or null if not found or doesn't belong to the club.
     */
    public function getFineDetailsByIdAndClubId(int $fine_id, int $club_id): ?object {
        $this->db->query("
            SELECT
                f.id, f.entity_type, f.entity_id, f.reason, f.amount, f.status, f.issued_date,
                u.id as player_user_id, u.first_name, u.last_name,
                c_fined.id as fined_club_id, c_fined.name as fined_club_name,
                c_player_club.id as player_club_id, c_player_club.name as player_club_name
            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
            LEFT JOIN clubs c_player_club ON t.club_id = c_player_club.id
            LEFT JOIN clubs c_fined ON f.entity_id = c_fined.id AND f.entity_type = 'club'
            WHERE f.id = :fine_id AND f.status = 'unpaid'
            AND (
                (f.entity_type = 'club' AND c_fined.id = :club_id)
                OR
                (f.entity_type = 'player' AND c_player_club.id = :club_id2)
            )
        ");
        $this->db->bind(':fine_id', $fine_id);
        $this->db->bind(':club_id', $club_id);
        $this->db->bind(':club_id2', $club_id);
        $row = $this->db->single();
        return $row ?: null;
    }

    /**
     * Gets all fines (paid and unpaid) for a club and its players.
     * @param int $club_id The ID of the club.
     * @return array An array of fine objects.
     */
    public function getAllFinesForTeam(int $club_id): array {
        $this->db->query("
            SELECT 
                f.id,
                f.entity_type,
                f.entity_id,
                f.reason,
                f.amount,
                f.status,
                f.issued_date,
                f.paid_date,
                u.first_name,
                u.id as player_id,
                u.last_name,
                u.profile_picture,
                c_fined.name as club_name_fined
            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
            LEFT JOIN clubs c_player_club ON t.club_id = c_player_club.id
            LEFT JOIN clubs c_fined ON f.entity_id = c_fined.id AND f.entity_type = 'club' -- This finds the club if the fine is for the club itself
            WHERE
                (
                    -- Case 1: The fine is directly for the club we are looking for.
                    (f.entity_type = 'club' AND f.entity_id = :club_id) 
                    OR 
                    -- Case 2: The fine is for a player who belongs to a team that is part of the club we are looking for.
                    (f.entity_type = 'player' AND t.club_id = :club_id2)
                )
            ORDER BY f.issued_date DESC
        ");
        $this->db->bind(':club_id', $club_id);
        $this->db->bind(':club_id2', $club_id);
        return $this->db->resultSet();
    }
}
?>