<?php
/**
 * Costudio_DB — database table provisioning.
 *
 * Creates / upgrades the wp_costudio_productions and wp_costudio_planning tables.
 *
 * Called via the 'costudio_install_tables' hook (fired on Core activation /
 * version upgrade) and also hooked at module load time to apply upgrades
 * without requiring plugin reactivation.
 *
 * @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 -- Schema migration queries use plugin-controlled table names ($table = $wpdb->prefix . constant); ALTER TABLE statements cannot use placeholders.

class Costudio_DB {

    const DB_VERSION = '1.30';

    // ── Public ───────────────────────────────────────────────────────────────

    /**
     * Install or upgrade database tables and seed default options.
     * Safe to call multiple times (dbDelta is idempotent).
     */
    public static function install(): void {
        self::create_tables();
		Costudio_Remote_Jobs::install();
		Costudio_BYOK_Jobs::install();
        self::maybe_migrate();
        self::migrate_v120(); // Ensure metrics columns
        self::seed_default_options();
        costudio_update_option( 'costudio_db_version', self::DB_VERSION, false );
    }

    /**
     * Run upgrade migrations when the stored DB version differs from the
     * current one.  Called on every module load for zero-effort upgrades.
     */
    public static function maybe_upgrade(): void {
        if ( costudio_get_option( 'costudio_db_version' ) !== self::DB_VERSION ) {
            self::install();
        }
    }

    // ── Table creation ────────────────────────────────────────────────────────

    private static function create_tables(): void {
        global $wpdb;

        $table_prod = $wpdb->prefix . Costudio_Production::TABLE;
        $table_plan = $wpdb->prefix . Costudio_Planning::TABLE;
        $table_publications = $wpdb->prefix . 'costudio_publication_attempts';
        $charset    = $wpdb->get_charset_collate();

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $sql_prod = "CREATE TABLE {$table_prod} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            status varchar(20) NOT NULL DEFAULT 'pending',
            site_key varchar(20) NOT NULL DEFAULT '',
            keyword varchar(255) NOT NULL DEFAULT '',
            title varchar(500) DEFAULT NULL,
            is_external tinyint(1) NOT NULL DEFAULT 0,
            plan_id bigint(20) unsigned DEFAULT NULL,
            inputs longtext DEFAULT NULL,
            links_internos longtext DEFAULT NULL,
            output_01 longtext DEFAULT NULL,
            output_02 longtext DEFAULT NULL,
            output_03 longtext DEFAULT NULL,
            output_04 longtext DEFAULT NULL,
            output_05 longtext DEFAULT NULL,
            output_06 longtext DEFAULT NULL,
            output_07 longtext DEFAULT NULL,
            sources_json longtext DEFAULT NULL,
            images_json longtext DEFAULT NULL,
            current_agent tinyint(4) DEFAULT NULL,
            current_agent_started_at datetime DEFAULT NULL,
            wp_post_id bigint(20) unsigned DEFAULT NULL,
            wp_post_url varchar(500) DEFAULT NULL,
            edit_url varchar(500) DEFAULT NULL,
            attempts tinyint(4) NOT NULL DEFAULT 0,
            heartbeat datetime DEFAULT NULL,
            error_agent tinyint(4) DEFAULT NULL,
            error_message text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            started_at datetime DEFAULT NULL,
            completed_at datetime DEFAULT NULL,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY status (status),
            KEY site_key (site_key),
            KEY wp_post_id (wp_post_id),
            KEY plan_id (plan_id),
            KEY created_at (created_at),
            KEY status_created_at (status, created_at),
            KEY site_status_created_at (site_key, status, created_at),
            KEY status_plan_id (status, plan_id)
        ) {$charset};";

        dbDelta( $sql_prod );

        $sql_publications = "CREATE TABLE {$table_publications} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            job_id char(36) DEFAULT NULL,
            idempotency_key char(64) NOT NULL,
            production_id bigint(20) unsigned NOT NULL,
            production_uid varchar(100) NOT NULL DEFAULT '',
            site_key varchar(100) NOT NULL DEFAULT '',
            origin_hash char(64) NOT NULL,
            payload_hash char(64) NOT NULL,
            payload_json longtext DEFAULT NULL,
            state varchar(20) NOT NULL DEFAULT 'preparing',
            job_status varchar(20) NOT NULL DEFAULT 'inactive',
            stage varchar(40) NOT NULL DEFAULT 'queued',
            driver varchar(20) NOT NULL DEFAULT '',
            available_at datetime DEFAULT NULL,
            worker_id varchar(190) DEFAULT NULL,
            claim_count int unsigned NOT NULL DEFAULT 0,
            heartbeat_at datetime DEFAULT NULL,
            lease_expires_at datetime DEFAULT NULL,
            stage_started_at datetime DEFAULT NULL,
            error_code varchar(100) DEFAULT NULL,
            terminal_source varchar(50) DEFAULT NULL,
            finished_at datetime DEFAULT NULL,
            attempts int unsigned NOT NULL DEFAULT 1,
            claim_token varchar(64) DEFAULT NULL,
            claimed_at datetime DEFAULT NULL,
            post_id bigint(20) unsigned DEFAULT NULL,
            post_url varchar(500) DEFAULT NULL,
            edit_url varchar(500) DEFAULT NULL,
            error_message text DEFAULT NULL,
            created_post_at datetime DEFAULT NULL,
            completed_at datetime DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY job_id (job_id),
            UNIQUE KEY idempotency_key (idempotency_key),
            UNIQUE KEY production_id (production_id),
            KEY job_status_available (job_status, available_at),
            KEY lease_expires_at (lease_expires_at),
            KEY state_updated (state, updated_at),
            KEY site_post (site_key, post_id)
        ) ENGINE=InnoDB {$charset};";

        dbDelta( $sql_publications );

        $sql_plan = "CREATE TABLE {$table_plan} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            title varchar(500) NOT NULL DEFAULT '',
            keyword varchar(255) DEFAULT '',
            site_key varchar(20) NOT NULL DEFAULT '',
            category varchar(100) DEFAULT '',
            planned_date date DEFAULT NULL,
            priority varchar(10) NOT NULL DEFAULT 'medium',
            status varchar(20) NOT NULL DEFAULT 'planned',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY site_key (site_key),
            KEY status (status),
            KEY priority (priority),
            KEY status_planned_date (status, planned_date),
            KEY site_status_planned_date (site_key, status, planned_date)
        ) {$charset};";

        dbDelta( $sql_plan );

        // ── Usage tracking table (v1.9) ──────────────────────────────────────
        $table_usage = $wpdb->prefix . Costudio_Usage::TABLE;

        $sql_usage = "CREATE TABLE {$table_usage} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            production_id bigint(20) unsigned NOT NULL DEFAULT 0,
            agent tinyint(4) NOT NULL DEFAULT 0,
            provider varchar(20) NOT NULL DEFAULT '',
            model varchar(100) NOT NULL DEFAULT '',
            input_tokens int unsigned NOT NULL DEFAULT 0,
            output_tokens int unsigned NOT NULL DEFAULT 0,
            cost_usd decimal(10,6) NOT NULL DEFAULT 0,
            grounding tinyint(1) NOT NULL DEFAULT 0,
            latency_ms int unsigned NOT NULL DEFAULT 0,
            started_at datetime DEFAULT NULL,
            byok_job_id char(36) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY byok_job_id (byok_job_id),
            KEY production_id (production_id),
            KEY provider (provider),
            KEY created_at (created_at),
            KEY provider_created_at (provider, created_at)
        ) {$charset};";

        dbDelta( $sql_usage );

        // ── Structured logs table (v1.9) ─────────────────────────────────────
        $table_logs = $wpdb->prefix . Costudio_Logger::TABLE;

        $sql_logs = "CREATE TABLE {$table_logs} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            level varchar(10) NOT NULL DEFAULT 'info',
            message text NOT NULL,
            context_json longtext DEFAULT NULL,
            production_id bigint(20) unsigned DEFAULT NULL,
            agent tinyint(4) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY level (level),
            KEY production_id (production_id),
            KEY created_at (created_at),
            KEY level_created_at (level, created_at)
        ) {$charset};";

        dbDelta( $sql_logs );
    }

    // ── Data migration ────────────────────────────────────────────────────────

    private static function maybe_migrate(): void {
        global $wpdb;

        $table_prod = $wpdb->prefix . Costudio_Production::TABLE;

        // Populate empty titles with keywords for existing productions.
        $wpdb->query( "UPDATE {$table_prod} SET title = keyword WHERE title IS NULL OR title = ''" );

        // Ensure plan_id column exists (manual fallback for very old installs).
        $has_plan_id = $wpdb->get_row( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_prod,
            'plan_id'
        ) );
        if ( ! $has_plan_id ) {
            $wpdb->query(
                "ALTER TABLE {$table_prod} ADD COLUMN plan_id bigint(20) unsigned DEFAULT NULL AFTER is_external"
            );
        }

        // v1.8 — Add performance indexes for high-volume filtering and sorting.
        self::ensure_performance_indexes();

        // v1.12 — Add missing updated_at column and ensure auxiliary tables exist.
        self::migrate_v112();

        // v1.13 — Add attempts and heartbeat for async watchdog.
        self::migrate_v113();

        // v1.14 — Add sources_json for the References Renderer.
        self::migrate_v114();

        // Ensure the Agent_Seeder has populated prompt options.
        self::cleanup_legacy_agent_prompts_v20();

        // v1.21 — Add output_07 column for the new 7-agent pipeline.
        self::migrate_v121();

        // v1.22 — Rename inputs.tipo_artigo → inputs.article_type and normalize
        // legacy Portuguese values to canonical English slugs.
        self::migrate_v122();

        // v1.24 — Keep Contentosapp Studio options out of WordPress' global
        // alloptions payload.
        self::migrate_v124();

        // v1.25 — Add images_json for the resumable/idempotent image manifest
        // (chunked publishing — survives server timeouts without regenerating
        // already-uploaded images).
        self::migrate_v125();

        // v1.26 — Persist the current agent attempt start and the corresponding
        // usage-row start so chunked ContentOS Auto polling cannot reset timers.
        self::migrate_v126();

        // v1.28 — Durable BYOK queue foundation and idempotent usage linkage.
        self::migrate_v128();

		// v1.29 — Exactly-once usage reconciliation checkpoint for BYOK workers.
		self::migrate_v129();

		// v1.30 — Durable final-publication execution metadata. dbDelta adds the
		// columns above; this migration only normalizes existing ledger rows.
		self::migrate_v130();
    }

	private static function migrate_v130(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'costudio_publication_attempts';
		$now   = current_time( 'mysql', true );

		// dbDelta normally provisions these fields. Keep an explicit idempotent
		// fallback because older/shared-host MySQL variants have historically
		// skipped parts of wide ALTERs. A missing queue column must never break the
		// first publication immediately after a normal plugin update.
		$columns = array(
			'job_id'            => 'char(36) DEFAULT NULL',
			'job_status'        => "varchar(20) NOT NULL DEFAULT 'inactive'",
			'stage'             => "varchar(40) NOT NULL DEFAULT 'queued'",
			'driver'            => "varchar(20) NOT NULL DEFAULT ''",
			'available_at'      => 'datetime DEFAULT NULL',
			'worker_id'         => 'varchar(190) DEFAULT NULL',
			'claim_count'       => 'int unsigned NOT NULL DEFAULT 0',
			'heartbeat_at'      => 'datetime DEFAULT NULL',
			'lease_expires_at'  => 'datetime DEFAULT NULL',
			'stage_started_at'  => 'datetime DEFAULT NULL',
			'error_code'        => 'varchar(100) DEFAULT NULL',
			'terminal_source'   => 'varchar(50) DEFAULT NULL',
			'finished_at'       => 'datetime DEFAULT NULL',
		);
		$existing = $wpdb->get_col( "SHOW COLUMNS FROM `{$table}`", 0 );
		foreach ( $columns as $name => $definition ) {
			if ( ! in_array( $name, $existing, true ) ) {
				$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$name}` {$definition}" );
			}
		}

		$indexes = (array) $wpdb->get_results( "SHOW INDEX FROM `{$table}`", ARRAY_A );
		$index_names = array_unique( array_map( static function ( array $index ): string {
			return (string) ( $index['Key_name'] ?? '' );
		}, $indexes ) );
		if ( ! in_array( 'job_id', $index_names, true ) ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD UNIQUE KEY `job_id` (`job_id`)" );
		}
		if ( ! in_array( 'job_status_available', $index_names, true ) ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD KEY `job_status_available` (`job_status`,`available_at`)" );
		}
		if ( ! in_array( 'lease_expires_at', $index_names, true ) ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD KEY `lease_expires_at` (`lease_expires_at`)" );
		}

		$wpdb->query( "UPDATE {$table} SET job_status='completed',stage='completed',finished_at=COALESCE(finished_at,completed_at,updated_at) WHERE state='succeeded' AND job_status<>'completed'" );
		$wpdb->query( "UPDATE {$table} SET job_status='failed',stage='failed',finished_at=COALESCE(finished_at,updated_at) WHERE state='failed' AND job_status NOT IN ('completed','failed')" );
		// Rows created by the old synchronous publisher have no durable job UUID.
		// They are ledger history, not queued work, and must never be picked up by
		// the new dispatcher merely because an added column received its default.
		$wpdb->query( "UPDATE {$table} SET job_status='inactive' WHERE job_id IS NULL AND state NOT IN ('succeeded','failed')" );
		$wpdb->query( $wpdb->prepare( "UPDATE {$table} SET available_at=COALESCE(available_at,created_at,%s),heartbeat_at=COALESCE(heartbeat_at,updated_at,created_at,%s) WHERE available_at IS NULL OR heartbeat_at IS NULL", $now, $now ) );
	}

    /**
     * v1.21 — Adiciona a coluna output_07 para suportar o pipeline de 7 agentes.
     */
    private static function migrate_v121(): void {
        global $wpdb;
        $table_prod = $wpdb->prefix . Costudio_Production::TABLE;

        // 1. Column output_07 (v1.21)
        $has_07 = $wpdb->get_row( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_prod,
            'output_07'
        ) );
        if ( ! $has_07 ) {
            $wpdb->query( "ALTER TABLE `{$table_prod}` ADD COLUMN `output_07` longtext DEFAULT NULL AFTER `output_06`" );
        }

        // 2. Column started_at (ensures it exists to avoid dbDelta noise)
        $has_started = $wpdb->get_row( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_prod,
            'started_at'
        ) );
        if ( ! $has_started ) {
            $wpdb->query( "ALTER TABLE `{$table_prod}` ADD COLUMN `started_at` datetime DEFAULT NULL AFTER `created_at`" );
        }
    }

    /**
     * v1.22 — Migrate JSON `inputs` field on every production row:
     *   - Rename legacy key `tipo_artigo` → `article_type`
     *   - Normalize the value via Costudio_Article_Types::normalize() so any
     *     Portuguese value (PILAR, SATÉLITE, NOTÍCIA, ATUALIZAÇÃO, COMPARATIVO,
     *     case-insensitive, accented or not) becomes a canonical English slug.
     *
     * Idempotent: only rows whose `inputs` JSON still contains the legacy key
     * are touched. Running on a fully-migrated DB is a no-op (the LIKE filter
     * returns zero rows).
     *
     * Pre-production note: this exists for dev/staging installs that have
     * accumulated rows under the old key. Once running, it self-heals on every
     * load until clean.
     */
    private static function migrate_v122(): void {
        global $wpdb;

        if ( ! class_exists( 'Costudio_Article_Types' ) ) {
            return;
        }

        $table_prod = $wpdb->prefix . Costudio_Production::TABLE;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is a controlled constant.
        $rows = $wpdb->get_results( "SELECT id, inputs FROM `{$table_prod}` WHERE inputs LIKE '%tipo_artigo%'" );

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

        $migrated = 0;

        foreach ( $rows as $row ) {
            $inputs = json_decode( $row->inputs, true );
            if ( ! is_array( $inputs ) || ! array_key_exists( 'tipo_artigo', $inputs ) ) {
                continue;
            }

            // Prefer an existing canonical `article_type` when present; otherwise
            // normalize the legacy `tipo_artigo`. normalize() defaults to
            // SATELLITE for empty/unknown values.
            $source = $inputs['article_type'] ?? $inputs['tipo_artigo'];
            $inputs['article_type'] = Costudio_Article_Types::normalize( (string) $source );
            unset( $inputs['tipo_artigo'] );

            $wpdb->update(
                $table_prod,
                [ 'inputs' => wp_json_encode( $inputs ) ],
                [ 'id'     => (int) $row->id ],
                [ '%s' ],
                [ '%d' ]
            );

            $migrated++;
        }

        if ( $migrated > 0 && function_exists( 'costudio_log_debug' ) ) {
            costudio_log_debug(
                'db.migration_article_type_inputs',
                [ 'migrated' => $migrated ]
            );
        }
    }

    /**
     * v1.24 — Plugin options are mostly admin/pipeline settings. They should
     * not be autoloaded on every public WordPress request.
     */
    private static function migrate_v124(): void {
        global $wpdb;

        $like = $wpdb->esc_like( 'costudio_' ) . '%';

        $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$wpdb->options}
                 SET autoload = 'no'
                 WHERE option_name LIKE %s
                   AND autoload <> 'no'",
                $like
            )
        );
    }

    /**
     * Ensure high-value composite indexes exist for scaled environments.
     *
     * dbDelta covers most paths, but explicit checks make upgrades deterministic
     * on older installations with partial schemas.
     */
    /**
     * v1.12 — Garantias de schema para instâncias que ficaram desatualizadas.
     *
     * Problemas corrigidos:
     *  1. Coluna `updated_at` ausente em wpjw_costudio_productions — causava
     *     UPDATE a falhar silenciosamente, impedindo output_01 de ser gravado
     *     e prendendo o pipeline num loop infinito no Agente 01.
     *  2. Tabelas `costudio_logs` e `costudio_usage` ausentes em instâncias antigas
     *     onde o dbDelta não as chegou a criar (plugin instalado antes de v1.9).
     */
    private static function migrate_v112(): void {
        global $wpdb;

        $table_prod  = $wpdb->prefix . Costudio_Production::TABLE;
        $table_logs  = $wpdb->prefix . Costudio_Logger::TABLE;
        $table_usage = $wpdb->prefix . Costudio_Usage::TABLE;
        $charset     = $wpdb->get_charset_collate();

        // 1. Coluna updated_at em costudio_productions.
        $col = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME   = %s
                   AND COLUMN_NAME  = %s',
                $table_prod,
                'updated_at'
            )
        );
        if ( ! $col ) {
            $wpdb->query(
                "ALTER TABLE `{$table_prod}`
                 ADD COLUMN `updated_at` datetime DEFAULT CURRENT_TIMESTAMP AFTER `completed_at`"
            );
        }

        // 2. Tabela costudio_logs (criada em v1.9 — pode não existir em instâncias antigas).
        $logs_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
                $table_logs
            )
        );
        if ( ! $logs_exists ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            $sql_logs = "CREATE TABLE {$table_logs} (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                level varchar(10) NOT NULL DEFAULT 'info',
                message text NOT NULL,
                context_json longtext DEFAULT NULL,
                production_id bigint(20) unsigned DEFAULT NULL,
                agent tinyint(4) DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY level (level),
                KEY production_id (production_id),
                KEY created_at (created_at),
                KEY level_created_at (level, created_at)
            ) {$charset};";
            dbDelta( $sql_logs );
        }

        // 3. Tabela costudio_usage (criada em v1.9 — pode não existir em instâncias antigas).
        $usage_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
                $table_usage
            )
        );
        if ( ! $usage_exists ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            $sql_usage = "CREATE TABLE {$table_usage} (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                production_id bigint(20) unsigned NOT NULL DEFAULT 0,
                agent tinyint(4) NOT NULL DEFAULT 0,
                provider varchar(20) NOT NULL DEFAULT '',
                model varchar(100) NOT NULL DEFAULT '',
                input_tokens int unsigned NOT NULL DEFAULT 0,
                output_tokens int unsigned NOT NULL DEFAULT 0,
                cost_usd decimal(10,6) NOT NULL DEFAULT 0,
                latency_ms int unsigned NOT NULL DEFAULT 0,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY production_id (production_id),
                KEY provider (provider),
                KEY created_at (created_at),
                KEY provider_created_at (provider, created_at)
            ) {$charset};";
            dbDelta( $sql_usage );
        }
    }

    /**
     * v1.13 — Add attempts and heartbeat columns to productions table.
     */
    private static function migrate_v113(): void {
        global $wpdb;
        $table_prod = $wpdb->prefix . Costudio_Production::TABLE;

        $col_attempts = $wpdb->get_row( $wpdb->prepare( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", $table_prod, 'attempts' ) );
        if ( ! $col_attempts ) {
            $wpdb->query( "ALTER TABLE `{$table_prod}` ADD COLUMN `attempts` tinyint(4) NOT NULL DEFAULT 0 AFTER `edit_url`" );
        }

        $col_heartbeat = $wpdb->get_row( $wpdb->prepare( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", $table_prod, 'heartbeat' ) );
        if ( ! $col_heartbeat ) {
            $wpdb->query( "ALTER TABLE `{$table_prod}` ADD COLUMN `heartbeat` datetime DEFAULT NULL AFTER `attempts`" );
        }
    }

    /**
     * v1.20 — Add latency_ms column to costudio_usage table for per-agent metrics.
     */
    private static function migrate_v120(): void {
        global $wpdb;
        $table_usage = $wpdb->prefix . 'costudio_usage';

        $col_latency = $wpdb->get_row( $wpdb->prepare( 
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", 
            $table_usage, 
            'latency_ms' 
        ) );

        if ( ! $col_latency ) {
            $wpdb->query( "ALTER TABLE `{$table_usage}` ADD COLUMN `latency_ms` int unsigned NOT NULL DEFAULT 0 AFTER `cost_usd`" );
        }
    }

    /**
     * v1.14 — Persist the structured source list returned by /ai/search so the
     * References Renderer (post-Writer) can build a deterministic "References"
     * section instead of relying on the LLM to transcribe URLs.
     */
    private static function migrate_v114(): void {
        global $wpdb;
        $table_prod = $wpdb->prefix . Costudio_Production::TABLE;

        $col = $wpdb->get_row( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_prod,
            'sources_json'
        ) );

        if ( ! $col ) {
            $wpdb->query( "ALTER TABLE `{$table_prod}` ADD COLUMN `sources_json` longtext DEFAULT NULL AFTER `output_06`" );
        }
    }

    /**
     * v1.25 — Add images_json: the per-production image manifest that makes the
     * publish step resumable/idempotent. Each generated+uploaded image is
     * checkpointed here, so a retry after a server timeout reuses the existing
     * attachments instead of regenerating them (no double cost, no orphans).
     */
    private static function migrate_v125(): void {
        global $wpdb;
        $table_prod = $wpdb->prefix . Costudio_Production::TABLE;

        $col = $wpdb->get_row( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_prod,
            'images_json'
        ) );

        if ( ! $col ) {
            $wpdb->query( "ALTER TABLE `{$table_prod}` ADD COLUMN `images_json` longtext DEFAULT NULL AFTER `sources_json`" );
        }
    }

    /**
     * v1.26 — Persist stable timing boundaries for production agents.
     *
     * Both checks are deliberately idempotent because maybe_upgrade() may be
     * called more than once during deployments with concurrent admin requests.
     */
    private static function migrate_v126(): void {
        global $wpdb;

        $table_prod  = $wpdb->prefix . Costudio_Production::TABLE;
        $table_usage = $wpdb->prefix . Costudio_Usage::TABLE;

        $agent_started_col = $wpdb->get_row( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_prod,
            'current_agent_started_at'
        ) );

        if ( ! $agent_started_col ) {
            $wpdb->query(
                "ALTER TABLE `{$table_prod}`
                 ADD COLUMN `current_agent_started_at` datetime DEFAULT NULL AFTER `current_agent`"
            );
        }

        $usage_started_col = $wpdb->get_row( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_usage,
            'started_at'
        ) );

        if ( ! $usage_started_col ) {
            $wpdb->query(
                "ALTER TABLE `{$table_usage}`
                 ADD COLUMN `started_at` datetime DEFAULT NULL AFTER `latency_ms`"
            );
        }
    }

    /**
     * v1.28 — Add the optional durable BYOK job link to usage records.
     *
     * INFORMATION_SCHEMA checks avoid dbDelta attempting to recreate keys on
     * installations that receive concurrent upgrade requests.
     */
    private static function migrate_v128(): void {
        global $wpdb;

        $table_usage = $wpdb->prefix . Costudio_Usage::TABLE;
        $job_col = $wpdb->get_var( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND COLUMN_NAME = %s",
            $table_usage,
            'byok_job_id'
        ) );

        if ( ! $job_col ) {
            $wpdb->query(
                "ALTER TABLE `{$table_usage}`
                 ADD COLUMN `byok_job_id` char(36) DEFAULT NULL AFTER `started_at`"
            );
        }

        $job_index = $wpdb->get_var( $wpdb->prepare(
            "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = %s
               AND INDEX_NAME = %s",
            $table_usage,
            'byok_job_id'
        ) );

        if ( ! $job_index ) {
            $wpdb->query(
                "ALTER TABLE `{$table_usage}`
                 ADD UNIQUE KEY `byok_job_id` (`byok_job_id`)"
            );
        }
    }

	/**
	 * v1.29 — Track whether terminal BYOK usage has reached costudio_usage.
	 *
	 * The table installer normally adds this through dbDelta. The explicit
	 * INFORMATION_SCHEMA fallback also covers installations whose host defers
	 * or partially applies concurrent dbDelta upgrades.
	 */
	private static function migrate_v129(): void {
		global $wpdb;

		$table_jobs = $wpdb->prefix . Costudio_BYOK_Jobs::TABLE;
		$column = $wpdb->get_var( $wpdb->prepare(
			"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
			 WHERE TABLE_SCHEMA = DATABASE()
			   AND TABLE_NAME = %s
			   AND COLUMN_NAME = %s",
			$table_jobs,
			'usage_recorded_at'
		) );

		if ( ! $column ) {
			$wpdb->query(
				"ALTER TABLE `{$table_jobs}`
				 ADD COLUMN `usage_recorded_at` datetime DEFAULT NULL AFTER `applied_at`"
			);
		}
	}

    /**
     * Seed canonical agent prompts when a new install has no prompt version.
     */
    private static function cleanup_legacy_agent_prompts_v20(): void {
        if ( get_option( 'costudio_prompts_version' ) ) {
            return;
        }

        // 1) Wipe canonical prompt-related options before reseeding.
        for ( $n = 1; $n <= 7; $n++ ) {
            $suffix = sprintf( '%02d', $n );
            delete_option( 'costudio_agent_' . $suffix );
            delete_option( 'costudio_agent_' . $suffix . '_niche' );
            delete_option( 'costudio_agent_' . $suffix . '_extra' );
        }

        // Force the seeder to refetch (or, since we're seeding inline below,
        // just clear the markers so nothing claims a stale version).
        delete_option( 'costudio_prompts_version' );
        delete_option( 'costudio_prompts_locale' );

        // Seed prompts from ContentOS API via the Seeder.
        $locale = class_exists( 'Costudio_Admin' )
            ? Costudio_Admin::get_default_content_language()
            : 'pt_BR';

        if ( class_exists( 'Costudio_Agent_Seeder' ) ) {
            Costudio_Agent_Seeder::maybe_fetch( $locale, true );
        }

        if ( function_exists( 'costudio_log_debug' ) ) {
            costudio_log_debug( 'db.prompt_seed_triggered', [ 'locale' => $locale ] );
        }
    }

    private static function ensure_performance_indexes(): void {
        global $wpdb;

        $prod = $wpdb->prefix . Costudio_Production::TABLE;
        $plan = $wpdb->prefix . Costudio_Planning::TABLE;

        self::maybe_add_index( $prod, 'status_created_at', 'status, created_at' );
        self::maybe_add_index( $prod, 'site_status_created_at', 'site_key, status, created_at' );
        self::maybe_add_index( $prod, 'status_plan_id', 'status, plan_id' );

        self::maybe_add_index( $plan, 'status_planned_date', 'status, planned_date' );
        self::maybe_add_index( $plan, 'site_status_planned_date', 'site_key, status, planned_date' );
    }

    /**
     * Add a missing index. Safe to run multiple times.
     */
    private static function maybe_add_index( string $table, string $index_name, string $columns ): void {
        global $wpdb;

        $exists = $wpdb->get_var(
            $wpdb->prepare(
                'SHOW INDEX FROM `' . esc_sql( $table ) . '` WHERE Key_name = %s',
                $index_name
            )
        );

        if ( $exists ) {
            return;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table/index/column names are controlled constants.
        $wpdb->query( "ALTER TABLE `{$table}` ADD INDEX `{$index_name}` ({$columns})" );
    }

    // ── Default options ───────────────────────────────────────────────────────

    private static function seed_default_options(): void {
        // Delegates to Costudio_Agent_Seeder which fetches prompts from the
        // contentosapp-api. The `agents/*.txt` files act as a
        // last-resort offline fallback consumed only by
        // Costudio_Admin::get_agent_default_prompt_text().
        if ( ! class_exists( 'Costudio_Agent_Seeder' ) ) {
            require_once COSTUDIO_DIR . 'includes/class-costudio-agent-seeder.php';
        }
        $lang = class_exists( 'Costudio_Admin' ) ? Costudio_Admin::get_default_content_language() : 'pt_BR';
        Costudio_Agent_Seeder::maybe_fetch( $lang );
    }
}
