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

/**
 * VMO Email Marketing - ULTRA-COMPETITIVE SYSTEM
 * 
 * BEATS MAILCHIMP with:
 * - AI-powered campaign generation (GPT-5.5)
 * - Deep Research integration
 * - In-email shopping with Stripe
 * - 2 tokens per email (100% profit margin)
 * - 600 tokens per campaign creation (70% profit margin)
 * - Users can send 17,000-181,000 emails per plan
 */
class VMO_Email_Marketing {
    
    private $campaign_generator;
    private $identity_manager;
    
    public function __construct() {
        // Load our ultra-competitive email system
        require_once dirname(__FILE__) . '/class-vmo-ai-email-campaign-generator.php';
        require_once dirname(__FILE__) . '/class-vmo-email-identity-manager.php';
        require_once dirname(__FILE__) . '/class-vmo-email-router.php';
        
        $this->campaign_generator = new VMO_AI_Email_Campaign_Generator();
        $this->identity_manager = new VMO_Email_Identity_Manager();
        
        add_action('wp_ajax_vmo_email_generate_campaign', [$this, 'generate_campaign']);
        add_action('wp_ajax_vmo_email_send_campaign', [$this, 'send_campaign']);
        add_action('wp_ajax_vmo_get_email_overview', [$this, 'get_overview']);
        // Back-compat: older UI used these action names.
        add_action('wp_ajax_vmo_get_email_metrics', [$this, 'get_overview']);
        add_action('wp_ajax_vmo_get_email_campaigns', [$this, 'get_overview']);

        // AI auto-reply status/toggle (mailbox tab).
        add_action('wp_ajax_vmo_get_ai_reply_status', [$this, 'get_ai_reply_status']);
        add_action('wp_ajax_vmo_update_ai_reply_settings', [$this, 'update_ai_reply_settings']);

        add_action('wp_ajax_vmo_save_email_signature', [$this, 'save_signature']);
        add_action('wp_ajax_vmo_get_email_signature', [$this, 'get_signature']);
        add_action('wp_ajax_vmo_get_signature_media_images', [$this, 'get_signature_media_images']);
        add_action('wp_ajax_vmo_list_email_accounts', [$this, 'list_email_accounts']);
        add_action('wp_ajax_vmo_generate_email_content', [$this, 'generate_email_content']);
        add_action('init', [$this, 'maybe_create_tables']);
        add_action('rest_api_init', [$this, 'register_tracking_routes']);
    }

    /**
     * Register REST routes for open-pixel and click tracking.
     */
    public function register_tracking_routes() {
        register_rest_route('vmo/v1', '/email/track/open', [
            'methods'             => 'GET',
            'callback'            => [$this, 'handle_open_tracking'],
            'permission_callback' => '__return_true',
        ]);
        register_rest_route('vmo/v1', '/email/track/click', [
            'methods'             => 'GET',
            'callback'            => [$this, 'handle_click_tracking'],
            'permission_callback' => '__return_true',
        ]);
    }

    private function get_auto_reply_account_param(): string {
        $account = isset($_REQUEST['account']) ? sanitize_email(wp_unslash($_REQUEST['account'])) : '';
        return $account ?: '';
    }

    private function get_auto_reply_account_settings(int $user_id): array {
        $settings = get_user_meta($user_id, 'vmo_ai_auto_reply_account_settings', true);
        return is_array($settings) ? $settings : array();
    }

    private function get_auto_reply_setting_for_account(int $user_id, string $account): array {
        $mode = get_user_meta($user_id, 'vmo_ai_auto_reply_mode', true);
        if (!in_array($mode, ['draft_only', 'auto_send_safe', 'ask_every_reply'], true)) {
            $mode = 'draft_only';
        }

        if ($account === '') {
            return array(
                'enabled' => !empty(get_user_meta($user_id, 'vmo_ai_auto_reply_enabled', true)) ? 1 : 0,
                'mode' => $mode,
            );
        }

        $settings = $this->get_auto_reply_account_settings($user_id);
        if (isset($settings[$account]) && is_array($settings[$account])) {
            $row = $settings[$account];
            $row_mode = isset($row['mode']) ? sanitize_key((string) $row['mode']) : $mode;
            if (!in_array($row_mode, ['draft_only', 'auto_send_safe', 'ask_every_reply'], true)) {
                $row_mode = $mode;
            }
            return array(
                'enabled' => !empty($row['enabled']) ? 1 : 0,
                'mode' => $row_mode,
            );
        }

        return array('enabled' => 0, 'mode' => $mode);
    }

    private function save_auto_reply_setting_for_account(int $user_id, string $account, int $enabled, string $mode): void {
        if (!in_array($mode, ['draft_only', 'auto_send_safe', 'ask_every_reply'], true)) {
            $mode = 'draft_only';
        }

        if ($account === '') {
            update_user_meta($user_id, 'vmo_ai_auto_reply_enabled', $enabled ? 1 : 0);
            update_user_meta($user_id, 'vmo_ai_auto_reply_mode', $mode);
            return;
        }

        $settings = $this->get_auto_reply_account_settings($user_id);
        $settings[$account] = array(
            'enabled' => $enabled ? 1 : 0,
            'mode' => $mode,
            'updated_at' => current_time('mysql'),
        );
        update_user_meta($user_id, 'vmo_ai_auto_reply_account_settings', $settings);

        $any_enabled = false;
        foreach ($settings as $row) {
            if (is_array($row) && !empty($row['enabled'])) {
                $any_enabled = true;
                break;
            }
        }
        update_user_meta($user_id, 'vmo_ai_auto_reply_enabled', $any_enabled ? 1 : 0);
        update_user_meta($user_id, 'vmo_ai_auto_reply_mode', $mode);
    }

    /**
     * Serve a 1×1 transparent GIF and record the open.
     */
    public function handle_open_tracking( \WP_REST_Request $request ) {
        $token = sanitize_text_field( $request->get_param('t') );
        if ( $token && preg_match('/^[a-f0-9]{32}$/', $token) ) {
            global $wpdb;
            $sends = $wpdb->prefix . 'vmo_email_sends';
            $wpdb->query( $wpdb->prepare(
                "UPDATE {$sends} SET opened_at = COALESCE(opened_at, %s) WHERE tracking_token = %s",
                current_time('mysql'), $token
            ) );
        }
        // Return a 1×1 transparent GIF
        $gif = base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7');
        header('Content-Type: image/gif');
        header('Content-Length: ' . strlen($gif));
        header('Cache-Control: no-store, no-cache, must-revalidate');
        echo $gif; // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
        exit;
    }

    /**
     * Record the click and redirect to the original URL.
     */
    public function handle_click_tracking( \WP_REST_Request $request ) {
        $token = sanitize_text_field( $request->get_param('t') );
        $url   = $request->get_param('u');

        if ( $token && preg_match('/^[a-f0-9]{32}$/', $token) ) {
            global $wpdb;
            $sends = $wpdb->prefix . 'vmo_email_sends';
            $wpdb->query( $wpdb->prepare(
                "UPDATE {$sends} SET clicked_at = COALESCE(clicked_at, %s) WHERE tracking_token = %s",
                current_time('mysql'), $token
            ) );
        }
        // Validate redirect URL to prevent open-redirect
        if ( $url && wp_http_validate_url( $url ) ) {
            wp_redirect( esc_url_raw( $url ), 302 );
            exit;
        }
        wp_redirect( home_url(), 302 );
        exit;
    }

    /**
     * Inject tracking pixel and click-wrapper into email HTML for a given tracking token.
     */
    private function inject_tracking( string $html, string $token ): string {
        $pixel_url = rest_url( 'vmo/v1/email/track/open' ) . '?t=' . urlencode( $token );
        $pixel = '<img src="' . esc_url( $pixel_url ) . '" width="1" height="1" alt="" style="display:none" />';

        // Wrap <a href="..."> links with click tracker (skip mailto: and tel:)
        $click_base = rest_url( 'vmo/v1/email/track/click' ) . '?t=' . urlencode( $token ) . '&u=';
        $html = preg_replace_callback(
            '/<a\s([^>]*?)href=["\'](?!mailto:|tel:)(https?:\/\/[^"\'>]+)["\']([^>]*)>/i',
            function( $m ) use ( $click_base ) {
                $wrapped = $click_base . urlencode( $m[2] );
                return '<a ' . $m[1] . 'href="' . esc_url( $wrapped ) . '"' . $m[3] . '>';
            },
            $html
        );

        // Insert pixel before </body> or append
        if ( stripos( $html, '</body>' ) !== false ) {
            $html = str_ireplace( '</body>', $pixel . '</body>', $html );
        } else {
            $html .= $pixel;
        }
        return $html;
    }

    public function get_ai_reply_status() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) {
            wp_send_json_error(['message' => 'Not logged in'], 401);
        }

        $user_id = get_current_user_id();
        $account = $this->get_auto_reply_account_param();
        $setting = $this->get_auto_reply_setting_for_account($user_id, $account);

        wp_send_json_success([
            'account' => $account,
            'enabled' => !empty($setting['enabled']) ? 1 : 0,
            'mode' => $setting['mode'],
        ]);
    }

    public function update_ai_reply_settings() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) {
            wp_send_json_error(['message' => 'Not logged in'], 401);
        }

        $user_id = get_current_user_id();
        $account = $this->get_auto_reply_account_param();
        $enabled = isset($_POST['enabled']) ? intval($_POST['enabled']) : 0;
        $mode = isset($_POST['mode']) ? sanitize_key((string) $_POST['mode']) : 'draft_only';
        if (!in_array($mode, ['draft_only', 'auto_send_safe', 'ask_every_reply'], true)) {
            $mode = 'draft_only';
        }
        $this->save_auto_reply_setting_for_account($user_id, $account, $enabled, $mode);

        wp_send_json_success([
            'account' => $account,
            'enabled' => $enabled ? 1 : 0,
            'mode' => $mode,
        ]);
    }

    private function verify_ajax_request(): bool {
        // Accept either our legacy builder nonce or the WP REST nonce.
        $valid = check_ajax_referer('vmo_builder_nonce', 'security', false);
        if ($valid) {
            return true;
        }

        $rest_nonce = isset($_SERVER['HTTP_X_WP_NONCE']) ? sanitize_text_field($_SERVER['HTTP_X_WP_NONCE']) : '';
        if ($rest_nonce && wp_verify_nonce($rest_nonce, 'wp_rest')) {
            return true;
        }

        return false;
    }

    private function ensure_signature_table_schema(): void {
        global $wpdb;

        $table = $wpdb->prefix . 'vmo_email_signatures';
        $charset_collate = $wpdb->get_charset_collate();

        if (defined('ABSPATH') && file_exists(ABSPATH . 'wp-admin/includes/upgrade.php')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            account varchar(255) NULL DEFAULT 'default',
            name varchar(255) NULL,
            designation varchar(255) NULL,
            social_links longtext NULL,
            custom_links longtext NULL,
            logo_url text NULL,
            website_url text NULL,
            theme_color varchar(32) NULL DEFAULT '#000000',
            is_default tinyint(1) DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY uniq_user_account (user_id, account),
            KEY user_id (user_id)
        ) {$charset_collate};";

        if (function_exists('dbDelta')) {
            dbDelta($sql);
        }

        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) === $table;
        if (!$table_exists) {
            return;
        }

        $columns = $wpdb->get_col("SHOW COLUMNS FROM {$table}", 0);
        $columns = is_array($columns) ? array_map('strval', $columns) : array();
        $missing = function($column) use ($columns) {
            return !in_array($column, $columns, true);
        };

        $alterations = array(
            'user_id' => "ADD COLUMN user_id bigint(20) unsigned NOT NULL DEFAULT 0 AFTER id",
            'account' => "ADD COLUMN account varchar(255) NULL DEFAULT 'default' AFTER user_id",
            'name' => "ADD COLUMN name varchar(255) NULL AFTER account",
            'designation' => "ADD COLUMN designation varchar(255) NULL AFTER name",
            'social_links' => "ADD COLUMN social_links longtext NULL AFTER designation",
            'custom_links' => "ADD COLUMN custom_links longtext NULL AFTER social_links",
            'logo_url' => "ADD COLUMN logo_url text NULL AFTER custom_links",
            'website_url' => "ADD COLUMN website_url text NULL AFTER logo_url",
            'theme_color' => "ADD COLUMN theme_color varchar(32) NULL DEFAULT '#000000' AFTER website_url",
            'is_default' => "ADD COLUMN is_default tinyint(1) DEFAULT 0 AFTER theme_color",
            'created_at' => "ADD COLUMN created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER is_default",
            'updated_at' => "ADD COLUMN updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER created_at",
        );

        foreach ($alterations as $column => $alter_sql) {
            if ($missing($column)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query("ALTER TABLE {$table} {$alter_sql}");
            }
        }
    }

    public function list_email_accounts() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) {
            wp_send_json_error(['message' => 'Not logged in'], 401);
        }

        $user_id = get_current_user_id();
        global $wpdb;
        $table = $wpdb->prefix . 'vmo_email_sender_identities';
        $accounts = [];

        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) === $table) {
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT email_address, email_role, display_name, verification_status, is_primary
                     FROM {$table}
                     WHERE user_id = %d
                     ORDER BY is_primary DESC, (verification_status = 'verified') DESC, email_role ASC, email_address ASC",
                    $user_id
                ),
                ARRAY_A
            );
            foreach ($rows as $r) {
                $email = sanitize_email($r['email_address'] ?? '');
                if (!$email) {
                    continue;
                }
                $accounts[] = [
                    'email' => $email,
                    'role' => sanitize_key($r['email_role'] ?? ''),
                    'display_name' => sanitize_text_field($r['display_name'] ?? ''),
                    'verification_status' => sanitize_text_field($r['verification_status'] ?? ''),
                    'is_primary' => !empty($r['is_primary']) ? 1 : 0,
                ];
            }
        }

        // Always include the current WP user email as a safe fallback option.
        $user = wp_get_current_user();
        $fallback_email = $user && $user->user_email ? sanitize_email($user->user_email) : '';
        if ($fallback_email) {
            $already = false;
            foreach ($accounts as $a) {
                if (strcasecmp($a['email'], $fallback_email) === 0) {
                    $already = true;
                    break;
                }
            }
            if (!$already) {
                array_unshift($accounts, [
                    'email' => $fallback_email,
                    'role' => 'primary',
                    'display_name' => $user->display_name ? sanitize_text_field($user->display_name) : '',
                    'verification_status' => 'fallback',
                    'is_primary' => 1,
                ]);
            }
        }

        wp_send_json_success(['accounts' => $accounts]);
    }

    public function get_signature_media_images() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in() || !current_user_can('read')) {
            wp_send_json_error(['message' => 'Unauthorized'], 403);
        }

        $search = isset($_POST['search']) ? sanitize_text_field(wp_unslash($_POST['search'])) : '';
        $per_page = isset($_POST['per_page']) ? max(1, min(80, intval($_POST['per_page']))) : 60;

        $query_args = array(
            'post_type' => 'attachment',
            'post_status' => 'inherit',
            'post_mime_type' => 'image',
            'posts_per_page' => $per_page,
            'orderby' => 'date',
            'order' => 'DESC',
            's' => $search,
            'fields' => 'ids',
        );

        $ids = get_posts($query_args);
        $images = array();
        foreach ((array) $ids as $id) {
            $id = intval($id);
            if ($id <= 0) {
                continue;
            }
            $full = wp_get_attachment_url($id);
            if (!$full) {
                continue;
            }
            $medium = wp_get_attachment_image_src($id, 'medium');
            $thumb = wp_get_attachment_image_src($id, 'thumbnail');
            $file = get_attached_file($id);
            $images[] = array(
                'id' => $id,
                'url' => esc_url_raw($full),
                'source_url' => esc_url_raw($full),
                'preview_url' => esc_url_raw(is_array($medium) && !empty($medium[0]) ? $medium[0] : (is_array($thumb) && !empty($thumb[0]) ? $thumb[0] : $full)),
                'filename' => $file ? wp_basename($file) : wp_basename(parse_url($full, PHP_URL_PATH)),
                'title' => get_the_title($id) ?: wp_basename(parse_url($full, PHP_URL_PATH)),
            );
        }

        wp_send_json_success(array('images' => $images));
    }

    public function save_signature() {
        // Discard any stray output (notices/warnings from other plugins) so the
        // JSON response is never polluted, which would surface as
        // "VMO signature service returned a non-JSON response" in the UI.
        if (function_exists('ob_get_level') && ob_get_level() > 0) { @ob_end_clean(); }
        if (function_exists('ob_start')) { ob_start(); }

        if (!$this->verify_ajax_request()) {
            if (function_exists('ob_get_level') && ob_get_level() > 0) { @ob_end_clean(); }
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) {
            if (function_exists('ob_get_level') && ob_get_level() > 0) { @ob_end_clean(); }
            wp_send_json_error(['message' => 'Not logged in'], 401);
        }
        $user_id = get_current_user_id();

        $account = isset($_POST['account']) ? sanitize_email($_POST['account']) : '';
        if (!$account) {
            // Back-compat: allow a single default signature if no account selected.
            $account = 'default';
        }
        
        $name = isset($_POST['name']) ? sanitize_text_field(wp_unslash($_POST['name'])) : '';
        $designation = isset($_POST['designation']) ? sanitize_text_field(wp_unslash($_POST['designation'])) : '';
        $logo_url = isset($_POST['logo_url']) ? esc_url_raw(wp_unslash($_POST['logo_url'])) : '';
        $website_url = isset($_POST['website_url']) ? esc_url_raw(wp_unslash($_POST['website_url'])) : '';
        $theme_color = isset($_POST['theme_color']) ? sanitize_hex_color(wp_unslash($_POST['theme_color'])) : '';

        // The DB column `name` is NOT NULL with no default. Without this guard the
        // INSERT fails silently and the user sees a generic "Error saving signature."
        if (trim($name) === '') {
            wp_send_json_error(['message' => 'Full Name is required to generate a signature.'], 400);
        }

        $social_links = null;
        if (isset($_POST['social_links'])) {
            $raw = wp_unslash($_POST['social_links']);
            $decoded = is_string($raw) ? json_decode($raw, true) : null;
            if (is_array($decoded)) {
                // The client (templates/tools-modals.php :: vmoSaveSignature)
                // already converts each handle into a full URL using the
                // platform map, so here we just sanitize and store.
                $clean = [];
                foreach ($decoded as $row) {
                    if (!is_array($row)) { continue; }
                    $platform = isset($row['platform']) ? sanitize_key($row['platform']) : '';
                    $url = isset($row['url']) ? esc_url_raw($row['url']) : '';
                    if ($url) {
                        $clean[] = ['platform' => $platform, 'url' => $url];
                    }
                }
                $social_links = wp_json_encode($clean);
            }
        }

        $custom_links = null;
        if (isset($_POST['custom_links'])) {
            $raw = wp_unslash($_POST['custom_links']);
            $decoded = is_string($raw) ? json_decode($raw, true) : null;
            if (is_array($decoded)) {
                $custom_links = wp_json_encode($decoded);
            }
        }
        
        global $wpdb;
        $table = $wpdb->prefix . 'vmo_email_signatures';
        $this->ensure_signature_table_schema();

        // [VMO_SIG_TABLE_HEAL] If the signatures table is missing on this
        // install (e.g. the user upgraded from a build that pre-dated it,
        // or activation never re-ran), the INSERT silently fails and the
        // user just sees "Failed to save signature." Re-create the schema
        // on demand so the save works on the very next click instead of
        // requiring the user to deactivate/reactivate the plugin.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
        if ( ! $table_exists && function_exists( 'vmo_create_plugin_tables' ) ) {
            try { vmo_create_plugin_tables(); } catch ( \Throwable $t ) { /* fall through */ }
            $this->ensure_signature_table_schema();
        }

        // Check if exists
        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM $table WHERE user_id = %d AND account = %s", $user_id, $account));
        
        $data = [
            'user_id' => $user_id,
            'account' => $account,
            'name' => $name,
            'designation' => $designation,
            'social_links' => $social_links,
            'custom_links' => $custom_links,
            'logo_url' => $logo_url,
            'website_url' => $website_url,
            'theme_color' => $theme_color,
            'is_default' => ($account === 'default') ? 1 : 0,
            'updated_at' => current_time('mysql')
        ];
        
        $ok = true;
        if ($exists) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $ok = (false !== $wpdb->update($table, $data, ['id' => $exists]));
        } else {
            $data['created_at'] = current_time('mysql');
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $ok = (false !== $wpdb->insert($table, $data));
        }

        // [VMO_SIG_RETRY_AFTER_HEAL] If the first write failed AND the table
        // looked missing or the error mentions a missing table/column, run
        // the migration once and retry. This makes the save idempotent for
        // users on stale schemas.
        if ( ! $ok ) {
            $err = (string) ( $wpdb->last_error ?? '' );
            $looks_schema = ( $err === '' )
                || stripos( $err, "doesn't exist" ) !== false
                || stripos( $err, 'unknown column' ) !== false
                || stripos( $err, 'no such table' ) !== false;
            if ( $looks_schema && function_exists( 'vmo_create_plugin_tables' ) ) {
                try { vmo_create_plugin_tables(); } catch ( \Throwable $t ) { /* fall through */ }
                $this->ensure_signature_table_schema();
                if ( $exists ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $ok = ( false !== $wpdb->update( $table, $data, [ 'id' => $exists ] ) );
                } else {
                    if ( ! isset( $data['created_at'] ) ) { $data['created_at'] = current_time( 'mysql' ); }
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $ok = ( false !== $wpdb->insert( $table, $data ) );
                }
            }
        }

        if (!$ok) {
            if (function_exists('ob_get_level') && ob_get_level() > 0) { @ob_end_clean(); }
            wp_send_json_error([
                'message'  => 'Could not save signature. ' . ( $wpdb->last_error ? trim( $wpdb->last_error ) : 'The database write was rejected.' ),
                'db_error' => $wpdb->last_error ?: null,
            ]);
        }

        if (function_exists('ob_get_level') && ob_get_level() > 0) { @ob_end_clean(); }
        wp_send_json_success(['message' => 'Signature saved successfully']);
    }

    public function get_signature() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) {
            wp_send_json_error(['message' => 'Not logged in'], 401);
        }
        $user_id = get_current_user_id();
        $account = isset($_REQUEST['account']) ? sanitize_email($_REQUEST['account']) : '';
        if (!$account) {
            $account = 'default';
        }
        global $wpdb;
        $table = $wpdb->prefix . 'vmo_email_signatures';
        $this->ensure_signature_table_schema();
        $sig = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE user_id = %d AND account = %s", $user_id, $account), ARRAY_A);

        if (!$sig && $account !== 'default') {
            // Fallback to default signature if a specific account signature doesn't exist.
            $sig = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE user_id = %d AND account = %s", $user_id, 'default'), ARRAY_A);
        }
        
        if ($sig) {
            $sig['social_links'] = json_decode($sig['social_links'], true);
            $sig['custom_links'] = json_decode($sig['custom_links'], true);
        }
        
        wp_send_json_success(['signature' => $sig]);
    }

    public function generate_email_content() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce'], 403);
        }
        if (!is_user_logged_in()) wp_send_json_error(['message' => 'Not logged in'], 401);
        
        $intent = isset($_POST['intent']) ? sanitize_textarea_field(wp_unslash($_POST['intent'])) : '';
        $format = isset($_POST['format']) ? sanitize_text_field(wp_unslash($_POST['format'])) : 'plain';
        $user_id = get_current_user_id();

        if ($intent === '') {
            wp_send_json_error(['message' => 'Email intent is required'], 400);
        }

        if (!in_array($format, ['plain', 'html'], true)) {
            $format = 'plain';
        }
        
        // Get user profile for context
        $profile = function_exists('vmo_get_consultation_profile') ? vmo_get_consultation_profile($user_id) : [];
        $business_name = $profile['business_name'] ?? 'My Business';
        
        // Call AI
        $prompt = "Generate an email for a business named '$business_name'.\nIntent: $intent\nFormat: $format.\n";
        if ($format === 'html') {
            $prompt .= "Return a JSON object with keys: 'subject', 'html_body' (full HTML with inline CSS), 'plain_body'. Make the HTML modern and responsive.";
        } else {
            $prompt .= "Return a JSON object with keys: 'subject', 'plain_body'.";
        }
        
        // Use the existing generator class or direct OpenAI call
        // For simplicity, reusing the generator's method if available or mocking a direct call structure
        // Assuming VMO_AI_Email_Campaign_Generator has a generic method or we add one.
        // Let's add a generic method to VMO_AI_Email_Campaign_Generator
        
        $generator = new VMO_AI_Email_Campaign_Generator();
        $result = $generator->generate_custom_email($prompt, $format);

        if (isset($result['error'])) {
            wp_send_json_error(['message' => $result['error']], 500);
        }
        
        wp_send_json_success($result);
    }

    public function maybe_create_tables() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $campaigns = $wpdb->prefix . 'vmo_email_campaigns';
        $sends     = $wpdb->prefix . 'vmo_email_sends';
        $signatures = $wpdb->prefix . 'vmo_email_signatures';

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $sql1 = "CREATE TABLE IF NOT EXISTS $campaigns (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            title varchar(255) NULL,
            payload longtext NULL,
            status varchar(32) NOT NULL DEFAULT 'draft',
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY user_id (user_id),
            KEY status (status)
        ) $charset_collate;";

        $sql2 = "CREATE TABLE IF NOT EXISTS $sends (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            campaign_id bigint(20) unsigned NOT NULL,
            user_id bigint(20) unsigned NOT NULL,
            lead_email varchar(255) NOT NULL,
            lead_name varchar(255) NULL,
            subject text NULL,
            status varchar(32) NOT NULL DEFAULT 'queued',
            error text NULL,
            tracking_token varchar(64) NULL,
            created_at datetime NOT NULL,
            sent_at datetime NULL,
            opened_at datetime NULL,
            clicked_at datetime NULL,
            PRIMARY KEY  (id),
            KEY tracking_token (tracking_token),
            KEY campaign_id (campaign_id),
            KEY user_id (user_id),
            KEY status (status)
        ) $charset_collate;";

        $sql3 = "CREATE TABLE IF NOT EXISTS $signatures (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            account varchar(255) NULL,
            name varchar(255) NULL,
            designation varchar(255) NULL,
            social_links text NULL,
            custom_links text NULL,
            logo_url text NULL,
            website_url text NULL,
            theme_color varchar(32) NULL,
            is_default tinyint(1) DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY uniq_user_account (user_id, account),
            KEY user_id (user_id)
        ) $charset_collate;";

        dbDelta($sql1);
        dbDelta($sql2);
        dbDelta($sql3);
        $this->ensure_signature_table_schema();
    }

    public function generate_campaign() {
        if (!check_ajax_referer('vmo_builder_nonce', 'security', false)) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) wp_send_json_error(['message' => 'Not logged in'], 401);
        $user_id = get_current_user_id();

        if (!function_exists('vmo_require_profile_or_error')) {
            $consultation_profile_file = plugin_dir_path(__FILE__) . 'consultation-profile.php';
            if (file_exists($consultation_profile_file) && is_readable($consultation_profile_file)) {
                try {
                    require_once $consultation_profile_file;
                } catch (Exception $e) {
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        error_log('VMO: Failed to load consultation profile in email marketing: ' . $e->getMessage());
                    }
                }
            }
        }
        vmo_require_profile_or_error($user_id);

        // Get install_id for token operations - auto-create if needed
        global $wpdb;
        $activations_table = $wpdb->prefix . 'vmo_manager_activations';
        
        // ✅ Use helper function to get or create activation
        $install_id = null;
        if (function_exists('vmo_get_user_install_id')) {
            $install_id = vmo_get_user_install_id($user_id);
        } else {
            // Fallback: check table directly
            $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $activations_table));
            if ($table_exists) {
                $install_id = $wpdb->get_var($wpdb->prepare(
                    "SELECT install_id FROM {$activations_table} WHERE user_id = %d LIMIT 1",
                    $user_id
                ));
            }
        }
        
        if (!$install_id) {
            wp_send_json_error(['message' => 'Installation not found. Please activate your plan first.']);
        }

        // Check if user has enough tokens for campaign creation (600 tokens)
        $activation = $wpdb->get_row($wpdb->prepare(
            "SELECT token_balance FROM {$activations_table} WHERE install_id = %s",
            $install_id
        ));
        
        if (!$activation || intval($activation->token_balance) < 600) {
            wp_send_json_error([
                'message' => 'Insufficient tokens. Campaign creation requires 600 tokens.',
                'tokens_needed' => 600,
                'tokens_available' => $activation ? intval($activation->token_balance) : 0
            ]);
        }

        $api_key = get_option('vmo_openai_api_key');
        if (!$api_key) wp_send_json_error(['message' => 'AI API key missing']);

        $profile = function_exists('vmo_get_consultation_profile') ? vmo_get_consultation_profile($user_id) : [];
        $business_name = sanitize_text_field($profile['business_name'] ?? ($profile['company'] ?? 'Your Business'));
        $industry = sanitize_text_field($profile['industry'] ?? 'General');
        $location = sanitize_text_field($profile['location'] ?? 'Local');
        $offer = sanitize_text_field($profile['offer'] ?? ($profile['value_proposition'] ?? 'your core service'));

        $lead_count = max(10, min(50, intval($_POST['lead_count'] ?? 25)));
        
        // ✅ NEW: Use our ultra-competitive AI campaign generator
        $campaign_data = [
            'business_data' => [
                'business_name' => $business_name,
                'industry' => $industry,
                'location' => $location,
                'brand_voice' => 'professional and engaging'
            ],
            'type' => 'promotional',
            'audience' => $profile['target_audience'] ?? 'local customers'
        ];
        
        // ✅ NEW: Use our ultra-competitive AI campaign generator
        $campaign = $this->campaign_generator->generate_campaign($install_id, $campaign_data);
        
        if (!$campaign || !$campaign['success']) {
            wp_send_json_error(['message' => 'Failed to generate campaign']);
        }
        
        // Generate A/B test variants (we get multiple subject lines)
        $ab_subjects = isset($campaign['ab_test_subjects']) ? $campaign['ab_test_subjects'] : [$campaign['subject']];
        
        // Create 4 campaign options with different subject lines and preview texts
        $options = [];
        for ($i = 0; $i < 4; $i++) {
            $subject_variant = isset($ab_subjects[$i]) ? $ab_subjects[$i] : $campaign['subject'];
            $preview_variant = $campaign['preview_text'];
            
            // Slight HTML variations if needed (for true A/B testing)
            $html_variant = $campaign['html_content'];
            
            $options[] = [
                'subject' => $subject_variant,
                'preheader' => $preview_variant,
                'html' => $html_variant,
                'text' => $campaign['plain_text']
            ];
        }
        
        // Recipient list from the CRM contacts table (never generate fake recipients).
        $contacts_table = $wpdb->prefix . 'vmo_contacts';
        $has_contacts = (bool) $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $wpdb->esc_like($contacts_table)));
        if (!$has_contacts) {
            wp_send_json_error(['message' => 'Contacts table missing. Please deactivate/reactivate the plugin to run database setup.']);
        }

        $existing_leads = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT *
                 FROM {$contacts_table}
                 WHERE user_id = %d
                   AND lifecycle_stage <> 'customer'
                   AND email IS NOT NULL AND email <> ''
                 ORDER BY updated_at DESC, created_at DESC
                 LIMIT %d",
                $user_id,
                $lead_count
            ),
            ARRAY_A
        );
        
        $leads = [];
        foreach (($existing_leads ?: []) as $lead) {
            $lead_name = $lead['full_name'] ?? '';
            if (empty($lead_name)) {
                $lead_name = trim(($lead['first_name'] ?? '') . ' ' . ($lead['last_name'] ?? ''));
            }
            $leads[] = [
                'name' => sanitize_text_field($lead_name),
                'email' => sanitize_email($lead['email'] ?? ''),
                'company' => sanitize_text_field($lead['company'] ?? ''),
                'notes' => sanitize_text_field($lead['notes'] ?? '')
            ];
        }

        if (!count($leads)) {
            wp_send_json_error(['message' => 'No leads found in CRM. Import contacts first (Lead Generation → Upload CSV).']);
        }

        // Save draft campaign
        global $wpdb; $campaigns = $wpdb->prefix . 'vmo_email_campaigns';
        $campaign_payload = [
            'profile' => [ 'business_name' => $business_name, 'industry' => $industry, 'location' => $location, 'offer' => $offer ],
            'options' => $options,
            'leads'   => $leads,
        ];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->insert($campaigns, [
            'user_id' => $user_id,
            'title' => $business_name . ' - Email Campaign Draft',
            'payload' => wp_json_encode($campaign_payload),
            'status' => 'draft',
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql'),
        ]);
        $campaign_id = $wpdb->insert_id;

        wp_send_json_success([
            'campaign_id' => $campaign_id,
            'options' => $options,
            'leads' => $leads,
            'tokens_used' => (int) ($campaign['tokens_used'] ?? 0),
        ]);
    }

    public function send_campaign() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) {
            wp_send_json_error(['message' => 'Not logged in'], 401);
        }

        $user_id = get_current_user_id();
        $campaign_id = intval($_POST['campaign_id'] ?? 0);
        $selected_index = intval($_POST['selected_index'] ?? -1);
        if ($campaign_id <= 0 || $selected_index < 0) {
            wp_send_json_error(['message' => 'Missing campaign or selection']);
        }

        global $wpdb;
        $campaigns = $wpdb->prefix . 'vmo_email_campaigns';
        $sends = $wpdb->prefix . 'vmo_email_sends';

        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM $campaigns WHERE id = %d AND user_id = %d", $campaign_id, $user_id));
        if (!$row) {
            wp_send_json_error(['message' => 'Campaign not found']);
        }

        $payload = json_decode($row->payload ?? '', true);
        $options = is_array($payload) ? ($payload['options'] ?? []) : [];
        $leads = is_array($payload) ? ($payload['leads'] ?? []) : [];
        if (!is_array($options) || !isset($options[$selected_index]) || !is_array($options[$selected_index])) {
            wp_send_json_error(['message' => 'Invalid selection']);
        }

        $option = $options[$selected_index];
        $subject = sanitize_text_field($option['subject'] ?? 'Email Campaign');
        $html = (string) ($option['html'] ?? ($option['text'] ?? ''));
        if (!$html) {
            wp_send_json_error(['message' => 'Campaign content is empty']);
        }

        $sender_role = isset($_POST['sender_role']) ? sanitize_key(wp_unslash($_POST['sender_role'])) : 'marketing';
        $sender_context = $this->identity_manager->get_sender_for_campaign($user_id, $sender_role);
        if (!empty($sender_context['signature']['html'])) {
            $html = $this->identity_manager->append_signature_to_html($html, $sender_context['signature']['html']);
        }

        $recipients = [];
        if (is_array($leads)) {
            foreach ($leads as $lead) {
                if (!is_array($lead)) {
                    continue;
                }
                $email = sanitize_email($lead['email'] ?? '');
                if ($email) {
                    $recipients[] = $email;
                }
            }
        }
        $recipients = array_values(array_unique($recipients));
        if (empty($recipients)) {
            wp_send_json_error(['message' => 'No recipients found for this campaign']);
        }

        $router = VMO_Email_Router::get_instance();
        $from_email = sanitize_email($sender_context['email'] ?? '');
        $from_name = sanitize_text_field($sender_context['name'] ?? '');
        $reply_to = sanitize_email($sender_context['reply_to'] ?? '');

        $sent = 0;
        $failed_list = [];
        $invalid_list = [];

        foreach ($recipients as $to) {
            $to = sanitize_email($to);
            if (!$to || !is_email($to)) {
                $invalid_list[] = $to;
                continue;
            }

            $headers = ['Content-Type: text/html; charset=UTF-8'];
            if ($reply_to) {
                $headers[] = 'Reply-To: ' . $reply_to;
            }

            $args = [
                'headers' => $headers,
                'charge_tokens' => true,
            ];
            if ($from_email) {
                $args['from_email'] = $from_email;
            }
            if ($from_name) {
                $args['from_name'] = $from_name;
            }

            // Per-recipient tracking token and HTML injection
            $tracking_token = md5( wp_generate_uuid4() );
            $tracked_html = $this->inject_tracking( $html, $tracking_token );

            $res = $router->send_email($to, $subject, $tracked_html, 'marketing', $args);
            $was_sent = (is_array($res) && !empty($res['success']));
            if (!$was_sent) {
                $failed_list[] = $to;
            } else {
                $sent++;
            }

            // Track each recipient
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->insert($sends, [
                'campaign_id' => $campaign_id,
                'user_id' => $user_id,
                'lead_email' => $to,
                'lead_name' => '',
                'subject' => $subject,
                'status' => $was_sent ? 'sent' : 'failed',
                'error' => $was_sent ? null : (in_array($to, $invalid_list, true) ? 'invalid_email' : 'send_failed'),
                'tracking_token' => $tracking_token,
                'created_at' => current_time('mysql'),
                'sent_at' => $was_sent ? current_time('mysql') : null,
            ]);

            // Log to CRM contact_interactions for unified timeline
            if ($was_sent) {
                $interactions_table = $wpdb->prefix . 'vmo_contact_interactions';
                $contact_id_hash = md5(strtolower(trim($to)) . '|' . $user_id);
                // Only log if contact exists
                $contacts_tbl = $wpdb->prefix . 'vmo_contacts';
                $contact_exists = $wpdb->get_var($wpdb->prepare("SELECT contact_id FROM $contacts_tbl WHERE user_id = %d AND contact_id = %s LIMIT 1", $user_id, $contact_id_hash));
                if ($contact_exists && $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $wpdb->esc_like($interactions_table)))) {
                    $wpdb->insert($interactions_table, [
                        'user_id'          => $user_id,
                        'contact_id'       => $contact_id_hash,
                        'interaction_type'  => 'email',
                        'subject'          => $subject,
                        'summary'          => 'Marketing email sent (Campaign #' . intval($campaign_id) . ')',
                        'created_at'       => current_time('mysql'),
                    ]);
                }
            }
        }

        $errors = array_values(array_unique(array_merge($failed_list, $invalid_list)));
        $failed = count($errors);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->update(
            $campaigns,
            [
                'status' => ($sent > 0 && $failed === 0) ? 'sent' : 'partial',
                'updated_at' => current_time('mysql'),
            ],
            ['id' => $campaign_id]
        );

        // Fire hook for auto-deduction and cross-system integration
        do_action('vmo_email_sent', $user_id, implode(',', array_slice($recipients, 0, 3)), $subject);

        // Create follow-up action in unified vmo_actions table
        $unified_actions = $wpdb->prefix . 'vmo_actions';
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $wpdb->esc_like($unified_actions))) === $unified_actions) {
            if ($failed > 0) {
                $wpdb->insert($unified_actions, [
                    'user_id'     => $user_id,
                    'title'       => 'Review failed email sends: ' . $subject,
                    'description' => $failed . ' recipient(s) failed in campaign #' . $campaign_id . '. Review and resend.',
                    'source'      => 'email_report',
                    'source_id'   => (string) $campaign_id,
                    'priority'    => 'high',
                    'category'    => 'follow-up',
                    'status'      => 'pending',
                    'due_date'    => date('Y-m-d', strtotime('+1 day')),
                    'created_at'  => current_time('mysql'),
                    'updated_at'  => current_time('mysql'),
                ]);
            }
            if ($sent > 5) {
                $wpdb->insert($unified_actions, [
                    'user_id'     => $user_id,
                    'title'       => 'Check email campaign results: ' . $subject,
                    'description' => 'Campaign sent to ' . $sent . ' recipients. Check open/click rates after 24h.',
                    'source'      => 'email_report',
                    'source_id'   => (string) $campaign_id,
                    'priority'    => 'normal',
                    'category'    => 'marketing',
                    'status'      => 'pending',
                    'due_date'    => date('Y-m-d', strtotime('+1 day')),
                    'created_at'  => current_time('mysql'),
                    'updated_at'  => current_time('mysql'),
                ]);
            }
        }

        wp_send_json_success([
            'sent' => $sent,
            'failed' => $failed,
            'failed_list' => $errors,
        ]);
    }

    public function get_overview() {
        if (!$this->verify_ajax_request()) {
            wp_send_json_error(['message' => 'Security check failed', 'status' => 'invalid_nonce']);
        }
        if (!is_user_logged_in()) {
            wp_send_json_error(['message' => 'Not logged in'], 401);
        }
        $user_id = get_current_user_id();

        global $wpdb; 
        $campaigns = $wpdb->prefix . 'vmo_email_campaigns';
        $sends     = $wpdb->prefix . 'vmo_email_sends';

        // Drafts count
        $drafts_count = intval($wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $campaigns WHERE user_id = %d AND status = 'draft'", $user_id)));

        // Last 30 days sends series
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(created_at) as d, COUNT(*) as c FROM $sends WHERE user_id = %d AND status = 'sent' AND created_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY d",
            $user_id
        ), ARRAY_A);
        $byDay = [];
        foreach ($rows as $r) { $byDay[$r['d']] = intval($r['c']); }
        // Build continuous 30-day series ending today
        $series_sent = [];
        $today = new DateTime('now', new DateTimeZone('UTC'));
        for ($i = 29; $i >= 0; $i--) {
            $d = clone $today; $d->modify("-$i day");
            $key = $d->format('Y-m-d');
            $series_sent[] = isset($byDay[$key]) ? $byDay[$key] : 0;
        }
        $sent_30 = array_sum($series_sent);

        // Opens series
        $open_rows = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(opened_at) as d, COUNT(*) as c FROM $sends WHERE user_id = %d AND opened_at IS NOT NULL AND opened_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY) GROUP BY DATE(opened_at) ORDER BY d",
            $user_id
        ), ARRAY_A);
        $openByDay = [];
        foreach ($open_rows as $r) { $openByDay[$r['d']] = intval($r['c']); }
        $series_open = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = clone $today; $d->modify("-$i day");
            $key = $d->format('Y-m-d');
            $series_open[] = isset($openByDay[$key]) ? $openByDay[$key] : 0;
        }

        // Clicks series
        $click_rows = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(clicked_at) as d, COUNT(*) as c FROM $sends WHERE user_id = %d AND clicked_at IS NOT NULL AND clicked_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY) GROUP BY DATE(clicked_at) ORDER BY d",
            $user_id
        ), ARRAY_A);
        $clickByDay = [];
        foreach ($click_rows as $r) { $clickByDay[$r['d']] = intval($r['c']); }
        $series_click = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = clone $today; $d->modify("-$i day");
            $key = $d->format('Y-m-d');
            $series_click[] = isset($clickByDay[$key]) ? $clickByDay[$key] : 0;
        }

        // Recent campaigns with basic stats
        $recent = $wpdb->get_results($wpdb->prepare("SELECT id, title, status, created_at FROM $campaigns WHERE user_id = %d ORDER BY created_at DESC LIMIT 25", $user_id), ARRAY_A);
        $campaigns_list = [];
        if ($recent) {
            // Preload send counts per campaign in one query
            $ids = array_map(function($r){ return intval($r['id']); }, $recent);
            if (!empty($ids)) {
                $in = implode(',', array_fill(0, count($ids), '%d'));
                $sql = $wpdb->prepare("SELECT campaign_id, SUM(status='sent') as sent FROM $sends WHERE campaign_id IN ($in) GROUP BY campaign_id", ...$ids);
                $sendCounts = [];
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                foreach ($wpdb->get_results($sql, ARRAY_A) as $row) { $sendCounts[intval($row['campaign_id'])] = intval($row['sent']); }

                $sql_opens = $wpdb->prepare("SELECT campaign_id, SUM(opened_at IS NOT NULL) as cnt FROM $sends WHERE campaign_id IN ($in) GROUP BY campaign_id", ...$ids);
                $openCounts = [];
                foreach ($wpdb->get_results($sql_opens, ARRAY_A) as $row) { $openCounts[intval($row['campaign_id'])] = intval($row['cnt']); }

                $sql_clicks = $wpdb->prepare("SELECT campaign_id, SUM(clicked_at IS NOT NULL) as cnt FROM $sends WHERE campaign_id IN ($in) GROUP BY campaign_id", ...$ids);
                $clickCounts = [];
                foreach ($wpdb->get_results($sql_clicks, ARRAY_A) as $row) { $clickCounts[intval($row['campaign_id'])] = intval($row['cnt']); }
            } else { $sendCounts = []; $openCounts = []; $clickCounts = []; }
            foreach ($recent as $r) {
                $cid = intval($r['id']);
                $campaigns_list[] = [
                    'id' => $cid,
                    'title' => $r['title'] ?: 'Campaign',
                    'status' => $r['status'] ?: 'draft',
                    'created_at' => $r['created_at'],
                    'sent' => isset($sendCounts[$cid]) ? $sendCounts[$cid] : 0,
                    'open' => isset($openCounts[$cid]) ? $openCounts[$cid] : 0,
                    'click' => isset($clickCounts[$cid]) ? $clickCounts[$cid] : 0,
                ];
            }
        }

        wp_send_json_success([
            'drafts_count' => $drafts_count,
            'sent_30' => $sent_30,
            'opened_30' => array_sum($series_open),
            'clicked_30' => array_sum($series_click),
            'sent_series' => $series_sent,
            'open_series' => $series_open,
            'click_series' => $series_click,
            'campaigns' => $campaigns_list,
        ]);
    }
}

// Initialize
// Instantiation is handled in the main plugin file via plugins_loaded hook.
// This prevents premature loading issues.
