<?php

namespace SerpIQ\Api\Controllers;

use SerpIQ\Content\Content_Refresh;
use SerpIQ\Links\Internal_Opportunities;

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

/**
 * CSV export endpoints for queries, pages, opportunities, and alerts.
 * Returns JSON: { filename, content } with content base64-encoded CSV (UTF-8 BOM for Excel).
 */
final class Reports_Controller {
	private const EXPORT_LIMIT = 10000;

	private static ?Reports_Controller $instance = null;

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

	public function register_routes(): void {
		register_rest_route(
			'serpiq/v1',
			'/reports/queries-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'queries_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array( 'clicks', 'impressions', 'ctr', 'position', 'query' ) ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/reports/pages-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'pages_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array( 'clicks', 'impressions', 'ctr', 'position', 'page_url' ) ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/reports/opportunities-high-imp-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'opportunities_high_imp_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array() ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/reports/opportunities-position-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'opportunities_position_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array() ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/reports/alerts-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'alerts_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array() ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/reports/decay-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'decay_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array() ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/reports/content-refresh-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'content_refresh_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array() ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/reports/internal-linking-csv',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'internal_linking_csv' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_args( array() ),
			)
		);
	}

	public function validate_date_arg( mixed $value, \WP_REST_Request $request, string $param ): bool {
		unset( $request, $param );
		$v = is_scalar( $value ) ? (string) $value : '';
		$v = trim( $v );
		if ( '' === $v ) {
			return true;
		}
		return (bool) preg_match( '/^\d{4}-\d{2}-\d{2}$/', $v );
	}

	/**
	 * Common args for CSV exports.
	 *
	 * @param string[] $allowed_order
	 * @return array<string, array<string, mixed>>
	 */
	private function common_args( array $allowed_order ): array {
		$args = array(
			'start'   => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field', 'validate_callback' => array( $this, 'validate_date_arg' ) ),
			'end'     => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field', 'validate_callback' => array( $this, 'validate_date_arg' ) ),
			'search'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
			'country' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
			'device'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
		);
		if ( ! empty( $allowed_order ) ) {
			$args['orderBy'] = array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_key',
				'enum'              => $allowed_order,
			);
			$args['orderDir'] = array(
				'required'          => false,
				'type'              => 'string',
				'default'           => 'DESC',
				'sanitize_callback' => 'sanitize_text_field',
				'enum'              => array( 'ASC', 'DESC', 'asc', 'desc' ),
			);
		}
		return $args;
	}

	public function queries_csv( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start   = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end     = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$search  = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$order_by  = sanitize_key( (string) $request->get_param( 'orderBy' ) );
		$order_dir = strtoupper( (string) $request->get_param( 'orderDir' ) );
		$order_dir = in_array( $order_dir, array( 'ASC', 'DESC' ), true ) ? $order_dir : 'DESC';
		$allowed   = array( 'clicks', 'impressions', 'ctr', 'position', 'query' );
		if ( ! in_array( $order_by, $allowed, true ) ) {
			$order_by = 'clicks';
		}

		$table  = $wpdb->prefix . 'serpiq_queries';
		$where  = 'date BETWEEN %s AND %s';
		$params = array( $start, $end );
		if ( '' !== $country ) {
			$where .= ' AND country = %s';
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$where .= ' AND device = %s';
			$params[] = $device;
		}
		if ( '' !== $search ) {
			$where .= ' AND query LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		$sql      = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS position
			FROM %i
			WHERE {$where}
			GROUP BY query
			ORDER BY %i {$order_dir}
			LIMIT %d
		";
		$prepare_params = array_merge( array( $table ), $params, array( $order_by ), array( self::EXPORT_LIMIT ) );
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $prepare_params ), ARRAY_A );
		$rows = is_array( $rows ) ? $rows : array();

		$headers = array( 'query', 'clicks', 'impressions', 'ctr', 'position' );
		$csv     = $this->build_csv( $headers, $rows );
		return $this->csv_response( $csv, 'serpiq-queries-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	public function pages_csv( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start   = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end     = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$search  = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$order_by  = sanitize_key( (string) $request->get_param( 'orderBy' ) );
		$order_dir = strtoupper( (string) $request->get_param( 'orderDir' ) );
		$order_dir = in_array( $order_dir, array( 'ASC', 'DESC' ), true ) ? $order_dir : 'DESC';
		$allowed   = array( 'clicks', 'impressions', 'ctr', 'position', 'page_url' );
		if ( ! in_array( $order_by, $allowed, true ) ) {
			$order_by = 'clicks';
		}

		$table  = $wpdb->prefix . 'serpiq_pages';
		$where  = 'date BETWEEN %s AND %s';
		$params = array( $start, $end );
		if ( '' !== $country ) {
			$where .= ' AND country = %s';
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$where .= ' AND device = %s';
			$params[] = $device;
		}
		if ( '' !== $search ) {
			$where .= ' AND page_url LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		$sql      = "
			SELECT
				page_url,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS position
			FROM %i
			WHERE {$where}
			GROUP BY page_url
			ORDER BY %i {$order_dir}
			LIMIT %d
		";
		$prepare_params = array_merge( array( $table ), $params, array( $order_by ), array( self::EXPORT_LIMIT ) );
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $prepare_params ), ARRAY_A );
		$rows = is_array( $rows ) ? $rows : array();

		$headers = array( 'page_url', 'clicks', 'impressions', 'ctr', 'position' );
		$csv     = $this->build_csv( $headers, $rows );
		return $this->csv_response( $csv, 'serpiq-pages-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	public function opportunities_high_imp_csv( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start   = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end     = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );
		$min_impressions = max( 0, (int) $request->get_param( 'min_impressions' ) );
		if ( $min_impressions <= 0 ) {
			$min_impressions = 100;
		}
		$max_ctr_pct = (float) $request->get_param( 'max_ctr_pct' );
		if ( $max_ctr_pct <= 0 || $max_ctr_pct > 100 ) {
			$max_ctr_pct = 5.0;
		}
		$max_ctr_decimal = $max_ctr_pct / 100;

		$table  = $wpdb->prefix . 'serpiq_queries';
		$params = array( $start, $end );
		if ( '' !== $country ) {
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$params[] = $device;
		}
		$params[] = $min_impressions;
		$params[] = $max_ctr_decimal;
		$params[] = self::EXPORT_LIMIT;

		$sql = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS position
			FROM %i
			WHERE date BETWEEN %s AND %s
			" . ( '' !== $country ? ' AND country = %s' : '' ) . "
			" . ( '' !== $device ? ' AND device = %s' : '' ) . "
			GROUP BY query
			HAVING COALESCE(SUM(impressions),0) >= %d
			   AND ( COALESCE(SUM(clicks),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) <= %f
			ORDER BY impressions DESC
			LIMIT %d
		";
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $table ), $params ) ), ARRAY_A );
		$rows = is_array( $rows ) ? $rows : array();

		$headers = array( 'query', 'clicks', 'impressions', 'ctr', 'position' );
		$csv     = $this->build_csv( $headers, $rows );
		return $this->csv_response( $csv, 'serpiq-opportunities-high-imp-low-ctr-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	public function opportunities_position_csv( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start   = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end     = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );
		$min_impressions = max( 0, (int) $request->get_param( 'min_impressions' ) );
		if ( $min_impressions <= 0 ) {
			$min_impressions = 10;
		}

		$table  = $wpdb->prefix . 'serpiq_queries';
		$params = array( $start, $end );
		if ( '' !== $country ) {
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$params[] = $device;
		}
		$params[] = $min_impressions;
		$params[] = self::EXPORT_LIMIT;

		$sql = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS position
			FROM %i
			WHERE date BETWEEN %s AND %s
			" . ( '' !== $country ? ' AND country = %s' : '' ) . "
			" . ( '' !== $device ? ' AND device = %s' : '' ) . "
			GROUP BY query
			HAVING COALESCE(SUM(impressions),0) >= %d
			   AND ( COALESCE(SUM(position * impressions),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) BETWEEN 8 AND 20
			ORDER BY impressions DESC
			LIMIT %d
		";
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $table ), $params ) ), ARRAY_A );
		$rows = is_array( $rows ) ? $rows : array();

		$headers = array( 'query', 'clicks', 'impressions', 'ctr', 'position' );
		$csv     = $this->build_csv( $headers, $rows );
		return $this->csv_response( $csv, 'serpiq-opportunities-position-8-20-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	public function alerts_csv( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$type = sanitize_key( (string) $request->get_param( 'type' ) );

		$table  = $wpdb->prefix . 'serpiq_alerts';
		$where  = '1=1';
		$params = array();
		if ( '' !== $type && in_array( $type, array( 'position_drop', 'ctr_drop', 'traffic_drop', 'impressions_spike' ), true ) ) {
			$where .= ' AND alert_type = %s';
			$params[] = $type;
		}
		$params[] = self::EXPORT_LIMIT;
		$sql = "SELECT id, alert_type, dimension_type, dimension_value, metric, previous_value, current_value, period_previous_start, period_previous_end, period_current_start, period_current_end, created_at FROM %i WHERE {$where} ORDER BY created_at DESC LIMIT %d";
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( $table ), $params ) ), ARRAY_A );
		$rows = is_array( $rows ) ? $rows : array();

		$headers = array( 'id', 'alert_type', 'dimension_type', 'dimension_value', 'metric', 'previous_value', 'current_value', 'period_previous_start', 'period_previous_end', 'period_current_start', 'period_current_end', 'created_at' );
		$csv     = $this->build_csv( $headers, $rows );
		return $this->csv_response( $csv, 'serpiq-alerts-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	public function decay_csv( \WP_REST_Request $request ): \WP_REST_Response {
		$req = new \WP_REST_Request( 'GET', '' );
		$req->set_param( 'period', $request->get_param( 'period' ) );
		$req->set_param( 'end', $request->get_param( 'end' ) );
		$req->set_param( 'min_impressions_prev', $request->get_param( 'min_impressions_prev' ) );
		$req->set_param( 'min_clicks_prev', $request->get_param( 'min_clicks_prev' ) );
		$response = Decay_Controller::instance()->get_decay( $req );
		$body     = $response->get_data();
		$items    = isset( $body['items'] ) && is_array( $body['items'] ) ? $body['items'] : array();

		$headers = array(
			'page_url',
			'wp_post_title',
			'decay_score',
			'probable_cause_label',
			'delta_clicks',
			'delta_impressions',
			'delta_position',
			'delta_ctr',
			'clicks_current',
			'clicks_previous',
			'impressions_current',
			'impressions_previous',
			'position_current',
			'position_previous',
			'ctr_current',
			'ctr_previous',
			'wp_post_modified',
			'freshness_days',
			'wp_edit_link',
		);
		$csv = $this->build_csv( $headers, $items );
		return $this->csv_response( $csv, 'serpiq-decay-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	public function content_refresh_csv( \WP_REST_Request $request ): \WP_REST_Response {
		$period = (int) $request->get_param( 'period' );
		if ( ! in_array( $period, array( 28, 90 ), true ) ) {
			$period = 28;
		}

		$end_param = (string) $request->get_param( 'end' );
		$end_date  = $this->sanitize_date( $end_param );
		if ( ! $end_date ) {
			$end_date = gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		}

		$args = array(
			'min_age_days'            => (int) $request->get_param( 'min_age_days' ),
			'min_freshness_gap_days'  => (int) $request->get_param( 'min_freshness_gap_days' ),
			'min_impressions_current' => (int) $request->get_param( 'min_impressions_current' ),
			'min_clicks_total'        => (int) $request->get_param( 'min_clicks_total' ),
		);

		$result = Content_Refresh::instance()->compute( $end_date, $period, $args );

		$items = isset( $result['items'] ) && is_array( $result['items'] ) ? $result['items'] : array();

		$priority_filter = sanitize_key( (string) $request->get_param( 'priority' ) );
		if ( '' !== $priority_filter ) {
			$items = array_values(
				array_filter(
					$items,
					static function ( $row ) use ( $priority_filter ) {
						return isset( $row['priority_level'] ) && $row['priority_level'] === $priority_filter;
					}
				)
			);
		}

		$post_type_filter = (string) $request->get_param( 'post_type' );
		if ( '' !== $post_type_filter ) {
			$items = array_values(
				array_filter(
					$items,
					static function ( $row ) use ( $post_type_filter ) {
						return isset( $row['post_type'] ) && $row['post_type'] === $post_type_filter;
					}
				)
			);
		}

		$headers = array(
			'post_id',
			'post_type',
			'post_status',
			'post_title',
			'page_url',
			'age_days',
			'freshness_days',
			'clicks_current',
			'clicks_previous',
			'impressions_current',
			'impressions_previous',
			'ctr_current',
			'ctr_previous',
			'position_current',
			'position_previous',
			'delta_clicks',
			'delta_clicks_pct',
			'delta_impressions',
			'delta_impressions_pct',
			'delta_ctr',
			'delta_position',
			'ctr_expected',
			'ctr_gap',
			'priority_score',
			'priority_level',
			'refresh_reason',
			'refresh_reason_label',
		);

		$csv = $this->build_csv( $headers, $items );
		return $this->csv_response( $csv, 'serpiq-content-refresh-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	public function internal_linking_csv( \WP_REST_Request $request ): \WP_REST_Response {
		$period = (int) $request->get_param( 'period' );
		if ( ! in_array( $period, array( 28, 90 ), true ) ) {
			$period = 28;
		}

		$end_param = (string) $request->get_param( 'end' );
		$end_date  = $this->sanitize_date( $end_param );
		if ( ! $end_date ) {
			$end_date = gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		}

		$args = array(
			'min_impressions'  => (int) $request->get_param( 'min_impressions' ),
			'min_clicks_total' => (int) $request->get_param( 'min_clicks_total' ),
			'min_age_days'     => (int) $request->get_param( 'min_age_days' ),
		);

		$result = Internal_Opportunities::instance()->compute( $end_date, $period, $args );

		$items = isset( $result['items'] ) && is_array( $result['items'] ) ? $result['items'] : array();

		$level_filter = sanitize_key( (string) $request->get_param( 'level' ) );
		if ( '' !== $level_filter ) {
			$items = array_values(
				array_filter(
					$items,
					static function ( $row ) use ( $level_filter ) {
						return isset( $row['opportunity_level'] ) && $row['opportunity_level'] === $level_filter;
					}
				)
			);
		}

		$post_type_filter = (string) $request->get_param( 'post_type' );
		if ( '' !== $post_type_filter ) {
			$items = array_values(
				array_filter(
					$items,
					static function ( $row ) use ( $post_type_filter ) {
						return isset( $row['post_type'] ) && $row['post_type'] === $post_type_filter;
					}
				)
			);
		}

		$headers = array(
			'post_id',
			'post_type',
			'post_status',
			'post_title',
			'page_url',
			'age_days',
			'inbound_links',
			'outbound_links',
			'clicks_current',
			'clicks_previous',
			'impressions_current',
			'impressions_previous',
			'ctr_current',
			'ctr_previous',
			'position_current',
			'position_previous',
			'delta_clicks',
			'delta_clicks_pct',
			'delta_impressions',
			'delta_impressions_pct',
			'delta_ctr',
			'delta_position',
			'organic_potential',
			'position_opportunity',
			'link_gap',
			'trend_score',
			'opportunity_score',
			'opportunity_level',
			'opportunity_reason',
			'opportunity_reason_label',
		);

		$csv = $this->build_csv( $headers, $items );
		return $this->csv_response( $csv, 'serpiq-internal-linking-' . gmdate( 'Y-m-d' ) . '.csv' );
	}

	/**
	 * Build CSV string with header row. UTF-8 BOM for Excel. Escapes fields.
	 *
	 * @param string[] $headers Column names.
	 * @param array<int,array<string,mixed>> $rows Rows (associative arrays).
	 * @return string
	 */
	private function build_csv( array $headers, array $rows ): string {
		$out = "\xEF\xBB\xBF"; // UTF-8 BOM
		$out .= implode( ',', array_map( array( $this, 'csv_escape' ), $headers ) ) . "\n";
		foreach ( $rows as $row ) {
			$line = array();
			foreach ( $headers as $h ) {
				$line[] = $this->csv_escape( isset( $row[ $h ] ) ? (string) $row[ $h ] : '' );
			}
			$out .= implode( ',', $line ) . "\n";
		}
		return $out;
	}

	private function csv_escape( string $value ): string {
		if ( strpos( $value, ',' ) !== false || strpos( $value, '"' ) !== false || strpos( $value, "\n" ) !== false || strpos( $value, "\r" ) !== false ) {
			return '"' . str_replace( '"', '""', $value ) . '"';
		}
		return $value;
	}

	private function csv_response( string $csv, string $filename ): \WP_REST_Response {
		return new \WP_REST_Response(
			array(
				'filename' => $filename,
				'content'  => base64_encode( $csv ),
			),
			200
		);
	}

	private function sanitize_date( string $date ): ?string {
		if ( '' === $date ) {
			return null;
		}
		return preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ? $date : null;
	}
}
