exec(
"CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
role ENUM('admin','manager','user') NOT NULL DEFAULT 'manager',
failed_attempts INT NOT NULL DEFAULT 0,
locked_until DATETIME NULL,
reset_token_hash CHAR(64) NULL,
reset_expires_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
);
$pdo->exec(
"CREATE TABLE IF NOT EXISTS athletes (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
sport_name VARCHAR(100) NOT NULL,
club_name VARCHAR(120) NOT NULL,
nationality VARCHAR(80) DEFAULT NULL,
position_name VARCHAR(80) DEFAULT NULL,
jersey_number INT DEFAULT NULL,
status ENUM('actif','blesse','suspendu','retraite') NOT NULL DEFAULT 'actif',
joined_on DATE DEFAULT NULL,
matches_played INT NOT NULL DEFAULT 0,
goals_scored INT NOT NULL DEFAULT 0,
assists_count INT NOT NULL DEFAULT 0,
awards VARCHAR(255) DEFAULT NULL,
career_note TEXT DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_athletes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_athletes_sport (sport_name),
INDEX idx_athletes_club (club_name),
INDEX idx_athletes_status (status),
INDEX idx_athletes_name (last_name, first_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
);
}
function e(?string $value): string
{
return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
}
function env_value(string $key, string $fallback = ''): string
{
$value = $_SERVER[$key] ?? getenv($key) ?: $fallback;
return is_string($value) ? $value : $fallback;
}
function app_name(): string
{
return env_value('PROJECT_NAME', 'RJLRESAKA');
}
function page_title(string $title): string
{
return $title . ' • ' . app_name();
}
function set_flash(string $type, string $message): void
{
$_SESSION['flash'][] = ['type' => $type, 'message' => $message];
}
function get_flashes(): array
{
$flashes = $_SESSION['flash'] ?? [];
unset($_SESSION['flash']);
return is_array($flashes) ? $flashes : [];
}
function current_user(): ?array
{
if (empty($_SESSION['user_id'])) {
return null;
}
static $user = null;
if ($user !== null) {
return $user;
}
$stmt = db()->prepare('SELECT id, first_name, last_name, email, role, created_at FROM users WHERE id = :id LIMIT 1');
$stmt->execute(['id' => (int) $_SESSION['user_id']]);
$user = $stmt->fetch() ?: null;
if (!$user) {
unset($_SESSION['user_id']);
}
return $user;
}
function require_login(): void
{
if (!current_user()) {
set_flash('warning', 'Veuillez vous connecter pour accéder à cette section.');
redirect('login.php');
}
}
function redirect(string $path): void
{
header('Location: ' . $path);
exit;
}
function csrf_token(): string
{
if (empty($_SESSION['csrf_token'])) {
$_SESSION['csrf_token'] = bin2hex(random_bytes(16));
}
return $_SESSION['csrf_token'];
}
function verify_csrf(): void
{
$token = $_POST['csrf_token'] ?? '';
if (!is_string($token) || !hash_equals($_SESSION['csrf_token'] ?? '', $token)) {
http_response_code(422);
exit('Jeton de formulaire invalide.');
}
}
function request_method(): string
{
return strtoupper($_SERVER['REQUEST_METHOD'] ?? 'GET');
}
function is_post(): bool
{
return request_method() === 'POST';
}
function old(string $key, string $fallback = ''): string
{
return e($_POST[$key] ?? $fallback);
}
function password_rules_ok(string $password): bool
{
return strlen($password) >= 8;
}
function login_user(array $user): void
{
session_regenerate_id(true);
$_SESSION['user_id'] = (int) $user['id'];
}
function logout_user(): void
{
$_SESSION = [];
if (ini_get('session.use_cookies')) {
$params = session_get_cookie_params();
setcookie(session_name(), '', time() - 42000, $params['path'], $params['domain'], (bool) $params['secure'], (bool) $params['httponly']);
}
session_destroy();
}
function format_datetime(?string $value): string
{
if (!$value) {
return '—';
}
return date('d/m/Y H:i', strtotime($value));
}
function format_date(?string $value): string
{
if (!$value) {
return '—';
}
return date('d/m/Y', strtotime($value));
}
function stat_badge_class(string $status): string
{
return match ($status) {
'actif' => 'success',
'blesse' => 'warning',
'suspendu' => 'danger',
'retraite' => 'secondary',
default => 'secondary',
};
}
function fetch_dashboard_stats(?int $userId = null): array
{
$pdo = db();
$where = $userId ? 'WHERE user_id = :user_id' : '';
$stmt = $pdo->prepare("SELECT COUNT(*) AS total, COUNT(DISTINCT sport_name) AS sports, COUNT(DISTINCT club_name) AS clubs FROM athletes $where");
$stmt->execute($userId ? ['user_id' => $userId] : []);
$base = $stmt->fetch() ?: ['total' => 0, 'sports' => 0, 'clubs' => 0];
$statusStmt = $pdo->prepare("SELECT status, COUNT(*) AS total FROM athletes $where GROUP BY status ORDER BY total DESC");
$statusStmt->execute($userId ? ['user_id' => $userId] : []);
$recentStmt = $pdo->prepare("SELECT id, first_name, last_name, sport_name, club_name, status, created_at FROM athletes $where ORDER BY created_at DESC LIMIT 5");
$recentStmt->execute($userId ? ['user_id' => $userId] : []);
return [
'total' => (int) ($base['total'] ?? 0),
'sports' => (int) ($base['sports'] ?? 0),
'clubs' => (int) ($base['clubs'] ?? 0),
'status_breakdown' => $statusStmt->fetchAll(),
'recent' => $recentStmt->fetchAll(),
];
}
function top_values(string $column, int $limit = 6): array
{
$allowed = ['sport_name', 'club_name'];
if (!in_array($column, $allowed, true)) {
return [];
}
$stmt = db()->query("SELECT {$column} AS label, COUNT(*) AS total FROM athletes GROUP BY {$column} ORDER BY total DESC, {$column} ASC LIMIT {$limit}");
return $stmt->fetchAll();
}
function render_header(string $title, array $options = []): void
{
$pageDescription = $options['description'] ?? env_value('PROJECT_DESCRIPTION', 'Gestion professionnelle des sportifs, clubs et sports.');
$projectImageUrl = env_value('PROJECT_IMAGE_URL', '');
$bodyClass = $options['body_class'] ?? '';
$user = current_user();
$flashes = get_flashes();
$assetVersion = (string) @filemtime(__DIR__ . '/../assets/css/custom.css');
?>
<?= e(page_title($title)) ?>