<?php
/**
 * Costudio_Production — CRUD helper for wp_costudio_productions.
 *
 * The table name is kept as 'costudio_productions' (not renamed) to maintain
 * backward compatibility with existing data from the legacy plugin.
 *
 * @package Contentosapp_Studio
 * @since   3.0.0
 */

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- CRUD on plugin-owned costudio_productions table; $table = $wpdb->prefix . constant, all user values pass through $wpdb->prepare placeholders.

class Costudio_Production {

    /** Database table suffix (without $wpdb->prefix). */
    const TABLE = 'costudio_productions';
    const TRACKING_UID_INPUT = 'costudio_production_uid';
    const TELEMETRY_STARTED_INPUT = 'costudio_telemetry_started_at';

    public int $id;
    public string $status;
    public string $site_key;
    public string $keyword;
    public string $title;
    public ?int $plan_id;
    public ?int $current_agent;
    public ?string $current_agent_started_at;
    public int $attempts = 0;
    public ?string $heartbeat;
    public ?string $updated_at;
    public ?string $inputs;
    public ?string $output_01;
    public ?string $output_02;
    public ?string $output_03;
    public ?string $output_04;
    public ?string $output_05;
    public ?string $output_06;
    public ?string $output_07;
    public ?string $sources_json;
    public ?string $images_json;
    public ?string $links_internos;
    public ?int $wp_post_id;
    public ?string $wp_post_url;
    public ?string $edit_url;
    public ?int $error_agent;
    public ?string $error_message;
    public ?string $started_at;
    public ?string $created_at;
    public ?string $completed_at;

    public function __construct( object $data ) {
        $this->id             = (int) $data->id;
        $this->status         = $data->status;
        $this->site_key       = $data->site_key;
        $this->keyword        = $data->keyword;
        $this->title          = $data->title ?? '';
        $this->plan_id        = ! empty( $data->plan_id ) ? (int) $data->plan_id : null;
        $this->current_agent  = ! empty( $data->current_agent ) ? (int) $data->current_agent : null;
        $this->current_agent_started_at = $data->current_agent_started_at ?? null;
        $this->attempts       = (int) ( $data->attempts ?? 0 );
        $this->heartbeat      = $data->heartbeat ?? null;
        $this->updated_at     = $data->updated_at ?? null;
        $this->inputs         = $data->inputs ?? null;
        $this->output_01      = $data->output_01 ?? null;
        $this->output_02      = $data->output_02 ?? null;
        $this->output_03      = $data->output_03 ?? null;
        $this->output_04      = $data->output_04 ?? null;
        $this->output_05      = $data->output_05 ?? null;
        $this->output_06      = $data->output_06 ?? null;
        $this->output_07      = $data->output_07 ?? null;
        $this->sources_json   = $data->sources_json ?? null;
        $this->images_json    = $data->images_json ?? null;
        $this->links_internos = $data->links_internos ?? null;
        $this->wp_post_id     = ! empty( $data->wp_post_id ) ? (int) $data->wp_post_id : null;
        $this->wp_post_url    = $data->wp_post_url ?? null;
        $this->edit_url       = $data->edit_url ?? null;
        $this->error_agent    = ! empty( $data->error_agent ) ? (int) $data->error_agent : null;
        $this->error_message  = $data->error_message ?? null;
        $this->started_at     = $data->started_at ?? null;
        $this->created_at     = $data->created_at ?? null;
        $this->completed_at   = $data->completed_at ?? null;
    }

    public static function get_by_id( int $id ): ?self {
        $raw = self::find( $id );
        return $raw ? new self( $raw ) : null;
    }

    public function save(): void {
        global $wpdb;
        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [
                'status'        => $this->status,
                'current_agent' => $this->current_agent,
                'attempts'      => $this->attempts,
                'heartbeat'     => $this->heartbeat,
                'updated_at'    => current_time( 'mysql' ),
            ],
            [ 'id' => $this->id ]
        );
    }

    /**
     * Persist the production inputs JSON explicitly.
     *
     * save() intentionally only writes runner state fields. Pipeline metadata
     * such as Brand Voice snapshots and reviewer feedback must use this method
     * so long-running productions keep the exact context they started with.
     *
     * @return bool True when the write was accepted by the database. Callers
     *              that depend on the write actually landing before doing
     *              further work (e.g. Costudio_Video_Discovery's lock/state
     *              machine) must check this — $wpdb->update() returns false
     *              on a DB error, and that must never be treated as success.
     */
    public static function update_inputs( int $id, array $inputs ): bool {
        global $wpdb;

        $updated = $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [
                'inputs'     => wp_json_encode( $inputs ),
                'updated_at' => current_time( 'mysql' ),
            ],
            [ 'id' => $id ]
        );

        return false !== $updated;
    }

    public function set_inputs( array $inputs ): void {
        $this->inputs = wp_json_encode( $inputs );
        self::update_inputs( $this->id, $inputs );
    }

    /**
     * Persist a word-count advisory warning on a production.
     *
     * Stored in `error_message` with `[WC_WARNING]` prefix so the admin
     * detail page can render it as a yellow notice (non-fatal) instead of
     * a red error. Status is NOT changed — production continues normally.
     *
     * Note: if the publisher later sets `[IMG_WARNING]` via complete(),
     * it overwrites this entry. Word-count is an early-pipeline signal
     * (computed after Agent 04) so users see it in the live pipeline log
     * regardless.
     */
    public static function set_wc_warning( int $id, string $warning ): void {
        global $wpdb;
        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [
                'error_message' => '[WC_WARNING] ' . $warning,
                'updated_at'    => current_time( 'mysql' ),
            ],
            [ 'id' => $id ]
        );
    }

    public function mark_failed( int $agent, string $message, ?\Throwable $exception = null ): void {
        $category = 'other';

        if ( $exception ) {
            if ( ! class_exists( 'Costudio_Error_Categorizer' ) ) {
                require_once dirname( __FILE__ ) . '/class-costudio-error-categorizer.php';
            }
            $category = Costudio_Error_Categorizer::categorize( $exception );
        }

        self::set_error( $this->id, $message, $agent, $category );
    }

    /**
     * Mark production as pending_upgrade when the managed service refuses a
     * request because a service quota or entitlement limit was reached. Stores
     * structured service context (code, message, upgrade_url) as JSON in
     * error_message so the detail view can render an actionable service card.
     */
    public static function mark_upgrade_required( int $id, string $code, string $message, string $upgrade_url, int $agent = 0 ): void {
        global $wpdb;

        $context = wp_json_encode( [
            'code'        => $code,
            'message'     => $message,
            'upgrade_url' => $upgrade_url ?: 'https://contentosapp.com/#pricing',
        ] );

        $data = [
            'status'        => 'pending_upgrade',
            'error_message' => $context,
            'heartbeat'     => null,
            'updated_at'    => current_time( 'mysql', true ),
        ];
        if ( $agent > 0 ) {
            $data['error_agent'] = $agent;
        }

        $wpdb->update( $wpdb->prefix . self::TABLE, $data, [ 'id' => $id ] );

        if ( class_exists( 'Costudio_Runner' ) ) {
            Costudio_Runner::pipeline_log_append(
                $id,
                sprintf( 'Managed service action required (%s): %s', $code, $message ),
                'info',
                $agent > 0 ? $agent : null
            );
        }

        self::queue_terminal_telemetry(
            $id,
            'production.blocked',
            [
                'status'        => 'pending_upgrade',
                'agent_failed'  => $agent,
                'code'          => $code,
                'error'         => $message,
                'upgrade_url'   => $upgrade_url ?: 'https://contentosapp.com/#pricing',
            ]
        );
    }

    // ── Create ───────────────────────────────────────────────────────────────

    public static function create( array $data ): int {
        global $wpdb;

        $inputs = $data['inputs'] ?? [];
        if ( ! is_array( $inputs ) ) {
            $inputs = json_decode( (string) $inputs, true ) ?: [];
        }
        
        // Force Version 7 for all new productions
        $inputs['pipeline_v'] = 7;
        if ( empty( $inputs[ self::TRACKING_UID_INPUT ] ) ) {
            $inputs[ self::TRACKING_UID_INPUT ] = self::new_tracking_uid();
        } else {
            $inputs[ self::TRACKING_UID_INPUT ] = sanitize_text_field( (string) $inputs[ self::TRACKING_UID_INPUT ] );
        }

        $status = sanitize_text_field( $data['status'] ?? 'pending' );
        $wpdb->insert( $wpdb->prefix . self::TABLE, [
                'status'      => $status,
                'site_key'    => sanitize_text_field( $data['site_key']   ?? '' ),
                'keyword'     => sanitize_text_field( $data['keyword']    ?? '' ),
                'title'       => sanitize_text_field( $data['title']      ?? '' ),
                'is_external' => ! empty( $data['is_external'] ) ? 1 : 0,
                'plan_id'     => ! empty( $data['plan_id'] ) ? (int) $data['plan_id'] : null,
                'wp_post_id'  => ! empty( $data['wp_post_id'] ) ? (int) $data['wp_post_id'] : null,
                'wp_post_url' => ! empty( $data['wp_post_url'] ) ? esc_url_raw( $data['wp_post_url'] ) : null,
                'inputs'      => wp_json_encode( $inputs ),
                'created_at'  => ! empty( $data['created_at'] ) ? $data['created_at'] : current_time( 'mysql', 1 ),
                'started_at'  => ! empty( $data['started_at'] )
                    ? $data['started_at']
                    : ( in_array( $status, array( 'pending', 'running', 'starting' ), true ) ? current_time( 'mysql', true ) : null ),
            ]
        );

        return (int) $wpdb->insert_id;
    }

    public static function tracking_uid_for( int $id ): string {
        if ( $id <= 0 ) {
            return '';
        }

        $production = self::find( $id );
        if ( ! $production ) {
            return '';
        }

        $inputs = json_decode( $production->inputs ?? '{}', true );
        if ( ! is_array( $inputs ) ) {
            $inputs = [];
        }

        $uid = isset( $inputs[ self::TRACKING_UID_INPUT ] )
            ? sanitize_text_field( (string) $inputs[ self::TRACKING_UID_INPUT ] )
            : '';

        if ( '' !== $uid ) {
            return $uid;
        }

        $uid = self::new_tracking_uid();
        $inputs[ self::TRACKING_UID_INPUT ] = $uid;
        self::update_inputs( $id, $inputs );

        return $uid;
    }

    private static function new_tracking_uid(): string {
        if ( function_exists( 'wp_generate_uuid4' ) ) {
            return sanitize_text_field( wp_generate_uuid4() );
        }

        return md5( uniqid( 'costudio-production-', true ) );
    }

    // ── Read ─────────────────────────────────────────────────────────────────

    public static function find( int $id ): ?self {
        global $wpdb;

        $raw = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM ' . $wpdb->prefix . self::TABLE . ' WHERE id = %d',
                $id
            )
        );

        return $raw ? new self( $raw ) : null;
    }

    public static function exists_by_post_id( string $site_key, int $wp_post_id ): bool {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;
        return (bool) $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM {$table} WHERE site_key = %s AND wp_post_id = %d LIMIT 1",
            $site_key,
            $wp_post_id
        ) );
    }

    /** @return object[] */
    public static function all( array $args = [] ): array {
        global $wpdb;

        $table      = $wpdb->prefix . self::TABLE;
        $plan_table = $wpdb->prefix . Costudio_Planning::TABLE;
        $lightweight = ! empty( $args['lightweight'] );
        $wheres     = [ '1=1' ];
        $params     = [];

        if ( ! empty( $args['site_key'] ) ) {
            $wheres[] = 'p.site_key = %s';
            $params[]  = $args['site_key'];
        }
        if ( ! empty( $args['hub_status'] ) ) {
            switch ( $args['hub_status'] ) {
                case 'planned':
                    $wheres[] = "p.status = 'pending'";
                    break;
                case 'running':
                    $wheres[] = "p.status = 'running'";
                    break;
                case 'published':
                    $wheres[] = "p.status = 'completed' AND p.wp_post_id IS NOT NULL AND p.wp_post_id > 0";
                    break;
                case 'draft':
                    $wheres[] = "p.status = 'completed' AND ( p.wp_post_id IS NULL OR p.wp_post_id = 0 )";
                    break;
                case 'error':
                    $wheres[] = "p.status = 'error'";
                    break;
            }
        } elseif ( ! empty( $args['status'] ) ) {
            $wheres[] = 'p.status = %s';
            $params[]  = $args['status'];
        }
        if ( ! empty( $args['search'] ) ) {
            $wheres[] = '(p.keyword LIKE %s OR p.title LIKE %s)';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
        }
        if ( ! empty( $args['priority'] ) && in_array( $args['priority'], [ 'high', 'medium', 'low' ], true ) ) {
            $wheres[] = 'pl.priority = %s';
            $params[] = $args['priority'];
        }
        if ( ! empty( $args['has_completed_at'] ) ) {
            $wheres[] = 'p.completed_at IS NOT NULL';
        }
        if ( ! empty( $args['completed_at_from'] ) && ! empty( $args['completed_at_to'] ) ) {
            $wheres[] = 'p.completed_at BETWEEN %s AND %s';
            $params[] = $args['completed_at_from'];
            $params[] = $args['completed_at_to'];
        }

        $where  = implode( ' AND ', $wheres );
        $limit  = (int) ( $args['limit']  ?? 20 );
        $offset = (int) ( $args['offset'] ?? 0 );
        
        $allowed_orderby = [
            'id'            => 'p.id',
            'title'         => 'p.title',
            'keyword'       => 'p.keyword',
            'site_key'      => 'p.site_key',
            'created_at'    => 'p.created_at',
            'updated_at'    => 'p.updated_at',
            'completed_at'  => 'p.completed_at',
            'status'        => 'p.status',
            'plan_priority' => 'pl.priority',
        ];
        
        $orderby_key = $args['orderby'] ?? 'created_at';
        if ( ! array_key_exists( $orderby_key, $allowed_orderby ) ) {
            $orderby_key = 'created_at';
        }
        $orderby = $allowed_orderby[ $orderby_key ];
        
        $order = strtoupper( $args['order'] ?? 'DESC' );
        if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
            $order = 'DESC';
        }
        
        $params = array_merge( $params, [ $limit, $offset ] );

        $select = $lightweight
            ? "p.id, p.status, p.site_key, p.keyword, p.title, p.plan_id, p.current_agent, p.wp_post_id, p.wp_post_url, p.edit_url, p.error_agent, p.error_message, p.created_at, p.updated_at, p.completed_at, p.inputs, pl.priority AS plan_priority"
            : "p.*, pl.priority AS plan_priority";

        $sql = "SELECT {$select} FROM {$table} p "
            . "LEFT JOIN {$plan_table} pl ON p.plan_id = pl.id "
            . "WHERE {$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

        return $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) ) ?: [];
    }

    public static function count( array $args = [] ): int {
        global $wpdb;

        $table      = $wpdb->prefix . self::TABLE;
        $plan_table = $wpdb->prefix . Costudio_Planning::TABLE;
        $wheres     = [ '1=1' ];
        $params     = [];

        if ( ! empty( $args['site_key'] ) ) {
            $wheres[] = 'p.site_key = %s';
            $params[]  = $args['site_key'];
        }
        if ( ! empty( $args['hub_status'] ) ) {
            switch ( $args['hub_status'] ) {
                case 'planned':
                    $wheres[] = "p.status = 'pending'";
                    break;
                case 'running':
                    $wheres[] = "p.status = 'running'";
                    break;
                case 'published':
                    $wheres[] = "p.status = 'completed' AND p.wp_post_id IS NOT NULL AND p.wp_post_id > 0";
                    break;
                case 'draft':
                    $wheres[] = "p.status = 'completed' AND ( p.wp_post_id IS NULL OR p.wp_post_id = 0 )";
                    break;
                case 'error':
                    $wheres[] = "p.status = 'error'";
                    break;
            }
        } elseif ( ! empty( $args['status'] ) ) {
            $wheres[] = 'p.status = %s';
            $params[]  = $args['status'];
        }
        if ( ! empty( $args['search'] ) ) {
            $wheres[] = '(p.keyword LIKE %s OR p.title LIKE %s)';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
        }
        if ( ! empty( $args['priority'] ) && in_array( $args['priority'], [ 'high', 'medium', 'low' ], true ) ) {
            $wheres[] = 'pl.priority = %s';
            $params[] = $args['priority'];
        }

        $where = implode( ' AND ', $wheres );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table/$plan_table are $wpdb->prefix . constant; $where is built from hardcoded placeholders only.
        $sql   = "SELECT COUNT(*) FROM {$table} p LEFT JOIN {$plan_table} pl ON p.plan_id = pl.id WHERE {$where}";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- always routed through prepare(); variable SQL is safe: tables = constant prefixes, where = hardcoded placeholders.
        return (int) $wpdb->get_var( empty( $params ) ? $wpdb->prepare( $sql ) : $wpdb->prepare( $sql, ...$params ) );
    }

    public static function count_all(): int {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is $wpdb->prefix . constant; no user input.
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status != 'error'" );
    }

    public static function count_total(): int {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is $wpdb->prefix . constant; no user input.
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
    }

    // ── Update ───────────────────────────────────────────────────────────────

    public static function set_status( int $id, string $status ): void {
        global $wpdb;
        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [
                'status'     => $status,
                'updated_at' => current_time( 'mysql' ),
            ],
            [ 'id' => $id ]
        );
    }

    public static function mark_pending( int $id, ?int $current_agent = null, bool $clear_error = false ): void {
        global $wpdb;

        $data = [
            'status'     => 'pending',
            'heartbeat'  => null,
            'updated_at' => current_time( 'mysql' ),
        ];

        if ( null !== $current_agent ) {
            $data['current_agent'] = max( 1, $current_agent );
        }

        if ( $clear_error ) {
            $data['error_message'] = null;
            $data['error_agent']   = null;
        }

        $wpdb->update( $wpdb->prefix . self::TABLE, $data, [ 'id' => $id ] );
    }

    public static function update_output( int $id, int $agent, string $output ): void {
        global $wpdb;

        // Strip all 4-byte characters to prevent MySQL truncation on older utf8 tables
        $safe_output = preg_replace('/[\x{10000}-\x{10FFFF}]/u', '', $output);
        $safe_output = wp_encode_emoji( $safe_output );

        // Support agents 01 to 07
        $col = sprintf( 'output_%02d', $agent );

        // NOTE: 'updated_at' is intentionally excluded here.
        // On older installs the column may not exist yet (added via ALTER TABLE in DB v1.12).
        // Including it caused a silent UPDATE failure (wpdb returns false, no exception),
        // which prevented agent output from being saved and trapped the pipeline in an
        // infinite loop on Agent 01. The schema migration will add the column, but the
        // pipeline must not depend on it to function correctly.
        $result = $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [ $col => $safe_output ],
            [ 'id' => $id ]
        );

        if ( $result === false && ! empty( $wpdb->last_error ) ) {
            costudio_log_debug( 'production.update_output_db_error', [ 'error' => $wpdb->last_error ] );
        }

        // Attempt to update updated_at separately — safe to fail if column not yet migrated.
        $wpdb->query( $wpdb->prepare(
            'UPDATE `' . $wpdb->prefix . self::TABLE . '` SET updated_at = %s WHERE id = %d',
            current_time( 'mysql' ),
            $id
        ) );
        $wpdb->last_error = ''; // Suppress spurious error if column absent.
    }

    /**
     * Update output with optimistic lock — fails if updated_at has changed.
     *
     * @return bool True if update succeeded, false if concurrent edit detected.
     */
    public static function update_output_with_lock( int $id, int $agent, string $output, string $expected_updated_at ): bool {
        global $wpdb;

        $col   = sprintf( 'output_%02d', $agent );
        $table = $wpdb->prefix . self::TABLE;
        $now   = current_time( 'mysql' );
        
        $safe_output = preg_replace('/[\x{10000}-\x{10FFFF}]/u', '', $output);
        $safe_output = wp_encode_emoji( $safe_output );

        $affected = $wpdb->query( $wpdb->prepare(
            "UPDATE {$table} SET {$col} = %s, updated_at = %s WHERE id = %d AND updated_at = %s",
            $safe_output, $now, $id, $expected_updated_at
        ) );

        if ( $affected === false && ! empty( $wpdb->last_error ) ) {
            costudio_log_debug( 'production.update_output_with_lock_db_error', [ 'error' => $wpdb->last_error ] );
        }

        return $affected > 0;
    }

    /**
     * Mark production as running and show Agent 1 in the UI before the first AI call finishes.
     *
     * Seeds `heartbeat` with the current time so the admin UI's stalled-pipeline
     * heuristic (which fires on `!heartbeat && has_pending_token`) does not
     * trigger during the brief window between dispatch and the runner's first
     * heartbeat. Genuine stalls are still caught via the heartbeat-age threshold.
     */
    public static function mark_pipeline_started( int $id, int $current_agent = 1 ): void {
        global $wpdb;
        $current_agent = max( 1, $current_agent );
        $table = $wpdb->prefix . self::TABLE;
        $now_gmt = current_time( 'mysql', true );

        // `started_at` is claimed when the production enters the local queue.
        // COALESCE keeps queue/pause time in the same wall-clock session while
        // still repairing legacy rows that predate the persistent timer.
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table}
                 SET status = 'running',
                     current_agent = %d,
                     heartbeat = %s,
                     started_at = COALESCE(started_at, %s),
                     updated_at = %s
                 WHERE id = %d",
                $current_agent,
                $now_gmt,
                $now_gmt,
                current_time( 'mysql' ),
                $id
            )
        );
    }

    /**
     * Atomically claim the start of the current agent attempt.
     *
     * Re-entering the runner after a ContentOS Auto chunk yield must return the
     * original value instead of replacing it. The conditional UPDATE also
     * prevents a delayed runner for an old agent from claiming the next agent.
     */
    public static function claim_current_agent_started_at( int $id, int $agent ): ?string {
        global $wpdb;

        if ( $id <= 0 || $agent <= 0 ) {
            return null;
        }

        $table     = $wpdb->prefix . self::TABLE;
        $candidate = self::recover_current_agent_started_at( $id, $agent );

        $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table}
                 SET current_agent_started_at = COALESCE(current_agent_started_at, %s)
                 WHERE id = %d
                   AND current_agent = %d",
                $candidate,
                $id,
                $agent
            )
        );

        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT current_agent, current_agent_started_at
                 FROM {$table}
                 WHERE id = %d",
                $id
            )
        );

        if ( ! $row || (int) $row->current_agent !== $agent || empty( $row->current_agent_started_at ) ) {
            return null;
        }

        return (string) $row->current_agent_started_at;
    }

    /**
     * Advance the runner and clear the previous attempt boundary atomically.
     */
    public static function transition_to_agent( int $id, int $expected_agent, int $next_agent, ?string $status = null ): bool {
        global $wpdb;

        $table = $wpdb->prefix . self::TABLE;
		$now_gmt = current_time( 'mysql', true );
        $data  = array(
            'current_agent'            => $next_agent,
            'current_agent_started_at' => null,
            // This is the durable handoff boundary. Keeping a GMT heartbeat
			// lets polling distinguish "selected but not started" from an active
			// provider request and safely recover a lost loopback dispatch.
            'heartbeat'                => $now_gmt,
            'updated_at'               => current_time( 'mysql' ),
        );
        $formats = array( '%d', '%s', '%s', '%s' );

        if ( null !== $status ) {
            $data['status'] = $status;
            $formats[]      = '%s';
        }

        $result = $wpdb->update(
            $table,
            $data,
            array(
                'id'            => $id,
                'current_agent' => $expected_agent,
            ),
            $formats,
            array( '%d', '%d' )
        );

        return false !== $result;
    }

    public static function clear_current_agent_timing( int $id, ?int $expected_agent = null ): bool {
        global $wpdb;

        $where        = array( 'id' => $id );
        $where_format = array( '%d' );
        if ( null !== $expected_agent ) {
            $where['current_agent'] = $expected_agent;
            $where_format[]         = '%d';
        }

        return false !== $wpdb->update(
            $wpdb->prefix . self::TABLE,
            array( 'current_agent_started_at' => null ),
            $where,
            array( '%s' ),
            $where_format
        );
    }

    /**
     * Return the stable UTC start and elapsed milliseconds for an agent.
     *
     * @return array{started_at:?string,elapsed_ms:int}
     */
    public static function get_agent_attempt_timing( int $id, int $agent ): array {
        $production = self::get_by_id( $id );
        if (
            ! $production
            || (int) ( $production->current_agent ?? 0 ) !== $agent
            || empty( $production->current_agent_started_at )
        ) {
            return array( 'started_at' => null, 'elapsed_ms' => 0 );
        }

        $start_ts = strtotime( $production->current_agent_started_at . ' UTC' );
        if ( false === $start_ts ) {
            return array( 'started_at' => null, 'elapsed_ms' => 0 );
        }

        return array(
            'started_at' => $production->current_agent_started_at,
            'elapsed_ms' => max( 0, (int) round( microtime( true ) * 1000 ) - ( $start_ts * 1000 ) ),
        );
    }

    /**
     * Wall-clock duration of the current production/reprocess session.
     */
    public function get_elapsed_seconds(): int {
        if ( empty( $this->started_at ) ) {
            return 0;
        }

        $start_ts = strtotime( $this->started_at . ' UTC' );
        if ( false === $start_ts ) {
            return 0;
        }

        $end_ts = time();
        if ( 'completed' === $this->status && ! empty( $this->completed_at ) ) {
            $end_ts = strtotime( $this->completed_at . ' UTC' );
        } elseif ( in_array( $this->status, array( 'paused', 'cancelled' ), true ) && ! empty( $this->updated_at ) ) {
            // set_status() stores updated_at in the site's local timezone.
            $end_ts = strtotime( get_gmt_from_date( $this->updated_at ) . ' UTC' );
        } elseif ( in_array( $this->status, array( 'error', 'pending_upgrade' ), true ) && ! empty( $this->updated_at ) ) {
            // Error paths already persist updated_at in GMT.
            $end_ts = strtotime( $this->updated_at . ' UTC' );
        }

        return max( 0, (int) $end_ts - (int) $start_ts );
    }

    /**
     * Elapsed seconds for the current agent, frozen for terminal/paused states.
     */
    public function get_current_agent_elapsed_seconds(): int {
        if ( empty( $this->current_agent_started_at ) ) {
            return 0;
        }

        $start_ts = strtotime( $this->current_agent_started_at . ' UTC' );
        if ( false === $start_ts ) {
            return 0;
        }

        $end_ts = time();
        if ( in_array( $this->status, array( 'paused', 'cancelled' ), true ) && ! empty( $this->updated_at ) ) {
            $end_ts = strtotime( get_gmt_from_date( $this->updated_at ) . ' UTC' );
        } elseif ( in_array( $this->status, array( 'error', 'pending_upgrade' ), true ) && ! empty( $this->updated_at ) ) {
            $end_ts = strtotime( $this->updated_at . ' UTC' );
        }

        return max( 0, (int) $end_ts - (int) $start_ts );
    }

    /**
     * Best-effort one-time recovery for a production already running during
     * the v1.26 migration. New attempts simply fall back to the current time.
     */
    private static function recover_current_agent_started_at( int $id, int $agent ): string {
        global $wpdb;

		// A superseded durable attempt proves this is a fresh execution, not a
		// one-time migration recovery. Reusing its historical log timestamp would
		// recreate the old idempotency key and point back to a cancelled job.
		if ( class_exists( 'Costudio_BYOK_Jobs' ) && method_exists( 'Costudio_BYOK_Jobs', 'latest_any_attempt' ) ) {
			$previous = Costudio_BYOK_Jobs::latest_any_attempt( $id, $agent );
			if ( $previous && ! empty( $previous['superseded_at'] ) ) {
				return current_time( 'mysql', true );
			}
		}

        $production = self::get_by_id( $id );
        $session_ts = $production && ! empty( $production->started_at )
            ? (int) strtotime( $production->started_at . ' UTC' )
            : 0;

        $pending = get_transient( 'costudio_pending_job_' . $id );
        if ( is_array( $pending ) && ! empty( $pending['first_seen_at'] ) ) {
            $pending_ts = (int) $pending['first_seen_at'];
            if ( $pending_ts > 0 && ( 0 === $session_ts || $pending_ts >= $session_ts ) ) {
                return gmdate( 'Y-m-d H:i:s', $pending_ts );
            }
        }

        if ( class_exists( 'Costudio_Logger' ) ) {
            $logs_table    = $wpdb->prefix . Costudio_Logger::TABLE;
            $session_local = $session_ts > 0
                ? get_date_from_gmt( gmdate( 'Y-m-d H:i:s', $session_ts ) )
                : '1970-01-01 00:00:00';
            $message       = sprintf( 'Agent %02d started', $agent );
            $log_start     = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT MIN(created_at)
                     FROM {$logs_table}
                     WHERE production_id = %d
                       AND agent = %d
                       AND message = %s
                       AND created_at >= %s",
                    $id,
                    $agent,
                    $message,
                    $session_local
                )
            );

            if ( $log_start ) {
                return get_gmt_from_date( (string) $log_start );
            }
        }

        return current_time( 'mysql', true );
    }

    /**
     * Persist the structured source list returned by /ai/search.
     *
     * Stored verbatim as JSON so the References Renderer can later resolve
     * Vertex redirect URLs, validate reachability and substitute {{ref:N}}
     * placeholders deterministically.
     *
     * @param int   $id       Production row ID.
     * @param array $sources  List of { url, title, snippet, … } items.
     */
    public static function set_sources_json( int $id, array $sources ): void {
        global $wpdb;
        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [ 'sources_json' => wp_json_encode( array_values( $sources ) ) ],
            [ 'id' => $id ]
        );
    }

    /**
     * Persist the image manifest (resumable/idempotent publishing).
     *
     * Stored verbatim as JSON: { source_hash, items: { filename => { status,
     * attachment_id, url, … } } }. The publisher reuses `done` items on retry
     * instead of regenerating them.
     *
     * @param int   $id       Production row ID.
     * @param array $manifest { source_hash:string, items:array } structure.
     */
    public static function set_images_json( int $id, array $manifest ): void {
        global $wpdb;
        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [ 'images_json' => wp_json_encode( $manifest ) ],
            [ 'id' => $id ]
        );
    }

    /**
     * Read the decoded image manifest for a production. Always returns a
     * well-formed array { source_hash:string, items:array }; malformed or
     * empty JSON yields an empty manifest.
     *
     * @param int $id Production row ID.
     * @return array{source_hash:string,items:array,provider_block:array}
     */
    public static function get_images_manifest( int $id ): array {
        $prod = self::get_by_id( $id );
        $raw  = $prod->images_json ?? '';
        $data = '' !== (string) $raw ? json_decode( (string) $raw, true ) : null;
        if ( ! is_array( $data ) ) {
            $data = array();
        }
        return array(
            'source_hash' => isset( $data['source_hash'] ) ? (string) $data['source_hash'] : '',
            'items'       => ( isset( $data['items'] ) && is_array( $data['items'] ) ) ? $data['items'] : array(),
            'provider_block' => ( isset( $data['provider_block'] ) && is_array( $data['provider_block'] ) ) ? $data['provider_block'] : array(),
        );
    }

    public static function set_links( int $id, string $links ): void {
        global $wpdb;
        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [ 'links_internos' => $links ],
            [ 'id' => $id ]
        );
    }

    public static function set_completed(
        int $id,
        int $post_id,
        string $post_url,
        string $edit_url,
        string $title = '',
        string $image_warnings = ''
    ): void {
        global $wpdb;

        $data = [
            'status'       => 'completed',
            'wp_post_id'   => $post_id,
            'wp_post_url'  => $post_url,
            'edit_url'     => $edit_url,
            'current_agent_started_at' => null,
            'completed_at' => current_time( 'mysql', true ),
        ];

        if ( ! empty( $title ) ) {
            $data['title'] = $title;
        }

        if ( ! empty( $image_warnings ) ) {
            $data['error_message'] = '[IMG_WARNING] ' . ltrim( $image_warnings, '[IMG_WARNING] ' );
        }

        $wpdb->update( $wpdb->prefix . self::TABLE, $data, [ 'id' => $id ] );

        // Propagate completion status to linked planning item.
        $prod = self::find( $id );
        if ( $prod && ! empty( $prod->plan_id ) ) {
            Costudio_Planning::update_status( (int) $prod->plan_id, 'published' );
        }

        /**
         * Fires after a production pipeline finishes successfully.
         *
         * @since 1.3.0
         *
         * @param int        $id   Production row ID.
         * @param object|null $prod Production record object.
         */
        do_action( 'costudio_production_completed', $id, $prod );
    }

    public static function checkpoint_publication( int $id, int $post_id, string $post_url, string $edit_url ): bool {
        global $wpdb;
        if ( $id <= 0 || $post_id <= 0 ) {
            return false;
        }
        $updated = $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [
                'wp_post_id'  => $post_id,
                'wp_post_url' => esc_url_raw( $post_url ),
                'edit_url'    => esc_url_raw( $edit_url ),
                'updated_at'  => current_time( 'mysql', true ),
            ],
            [ 'id' => $id ]
        );
        return false !== $updated;
    }

    /**
     * Mark a production as completed when the pipeline ends without a publish
     * payload. Publishing flows should keep using set_completed().
     */
    public static function mark_pipeline_completed( int $id ): void {
        global $wpdb;

        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [
                'status'       => 'completed',
                'heartbeat'    => null,
                'current_agent_started_at' => null,
                'completed_at' => current_time( 'mysql', true ),
                'updated_at'   => current_time( 'mysql', true ),
            ],
            [ 'id' => $id ]
        );
    }

    public static function update_heartbeat( int $id ): void {
        global $wpdb;
        $wpdb->update(
            $wpdb->prefix . self::TABLE,
            [ 'heartbeat' => current_time( 'mysql', true ) ],
            [ 'id' => $id ]
        );
    }

    public static function set_error( int $id, string $message, int $agent = 0, string $error_category = 'other' ): void {
        global $wpdb;

        $production_for_log = self::get_by_id( $id );
        $is_final_publish_error = false;
        if ( $production_for_log ) {
            $version = $production_for_log->get_pipeline_version();
            $max     = ( $version === 7 ) ? 7 : 6;
            $is_final_publish_error = $agent > $max;
        }

        $data = [
            'status'        => 'error',
            'error_message' => $message,
            'heartbeat'     => null,
            'updated_at'    => current_time( 'mysql', true ),
        ];
        if ( $agent > 0 ) {
            $data['error_agent'] = $agent;
        }

        $wpdb->update( $wpdb->prefix . self::TABLE, $data, [ 'id' => $id ] );

        if ( class_exists( 'Costudio_Runner' ) ) {
            $log_message = 'Erro: ' . $message;
            $log_agent   = null;

            if ( $agent > 0 ) {
                if ( $is_final_publish_error ) {
                    $log_message = __( 'Erro na etapa final de publicação: ', 'contentosapp-studio' ) . $message;
                } else {
                    $log_message = __( 'Erro no agente ', 'contentosapp-studio' ) . $agent . ': ' . $message;
                    $log_agent   = $agent;
                }
            }

            Costudio_Runner::pipeline_log_append(
                $id,
                $log_message,
                'err',
                $log_agent
            );
        }

        self::queue_terminal_telemetry(
            $id,
            'production.failed',
            [
                'status'         => 'error',
                'agent_failed'   => $agent,
                'error'          => $message,
                'error_category' => $error_category,
            ],
            $production_for_log
        );

        /**
         * Fires after a production pipeline fails.
         *
         * @since 1.3.0
         *
         * @param int    $id      Production row ID.
         * @param string $message Error message from the failing agent.
         * @param int    $agent   Agent index that failed (0 = unknown).
         */
        do_action( 'costudio_production_failed', $id, $message, $agent );
    }

    /**
     * Queue and immediately flush terminal production telemetry.
     *
     * @param object|null $production Optional production record already loaded by the caller.
     */
    private static function queue_terminal_telemetry( int $id, string $event_name, array $payload, ?object $production = null ): void {
        if ( ! class_exists( 'Costudio_Telemetry' ) ) {
            return;
        }

        $production = $production ?: self::get_by_id( $id );
        $inputs     = $production ? json_decode( $production->inputs ?? '{}', true ) : [];
        if ( ! is_array( $inputs ) ) {
            $inputs = [];
        }

        $type = '';
        if ( class_exists( 'Costudio_Article_Types' ) ) {
            $type = Costudio_Article_Types::normalize( $inputs['article_type'] ?? '' );
        } else {
            $type = (string) ( $inputs['article_type'] ?? '' );
        }

        $base = [
            'production_id'  => $id,
            'production_uid' => self::tracking_uid_for( $id ),
            'type'           => $type,
            'locale'         => function_exists( 'costudio_resolve_ui_locale_from_options' ) ? costudio_resolve_ui_locale_from_options() : 'pt_BR',
            'attempts'       => $production ? (int) ( $production->attempts ?? 0 ) : 0,
        ];

        Costudio_Telemetry::queue( $event_name, array_merge( $base, $payload ) );
        Costudio_Telemetry::flush();
    }

    /**
     * Reset agent outputs for a new attempt.
     *
     * Manual retries start a new user-visible session and therefore replace
     * `started_at`. Internal pipeline rewinds (Reviewer → Writer) remain part of
     * the same production session and must pass `$restart_session = false`.
     */
    public static function reset_for_retry(
        int $id,
        int $from_agent,
        string $status = 'running',
        bool $restart_session = true
    ): void {
        global $wpdb;

        $instance = self::get_by_id( $id );
        if ( ! $instance ) return;

        $version = $instance->get_pipeline_version();
        $max     = ( $version === 7 ) ? 7 : 6;
        $status  = in_array( $status, array( 'running', 'pending' ), true ) ? $status : 'running';

        $data = [
            'status'        => $status,
            'error_message' => null,
            'error_agent'   => null,
            'current_agent' => $from_agent,
            'current_agent_started_at' => null,
            'attempts'      => 0,
            'heartbeat'     => null,
            'completed_at'  => null,
            'updated_at'    => current_time( 'mysql' ),
        ];

        if ( $restart_session ) {
            $data['started_at'] = current_time( 'mysql', true );
        }

        for ( $i = $from_agent; $i <= $max; $i++ ) {
            $col = $instance->map_step_to_column( $i );
            if ( $col ) {
                $data[ $col ] = null;
            }
        }

        $wpdb->update( $wpdb->prefix . self::TABLE, $data, [ 'id' => $id ] );

        // A retry-from-agent is a deliberate fresh start for that agent. Discard
        // any chunked-poll job left pending by Costudio_ContentOS_Adapter for a
        // previous (possibly now-dead) attempt, so the next run always submits a
        // brand-new remote job instead of trying to resume a stale one.
		delete_transient( 'costudio_pending_job_' . $id );
		delete_transient( "costudio_bg_{$id}" );
		delete_transient( "costudio_bg_src_{$id}" );
		delete_transient( "costudio_bg_at_{$id}" );
		if ( function_exists( 'delete_option' ) ) {
			delete_option( "costudio_handoff_recovery_{$id}" );
		}
		if ( class_exists( 'Costudio_Remote_Jobs' ) ) {
			Costudio_Remote_Jobs::request_cancel( $id );
			Costudio_Remote_Jobs::supersede_from( $id, $from_agent );
		}
		if ( class_exists( 'Costudio_BYOK_Jobs' ) ) {
			Costudio_BYOK_Jobs::supersede_from( $id, $from_agent, $restart_session ? 'manual_retry' : 'editorial_rewind' );
		}
    }

    // ── Delete ───────────────────────────────────────────────────────────────

    public static function delete( int $id ): void {
        global $wpdb;
		delete_transient( "costudio_bg_{$id}" );
		delete_transient( "costudio_bg_src_{$id}" );
		delete_transient( "costudio_bg_at_{$id}" );
		if ( function_exists( 'delete_option' ) ) {
			delete_option( "costudio_handoff_recovery_{$id}" );
		}
		if ( class_exists( 'Costudio_Remote_Jobs' ) ) {
			Costudio_Remote_Jobs::request_cancel( $id );
			Costudio_Remote_Jobs::delete_for_production( $id );
		}
		if ( class_exists( 'Costudio_BYOK_Jobs' ) ) {
			Costudio_BYOK_Jobs::request_cancel_for_production( $id, 'production_deleted' );
			Costudio_BYOK_Jobs::supersede_from( $id, 1, 'production_deleted' );
		}
        $wpdb->delete( $wpdb->prefix . self::TABLE, [ 'id' => $id ] );
    }

    // ── Pipeline Abstraction Layer (Rollback-Safe) ───────────────────────────

    /**
     * Returns the pipeline version for this specific production.
     * Productions created BEFORE the 7-agent refactor = 6.
     * Productions created AFTER = 7.
     */
    public function get_pipeline_version(): int {
        $inputs = json_decode( $this->inputs ?? '{}', true );
        if ( isset( $inputs['pipeline_v'] ) ) {
            return (int) $inputs['pipeline_v'];
        }

        // Heuristic fallback: if output_07 is used, it's definitely v7.
        if ( ! empty( $this->output_07 ) ) {
            return 7;
        }

        // If it was recently created or is currently running/error, we assume v7
        // because the system-wide default is now v7.
        if ( in_array( $this->status, [ 'running', 'error', 'starting', 'awaiting_approval' ], true ) ) {
            return 7;
        }

        // Final fallback: everything else (likely older completed/cancelled records) is v6.
        return 6;
    }

    /**
     * Maps a logical step ID to its physical database column name.
     *
     * v6 Map: 1->out01, 2->out02, 3->out03, 4->out04, 5->out05 (Visual), 6->out06 (Social)
     * v7 Map: 1->out01, 2->out02, 3->out03, 4->out04, 5->out05 (Reviewer), 6->out06 (Visual), 7->out07 (Social)
     */
    public function map_step_to_column( int $logical_step ): ?string {
        $version = $this->get_pipeline_version();

        if ( $version === 6 ) {
            // Legacy Map (Visual is 5, Social is 6)
            if ( $logical_step > 6 ) return null;
            $phys = $logical_step;
        } else {
            // New Map (Reviewer is 5, Visual is 6, Social is 7)
            if ( $logical_step > 7 ) return null;
            $phys = $logical_step;
        }

        return sprintf( 'output_%02d', $phys );
    }

    /**
     * Retrieves the content for a specific logical step.
     */
    public function get_agent_output( int $logical_step ): ?string {
        $col = $this->map_step_to_column( $logical_step );
        return $col ? ( $this->$col ?? null ) : null;
    }

    /**
     * Calculates the sum of all agent latencies for this production.
     * Returns total seconds.
     */
    public function get_total_processing_seconds(): int {
        global $wpdb;
        $usage_table = $wpdb->prefix . 'costudio_usage';
        $latency = $wpdb->get_var( $wpdb->prepare(
            "SELECT SUM(latency_ms) FROM {$usage_table} WHERE production_id = %d",
            $this->id
        ) );

        return (int) round( ( (int) $latency ) / 1000 );
    }
}
