AsterionWP2026/tools/av-wpml-fix-status.php

<?php
/**
 * Plugin Name: Asterion — WPML translation_status diagnose + fix
 * Description:
 *   /?av_wpml_status_dump=1   → dump state of every linked FR page (icl_translations
 *                                + icl_translation_status + Bricks meta + ATE editor pref)
 *   /?av_wpml_status_fix=1    → insert/update icl_translation_status rows so WPML
 *                                marks our programmatically-translated pages as
 *                                "completed via ATE", clears the orange "needs update"
 *                                icon, and lets the pencil → ATE button work.
 *
 * Why : our pipeline writes _bricks_page_content_2 directly via $wpdb. That bypasses
 * WPML's normal "translation completed" hook. WPML sees the linked posts in
 * icl_translations but icl_translation_status is empty → the WPML UI thinks the
 * translation is missing/incomplete and routes the pencil button to the WP editor
 * fallback instead of the Advanced Translation Editor.
 */
defined('ABSPATH') || exit;

add_action('init', function () {
    if (! in_array($_SERVER['SERVER_NAME'] ?? '', ['localhost', '127.0.0.1'], true)) return;

    if (! empty($_GET['av_wpml_status_dump'])) {
        av_wpml_status_dump();
    }
    if (! empty($_GET['av_wpml_status_fix'])) {
        av_wpml_status_fix();
    }
    if (! empty($_GET['av_wpml_repair'])) {
        av_wpml_repair();   // full pipeline : relink-trid → cleanup orphans → status_fix
    }
});

function av_wpml_target_trids() {
    global $wpdb;
    // All trids that have a /fr/ post WHERE EN parent is /industries/, /solutions/
    // or the hub pages themselves.
    return $wpdb->get_col(
        "SELECT DISTINCT t.trid
         FROM {$wpdb->prefix}icl_translations t
         JOIN {$wpdb->posts} p ON p.ID = t.element_id
         WHERE t.element_type='post_page'
           AND t.element_id > 0
           AND (p.post_name IN ('industries','solutions','police','special-forces','military','firefighters','my-proserve','proserve-flex','proserve-academy','customization'))"
    );
}

function av_wpml_status_dump() {
    global $wpdb;
    header('Content-Type: text/plain; charset=utf-8');

    // 1) Sitepress editor preference
    $opts = get_option('icl_sitepress_settings');
    $editor_default = $opts['translation-management']['doc_translation_method'] ?? '(unset)';
    $editor_alt     = $opts['translation_method'] ?? '(unset)';
    $ate_enabled    = $opts['translation-management']['ate']['ate_enabled'] ?? '(unset)';
    echo "WPML editor preference :\n";
    echo "  doc_translation_method = $editor_default\n";
    echo "  translation_method     = $editor_alt\n";
    echo "  ate_enabled            = $ate_enabled\n";
    echo "\n";

    // 2) For each target trid, dump rows
    $trids = av_wpml_target_trids();
    if (! $trids) { echo "(no target trids found)\n"; exit; }
    echo "Found " . count($trids) . " target trid(s) : " . implode(', ', $trids) . "\n\n";

    foreach ($trids as $trid) {
        echo "── trid $trid ──\n";
        $tr_rows = $wpdb->get_results($wpdb->prepare(
            "SELECT translation_id, element_id, language_code, source_language_code
             FROM {$wpdb->prefix}icl_translations
             WHERE trid=%d ORDER BY language_code",
            $trid
        ));
        foreach ($tr_rows as $r) {
            $title = get_the_title($r->element_id);
            echo "  [{$r->language_code}] post #{$r->element_id} '{$title}' (translation_id={$r->translation_id}, src={$r->source_language_code})\n";

            // status row
            $st = $wpdb->get_row($wpdb->prepare(
                "SELECT status, needs_update, translation_service, translator_id, md5
                 FROM {$wpdb->prefix}icl_translation_status
                 WHERE translation_id=%d",
                $r->translation_id
            ));
            if ($st) {
                echo "       status_row : status=$st->status needs_update=$st->needs_update service=$st->translation_service translator=$st->translator_id md5=" . substr($st->md5 ?? '', 0, 8) . "\n";
            } else {
                echo "       status_row : MISSING (no row in icl_translation_status)\n";
            }
        }
        echo "\n";
    }
    exit;
}

/**
 * Full repair pipeline :
 *   1. For each known EN slug, find EN's trid.
 *   2. Find any FR post with the same slug. If it's linked to a DIFFERENT trid,
 *      relink it to EN's trid (this fixes session-3 re-creation cases where
 *      set_element_language_details fell back to a fresh trid because an orphan
 *      empty FR row blocked the canonical (trid, fr, ...) slot).
 *   3. Delete orphan FR rows (element_id NULL/0/empty) in the canonical trid.
 *   4. Reset icl_translation_status (status=10, needs_update=0, md5 from EN content).
 */
function av_wpml_repair() {
    global $wpdb;
    header('Content-Type: text/plain; charset=utf-8');

    $slugs = [
        // Industries
        'industries', 'police', 'special-forces', 'military', 'firefighters',
        // Solutions
        'solutions', 'my-proserve', 'proserve-flex', 'proserve-academy', 'customization',
    ];

    echo "── STEP 1+2 : align FR posts to EN's trid ──\n\n";
    foreach ($slugs as $slug) {
        // EN post for slug
        $en = $wpdb->get_row($wpdb->prepare(
            "SELECT p.ID, t.trid
             FROM {$wpdb->posts} p
             JOIN {$wpdb->prefix}icl_translations t ON t.element_id = p.ID AND t.element_type='post_page'
             WHERE p.post_name=%s AND t.language_code='en'
             LIMIT 1",
            $slug
        ));
        if (! $en) { echo "  $slug : no EN page found — skip\n"; continue; }

        // FR post for slug (regardless of current trid)
        $fr = $wpdb->get_row($wpdb->prepare(
            "SELECT p.ID, t.trid, t.translation_id
             FROM {$wpdb->posts} p
             JOIN {$wpdb->prefix}icl_translations t ON t.element_id = p.ID AND t.element_type='post_page'
             WHERE p.post_name=%s AND t.language_code='fr'
             LIMIT 1",
            $slug
        ));
        if (! $fr) { echo "  $slug : EN #{$en->ID} (trid={$en->trid}) — no FR yet\n"; continue; }

        if ((int) $fr->trid === (int) $en->trid) {
            echo "  $slug : EN #{$en->ID} ↔ FR #{$fr->ID} aligned on trid={$en->trid} ✓\n";
        } else {
            // 1) Remove any orphan FR row in EN's canonical trid
            $orphans = $wpdb->query($wpdb->prepare(
                "DELETE FROM {$wpdb->prefix}icl_translations
                 WHERE trid=%d AND language_code='fr' AND element_type='post_page'
                   AND (element_id IS NULL OR element_id = 0 OR element_id = '')",
                $en->trid
            ));
            // 2) Move FR translation_id to EN's trid
            $wpdb->update(
                $wpdb->prefix . 'icl_translations',
                ['trid' => $en->trid, 'source_language_code' => 'en'],
                ['translation_id' => $fr->translation_id],
                ['%d', '%s'],
                ['%d']
            );
            // 3) Delete the now-empty old trid row(s) of FR's previous trid (in case anything else was attached there)
            // Skip — keep it conservative. The old trid row for FR is now moved, not deleted.

            echo "  $slug : FR #{$fr->ID} moved from trid={$fr->trid} → {$en->trid} (deleted $orphans orphan(s))\n";
        }
    }

    echo "\n── STEP 3 : remove ALL orphan FR rows across known trids ──\n\n";
    $trids = av_wpml_target_trids();
    foreach ($trids as $trid) {
        $deleted = $wpdb->query($wpdb->prepare(
            "DELETE FROM {$wpdb->prefix}icl_translations
             WHERE trid=%d AND (element_id IS NULL OR element_id = 0 OR element_id = '')",
            $trid
        ));
        if ($deleted > 0) echo "  trid $trid : deleted $deleted orphan row(s)\n";
    }

    echo "\n── STEP 4 : write icl_translation_status (status=10 completed) ──\n\n";
    av_wpml_status_fix(false);
    echo "\nDONE.\n";
    exit;
}

function av_wpml_status_fix($exit_after = true) {
    global $wpdb;
    header('Content-Type: text/plain; charset=utf-8');

    $trids = av_wpml_target_trids();
    $patched = 0; $inserted = 0;

    foreach ($trids as $trid) {
        // Get EN and FR translation_id + post_id
        $en = $wpdb->get_row($wpdb->prepare(
            "SELECT translation_id, element_id FROM {$wpdb->prefix}icl_translations
             WHERE trid=%d AND language_code='en' AND element_id > 0 LIMIT 1",
            $trid
        ));
        $fr = $wpdb->get_row($wpdb->prepare(
            "SELECT translation_id, element_id FROM {$wpdb->prefix}icl_translations
             WHERE trid=%d AND language_code='fr' AND element_id > 0 LIMIT 1",
            $trid
        ));
        if (! $en || ! $fr) {
            echo "trid $trid : EN or FR missing — skip\n";
            continue;
        }

        // Compute md5 of EN _bricks_page_content_2 (what WPML's translation engine would
        // hash on a normal flow). When EN changes, md5 differs → WPML shows "needs update".
        $en_content = get_post_meta($en->element_id, '_bricks_page_content_2', true);
        if (is_array($en_content)) $en_content = wp_json_encode($en_content);
        $md5 = md5((string) $en_content);

        // Status codes (WPML constants, hardcoded for portability) :
        //   ICL_TM_NOT_TRANSLATED = 0
        //   ICL_TM_WAITING        = 1
        //   ICL_TM_NEEDS_UPDATE   = 3
        //   ICL_TM_DUPLICATE      = 9
        //   ICL_TM_COMPLETE       = 10
        $data = [
            'translation_id'      => $fr->translation_id,
            'status'              => 10,                 // ICL_TM_COMPLETE
            'needs_update'        => 0,
            'md5'                 => $md5,
            'translation_service' => 'local',           // not delegated to a paid service
            'translator_id'       => 0,                 // local human (us, programmatic)
            'translated_by_id'    => 0,
            'links_fixed'         => 1,
        ];

        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT rid FROM {$wpdb->prefix}icl_translation_status WHERE translation_id=%d",
            $fr->translation_id
        ));

        if ($existing) {
            // Only update fields that exist (drop optional ones to be safe)
            $update = $data;
            unset($update['translation_id']); // not changing PK
            $wpdb->update($wpdb->prefix . 'icl_translation_status', $update, ['rid' => $existing]);
            echo "trid $trid : FR #{$fr->element_id} → status row UPDATED (rid=$existing, status=10, md5=" . substr($md5, 0, 8) . ")\n";
            $patched++;
        } else {
            // Be careful : the table may not have all columns. Try with full first, fallback.
            $ok = $wpdb->insert($wpdb->prefix . 'icl_translation_status', $data);
            if (! $ok) {
                // Retry minimal
                $minimal = [
                    'translation_id' => $fr->translation_id,
                    'status'         => 10,
                    'needs_update'   => 0,
                    'md5'            => $md5,
                ];
                $ok = $wpdb->insert($wpdb->prefix . 'icl_translation_status', $minimal);
            }
            if ($ok) {
                echo "trid $trid : FR #{$fr->element_id} → status row INSERTED (status=10, md5=" . substr($md5, 0, 8) . ")\n";
                $inserted++;
            } else {
                echo "trid $trid : FR #{$fr->element_id} → INSERT FAILED : " . $wpdb->last_error . "\n";
            }
        }
    }

    echo "\nDONE. patched=$patched, inserted=$inserted\n";
    echo "Refresh WP Admin → Pages — orange refresh icons should clear, pencils should\n";
    echo "route to WPML's editor (ATE if ate_enabled=1, classic editor otherwise).\n";
    if ($exit_after) exit;
}