<?php
if (!defined('ABSPATH')) exit;

/*
 * CAS custom table owner.
 *
 * This module owns the CAS email verification ledger table. Direct $wpdb calls are
 * intentional here because WordPress has no native CRUD API for this custom table,
 * token state must be read/written immediately, and caching token verification
 * would risk stale authentication state.
 *
 * Table identifiers are generated internally from the WordPress table prefix via
 * custausu_db_email_verifications_table(); they are not user input. Dynamic IN()
 * placeholders are built from internal allowlisted purposes only.
 *
 * phpcs rules disabled in this file are therefore scoped to this custom-table
 * persistence layer and must not be copied to request/input/rendering code.
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Intentional CAS custom table CRUD.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Token/verification state must not be cached.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Dynamic identifiers/placeholders are internally generated and documented above.
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic IN() placeholder lists use internal allowlisted arrays.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table name is generated internally from the WP prefix, not user input.


/**
 * CAS DB repository — Email verifications
 */

if (!function_exists('custausu_db_email_verifications_table')) {
    function custausu_db_email_verifications_table(): string {
        return custausu_db_table('email_verifications');
    }
}

if (!function_exists('custausu_db_email_verifications_is_ready')) {
    function custausu_db_email_verifications_is_ready(): bool {
        static $cache = null;

        if ($cache !== null) {
            return (bool) $cache;
        }

        global $wpdb;

        $table = custausu_db_email_verifications_table();
        if ($table === '') {
            $cache = false;
            return false;
        }

        $found = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));
        $cache = (is_string($found) && $found === $table);

        return (bool) $cache;
    }
}

if (!function_exists('custausu_db_email_verifications_now_mysql')) {
    function custausu_db_email_verifications_now_mysql(): string {
        return gmdate('Y-m-d H:i:s');
    }
}

if (!function_exists('custausu_db_email_verifications_ts_to_mysql_utc')) {
    function custausu_db_email_verifications_ts_to_mysql_utc(int $ts): ?string {
        if ($ts <= 0) return null;
        return gmdate('Y-m-d H:i:s', $ts);
    }
}

if (!function_exists('custausu_db_email_verifications_sanitize_nullable_mysql_utc')) {
    function custausu_db_email_verifications_sanitize_nullable_mysql_utc($value): ?string {
        if (is_int($value) || is_float($value)) {
            $ts = (int) $value;
            return $ts > 0 ? gmdate('Y-m-d H:i:s', $ts) : null;
        }

        $value = is_string($value) ? trim($value) : '';

        if ($value === '') {
            return null;
        }

        if (ctype_digit($value)) {
            $ts = (int) $value;
            return $ts > 0 ? gmdate('Y-m-d H:i:s', $ts) : null;
        }

        $ts = strtotime($value . ' UTC');

        if (!is_int($ts) || $ts <= 0) {
            return null;
        }

        return gmdate('Y-m-d H:i:s', $ts);
    }
}

if (!function_exists('custausu_db_email_verifications_mysql_utc_ts')) {
    function custausu_db_email_verifications_mysql_utc_ts($value): int {
        $value = is_string($value) ? trim($value) : '';

        if ($value === '') {
            return 0;
        }

        $ts = strtotime($value . ' UTC');

        return is_int($ts) && $ts > 0 ? $ts : 0;
    }
}

if (!function_exists('custausu_db_email_verifications_hash_token')) {
    function custausu_db_email_verifications_hash_token(string $token): string {
        $token = trim((string)$token);
        return $token !== '' ? hash('sha256', $token) : '';
    }
}

if (!function_exists('custausu_db_email_verifications_json_encode')) {
    function custausu_db_email_verifications_json_encode($value): string {
        if (!is_array($value)) $value = [];
        return wp_json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) ?: '{}';
    }
}

if (!function_exists('custausu_db_email_verifications_json_decode_array')) {
    function custausu_db_email_verifications_json_decode_array($value): array {
        if (!is_string($value) || trim($value) === '') return [];
        $arr = json_decode($value, true);
        return is_array($arr) ? $arr : [];
    }
}

if (!function_exists('custausu_db_email_verifications_normalize_purpose')) {
    function custausu_db_email_verifications_normalize_purpose(string $purpose): string {
        $purpose = strtolower(trim((string)$purpose));
        $purpose = preg_replace('/[^a-z0-9_\-]/', '', $purpose);

        if ($purpose === '') {
            return 'register_verify';
        }

        if (strlen($purpose) > 50) {
            $purpose = substr($purpose, 0, 50);
        }

        return $purpose;
    }
}

if (!function_exists('custausu_db_email_verifications_normalize_email')) {
    function custausu_db_email_verifications_normalize_email($email): string {
        $email = sanitize_email((string) $email);
        $email = strtolower(trim($email));

        return is_email($email) ? $email : '';
    }
}

if (!function_exists('custausu_db_email_verifications_email_hash')) {
    function custausu_db_email_verifications_email_hash(string $email): string {
        $email = custausu_db_email_verifications_normalize_email($email);
        return $email !== '' ? hash('sha256', $email) : '';
    }
}

if (!function_exists('custausu_db_email_verifications_verified_purposes')) {
    function custausu_db_email_verifications_verified_purposes(): array {
        return [
            'register_verify',
            'existing_user_baseline',
            'legacy_existing_user',
            'wp_email_change',
        ];
    }
}

if (!function_exists('custausu_db_email_verifications_is_user_email_verified')) {
    function custausu_db_email_verifications_is_user_email_verified(int $user_id, string $email = ''): bool {
        global $wpdb;

        if ($user_id <= 0) return false;
        if (!custausu_db_email_verifications_is_ready()) return false;

        if ($email === '') {
            $user = get_userdata($user_id);
            if (!$user) return false;
            $email = (string) $user->user_email;
        }

        $email = custausu_db_email_verifications_normalize_email($email);
        if ($email === '') return false;

        $table = custausu_db_email_verifications_table();

        $purposes = custausu_db_email_verifications_verified_purposes();
        $placeholders = implode(',', array_fill(0, count($purposes), '%s'));

        $sql = $wpdb->prepare(
            "SELECT id
             FROM {$table}
             WHERE user_id = %d
               AND email = %s
               AND status = %s
               AND purpose IN ({$placeholders})
             ORDER BY used_at DESC, id DESC
             LIMIT 1",
            array_merge(
                [
                    $user_id,
                    $email,
                    'used',
                ],
                $purposes
            )
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is prepared above with $wpdb->prepare() and dynamic IN placeholders.
        $found = $wpdb->get_var($sql);

        return !empty($found);
    }
}

if (!function_exists('custausu_db_email_verifications_mark_user_email_verified')) {
    function custausu_db_email_verifications_mark_user_email_verified(
        int $user_id,
        string $email = '',
        string $purpose = 'existing_user_baseline',
        array $meta = []
    ): bool {
        global $wpdb;

        if ($user_id <= 0) return false;
        if (!custausu_db_email_verifications_is_ready()) return false;

        if ($email === '') {
            $user = get_userdata($user_id);
            if (!$user) return false;
            $email = (string) $user->user_email;
        }

        $email = custausu_db_email_verifications_normalize_email($email);
        if ($email === '') return false;

        $purpose = custausu_db_email_verifications_normalize_purpose($purpose);

        if (custausu_db_email_verifications_is_user_email_verified($user_id, $email)) {
            /*
             * If this call comes from a WordPress email change and the new email
             * is already marked as verified, still revoke older verified rows for
             * previous emails. This keeps historical rows clean even on repeated calls.
             */
            if (
                $purpose === 'wp_email_change' &&
                function_exists('custausu_db_email_verifications_revoke_previous_verified_for_email')
            ) {
                custausu_db_email_verifications_revoke_previous_verified_for_email(
                    (int) $user_id,
                    (string) $email,
                    'wp_email_change'
                );
            }

            return true;
        }

        $table = custausu_db_email_verifications_table();
        $now   = custausu_db_email_verifications_now_mysql();

        /*
         * Synthetic non-login token hash.
         * This row represents a verified baseline state, not a clickable token.
         */
        $synthetic_token = 'verified|' . $purpose . '|' . $user_id . '|' . $email . '|' . wp_salt('custausu_email_verified');
        $hash = custausu_db_email_verifications_hash_token($synthetic_token);

        if ($hash === '') return false;

        $meta = array_merge([
            'source'     => $purpose,
            'email_hash' => custausu_db_email_verifications_email_hash($email),
            'created_by' => 'cas_backfill',
        ], $meta);

        /*
         * Re-activation path:
         * If this exact user/email already exists as a synthetic verified row
         * but was revoked because the user switched emails, reactivate it instead
         * of trying to insert a duplicate synthetic token row.
         */
        $purposes = custausu_db_email_verifications_verified_purposes();
        $purpose_placeholders = implode(',', array_fill(0, count($purposes), '%s'));

        $existing = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, purpose, meta_json
                 FROM {$table}
                 WHERE user_id = %d
                   AND email = %s
                   AND purpose IN ({$purpose_placeholders})
                   AND status IN (%s, %s)
                 ORDER BY id DESC
                 LIMIT 1",
                array_merge(
                    [
                        $user_id,
                        $email,
                    ],
                    $purposes,
                    [
                        'used',
                        'revoked',
                    ]
                )
            ),
            ARRAY_A
        );

        if (is_array($existing) && !empty($existing['id'])) {
            $old_meta = custausu_db_email_verifications_json_decode_array($existing['meta_json'] ?? '');

            $reactivated_meta = array_merge($old_meta, $meta, [
                'reactivated_at' => $now,
                'reactivated_by' => $purpose,
            ]);

            $reactivated = $wpdb->update(
                $table,
                [
                    'status'    => 'used',
                    'used_at'   => $now,
                    'meta_json' => custausu_db_email_verifications_json_encode($reactivated_meta),
                ],
                [
                    'id' => (int) $existing['id'],
                ],
                ['%s', '%s', '%s'],
                ['%d']
            );

            if ($reactivated !== false) {
                if (
                    $purpose === 'wp_email_change' &&
                    function_exists('custausu_db_email_verifications_revoke_previous_verified_for_email')
                ) {
                    custausu_db_email_verifications_revoke_previous_verified_for_email(
                        (int) $user_id,
                        (string) $email,
                        'wp_email_change'
                    );
                }

                if (function_exists('custausu_log')) {
                    custausu_log('email', 'CAS email verification row reactivated', [
                        'user_id' => (int) $user_id,
                        'email_hash' => custausu_db_email_verifications_email_hash($email),
                        'row_id' => (int) $existing['id'],
                        'purpose' => (string) ($existing['purpose'] ?? ''),
                        'reactivated_by' => (string) $purpose,
                    ], 'info');
                }

                return true;
            }
        }

        $inserted = $wpdb->insert(
            $table,
            [
                'user_id'      => $user_id,
                'email'        => $email,
                'purpose'      => $purpose,
                'token_hash'   => $hash,
                'status'       => 'used',
                'sent_count'   => 0,
                'created_at'   => $now,
                'expires_at'   => null,
                'used_at'      => $now,
                'last_sent_at' => null,
                'meta_json'    => custausu_db_email_verifications_json_encode($meta),
            ],
            ['%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s']
        );

        if ($inserted === false && function_exists('custausu_log')) {
            custausu_log('email', 'CAS email verification baseline insert failed', [
                'user_id'  => $user_id,
                'email'    => $email,
                'purpose'  => $purpose,
                'db_error' => (string) $wpdb->last_error,
            ], 'error');
        }

        if (
            $inserted !== false &&
            $purpose === 'wp_email_change' &&
            function_exists('custausu_db_email_verifications_revoke_previous_verified_for_email')
        ) {
            custausu_db_email_verifications_revoke_previous_verified_for_email(
                (int) $user_id,
                (string) $email,
                'wp_email_change'
            );
        }

        return $inserted !== false;
    }
}

if (!function_exists('custausu_db_email_verifications_backfill_existing_users_option')) {
    function custausu_db_email_verifications_backfill_existing_users_option(): string {
        return 'custausu_email_verifications_existing_users_backfill';
    }
}

if (!function_exists('custausu_db_email_verifications_backfill_existing_users_state')) {
    function custausu_db_email_verifications_backfill_existing_users_state(): array {
        $raw = get_option(custausu_db_email_verifications_backfill_existing_users_option(), []);
        $raw = is_array($raw) ? $raw : [];

        return [
            'done'       => !empty($raw['done']) ? 1 : 0,
            'last_id'    => max(0, (int)($raw['last_id'] ?? 0)),
            'processed'  => max(0, (int)($raw['processed'] ?? 0)),
            'inserted'   => max(0, (int)($raw['inserted'] ?? 0)),
            'skipped'    => max(0, (int)($raw['skipped'] ?? 0)),
            'started_at' => is_string($raw['started_at'] ?? null) ? trim((string)$raw['started_at']) : '',
            'updated_at' => is_string($raw['updated_at'] ?? null) ? trim((string)$raw['updated_at']) : '',
        ];
    }
}

if (!function_exists('custausu_db_email_verifications_update_backfill_existing_users_state')) {
    function custausu_db_email_verifications_update_backfill_existing_users_state(array $state): bool {
        $now = custausu_db_email_verifications_now_mysql();

        $out = [
            'done'       => !empty($state['done']) ? 1 : 0,
            'last_id'    => max(0, (int)($state['last_id'] ?? 0)),
            'processed'  => max(0, (int)($state['processed'] ?? 0)),
            'inserted'   => max(0, (int)($state['inserted'] ?? 0)),
            'skipped'    => max(0, (int)($state['skipped'] ?? 0)),
            'started_at' => is_string($state['started_at'] ?? null) && trim((string)$state['started_at']) !== ''
                ? trim((string)$state['started_at'])
                : $now,
            'updated_at' => $now,
        ];

        return (bool) update_option(custausu_db_email_verifications_backfill_existing_users_option(), $out, false);
    }
}

if (!function_exists('custausu_db_email_verifications_backfill_existing_users_batch')) {
    function custausu_db_email_verifications_backfill_existing_users_batch(int $limit = 50): array {
        $limit = max(1, min(500, $limit));

        $state = custausu_db_email_verifications_backfill_existing_users_state();

        if (!empty($state['done'])) {
            return $state;
        }

        if (!custausu_db_email_verifications_is_ready()) {
            return $state;
        }

        /*
         * WP_User_Query has no native "ID > last_id" arg, so use a lean custom query.
         */
        global $wpdb;

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT ID, user_email
                 FROM {$wpdb->users}
                 WHERE ID > %d
                 ORDER BY ID ASC
                 LIMIT %d",
                (int)$state['last_id'],
                $limit
            ),
            ARRAY_A
        );

        if (empty($rows)) {
            $state['done'] = 1;
            custausu_db_email_verifications_update_backfill_existing_users_state($state);

            if (function_exists('custausu_log')) {
                custausu_log('email', 'CAS existing users email verification backfill completed', $state, 'info');
            }

            return custausu_db_email_verifications_backfill_existing_users_state();
        }

        foreach ($rows as $row) {
            $user_id = (int)($row['ID'] ?? 0);
            $email   = custausu_db_email_verifications_normalize_email($row['user_email'] ?? '');

            if ($user_id > $state['last_id']) {
                $state['last_id'] = $user_id;
            }

            $state['processed']++;

            if ($user_id <= 0 || $email === '') {
                $state['skipped']++;
                continue;
            }

            if (custausu_db_email_verifications_is_user_email_verified($user_id, $email)) {
                $state['skipped']++;
                continue;
            }

            $ok = custausu_db_email_verifications_mark_user_email_verified(
                $user_id,
                $email,
                'existing_user_baseline',
                [
                    'backfill_reason' => 'user_existed_before_cas_email_sql_baseline',
                ]
            );

            if ($ok) {
                $state['inserted']++;
            } else {
                $state['skipped']++;
            }
        }

        custausu_db_email_verifications_update_backfill_existing_users_state($state);

        return custausu_db_email_verifications_backfill_existing_users_state();
    }
}

if (!function_exists('custausu_db_email_verifications_maybe_backfill_existing_users')) {
    function custausu_db_email_verifications_maybe_backfill_existing_users(): void {
        if (!is_admin()) return;
        if (!current_user_can('manage_options')) return;
        if (wp_doing_ajax() || wp_doing_cron()) return;

        $state = custausu_db_email_verifications_backfill_existing_users_state();
        if (!empty($state['done'])) return;

        custausu_db_email_verifications_backfill_existing_users_batch(50);
    }
}

if (!function_exists('custausu_db_email_verifications_create_or_replace_pending')) {
    function custausu_db_email_verifications_create_or_replace_pending(
        int $user_id,
        string $email,
        string $token,
        ?string $expires_at = null,
        string $purpose = 'register_verify',
        array $meta = []
    ): bool {
        global $wpdb;

        if ($user_id <= 0) return false;

        $email = custausu_db_email_verifications_normalize_email($email);
        $token = trim((string)$token);
        $purpose = custausu_db_email_verifications_normalize_purpose($purpose);
        $expires_at = custausu_db_email_verifications_sanitize_nullable_mysql_utc($expires_at);

        if ($email === '' || $token === '') return false;
        if (!custausu_db_email_verifications_is_ready()) return false;

        $table = custausu_db_email_verifications_table();
        $now   = custausu_db_email_verifications_now_mysql();
        $hash  = custausu_db_email_verifications_hash_token($token);

        if ($hash === '') return false;

        $max_sent = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(MAX(sent_count), 0) FROM {$table} WHERE user_id = %d AND purpose = %s",
                $user_id,
                $purpose
            )
        );
        $sent_count = max(1, $max_sent + 1);

        $wpdb->update(
            $table,
            [
                'status' => 'revoked',
            ],
            [
                'user_id' => $user_id,
                'purpose' => $purpose,
                'status'  => 'pending',
            ],
            ['%s'],
            ['%d', '%s', '%s']
        );

        $inserted = $wpdb->insert(
            $table,
            [
                'user_id'      => $user_id,
                'email'        => $email,
                'purpose'      => $purpose,
                'token_hash'   => $hash,
                'status'       => 'pending',
                'sent_count'   => $sent_count,
                'created_at'   => $now,
                'expires_at'   => $expires_at,
                'used_at'      => null,
                'last_sent_at' => $now,
                'meta_json'    => custausu_db_email_verifications_json_encode($meta),
            ],
            ['%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s']
        );

        return $inserted !== false;
    }
}

if (!function_exists('custausu_db_email_verifications_get_latest_pending')) {
    function custausu_db_email_verifications_get_latest_pending(
        int $user_id,
        string $purpose = 'register_verify'
    ): ?array {
        global $wpdb;

        if ($user_id <= 0) return null;
        if (!custausu_db_email_verifications_is_ready()) return null;

        $purpose = custausu_db_email_verifications_normalize_purpose($purpose);
        $table   = custausu_db_email_verifications_table();

        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$table}
                 WHERE user_id = %d
                   AND purpose = %s
                   AND status = %s
                 ORDER BY id DESC
                 LIMIT 1",
                $user_id,
                $purpose,
                'pending'
            ),
            ARRAY_A
        );

        return is_array($row) ? $row : null;
    }
}

if (!function_exists('custausu_db_email_verifications_mark_expired')) {
    function custausu_db_email_verifications_mark_expired(int $row_id): bool {
        global $wpdb;

        if ($row_id <= 0) return false;
        if (!custausu_db_email_verifications_is_ready()) return false;

        $table = custausu_db_email_verifications_table();

        $res = $wpdb->update(
            $table,
            ['status' => 'expired'],
            ['id' => $row_id],
            ['%s'],
            ['%d']
        );

        return $res !== false;
    }
}

if (!function_exists('custausu_db_email_verifications_mark_used')) {
    function custausu_db_email_verifications_mark_used(int $row_id): bool {
        global $wpdb;

        if ($row_id <= 0) return false;
        if (!custausu_db_email_verifications_is_ready()) return false;

        $table = custausu_db_email_verifications_table();
        $now   = custausu_db_email_verifications_now_mysql();

        $res = $wpdb->update(
            $table,
            [
                'status'  => 'used',
                'used_at' => $now,
            ],
            ['id' => $row_id],
            ['%s', '%s'],
            ['%d']
        );

        return $res !== false;
    }
}

if (!function_exists('custausu_db_email_verifications_delete_user_rows')) {
    function custausu_db_email_verifications_delete_user_rows(int $user_id): int {
        global $wpdb;

        if ($user_id <= 0) return 0;
        if (!custausu_db_email_verifications_is_ready()) return 0;

        $table = custausu_db_email_verifications_table();

        $count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE user_id = %d",
                $user_id
            )
        );

        if ($count <= 0) return 0;

        $deleted = $wpdb->delete(
            $table,
            ['user_id' => $user_id],
            ['%d']
        );

        if ($deleted === false) {
            if (function_exists('custausu_log')) {
                custausu_log('email', 'CAS email verification cleanup on user delete failed', [
                    'user_id' => (int) $user_id,
                ], 'error');
            }
            return 0;
        }

        if (function_exists('custausu_log')) {
            custausu_log('email', 'CAS email verification rows deleted on user delete', [
                'user_id' => (int) $user_id,
                'rows'    => (int) $count,
            ], 'info');
        }

        return $count;
    }
}

if (!function_exists('custausu_db_email_verifications_verify_token')) {
    function custausu_db_email_verifications_verify_token(
        int $user_id,
        string $token,
        string $purpose = 'register_verify'
    ): array {
        $token   = trim((string)$token);
        $purpose = custausu_db_email_verifications_normalize_purpose($purpose);

        if ($user_id <= 0 || $token === '') {
            return ['ok' => false, 'reason' => 'invalid', 'row' => null];
        }

        if (!custausu_db_email_verifications_is_ready()) {
            return ['ok' => false, 'reason' => 'not_ready', 'row' => null];
        }

        $row = custausu_db_email_verifications_get_latest_pending($user_id, $purpose);
        if (!$row || empty($row['id'])) {
            return ['ok' => false, 'reason' => 'invalid', 'row' => null];
        }

        $expires_at = trim((string)($row['expires_at'] ?? ''));
        $expires_ts = custausu_db_email_verifications_mysql_utc_ts($expires_at);

        if ($expires_ts > 0 && time() > $expires_ts) {
            custausu_db_email_verifications_mark_expired((int)$row['id']);
            return ['ok' => false, 'reason' => 'expired', 'row' => $row];
        }

        $hash = custausu_db_email_verifications_hash_token($token);
        $stored_hash = (string)($row['token_hash'] ?? '');

        if ($hash === '' || $stored_hash === '' || !hash_equals($stored_hash, $hash)) {
            return ['ok' => false, 'reason' => 'invalid', 'row' => $row];
        }

        $ok = custausu_db_email_verifications_mark_used((int)$row['id']);
        if (!$ok) {
            return ['ok' => false, 'reason' => 'db_write_failed', 'row' => $row];
        }

        return ['ok' => true, 'reason' => 'ok', 'row' => $row];
    }
}

add_action('admin_init', function () {
    if (function_exists('custausu_db_email_verifications_maybe_backfill_existing_users')) {
        custausu_db_email_verifications_maybe_backfill_existing_users();
    }
}, 20);

if (!function_exists('custausu_db_email_verifications_revoke_previous_verified_for_email')) {
    function custausu_db_email_verifications_revoke_previous_verified_for_email(
        int $user_id,
        string $new_email,
        string $source = 'wp_email_change'
    ): int {
        global $wpdb;

        if ($user_id <= 0) return 0;
        if (!custausu_db_email_verifications_is_ready()) return 0;

        $new_email = custausu_db_email_verifications_normalize_email($new_email);
        if ($new_email === '') return 0;

        $table = custausu_db_email_verifications_table();
        $now   = custausu_db_email_verifications_now_mysql();

        /*
        * Email identity hash used to compare historical verified rows
        * without relying only on the raw email string.
        */
        $new_hash = custausu_db_email_verifications_email_hash($new_email);

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, email, purpose, token_hash, status, meta_json
                 FROM {$table}
                 WHERE user_id = %d
                   AND status = %s
                 ORDER BY id ASC",
                $user_id,
                'used'
            ),
            ARRAY_A
        );

        if (!is_array($rows) || empty($rows)) {
            return 0;
        }

        $changed = 0;

        foreach ($rows as $row) {
            $row_id = (int)($row['id'] ?? 0);
            if ($row_id <= 0) continue;

            $row_email = sanitize_email((string)($row['email'] ?? ''));

            $meta = custausu_db_email_verifications_json_decode_array($row['meta_json'] ?? '');
            $old_hash = '';

            if (!empty($meta['email_hash']) && is_string($meta['email_hash'])) {
                $old_hash = strtolower(trim((string)$meta['email_hash']));
            } elseif (!empty($row['token_hash']) && is_string($row['token_hash'])) {
                $old_hash = strtolower(trim((string)$row['token_hash']));
            } elseif ($row_email !== '') {
                $old_hash = hash('sha256', strtolower(trim($row_email)));
            }

            /*
             * Keep the current/new verified row valid.
             * Revoke only previous rows that clearly refer to another email.
             */
            $same_email = ($row_email !== '' && strtolower($row_email) === strtolower($new_email));
            $same_hash  = ($old_hash !== '' && hash_equals($old_hash, $new_hash));

            if ($same_email || $same_hash) {
                continue;
            }

            $meta['revoked_by'] = $source;
            $meta['revoked_at'] = $now;
            $meta['superseded_by_email_hash'] = $new_hash;
            $meta['superseded_reason'] = 'user_email_changed';

            $res = $wpdb->update(
            $table,
            [
                'status'    => 'revoked',
                'meta_json' => custausu_db_email_verifications_json_encode($meta),
            ],
            [
                'id' => $row_id,
            ],
            ['%s', '%s'],
            ['%d']
        );

            if ($res !== false) {
                $changed++;
            }
        }

        if ($changed > 0 && function_exists('custausu_log')) {
            custausu_log('email', 'previous verified email rows revoked after email change', [
                'user_id' => (int) $user_id,
                'new_email_hash' => $new_hash,
                'rows' => (int) $changed,
                'source' => (string) $source,
            ], 'info');
        }

        return $changed;
    }
}

add_action('deleted_user', function ($user_id) {
    $user_id = (int) $user_id;
    if ($user_id <= 0) return;

    if (function_exists('custausu_db_email_verifications_delete_user_rows')) {
        custausu_db_email_verifications_delete_user_rows($user_id);
    }
}, 10, 1);