<?php
/**
 * Costudio_Admin_Ajax_Tables — Content Hub + Dashboard AJAX endpoints.
 *
 * Handles the unified Content Hub table (planning + productions), Dashboard
 * aggregated metrics, and bulk actions (delete, change priority).
 *
 * Extracted from Costudio_Admin as part of Task 2 (PR 8 — Tables split).
 *
 * @package Contentosapp_Studio
 * @since   3.2.0
 */

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB queries run against plugin-owned tables ($table = $wpdb->prefix . constant).

class Costudio_Admin_Ajax_Tables extends Costudio_Admin_Ajax_Base {

	/** @var self|null */
	private static ?self $instance = null;

	private function __construct() {}

	public static function get_instance(): self {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	public function register_hooks(): void {
		$this->register_ajax_actions(
			array(
				'costudio_get_central_table',
				'costudio_get_dashboard_data',
				'costudio_bulk_action',
			)
		);
	}

	/**
	 * Handle bulk actions from Content Hub (delete, priority).
	 */
	public function ajax_costudio_bulk_action(): void {
		$in = $this->read_request(
			array(
				'bulk_action' => 'key',
				'bulk_value'  => 'text',
				'items'       => 'array',
				'ids'         => 'array',
				'tab'         => 'key',
			)
		);

		$action = $in['bulk_action'];
		$val    = $in['bulk_value'];

		// Unified table sends items[] with {id, source}; legacy sends tab + ids[].
		$items = array();
		$posted_items = $in['items'];
		$posted_ids   = $in['ids'];
		if ( ! empty( $posted_items ) ) {
			foreach ( $posted_items as $item ) {
				$items[] = array(
					'id'     => (int) ( $item['id'] ?? 0 ),
					'source' => sanitize_key( $item['source'] ?? 'production' ),
				);
			}
		} elseif ( ! empty( $posted_ids ) ) {
			$tab    = $in['tab'] ?: 'planning';
			$source = ( 'planning' === $tab ) ? 'plan' : 'production';
			foreach ( $posted_ids as $raw_id ) {
				$items[] = array( 'id' => (int) $raw_id, 'source' => $source );
			}
		}

		$items = array_filter( $items, static function ( $it ) { return $it['id'] > 0; } );
		if ( empty( $items ) ) {
			wp_send_json_error( __( 'No items selected.', 'contentosapp-studio' ) );
		}

		if ( 'delete' === $action ) {
			foreach ( $items as $it ) {
				if ( 'plan' === $it['source'] ) {
					Costudio_Planning::delete( $it['id'] );
				} else {
					Costudio_Production::delete( $it['id'] );
				}
			}
			wp_send_json_success( __( 'Deleted successfully.', 'contentosapp-studio' ) );
		} elseif ( 'priority' === $action ) {
			if ( ! in_array( $val, array( 'high', 'medium', 'low' ), true ) ) {
				wp_send_json_error( __( 'Invalid priority.', 'contentosapp-studio' ) );
			}
			foreach ( $items as $it ) {
				if ( 'plan' === $it['source'] ) {
					Costudio_Planning::update( $it['id'], array( 'priority' => $val ) );
				} else {
					$prod = Costudio_Production::find( $it['id'] );
					if ( $prod && ! empty( $prod->plan_id ) ) {
						Costudio_Planning::update( (int) $prod->plan_id, array( 'priority' => $val ) );
					}
				}
			}
			wp_send_json_success( __( 'Priority updated.', 'contentosapp-studio' ) );
		}

		wp_send_json_error( __( 'Invalid action.', 'contentosapp-studio' ) );
	}

	/**
	 * AJAX: Central table (unified — merges Planning + Productions).
	 */
	public function ajax_costudio_get_central_table(): void {
		$in = $this->read_request(
			array(
				'paged'           => 'int',
				'per_page'        => 'int',
				'search'          => 'text',
				'site_key'        => 'text',
				'filter_status'   => 'text',
				'filter_priority' => 'text',
				'hub_tab'         => 'text',
				'orderby'         => 'text',
				'order'           => 'text',
			)
		);

		// Accept legacy 'tab' param but ignore it — always unified now.
		$paged           = max( 1, $in['paged'] );
		$per_page_raw    = $in['per_page'];
		$per_page        = in_array( $per_page_raw, array( 10, 20, 50 ), true ) ? $per_page_raw : 10;
		$search          = $in['search'];
		$site_key        = $in['site_key'];
		$filter_status   = $in['filter_status'];
		$filter_priority = $in['filter_priority'];
		$hub_tab         = $in['hub_tab'] ?: 'all';
		$orderby         = $in['orderby'] ?: 'created_at';
		$order           = strtoupper( $in['order'] ?: 'DESC' );
		if ( ! in_array( $order, array( 'ASC', 'DESC' ), true ) ) {
			$order = 'DESC';
		}

		$sites_repo = costudio_sites();
		$sites      = $sites_repo ? $sites_repo->get_all() : array();

		$plan_args = array( 'limit' => 1000, 'offset' => 0, 'search' => $search, 'site_key' => $site_key, 'orderby' => 'created_at', 'order' => 'DESC' );
		if ( $filter_priority && in_array( $filter_priority, array( 'high', 'medium', 'low' ), true ) ) {
			$plan_args['priority'] = $filter_priority;
		}
		$prod_args = array( 'limit' => 1000, 'offset' => 0, 'search' => $search, 'site_key' => $site_key, 'orderby' => 'created_at', 'order' => 'DESC', 'lightweight' => true );

		$plans       = Costudio_Planning::all( $plan_args );
		$productions = Costudio_Production::all( $prod_args );

		// Build a set of plan_ids that already have a production.
		$plan_ids_with_prod = array();
		foreach ( $productions as $prod ) {
			if ( ! empty( $prod->plan_id ) ) {
				$plan_ids_with_prod[ (int) $prod->plan_id ] = (int) $prod->id;
			}
		}

		$unified = array();

		// Plans without a linked production yet.
		foreach ( $plans as $plan ) {
			$pid = (int) $plan->id;
			if ( isset( $plan_ids_with_prod[ $pid ] ) ) {
				continue;
			}
			$unified[] = (object) array(
				'source'        => 'plan',
				'id'            => $pid,
				'title'         => $plan->title ?? '',
				'keyword'       => $plan->keyword ?? '',
				'site_key'      => $plan->site_key ?? '',
				'status'        => 'planned',
				'priority'      => $plan->priority ?? 'medium',
				'current_agent' => 0,
				'wp_post_id'    => 0,
				'error_agent'   => 0,
				'created_at'    => $plan->created_at ?? '',
				'planned_date'  => $plan->planned_date ?? '',
				'effective_date' => $plan->planned_date ?: ( $plan->created_at ?? '' ),
				'updated_at'    => $plan->created_at ?? '',
			);
		}

		foreach ( $productions as $prod ) {
			$raw_st = $prod->status ?? 'pending';
			$wp_id  = (int) ( $prod->wp_post_id ?? 0 );

			if ( 'completed' === $raw_st && $wp_id > 0 ) {
				$norm_st = 'published';
			} elseif ( 'completed' === $raw_st ) {
				$norm_st = 'draft';
			} elseif ( 'running' === $raw_st ) {
				$norm_st = 'producing';
			} elseif ( 'error' === $raw_st ) {
				$norm_st = 'error';
			} elseif ( 'pending' === $raw_st ) {
				$norm_st = 'queued';
			} else {
				$norm_st = $raw_st;
			}

			$unified[] = (object) array(
				'source'        => 'production',
				'id'            => (int) $prod->id,
				'title'         => $prod->title ?? '',
				'keyword'       => $prod->keyword ?? '',
				'site_key'      => $prod->site_key ?? '',
				'status'        => $norm_st,
				'priority'      => $prod->plan_priority ?? '',
				'current_agent' => (int) ( $prod->current_agent ?? 0 ),
				'wp_post_id'    => $wp_id,
				'error_agent'   => (int) ( $prod->error_agent ?? 0 ),
				'created_at'    => $prod->created_at ?? '',
				'planned_date'  => '',
				'effective_date' => $prod->created_at ?? '',
				'updated_at'    => $prod->completed_at ?? $prod->created_at ?? '',
			);
		}

		// Filter by Hub Tab; specific filter_status overrides hub_tab logic.
		if ( 'all' !== $hub_tab && empty( $filter_status ) ) {
			$unified = array_values( array_filter( $unified, static function ( $r ) use ( $hub_tab ) {
				if ( 'planned' === $hub_tab ) {
					return 'planned' === $r->status;
				}
				if ( 'producing' === $hub_tab ) {
					return in_array( $r->status, array( 'producing', 'queued', 'error', 'paused', 'cancelled' ), true );
				}
				if ( 'completed' === $hub_tab ) {
					return in_array( $r->status, array( 'published', 'draft' ), true );
				}
				return true;
			} ) );
		}

		if ( $filter_status ) {
			$unified = array_values( array_filter( $unified, static function ( $r ) use ( $filter_status ) {
				return $r->status === $filter_status;
			} ) );
		}

		if ( $filter_priority ) {
			$unified = array_values( array_filter( $unified, static function ( $r ) use ( $filter_priority ) {
				return $r->priority === $filter_priority;
			} ) );
		}

		usort( $unified, static function ( $a, $b ) use ( $orderby, $order ) {
			$val_a = $a->$orderby ?? '';
			$val_b = $b->$orderby ?? '';

			if ( 'priority' === $orderby ) {
				$p_map = array( 'high' => 1, 'medium' => 2, 'low' => 3, '' => 4 );
				$val_a = $p_map[ $val_a ] ?? 4;
				$val_b = $p_map[ $val_b ] ?? 4;
			} elseif ( 'date' === $orderby || 'created_at' === $orderby ) {
				$val_a = $a->effective_date ?? $a->created_at;
				$val_b = $b->effective_date ?? $b->created_at;
			}

			if ( $val_a == $val_b ) {
				return 0;
			}

			$res = ( $val_a < $val_b ) ? -1 : 1;
			return ( 'DESC' === $order ) ? ( $res * -1 ) : $res;
		} );

		$total = count( $unified );
		$pages = (int) ceil( $total / $per_page );
		$slice = array_slice( $unified, ( $paged - 1 ) * $per_page, $per_page );

		$agent_names = array(
			1 => __( 'Discoverer', 'contentosapp-studio' ),
			2 => __( 'Strategist', 'contentosapp-studio' ),
			3 => __( 'Researcher', 'contentosapp-studio' ),
			4 => __( 'Writer', 'contentosapp-studio' ),
			5 => __( 'Visual', 'contentosapp-studio' ),
			6 => __( 'Social', 'contentosapp-studio' ),
			7 => __( 'Publishing', 'contentosapp-studio' ),
		);

		ob_start();
		?>
		<table class="fos-card fos-central-table">
			<thead>
				<tr>
					<th class="fos-col-check" style="width:36px">
						<input type="checkbox" class="fos-select-all-rows" title="<?php esc_attr_e( 'Select all', 'contentosapp-studio' ); ?>">
					</th>
					<?php
					$cols = array(
						'title'    => __( 'Title', 'contentosapp-studio' ),
						'site_key' => __( 'Target site', 'contentosapp-studio' ),
						'priority' => __( 'Priority', 'contentosapp-studio' ),
						'date'     => __( 'Date', 'contentosapp-studio' ),
						'status'   => __( 'Status', 'contentosapp-studio' ),
					);
					foreach ( $cols as $key => $lbl ) :
						$is_active = $orderby === $key;
						$next_dir  = ( $is_active && 'ASC' === $order ) ? 'DESC' : 'ASC';
						$icon      = '';
						if ( $is_active ) {
							$icon = 'ASC' === $order ? ' ↑' : ' ↓';
						}
						$width = '';
						if ( 'site_key' === $key ) $width = '130px';
						if ( 'priority' === $key ) $width = '100px';
						if ( 'date' === $key )     $width = '90px';
						if ( 'status' === $key )   $width = '110px';
						?>
						<th<?php if ( $width ) { echo ' style="width:' . esc_attr( $width ) . '"'; } ?>>
							<a href="#" class="fos-sortable" data-orderby="<?php echo esc_attr( $key ); ?>" data-order="<?php echo esc_attr( $next_dir ); ?>">
								<?php echo esc_html( $lbl ); ?>
								<span class="fos-sort-icon"><?php echo esc_html( $icon ); ?></span>
							</a>
						</th>
					<?php endforeach; ?>
					<th style="width:110px"><?php esc_html_e( 'Actions', 'contentosapp-studio' ); ?></th>
				</tr>
			</thead>
			<tbody>
				<?php if ( empty( $slice ) ) : ?>
					<tr><td colspan="7" class="fos-central-empty"><?php esc_html_e( 'No records found.', 'contentosapp-studio' ); ?></td></tr>
				<?php else : ?>
					<?php foreach ( $slice as $row ) {
						Costudio_Admin_Tables::render_unified_row( $row, $sites, $agent_names );
					} ?>
				<?php endif; ?>
			</tbody>
		</table>
		<div class="fos-central-footer">
			<span class="fos-central-footer__count">
				<?php
				printf(
					/* translators: 1: displayed count, 2: total count */
					esc_html__( 'Showing %1$d of %2$d productions', 'contentosapp-studio' ),
					(int) count( $slice ),
					(int) $total
				);
				?>
			</span>
			<div class="fos-central-footer__perpage">
				<label for="fos-per-page" class="fos-central-footer__perpage-label">
					<?php esc_html_e( 'Per page:', 'contentosapp-studio' ); ?>
				</label>
				<select id="fos-per-page" class="fos-central-footer__perpage-select">
					<option value="10" <?php selected( $per_page, 10 ); ?>>10</option>
					<option value="20" <?php selected( $per_page, 20 ); ?>>20</option>
					<option value="50" <?php selected( $per_page, 50 ); ?>>50</option>
				</select>
			</div>
			<?php if ( $pages > 1 ) : ?>
				<div class="fos-central-pagination">
					<?php
					echo wp_kses_post( paginate_links( array(
						'base'      => admin_url( 'admin.php?page=costudio-studio&tab=hub&paged=%#%' ),
						'current'   => $paged,
						'total'     => $pages,
						'prev_text' => '&larr;',
						'next_text' => '&rarr;',
					) ) );
					?>
				</div>
			<?php endif; ?>
		</div>
		<?php
		$html = ob_get_clean();

		$kpis = Costudio_Admin_Tables::get_content_hub_kpis();

		$pub_diff   = (int) $kpis['pub_diff'];
		$pub_arrow  = $pub_diff > 0 ? '↑' : ( $pub_diff < 0 ? '↓' : '→' );
		$pub_trend  = (string) $kpis['pub_trend'];
		$pace_trend = (string) $kpis['pace_trend'];
		$pace_avg   = (int) $kpis['pace_avg'];
		$curr_m     = (int) $kpis['curr_month_articles'];
		$pub_trend_text_ajax = $pub_arrow . ' ' . ( 0 === $pub_diff ? '0' : sprintf( '%+d', $pub_diff ) );

		$hours_sub = sprintf(
			/* translators: %d: article count */
			__( '%d articles × 3.7 h saved', 'contentosapp-studio' ),
			(int) ( $kpis['articles_total_raw'] ?? 0 )
		);
		$pace_avg_line = sprintf(
			/* translators: %d: average articles per month */
			__( '3-month average: %d/mo.', 'contentosapp-studio' ),
			$pace_avg
		);

		wp_send_json_success(
			array(
				'html' => $html,
				'kpis' => array(
					'articles_total_fmt'  => $kpis['articles_total_fmt'],
					'published_month_fmt' => $kpis['published_month_fmt'],
					'pub_trend_class'     => 'fos-kpi-trend fos-kpi-trend--' . $pub_trend,
					'pub_trend_text'      => $pub_trend_text_ajax,
					'hours_saved_fmt'     => $kpis['hours_saved_fmt'],
					'hours_sub'           => $hours_sub,
					'pace_trend_class'    => 'fos-kpi-trend fos-kpi-trend--' . $pace_trend,
					'pace_trend_text'     => 'up' === $pace_trend
						? __( '↑ above', 'contentosapp-studio' )
						: ( 'down' === $pace_trend ? __( '↓ below', 'contentosapp-studio' ) : __( '→ on par', 'contentosapp-studio' ) ),
					'pace_avg_line'       => $pace_avg_line,
					'curr_month_articles' => (string) $curr_m,
				),
			)
		);
	}

	/**
	 * AJAX: Dashboard aggregated data.
	 */
	public function ajax_costudio_get_dashboard_data(): void {
		$in = $this->read_request( array( 'period' => 'key' ) );

		try {
			$this->do_get_dashboard_data( $in['period'] );
		} catch ( \Throwable $e ) {
			$context = array(
				'message' => $e->getMessage(),
				'file'    => $e->getFile(),
				'line'    => $e->getLine(),
			);

			if ( function_exists( 'costudio_log' ) ) {
				costudio_log( 'dashboard.ajax_failed', $context, 'error' );
			} elseif ( function_exists( 'costudio_log_debug' ) ) {
				costudio_log_debug( 'dashboard.ajax_fatal', $context );
			}

			wp_send_json_error( [
				'message' => __( 'Could not load dashboard data right now. Please refresh and check the logs if it persists.', 'contentosapp-studio' ),
			], 500 );
		}
	}

	private function do_get_dashboard_data( string $period ): void {
		global $wpdb;

		// ── 0. Period window ─────────────────────────────────────────────────
		$allowed_periods = array( 'this_month', 'last_month', 'last_90', 'all' );
		if ( ! in_array( $period, $allowed_periods, true ) ) {
			$period = 'this_month';
		}

		try {
			$tz  = wp_timezone();
			$now = new \DateTimeImmutable( 'now', $tz );
		} catch ( \Throwable $e ) {
			$tz  = new \DateTimeZone( 'UTC' );
			$now = new \DateTimeImmutable( 'now', $tz );
		}

		$from         = null;
		$to           = null;
		$prev_from    = null;
		$prev_to      = null;
		$period_label = '';

		$main_label   = '';

		if ( 'this_month' === $period ) {
			$from         = $now->modify( 'first day of this month' )->setTime( 0, 0, 0 );
			$to           = $now->modify( 'last day of this month' )->setTime( 23, 59, 59 );
			$prev_from    = $from->modify( '-1 month' );
			$prev_to      = $prev_from->modify( 'last day of this month' )->setTime( 23, 59, 59 );
			$period_label = __( 'vs last month', 'contentosapp-studio' );
			$main_label   = __( 'Published this month', 'contentosapp-studio' );
		} elseif ( 'last_month' === $period ) {
			$from         = $now->modify( 'first day of last month' )->setTime( 0, 0, 0 );
			$to           = $from->modify( 'last day of this month' )->setTime( 23, 59, 59 );
			$prev_from    = $from->modify( '-1 month' );
			$prev_to      = $prev_from->modify( 'last day of this month' )->setTime( 23, 59, 59 );
			$period_label = __( 'vs previous month', 'contentosapp-studio' );
			$main_label   = __( 'Published last month', 'contentosapp-studio' );
		} elseif ( 'last_90' === $period ) {
			$to           = $now->setTime( 23, 59, 59 );
			$from         = $now->modify( '-89 days' )->setTime( 0, 0, 0 );
			$prev_to      = $from->modify( '-1 second' );
			$prev_from    = $prev_to->modify( '-89 days' )->setTime( 0, 0, 0 );
			$period_label = __( 'vs previous 90 days', 'contentosapp-studio' );
			$main_label   = __( 'Last 90 days', 'contentosapp-studio' );
		} else {
			$from         = null;
			$to           = null;
			$period_label = '';
			$main_label   = __( 'All time articles', 'contentosapp-studio' );
		}

		$from_sql      = $from ? $from->format( 'Y-m-d H:i:s' ) : null;
		$to_sql        = $to   ? $to->format( 'Y-m-d H:i:s' )   : null;
		$prev_from_sql = $prev_from ? $prev_from->format( 'Y-m-d H:i:s' ) : null;
		$prev_to_sql   = $prev_to   ? $prev_to->format( 'Y-m-d H:i:s' )   : null;

		$today_ymd     = $now->format( 'Y-m-d' );
		$yesterday_ymd = $now->modify( '-1 day' )->format( 'Y-m-d' );

		// ── 1. Plan & License ────────────────────────────────────────────────
		$license_service = Costudio_Standalone_License::get_instance();
		$monthly_count   = Costudio_Standalone_License::get_monthly_count();
		$prod_limit      = $license_service->get_api_production_limit();
		$site_limit      = __( 'Unlimited', 'contentosapp-studio' );

		// ── 2. Settings ──────────────────────────────────────────────────────
		$settings       = costudio_settings();
		$ai_provider    = $settings ? $settings->get( 'ai_provider' ) : 'gemini';
		$ai_model       = $settings ? $settings->get( 'ai_model' ) : '';
		$ai_key_set     = $settings ? ! empty( $settings->get( 'ai_api_key' ) ) : false;
		$image_provider = $settings ? $settings->get( 'ai_image_provider' ) : '';
		$image_model    = $settings ? $settings->get( 'ai_image_model' ) : '';
		$image_key_set  = $settings ? ! empty( $settings->get( 'ai_image_api_key' ) ) : false;
		$image_mode     = $settings ? $settings->get( 'costudio_image_mode', 'generate' ) : 'generate';

		$ai_mode   = $settings ? $settings->get( 'ai_mode', '' ) : '';
		$ai_labels  = array( 'gemini' => 'Google Gemini', 'openai' => 'OpenAI GPT-4o', 'claude' => 'Anthropic Claude', 'contentos' => 'ContentOS Auto' );
		$img_labels = array( 'gemini_imagen' => 'Google Imagen', 'openai_dalle' => 'OpenAI DALL-E', 'contentos' => 'ContentOS Auto' );
		$img_health_label = __( 'Not configured', 'contentosapp-studio' );
		$img_health_sub   = __( 'Configure image generation in Settings.', 'contentosapp-studio' );
		$img_health_model = '';
		$img_health_ok    = false;

		if ( 'none' === $image_mode ) {
			$img_health_label = __( 'No images', 'contentosapp-studio' );
			$img_health_sub   = __( 'Articles are published without generated images.', 'contentosapp-studio' );
			$img_health_ok    = true;
		} elseif ( 'placeholder' === $image_mode ) {
			$img_health_label = __( 'Suggest placements', 'contentosapp-studio' );
			$img_health_sub   = __( 'The Designer suggests image positions; no image API is used.', 'contentosapp-studio' );
			$img_health_ok    = true;
		} elseif ( 'contentos' === $image_provider ) {
			$img_health_label = __( 'ContentOS Auto', 'contentosapp-studio' );
			$img_health_sub   = __( 'Uses ContentOS Auto service quota. No API key is required.', 'contentosapp-studio' );
			$img_health_model = $image_model;
			$img_health_ok    = true;
		} elseif ( $image_provider && $image_key_set ) {
			$img_health_label = $img_labels[ $image_provider ] ?? ucfirst( (string) $image_provider );
			$img_health_sub   = __( 'BYOK image provider configured.', 'contentosapp-studio' );
			$img_health_model = $image_model;
			$img_health_ok    = true;
		} elseif ( $image_provider ) {
			$img_health_label = $img_labels[ $image_provider ] ?? ucfirst( (string) $image_provider );
			$img_health_sub   = __( 'API key pending.', 'contentosapp-studio' );
			$img_health_model = $image_model;
		}

		// ── 3. Objects ───────────────────────────────────────────────────────
		$sites_repo  = Costudio_Standalone_Sites::get_instance();
		$sites_map   = $sites_repo->get_all();
		$sites_count = count( $sites_map );
		$operable_sites_count = count(
			array_filter(
				array_keys( $sites_map ),
				static function ( $site_key ) use ( $sites_repo ) {
					return $sites_repo->is_site_operable( (string) $site_key );
				}
			)
		);

		$running_prods = Costudio_Production::all( array( 'status' => 'running', 'limit' => 1, 'lightweight' => true ) );
		$live_row      = ! empty( $running_prods ) ? $running_prods[0] : null;
		$live_prod     = $live_row && ! empty( $live_row->id )
			? Costudio_Production::get_by_id( (int) $live_row->id )
			: null;

		$plan_table    = $wpdb->prefix . Costudio_Planning::TABLE;
		$planned_items = $wpdb->get_results( "SELECT * FROM {$plan_table} WHERE status = 'planned' ORDER BY CASE priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 ELSE 4 END ASC, planned_date ASC, id DESC LIMIT 5" );
		$next_up       = ! empty( $planned_items ) ? $planned_items[0] : null;

		// ── 4. Metrics ───────────────────────────────────────────────────────
		$total_articles = Costudio_Production::count_all();
		$prod_table     = $wpdb->prefix . Costudio_Production::TABLE;

		// Count articles in current period (published/completed)
		if ( $from_sql && $to_sql ) {
			$period_articles = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(*) FROM {$prod_table} WHERE status = 'completed' AND completed_at BETWEEN %s AND %s",
				$from_sql, $to_sql
			) );
			$pub_current = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(*) FROM {$prod_table} WHERE status = 'completed' AND wp_post_id > 0 AND completed_at BETWEEN %s AND %s",
				$from_sql, $to_sql
			) );
		} else {
			$period_articles = $total_articles;
			$pub_current     = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$prod_table} WHERE status = 'completed' AND wp_post_id > 0" );
		}

		$hours_saved = (int) round( $period_articles * 3.7 );

		if ( $prev_from_sql && $prev_to_sql ) {
			$pub_prev = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(*) FROM {$prod_table} WHERE status = 'completed' AND wp_post_id > 0 AND completed_at BETWEEN %s AND %s",
				$prev_from_sql, $prev_to_sql
			) );
		} else {
			$pub_prev = 0;
		}
		$status_rows = $wpdb->get_results( "SELECT status, COUNT(*) AS total FROM {$prod_table} GROUP BY status", ARRAY_A );
		$status_map  = array();
		foreach ( (array) $status_rows as $row ) {
			$k = isset( $row['status'] ) ? (string) $row['status'] : '';
			$v = isset( $row['total'] )  ? (int) $row['total']     : 0;
			if ( '' !== $k ) {
				$status_map[ $k ] = $v;
			}
		}
		$ops_metrics    = class_exists( 'Costudio_Runner' )    ? Costudio_Runner::get_perf_metrics()    : array();
		$slot_status    = class_exists( 'Costudio_Runner' )    ? Costudio_Runner::get_slot_status()     : array();
		$telemetry_last = class_exists( 'Costudio_Telemetry' ) ? Costudio_Telemetry::get_last_status() : array();

		// Fetch recent productions (all statuses) and planning items, merge into one list.
		$recent_prod_args = array(
			'limit'       => 10,
			'lightweight' => true,
			'orderby'     => 'updated_at',
			'order'       => 'DESC',
		);
		$recent_productions = Costudio_Production::all( $recent_prod_args );

		$planning_table  = $wpdb->prefix . 'costudio_planning';
		$recent_planning = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, title, keyword, site_key, priority, created_at FROM {$planning_table} WHERE status = %s ORDER BY created_at DESC LIMIT %d",
				'planned',
				10
			)
		);

		$recent_unified = array();
		$recent_production_plan_ids = array();
		$recent_production_keys     = array();
		$recent_item_key = static function( $site_key, $value ) {
			$value = trim( strtolower( remove_accents( (string) $value ) ) );
			$value = preg_replace( '/\s+/', ' ', $value );
			if ( '' === $value ) {
				return '';
			}
			return strtolower( trim( (string) $site_key ) ) . '|' . $value;
		};
		foreach ( (array) $recent_productions as $p ) {
			$p_status = (string) ( $p->status ?? 'completed' );
			$p_created = (string) ( $p->created_at ?? '' );
			$p_updated = (string) ( $p->updated_at ?? '' );
			$p_completed = (string) ( $p->completed_at ?? '' );
			$p_ts     = 'completed' === $p_status
				? ( $p_completed ?: $p_updated ?: $p_created )
				: ( $p_updated ?: $p_created );
			$recent_unified[] = array( 'source' => 'production', 'obj' => $p, 'ts' => (string) $p_ts );

			if ( ! empty( $p->plan_id ) ) {
				$recent_production_plan_ids[ (int) $p->plan_id ] = true;
			}

			foreach ( array( $p->title ?? '', $p->keyword ?? '' ) as $candidate ) {
				$key = $recent_item_key( $p->site_key ?? '', $candidate );
				if ( '' !== $key ) {
					$recent_production_keys[ $key ] = true;
				}
			}
		}
		foreach ( (array) $recent_planning as $pl ) {
			if ( isset( $recent_production_plan_ids[ (int) $pl->id ] ) ) {
				continue;
			}

			$planning_keys = array_filter( array(
				$recent_item_key( $pl->site_key ?? '', $pl->title ?? '' ),
				$recent_item_key( $pl->site_key ?? '', $pl->keyword ?? '' ),
			) );

			if ( array_intersect_key( array_flip( $planning_keys ), $recent_production_keys ) ) {
				continue;
			}

			$recent_unified[] = array( 'source' => 'planning', 'obj' => $pl, 'ts' => (string) ( $pl->created_at ?? '' ) );
		}
		usort( $recent_unified, function( $a, $b ) { return strcmp( $b['ts'], $a['ts'] ); } );
		$recent = array_slice( $recent_unified, 0, 5 );
		$queued_productions = Costudio_Production::all( array(
			'status'      => 'pending',
			'limit'       => 5,
			'lightweight' => true,
			'orderby'     => 'created_at',
			'order'       => 'ASC',
		) );

		$version     = 7; // Dashboard is usually for modern productions
		$agent_defs  = Costudio_Pipeline::get_agent_definitions( $version );
		$agent_names = array();
		foreach ( $agent_defs as $id => $name ) {
			$agent_names[ $id ] = $name;
		}

		// Sprint B.2 — Extra Quotas (addon) snapshot for the dashboard.
		// Data origin is the cached transient populated by sync_remote_usage();
		// no extra remote call here.
		//
		// Math model:
		//   monthly_count        — ContentOS Auto productions debited from service quota
		//   addon.used_this_month — ContentOS Auto productions debited from extras this month
		//   addon.balance        — current extras saldo (persistent, doesn't reset)
		//
		//   monthly_total      = monthly_count + used_this_month   → "produzidas no mês"
		//   effective_total    = limit + balance + used_this_month → capacidade efetiva do serviço no mês
		//   effective_remaining = (limit - monthly_count) + balance → saldo restante do serviço gerenciado
		$addon              = Costudio_Standalone_License::get_addon_balance();
		$addon_enabled      = ! empty( $addon['enabled'] );
		$addon_balance      = $addon_enabled ? (int) $addon['balance']         : 0;
		$addon_used_month   = $addon_enabled ? (int) $addon['used_this_month'] : 0;
		$monthly_remaining  = max( 0, (int) $prod_limit - (int) $monthly_count );
		$byok_count         = method_exists( 'Costudio_Standalone_License', 'get_monthly_byok_production_count' )
			? Costudio_Standalone_License::get_monthly_byok_production_count()
			: Costudio_Standalone_License::get_monthly_byok_count();
		$monthly_total      = (int) $monthly_count + $addon_used_month + $byok_count;
		$effective_total    = (int) $prod_limit + $addon_balance + $addon_used_month;
		$effective_remaining = $monthly_remaining + $addon_balance;
		$live_current_agent = $live_prod ? (int) ( $live_prod->current_agent ?? 1 ) : 0;
		$live_agent_qty     = $live_prod ? (int) ( $live_prod->get_pipeline_version() === 7 ? 7 : 6 ) : 0;
		$live_all_done      = false;
		if ( $live_prod && $live_agent_qty > 0 ) {
			$live_all_done = true;
			for ( $i = 1; $i <= $live_agent_qty; $i++ ) {
				$col = method_exists( $live_prod, 'map_step_to_column' )
					? $live_prod->map_step_to_column( $i )
					: sprintf( 'output_%02d', $i );
				if ( empty( $live_prod->$col ) ) {
					$live_all_done = false;
					break;
				}
			}
		}
		$live_phase         = ( $live_prod && ( $live_current_agent > $live_agent_qty || $live_all_done ) ) ? 'publishing' : 'agent';
		$live_agent_name    = $live_prod
			? ( 'publishing' === $live_phase ? __( 'Finalizando publicação', 'contentosapp-studio' ) : ( $agent_names[ $live_current_agent ] ?? '' ) )
			: '';

		wp_send_json_success( array(
			'plan' => array(
				'service_plan_active' => true,
				'service_plan_name'   => strtoupper( $license_service->get_plan() ),
				'monthly_count'       => $monthly_count,
				'monthly_total'       => $monthly_total,
				'effective_total'     => $effective_total,
				'limit'               => $prod_limit,
				'site_limit'          => $site_limit,
				'byok_count'          => $byok_count,
				'byok_limit'          => 'unlimited',
				'addon'               => array(
					'enabled'         => $addon_enabled,
					'balance'         => $addon_balance,
					'used_this_month' => $addon_used_month,
				),
				'effective_remaining' => $effective_remaining,
			),
			'live' => $live_prod ? array(
				'id'            => $live_prod->id,
				'title'         => $live_prod->title ?: $live_prod->keyword,
				'site'          => isset( $sites_map[ $live_prod->site_key ] ) ? costudio_site_display_name( $sites_map[ $live_prod->site_key ], $live_prod->site_key ) : $live_prod->site_key,
				'current_agent' => $live_current_agent,
				'agent_qty'     => $live_agent_qty,
				'phase'         => $live_phase,
				'agent_name'    => $live_agent_name,
				'url'           => admin_url( 'admin.php?page=costudio-detalhe&id=' . $live_prod->id ),
			) : null,
			'next_up' => $next_up ? array(
				'id'       => $next_up->id,
				'keyword'  => $next_up->keyword ?: $next_up->title,
				'priority' => $next_up->priority,
				'site'     => isset( $sites_map[ $next_up->site_key ] ) ? costudio_site_display_name( $sites_map[ $next_up->site_key ], $next_up->site_key ) : $next_up->site_key,
				'category' => $next_up->category,
				'url'      => admin_url( 'admin.php?page=costudio-studio&tab=producao&plan_id=' . $next_up->id ),
			) : null,
			'metrics' => array(
				'total'          => $total_articles,
				'published'      => $pub_current,
				'diff'           => $pub_current - $pub_prev,
				'hours'          => $hours_saved,
				'period'         => $period,
				'period_compare' => $period_label,
				'main_label'     => $main_label,
				'period_articles' => $period_articles,
			),
			'ops' => array(
				'backlog_pending' => (int) ( $status_map['pending'] ?? 0 ),
				'running'         => (int) ( $status_map['running'] ?? 0 ),
				'paused'          => (int) ( $status_map['paused'] ?? 0 ),
				'errors'          => (int) ( $status_map['error'] ?? 0 ),
				'perf'            => $ops_metrics,
				'slots'           => $slot_status,
				'telemetry_last'  => $telemetry_last,
			),
			'health' => array(
				'ai' => array(
					'label' => $ai_mode === 'contentos'
						? __( 'ContentOS Auto', 'contentosapp-studio' )
						: ( $ai_labels[ $ai_provider ] ?? ucfirst( (string) $ai_provider ) ),
					'model' => $ai_mode === 'contentos' ? '' : $ai_model,
					'ok'    => $ai_mode === 'contentos' || $ai_key_set,
				),
				'img' => array(
					'mode'        => $image_mode,
					'label'       => $img_health_label,
					'model'       => $img_health_model,
					'sub'         => $img_health_sub,
					'ok'          => $img_health_ok,
				),
				'sites'  => array( 'count' => $operable_sites_count, 'total' => $sites_count, 'limit' => $site_limit ),
				'service_plan_active' => true,
			),
			'recent' => array_map( function ( $entry ) use ( $sites_map, $today_ymd, $yesterday_ymd ) {
				$source = $entry['source'];
				$obj    = $entry['obj'];
				$ref_ts = $entry['ts'];

				$date_ymd = $ref_ts ? substr( $ref_ts, 0, 10 ) : '';
				if ( $date_ymd === $today_ymd ) {
					$date_label = __( 'Today', 'contentosapp-studio' );
				} elseif ( $date_ymd === $yesterday_ymd ) {
					$date_label = __( 'Yesterday', 'contentosapp-studio' );
				} elseif ( $ref_ts ) {
					$date_label = mysql2date( 'd/m/Y', $ref_ts );
				} else {
					$date_label = '—';
				}

				if ( 'planning' === $source ) {
					$priority = isset( $obj->priority ) && $obj->priority ? (string) $obj->priority : '';
					return array(
						'id'        => (int) $obj->id,
						'title'     => $obj->title ?: $obj->keyword,
						'site'      => isset( $sites_map[ $obj->site_key ] ) ? costudio_site_display_name( $sites_map[ $obj->site_key ], $obj->site_key ) : (string) $obj->site_key,
						'date'      => $date_label,
						'priority'  => $priority,
						'status'    => 'planned',
						'published' => false,
						'url'       => admin_url( 'admin.php?page=costudio-studio&tab=producao&plan_id=' . (int) $obj->id ),
					);
				}

				$status   = (string) ( $obj->status ?? 'completed' );
				$priority = isset( $obj->plan_priority ) && $obj->plan_priority ? (string) $obj->plan_priority : '';
				return array(
					'id'        => $obj->id,
					'title'     => $obj->title ?: $obj->keyword,
					'site'      => isset( $sites_map[ $obj->site_key ] ) ? costudio_site_display_name( $sites_map[ $obj->site_key ], $obj->site_key ) : $obj->site_key,
					'date'      => $date_label,
					'priority'  => $priority,
					'status'    => $status,
					'published' => ! empty( $obj->wp_post_id ) && (int) $obj->wp_post_id > 0,
					'url'       => admin_url( 'admin.php?page=costudio-detalhe&id=' . $obj->id ),
				);
			}, $recent ),
			'queue' => array(
				'pending_total' => (int) ( $status_map['pending'] ?? 0 ),
				'running_total' => (int) ( $status_map['running'] ?? 0 ),
				'items'         => array_map( function ( $item, $idx ) use ( $sites_map, $agent_names ) {
					$current_agent = max( 1, (int) ( $item->current_agent ?? 1 ) );
					$position      = class_exists( 'Costudio_Production_Queue' )
						? Costudio_Production_Queue::queue_position( (int) $item->id )
						: ( $idx + 1 );
					$created_at    = (string) ( $item->created_at ?? '' );

					return array(
						'id'            => (int) $item->id,
						'title'         => $item->title ?: $item->keyword,
						'site'          => isset( $sites_map[ $item->site_key ] ) ? costudio_site_display_name( $sites_map[ $item->site_key ], $item->site_key ) : $item->site_key,
						'priority'      => isset( $item->plan_priority ) ? (string) $item->plan_priority : '',
						'position'      => max( 1, (int) $position ),
						'current_agent' => $current_agent,
						/* translators: %02d: agent number (zero-padded, e.g. 01, 02) */
						'agent_name'    => $agent_names[ $current_agent ] ?? sprintf(
							/* translators: %02d: two-digit agent number. */
							__( 'Agent %02d', 'contentosapp-studio' ),
							$current_agent
						),
						'created_at'    => $created_at ? wp_date( 'H:i', strtotime( $created_at . ' UTC' ) ) : '',
						'url'           => admin_url( 'admin.php?page=costudio-detalhe&id=' . (int) $item->id ),
					);
				}, (array) $queued_productions, array_keys( (array) $queued_productions ) ),
			),
		) );
	}
}
