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

final class Aysa_Internal_Linking_Workspace
{
    private const OPTION_LEGACY_IMPORT_PREFIX = 'aysa_internal_linking_workspace_imported_';

    private string $inventory_table;
    private string $opportunities_table;
    private string $runs_table;

    public function __construct()
    {
        global $wpdb;
        $this->inventory_table = $wpdb->prefix . 'aysa_internal_link_inventory';
        $this->opportunities_table = $wpdb->prefix . 'aysa_internal_link_opportunities';
        $this->runs_table = $wpdb->prefix . 'aysa_internal_link_projection_runs';
    }

    public function ensure_schema(): void
    {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();

        dbDelta(
            "CREATE TABLE {$this->inventory_table} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                project_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                entity_key VARCHAR(191) NOT NULL,
                entity_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                entity_type VARCHAR(32) NOT NULL DEFAULT '',
                taxonomy VARCHAR(64) DEFAULT NULL,
                url TEXT DEFAULT NULL,
                title TEXT DEFAULT NULL,
                content_hash VARCHAR(64) DEFAULT NULL,
                content_source_kind VARCHAR(32) NOT NULL DEFAULT 'wordpress',
                inventory_status VARCHAR(32) NOT NULL DEFAULT 'discovered',
                manual_only_reason VARCHAR(128) DEFAULT NULL,
                last_scanned_at DATETIME DEFAULT NULL,
                last_applied_at DATETIME DEFAULT NULL,
                last_reconciled_at DATETIME DEFAULT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY uniq_project_entity (project_id, entity_key(191)),
                KEY idx_project_status (project_id, inventory_status),
                KEY idx_project_type (project_id, entity_type)
            ) {$charset};"
        );

        dbDelta(
            "CREATE TABLE {$this->opportunities_table} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                project_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                source_entity_key VARCHAR(191) NOT NULL,
                target_entity_key VARCHAR(191) DEFAULT NULL,
                source_title TEXT DEFAULT NULL,
                source_url TEXT DEFAULT NULL,
                target_title TEXT DEFAULT NULL,
                target_url TEXT DEFAULT NULL,
                keyword VARCHAR(255) DEFAULT NULL,
                anchor_text VARCHAR(255) DEFAULT NULL,
                match_type VARCHAR(32) NOT NULL DEFAULT 'exact',
                opportunity_status VARCHAR(32) NOT NULL DEFAULT 'discovered',
                conflict_reason VARCHAR(255) DEFAULT NULL,
                approved_state VARCHAR(32) NOT NULL DEFAULT 'pending',
                applied_state VARCHAR(32) NOT NULL DEFAULT 'pending',
                worker_run_id BIGINT UNSIGNED DEFAULT NULL,
                last_validated_at DATETIME DEFAULT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_project_status (project_id, opportunity_status),
                KEY idx_project_source (project_id, source_entity_key(191)),
                KEY idx_project_run (project_id, worker_run_id)
            ) {$charset};"
        );

        dbDelta(
            "CREATE TABLE {$this->runs_table} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                project_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                run_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                run_type VARCHAR(64) NOT NULL DEFAULT 'internal_linking',
                status VARCHAR(32) NOT NULL DEFAULT 'idle',
                current_task_key VARCHAR(128) DEFAULT NULL,
                current_milestone VARCHAR(255) DEFAULT NULL,
                summary_json LONGTEXT DEFAULT NULL,
                started_at DATETIME DEFAULT NULL,
                finished_at DATETIME DEFAULT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY uniq_project_run (project_id, run_id),
                KEY idx_project_status (project_id, status)
            ) {$charset};"
        );
    }

    public function build_workspace(int $project_id, array $args = array()): array
    {
        $this->ensure_schema();
        $project_id = max(0, $project_id);
        $this->ensure_seeded($project_id, !empty($args['refresh']));

        $filters = isset($args['filters']) && is_array($args['filters']) ? $args['filters'] : array();
        $page = max(1, intval($args['page'] ?? 1));
        $per_page = max(1, min(100, intval($args['per_page'] ?? 50)));

        $inventory = $this->get_inventory_rows($project_id);
        $opportunities = $this->get_opportunity_rows($project_id);
        $run = $this->get_latest_run($project_id);

        $rows = $this->build_workspace_rows($inventory, $opportunities);
        $filtered = $this->apply_filters($rows, $filters);
        $total = count($filtered);
        $offset = ($page - 1) * $per_page;
        $paged = array_slice($filtered, $offset, $per_page);

        return array(
            'summary' => $this->build_summary($rows, $run),
            'filters' => $this->build_filter_counts($rows),
            'rows' => array_values($paged),
            'pagination' => array(
                'page' => $page,
                'per_page' => $per_page,
                'total' => $total,
                'pages' => max(1, (int) ceil($total / $per_page)),
            ),
            'run' => $run,
        );
    }

    public function get_detail(int $project_id, int $opportunity_id = 0, string $entity_key = ''): array
    {
        global $wpdb;
        $this->ensure_schema();
        $this->ensure_seeded($project_id, false);

        $inventory_row = null;
        $opportunities = array();

        if ($opportunity_id > 0) {
            $opportunity = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM {$this->opportunities_table} WHERE project_id = %d AND id = %d LIMIT 1", $project_id, $opportunity_id),
                ARRAY_A
            );
            if (is_array($opportunity)) {
                $entity_key = (string) ($opportunity['source_entity_key'] ?? '');
                $opportunities[] = $opportunity;
            }
        }

        if ($entity_key !== '') {
            $inventory_row = $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM {$this->inventory_table} WHERE project_id = %d AND entity_key = %s LIMIT 1", $project_id, $entity_key),
                ARRAY_A
            );
            $opp_rows = $wpdb->get_results(
                $wpdb->prepare("SELECT * FROM {$this->opportunities_table} WHERE project_id = %d AND source_entity_key = %s ORDER BY updated_at DESC, id DESC", $project_id, $entity_key),
                ARRAY_A
            );
            if (is_array($opp_rows) && !empty($opp_rows)) {
                $opportunities = $opp_rows;
            }
        }

        return array(
            'inventory' => is_array($inventory_row) ? $inventory_row : array(),
            'opportunities' => is_array($opportunities) ? array_values($opportunities) : array(),
        );
    }

    public function build_preview_package(int $project_id): array
    {
        $this->ensure_schema();
        $project_id = max(0, $project_id);
        $inventory = $this->get_inventory_rows($project_id);
        $opportunities = $this->get_opportunity_rows($project_id);
        $inventory_by_key = array();
        foreach ($inventory as $entity) {
            if (!is_array($entity)) {
                continue;
            }
            $key = (string) ($entity['entity_key'] ?? '');
            if ($key !== '') {
                $inventory_by_key[$key] = $entity;
            }
        }

        $items = array();
        foreach ($opportunities as $row) {
            if (!is_array($row)) {
                continue;
            }
            $status = sanitize_key((string) ($row['opportunity_status'] ?? ''));
            if (!in_array($status, array('ready_to_apply', 'analyzed', 'discovered'), true)) {
                continue;
            }
            $source_key = (string) ($row['source_entity_key'] ?? '');
            $entity = isset($inventory_by_key[$source_key]) && is_array($inventory_by_key[$source_key]) ? $inventory_by_key[$source_key] : array();
            $source_url = esc_url_raw((string) ($row['source_url'] ?? ($entity['url'] ?? '')));
            $target_url = esc_url_raw((string) ($row['target_url'] ?? ''));
            $anchor = sanitize_text_field((string) ($row['anchor_text'] ?? ($row['keyword'] ?? '')));
            $self_link = $source_url !== '' && $target_url !== '' && rtrim(strtolower($source_url), '/') === rtrim(strtolower($target_url), '/');
            $already_linked = !empty($entity) && $this->entity_has_live_aysa_link($entity, $row);
            $risk_flags = array_values(array_filter(array(
                $source_url === '' ? 'source_url_missing' : '',
                $target_url === '' ? 'target_url_missing' : '',
                $anchor === '' ? 'anchor_missing' : '',
                $self_link ? 'self_link' : '',
                $already_linked ? 'already_linked' : '',
                trim((string) ($row['conflict_reason'] ?? '')) !== '' ? sanitize_key((string) ($row['conflict_reason'] ?? 'conflict')) : '',
            )));
            $safe = $risk_flags === array() && $status === 'ready_to_apply';
            $items[] = array(
                'source_post_id' => intval($entity['entity_id'] ?? 0),
                'source_url' => $source_url,
                'source_title' => sanitize_text_field((string) ($row['source_title'] ?? ($entity['title'] ?? ''))),
                'anchor_text' => $anchor,
                'target_post_id' => $target_url !== '' ? intval(url_to_postid($target_url)) : 0,
                'target_url' => $target_url,
                'target_title' => sanitize_text_field((string) ($row['target_title'] ?? '')),
                'reason' => sanitize_text_field((string) ($row['match_type'] ?? 'keyword_mapping_match')),
                'confidence' => $safe ? 90 : 60,
                'safe_to_apply' => $safe,
                'risk_flags' => $risk_flags,
                'already_linked' => $already_linked,
                'self_link' => $self_link,
                'source_content_hash' => sanitize_text_field((string) ($entity['content_hash'] ?? '')),
                'status' => $safe ? 'preview_ready' : 'manual_review',
            );
        }

        $hash_payload = wp_json_encode(array(
            'inventory' => array_map(static fn($row) => array(
                'entity_key' => (string) ($row['entity_key'] ?? ''),
                'content_hash' => (string) ($row['content_hash'] ?? ''),
                'updated_at' => (string) ($row['updated_at'] ?? ''),
            ), $inventory),
            'opportunities' => array_map(static fn($row) => array(
                'id' => intval($row['id'] ?? 0),
                'status' => (string) ($row['opportunity_status'] ?? ''),
                'target_url' => (string) ($row['target_url'] ?? ''),
                'anchor_text' => (string) ($row['anchor_text'] ?? ''),
                'updated_at' => (string) ($row['updated_at'] ?? ''),
            ), $opportunities),
        ));
        $snapshot_hash = hash('sha256', is_string($hash_payload) ? $hash_payload : '');
        $package_id = 'internal_linking_preview_' . $project_id . '_' . substr($snapshot_hash, 0, 16);

        return array(
            'package_id' => $package_id,
            'preview_id' => $package_id,
            'project_id' => $project_id,
            'created_at' => current_time('mysql'),
            'workspace_snapshot_hash' => $snapshot_hash,
            'source' => 'internal_linking_workspace',
            'items' => $items,
        );
    }

    public function apply_projection(int $project_id, int $run_id, array $projection): array
    {
        global $wpdb;
        $this->ensure_schema();
        $this->ensure_seeded($project_id, false);

        $inventory_rows = isset($projection['inventory_rows']) && is_array($projection['inventory_rows']) ? $projection['inventory_rows'] : array();
        $opportunities = isset($projection['opportunities']) && is_array($projection['opportunities']) ? $projection['opportunities'] : array();
        $summary = isset($projection['summary']) && is_array($projection['summary']) ? $projection['summary'] : array();

        foreach ($inventory_rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $this->upsert_inventory($project_id, $row);
        }

        foreach ($opportunities as $row) {
            if (!is_array($row)) {
                continue;
            }
            $this->upsert_opportunity($project_id, $row);
        }

        $existing = $wpdb->get_var(
            $wpdb->prepare("SELECT id FROM {$this->runs_table} WHERE project_id = %d AND run_id = %d LIMIT 1", $project_id, $run_id)
        );
        $payload = array(
            'project_id' => $project_id,
            'run_id' => $run_id,
            'run_type' => sanitize_key((string) ($projection['run_type'] ?? 'internal_linking')),
            'status' => sanitize_key((string) ($projection['run_status'] ?? 'completed')),
            'current_task_key' => sanitize_text_field((string) ($projection['current_task_key'] ?? 'plugin_local_projection_sync')),
            'current_milestone' => sanitize_text_field((string) ($projection['current_milestone'] ?? 'Projection synced')),
            'summary_json' => wp_json_encode($summary),
            'started_at' => $this->nullable_datetime($projection['started_at'] ?? current_time('mysql')),
            'finished_at' => $this->nullable_datetime($projection['finished_at'] ?? current_time('mysql')),
        );

        if ($existing) {
            $wpdb->update($this->runs_table, $payload, array('id' => intval($existing)));
        } else {
            $wpdb->insert($this->runs_table, $payload);
        }

        return array(
            'ok' => true,
            'run_id' => $run_id,
            'inventory_rows' => count($inventory_rows),
            'opportunities' => count($opportunities),
        );
    }

    public function update_row_action(int $project_id, int $opportunity_id, string $action): array
    {
        global $wpdb;
        $this->ensure_schema();
        $allowed = array('skip', 'validate', 'rewrite', 'mark_manual_only', 'apply');
        if (!in_array($action, $allowed, true)) {
            return array('ok' => false, 'error' => 'unsupported_action');
        }

        $row = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$this->opportunities_table} WHERE project_id = %d AND id = %d LIMIT 1", $project_id, $opportunity_id),
            ARRAY_A
        );
        if (!is_array($row)) {
            return array('ok' => false, 'error' => 'opportunity_not_found');
        }

        if ($action === 'mark_manual_only') {
            $wpdb->update(
                $this->inventory_table,
                array(
                    'inventory_status' => 'manual_only',
                    'manual_only_reason' => 'manual_override',
                    'last_reconciled_at' => current_time('mysql'),
                ),
                array(
                    'project_id' => $project_id,
                    'entity_key' => (string) ($row['source_entity_key'] ?? ''),
                )
            );
            $wpdb->update(
                $this->opportunities_table,
                array(
                    'opportunity_status' => 'manual_only',
                    'updated_at' => current_time('mysql'),
                ),
                array('id' => $opportunity_id)
            );
        } elseif ($action === 'skip') {
            $wpdb->update(
                $this->opportunities_table,
                array(
                    'opportunity_status' => 'skipped',
                    'approved_state' => 'skipped',
                ),
                array('id' => $opportunity_id)
            );
        } elseif ($action === 'validate') {
            $wpdb->update(
                $this->opportunities_table,
                array(
                    'last_validated_at' => current_time('mysql'),
                    'opportunity_status' => 'analyzed',
                ),
                array('id' => $opportunity_id)
            );
        } elseif ($action === 'rewrite') {
            $wpdb->update(
                $this->opportunities_table,
                array(
                    'opportunity_status' => 'ready_to_apply',
                    'approved_state' => 'approved',
                ),
                array('id' => $opportunity_id)
            );
        } elseif ($action === 'apply') {
            $wpdb->update(
                $this->opportunities_table,
                array(
                    'opportunity_status' => 'applied_by_aysa',
                    'approved_state' => 'approved',
                    'applied_state' => 'applied',
                    'last_validated_at' => current_time('mysql'),
                ),
                array('id' => $opportunity_id)
            );
            $wpdb->update(
                $this->inventory_table,
                array(
                    'inventory_status' => 'applied_by_aysa',
                    'last_applied_at' => current_time('mysql'),
                ),
                array(
                    'project_id' => $project_id,
                    'entity_key' => (string) ($row['source_entity_key'] ?? ''),
                )
            );
        }

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

    public function run_legacy_action(int $project_id, string $action): array
    {
        if (!class_exists('Aysa_Internal_Linking')) {
            return array('ok' => false, 'error' => 'legacy_engine_missing');
        }

        $association_bootstrap = $this->ensure_local_keyword_associations($project_id);
        if (empty($association_bootstrap['ok'])) {
            return array(
                'ok' => false,
                'error' => (string) ($association_bootstrap['error'] ?? 'keyword_mapping_bootstrap_failed'),
            );
        }

        $engine = new Aysa_Internal_Linking();
        $preview_mode = $action !== 'apply_safe';
        $result = $engine->process_all_content_with_main_keywords($preview_mode, true);
        if (!is_array($result) || empty($result['success'])) {
            return array('ok' => false, 'error' => is_array($result) ? (string) ($result['message'] ?? 'legacy_run_failed') : 'legacy_run_failed');
        }

        if ($preview_mode) {
            $state = array(
                'updated_at' => current_time('mysql'),
                'data' => array(
                    'total_keywords_found' => intval($result['total_keywords_found'] ?? 0),
                    'content_items_scanned' => intval($result['content_items_scanned'] ?? 0),
                    'links_to_be_added' => intval($result['links_added'] ?? 0),
                    'summary' => sanitize_text_field((string) ($result['summary'] ?? '')),
                    'preview_changes' => $this->format_preview_changes_from_mappings($result['link_mappings'] ?? array()),
                ),
            );
            update_option($this->preview_state_key($project_id), $state, false);
        }

        $this->ensure_seeded($project_id, true);

        return array(
            'ok' => true,
            'result' => $result,
            'projection' => $this->seed_projection($project_id),
            'association_bootstrap' => $association_bootstrap,
        );
    }

    public function seed_projection(int $project_id): array
    {
        $workspace = $this->build_workspace($project_id, array('page' => 1, 'per_page' => 200));
        return array(
            'run_type' => 'internal_linking',
            'run_status' => 'completed',
            'current_task_key' => 'plugin_local_projection_sync',
            'current_milestone' => 'Plugin projection synced',
            'summary' => isset($workspace['summary']) && is_array($workspace['summary']) ? $workspace['summary'] : array(),
            'inventory_rows' => $this->get_inventory_rows($project_id),
            'opportunities' => $this->get_opportunity_rows($project_id),
            'workspace_rows' => isset($workspace['rows']) && is_array($workspace['rows']) ? $workspace['rows'] : array(),
            'generated_at' => current_time('mysql'),
        );
    }

    private function ensure_seeded(int $project_id, bool $force = false): void
    {
        if ($project_id <= 0) {
            return;
        }

        $flag_key = self::OPTION_LEGACY_IMPORT_PREFIX . $project_id;
        if (!$force && get_option($flag_key)) {
            return;
        }

        $this->scan_entities($project_id);
        $this->import_legacy_log($project_id);
        $this->import_legacy_preview($project_id);
        update_option($flag_key, current_time('mysql'), false);
    }

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

        $option_key = 'aysa_keyword_discovery_local_associations_' . $project_id;
        $existing = get_option($option_key, array());
        if (is_array($existing) && !empty($existing)) {
            return array(
                'ok' => true,
                'source' => 'local_option',
                'count' => count($existing),
            );
        }

        $workspace = $this->read_keyword_mapping_workspace_snapshot($project_id);
        $workspace_source = 'snapshot';
        if (empty($workspace)) {
            $workspace = $this->fetch_keyword_mapping_workspace($project_id);
            $workspace_source = 'broker';
        }
        if (!empty($workspace)) {
            $prepared = $this->build_local_associations_from_keyword_mapping_workspace($project_id, $workspace);
            $association_rows = $prepared['association_rows'];
            $keyword_rows = $prepared['keyword_rows'];
            if ($association_rows !== array() && $keyword_rows !== array()) {
                update_option($option_key, array_values($association_rows), false);
                $this->persist_keyword_target_rows($project_id, $keyword_rows);

                return array(
                    'ok' => true,
                    'source' => $workspace_source,
                    'count' => count($association_rows),
                );
            }
        }

        $table_fallback = $this->build_local_associations_from_keyword_discovery_table($project_id);
        $association_rows = $table_fallback['association_rows'];
        $keyword_rows = $table_fallback['keyword_rows'];
        if ($association_rows === array() || $keyword_rows === array()) {
            return array('ok' => false, 'error' => 'no_mapped_keyword_associations');
        }

        update_option($option_key, array_values($association_rows), false);
        $this->persist_keyword_target_rows($project_id, $keyword_rows);

        return array(
            'ok' => true,
            'source' => 'keyword_discovery_table',
            'count' => count($association_rows),
        );
    }

    private function read_keyword_mapping_workspace_snapshot(int $project_id): array
    {
        global $wpdb;

        $table = $wpdb->prefix . 'aysa_keyword_v2_snapshots';
        $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
        if ($exists !== $table) {
            return array();
        }

        $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 array();
        }

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

    private function fetch_keyword_mapping_workspace(int $project_id): array
    {
        $site_host = wp_parse_url(home_url('/'), PHP_URL_HOST);
        $payload = array(
            'project_id' => $project_id,
            'site_host' => is_string($site_host) ? $site_host : '',
            'force_refresh' => 0,
        );

        $result = $this->broker_request('POST', '/v1/research/workspaces/keyword-mapping', $payload, 120);
        if (is_wp_error($result)) {
            return array();
        }
        if (is_array($result) && isset($result['data']) && is_array($result['data'])) {
            $result = $result['data'];
        }

        return is_array($result) && !empty($result['ok']) ? $result : array();
    }

    private function build_local_associations_from_keyword_mapping_workspace(int $project_id, array $workspace): array
    {
        $clusters = isset($workspace['clusters']) && is_array($workspace['clusters']) ? $workspace['clusters'] : array();
        $association_rows = array();
        $keyword_rows = array();
        $seen = array();

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

            $selection_state = sanitize_key((string) ($cluster['selection_state'] ?? 'review'));
            if ($selection_state === 'excluded') {
                continue;
            }

            $mapping_status = sanitize_key((string) ($cluster['mapping_status'] ?? ''));
            $matched_page = isset($cluster['matched_page']) && is_array($cluster['matched_page']) ? $cluster['matched_page'] : array();
            $target_url = esc_url_raw((string) ($matched_page['url'] ?? ''));
            if ($target_url === '' || !in_array($mapping_status, array('mapped', 'review'), true)) {
                continue;
            }

            $target = $this->resolve_target_by_url($target_url);
            if (!is_array($target) || empty($target['associated_on']) || intval($target['id_intern'] ?? 0) <= 0) {
                continue;
            }

            $keyword_rows_for_cluster = array();
            $primary = isset($cluster['primary_keyword']) && is_array($cluster['primary_keyword']) ? $cluster['primary_keyword'] : array();
            if ($primary !== array()) {
                $keyword_rows_for_cluster[] = array('row' => $primary, 'is_main' => 1);
            }
            foreach ((array) ($cluster['secondary_keywords'] ?? array()) as $secondary) {
                if (is_array($secondary)) {
                    $keyword_rows_for_cluster[] = array('row' => $secondary, 'is_main' => 0);
                }
            }

            foreach ($keyword_rows_for_cluster as $candidate) {
                $keyword_row = isset($candidate['row']) && is_array($candidate['row']) ? $candidate['row'] : array();
                $keyword = trim((string) ($keyword_row['keyword'] ?? ''));
                if ($keyword === '') {
                    continue;
                }
                $keyword_state = sanitize_key((string) ($keyword_row['selection_state'] ?? $selection_state));
                if ($keyword_state === 'excluded') {
                    continue;
                }

                $assoc_key = strtolower($keyword) . '|' . strtolower((string) $target['associated_on']) . '|' . intval($target['id_intern']);
                if (isset($seen[$assoc_key])) {
                    continue;
                }
                $seen[$assoc_key] = true;

                $association_rows[] = array(
                    'id' => abs(crc32($assoc_key)),
                    'projectId' => $project_id,
                    'keyword' => $keyword,
                    'associatedOn' => (string) $target['associated_on'],
                    'idIntern' => intval($target['id_intern']),
                    'value' => (string) ($target['value'] ?? $target_url),
                    'desiredPosition' => null,
                    'source' => 'keyword_mapping_v2',
                    'updatedAt' => current_time('mysql'),
                    'link' => $target_url,
                    'target_url' => $target_url,
                    'isMain' => intval($candidate['is_main'] ?? 0),
                );

                $keyword_rows[] = array(
                    'keyword' => $keyword,
                    'target_url' => $target_url,
                    'url' => $target_url,
                    'link' => $target_url,
                    'source' => 'association_seed',
                );
            }
        }

        return array(
            'association_rows' => $association_rows,
            'keyword_rows' => $keyword_rows,
        );
    }

    private function build_local_associations_from_keyword_discovery_table(int $project_id): array
    {
        global $wpdb;

        $table = $wpdb->prefix . 'aysa_keyword_discovery';
        $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
        if ($exists !== $table) {
            return array('association_rows' => array(), 'keyword_rows' => array());
        }

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT keyword, target_url
                 FROM {$table}
                 WHERE project_id = %d
                   AND keyword IS NOT NULL
                   AND TRIM(keyword) <> ''
                   AND target_url IS NOT NULL
                   AND TRIM(target_url) <> ''
                 ORDER BY updated_at DESC, id DESC
                 LIMIT 10000",
                $project_id
            ),
            ARRAY_A
        );

        if (!is_array($rows) || $rows === array()) {
            return array('association_rows' => array(), 'keyword_rows' => array());
        }

        $association_rows = array();
        $keyword_rows = array();
        $seen = array();

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $keyword = trim((string) ($row['keyword'] ?? ''));
            $target_url = esc_url_raw((string) ($row['target_url'] ?? ''));
            if ($keyword === '' || $target_url === '') {
                continue;
            }

            $target = $this->resolve_target_by_url($target_url);
            if (!is_array($target) || empty($target['associated_on']) || intval($target['id_intern'] ?? 0) <= 0) {
                continue;
            }

            $assoc_key = strtolower($keyword) . '|' . strtolower((string) $target['associated_on']) . '|' . intval($target['id_intern']);
            if (isset($seen[$assoc_key])) {
                continue;
            }
            $seen[$assoc_key] = true;

            $association_rows[] = array(
                'id' => abs(crc32($assoc_key)),
                'projectId' => $project_id,
                'keyword' => $keyword,
                'associatedOn' => (string) $target['associated_on'],
                'idIntern' => intval($target['id_intern']),
                'value' => (string) ($target['value'] ?? $target_url),
                'desiredPosition' => null,
                'source' => 'keyword_discovery_table',
                'updatedAt' => current_time('mysql'),
                'link' => $target_url,
                'target_url' => $target_url,
                'isMain' => 1,
            );

            $keyword_rows[] = array(
                'keyword' => $keyword,
                'target_url' => $target_url,
                'url' => $target_url,
                'link' => $target_url,
                'source' => 'association_seed',
            );
        }

        return array(
            'association_rows' => $association_rows,
            'keyword_rows' => $keyword_rows,
        );
    }

    private function persist_keyword_target_rows(int $project_id, array $rows): void
    {
        global $wpdb;

        if ($project_id <= 0 || $rows === array()) {
            return;
        }

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

        $now = current_time('mysql');
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $keyword = trim((string) ($row['keyword'] ?? ''));
            $target_url = esc_url_raw((string) ($row['target_url'] ?? $row['url'] ?? $row['link'] ?? ''));
            if ($keyword === '' || $target_url === '') {
                continue;
            }

            $existing_id = intval($wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$table} WHERE project_id = %d AND keyword = %s LIMIT 1",
                $project_id,
                $keyword
            )));

            $data = array(
                'project_id' => $project_id,
                'keyword' => $keyword,
                'target_url' => $target_url,
                'source' => 'association_seed',
                'updated_at' => $now,
            );

            if ($existing_id > 0) {
                $wpdb->update($table, $data, array('id' => $existing_id));
            } else {
                $data['created_at'] = $now;
                $wpdb->insert($table, $data);
            }
        }
    }

    private function resolve_target_by_url(string $url): ?array
    {
        $url = esc_url_raw($url);
        if ($url === '') {
            return null;
        }

        $post_id = url_to_postid($url);
        if ($post_id > 0) {
            $title = get_the_title($post_id);
            return array(
                'associated_on' => 'post',
                'id_intern' => $post_id,
                'value' => is_string($title) && $title !== '' ? $title : $url,
            );
        }

        $path = trim((string) wp_parse_url($url, PHP_URL_PATH), '/');
        if ($path === '') {
            return null;
        }

        $slug = basename($path);
        foreach ((array) get_taxonomies(array('public' => true), 'names') as $taxonomy) {
            $term = get_term_by('slug', $slug, $taxonomy);
            if (!$term || is_wp_error($term)) {
                continue;
            }
            $term_link = get_term_link($term);
            if (is_string($term_link) && untrailingslashit($term_link) === untrailingslashit($url)) {
                return array(
                    'associated_on' => 'term',
                    'id_intern' => intval($term->term_id),
                    'value' => (string) $term->name,
                );
            }
        }

        return null;
    }

    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', '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(string $method, string $path, array $payload = array(), int $timeout = 30)
    {
        $cfg = $this->get_broker_config();
        if (is_wp_error($cfg)) {
            return $cfg;
        }

        $method = strtoupper($method);
        $path = '/' . ltrim($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']);
        $response = wp_remote_request($cfg['base_url'] . $path, array(
            'timeout' => max(10, min(240, $timeout)),
            'method' => $method,
            'headers' => array(
                'Content-Type' => 'application/json',
                'X-Aysa-Key' => $cfg['key_id'],
                'X-Aysa-Timestamp' => $timestamp,
                'X-Aysa-Nonce' => $nonce,
                'X-Aysa-Signature' => $signature,
            ),
            'body' => $method === 'POST' ? $body : null,
        ));

        if (is_wp_error($response)) {
            return new WP_Error('broker_http_failed', $response->get_error_message());
        }

        $code = wp_remote_retrieve_response_code($response);
        $data = json_decode((string) wp_remote_retrieve_body($response), 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'])) {
            return new WP_Error('broker_error', (string) ($data['error'] ?? 'Broker returned error.'));
        }

        return $data;
    }

    private function scan_entities(int $project_id): void
    {
        $post_types = array('post', 'page');
        if (class_exists('WooCommerce')) {
            $post_types[] = 'product';
        }
        $posts = get_posts(array(
            'numberposts' => -1,
            'post_status' => 'publish',
            'post_type' => $post_types,
            'orderby' => 'ID',
            'order' => 'ASC',
        ));
        foreach ($posts as $post) {
            $content = (string) ($post->post_content ?? '');
            $this->upsert_inventory($project_id, array(
                'entity_key' => $this->entity_key((string) $post->post_type, intval($post->ID)),
                'entity_id' => intval($post->ID),
                'entity_type' => (string) $post->post_type,
                'taxonomy' => '',
                'url' => (string) get_permalink($post->ID),
                'title' => (string) $post->post_title,
                'content_hash' => md5($content),
                'content_source_kind' => $this->is_elementor_post($post->ID) ? 'elementor' : 'wordpress',
                'inventory_status' => $this->is_elementor_post($post->ID) ? 'manual_only' : 'discovered',
                'manual_only_reason' => $this->is_elementor_post($post->ID) ? 'elementor_content' : null,
                'last_scanned_at' => current_time('mysql'),
            ));
        }

        $taxonomies = array('category', 'post_tag');
        if (class_exists('WooCommerce')) {
            $taxonomies[] = 'product_cat';
        }
        $terms = get_terms(array(
            'taxonomy' => $taxonomies,
            'hide_empty' => false,
        ));
        if (!is_wp_error($terms)) {
            foreach ($terms as $term) {
                $description = (string) ($term->description ?? '');
                $url = get_term_link($term);
                $this->upsert_inventory($project_id, array(
                    'entity_key' => $this->entity_key('term', intval($term->term_id)),
                    'entity_id' => intval($term->term_id),
                    'entity_type' => 'term',
                    'taxonomy' => (string) $term->taxonomy,
                    'url' => is_wp_error($url) ? '' : (string) $url,
                    'title' => (string) $term->name,
                    'content_hash' => md5($description),
                    'content_source_kind' => 'term',
                    'inventory_status' => 'discovered',
                    'manual_only_reason' => null,
                    'last_scanned_at' => current_time('mysql'),
                ));
            }
        }
    }

    private function import_legacy_log(int $project_id): void
    {
        $log = get_option('aysa_internal_linking_log', array());
        if (!is_array($log)) {
            return;
        }

        foreach ($log as $entry) {
            if (!is_array($entry)) {
                continue;
            }
            $source_type = sanitize_key((string) ($entry['source_entity_type'] ?? ''));
            $source_id = intval($entry['source_entity_id'] ?? 0);
            $source_key = $source_id > 0 ? $this->entity_key($source_type, $source_id) : 'url:' . md5((string) ($entry['source_url'] ?? ''));
            $target_url = $this->normalize_internal_target_url((string) ($entry['target_url'] ?? ''));
            if ($target_url === '') {
                continue;
            }
            $this->upsert_opportunity($project_id, array(
                'source_entity_key' => $source_key,
                'target_entity_key' => 'url:' . md5($target_url),
                'source_title' => (string) ($entry['source_title'] ?? ''),
                'source_url' => (string) ($entry['source_url'] ?? ''),
                'target_title' => (string) ($entry['target_title'] ?? ''),
                'target_url' => $target_url,
                'keyword' => (string) ($entry['keyword'] ?? ''),
                'anchor_text' => (string) ($entry['keyword'] ?? ''),
                'match_type' => !empty($entry['is_partial']) ? 'partial' : 'exact',
                'opportunity_status' => 'applied_by_aysa',
                'conflict_reason' => '',
                'approved_state' => 'approved',
                'applied_state' => 'applied',
                'last_validated_at' => (string) ($entry['timestamp'] ?? current_time('mysql')),
            ));
        }
    }

    private function import_legacy_preview(int $project_id): void
    {
        $state = get_option($this->preview_state_key($project_id), array());
        $data = is_array($state) && isset($state['data']) && is_array($state['data']) ? $state['data'] : array();
        $changes = isset($data['preview_changes']) && is_array($data['preview_changes']) ? $data['preview_changes'] : array();
        foreach ($changes as $row) {
            if (!is_array($row)) {
                continue;
            }
            $content_type = sanitize_key((string) ($row['content_type'] ?? 'post'));
            $content_id = intval($row['content_id'] ?? 0);
            $source_key = $content_id > 0 ? $this->entity_key($content_type, $content_id) : 'preview:' . md5((string) ($row['content_title'] ?? ''));
            $changes_rows = isset($row['changes']) && is_array($row['changes']) ? $row['changes'] : array();
            foreach ($changes_rows as $change) {
                if (!is_array($change)) {
                    continue;
                }
                $target_url = $this->normalize_internal_target_url((string) ($change['target_url'] ?? ''));
                if ($target_url === '') {
                    continue;
                }
                $this->upsert_opportunity($project_id, array(
                    'source_entity_key' => $source_key,
                    'target_entity_key' => 'url:' . md5($target_url),
                    'source_title' => (string) ($row['content_title'] ?? ''),
                    'source_url' => '',
                    'target_title' => (string) ($change['target_title'] ?? ''),
                    'target_url' => $target_url,
                    'keyword' => (string) ($change['keyword'] ?? ''),
                    'anchor_text' => (string) ($change['keyword'] ?? ''),
                    'match_type' => 'exact',
                    'opportunity_status' => 'ready_to_apply',
                    'conflict_reason' => '',
                    'approved_state' => 'pending',
                    'applied_state' => 'pending',
                    'last_validated_at' => current_time('mysql'),
                ));
            }
        }
    }

    private function get_inventory_rows(int $project_id): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM {$this->inventory_table} WHERE project_id = %d ORDER BY title ASC, id ASC", $project_id),
            ARRAY_A
        );
        return is_array($rows) ? $rows : array();
    }

    private function get_opportunity_rows(int $project_id): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM {$this->opportunities_table} WHERE project_id = %d ORDER BY updated_at DESC, id DESC", $project_id),
            ARRAY_A
        );
        if (!is_array($rows)) {
            return array();
        }

        $sanitized = array();
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $row = $this->sanitize_stored_opportunity_row($row);
            $sanitized[] = $row;
        }

        return $sanitized;
    }

    private function get_latest_run(int $project_id): array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$this->runs_table} WHERE project_id = %d ORDER BY updated_at DESC, id DESC LIMIT 1", $project_id),
            ARRAY_A
        );
        if (!is_array($row)) {
            return array();
        }
        $row['summary_json'] = $this->decode_json_field($row['summary_json'] ?? '');
        return $row;
    }

    private function build_workspace_rows(array $inventory, array $opportunities): array
    {
        $best = array();
        foreach ($opportunities as $opportunity) {
            if (!is_array($opportunity)) {
                continue;
            }
            $key = (string) ($opportunity['source_entity_key'] ?? '');
            if ($key === '') {
                continue;
            }
            $priority = $this->opportunity_priority((string) ($opportunity['opportunity_status'] ?? ''));
            if (!isset($best[$key]) || $priority > $best[$key]['_priority']) {
                $opportunity['_priority'] = $priority;
                $best[$key] = $opportunity;
            }
        }

        $rows = array();
        foreach ($inventory as $entity) {
            if (!is_array($entity)) {
                continue;
            }
            $key = (string) ($entity['entity_key'] ?? '');
            $opportunity = $best[$key] ?? array();
            $state = (string) ($opportunity['opportunity_status'] ?? ($entity['inventory_status'] ?? 'discovered'));
            $target_url = (string) ($opportunity['target_url'] ?? '');
            if ($state === 'applied_by_aysa') {
                if ($target_url === '') {
                    $state = 'invalid_target';
                    if (empty($opportunity['conflict_reason'])) {
                        $opportunity['conflict_reason'] = 'invalid_target';
                    }
                } elseif (!$this->entity_has_live_aysa_link($entity, $opportunity)) {
                    if (!empty($entity['manual_only_reason'])) {
                        $state = 'manual_only';
                        if (empty($opportunity['conflict_reason'])) {
                            $opportunity['conflict_reason'] = (string) $entity['manual_only_reason'];
                        }
                    } else {
                        $state = 'ready_to_apply';
                        if (empty($opportunity['conflict_reason'])) {
                            $opportunity['conflict_reason'] = 'missing_live_link';
                        }
                    }
                }
            }
            $entity_type = (string) ($entity['entity_type'] ?? '');
            $rows[] = array(
                'entity_key' => $key,
                'entity_id' => intval($entity['entity_id'] ?? 0),
                'source_page' => (string) ($entity['title'] ?? ''),
                'source_type' => $entity_type === 'term' ? (string) ($entity['taxonomy'] ?? 'term') : $entity_type,
                'url' => (string) ($entity['url'] ?? ''),
                'suggested_target' => (string) ($opportunity['target_title'] ?? ''),
                'target_url' => $target_url,
                'anchor' => (string) ($opportunity['anchor_text'] ?? ''),
                'state' => $state,
                'conflict_reason' => (string) ($opportunity['conflict_reason'] ?? ($entity['manual_only_reason'] ?? '')),
                'ownership' => $state === 'applied_by_aysa' ? 'AYSA' : 'None',
                'last_analyzed' => (string) ($entity['last_scanned_at'] ?? ''),
                'last_applied' => (string) ($entity['last_applied_at'] ?? ''),
                'next_step' => $this->next_step_for_row($state, $entity, $opportunity),
                'opportunity_id' => intval($opportunity['id'] ?? 0),
                'manual_only' => !empty($entity['manual_only_reason']),
            );
        }

        usort($rows, static function ($a, $b) {
            return strcmp((string) ($a['source_page'] ?? ''), (string) ($b['source_page'] ?? ''));
        });

        return $rows;
    }

    private function entity_has_live_aysa_link(array $entity, array $opportunity): bool
    {
        $content = $this->get_entity_stored_content($entity);
        if ($content === '' || strpos($content, 'aysa-internal-link') === false) {
            return false;
        }

        $target_url = $this->normalize_internal_target_url((string) ($opportunity['target_url'] ?? ''));
        if ($target_url === '') {
            return false;
        }

        $target_variants = array_unique(array_filter(array(
            untrailingslashit($target_url),
            trailingslashit($target_url),
        )));

        if (!preg_match_all('/<a\b[^>]*class=["\'][^"\']*aysa-internal-link[^"\']*["\'][^>]*>.*?<\/a>/isu', $content, $matches)) {
            return false;
        }

        foreach ((array) ($matches[0] ?? array()) as $anchor_html) {
            if (!is_string($anchor_html) || $anchor_html === '') {
                continue;
            }

            $href = $this->extract_anchor_href($anchor_html);
            if ($href === '') {
                continue;
            }

            $href = $this->normalize_internal_target_url($href);
            if ($href === '') {
                continue;
            }

            $href_variants = array_unique(array_filter(array(
                untrailingslashit($href),
                trailingslashit($href),
            )));

            if (count(array_intersect($target_variants, $href_variants)) > 0) {
                return true;
            }
        }

        return false;
    }

    private function get_entity_stored_content(array $entity): string
    {
        $entity_id = intval($entity['entity_id'] ?? 0);
        $entity_type = sanitize_key((string) ($entity['entity_type'] ?? ''));
        if ($entity_id <= 0 || $entity_type === '') {
            return '';
        }

        if ($entity_type === 'term') {
            $term = get_term($entity_id);
            if ($term && !is_wp_error($term)) {
                return (string) ($term->description ?? '');
            }
            return '';
        }

        $post = get_post($entity_id);
        if ($post instanceof WP_Post) {
            return (string) ($post->post_content ?? '');
        }

        return '';
    }

    private function extract_anchor_href(string $anchor_html): string
    {
        if (preg_match('/\bhref=(["\'])(.*?)\1/isu', $anchor_html, $matches)) {
            return html_entity_decode(trim((string) ($matches[2] ?? '')), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        }

        return '';
    }

    private function build_summary(array $rows, array $run): array
    {
        $summary = array(
            'all' => count($rows),
            'needs_review' => 0,
            'ready_to_apply' => 0,
            'applied_by_aysa' => 0,
            'conflict' => 0,
            'manual_only' => 0,
            'invalid_target' => 0,
            'status' => (string) ($run['status'] ?? 'idle'),
            'current_milestone' => (string) ($run['current_milestone'] ?? ''),
        );
        foreach ($rows as $row) {
            $state = (string) ($row['state'] ?? '');
            if (in_array($state, array('conflict', 'manual_only', 'invalid_target', 'discovered', 'analyzed'), true)) {
                $summary['needs_review']++;
            }
            if (isset($summary[$state])) {
                $summary[$state]++;
            }
        }
        return $summary;
    }

    private function build_filter_counts(array $rows): array
    {
        $counts = array(
            'all' => count($rows),
            'needs_review' => 0,
            'ready_to_apply' => 0,
            'applied_by_aysa' => 0,
            'conflict' => 0,
            'manual_only' => 0,
            'invalid_target' => 0,
            'articles' => 0,
            'products' => 0,
            'pages' => 0,
            'terms' => 0,
        );
        foreach ($rows as $row) {
            $state = (string) ($row['state'] ?? '');
            if (isset($counts[$state])) {
                $counts[$state]++;
            }
            if (in_array($state, array('conflict', 'manual_only', 'invalid_target', 'discovered', 'analyzed'), true)) {
                $counts['needs_review']++;
            }
            $type = (string) ($row['source_type'] ?? '');
            if ($type === 'post') {
                $counts['articles']++;
            } elseif ($type === 'product') {
                $counts['products']++;
            } elseif ($type === 'page') {
                $counts['pages']++;
            } else {
                $counts['terms']++;
            }
        }
        return $counts;
    }

    private function apply_filters(array $rows, array $filters): array
    {
        $view = sanitize_key((string) ($filters['view'] ?? 'all'));
        return array_values(array_filter($rows, static function ($row) use ($view) {
            $state = (string) ($row['state'] ?? '');
            $type = (string) ($row['source_type'] ?? '');
            switch ($view) {
                case 'needs_review':
                    return in_array($state, array('conflict', 'manual_only', 'invalid_target', 'discovered', 'analyzed'), true);
                case 'ready_to_apply':
                case 'applied_by_aysa':
                case 'conflict':
                case 'manual_only':
                case 'invalid_target':
                    return $state === $view;
                case 'articles':
                    return $type === 'post';
                case 'products':
                    return $type === 'product';
                case 'pages':
                    return $type === 'page';
                case 'terms':
                    return !in_array($type, array('post', 'product', 'page'), true);
                default:
                    return true;
            }
        }));
    }

    private function upsert_inventory(int $project_id, array $row): void
    {
        global $wpdb;
        $entity_key = sanitize_text_field((string) ($row['entity_key'] ?? ''));
        if ($entity_key === '') {
            return;
        }
        $payload = array(
            'project_id' => $project_id,
            'entity_key' => $entity_key,
            'entity_id' => intval($row['entity_id'] ?? 0),
            'entity_type' => sanitize_key((string) ($row['entity_type'] ?? '')),
            'taxonomy' => sanitize_key((string) ($row['taxonomy'] ?? '')),
            'url' => esc_url_raw((string) ($row['url'] ?? '')),
            'title' => sanitize_text_field((string) ($row['title'] ?? '')),
            'content_hash' => sanitize_text_field((string) ($row['content_hash'] ?? '')),
            'content_source_kind' => sanitize_key((string) ($row['content_source_kind'] ?? 'wordpress')),
            'inventory_status' => sanitize_key((string) ($row['inventory_status'] ?? 'discovered')),
            'manual_only_reason' => $this->nullable_string($row['manual_only_reason'] ?? null),
            'last_scanned_at' => $this->nullable_datetime($row['last_scanned_at'] ?? null),
            'last_applied_at' => $this->nullable_datetime($row['last_applied_at'] ?? null),
            'last_reconciled_at' => $this->nullable_datetime($row['last_reconciled_at'] ?? null),
        );
        $existing = $wpdb->get_var(
            $wpdb->prepare("SELECT id FROM {$this->inventory_table} WHERE project_id = %d AND entity_key = %s LIMIT 1", $project_id, $entity_key)
        );
        if ($existing) {
            $wpdb->update($this->inventory_table, $payload, array('id' => intval($existing)));
        } else {
            $wpdb->insert($this->inventory_table, $payload);
        }
    }

    private function upsert_opportunity(int $project_id, array $row): void
    {
        global $wpdb;
        $source_entity_key = sanitize_text_field((string) ($row['source_entity_key'] ?? ''));
        if ($source_entity_key === '') {
            return;
        }
        $match_type = sanitize_key((string) ($row['match_type'] ?? 'exact'));
        $keyword = sanitize_text_field((string) ($row['keyword'] ?? ''));
        $target_url = $this->normalize_internal_target_url((string) ($row['target_url'] ?? ''));
        $existing = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM {$this->opportunities_table} WHERE project_id = %d AND source_entity_key = %s AND keyword = %s AND target_url = %s AND match_type = %s LIMIT 1",
                $project_id,
                $source_entity_key,
                $keyword,
                $target_url,
                $match_type
            )
        );

        $status = sanitize_key((string) ($row['opportunity_status'] ?? 'discovered'));
        if ($target_url === '' && in_array($status, array('ready_to_apply', 'applied_by_aysa', 'conflict'), true)) {
            $status = 'invalid_target';
        }

        $payload = array(
            'project_id' => $project_id,
            'source_entity_key' => $source_entity_key,
            'target_entity_key' => sanitize_text_field((string) ($row['target_entity_key'] ?? ($target_url !== '' ? 'url:' . md5($target_url) : ''))),
            'source_title' => sanitize_text_field((string) ($row['source_title'] ?? '')),
            'source_url' => esc_url_raw((string) ($row['source_url'] ?? '')),
            'target_title' => sanitize_text_field((string) ($this->normalize_internal_target_title((string) ($row['target_title'] ?? ''), $target_url) ?? '')),
            'target_url' => $target_url,
            'keyword' => $keyword,
            'anchor_text' => sanitize_text_field((string) ($row['anchor_text'] ?? $keyword)),
            'match_type' => $match_type,
            'opportunity_status' => $status,
            'conflict_reason' => $this->nullable_string($row['conflict_reason'] ?? ($status === 'invalid_target' ? 'invalid_target' : null)),
            'approved_state' => sanitize_key((string) ($row['approved_state'] ?? ($status === 'invalid_target' ? 'pending' : 'pending'))),
            'applied_state' => sanitize_key((string) ($row['applied_state'] ?? ($status === 'invalid_target' ? 'pending' : 'pending'))),
            'worker_run_id' => intval($row['worker_run_id'] ?? 0),
            'last_validated_at' => $this->nullable_datetime($row['last_validated_at'] ?? null),
        );

        if ($existing) {
            $wpdb->update($this->opportunities_table, $payload, array('id' => intval($existing)));
        } else {
            $wpdb->insert($this->opportunities_table, $payload);
        }
    }

    private function preview_state_key(int $project_id): string
    {
        return 'aysa_technical_internal_linking_preview_state_' . $project_id;
    }

    private function entity_key(string $type, int $id): string
    {
        return sanitize_key($type) . ':' . max(0, $id);
    }

    private function is_elementor_post(int $post_id): bool
    {
        return (bool) get_post_meta($post_id, '_elementor_edit_mode', true) || (bool) get_post_meta($post_id, '_elementor_data', true);
    }

    private function opportunity_priority(string $status): int
    {
        $map = array(
            'conflict' => 90,
            'manual_only' => 80,
            'invalid_target' => 70,
            'ready_to_apply' => 60,
            'analyzed' => 50,
            'discovered' => 40,
            'applied_by_aysa' => 30,
            'skipped' => 20,
            'removed' => 10,
        );
        return intval($map[$status] ?? 0);
    }

    private function next_step_for_row(string $state, array $entity, array $opportunity): string
    {
        if (!empty($entity['manual_only_reason'])) {
            return 'Manual only';
        }
        switch ($state) {
            case 'ready_to_apply':
                return 'Apply';
            case 'conflict':
                return 'Review conflict';
            case 'invalid_target':
                return 'Validate target';
            case 'applied_by_aysa':
                return 'Validate';
            case 'skipped':
                return 'Review skipped';
            default:
                return !empty($opportunity) ? 'Review' : 'Analyze';
        }
    }

    private function format_preview_changes_from_mappings($link_mappings): array
    {
        $changes_by_content = array();
        foreach (is_array($link_mappings) ? $link_mappings : array() as $mapping) {
            if (!is_array($mapping)) {
                continue;
            }
            $target_url = $this->normalize_internal_target_url((string) ($mapping['target_url'] ?? ''));
            if ($target_url === '') {
                continue;
            }
            $content_id = intval($mapping['source_content_id'] ?? 0);
            $key = $content_id > 0 ? 'id:' . $content_id : md5((string) ($mapping['source_content_title'] ?? ''));
            if (!isset($changes_by_content[$key])) {
                $changes_by_content[$key] = array(
                    'content_title' => sanitize_text_field((string) ($mapping['source_content_title'] ?? "Content #{$content_id}")),
                    'content_id' => $content_id,
                    'content_type' => sanitize_key((string) ($mapping['source_content_type'] ?? 'post')),
                    'links_count' => 0,
                    'changes' => array(),
                );
            }
            $changes_by_content[$key]['links_count']++;
            $changes_by_content[$key]['changes'][] = array(
                'keyword' => sanitize_text_field((string) ($mapping['keyword'] ?? '')),
                'target_title' => sanitize_text_field((string) ($mapping['target_page'] ?? '')),
                'target_url' => $target_url,
            );
        }
        return array_values($changes_by_content);
    }

    private function sanitize_stored_opportunity_row(array $row): array
    {
        $target_url = $this->normalize_internal_target_url((string) ($row['target_url'] ?? ''));
        $row['target_url'] = $target_url;
        $row['target_title'] = $this->normalize_internal_target_title((string) ($row['target_title'] ?? ''), $target_url);

        $status = sanitize_key((string) ($row['opportunity_status'] ?? 'discovered'));
        if ($target_url === '' && in_array($status, array('ready_to_apply', 'applied_by_aysa', 'conflict'), true)) {
            $row['opportunity_status'] = 'invalid_target';
            if (empty($row['conflict_reason'])) {
                $row['conflict_reason'] = 'invalid_target';
            }
            $row['approved_state'] = 'pending';
            $row['applied_state'] = 'pending';
        }

        return $row;
    }

    private function normalize_internal_target_title(string $title, string $target_url): string
    {
        $title = sanitize_text_field($title);
        if ($title !== '' || $target_url === '') {
            return $title;
        }

        $post_id = url_to_postid($target_url);
        if ($post_id > 0) {
            return sanitize_text_field((string) get_the_title($post_id));
        }

        $path = trim((string) wp_parse_url($target_url, PHP_URL_PATH), '/');
        if ($path === '') {
            return '';
        }
        $slug = basename($path);
        foreach ((array) get_taxonomies(array('public' => true), 'names') as $taxonomy) {
            $term = get_term_by('slug', $slug, $taxonomy);
            if ($term && !is_wp_error($term)) {
                $term_link = get_term_link($term);
                if (is_string($term_link) && untrailingslashit($term_link) === untrailingslashit($target_url)) {
                    return sanitize_text_field((string) $term->name);
                }
            }
        }

        return '';
    }

    private function normalize_internal_target_url(string $url): string
    {
        $url = html_entity_decode(trim($url), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        if ($url === '') {
            return '';
        }

        $normalized = strtolower($url);
        if (in_array($normalized, array('false', 'http://false', 'https://false', 'http://', 'https://'), true)) {
            return '';
        }

        $url = esc_url_raw($url);
        if ($url === '' || !wp_http_validate_url($url)) {
            return '';
        }

        $home_host = strtolower((string) wp_parse_url(home_url('/'), PHP_URL_HOST));
        $url_host = strtolower((string) wp_parse_url($url, PHP_URL_HOST));
        if ($home_host !== '' && $url_host !== '' && $home_host !== $url_host) {
            return '';
        }

        $path = trim((string) wp_parse_url($url, PHP_URL_PATH), '/');
        if (in_array($path, array('sample-page'), true)) {
            return '';
        }

        if ($path === 'wp-login.php' || strpos($path, 'wp-admin/') === 0 || $path === 'wp-admin') {
            return '';
        }

        $post_id = url_to_postid($url);
        if ($post_id > 0) {
            $permalink = get_permalink($post_id);
            if (is_string($permalink) && $permalink !== '') {
                return esc_url_raw($permalink);
            }
        }

        if ($path !== '') {
            $slug = basename($path);
            foreach ((array) get_taxonomies(array('public' => true), 'names') as $taxonomy) {
                $term = get_term_by('slug', $slug, $taxonomy);
                if (!$term || is_wp_error($term)) {
                    continue;
                }
                $term_link = get_term_link($term);
                if (is_string($term_link) && untrailingslashit($term_link) === untrailingslashit($url)) {
                    return esc_url_raw($term_link);
                }
            }
        }

        return $url;
    }

    private function decode_json_field($value): array
    {
        $decoded = json_decode((string) $value, true);
        return is_array($decoded) ? $decoded : array();
    }

    private function nullable_string($value): ?string
    {
        $value = is_scalar($value) ? trim((string) $value) : '';
        return $value === '' ? null : $value;
    }

    private function nullable_datetime($value): ?string
    {
        $value = is_scalar($value) ? trim((string) $value) : '';
        return $value === '' ? null : $value;
    }
}
