<?php
/**
 * Manager for Content Strategy persistence and synchronization
 *
 * @package    Aysa
 * @subpackage Aysa/includes
 */

if (!defined('ABSPATH')) {
    exit;
}

class Aysa_Content_Strategy_Manager {

    private $table_name;
    private $db_version = '1.7';
    private $cron_hook = 'aysa_content_planner_tick';
    private $daily_summary_hook = 'aysa_content_daily_summary_tick';
    private $image_retry_hook = 'aysa_article_image_retry_tick';

    public function __construct() {
        global $wpdb;
        $this->table_name = esc_sql($wpdb->prefix . 'aysa_content_strategies');
        
        // Register hooks
        add_action('init', array($this, 'check_table_exists'), 1);
        add_action('wp_ajax_aysa_save_strategies', array($this, 'ajax_save_strategies'));
        add_action('wp_ajax_aysa_get_strategies', array($this, 'ajax_get_strategies'));
        add_action('wp_ajax_aysa_delete_strategy', array($this, 'ajax_delete_strategy'));
        add_action('wp_ajax_aysa_plan_content_schedule', array($this, 'ajax_plan_content_schedule'));
        add_action('wp_ajax_aysa_get_content_plan_status', array($this, 'ajax_get_content_plan_status'));
        add_action('wp_ajax_aysa_get_content_calendar', array($this, 'ajax_get_content_calendar'));
        add_action('wp_ajax_aysa_get_content_daily_summary_status', array($this, 'ajax_get_content_daily_summary_status'));
        add_action('wp_ajax_aysa_run_content_daily_summary_now', array($this, 'ajax_run_content_daily_summary_now'));
        add_action('wp_ajax_aysa_agent8_notifications_fetch', array($this, 'ajax_agent8_notifications_fetch'));
        add_action('wp_ajax_aysa_agent8_notifications_mark_read', array($this, 'ajax_agent8_notifications_mark_read'));
        add_action('wp_ajax_aysa_agent8_create_test_notification', array($this, 'ajax_agent8_create_test_notification'));
        add_action('wp_ajax_aysa_run_content_planner_now', array($this, 'ajax_run_content_planner_now'));
        add_action('wp_ajax_aysa_content_strategy_associate_missing', array($this, 'ajax_content_strategy_associate_missing'));
        add_action('before_delete_post', array($this, 'handle_post_deletion'));
        add_action('admin_notices', array($this, 'render_daily_publish_notice'));
        add_action('admin_menu', array($this, 'register_agent8_notifications_page'), 30);
        add_filter('wp_mail', array($this, 'append_aysa_signature_to_wp_mail'));
        add_filter('cron_schedules', array($this, 'register_cron_schedules'));
        add_action('init', array($this, 'ensure_cron_scheduled'));
        add_action($this->cron_hook, array($this, 'process_planned_content_queue'));
        add_action($this->daily_summary_hook, array($this, 'send_daily_content_summary'));
        add_action($this->image_retry_hook, array($this, 'process_pending_article_images'));
        add_action('aysa_process_planned_content_now', array($this, 'process_planned_content_queue'));
        add_action('aysa_process_article_images_now', array($this, 'process_pending_article_images'), 10, 1);
        add_action('aysa_generate_existing_strategy_post', array($this, 'handle_generate_existing_strategy_post'), 10, 1);
        register_deactivation_hook(AYSA_PLUGIN_FILE, array($this, 'clear_scheduled_hooks'));
    }

    private function resolve_bound_project_id_from_request() {
        $bound_project_id = intval(get_option('aysa_project_id', 0));
        // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
        $requested_project_id = isset($_POST['project_id'])
            // phpcs:ignore WordPress.Security.NonceVerification.Missing -- Reviewed callback inventory: this request read is downstream of the handler nonce and capability guard, a shared guarded delegate, or the authenticated WordPress core save/query flow.
            ? intval((string) wp_unslash($_POST['project_id']))
            : 0;

        if ($bound_project_id <= 0 || ($requested_project_id > 0 && $requested_project_id !== $bound_project_id)) {
            return 0;
        }
        return $bound_project_id;
    }

    private function content_reset_option_name($project_id) {
        return 'aysa_content_strategy_hard_reset_' . intval($project_id);
    }

    private function is_content_strategy_hard_reset($project_id) {
        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return false;
        }

        return !empty(get_option($this->content_reset_option_name($project_id), ''));
    }

    /**
     * Check if table exists and create if not
     */
    public function check_table_exists() {
        if (get_option('aysa_content_strategies_db_version') !== $this->db_version) {
            $this->create_table();
        }
        $this->ensure_agent8_communications_table();
        $backfill_version = (string) get_option('aysa_content_strategies_backfill_version', '');
        if ($backfill_version !== $this->db_version) {
            $this->backfill_missing_pages_strategy_fields();
            $this->backfill_editorial_identity_fields();
            update_option('aysa_content_strategies_backfill_version', $this->db_version);
        }
    }

    public function append_aysa_signature_to_wp_mail($args) {
        if (!is_array($args)) {
            return $args;
        }
        $message = (string) ($args['message'] ?? '');
        $headers = $args['headers'] ?? array();
        $headers_text = is_array($headers) ? implode("\n", $headers) : (string) $headers;
        if (stripos($headers_text, 'X-AYSA-Mail-Signature: 1') === false) {
            return $args;
        }
        if (strpos($message, "Agent 8\nAYSA AI SEO Agent") !== false || strpos($message, 'AYSA AI SEO Agent') !== false) {
            return $args;
        }

        if (is_array($headers)) {
            $args['headers'] = array_values(array_filter($headers, static function ($header) {
                return stripos((string) $header, 'X-AYSA-Mail-Signature:') !== 0;
            }));
            $headers_text = implode("\n", $args['headers']);
        } else {
            $args['headers'] = trim((string) preg_replace('/^X-AYSA-Mail-Signature:\s*1\s*$/mi', '', (string) $headers));
            $headers_text = (string) $args['headers'];
        }

        if (stripos($headers_text, 'text/html') !== false) {
            $signature = '<br><br>Agent 8<br>AYSA AI SEO Agent<br><br>Ai nevoie de ajutor?<br><a href="mailto:support@aysa.ai">support@aysa.ai</a><br><a href="tel:+40722237373">0722 237 373</a><br><a href="https://aysa.ai">https://aysa.ai</a>';
            $args['message'] = rtrim($message) . $signature;
            return $args;
        }

        $args['message'] = rtrim($message) . "\n\n" . "Agent 8\nAYSA AI SEO Agent\n\nAi nevoie de ajutor?\nsupport@aysa.ai\n0722 237 373\nhttps://aysa.ai";
        return $args;
    }

    /**
     * Create the database table
     */
    public function create_table() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $this->table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            project_id mediumint(9) NOT NULL,
            title text NOT NULL,
            keywords text,
            intent varchar(50),
            associated_page text,
            strategy_type varchar(50),
            strategy_meta longtext,
            canonical_topic varchar(191) DEFAULT NULL,
            content_fingerprint char(64) DEFAULT NULL,
            recommended_page_type varchar(30) DEFAULT NULL,
            content_type varchar(30) DEFAULT 'post',
            intent_layer varchar(32) DEFAULT NULL,
            journey_stage varchar(32) DEFAULT NULL,
            persona_key varchar(64) DEFAULT NULL,
            locality_key varchar(64) DEFAULT NULL,
            seasonality_key varchar(64) DEFAULT NULL,
            content_goal varchar(64) DEFAULT NULL,
            execution_stage varchar(30) DEFAULT 'planned',
            status varchar(50) DEFAULT 'proposed',
            duplicate_of_strategy_id bigint(20) UNSIGNED DEFAULT NULL,
            superseded_by_strategy_id bigint(20) UNSIGNED DEFAULT NULL,
            post_id bigint(20) UNSIGNED DEFAULT NULL,
            scheduled_for datetime DEFAULT NULL,
            generated_at datetime DEFAULT NULL,
            automation_status varchar(50) DEFAULT NULL,
            automation_attempts tinyint(3) UNSIGNED DEFAULT 0,
            plan_batch varchar(64) DEFAULT NULL,
            last_error text,
            automation_updated_at datetime DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY project_id (project_id),
            KEY post_id (post_id),
            KEY scheduled_for (scheduled_for),
            KEY automation_status (automation_status),
            KEY plan_batch (plan_batch),
            KEY content_type (content_type),
            KEY execution_stage (execution_stage),
            KEY canonical_topic (canonical_topic),
            KEY content_fingerprint (content_fingerprint),
            KEY project_status (project_id, status)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        update_option('aysa_content_strategies_db_version', $this->db_version);
    }

    private function agent8_communications_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'aysa_agent8_communications';
    }

    private function ensure_agent8_communications_table() {
        global $wpdb;
        $table = esc_sql($this->agent8_communications_table_name());
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            project_id bigint(20) UNSIGNED NOT NULL,
            conversation_id varchar(128) DEFAULT NULL,
            event_type varchar(80) NOT NULL,
            title varchar(255) NOT NULL,
            message text NOT NULL,
            payload longtext NULL,
            severity varchar(32) DEFAULT 'info',
            importance varchar(32) DEFAULT 'normal',
            channels_required text NULL,
            recipient varchar(191) DEFAULT NULL,
            provider varchar(64) DEFAULT NULL,
            provider_message_id varchar(191) DEFAULT NULL,
            email_status varchar(32) DEFAULT 'not_required',
            in_app_status varchar(32) DEFAULT 'unread',
            last_error text NULL,
            read_at datetime DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY project_created (project_id, created_at),
            KEY project_read (project_id, in_app_status),
            KEY email_status (email_status)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    /**
     * AJAX: Save generated strategies
     */
    public function ajax_save_strategies() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Reviewed release input boundary: the guarded callback preserves structured input only until its field-specific validation or normalizer, before persistence, output, SQL, or a broker request.
        $strategies = isset($_POST['strategies']) ? json_decode((string) wp_unslash($_POST['strategies']), true) : array();
        $project_id = $this->resolve_bound_project_id_from_request();
        $strategy_type = isset($_POST['strategy_type']) ? sanitize_text_field(wp_unslash($_POST['strategy_type'])) : 'unknown';

        if (empty($strategies) || empty($project_id)) {
            wp_send_json_error('Invalid data');
        }

        global $wpdb;
        $saved_count = 0;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $existing_rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT title FROM $this->table_name
             WHERE project_id = %d
               AND COALESCE(automation_status, '') <> 'deleted'",
            $project_id
        ), ARRAY_A);
        $topic_pool = array();
        foreach ((array) $existing_rows as $row) {
            $existing_title = sanitize_text_field((string) ($row['title'] ?? ''));
            if ($existing_title !== '') {
                $topic_pool[] = $existing_title;
            }
        }

        $strategies = $this->interleave_strategies_by_content_type($strategies);

        foreach ($strategies as $strategy) {
            $title = sanitize_text_field($strategy['title'] ?? '');
            if ($title === '') {
                continue;
            }
            if ($this->is_duplicate_topic_in_pool($title, $topic_pool)) {
                continue;
            }
            $associated_pages_input = $strategy['associatedPages']
                ?? $strategy['AssociatedPages']
                ?? $strategy['associated_pages']
                ?? ($strategy['associatedPage'] ?? '');
            $associated_pages = $this->normalize_associated_pages($associated_pages_input);
            $associated_page = $this->serialize_associated_pages($associated_pages);
            $keywords = $strategy['targetKeywords']
                ?? $strategy['TargetKeywords']
                ?? $strategy['target_keywords']
                ?? array();
            $strategy_meta = array();
            if (in_array($strategy_type, array('competitor_derived_titles', 'competitor_titles', 'competitor'), true)) {
                $strategy_meta = array(
                    'competitor_title' => sanitize_text_field((string) ($strategy['competitor_title'] ?? '')),
                    'competitor_domain' => sanitize_text_field((string) ($strategy['competitor_domain'] ?? '')),
                    'competitor_url' => esc_url_raw((string) ($strategy['competitor_url'] ?? '')),
                    'why_better' => sanitize_textarea_field((string) ($strategy['why_better'] ?? '')),
                    'strategy' => sanitize_key((string) ($strategy['strategy'] ?? '')),
                    'improvement_type' => sanitize_key((string) ($strategy['improvement_type'] ?? '')),
                    'angle' => sanitize_text_field((string) ($strategy['angle'] ?? '')),
                );
                $strategy_meta = array_filter($strategy_meta, static function($value) {
                    return !($value === '' || $value === null);
                });
            }

            // Check duplicates using title + strategy type + associated page.
            // Title-only dedupe was too aggressive and reduced generated sets (e.g. 6 -> 4).
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $exists = $wpdb->get_var($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT id FROM $this->table_name WHERE project_id = %d AND title = %s AND strategy_type = %s AND COALESCE(associated_page, '') = %s",
                $project_id,
                $title,
                $strategy_type,
                $associated_page
            ));

            if (!$exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
                $wpdb->insert(
                    $this->table_name,
                    array(
                        'project_id' => $project_id,
                        'title' => $title,
                        'keywords' => is_array($keywords) ? wp_json_encode($keywords) : sanitize_text_field((string) $keywords),
                        'intent' => sanitize_text_field($strategy['intent'] ?? 'I'),
                        'associated_page' => $associated_page,
                        'strategy_type' => $strategy_type,
                        'strategy_meta' => !empty($strategy_meta) ? wp_json_encode($strategy_meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
                        'recommended_page_type' => null,
                        'content_type' => 'post',
                        'execution_stage' => 'planned',
                        'status' => 'proposed',
                        'automation_status' => null,
                        'automation_attempts' => 0,
                        'scheduled_for' => null,
                        'generated_at' => null,
                        'plan_batch' => null,
                        'last_error' => null,
                        'automation_updated_at' => current_time('mysql')
                    )
                );
                $saved_count++;
                $topic_pool[] = $title;
            }
        }

        wp_send_json_success(array('saved' => $saved_count));
    }

    /**
     * Normalize associated page values to avoid invalid placeholders (e.g. "http://-").
     */
    private function normalize_associated_page($value) {
        $raw = trim((string) $value);
        if ($raw === '' || $raw === '-' || strtolower($raw) === 'n/a' || strpos($raw, 'http://-') === 0 || strpos($raw, 'https://-') === 0) {
            return '';
        }

        if (preg_match('#^https?://#i', $raw)) {
            return esc_url_raw($raw);
        }

        if (strpos($raw, '/') === 0) {
            return esc_url_raw(home_url($raw));
        }

        return '';
    }

    private function normalize_associated_pages($value) {
        $values = is_array($value) ? $value : array($value);
        $seen = array();
        $clean = array();

        foreach ($values as $item) {
            $normalized = $this->normalize_associated_page($item);
            if ($normalized === '') {
                continue;
            }
            $key = strtolower($normalized);
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $clean[] = $normalized;
        }

        return $clean;
    }

    private function serialize_associated_pages($pages) {
        $clean = $this->normalize_associated_pages($pages);
        if (empty($clean)) {
            return '';
        }
        if (count($clean) === 1) {
            return $clean[0];
        }
        return wp_json_encode(array_values($clean));
    }

    private function parse_associated_pages($value) {
        $raw = trim((string) $value);
        if ($raw === '') {
            return array();
        }

        if (strpos($raw, '[') === 0) {
            $decoded = json_decode($raw, true);
            if (is_array($decoded)) {
                return $this->normalize_associated_pages($decoded);
            }
        }

        return $this->normalize_associated_pages($raw);
    }

    private function parse_strategy_keywords($value) {
        if (is_array($value)) {
            return array_values(array_filter(array_map(static function($item) {
                return trim((string) $item);
            }, $value)));
        }

        $raw = trim((string) $value);
        if ($raw === '') {
            return array();
        }

        if (($raw[0] ?? '') === '[' || ($raw[0] ?? '') === '{') {
            $decoded = json_decode($raw, true);
            if (is_array($decoded)) {
                return $this->parse_strategy_keywords($decoded);
            }
        }

        if (strpos($raw, ',') !== false) {
            return array_values(array_filter(array_map('trim', explode(',', $raw))));
        }

        return array($raw);
    }

    private function parse_strategy_meta_payload($value) {
        if (is_array($value)) {
            return $value;
        }

        $raw = trim((string) $value);
        if ($raw === '') {
            return array();
        }

        $decoded = json_decode($raw, true);
        return is_array($decoded) ? $decoded : array();
    }

    private function normalize_strategy_row_for_ui(array $row, array $meta_map = array()) {
        $id = intval($row['id'] ?? 0);
        $keywords = $this->parse_strategy_keywords($row['keywords'] ?? '');
        $associated_pages = $this->parse_associated_pages($row['associated_page'] ?? '');
        $associated_page = $associated_pages[0] ?? '';
        $strategy_type = sanitize_key((string) ($row['strategy_type'] ?? 'unknown'));
        $strategy_meta = $this->parse_strategy_meta_payload($row['strategy_meta'] ?? '');
        $meta_row = ($id > 0 && isset($meta_map[$id]) && is_array($meta_map[$id])) ? $meta_map[$id] : array();

        $scheduled_for = (string) ($row['scheduled_for'] ?? '');
        $scheduled_for_local = $scheduled_for;
        if ($scheduled_for !== '') {
            $ts = strtotime($scheduled_for);
            if ($ts) {
                $scheduled_for_local = wp_date('Y-m-d H:i:s', $ts);
            }
        }

        return array(
            'id' => $id,
            'title' => (string) ($row['title'] ?? ''),
            'Title' => (string) ($row['title'] ?? ''),
            'targetKeywords' => $keywords,
            'TargetKeywords' => $keywords,
            'target_keywords' => $keywords,
            'keywords' => $keywords,
            'intent' => (string) ($row['intent'] ?? 'I'),
            'Intent' => (string) ($row['intent'] ?? 'I'),
            'associatedPages' => $associated_pages,
            'AssociatedPages' => $associated_pages,
            'associated_pages' => $associated_pages,
            'associatedPage' => $associated_page,
            'AssociatedPage' => $associated_page,
            'associated_page' => $associated_page,
            'strategy_type' => $strategy_type,
            'strategyType' => $strategy_type,
            'recommended_page_type' => (string) ($row['recommended_page_type'] ?? ''),
            'content_type' => (string) ($row['content_type'] ?? ''),
            'execution_stage' => (string) ($row['execution_stage'] ?? ''),
            'status' => (string) ($row['status'] ?? 'proposed'),
            'automation_status' => (string) ($row['automation_status'] ?? ''),
            'post_id' => !empty($row['post_id']) ? intval($row['post_id']) : null,
            'scheduled_for' => $scheduled_for,
            'scheduled_for_local' => $scheduled_for_local,
            'generated_at' => (string) ($row['generated_at'] ?? ''),
            'meta_h1' => (string) ($meta_row['h1'] ?? ''),
            'meta_description' => (string) ($meta_row['meta_description'] ?? ''),
            'meta_warning' => (string) ($meta_row['warning'] ?? ''),
            'meta_source' => '',
            'competitor_title' => (string) ($strategy_meta['competitor_title'] ?? ''),
            'competitor_domain' => (string) ($strategy_meta['competitor_domain'] ?? ''),
            'competitor_url' => (string) ($strategy_meta['competitor_url'] ?? ''),
            'why_better' => (string) ($strategy_meta['why_better'] ?? ''),
        );
    }

    private function build_calendar_events_from_rows(array $rows) {
        $events = array();
        foreach ($rows as $row) {
            $scheduled_for = (string) ($row['scheduled_for'] ?? '');
            $scheduled_ts = $scheduled_for !== '' ? strtotime($scheduled_for) : false;
            if (!$scheduled_ts) {
                continue;
            }

            $automation_status = strtolower((string) ($row['automation_status'] ?? ''));
            $status = strtolower((string) ($row['status'] ?? ''));
            $type = ($status === 'generated' || $automation_status === 'generated') ? 'published' : 'scheduled';
            $post_id = intval($row['post_id'] ?? 0);
            $post = $post_id > 0 ? get_post($post_id) : null;
            $post_status = ($post && !is_wp_error($post)) ? sanitize_text_field((string) $post->post_status) : '';
            $edit_url = $post_id > 0 ? admin_url('post.php?post=' . $post_id . '&action=edit') : '';
            $public_url = ($post_id > 0 && $post && !is_wp_error($post)) ? get_permalink($post_id) : '';

            $events[] = array(
                'id' => 'local-' . intval($row['id'] ?? 0),
                'strategy_id' => intval($row['id'] ?? 0),
                'title' => ($post_status === 'publish' && $post_id > 0 && $post) ? get_the_title($post_id) : (string) ($row['title'] ?? 'Untitled'),
                'type' => $type,
                'strategy_type' => (string) ($row['strategy_type'] ?? 'unknown'),
                'content_type' => (string) ($row['content_type'] ?? ''),
                'intent' => (string) ($row['intent'] ?? '-'),
                'status' => $automation_status !== '' ? $automation_status : $status,
                'datetime' => gmdate('c', $scheduled_ts),
                'timestamp' => $scheduled_ts * 1000,
                'scheduled_for' => $scheduled_for,
                'scheduled_for_local' => wp_date('Y-m-d H:i:s', $scheduled_ts),
                'post_id' => $post_id > 0 ? $post_id : null,
                'post_status' => $post_status,
                'edit_url' => $edit_url,
                'public_url' => $public_url ?: '',
            );
        }

        usort($events, static function($left, $right) {
            return intval($left['timestamp'] ?? 0) <=> intval($right['timestamp'] ?? 0);
        });

        return $events;
    }

    private function build_today_summary_from_events(array $events) {
        $today = wp_date('Y-m-d');
        $scheduled_items = array();
        $published_items = array();

        foreach ($events as $event) {
            $date = '';
            if (!empty($event['scheduled_for_local'])) {
                $date = substr((string) $event['scheduled_for_local'], 0, 10);
            } elseif (!empty($event['scheduled_for'])) {
                $date = substr((string) $event['scheduled_for'], 0, 10);
            }
            if ($date !== $today) {
                continue;
            }

            $payload = array(
                'title' => (string) ($event['title'] ?? '-'),
                'status' => (string) ($event['status'] ?? ''),
                'edit_url' => (string) ($event['edit_url'] ?? ''),
                'public_url' => (string) ($event['public_url'] ?? ''),
                'post_id' => !empty($event['post_id']) ? intval($event['post_id']) : null,
            );
            if ((string) ($event['type'] ?? '') === 'published') {
                $published_items[] = $payload;
            } else {
                $scheduled_items[] = $payload;
            }
        }

        return array(
            'date' => $today,
            'scheduled_count' => count($scheduled_items),
            'published_count' => count($published_items),
            'scheduled_items' => array_slice($scheduled_items, 0, 20),
            'published_items' => array_slice($published_items, 0, 20),
        );
    }

    private function load_keyword_mapping_workspace_snapshot($project_id) {
        global $wpdb;

        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return null;
        }

        $table = esc_sql($wpdb->prefix . 'aysa_keyword_v2_snapshots');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
        if ($table_exists !== $table) {
            return null;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $payload_json = $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT payload_json FROM {$table} WHERE project_id = %d AND tab_key = %s LIMIT 1",
            $project_id,
            'keyword_mapping_v2'
        ));
        if (!is_string($payload_json) || $payload_json === '') {
            return null;
        }

        $payload = json_decode($payload_json, true);
        return is_array($payload) ? $payload : null;
    }

    private function normalize_keyword_token($value) {
        return trim(strtolower(remove_accents((string) $value)));
    }

    private function extract_strategy_keywords($raw_keywords) {
        $items = array();
        if (is_array($raw_keywords)) {
            $items = $raw_keywords;
        } else {
            $raw = trim((string) $raw_keywords);
            if ($raw === '') {
                return array();
            }
            $decoded = json_decode($raw, true);
            if (is_array($decoded)) {
                $items = $decoded;
            } else {
                $items = preg_split('/\s*,\s*/', $raw);
            }
        }

        $seen = array();
        $keywords = array();
        foreach ((array) $items as $item) {
            $kw = trim((string) $item);
            if ($kw === '') {
                continue;
            }
            $key = $this->normalize_keyword_token($kw);
            if ($key === '' || isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $keywords[] = $kw;
        }

        return $keywords;
    }

    private function infer_associated_pages_from_keywords($project_id, $raw_keywords) {
        global $wpdb;

        $keywords = $this->extract_strategy_keywords($raw_keywords);
        if (empty($keywords)) {
            return array();
        }

        $normalized = array();
        foreach ($keywords as $kw) {
            $normalized[$this->normalize_keyword_token($kw)] = true;
        }

        $candidates = array();
        $snapshot_table = esc_sql($wpdb->prefix . 'aysa_keyword_v2_snapshots');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $snapshot_table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $snapshot_table));
        if ($snapshot_table_exists === $snapshot_table) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $workspace_payload_json = $wpdb->get_var($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT payload_json
" .
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
                "                 FROM {$snapshot_table}
                 WHERE project_id = %d
                   AND tab_key = %s
                 LIMIT 1",
                intval($project_id),
                'keyword_mapping_v2'
            ));
            if (is_string($workspace_payload_json) && $workspace_payload_json !== '') {
                $workspace_payload = json_decode($workspace_payload_json, true);
                $workspace_clusters = isset($workspace_payload['clusters']) && is_array($workspace_payload['clusters'])
                    ? $workspace_payload['clusters']
                    : array();

                foreach ($workspace_clusters as $cluster) {
                    if (!is_array($cluster)) {
                        continue;
                    }

                    $cluster_keyword_map = array();
                    $primary_keyword = is_array($cluster['primary_keyword'] ?? null)
                        ? (string) ($cluster['primary_keyword']['keyword'] ?? '')
                        : (string) ($cluster['primary_keyword'] ?? '');
                    $primary_keyword = trim($primary_keyword);
                    if ($primary_keyword !== '') {
                        $cluster_keyword_map[$this->normalize_keyword_token($primary_keyword)] = true;
                    }

                    foreach ((array) ($cluster['secondary_keywords'] ?? array()) as $secondary_row) {
                        if (!is_array($secondary_row)) {
                            continue;
                        }
                        $secondary_keyword = trim((string) ($secondary_row['keyword'] ?? ''));
                        if ($secondary_keyword === '') {
                            continue;
                        }
                        $cluster_keyword_map[$this->normalize_keyword_token($secondary_keyword)] = true;
                    }

                    if (empty(array_intersect_key($cluster_keyword_map, $normalized))) {
                        continue;
                    }

                    $matched_page = $cluster['matched_page'] ?? null;
                    if (is_array($matched_page)) {
                        $value = $this->normalize_associated_page($matched_page['url'] ?? '');
                        if ($value !== '') {
                            $candidates[] = $value;
                        }
                    }

                    $page_matches = isset($cluster['explainability']['page_matches']) && is_array($cluster['explainability']['page_matches'])
                        ? $cluster['explainability']['page_matches']
                        : array();
                    foreach ($page_matches as $page_match) {
                        if (!is_array($page_match)) {
                            continue;
                        }
                        $value = $this->normalize_associated_page($page_match['url'] ?? '');
                        if ($value !== '') {
                            $candidates[] = $value;
                        }
                    }
                }
            }
        }

        $association_option_key = 'aysa_keyword_discovery_local_associations_' . intval($project_id);
        $local_associations = get_option($association_option_key, array());
        if (is_array($local_associations)) {
            foreach ($local_associations as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $row_kw = $this->normalize_keyword_token($row['keyword'] ?? '');
                if ($row_kw === '' || !isset($normalized[$row_kw])) {
                    continue;
                }
                $value = $this->normalize_associated_page($row['value'] ?? '');
                if ($value !== '') {
                    $candidates[] = $value;
                }
            }
        }

        $metrics_table = esc_sql($wpdb->prefix . 'aysa_keyword_metrics');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $metrics_table));
        if ($table_exists === $metrics_table) {
            $placeholders = implode(',', array_fill(0, count($keywords), '%s'));
            $query = $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT target_url
" .
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
                "                 FROM {$metrics_table}
                 WHERE project_id = %d
" .
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
                "                   AND keyword IN ({$placeholders})
                   AND target_url IS NOT NULL
                   AND target_url <> ''",
                array_merge(array(intval($project_id)), $keywords)
            );
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query: table identifiers and structure are internal, and request-derived values are prepared before execution. Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $rows = $wpdb->get_col($query);
            if (is_array($rows)) {
                foreach ($rows as $target_url) {
                    $value = $this->normalize_associated_page($target_url);
                    if ($value !== '') {
                        $candidates[] = $value;
                    }
                }
            }

            // Fallback: normalized keyword matching for cases where strategy keyword text
            // differs slightly from discovery rows (spacing/diacritics/formatting).
            if (empty($candidates)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
                $all_rows = $wpdb->get_results($wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                    "SELECT keyword, target_url
" .
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
                    "                     FROM {$metrics_table}
                     WHERE project_id = %d
                       AND target_url IS NOT NULL
                       AND target_url <> ''",
                    intval($project_id)
                ), ARRAY_A);

                if (is_array($all_rows)) {
                    foreach ($all_rows as $metric_row) {
                        $metric_kw = $this->normalize_keyword_token($metric_row['keyword'] ?? '');
                        if ($metric_kw === '' || !isset($normalized[$metric_kw])) {
                            continue;
                        }
                        $value = $this->normalize_associated_page($metric_row['target_url'] ?? '');
                        if ($value !== '') {
                            $candidates[] = $value;
                        }
                    }
                }
            }
        }

        return $this->normalize_associated_pages($candidates);
    }

    private function build_topic_signature($title) {
        $title = strtolower(trim(remove_accents((string) $title)));
        if ($title === '') {
            return '';
        }

        $title = preg_replace('/[^a-z0-9\s]+/u', ' ', $title);
        $parts = preg_split('/\s+/', $title);
        if (!is_array($parts)) {
            return '';
        }

        $stopwords = array(
            'si','sau','de','la','in','din','pe','cu','pentru','cele','cel','cea','cei','un','o','a','ale','al',
            'the','and','for','with','from','into','your','how','top','guide','ghid','complet','best','celemai',
            '2024','2025','2026'
        );
        $stop = array_fill_keys($stopwords, true);
        $tokens = array();

        foreach ($parts as $p) {
            $p = trim($p);
            if ($p === '' || isset($stop[$p])) {
                continue;
            }
            if (strlen($p) < 3) {
                continue;
            }
            $tokens[$p] = true;
        }

        if (empty($tokens)) {
            return '';
        }

        $keys = array_keys($tokens);
        sort($keys);
        return implode(' ', $keys);
    }

    private function topic_similarity_score($a, $b) {
        $sa = $this->build_topic_signature($a);
        $sb = $this->build_topic_signature($b);
        if ($sa === '' || $sb === '') {
            return 0.0;
        }
        if ($sa === $sb) {
            return 1.0;
        }

        $ta = array_fill_keys(explode(' ', $sa), true);
        $tb = array_fill_keys(explode(' ', $sb), true);
        $inter = array_intersect_key($ta, $tb);
        $union = $ta + $tb;
        $jaccard = count($union) > 0 ? (count($inter) / count($union)) : 0.0;

        return (float) $jaccard;
    }

    private function is_duplicate_topic_in_pool($title, array $pool) {
        $title_clean = trim((string) $title);
        if ($title_clean === '') {
            return false;
        }

        foreach ($pool as $existing) {
            $existing_clean = trim((string) $existing);
            if ($existing_clean === '') {
                continue;
            }
            if (strcasecmp($title_clean, $existing_clean) === 0) {
                return true;
            }
            $score = $this->topic_similarity_score($title_clean, $existing_clean);
            if ($score >= 0.80) {
                return true;
            }
        }

        return false;
    }

    /**
     * AJAX: Get stored strategies
     */
    public function ajax_get_strategies() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        
        if (empty($project_id)) {
            wp_send_json_error('Invalid Project ID');
        }

        if ($this->is_content_strategy_hard_reset($project_id)) {
            wp_send_json_success(array());
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $results = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT * FROM $this->table_name
             WHERE project_id = %d
               AND COALESCE(automation_status, '') <> 'deleted'
               AND duplicate_of_strategy_id IS NULL
               AND superseded_by_strategy_id IS NULL
             ORDER BY
               CASE
                 WHEN COALESCE(automation_status, '') IN ('processing','queued','retry') THEN 0
                 WHEN COALESCE(automation_status, '') = 'generated' THEN 1
                 ELSE 2
               END ASC,
               CASE
                 WHEN scheduled_for IS NULL THEN 1
                 ELSE 0
               END ASC,
               scheduled_for ASC,
               created_at DESC",
            $project_id
        ), ARRAY_A);

        $meta_map = array();
        $meta_table = esc_sql($wpdb->prefix . 'aysa_missing_page_meta');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $meta_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $meta_table));
        if ($meta_exists === $meta_table) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $meta_rows = $wpdb->get_results($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT strategy_id, seo_title, h1, meta_description, warning, updated_at
" .
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
                "                 FROM {$meta_table}
                 WHERE project_id = %d",
                $project_id
            ), ARRAY_A);
            if (is_array($meta_rows)) {
                foreach ($meta_rows as $meta_row) {
                    $sid = intval($meta_row['strategy_id'] ?? 0);
                    if ($sid <= 0) {
                        continue;
                    }
                    $meta_map[$sid] = $meta_row;
                }
            }
        }

        foreach ($results as &$row) {
            // Check if post still exists if post_id is set
            if (!empty($row['post_id'])) {
                $post_status = get_post_status($row['post_id']);
                if (!$post_status) {
                    // Post was deleted outside of our hook? Update status
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
                    $wpdb->update(
                        $this->table_name,
                        array('post_id' => NULL, 'status' => 'proposed'),
                        array('id' => $row['id'])
                    );
                    $row['post_id'] = NULL;
                    $row['status'] = 'proposed';
                } else {
                    $row['post_status'] = $post_status;
                }
            }
        }
        unset($row);

        $items = array();
        foreach ((array) $results as $row) {
            $items[] = $this->normalize_strategy_row_for_ui($row, $meta_map);
        }

        wp_send_json_success($items);
    }
    
    /**
     * AJAX: Delete a strategy
     */
    public function ajax_delete_strategy() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Insufficient permissions');
            return;
        }
        
        $id = isset($_POST['id']) ? intval(wp_unslash($_POST['id'])) : 0;
        $project_id = intval(get_option('aysa_project_id', 0));
        
        if (!$id || $project_id <= 0) {
            wp_send_json_error('Invalid ID');
            return;
        }
        
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $deleted = $wpdb->delete(
            $this->table_name,
            array('id' => $id, 'project_id' => $project_id),
            array('%d', '%d')
        );
        if ($deleted === false) {
            wp_send_json_error('Strategy deletion failed');
            return;
        }
        if ($deleted === 0) {
            wp_send_json_error('Strategy not found for this project');
            return;
        }
        
        wp_send_json_success();
    }

    /**
     * Handle WP Post deletion to sync status
     */
    public function handle_post_deletion($post_id) {
        global $wpdb;
        
        // Check if this post is linked to a strategy
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $strategy = $wpdb->get_row($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id FROM $this->table_name WHERE post_id = %d",
            $post_id
        ));

        if ($strategy) {
            // Reset the strategy status instead of deleting it, so the user can regenerate if they want
            // Or delete it? The user said "if I delete content... it should not remain in frontend".
            // This implies removing the *generated* status, or removing the entry entirely?
            // "nu mai ramana in frontendul pluginului nostru" -> usually means remove the entry.
            // BUT, if it was a "proposed" strategy that became a post, maybe they want it back as "proposed"?
            // Let's assume they want the connection broken. If they delete the post, the strategy goes back to 'proposed' or is deleted?
            // User said: "if I delete the content ... it should not remain in the frontend".
            // This sounds like: The entry in the table (which represents the article) should be gone.
            // However, these are "Strategies" (Ideas). If I delete the article, maybe I want the Idea back?
            // Let's stick to: Update status to 'proposed' and remove post_id.
            // Wait, "nu mai ramana" means "should not remain".
            // If the strategy entry ITSELF represents the article in the user's mind, then deleting the article should delete the strategy entry?
            // Let's implement: Reset to 'proposed' and NULL post_id. This is safer.
            // If the user wants to delete the strategy suggestion, they can do that from the table.
            
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update(
                $this->table_name,
                array(
                    'post_id' => NULL,
                    'status' => 'proposed',
                    'automation_status' => 'deleted',
                    'automation_updated_at' => current_time('mysql')
                ),
                array('post_id' => $post_id)
            );
        }
    }
    
    /**
     * Update strategy with post ID (to be called from create post handler)
     */
    public static function link_post_to_strategy($strategy_id, $post_id) {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aysa_content_strategies');
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $wpdb->update(
            $table_name,
            array(
                'post_id' => $post_id,
                'status' => 'generated',
                'automation_status' => 'generated',
                'execution_stage' => 'published',
                'generated_at' => current_time('mysql'),
                'automation_updated_at' => current_time('mysql')
            ),
            array('id' => $strategy_id)
        );
    }
    
    /**
     * Find strategy by title (fallback)
     */
    public static function link_post_by_title($title, $post_id) {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aysa_content_strategies');
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $wpdb->update(
            $table_name,
            array(
                'post_id' => $post_id,
                'status' => 'generated',
                'automation_status' => 'generated',
                'execution_stage' => 'published',
                'generated_at' => current_time('mysql'),
                'automation_updated_at' => current_time('mysql')
            ),
            array('title' => $title)
        );
    }

    public function register_cron_schedules($schedules) {
        if (!isset($schedules['aysa_every_five_minutes'])) {
            $schedules['aysa_every_five_minutes'] = array(
                'interval' => 300,
                'display' => __('Every 5 Minutes (AYSA)', 'aysaaiseo')
            );
        }
        return $schedules;
    }

    public function ensure_cron_scheduled() {
        if (!wp_next_scheduled($this->cron_hook)) {
            wp_schedule_event(time() + 60, 'aysa_every_five_minutes', $this->cron_hook);
        }
        if (!wp_next_scheduled($this->image_retry_hook)) {
            wp_schedule_event(time() + 120, 'aysa_every_five_minutes', $this->image_retry_hook);
        }
        $daily_next = wp_next_scheduled($this->daily_summary_hook);
        if ($daily_next && wp_date('H:i', $daily_next, $this->daily_summary_timezone()) !== '22:00') {
            wp_clear_scheduled_hook($this->daily_summary_hook);
            $daily_next = false;
        }
        if (!$daily_next) {
            wp_schedule_event($this->next_daily_summary_timestamp(), 'daily', $this->daily_summary_hook);
        }
    }

    public function clear_scheduled_hooks() {
        wp_clear_scheduled_hook($this->cron_hook);
        wp_clear_scheduled_hook($this->daily_summary_hook);
        wp_clear_scheduled_hook($this->image_retry_hook);
        wp_clear_scheduled_hook('aysa_process_planned_content_now');
        wp_clear_scheduled_hook('aysa_process_article_images_now');
    }

    private function daily_summary_timezone() {
        return new DateTimeZone('Europe/Bucharest');
    }

    private function next_daily_summary_timestamp() {
        $tz = $this->daily_summary_timezone();
        $now = new DateTimeImmutable('now', $tz);
        $next = $now->setTime(22, 0, 0);
        if ($next <= $now) {
            $next = $next->modify('+1 day');
        }
        return $next->getTimestamp();
    }

    public function ajax_plan_content_schedule() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        // Weekly cadence (long-term planning): default 3-4 articles per week.
        // Keep backward compatibility with legacy min_per_day/max_per_day inputs.
        $min_per_week_input = isset($_POST['min_per_week']) ? absint(wp_unslash($_POST['min_per_week'])) : absint(wp_unslash($_POST['min_per_day'] ?? 3));
        $max_per_week_input = isset($_POST['max_per_week']) ? absint(wp_unslash($_POST['max_per_week'])) : absint(wp_unslash($_POST['max_per_day'] ?? 4));
        $min_per_week = max(1, intval($min_per_week_input));
        $max_per_week = max($min_per_week, intval($max_per_week_input));
        // Product requirement: maximum 3-4 per week.
        $min_per_week = min(max($min_per_week, 3), 4);
        $max_per_week = min(max($max_per_week, $min_per_week), 4);

        if ($project_id <= 0) {
            wp_send_json_error('Invalid project ID');
        }
        $strategy_scope = sanitize_key((string) wp_unslash($_POST['strategy_scope'] ?? 'all'));
        if (!in_array($strategy_scope, array('all', 'missing_pages', 'content_only'), true)) {
            $strategy_scope = 'all';
        }
        if ($strategy_scope !== 'content_only') {
            $this->backfill_missing_pages_strategy_fields();
        }
        global $wpdb;
        // Plan only new/unprocessed strategies.
        // Do NOT re-queue already scheduled/queued items, otherwise each click re-plans the same rows.
        // Allow retry of previously blocked (no association) entries after associations are fixed.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $strategies = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT * FROM $this->table_name
             WHERE project_id = %d
              AND (%s = 'all'
                   OR (%s = 'missing_pages' AND strategy_type = 'missing_pages')
                   OR (%s = 'content_only' AND strategy_type <> 'missing_pages'))
              AND post_id IS NULL
              AND (
                    status = 'proposed'
                    OR (status = 'blocked' AND COALESCE(automation_status, '') = 'blocked_no_association')
               )
             ORDER BY
               CASE WHEN status = 'proposed' THEN 0 ELSE 1 END ASC,
               created_at ASC",
            $project_id,
            $strategy_scope,
            $strategy_scope,
            $strategy_scope
        ), ARRAY_A);

        $force_generate_now = !empty($_POST['force_generate_now']);
        $redistribute_pending = !isset($_POST['redistribute_pending']) || !empty($_POST['redistribute_pending']);
        $pending_queue = array();
        if ($redistribute_pending && !$force_generate_now) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $pending_queue = $wpdb->get_results($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT id, content_type, scheduled_for
" .
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
                "                 FROM $this->table_name
                 WHERE project_id = %d
                   AND (%s = 'all'
                        OR (%s = 'missing_pages' AND strategy_type = 'missing_pages')
                        OR (%s = 'content_only' AND strategy_type <> 'missing_pages'))
                   AND status = 'scheduled'
                   AND post_id IS NULL
                   AND COALESCE(automation_status, '') IN ('queued','retry')
                 ORDER BY scheduled_for ASC, id ASC",
                $project_id,
                $strategy_scope,
                $strategy_scope,
                $strategy_scope
            ), ARRAY_A);
        }

        if (empty($strategies) && empty($pending_queue)) {
            wp_send_json_success(array(
                'planned' => 0,
                'without_association' => 0,
                'redistributed' => 0,
                'batch_id' => null,
                'forced_now' => $force_generate_now ? 1 : 0,
                'strategy_scope' => $strategy_scope,
                'planned_by_type' => array(),
                'weekly_range' => array('min' => $min_per_week, 'max' => $max_per_week),
                'message' => 'No additional content ideas are ready for planning right now.'
            ));
        }

        $mapping_workspace = $this->load_keyword_mapping_workspace_snapshot($project_id);
        $payload_strategies = array();
        foreach ((array) $strategies as $strategy) {
            if (!is_array($strategy)) {
                continue;
            }
            $payload_strategies[] = array(
                'id' => intval($strategy['id'] ?? 0),
                'keywords' => $strategy['keywords'] ?? '',
                'associated_page' => $strategy['associated_page'] ?? '',
                'associated_pages' => $this->parse_associated_pages($strategy['associated_page'] ?? ''),
                'content_type' => sanitize_key((string) ($strategy['content_type'] ?? 'post')),
            );
        }

        $result = $this->broker_request('POST', '/v1/content-strategy/plan', array(
            'project_id' => $project_id,
            'strategy_scope' => $strategy_scope,
            'strategies' => $payload_strategies,
            'pending_queue' => array_values((array) $pending_queue),
            'mapping_workspace' => is_array($mapping_workspace) ? $mapping_workspace : array(),
            'min_per_week' => $min_per_week,
            'max_per_week' => $max_per_week,
            'force_generate_now' => $force_generate_now ? 1 : 0,
            'redistribute_pending' => $redistribute_pending ? 1 : 0,
        ), 90);
        if (is_wp_error($result)) {
            wp_send_json_error($result->get_error_message());
        }
        if (!is_array($result) || empty($result['ok'])) {
            wp_send_json_error(is_array($result) ? (string) ($result['error'] ?? 'content_strategy_plan_failed') : 'content_strategy_plan_failed');
        }

        $batch_id = sanitize_text_field((string) ($result['batch_id'] ?? ''));
        $redistributed_count = 0;
        foreach ((array) ($result['redistributed_updates'] ?? array()) as $row) {
            if (!is_array($row) || empty($row['id'])) {
                continue;
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update(
                $this->table_name,
                array(
                    'scheduled_for' => sanitize_text_field((string) ($row['scheduled_for'] ?? '')),
                    'plan_batch' => $batch_id,
                    'automation_updated_at' => current_time('mysql'),
                ),
                array('id' => intval($row['id']))
            );
            $redistributed_count++;
        }

        $planned_count = 0;
        $planned_by_type = is_array($result['planned_by_type'] ?? null) ? $result['planned_by_type'] : array();
        foreach ((array) ($result['strategy_updates'] ?? array()) as $row) {
            if (!is_array($row) || empty($row['id'])) {
                continue;
            }
            $update_data = array(
                'status' => sanitize_key((string) ($row['status'] ?? 'scheduled')),
                'scheduled_for' => sanitize_text_field((string) ($row['scheduled_for'] ?? '')),
                'automation_status' => sanitize_key((string) ($row['automation_status'] ?? 'queued')),
                'automation_attempts' => intval($row['automation_attempts'] ?? 0),
                'plan_batch' => $batch_id,
                'last_error' => null,
                'automation_updated_at' => current_time('mysql'),
            );
            $associated_pages = isset($row['associated_pages']) && is_array($row['associated_pages'])
                ? $this->normalize_associated_pages($row['associated_pages'])
                : array();
            if (!empty($associated_pages)) {
                $update_data['associated_page'] = $this->serialize_associated_pages($associated_pages);
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update($this->table_name, $update_data, array('id' => intval($row['id'])));
            $planned_count++;
        }

        $without_association = intval($result['without_association'] ?? 0);

        // Trigger background worker pass; keep AJAX response fast.
        wp_schedule_single_event(time() + ($force_generate_now ? 1 : 15), 'aysa_process_planned_content_now');

        wp_send_json_success(array(
            'planned' => $planned_count,
            'without_association' => $without_association,
            'redistributed' => $redistributed_count,
            'batch_id' => $batch_id,
            'forced_now' => $force_generate_now ? 1 : 0,
            'strategy_scope' => $strategy_scope,
            'planned_by_type' => $planned_by_type,
            'weekly_range' => array('min' => $min_per_week, 'max' => $max_per_week),
            'message' => sprintf(
                'Planned %d strategy items with weekly cadence %d-%d/week. Redistributed %d queued items. %d currently without associations (will generate now and can be linked later).%s',
                $planned_count,
                $min_per_week,
                $max_per_week,
                $redistributed_count,
                $without_association,
                $force_generate_now ? ' Forced immediate generation enabled.' : ''
            )
        ));
    }

    /**
     * AJAX: Associate missing strategy rows using existing keyword->URL signals.
     */
    public function ajax_content_strategy_associate_missing() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        if ($project_id <= 0) {
            wp_send_json_error('Invalid project ID');
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id, keywords, associated_page, status, automation_status
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM $this->table_name
             WHERE project_id = %d
               AND post_id IS NULL
               AND COALESCE(automation_status, '') <> 'deleted'
             ORDER BY id ASC",
            $project_id
        ), ARRAY_A);

        if (empty($rows)) {
            $this->track_action_usage(
                $project_id,
                'aysa_content_strategy_associate_missing',
                'content_strategy_associate_missing',
                array(
                    'module' => 'content_strategy',
                    'checked' => 0,
                    'updated' => 0,
                    'unblocked' => 0,
                    'unresolved' => 0,
                    'source' => 'empty_rows',
                ),
                5,
                '/v1/content-strategy/associate-missing'
            );
            wp_send_json_success(array(
                'checked' => 0,
                'updated' => 0,
                'unblocked' => 0,
                'unresolved' => 0,
                'message' => 'No unassigned content ideas need page matching right now.'
            ));
        }

        $mapping_workspace = $this->load_keyword_mapping_workspace_snapshot($project_id);
        $result = $this->broker_request('POST', '/v1/content-strategy/associate-missing', array(
            'project_id' => $project_id,
            'rows' => $rows,
            'mapping_workspace' => is_array($mapping_workspace) ? $mapping_workspace : array(),
        ), 60);
        if (is_wp_error($result)) {
            wp_send_json_error($result->get_error_message());
        }

        $checked = intval($result['checked'] ?? 0);
        $updated = 0;
        $unblocked = intval($result['unblocked'] ?? 0);
        $unresolved = intval($result['unresolved'] ?? 0);
        $updates = isset($result['updates']) && is_array($result['updates']) ? $result['updates'] : array();

        foreach ($updates as $update_row) {
            if (!is_array($update_row)) {
                continue;
            }
            $id = intval($update_row['id'] ?? 0);
            if ($id <= 0) {
                continue;
            }

            $update_data = array(
                'associated_page' => $this->serialize_associated_pages($update_row['associated_pages'] ?? array()),
                'automation_updated_at' => current_time('mysql'),
            );
            if (array_key_exists('status', $update_row) && $update_row['status'] !== null) {
                $update_data['status'] = sanitize_text_field((string) $update_row['status']);
            }
            if (array_key_exists('automation_status', $update_row)) {
                $automation_status = trim((string) ($update_row['automation_status'] ?? ''));
                $update_data['automation_status'] = ($automation_status === '') ? null : sanitize_text_field($automation_status);
            }
            if (array_key_exists('last_error', $update_row)) {
                $last_error = trim((string) ($update_row['last_error'] ?? ''));
                $update_data['last_error'] = ($last_error === '') ? null : sanitize_text_field($last_error);
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update(
                $this->table_name,
                $update_data,
                array('id' => $id)
            );
            $updated++;
        }

        $this->track_action_usage(
            $project_id,
            'aysa_content_strategy_associate_missing',
            'content_strategy_associate_missing',
            array(
                'module' => 'content_strategy',
                'checked' => $checked,
                'updated' => $updated,
                'unblocked' => $unblocked,
                'unresolved' => $unresolved,
                'source' => 'resolved_rows',
            ),
            5,
            '/v1/content-strategy/associate-missing'
        );

        wp_send_json_success(array(
            'checked' => $checked,
            'updated' => $updated,
            'unblocked' => $unblocked,
            'unresolved' => $unresolved,
            'message' => sprintf(
                'Associated %d strategy rows (%d unresolved, %d moved back to proposed).',
                $updated,
                $unresolved,
                $unblocked
            )
        ));
    }

    public function ajax_get_content_plan_status() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        if ($project_id <= 0) {
            wp_send_json_error('Invalid project ID');
        }

        if ($this->is_content_strategy_hard_reset($project_id)) {
            wp_send_json_success(array(
                'statuses' => array(),
                'next' => null,
                'cron_next_run' => wp_next_scheduled($this->cron_hook)
            ));
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $status_rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT COALESCE(automation_status, 'none') AS s, COUNT(*) AS c
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM $this->table_name
             WHERE project_id = %d
             GROUP BY COALESCE(automation_status, 'none')",
            $project_id
        ), ARRAY_A);

        $status_map = array();
        foreach ($status_rows as $row) {
            $status_map[(string) $row['s']] = intval($row['c']);
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $next_item = $wpdb->get_row($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id, title, scheduled_for
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM $this->table_name
             WHERE project_id = %d
               AND status = 'scheduled'
               AND automation_status IN ('queued', 'retry')
               AND scheduled_for IS NOT NULL
             ORDER BY scheduled_for ASC
             LIMIT 1",
            $project_id
        ), ARRAY_A);

        wp_send_json_success(array(
            'statuses' => $status_map,
            'next' => is_array($next_item) ? $next_item : null,
            'cron_next_run' => wp_next_scheduled($this->cron_hook)
        ));
    }

    public function ajax_get_content_calendar() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        if ($project_id <= 0) {
            wp_send_json_error('Invalid project ID');
        }

        if ($this->is_content_strategy_hard_reset($project_id)) {
            wp_send_json_success(array(
                'events' => array(),
                'today' => null,
            ));
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id, title, strategy_type, recommended_page_type, content_type, execution_stage, intent, status, post_id, scheduled_for, generated_at, automation_status
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM $this->table_name
             WHERE project_id = %d
               AND COALESCE(automation_status, '') <> 'deleted'
               AND duplicate_of_strategy_id IS NULL
               AND superseded_by_strategy_id IS NULL
             ORDER BY COALESCE(scheduled_for, created_at) ASC",
            $project_id
        ), ARRAY_A);

        foreach ((array) $rows as &$row) {
            $recommended_page_type = $this->normalize_recommended_page_type_label((string) ($row['recommended_page_type'] ?? ''));
            if ($recommended_page_type === '' && (string) ($row['strategy_type'] ?? '') === 'missing_pages') {
                $recommended_page_type = $this->infer_recommended_page_type_from_title((string) ($row['title'] ?? ''));
            }
            $content_type = sanitize_key((string) ($row['content_type'] ?? ''));
            if ($content_type === '') {
                $content_type = $this->map_recommended_page_type_to_content_type($recommended_page_type);
            }
            $execution_stage = sanitize_key((string) ($row['execution_stage'] ?? ''));
            if ($execution_stage === '') {
                $execution_stage = ((string) ($row['status'] ?? '') === 'generated') ? 'published' : 'planned';
            }

            $row['recommended_page_type'] = $recommended_page_type;
            $row['content_type'] = $content_type;
            $row['execution_stage'] = $execution_stage;
        }
        unset($row);

        $events = $this->build_calendar_events_from_rows((array) $rows);
        $today = $this->build_today_summary_from_events($events);

        wp_send_json_success(array(
            'events' => $events,
            'today' => $today,
            'timezone' => wp_timezone_string() ? wp_timezone_string() : 'UTC',
        ));
    }

    private function normalize_agent8_communication_row($row) {
        $payload = array();
        if (!empty($row['payload'])) {
            $decoded = json_decode((string) $row['payload'], true);
            $payload = is_array($decoded) ? $decoded : array();
        }
        $channels = array();
        if (!empty($row['channels_required'])) {
            $decoded = json_decode((string) $row['channels_required'], true);
            $channels = is_array($decoded) ? array_values(array_map('strval', $decoded)) : array();
        }

        return array(
            'event_id' => intval($row['id'] ?? 0),
            'project_id' => intval($row['project_id'] ?? 0),
            'conversation_id' => (string) ($row['conversation_id'] ?? ''),
            'event_type' => (string) ($row['event_type'] ?? ''),
            'title' => (string) ($row['title'] ?? ''),
            'message' => (string) ($row['message'] ?? ''),
            'payload' => $payload,
            'severity' => (string) ($row['severity'] ?? 'info'),
            'importance' => (string) ($row['importance'] ?? 'normal'),
            'channels_required' => $channels,
            'recipient' => (string) ($row['recipient'] ?? ''),
            'provider' => (string) ($row['provider'] ?? ''),
            'provider_message_id' => (string) ($row['provider_message_id'] ?? ''),
            'email_status' => (string) ($row['email_status'] ?? ''),
            'in_app_status' => (string) ($row['in_app_status'] ?? ''),
            'last_error' => (string) ($row['last_error'] ?? ''),
            'read_at' => (string) ($row['read_at'] ?? ''),
            'created_at' => (string) ($row['created_at'] ?? ''),
            'updated_at' => (string) ($row['updated_at'] ?? ''),
        );
    }

    private function create_agent8_communication_event(array $args) {
        global $wpdb;
        $this->ensure_agent8_communications_table();

        $project_id = intval($args['project_id'] ?? get_option('aysa_project_id', 0));
        if ($project_id <= 0) {
            return array('ok' => false, 'error' => 'missing_project_id');
        }

        $channels = isset($args['channels_required']) && is_array($args['channels_required'])
            ? array_values(array_unique(array_map('sanitize_key', $args['channels_required'])))
            : array('in_app');
        if (!in_array('in_app', $channels, true)) {
            $channels[] = 'in_app';
        }

        $recipient = sanitize_email((string) ($args['recipient'] ?? ''));
        $email_required = in_array('email', $channels, true);
        $email_status = $email_required ? 'pending' : 'not_required';
        $last_error = '';
        if ($email_required && ($recipient === '' || !is_email($recipient))) {
            $email_status = 'failed';
            $last_error = 'missing_or_invalid_recipient';
        }

        $payload = isset($args['payload']) && is_array($args['payload']) ? $args['payload'] : array();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $inserted = $wpdb->insert(
            $this->agent8_communications_table_name(),
            array(
                'project_id' => $project_id,
                'conversation_id' => sanitize_text_field((string) ($args['conversation_id'] ?? '')),
                'event_type' => sanitize_key((string) ($args['event_type'] ?? 'agent8_event')),
                'title' => sanitize_text_field((string) ($args['title'] ?? 'Agent8 update')),
                'message' => sanitize_textarea_field((string) ($args['message'] ?? '')),
                'payload' => wp_json_encode($payload),
                'severity' => sanitize_key((string) ($args['severity'] ?? 'info')),
                'importance' => sanitize_key((string) ($args['importance'] ?? 'normal')),
                'channels_required' => wp_json_encode($channels),
                'recipient' => $recipient,
                'provider' => $email_required ? 'PostmarkMailer' : '',
                'provider_message_id' => '',
                'email_status' => $email_status,
                'in_app_status' => 'unread',
                'last_error' => $last_error,
                'created_at' => current_time('mysql'),
                'updated_at' => current_time('mysql'),
            )
        );
        if (!$inserted) {
            return array('ok' => false, 'error' => 'insert_failed');
        }

        $event_id = intval($wpdb->insert_id);
        if ($email_required && $email_status === 'pending') {
            $subject = sanitize_text_field((string) ($args['email_subject'] ?? $args['title'] ?? 'Agent8 update'));
            $body = (string) ($args['email_body'] ?? $args['message'] ?? '');
            $html_body = (string) ($args['email_html'] ?? '');
            if ($html_body === '') {
                $html_body = $this->agent8_plain_text_to_email_html($body);
            }
            $send_result = $this->send_agent8_email_via_postmark($recipient, $subject, $body, $html_body);
            $email_status = !empty($send_result['ok']) ? 'sent' : 'failed';
            $last_error = !empty($send_result['ok']) ? '' : (string) ($send_result['error'] ?? 'postmark_send_failed');
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update(
                $this->agent8_communications_table_name(),
                array(
                    'email_status' => $email_status,
                    'provider' => 'PostmarkMailer',
                    'provider_message_id' => (string) ($send_result['message_id'] ?? ''),
                    'last_error' => $last_error,
                    'updated_at' => current_time('mysql'),
                ),
                array('id' => $event_id)
            );
        }

        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is initialized from $wpdb->prefix plus a fixed plugin-owned literal; no request input reaches the identifier. Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $row = $wpdb->get_row($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT * FROM {$this->agent8_communications_table_name()} WHERE id = %d",
            $event_id
        ), ARRAY_A);

        return array(
            'ok' => true,
            'event' => $this->normalize_agent8_communication_row(is_array($row) ? $row : array('id' => $event_id)),
        );
    }

    private function agent8_plain_text_to_email_html($text) {
        $html = esc_html((string) $text);
        $html = preg_replace(
            '~(https?://[^\s<]+)~',
            '<a href="$1">$1</a>',
            $html
        );
        $html = preg_replace(
            '~([A-Z0-9._%+\-]+@[A-Z0-9.\-]+\.[A-Z]{2,})~i',
            '<a href="mailto:$1">$1</a>',
            $html
        );

        return '<div style="font-family:Arial,sans-serif;font-size:16px;line-height:1.55;color:#1f2937;">' . nl2br($html, false) . '</div>';
    }

    private function send_agent8_email_via_postmark($recipient, $subject, $text_body, $html_body = '') {
        if (!class_exists('Aysa_API_Config')) {
            require_once AYSA_PLUGIN_DIR . 'includes/config/aysa-api-config.php';
        }
        if (!class_exists('Aysa_API_Config')) {
            return array('ok' => false, 'error' => 'api_config_missing', 'provider' => 'PostmarkMailer');
        }

        $secret_key = trim((string) get_option('aysa_user_secret_key', ''));
        if ($secret_key === '') {
            $secret_key = trim((string) get_option('aysa_active_access_key', ''));
        }
        if ($secret_key === '') {
            return array('ok' => false, 'error' => 'access_key_missing', 'provider' => 'PostmarkMailer');
        }

        $url = rtrim(Aysa_API_Config::get_base_url(), '/') . '/api/Agent8Communication/sendEmail';
        $response = wp_remote_post($url, array(
            'timeout' => 30,
            'headers' => array('Content-Type' => 'application/json'),
            'body' => wp_json_encode(array(
                'secretKey' => $secret_key,
                'domainFromWordpress' => wp_parse_url(home_url(), PHP_URL_HOST),
                'to_email' => sanitize_email((string) $recipient),
                'subject' => sanitize_text_field((string) $subject),
                'text_body' => (string) $text_body,
                'html_body' => (string) $html_body,
            )),
        ));
        if (is_wp_error($response)) {
            return array('ok' => false, 'error' => $response->get_error_message(), 'provider' => 'PostmarkMailer');
        }

        $data = json_decode((string) wp_remote_retrieve_body($response), true);
        if (!is_array($data)) {
            return array('ok' => false, 'error' => 'invalid_postmark_api_response', 'provider' => 'PostmarkMailer');
        }
        if (empty($data['provider'])) {
            $data['provider'] = 'PostmarkMailer';
        }
        return $data;
    }

    public function ajax_agent8_notifications_fetch() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');
        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        global $wpdb;
        $this->ensure_agent8_communications_table();
        $project_id = $this->resolve_bound_project_id_from_request();
        $limit = isset($_POST['limit']) ? max(1, min(50, intval(wp_unslash($_POST['limit'])))) : 12;
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is initialized from $wpdb->prefix plus a fixed plugin-owned literal; no request input reaches the identifier. Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT * FROM {$this->agent8_communications_table_name()} WHERE project_id = %d ORDER BY id DESC LIMIT %d",
            $project_id,
            $limit
        ), ARRAY_A);
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is initialized from $wpdb->prefix plus a fixed plugin-owned literal; no request input reaches the identifier. Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $unread = intval($wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT COUNT(*) FROM {$this->agent8_communications_table_name()} WHERE project_id = %d AND in_app_status = 'unread'",
            $project_id
        )));

        wp_send_json_success(array(
            'notifications' => array_map(array($this, 'normalize_agent8_communication_row'), is_array($rows) ? $rows : array()),
            'unread_count' => $unread,
        ));
    }

    public function ajax_agent8_notifications_mark_read() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');
        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        global $wpdb;
        $this->ensure_agent8_communications_table();
        $project_id = $this->resolve_bound_project_id_from_request();
        $event_id = isset($_POST['event_id']) ? intval(wp_unslash($_POST['event_id'])) : 0;
        if ($event_id > 0) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update(
                $this->agent8_communications_table_name(),
                array('in_app_status' => 'read', 'read_at' => current_time('mysql'), 'updated_at' => current_time('mysql')),
                array('id' => $event_id, 'project_id' => $project_id)
            );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update(
                $this->agent8_communications_table_name(),
                array('in_app_status' => 'read', 'read_at' => current_time('mysql'), 'updated_at' => current_time('mysql')),
                array('project_id' => $project_id, 'in_app_status' => 'unread')
            );
        }

        wp_send_json_success(array('marked_read' => true));
    }

    public function ajax_agent8_create_test_notification() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        $mode = sanitize_key((string) wp_unslash($_POST['mode'] ?? 'sent'));
        $recipient = $mode === 'failed' ? '' : $this->content_summary_recipient($project_id);
        $result = $this->create_agent8_communication_event(array(
            'project_id' => $project_id,
            'event_type' => $mode === 'failed' ? 'test_email_failed' : 'test_agent8_event',
            'title' => $mode === 'failed' ? 'Agent8 email delivery needs attention' : 'Agent8 communication test',
            'message' => $mode === 'failed'
                ? 'I created this in-product event, but email delivery could not start because the recipient is missing or invalid.'
                : 'This is a test Agent8 communication. It is visible here and email delivery was attempted.',
            'severity' => $mode === 'failed' ? 'warning' : 'info',
            'importance' => 'important',
            'channels_required' => array('in_app', 'email'),
            'recipient' => $recipient,
            'email_subject' => 'Agent8 communication test',
            'email_body' => 'This is a test Agent8 communication event.',
            'payload' => array('test_mode' => $mode),
        ));

        if (empty($result['ok'])) {
            wp_send_json_error($result);
        }
        wp_send_json_success($result);
    }

    public function register_agent8_notifications_page() {
        add_submenu_page(
            null,
            aysa_interface_text('Notificări Agent8', 'Agent8 Notifications'),
            aysa_interface_text('Notificări', 'Notifications'),
            'manage_options',
            'aysa-agent8-notifications',
            array($this, 'render_agent8_notifications_page')
        );
    }

    public function render_agent8_notifications_page() {
        if (!current_user_can('manage_options')) {
            wp_die(esc_html__('Permission denied', 'aysaaiseo'));
        }
        global $wpdb;
        $this->ensure_agent8_communications_table();
        $project_id = intval(get_option('aysa_project_id', 0));
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is initialized from $wpdb->prefix plus a fixed plugin-owned literal; no request input reaches the identifier. Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT * FROM {$this->agent8_communications_table_name()} WHERE project_id = %d ORDER BY id DESC LIMIT 100",
            $project_id
        ), ARRAY_A);
        $tr = static function ($romanian, $english) {
            return aysa_interface_text($romanian, $english);
        };
        ?>
        <div class="wrap">
            <h1><?php echo esc_html($tr('Notificări Agent8', 'Agent8 Notifications')); ?></h1>
            <p><?php echo esc_html($tr('Istoricul canonic al comunicărilor Agent8. Evenimentele importante sunt înregistrate aici, iar trimiterea prin email este încercată prin serviciul configurat.', 'Canonical Agent8 communication history. Important events are logged here, and email delivery is attempted through the configured service.')); ?></p>
            <table class="widefat striped">
                <thead>
                    <tr>
                        <th><?php echo esc_html($tr('Dată', 'Time')); ?></th>
                        <th><?php echo esc_html($tr('Eveniment', 'Event')); ?></th>
                        <th><?php echo esc_html($tr('Mesaj', 'Message')); ?></th>
                        <th><?php echo esc_html($tr('Destinatar', 'Recipient')); ?></th>
                        <th><?php echo esc_html($tr('Serviciu', 'Provider')); ?></th>
                        <th><?php echo esc_html($tr('Email', 'Email')); ?></th>
                        <th><?php echo esc_html($tr('În aplicație', 'In app')); ?></th>
                        <th><?php echo esc_html($tr('Eroare', 'Error')); ?></th>
                    </tr>
                </thead>
                <tbody>
                <?php if (empty($rows)) : ?>
                    <tr><td colspan="8"><?php echo esc_html($tr('Nu există încă evenimente de comunicare Agent8.', 'No Agent8 communication events yet.')); ?></td></tr>
                <?php else : ?>
                    <?php foreach ($rows as $row) : $item = $this->normalize_agent8_communication_row($row); ?>
                        <tr>
                            <td><?php echo esc_html($item['created_at']); ?></td>
                            <td><strong><?php echo esc_html($item['title']); ?></strong><br><code><?php echo esc_html($item['event_type']); ?></code></td>
                            <td><?php echo esc_html($item['message']); ?></td>
                            <td><?php echo esc_html($item['recipient']); ?></td>
                            <td><?php echo esc_html($item['provider']); ?><br><?php echo esc_html($item['provider_message_id']); ?></td>
                            <td><?php echo esc_html($item['email_status']); ?></td>
                            <td><?php echo esc_html($item['in_app_status']); ?></td>
                            <td><?php echo esc_html($item['last_error']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
                </tbody>
            </table>
        </div>
        <?php
    }

    public function ajax_get_content_daily_summary_status() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('edit_posts')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        if ($project_id <= 0) {
            wp_send_json_error('Invalid project ID');
        }

        $next_run = wp_next_scheduled($this->daily_summary_hook);
        $last_result = get_option($this->daily_summary_last_result_option_name($project_id), array());
        if (!is_array($last_result)) {
            $last_result = array();
        }

        wp_send_json_success(array(
            'timezone' => $this->daily_summary_timezone()->getName(),
            'recipient' => $this->content_summary_recipient($project_id),
            'recipient_configured' => $this->content_summary_recipient($project_id) !== '',
            'next_run' => $next_run ? wp_date('Y-m-d H:i:s', $next_run, $this->daily_summary_timezone()) : null,
            'next_run_unix' => $next_run ? intval($next_run) : null,
            'last_result' => $last_result,
        ));
    }

    public function ajax_run_content_daily_summary_now() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error('Permission denied');
        }

        $project_id = $this->resolve_bound_project_id_from_request();
        if ($project_id <= 0) {
            wp_send_json_error('Invalid project ID');
        }

        $result = $this->send_daily_content_summary($project_id, null, true);
        if (empty($result['ok'])) {
            wp_send_json_error($result);
        }

        wp_send_json_success($result);
    }

    public function render_daily_publish_notice() {
        if (!is_admin() || !current_user_can('edit_posts')) {
            return;
        }

        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Reviewed callback inventory: this read-only/core lifecycle request is authenticated by WordPress or an enclosing guard and performs no unapproved write.
        $page = isset($_GET['page']) ? sanitize_text_field((string) wp_unslash($_GET['page'])) : '';
        if (strpos($page, 'aysa') !== 0) {
            return;
        }

        $project_id = intval(get_option('aysa_project_id', 0));
        if ($project_id <= 0) {
            return;
        }

        $user_id = get_current_user_id();
        if ($user_id <= 0) {
            return;
        }

        $last_result = get_option($this->daily_summary_last_result_option_name($project_id), array());
        if (is_array($last_result) && (string) ($last_result['status'] ?? '') === 'failed') {
            ?>
            <div class="notice notice-error">
                <p>
                    <strong><?php esc_html_e('Agent8 communication issue:', 'aysaaiseo'); ?></strong>
                    <?php echo esc_html('Daily summary email failed: ' . (string) ($last_result['error'] ?? 'unknown_error')); ?>
                    <a href="<?php echo esc_url(admin_url('admin.php?page=aysa-agent8-notifications')); ?>"><?php esc_html_e('Open notifications', 'aysaaiseo'); ?></a>
                </p>
            </div>
            <?php
        }

        $today_key = wp_date('Y-m-d');
        $shown_key = 'aysa_daily_publish_notice_shown_' . $project_id;
        $already_shown = (string) get_user_meta($user_id, $shown_key, true);
        if ($already_shown === $today_key) {
            return;
        }

        $today = $this->get_today_upcoming_publications($project_id);
        if (empty($today)) {
            update_user_meta($user_id, $shown_key, $today_key);
            return;
        }

        update_user_meta($user_id, $shown_key, $today_key);
        $titles = array_map(function($item) {
            return sanitize_text_field((string) ($item['title'] ?? ''));
        }, array_slice($today, 0, 3));
        $list = implode(' | ', array_filter($titles));
        $remaining = max(0, count($today) - count($titles));
        ?>
        <div class="notice notice-info is-dismissible">
            <p>
                <strong><?php esc_html_e('Aysa Content Calendar:', 'aysaaiseo'); ?></strong>
                <?php
                printf(
                    /* translators: 1: number of scheduled items, 2: item title list, 3: remaining count suffix. */
                    esc_html__('Today scheduled publications: %1$d item(s): %2$s%3$s', 'aysaaiseo'),
                    count($today),
                    esc_html($list),
                    $remaining > 0 ? esc_html(' +' . $remaining) : ''
                );
                ?>
            </p>
        </div>
        <?php
    }

    private function get_today_upcoming_publications($project_id) {
        global $wpdb;
        $tz = wp_timezone();
        $today_start = new DateTimeImmutable('today', $tz);
        $today_end = $today_start->modify('+1 day');
        $now_mysql = current_time('mysql');

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id, title, scheduled_for, automation_status
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM $this->table_name
             WHERE project_id = %d
               AND status = 'scheduled'
               AND scheduled_for IS NOT NULL
               AND scheduled_for >= %s
               AND scheduled_for < %s
               AND scheduled_for >= %s
               AND COALESCE(automation_status, '') NOT IN ('generated','failed')
             ORDER BY scheduled_for ASC",
            intval($project_id),
            $today_start->format('Y-m-d H:i:s'),
            $today_end->format('Y-m-d H:i:s'),
            $now_mysql
        ), ARRAY_A);

        return is_array($rows) ? $rows : array();
    }

    private function content_summary_recipient($project_id) {
        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return '';
        }

        // Temporary operator override while validating daily summary delivery.
        return 'marius@aysa.ai';

        $email = sanitize_email((string) get_option('aysa_account_email', ''));
        if ($email !== '' && is_email($email)) {
            return $email;
        }

        return $this->resolve_content_summary_account_email();
    }

    private function resolve_content_summary_account_email() {
        $secret_key = trim((string) get_option('aysa_user_secret_key', ''));
        if ($secret_key === '') {
            return '';
        }
        if (!class_exists('Aysa_API_Config')) {
            require_once AYSA_PLUGIN_DIR . 'includes/config/aysa-api-config.php';
        }
        if (!class_exists('Aysa_API_Config')) {
            return '';
        }

        $api_url = Aysa_API_Config::get_endpoint_url('user_verify');
        if (!$api_url) {
            return '';
        }

        $response = wp_remote_post($api_url, array(
            'timeout' => 15,
            'headers' => array('Content-Type' => 'application/json'),
            'body' => wp_json_encode(array(
                'secretKey' => $secret_key,
                'domainFromWordpress' => wp_parse_url(home_url(), PHP_URL_HOST),
            )),
        ));
        if (is_wp_error($response) || intval(wp_remote_retrieve_response_code($response)) !== 200) {
            return '';
        }

        $data = json_decode((string) wp_remote_retrieve_body($response), true);
        if (!is_array($data)) {
            return '';
        }

        $email = sanitize_email((string) ($data['userEmail'] ?? $data['email'] ?? ''));
        if ($email !== '' && is_email($email)) {
            update_option('aysa_account_email', $email, false);
            return $email;
        }

        return '';
    }

    private function daily_summary_processed_option_name($project_id, $date_key) {
        return 'aysa_content_daily_summary_processed_' . intval($project_id) . '_' . sanitize_key((string) $date_key);
    }

    private function daily_summary_last_result_option_name($project_id) {
        return 'aysa_content_daily_summary_last_result_' . intval($project_id);
    }

    private function normalize_summary_datetime($value, DateTimeZone $tz) {
        if ($value instanceof DateTimeInterface) {
            return (new DateTimeImmutable('@' . $value->getTimestamp()))->setTimezone($tz);
        }
        $raw = trim((string) $value);
        if ($raw === '') {
            return new DateTimeImmutable('now', $tz);
        }
        try {
            return new DateTimeImmutable($raw, $tz);
        } catch (Throwable $e) {
            return new DateTimeImmutable('now', $tz);
        }
    }

    private function normalize_row_datetime($value, DateTimeZone $tz) {
        $raw = trim((string) $value);
        if ($raw === '') {
            return null;
        }
        $dt = DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $raw, $tz);
        if ($dt instanceof DateTimeImmutable) {
            return $dt;
        }
        try {
            return new DateTimeImmutable($raw, $tz);
        } catch (Throwable $e) {
            return null;
        }
    }

    private function normalize_post_publish_datetime(array $row, DateTimeZone $tz) {
        $gmt = trim((string) ($row['post_date_gmt'] ?? ''));
        if ($gmt !== '' && $gmt !== '0000-00-00 00:00:00') {
            $gmt_dt = DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $gmt, new DateTimeZone('UTC'));
            if ($gmt_dt instanceof DateTimeImmutable) {
                return $gmt_dt->setTimezone($tz);
            }
        }

        return $this->normalize_row_datetime((string) ($row['post_date'] ?? ''), $tz);
    }

    private function build_content_summary_item(array $row, $timing, $type_label) {
        return array(
            'strategy_id' => intval($row['id'] ?? 0),
            'title' => sanitize_text_field((string) ($row['title'] ?? 'Untitled')),
            'type' => sanitize_text_field((string) ($row['content_type'] ?? $row['strategy_type'] ?? 'unknown')),
            'timing' => $timing instanceof DateTimeInterface ? $timing->format('Y-m-d H:i:s') : '',
            'edit_url' => !empty($row['post_id']) ? admin_url('post.php?post=' . intval($row['post_id']) . '&action=edit') : '',
            'public_url' => !empty($row['post_id']) ? get_permalink(intval($row['post_id'])) : '',
            'status' => sanitize_text_field((string) ($row['automation_status'] ?? $row['status'] ?? '')),
            'label' => $type_label,
        );
    }

    public function build_daily_content_summary($project_id, $reference = null) {
        global $wpdb;

        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return array(
                'ok' => false,
                'error' => 'invalid_project_id',
            );
        }

        $tz = $this->daily_summary_timezone();
        $reference_dt = $this->normalize_summary_datetime($reference, $tz);
        $window_start = $reference_dt->setTime(0, 0, 0);
        $window_end = $window_start->modify('+1 day');
        $summary_cutoff = $reference_dt;
        $late_grace_cutoff = $window_start->modify('+6 hours');
        if ((int) $reference_dt->format('G') < 6) {
            $window_start = $window_start->modify('-1 day');
            $window_end = $window_start->modify('+1 day');
            $summary_cutoff = $window_end;
        } elseif ($summary_cutoff < $window_end && $summary_cutoff > $late_grace_cutoff) {
            // keep the actual runtime as the cutoff for same-day summary
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT cs.*, p.post_status, p.post_date, p.post_date_gmt
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM {$this->table_name} cs
             LEFT JOIN {$wpdb->posts} p ON p.ID = cs.post_id
             WHERE cs.project_id = %d
               AND COALESCE(cs.automation_status, '') <> 'deleted'
             ORDER BY COALESCE(cs.scheduled_for, cs.created_at) ASC, cs.id ASC",
            $project_id
        ), ARRAY_A);

        $published_today = array();
        $scheduled_today = array();
        $failed_or_blocked = array();
        $missed = array();
        $next_item = null;

        foreach ((array) $rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $scheduled_dt = $this->normalize_row_datetime((string) ($row['scheduled_for'] ?? ''), $tz);
            $publish_dt = $this->normalize_post_publish_datetime($row, $tz);
            $automation_updated_dt = $this->normalize_row_datetime((string) ($row['automation_updated_at'] ?? ''), $tz);
            $post_status = sanitize_key((string) ($row['post_status'] ?? ''));
            $automation_status = sanitize_key((string) ($row['automation_status'] ?? ''));
            $status = sanitize_key((string) ($row['status'] ?? ''));
            $is_published = !empty($row['post_id']) && $post_status === 'publish' && $publish_dt instanceof DateTimeInterface;
            $is_published_today = $is_published && $publish_dt >= $window_start && $publish_dt < $window_end;
            $is_scheduled_today = $scheduled_dt instanceof DateTimeInterface && $scheduled_dt >= $window_start && $scheduled_dt < $window_end;
            $is_failed_or_blocked = in_array($automation_status, array('failed', 'blocked_no_association', 'blocked'), true);

            if ($is_published_today) {
                $published_today[] = $this->build_content_summary_item($row, $publish_dt, 'published');
            }

            if ($is_scheduled_today && !$is_published_today) {
                $scheduled_today[] = $this->build_content_summary_item($row, $scheduled_dt, 'scheduled');
            }

            if ($is_failed_or_blocked && (
                $is_scheduled_today
                || ($scheduled_dt instanceof DateTimeInterface && $scheduled_dt < $summary_cutoff)
                || ($automation_updated_dt instanceof DateTimeInterface
                    && $automation_updated_dt >= $window_start
                    && $automation_updated_dt < $window_end)
            )) {
                $failed_or_blocked[] = $this->build_content_summary_item($row, $scheduled_dt, 'failed_or_blocked');
            }

            if (
                $scheduled_dt instanceof DateTimeInterface
                && $scheduled_dt >= $window_start
                && $scheduled_dt < $summary_cutoff
                && !$is_published_today
                && !$is_published
                && !$is_failed_or_blocked
            ) {
                $missed[] = $this->build_content_summary_item($row, $scheduled_dt, 'missed');
            }

            if (
                $next_item === null
                && $scheduled_dt instanceof DateTimeInterface
                && $scheduled_dt > $summary_cutoff
                && !$is_published
            ) {
                $next_item = $this->build_content_summary_item($row, $scheduled_dt, 'next_item');
            }
        }

        return array(
            'ok' => true,
            'project_id' => $project_id,
            'date' => $window_start->format('Y-m-d'),
            'timezone' => $tz->getName(),
            'cutoff' => $summary_cutoff->format('Y-m-d H:i:s'),
            'published_today' => array_values($published_today),
            'scheduled_today' => array_values($scheduled_today),
            'failed_or_blocked' => array_values($failed_or_blocked),
            'missed' => array_values($missed),
            'next_item' => $next_item,
            'has_activity' => (
                !empty($published_today)
                || !empty($scheduled_today)
                || !empty($failed_or_blocked)
                || !empty($missed)
                || !empty($next_item)
            ),
        );
    }

    private function agent8_communication_language() {
        $language = function_exists('aysa_get_interface_language')
            ? strtolower(trim((string) aysa_get_interface_language()))
            : 'ro';
        return strpos($language, 'ro') === 0 ? 'ro' : 'en';
    }

    private function format_daily_content_summary_message(array $summary, $language = 'en') {
        $lines = array();
        $date_label = sanitize_text_field((string) ($summary['date'] ?? ''));
        $is_ro = $language === 'ro';
        $variant_seed = abs(crc32($date_label . ':' . intval($summary['project_id'] ?? 0))) % 3;
        $published_count = count((array) ($summary['published_today'] ?? array()));
        $scheduled_count = count((array) ($summary['scheduled_today'] ?? array()));
        $failed_count = count((array) ($summary['failed_or_blocked'] ?? array()));
        $missed_count = count((array) ($summary['missed'] ?? array()));
        $intro_options = $is_ro ? array(
            'Bună, bine te-am regăsit.',
            'Hei, revin cu rezumatul de azi.',
            'Bună, am verificat execuția de conținut de azi.',
        ) : array(
            'Hi, here is today’s update.',
            'Hi, I checked today’s content execution.',
            'Hi, I am back with today’s content summary.',
        );
        $closing_options = $is_ro ? array(
            'Eu continui să urmăresc execuția, iar mâine revin cu un nou rezumat.',
            'Totul rămâne sub observație. Revin mâine cu următorul update.',
            'Eu mă ocup mai departe de monitorizare și te țin la curent.',
        ) : array(
            'I will keep watching the execution and send another summary tomorrow.',
            'Everything stays under observation. I will come back tomorrow with the next update.',
            'I will keep monitoring the plan and keep you posted.',
        );

        $lines[] = $intro_options[$variant_seed];
        $lines[] = '';
        $lines[] = $is_ro
            ? 'Strategia SEO merge mai departe. Am verificat ce s-a întâmplat azi în planul de conținut pentru ' . $date_label . '.'
            : 'The SEO strategy is moving forward. I checked today’s content plan for ' . $date_label . '.';
        $lines[] = '';
        $lines[] = $is_ro ? 'Astăzi:' : 'Today:';
        $lines[] = $is_ro
            ? '- ' . ($published_count > 0 ? $published_count . ' materiale au fost publicate' : 'nu s-a publicat încă nimic nou')
            : '- ' . ($published_count > 0 ? $published_count . ' items were published' : 'nothing new was published yet');
        $lines[] = $is_ro
            ? '- ' . ($scheduled_count === 1 ? '1 material este programat' : $scheduled_count . ' materiale sunt programate')
            : '- ' . ($scheduled_count === 1 ? '1 item is scheduled' : $scheduled_count . ' items are scheduled');
        $lines[] = $is_ro
            ? '- ' . ($failed_count > 0 ? $failed_count . ' materiale au nevoie de atenție' : 'nu avem blocaje sau erori')
            : '- ' . ($failed_count > 0 ? $failed_count . ' items need attention' : 'there are no blockers or errors');
        if ($missed_count > 0) {
            $lines[] = $is_ro
                ? '- ' . $missed_count . ' materiale par ratate și trebuie verificate'
                : '- ' . $missed_count . ' items look missed and should be checked';
        }

        foreach ((array) ($summary['published_today'] ?? array()) as $item) {
            if (!is_array($item)) {
                continue;
            }
            $links = array_filter(array(
                !empty($item['public_url']) ? ($is_ro ? 'Public: ' : 'Public: ') . $item['public_url'] : '',
                !empty($item['edit_url']) ? ($is_ro ? 'Editare: ' : 'Edit: ') . $item['edit_url'] : '',
            ));
            $lines[] = '  - ' . (string) ($item['title'] ?? '-') . ' | ' . (string) ($item['type'] ?? '-') . ' | ' . (string) ($item['timing'] ?? '-') . (empty($links) ? '' : ' | ' . implode(' | ', $links));
        }

        $next = isset($summary['next_item']) && is_array($summary['next_item']) ? $summary['next_item'] : array();
        if (!empty($next)) {
            $lines[] = '';
            $lines[] = $is_ro ? 'Următorul material pregătit este:' : 'The next prepared item is:';
            $lines[] = (string) ($next['title'] ?? '-');
            $lines[] = ($is_ro ? 'Programat pentru: ' : 'Scheduled for: ') . (string) ($next['timing'] ?? '-');
        }
        $lines[] = '';
        $lines[] = $closing_options[$variant_seed];

        return implode("\n", $lines);
    }

    public function send_daily_content_summary($project_id = null, $reference = null, $force = false) {
        $project_id = intval($project_id ?: get_option('aysa_project_id', 0));
        if ($project_id <= 0) {
            return array('ok' => false, 'error' => 'missing_project_id');
        }

        $summary = $this->build_daily_content_summary($project_id, $reference);
        if (empty($summary['ok'])) {
            return $summary;
        }

        $date_key = (string) ($summary['date'] ?? '');
        $processed_option = $this->daily_summary_processed_option_name($project_id, $date_key);
        if (!$force && !empty(get_option($processed_option, ''))) {
            return array(
                'ok' => true,
                'already_processed' => true,
                'date' => $date_key,
            );
        }

        if (empty($summary['has_activity'])) {
            $language = $this->agent8_communication_language();
            $this->create_agent8_communication_event(array(
                'project_id' => $project_id,
                'event_type' => 'daily_summary_skipped',
                'title' => $language === 'ro' ? 'Rezumatul zilnic a fost sărit' : 'Daily summary skipped',
                'message' => $language === 'ro' ? 'Am verificat activitatea de conținut de azi. Nu a fost nimic nou de rezumat.' : 'I checked today’s content activity. There was nothing new to summarize.',
                'severity' => 'info',
                'importance' => 'normal',
                'channels_required' => array('in_app'),
                'payload' => array('date' => $date_key, 'summary' => $summary),
            ));
            update_option($processed_option, wp_json_encode(array(
                'status' => 'no_activity',
                'processed_at' => current_time('mysql'),
            )), false);
            update_option($this->daily_summary_last_result_option_name($project_id), array(
                'status' => 'no_activity',
                'date' => $date_key,
                'processed_at' => current_time('mysql'),
                'summary' => $summary,
            ), false);
            return array(
                'ok' => true,
                'skipped' => true,
                'reason' => 'no_activity',
                'date' => $date_key,
                'summary' => $summary,
            );
        }

        $recipient = $this->content_summary_recipient($project_id);
        $language = $this->agent8_communication_language();
        $subject = ($language === 'ro' ? 'Rezumat zilnic AYSA pentru conținut - ' : 'AYSA daily content summary - ') . $date_key;
        $message = $this->format_daily_content_summary_message($summary, $language);
        if ($recipient === '') {
            $this->create_agent8_communication_event(array(
                'project_id' => $project_id,
                'event_type' => 'daily_summary_failed',
                'title' => $language === 'ro' ? 'Emailul cu rezumatul zilnic nu a putut fi trimis' : 'Daily summary email could not be sent',
                'message' => $language === 'ro' ? 'Am generat rezumatul zilnic, dar nu am putut trimite emailul pentru că nu este configurat niciun destinatar.' : 'I generated the daily content summary, but I could not send the email because no recipient is configured.',
                'severity' => 'warning',
                'importance' => 'important',
                'channels_required' => array('in_app', 'email'),
                'recipient' => '',
                'email_subject' => $subject,
                'email_body' => $message,
                'payload' => array('date' => $date_key, 'summary' => $summary),
            ));
            update_option($this->daily_summary_last_result_option_name($project_id), array(
                'status' => 'failed',
                'error' => 'missing_recipient_email',
                'date' => $date_key,
                'processed_at' => current_time('mysql'),
                'timezone' => $this->daily_summary_timezone()->getName(),
                'summary' => $summary,
            ), false);
            return array('ok' => false, 'error' => 'missing_recipient_email', 'summary' => $summary);
        }

        $event_result = $this->create_agent8_communication_event(array(
            'project_id' => $project_id,
            'event_type' => 'daily_summary_generated',
            'title' => $language === 'ro' ? 'Rezumatul zilnic este gata' : 'Daily summary is ready',
            'message' => $language === 'ro' ? 'Am generat rezumatul de conținut de azi și l-am trimis pe email.' : 'I generated today’s content summary and sent it by email.',
            'severity' => 'info',
            'importance' => 'important',
            'channels_required' => array('in_app', 'email'),
            'recipient' => $recipient,
            'email_subject' => $subject,
            'email_body' => $message,
            'payload' => array('date' => $date_key, 'summary' => $summary),
        ));
        $event = is_array($event_result) && !empty($event_result['event']) && is_array($event_result['event'])
            ? $event_result['event']
            : array();
        $sent = (string) ($event['email_status'] ?? '') === 'sent';
        if (!$sent) {
            update_option($this->daily_summary_last_result_option_name($project_id), array(
                'status' => 'failed',
                'error' => (string) ($event['last_error'] ?? 'postmark_send_failed'),
                'date' => $date_key,
                'recipient' => $recipient,
                'processed_at' => current_time('mysql'),
                'timezone' => $this->daily_summary_timezone()->getName(),
                'summary' => $summary,
                'subject' => $subject,
                'message' => $message,
                'communication_event_id' => intval($event['event_id'] ?? 0),
            ), false);
            return array('ok' => false, 'error' => 'postmark_send_failed', 'summary' => $summary);
        }

        $meta = array(
            'status' => 'sent',
            'date' => $date_key,
            'recipient' => $recipient,
            'processed_at' => current_time('mysql'),
            'timezone' => $this->daily_summary_timezone()->getName(),
        );
        update_option($processed_option, wp_json_encode($meta), false);
        update_option($this->daily_summary_last_result_option_name($project_id), array_merge($meta, array(
            'summary' => $summary,
            'subject' => $subject,
            'message' => $message,
            'communication_event_id' => intval($event['event_id'] ?? 0),
        )), false);

        return array(
            'ok' => true,
            'sent' => true,
            'recipient' => $recipient,
            'date' => $date_key,
            'subject' => $subject,
            'message' => $message,
            'summary' => $summary,
        );
    }

    public function ajax_run_content_planner_now() {
        check_ajax_referer('aysa_content_strategy_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Permission denied');
        }

        // Run in background to avoid long-running AJAX timeouts on slow API responses.
        wp_schedule_single_event(time() + 1, 'aysa_process_planned_content_now');

        wp_send_json_success(array('ran' => 1));
    }

    public function process_planned_content_queue() {
        global $wpdb;

        $project_id = intval(get_option('aysa_project_id', 0));
        if ($project_id <= 0) {
            return;
        }

        $now_mysql = current_time('mysql');
        $stale_cutoff = wp_date('Y-m-d H:i:s', current_time('timestamp') - (15 * 60));

        // Recover orphan "processing" rows (worker interrupted/timeouts).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $wpdb->query($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "UPDATE $this->table_name
             SET automation_status = 'retry',
                 automation_updated_at = %s
             WHERE status = 'scheduled'
               AND project_id = %d
               AND automation_status = 'processing'
               AND (
                    automation_attempts >= 3
                    OR automation_updated_at IS NULL
                    OR automation_updated_at < %s
               )",
            $now_mysql,
            $project_id,
            $stale_cutoff
        ));

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $items = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT * FROM $this->table_name
             WHERE status = 'scheduled'
               AND project_id = %d
               AND automation_status IN ('queued', 'retry')
               AND scheduled_for IS NOT NULL
               AND scheduled_for <= %s
               AND post_id IS NULL
               AND automation_attempts < 4
             ORDER BY scheduled_for ASC
             LIMIT 1",
            $project_id,
            $now_mysql
        ), ARRAY_A);

        if (empty($items)) {
            return;
        }

        foreach ($items as $item) {
            $strategy_id = intval($item['id']);
            $attempts = intval($item['automation_attempts']) + 1;

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $locked = $wpdb->query($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "UPDATE $this->table_name
                 SET automation_status = 'processing',
                     automation_attempts = %d,
                     automation_updated_at = %s
                 WHERE id = %d
                   AND status = 'scheduled'
                   AND automation_status IN ('queued', 'retry')
                   AND post_id IS NULL",
                $attempts,
                current_time('mysql'),
                $strategy_id
            ));

            if (empty($locked)) {
                continue;
            }

            try {
                $result = $this->process_single_scheduled_strategy($item);
            } catch (Throwable $e) {
                $result = array('success' => false, 'message' => 'Worker exception: ' . $e->getMessage());
            }

            if ($result['success']) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
                $wpdb->update(
                    $this->table_name,
                    array(
                        'post_id' => intval($result['post_id']),
                        'status' => 'generated',
                        'automation_status' => 'generated',
                        'generated_at' => current_time('mysql'),
                        'last_error' => null,
                        'automation_updated_at' => current_time('mysql')
                    ),
                    array('id' => $strategy_id)
                );
                if (empty($result['existing_post'])) {
                    $public_url = get_permalink(intval($result['post_id']));
                    $this->create_agent8_communication_event(array(
                        'project_id' => intval($item['project_id'] ?? get_option('aysa_project_id', 0)),
                        'event_type' => 'content_published',
                        'title' => 'Content published',
                        'message' => 'I published one planned article: ' . sanitize_text_field((string) ($item['title'] ?? 'Untitled content')) . '.',
                        'severity' => 'success',
                        'importance' => 'important',
                        'channels_required' => array('in_app', 'email'),
                        'recipient' => $this->content_summary_recipient(intval($item['project_id'] ?? get_option('aysa_project_id', 0))),
                        'email_subject' => 'AYSA published planned content',
                        'email_body' => 'I published one planned article: ' . sanitize_text_field((string) ($item['title'] ?? 'Untitled content')) . "\n\n" . ($public_url ? 'URL: ' . $public_url : ''),
                        'payload' => array(
                            'strategy_id' => $strategy_id,
                            'post_id' => intval($result['post_id']),
                            'public_url' => $public_url ? $public_url : '',
                        ),
                    ));
                }
            } else {
                $new_status = ($attempts >= 3) ? 'failed' : 'retry';
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
                $wpdb->update(
                    $this->table_name,
                    array(
                        'automation_status' => $new_status,
                        'last_error' => sanitize_text_field($result['message'] ?? 'Unknown error'),
                        'automation_updated_at' => current_time('mysql')
                    ),
                    array('id' => $strategy_id)
                );
            }
        }
    }

    private function process_single_scheduled_strategy(array $strategy) {
        $project_id = intval($strategy['project_id'] ?? get_option('aysa_project_id', 0));
        $title = sanitize_text_field((string) ($strategy['title'] ?? ''));
        if ($title === '') {
            return array('success' => false, 'message' => 'Missing strategy title');
        }

        $keywords = json_decode((string) ($strategy['keywords'] ?? '[]'), true);
        if (!is_array($keywords)) {
            $keywords = array();
        }
        $associated_pages = $this->parse_associated_pages($strategy['associated_page'] ?? '');
        if (empty($associated_pages)) {
            $associated_pages = $this->infer_associated_pages_from_keywords($project_id, $strategy['keywords'] ?? '[]');
        }
        $main_keyword = sanitize_text_field((string) ($keywords[0] ?? $title));
        $secondary_keywords = array();
        if (count($keywords) > 1) {
            foreach (array_slice($keywords, 1) as $kw) {
                $kw_clean = sanitize_text_field((string) $kw);
                if ($kw_clean !== '') {
                    $secondary_keywords[] = $kw_clean;
                }
            }
        }

        $existing_post_id = $this->find_existing_post_for_strategy($strategy, $title);
        if ($existing_post_id > 0) {
            return array('success' => true, 'post_id' => $existing_post_id, 'existing_post' => true);
        }

        $post_id = $this->create_post_for_strategy($strategy, $associated_pages, $main_keyword, $secondary_keywords);
        if (is_wp_error($post_id)) {
            return array('success' => false, 'message' => $post_id->get_error_message());
        }

        $generation = $this->generate_blog_content_for_post($post_id, $title, $main_keyword, $secondary_keywords, $associated_pages);
        if (!$generation['success']) {
            $this->cleanup_failed_strategy_post($post_id);
            return array('success' => false, 'message' => $generation['message'] ?? 'Generation failed');
        }

        $save_result = $this->apply_generated_blog_content_to_post(
            $post_id,
            $generation['data'],
            $associated_pages,
            $main_keyword,
            $secondary_keywords
        );
        if (!$save_result['success']) {
            $this->cleanup_failed_strategy_post($post_id);
            return array('success' => false, 'message' => $save_result['message']);
        }

        $schedule_result = $this->schedule_post_publication($post_id, (string) ($strategy['scheduled_for'] ?? ''));
        if (!$schedule_result['success']) {
            return array('success' => false, 'message' => $schedule_result['message']);
        }

        if (!empty($associated_pages)) {
            $this->append_internal_linking_report_entries($post_id, $title, $main_keyword, $associated_pages);
        }

        $this->bill_generated_article_once(
            $project_id,
            $post_id,
            intval($strategy['id'] ?? 0),
            'cron_queue',
            $main_keyword
        );

        return array('success' => true, 'post_id' => $post_id);
    }

    /**
     * Cron worker for posts already created from Content Strategy "Generate Article" button.
     * Generates full content server-side so it no longer depends on browser redirect flow.
     */
    public function handle_generate_existing_strategy_post($payload) {
        if (!is_array($payload)) {
            return;
        }

        $post_id = intval($payload['post_id'] ?? 0);
        $title = sanitize_text_field((string) ($payload['title'] ?? ''));
        $main_keyword = sanitize_text_field((string) ($payload['main_keyword'] ?? ''));
        $secondary_keywords = isset($payload['secondary_keywords']) && is_array($payload['secondary_keywords'])
            ? array_values(array_filter(array_map('sanitize_text_field', $payload['secondary_keywords'])))
            : array();
        $associated_pages = isset($payload['associated_pages']) && is_array($payload['associated_pages'])
            ? $this->normalize_associated_pages($payload['associated_pages'])
            : array();

        if ($post_id <= 0 || $title === '') {
            return;
        }

        $post = get_post($post_id);
        if (!$post || is_wp_error($post)) {
            return;
        }

        if ($main_keyword === '') {
            $main_keyword = $title;
        }

        $bound_project_id = intval(get_option('aysa_project_id', 0));
        $requested_project_id = intval($payload['project_id'] ?? $bound_project_id);
        if ($bound_project_id <= 0 || $requested_project_id !== $bound_project_id) {
            return;
        }
        $project_id = $bound_project_id;
        if (empty($associated_pages)) {
            $associated_pages = $this->infer_associated_pages_from_keywords($project_id, array_merge(array($main_keyword), $secondary_keywords));
        }
        $generation = $this->generate_blog_content_for_post($post_id, $title, $main_keyword, $secondary_keywords, $associated_pages);
        if (!$generation['success']) {
            update_post_meta($post_id, '_aysa_existing_post_generation_status', 'failed');
            update_post_meta($post_id, '_aysa_existing_post_generation_error', sanitize_text_field((string) ($generation['message'] ?? 'Generation failed')));
            return;
        }

        $save_result = $this->apply_generated_blog_content_to_post(
            $post_id,
            $generation['data'],
            $associated_pages,
            $main_keyword,
            $secondary_keywords
        );
        if (!$save_result['success']) {
            update_post_meta($post_id, '_aysa_existing_post_generation_status', 'failed');
            update_post_meta($post_id, '_aysa_existing_post_generation_error', sanitize_text_field((string) ($save_result['message'] ?? 'Save failed')));
            return;
        }

        // Auto-publish articles generated from Content Strategy "Generate Article" flow.
        $auto_publish = apply_filters('aysa_content_strategy_auto_publish_existing_post', true, $post_id, $payload);
        if ($auto_publish) {
            $publish_result = $this->publish_existing_strategy_post($post_id);
            if (!$publish_result['success']) {
                update_post_meta($post_id, '_aysa_existing_post_generation_status', 'failed');
                update_post_meta($post_id, '_aysa_existing_post_generation_error', sanitize_text_field((string) ($publish_result['message'] ?? 'Publish failed')));
                return;
            }
        }

        update_post_meta($post_id, '_aysa_existing_post_generation_status', 'generated');
        delete_post_meta($post_id, '_aysa_existing_post_generation_error');
        update_post_meta($post_id, '_aysa_existing_post_generated_at', current_time('mysql'));

        $this->bill_generated_article_once(
            $project_id,
            $post_id,
            0,
            'existing_post_cron',
            $main_keyword
        );
    }

    private function publish_existing_strategy_post($post_id) {
        $post_id = intval($post_id);
        if ($post_id <= 0) {
            return array('success' => false, 'message' => 'Invalid post ID for publishing');
        }

        $local_now = current_time('mysql');
        $gmt_now = get_gmt_from_date($local_now);
        $result = wp_update_post(array(
            'ID' => $post_id,
            'post_status' => 'publish',
            'post_date' => $local_now,
            'post_date_gmt' => $gmt_now,
        ), true);

        if (is_wp_error($result)) {
            return array('success' => false, 'message' => $result->get_error_message());
        }

        return array('success' => true);
    }

    private function cleanup_failed_strategy_post($post_id) {
        $post_id = intval($post_id);
        if ($post_id <= 0) {
            return;
        }
        $post = get_post($post_id);
        if (!$post || is_wp_error($post)) {
            return;
        }
        // Remove orphan drafts created by strategy worker when generation fails.
        if ($post->post_status === 'draft') {
            wp_delete_post($post_id, true);
        }
    }

    private function find_existing_post_for_strategy(array $strategy, $title) {
        global $wpdb;

        $fingerprint = sanitize_text_field((string) ($strategy['content_fingerprint'] ?? ''));
        if ($fingerprint !== '') {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $post_id = intval($wpdb->get_var($wpdb->prepare(
                "SELECT p.ID
                 FROM {$wpdb->posts} p
                 INNER JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID
                 WHERE pm.meta_key = '_aysa_content_fingerprint'
                   AND pm.meta_value = %s
                   AND p.post_type = 'post'
                   AND p.post_status IN ('draft', 'pending', 'future', 'publish', 'private')
                 ORDER BY p.ID ASC
                 LIMIT 1",
                $fingerprint
            )));
            if ($post_id > 0) {
                return $post_id;
            }
        }

        $title = sanitize_text_field((string) $title);
        if ($title === '') {
            return 0;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT p.ID
             FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID
             WHERE p.post_title = %s
               AND p.post_type = 'post'
               AND p.post_status IN ('draft', 'pending', 'future', 'publish', 'private')
               AND pm.meta_key IN ('aysa_generation_source', '_aysa_generation_source')
               AND pm.meta_value = 'content-strategy-auto'
             ORDER BY p.ID ASC
             LIMIT 1",
            $title
        )));
    }

    private function create_post_for_strategy(array $strategy, array $associated_pages, $main_keyword, array $secondary_keywords) {
        $title = sanitize_text_field((string) ($strategy['title'] ?? ''));
        $associated_page = $associated_pages[0] ?? '';
        $content = '';
        if ($associated_page !== '') {
            $content = sprintf(
                '<p>%s <a href="%s">%s</a></p>',
                esc_html__('See also:', 'aysaaiseo'),
                esc_url($associated_page),
                esc_html__('Related Content', 'aysaaiseo')
            );
        }

        $post_id = wp_insert_post(array(
            'post_title'   => $title,
            'post_content' => $content,
            'post_status'  => 'draft',
            'post_type'    => 'post',
            'post_author'  => get_current_user_id() ?: 1,
        ));

        if (is_wp_error($post_id)) {
            return $post_id;
        }

        $keyword_data = array(
            'article_topic'  => $title,
            'keyword'        => $main_keyword,
            'second_keyword' => implode(', ', $secondary_keywords),
            'intent'         => sanitize_text_field((string) ($strategy['intent'] ?? 'I')),
            'source'         => 'content-strategy-auto'
        );

        update_post_meta($post_id, 'aysa_keyword_data', $keyword_data);
        update_post_meta($post_id, 'aysa_main_keyword', $main_keyword);
        update_post_meta($post_id, 'aysa_secondary_keywords', implode(', ', $secondary_keywords));
        update_post_meta($post_id, 'aysa_search_intent', sanitize_text_field((string) ($strategy['intent'] ?? 'I')));
        update_post_meta($post_id, 'aysa_associated_page', $associated_page);
        update_post_meta($post_id, 'aysa_associated_pages', $associated_pages);
        update_post_meta($post_id, 'aysa_generation_source', 'content-strategy-auto');
        update_post_meta($post_id, '_aysa_content_strategy_id', intval($strategy['id'] ?? 0));
        update_post_meta($post_id, '_aysa_content_fingerprint', sanitize_text_field((string) ($strategy['content_fingerprint'] ?? '')));
        update_post_meta($post_id, '_aysa_content_plan_batch', sanitize_text_field((string) ($strategy['plan_batch'] ?? '')));
        update_post_meta($post_id, '_aysa_generation_source', 'content-strategy-auto');

        return $post_id;
    }

    private function get_blog_content_api_base_url() {
        if (defined('AYSA_BLOG_CONTENT_API_URL') && is_string(AYSA_BLOG_CONTENT_API_URL) && trim(AYSA_BLOG_CONTENT_API_URL) !== '') {
            return rtrim((string) AYSA_BLOG_CONTENT_API_URL, '/');
        }
        $custom_endpoint = get_option('aysa_blog_content_api_endpoint', '');
        if (is_string($custom_endpoint) && trim($custom_endpoint) !== '') {
            return rtrim((string) $custom_endpoint, '/');
        }
        if (defined('AYSA_API_BASE_URL') && is_string(AYSA_API_BASE_URL) && trim(AYSA_API_BASE_URL) !== '') {
            return rtrim((string) AYSA_API_BASE_URL, '/');
        }
        if (class_exists('Aysa_API_Config')) {
            return rtrim((string) Aysa_API_Config::get_base_url(), '/');
        }
        return 'https://api.aysa.ai';
    }

    private function get_broker_hmac_config() {
        $default_base_url = defined('AYSA_API_BASE_URL') && is_string(AYSA_API_BASE_URL) && trim(AYSA_API_BASE_URL) !== ''
            ? trim((string) AYSA_API_BASE_URL)
            : (class_exists('Aysa_API_Config') ? trim((string) Aysa_API_Config::get_base_url()) : 'https://api.aysa.ai');
        $default_key_id = 'wp_plugin';
        $default_key_secret = '';

        if (defined('AYSA_OAUTH_BROKER_BASE_URL') && is_string(AYSA_OAUTH_BROKER_BASE_URL) && trim(AYSA_OAUTH_BROKER_BASE_URL) !== '') {
            $default_base_url = trim((string) AYSA_OAUTH_BROKER_BASE_URL);
        } else {
            $opt_base = get_option('aysa_oauth_broker_base_url', '');
            if (is_string($opt_base) && trim($opt_base) !== '') {
                $default_base_url = trim($opt_base);
            }
        }

        if (defined('AYSA_OAUTH_BROKER_KEY_ID') && is_string(AYSA_OAUTH_BROKER_KEY_ID) && trim(AYSA_OAUTH_BROKER_KEY_ID) !== '') {
            $default_key_id = trim((string) AYSA_OAUTH_BROKER_KEY_ID);
        } else {
            $opt_key_id = get_option('aysa_oauth_broker_key_id', '');
            if (is_string($opt_key_id) && trim($opt_key_id) !== '') {
                $default_key_id = trim($opt_key_id);
            }
        }

        if (defined('AYSA_OAUTH_BROKER_KEY_SECRET') && is_string(AYSA_OAUTH_BROKER_KEY_SECRET) && trim(AYSA_OAUTH_BROKER_KEY_SECRET) !== '') {
            $default_key_secret = trim((string) AYSA_OAUTH_BROKER_KEY_SECRET);
        } else {
            $opt_key_secret = get_option('aysa_oauth_broker_key_secret', '');
            if (is_string($opt_key_secret) && trim($opt_key_secret) !== '') {
                $default_key_secret = trim($opt_key_secret);
            }
        }

        if ($default_key_secret === '') {
            return new WP_Error(
                'broker_hmac_config_missing',
                'OAuth broker HMAC secret is not configured on this site.'
            );
        }

        return array(
            'base_url' => rtrim((string) $default_base_url, '/'),
            'key_id' => trim((string) $default_key_id),
            'key_secret' => trim((string) $default_key_secret),
        );
    }

    private function build_broker_hmac_headers($method, $request_path, $body_json) {
        $cfg = $this->get_broker_hmac_config();
        if (is_wp_error($cfg)) {
            return $cfg;
        }
        $method = strtoupper((string) $method);
        $path = '/' . ltrim((string) $request_path, '/');
        $body = (string) $body_json;

        $timestamp = (string) time();
        try {
            $nonce = bin2hex(random_bytes(16));
        } catch (Throwable $e) {
            $nonce = wp_generate_password(32, false, false);
        }

        $body_hash = hash('sha256', $body);
        $canonical = implode("\n", array($method, $path, $body_hash, $timestamp, $nonce));
        $signature = hash_hmac('sha256', $canonical, (string) ($cfg['key_secret'] ?? ''));

        return array(
            'Content-Type' => 'application/json',
            'X-Aysa-Key' => (string) ($cfg['key_id'] ?? ''),
            'X-Aysa-Timestamp' => $timestamp,
            'X-Aysa-Nonce' => $nonce,
            'X-Aysa-Signature' => $signature,
        );
    }

    private function call_blog_content_api($endpoint, $payload) {
        $base = trailingslashit($this->get_blog_content_api_base_url());
        // Keep Content Strategy on the same generation path used by metabox flow.
        $primary_url = $base . 'blog-content/' . ltrim($endpoint, '/');
        $request_body = wp_json_encode($payload);
        if ($request_body === false) {
            $request_body = '{}';
        }

        $primary_path = (string) (wp_parse_url($primary_url, PHP_URL_PATH) ?: '/');

        $request_args = array(
            'timeout' => 120,
            'headers' => $this->build_broker_hmac_headers('POST', $primary_path, $request_body),
            'body' => $request_body,
        );
        if (is_wp_error($request_args['headers'])) {
            return array(
                'success' => false,
                'message' => $request_args['headers']->get_error_message(),
            );
        }

        $response = wp_remote_post($primary_url, $request_args);
        if (is_wp_error($response)) {
            return array('success' => false, 'message' => $response->get_error_message());
        }

        $code = wp_remote_retrieve_response_code($response);
        $body = wp_remote_retrieve_body($response);
        $data = json_decode($body, true);

        if ($code < 200 || $code >= 300) {
            $message = '';
            if (is_array($data) && !empty($data['message'])) {
                $message = (string) $data['message'];
            }
            if ($message === '') {
                $message = 'Blog API call failed with status ' . $code;
            }
            return array('success' => false, 'message' => $message);
        }

        $normalized = array();
        if (is_array($data)) {
            if (isset($data['data']) && is_array($data['data'])) {
                $normalized = $data['data'];
            } elseif (isset($data['result']) && is_array($data['result'])) {
                $normalized = $data['result'];
            } else {
                $normalized = $data;
            }
        }

        return array('success' => true, 'data' => $normalized);
    }

    private function extract_first_non_empty_value($source, array $keys) {
        if (!is_array($source)) {
            return '';
        }

        foreach ($keys as $key) {
            if (isset($source[$key]) && is_scalar($source[$key])) {
                $value = trim((string) $source[$key]);
                if ($value !== '') {
                    return $value;
                }
            }
        }

        foreach ($source as $value) {
            if (is_array($value)) {
                $found = $this->extract_first_non_empty_value($value, $keys);
                if ($found !== '') {
                    return $found;
                }
            }
        }

        return '';
    }

    private function build_blog_generation_payload($post_id, $article_topic, $primary_keyword, array $secondary_keywords, array $associated_pages = array()) {
        $brand_name = (string) get_option('aysa_brand_name', get_bloginfo('name'));
        $phone = (string) get_option('aysa_phone_number', '');
        $email = (string) get_option('aysa_email_address', get_option('admin_email'));
        $client_info = trim($brand_name . ' ' . $phone . ' ' . $email);
        $city = (string) get_option('aysa_city', '');
        $internal_links = $this->build_article_internal_links($associated_pages);
        $commerce_links = $this->build_commerce_recommendation_links($primary_keyword, $secondary_keywords, $associated_pages);

        return array(
            'ArticleTopic' => $article_topic,
            'BrandName' => $brand_name,
            'ClientInfo' => $client_info !== '' ? $client_info : $brand_name,
            'Domain' => wp_parse_url(home_url(), PHP_URL_HOST),
            'PublicationLocation' => (string) get_option('aysa_publication_location', get_bloginfo('name')),
            'Language' => (string) get_option('aysa_language', 'ro'),
            'LocalSpecifics' => (string) get_option('aysa_local_specifics', ''),
            'City' => $city,
            'TargetAudience' => (string) get_option('aysa_target_audience', 'general public'),
            'AudienceLevel' => (string) get_option('aysa_audience_level', ''),
            'TargetWordCount' => (string) get_option('aysa_target_word_count', '2000'),
            'PrimaryKeyword' => $primary_keyword,
            'SecondaryKeywords' => array_values($secondary_keywords),
            'RelatedKeywords' => array_values($secondary_keywords),
            'Tone' => (string) get_option('aysa_tone', 'profesionist'),
            'WebsiteContext' => (string) get_option('aysa_website_context', get_bloginfo('description')),
            'PhoneNumber' => $phone,
            'EmailAddress' => $email,
            'ProjectId' => intval(get_option('aysa_project_id', 0)),
            'PostId' => intval($post_id),
            'associated_pages' => array_values($associated_pages),
            'article_context' => array(
                'associated_pages' => $internal_links,
                'internal_links' => $internal_links,
                'commerce_recommendations' => $commerce_links,
            ),
        );
    }

    private function generate_blog_content_for_post($post_id, $article_topic, $primary_keyword, array $secondary_keywords, array $associated_pages = array()) {
        $payload = $this->build_blog_generation_payload($post_id, $article_topic, $primary_keyword, $secondary_keywords, $associated_pages);
        $results = array();

        $outline = $this->call_blog_content_api('35-document-outline', $payload);
        if (!$outline['success']) {
            return $outline;
        }
        $results['outline'] = $this->extract_first_non_empty_value($outline['data'], array(
            'outline', 'document_outline', 'documentOutline', 'DocumentOutline', 'content', 'text', 'message'
        ));
        $payload['DocumentOutline'] = $results['outline'];
        $payload['document_outline'] = $results['outline'];

        $tldr = $this->call_blog_content_api('36-tldr-introduction', $payload);
        if ($tldr['success']) {
            $results['tldr_content'] = $this->extract_first_non_empty_value($tldr['data'], array(
                'tldr_content', 'tldrContent', 'tldr', 'TLDR', 'summary', 'content'
            ));
            $results['introduction_content'] = $this->extract_first_non_empty_value($tldr['data'], array(
                'introduction_content', 'introductionContent', 'introduction', 'intro', 'content'
            ));
        }

        $extended = $this->call_blog_content_api('37-extended-content', $payload);
        if ($extended['success']) {
            $results['extended_content'] = $this->extract_first_non_empty_value($extended['data'], array(
                'extended_content', 'extendedContent', 'content_html', 'content', 'article_content', 'body', 'text'
            ));
        }

        $faq = $this->call_blog_content_api('38-faq-microdata', $payload);
        if ($faq['success']) {
            $results['faq_html'] = $this->extract_first_non_empty_value($faq['data'], array(
                'faq_html', 'faqHtml', 'FaqHtml', 'faq', 'faq_content', 'content', 'html'
            ));
        }

        $conclusion = $this->call_blog_content_api('39-conclusion-cta', $payload);
        if ($conclusion['success']) {
            $results['conclusion_content'] = $this->extract_first_non_empty_value($conclusion['data'], array(
                'conclusion_content', 'conclusionContent', 'conclusion', 'cta', 'content', 'text'
            ));
        }

        $image_payload = array(
            'project_id' => intval(get_option('aysa_project_id', 0)),
            'site_host' => wp_parse_url(home_url(), PHP_URL_HOST),
            'article_topic' => $article_topic,
            'primary_keyword' => $primary_keyword,
            'secondary_keywords' => array_values($secondary_keywords),
            'language' => (string) ($payload['Language'] ?? 'ro'),
            'brand_name' => (string) ($payload['BrandName'] ?? get_bloginfo('name')),
            'post_id' => intval($post_id),
        );

        $image = $this->call_article_image_broker($image_payload + array(
            'image_roles' => array('featured'),
        ));
        if ($image['success']) {
            $featured_image = $this->extract_article_image_by_role($image['data'], 'featured');

            $results['featured_image_url'] = $this->extract_first_non_empty_value($featured_image ?: $image['data'], array(
                'image_url', 'imageUrl', 'ImageUrl', 'featured_image_url', 'url'
            ));
            $results['featured_image_alt'] = $this->extract_first_non_empty_value($featured_image ?: $image['data'], array(
                'alt_text', 'altText', 'AltText', 'image_alt_text', 'imageAltText', 'alternativeText', 'alternative_text'
            ));
            $results['featured_image_title'] = $this->extract_first_non_empty_value($featured_image ?: $image['data'], array(
                'title', 'image_title', 'imageTitle'
            ));
            $results['featured_image_caption'] = $this->extract_first_non_empty_value($featured_image ?: $image['data'], array(
                'caption', 'associated_text', 'associatedText'
            ));
            $results['featured_image_description'] = $this->extract_first_non_empty_value($featured_image ?: $image['data'], array(
                'description', 'image_description', 'imageDescription'
            ));
        } else {
            $results['pending_image_roles'][] = 'featured';
            $results['featured_image_error'] = (string) ($image['message'] ?? 'featured_image_generation_failed');
        }

        $inline_image_response = $this->call_article_image_broker($image_payload + array(
            'image_roles' => array('inline_primary'),
        ));
        if ($inline_image_response['success']) {
            $inline_image = $this->extract_article_image_by_role($inline_image_response['data'], 'inline_primary');
            if (!empty($inline_image)) {
                $results['inline_image_url'] = $this->extract_first_non_empty_value($inline_image, array(
                    'image_url', 'imageUrl', 'ImageUrl', 'inline_image_url', 'url'
                ));
                $results['inline_image_alt'] = $this->extract_first_non_empty_value($inline_image, array(
                    'alt_text', 'altText', 'AltText', 'image_alt_text', 'imageAltText', 'alternativeText', 'alternative_text'
                ));
                $results['inline_image_title'] = $this->extract_first_non_empty_value($inline_image, array(
                    'title', 'image_title', 'imageTitle'
                ));
                $results['inline_image_caption'] = $this->extract_first_non_empty_value($inline_image, array(
                    'caption', 'associated_text', 'associatedText'
                ));
                $results['inline_image_description'] = $this->extract_first_non_empty_value($inline_image, array(
                    'description', 'image_description', 'imageDescription'
                ));
            }
        } else {
            $results['pending_image_roles'][] = 'inline_primary';
            $results['inline_image_error'] = (string) ($inline_image_response['message'] ?? 'inline_image_generation_failed');
        }

        $meta = $this->call_blog_content_api('41-meta-seo-elements', $payload);
        if ($meta['success']) {
            $results['meta_title'] = $this->limit_seo_meta_text($this->extract_first_non_empty_value($meta['data'], array(
                'meta_title', 'metaTitle', 'title', 'seo_title', 'MetaTitle'
            )), 60);
            $results['meta_description'] = $this->limit_seo_meta_text($this->extract_first_non_empty_value($meta['data'], array(
                'meta_description', 'metaDescription', 'description', 'seo_description', 'MetaDescription'
            )), 159);
            $results['url_recomandat'] = $this->extract_first_non_empty_value($meta['data'], array(
                'url_recomandat', 'urlRecomandat', 'slug', 'recommended_url', 'url'
            ));
        }

        return array('success' => true, 'data' => $results);
    }

    private function sanitize_content_html($html) {
        if (!is_string($html)) {
            return '';
        }

        $clean = str_replace(array('\\n', '\\r', '\\"'), array("\n", "\n", '"'), $html);
        $clean = preg_replace('/(^|[^a-zA-Z])n(\\s*<)/', '$1$2', $clean);
        $clean = str_replace(array('nn', "\r\n"), array("\n\n", "\n"), $clean);
        $clean = $this->strip_generated_heading_numbering($clean);
        $clean = $this->strip_generic_conclusion_headings($clean);
        return trim($clean);
    }

    private function limit_seo_meta_text($text, $max_chars) {
        $clean = trim(preg_replace('/\s+/', ' ', wp_strip_all_tags((string) $text)));
        $max_chars = max(1, intval($max_chars));
        if ($clean === '' || mb_strlen($clean) <= $max_chars) {
            return $clean;
        }

        $truncated = rtrim(mb_substr($clean, 0, $max_chars), " \t\n\r\0\x0B.,;:-");
        $last_space = mb_strrpos($truncated, ' ');
        if ($last_space !== false && $last_space >= max(20, intval($max_chars * 0.65))) {
            $truncated = rtrim(mb_substr($truncated, 0, $last_space), " \t\n\r\0\x0B.,;:-");
        }

        return $truncated;
    }

    private function build_article_url_slug($recommended_url, $title, $main_keyword, array $secondary_keywords) {
        $source_parts = array();
        $recommended_path = (string) wp_parse_url((string) $recommended_url, PHP_URL_PATH);
        if ($recommended_path !== '') {
            $source_parts[] = basename(trim($recommended_path, '/'));
        } elseif ((string) $recommended_url !== '') {
            $source_parts[] = (string) $recommended_url;
        }
        $source_parts[] = (string) $main_keyword;
        foreach (array_slice($secondary_keywords, 0, 4) as $secondary_keyword) {
            $source_parts[] = (string) $secondary_keyword;
        }
        $source_parts[] = (string) $title;

        $stopwords = array(
            'a','ai','ale','al','asta','acest','aceasta','aceste','acesti','acestia','acolo','asa',
            'ca','care','cat','cât','cand','când','ce','cea','cele','cel','cei','cum','cu',
            'de','din','dupa','după','este','fara','fără','fi','fie','in','în','intre','între',
            'la','mai','mult','nu','o','pe','pentru','prin','sau','sa','să','si','și','spre',
            'the','and','or','for','with','from','into','how','what','when','where','why','your'
        );
        $stop = array_fill_keys(array_map('remove_accents', $stopwords), true);
        $tokens = array();
        $seen = array();

        foreach ($source_parts as $part) {
            $slug_part = sanitize_title((string) $part);
            foreach (explode('-', $slug_part) as $token) {
                $token = trim((string) $token);
                if ($token === '') {
                    continue;
                }
                $token_key = remove_accents($token);
                if ((strlen($token_key) < 3 && !preg_match('/^\d+$/', $token_key)) || isset($stop[$token_key]) || isset($seen[$token_key])) {
                    continue;
                }
                $seen[$token_key] = true;
                $tokens[] = $token;
            }
        }

        if (empty($tokens)) {
            return sanitize_title((string) $title);
        }

        $base_url_length = mb_strlen(home_url('/'));
        $ideal_slug_chars = max(42, 80 - $base_url_length);
        $max_slug_chars = max($ideal_slug_chars, 120 - $base_url_length);
        $slug = '';

        foreach ($tokens as $token) {
            $candidate = $slug === '' ? $token : $slug . '-' . $token;
            if (mb_strlen($candidate) > $max_slug_chars) {
                break;
            }
            $slug = $candidate;
            if (mb_strlen(home_url('/' . $slug . '/')) >= 50 && mb_strlen($slug) >= $ideal_slug_chars) {
                break;
            }
        }

        return $slug !== '' ? $slug : sanitize_title((string) $title);
    }

    private function strip_generated_heading_numbering($html) {
        if (!is_string($html) || $html === '') {
            return '';
        }

        return (string) preg_replace_callback('/(<h[2-4]\b[^>]*>)(.*?)(<\/h[2-4]>)/is', function ($matches) {
            $heading = preg_replace('/^\s*(?:\d{1,2}\s*[\.)]|(?:pasul|step|sec(?:țiunea|tiunea|tion)|șablon|sablon|template|model)\s+\d{1,2}\s*[\.:)\-]?)\s+/iu', '', (string) $matches[2]);
            return (string) $matches[1] . $heading . (string) $matches[3];
        }, $html);
    }

    private function strip_generic_conclusion_headings($html) {
        if (!is_string($html) || $html === '') {
            return '';
        }

        return (string) preg_replace('/<h[2-4]\b[^>]*>\s*(?:concluzie|în concluzie|in concluzie|conclusion|final thoughts|rezumat|pe scurt)\s*<\/h[2-4]>\s*/iu', '', $html);
    }

    private function build_article_internal_links(array $associated_pages) {
        $links = array();
        $seen = array();
        foreach ($associated_pages as $page_url) {
            $safe_url = esc_url_raw((string) $page_url);
            if ($safe_url === '') {
                continue;
            }
            $key = strtolower(untrailingslashit($safe_url));
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $links[] = array(
                'url' => $safe_url,
                'label' => $this->resolve_internal_link_label($safe_url),
                'type' => 'associated_page',
            );
        }
        return array_values($links);
    }

    private function resolve_internal_link_label($url) {
        $post_id = url_to_postid((string) $url);
        if ($post_id > 0) {
            $title = get_the_title($post_id);
            if (is_string($title) && trim($title) !== '') {
                return wp_strip_all_tags($title);
            }
        }

        $path = (string) wp_parse_url((string) $url, PHP_URL_PATH);
        $path = trim($path, '/');
        if ($path === '') {
            return __('pagina recomandată', 'aysaaiseo');
        }

        $parts = explode('/', $path);
        $last = end($parts);
        $label = str_replace(array('-', '_'), ' ', (string) $last);
        $label = trim(preg_replace('/\s+/', ' ', $label));
        return $label !== '' ? ucwords($label) : __('pagina recomandată', 'aysaaiseo');
    }

    private function build_commerce_recommendation_links($main_keyword, array $secondary_keywords, array $associated_pages = array()) {
        if (!post_type_exists('product') && !taxonomy_exists('product_cat')) {
            return array();
        }

        $tokens = $this->build_recommendation_tokens(array_merge(array($main_keyword), $secondary_keywords));
        if (empty($tokens)) {
            return array();
        }

        $candidates = array();
        if (taxonomy_exists('product_cat')) {
            $terms = get_terms(array(
                'taxonomy' => 'product_cat',
                'hide_empty' => false,
                'number' => 40,
            ));
            if (is_array($terms)) {
                foreach ($terms as $term) {
                    if (!is_object($term)) {
                        continue;
                    }
                    $link = get_term_link($term);
                    if (is_wp_error($link)) {
                        continue;
                    }
                    $score = $this->score_recommendation_text($term->name . ' ' . $term->slug . ' ' . $term->description, $tokens);
                    if ($score > 0) {
                        $candidates[] = array(
                            'url' => esc_url_raw($link),
                            'label' => wp_strip_all_tags($term->name),
                            'type' => 'product_category',
                            'score' => $score + 2,
                        );
                    }
                }
            }
        }

        if (post_type_exists('product')) {
            $products = get_posts(array(
                'post_type' => 'product',
                'post_status' => 'publish',
                'numberposts' => 40,
                'orderby' => 'date',
                'order' => 'DESC',
                // phpcs:ignore WordPressVIPMinimum.Performance.WPQueryParams.SuppressFilters_suppress_filters -- Recommendation candidates intentionally span every installed language; the query is bounded to 40 published products.
                'suppress_filters' => true,
            ));
            foreach ((array) $products as $product) {
                if (!is_object($product)) {
                    continue;
                }
                $score = $this->score_recommendation_text($product->post_title . ' ' . $product->post_excerpt . ' ' . wp_strip_all_tags($product->post_content), $tokens);
                if ($score > 0) {
                    $candidates[] = array(
                        'url' => esc_url_raw(get_permalink($product)),
                        'label' => wp_strip_all_tags($product->post_title),
                        'type' => 'product',
                        'score' => $score,
                    );
                }
            }
        }

        foreach ($this->build_article_internal_links($associated_pages) as $link) {
            $score = $this->score_recommendation_text(($link['label'] ?? '') . ' ' . ($link['url'] ?? ''), $tokens);
            if ($score > 0) {
                $link['type'] = 'associated_commerce_page';
                $link['score'] = $score + 1;
                $candidates[] = $link;
            }
        }

        $max_score = 0;
        foreach ($candidates as $candidate) {
            $max_score = max($max_score, intval($candidate['score'] ?? 0));
        }
        if ($max_score >= 2) {
            $candidates = array_values(array_filter($candidates, static function ($candidate) {
                return intval($candidate['score'] ?? 0) >= 2;
            }));
        }

        usort($candidates, function ($a, $b) {
            return intval($b['score'] ?? 0) <=> intval($a['score'] ?? 0);
        });

        $out = array();
        $seen = array();
        foreach ($candidates as $candidate) {
            $url = esc_url_raw((string) ($candidate['url'] ?? ''));
            $label = trim(wp_strip_all_tags((string) ($candidate['label'] ?? '')));
            if ($url === '' || $label === '') {
                continue;
            }
            $key = strtolower(untrailingslashit($url));
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $out[] = array(
                'url' => $url,
                'label' => $label,
                'type' => sanitize_key((string) ($candidate['type'] ?? 'commerce')),
            );
            if (count($out) >= 3) {
                break;
            }
        }

        return $out;
    }

    private function build_recommendation_tokens(array $keywords) {
        $stopwords = array('pentru','despre','cum','unde','cand','când','este','sunt','cele','cel','cea','din','in','în','la','pe','cu','si','și','sau','de','un','o','a','ale','al','the','and','for','with');
        $stop = array_fill_keys($stopwords, true);
        $tokens = array();
        foreach ($keywords as $keyword) {
            $text = strtolower(remove_accents((string) $keyword));
            $parts = preg_split('/[^a-z0-9]+/i', $text);
            foreach ((array) $parts as $part) {
                $part = trim($part);
                if (strlen($part) < 3 || isset($stop[$part])) {
                    continue;
                }
                $tokens[$part] = true;
            }
        }
        return array_keys($tokens);
    }

    private function score_recommendation_text($text, array $tokens) {
        $haystack = strtolower(remove_accents(wp_strip_all_tags((string) $text)));
        if ($haystack === '') {
            return 0;
        }

        $score = 0;
        foreach ($tokens as $token) {
            if ($token !== '' && strpos($haystack, $token) !== false) {
                $score++;
            }
        }
        return $score;
    }

    private function append_commerce_recommendations_to_content($content, $main_keyword, array $secondary_keywords, array $associated_pages = array()) {
        if (strpos((string) $content, 'aysa-commerce-inline-recommendation') !== false) {
            return $content;
        }

        $links = $this->build_commerce_recommendation_links($main_keyword, $secondary_keywords, $associated_pages);
        if (empty($links)) {
            return $content;
        }

        $items = array();
        foreach ($links as $link) {
            $url = esc_url((string) ($link['url'] ?? ''));
            $label = trim(wp_strip_all_tags((string) ($link['label'] ?? '')));
            if ($url === '' || $label === '') {
                continue;
            }
            $items[] = '<li><a href="' . $url . '">' . esc_html($label) . '</a></li>';
        }
        if (empty($items)) {
            return $content;
        }

        return trim($content) . "\n\n" . '<aside class="aysa-commerce-inline-recommendation"><h3>' . esc_html__('Recomandări utile', 'aysaaiseo') . '</h3><ul>' . implode('', $items) . '</ul></aside>';
    }

    private function append_associated_links_to_content($content, array $associated_pages) {
        $links = array();
        foreach ($this->build_article_internal_links($associated_pages) as $link) {
            $safe_url = esc_url((string) ($link['url'] ?? ''));
            $label = trim(wp_strip_all_tags((string) ($link['label'] ?? '')));
            if ($safe_url === '') {
                continue;
            }
            $links[] = '<a class="aysa-internal-link" href="' . $safe_url . '">' . esc_html($label !== '' ? $label : $safe_url) . '</a>';
        }
        if (empty($links)) {
            return $content;
        }
        return trim($content) . "\n\n<h3>Resurse utile</h3>\n<p>" . implode(' | ', $links) . '</p>';
    }

    private function apply_generated_blog_content_to_post($post_id, array $generated, array $associated_pages, $main_keyword, array $secondary_keywords) {
        $parts = array();
        foreach (array('tldr_content', 'introduction_content', 'extended_content', 'faq_html', 'conclusion_content') as $key) {
            if (!empty($generated[$key])) {
                $parts[] = $this->sanitize_content_html((string) $generated[$key]);
            }
        }
        $content_html = trim(implode("\n\n", array_filter($parts)));
        if ($content_html === '') {
            return array('success' => false, 'message' => 'Generated content is empty');
        }

        $content_html = $this->append_commerce_recommendations_to_content($content_html, $main_keyword, $secondary_keywords, $associated_pages);
        $content_html = $this->append_associated_links_to_content($content_html, $associated_pages);
        if (!empty($generated['inline_image_url'])) {
            $inline_url = esc_url_raw((string) $generated['inline_image_url']);
            $featured_url = esc_url_raw((string) ($generated['featured_image_url'] ?? ''));
            if ($inline_url !== '' && $inline_url !== $featured_url) {
                $inline_result = $this->download_and_attach_inline_image($post_id, $inline_url, array(
                    'alt_text' => (string) ($generated['inline_image_alt'] ?? ''),
                    'title' => (string) ($generated['inline_image_title'] ?? ''),
                    'caption' => (string) ($generated['inline_image_caption'] ?? ''),
                    'description' => (string) ($generated['inline_image_description'] ?? ''),
                    'main_keyword' => (string) $main_keyword,
                    'secondary_keywords' => array_values($secondary_keywords),
                ));
                if (is_wp_error($inline_result)) {
                    update_post_meta($post_id, '_aysa_inline_image_error', sanitize_text_field($inline_result->get_error_message()));
                } else {
                    $content_html = $this->insert_inline_image_figure($content_html, (array) $inline_result);
                    update_post_meta($post_id, 'aysa_inline_image_url', esc_url_raw((string) ($inline_result['url'] ?? $inline_url)));
                    delete_post_meta($post_id, '_aysa_inline_image_error');
                }
            }
        }

        $update = wp_update_post(array(
            'ID' => intval($post_id),
            'post_name' => $this->build_article_url_slug(
                (string) ($generated['url_recomandat'] ?? ''),
                get_the_title($post_id),
                $main_keyword,
                $secondary_keywords
            ),
            'post_content' => $content_html,
            'post_excerpt' => wp_trim_words(wp_strip_all_tags($content_html), 35, '...')
        ), true);

        if (is_wp_error($update)) {
            return array('success' => false, 'message' => $update->get_error_message());
        }

        if (!empty($generated['meta_title'])) {
            update_post_meta($post_id, 'aysa_meta_title', sanitize_text_field((string) $generated['meta_title']));
        }
        if (!empty($generated['meta_description'])) {
            update_post_meta($post_id, 'aysa_meta_description', sanitize_text_field((string) $generated['meta_description']));
        }
        if (!empty($generated['url_recomandat'])) {
            update_post_meta($post_id, 'aysa_url_recomandat', get_post_field('post_name', $post_id));
        }
        if (!empty($generated['outline'])) {
            update_post_meta($post_id, 'aysa_blog_content_outline', (string) $generated['outline']);
        }
        if (!empty($generated['faq_html'])) {
            update_post_meta($post_id, 'aysa_blog_content_faq', (string) $generated['faq_html']);
        }
        if (!empty($generated['featured_image_url'])) {
            update_post_meta($post_id, 'aysa_featured_image_url', esc_url_raw((string) $generated['featured_image_url']));
            $image_set_result = $this->download_and_set_featured_image($post_id, (string) $generated['featured_image_url'], array(
                'alt_text' => (string) ($generated['featured_image_alt'] ?? ''),
                'title' => (string) ($generated['featured_image_title'] ?? ''),
                'caption' => (string) ($generated['featured_image_caption'] ?? ''),
                'description' => (string) ($generated['featured_image_description'] ?? ''),
                'main_keyword' => (string) $main_keyword,
                'secondary_keywords' => array_values($secondary_keywords),
            ));
            if (is_wp_error($image_set_result)) {
                update_post_meta($post_id, '_aysa_featured_image_error', sanitize_text_field($image_set_result->get_error_message()));
            } else {
                delete_post_meta($post_id, '_aysa_featured_image_error');
            }
        }
        if (!empty($generated['pending_image_roles']) && is_array($generated['pending_image_roles'])) {
            $this->queue_article_image_retry($post_id, array_values($generated['pending_image_roles']), array(
                'article_topic' => get_the_title($post_id),
                'primary_keyword' => (string) $main_keyword,
                'secondary_keywords' => array_values($secondary_keywords),
                'language' => (string) get_option('aysa_language', 'ro'),
                'brand_name' => (string) get_option('aysa_brand_name', get_bloginfo('name')),
                'last_error' => trim((string) (($generated['featured_image_error'] ?? '') . ' ' . ($generated['inline_image_error'] ?? ''))),
            ));
        } elseif (empty($generated['inline_image_url'])) {
            $this->queue_article_image_retry($post_id, array('inline_primary'), array(
                'article_topic' => get_the_title($post_id),
                'primary_keyword' => (string) $main_keyword,
                'secondary_keywords' => array_values($secondary_keywords),
                'language' => (string) get_option('aysa_language', 'ro'),
                'brand_name' => (string) get_option('aysa_brand_name', get_bloginfo('name')),
                'last_error' => 'inline_image_missing',
            ));
        }

        update_post_meta($post_id, '_aysa_generated_content', array(
            'generated_at' => current_time('mysql'),
            'source' => 'content-strategy-cron',
            'primary_keyword' => $main_keyword,
            'secondary_keywords' => $secondary_keywords,
        ));

        $keyword_data = get_post_meta($post_id, 'aysa_keyword_data', true);
        if (!is_array($keyword_data)) {
            $keyword_data = array();
        }
        $keyword_data['article_topic'] = get_the_title($post_id);
        $keyword_data['keyword'] = $main_keyword;
        $keyword_data['second_keyword'] = implode(', ', $secondary_keywords);
        $keyword_data['outline'] = (string) ($generated['outline'] ?? '');
        $keyword_data['extended_content'] = (string) ($generated['extended_content'] ?? '');
        $keyword_data['faq_html'] = (string) ($generated['faq_html'] ?? '');
        $keyword_data['meta_title'] = (string) ($generated['meta_title'] ?? '');
        $keyword_data['meta_description'] = (string) ($generated['meta_description'] ?? '');
        update_post_meta($post_id, 'aysa_keyword_data', $keyword_data);

        $this->apply_semantic_taxonomy_to_post(
            intval($post_id),
            (string) get_the_title($post_id),
            (string) $main_keyword,
            array_values($secondary_keywords),
            $content_html,
            array_values($associated_pages)
        );

        return array('success' => true);
    }

    private function schedule_post_publication($post_id, $scheduled_for_mysql) {
        if ($scheduled_for_mysql === '') {
            return array('success' => false, 'message' => 'Missing scheduled date');
        }
        $scheduled_ts = strtotime($scheduled_for_mysql);
        if (!$scheduled_ts) {
            return array('success' => false, 'message' => 'Invalid scheduled date');
        }

        $now = current_time('timestamp');
        $status = ($scheduled_ts > ($now + 60)) ? 'future' : 'publish';

        $post_update = array(
            'ID' => intval($post_id),
            'post_status' => $status,
            'post_date' => wp_date('Y-m-d H:i:s', $scheduled_ts),
            'post_date_gmt' => gmdate('Y-m-d H:i:s', $scheduled_ts),
        );
        $result = wp_update_post($post_update, true);
        if (is_wp_error($result)) {
            return array('success' => false, 'message' => $result->get_error_message());
        }

        return array('success' => true);
    }

    private function append_internal_linking_report_entries($post_id, $source_title, $keyword, array $target_urls) {
        $log = get_option('aysa_internal_linking_log', array());
        if (!is_array($log)) {
            $log = array();
        }

        foreach ($target_urls as $target_url) {
            $target_url = esc_url_raw((string) $target_url);
            if ($target_url === '') {
                continue;
            }
            $log[] = array(
                'timestamp' => current_time('mysql'),
                'keyword' => $keyword,
                'target_url' => $target_url,
                'target_title' => $target_url,
                'source_entity_id' => intval($post_id),
                'source_entity_type' => 'post',
                'source_title' => sanitize_text_field($source_title),
                'is_partial' => false
            );
        }

        if (count($log) > 1000) {
            $log = array_slice($log, -1000);
        }
        update_option('aysa_internal_linking_log', $log);
    }

    private function get_broker_config() {
        $base_url = defined('AYSA_OAUTH_BROKER_BASE_URL')
            ? AYSA_OAUTH_BROKER_BASE_URL
            : (defined('AYSA_API_BASE_URL') ? AYSA_API_BASE_URL : 'https://api.aysa.ai');
        $key_id = defined('AYSA_OAUTH_BROKER_KEY_ID') ? AYSA_OAUTH_BROKER_KEY_ID : 'wp_plugin';
        $key_secret = defined('AYSA_OAUTH_BROKER_KEY_SECRET') ? AYSA_OAUTH_BROKER_KEY_SECRET : '';

        $base_url = rtrim((string) $base_url, '/');
        $key_id = trim((string) $key_id);
        $key_secret = trim((string) $key_secret);

        if ($base_url === '' || $key_id === '' || $key_secret === '') {
            return new WP_Error('broker_config_missing', 'OAuth broker is not configured on this site.');
        }

        return array(
            'base_url' => $base_url,
            'key_id' => $key_id,
            'key_secret' => $key_secret,
        );
    }

    private function broker_request($method, $path, $payload = array(), $timeout = 30) {
        $cfg = $this->get_broker_config();
        if (is_wp_error($cfg)) {
            return $cfg;
        }

        $method = strtoupper((string) $method);
        $path = '/' . ltrim((string) $path, '/');
        $url = $cfg['base_url'] . $path;
        $body = ($method === 'POST') ? wp_json_encode($payload) : '';
        if ($body === false) {
            $body = '{}';
        }

        $timestamp = (string) time();
        try {
            $nonce = bin2hex(random_bytes(16));
        } catch (Exception $e) {
            $nonce = wp_generate_password(32, false, false);
        }

        $body_hash = hash('sha256', $body);
        $canonical = implode("\n", array($method, $path, $body_hash, $timestamp, $nonce));
        $signature = hash_hmac('sha256', $canonical, $cfg['key_secret']);

        $headers = array(
            'Content-Type' => 'application/json',
            'X-Aysa-Key' => $cfg['key_id'],
            'X-Aysa-Timestamp' => $timestamp,
            'X-Aysa-Nonce' => $nonce,
            'X-Aysa-Signature' => $signature,
        );

        $args = array(
            'timeout' => max(10, min(120, intval($timeout))),
            'method' => $method,
            'headers' => $headers,
        );
        if ($method === 'POST') {
            $args['body'] = $body;
        }

        $resp = wp_remote_request($url, $args);
        if (is_wp_error($resp)) {
            return new WP_Error('broker_http_failed', 'Broker request failed: ' . $resp->get_error_message());
        }

        $code = wp_remote_retrieve_response_code($resp);
        $raw_body = (string) wp_remote_retrieve_body($resp);
        $data = json_decode($raw_body, true);
        if (!is_array($data)) {
            $snippet = substr(wp_strip_all_tags($raw_body), 0, 180);
            return new WP_Error('broker_invalid_response', 'Broker returned invalid response. [code=' . intval($code) . '] ' . $snippet);
        }
        if ($code < 200 || $code >= 300 || (isset($data['ok']) && !$data['ok'])) {
            $msg = isset($data['error']) ? (string) $data['error'] : 'Broker returned error.';
            if (isset($data['details']) && is_scalar($data['details'])) {
                $msg .= ': ' . (string) $data['details'];
            }
            return new WP_Error('broker_error', $msg . ' [code=' . intval($code) . ']');
        }

        return $data;
    }

    private function call_article_image_broker(array $payload) {
        $result = $this->broker_request('POST', '/v1/content/generate-article-image.php', $payload, 90);
        if (is_wp_error($result)) {
            return array('success' => false, 'message' => $result->get_error_message());
        }
        return array('success' => true, 'data' => is_array($result) ? $result : array());
    }

    private function queue_article_image_retry($post_id, array $roles, array $context) {
        $post_id = intval($post_id);
        $roles = array_values(array_unique(array_filter(array_map('sanitize_key', $roles))));
        if ($post_id <= 0 || empty($roles)) {
            return;
        }
        $existing = get_post_meta($post_id, '_aysa_article_image_retry_context', true);
        if (!is_array($existing)) {
            $existing = array();
        }
        update_post_meta($post_id, '_aysa_article_image_retry_pending', '1');
        update_post_meta($post_id, '_aysa_article_image_retry_roles', $roles);
        update_post_meta($post_id, '_aysa_article_image_retry_context', array_merge($existing, $context, array(
            'queued_at' => current_time('mysql'),
            'attempts' => intval($existing['attempts'] ?? 0),
        )));
        if (!wp_next_scheduled('aysa_process_article_images_now', array($post_id))) {
            wp_schedule_single_event(time() + 60, 'aysa_process_article_images_now', array($post_id));
        }
    }

    public function process_pending_article_images($post_id = 0) {
        $post_ids = array();
        $post_id = intval($post_id);
        if ($post_id > 0) {
            $post_ids[] = $post_id;
        } else {
            $query = new WP_Query(array(
                'post_type' => 'post',
                'post_status' => array('publish', 'future', 'draft'),
                'fields' => 'ids',
                'posts_per_page' => 5,
                // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Reviewed WordPress query: the metadata selector is required by the feature and is constrained by the surrounding post type, status, limit, or administrative scope.
                'meta_key' => '_aysa_article_image_retry_pending',
                // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- Reviewed WordPress query: the metadata selector is required by the feature and is constrained by the surrounding post type, status, limit, or administrative scope.
                'meta_value' => '1',
                'orderby' => 'date',
                'order' => 'ASC',
                'no_found_rows' => true,
            ));
            $post_ids = array_map('intval', (array) $query->posts);
        }

        foreach ($post_ids as $pending_post_id) {
            $this->process_pending_article_images_for_post($pending_post_id);
        }
    }

    private function process_pending_article_images_for_post($post_id) {
        $post_id = intval($post_id);
        $context = get_post_meta($post_id, '_aysa_article_image_retry_context', true);
        $roles = get_post_meta($post_id, '_aysa_article_image_retry_roles', true);
        if ($post_id <= 0 || !is_array($context) || !is_array($roles) || empty($roles)) {
            return;
        }
        $attempts = intval($context['attempts'] ?? 0);
        if ($attempts >= 5) {
            update_post_meta($post_id, '_aysa_article_image_retry_pending', 'failed');
            return;
        }

        $payload_base = array(
            'project_id' => intval(get_option('aysa_project_id', 0)),
            'site_host' => wp_parse_url(home_url(), PHP_URL_HOST),
            'article_topic' => (string) ($context['article_topic'] ?? get_the_title($post_id)),
            'primary_keyword' => (string) ($context['primary_keyword'] ?? get_the_title($post_id)),
            'secondary_keywords' => isset($context['secondary_keywords']) && is_array($context['secondary_keywords']) ? array_values($context['secondary_keywords']) : array(),
            'language' => (string) ($context['language'] ?? get_option('aysa_language', 'ro')),
            'brand_name' => (string) ($context['brand_name'] ?? get_option('aysa_brand_name', get_bloginfo('name'))),
            'post_id' => intval($post_id),
        );

        $remaining = array();
        $roles = array_values($roles);
        foreach ($roles as $role_index => $role) {
            $role = sanitize_key((string) $role);
            if ($role === 'featured' && has_post_thumbnail($post_id)) {
                continue;
            }
            if ($role === 'inline_primary' && strpos((string) get_post_field('post_content', $post_id), 'aysa-article-inline-image') !== false) {
                continue;
            }

            $image = $this->call_article_image_broker($payload_base + array('image_roles' => array($role)));
            if (!$image['success']) {
                $context['last_error'] = 'broker_' . $role . ': ' . (string) ($image['message'] ?? 'unknown_error');
                $remaining[] = $role;
                $remaining = array_merge($remaining, array_slice($roles, $role_index + 1));
                break;
            }
            $remaining = array_merge($remaining, array_slice($roles, $role_index + 1));
            if (!empty($remaining)) {
                $context['last_deferred_roles'] = array_values(array_unique($remaining));
            }
            if (empty($remaining)) {
                unset($context['last_deferred_roles']);
            }
            $processed_current_role = true;
            if (!$processed_current_role) {
                continue;
            }
            $image_data = $this->extract_article_image_by_role($image['data'], $role);
            $image_url = $this->extract_first_non_empty_value($image_data ?: $image['data'], array('image_url', 'imageUrl', 'ImageUrl', 'url'));
            if ($image_url === '') {
                $context['last_error'] = 'missing_image_url_' . $role;
                $remaining[] = $role;
                $remaining = array_values(array_unique($remaining));
                break;
            }
            if (isset($context['last_error']) && strpos((string) $context['last_error'], $role) !== false) {
                unset($context['last_error']);
            }

            $meta = array(
                'alt_text' => $this->extract_first_non_empty_value($image_data ?: $image['data'], array('alt_text', 'altText', 'AltText')),
                'title' => $this->extract_first_non_empty_value($image_data ?: $image['data'], array('title', 'image_title', 'imageTitle')),
                'caption' => $this->extract_first_non_empty_value($image_data ?: $image['data'], array('caption', 'associated_text', 'associatedText')),
                'description' => $this->extract_first_non_empty_value($image_data ?: $image['data'], array('description', 'image_description', 'imageDescription')),
                'main_keyword' => (string) ($context['primary_keyword'] ?? ''),
                'secondary_keywords' => isset($context['secondary_keywords']) && is_array($context['secondary_keywords']) ? array_values($context['secondary_keywords']) : array(),
            );

            if ($role === 'featured') {
                $result = $this->download_and_set_featured_image($post_id, $image_url, $meta);
                if (is_wp_error($result)) {
                    $context['last_error'] = 'featured_download_' . $result->get_error_code() . ': ' . $result->get_error_message();
                    $remaining[] = $role;
                }
            } elseif ($role === 'inline_primary') {
                $result = $this->download_and_attach_inline_image($post_id, $image_url, $meta);
                if (is_wp_error($result)) {
                    $context['last_error'] = 'inline_download_' . $result->get_error_code() . ': ' . $result->get_error_message();
                    $remaining[] = $role;
                } else {
                    $content = (string) get_post_field('post_content', $post_id);
                    wp_update_post(array(
                        'ID' => $post_id,
                        'post_content' => $this->insert_inline_image_figure($content, (array) $result),
                    ));
                }
            }
            $remaining = array_values(array_unique($remaining));
            break;
        }

        if (empty($remaining)) {
            delete_post_meta($post_id, '_aysa_article_image_retry_pending');
            delete_post_meta($post_id, '_aysa_article_image_retry_roles');
            delete_post_meta($post_id, '_aysa_article_image_retry_context');
            return;
        }

        $context['attempts'] = $attempts + 1;
        $context['last_attempt_at'] = current_time('mysql');
        update_post_meta($post_id, '_aysa_article_image_retry_roles', array_values(array_unique($remaining)));
        update_post_meta($post_id, '_aysa_article_image_retry_context', $context);
        if (!wp_next_scheduled('aysa_process_article_images_now', array($post_id))) {
            wp_schedule_single_event(time() + 300, 'aysa_process_article_images_now', array($post_id));
        }
    }

    private function extract_article_image_by_role($source, $role) {
        if (!is_array($source)) {
            return array();
        }
        $role = (string) $role;
        $images = isset($source['images']) && is_array($source['images']) ? $source['images'] : array();
        foreach ($images as $image) {
            if (is_array($image) && (string) ($image['role'] ?? '') === $role) {
                return $image;
            }
        }
        return array();
    }

    private function download_and_set_featured_image($post_id, $image_url, array $image_meta = array()) {
        $post_id = intval($post_id);
        $image_url = esc_url_raw((string) $image_url);
        if ($post_id <= 0 || $image_url === '') {
            return new WP_Error('invalid_params', 'Missing post_id or image_url.');
        }

        if (!function_exists('media_handle_sideload')) {
            require_once ABSPATH . 'wp-admin/includes/media.php';
            require_once ABSPATH . 'wp-admin/includes/file.php';
            require_once ABSPATH . 'wp-admin/includes/image.php';
        }

        $tmp = download_url($image_url, 45);
        if (is_wp_error($tmp)) {
            return $tmp;
        }

        $path = wp_parse_url($image_url, PHP_URL_PATH);
        $name = basename((string) $path);
        if ($name === '' || $name === '/' || strpos($name, '.') === false) {
            $name = 'aysa-featured-' . $post_id . '.png';
        }
        $file_array = array(
            'name' => sanitize_file_name($name),
            'tmp_name' => $tmp,
        );

        $attachment_id = media_handle_sideload($file_array, $post_id, 'AYSA Generated Featured Image');
        if (is_wp_error($attachment_id)) {
            wp_delete_file($tmp);
            return $attachment_id;
        }

        if (!set_post_thumbnail($post_id, $attachment_id)) {
            return new WP_Error('set_thumbnail_failed', 'Could not set featured image.');
        }

        $alt_text = sanitize_text_field((string) ($image_meta['alt_text'] ?? ''));
        if ($alt_text === '') {
            $alt_text = $this->build_featured_image_alt_fallback($post_id, $image_meta);
        }
        if ($alt_text !== '') {
            update_post_meta($attachment_id, '_wp_attachment_image_alt', $alt_text);
        }

        $attachment_title = sanitize_text_field((string) ($image_meta['title'] ?? ''));
        if ($attachment_title === '') {
            $attachment_title = get_the_title($post_id) ?: 'AYSA generated image';
        }
        $attachment_excerpt = sanitize_text_field((string) ($image_meta['caption'] ?? ''));
        $attachment_content = wp_kses_post((string) ($image_meta['description'] ?? ''));
        wp_update_post(array(
            'ID' => intval($attachment_id),
            'post_title' => $attachment_title,
            'post_excerpt' => $attachment_excerpt,
            'post_content' => $attachment_content,
        ));

        return array('attachment_id' => intval($attachment_id));
    }

    private function download_and_attach_inline_image($post_id, $image_url, array $image_meta = array()) {
        $post_id = intval($post_id);
        $image_url = esc_url_raw((string) $image_url);
        if ($post_id <= 0 || $image_url === '') {
            return new WP_Error('invalid_params', 'Missing post_id or image_url.');
        }

        if (!function_exists('media_handle_sideload')) {
            require_once ABSPATH . 'wp-admin/includes/media.php';
            require_once ABSPATH . 'wp-admin/includes/file.php';
            require_once ABSPATH . 'wp-admin/includes/image.php';
        }

        $tmp = download_url($image_url, 45);
        if (is_wp_error($tmp)) {
            return $tmp;
        }

        $path = wp_parse_url($image_url, PHP_URL_PATH);
        $name = basename((string) $path);
        if ($name === '' || $name === '/' || strpos($name, '.') === false) {
            $name = 'aysa-inline-' . $post_id . '.png';
        }
        $file_array = array(
            'name' => sanitize_file_name($name),
            'tmp_name' => $tmp,
        );

        $attachment_id = media_handle_sideload($file_array, $post_id, 'AYSA Generated Inline Article Image');
        if (is_wp_error($attachment_id)) {
            wp_delete_file($tmp);
            return $attachment_id;
        }

        $alt_text = sanitize_text_field((string) ($image_meta['alt_text'] ?? ''));
        if ($alt_text === '') {
            $alt_text = $this->build_featured_image_alt_fallback($post_id, $image_meta);
        }
        if ($alt_text !== '') {
            update_post_meta($attachment_id, '_wp_attachment_image_alt', $alt_text);
        }

        $attachment_title = sanitize_text_field((string) ($image_meta['title'] ?? ''));
        if ($attachment_title === '') {
            $attachment_title = get_the_title($post_id) ?: 'AYSA generated inline image';
        }
        $attachment_excerpt = sanitize_text_field((string) ($image_meta['caption'] ?? ''));
        $attachment_content = wp_kses_post((string) ($image_meta['description'] ?? ''));
        wp_update_post(array(
            'ID' => intval($attachment_id),
            'post_title' => $attachment_title,
            'post_excerpt' => $attachment_excerpt,
            'post_content' => $attachment_content,
        ));

        return array(
            'attachment_id' => intval($attachment_id),
            'url' => wp_get_attachment_url($attachment_id),
            'alt_text' => $alt_text,
            'caption' => $attachment_excerpt,
        );
    }

    private function insert_inline_image_figure($content_html, array $image) {
        $url = esc_url((string) ($image['url'] ?? ''));
        if ($url === '') {
            return $content_html;
        }
        $alt = esc_attr((string) ($image['alt_text'] ?? ''));
        $caption = sanitize_text_field((string) ($image['caption'] ?? ''));
        $figure = '<figure class="aysa-article-inline-image"><img src="' . $url . '" alt="' . $alt . '" loading="lazy" />';
        if ($caption !== '') {
            $figure .= '<figcaption>' . esc_html($caption) . '</figcaption>';
        }
        $figure .= '</figure>';

        $content_html = (string) $content_html;
        if (strpos($content_html, 'aysa-article-inline-image') !== false) {
            return $content_html;
        }
        if (preg_match('/<\/p>/i', $content_html, $match, PREG_OFFSET_CAPTURE)) {
            $pos = intval($match[0][1]) + strlen($match[0][0]);
            return substr($content_html, 0, $pos) . "\n\n" . $figure . "\n\n" . substr($content_html, $pos);
        }
        return $content_html . "\n\n" . $figure;
    }

    private function build_featured_image_alt_fallback($post_id, array $image_meta) {
        $main = sanitize_text_field((string) ($image_meta['main_keyword'] ?? ''));
        $title = sanitize_text_field((string) get_the_title($post_id));
        $secondary = isset($image_meta['secondary_keywords']) && is_array($image_meta['secondary_keywords'])
            ? array_values(array_filter(array_map('sanitize_text_field', $image_meta['secondary_keywords'])))
            : array();

        $parts = array();
        if ($main !== '') {
            $parts[] = $main;
        }
        if ($title !== '' && stripos($title, $main) === false) {
            $parts[] = $title;
        }
        if (!empty($secondary)) {
            $first_secondary = (string) $secondary[0];
            if ($first_secondary !== '' && stripos($first_secondary, $main) === false) {
                $parts[] = $first_secondary;
            }
        }

        $text = trim(implode(' - ', array_filter($parts)));
        if ($text === '') {
            $text = 'AYSA generated featured image';
        }
        return sanitize_text_field(mb_substr($text, 0, 180));
    }

    private function apply_semantic_taxonomy_to_post($post_id, $title, $main_keyword, array $secondary_keywords, $content_html, array $associated_pages) {
        $post_id = intval($post_id);
        if ($post_id <= 0) {
            return;
        }

        $best_category_id = $this->resolve_best_category_id($title, $main_keyword, $secondary_keywords, $content_html, $associated_pages);
        if ($best_category_id > 0) {
            wp_set_post_categories($post_id, array($best_category_id), false);
            update_post_meta($post_id, '_aysa_semantic_category_id', intval($best_category_id));
        }

        $tag_names = $this->generate_semantic_tags_via_broker($post_id, $title, $main_keyword, $secondary_keywords, $content_html);
        if (empty($tag_names)) {
            $tag_names = $this->build_semantic_tags($main_keyword, $secondary_keywords, $title, $content_html);
        }
        if (!empty($tag_names)) {
            wp_set_post_tags($post_id, $tag_names, false);
            update_post_meta($post_id, '_aysa_semantic_tags', $tag_names);
        }
    }

    private function generate_semantic_tags_via_broker($post_id, $title, $main_keyword, array $secondary_keywords, $content_html) {
        $payload = array(
            'project_id' => intval(get_option('aysa_project_id', 0)),
            'post_id' => intval($post_id),
            'title' => (string) $title,
            'article_topic' => (string) $title,
            'primary_keyword' => (string) $main_keyword,
            'secondary_keywords' => array_values($secondary_keywords),
            'content_text' => wp_strip_all_tags((string) $content_html),
            'language' => (string) get_option('aysa_language', 'ro'),
        );
        $result = $this->broker_request('POST', '/v1/content/generate-article-tags.php', $payload, 60);
        if (is_wp_error($result) || empty($result['ok']) || !is_array($result['tags'] ?? null)) {
            return array();
        }
        $tags = array();
        foreach ($result['tags'] as $tag) {
            $tag = sanitize_text_field((string) $tag);
            if ($tag !== '' && mb_strlen($tag) <= 48) {
                $tags[] = $tag;
            }
        }
        return array_values(array_unique(array_slice($tags, 0, 8)));
    }

    private function resolve_best_category_id($title, $main_keyword, array $secondary_keywords, $content_html, array $associated_pages) {
        $terms = get_terms(array(
            'taxonomy' => 'category',
            'hide_empty' => false,
        ));
        if (is_wp_error($terms) || empty($terms)) {
            return intval(get_option('default_category', 0));
        }

        $context_raw = trim(implode(' ', array(
            (string) $title,
            (string) $main_keyword,
            implode(' ', array_values($secondary_keywords)),
            wp_strip_all_tags((string) $content_html),
            implode(' ', array_values($associated_pages)),
        )));

        $context_norm = $this->normalize_semantic_text($context_raw);
        $context_tokens = $this->tokenize_semantic_text($context_norm);
        $best_id = 0;
        $best_score = -1;

        foreach ($terms as $term) {
            if (!($term instanceof WP_Term)) {
                continue;
            }
            $term_name = $this->normalize_semantic_text((string) $term->name);
            $term_slug = $this->normalize_semantic_text((string) $term->slug);
            $term_desc = $this->normalize_semantic_text((string) $term->description);
            $term_tokens = array_unique(array_merge(
                $this->tokenize_semantic_text($term_name),
                $this->tokenize_semantic_text($term_slug),
                $this->tokenize_semantic_text($term_desc)
            ));

            $score = 0;
            foreach ($term_tokens as $token) {
                if (isset($context_tokens[$token])) {
                    $score += 2;
                }
            }

            if ($term_name !== '' && strpos($context_norm, $term_name) !== false) {
                $score += 6;
            }
            if ($term_slug !== '' && strpos($context_norm, $term_slug) !== false) {
                $score += 4;
            }

            foreach ($associated_pages as $url) {
                $path = strtolower((string) wp_parse_url((string) $url, PHP_URL_PATH));
                if ($term_slug !== '' && $path !== '' && strpos($path, '/' . $term_slug) !== false) {
                    $score += 10;
                }
            }

            if ($score > $best_score) {
                $best_score = $score;
                $best_id = intval($term->term_id);
            }
        }

        if ($best_id <= 0) {
            $best_id = intval(get_option('default_category', 0));
        }
        return $best_id;
    }

    private function build_semantic_tags($main_keyword, array $secondary_keywords, $title, $content_html) {
        $main_norm = $this->normalize_semantic_text((string) $main_keyword);
        $main_tokens = $this->tokenize_semantic_text($main_norm);

        $candidates = array();
        foreach ($secondary_keywords as $kw) {
            $kw_clean = sanitize_text_field((string) $kw);
            if ($kw_clean === '') {
                continue;
            }
            $kw_norm = $this->normalize_semantic_text($kw_clean);
            if ($kw_norm === '' || $kw_norm === $main_norm) {
                continue;
            }
            if ($this->is_keyword_too_close_to_main($kw_norm, $main_tokens)) {
                continue;
            }
            $candidates[$kw_norm] = $kw_clean;
        }

        if (count($candidates) < 4) {
            $title_tokens = array_keys($this->tokenize_semantic_text($this->normalize_semantic_text((string) $title)));
            $content_tokens = array_keys($this->tokenize_semantic_text($this->normalize_semantic_text(wp_strip_all_tags((string) $content_html))));
            $pool = array_slice(array_values(array_unique(array_merge($title_tokens, $content_tokens))), 0, 40);
            foreach ($pool as $token) {
                if (strlen($token) < 4) {
                    continue;
                }
                if (isset($main_tokens[$token])) {
                    continue;
                }
                if (isset($candidates[$token])) {
                    continue;
                }
                $candidates[$token] = $token;
                if (count($candidates) >= 8) {
                    break;
                }
            }
        }

        return array_values(array_slice($candidates, 0, 8));
    }

    private function is_keyword_too_close_to_main($keyword_norm, array $main_tokens) {
        if ($keyword_norm === '') {
            return true;
        }
        foreach (array_keys($main_tokens) as $token) {
            if ($token !== '' && strpos($keyword_norm, $token) !== false) {
                return true;
            }
        }
        return false;
    }

    private function normalize_semantic_text($text) {
        $text = remove_accents((string) $text);
        $text = function_exists('mb_strtolower') ? mb_strtolower($text, 'UTF-8') : strtolower($text);
        $text = preg_replace('/[^a-z0-9\s\-]+/i', ' ', $text);
        $text = preg_replace('/\s+/', ' ', (string) $text);
        return trim((string) $text);
    }

    private function tokenize_semantic_text($text) {
        $tokens = preg_split('/\s+/', (string) $text);
        $map = array();
        foreach ((array) $tokens as $token) {
            $token = trim((string) $token);
            if ($token === '' || strlen($token) < 3) {
                continue;
            }
            $map[$token] = true;
        }
        return $map;
    }

    private function infer_recommended_page_type_from_title($title) {
        $normalized = strtolower((string) $title);
        if (strpos($normalized, 'create product page') !== false) {
            return 'Product Page';
        }
        if (strpos($normalized, 'create category page') !== false) {
            return 'Category Page';
        }
        if (strpos($normalized, 'create landing page') !== false) {
            return 'Landing Page';
        }
        return '';
    }

    private function recommended_page_type_from_key($page_type_key) {
        $key = sanitize_key((string) $page_type_key);
        if ($key === 'product') {
            return 'Product Page';
        }
        if ($key === 'category') {
            return 'Category Page';
        }
        if ($key === 'landing_page') {
            return 'Landing Page';
        }
        return '';
    }

    private function normalize_recommended_page_type_label($label) {
        $value = strtolower(trim((string) $label));
        if ($value === '') {
            return '';
        }
        if (strpos($value, 'product') !== false) {
            return 'Product Page';
        }
        if (strpos($value, 'category') !== false) {
            return 'Category Page';
        }
        return 'Landing Page';
    }

    private function map_recommended_page_type_to_content_type($label) {
        $normalized = $this->normalize_recommended_page_type_label($label);
        if ($normalized === 'Product Page') {
            return 'product_page';
        }
        if ($normalized === 'Category Page') {
            return 'category_page';
        }
        if ($normalized === 'Landing Page') {
            return 'landing_page';
        }
        return 'post';
    }

    private function backfill_missing_pages_strategy_fields() {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id, title, keywords, intent, strategy_type, recommended_page_type, content_type, execution_stage, status
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM {$this->table_name}
             WHERE strategy_type = 'missing_pages'
             LIMIT 5000",
            ARRAY_A
        );
        if (!is_array($rows) || empty($rows)) {
            return;
        }

        $meta_table = esc_sql($wpdb->prefix . 'aysa_missing_page_meta');
        $meta_map = array();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $meta_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $meta_table));
        if ($meta_exists === $meta_table) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $meta_rows = $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
                "SELECT strategy_id, page_type
" .
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
                "                 FROM {$meta_table}
                 WHERE strategy_id IS NOT NULL",
                ARRAY_A
            );
            if (is_array($meta_rows)) {
                foreach ($meta_rows as $meta_row) {
                    $sid = intval($meta_row['strategy_id'] ?? 0);
                    if ($sid <= 0) {
                        continue;
                    }
                    $meta_map[$sid] = sanitize_key((string) ($meta_row['page_type'] ?? ''));
                }
            }
        }

        foreach ($rows as $row) {
            $id = intval($row['id'] ?? 0);
            if ($id <= 0) {
                continue;
            }
            $recommended = $this->normalize_recommended_page_type_label((string) ($row['recommended_page_type'] ?? ''));
            if ($recommended === '') {
                $recommended = $this->recommended_page_type_from_key((string) ($meta_map[$id] ?? ''));
            }
            if ($recommended === '') {
                $recommended = $this->normalize_recommended_page_type_label($this->infer_recommended_page_type_from_title((string) ($row['title'] ?? '')));
            }
            $primary_keyword = $this->extract_primary_keyword_from_payload((string) ($row['keywords'] ?? ''), (string) ($row['title'] ?? ''));
            $intent_value = strtoupper(trim((string) ($row['intent'] ?? '')));
            if ($recommended === '' || $recommended === 'Landing Page') {
                $recommended = $this->infer_recommended_page_type_from_keyword($primary_keyword, $intent_value, $recommended);
            }
            if ($recommended === '') {
                $recommended = 'Landing Page';
            }

            $current_content_type = sanitize_key((string) ($row['content_type'] ?? ''));
            $mapped_content_type = $this->map_recommended_page_type_to_content_type($recommended);
            $content_type = $current_content_type;
            if ($content_type === '' || $content_type === 'post' || $content_type !== $mapped_content_type) {
                $content_type = $mapped_content_type;
            }

            $execution_stage = sanitize_key((string) ($row['execution_stage'] ?? ''));
            if ($execution_stage === '') {
                $execution_stage = ((string) ($row['status'] ?? '') === 'generated') ? 'published' : 'planned';
                if ($execution_stage === 'planned' && isset($meta_map[$id]) && (string) ($meta_map[$id] ?? '') !== '') {
                    $execution_stage = 'seo_meta_ready';
                }
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
            $wpdb->update(
                $this->table_name,
                array(
                    'recommended_page_type' => $recommended,
                    'content_type' => $content_type,
                    'execution_stage' => $execution_stage,
                ),
                array('id' => $id),
                array('%s', '%s', '%s'),
                array('%d')
            );
        }
    }

    private function backfill_editorial_identity_fields() {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $rows = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id, project_id, title, keywords, intent, associated_page, strategy_type, recommended_page_type, content_type, status, post_id, scheduled_for, automation_status
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM {$this->table_name}
             WHERE canonical_topic IS NULL
                OR canonical_topic = ''
                OR content_fingerprint IS NULL
                OR content_fingerprint = ''
                OR intent_layer IS NULL
                OR journey_stage IS NULL
             ORDER BY project_id ASC, id ASC
             LIMIT 5000",
            ARRAY_A
        );
        if (!is_array($rows) || empty($rows)) {
            return;
        }

        $by_project = array();
        foreach ($rows as $row) {
            $project_id = intval($row['project_id'] ?? 0);
            if ($project_id <= 0) {
                continue;
            }
            if (!isset($by_project[$project_id])) {
                $by_project[$project_id] = array();
            }
            $by_project[$project_id][] = $row;
        }

        foreach ($by_project as $project_rows) {
            $fingerprint_owner = array();
            $near_owner = array();

            foreach ($project_rows as $row) {
                $id = intval($row['id'] ?? 0);
                if ($id <= 0) {
                    continue;
                }

                $primary_keyword = $this->extract_primary_keyword_from_payload((string) ($row['keywords'] ?? ''), (string) ($row['title'] ?? ''));
                $associated_page = $this->normalize_associated_page((string) ($row['associated_page'] ?? ''));
                $recommended = $this->normalize_recommended_page_type_label((string) ($row['recommended_page_type'] ?? ''));
                $content_type = sanitize_key((string) ($row['content_type'] ?? ''));
                if ($content_type === '') {
                    $content_type = $this->map_recommended_page_type_to_content_type($recommended);
                }

                $identity = $this->build_editorial_identity_payload(array(
                    'title' => (string) ($row['title'] ?? ''),
                    'keywords' => (string) ($row['keywords'] ?? ''),
                    'intent' => (string) ($row['intent'] ?? ''),
                    'strategy_type' => (string) ($row['strategy_type'] ?? ''),
                    'associated_page' => $associated_page,
                    'recommended_page_type' => $recommended,
                    'content_type' => $content_type,
                ), $primary_keyword);

                $status = sanitize_text_field((string) ($row['status'] ?? 'proposed'));
                $duplicate_of = null;
                $superseded_by = null;

                $fingerprint = (string) ($identity['content_fingerprint'] ?? '');
                if ($fingerprint !== '') {
                    if (isset($fingerprint_owner[$fingerprint])) {
                        $canonical_id = intval($fingerprint_owner[$fingerprint]);
                        if ($canonical_id > 0 && $canonical_id !== $id) {
                            $status = 'duplicate';
                            $duplicate_of = $canonical_id;
                        }
                    } else {
                        $fingerprint_owner[$fingerprint] = $id;
                    }
                }

                $near_key = implode('|', array(
                    (string) ($identity['canonical_topic'] ?? ''),
                    (string) ($identity['intent_layer'] ?? ''),
                    (string) ($identity['journey_stage'] ?? ''),
                    (string) ($identity['content_goal'] ?? ''),
                    strtolower($associated_page),
                ));
                if ($status !== 'duplicate' && $near_key !== '||||') {
                    if (isset($near_owner[$near_key])) {
                        $canonical_id = intval($near_owner[$near_key]);
                        if ($canonical_id > 0 && $canonical_id !== $id) {
                            $status = 'superseded';
                            $superseded_by = $canonical_id;
                        }
                    } else {
                        $near_owner[$near_key] = $id;
                    }
                }

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
                $wpdb->update(
                    $this->table_name,
                    array(
                        'canonical_topic' => $identity['canonical_topic'],
                        'content_fingerprint' => $identity['content_fingerprint'],
                        'intent_layer' => $identity['intent_layer'],
                        'journey_stage' => $identity['journey_stage'],
                        'persona_key' => $identity['persona_key'],
                        'locality_key' => $identity['locality_key'],
                        'seasonality_key' => $identity['seasonality_key'],
                        'content_goal' => $identity['content_goal'],
                        'status' => $status,
                        'duplicate_of_strategy_id' => $duplicate_of,
                        'superseded_by_strategy_id' => $superseded_by,
                    ),
                    array('id' => $id),
                    array('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d'),
                    array('%d')
                );
            }
        }
    }

    private function build_editorial_identity_payload($row, $primary_keyword = '') {
        $title = trim((string) ($row['title'] ?? ''));
        $strategy_type = sanitize_key((string) ($row['strategy_type'] ?? ''));
        $recommended = $this->normalize_recommended_page_type_label((string) ($row['recommended_page_type'] ?? ''));
        $content_type = sanitize_key((string) ($row['content_type'] ?? ''));
        $associated_page = $this->normalize_associated_page((string) ($row['associated_page'] ?? ''));
        $primary_keyword = trim((string) $primary_keyword);
        if ($primary_keyword === '') {
            $primary_keyword = $this->extract_primary_keyword_from_payload((string) ($row['keywords'] ?? ''), $title);
        }

        $haystack = strtolower(remove_accents(trim($title . ' ' . $primary_keyword . ' ' . (string) ($row['keywords'] ?? ''))));
        $intent_layer = ($strategy_type === 'keyword_mapping' || $content_type === 'existing_page_optimization') ? 'commercial' : 'informational';
        $journey_stage = ($strategy_type === 'keyword_mapping' || $recommended === 'Landing Page') ? 'decision' : 'awareness';
        if (preg_match('/\b(pret|preț|comanda|comandă|livrare|cumpara|cumpără)\b/u', $haystack)) {
            $intent_layer = 'commercial';
            $journey_stage = 'consideration';
        }

        $persona_key = $this->match_editorial_token($haystack, $this->get_editorial_persona_tokens());
        $locality_key = $this->match_editorial_token($haystack, $this->get_editorial_locality_tokens());
        $seasonality_key = $this->match_editorial_token($haystack, array('primavara', 'primăvara', 'vara', 'toamna', 'toamnă', 'iarna', 'paste', 'paște', 'craciun', 'crăciun', '8 martie', 'valentine'));

        $content_goal = 'publish_supporting_article';
        if ($strategy_type === 'missing_pages') {
            $content_goal = 'capture_missing_demand';
        } elseif ($strategy_type === 'keyword_mapping') {
            $content_goal = 'optimize_existing_coverage';
        } elseif ($recommended === 'Category Page') {
            $content_goal = 'expand_category_coverage';
        }

        $canonical_parts = array_filter(array(
            $this->normalize_editorial_token($primary_keyword),
            $this->normalize_editorial_token($locality_key),
            $this->normalize_editorial_token($seasonality_key),
        ));
        $canonical_topic = !empty($canonical_parts)
            ? implode('|', array_unique($canonical_parts))
            : $this->normalize_editorial_token($title);

        $fingerprint = hash('sha256', implode('|', array(
            $canonical_topic,
            $this->normalize_editorial_token($associated_page),
            $intent_layer,
            $journey_stage,
            $this->normalize_editorial_token($persona_key),
            $this->normalize_editorial_token($locality_key),
            $this->normalize_editorial_token($seasonality_key),
            $content_goal,
            $content_type,
        )));

        return array(
            'canonical_topic' => $canonical_topic,
            'content_fingerprint' => $fingerprint,
            'intent_layer' => $intent_layer,
            'journey_stage' => $journey_stage,
            'persona_key' => $this->normalize_editorial_token($persona_key),
            'locality_key' => $this->normalize_editorial_token($locality_key),
            'seasonality_key' => $this->normalize_editorial_token($seasonality_key),
            'content_goal' => $content_goal,
        );
    }

    private function match_editorial_token($haystack, $tokens) {
        $haystack = strtolower(remove_accents((string) $haystack));
        foreach ((array) $tokens as $token) {
            $candidate = strtolower(remove_accents((string) $token));
            if ($candidate !== '' && strpos($haystack, $candidate) !== false) {
                return (string) $token;
            }
        }
        return '';
    }

    private function get_editorial_locality_tokens() {
        $raw_values = array(
            get_option('aysa_city', ''),
            get_option('aysa_publication_location', ''),
            get_option('aysa_local_specifics', ''),
        );
        $tokens = array();
        foreach ($raw_values as $raw) {
            foreach (preg_split('/[,;|\n]+/', (string) $raw) as $part) {
                $part = trim(wp_strip_all_tags((string) $part));
                if ($part !== '' && strlen(remove_accents($part)) >= 3) {
                    $tokens[] = $part;
                }
            }
        }
        return array_values(array_unique($tokens));
    }

    private function get_editorial_persona_tokens() {
        $raw = (string) get_option('aysa_target_audience', '');
        $tokens = array();
        foreach (preg_split('/[,;|\n]+/', $raw) as $part) {
            $part = trim(wp_strip_all_tags((string) $part));
            if ($part !== '' && strlen(remove_accents($part)) >= 3) {
                $tokens[] = $part;
            }
        }
        return array_values(array_unique($tokens));
    }

    private function normalize_editorial_token($value) {
        $value = strtolower(remove_accents(trim((string) $value)));
        if ($value === '') {
            return '';
        }
        $value = preg_replace('/[^a-z0-9]+/', '-', $value);
        $value = is_string($value) ? trim($value, '-') : '';
        return is_string($value) ? preg_replace('/-+/', '-', $value) : '';
    }
    private function extract_primary_keyword_from_payload($keywords_payload, $fallback_title = '') {
        $payload = trim((string) $keywords_payload);
        if ($payload !== '') {
            $decoded = json_decode($payload, true);
            if (is_array($decoded) && isset($decoded[0]) && is_string($decoded[0])) {
                $kw = trim((string) $decoded[0]);
                if ($kw !== '') {
                    return $kw;
                }
            }
        }

        if (preg_match('/\bfor\s+(.+)$/iu', (string) $fallback_title, $m)) {
            $kw = trim((string) ($m[1] ?? ''));
            if ($kw !== '') {
                return $kw;
            }
        }

        return '';
    }

    private function infer_recommended_page_type_from_keyword($keyword, $intent = '', $fallback = 'Landing Page') {
        $kw = strtolower(remove_accents(trim((string) $keyword)));
        if ($kw === '') {
            return $fallback;
        }

        $landing_tokens = array_merge($this->get_editorial_locality_tokens(), array(
            'langa mine', 'near me', 'aproape', 'program', 'non stop', 'contact', 'locatie', 'locație',
            'local', 'zona', 'zone', 'oras', 'oraș', 'judet', 'județ', 'city', 'location', 'nearby',
        ));
        foreach ($landing_tokens as $token) {
            if (strpos($kw, $token) !== false) {
                return 'Landing Page';
            }
        }

        $category_tokens = array(
            'categorie', 'categorii', 'colectie', 'colectii', 'colecție', 'colecții', 'servicii',
            'produse', 'products', 'services', 'category', 'categories', 'collection', 'collections',
            'oferta', 'oferte', 'ofertă', 'modele', 'tipuri', 'variante', 'delivery', 'livrare',
        );
        foreach ($category_tokens as $token) {
            if (strpos($kw, $token) !== false) {
                return 'Category Page';
            }
        }

        $has_numeric = preg_match('/\d+/', $kw) === 1;
        $product_tokens = array(
            'produs', 'product', 'model', 'set', 'pachet', 'pack', 'cod', 'sku', 'serie', 'variantă', 'varianta'
        );
        foreach ($product_tokens as $token) {
            if ($has_numeric && strpos($kw, $token) !== false) {
                return 'Product Page';
            }
        }
        if ($has_numeric && preg_match('/\b(lei|ron|euro)\b/u', $kw)) {
            return 'Product Page';
        }
        if ($has_numeric && in_array($intent, array('T', 'C', 'TRANSACTIONAL', 'COMMERCIAL'), true)) {
            return 'Product Page';
        }

        if (preg_match('/\b(cum|ce|cand|de ce|ghid|idei|sfaturi)\b/u', $kw)) {
            return 'Landing Page';
        }

        return $fallback;
    }

    private function interleave_strategies_by_content_type($strategies) {
        $rows = is_array($strategies) ? $strategies : array();
        if (empty($rows)) {
            return array();
        }

        $buckets = array();
        foreach ($rows as $row) {
            $type = sanitize_key((string) ($row['content_type'] ?? ''));
            if ($type === '') {
                $type = 'post';
            }
            if (!isset($buckets[$type])) {
                $buckets[$type] = array();
            }
            $buckets[$type][] = $row;
        }

        $priority = array('product_page', 'category_page', 'landing_page', 'post');
        $type_order = array();
        foreach ($priority as $t) {
            if (!empty($buckets[$t])) {
                $type_order[] = $t;
            }
        }
        foreach (array_keys($buckets) as $t) {
            if (!in_array($t, $type_order, true)) {
                $type_order[] = $t;
            }
        }

        $result = array();
        while (true) {
            $added = false;
            foreach ($type_order as $t) {
                if (empty($buckets[$t])) {
                    continue;
                }
                $result[] = array_shift($buckets[$t]);
                $added = true;
            }
            if (!$added) {
                break;
            }
        }
        return $result;
    }

    private function track_action_usage($project_id, $action_key, $reference = '', $meta = array(), $fallback_cost_cents = 0, $endpoint = '') {
        $project_id = intval($project_id);
        if ($project_id <= 0 || trim((string) $action_key) === '') {
            return;
        }

        if (!class_exists('Aysa_Wallet_Manager')) {
            $wallet_file = AYSA_PLUGIN_DIR . 'includes/class-aysa-wallet-manager.php';
            if (file_exists($wallet_file)) {
                require_once $wallet_file;
            }
        }
        if (!class_exists('Aysa_Wallet_Manager')) {
            return;
        }

        $wallet = Aysa_Wallet_Manager::get_instance();
        if (method_exists($wallet, 'track_action_usage')) {
            $wallet->track_action_usage(
                $project_id,
                sanitize_key((string) $action_key),
                (string) $reference,
                is_array($meta) ? $meta : array(),
                intval($fallback_cost_cents),
                (string) $endpoint
            );
            return;
        }

        $cost = max(0, intval($fallback_cost_cents));
        if ($cost <= 0 || !method_exists($wallet, 'apply_credit_delta') || !method_exists($wallet, 'enqueue_usage_event')) {
            return;
        }
        $payload = is_array($meta) ? $meta : array();
        $payload['server_side'] = true;
        $payload['user_id'] = get_current_user_id();

        if (method_exists($wallet, 'is_api_billed_action') && $wallet->is_api_billed_action($action_key)) {
            $payload['billing_authority'] = 'api_wallet';
            $wallet->enqueue_usage_event($project_id, (string) $endpoint, $cost, $payload);
            if (method_exists($wallet, 'push_usage_events_now')) {
                $wallet->push_usage_events_now($project_id, array(array(
                    'endpoint' => (string) $endpoint,
                    'cost_cents' => $cost,
                    'meta' => $payload,
                    'ts' => time(),
                )));
            }
            return;
        }

        $wallet->enqueue_usage_event($project_id, (string) $endpoint, $cost, $payload);
        $wallet->apply_credit_delta($project_id, -abs($cost), 'usage', 'wp_plugin_server', (string) $reference, null, $payload);
    }

    private function bill_generated_article_once($project_id, $post_id, $strategy_id, $source, $main_keyword) {
        $project_id = intval($project_id);
        $post_id = intval($post_id);
        $strategy_id = intval($strategy_id);
        if ($project_id <= 0 || $post_id <= 0) {
            return;
        }

        $reference = $strategy_id > 0
            ? 'content_article_generate:strategy:' . $strategy_id . ':post:' . $post_id
            : 'content_article_generate:post:' . $post_id;

        if (!$this->should_bill_unique_usage($project_id, $reference)) {
            update_post_meta($post_id, '_aysa_article_billing_status', 'skipped_duplicate');
            update_post_meta($post_id, '_aysa_article_billing_reference', $reference);
            return;
        }

        $this->track_action_usage(
            $project_id,
            'content_article_generate',
            $reference,
            array(
                'module' => 'content_strategy',
                'source' => sanitize_key((string) $source),
                'strategy_id' => $strategy_id,
                'post_id' => $post_id,
                'main_keyword' => sanitize_text_field((string) $main_keyword),
                'billing_scope' => 'content_article_generate',
                'billing_reference' => $reference,
                'idempotency_key' => $reference,
                'server_side' => true,
            ),
            95,
            '/v1/content/article/generate'
        );

        update_post_meta($post_id, '_aysa_article_billing_status', 'billed');
        update_post_meta($post_id, '_aysa_article_billing_reference', $reference);
        update_post_meta($post_id, '_aysa_article_billed_at', current_time('mysql'));
    }

    private function should_bill_unique_usage($project_id, $reference) {
        global $wpdb;

        $project_id = intval($project_id);
        $reference = sanitize_text_field((string) $reference);
        if ($project_id <= 0 || $reference === '') {
            return false;
        }

        $ledger_table = esc_sql($wpdb->prefix . 'aysa_wallet_ledger');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $ledger_table));
        if ($table_exists !== $ledger_table) {
            return true;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $existing_id = $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT id
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM {$ledger_table}
             WHERE project_id = %d
               AND entry_type = 'usage'
               AND source IN ('wp_plugin_action', 'wp_plugin_server')
               AND reference = %s
             LIMIT 1",
            $project_id,
            $reference
        ));

        return empty($existing_id);
    }

    private function should_bill_plan_content_schedule($project_id, $reference, $cooldown_seconds = 300) {
        global $wpdb;

        $project_id = intval($project_id);
        $reference = sanitize_text_field((string) $reference);
        $cooldown_seconds = max(30, intval($cooldown_seconds));
        if ($project_id <= 0 || $reference === '') {
            return false;
        }

        $ledger_table = esc_sql($wpdb->prefix . 'aysa_wallet_ledger');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $ledger_table));
        if ($table_exists !== $ledger_table) {
            return true;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reviewed release query uses WordPress or plugin-owned operational tables; current job, billing and idempotency state must not be cached because stale reads can duplicate or mis-order work.
        $last_created_at = $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare. Reviewed release SQL: interpolation is limited to escaped or allowlisted plugin-owned table identifiers and generated placeholder lists; all request-derived values are passed to prepare.
            "SELECT created_at
" .
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Reviewed release SQL: the interpolated identifier is an escaped or allowlisted plugin-owned table name; request-derived values remain prepared.
            "             FROM {$ledger_table}
             WHERE project_id = %d
               AND entry_type = 'usage'
               AND source = 'wp_plugin_action'
               AND reference = %s
             ORDER BY id DESC
             LIMIT 1",
            $project_id,
            $reference
        ));

        if (!is_string($last_created_at) || trim($last_created_at) === '') {
            return true;
        }

        $last_ts = strtotime($last_created_at);
        if ($last_ts === false) {
            return true;
        }

        $now_ts = current_time('timestamp');
        return (($now_ts - $last_ts) >= $cooldown_seconds);
    }
}

new Aysa_Content_Strategy_Manager();
