<?php
/**
 * File Path: includes/data/class-mbs-brand-feed-cache-dao.php
 *
 * Brand Feed Cache DAO
 *
 * CRUD operations for the mbs_brand_feed_cache table.
 * Table created by MBS_Migration_4_2. Extracted from
 * MBS_Dashboard_Feed_Fetcher during RF-2 DAO boundary enforcement.
 *
 * FIFO limit: RESULTS_PER_FEED = 5 rows per feed URL. The current codebase
 * hardcoded 2; this constant sets the correct value per REFACTOR-PLAN RF-2
 * PA-014 decision.
 *
 * @package MyBookShowroom
 * @since   1.2.0
 */

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

/**
 * Class MBS_Brand_Feed_Cache_DAO
 *
 * @since 1.2.0
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_Brand_Feed_Cache_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Maximum cached results stored per feed URL (FIFO).
	 *
	 * Per-session audit correction PA-014: changed from hardcoded 2 to 5.
	 * MBS_Dashboard_Feed_Fetcher::fetch_brand_feed() must reference this
	 * constant rather than a literal integer.
	 *
	 * @since 1.2.0
	 */
	const RESULTS_PER_FEED = 5;

	// ── Write operations ────────────────────────────────────────────────────

	/**
	 * Insert a single brand feed result row.
	 *
	 * @since  1.2.0
	 * @param  string $feed_url    Absolute URL of the RSS feed.
	 * @param  string $title       Stripped article title.
	 * @param  string $url         Absolute URL of the article.
	 * @param  string $fetched_at  UTC datetime string (Y-m-d H:i:s).
	 * @return void
	 */
	public static function insert_result(
		string $feed_url,
		string $title,
		string $url,
		string $fetched_at
	): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert(
			$wpdb->prefix . 'mbs_brand_feed_cache',
			array(
				'feed_url'     => $feed_url,
				'result_title' => $title,
				'result_url'   => $url,
				'fetched_at'   => $fetched_at,
			),
			array( '%s', '%s', '%s', '%s' )
		);
	}

	/**
	 * Enforce the FIFO cap for a single feed URL.
	 *
	 * If the total row count for $feed_url exceeds RESULTS_PER_FEED, delete
	 * the oldest excess rows (ordered by fetched_at ASC).
	 *
	 * @since  1.2.0
	 * @param  string $feed_url Absolute URL of the RSS feed.
	 * @return void
	 */
	public static function prune_fifo( string $feed_url ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_brand_feed_cache';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$total = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE feed_url = %s", $feed_url )
		);

		if ( $total > self::RESULTS_PER_FEED ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"DELETE FROM `{$table}` WHERE feed_url = %s ORDER BY fetched_at ASC LIMIT %d",
					$feed_url,
					$total - self::RESULTS_PER_FEED
				)
			);
		}
	}

	// ── Read operations ─────────────────────────────────────────────────────

	/**
	 * Return all cached feed rows, ordered by feed URL then newest first.
	 *
	 * Used by MBS_Dashboard_Feed_Fetcher::fetch_brand_feed() to assemble
	 * the grouped response after inserting and pruning.
	 *
	 * @since  1.2.0
	 * @return object[] Rows from mbs_brand_feed_cache.
	 */
	public static function get_all(): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_brand_feed_cache';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT * FROM `{$table}` ORDER BY feed_url ASC, fetched_at DESC"
		);

		return is_array( $rows ) ? $rows : array();
	}
}
