<?php
/**
 * File Path: includes/data/class-mbs-related-products-dao.php
 *
 * Related Products DAO
 *
 * F-020 Phase 8 -- CRUD for the mbs_related_products table.
 * Table stores ONIX List 51 product relation codes linking book editions
 * and format variants. PRO/PUB gated.
 *
 * @package    My_Book_Showroom
 * @subpackage Includes/Data
 * @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_Related_Products_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Allowed ONIX List 51 relation codes.
	 *
	 * 03 Replaces, 05 Replaced by, 06 Alternative format,
	 * 13 Epublication based on print, 27 Manifested as.
	 */
	const ALLOWED_RELATION_CODES = array( '03', '05', '06', '13', '27' );

	/**
	 * Get all related product rows for a book, ordered by sort_order.
	 *
	 * @param int $post_id
	 * @return object[]
	 */
	public static function get_for_post( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_related_products';
		return $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 id, post_id, relation_code, related_isbn, related_format, sort_order"
				. " FROM {$table} WHERE post_id = %d ORDER BY sort_order ASC",
				$post_id
			)
		) ?: array();
	}

	/**
	 * Insert or update a related product row.
	 *
	 * If $id is provided and > 0, updates that row; otherwise inserts.
	 *
	 * @param int         $post_id
	 * @param string      $relation_code  ONIX List 51 code.
	 * @param string      $related_isbn   13-digit numeric ISBN.
	 * @param string|null $related_format Format key (optional).
	 * @param int         $sort_order
	 * @param int         $id             Existing row ID; 0 for insert.
	 * @return int|false                  Row ID on success, false on failure.
	 */
	public static function upsert(
		int $post_id,
		string $relation_code,
		string $related_isbn,
		?string $related_format,
		int $sort_order,
		int $id = 0
	): int|false {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_related_products';

		$data = array(
			'post_id'        => $post_id,
			'relation_code'  => $relation_code,
			'related_isbn'   => $related_isbn,
			'related_format' => $related_format,
			'sort_order'     => $sort_order,
		);
		$formats = array( '%d', '%s', '%s', '%s', '%d' );

		if ( $id > 0 ) {
			$wpdb->update( $table, $data, array( 'id' => $id ), $formats, array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			return $id;
		}

		$result = $wpdb->insert( $table, $data, $formats ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		return $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Delete a single related product row by ID.
	 *
	 * @param int $id
	 */
	public static function delete( int $id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_related_products';
		$wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}

	/**
	 * Delete all related product rows for a book.
	 *
	 * @param int $post_id
	 */
	public static function delete_for_post( int $post_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_related_products';
		$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
	}

	/**
	 * Human-readable label for a relation code.
	 *
	 * @param string $code ONIX List 51 code.
	 * @return string
	 */
	public static function relation_label( string $code ): string {
		$labels = array(
			'03' => __( 'Replaces', 'my-book-showroom' ),
			'05' => __( 'Replaced by', 'my-book-showroom' ),
			'06' => __( 'Alternative format', 'my-book-showroom' ),
			'13' => __( 'Epublication based on print', 'my-book-showroom' ),
			'27' => __( 'Manifested as', 'my-book-showroom' ),
		);
		return $labels[ $code ] ?? $code;
	}
}
