WooCommerce Subscriptions: Diagnostic Queries - Detect subscriptions silently broken by the requires_manual_renewal bug

wcs-diagnostics.sql

-- =============================================================================
-- WooCommerce Subscriptions: Diagnostic Queries
-- Detect subscriptions silently broken by the requires_manual_renewal bug
-- =============================================================================
--
-- WHO SHOULD RUN THIS:
--   Any store running WooCommerce Subscriptions that:
--   - Used HPOS (High-Performance Order Storage) before mid-2024
--   - Has subscriptions dating back to 2017 or later
--   - Uses Stripe, PayPal, or any non-WooCommerce-Payments gateway
--
-- The bugs were fixed piecemeal in subscriptions-core 6.5.0 through 7.1.0
-- (WCS plugin versions ~5.6.0 through ~6.3.1). If your store was running
-- any version before WCS 6.3.1, you may have affected subscriptions.
--
-- BEFORE YOU START:
--   0. The queries below should ONLY be used on ENGLISH stores! Other languages will return FALSE POSITIVES.
--   1. Replace {prefix} with your WordPress table prefix (e.g. wp_)
--   2. The SELECT queries are read-only. They do not modify any data.
--   3. The UPDATE queries MODIFY DATA. Read them carefully and BACK UP your database before running.
--   4. Run them in phpMyAdmin, Adminer, or any MySQL client.
--   5. Back up your database before running any UPDATE queries (Section 6).
--
-- !! IMPORTANT DISCLAIMER !!
--   Do not run the remediation queries WITHOUT READING THEM FIRST.
--   DO NOT RUN THEM ALL AT ONCE. Read each one, understand what it does,
--   run them in order, and verify the results at each step before
--   proceeding to the next. Always back up your database before running
--   any SQL queries. Or, better yet, run them on a staging copy first
--   to verify the results before applying to production.
--
-- TABLE GUIDE:
--   HPOS stores:  subscriptions live in {prefix}wc_orders
--                 subscription meta lives in {prefix}wc_orders_meta
--   CPT stores:   subscriptions live in {prefix}posts
--                 subscription meta lives in {prefix}postmeta
--   Order notes:  always in {prefix}comments (both HPOS and CPT)
--
-- HOW TO TELL IF YOU USE HPOS:
--   WooCommerce > Settings > Advanced > Features
--   If "High-performance order storage" is checked, you're on HPOS.
--   If you're unsure, try the HPOS queries first. If they return 0 results
--   but the CPT queries return results, you're on CPT (or HPOS with sync).
--
-- =============================================================================


-- =============================================================================
-- SECTION 1: QUICK CHECK — Am I affected?
-- =============================================================================
-- This counts subscriptions that are likely bug victims:
--   - Active, on-hold, or pending-cancel
--   - Has a valid payment method (not empty)
--   - requires_manual_renewal = 'true'
--   - Customer did NOT intentionally turn off auto-renewal
--
-- If this returns 0, you're probably fine. If it returns any number,
-- proceed to Section 2 for the full list.
-- =============================================================================

-- HPOS version:
SELECT COUNT(DISTINCT o.id) AS affected_subscriptions
FROM {prefix}wc_orders o
JOIN {prefix}wc_orders_meta mr
    ON o.id = mr.order_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
WHERE o.type = 'shop_subscription'
  AND o.status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  AND o.payment_method != ''
  AND o.payment_method IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = o.id
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  );

-- CPT version (if you don't use HPOS):
SELECT COUNT(DISTINCT p.ID) AS affected_subscriptions
FROM {prefix}posts p
JOIN {prefix}postmeta mr
    ON p.ID = mr.post_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
JOIN {prefix}postmeta pm
    ON p.ID = pm.post_id
    AND pm.meta_key = '_payment_method'
    AND pm.meta_value != ''
WHERE p.post_type = 'shop_subscription'
  AND p.post_status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = p.ID
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  );


-- =============================================================================
-- SECTION 2: FULL LIST — Which subscriptions are affected?
-- =============================================================================
-- Shows each affected subscription with its status, payment method, and
-- creation date. Use this to review before applying any fix.
-- =============================================================================

-- HPOS version:
SELECT
    o.id              AS subscription_id,
    o.status          AS sub_status,
    o.payment_method  AS payment_method,
    o.date_created_gmt AS created,
    o.total_amount    AS recurring_total,
    mr.meta_value     AS manual_renewal
FROM {prefix}wc_orders o
JOIN {prefix}wc_orders_meta mr
    ON o.id = mr.order_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
WHERE o.type = 'shop_subscription'
  AND o.status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  AND o.payment_method != ''
  AND o.payment_method IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = o.id
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  )
ORDER BY o.date_created_gmt ASC;

-- CPT version:
SELECT
    p.ID              AS subscription_id,
    p.post_status     AS sub_status,
    pm.meta_value     AS payment_method,
    p.post_date_gmt   AS created,
    tot.meta_value    AS recurring_total,
    mr.meta_value     AS manual_renewal
FROM {prefix}posts p
JOIN {prefix}postmeta mr
    ON p.ID = mr.post_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
JOIN {prefix}postmeta pm
    ON p.ID = pm.post_id
    AND pm.meta_key = '_payment_method'
    AND pm.meta_value != ''
LEFT JOIN {prefix}postmeta tot
    ON p.ID = tot.post_id
    AND tot.meta_key = '_order_total'
WHERE p.post_type = 'shop_subscription'
  AND p.post_status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = p.ID
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  )
ORDER BY p.post_date_gmt ASC;


-- =============================================================================
-- SECTION 3: HISTORICAL — Include cancelled/expired subscriptions
-- =============================================================================
-- The queries above only show live subscriptions (active/on-hold/pending-cancel).
-- Many affected subscriptions may have already been cancelled because the
-- customer churned. This query shows the full scope including cancelled ones.
--
-- WARNING: This will include more false positives. Some cancelled subscriptions
-- were legitimately manual. Cross-reference with order notes if needed.
-- =============================================================================

-- HPOS version:
SELECT
    o.id              AS subscription_id,
    o.status          AS sub_status,
    o.payment_method  AS payment_method,
    o.date_created_gmt AS created,
    o.total_amount    AS recurring_total
FROM {prefix}wc_orders o
JOIN {prefix}wc_orders_meta mr
    ON o.id = mr.order_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
WHERE o.type = 'shop_subscription'
  AND o.payment_method != ''
  AND o.payment_method IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = o.id
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  )
ORDER BY o.date_created_gmt ASC;

-- CPT version:
SELECT
    p.ID              AS subscription_id,
    p.post_status     AS sub_status,
    pm.meta_value     AS payment_method,
    p.post_date_gmt   AS created,
    tot.meta_value    AS recurring_total
FROM {prefix}posts p
JOIN {prefix}postmeta mr
    ON p.ID = mr.post_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
JOIN {prefix}postmeta pm
    ON p.ID = pm.post_id
    AND pm.meta_key = '_payment_method'
    AND pm.meta_value != ''
LEFT JOIN {prefix}postmeta tot
    ON p.ID = tot.post_id
    AND tot.meta_key = '_order_total'
WHERE p.post_type = 'shop_subscription'
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = p.ID
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  )
ORDER BY p.post_date_gmt ASC;


-- =============================================================================
-- SECTION 4: SWITCH BUG — Subscriptions also hit by Bug 4
-- =============================================================================
-- This finds subscriptions from Section 2 that also have a completed switch
-- order using the same payment gateway. These were double-hit: born broken
-- by Bugs 1-3, then the switch failed to recover them (Bug 4).
--
-- The switch bug: when a customer switches (upgrades/downgrades) a subscription,
-- maybe_set_payment_method_after_switch() compares the switch order's gateway
-- to the subscription's current gateway. If they match, it short-circuits
-- and never clears requires_manual_renewal. The method assumed setting the
-- payment method would clear the flag, but WC_Subscription::set_payment_method()
-- has an internal guard: if the gateway is already the same, it does nothing.
-- =============================================================================

-- HPOS version:
SELECT
    o.id                    AS subscription_id,
    o.status                AS sub_status,
    o.payment_method        AS sub_gateway,
    switch_order.id         AS switch_order_id,
    switch_order.payment_method AS switch_gateway,
    switch_order.date_created_gmt AS switch_date
FROM {prefix}wc_orders o
JOIN {prefix}wc_orders_meta mr
    ON o.id = mr.order_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
JOIN {prefix}wc_orders_meta switch_link
    ON switch_link.meta_key = '_subscription_switch'
    AND switch_link.meta_value = o.id
JOIN {prefix}wc_orders switch_order
    ON switch_order.id = switch_link.order_id
    AND switch_order.status IN ('wc-completed', 'wc-processing')
    AND switch_order.payment_method = o.payment_method
WHERE o.type = 'shop_subscription'
  AND o.status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  AND o.payment_method != ''
  AND o.payment_method IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = o.id
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  )
ORDER BY o.id;

-- CPT version:
SELECT
    p.ID                    AS subscription_id,
    p.post_status           AS sub_status,
    pm.meta_value           AS sub_gateway,
    switch_order.ID         AS switch_order_id,
    spm.meta_value          AS switch_gateway,
    switch_order.post_date_gmt AS switch_date
FROM {prefix}posts p
JOIN {prefix}postmeta mr
    ON p.ID = mr.post_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
JOIN {prefix}postmeta pm
    ON p.ID = pm.post_id
    AND pm.meta_key = '_payment_method'
    AND pm.meta_value != ''
JOIN {prefix}postmeta switch_link
    ON switch_link.meta_key = '_subscription_switch'
    AND switch_link.meta_value = p.ID
JOIN {prefix}posts switch_order
    ON switch_order.ID = switch_link.post_id
    AND switch_order.post_status IN ('wc-completed', 'wc-processing')
JOIN {prefix}postmeta spm
    ON spm.post_id = switch_order.ID
    AND spm.meta_key = '_payment_method'
    AND spm.meta_value = pm.meta_value
WHERE p.post_type = 'shop_subscription'
  AND p.post_status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = p.ID
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  )
ORDER BY p.ID;


-- =============================================================================
-- SECTION 5: REVENUE LOSS ESTIMATION
-- =============================================================================
-- Estimates how much revenue was lost by comparing expected renewals
-- (based on recurring total and billing interval) to actual collected
-- renewals for each affected subscription.
--
-- NOTE: This is an estimate. It assumes the subscription would have renewed
-- continuously from creation to cancellation (or today, if still active).
-- Real-world churn means actual losses may differ.
-- =============================================================================

-- HPOS version:
SELECT
    base.subscription_id,
    base.sub_status,
    base.recurring_total,
    base.billing_period,
    base.billing_interval,
    base.created,
    base.end_date,
    base.expected_renewals,
    base.expected_renewals * base.recurring_total AS expected_revenue,
    base.actual_collected,
    base.expected_renewals * base.recurring_total - base.actual_collected AS estimated_loss
FROM (
    SELECT
        sub.id                  AS subscription_id,
        sub.status              AS sub_status,
        sub.total_amount        AS recurring_total,
        COALESCE(period.meta_value, 'year')  AS billing_period,
        COALESCE(intrvl.meta_value, 1) + 0   AS billing_interval,
        sub.date_created_gmt    AS created,
        COALESCE(LEAST(
            COALESCE(cancel.meta_value, CURDATE()),
            COALESCE(enddt.meta_value, CURDATE())
        ), CURDATE()) AS end_date,
        CASE COALESCE(period.meta_value, 'year')
            WHEN 'day'   THEN FLOOR(TIMESTAMPDIFF(DAY,   sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'week'  THEN FLOOR(TIMESTAMPDIFF(WEEK,  sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'month' THEN FLOOR(TIMESTAMPDIFF(MONTH, sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'year'  THEN FLOOR(TIMESTAMPDIFF(MONTH, sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / ((COALESCE(intrvl.meta_value, 1) + 0) * 12))
        END AS expected_renewals,
        COALESCE(collected.total, 0) AS actual_collected

    FROM {prefix}wc_orders sub
    JOIN {prefix}wc_orders_meta mr
        ON sub.id = mr.order_id
        AND mr.meta_key = '_requires_manual_renewal'
        AND mr.meta_value = 'true'
    LEFT JOIN {prefix}wc_orders_meta period
        ON sub.id = period.order_id
        AND period.meta_key = '_billing_period'
    LEFT JOIN {prefix}wc_orders_meta intrvl
        ON sub.id = intrvl.order_id
        AND intrvl.meta_key = '_billing_interval'
    LEFT JOIN {prefix}wc_orders_meta cancel
        ON sub.id = cancel.order_id
        AND cancel.meta_key = '_schedule_cancelled'
    LEFT JOIN {prefix}wc_orders_meta enddt
        ON sub.id = enddt.order_id
        AND enddt.meta_key = '_schedule_end'
    LEFT JOIN (
        SELECT
            CAST(rl.meta_value AS UNSIGNED) AS sub_id,
            SUM(ro.total_amount) AS total
        FROM {prefix}wc_orders_meta rl
        JOIN {prefix}wc_orders ro
            ON ro.id = rl.order_id
            AND ro.status IN ('wc-completed', 'wc-processing')
        WHERE rl.meta_key = '_subscription_renewal'
        GROUP BY CAST(rl.meta_value AS UNSIGNED)
    ) collected ON collected.sub_id = sub.id

    WHERE sub.type = 'shop_subscription'
      AND sub.status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel', 'wc-cancelled')
      AND sub.payment_method != ''
      AND sub.payment_method IS NOT NULL
      AND NOT EXISTS (
          SELECT 1 FROM {prefix}comments c
          WHERE c.comment_post_ID = sub.id
            AND c.comment_type = 'order_note'
            AND c.comment_content LIKE '%turned off automatic renewals%'
      )
) base
ORDER BY base.created ASC;


-- =============================================================================
-- SECTION 5b: REVENUE LOSS SUMMARY
-- =============================================================================
-- Same as above, but aggregated into a single row with totals.
-- =============================================================================

-- HPOS version:
SELECT
    COUNT(*) AS affected_subscriptions,
    SUM(
        (CASE COALESCE(period.meta_value, 'year')
            WHEN 'day'   THEN FLOOR(TIMESTAMPDIFF(DAY,   sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'week'  THEN FLOOR(TIMESTAMPDIFF(WEEK,  sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'month' THEN FLOOR(TIMESTAMPDIFF(MONTH, sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'year'  THEN FLOOR(TIMESTAMPDIFF(MONTH, sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / ((COALESCE(intrvl.meta_value, 1) + 0) * 12))
        END) * sub.total_amount
    ) AS total_expected_revenue,
    SUM(COALESCE(collected.total, 0)) AS total_actual_collected,
    SUM(
        (CASE COALESCE(period.meta_value, 'year')
            WHEN 'day'   THEN FLOOR(TIMESTAMPDIFF(DAY,   sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'week'  THEN FLOOR(TIMESTAMPDIFF(WEEK,  sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'month' THEN FLOOR(TIMESTAMPDIFF(MONTH, sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / (COALESCE(intrvl.meta_value, 1) + 0))
            WHEN 'year'  THEN FLOOR(TIMESTAMPDIFF(MONTH, sub.date_created_gmt, COALESCE(LEAST(COALESCE(cancel.meta_value, CURDATE()), COALESCE(enddt.meta_value, CURDATE())), CURDATE())) / ((COALESCE(intrvl.meta_value, 1) + 0) * 12))
        END) * sub.total_amount
    ) - SUM(COALESCE(collected.total, 0)) AS total_estimated_loss

FROM {prefix}wc_orders sub
JOIN {prefix}wc_orders_meta mr
    ON sub.id = mr.order_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
LEFT JOIN {prefix}wc_orders_meta period
    ON sub.id = period.order_id
    AND period.meta_key = '_billing_period'
LEFT JOIN {prefix}wc_orders_meta intrvl
    ON sub.id = intrvl.order_id
    AND intrvl.meta_key = '_billing_interval'
LEFT JOIN {prefix}wc_orders_meta cancel
    ON sub.id = cancel.order_id
    AND cancel.meta_key = '_schedule_cancelled'
LEFT JOIN {prefix}wc_orders_meta enddt
    ON sub.id = enddt.order_id
    AND enddt.meta_key = '_schedule_end'
LEFT JOIN (
    SELECT
        CAST(rl.meta_value AS UNSIGNED) AS sub_id,
        SUM(ro.total_amount) AS total
    FROM {prefix}wc_orders_meta rl
    JOIN {prefix}wc_orders ro
        ON ro.id = rl.order_id
        AND ro.status IN ('wc-completed', 'wc-processing')
    WHERE rl.meta_key = '_subscription_renewal'
    GROUP BY CAST(rl.meta_value AS UNSIGNED)
) collected ON collected.sub_id = sub.id
WHERE sub.type = 'shop_subscription'
  AND sub.status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel', 'wc-cancelled')
  AND sub.payment_method != ''
  AND sub.payment_method IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = sub.id
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  );


-- =============================================================================
-- SECTION 6: FIX — Remediate affected subscriptions
-- =============================================================================
--
-- !! DANGER ZONE — THESE QUERIES MODIFY DATA !!
--
-- Only run these AFTER reviewing Sections 1-2 and confirming the results
-- match your expectations. BACK UP YOUR DATABASE FIRST.
--
-- What the fix does:
--   Sets requires_manual_renewal from 'true' to 'false' on all affected
--   subscriptions that have a valid payment method and no customer opt-out.
--   This restores automatic payment processing on the next renewal.
--
-- What the fix does NOT do:
--   - It does not retroactively charge missed renewals
--   - It does not reactivate cancelled subscriptions
--   - It does not change any payment method or token data
--   - It does not touch subscriptions where the customer opted out
--
-- After running: verify in WooCommerce admin that affected subscriptions
-- now show "Automatic renewal" instead of "Manual renewal."
-- =============================================================================

-- FIX: HPOS (run this if you use HPOS)
UPDATE {prefix}wc_orders_meta
SET meta_value = 'false'
WHERE meta_key = '_requires_manual_renewal'
  AND meta_value = 'true'
  AND order_id IN (
      SELECT o.id FROM {prefix}wc_orders o
      WHERE o.type = 'shop_subscription'
        AND o.status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
        AND o.payment_method != ''
        AND o.payment_method IS NOT NULL
        AND NOT EXISTS (
            SELECT 1 FROM {prefix}comments c
            WHERE c.comment_post_ID = o.id
              AND c.comment_type = 'order_note'
              AND c.comment_content LIKE '%turned off automatic renewals%'
        )
  );

-- FIX: Postmeta sync (run this too if you have HPOS with data sync enabled,
-- or if you use CPT storage)
UPDATE {prefix}postmeta
SET meta_value = 'false'
WHERE meta_key = '_requires_manual_renewal'
  AND meta_value = 'true'
  AND post_id IN (
      SELECT ID FROM {prefix}posts
      WHERE post_type = 'shop_subscription'
        AND post_status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  )
  AND post_id IN (
      SELECT post_id FROM {prefix}postmeta
      WHERE meta_key = '_payment_method'
        AND meta_value != ''
  )
  AND post_id NOT IN (
      SELECT DISTINCT c.comment_post_ID FROM {prefix}comments c
      WHERE c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  );

-- FIX: Set payment method on pending renewal orders that were created
-- with NULL payment_method (these renewals were DOA — no gateway to charge)
UPDATE {prefix}wc_orders renewal
JOIN {prefix}wc_orders_meta rel
    ON rel.order_id = renewal.id
    AND rel.meta_key = '_subscription_renewal'
JOIN {prefix}wc_orders sub
    ON sub.id = CAST(rel.meta_value AS UNSIGNED)
    AND sub.type = 'shop_subscription'
    AND sub.payment_method != ''
    AND sub.payment_method IS NOT NULL
SET renewal.payment_method       = sub.payment_method,
    renewal.payment_method_title = sub.payment_method_title
WHERE renewal.status = 'wc-pending'
  AND (renewal.payment_method IS NULL OR renewal.payment_method = '');


-- =============================================================================
-- SECTION 7: VERIFY — Confirm the fix worked
-- =============================================================================
-- Re-run the quick check from Section 1. It should return 0.
-- =============================================================================

-- HPOS version:
SELECT COUNT(DISTINCT o.id) AS still_affected
FROM {prefix}wc_orders o
JOIN {prefix}wc_orders_meta mr
    ON o.id = mr.order_id
    AND mr.meta_key = '_requires_manual_renewal'
    AND mr.meta_value = 'true'
WHERE o.type = 'shop_subscription'
  AND o.status IN ('wc-active', 'wc-on-hold', 'wc-pending-cancel')
  AND o.payment_method != ''
  AND o.payment_method IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM {prefix}comments c
      WHERE c.comment_post_ID = o.id
        AND c.comment_type = 'order_note'
        AND c.comment_content LIKE '%turned off automatic renewals%'
  );