feadulta/scripts/unpublish_date_slug_posts.php

<?php
/**
 * Unpublishes migrated posts whose title or slug is only a date (dd-mm-yyyy).
 *
 * Usage:
 *   docker cp scripts/unpublish_date_slug_posts.php wordpress-web:/tmp/unpublish_date_slug_posts.php
 *   docker exec wordpress-web php /tmp/unpublish_date_slug_posts.php --dry-run
 *   docker exec wordpress-web php /tmp/unpublish_date_slug_posts.php --apply
 */

$cli_args = array_merge($argv ?? [], $args ?? []);
$apply = getenv('APPLY') === '1' || in_array('--apply', $cli_args, true);
$dry_run = in_array('--dry-run', $cli_args, true) || !$apply;

$backup_table = 'wp_fea_date_slug_posts_backup';
$where = "
    post_type = 'post'
    AND post_status = 'publish'
    AND (
        post_title REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$'
        OR post_name REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$'
    )
";

echo "=== Unpublish date-like migrated posts ===\n";
echo $dry_run ? "[DRY RUN]\n\n" : "[APPLY]\n\n";

if (isset($GLOBALS['wpdb']) && $GLOBALS['wpdb'] instanceof wpdb) {
    global $wpdb;

    $posts_table = $wpdb->posts;
    $backup_table = $wpdb->prefix . 'fea_date_slug_posts_backup';

    $count = (int)$wpdb->get_var("SELECT COUNT(*) FROM $posts_table WHERE $where");
    echo "Matching published posts: $count\n";

    $sample = $wpdb->get_results("
        SELECT ID, post_date, post_title, post_name
        FROM $posts_table
        WHERE $where
        ORDER BY post_date DESC
        LIMIT 20
    ", ARRAY_A);

    foreach ($sample as $row) {
        echo sprintf(
            "  #%d %s %s (%s)\n",
            $row['ID'],
            $row['post_date'],
            $row['post_title'],
            $row['post_name']
        );
    }

    if ($dry_run) {
        echo "\nNo changes made. Re-run with APPLY=1 to set these posts to draft.\n";
        return;
    }

    $wpdb->query("
        CREATE TABLE IF NOT EXISTS $backup_table (
            post_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
            old_status VARCHAR(20) NOT NULL,
            old_modified DATETIME NOT NULL,
            backed_up_at DATETIME NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $backed_up = $wpdb->query("
        INSERT IGNORE INTO $backup_table (post_id, old_status, old_modified, backed_up_at)
        SELECT ID, post_status, post_modified, NOW()
        FROM $posts_table
        WHERE $where
    ");

    $updated = $wpdb->query("
        UPDATE $posts_table
        SET post_status = 'draft',
            post_modified = NOW(),
            post_modified_gmt = UTC_TIMESTAMP()
        WHERE $where
    ");

    echo "\nBacked up rows in $backup_table: $backed_up\n";
    echo "Posts set to draft: $updated\n";
    return;
}

$db_host = getenv('WORDPRESS_DB_HOST') ?: 'wordpress-db';
$db_name = getenv('WORDPRESS_DB_NAME') ?: 'wordpress_db';
$db_user = getenv('WORDPRESS_DB_USER') ?: 'wordpress_user';
$db_pass = getenv('WORDPRESS_DB_PASSWORD') ?: 'wordpress_pass';

$pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$count = (int)$pdo->query("SELECT COUNT(*) FROM wp_posts WHERE $where")->fetchColumn();
echo "Matching published posts: $count\n";

$sample = $pdo->query("
    SELECT ID, post_date, post_title, post_name
    FROM wp_posts
    WHERE $where
    ORDER BY post_date DESC
    LIMIT 20
")->fetchAll(PDO::FETCH_ASSOC);

foreach ($sample as $row) {
    echo sprintf(
        "  #%d %s %s (%s)\n",
        $row['ID'],
        $row['post_date'],
        $row['post_title'],
        $row['post_name']
    );
}

if ($dry_run) {
    echo "\nNo changes made. Re-run with --apply to set these posts to draft.\n";
    exit(0);
}

$pdo->exec("
    CREATE TABLE IF NOT EXISTS $backup_table (
        post_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
        old_status VARCHAR(20) NOT NULL,
        old_modified DATETIME NOT NULL,
        backed_up_at DATETIME NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

$pdo->beginTransaction();

$backed_up = $pdo->exec("
    INSERT IGNORE INTO $backup_table (post_id, old_status, old_modified, backed_up_at)
    SELECT ID, post_status, post_modified, NOW()
    FROM wp_posts
    WHERE $where
");

$updated = $pdo->exec("
    UPDATE wp_posts
    SET post_status = 'draft',
        post_modified = NOW(),
        post_modified_gmt = UTC_TIMESTAMP()
    WHERE $where
");

$pdo->commit();

echo "\nBacked up rows in $backup_table: $backed_up\n";
echo "Posts set to draft: $updated\n";