<?php
/**
 * File Path: includes/data/class-mbs-classification-dao.php
 *
 * Classification DAO
 *
 * Read-only data access object for the mbs_classification_codes,
 * mbs_bisac_thema_map, and mbs_bic_thema_map tables. All queries use
 * $wpdb->prepare(). No business logic lives here.
 *
 * @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_Classification_DAO {

	/**
	 * Search BISAC codes by code prefix or description substring.
	 *
	 * @param string $query Search string (minimum 2 characters recommended).
	 * @param int    $limit Maximum rows to return. Default 10.
	 * @return array<int, array{ code: string, description: string }> Matching codes.
	 */
	public function get_bisac_by_prefix( string $query, int $limit = 10 ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_classification_codes';
		$like  = '%' . $wpdb->esc_like( $query ) . '%';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		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 code, description
				FROM {$table}
				WHERE scheme = 'bisac'
				  AND ( code LIKE %s OR description LIKE %s )
				ORDER BY code ASC
				LIMIT %d",
				$like,
				$like,
				$limit
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Search Thema codes by code prefix or description substring.
	 *
	 * Returns both subject codes (alphabetic) and qualifier codes (numeric)
	 * so the Layer 2 typeahead can surface the full Thema code list.
	 *
	 * @param string $query Search string (minimum 2 characters recommended).
	 * @param int    $limit Maximum rows to return. Default 10.
	 * @return array<int, array{ code: string, description: string, notes: string }> Matching codes.
	 */
	public function get_thema_by_prefix( string $query, int $limit = 10 ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_classification_codes';
		$like  = '%' . $wpdb->esc_like( $query ) . '%';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		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 code, description, notes
				FROM {$table}
				WHERE scheme = 'thema'
				  AND ( code LIKE %s OR description LIKE %s )
				ORDER BY code ASC
				LIMIT %d",
				$like,
				$like,
				$limit
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Search Bowker subject codes by code prefix or description substring.
	 *
	 * @param string $query Partial code or description string (minimum 2 characters recommended).
	 * @param int    $limit Maximum rows returned. Default 10.
	 * @return array<int, array{ code: string, description: string }> Matching codes.
	 * @since 1.5.0
	 */
	public function get_bowker_by_prefix( string $query, int $limit = 10 ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_classification_codes';
		$like  = '%' . $wpdb->esc_like( $query ) . '%';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		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 code, description
				FROM {$table}
				WHERE scheme = 'bowker'
				  AND ( code LIKE %s OR description LIKE %s )
				ORDER BY code ASC
				LIMIT %d",
				$like,
				$like,
				$limit
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Get all Thema codes mapped to a given BISAC code.
	 *
	 * Joins mbs_bisac_thema_map against mbs_classification_codes to enrich
	 * each result with description and usage notes. The notes field is suitable
	 * for rendering as a UI tooltip on hover.
	 *
	 * @param string $bisac_code Uppercase BISAC code (e.g. 'FIC027020').
	 * @return array<int, array{
	 *   thema_code:   string,
	 *   is_qualifier: string,
	 *   description:  string,
	 *   notes:        string
	 * }> Mapped Thema codes with metadata.
	 */
	public function get_thema_for_bisac( string $bisac_code ): array {
		global $wpdb;
		$map_table   = $wpdb->prefix . 'mbs_bisac_thema_map';
		$codes_table = $wpdb->prefix . 'mbs_classification_codes';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		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 m.thema_code, m.is_qualifier, c.description, c.notes
				FROM {$map_table} AS m
				LEFT JOIN {$codes_table} AS c
				    ON c.scheme = 'thema' AND c.code COLLATE utf8mb4_unicode_ci = m.thema_code COLLATE utf8mb4_unicode_ci
				WHERE m.bisac_code = %s
				ORDER BY m.is_qualifier ASC, m.thema_code ASC",
				strtoupper( $bisac_code )
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Get Thema codes mapped to any of the given BISAC codes (batch lookup).
	 *
	 * Used by the Layer 3 Thema suggestions panel: after BISAC codes are surfaced
	 * for a selected genre, this returns the corresponding Thema codes via the
	 * mbs_bisac_thema_map table, deduplicated and ordered by qualifier status.
	 *
	 * @param string[] $bisac_codes BISAC code strings (e.g. ['FIC042000']).
	 * @param int      $limit       Maximum rows. Default 20.
	 * @return array<int, array{ code: string, description: string, notes: string }>
	 */
	public function get_thema_for_bisac_batch( array $bisac_codes, int $limit = 20 ): array {
		global $wpdb;
		if ( empty( $bisac_codes ) ) { return array(); }

		$map_table   = $wpdb->prefix . 'mbs_bisac_thema_map';
		$codes_table = $wpdb->prefix . 'mbs_classification_codes';
		$holders     = implode( ',', array_fill( 0, count( $bisac_codes ), '%s' ) );
		$args        = array_merge( array_map( 'strtoupper', $bisac_codes ), array( $limit ) );

		// 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 m.thema_code AS code, c.description, c.notes, MIN(m.is_qualifier) AS is_qualifier
				FROM {$map_table} AS m
				LEFT JOIN {$codes_table} AS c
				    ON c.scheme = 'thema' AND c.code COLLATE utf8mb4_unicode_ci = m.thema_code COLLATE utf8mb4_unicode_ci
				WHERE m.bisac_code IN ({$holders})
				GROUP BY m.thema_code, c.description, c.notes
				ORDER BY is_qualifier ASC, m.thema_code ASC
				LIMIT %d",
				...$args
			),
			ARRAY_A
		) ?: array();

		return array_map( static function ( array $row ): array {
			return array(
				'code'        => $row['code'],
				'description' => $row['description'] ?? '',
				'notes'       => $row['notes'] ?? '',
			);
		}, $rows );
	}

	/**
	 * Get all THEMA codes mapped to a given BIC code (forward lookup).
	 *
	 * @param string $bic_code BIC v2.1 code string (e.g. 'FW').
	 * @return array<int, array{
	 *   thema_code:   string,
	 *   is_qualifier: string,
	 *   description:  string,
	 *   notes:        string
	 * }> Mapped Thema codes with metadata.
	 * @since 1.5.0
	 */
	public function get_thema_for_bic( string $bic_code ): array {
		global $wpdb;
		$map_table   = $wpdb->prefix . 'mbs_bic_thema_map';
		$codes_table = $wpdb->prefix . 'mbs_classification_codes';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		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 m.thema_code, m.is_qualifier, c.description, c.notes
				FROM {$map_table} AS m
				LEFT JOIN {$codes_table} AS c
				    ON c.scheme = 'thema' AND c.code COLLATE utf8mb4_unicode_ci = m.thema_code COLLATE utf8mb4_unicode_ci
				WHERE m.bic_code = %s
				ORDER BY m.is_qualifier ASC, m.thema_code ASC",
				strtoupper( $bic_code )
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Get all BIC codes mapped to a given THEMA code (reverse lookup).
	 *
	 * @param string $thema_code Thema code string (e.g. 'FW').
	 * @return array<int, array{
	 *   bic_code:     string,
	 *   is_qualifier: string
	 * }> BIC codes that map to this Thema code.
	 * @since 1.5.0
	 */
	public function get_bic_for_thema( string $thema_code ): array {
		global $wpdb;
		$map_table = $wpdb->prefix . 'mbs_bic_thema_map';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		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 bic_code, is_qualifier
				FROM {$map_table}
				WHERE thema_code = %s
				ORDER BY is_qualifier ASC, bic_code ASC",
				$thema_code
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Batch reverse lookup: BIC codes for an array of THEMA codes.
	 *
	 * Returns deduplicated BIC codes derived from the full set of Thema codes
	 * stored on a book record. Used by the ONIX 2.1 exporter and the future
	 * Book Bible export to emit BIC Subject blocks without per-book BIC storage.
	 *
	 * @param string[] $thema_codes Array of Thema code strings.
	 * @param int      $limit       Maximum rows returned. Default 10.
	 * @return array<int, array{ bic_code: string, is_qualifier: string }> Deduplicated BIC codes.
	 * @since 1.5.0
	 */
	public function get_bic_for_thema_batch( array $thema_codes, int $limit = 10 ): array {
		global $wpdb;
		if ( empty( $thema_codes ) ) {
			return array();
		}

		$map_table = $wpdb->prefix . 'mbs_bic_thema_map';
		$holders   = implode( ',', array_fill( 0, count( $thema_codes ), '%s' ) );
		$args      = array_merge( $thema_codes, array( $limit ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		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 bic_code, MIN(is_qualifier) AS is_qualifier
				FROM {$map_table}
				WHERE thema_code IN ({$holders})
				GROUP BY bic_code
				ORDER BY is_qualifier ASC, bic_code ASC
				LIMIT %d",
				...$args
			),
			ARRAY_A
		) ?: array();
	}
	/**
	 * Batch description lookup for any classification scheme.
	 *
	 * Returns a code-to-description map for the given codes. Used by the
	 * Book Bible exporter to enrich BISAC, Thema, and BIC code rows with
	 * human-readable labels without per-code round-trips.
	 *
	 * @param string   $scheme One of 'bisac', 'thema', 'bowker', 'bic'.
	 * @param string[] $codes  Array of code strings (e.g. ['FIC042000', 'FW']).
	 * @return array<string, string> Map of code => description.
	 * @since 1.5.0
	 */
	public function get_descriptions_batch( string $scheme, array $codes ): array {
		global $wpdb;
		if ( empty( $codes ) ) {
			return array();
		}
		$table   = $wpdb->prefix . 'mbs_classification_codes';
		$holders = implode( ',', array_fill( 0, count( $codes ), '%s' ) );
		$args    = array_merge( array( $scheme ), $codes );

		// 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 code, description
				FROM {$table}
				WHERE scheme = %s
				  AND code IN ({$holders})",
				...$args
			),
			ARRAY_A
		) ?: array();

		$map = array();
		foreach ( $rows as $row ) {
			$map[ $row['code'] ] = $row['description'] ?? '';
		}
		return $map;
	}

	// ── Book Classification Codes (mbs_book_classification_codes) ────────────

	/**
	 * Replace all stored codes for a post+scheme with the supplied list.
	 *
	 * Deletes existing rows for the combination first, then inserts the new
	 * codes in the supplied order. An empty $codes array is a valid call and
	 * simply removes all rows for that post+scheme.
	 *
	 * @param int      $post_id WordPress post ID.
	 * @param string   $scheme  Scheme slug: 'bisac', 'thema', 'bic', or 'illustration'.
	 * @param string[] $codes   Ordered array of code strings.
	 * @return void
	 * @since 1.6.0
	 */
	public static function save_book_codes( int $post_id, string $scheme, array $codes ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_classification_codes';

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

		foreach ( array_values( $codes ) as $sort_order => $code ) {
			$code = (string) $code;
			if ( '' === $code ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'post_id'    => $post_id,
					'scheme'     => $scheme,
					'code'       => $code,
					'sort_order' => $sort_order,
				),
				array( '%d', '%s', '%s', '%d' )
			);
		}
	}

	/**
	 * Retrieve all codes for a post+scheme, ordered by sort_order.
	 *
	 * @param int    $post_id WordPress post ID.
	 * @param string $scheme  Scheme slug: 'bisac', 'thema', 'bic', or 'illustration'.
	 * @return string[] Ordered array of code strings; empty array when none.
	 * @since 1.6.0
	 */
	public static function get_book_codes( int $post_id, string $scheme ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_classification_codes';

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

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

	/**
	 * Delete all stored codes for a specific post+scheme combination.
	 *
	 * @param int    $post_id WordPress post ID.
	 * @param string $scheme  Scheme slug.
	 * @return void
	 * @since 1.6.0
	 */
	public static function delete_book_codes( int $post_id, string $scheme ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_classification_codes';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->delete( $table, array( 'post_id' => $post_id, 'scheme' => $scheme ), array( '%d', '%s' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}

	/**
	 * Delete every classification-code row for a post (all schemes).
	 *
	 * 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_for_post( int $post_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_classification_codes';
		// 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
	}

}
