<?php
/**
 * File Path: includes/data/class-mbs-book-collection-position-dao.php
 *
 * Book Collection Position DAO
 *
 * Data-access layer for the mbs_book_collection_positions table introduced
 * in migration 4.12. Replaces the dynamic mbs_collection_{term_id}_position
 * postmeta pattern with a normalised relational table.
 *
 * Transition complete (Session 162): saves and reads are both DAO-only.
 * The legacy mbs_collection_{term_id}_position postmeta keys were retired
 * by migration 4.17.
 *
 * Per project SQL rules: all raw SQL for this domain lives in this file.
 *
 * @package    My_Book_Showroom
 * @subpackage Includes/Data
 * @since      1.7.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_Book_Collection_Position_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/** Unprefixed table name. */
	private const TABLE = 'mbs_book_collection_positions';

	/**
	 * Return the fully-qualified table name for the current site.
	 *
	 * @return string
	 */
	private static function table(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * Insert or update the position for a single book-collection pair.
	 *
	 * Uses INSERT ... ON DUPLICATE KEY UPDATE on the UNIQUE KEY (post_id, term_id)
	 * so repeated calls are safe and idempotent.
	 *
	 * @param int $post_id  Book post ID.
	 * @param int $term_id  mbs_collection term ID.
	 * @param int $position Numeric sort position (1-based; 0 is treated as unset).
	 * @return void
	 */
	public static function upsert( int $post_id, int $term_id, int $position ): void {
		if ( $post_id <= 0 || $term_id <= 0 || $position <= 0 ) {
			return;
		}
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"INSERT INTO {$table} (post_id, term_id, position)
				 VALUES (%d, %d, %d)
				 ON DUPLICATE KEY UPDATE position = VALUES(position)",
				$post_id,
				$term_id,
				$position
			)
		);
	}

	/**
	 * Delete the position row for a specific book-collection pair.
	 *
	 * Called when a book is removed from a collection.
	 *
	 * @param int $post_id Book post ID.
	 * @param int $term_id mbs_collection term ID.
	 * @return void
	 */
	public static function delete( int $post_id, int $term_id ): void {
		if ( $post_id <= 0 || $term_id <= 0 ) {
			return;
		}
		global $wpdb;
		$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			self::table(),
			array(
				'post_id' => $post_id,
				'term_id' => $term_id,
			),
			array( '%d', '%d' )
		);
	}

	/**
	 * Delete all position rows for a book across every collection.
	 *
	 * Called on book deletion.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function delete_all_for_book( int $post_id ): void {
		if ( $post_id <= 0 ) {
			return;
		}
		global $wpdb;
		$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			self::table(),
			array( 'post_id' => $post_id ),
			array( '%d' )
		);
	}

	/**
	 * Get the stored position for a specific book-collection pair.
	 *
	 * @param int $post_id Book post ID.
	 * @param int $term_id mbs_collection term ID.
	 * @return int|null Position value, or null if not set.
	 */
	public static function get_position( int $post_id, int $term_id ): ?int {
		if ( $post_id <= 0 || $term_id <= 0 ) {
			return null;
		}
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$val = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT position FROM {$table} WHERE post_id = %d AND term_id = %d LIMIT 1",
				$post_id,
				$term_id
			)
		);
		return null === $val ? null : (int) $val;
	}

	/**
	 * Get all collection positions for a book, keyed by term_id.
	 *
	 * @param int $post_id Book post ID.
	 * @return array<int, int> Map of term_id => position.
	 */
	public static function get_positions_for_book( int $post_id ): array {
		if ( $post_id <= 0 ) {
			return array();
		}
		global $wpdb;
		$table = self::table();
		// 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 term_id, position FROM {$table} WHERE post_id = %d",
				$post_id
			),
			ARRAY_A
		);
		$map = array();
		foreach ( (array) $rows as $row ) {
			$map[ (int) $row['term_id'] ] = (int) $row['position'];
		}
		return $map;
	}

	/**
	 * Get all position rows for a collection term, keyed by post_id.
	 *
	 * Used by MBS_Collection_Reading_Order_DAO::compute_default_order() to
	 * replace the postmeta JOIN with a direct table read.
	 *
	 * @param int $term_id mbs_collection term ID.
	 * @return array<int, int> Map of post_id => position.
	 */
	public static function get_positions_for_collection( int $term_id ): array {
		if ( $term_id <= 0 ) {
			return array();
		}
		global $wpdb;
		$table = self::table();
		// 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 post_id, position FROM {$table} WHERE term_id = %d",
				$term_id
			),
			ARRAY_A
		);
		$map = array();
		foreach ( (array) $rows as $row ) {
			$map[ (int) $row['post_id'] ] = (int) $row['position'];
		}
		return $map;
	}
}
