<?php

namespace WPVOID\StockBeacon\Core;

if ( !defined( 'ABSPATH' ) ) {
    exit;
}
class WaitlistManager {
    private string $table;

    private string $log_table;

    public function __construct() {
        global $wpdb;
        $this->table = $wpdb->prefix . 'stockbeacon_subscriptions';
        $this->log_table = $wpdb->prefix . 'stockbeacon_logs';
    }

    public function add( array $data ) : int {
        global $wpdb;
        $email = SignupValidator::normalize_email( (string) ($data['email'] ?? '') );
        if ( !is_email( $email ) ) {
            return 0;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Insert into the plugin-owned subscriptions table.
        $inserted = $wpdb->insert( $this->table, array(
            'product_id'   => absint( $data['product_id'] ),
            'variation_id' => absint( $data['variation_id'] ?? 0 ),
            'email'        => $email,
            'user_id'      => absint( $data['user_id'] ?? 0 ),
            'status'       => sanitize_text_field( $data['status'] ?? 'active' ),
            'opt_in_token' => $data['opt_in_token'] ?? null,
            'ip_address'   => sanitize_text_field( $data['ip_address'] ?? '' ),
            'locale'       => sanitize_text_field( $data['locale'] ?? get_locale() ),
            'consent'      => ( empty( $data['consent'] ) ? 0 : 1 ),
        ), array(
            '%d',
            '%d',
            '%s',
            '%d',
            '%s',
            '%s',
            '%s',
            '%s',
            '%d'
        ) );
        if ( !$inserted ) {
            return 0;
        }
        $id = (int) $wpdb->insert_id;
        $this->log_event( $id, 'subscribed' );
        $this->bust_count_cache( (int) ($data['product_id'] ?? 0) );
        do_action( 'stockbeacon_waitlist_subscribed', $id, array(
            'email'        => $email,
            'product_id'   => absint( $data['product_id'] ),
            'variation_id' => absint( $data['variation_id'] ?? 0 ),
            'status'       => sanitize_text_field( $data['status'] ?? 'active' ),
        ) );
        return $id;
    }

    /**
     * Insert a new subscription, or reactivate an existing row for the same email / product / variation.
     *
     * Unsubscribe (and similar) keeps the row with status `unsubscribed` so the unique key still applies;
     * a plain INSERT would fail with a duplicate key. This path updates the existing row instead.
     *
     * @param array $data Same shape as {@see add()}.
     * @return int Subscription id, or 0 on failure. Returns 0 if a row is already active or pending (caller should treat as duplicate).
     */
    public function add_or_resubscribe( array $data ) : int {
        global $wpdb;
        $email = SignupValidator::normalize_email( (string) ($data['email'] ?? '') );
        $product_id = absint( $data['product_id'] ?? 0 );
        $variation_id = absint( $data['variation_id'] ?? 0 );
        if ( !is_email( $email ) || !$product_id ) {
            return 0;
        }
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare(
            'SELECT id, status FROM ' . $this->table . ' WHERE email=%s AND product_id=%d AND variation_id=%d LIMIT 1',
            $email,
            $product_id,
            $variation_id
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        $existing = $wpdb->get_row( $sql );
        if ( !$existing ) {
            $id = $this->add( $data );
            if ( $id > 0 ) {
                return $id;
            }
            // INSERT can fail with duplicate key if another request created the row between SELECT and INSERT.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
            $existing = $wpdb->get_row( $sql );
            if ( !$existing ) {
                return 0;
            }
            if ( in_array( (string) $existing->status, array('active', 'pending'), true ) ) {
                return 0;
            }
        }
        if ( in_array( (string) $existing->status, array('active', 'pending'), true ) ) {
            return 0;
        }
        $new_status = sanitize_text_field( (string) ($data['status'] ?? 'active') );
        $opt_in_token = $data['opt_in_token'] ?? null;
        $now_mysql = current_time( 'mysql' );
        $fields = array(
            'status'                 => $new_status,
            'user_id'                => absint( $data['user_id'] ?? 0 ),
            'ip_address'             => sanitize_text_field( (string) ($data['ip_address'] ?? '') ),
            'locale'                 => sanitize_text_field( (string) ($data['locale'] ?? get_locale()) ),
            'consent'                => ( empty( $data['consent'] ) ? 0 : 1 ),
            'opt_in_token'           => $opt_in_token,
            'unsubscribe_token_hash' => null,
            'opted_in_at'            => null,
            'notified_at'            => null,
            'purchased_at'           => null,
            'created_at'             => $now_mysql,
        );
        $formats = array(
            '%s',
            '%d',
            '%s',
            '%s',
            '%d',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s'
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update on the plugin-owned subscriptions table.
        $updated = $wpdb->update(
            $this->table,
            $fields,
            array(
                'id' => (int) $existing->id,
            ),
            $formats,
            array('%d')
        );
        if ( false === $updated ) {
            return 0;
        }
        // $updated === 0 means no column values changed; still treat as successful resubscribe.
        $prior_token = $this->build_unsubscribe_token( (int) $existing->id, $email );
        delete_transient( 'stockbeacon_unsubscribe_' . $prior_token );
        $this->log_event( (int) $existing->id, 'subscribed' );
        $this->bust_count_cache( $product_id );
        do_action( 'stockbeacon_waitlist_subscribed', (int) $existing->id, array(
            'email'        => $email,
            'product_id'   => $product_id,
            'variation_id' => $variation_id,
            'status'       => $new_status,
            'resubscribed' => true,
        ) );
        return (int) $existing->id;
    }

    public function exists_active( string $email, int $product_id, int $variation_id = 0 ) : bool {
        global $wpdb;
        $email = SignupValidator::normalize_email( $email );
        if ( !is_email( $email ) ) {
            return false;
        }
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare(
            'SELECT id FROM ' . $this->table . ' WHERE email=%s AND product_id=%d AND variation_id=%d AND status IN (\'active\',\'pending\') LIMIT 1',
            $email,
            $product_id,
            $variation_id
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        return (bool) $wpdb->get_var( $sql );
    }

    public function get_active(
        int $product_id,
        int $variation_id = 0,
        int $limit = 25,
        int $offset = 0
    ) : array {
        global $wpdb;
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare(
            'SELECT * FROM ' . $this->table . ' WHERE product_id=%d AND variation_id=%d AND status=\'active\' ORDER BY id ASC LIMIT %d OFFSET %d',
            $product_id,
            $variation_id,
            $limit,
            $offset
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        return (array) $wpdb->get_results( $sql );
    }

    /**
     * Fetch an immutable page of active subscribers using the primary key as a
     * cursor. Unlike OFFSET pagination, changing rows to "notified" while a
     * batch runs cannot shift later rows out of the result set.
     */
    public function get_active_after_id(
        int $product_id,
        int $variation_id = 0,
        int $limit = 25,
        int $after_id = 0
    ) : array {
        global $wpdb;
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare(
            'SELECT * FROM ' . $this->table . ' WHERE product_id=%d AND variation_id=%d AND status=\'active\' AND id>%d ORDER BY id ASC LIMIT %d',
            $product_id,
            $variation_id,
            max( 0, $after_id ),
            max( 1, $limit )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        return (array) $wpdb->get_results( $sql );
    }

    public function get_for_product( int $product_id, int $limit = 50 ) : array {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table . ' WHERE product_id=%d ORDER BY id DESC LIMIT %d', $product_id, $limit );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        return (array) $wpdb->get_results( $sql );
    }

    public function get_waitlist_product_summary( int $product_id, array $counts = array() ) : array {
        $product = ( $product_id > 0 ? wc_get_product( $product_id ) : null );
        $name = ( $product instanceof \WC_Product ? $product->get_name() : get_the_title( $product_id ) );
        $summary = array(
            'product_id'            => $product_id,
            'product_name'          => (string) (( $name ?: '' )),
            'product_url'           => ( $product_id > 0 ? ( get_permalink( $product_id ) ?: '' ) : '' ),
            'edit_url'              => ( $product_id > 0 ? ( get_edit_post_link( $product_id, 'raw' ) ?: '' ) : '' ),
            'active_count'          => (int) ($counts['active_count'] ?? 0),
            'pending_count'         => (int) ($counts['pending_count'] ?? 0),
            'notified_count'        => (int) ($counts['notified_count'] ?? 0),
            'purchased_count'       => (int) ($counts['purchased_count'] ?? 0),
            'total_count'           => (int) ($counts['total_count'] ?? 0),
            'product_stock_status'  => ( $product instanceof \WC_Product ? (string) $product->get_stock_status() : '' ),
            'waitlist_form_visible' => 'yes' !== get_post_meta( $product_id, '_stockbeacon_hide_waitlist', true ),
            'waitlist_hidden'       => 'yes' === get_post_meta( $product_id, '_stockbeacon_hide_waitlist', true ),
            'sku'                   => ( $product instanceof \WC_Product ? (string) $product->get_sku() : '' ),
            'stock_quantity'        => ( $product instanceof \WC_Product ? $product->get_stock_quantity() : null ),
            'manage_stock'          => ( $product instanceof \WC_Product ? (bool) $product->managing_stock() : false ),
        );
        return $summary;
    }

    public function get_out_of_stock_products( int $page = 1, int $per_page = 20, string $search = '' ) : array {
        global $wpdb;
        $offset = max( 0, ($page - 1) * $per_page );
        $per_page = max( 1, min( 100, $per_page ) );
        $base = 'SELECT p.ID
			FROM ' . $wpdb->posts . ' p
			INNER JOIN ' . $wpdb->postmeta . " stock ON stock.post_id = p.ID AND stock.meta_key = '_stock_status'\n\t\t\tWHERE p.post_type = 'product'\n\t\t\tAND p.post_status = 'publish'\n\t\t\tAND stock.meta_value = 'outofstock'";
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Fully prepared; $base only interpolates core table names.
        if ( '' !== $search ) {
            $sql = $wpdb->prepare(
                $base . ' AND p.post_title LIKE %s ORDER BY p.post_modified_gmt DESC LIMIT %d OFFSET %d',
                '%' . $wpdb->esc_like( $search ) . '%',
                $per_page,
                $offset
            );
        } else {
            $sql = $wpdb->prepare( $base . ' ORDER BY p.post_modified_gmt DESC LIMIT %d OFFSET %d', $per_page, $offset );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; uncached admin listing read.
        $product_ids = array_map( 'absint', (array) $wpdb->get_col( $sql ) );
        if ( array() === $product_ids ) {
            return array();
        }
        $table = esc_sql( $this->table );
        $placeholders = implode( ',', array_fill( 0, count( $product_ids ), '%d' ) );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared; the esc_sql()'d plugin table and generated %d placeholders matching the sanitized ID list are interpolated.
        $count_rows = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT product_id,
					SUM(CASE WHEN status=\'active\' THEN 1 ELSE 0 END) AS active_count,
					SUM(CASE WHEN status=\'pending\' THEN 1 ELSE 0 END) AS pending_count,
					SUM(CASE WHEN status=\'notified\' THEN 1 ELSE 0 END) AS notified_count,
					SUM(CASE WHEN status=\'purchased\' THEN 1 ELSE 0 END) AS purchased_count,
					COUNT(*) AS total_count
				FROM ' . $table . "\n\t\t\t\tWHERE status IN ('active','pending','notified','purchased')\n\t\t\t\tAND product_id IN ({$placeholders})\n\t\t\t\tGROUP BY product_id", ...$product_ids ), OBJECT_K );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return array_map( function ( int $product_id ) use($count_rows) : array {
            $row = ( isset( $count_rows[$product_id] ) ? (array) $count_rows[$product_id] : array() );
            return $this->get_waitlist_product_summary( $product_id, $row );
        }, $product_ids );
    }

    public function get_products_with_waitlists( int $page = 1, int $per_page = 20, string $search = '' ) : array {
        global $wpdb;
        $offset = max( 0, ($page - 1) * $per_page );
        // Include pending (double opt-in) so products with only unconfirmed signups still appear.
        $base = 'SELECT s.product_id, p.post_title AS product_name,
				SUM(CASE WHEN s.status=\'active\' THEN 1 ELSE 0 END) AS active_count,
				SUM(CASE WHEN s.status=\'pending\' THEN 1 ELSE 0 END) AS pending_count,
				SUM(CASE WHEN s.status=\'notified\' THEN 1 ELSE 0 END) AS notified_count,
				SUM(CASE WHEN s.status=\'purchased\' THEN 1 ELSE 0 END) AS purchased_count,
				COUNT(*) AS total_count
			FROM ' . $this->table . ' s
			INNER JOIN ' . $wpdb->posts . ' p ON p.ID = s.product_id
			WHERE s.status IN (\'active\',\'pending\',\'notified\',\'purchased\')';
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Fully prepared; $base only interpolates the plugin-owned table and core table names.
        if ( '' !== $search ) {
            $sql = $wpdb->prepare(
                $base . ' AND p.post_title LIKE %s
			GROUP BY s.product_id
			ORDER BY active_count DESC
			LIMIT %d OFFSET %d',
                '%' . $wpdb->esc_like( $search ) . '%',
                $per_page,
                $offset
            );
        } else {
            $sql = $wpdb->prepare( $base . '
			GROUP BY s.product_id
			ORDER BY active_count DESC
			LIMIT %d OFFSET %d', $per_page, $offset );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; uncached admin listing read.
        $rows = (array) $wpdb->get_results( $sql );
        return array_map( function ( $row ) : array {
            $product_id = (int) ($row->product_id ?? 0);
            return $this->get_waitlist_product_summary( $product_id, (array) $row );
        }, $rows );
    }

    /**
     * Global snapshot for the admin overview (accurate counts + cross-product recent signups).
     */
    public function get_overview_snapshot( int $recent_limit = 12, string $email_search = '' ) : array {
        global $wpdb;
        // Include pending so recently signed-up users are reflected immediately in admin metrics.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Constant aggregates on the plugin-owned table; only the trusted table name is interpolated, no user input.
        $total_active = (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . $this->table . " WHERE status IN ('active','pending')" );
        $total_notified = (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . $this->table . " WHERE status='notified'" );
        $products_count = (int) $wpdb->get_var( 'SELECT COUNT(DISTINCT product_id) FROM ' . $this->table . " WHERE status IN ('active','pending','notified','purchased')" );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $recent_limit = max( 1, min( 50, $recent_limit ) );
        $email_search = sanitize_text_field( $email_search );
        $recent_base = 'SELECT s.id, s.email, s.created_at, s.notified_at, s.purchased_at, s.product_id, s.variation_id, s.user_id, s.status,
			CASE WHEN p.post_type = \'product_variation\' AND parent.post_title IS NOT NULL THEN parent.post_title ELSE p.post_title END AS product_name,
			CASE WHEN p.post_type = \'product_variation\' AND parent.ID IS NOT NULL THEN parent.ID ELSE s.product_id END AS product_link_id
			FROM ' . $this->table . ' s
			INNER JOIN ' . $wpdb->posts . ' p ON p.ID = s.product_id
			LEFT JOIN ' . $wpdb->posts . ' parent ON parent.ID = p.post_parent AND p.post_type = \'product_variation\'
			WHERE p.post_type IN (\'product\',\'product_variation\')
			AND s.status IN (\'active\',\'pending\',\'notified\',\'purchased\')';
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Fully prepared; $recent_base only interpolates the plugin-owned table and core table names.
        if ( '' !== $email_search ) {
            $sql = $wpdb->prepare( $recent_base . ' AND s.email LIKE %s
			ORDER BY s.id DESC
			LIMIT %d', '%' . $wpdb->esc_like( $email_search ) . '%', $recent_limit );
        } else {
            $sql = $wpdb->prepare( $recent_base . '
			ORDER BY s.id DESC
			LIMIT %d', $recent_limit );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; uncached admin overview read.
        $recent = (array) $wpdb->get_results( $sql );
        $today_start = gmdate( 'Y-m-d' ) . ' 00:00:00';
        $week_start = gmdate( 'Y-m-d H:i:s', strtotime( '-7 days' ) );
        $month_start = gmdate( 'Y-m-d H:i:s', strtotime( '-30 days' ) );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared aggregates; only the plugin-owned table name is interpolated.
        $subscribers_today = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . $this->table . " WHERE status IN ('active','pending') AND created_at >= %s", $today_start ) );
        $subscribers_7d = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . $this->table . " WHERE status IN ('active','pending') AND created_at >= %s", $week_start ) );
        $subscribers_1m = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . $this->table . " WHERE status IN ('active','pending') AND created_at >= %s", $month_start ) );
        $total_purchased = (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . $this->table . " WHERE status='purchased'" );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $notified_plus = $total_notified + $total_purchased;
        $conversion_rate = ( $notified_plus > 0 ? round( $total_purchased / $notified_plus * 100, 1 ) : 0.0 );
        return array(
            'total_active'      => $total_active,
            'total_notified'    => $total_notified,
            'products_count'    => $products_count,
            'subscribers_today' => $subscribers_today,
            'subscribers_7d'    => $subscribers_7d,
            'subscribers_1m'    => $subscribers_1m,
            'conversion_rate'   => $conversion_rate,
            'recent_signups'    => array_map( static function ( $row ) {
                $product_link_id = (int) ($row->product_link_id ?? $row->product_id);
                // Resolve WC customer edit URL via stored user_id or email lookup.
                $edit_user_url = '';
                $user_id = (int) ($row->user_id ?? 0);
                if ( !$user_id ) {
                    $found = email_exists( (string) $row->email );
                    $user_id = ( $found ? (int) $found : 0 );
                }
                if ( $user_id > 0 ) {
                    $edit_user_url = ( get_edit_user_link( $user_id ) ?: '' );
                }
                // Current stock status for the exact waitlisted line, including variations.
                $stock_product_id = (int) (( !empty( $row->variation_id ) ? $row->variation_id : $row->product_id ));
                $stock_product = ( $stock_product_id > 0 ? wc_get_product( $stock_product_id ) : null );
                $stock_status = ( $stock_product instanceof \WC_Product ? (string) $stock_product->get_stock_status() : '' );
                return array(
                    'id'                   => (int) $row->id,
                    'email'                => (string) $row->email,
                    'created_at'           => (string) $row->created_at,
                    'notified_at'          => ( isset( $row->notified_at ) ? (string) $row->notified_at : '' ),
                    'purchased_at'         => ( isset( $row->purchased_at ) ? (string) $row->purchased_at : '' ),
                    'product_id'           => (int) $row->product_id,
                    'variation_id'         => (int) ($row->variation_id ?? 0),
                    'product_name'         => (string) $row->product_name,
                    'product_url'          => ( get_permalink( $product_link_id ) ?: '' ),
                    'status'               => (string) $row->status,
                    'product_stock_status' => $stock_status,
                    'edit_user_url'        => $edit_user_url,
                );
            }, $recent ),
        );
    }

    public function get_recent_subscribers(
        int $limit = 50,
        string $email_search = '',
        string $status = '',
        string $date_from = '',
        string $date_to = ''
    ) : array {
        global $wpdb;
        $limit = max( 1, min( 100, absint( $limit ) ) );
        $email_search = sanitize_text_field( $email_search );
        $status = sanitize_key( $status );
        $date_from = sanitize_text_field( $date_from );
        $date_to = sanitize_text_field( $date_to );
        $table = esc_sql( $this->table );
        $posts_table = esc_sql( $wpdb->posts );
        $base_sql = 'SELECT s.id, s.email, s.created_at, s.notified_at, s.purchased_at, s.product_id, s.variation_id, s.user_id, s.status,
			CASE WHEN p.post_type = \'product_variation\' AND parent.post_title IS NOT NULL THEN parent.post_title ELSE p.post_title END AS product_name,
			CASE WHEN p.post_type = \'product_variation\' AND parent.ID IS NOT NULL THEN parent.ID ELSE s.product_id END AS product_link_id
			FROM ' . $table . ' s
			INNER JOIN ' . $posts_table . ' p ON p.ID = s.product_id
			LEFT JOIN ' . $posts_table . ' parent ON parent.ID = p.post_parent AND p.post_type = \'product_variation\'
			WHERE p.post_type IN (\'product\',\'product_variation\')
			AND s.status IN (\'active\',\'pending\',\'notified\',\'purchased\',\'unsubscribed\')';
        $params = array();
        if ( '' !== $email_search ) {
            $base_sql .= ' AND s.email LIKE %s';
            $params[] = '%' . $wpdb->esc_like( $email_search ) . '%';
        }
        if ( in_array( $status, array(
            'active',
            'pending',
            'notified',
            'purchased',
            'unsubscribed'
        ), true ) ) {
            $base_sql .= ' AND s.status = %s';
            $params[] = $status;
        }
        if ( preg_match( '/^\\d{4}-\\d{2}-\\d{2}$/', $date_from ) ) {
            $base_sql .= ' AND s.created_at >= %s';
            $params[] = $date_from . ' 00:00:00';
        }
        if ( preg_match( '/^\\d{4}-\\d{2}-\\d{2}$/', $date_to ) ) {
            $base_sql .= ' AND s.created_at <= %s';
            $params[] = $date_to . ' 23:59:59';
        }
        $params[] = $limit;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Fully prepared; $base_sql only interpolates esc_sql()'d plugin/core table names, values are passed via $params.
        $sql = $wpdb->prepare( $base_sql . ' ORDER BY s.id DESC LIMIT %d', $params );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; uncached admin subscriber listing read.
        $rows = (array) $wpdb->get_results( $sql );
        if ( array() === $rows ) {
            return array();
        }
        $user_email_map = array();
        $lookup_emails = array();
        $stock_ids = array();
        foreach ( $rows as $row ) {
            if ( empty( $row->user_id ) && !empty( $row->email ) ) {
                $lookup_emails[] = strtolower( (string) $row->email );
            }
            $stock_ids[] = (int) (( !empty( $row->variation_id ) ? $row->variation_id : $row->product_id ));
        }
        $lookup_emails = array_values( array_unique( array_filter( $lookup_emails ) ) );
        if ( array() !== $lookup_emails ) {
            $users_table = esc_sql( $wpdb->users );
            $placeholders = implode( ',', array_fill( 0, count( $lookup_emails ), '%s' ) );
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Fully prepared; %s placeholders generated to match the email list, only the esc_sql()'d core users table is interpolated.
            $user_rows = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT ID, user_email FROM ' . $users_table . ' WHERE user_email IN (' . $placeholders . ')', ...$lookup_emails ) );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
            foreach ( $user_rows as $user_row ) {
                $user_email_map[strtolower( (string) $user_row->user_email )] = (int) $user_row->ID;
            }
        }
        $stock_status_map = array();
        $stock_ids = array_values( array_unique( array_filter( array_map( 'absint', $stock_ids ) ) ) );
        if ( array() !== $stock_ids ) {
            $postmeta_table = esc_sql( $wpdb->postmeta );
            $placeholders = implode( ',', array_fill( 0, count( $stock_ids ), '%d' ) );
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Fully prepared; %d placeholders generated to match the sanitized ID list, only the esc_sql()'d core postmeta table is interpolated.
            $stock_rows = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT post_id, meta_value FROM ' . $postmeta_table . ' WHERE meta_key = %s AND post_id IN (' . $placeholders . ')', '_stock_status', ...$stock_ids ) );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
            foreach ( $stock_rows as $stock_row ) {
                $stock_status_map[(int) $stock_row->post_id] = (string) $stock_row->meta_value;
            }
        }
        return array_map( static function ( $row ) use($user_email_map, $stock_status_map) : array {
            $email = (string) $row->email;
            $product_link_id = (int) ($row->product_link_id ?? $row->product_id);
            $stock_id = (int) (( !empty( $row->variation_id ) ? $row->variation_id : $row->product_id ));
            $user_id = (int) ($row->user_id ?? 0);
            if ( !$user_id ) {
                $user_id = $user_email_map[strtolower( $email )] ?? 0;
            }
            return array(
                'id'                   => (int) $row->id,
                'email'                => $email,
                'created_at'           => (string) $row->created_at,
                'notified_at'          => ( isset( $row->notified_at ) ? (string) $row->notified_at : '' ),
                'purchased_at'         => ( isset( $row->purchased_at ) ? (string) $row->purchased_at : '' ),
                'product_id'           => (int) $row->product_id,
                'variation_id'         => (int) ($row->variation_id ?? 0),
                'product_name'         => (string) $row->product_name,
                'product_url'          => ( get_permalink( $product_link_id ) ?: '' ),
                'status'               => (string) $row->status,
                'product_stock_status' => $stock_status_map[$stock_id] ?? '',
                'edit_user_url'        => ( $user_id > 0 ? ( get_edit_user_link( $user_id ) ?: '' ) : '' ),
            );
        }, $rows );
    }

    public function get_subscribers( int $product_id, int $page = 1, int $per_page = 20 ) : array {
        global $wpdb;
        $offset = max( 0, ($page - 1) * $per_page );
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare(
            'SELECT * FROM ' . $this->table . ' WHERE product_id=%d ORDER BY id DESC LIMIT %d OFFSET %d',
            $product_id,
            $per_page,
            $offset
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        return (array) $wpdb->get_results( $sql );
    }

    public function get_activity_logs(
        int $page = 1,
        int $per_page = 50,
        string $search = '',
        string $type = '',
        string $delivery = ''
    ) : array {
        global $wpdb;
        $page = max( 1, absint( $page ) );
        $per_page = max( 1, min( 100, absint( $per_page ) ) );
        $offset = ($page - 1) * $per_page;
        $where = array();
        $params = array();
        $search = trim( $search );
        $type = sanitize_key( $type );
        $delivery = sanitize_key( $delivery );
        if ( '' !== $search ) {
            $like = '%' . $wpdb->esc_like( $search ) . '%';
            $where[] = '(s.email LIKE %s OR l.metadata_json LIKE %s OR l.event_type LIKE %s OR p.post_title LIKE %s OR parent.post_title LIKE %s)';
            array_push(
                $params,
                $like,
                $like,
                $like,
                $like,
                $like
            );
        }
        if ( 'email' === $type ) {
            $where[] = 'l.event_type LIKE %s';
            $params[] = 'email_%';
        } elseif ( 'subscription' === $type ) {
            $where[] = "l.event_type IN ('subscribed','confirmed','unsubscribed')";
        } elseif ( 'restock' === $type ) {
            $where[] = "(l.event_type IN ('notified','email_restock_sent','email_restock_failed'))";
        } elseif ( 'purchase' === $type ) {
            $where[] = "l.event_type = 'purchased'";
        } elseif ( 'test' === $type ) {
            $where[] = "l.event_type IN ('email_test_sent','email_test_failed')";
        }
        if ( 'delivered' === $delivery ) {
            $where[] = "l.event_type IN ('email_restock_sent','email_confirm_sent','email_test_sent')";
        } elseif ( 'failed' === $delivery ) {
            $where[] = "l.event_type IN ('email_restock_failed','email_confirm_fail','email_test_failed')";
        }
        $where_sql = ( empty( $where ) ? '' : ' WHERE ' . implode( ' AND ', $where ) );
        $params[] = $per_page;
        $params[] = $offset;
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Fully prepared; only plugin-owned/core table names and %s/%d placeholder fragments are interpolated, values are passed via $params matching the generated placeholders.
        $sql = $wpdb->prepare( 'SELECT l.id, l.subscription_id, l.event_type, l.metadata_json, l.created_at,
				s.email, s.product_id, s.variation_id, s.status,
				CASE WHEN p.post_type = \'product_variation\' AND parent.post_title IS NOT NULL THEN parent.post_title ELSE p.post_title END AS product_name,
				CASE WHEN p.post_type = \'product_variation\' AND parent.ID IS NOT NULL THEN parent.ID ELSE s.product_id END AS product_link_id
			FROM ' . $this->log_table . ' l
			LEFT JOIN ' . $this->table . ' s ON s.id = l.subscription_id
			LEFT JOIN ' . $wpdb->posts . ' p ON p.ID = s.product_id
			LEFT JOIN ' . $wpdb->posts . ' parent ON parent.ID = p.post_parent AND p.post_type = \'product_variation\'
			' . $where_sql . '
			ORDER BY l.id DESC
			LIMIT %d OFFSET %d', $params );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; uncached admin activity read.
        $rows = (array) $wpdb->get_results( $sql );
        return array_map( static function ( $row ) : array {
            $product_link_id = (int) ($row->product_link_id ?? $row->product_id ?? 0);
            return array(
                'id'              => (int) $row->id,
                'subscription_id' => (int) $row->subscription_id,
                'event_type'      => (string) $row->event_type,
                'metadata_json'   => (string) ($row->metadata_json ?? ''),
                'created_at'      => (string) $row->created_at,
                'email'           => (string) ($row->email ?? ''),
                'product_id'      => (int) ($row->product_id ?? 0),
                'variation_id'    => (int) ($row->variation_id ?? 0),
                'status'          => (string) ($row->status ?? ''),
                'product_name'    => (string) ($row->product_name ?? ''),
                'product_url'     => ( $product_link_id > 0 ? ( get_permalink( $product_link_id ) ?: '' ) : '' ),
            );
        }, $rows );
    }

    public function delete_subscription( int $id, bool $delete_logs = true ) : bool {
        global $wpdb;
        $sub = $this->get_by_id( $id );
        if ( !$sub ) {
            return false;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Delete from the plugin-owned subscriptions table.
        $deleted = $wpdb->delete( $this->table, array(
            'id' => $id,
        ), array('%d') );
        if ( $deleted ) {
            $this->bust_count_cache( (int) $sub->product_id );
            if ( $delete_logs ) {
                $this->delete_unsubscribe_transient( $sub );
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Delete from the plugin-owned logs table.
                $wpdb->delete( $this->log_table, array(
                    'subscription_id' => $id,
                ), array('%d') );
            } else {
                $this->redact_email_from_logs( $id );
                $this->redact_unsubscribe_transient( $sub );
            }
            return true;
        }
        return false;
    }

    public function clear_product( int $product_id ) : int {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared; only the plugin-owned table name is interpolated.
        $subscriptions = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT id, email FROM ' . $this->table . ' WHERE product_id=%d', $product_id ) );
        if ( empty( $subscriptions ) ) {
            return 0;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Delete from the plugin-owned subscriptions table.
        $count = (int) $wpdb->delete( $this->table, array(
            'product_id' => $product_id,
        ), array('%d') );
        foreach ( $subscriptions as $subscription ) {
            $this->delete_unsubscribe_transient( $subscription );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Delete from the plugin-owned logs table.
            $wpdb->delete( $this->log_table, array(
                'subscription_id' => (int) $subscription->id,
            ), array('%d') );
        }
        $this->bust_count_cache( $product_id );
        return $count;
    }

    public function update_status( int $id, string $status ) : void {
        global $wpdb;
        $sub = $this->get_by_id( $id );
        $fields = array(
            'status' => $status,
        );
        if ( 'notified' === $status ) {
            $fields['notified_at'] = current_time( 'mysql' );
            $fields['purchased_at'] = null;
        }
        if ( 'purchased' === $status ) {
            $fields['purchased_at'] = current_time( 'mysql' );
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update on the plugin-owned subscriptions table.
        $wpdb->update(
            $this->table,
            $fields,
            array(
                'id' => $id,
            ),
            null,
            array('%d')
        );
        $this->log_event( $id, $status );
        if ( $sub ) {
            $this->bust_count_cache( (int) $sub->product_id );
        }
    }

    /**
     * When a product (or variation) is out of stock again, turn prior restock / purchase outcomes back into the waiting pool.
     *
     * Rows with `notified` or `purchased` become `active` so the next in-stock cycle can email them again.
     *
     * @param int      $product_id   WooCommerce product ID (parent for variations).
     * @param int|null $variation_id `null` = all rows for this product regardless of variation. `0` = simple / aggregated waitlist rows only. `>0` = that variation only.
     * @return int Rows updated.
     */
    public function reset_notified_subscribers_to_active( int $product_id, ?int $variation_id ) : int {
        global $wpdb;
        $product_id = absint( $product_id );
        if ( !$product_id ) {
            return 0;
        }
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        if ( null === $variation_id ) {
            $sql = $wpdb->prepare( 'UPDATE ' . $this->table . " SET status='active', notified_at=NULL, purchased_at=NULL WHERE product_id=%d AND status IN ('notified','purchased')", $product_id );
        } else {
            $sql = $wpdb->prepare( 'UPDATE ' . $this->table . " SET status='active', notified_at=NULL, purchased_at=NULL WHERE product_id=%d AND variation_id=%d AND status IN ('notified','purchased')", $product_id, absint( $variation_id ) );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; bulk status reset on the plugin-owned table.
        $updated = $wpdb->query( $sql );
        if ( !is_numeric( $updated ) || (int) $updated <= 0 ) {
            return 0;
        }
        $this->bust_count_cache( $product_id );
        return (int) $updated;
    }

    /**
     * Distinct variation_id values that currently have at least one active (waiting) subscriber.
     *
     * @return int[] Sorted variation IDs (always includes each id once; may include 0).
     */
    public function get_distinct_variation_ids_with_active_waiters( int $product_id ) : array {
        global $wpdb;
        $product_id = absint( $product_id );
        if ( !$product_id ) {
            return array();
        }
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared; only the plugin-owned table name is interpolated.
        $col = $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT variation_id FROM ' . $this->table . " WHERE product_id=%d AND status='active' ORDER BY variation_id ASC", $product_id ) );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return array_values( array_unique( array_map( 'intval', (array) $col ) ) );
    }

    /**
     * Clears debounce / lock transients so a new out-of-stock → in-stock cycle can schedule restock mail again.
     */
    public function clear_restock_send_locks( int $product_id ) : void {
        $product_id = absint( $product_id );
        if ( !$product_id ) {
            return;
        }
        delete_transient( 'stockbeacon_restock_sent_' . $product_id );
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared; only the plugin-owned table name is interpolated.
        $ids = $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT variation_id FROM ' . $this->table . ' WHERE product_id=%d', $product_id ) );
        $all = array_unique( array_merge( array(0), array_map( 'intval', (array) $ids ) ) );
        foreach ( $all as $vid ) {
            delete_transient( 'stockbeacon_restock_sent_' . $product_id . '_' . $vid );
            delete_transient( 'stockbeacon_restock_lock_' . $product_id . '_' . $vid );
        }
    }

    public function count_active( int $product_id, int $variation_id = 0 ) : int {
        return $this->count_by_statuses( $product_id, $variation_id, array('active') );
    }

    /**
     * Count records used for storefront social proof.
     *
     * Includes pending signups so double opt-in stores still show demand
     * between signup and confirmation.
     */
    public function count_for_display( int $product_id, int $variation_id = 0 ) : int {
        return $this->count_by_statuses( $product_id, $variation_id, array('active', 'pending') );
    }

    /**
     * @param array<int, string> $statuses
     */
    private function count_by_statuses( int $product_id, int $variation_id, array $statuses ) : int {
        global $wpdb;
        $statuses = array_values( array_filter( array_map( 'sanitize_key', $statuses ), static function ( string $status ) : bool {
            return in_array( $status, array(
                'active',
                'pending',
                'notified',
                'purchased',
                'unsubscribed'
            ), true );
        } ) );
        if ( empty( $statuses ) ) {
            return 0;
        }
        $placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
        $params = array_merge( array($product_id, $variation_id), $statuses );
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Fully prepared; %s placeholders generated to match the allowlisted status values, only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare( 'SELECT COUNT(*) FROM ' . $this->table . " WHERE product_id=%d AND variation_id=%d AND status IN ({$placeholders})", ...$params );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; result is cached in post meta by the caller.
        return (int) $wpdb->get_var( $sql );
    }

    public function get_cached_count( int $product_id ) : int {
        $cached = get_post_meta( $product_id, '_stockbeacon_count_cache', true );
        if ( '' !== (string) $cached ) {
            return (int) $cached;
        }
        $count = $this->count_for_display( $product_id );
        update_post_meta( $product_id, '_stockbeacon_count_cache', $count );
        return $count;
    }

    public function bust_count_cache( int $product_id ) : void {
        delete_post_meta( $product_id, '_stockbeacon_count_cache' );
    }

    /**
     * Create an unsubscribe token and persist its one-way lookup digest before email is sent.
     */
    public function issue_unsubscribe_token( int $subscription_id, string $email ) : ?string {
        global $wpdb;
        $email = SignupValidator::normalize_email( $email );
        if ( $subscription_id <= 0 || !is_email( $email ) ) {
            return null;
        }
        $token = $this->build_unsubscribe_token( $subscription_id, $email );
        $digest = hash( 'sha256', $token );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update on the plugin-owned subscriptions table.
        $result = $wpdb->update(
            $this->table,
            array(
                'unsubscribe_token_hash' => $digest,
            ),
            array(
                'id'    => $subscription_id,
                'email' => $email,
            ),
            array('%s'),
            array('%d', '%s')
        );
        return ( false === $result ? null : $token );
    }

    public function unsubscribe_by_token( string $token ) : bool {
        global $wpdb;
        if ( !preg_match( '/\\A[0-9a-f]{64}\\z/D', $token ) ) {
            return false;
        }
        $digest = hash( 'sha256', $token );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared; only the plugin-owned table name is interpolated.
        $row = $wpdb->get_row( $wpdb->prepare( 'SELECT id, product_id, status, unsubscribe_token_hash FROM ' . $this->table . " WHERE unsubscribe_token_hash=%s AND status IN ('active','notified','purchased','unsubscribed') LIMIT 1", $digest ) );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ( $row && hash_equals( (string) $row->unsubscribe_token_hash, $digest ) ) {
            if ( 'unsubscribed' !== (string) $row->status ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update on the plugin-owned subscriptions table.
                $wpdb->update( $this->table, array(
                    'status' => 'unsubscribed',
                ), array(
                    'id' => (int) $row->id,
                ) );
                $this->log_event( (int) $row->id, 'unsubscribed', array(
                    'product_id' => (int) $row->product_id,
                    'source'     => 'email_unsubscribe_link',
                ) );
                $this->bust_count_cache( (int) $row->product_id );
            }
            return true;
        }
        $fallback_key = 'stockbeacon_unsubscribe_' . $token;
        $fallback = get_transient( $fallback_key );
        if ( is_array( $fallback ) ) {
            $fallback_subscription_id = absint( $fallback['subscription_id'] ?? 0 );
            if ( $fallback_subscription_id > 0 && $this->get_by_id( $fallback_subscription_id ) ) {
                // Fallback metadata is only valid after deliberate subscription deletion.
                delete_transient( $fallback_key );
                return false;
            }
            $changed = false;
            if ( empty( $fallback['logged'] ) ) {
                $this->log_event( $fallback_subscription_id, 'unsubscribed', array(
                    'product_id'   => absint( $fallback['product_id'] ?? 0 ),
                    'variation_id' => absint( $fallback['variation_id'] ?? 0 ),
                    'product_name' => sanitize_text_field( (string) ($fallback['product_name'] ?? '') ),
                    'product_url'  => esc_url_raw( (string) ($fallback['product_url'] ?? '') ),
                    'source'       => 'email_unsubscribe_link',
                ) );
                $fallback['logged'] = true;
                $changed = true;
            }
            if ( array_key_exists( 'email', $fallback ) ) {
                unset($fallback['email']);
                $changed = true;
            }
            if ( $changed ) {
                set_transient( $fallback_key, $fallback, 90 * DAY_IN_SECONDS );
            }
            return true;
        }
        return false;
    }

    public function get_by_id( int $id ) : ?object {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table . ' WHERE id=%d LIMIT 1', $id );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        $row = $wpdb->get_row( $sql );
        return ( $row ?: null );
    }

    public function get_pending_by_email_product( string $email, int $product_id, int $variation_id = 0 ) : ?object {
        global $wpdb;
        $email = SignupValidator::normalize_email( $email );
        if ( !is_email( $email ) ) {
            return null;
        }
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare(
            'SELECT * FROM ' . $this->table . ' WHERE email=%s AND product_id=%d AND variation_id=%d AND status=\'pending\' ORDER BY id DESC LIMIT 1',
            $email,
            $product_id,
            $variation_id
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        $row = $wpdb->get_row( $sql );
        return ( $row ?: null );
    }

    public function update_optin_token( int $id, string $token_hash ) : bool {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update on the plugin-owned subscriptions table.
        $updated = $wpdb->update(
            $this->table,
            array(
                'opt_in_token' => $token_hash,
            ),
            array(
                'id' => $id,
            ),
            array('%s'),
            array('%d')
        );
        return false !== $updated;
    }

    /**
     * Finish a signup left in `pending` by the double opt-in flow when that flow is unavailable.
     *
     * Downgrading to the free version (or disabling double opt-in) removes the confirmation
     * flow and would otherwise strand these rows; a fresh form submission from the same
     * shopper is treated as their confirmation.
     */
    public function activate_pending_without_optin( int $id, int $product_id ) : void {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update on the plugin-owned subscriptions table.
        $updated = $wpdb->update(
            $this->table,
            array(
                'status'       => 'active',
                'opted_in_at'  => current_time( 'mysql' ),
                'opt_in_token' => null,
            ),
            array(
                'id'     => $id,
                'status' => 'pending',
            ),
            array('%s', '%s', '%s'),
            array('%d', '%s')
        );
        if ( $updated ) {
            $this->log_event( $id, 'confirmed', array(
                'source' => 'resubscribe_without_double_optin',
            ) );
            $this->bust_count_cache( $product_id );
            $subscription = $this->get_by_id( $id );
            if ( $subscription ) {
                do_action( 'stockbeacon_waitlist_confirmed', $id, array(
                    'email'        => (string) $subscription->email,
                    'product_id'   => (int) $subscription->product_id,
                    'variation_id' => (int) $subscription->variation_id,
                ) );
            }
        }
    }

    public function activate_by_optin_hash( string $token_hash ) : bool {
        global $wpdb;
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare( 'SELECT id,email,product_id,variation_id,created_at FROM ' . $this->table . ' WHERE opt_in_token=%s AND status=\'pending\' LIMIT 1', $token_hash );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        $row = $wpdb->get_row( $sql );
        if ( !$row ) {
            return false;
        }
        if ( time() - strtotime( (string) $row->created_at ) > 48 * HOUR_IN_SECONDS ) {
            return false;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update on the plugin-owned subscriptions table.
        $updated = $wpdb->update(
            $this->table,
            array(
                'status'      => 'active',
                'opted_in_at' => current_time( 'mysql' ),
            ),
            array(
                'id' => (int) $row->id,
            ),
            array('%s', '%s'),
            array('%d')
        );
        if ( $updated ) {
            $this->log_event( (int) $row->id, 'confirmed' );
            $this->bust_count_cache( (int) $row->product_id );
            do_action( 'stockbeacon_waitlist_confirmed', (int) $row->id, array(
                'email'        => (string) $row->email,
                'product_id'   => (int) $row->product_id,
                'variation_id' => (int) $row->variation_id,
            ) );
            return true;
        }
        return false;
    }

    public function get_by_email( string $email ) : array {
        global $wpdb;
        $email = SignupValidator::normalize_email( $email );
        if ( !is_email( $email ) ) {
            return array();
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table . ' WHERE email=%s ORDER BY id DESC', $email );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; plugin-owned table with no core caching layer.
        return (array) $wpdb->get_results( $sql );
    }

    public function erase_by_email( string $email ) : int {
        global $wpdb;
        $email = SignupValidator::normalize_email( $email );
        if ( !is_email( $email ) ) {
            return 0;
        }
        $rows = $this->get_by_email( $email );
        $count = 0;
        foreach ( $rows as $row ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- GDPR erasure delete from the plugin-owned subscriptions table.
            if ( $wpdb->delete( $this->table, array(
                'id' => (int) $row->id,
            ), array('%d') ) ) {
                $this->delete_unsubscribe_transient( $row );
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- GDPR erasure delete from the plugin-owned logs table.
                $wpdb->delete( $this->log_table, array(
                    'subscription_id' => (int) $row->id,
                ), array('%d') );
                $this->bust_count_cache( (int) $row->product_id );
                $count++;
            }
        }
        // Email activity can outlive its subscription (retention removal and test sends).
        // Narrow candidates with LIKE, then JSON-decode and require an exact normalized email match.
        $needle = '%"email":"' . $wpdb->esc_like( $email ) . '"%';
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared; only the plugin-owned table name is interpolated.
        $candidates = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT id, metadata_json FROM ' . $this->log_table . ' WHERE metadata_json LIKE %s', $needle ) );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        foreach ( $candidates as $candidate ) {
            $metadata = json_decode( (string) $candidate->metadata_json, true );
            if ( !is_array( $metadata ) || !$this->metadata_contains_email( $metadata, $email ) ) {
                continue;
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- GDPR erasure delete from the plugin-owned logs table.
            if ( $wpdb->delete( $this->log_table, array(
                'id' => (int) $candidate->id,
            ), array('%d') ) ) {
                ++$count;
            }
        }
        return $count;
    }

    public function log_event( int $subscription_id, string $event, array $meta = array() ) : void {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Insert into the plugin-owned logs table.
        $wpdb->insert( $this->log_table, array(
            'subscription_id' => $subscription_id,
            'event_type'      => $event,
            'metadata_json'   => wp_json_encode( $meta ),
        ), array('%d', '%s', '%s') );
    }

    public function purge_old_records( int $days ) : int {
        global $wpdb;
        $days = max( 1, absint( $days ) );
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully prepared; only the plugin-owned table name is interpolated.
        $sql = $wpdb->prepare( 'SELECT id FROM ' . $this->table . "\n\t\t\t WHERE status IN ('notified','purchased')\n\t\t\t AND COALESCE(notified_at, purchased_at, created_at) < DATE_SUB(NOW(), INTERVAL %d DAY)", $days );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared above; retention purge on the plugin-owned table.
        $ids = array_map( 'absint', (array) $wpdb->get_col( $sql ) );
        $count = 0;
        foreach ( $ids as $id ) {
            if ( $this->delete_subscription( $id, false ) ) {
                ++$count;
            }
        }
        return $count;
    }

    private function build_unsubscribe_token( int $subscription_id, string $email ) : string {
        return hash_hmac( 'sha256', $subscription_id . $email, wp_salt( 'nonce' ) );
    }

    private function delete_unsubscribe_transient( object $subscription ) : void {
        $token = $this->build_unsubscribe_token( (int) $subscription->id, (string) $subscription->email );
        delete_transient( 'stockbeacon_unsubscribe_' . $token );
    }

    private function redact_unsubscribe_transient( object $subscription ) : void {
        $token = $this->build_unsubscribe_token( (int) $subscription->id, (string) $subscription->email );
        $key = 'stockbeacon_unsubscribe_' . $token;
        $fallback = get_transient( $key );
        if ( !is_array( $fallback ) || !array_key_exists( 'email', $fallback ) ) {
            return;
        }
        unset($fallback['email']);
        set_transient( $key, $fallback, 90 * DAY_IN_SECONDS );
    }

    private function redact_email_from_logs( int $subscription_id ) : void {
        global $wpdb;
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fully prepared; only the plugin-owned table name is interpolated.
        $rows = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT id, metadata_json FROM ' . $this->log_table . ' WHERE subscription_id=%d', $subscription_id ) );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        foreach ( $rows as $row ) {
            $metadata = json_decode( (string) $row->metadata_json, true );
            if ( !is_array( $metadata ) ) {
                continue;
            }
            $changed = false;
            $metadata = $this->remove_email_fields( $metadata, $changed );
            if ( $changed ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Redaction update on the plugin-owned logs table.
                $wpdb->update(
                    $this->log_table,
                    array(
                        'metadata_json' => wp_json_encode( $metadata ),
                    ),
                    array(
                        'id' => (int) $row->id,
                    ),
                    array('%s'),
                    array('%d')
                );
            }
        }
    }

    /**
     * @param array<mixed> $metadata
     * @return array<mixed>
     */
    private function remove_email_fields( array $metadata, bool &$changed ) : array {
        foreach ( $metadata as $key => $value ) {
            if ( is_string( $key ) && 'email' === strtolower( $key ) ) {
                unset($metadata[$key]);
                $changed = true;
                continue;
            }
            if ( is_array( $value ) ) {
                $metadata[$key] = $this->remove_email_fields( $value, $changed );
            }
        }
        return $metadata;
    }

    /**
     * @param array<mixed> $metadata
     */
    private function metadata_contains_email( array $metadata, string $email ) : bool {
        foreach ( $metadata as $key => $value ) {
            if ( is_string( $key ) && 'email' === strtolower( $key ) && is_scalar( $value ) ) {
                if ( $email === SignupValidator::normalize_email( (string) $value ) ) {
                    return true;
                }
            }
            if ( is_array( $value ) && $this->metadata_contains_email( $value, $email ) ) {
                return true;
            }
        }
        return false;
    }

}
