<?php
/**
 * Persistent publication history for Social Publisher.
 *
 * Keeps user-facing publication outcomes separate from the short diagnostic log.
 * One row represents one destination inside a publication run.
 */

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

final class Calliope_SP_History {
    private const DB_VERSION = '4';
    private const DB_VERSION_OPTION = 'calliope_sp_history_db_version';
    private const TABLE_SUFFIX = 'calliope_sp_publication_history';
    private const UPGRADE_LOCK_OPTION = 'calliope_sp_history_db_upgrade_lock';
    private const UPGRADE_LOCK_TTL = 120;

    /** @var array<int,bool> */
    private static $table_exists_cache = [];

    /**
     * Create the history table on activation. Supports network activation.
     *
     * @param bool $network_wide Whether the plugin was network activated.
     * @return void
     */
    public static function activate($network_wide = false): void {
        if (is_multisite() && $network_wide) {
            // Batch site enumeration so activation remains predictable on large networks.
            $offset = 0;
            $batch_size = 100;
            do {
                $site_ids = get_sites([
                    'fields' => 'ids',
                    'number' => $batch_size,
                    'offset' => $offset,
                    'orderby' => 'id',
                    'order' => 'ASC',
                ]);

                foreach ((array) $site_ids as $site_id) {
                    switch_to_blog((int) $site_id);
                    try {
                        self::install_schema();
                    } finally {
                        restore_current_blog();
                    }
                }

                $count = is_array($site_ids) ? count($site_ids) : 0;
                $offset += $count;
            } while ($count === $batch_size);
            return;
        }

        self::install_schema();
    }

    /**
     * Create storage for a site added after network activation.
     *
     * @param WP_Site|int $new_site New site object or ID.
     * @return void
     */
    public static function initialize_new_site($new_site): void {
        if (!is_multisite()) {
            return;
        }

        $site_id = $new_site instanceof WP_Site ? (int) $new_site->blog_id : absint($new_site);
        if ($site_id <= 0) {
            return;
        }

        if (!function_exists('is_plugin_active_for_network')) {
            require_once ABSPATH . 'wp-admin/includes/plugin.php';
        }

        if (!is_plugin_active_for_network('calliope-social-publisher/calliope-social-publisher.php')) {
            return;
        }

        switch_to_blog($site_id);
        try {
            self::install_schema();
        } finally {
            restore_current_blog();
        }
    }

    /**
     * Upgrade the table for existing installs after a normal plugin update.
     *
     * @return void
     */
    public static function maybe_upgrade(): void {
        if ((string) get_option(self::DB_VERSION_OPTION, '') === self::DB_VERSION) {
            return;
        }

        $now = time();
        $lock = get_option(self::UPGRADE_LOCK_OPTION, []);
        $locked_at = is_array($lock) ? absint($lock['created_at'] ?? 0) : 0;
        if ($locked_at > 0 && $locked_at >= ($now - self::UPGRADE_LOCK_TTL)) {
            return;
        }

        delete_option(self::UPGRADE_LOCK_OPTION);
        if (!add_option(self::UPGRADE_LOCK_OPTION, ['created_at' => $now], '', 'no')) {
            return;
        }

        try {
            // Recheck after acquiring the lock in case another request completed the migration.
            if ((string) get_option(self::DB_VERSION_OPTION, '') !== self::DB_VERSION) {
                self::install_schema();
            }
        } finally {
            delete_option(self::UPGRADE_LOCK_OPTION);
        }
    }

    /**
     * Return the site-specific history table name.
     *
     * @return string
     */
    public static function table_name(): string {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_SUFFIX;
    }

    /**
     * Return a lightweight, secret-free dashboard summary of persistent history.
     *
     * @return array{ready:bool,total:int,runs:int,success:int,failed:int,pending:int,last_activity:string}
     */
    public static function get_dashboard_summary(): array {
        global $wpdb;

        $empty = [
            'ready'         => false,
            'total'         => 0,
            'runs'          => 0,
            'success'       => 0,
            'failed'        => 0,
            'pending'       => 0,
            'last_activity' => '',
        ];

        $table = self::table_name();

        if (!self::table_exists()) {
            return $empty;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is generated internally from $wpdb->prefix and a fixed suffix.
        $row = $wpdb->get_row(
            "SELECT COUNT(*) AS total,
                    COUNT(DISTINCT CASE WHEN job_id <> '' THEN CONCAT('job:', job_id) WHEN schedule_uuid <> '' THEN CONCAT('schedule:', schedule_uuid) ELSE CONCAT('publication:', publication_uuid) END) AS runs,
                    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
                    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
                    MAX(created_at) AS last_activity
             FROM {$table}",
            ARRAY_A
        );

        if (!is_array($row)) {
            $empty['ready'] = true;
            return $empty;
        }

        return [
            'ready'         => true,
            'total'         => absint($row['total'] ?? 0),
            'runs'          => absint($row['runs'] ?? 0),
            'success'       => absint($row['success'] ?? 0),
            'failed'        => absint($row['failed'] ?? 0),
            'pending'       => absint($row['pending'] ?? 0),
            'last_activity' => sanitize_text_field((string) ($row['last_activity'] ?? '')),
        ];
    }

    /**
     * Return a compact dashboard summary for the current WordPress calendar day.
     *
     * History timestamps are stored in UTC. Day boundaries are calculated in the
     * site timezone and converted back to UTC before querying, so "Today" matches
     * the date administrators see in WordPress rather than the server timezone.
     * updated_at is used because a History retry should count as activity on the
     * day it is actually processed even when the original row was created earlier.
     *
     * @return array{ready:bool,publications:int,success:int,failed:int,pending:int,runs:int,most_active_network:string,most_active_count:int}
     */
    public static function get_today_summary(): array {
        global $wpdb;

        $empty = [
            'ready'               => false,
            'publications'        => 0,
            'success'             => 0,
            'failed'              => 0,
            'pending'             => 0,
            'runs'                => 0,
            'most_active_network' => '',
            'most_active_count'   => 0,
        ];

        if (!self::table_exists()) {
            return $empty;
        }

        $table = self::table_name();
        $site_timezone = wp_timezone();
        $utc_timezone = new DateTimeZone('UTC');
        $today = new DateTimeImmutable('now', $site_timezone);
        $local_start = $today->setTime(0, 0, 0);
        $local_end = $local_start->modify('+1 day');
        $from = $local_start->setTimezone($utc_timezone)->format('Y-m-d H:i:s');
        $to = $local_end->setTimezone($utc_timezone)->format('Y-m-d H:i:s');

        $summary_sql = $wpdb->prepare(
            "SELECT SUM(CASE WHEN status IN ('success', 'failed') THEN 1 ELSE 0 END) AS publications,
                    COUNT(DISTINCT CASE WHEN status IN ('success', 'failed') THEN CASE WHEN job_id <> '' THEN CONCAT('job:', job_id) WHEN schedule_uuid <> '' THEN CONCAT('schedule:', schedule_uuid) ELSE CONCAT('publication:', publication_uuid) END ELSE NULL END) AS runs,
                    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
                    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
             FROM {$table}
             WHERE updated_at >= %s AND updated_at < %s",
            $from,
            $to
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Plugin-owned operational History table; values are prepared above.
        $summary = $wpdb->get_row($summary_sql, ARRAY_A);

        $network_sql = $wpdb->prepare(
            "SELECT network, COUNT(*) AS activity_count, MAX(updated_at) AS last_activity
             FROM {$table}
             WHERE updated_at >= %s AND updated_at < %s AND network <> '' AND status IN ('success', 'failed')
             GROUP BY network
             ORDER BY activity_count DESC, last_activity DESC, network ASC
             LIMIT 1",
            $from,
            $to
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Plugin-owned operational History table; values are prepared above.
        $network = $wpdb->get_row($network_sql, ARRAY_A);

        return [
            'ready'               => true,
            'publications'        => absint(is_array($summary) ? ($summary['publications'] ?? 0) : 0),
            'success'             => absint(is_array($summary) ? ($summary['success'] ?? 0) : 0),
            'failed'              => absint(is_array($summary) ? ($summary['failed'] ?? 0) : 0),
            'pending'             => absint(is_array($summary) ? ($summary['pending'] ?? 0) : 0),
            'runs'                => absint(is_array($summary) ? ($summary['runs'] ?? 0) : 0),
            'most_active_network' => sanitize_key(is_array($network) ? (string) ($network['network'] ?? '') : ''),
            'most_active_count'   => absint(is_array($network) ? ($network['activity_count'] ?? 0) : 0),
        ];
    }

    /**
     * Return the most recent publication runs grouped by publication UUID.
     *
     * The dashboard intentionally shows publication-level activity instead of one
     * row per destination, so a post sent to ten networks remains a single item.
     *
     * @param int $limit Number of publication runs to return.
     * @return array<int,array<string,mixed>>
     */
    public static function get_recent_publication_runs(int $limit = 3): array {
        global $wpdb;

        $limit = max(1, min(10, $limit));
        if (!self::table_exists()) {
            return [];
        }

        $table = self::table_name();
        $sql = $wpdb->prepare(
            "SELECT CASE WHEN job_id <> '' THEN CONCAT('job:', job_id) WHEN schedule_uuid <> '' THEN CONCAT('schedule:', schedule_uuid) ELSE CONCAT('publication:', publication_uuid) END AS run_key,
                    MAX(publication_uuid) AS publication_uuid,
                    MAX(job_id) AS job_id,
                    MAX(schedule_uuid) AS schedule_uuid,
                    MAX(updated_at) AS activity_at,
                    MAX(post_title) AS post_title,
                    MAX(source) AS source,
                    COUNT(*) AS destinations,
                    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
                    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
             FROM {$table}
             GROUP BY run_key
             ORDER BY activity_at DESC
             LIMIT %d",
            $limit
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Plugin-owned table; limit is prepared above.
        $rows = $wpdb->get_results($sql, ARRAY_A);

        $recent = [];
        foreach (is_array($rows) ? $rows : [] as $row) {
            $uuid = sanitize_text_field((string) ($row['publication_uuid'] ?? ''));
            if ($uuid === '') {
                continue;
            }
            $recent[] = [
                'publication_uuid' => $uuid,
                'run_key'          => sanitize_text_field((string) ($row['run_key'] ?? '')),
                'job_id'           => sanitize_text_field((string) ($row['job_id'] ?? '')),
                'schedule_uuid'    => sanitize_text_field((string) ($row['schedule_uuid'] ?? '')),
                'activity_at'      => sanitize_text_field((string) ($row['activity_at'] ?? '')),
                'post_title'       => sanitize_text_field((string) ($row['post_title'] ?? '')),
                'source'           => sanitize_key((string) ($row['source'] ?? '')),
                'destinations'     => absint($row['destinations'] ?? 0),
                'success'          => absint($row['success'] ?? 0),
                'failed'           => absint($row['failed'] ?? 0),
                'pending'          => absint($row['pending'] ?? 0),
            ];
        }

        return $recent;
    }

    /**
     * Return basic Free analytics for a rolling period.
     *
     * Publications are counted as unique publication runs, while status totals are
     * destination-level outcomes. Pending destinations are excluded from the
     * success-rate denominator so an in-flight publication does not look like a failure.
     *
     * @param int $days Rolling period in days. Free supports 7, 30, and 90 days.
     * @return array{ready:bool,days:int,runs:int,total:int,success:int,failed:int,pending:int,success_rate:float,active_networks:int,networks:array<int,array<string,mixed>>}
     */
    public static function get_basic_analytics(int $days = 30): array {
        global $wpdb;

        $allowed_days = [7, 30, 90];
        if (!in_array($days, $allowed_days, true)) {
            $days = 30;
        }

        $empty = [
            'ready'           => false,
            'days'            => $days,
            'runs'            => 0,
            'total'           => 0,
            'success'         => 0,
            'failed'          => 0,
            'pending'         => 0,
            'success_rate'    => 0.0,
            'active_networks' => 0,
            'networks'        => [],
        ];

        if (!self::table_exists()) {
            return $empty;
        }

        $table = self::table_name();
        $from = gmdate('Y-m-d 00:00:00', time() - (($days - 1) * DAY_IN_SECONDS));

        $summary_sql = $wpdb->prepare(
            "SELECT COUNT(DISTINCT CASE WHEN job_id <> '' THEN CONCAT('job:', job_id) WHEN schedule_uuid <> '' THEN CONCAT('schedule:', schedule_uuid) ELSE CONCAT('publication:', publication_uuid) END) AS runs,
                    COUNT(*) AS total,
                    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
                    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
                    COUNT(DISTINCT network) AS active_networks
             FROM {$table}
             WHERE created_at >= %s",
            $from
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Internal table name; date is prepared above.
        $summary = $wpdb->get_row($summary_sql, ARRAY_A);

        $network_sql = $wpdb->prepare(
            "SELECT network,
                    COUNT(DISTINCT CASE WHEN job_id <> '' THEN CONCAT('job:', job_id) WHEN schedule_uuid <> '' THEN CONCAT('schedule:', schedule_uuid) ELSE CONCAT('publication:', publication_uuid) END) AS runs,
                    COUNT(*) AS total,
                    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
                    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
             FROM {$table}
             WHERE created_at >= %s
             GROUP BY network
             ORDER BY total DESC, network ASC",
            $from
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Internal table name; date is prepared above.
        $network_rows = $wpdb->get_results($network_sql, ARRAY_A);

        $success = absint(is_array($summary) ? ($summary['success'] ?? 0) : 0);
        $failed = absint(is_array($summary) ? ($summary['failed'] ?? 0) : 0);
        $completed = $success + $failed;

        $networks = [];
        foreach (is_array($network_rows) ? $network_rows : [] as $row) {
            $network_success = absint($row['success'] ?? 0);
            $network_failed = absint($row['failed'] ?? 0);
            $network_completed = $network_success + $network_failed;
            $networks[] = [
                'network'      => sanitize_key((string) ($row['network'] ?? '')),
                'runs'         => absint($row['runs'] ?? 0),
                'total'        => absint($row['total'] ?? 0),
                'success'      => $network_success,
                'failed'       => $network_failed,
                'pending'      => absint($row['pending'] ?? 0),
                'success_rate' => $network_completed > 0 ? round(($network_success / $network_completed) * 100, 1) : 0.0,
            ];
        }

        return [
            'ready'           => true,
            'days'            => $days,
            'runs'            => absint(is_array($summary) ? ($summary['runs'] ?? 0) : 0),
            'total'           => absint(is_array($summary) ? ($summary['total'] ?? 0) : 0),
            'success'         => $success,
            'failed'          => $failed,
            'pending'         => absint(is_array($summary) ? ($summary['pending'] ?? 0) : 0),
            'success_rate'    => $completed > 0 ? round(($success / $completed) * 100, 1) : 0.0,
            'active_networks' => absint(is_array($summary) ? ($summary['active_networks'] ?? 0) : 0),
            'networks'        => $networks,
        ];
    }

    /**
     * Return daily Free activity points for the Analytics chart.
     *
     * The chart uses the same calendar-day window as get_basic_analytics(), so
     * totals remain consistent with the KPI cards. Network filtering is optional
     * and applies only to the chart, not to the global KPI summary.
     *
     * @param int    $days    Calendar-day period. Free supports 7, 30, and 90 days.
     * @param string $network Optional network slug.
     * @return array{ready:bool,days:int,network:string,points:array<int,array<string,mixed>>,max_value:int}
     */
    public static function get_activity_series(int $days = 30, string $network = ''): array {
        global $wpdb;

        $allowed_days = [7, 30, 90];
        if (!in_array($days, $allowed_days, true)) {
            $days = 30;
        }

        $network = sanitize_key($network);
        $empty = [
            'ready'     => false,
            'days'      => $days,
            'network'   => $network,
            'points'    => [],
            'max_value' => 0,
        ];

        if (!self::table_exists()) {
            return $empty;
        }

        $table = self::table_name();
        $start_timestamp = time() - (($days - 1) * DAY_IN_SECONDS);
        $from = gmdate('Y-m-d 00:00:00', $start_timestamp);

        if ($network !== '') {
            $sql = $wpdb->prepare(
                "SELECT DATE(created_at) AS activity_date,
                        SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success,
                        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
                        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
                 FROM {$table}
                 WHERE created_at >= %s AND network = %s
                 GROUP BY DATE(created_at)
                 ORDER BY activity_date ASC",
                $from,
                $network
            );
        } else {
            $sql = $wpdb->prepare(
                "SELECT DATE(created_at) AS activity_date,
                        SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success,
                        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
                        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
                 FROM {$table}
                 WHERE created_at >= %s
                 GROUP BY DATE(created_at)
                 ORDER BY activity_date ASC",
                $from
            );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Internal table name; dynamic values are prepared above.
        $rows = $wpdb->get_results($sql, ARRAY_A);
        $by_date = [];
        foreach (is_array($rows) ? $rows : [] as $row) {
            $key = sanitize_text_field((string) ($row['activity_date'] ?? ''));
            if ($key === '') {
                continue;
            }
            $by_date[$key] = [
                'success' => absint($row['success'] ?? 0),
                'failed'  => absint($row['failed'] ?? 0),
                'pending' => absint($row['pending'] ?? 0),
            ];
        }

        $points = [];
        $max_value = 0;
        for ($i = 0; $i < $days; $i++) {
            $timestamp = $start_timestamp + ($i * DAY_IN_SECONDS);
            $date_key = gmdate('Y-m-d', $timestamp);
            $values = $by_date[$date_key] ?? ['success' => 0, 'failed' => 0, 'pending' => 0];
            $success = absint($values['success'] ?? 0);
            $failed = absint($values['failed'] ?? 0);
            $pending = absint($values['pending'] ?? 0);
            $max_value = max($max_value, $success, $failed);
            $points[] = [
                'date'    => $date_key,
                'label'   => wp_date('M j', strtotime($date_key . ' 12:00:00 UTC')),
                'success' => $success,
                'failed'  => $failed,
                'pending' => $pending,
            ];
        }

        return [
            'ready'     => true,
            'days'      => $days,
            'network'   => $network,
            'points'    => $points,
            'max_value' => $max_value,
        ];
    }

    /**
     * Return a paginated, filtered list of persistent publication destinations.
     *
     * The Free History screen intentionally exposes only broad operational filters.
     * Pro can layer account/content/date analytics on top without changing this storage API.
     *
     * @param array<string,mixed> $args Query arguments.
     * @return array{ready:bool,items:array<int,array<string,mixed>>,total:int,page:int,per_page:int,total_pages:int}
     */
    public static function get_history_page(array $args = []): array {
        global $wpdb;

        $page = max(1, absint($args['page'] ?? 1));
        $per_page = min(100, max(10, absint($args['per_page'] ?? 20)));
        $network = sanitize_key((string) ($args['network'] ?? ''));
        $status = sanitize_key((string) ($args['status'] ?? ''));
        $search = sanitize_text_field((string) ($args['search'] ?? ''));
        if (!in_array($status, ['', 'success', 'failed', 'pending'], true)) {
            $status = '';
        }

        $empty = [
            'ready'       => false,
            'items'       => [],
            'total'       => 0,
            'page'        => $page,
            'per_page'    => $per_page,
            'total_pages' => 0,
        ];

        if (!self::table_exists()) {
            return $empty;
        }

        $table = self::table_name();
        $where = ['1=1'];
        $params = [];

        if ($network !== '') {
            $where[] = 'network = %s';
            $params[] = $network;
        }
        if ($status !== '') {
            $where[] = 'status = %s';
            $params[] = $status;
        }
        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where[] = '(post_title LIKE %s OR account_label LIKE %s OR account_id LIKE %s OR provider_post_id LIKE %s OR error_summary LIKE %s)';
            array_push($params, $like, $like, $like, $like, $like);
        }

        $where_sql = implode(' AND ', $where);
        $count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}";
        if ($params) {
            $count_sql = $wpdb->prepare($count_sql, $params);
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Table and WHERE fragments are internal; values are prepared above.
        $total = absint($wpdb->get_var($count_sql));
        $total_pages = $total > 0 ? (int) ceil($total / $per_page) : 0;
        if ($total_pages > 0 && $page > $total_pages) {
            $page = $total_pages;
        }
        $offset = ($page - 1) * $per_page;

        $items_sql = "SELECT id, publication_uuid, schedule_uuid, post_id, post_title, post_url, media_attachment_id, network, account_id, account_label, trigger_type, source, ai_used, content_type, status, outcome, provider_post_id, provider_post_url, retry_count, error_code, error_summary, created_at, updated_at, published_at,
                             CASE WHEN retry_payload IS NOT NULL AND retry_payload <> '' THEN 1 ELSE 0 END AS retry_available
                      FROM {$table}
                      WHERE {$where_sql}
                      ORDER BY created_at DESC, id DESC
                      LIMIT %d OFFSET %d";
        $items_params = array_merge($params, [$per_page, $offset]);
        $items_sql = $wpdb->prepare($items_sql, $items_params);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- Table and WHERE fragments are internal; values are prepared above.
        $items = $wpdb->get_results($items_sql, ARRAY_A);

        return [
            'ready'       => true,
            'items'       => is_array($items) ? $items : [],
            'total'       => $total,
            'page'        => $page,
            'per_page'    => $per_page,
            'total_pages' => $total_pages,
        ];
    }

    /**
     * Return the networks currently present in persistent History.
     *
     * @return string[]
     */
    public static function get_history_networks(): array {
        global $wpdb;

        if (!self::table_exists()) {
            return [];
        }

        $table = self::table_name();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is generated internally from a fixed suffix.
        $networks = $wpdb->get_col("SELECT DISTINCT network FROM {$table} WHERE network <> '' ORDER BY network ASC");
        if (!is_array($networks)) {
            return [];
        }

        return array_values(array_unique(array_filter(array_map('sanitize_key', $networks))));
    }

    /**
     * Check whether persistent History storage is available for this site.
     *
     * @return bool
     */
    public static function table_exists(): bool {
        global $wpdb;

        $blog_id = function_exists('get_current_blog_id') ? (int) get_current_blog_id() : 0;
        if (array_key_exists($blog_id, self::$table_exists_cache)) {
            return self::$table_exists_cache[$blog_id];
        }

        $table = self::table_name();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned schema readiness check cached for the request.
        $found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table)));
        self::$table_exists_cache[$blog_id] = is_string($found) && $found === $table;
        return self::$table_exists_cache[$blog_id];
    }

    /**
     * Create or upgrade the history table.
     *
     * @return void
     */
    private static function install_schema(): void {
        global $wpdb;

        if (!function_exists('dbDelta')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        $table = self::table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            publication_uuid char(36) NOT NULL,
            history_key varchar(191) NOT NULL,
            job_id varchar(64) NOT NULL DEFAULT '',
            schedule_uuid char(36) NOT NULL DEFAULT '',
            post_id bigint(20) unsigned NOT NULL DEFAULT 0,
            post_title varchar(255) NOT NULL DEFAULT '',
            post_url text NULL,
            media_attachment_id bigint(20) unsigned NOT NULL DEFAULT 0,
            network varchar(64) NOT NULL,
            account_id varchar(191) NOT NULL DEFAULT '',
            account_label varchar(191) NOT NULL DEFAULT '',
            trigger_type varchar(20) NOT NULL DEFAULT 'manual',
            source varchar(64) NOT NULL DEFAULT 'manual',
            ai_used tinyint(1) unsigned NOT NULL DEFAULT 0,
            content_type varchar(20) NOT NULL DEFAULT 'text',
            status varchar(20) NOT NULL DEFAULT 'pending',
            outcome varchar(32) NOT NULL DEFAULT 'pending',
            provider_post_id varchar(255) NOT NULL DEFAULT '',
            provider_post_url text NULL,
            retry_count smallint(5) unsigned NOT NULL DEFAULT 0,
            error_code varchar(100) NOT NULL DEFAULT '',
            error_summary text NULL,
            retry_payload longtext NULL,
            user_id bigint(20) unsigned NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            published_at datetime NULL DEFAULT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY history_key (history_key),
            KEY publication_uuid (publication_uuid),
            KEY post_id (post_id),
            KEY media_attachment_id (media_attachment_id),
            KEY network (network),
            KEY status (status),
            KEY trigger_type (trigger_type),
            KEY created_at (created_at),
            KEY updated_at (updated_at),
            KEY status_created_at (status, created_at),
            KEY network_created_at (network, created_at),
            KEY network_updated_at (network, updated_at)
        ) {$charset_collate};";

        dbDelta($sql);
        $blog_id = function_exists('get_current_blog_id') ? (int) get_current_blog_id() : 0;
        unset(self::$table_exists_cache[$blog_id]);

        // Do not mark the migration complete unless WordPress can actually see the table.
        if (self::table_exists()) {
            update_option(self::DB_VERSION_OPTION, self::DB_VERSION, false);
        } else {
            delete_option(self::DB_VERSION_OPTION);
        }
    }

    /**
     * Generate a publication UUID shared by all destination rows in one run.
     *
     * @return string
     */
    public static function new_publication_uuid(): string {
        $uuid = wp_generate_uuid4();
        if (is_string($uuid) && preg_match('/^[a-f0-9-]{36}$/Di', $uuid)) {
            return strtolower($uuid);
        }

        return substr(hash('sha256', microtime(true) . '|' . wp_rand() . '|' . wp_salt('nonce')), 0, 8)
            . '-0000-4000-8000-'
            . substr(hash('sha256', wp_rand() . '|' . microtime(true)), 0, 12);
    }

    /**
     * Normalize source information used for history rows.
     *
     * @param array<string,mixed> $context History context.
     * @param array<string,mixed> $payload Publication payload.
     * @return array<string,mixed>
     */
    public static function normalize_context(array $context, array $payload = []): array {
        $source = sanitize_key((string) ($context['source'] ?? 'manual'));
        if ($source === '') {
            $source = 'manual';
        }

        $trigger_type = sanitize_key((string) ($context['trigger_type'] ?? ''));
        if (!in_array($trigger_type, ['manual', 'automatic'], true)) {
            $trigger_type = in_array($source, ['manual', 'publisher'], true) ? 'manual' : 'automatic';
        }

        $post_id = absint($context['post_id'] ?? 0);
        if ($post_id <= 0) {
            $link = isset($payload['link']) ? esc_url_raw((string) $payload['link']) : '';
            if ($link !== '' && function_exists('url_to_postid')) {
                $post_id = absint(url_to_postid($link));
            }
        }

        $post_title = '';
        $post_url = '';
        if ($post_id > 0) {
            $post = get_post($post_id);
            if ($post instanceof WP_Post) {
                $post_title = sanitize_text_field(wp_strip_all_tags(get_the_title($post)));
                $permalink = get_permalink($post);
                $post_url = is_string($permalink) ? esc_url_raw($permalink) : '';
            } else {
                $post_id = 0;
            }
        }

        $publication_uuid = sanitize_text_field((string) ($context['publication_uuid'] ?? ''));
        if (!preg_match('/^[a-f0-9-]{36}$/Di', $publication_uuid)) {
            $publication_uuid = self::new_publication_uuid();
        }

        $normalized = [
            'publication_uuid' => strtolower($publication_uuid),
            'job_id'           => sanitize_key((string) ($context['job_id'] ?? '')),
            'schedule_uuid'     => sanitize_text_field((string) ($context['schedule_uuid'] ?? '')),
            'post_id'           => $post_id,
            'post_title'        => self::truncate($post_title, 255),
            'post_url'          => $post_url,
            'media_attachment_id' => absint($context['media_attachment_id'] ?? ($payload['single_image_attachment_id'] ?? 0)),
            'trigger_type'      => $trigger_type,
            'source'            => $source,
            'ai_used'           => !empty($context['ai_used']) ? 1 : 0,
            'content_type'      => self::detect_content_type($payload),
            'user_id'           => absint($context['user_id'] ?? get_current_user_id()),
            'retry_count'       => absint($context['retry_count'] ?? 0),
        ];

        /**
         * Filter history context before it is persisted.
         * Pro and third-party extensions may add authoritative source metadata here.
         * Secrets must never be returned by this filter.
         *
         * @param array<string,mixed> $normalized Normalized context.
         * @param array<string,mixed> $context    Raw context.
         * @param array<string,mixed> $payload    Publication payload.
         */
        $filtered = apply_filters('calliope_sp_history_context', $normalized, $context, $payload);
        return is_array($filtered) ? array_merge($normalized, self::sanitize_context_override($filtered)) : $normalized;
    }

    /**
     * Create pending destination rows for a queued or direct publication.
     *
     * @param string              $publication_uuid Shared publication UUID.
     * @param string              $job_id           Queue job ID, when available.
     * @param string[]            $networks         Destination slugs.
     * @param array<string,mixed> $payload          Publication payload.
     * @param array<string,mixed> $context          History context.
     * @return array<string,int> Map of network slug to history row ID.
     */
    public static function create_pending_rows($publication_uuid, $job_id, array $networks, array $payload, array $context = []): array {
        $context['publication_uuid'] = $publication_uuid;
        $context['job_id'] = $job_id;
        $context = self::normalize_context($context, $payload);
        $rows = [];

        foreach ($networks as $network) {
            $network = sanitize_key((string) $network);
            if ($network === '') {
                continue;
            }

            $history_key = self::build_history_key($context['publication_uuid'], (string) $context['job_id'], $network);
            $account = self::resolve_account($network, $payload, []);
            $row_id = self::upsert_pending_row($history_key, $network, $payload, $context, $account);
            if ($row_id > 0) {
                $rows[$network] = $row_id;
            }
        }

        return $rows;
    }

    /**
     * Ensure a pending row exists for one direct destination.
     *
     * @param string              $network Network slug.
     * @param array<string,mixed> $payload Publication payload.
     * @param array<string,mixed> $context History context.
     * @return int History row ID, or 0 if history storage is unavailable.
     */
    public static function ensure_pending_row($network, array $payload, array $context = []): int {
        $network = sanitize_key((string) $network);
        if ($network === '') {
            return 0;
        }

        // A concrete Pro destination can share a network with another account/profile.
        // Keep the DB network real for History/Analytics, while allowing the queue to
        // provide a stable per-destination identity for the unique history key.
        $history_destination_key = isset($context['history_destination_key']) && is_scalar($context['history_destination_key'])
            ? sanitize_key((string) $context['history_destination_key'])
            : '';

        $context = self::normalize_context($context, $payload);
        $history_key = isset($context['history_key']) && is_scalar($context['history_key'])
            ? sanitize_text_field((string) $context['history_key'])
            : self::build_history_key(
                $context['publication_uuid'],
                (string) $context['job_id'],
                $history_destination_key !== '' ? $history_destination_key : $network
            );
        $account = self::resolve_account($network, $payload, []);

        return self::upsert_pending_row($history_key, $network, $payload, $context, $account);
    }

    /**
     * Update a history row from a normalized network result.
     *
     * @param int                 $history_id History row ID.
     * @param string              $network    Network slug.
     * @param array<string,mixed> $payload    Publication payload.
     * @param array<string,mixed> $result     Normalized provider result.
     * @param array<string,mixed> $context    History context.
     * @return void
     */
    public static function record_result($history_id, $network, array $payload, array $result, array $context = []): void {
        global $wpdb;

        $history_id = absint($history_id);
        $network = sanitize_key((string) $network);
        if ($history_id <= 0 || $network === '') {
            return;
        }

        $is_pending = !empty($result['pending']);
        $is_success = !$is_pending && !empty($result['ok']);
        $status = $is_pending ? 'pending' : ($is_success ? 'success' : 'failed');
        $outcome = self::detect_outcome($result, $status);
        $account = self::resolve_account($network, $payload, $result);
        $provider_post_id = self::extract_provider_post_id($result);
        $provider_post_url = self::extract_provider_post_url($result);
        $error_code = $status === 'failed'
            ? sanitize_key((string) ($result['error_code'] ?? $result['code'] ?? 'publish_failed'))
            : '';
        $error_summary = $status === 'failed'
            ? self::extract_error_summary($result)
            : '';
        $retry_count = absint($context['retry_count'] ?? ($result['retry_count'] ?? 0));
        $keep_retry_payload = self::is_retry_enabled() && ($status === 'pending' || ($status === 'failed' && $outcome === 'failed'));
        $retry_payload = $keep_retry_payload ? self::encode_retry_payload($payload) : '';
        $now = current_time('mysql', true);

        $data = [
            'account_id'        => self::truncate((string) ($account['id'] ?? ''), 191),
            'account_label'     => self::truncate((string) ($account['label'] ?? ''), 191),
            'content_type'      => self::detect_content_type($payload, $result),
            'status'            => $status,
            'outcome'           => $outcome,
            'provider_post_id'  => self::truncate($provider_post_id, 255),
            'provider_post_url' => $provider_post_url,
            'retry_count'       => $retry_count,
            'error_code'        => self::truncate($error_code, 100),
            'error_summary'     => $error_summary,
            'retry_payload'     => $retry_payload,
            'updated_at'        => $now,
        ];

        if ($is_success) {
            $data['published_at'] = $now;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Persistent history is plugin-owned operational data.
        $wpdb->update(
            self::table_name(),
            $data,
            ['id' => $history_id],
            self::formats_for_update($data),
            ['%d']
        );

        /**
         * Fires after a destination history row is updated.
         * Pro can use this event to schedule provider insight collection later.
         *
         * @param int                 $history_id History row ID.
         * @param string              $status     pending|success|failed.
         * @param string              $network    Network slug.
         * @param array<string,mixed> $result     Normalized provider result.
         */
        do_action('calliope_sp_history_recorded', $history_id, $status, $network, $result);

        /**
         * Fires after a concrete destination result is persisted, with the
         * publication context and sanitized runtime payload still available.
         * Extensions may correlate Scheduler/automation metadata without Free
         * depending on any Pro implementation.
         *
         * @param int                 $history_id History row ID.
         * @param string              $status     pending|success|failed.
         * @param string              $network    Real network slug.
         * @param array<string,mixed> $result     Normalized provider result.
         * @param array<string,mixed> $context    Publication/queue context.
         * @param array<string,mixed> $payload    Destination payload snapshot.
         */
        do_action('calliope_sp_publication_destination_result', $history_id, $status, $network, $result, $context, $payload);
    }

    /**
     * Return one failed History row plus its sanitized retry snapshot.
     * The snapshot is never returned by the normal History listing query.
     *
     * @param int $history_id History row ID.
     * @return array<string,mixed>|null
     */
    public static function get_retry_record($history_id): ?array {
        global $wpdb;

        $history_id = absint($history_id);
        if ($history_id <= 0 || !self::table_exists()) {
            return null;
        }

        $table = self::table_name();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Exact lookup in plugin-owned operational history.
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, publication_uuid, schedule_uuid, post_id, network, trigger_type, source, ai_used, status, outcome, provider_post_id, provider_post_url, retry_count, user_id, retry_payload FROM {$table} WHERE id = %d LIMIT 1",
                $history_id
            ),
            ARRAY_A
        );

        if (!is_array($row)) {
            return null;
        }

        $row['id'] = absint($row['id'] ?? 0);
        $row['post_id'] = absint($row['post_id'] ?? 0);
        $row['retry_count'] = absint($row['retry_count'] ?? 0);
        $row['user_id'] = absint($row['user_id'] ?? 0);
        $row['ai_used'] = !empty($row['ai_used']) ? 1 : 0;
        $row['network'] = sanitize_key((string) ($row['network'] ?? ''));
        $row['status'] = sanitize_key((string) ($row['status'] ?? ''));
        $row['outcome'] = sanitize_key((string) ($row['outcome'] ?? ''));
        $row['trigger_type'] = sanitize_key((string) ($row['trigger_type'] ?? 'manual'));
        $row['source'] = sanitize_key((string) ($row['source'] ?? 'manual'));
        $row['publication_uuid'] = self::valid_uuid_or_empty($row['publication_uuid'] ?? '');
        $row['provider_post_id'] = sanitize_text_field((string) ($row['provider_post_id'] ?? ''));
        $row['provider_post_url'] = esc_url_raw((string) ($row['provider_post_url'] ?? ''));

        $raw_payload = isset($row['retry_payload']) && is_string($row['retry_payload']) ? $row['retry_payload'] : '';
        $payload = $raw_payload !== '' ? json_decode($raw_payload, true) : null;
        $row['retry_payload'] = is_array($payload) ? $payload : [];

        /**
         * Allow a compatible add-on to restore a retry-safe payload from an
         * immutable external snapshot (for example Pro Scheduler / Evergreen)
         * when the local History retry snapshot is unavailable.
         *
         * @param array<string,mixed> $row
         * @param int                 $history_id
         */
        $filtered = apply_filters('calliope_sp_history_retry_record', $row, $history_id);
        if (is_array($filtered)) {
            $row = $filtered;
        }

        return $row;
    }

    /**
     * Move one failed row back to pending after a retry job has been safely stored.
     *
     * @param int    $history_id History row ID.
     * @param string $job_id     New retry queue job ID.
     * @param int    $retry_count New retry count.
     * @return bool
     */
    public static function mark_retry_queued($history_id, $job_id, $retry_count): bool {
        global $wpdb;

        $history_id = absint($history_id);
        $job_id = sanitize_key((string) $job_id);
        $retry_count = absint($retry_count);
        if ($history_id <= 0 || $job_id === '') {
            return false;
        }

        $data = [
            'job_id'            => self::truncate($job_id, 64),
            'status'            => 'pending',
            'outcome'           => 'pending',
            'retry_count'       => $retry_count,
            'error_code'        => '',
            'error_summary'     => '',
            'provider_post_id'  => '',
            'provider_post_url' => '',
            'updated_at'        => current_time('mysql', true),
        ];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Update of one plugin-owned History row.
        return $wpdb->update(
            self::table_name(),
            $data,
            ['id' => $history_id],
            self::formats_for_update($data),
            ['%d']
        ) !== false;
    }

    /**
     * Return whether manual History Retry is enabled by the site administrator.
     * Enabled by default for backward compatibility with the Step 3 release.
     */
    public static function is_retry_enabled(): bool {
        $raw = get_option('calliope_sp_history_retry_enabled', '1');
        $enabled = !in_array($raw, [0, '0', false, 'false', 'off', 'no'], true);
        return (bool) apply_filters('calliope_sp_history_retry_enabled', $enabled);
    }

    /**
     * Remove stored retry snapshots while preserving History rows and retry counters.
     * Used when the administrator disables manual Retry in Settings.
     */
    public static function clear_retry_payloads(): void {
        global $wpdb;
        self::maybe_upgrade();
        $table = self::table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bulk cleanup of plugin-owned retry snapshots after an explicit admin setting change.
        $wpdb->query("UPDATE {$table} SET retry_payload = NULL WHERE retry_payload IS NOT NULL AND retry_payload <> ''");
    }

    /**
     * Return whether a History row is safe to expose as a manual retry action.
     *
     * @param array<string,mixed> $row History list or retry row.
     */
    public static function can_retry(array $row): bool {
        $local_available = !empty($row['retry_available'] ?? $row['retry_payload'] ?? []);
        /**
         * Pro may confirm that an immutable Scheduler snapshot can safely
         * reconstruct the original publication when History's local snapshot
         * is missing. This never bypasses the administrator Retry preference.
         */
        $external_available = (bool) apply_filters('calliope_sp_history_retry_available', false, $row);

        return self::is_retry_enabled()
            && sanitize_key((string) ($row['status'] ?? '')) === 'failed'
            && sanitize_key((string) ($row['outcome'] ?? '')) === 'failed'
            && trim((string) ($row['provider_post_id'] ?? '')) === ''
            && trim((string) ($row['provider_post_url'] ?? '')) === ''
            && ($local_available || $external_available);
    }

    /**
     * Explicitly increment the retry counter for compatibility with external retry integrations.
     *
     * @param int $history_id History row ID.
     * @return void
     */
    public static function increment_retry($history_id): void {
        global $wpdb;
        $history_id = absint($history_id);
        if ($history_id <= 0) {
            return;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Atomic counter update on plugin-owned history table.
        $wpdb->query(
            $wpdb->prepare(
                'UPDATE ' . self::table_name() . ' SET retry_count = retry_count + 1, updated_at = %s WHERE id = %d',
                current_time('mysql', true),
                $history_id
            )
        );
    }

    /** @return int */
    private static function upsert_pending_row($history_key, $network, array $payload, array $context, array $account): int {
        global $wpdb;

        $table = self::table_name();
        $history_key = self::truncate(sanitize_text_field((string) $history_key), 191);
        if ($history_key === '') {
            return 0;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Exact key lookup in plugin-owned history table.
        $existing_id = absint($wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE history_key = %s LIMIT 1", $history_key)));
        if ($existing_id > 0) {
            return $existing_id;
        }

        $now = current_time('mysql', true);
        $data = [
            'publication_uuid' => strtolower((string) $context['publication_uuid']),
            'history_key'      => $history_key,
            'job_id'           => self::truncate((string) ($context['job_id'] ?? ''), 64),
            'schedule_uuid'     => self::valid_uuid_or_empty($context['schedule_uuid'] ?? ''),
            'post_id'           => absint($context['post_id'] ?? 0),
            'post_title'        => self::truncate((string) ($context['post_title'] ?? ''), 255),
            'post_url'          => esc_url_raw((string) ($context['post_url'] ?? '')),
            'media_attachment_id' => absint($context['media_attachment_id'] ?? 0),
            'network'           => sanitize_key((string) $network),
            'account_id'        => self::truncate((string) ($account['id'] ?? ''), 191),
            'account_label'     => self::truncate((string) ($account['label'] ?? ''), 191),
            'trigger_type'      => in_array(($context['trigger_type'] ?? ''), ['manual', 'automatic'], true) ? (string) $context['trigger_type'] : 'manual',
            'source'            => sanitize_key((string) ($context['source'] ?? 'manual')),
            'ai_used'           => !empty($context['ai_used']) ? 1 : 0,
            'content_type'      => self::detect_content_type($payload),
            'status'            => 'pending',
            'outcome'           => 'pending',
            'provider_post_id'  => '',
            'provider_post_url' => '',
            'retry_count'       => absint($context['retry_count'] ?? 0),
            'error_code'        => '',
            'error_summary'     => '',
            'retry_payload'     => self::is_retry_enabled() ? self::encode_retry_payload($payload) : '',
            'user_id'           => absint($context['user_id'] ?? 0),
            'created_at'        => $now,
            'updated_at'        => $now,
            'published_at'      => null,
        ];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Insert into plugin-owned persistent history table.
        $inserted = $wpdb->insert($table, $data, self::formats_for_insert($data));
        if ($inserted === false) {
            // A concurrent request may have inserted the same history key first.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Exact fallback lookup after unique-key collision.
            return absint($wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE history_key = %s LIMIT 1", $history_key)));
        }

        return absint($wpdb->insert_id);
    }

    /** @return array{id:string,label:string} */
    private static function resolve_account($network, array $payload, array $result): array {
        $network = sanitize_key((string) $network);
        $account = ['id' => '', 'label' => ''];

        foreach (['account_id', 'page_id', 'profile_id', 'user_id', 'blog_id', 'chat_id', 'board_id', 'organization_id', 'location_id'] as $key) {
            if (isset($result[$key]) && is_scalar($result[$key]) && trim((string) $result[$key]) !== '') {
                $account['id'] = sanitize_text_field((string) $result[$key]);
                break;
            }
        }
        foreach (['account_label', 'account_name', 'page_name', 'profile_name', 'username', 'blog_name', 'chat_title', 'board_name', 'organization_name', 'location_name'] as $key) {
            if (isset($result[$key]) && is_scalar($result[$key]) && trim((string) $result[$key]) !== '') {
                $account['label'] = sanitize_text_field((string) $result[$key]);
                break;
            }
        }

        $fallback = self::resolve_account_from_saved_state($network);
        if ($account['id'] === '') {
            $account['id'] = $fallback['id'];
        }
        if ($account['label'] === '') {
            $account['label'] = $fallback['label'];
        }

        /**
         * Filter the public account identity stored in history.
         * Never return access tokens, secrets, API keys, or authorization headers.
         *
         * @param array{id:string,label:string} $account Account identity.
         * @param string                       $network Network slug.
         * @param array<string,mixed>          $payload Publication payload.
         * @param array<string,mixed>          $result Normalized provider result.
         */
        $filtered = apply_filters('calliope_sp_history_account', $account, $network, $payload, $result);
        if (is_array($filtered)) {
            $account['id'] = isset($filtered['id']) && is_scalar($filtered['id']) ? sanitize_text_field((string) $filtered['id']) : $account['id'];
            $account['label'] = isset($filtered['label']) && is_scalar($filtered['label']) ? sanitize_text_field((string) $filtered['label']) : $account['label'];
        }

        return [
            'id' => self::truncate((string) $account['id'], 191),
            'label' => self::truncate((string) $account['label'], 191),
        ];
    }

    /** @return array{id:string,label:string} */
    private static function resolve_account_from_saved_state($network): array {
        $id = '';
        $label = '';

        switch ($network) {
            case 'bsky':
            case 'bluesky':
                $id = sanitize_text_field((string) get_option('calliope_sp_bsky_handle', ''));
                $label = $id;
                break;

            case 'mastodon':
                $id = esc_url_raw((string) get_option('calliope_sp_mstdn_instance', ''));
                $label = $id;
                break;

            case 'tumblr':
                $id = sanitize_text_field((string) get_option('calliope_sp_tumblr_blog_identifier', ''));
                $label = $id;
                break;

            case 'pinterest':
                $id = sanitize_text_field((string) get_option('calliope_sp_pinterest_board_id', ''));
                $label = self::lookup_cached_label(get_option('calliope_sp_pinterest_boards_last', []), $id);
                if ($label === '') {
                    $label = $id;
                }
                break;

            case 'threads':
                $id = sanitize_text_field((string) get_option('calliope_sp_threads_user_id', ''));
                $validation = get_option('calliope_sp_threads_validation', []);
                if (is_array($validation)) {
                    foreach (['username', 'name'] as $key) {
                        if (!empty($validation[$key]) && is_scalar($validation[$key])) {
                            $label = sanitize_text_field((string) $validation[$key]);
                            break;
                        }
                    }
                }
                if ($label === '') {
                    $label = $id;
                }
                break;

            case 'instagram':
                $id = sanitize_text_field((string) get_option('calliope_sp_ig_user_id', ''));
                $label = sanitize_text_field((string) get_option('calliope_sp_ig_username', ''));
                if ($label === '') {
                    $label = $id;
                }
                break;

            case 'linkedin':
                $id = sanitize_text_field((string) get_option('calliope_sp_li_person_id', ''));
                $label = $id;
                break;

            case 'blogger':
                $id = sanitize_text_field((string) get_option('calliope_sp_blogger_blog_id', ''));
                $label = self::lookup_cached_label(get_option('calliope_sp_blogger_blogs_last', []), $id);
                if ($label === '') {
                    $label = $id;
                }
                break;

            case 'telegram':
                $id = sanitize_text_field((string) get_option('calliope_sp_telegram_chat_id', ''));
                $label = $id;
                break;

            case 'facebook':
                $id = sanitize_text_field((string) get_option('calliope_sp_fb_page_id', ''));
                $label = self::lookup_cached_label(get_option('calliope_sp_fb_pages_last', []), $id);
                if ($label === '') {
                    $label = $id;
                }
                break;
        }

        return ['id' => self::truncate($id, 191), 'label' => self::truncate($label, 191)];
    }

    /** @return string */
    private static function lookup_cached_label($cache, $wanted_id): string {
        $wanted_id = trim((string) $wanted_id);
        if ($wanted_id === '' || !is_array($cache)) {
            return '';
        }

        foreach ($cache as $key => $item) {
            if (is_array($item)) {
                $candidate_id = '';
                foreach (['id', 'page_id', 'blog_id', 'board_id'] as $id_key) {
                    if (isset($item[$id_key]) && is_scalar($item[$id_key])) {
                        $candidate_id = trim((string) $item[$id_key]);
                        break;
                    }
                }
                if ($candidate_id === '' && is_scalar($key)) {
                    $candidate_id = trim((string) $key);
                }
                if (!hash_equals($wanted_id, $candidate_id)) {
                    continue;
                }
                foreach (['name', 'title', 'label', 'username'] as $label_key) {
                    if (!empty($item[$label_key]) && is_scalar($item[$label_key])) {
                        return sanitize_text_field((string) $item[$label_key]);
                    }
                }
            } elseif (is_scalar($key) && hash_equals($wanted_id, trim((string) $key)) && is_scalar($item)) {
                return sanitize_text_field((string) $item);
            }
        }

        return '';
    }

    /** @return string */
    private static function detect_content_type(array $payload, array $result = []): string {
        $result_type = sanitize_key((string) ($result['media_type'] ?? ''));
        if (in_array($result_type, ['video'], true)) {
            return 'video';
        }
        if (in_array($result_type, ['gallery', 'carousel', 'photoset', 'album', 'multiple_images', 'multi_image', 'media_group', 'multi_pin'], true)) {
            return 'carousel';
        }
        if (in_array($result_type, ['image', 'photo'], true)) {
            return 'image';
        }

        $mode = sanitize_key((string) ($payload['media_mode'] ?? ''));
        if ($mode === 'video') {
            return 'video';
        }
        if ($mode === 'multiple_images') {
            return 'carousel';
        }

        $media = isset($payload['media']) && is_array($payload['media']) ? $payload['media'] : [];
        $image_count = 0;
        $video_count = 0;
        foreach ($media as $item) {
            if (!is_array($item)) {
                continue;
            }
            $type = sanitize_key((string) ($item['type'] ?? ''));
            if ($type === 'video') {
                $video_count++;
            } elseif ($type === 'image') {
                $image_count++;
            }
        }
        if ($video_count > 0) {
            return 'video';
        }
        if ($image_count > 1) {
            return 'carousel';
        }
        if ($image_count === 1 || !empty($payload['image_b64']) || $mode === 'single_image') {
            return 'image';
        }

        return 'text';
    }

    /**
     * Encode a retry-safe publication snapshot without provider context or credentials.
     * Credentials are always resolved from current connection settings at retry time.
     */
    private static function encode_retry_payload(array $payload): string {
        $clean = self::sanitize_retry_value($payload, '');
        if (!is_array($clean)) {
            return '';
        }

        if (isset($clean['metadata']) && is_array($clean['metadata'])) {
            unset($clean['metadata']['provider_context']);
        }

        $json = wp_json_encode($clean, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        return is_string($json) ? $json : '';
    }

    /** @return mixed */
    private static function sanitize_retry_value($value, $key = '') {
        $key = strtolower((string) $key);
        if ($key !== '' && preg_match('/(?:access[_-]?token|refresh[_-]?token|token|secret|password|authorization|api[_-]?key|apikey|credential|cookie)/i', $key)) {
            return null;
        }

        if (is_bool($value) || is_int($value) || is_float($value)) {
            return $value;
        }
        if (is_string($value)) {
            return $value;
        }
        if (!is_array($value)) {
            return null;
        }

        $clean = [];
        foreach ($value as $child_key => $child_value) {
            $safe_key = is_int($child_key) ? $child_key : sanitize_key((string) $child_key);
            if (!is_int($child_key) && $safe_key === '') {
                continue;
            }
            $safe_value = self::sanitize_retry_value($child_value, (string) $safe_key);
            if ($safe_value === null) {
                continue;
            }
            $clean[$safe_key] = $safe_value;
        }
        return $clean;
    }

    /** @return string */
    private static function detect_outcome(array $result, $status): string {
        if ($status === 'pending') {
            return 'pending';
        }
        if (!empty($result['skipped']) || sanitize_key((string) ($result['error_code'] ?? '')) === 'duplicate_skipped') {
            return 'skipped';
        }
        if (!empty($result['needs_review']) || sanitize_key((string) ($result['stage'] ?? '')) === 'needs_review') {
            return 'needs_review';
        }
        if ($status === 'success') {
            if (!empty($result['warning']) || sanitize_key((string) ($result['ui_status'] ?? '')) === 'warning' || sanitize_key((string) ($result['warning_code'] ?? '')) !== '') {
                return 'warning';
            }
            return 'published';
        }
        return 'failed';
    }

    /** @return string */
    private static function extract_provider_post_id(array $result): string {
        foreach (['provider_post_id', 'post_id', 'post_urn', 'post_uri', 'uri', 'id', 'media_id', 'creation_id', 'video_id', 'message_id', 'post_cid'] as $key) {
            if (isset($result[$key]) && is_scalar($result[$key]) && trim((string) $result[$key]) !== '') {
                return sanitize_text_field((string) $result[$key]);
            }
        }
        return '';
    }

    /** @return string */
    private static function extract_provider_post_url(array $result): string {
        foreach (['provider_post_url', 'post_url', 'permalink', 'url'] as $key) {
            if (isset($result[$key]) && is_scalar($result[$key])) {
                $url = esc_url_raw((string) $result[$key]);
                if ($url !== '') {
                    return $url;
                }
            }
        }
        return '';
    }

    /** @return string */
    private static function extract_error_summary(array $result): string {
        foreach (['user_message', 'error', 'message', 'short_message'] as $key) {
            if (isset($result[$key]) && is_scalar($result[$key])) {
                $message = trim(wp_strip_all_tags((string) $result[$key]));
                if ($message !== '') {
                    return self::truncate($message, 500);
                }
            }
        }
        return __('Unknown publication error.', 'calliope-social-publisher');
    }

    /** @return string */
    private static function build_history_key($publication_uuid, $job_id, $network): string {
        $publication_uuid = strtolower(sanitize_text_field((string) $publication_uuid));
        $job_id = sanitize_key((string) $job_id);
        $network = sanitize_key((string) $network);
        $seed = ($job_id !== '' ? 'job:' . $job_id : 'publication:' . $publication_uuid) . ':' . $network;
        return self::truncate($seed, 191);
    }

    /** @return array<string,mixed> */
    private static function sanitize_context_override(array $filtered): array {
        $clean = [];
        if (isset($filtered['publication_uuid']) && preg_match('/^[a-f0-9-]{36}$/Di', (string) $filtered['publication_uuid'])) {
            $clean['publication_uuid'] = strtolower((string) $filtered['publication_uuid']);
        }
        foreach (['job_id', 'source'] as $key) {
            if (isset($filtered[$key]) && is_scalar($filtered[$key])) {
                $clean[$key] = sanitize_key((string) $filtered[$key]);
            }
        }
        if (isset($filtered['schedule_uuid'])) {
            $clean['schedule_uuid'] = self::valid_uuid_or_empty($filtered['schedule_uuid']);
        }
        if (isset($filtered['post_id'])) {
            $clean['post_id'] = absint($filtered['post_id']);
        }
        if (isset($filtered['post_title']) && is_scalar($filtered['post_title'])) {
            $clean['post_title'] = self::truncate(sanitize_text_field((string) $filtered['post_title']), 255);
        }
        if (isset($filtered['post_url']) && is_scalar($filtered['post_url'])) {
            $clean['post_url'] = esc_url_raw((string) $filtered['post_url']);
        }
        if (isset($filtered['trigger_type']) && in_array($filtered['trigger_type'], ['manual', 'automatic'], true)) {
            $clean['trigger_type'] = $filtered['trigger_type'];
        }
        if (array_key_exists('ai_used', $filtered)) {
            $clean['ai_used'] = !empty($filtered['ai_used']) ? 1 : 0;
        }
        if (isset($filtered['content_type']) && in_array($filtered['content_type'], ['text', 'image', 'video', 'carousel'], true)) {
            $clean['content_type'] = $filtered['content_type'];
        }
        if (isset($filtered['user_id'])) {
            $clean['user_id'] = absint($filtered['user_id']);
        }
        if (isset($filtered['retry_count'])) {
            $clean['retry_count'] = absint($filtered['retry_count']);
        }
        return $clean;
    }

    /** @return string */
    private static function valid_uuid_or_empty($value): string {
        $value = sanitize_text_field((string) $value);
        return preg_match('/^[a-f0-9-]{36}$/Di', $value) ? strtolower($value) : '';
    }

    /** @return string */
    private static function truncate($value, $length): string {
        $value = (string) $value;
        $length = max(1, (int) $length);
        if (function_exists('mb_substr')) {
            return mb_substr($value, 0, $length, 'UTF-8');
        }
        return substr($value, 0, $length);
    }

    /** @return string[] */
    private static function formats_for_insert(array $data): array {
        $integer_keys = ['post_id', 'media_attachment_id', 'ai_used', 'retry_count', 'user_id'];
        $formats = [];
        foreach ($data as $key => $value) {
            $formats[] = in_array($key, $integer_keys, true) ? '%d' : '%s';
        }
        return $formats;
    }

    /** @return string[] */
    private static function formats_for_update(array $data): array {
        $integer_keys = ['retry_count'];
        $formats = [];
        foreach ($data as $key => $value) {
            $formats[] = in_array($key, $integer_keys, true) ? '%d' : '%s';
        }
        return $formats;
    }
}
