xamxam/app/public/admin/actions/_peertube-orphans-data.php

<?php
/**
 * Shared PeerTube orphan data-fetching logic.
 *
 * Used by both peertube-orphans.php (JSON endpoint) and
 * peertube-orphans-fragment.php (HTML fragment).
 */

function getPeerTubeOrphansData(): array
{
    require_once APP_ROOT . '/src/Database.php';
    require_once APP_ROOT . '/src/PeerTubeService.php';

    $db = new Database();

    if (!PeerTubeService::isConfigured($db)) {
        return [
            'configured' => false,
            'error'      => 'PeerTube non configuré.',
        ];
    }

    // ── Collect all Peertube UUIDs linked in the DB ──────────────────────
    $pdo       = $db->getPDO();
    $dbUuids   = [];
    $linkedMap = []; // uuid → [thesis_id, thesis_title, thesis_identifier]

    $stmt = $pdo->query(
        "SELECT tf.file_path, tf.file_name, t.id AS thesis_id, t.title, t.identifier
         FROM thesis_files tf
         JOIN theses t ON t.id = tf.thesis_id
         WHERE tf.file_path LIKE 'peertube_ids:%'
           AND t.deleted_at IS NULL"
    );
    while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
        $uuid = substr($row['file_path'], strlen('peertube_ids:'));
        $dbUuids[$uuid] = true;
        $linkedMap[$uuid][] = [
            'thesis_id'    => (int)$row['thesis_id'],
            'title'        => $row['title'],
            'identifier'   => $row['identifier'] ?? '',
        ];
    }

    // ── List all channel videos ──────────────────────────────────────────
    try {
        $channelVideos = PeerTubeService::listChannelVideos($db);
    } catch (\Throwable $e) {
        return [
            'configured' => true,
            'error'      => 'Erreur lors du listage des vidéos : ' . $e->getMessage(),
        ];
    }

    // ── Find orphans: on channel but not in DB ───────────────────────────
    $orphans = [];
    $linked  = [];
    foreach ($channelVideos as $v) {
        $uuid = $v['shortUUID'] ?: $v['uuid'];
        if ($uuid === '') {
            continue;
        }
        if (isset($dbUuids[$uuid])) {
            $linked[] = [
                'uuid'   => $uuid,
                'name'   => $v['name'],
                'theses' => $linkedMap[$uuid] ?? [],
            ];
        } else {
            $orphans[] = [
                'uuid'      => $uuid,
                'name'      => $v['name'],
                'createdAt' => $v['createdAt'],
            ];
        }
    }

    // ── Find stale DB entries: in DB but not on channel ──────────────────
    $stale = [];
    foreach ($dbUuids as $uuid => $_) {
        $found = false;
        foreach ($channelVideos as $v) {
            if (($v['shortUUID'] ?: $v['uuid']) === $uuid) {
                $found = true;
                break;
            }
        }
        if (!$found) {
            $stale[] = [
                'uuid'   => $uuid,
                'theses' => $linkedMap[$uuid] ?? [],
            ];
        }
    }

    $totalOnChannel = count($channelVideos);

    return [
        'configured'       => true,
        'channel_name'     => PeerTubeService::getSettings($db)['channel_name'],
        'total_on_channel' => $totalOnChannel,
        'total_linked'     => count($linked),
        'orphan_count'     => count($orphans),
        'orphans'          => $orphans,
        'stale_count'      => count($stale),
        'stale_entries'    => $stale,
    ];
}