VITEC-website/migrations/fix_story_content_errors.php

<?php

declare(strict_types=1);

/**
 * Ticket 0017-FE Punkt 1 + 2 (Altbestand vitec.com-Import, gegen das
 * Original auf www.vitec.com verifiziert, 2026-10-29):
 *
 * 1) CDC (centers-for-disease-control-cdc): die Background-vitec_columns
 *    (#355) traegt links den Text des Crowne Plaza Muscat OCEC und rechts
 *    dessen Logo. Das Original hat dort CDC-Copy. -> linkes Text-Item auf
 *    die Original-Absaetze setzen, rechtes Logo-Item soft-loeschen
 *    (CDC-Bild kann redaktionell nachgelegt werden).
 *
 * 2) "Key Feature"-textmedia mit identischem Hotel-/Signage-Text in 5
 *    Stories. Abgleich mit dem Original:
 *      - parramatta-eels #218: Text ist DORT ECHT -> behalten
 *      - belgrade #253, isr #266, meydan #206: Original hat KEIN
 *        Key Feature -> Element soft-loeschen
 *      - proteo #234: Original hat MIL-spec-Bullets -> bodytext ersetzen
 *
 * Usage:  php migrations/fix_story_content_errors.php          (inspect)
 *         php migrations/fix_story_content_errors.php --apply
 */

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

const CDC_COLUMNS_UID = 355;
const DELETE_ELEMENTS = [253, 266, 206];
const PROTEO_UID = 234;
const KEEP_CHECK = 218;

const CDC_BACKGROUND = '<p>When it comes mitigating major threats to public health, it is imperative for organizations like the Centers for Disease Control (CDC) to keep their finger on the pulse of global developments that require a coordinated national or global response.</p>'
    . '<p>In addition to being ardent consumers of news and information, doctors, scientists, and other healthcare experts at the CDC produce a tremendous amount of original content for both internal and external consumption. This makes the agency one of the most video-centric departments in the federal government.</p>';

const PROTEO_KEYFEATURE = '<ul>'
    . '<li>MIL-spec ruggedization HEVC &amp; H.264 Encoder</li>'
    . '<li>Low latency HD/SD Streaming</li>'
    . '<li>Low bandwidth HEVC Streaming</li>'
    . '<li>Overlay for video input identification and timestamping</li>'
    . '<li>KLV / STANAG metadata processing for ISR</li>'
    . '</ul>';

$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";

// --- 1) CDC-Spalten-Items ----------------------------------------------------
// Collection-Tabellen generisch ueber foreign_table_parent_uid finden
// (die Content-Blocks-Felder heissen hier unprefixed: column/text/item_type)
$itemTables = $pdo->query(
    "SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND COLUMN_NAME = 'foreign_table_parent_uid'"
)->fetchAll(PDO::FETCH_COLUMN);
echo "\n--- 1) CDC #" . CDC_COLUMNS_UID . " · Kandidaten-Tabellen: " . implode(', ', $itemTables) . "\n";
$fixedLeft = false;
$deletedRight = false;
foreach ($itemTables as $table) {
    $cols = $pdo->query("SELECT COLUMN_NAME FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = " . $pdo->quote($table))->fetchAll(PDO::FETCH_COLUMN);
    $textCol = in_array('vitec_text', $cols, true) ? 'vitec_text' : (in_array('text', $cols, true) ? 'text' : null);
    $colCol = in_array('vitec_column', $cols, true) ? 'vitec_column' : (in_array('column', $cols, true) ? 'column' : null);
    if ($textCol === null || $colCol === null) {
        continue;
    }
    $items = $pdo->query("SELECT * FROM `$table` WHERE deleted = 0 AND foreign_table_parent_uid = " . CDC_COLUMNS_UID . " ORDER BY sorting")->fetchAll();
    if ($items === []) {
        continue;
    }
    echo "  Tabelle: $table (textCol=$textCol, colCol=$colCol)\n";
    foreach ($items as $item) {
        $text = (string)$item[$textCol];
        printf("  item #%d col=%s type=%s text=\"%s\"\n",
            $item['uid'], $item[$colCol] ?? '?',
            $item['vitec_item_type'] ?? ($item['item_type'] ?? '?'),
            mb_substr(trim(strip_tags($text)), 0, 70));
        $isHotelText = str_contains($text, 'Crowne Plaza');
        $column = (string)($item[$colCol] ?? 'left');
        if ($isHotelText && $textCol !== null) {
            echo "    -> linkes Text-Item: Hotel-Text wird durch CDC-Background ersetzt\n";
            if ($apply) {
                $pdo->prepare("UPDATE `$table` SET `$textCol` = ?, tstamp = UNIX_TIMESTAMP() WHERE uid = ?")
                    ->execute([CDC_BACKGROUND, (int)$item['uid']]);
                $fixedLeft = true;
            }
        } elseif ($column === 'right') {
            echo "    -> rechtes Item (Hotel-Logo): wird soft-geloescht\n";
            if ($apply) {
                $pdo->prepare("UPDATE `$table` SET deleted = 1, tstamp = UNIX_TIMESTAMP() WHERE uid = ?")
                    ->execute([(int)$item['uid']]);
                $deletedRight = true;
            }
        }
    }
    break; // erste Tabelle mit Items reicht
}
if ($apply) {
    echo $fixedLeft ? "  Text ersetzt.\n" : "  WARNUNG: kein Hotel-Text-Item gefunden!\n";
    echo $deletedRight ? "  Logo-Item geloescht.\n" : "  Hinweis: kein rechtes Item geloescht.\n";
}

// --- 2) Key-Feature-Elemente -------------------------------------------------
echo "\n--- 2) Key-Feature-Elemente ---\n";
$in = implode(',', array_merge(DELETE_ELEMENTS, [PROTEO_UID, KEEP_CHECK]));
foreach ($pdo->query("SELECT uid, CType, LEFT(header,30) h, LEFT(bodytext,70) b, deleted FROM tt_content WHERE uid IN ($in)") as $row) {
    $uid = (int)$row['uid'];
    $action = in_array($uid, DELETE_ELEMENTS, true) ? 'LOESCHEN (Original hat kein Key Feature)'
        : ($uid === PROTEO_UID ? 'bodytext -> MIL-spec-Bullets (Original)' : 'BEHALTEN (Text im Original echt)');
    printf("  #%-4d %-12s \"%s\" \"%s\"%s -> %s\n", $uid, $row['CType'], $row['h'],
        trim(strip_tags((string)$row['b'])), $row['deleted'] ? ' [DELETED]' : '', $action);
}
if ($apply) {
    $pdo->exec('UPDATE tt_content SET deleted = 1, tstamp = UNIX_TIMESTAMP() WHERE uid IN (' . implode(',', DELETE_ELEMENTS) . ')');
    $pdo->prepare('UPDATE tt_content SET bodytext = ?, tstamp = UNIX_TIMESTAMP() WHERE uid = ?')
        ->execute([PROTEO_KEYFEATURE, PROTEO_UID]);
    echo "  3 geloescht, Proteo ersetzt.\n";
}

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