<?php
/**
 * File Path: includes/data/class-mbs-book-awards-dao.php
 *
 * Book Awards DAO
 *
 * Static data-access object for the mbs_book_awards table. Provides
 * save/get/delete operations for per-book award rows. All queries use
 * $wpdb->prepare(). No business logic lives here.
 *
 * @package MyBookShowroom
 * @since   1.6.0
 */

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

/**
 * Data access object for the mbs_book_awards table.
 */
// 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_Book_Awards_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Replace all stored awards for a post with the supplied list.
	 *
	 * Deletes existing rows for the post_id first, then inserts each award
	 * in the supplied order. An empty $awards array removes all rows for the post.
	 *
	 * Each entry in $awards must contain:
	 *   'name'   (string) Award title.
	 *   'year'   (int|string) Four-digit year or empty string for unknown.
	 *   'status' (string) One of: winner, finalist, nominee, honorable_mention, longlist.
	 *
	 * @param int   $post_id WordPress post ID.
	 * @param array $awards  Ordered array of award arrays.
	 * @return void
	 * @since 1.6.0
	 */
	public static function save( int $post_id, array $awards ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_awards';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->delete( $table, array( 'post_id' => $post_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture

		foreach ( array_values( $awards ) as $sort_order => $award ) {
			$title  = (string) ( $award['name']   ?? '' );
			$status = (string) ( $award['status'] ?? 'finalist' );
			if ( '' === $title ) {
				continue;
			}
			$year_raw = isset( $award['year'] ) && '' !== (string) $award['year']
			            ? (int) $award['year'] : null;

			if ( null !== $year_raw ) {
				$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
					$wpdb->prepare(
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						"INSERT INTO {$table}
						 (post_id, title, award_year, status, sort_order)
						 VALUES (%d, %s, %d, %s, %d)",
						$post_id, $title, $year_raw, $status, $sort_order
					)
				);
			} else {
				$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
					$wpdb->prepare(
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						"INSERT INTO {$table}
						 (post_id, title, award_year, status, sort_order)
						 VALUES (%d, %s, NULL, %s, %d)",
						$post_id, $title, $status, $sort_order
					)
				);
			}
		}
	}

	/**
	 * Retrieve all awards for a post, ordered by sort_order.
	 *
	 * Returns each row as an associative array with keys:
	 *   'name'   string  Award title.
	 *   'year'   string  Four-digit year as string, or '' if NULL.
	 *   'status' string  Status slug.
	 *
	 * @param int $post_id WordPress post ID.
	 * @return array<int, array{name: string, year: string, status: string, sort_order: int}>
	 *         Ordered array of award arrays; empty array when none.
	 * @since 1.6.0
	 */
	public static function get( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_awards';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT title, award_year, status, sort_order
				 FROM {$table}
				 WHERE post_id = %d
				 ORDER BY sort_order ASC, id ASC",
				$post_id
			),
			ARRAY_A
		);

		if ( ! is_array( $rows ) ) {
			return array();
		}

		return array_map( static function ( array $row ): array {
			return array(
				'name'       => (string) ( $row['title']      ?? '' ),
				'year'       => null !== $row['award_year'] ? (string) $row['award_year'] : '',
				'status'     => (string) ( $row['status']     ?? '' ),
				'sort_order' => (int) ( $row['sort_order'] ?? 0 ),
			);
		}, $rows );
	}

	/**
	 * Delete all award rows for a post.
	 *
	 * Called from the post-delete hook so orphan rows are not left behind.
	 *
	 * @param int $post_id WordPress post ID.
	 * @return void
	 * @since 1.6.0
	 */
	public static function delete_all( int $post_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_awards';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->delete( $table, array( 'post_id' => $post_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}
}
