VITEC-website/migrations/fix_story_videos.php

<?php

declare(strict_types=1);

/**
 * Ticket 0017-FE Punkt 5b: Videos als echte vitec_video-Elemente.
 *
 * - betfred-hq: "The Result" endet mit "<p>Case Study Video</p>" + Link
 *   auf youtube nw52kbYpF3A -> Absaetze strippen, vitec_video danach.
 * - kanha-shanti-vanam: "The Result" traegt "<p>Short Videos</p><ul>"
 *   mit zwei YouTube-Links (WD0dHmqN4s0, C8FzHR5aUAI) -> strippen,
 *   zwei vitec_video danach.
 * - atlanta-hawks: html-Element #326 traegt den escapten iframe
 *   (-ds3p9jb5F0) -> Element loeschen, vitec_video an seiner Position.
 *
 * Einfuegung direkt per SQL (pid/colPos 999/tx_vitec_usecase_content/
 * sorting zwischen den Nachbarn); der Inline-Zaehler content_elements am
 * Usecase-Record wird mitgezogen.
 *
 * Usage:  php migrations/fix_story_videos.php          (inspect)
 *         php migrations/fix_story_videos.php --apply
 */

error_reporting(E_ALL);
ini_set('display_errors', '1');

$apply = in_array('--apply', $argv, true);

$root = null;
$dir = __DIR__;
for ($i = 0; $i < 5; $i++) {
    if (is_file($dir . '/config/system/settings.php')) {
        $root = $dir;
        break;
    }
    $dir = dirname($dir);
}
if ($root === null) {
    exit("Could not locate project root above " . __DIR__ . "\n");
}
$settings = require $root . '/config/system/settings.php';
$db = $settings['DB']['Connections']['Default'];
$pdo = new PDO(
    sprintf('mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $db['host'] ?? 'localhost', $db['port'] ?? 3306, $db['dbname'] ?? ''),
    $db['user'] ?? '',
    $db['password'] ?? '',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
);

echo ($apply ? 'APPLY' : 'INSPECT') . "\n";

$col = $pdo->query("SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tt_content' AND COLUMN_NAME = 'vitec_youtube_code'")->fetchColumn();
if ((int)$col !== 1) {
    exit("ABBRUCH: Spalte tt_content.vitec_youtube_code nicht gefunden.\n");
}

function storyUid(PDO $pdo, string $slug): int
{
    $stmt = $pdo->prepare('SELECT uid FROM tx_vitec_domain_model_usecase WHERE deleted = 0 AND slug = ?');
    $stmt->execute([$slug]);
    return (int)$stmt->fetchColumn();
}

/** sorting-Wert direkt nach $anchorUid; bei zu kleinem Abstand werden die Kinder vorher mit Stride 256 neu nummeriert. */
function sortingAfter(PDO $pdo, int $storyUid, int $anchorUid, bool $apply): int
{
    $children = $pdo->query("SELECT uid, sorting FROM tt_content
        WHERE deleted = 0 AND tx_vitec_usecase_content = $storyUid ORDER BY sorting, uid")->fetchAll();
    $index = array_search($anchorUid, array_map(static fn(array $r): int => (int)$r['uid'], $children), true);
    if ($index === false) {
        throw new RuntimeException("Anker #$anchorUid nicht unter den Kindern von Story #$storyUid");
    }
    $anchorSort = (int)$children[$index]['sorting'];
    $nextSort = isset($children[$index + 1]) ? (int)$children[$index + 1]['sorting'] : $anchorSort + 512;
    if ($nextSort - $anchorSort >= 4) {
        return $anchorSort + intdiv($nextSort - $anchorSort, 2);
    }
    // zu eng: komplett neu nummerieren und erneut rechnen
    if ($apply) {
        $sorting = 0;
        $stmt = $pdo->prepare('UPDATE tt_content SET sorting = ? WHERE uid = ?');
        foreach ($children as $child) {
            $sorting += 256;
            $stmt->execute([$sorting, (int)$child['uid']]);
        }
    }
    return ($index + 1) * 256 + 128;
}

function insertVideo(PDO $pdo, int $storyUid, int $pid, int $sorting, string $header, string $code, bool $apply): void
{
    printf("  + vitec_video \"%s\" code=%s sorting=%d\n", $header, $code, $sorting);
    if (!$apply) {
        return;
    }
    $pdo->prepare("INSERT INTO tt_content
        (pid, CType, colPos, tx_vitec_usecase_content, sorting, header, vitec_youtube_code, crdate, tstamp)
        VALUES (?, 'vitec_video', 999, ?, ?, ?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())")
        ->execute([$pid, $storyUid, $sorting, $header, $code]);
    $pdo->exec("UPDATE tx_vitec_domain_model_usecase
        SET content_elements = (SELECT COUNT(*) FROM tt_content WHERE deleted = 0 AND tx_vitec_usecase_content = $storyUid),
            tstamp = UNIX_TIMESTAMP()
        WHERE uid = $storyUid");
}

function stripFromBody(PDO $pdo, int $elementUid, string $pattern, bool $apply): bool
{
    $body = (string)$pdo->query("SELECT bodytext FROM tt_content WHERE uid = $elementUid")->fetchColumn();
    $clean = (string)preg_replace($pattern, '', $body);
    if ($clean === $body) {
        echo "  ! Strip-Muster traf NICHT in #$elementUid - nichts geaendert\n";
        return false;
    }
    echo "  ~ #$elementUid: Video-Absaetze aus bodytext entfernt\n";
    if ($apply) {
        $pdo->prepare('UPDATE tt_content SET bodytext = ?, tstamp = UNIX_TIMESTAMP() WHERE uid = ?')
            ->execute([trim($clean), $elementUid]);
    }
    return true;
}

// --- Betfred -----------------------------------------------------------------
echo "\n--- betfred-hq ---\n";
$uid = storyUid($pdo, 'betfred-hq');
$pid = (int)$pdo->query("SELECT pid FROM tx_vitec_domain_model_usecase WHERE uid = $uid")->fetchColumn();
if (stripFromBody($pdo, 11910, '#<p>\s*Case Study Video\s*</p>\s*<p>[^<]*nw52kbYpF3A[^<]*</p>#u', $apply)) {
    insertVideo($pdo, $uid, $pid, sortingAfter($pdo, $uid, 11910, $apply), 'Case Study Video', 'nw52kbYpF3A', $apply);
}

// --- Kanha Shanti Vanam --------------------------------------------------------
echo "\n--- kanha-shanti-vanam ---\n";
$uid = storyUid($pdo, 'kanha-shanti-vanam-meditation-center');
$pid = (int)$pdo->query("SELECT pid FROM tx_vitec_domain_model_usecase WHERE uid = $uid")->fetchColumn();
if (stripFromBody($pdo, 11964, '#<p>\s*Short Videos\s*</p>\s*<ul>\s*<li>[^<]*WD0dHmqN4s0[^<]*</li>\s*<li>[^<]*C8FzHR5aUAI[^<]*</li>\s*</ul>#u', $apply)) {
    $sorting = sortingAfter($pdo, $uid, 11964, $apply);
    insertVideo($pdo, $uid, $pid, $sorting, 'Short Videos', 'WD0dHmqN4s0', $apply);
    insertVideo($pdo, $uid, $pid, $sorting + 1, '', 'C8FzHR5aUAI', $apply);
}

// --- Atlanta Hawks --------------------------------------------------------------
echo "\n--- atlanta-hawks ---\n";
$uid = storyUid($pdo, 'atlanta-hawks');
$old = $pdo->query('SELECT uid, pid, sorting, bodytext FROM tt_content WHERE uid = 326 AND deleted = 0')->fetch();
if ($old && str_contains((string)$old['bodytext'], '-ds3p9jb5F0')) {
    echo "  ~ html #326 (escapter iframe) wird geloescht, vitec_video an seiner Position\n";
    if ($apply) {
        $pdo->exec('UPDATE tt_content SET deleted = 1, tstamp = UNIX_TIMESTAMP() WHERE uid = 326');
    }
    insertVideo($pdo, $uid, (int)$old['pid'], (int)$old['sorting'], '', '-ds3p9jb5F0', $apply);
} else {
    echo "  ! html #326 nicht gefunden oder ohne erwarteten iframe - uebersprungen\n";
}

echo $apply ? "\nFertig. Danach: vendor/bin/typo3 cache:flush\n" : "\nInspect only - mit --apply schreiben.\n";