<?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';

    public function __construct() {
        global $wpdb;
        $this->table_name = $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('aysa_process_planned_content_now', array($this, 'process_planned_content_queue'));
        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 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;
        }
        $subject = (string) ($args['subject'] ?? '');
        $message = (string) ($args['message'] ?? '');
        if (stripos($subject . "\n" . $message, 'AYSA') === false && stripos($subject . "\n" . $message, 'Agent 8') === false) {
            return $args;
        }
        if (strpos($message, "Agent 8\nAYSA AI SEO Agent") !== false || strpos($message, 'AYSA AI SEO Agent') !== false) {
            return $args;
        }

        $headers = $args['headers'] ?? array();
        $headers_text = is_array($headers) ? implode("\n", $headers) : (string) $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 = $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');
        }

        $strategies = isset($_POST['strategies']) ? json_decode(stripslashes($_POST['strategies']), true) : array();
        $project_id = isset($_POST['project_id']) ? intval($_POST['project_id']) : 0;
        $strategy_type = isset($_POST['strategy_type']) ? sanitize_text_field($_POST['strategy_type']) : 'unknown';

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

        global $wpdb;
        $saved_count = 0;
        $existing_rows = $wpdb->get_results($wpdb->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).
            $exists = $wpdb->get_var($wpdb->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) {
                $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 = $wpdb->prefix . 'aysa_keyword_v2_snapshots';
        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
        if ($table_exists !== $table) {
            return null;
        }

        $payload_json = $wpdb->get_var($wpdb->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 = $wpdb->prefix . 'aysa_keyword_v2_snapshots';
        $snapshot_table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $snapshot_table));
        if ($snapshot_table_exists === $snapshot_table) {
            $workspace_payload_json = $wpdb->get_var($wpdb->prepare(
                "SELECT payload_json
                 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 = $wpdb->prefix . 'aysa_keyword_metrics';
        $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(
                "SELECT target_url
                 FROM {$metrics_table}
                 WHERE project_id = %d
                   AND keyword IN ({$placeholders})
                   AND target_url IS NOT NULL
                   AND target_url <> ''",
                array_merge(array(intval($project_id)), $keywords)
            );
            $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)) {
                $all_rows = $wpdb->get_results($wpdb->prepare(
                    "SELECT keyword, target_url
                     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');

        $project_id = isset($_POST['project_id']) ? intval($_POST['project_id']) : 0;
        
        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;
        $results = $wpdb->get_results($wpdb->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 = $wpdb->prefix . 'aysa_missing_page_meta';
        $meta_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $meta_table));
        if ($meta_exists === $meta_table) {
            $meta_rows = $wpdb->get_results($wpdb->prepare(
                "SELECT strategy_id, seo_title, h1, meta_description, warning, updated_at
                 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
                    $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');
        
        $id = isset($_POST['id']) ? intval($_POST['id']) : 0;
        
        if (!$id) {
            wp_send_json_error('Invalid ID');
        }
        
        global $wpdb;
        $wpdb->delete($this->table_name, array('id' => $id));
        
        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
        $strategy = $wpdb->get_row($wpdb->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.
            
            $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 = $wpdb->prefix . 'aysa_content_strategies';
        
        $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 = $wpdb->prefix . 'aysa_content_strategies';
        
        $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)', 'aysa')
            );
        }
        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);
        }
        $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('aysa_process_planned_content_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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : 0;
        // 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']) ? $_POST['min_per_week'] : ($_POST['min_per_day'] ?? 3);
        $max_per_week_input = isset($_POST['max_per_week']) ? $_POST['max_per_week'] : ($_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) ($_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();
        }
        $scope_condition = '';
        if ($strategy_scope === 'missing_pages') {
            $scope_condition = " AND strategy_type = 'missing_pages' ";
        } elseif ($strategy_scope === 'content_only') {
            $scope_condition = " AND strategy_type <> 'missing_pages' ";
        }

        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.
        $strategies = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM $this->table_name
             WHERE project_id = %d
              {$scope_condition}
              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
        ), 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) {
            $pending_queue = $wpdb->get_results($wpdb->prepare(
                "SELECT id, content_type, scheduled_for
                 FROM $this->table_name
                 WHERE project_id = %d
                   {$scope_condition}
                   AND status = 'scheduled'
                   AND post_id IS NULL
                   AND COALESCE(automation_status, '') IN ('queued','retry')
                 ORDER BY scheduled_for ASC, id ASC",
                $project_id
            ), 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;
            }
            $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);
            }
            $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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : 0;
        if ($project_id <= 0) {
            wp_send_json_error('Invalid project ID');
        }

        global $wpdb;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT id, keywords, associated_page, status, automation_status
             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);
            }

            $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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : 0;
        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;
        $status_rows = $wpdb->get_results($wpdb->prepare(
            "SELECT COALESCE(automation_status, 'none') AS s, COUNT(*) AS c
             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']);
        }

        $next_item = $wpdb->get_row($wpdb->prepare(
            "SELECT id, title, scheduled_for
             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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : 0;
        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;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT id, title, strategy_type, recommended_page_type, content_type, execution_stage, intent, status, post_id, scheduled_for, generated_at, automation_status
             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();
        $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');
            $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)
            );
        }

        $row = $wpdb->get_row($wpdb->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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : intval(get_option('aysa_project_id', 0));
        $limit = isset($_POST['limit']) ? max(1, min(50, intval($_POST['limit']))) : 12;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$this->agent8_communications_table_name()} WHERE project_id = %d ORDER BY id DESC LIMIT %d",
            $project_id,
            $limit
        ), ARRAY_A);
        $unread = intval($wpdb->get_var($wpdb->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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : intval(get_option('aysa_project_id', 0));
        $event_id = isset($_POST['event_id']) ? intval($_POST['event_id']) : 0;
        if ($event_id > 0) {
            $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 {
            $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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : intval(get_option('aysa_project_id', 0));
        $mode = sanitize_key((string) ($_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(
            'aysa',
            'Agent8 Notifications',
            'Notifications',
            'edit_posts',
            'aysa-agent8-notifications',
            array($this, 'render_agent8_notifications_page')
        );
    }

    public function render_agent8_notifications_page() {
        if (!current_user_can('edit_posts')) {
            wp_die(esc_html__('Permission denied', 'aysa'));
        }
        global $wpdb;
        $this->ensure_agent8_communications_table();
        $project_id = intval(get_option('aysa_project_id', 0));
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$this->agent8_communications_table_name()} WHERE project_id = %d ORDER BY id DESC LIMIT 100",
            $project_id
        ), ARRAY_A);
        ?>
        <div class="wrap">
            <h1>Agent8 Notifications</h1>
            <p>Canonical Agent8 communication history. Important events are logged here and email delivery is attempted through PostmarkMailer.</p>
            <table class="widefat striped">
                <thead>
                    <tr>
                        <th>Time</th>
                        <th>Event</th>
                        <th>Message</th>
                        <th>Recipient</th>
                        <th>Provider</th>
                        <th>Email</th>
                        <th>In app</th>
                        <th>Error</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (empty($rows)) : ?>
                    <tr><td colspan="8">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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : intval(get_option('aysa_project_id', 0));
        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 = isset($_POST['project_id']) ? intval($_POST['project_id']) : intval(get_option('aysa_project_id', 0));
        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;
        }

        $page = isset($_GET['page']) ? sanitize_text_field((string) $_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:', 'aysa'); ?></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', 'aysa'); ?></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:', 'aysa'); ?></strong>
                <?php
                printf(
                    esc_html__('Today scheduled publications: %1$d item(s): %2$s%3$s', 'aysa'),
                    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');

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT id, title, scheduled_for, automation_status
             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
        }

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT cs.*, p.post_status, p.post_date, p.post_date_gmt
             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 = strtolower(trim((string) get_option('aysa_language', '')));
        if ($language === '') {
            $language = strtolower(trim((string) get_locale()));
        }
        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;

        $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).
        $wpdb->query($wpdb->prepare(
            "UPDATE $this->table_name
             SET automation_status = 'retry',
                 automation_updated_at = %s
             WHERE status = 'scheduled'
               AND automation_status = 'processing'
               AND (
                    automation_attempts >= 3
                    OR automation_updated_at IS NULL
                    OR automation_updated_at < %s
               )",
            $now_mysql,
            $stale_cutoff
        ));

        $items = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM $this->table_name
             WHERE status = 'scheduled'
               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",
            $now_mysql
        ), ARRAY_A);

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

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

            $locked = $wpdb->query($wpdb->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']) {
                $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';
                $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');
        }

        $associated_pages = $this->parse_associated_pages($strategy['associated_page'] ?? '');

        $keywords = json_decode((string) ($strategy['keywords'] ?? '[]'), true);
        if (!is_array($keywords)) {
            $keywords = array();
        }
        $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);
        if (!empty($generation['success'])) {
            $this->track_action_usage(
                $project_id,
                'aysa_content_strategy_generate',
                'content_strategy_generate_cron_queue',
                array(
                    'module' => 'content_strategy',
                    'source' => 'cron_queue',
                    'strategy_id' => intval($strategy['id'] ?? 0),
                    'post_id' => intval($post_id),
                    'main_keyword' => (string) $main_keyword,
                ),
                55,
                '/v1/content-strategy/generate'
            );
        }
        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);
        }

        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;
        }

        $generation = $this->generate_blog_content_for_post($post_id, $title, $main_keyword, $secondary_keywords);
        $project_id = intval($payload['project_id'] ?? get_option('aysa_project_id', 0));
        if (!empty($generation['success'])) {
            $this->track_action_usage(
                $project_id,
                'aysa_content_strategy_generate',
                'content_strategy_generate_existing_post_cron',
                array(
                    'module' => 'content_strategy',
                    'source' => 'existing_post_cron',
                    'post_id' => intval($post_id),
                    'main_keyword' => (string) $main_keyword,
                ),
                55,
                '/v1/content-strategy/generate'
            );
        }
        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'));
    }

    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 !== '') {
            $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;
        }

        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:', 'aysa'),
                esc_url($associated_page),
                esc_html__('Related Content', 'aysa')
            );
        }

        $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 = 'kQ4v9dH2uR6xZ1mP8sT3fY7nB5cL0eW4';

        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) {
        $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', '');

        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),
        );
    }

    private function generate_blog_content_for_post($post_id, $article_topic, $primary_keyword, array $secondary_keywords) {
        $payload = $this->build_blog_generation_payload($post_id, $article_topic, $primary_keyword, $secondary_keywords);
        $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 = $this->call_article_image_broker(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),
        ));
        if ($image['success']) {
            $results['featured_image_url'] = $this->extract_first_non_empty_value($image['data'], array(
                'image_url', 'imageUrl', 'ImageUrl', 'featured_image_url', 'url'
            ));
            $results['featured_image_alt'] = $this->extract_first_non_empty_value($image['data'], array(
                'alt_text', 'altText', 'AltText', 'image_alt_text', 'imageAltText', 'alternativeText', 'alternative_text'
            ));
            $results['featured_image_title'] = $this->extract_first_non_empty_value($image['data'], array(
                'title', 'image_title', 'imageTitle'
            ));
            $results['featured_image_caption'] = $this->extract_first_non_empty_value($image['data'], array(
                'caption', 'associated_text', 'associatedText'
            ));
            $results['featured_image_description'] = $this->extract_first_non_empty_value($image['data'], array(
                'description', 'image_description', 'imageDescription'
            ));
        }

        $meta = $this->call_blog_content_api('41-meta-seo-elements', $payload);
        if ($meta['success']) {
            $results['meta_title'] = $this->extract_first_non_empty_value($meta['data'], array(
                'meta_title', 'metaTitle', 'title', 'seo_title', 'MetaTitle'
            ));
            $results['meta_description'] = $this->extract_first_non_empty_value($meta['data'], array(
                'meta_description', 'metaDescription', 'description', 'seo_description', 'MetaDescription'
            ));
            $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);
        return trim($clean);
    }

    private function append_associated_links_to_content($content, array $associated_pages) {
        $links = array();
        foreach ($associated_pages as $page_url) {
            $safe_url = esc_url($page_url);
            if ($safe_url === '') {
                continue;
            }
            $links[] = '<a class="aysa-internal-link" href="' . $safe_url . '">' . esc_html($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_associated_links_to_content($content_html, $associated_pages);

        $update = wp_update_post(array(
            'ID' => intval($post_id),
            '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', sanitize_title((string) $generated['url_recomandat']));
        }
        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');
            }
        }

        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 : 'kQ4v9dH2uR6xZ1mP8sT3fY7nB5cL0eW4';

        $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);
        $data = json_decode((string) wp_remote_retrieve_body($resp), true);
        if (!is_array($data)) {
            return new WP_Error('broker_invalid_response', 'Broker returned invalid response.');
        }
        if ($code < 200 || $code >= 300 || (isset($data['ok']) && !$data['ok'])) {
            $msg = isset($data['error']) ? (string) $data['error'] : 'Broker returned error.';
            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 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 = 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)) {
            @unlink($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 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->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 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) 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;

        $rows = $wpdb->get_results(
            "SELECT id, title, keywords, intent, strategy_type, recommended_page_type, content_type, execution_stage, status
             FROM {$this->table_name}
             WHERE strategy_type = 'missing_pages'
             LIMIT 5000",
            ARRAY_A
        );
        if (!is_array($rows) || empty($rows)) {
            return;
        }

        $meta_table = $wpdb->prefix . 'aysa_missing_page_meta';
        $meta_map = array();
        $meta_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $meta_table));
        if ($meta_exists === $meta_table) {
            $meta_rows = $wpdb->get_results(
                "SELECT strategy_id, page_type
                 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';
                }
            }

            $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;

        $rows = $wpdb->get_results(
            "SELECT id, project_id, title, keywords, intent, associated_page, strategy_type, recommended_page_type, content_type, status, post_id, scheduled_for, automation_status
             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;
                    }
                }

                $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, array('mireasa', 'mama', 'barbati', 'bărbați', 'copii', 'corporate', 'iubita', 'iubită'));
        $locality_key = $this->match_editorial_token($haystack, array('bragadiru', 'bucuresti', 'bucurești', 'sector 1', 'sector 2', 'sector 3', 'sector 4', 'sector 5', 'sector 6'));
        $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 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(
            'bucuresti', 'bragadiru', 'sector', 'langa mine', 'near me', 'aproape', 'program', 'non stop'
        );
        foreach ($landing_tokens as $token) {
            if (strpos($kw, $token) !== false) {
                return 'Landing Page';
            }
        }

        $category_tokens = array(
            'buchete', 'aranjamente', 'flori', 'cadouri', 'colectie', 'colectii', 'servicii',
            'delivery', 'livrare', 'categorie', 'categorii', 'oferta', 'oferte', 'modele',
            'trandafiri', 'lalele', 'frezii', 'orhidee'
        );
        foreach ($category_tokens as $token) {
            if (strpos($kw, $token) !== false) {
                return 'Category Page';
            }
        }

        $has_numeric = preg_match('/\d+/', $kw) === 1;
        $product_tokens = array(
            'trandafir', 'lalea', 'orhidee', 'bonsai', 'cutie', 'buchet', 'model', 'set', 'aranjament'
        );
        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 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 = $wpdb->prefix . 'aysa_wallet_ledger';
        $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $ledger_table));
        if ($table_exists !== $ledger_table) {
            return true;
        }

        $last_created_at = $wpdb->get_var($wpdb->prepare(
            "SELECT created_at
             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();
