<?php
/**
 * Comptes du Coupé 406 Club et outils d'administration.
 *
 * Les membres sont enregistrés dans la base MySQL déjà utilisée par Gears, mais
 * dans des tables qui nous appartiennent (préfixe c406_). Aucune table ni aucun
 * fichier de Gears n'est lu, modifié ou supprimé.
 */

function db(): ?PDO
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }
    // Les identifiants ne sont pas recopiés ici : on lit ceux de Gears au vol.
    $url = '';
    foreach (['/.env.local', '/.env'] as $file) {
        $path = GEARS_ROOT . $file;
        if (!is_readable($path)) {
            continue;
        }
        foreach (file($path, FILE_IGNORE_NEW_LINES) ?: [] as $line) {
            if (preg_match('/^\s*DATABASE_URL\s*=\s*(.+)$/', $line, $found)) {
                $url = trim($found[1], " \t\"'");
                break 2;
            }
        }
    }
    $parts = $url !== '' ? parse_url($url) : false;
    if (!is_array($parts) || !isset($parts['host'], $parts['user'], $parts['path'])) {
        return null;
    }
    try {
        $pdo = new PDO(
            sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4', $parts['host'], $parts['port'] ?? 3306, ltrim($parts['path'], '/')),
            // Les identifiants sont encodés dans l'URL : il faut les décoder,
            // sinon un caractère comme « ) » arrive sous sa forme %29.
            rawurldecode($parts['user']),
            rawurldecode($parts['pass'] ?? ''),
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
        );
    } catch (Throwable $error) {
        error_log('[coupe406] base indisponible : ' . $error->getMessage());
        return null;
    }
    return $pdo;
}

/** Les tables se créent d'elles-mêmes au premier appel. */
function ensureTables(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_members (
        id VARCHAR(32) PRIMARY KEY,
        email VARCHAR(190) NOT NULL UNIQUE,
        name VARCHAR(80) NOT NULL,
        city VARCHAR(80) NOT NULL DEFAULT '',
        region VARCHAR(80) NOT NULL DEFAULT '',
        pass_hash VARCHAR(255) NOT NULL,
        role VARCHAR(16) NOT NULL DEFAULT 'member',
        verified_at INT NULL,
        banned_at INT NULL,
        ban_reason VARCHAR(255) NULL,
        created_at INT NOT NULL,
        last_seen_at INT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_reports (
        id INT AUTO_INCREMENT PRIMARY KEY,
        reporter_id VARCHAR(32) NOT NULL,
        target_id VARCHAR(32) NOT NULL,
        reason VARCHAR(60) NOT NULL,
        message TEXT NULL,
        status VARCHAR(16) NOT NULL DEFAULT 'open',
        created_at INT NOT NULL,
        handled_at INT NULL,
        INDEX c406_reports_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_push (
        token VARCHAR(255) PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        platform VARCHAR(16) NOT NULL DEFAULT 'android',
        updated_at INT NOT NULL,
        INDEX c406_push_member (member_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_backups (
        member_id VARCHAR(32) PRIMARY KEY,
        payload MEDIUMTEXT NOT NULL,
        updated_at INT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_messages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        sender_id VARCHAR(32) NOT NULL,
        target_id VARCHAR(32) NOT NULL,
        body TEXT NOT NULL,
        created_at INT NOT NULL,
        read_at INT NULL,
        edited_at INT NULL,
        INDEX c406_messages_pair (target_id, sender_id),
        INDEX c406_messages_inbox (target_id, read_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_news (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(120) NOT NULL,
        body TEXT NOT NULL,
        author_id VARCHAR(32) NOT NULL,
        created_at INT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Une lecture par publication et par compte : la pastille des dernières
    // nouvelles suit le membre même s'il change de téléphone.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_news_reads (
        news_id INT NOT NULL,
        member_id VARCHAR(32) NOT NULL,
        seen_at INT NOT NULL,
        PRIMARY KEY (news_id, member_id),
        INDEX c406_news_reads_member (member_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Confirmation du membre pour la campagne de test Google Play. En la
    // gardant sur le serveur, la fenetre ne revient pas sur un autre appareil.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_play_test_acceptances (
        campaign VARCHAR(48) NOT NULL,
        member_id VARCHAR(32) NOT NULL,
        accepted_at INT NOT NULL,
        PRIMARY KEY (campaign, member_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Le seuil atteint est memorise une fois pour toutes. Une suppression de
    // compte ulterieure ne doit jamais faire reapparaitre la demande.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_play_test_campaigns (
        campaign VARCHAR(48) NOT NULL PRIMARY KEY,
        target INT NOT NULL,
        completed_at INT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_ideas (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        category VARCHAR(60) NOT NULL DEFAULT '',
        body TEXT NOT NULL,
        created_at INT NOT NULL,
        seen_at INT NULL,
        status VARCHAR(16) NOT NULL DEFAULT 'pending',
        decided_at INT NULL,
        admin_reflection VARCHAR(500) NOT NULL DEFAULT '',
        admin_reflection_at INT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_idea_votes (
        idea_id INT NOT NULL,
        member_id VARCHAR(32) NOT NULL,
        value TINYINT NOT NULL,
        PRIMARY KEY (idea_id, member_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Lecture propre à chaque membre : une nouvelle idée garde sa pastille
    // chez les autres jusqu'à ce qu'ils ouvrent réellement la boîte à idées.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_idea_reads (
        idea_id INT NOT NULL,
        member_id VARCHAR(32) NOT NULL,
        seen_at INT NOT NULL,
        PRIMARY KEY (idea_id, member_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Messages personnels persistants : contrairement à une notification
    // Android, ils attendent la prochaine connexion du membre.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_member_notices (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        kind VARCHAR(32) NOT NULL DEFAULT '',
        title VARCHAR(120) NOT NULL,
        body TEXT NOT NULL,
        created_at INT NOT NULL,
        seen_at INT NULL,
        INDEX c406_member_notices_pending (member_id, seen_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // À chaque adhésion, le club dispose de 24 heures pour souhaiter la
    // bienvenue. Les clics sont regroupés : aucun message ne part séparément.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_welcome_rounds (
        newcomer_id VARCHAR(32) PRIMARY KEY,
        newcomer_name VARCHAR(80) NOT NULL,
        started_at INT NOT NULL,
        deliver_at INT NOT NULL,
        delivered_at INT NULL,
        INDEX c406_welcome_rounds_due (deliver_at, delivered_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_welcome_wishes (
        newcomer_id VARCHAR(32) NOT NULL,
        greeter_id VARCHAR(32) NOT NULL,
        wished_at INT NOT NULL,
        PRIMARY KEY (newcomer_id, greeter_id),
        INDEX c406_welcome_wishes_greeter (greeter_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_appeals (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        kind VARCHAR(16) NOT NULL,
        target_id VARCHAR(32) NOT NULL DEFAULT '',
        category VARCHAR(60) NOT NULL DEFAULT '',
        body TEXT NOT NULL,
        reason VARCHAR(120) NOT NULL DEFAULT '',
        status VARCHAR(16) NOT NULL DEFAULT 'pending',
        created_at INT NOT NULL,
        handled_at INT NULL,
        context TEXT NULL,
        INDEX c406_appeals_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Colonne ajoutée après coup : sans effet si elle est déjà là.
    try { $pdo->exec("ALTER TABLE c406_appeals ADD COLUMN context TEXT NULL"); } catch (Throwable $ignore) {}
    foreach (["status VARCHAR(16) NOT NULL DEFAULT 'pending'", 'decided_at INT NULL',
        "admin_reflection VARCHAR(500) NOT NULL DEFAULT ''", 'admin_reflection_at INT NULL'] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_ideas ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    // Position approximative et vitrine du membre dans l annuaire.
    foreach ([
        'lat DECIMAL(8,4) NULL',
        'lon DECIMAL(8,4) NULL',
        'location_at INT NULL',
        'discoverable TINYINT(1) NOT NULL DEFAULT 1',
        'car_label VARCHAR(80) NOT NULL DEFAULT \'\'',
        'show_city TINYINT(1) NOT NULL DEFAULT 1',
        'approved_at INT NULL',
        'refused_at INT NULL',
        "owns_coupe VARCHAR(80) NOT NULL DEFAULT ''",
        "motivation VARCHAR(600) NOT NULL DEFAULT ''",
        'charter_at INT NULL',
        'tester TINYINT(1) NOT NULL DEFAULT 0',
        "app_version VARCHAR(16) NOT NULL DEFAULT ''",
        'notify_idea_reactions TINYINT(1) NOT NULL DEFAULT 1',
        // Renseignes librement par le membre : ils ne servent qu aux chiffres du club.
        "gender VARCHAR(16) NOT NULL DEFAULT ''",
        "age_band VARCHAR(16) NOT NULL DEFAULT ''",
    ] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_members ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    try { $pdo->exec("ALTER TABLE c406_registry ADD COLUMN phase VARCHAR(8) NOT NULL DEFAULT ''"); } catch (Throwable $ignore) {}
    // Une voiture recensee ne disparait pas du recensement : elle change d etat.
    // Ce qui identifie vraiment une voiture, et que les membres veulent voir :
    // la plaque, le VIN et le numero DAM.
    foreach (["plate VARCHAR(16) NOT NULL DEFAULT ''", "vin VARCHAR(20) NOT NULL DEFAULT ''",
              "dam VARCHAR(16) NOT NULL DEFAULT ''"] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_registry ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    foreach (["statut VARCHAR(16) NOT NULL DEFAULT 'circulation'", 'statut_at INT NOT NULL DEFAULT 0',
              'confirme_at INT NOT NULL DEFAULT 0', 'relance_at INT NOT NULL DEFAULT 0'] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_registry ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    // Ne jamais remplir approved_at ici : ensureTables() est appelee au debut
    // de presque chaque action. Une ancienne migration le faisait et acceptait
    // donc automatiquement toute nouvelle adresse des qu elle etait confirmee,
    // avant que l administrateur puisse voir la demande.
    // Une annonce vendue reste dans l historique de son auteur sans encombrer le club.
    foreach (['c406_parts', 'c406_services'] as $table) {
        foreach (["status VARCHAR(16) NOT NULL DEFAULT 'open'", 'closed_at INT NULL'] as $colonne) {
            try { $pdo->exec("ALTER TABLE {$table} ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
        }
    }
    // A quelle voiture la piece se destine : phase, annee et teinte.
    foreach (["phase VARCHAR(8) NOT NULL DEFAULT ''", "car_year VARCHAR(8) NOT NULL DEFAULT ''",
              "color VARCHAR(40) NOT NULL DEFAULT ''"] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_parts ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    // Une annonce de piece est soit une vente, soit une recherche.
    try { $pdo->exec("ALTER TABLE c406_parts ADD COLUMN wanted TINYINT NOT NULL DEFAULT 0"); } catch (Throwable $ignore) {}
    // Les anciennes installations peuvent avoir créé la messagerie avant les
    // accusés de lecture : on ajoute la colonne sans toucher aux messages.
    try { $pdo->exec("ALTER TABLE c406_messages ADD COLUMN read_at INT NULL"); } catch (Throwable $ignore) {}
    // Une modification conserve la date d'envoi et l'accusé de lecture, mais
    // elle reste clairement signalée aux deux personnes.
    try { $pdo->exec("ALTER TABLE c406_messages ADD COLUMN edited_at INT NULL"); } catch (Throwable $ignore) {}
    // Quand un membre ecrit au sujet d une piece, le vendeur est relance tous
    // les deux jours : la piece est-elle toujours a vendre ?
    foreach (['interest_at INT NOT NULL DEFAULT 0', 'asked_at INT NOT NULL DEFAULT 0', 'confirmed_at INT NOT NULL DEFAULT 0'] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_parts ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    // Un message ecrit depuis une annonce lui reste attache : il part avec elle.
    foreach (["listing_kind VARCHAR(16) NOT NULL DEFAULT ''", 'listing_id INT NOT NULL DEFAULT 0'] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_messages ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_guide_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        title VARCHAR(160) NOT NULL,
        category VARCHAR(60) NOT NULL DEFAULT '',
        motors VARCHAR(120) NOT NULL DEFAULT '',
        note TEXT NULL,
        stored_name VARCHAR(80) NOT NULL,
        original VARCHAR(190) NOT NULL,
        size_kb INT NOT NULL DEFAULT 0,
        status VARCHAR(16) NOT NULL DEFAULT 'pending',
        created_at INT NOT NULL,
        handled_at INT NULL,
        INDEX c406_guide_requests_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_services (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        title VARCHAR(160) NOT NULL,
        category VARCHAR(60) NOT NULL DEFAULT '',
        description TEXT NOT NULL,
        city VARCHAR(80) NOT NULL DEFAULT '',
        region VARCHAR(80) NOT NULL DEFAULT '',
        created_at INT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_parts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        title VARCHAR(160) NOT NULL,
        price VARCHAR(40) NOT NULL DEFAULT '',
        condition_label VARCHAR(60) NOT NULL DEFAULT '',
        description TEXT NOT NULL,
        city VARCHAR(80) NOT NULL DEFAULT '',
        region VARCHAR(80) NOT NULL DEFAULT '',
        created_at INT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_events (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        title VARCHAR(160) NOT NULL,
        event_date VARCHAR(20) NOT NULL,
        city VARCHAR(80) NOT NULL DEFAULT '',
        region VARCHAR(80) NOT NULL DEFAULT '',
        description TEXT NOT NULL,
        created_at INT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_event_guests (
        event_id INT NOT NULL,
        member_id VARCHAR(32) NOT NULL,
        PRIMARY KEY (event_id, member_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Un inscrit ecarte par l organisateur garde sa ligne : c est elle qui
    // l empeche de se reinscrire dans la foulee.
    foreach (['status TINYINT NOT NULL DEFAULT 0', 'joined_at INT NOT NULL DEFAULT 0'] as $colonne) {
        try { $pdo->exec("ALTER TABLE c406_event_guests ADD COLUMN " . $colonne); } catch (Throwable $ignore) {}
    }
    // Date du rappel deja envoye, pour ne pas sonner deux fois.
    try { $pdo->exec("ALTER TABLE c406_events ADD COLUMN reminded_at INT NOT NULL DEFAULT 0"); } catch (Throwable $ignore) {}
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_guide_views (
        guide_id VARCHAR(40) PRIMARY KEY,
        title VARCHAR(190) NOT NULL DEFAULT '',
        opens INT NOT NULL DEFAULT 0,
        last_at INT NOT NULL DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_listing_photos (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        kind VARCHAR(16) NOT NULL,
        listing_id INT NOT NULL,
        file_name VARCHAR(80) NOT NULL,
        status VARCHAR(16) NOT NULL DEFAULT 'pending',
        created_at INT NOT NULL,
        handled_at INT NULL,
        INDEX c406_listing_photos_cible (kind, listing_id, status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_registry (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id VARCHAR(32) NOT NULL,
        car_key VARCHAR(64) NOT NULL,
        motor_id VARCHAR(24) NOT NULL DEFAULT '',
        year VARCHAR(8) NOT NULL DEFAULT '',
        color VARCHAR(60) NOT NULL DEFAULT '',
        gearbox VARCHAR(60) NOT NULL DEFAULT '',
        trim_label VARCHAR(60) NOT NULL DEFAULT '',
        serial VARCHAR(12) NOT NULL DEFAULT '',
        note VARCHAR(300) NOT NULL DEFAULT '',
        phase VARCHAR(8) NOT NULL DEFAULT '',
        created_at INT NOT NULL,
        updated_at INT NOT NULL,
        UNIQUE KEY c406_registry_voiture (member_id, car_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Les annonces mises de cote par un membre : elles le suivent d un
    // telephone a l autre puisqu elles vivent sur le serveur.
    // Prix des huiles : releves a la main, avec la date et la source. Les
    // marchands refusent la lecture automatique, donc rien n est aspire.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_oil_prices (
        oil_id VARCHAR(48) PRIMARY KEY,
        price VARCHAR(16) NOT NULL DEFAULT '',
        pack VARCHAR(16) NOT NULL DEFAULT '5 L',
        source VARCHAR(80) NOT NULL DEFAULT '',
        updated_at INT NOT NULL DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Photo du bidon, prise par l administrateur : une photo maison, pas un
    // visuel de marque recopie.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_oil_photos (
        oil_id VARCHAR(48) PRIMARY KEY,
        file_name VARCHAR(80) NOT NULL,
        updated_at INT NOT NULL DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Reglages libres du club : lien Facebook, et ce qui viendra ensuite.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_settings (
        cle VARCHAR(40) PRIMARY KEY,
        valeur VARCHAR(255) NOT NULL DEFAULT '',
        updated_at INT NOT NULL DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Au déploiement du vrai compteur, les anciennes publications ont déjà
    // été présentées aux membres. On les acquitte une seule fois afin de ne pas
    // faire apparaître artificiellement « 3 nouvelles » chez tout le club.
    $migrationNouvelles = $pdo->prepare("SELECT 1 FROM c406_settings WHERE cle = 'news_reads_initialized_v1' LIMIT 1");
    $migrationNouvelles->execute();
    if (!$migrationNouvelles->fetchColumn()) {
        $pdo->exec('INSERT IGNORE INTO c406_news_reads (news_id, member_id, seen_at)
                    SELECT n.id, m.id, UNIX_TIMESTAMP() FROM c406_news n CROSS JOIN c406_members m');
        $pdo->prepare("INSERT INTO c406_settings (cle, valeur, updated_at) VALUES ('news_reads_initialized_v1', '1', ?)
                       ON DUPLICATE KEY UPDATE valeur = VALUES(valeur), updated_at = VALUES(updated_at)")->execute([time()]);
    }
    // Qui est en train d ecrire a qui, et depuis quand : une ligne par paire,
    // ecrasee a chaque frappe. Rien a purger, la date suffit a la perimer.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_typing (
        member_id VARCHAR(32) NOT NULL,
        target_id VARCHAR(32) NOT NULL,
        at INT NOT NULL,
        PRIMARY KEY (member_id, target_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Les references d origine du Coupe, telles que le constructeur les publie.
    // 33 000 lignes : trop pour l application, juste ce qu il faut ici.
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_pieces (
        id INT AUTO_INCREMENT PRIMARY KEY,
        moteur VARCHAR(12) NOT NULL DEFAULT '',
        phase VARCHAR(4) NOT NULL DEFAULT '',
        moteur_libelle VARCHAR(48) NOT NULL DEFAULT '',
        reference VARCHAR(24) NOT NULL,
        designation VARCHAR(120) NOT NULL,
        complement VARCHAR(80) NOT NULL DEFAULT '',
        applicabilite VARCHAR(255) NOT NULL DEFAULT '',
        opr_depuis INT NOT NULL DEFAULT 0,
        opr_jusqua INT NOT NULL DEFAULT 0,
        fonction VARCHAR(120) NOT NULL DEFAULT '',
        domaines VARCHAR(120) NOT NULL DEFAULT '',
        fonctions VARCHAR(600) NOT NULL DEFAULT '',
        document VARCHAR(120) NOT NULL DEFAULT '',
        repere VARCHAR(12) NOT NULL DEFAULT '',
        INDEX c406_pieces_moteur (moteur, phase),
        INDEX c406_pieces_reference (reference),
        FULLTEXT KEY c406_pieces_mots (designation, complement, fonction, document)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_favorites (
        member_id VARCHAR(32) NOT NULL,
        kind VARCHAR(16) NOT NULL,
        listing_id INT NOT NULL,
        created_at INT NOT NULL,
        PRIMARY KEY (member_id, kind, listing_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS c406_moderation_allow (
        id INT AUTO_INCREMENT PRIMARY KEY,
        terme VARCHAR(80) NOT NULL UNIQUE,
        added_by VARCHAR(32) NOT NULL DEFAULT '',
        created_at INT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $done = true;
}

function memberSession(string $id): string
{
    $payload = $id . '.' . (time() + 60 * 86400);
    return $payload . '.' . hash_hmac('sha256', $payload, C406_SECRET);
}

function sessionMember(?string $token): ?array
{
    $pdo = db();
    if ($pdo === null || $token === null || $token === '') {
        return null;
    }
    $parts = explode('.', $token);
    if (count($parts) !== 3) {
        return null;
    }
    [$id, $expiry, $signature] = $parts;
    if ((int)$expiry < time() || !hash_equals(hash_hmac('sha256', $id . '.' . $expiry, C406_SECRET), $signature)) {
        return null;
    }
    ensureTables($pdo);
    $statement = $pdo->prepare('SELECT * FROM c406_members WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $member = $statement->fetch();
    if (!$member || $member['banned_at'] !== null) {
        return null;
    }
    $pdo->prepare('UPDATE c406_members SET last_seen_at = ? WHERE id = ?')->execute([time(), $id]);
    return $member;
}

function publicMember(array $row, bool $forAdmin = false): array
{
    $base = [
        'id' => $row['id'],
        'name' => $row['name'],
        'city' => $row['city'],
        'region' => $row['region'],
        'role' => $row['role'],
        'verified' => $row['verified_at'] !== null,
    ];
    if (!$forAdmin) {
        return $base;
    }
    return $base + [
        'email' => $row['email'],
        // Quelle version chacun a installee : sans cela, impossible de savoir
        // qui n a pas encore la correction qu on vient de publier.
        'version' => (string)($row['app_version'] ?? ''),
        'tester' => (int)($row['tester'] ?? 0) === 1,
        // Tant qu une demande n est pas tranchee, le compte n est pas un membre.
        'pending' => ($row['approved_at'] ?? null) === null && ($row['refused_at'] ?? null) === null,
        'refused' => ($row['refused_at'] ?? null) !== null,
        'banned' => $row['banned_at'] !== null,
        'banReason' => $row['ban_reason'],
        'createdAt' => (int)$row['created_at'],
        'lastSeenAt' => $row['last_seen_at'] !== null ? (int)$row['last_seen_at'] : null,
    ];
}

function requireAdmin(array $payload): array
{
    $member = sessionMember(isset($payload['session']) ? (string)$payload['session'] : null);
    if ($member === null || $member['role'] !== 'admin') {
        reply(['ok' => false, 'error' => 'FORBIDDEN', 'message' => 'Réservé à l’administrateur du club.'], 403);
    }
    return $member;
}

function plainEmail(string $to, string $subject, string $title, string $body): array
{
    $html = '<!doctype html><html lang="fr"><body style="margin:0;background:#edf0ef;font-family:Arial,sans-serif;color:#172128">'
        . '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="padding:28px 12px"><tr><td align="center">'
        . '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="max-width:500px;background:#fff;border-radius:20px;overflow:hidden">'
        . '<tr><td style="background:#172229;padding:18px 22px;color:#fff"><span style="font-size:23px;font-weight:800">406</span>'
        . '<span style="font-size:11px;font-weight:700;letter-spacing:1.6px;color:#e6ff38;padding-left:8px">COUPÉ CLUB</span></td></tr>'
        . '<tr><td style="padding:24px 22px"><h1 style="margin:0 0 12px;font-size:20px">' . htmlspecialchars($title, ENT_QUOTES) . '</h1>'
        . '<p style="margin:0;font-size:14px;line-height:1.7;color:#4d585d;white-space:pre-wrap">' . htmlspecialchars($body, ENT_QUOTES) . '</p></td></tr>'
        . '<tr><td style="padding:14px 22px 22px;border-top:1px solid #e7eae6;font-size:11px;color:#8b9498">'
        . 'Coupé 406 Club — développé par Novalys Apps.</td></tr></table></td></tr></table></body></html>';
    return sendMail($to, $subject, $title . "\n\n" . $body . "\n\n— Coupé 406 Club, par Novalys Apps", $html);
}

/**
 * Notification poussée. Elle n'est envoyée que si le compte de service Firebase
 * a été déposé à côté de ce fichier ; sinon l'appel est simplement ignoré.
 */
/**
 * Envoie une notification aux appareils de ces membres.
 *
 * $data voyage avec la notification : c est ce qui permet a l application
 * d ouvrir directement la bonne conversation quand on touche le message,
 * plutot que de retomber sur l accueil.
 */
function pushToMembers(PDO $pdo, array $memberIds, string $title, string $body, array $data = []): int
{
    // Hors du dossier web : ce fichier contient une clé privée et ne doit
    // jamais pouvoir être téléchargé depuis un navigateur.
    $account = GEARS_ROOT . '/var/coupe406/firebase-service-account.json';
    if (!is_readable($account) || $memberIds === []) {
        return 0;
    }
    $credentials = json_decode((string)file_get_contents($account), true);
    if (!is_array($credentials) || !isset($credentials['client_email'], $credentials['private_key'], $credentials['project_id'])) {
        return 0;
    }

    // Jeton OAuth2 signé avec la clé du compte de service.
    $now = time();
    $header = rtrim(strtr(base64_encode(json_encode(['alg' => 'RS256', 'typ' => 'JWT'])), '+/', '-_'), '=');
    $claims = rtrim(strtr(base64_encode(json_encode([
        'iss' => $credentials['client_email'],
        'scope' => 'https://www.googleapis.com/auth/firebase.messaging',
        'aud' => 'https://oauth2.googleapis.com/token',
        'iat' => $now,
        'exp' => $now + 3600,
    ])), '+/', '-_'), '=');
    $signature = '';
    if (!openssl_sign($header . '.' . $claims, $signature, $credentials['private_key'], 'sha256WithRSAEncryption')) {
        return 0;
    }
    $assertion = $header . '.' . $claims . '.' . rtrim(strtr(base64_encode($signature), '+/', '-_'), '=');
    $tokenResponse = @file_get_contents('https://oauth2.googleapis.com/token', false, stream_context_create(['http' => [
        'method' => 'POST',
        'timeout' => 15,
        'ignore_errors' => true,
        'header' => "Content-Type: application/x-www-form-urlencoded\r\n",
        'content' => http_build_query(['grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer', 'assertion' => $assertion]),
    ]]));
    $accessToken = json_decode((string)$tokenResponse, true)['access_token'] ?? '';
    if ($accessToken === '') {
        return 0;
    }

    // `from` est une clé réservée par Firebase Cloud Messaging. Son emploi
    // faisait rejeter entièrement les notifications de messages privés avec
    // INVALID_ARGUMENT. Le serveur la convertit aussi pour les anciens appels
    // afin qu'aucune future notification ne puisse retomber dans ce piège.
    if (isset($data['from']) && !isset($data['senderId'])) {
        $data['senderId'] = $data['from'];
    }
    unset($data['from']);

    $placeholders = implode(',', array_fill(0, count($memberIds), '?'));
    $statement = $pdo->prepare("SELECT token FROM c406_push WHERE member_id IN ({$placeholders})");
    $statement->execute($memberIds);
    $sent = 0;
    $channel = (($data['type'] ?? '') === 'message') ? 'messages' : 'general';
    foreach ($statement->fetchAll(PDO::FETCH_COLUMN) as $deviceToken) {
        $response = @file_get_contents(
            'https://fcm.googleapis.com/v1/projects/' . $credentials['project_id'] . '/messages:send',
            false,
            stream_context_create(['http' => [
                'method' => 'POST',
                'timeout' => 15,
                'ignore_errors' => true,
                'header' => "Content-Type: application/json\r\nAuthorization: Bearer {$accessToken}\r\n",
                'content' => json_encode(['message' => array_filter([
                    'token' => $deviceToken,
                    'notification' => ['title' => $title, 'body' => $body],
                    'android' => [
                        'priority' => 'high',
                        'notification' => [
                            'channel_id' => $channel,
                            'sound' => 'default',
                            'default_vibrate_timings' => true,
                        ],
                    ],
                    // Firebase n accepte que du texte dans ces champs.
                    'data' => $data === [] ? null : array_map('strval', $data),
                ], static fn ($valeur) => $valeur !== null)]),
            ]]),
        );
        if ($response !== false && !str_contains((string)$response, '"error"')) {
            $sent++;
            continue;
        }
        // Garder une trace exploitable : auparavant la messagerie répondait
        // « envoyé » même quand Firebase refusait silencieusement le jeton.
        $journal = GEARS_ROOT . '/var/coupe406/fcm-errors.log';
        @file_put_contents($journal,
            '[' . date('c') . '] ' . ($response === false ? 'FCM indisponible' : mb_substr((string)$response, 0, 2000)) . "\n",
            FILE_APPEND | LOCK_EX);
        // Application desinstallee ou jeton perime : Firebase le dit une fois,
        // ensuite il ne sert plus a rien de l appeler. On le retire.
        // INVALID_ARGUMENT peut signaler une erreur dans le contenu envoyé
        // (par exemple une clé réservée) alors que le téléphone est parfaitement
        // valide. Ne supprimer que les jetons explicitement déclarés expirés.
        if (is_string($response) && (str_contains($response, 'UNREGISTERED') || str_contains($response, 'NotRegistered'))) {
            $pdo->prepare('DELETE FROM c406_push WHERE token = ?')->execute([$deviceToken]);
        }
    }
    return $sent;
}

/**
 * Ouvre la journée d'accueil d'un nouveau membre et prévient la famille.
 * L'administrateur qui valide l'adhésion compte déjà parmi les personnes qui
 * lui souhaitent la bienvenue ; les autres participent en touchant la notif.
 */
function startWelcomeRound(PDO $pdo, string $newcomerId, string $newcomerName, string $adminId): bool
{
    ensureTables($pdo);
    $now = time();
    $insert = $pdo->prepare('INSERT IGNORE INTO c406_welcome_rounds
        (newcomer_id, newcomer_name, started_at, deliver_at, delivered_at)
        VALUES (?, ?, ?, ?, NULL)');
    $insert->execute([$newcomerId, mb_substr($newcomerName, 0, 80), $now, $now + 86400]);
    if ($insert->rowCount() === 0) {
        return false;
    }

    if ($adminId !== '' && $adminId !== $newcomerId) {
        $pdo->prepare('INSERT IGNORE INTO c406_welcome_wishes (newcomer_id, greeter_id, wished_at)
                       VALUES (?, ?, ?)')->execute([$newcomerId, $adminId, $now]);
    }

    $members = $pdo->prepare("SELECT id FROM c406_members
        WHERE approved_at IS NOT NULL AND verified_at IS NOT NULL AND banned_at IS NULL
          AND id <> ? AND id <> ?");
    $members->execute([$newcomerId, $adminId]);
    $ids = $members->fetchAll(PDO::FETCH_COLUMN);
    pushToMembers(
        $pdo,
        $ids,
        'La famille s’agrandit 🎉',
        $newcomerName . ' vient de rejoindre le club. Souhaitons-lui la bienvenue !',
        ['type' => 'welcome-invitation', 'newcomer' => $newcomerId, 'name' => $newcomerName],
    );
    return true;
}

/**
 * Transforme les souhaits vieux de 24 heures en un seul accueil persistant.
 * Le texte ne donne volontairement aucun nombre : tous les bonjours sont
 * réunis dans un message chaleureux au nom de la famille 406 Coupé.
 */
function processWelcomeRounds(PDO $pdo): int
{
    ensureTables($pdo);
    $now = time();
    $due = $pdo->prepare('SELECT newcomer_id FROM c406_welcome_rounds
                          WHERE delivered_at IS NULL AND deliver_at <= ?
                          ORDER BY deliver_at ASC LIMIT 50');
    $due->execute([$now]);
    $delivered = 0;

    foreach ($due->fetchAll(PDO::FETCH_COLUMN) as $newcomerId) {
        $pdo->beginTransaction();
        try {
            $lock = $pdo->prepare('SELECT newcomer_name, deliver_at, delivered_at
                                   FROM c406_welcome_rounds WHERE newcomer_id = ? FOR UPDATE');
            $lock->execute([(string)$newcomerId]);
            $round = $lock->fetch();
            if (!$round || $round['delivered_at'] !== null || (int)$round['deliver_at'] > $now) {
                $pdo->rollBack();
                continue;
            }

            $member = $pdo->prepare('SELECT id FROM c406_members
                                     WHERE id = ? AND approved_at IS NOT NULL AND banned_at IS NULL LIMIT 1');
            $member->execute([(string)$newcomerId]);
            if (!$member->fetchColumn()) {
                $pdo->prepare('UPDATE c406_welcome_rounds SET delivered_at = ? WHERE newcomer_id = ?')
                    ->execute([$now, (string)$newcomerId]);
                $pdo->commit();
                continue;
            }

            $title = 'Bienvenue dans la famille 406 Coupé !';
            $body = 'Les membres de la famille 406 Coupé vous souhaitent la bienvenue. '
                . 'Toute la communauté est heureuse de vous accueillir parmi nous !';
            $pdo->prepare('INSERT INTO c406_member_notices (member_id, kind, title, body, created_at, seen_at)
                           VALUES (?, ?, ?, ?, ?, NULL)')
                ->execute([(string)$newcomerId, 'welcome-summary', $title, $body, $now]);
            $pdo->prepare('UPDATE c406_welcome_rounds SET delivered_at = ? WHERE newcomer_id = ?')
                ->execute([$now, (string)$newcomerId]);
            $pdo->commit();

            pushToMembers($pdo, [(string)$newcomerId], $title,
                'Les membres du club vous ont laissé un message de bienvenue.',
                ['type' => 'welcome-summary']);
            $delivered++;
        } catch (Throwable $error) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            error_log('[coupe406] accueil collectif impossible : ' . $error->getMessage());
        }
    }
    return $delivered;
}