<?php
defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Export queries on plugin-owned tables; $table = $wpdb->prefix . constant; {$placeholders} is a pre-built list of %d tokens, not user data.

/**
 * Export helpers for production/history data.
 */
final class Costudio_Admin_Production_Exporter {

	private function __construct() {
	}

	/**
	 * @param object $production Costudio_Production row-like object.
	 * @return array{filename:string,content:string}|\WP_Error
	 */
	public static function export_production_zip( object $production ) {
		if ( ! class_exists( 'ZipArchive' ) ) {
			return new WP_Error( 'costudio_zip_unavailable', __( 'ZipArchive not available on this server.', 'contentosapp-studio' ) );
		}

		$inputs       = json_decode( $production->inputs ?? '{}', true );
		$keyword_slug = sanitize_title( $inputs['keyword'] ?? 'article' );
		$version      = $production->get_pipeline_version();
		$agent_names  = self::agent_export_names( $version );

		$tmp = tempnam( sys_get_temp_dir(), 'costudio_' );
		$zip = new ZipArchive();
		$zip->open( $tmp, ZipArchive::OVERWRITE );

		foreach ( $agent_names as $num => $name ) {
			$col     = $production->map_step_to_column( (int) $num );
			$content = $col ? ( $production->$col ?? '' ) : '';
			if ( ! empty( $content ) ) {
				$zip->addFromString( "{$num}_{$name}.md", $content );
			}
		}

		if ( ! empty( $production->links_internos ) ) {
			$zip->addFromString( '00_internal_links.md', $production->links_internos );
		}

		$zip->close();

		$encoded = base64_encode( (string) file_get_contents( $tmp ) );
		wp_delete_file( $tmp );

		return array(
			'filename' => "costudio_{$keyword_slug}.zip",
			'content'  => $encoded,
		);
	}

	/**
	 * @param array<int> $ids
	 * @return array{filename:string,content:string}
	 */
	public static function export_csv( string $tab, array $ids ): array {
		global $wpdb;
		$sites_repo = costudio_sites();
		$sites      = $sites_repo ? $sites_repo->get_all() : array();

		if ( 'planning' === $tab ) {
			$result = self::build_planning_csv_lines( $ids, $sites );
			$slug   = 'planning';
		} else {
			$result = self::build_history_csv_lines( $ids, $sites );
			$slug   = 'history';
		}

		return array(
			'filename' => 'costudio-' . $slug . '-' . wp_date( 'Y-m-d' ) . '.csv',
			'content'  => base64_encode( "\xEF\xBB\xBF" . implode( "\n", $result ) ),
		);
	}

	private static function agent_export_names( int $version ): array {
		if ( 7 === $version ) {
			return array(
				'01' => 'discoverer',
				'02' => 'strategist',
				'03' => 'researcher',
				'04' => 'writer',
				'05' => 'editorial-reviewer',
				'06' => 'visual-designer',
				'07' => 'social-media',
			);
		}

		return array(
			'01' => 'discoverer',
			'02' => 'strategist',
			'03' => 'researcher',
			'04' => 'writer',
			'05' => 'visual-designer',
			'06' => 'social-media',
		);
	}

	private static function build_planning_csv_lines( array $ids, array $sites ): array {
		global $wpdb;
		$plan_table = $wpdb->prefix . Costudio_Planning::TABLE;
		if ( empty( $ids ) ) {
			$rows = $wpdb->get_results(
				"SELECT id, title, keyword, site_key, planned_date, priority, status FROM {$plan_table} ORDER BY planned_date ASC, id ASC"
			);
		} else {
			$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
			$rows         = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT id, title, keyword, site_key, planned_date, priority, status FROM {$plan_table} WHERE id IN ({$placeholders}) ORDER BY planned_date ASC, id ASC",
					...$ids
				)
			);
		}

		$lines = array( '"#","Title / Keyword","Site","Planned Date","Priority","Status"' );
		foreach ( $rows as $row ) {
			$site_name = costudio_site_display_name( $sites[ $row->site_key ] ?? array(), $row->site_key );
			$date      = $row->planned_date ? wp_date( 'd/m/Y', strtotime( $row->planned_date ) ) : '';
			$lines[]   = sprintf(
				'"%s","%s","%s","%s","%s","%s"',
				$row->id,
				str_replace( '"', '""', $row->title ?: $row->keyword ),
				str_replace( '"', '""', $site_name ),
				$date,
				$row->priority ?? '',
				$row->status ?? ''
			);
		}

		return $lines;
	}

	private static function build_history_csv_lines( array $ids, array $sites ): array {
		global $wpdb;
		$prod_table = $wpdb->prefix . Costudio_Production::TABLE;
		if ( empty( $ids ) ) {
			$rows = $wpdb->get_results(
				"SELECT id, title, keyword, site_key, status, created_at, wp_post_url FROM {$prod_table} ORDER BY created_at DESC"
			);
		} else {
			$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
			$rows         = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT id, title, keyword, site_key, status, created_at, wp_post_url FROM {$prod_table} WHERE id IN ({$placeholders}) ORDER BY created_at DESC",
					...$ids
				)
			);
		}

		$lines = array( '"#","Title / Keyword","Site","Date","Status","Post URL"' );
		foreach ( $rows as $row ) {
			$site_name = costudio_site_display_name( $sites[ $row->site_key ] ?? array(), $row->site_key );
			$date      = wp_date( 'd/m/Y', strtotime( $row->created_at ) );
			$lines[]   = sprintf(
				'"%s","%s","%s","%s","%s","%s"',
				$row->id,
				str_replace( '"', '""', $row->title ?: $row->keyword ),
				str_replace( '"', '""', $site_name ),
				$date,
				$row->status ?? '',
				$row->wp_post_url ?? ''
			);
		}

		return $lines;
	}
}
