<?php
/**
 * File Path: includes/data/class-mbs-contributor-book-dao.php
 *
 * MBS_Contributor_Book_DAO
 *
 * F-020 Phase 5 — CRUD for mbs_book_contributors pivot table.
 * Replaces direct MBS_Contributor_DAO usage for book-level contributor
 * assignments. All writes go through this class; never direct $wpdb in
 * business logic.
 *
 * Table: mbs_book_contributors
 *   id, post_id, term_id, onix_role_code, role_label,
 *   sort_order, public, statement, deleted_at, created_at
 *
 * @package My_Book_Showroom
 * @since   2.4.0
 */

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

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

	/**
	 * ONIX List 17 — curated 21-role subset used across all MBS contributor UI.
	 *
	 * @return array<string,string> code => label
	 */
	public static function get_role_options(): array {
		return array(
			'A01' => 'Author (by)',
			'A02' => 'With',
			'A06' => 'Composer',
			'A07' => 'Artist/Illustrator',
			'A12' => 'Illustrator',
			'A13' => 'Photographer',
			'A15' => 'Preface writer',
			'A19' => 'Afterword writer',
			'A23' => 'Introduction writer',
			'A36' => 'Cover designer',
			'A38' => 'Narrative voice',
			'B01' => 'Editor',
			'B02' => 'Revised by',
			'B06' => 'Translated by',
			'B09' => 'Series editor',
			'B11' => 'Editor in chief',
			'C01' => 'Producer',
			'C02' => 'Director',
			'C04' => 'Narrator',
			'E07' => 'Read by',
			'Z01' => 'Other (use sparingly)',
		);
	}

	/**
	 * Get all contributor rows for a post, joined with term name.
	 * Excludes soft-deleted rows (deleted_at IS NOT NULL).
	 *
	 * @param int $post_id
	 * @return object[]
	 */
	public static function get_by_post( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_contributors';
		$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 bc.*, t.name AS contributor_name
				 FROM {$table} bc
				 LEFT JOIN {$wpdb->terms} t ON t.term_id = bc.term_id
				 WHERE bc.post_id = %d
				   AND bc.deleted_at IS NULL
				 ORDER BY bc.sort_order ASC, bc.id ASC",
				$post_id
			)
		);
		return $rows ?: array();
	}

	/**
	 * Insert a new contributor row.
	 *
	 * @param array $data Must include: post_id, term_id, onix_role_code.
	 * @return int|false New row ID or false on failure.
	 */
	public static function insert( array $data ): int|false {
		if ( MBS_POST_TYPE !== get_post_type( (int) ( $data['post_id'] ?? 0 ) ) ) {
			return false; // V8-C-010: Only mbs_book posts can have contributor relationships.
		}
		global $wpdb;
		$table  = $wpdb->prefix . 'mbs_book_contributors';
		$result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$table,
			array(
				'post_id'        => (int) $data['post_id'],
				'term_id'        => (int) $data['term_id'],
				'onix_role_code' => strtoupper( sanitize_text_field( $data['onix_role_code'] ?? 'A01' ) ),
				'role_label'     => sanitize_text_field( $data['role_label'] ?? '' ),
				'sort_order'     => (int) ( $data['sort_order'] ?? 0 ),
				'public'         => (int) (bool) ( $data['public'] ?? 1 ),
				'statement'      => sanitize_textarea_field( $data['statement'] ?? '' ),
			),
			array( '%d', '%d', '%s', '%s', '%d', '%d', '%s' )
		);
		return $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Update an existing contributor row.
	 *
	 * @param int   $row_id
	 * @param array $data
	 * @return bool
	 */
	public static function update( int $row_id, array $data ): bool {
		global $wpdb;
		$table  = $wpdb->prefix . 'mbs_book_contributors';
		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$table,
			array(
				'onix_role_code' => strtoupper( sanitize_text_field( $data['onix_role_code'] ?? 'A01' ) ),
				'role_label'     => sanitize_text_field( $data['role_label'] ?? '' ),
				'sort_order'     => (int) ( $data['sort_order'] ?? 0 ),
				'public'         => (int) (bool) ( $data['public'] ?? 1 ),
				'statement'      => sanitize_textarea_field( $data['statement'] ?? '' ),
			),
			array( 'id' => $row_id ),
			array( '%s', '%s', '%d', '%d', '%s' ),
			array( '%d' )
		);
		return false !== $result;
	}

	/**
	 * Soft-delete rows for a post whose IDs are not in $keep_ids.
	 *
	 * @param int   $post_id
	 * @param int[] $keep_ids Row IDs to preserve.
	 */
	public static function soft_delete_missing_for_post( int $post_id, array $keep_ids ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_contributors';
		if ( empty( $keep_ids ) ) {
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"UPDATE {$table} SET deleted_at = NOW() WHERE post_id = %d AND deleted_at IS NULL",
					$post_id
				)
			);
			return;
		}
		$placeholders = implode( ',', array_fill( 0, count( $keep_ids ), '%d' ) );
		$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.PreparedSQLPlaceholders.UnfinishedPrepare
				"UPDATE {$table} SET deleted_at = NOW() WHERE post_id = %d AND id NOT IN ({$placeholders}) AND deleted_at IS NULL",
				array_merge( array( $post_id ), $keep_ids )
			)
		);
	}
	/**
	 * Map an ONIX contributor role code to its EPUB OPF / MARC relator code.
	 *
	 * Used by MBS_Book_Bible_Tab_Writer::assemble_epub_opf_xml() to populate
	 * the opf:role attribute on <dc:creator> and <dc:contributor> elements.
	 *
	 * Only roles present in the ONIX-to-MARC mapping table are mapped
	 * explicitly; all unmapped codes return '' (empty string).
	 *
	 * @param  string $onix_code  Three-character ONIX list-17 role code (e.g. 'A01').
	 * @return string             Three-letter MARC relator code (e.g. 'aut').
	 * @since  2.5.0
	 */
	public static function get_marc_role( string $onix_code ): string {
		$map = array(
			'A01' => 'aut', // By (author)
			'A02' => 'ctb', // With (contributor)
			'A06' => 'cmp', // Composed by
			'A07' => 'ill', // Illustrated by
			'A12' => 'ill', // Illustrated by (artwork)
			'A13' => 'pht', // Photographed by
			'A15' => 'pfr', // Preface by
			'A19' => 'aft', // Afterword by
			'A23' => 'aui', // Introduction by
			'A36' => 'bjd', // Cover design or artwork by
			'A38' => 'nrt', // Read by (narrator)
			'B01' => 'edt', // Edited by
			'B02' => 'edt', // Revised by
			'B06' => 'trl', // Translated by
			'B09' => 'edt', // Series edited by
			'B11' => 'edt', // Abridged by
			'C01' => 'pro', // Produced by
			'C02' => 'drt', // Directed by
			'C04' => 'nrt', // Narrated by (audio)
			'E07' => 'nrt', // Performed by (narrator)
			'Z01' => 'ctb', // Miscellaneous
		);
		return $map[ $onix_code ] ?? '';
	}

	/**
	 * Map a MARC relator code to its ONIX List 17 contributor role code.
	 *
	 * Bidirectional complement to get_marc_role() (ONIX -> MARC).
	 * Used by MBS_EPUB_Parser to convert opf:role attributes back to ONIX codes.
	 * Unmapped codes return '' (empty string).
	 *
	 * @param  string $marc  Three-letter MARC relator code (e.g. aut).
	 * @return string        ONIX List 17 code (e.g. A01).
	 * @since  2.5.0
	 */
	public static function get_onix_from_marc( string $marc ): string {
		$map = array(
			'aut' => 'A01',
			'edt' => 'B01',
			'ill' => 'A12',
			'pht' => 'A13',
			'trl' => 'B06',
			'nrt' => 'C04',
			'pfr' => 'A15',
			'aft' => 'A19',
			'aui' => 'A23',
			'bjd' => 'A36',
			'cmp' => 'A06',
			'pro' => 'C01',
			'drt' => 'C02',
			'bkp' => 'Z01',
			'ctb' => 'Z01',
		);
		return $map[ $marc ] ?? '';
	}
	/**
	 * Hard-delete all contributor rows for a book, including soft-deleted rows.
	 *
	 * Called from the before_delete_post hook so no orphan rows remain after
	 * book deletion. Unlike soft_delete_missing_for_post(), this permanently
	 * removes all rows regardless of deleted_at status.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function hard_delete_for_post( int $post_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_contributors';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}

}
