<?php
/**
 * Builds the site-wide content-score report: one aggregated SQL query
 * (JOIN wp_posts + wp_abds_seo_page_scores) rather than looping the
 * analyzer per post, which would be an N+1 on a site with hundreds of
 * pages. Shared by the REST endpoint, the CSV export handler, and the
 * server-rendered reports admin page, so all three always agree on the
 * same rows for the same filters.
 *
 * @package ABDS_SEO
 */

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

/**
 * Query builder + CSV streamer for the content-score report.
 */
class ABDS_SEO_Reports {

	const DEFAULT_PER_PAGE = 20;
	const MAX_PER_PAGE     = 100;

	/**
	 * Rows fetched per LIMIT/OFFSET round trip while streaming CSV, so a
	 * large export never holds the full result set in memory at once.
	 */
	const CSV_BATCH_SIZE = 500;

	/**
	 * Whitelisted orderby values mapped to the real SQL expression each
	 * one sorts by. Never interpolate a raw orderby/order param directly
	 * into SQL — user input, however "trusted" the caller (admin screen or
	 * REST), only ever selects a key from this map.
	 */
	const ORDERBY_MAP = array(
		'title'         => 'p.post_title',
		'date'          => 'p.post_date',
		'content_score' => 'ps.content_score',
	);

	/**
	 * Returns one page of the report plus pagination metadata. Every arg
	 * is validated/sanitized here regardless of what the caller already
	 * did — this is called directly (bypassing REST's own arg-schema
	 * validation entirely) from the server-rendered admin page's raw
	 * $_GET values, so this method has to be the actual trust boundary.
	 *
	 * @param array<string,mixed> $args Raw filter/pagination args (see normalize_args()).
	 * @return array{items:array<int,array{post_id:int,title:string,post_type:string,date:string,content_score:?int,status:string}>, total:int, page:int, per_page:int, total_pages:int}
	 */
	public function query( array $args ): array {
		global $wpdb;

		$args                       = $this->normalize_args( $args );
		list( $where_sql, $params ) = $this->build_where( $args );
		$posts_table                = $wpdb->posts;
		$page_scores_table          = $wpdb->prefix . 'abds_seo_page_scores';
		$orderby                    = self::ORDERBY_MAP[ $args['orderby'] ];
		$offset                     = ( $args['page'] - 1 ) * $args['per_page'];

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $posts_table/$page_scores_table are $wpdb->posts / $wpdb->prefix plus a hardcoded literal, never user input; $where_sql is built only from the whitelisted fragments in build_where(), with every value passed through $wpdb->prepare() as a single array argument (phpcs's static placeholder counter can't see how many %-placeholders are inside a variable string or how many elements are in a variable args array — both are correct at runtime). Runs once per report-page view, not a cacheable read path.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$posts_table} p LEFT JOIN {$page_scores_table} ps ON ps.post_id = p.ID WHERE {$where_sql}",
				$params
			)
		);

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT p.ID, p.post_title, p.post_type, p.post_date, ps.content_score
				FROM {$posts_table} p LEFT JOIN {$page_scores_table} ps ON ps.post_id = p.ID
				WHERE {$where_sql}
				ORDER BY {$orderby} {$args['order']}
				LIMIT %d OFFSET %d",
				array_merge( $params, array( $args['per_page'], $offset ) )
			)
		);
		// phpcs:enable

		return array(
			'items'       => array_map( array( $this, 'format_row' ), $rows ),
			'total'       => $total,
			'page'        => $args['page'],
			'per_page'    => $args['per_page'],
			'total_pages' => (int) ceil( $total / max( 1, $args['per_page'] ) ),
		);
	}

	/**
	 * Streams the full (unpaginated) filtered result set as CSV directly
	 * to the current output buffer, in CSV_BATCH_SIZE-row pages, flushing
	 * after each page — so exporting a large site never has to hold every
	 * row in memory at once, and the browser starts receiving bytes long
	 * before the export finishes. Caller is responsible for having already
	 * sent the appropriate Content-Type/Content-Disposition headers.
	 *
	 * @param array<string,mixed> $args Same filter args as query() (page/per_page are ignored).
	 */
	public function stream_csv( array $args ): void {
		global $wpdb;

		$args                       = $this->normalize_args( $args );
		list( $where_sql, $params ) = $this->build_where( $args );
		$posts_table                = $wpdb->posts;
		$page_scores_table          = $wpdb->prefix . 'abds_seo_page_scores';
		$orderby                    = self::ORDERBY_MAP[ $args['orderby'] ];

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- php://output is a stream handle, not a real file; WP_Filesystem has no equivalent for streaming output directly to the response body.
		$output = fopen( 'php://output', 'w' );
		fputcsv( $output, array( 'post_id', 'title', 'post_type', 'date', 'content_score', 'status' ) );

		$offset    = 0;
		$row_count = 0;

		do {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- see query()'s identical justification above; this is the same query shape with a fixed CSV_BATCH_SIZE page size instead of the caller-chosen per_page.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT p.ID, p.post_title, p.post_type, p.post_date, ps.content_score
					FROM {$posts_table} p LEFT JOIN {$page_scores_table} ps ON ps.post_id = p.ID
					WHERE {$where_sql}
					ORDER BY {$orderby} {$args['order']}
					LIMIT %d OFFSET %d",
					array_merge( $params, array( self::CSV_BATCH_SIZE, $offset ) )
				)
			);
			// phpcs:enable

			foreach ( $rows as $row ) {
				$item = $this->format_row( $row );
				fputcsv(
					$output,
					array(
						$item['post_id'],
						$item['title'],
						$item['post_type'],
						$item['date'],
						$item['content_score'] ?? '',
						$item['status'],
					)
				);
			}

			flush();
			$row_count = count( $rows );
			$offset   += self::CSV_BATCH_SIZE;
		} while ( self::CSV_BATCH_SIZE === $row_count );

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- pairs with the fopen( 'php://output' ) above, same justification.
		fclose( $output );
	}

	/**
	 * Validates and sanitizes every filter/pagination arg. The single trust
	 * boundary for this class — see query()'s docblock for why that matters.
	 *
	 * @param array<string,mixed> $args Raw args.
	 * @return array{post_types:string[], date_from:string, date_to:string, min_score:?int, max_score:?int, orderby:string, order:string, page:int, per_page:int}
	 */
	protected function normalize_args( array $args ): array {
		$public_post_types = array_values( get_post_types( array( 'public' => true ) ) );

		$requested_post_type = isset( $args['post_type'] ) ? (string) $args['post_type'] : '';
		$post_types          = ( '' !== $requested_post_type && in_array( $requested_post_type, $public_post_types, true ) )
			? array( $requested_post_type )
			: $public_post_types;

		$orderby = isset( $args['orderby'] ) ? (string) $args['orderby'] : 'content_score';
		if ( ! isset( self::ORDERBY_MAP[ $orderby ] ) ) {
			$orderby = 'content_score';
		}

		$order = isset( $args['order'] ) ? strtoupper( (string) $args['order'] ) : 'ASC';
		$order = in_array( $order, array( 'ASC', 'DESC' ), true ) ? $order : 'ASC';

		$per_page = isset( $args['per_page'] ) ? (int) $args['per_page'] : self::DEFAULT_PER_PAGE;
		$per_page = max( 1, min( self::MAX_PER_PAGE, $per_page ) );

		$page = isset( $args['page'] ) ? max( 1, (int) $args['page'] ) : 1;

		return array(
			'post_types' => $post_types,
			'date_from'  => $this->sanitize_date( isset( $args['date_from'] ) ? (string) $args['date_from'] : '' ),
			'date_to'    => $this->sanitize_date( isset( $args['date_to'] ) ? (string) $args['date_to'] : '' ),
			'min_score'  => $this->sanitize_score( $args['min_score'] ?? null ),
			'max_score'  => $this->sanitize_score( $args['max_score'] ?? null ),
			'orderby'    => $orderby,
			'order'      => $order,
			'page'       => $page,
			'per_page'   => $per_page,
		);
	}

	/**
	 * Validates a "Y-m-d" date string, returning '' for anything else
	 * (missing filter, malformed input) rather than passing it through.
	 *
	 * @param string $date Raw date string.
	 * @return string Valid "Y-m-d" date, or ''.
	 */
	protected function sanitize_date( string $date ): string {
		return preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ? $date : '';
	}

	/**
	 * Clamps a raw min/max score filter value to 0-100, or null if absent/empty.
	 *
	 * @param mixed $value Raw filter value.
	 * @return ?int Clamped 0-100 score, or null.
	 */
	protected function sanitize_score( $value ): ?int {
		if ( null === $value || '' === $value ) {
			return null;
		}

		return max( 0, min( 100, (int) $value ) );
	}

	/**
	 * Builds the WHERE clause (with %-placeholders) and its matching
	 * ordered params array from normalized args. post_status = 'publish'
	 * is always the first condition, so $params is never empty even when
	 * no optional filter is set — $wpdb->prepare() is always called with
	 * at least one real substitution.
	 *
	 * @param array<string,mixed> $args Normalized args (see normalize_args()).
	 * @return array{0:string, 1:array<int,mixed>} WHERE clause SQL and its ordered params.
	 */
	protected function build_where( array $args ): array {
		$conditions = array( 'p.post_status = %s' );
		$params     = array( 'publish' );

		$placeholders = implode( ', ', array_fill( 0, count( $args['post_types'] ), '%s' ) );
		$conditions[] = "p.post_type IN ({$placeholders})";
		$params       = array_merge( $params, $args['post_types'] );

		if ( '' !== $args['date_from'] ) {
			$conditions[] = 'p.post_date >= %s';
			$params[]     = $args['date_from'] . ' 00:00:00';
		}

		if ( '' !== $args['date_to'] ) {
			$conditions[] = 'p.post_date <= %s';
			$params[]     = $args['date_to'] . ' 23:59:59';
		}

		if ( null !== $args['min_score'] ) {
			$conditions[] = 'ps.content_score >= %d';
			$params[]     = $args['min_score'];
		}

		if ( null !== $args['max_score'] ) {
			$conditions[] = 'ps.content_score <= %d';
			$params[]     = $args['max_score'];
		}

		return array( implode( ' AND ', $conditions ), $params );
	}

	/**
	 * Maps one raw SQL row to the report's public item shape, computing
	 * "status" from ABDS_SEO_Content_Analyzer::categorize_score() rather
	 * than storing it — one source of truth for what "good/ok/poor" mean.
	 *
	 * @param object $row Raw row from query()/stream_csv()'s SQL.
	 * @return array{post_id:int, title:string, post_type:string, date:string, content_score:?int, status:string}
	 */
	protected function format_row( $row ): array {
		$score = null !== $row->content_score ? (int) round( (float) $row->content_score ) : null;

		return array(
			'post_id'       => (int) $row->ID,
			'title'         => $row->post_title,
			'post_type'     => $row->post_type,
			'date'          => $row->post_date,
			'content_score' => $score,
			'status'        => ABDS_SEO_Content_Analyzer::categorize_score( $score ),
		);
	}
}