<?php
/**
 * @file    includes/data/class-mbs-pa-api-cache-dao.php
 * Data access layer for the mbs_pa_api_cache table.
 * @package MyBookShowroom
 * @since   1.1.0
 */

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

// 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_PA_API_Cache_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Fetch a single cache row by ASIN.
	 *
	 * @param string $asin Amazon Standard Identification Number (10 chars).
	 * @return object|null Row object on hit, null on miss.
	 */
	public static function get_by_asin( string $asin ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_pa_api_cache';

		$row = $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT asin, price_amount, price_currency, star_rating, review_count, availability, sales_rank, fetched_at
			 FROM {$table}
			 WHERE asin = %s",
			$asin
		) );

		return $row ?: null;
	}

	/**
	 * Insert or update a cache row for the given ASIN.
	 *
	 * Accepted keys in $data: price_amount, price_currency, star_rating,
	 * review_count, availability. fetched_at is always set to NOW().
	 *
	 * @param string $asin ASIN to cache.
	 * @param array  $data Associative array of column => value pairs.
	 * @return bool True on success, false on DB error.
	 */
	public static function upsert( string $asin, array $data ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_pa_api_cache';

		$allowed = array( 'price_amount', 'price_currency', 'star_rating', 'review_count', 'availability', 'sales_rank' );
		$payload = array( 'asin' => $asin );

		foreach ( $allowed as $col ) {
			if ( array_key_exists( $col, $data ) ) {
				$payload[ $col ] = $data[ $col ];
			}
		}

		$payload['fetched_at'] = current_time( 'mysql', true ); // UTC

		$formats = array();
		foreach ( array_keys( $payload ) as $col ) {
			switch ( $col ) {
				case 'review_count':
					case 'sales_rank':
					$formats[] = '%d';
					break;
				case 'price_amount':
				case 'star_rating':
					$formats[] = '%f';
					break;
				default:
					$formats[] = '%s';
			}
		}

		$result = $wpdb->replace( $table, $payload, $formats ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture

		return false !== $result;
	}

	/**
	 * Return true if the cached row is older than $ttl seconds or does not exist.
	 *
	 * @param string $asin ASIN to check.
	 * @param int    $ttl  Time-to-live in seconds. Default 86400 (24 h).
	 * @return bool True when a refresh is needed.
	 */
	public static function is_stale( string $asin, int $ttl = 86400 ): bool {
		$row = self::get_by_asin( $asin );

		if ( null === $row ) {
			return true;
		}

		$fetched = strtotime( $row->fetched_at );
		if ( false === $fetched ) {
			return true;
		}

		return ( time() - $fetched ) >= $ttl;
	}
	/**
	 * Return the lowest sales_rank across all ASINs registered for a given post.
	 *
	 * Joins mbs_format_rows (for ASINs) -> mbs_pa_api_cache (for rank).
	 * Returns null if the book has no ASINs or no cache rows with a rank.
	 *
	 * @param int $post_id The mbs_book post ID.
	 * @return int|null Lowest (best) sales rank, or null if none found.
	 */
	/**
	 * Return post IDs of books whose best sales rank is within the threshold.
	 *
	 * Joins mbs_format_rows with mbs_pa_api_cache on ASIN, groups by post ID,
	 * and orders by the minimum (best) sales rank ascending.
	 *
	 * Used by the [mbs_bestseller] shortcode.
	 *
	 * @since  1.2.0
	 * @param  int $threshold Max qualifying rank (inclusive). Lower = better.
	 * @param  int $limit     Max number of post IDs to return.
	 * @return int[]          Post IDs ordered best-rank first.
	 */
	public static function get_post_ids_by_rank_threshold( int $threshold, int $limit ): array {
		global $wpdb;
		$fr    = $wpdb->prefix . 'mbs_format_rows';
		$cache = $wpdb->prefix . 'mbs_pa_api_cache';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table names from $wpdb->prefix only.
		$rows = $wpdb->get_col( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT fr.post_id
			   FROM {$fr} fr
			  INNER JOIN {$cache} c ON c.asin = fr.asin
			  WHERE fr.is_deleted = 0
			    AND fr.asin IS NOT NULL
			    AND fr.asin != ''
			    AND c.sales_rank IS NOT NULL
			    AND c.sales_rank > 0
			    AND c.sales_rank <= %d
			  GROUP BY fr.post_id
			  ORDER BY MIN(c.sales_rank) ASC
			  LIMIT %d",
			$threshold,
			$limit
		) );
		return array_map( 'intval', $rows ?: array() );
	}

	public static function get_best_sales_rank_for_post( int $post_id ): ?int {
		global $wpdb;

		$fr    = $wpdb->prefix . 'mbs_format_rows';
		$cache = $wpdb->prefix . 'mbs_pa_api_cache';

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table names from $wpdb->prefix only.
		$rank = $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT MIN(c.sales_rank)
			   FROM {$fr} fr
			  INNER JOIN {$cache} c ON c.asin = fr.asin
			  WHERE fr.post_id   = %d
			    AND fr.is_deleted = 0
			    AND fr.asin IS NOT NULL
			    AND fr.asin != ''
			    AND c.sales_rank IS NOT NULL
			    AND c.sales_rank > 0",
			$post_id
		) );

		return ( null !== $rank ) ? (int) $rank : null;
	}

}
