<?php
/**
 * File Path: includes/data/class-mbs-endorsement-dao.php
 *
 * Endorsement DAO
 *
 * CRUD operations for the mbs_endorsements table.
 *
 */

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
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- safe: array_map('intval') cast ensures type safety for IN clause IDs; all values wrapped in $wpdb->prepare()
class MBS_Endorsement_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	public static function save_from_post( int $post_id, array $post_data ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_endorsements';

		if ( empty( $post_data['mbs_endorsement_quote'] ) || ! is_array( $post_data['mbs_endorsement_quote'] ) ) {
			// No endorsements submitted — soft-delete all existing (safe fallback)
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'is_deleted' => 1,
					'deleted_at' => current_time( 'mysql', true ),
				),
				array( 'post_id' => $post_id ),
				array( '%d', '%s' ),
				array( '%d' )
			);
			return;
		}

		$submitted = array();
		foreach ( $post_data['mbs_endorsement_quote'] as $i => $quote ) {
			$quote = sanitize_textarea_field( wp_unslash( $quote ) );
			if ( empty( $quote ) ) {
				continue;
			}
			$submitted[] = array(
				'quote_text'        => $quote,
				'attributed_to'     => isset( $post_data['mbs_endorsement_attributed_to'][ $i ] )
					? sanitize_text_field( wp_unslash( $post_data['mbs_endorsement_attributed_to'][ $i ] ) )
					: '',
				'attribution_title' => isset( $post_data['mbs_endorsement_attribution_title'][ $i ] )
					? sanitize_text_field( wp_unslash( $post_data['mbs_endorsement_attribution_title'][ $i ] ) )
					: '',
				'is_visible'        => isset( $post_data['mbs_endorsement_visible'][ $i ] ) ? 1 : 0,
				'sort_order'        => $i,
			);
		}

		// Fetch existing endorsements for this post (H-18: update-in-place pattern)
		$existing = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT id, sort_order FROM {$table} WHERE post_id = %d AND is_deleted = 0 ORDER BY sort_order ASC",
			$post_id
		) );

		$keep_ids = array();

		foreach ( $submitted as $idx => $data ) {
			$data['post_id'] = $post_id;

			// Match by sort_order position (stable across saves)
			$match = isset( $existing[ $idx ] ) ? $existing[ $idx ] : null;

			if ( $match ) {
				// Update existing row — do NOT overwrite created_at.
				// D-003: explicit format specifiers required.
				$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
					$table,
					$data,
					array( 'id' => $match->id ),
					self::get_format_specifiers( $data ),
					array( '%d' )
				);
				$keep_ids[] = $match->id;
			} else {
				// Insert new row — set created_at only on first insert.
				$data['created_at'] = current_time( 'mysql', true );
				// D-003: explicit format specifiers required.
				$result = $wpdb->insert( $table, $data, self::get_format_specifiers( $data ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				if ( $result ) {
					$keep_ids[] = $wpdb->insert_id;
				}
			}
		}

		// Soft-delete any endorsements that were removed (only those not in submitted set)
		if ( ! empty( $keep_ids ) ) {
			$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				"UPDATE {$table} SET is_deleted = 1, deleted_at = %s 
				 WHERE post_id = %d AND id NOT IN (" . implode( ',', array_map( 'intval', $keep_ids ) ) . ")
				   AND is_deleted = 0",
				current_time( 'mysql', true ),
				$post_id
			) );
		}
		// CQ-05: Removed duplicate else-block soft-delete. The empty-submission case
		// is fully handled by the early-return guard at the top of this method.
	}

	/**
	 * Insert a single endorsement row.
	 *
	 * Called by MBS_Book_Hydrator during archive import. Accepts canonical
	 * transport keys that map 1:1 to DB column names. Sanitization is the
	 * caller's responsibility (IEF-B-007 / Sprint Plan §Phase 3).
	 *
	 * This is a NEW method and is NOT a refactor of save_from_post().
	 * save_from_post() handles admin form submissions (update-in-place pattern
	 * with soft-delete coordination); this method handles single-row inserts
	 * from a structured import array.
	 *
	 * @param array $row {
	 *   @type string $quote_text        Required. Endorsement body text.
	 *   @type string $attributed_to     Optional. Name of the endorser.
	 *   @type string $attribution_title Optional. Title or publication.
	 *   @type int    $is_visible        Optional. 1 = visible, 0 = hidden. Default 1.
	 *   @type int    $sort_order        Optional. Display order. Default 0.
	 * }
	 * @param int $post_id Book post ID.
	 * @return int|false New row ID on success, false on failure.
	 */
	public static function insert( int $post_id, array $row ): int|false {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_endorsements';

		$data = array(
			'post_id'           => $post_id,
			'quote_text'        => (string) ( $row['quote_text']        ?? '' ),
			'attributed_to'     => (string) ( $row['attributed_to']     ?? '' ),
			'attribution_title' => (string) ( $row['attribution_title'] ?? '' ),
			'is_visible'        => (int)    ( $row['is_visible']        ?? 1 ),
			'sort_order'        => (int)    ( $row['sort_order']        ?? 0 ),
			'created_at'        => current_time( 'mysql', true ),
		);

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

	/**
	 * Get all non-deleted endorsements for a post, regardless of visibility.
	 * Used by the admin metabox so hidden endorsements remain editable.
	 *
	 * @param int $post_id Post ID.
	 * @return object[]
	 */
	public static function get_all_for_post( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_endorsements';

		return $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT id, post_id, quote_text, attributed_to, attribution_title, sort_order, is_visible, is_deleted, deleted_at, created_at FROM {$table} WHERE post_id = %d AND is_deleted = 0 ORDER BY sort_order ASC",
			$post_id
		) );
	}

	/**
	 * Get visible endorsements for a post.
	 * Used by front-end templates (is_visible = 1 filter applied).
	 *
	 * @param int $post_id Post ID.
	 * @return object[]
	 */
	public static function get_by_post( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_endorsements';

		return $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT id, post_id, quote_text, attributed_to, attribution_title, sort_order, is_visible, is_deleted, deleted_at, created_at FROM {$table} WHERE post_id = %d AND is_visible = 1 AND is_deleted = 0 ORDER BY sort_order ASC",
			$post_id
		) );
	}

	/**
	 * Returns explicit $wpdb format specifiers for each key in $data.
	 *
	 * D-003: Integer columns in mbs_endorsements mapped to %d.
	 *
	 * @param array $data  Associative array of column→value pairs.
	 * @return array       Indexed array of format strings.
	 */
	private static function get_format_specifiers( array $data ): array {
		$int_cols = array(
			'post_id',
			'is_visible',
			'sort_order',
			'is_deleted',
		);
		$formats = array();
		foreach ( array_keys( $data ) as $col ) {
			$formats[] = in_array( $col, $int_cols, true ) ? '%d' : '%s';
		}
		return $formats;
	}
	/**
	 * Hard-delete all endorsement rows for a book, including soft-deleted rows.
	 *
	 * Called from the before_delete_post hook so no orphan rows remain after
	 * book deletion.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function delete_for_post( int $post_id ): void {
		global $wpdb;
		$wpdb->delete( $wpdb->prefix . 'mbs_endorsements', 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
	}

}
