<?php
/**
 * File Path: includes/data/class-mbs-format-row-dao.php
 *
 * Format Row DAO
 *
 * CRUD operations for the mbs_format_rows table.
 * Now aligned with canonical schema and metabox expectations.
 *
 * @package MyBookShowroom
 * @since   1.0.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_Format_Row_DAO {

	/**
	 * Save or update a format row.
	 * If $data contains a valid 'id', it updates; otherwise inserts.
	 * Returns the row ID on insert or update, or false on failure.
	 */
	public static function save( int $post_id, array $data ): int|false {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		// Normalize ISBN if present (FV-007 wiring point — callers must check duplicates before save).
		if ( isset( $data['isbn13'] ) ) {
			$data['isbn13'] = MBS_ISBN_Helper::normalize_isbn( $data['isbn13'] ) ?: null;
		}

		// FV-005: Normalize ASIN — uppercase, strip non-alphanumeric, enforce 10 chars.
		if ( isset( $data['asin'] ) && '' !== (string) $data['asin'] ) {
			$data['asin'] = MBS_ISBN_Helper::normalize_asin( $data['asin'] ) ?: null;
		}

		// Normalize empty date strings to null for DATE columns.
		// MySQL strict mode rejects '' for DATE columns; null is stored as SQL NULL.
		foreach ( array( 'pub_date', 'on_sale_date' ) as $_date_col ) {
			if ( isset( $data[ $_date_col ] ) && '' === (string) $data[ $_date_col ] ) {
				$data[ $_date_col ] = null;
			}
		}

		if ( ! empty( $data['id'] ) ) {
			// Update existing row
			$id = (int) $data['id'];
			unset( $data['id'] ); // do not update primary key
			// CQ-04: Explicit format specifiers per column type (not all-%s).
			$wpdb->update( $table, $data, array( 'id' => $id ), self::get_format_specifiers( $data ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			return $id;
		} else {
			// Insert new row
			$data['post_id'] = $post_id;
			// CQ-04: Explicit format specifiers per column type.
			$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 $wpdb->insert_id ?: false;
		}
	}

	/**
	 * Update an existing format row by ID (metabox expectation).
	 */
	public static function update( int $row_id, array $data ): int|false {
		return self::save( 0, array_merge( $data, array( 'id' => $row_id ) ) );
	}

	/**
	 * Insert a new format row (metabox expectation).
	 *
	 * BUG-02: pass the post_id that the caller already set in $data through to
	 * save() instead of hard-coding 0.  save() overwrites $data['post_id'] with
	 * its $post_id parameter, so passing 0 here caused every new format row to
	 * be stored with post_id = 0, making get_by_post() return nothing on reload.
	 */
	public static function insert( array $data ): int|false {
		$post_id = isset( $data['post_id'] ) ? (int) $data['post_id'] : 0;
		return self::save( $post_id, $data );
	}

	/**
	 * Get a single format row by ID.
	 *
	 * D-002: Column is format_label — matches the schema (VARCHAR 100).
	 */
	public static function get_by_id( int $row_id ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		$row = $wpdb->get_row( $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, format, format_label, subtitle, pub_date, edition_notes, edition, edition_number, edition_type, isbn13, asin, price, page_length, narrator, duration_hh, duration_mm, file_format, audio_sample_id, cover_image_id, audiobook_cover_style, spinning_video_id, video_hover_mode, autographed_available, sort_order, is_deleted, deleted_at, created_at, updated_at, price_type, currency_code, supplier_name, discount_code, on_sale_date, returnability, square_item_id FROM {$table} WHERE id = %d AND is_deleted = 0",
			$row_id
		) );

		return $row ?: null;
	}

	/**
	 * Get all active format rows for a book, ordered by sort_order.
	 * Alias used by templates and metabox.
	 */
	public static function get_by_post( int $post_id ): array {
		return self::get_all_for_post( $post_id );
	}

	/**
	 * Internal implementation.
	 *
	 * D-002: Column is format_label — same correction as get_by_id().
	 */
	private static function get_all_for_post( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		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, format, format_label, subtitle, pub_date, edition_notes, edition, edition_number, edition_type, isbn13, asin, price, page_length, narrator, duration_hh, duration_mm, file_format, audio_sample_id, cover_image_id, audiobook_cover_style, spinning_video_id, video_hover_mode, autographed_available, sort_order, is_deleted, deleted_at, created_at, updated_at, price_type, currency_code, supplier_name, discount_code, on_sale_date, returnability, square_item_id FROM {$table} WHERE post_id = %d AND is_deleted = 0 ORDER BY sort_order ASC",
			$post_id
		) );
	}

	/**
	 * Soft delete a format row.
	 */
	public static function soft_delete( int $row_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		$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( 'id' => $row_id ),
			array( '%d', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Soft delete any format rows for a post that are no longer in the submitted list.
	 */
	public static function soft_delete_missing_for_post( int $post_id, array $keep_ids ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		if ( empty( $keep_ids ) ) {
			$keep_ids = array( 0 );
		}

		$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 ) ) . ")", // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- safe: values cast to int via array_map('intval'); wrapped in $wpdb->prepare()
			current_time( 'mysql', true ),
			$post_id
		) );
	}

	/**
	 * Return all post IDs that have at least one active format row
	 * matching the given format string.
	 *
	 * @param string $format Sanitized format key (e.g. 'ebook', 'paperback').
	 * @return int[]
	 */
	public static function get_post_ids_by_format( string $format ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		$results = $wpdb->get_col( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT DISTINCT post_id FROM {$table}
			  WHERE format = %s
			    AND is_deleted = 0",
			$format
		) );

		return array_map( 'absint', $results ?: array() );
	}

	/**
	 * Get book post ID by normalized ISBN13 (NEW-05 fix).
	 * Used by MBS_Duplicate_Detector::query_by_isbn().
	 */
	public static function get_book_id_by_isbn( string $normalized ): ?int {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		$post_id = $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT post_id FROM {$table} 
			 WHERE isbn13 = %s 
			   AND is_deleted = 0 
			 LIMIT 1",
			$normalized
		) );

		return $post_id ? (int) $post_id : null;
	}

	/**
	 * Get the format row that owns a given ASIN, optionally excluding a row and a book.
	 *
	 * FV-006: Used by the metabox save to detect cross-book ASIN duplicates.
	 * $exclude_post_id prevents same-book cross-format rows from triggering a
	 * false conflict — two editions of the same book sharing an ASIN is the
	 * author's own data and not a cross-book integrity violation (BUG-04 fix).
	 *
	 * BUG-09: Added INNER JOIN to wp_posts with post_status filter so that
	 * format rows belonging to trashed or auto-draft posts are never treated
	 * as live conflicts. A deleted book must not block ISBN/ASIN reuse.
	 *
	 * @param string $asin            Normalised 10-char ASIN.
	 * @param int    $exclude_row_id  Row ID to exclude (the row currently being saved).
	 * @param int    $exclude_post_id Post ID to exclude (the book currently being saved).
	 * @return object|null Row object with {id, post_id, format} on conflict, null if clear.
	 */
	public static function get_format_by_asin( string $asin, int $exclude_row_id = 0, int $exclude_post_id = 0 ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		$row = $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT r.id, r.post_id, r.format
			   FROM {$table} r
			  INNER JOIN {$wpdb->posts} p ON p.ID = r.post_id
			  WHERE r.asin       = %s
			    AND r.is_deleted = 0
			    AND r.post_id   != 0
			    AND r.post_id   != %d
			    AND r.id        != %d
			    AND p.post_status NOT IN ('trash', 'auto-draft')
			  LIMIT 1",
			$asin,
			$exclude_post_id,
			$exclude_row_id
		) );

		return $row ?: null;
	}

	/**
	 * Get the format row that owns a given ISBN-13, optionally excluding a row and a book.
	 *
	 * FV-007: Parallel to get_format_by_asin(). $exclude_post_id prevents same-book
	 * cross-format rows from triggering a false conflict (BUG-04 fix).
	 *
	 * BUG-09: Added INNER JOIN to wp_posts with post_status filter so that
	 * format rows belonging to trashed or auto-draft posts are never treated
	 * as live conflicts. A deleted book must not block ISBN/ASIN reuse.
	 *
	 * @param string $normalized     Normalised 13-digit ISBN (digits only, no hyphens).
	 * @param int    $exclude_row_id  Row ID to exclude (the row currently being saved).
	 * @param int    $exclude_post_id Post ID to exclude (the book currently being saved).
	 * @return object|null Row object with {id, post_id, format} on conflict, null if clear.
	 */
	public static function get_format_by_isbn( string $normalized, int $exclude_row_id = 0, int $exclude_post_id = 0 ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		$row = $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT r.id, r.post_id, r.format
			   FROM {$table} r
			  INNER JOIN {$wpdb->posts} p ON p.ID = r.post_id
			  WHERE r.isbn13     = %s
			    AND r.is_deleted = 0
			    AND r.post_id   != 0
			    AND r.post_id   != %d
			    AND r.id        != %d
			    AND p.post_status NOT IN ('trash', 'auto-draft')
			  LIMIT 1",
			$normalized,
			$exclude_post_id,
			$exclude_row_id
		) );

		return $row ?: null;
	}

	/**
	 * Returns explicit $wpdb format specifiers for each key in $data.
	 *
	 * CQ-04: Using %d for integers and %f for decimals prevents precision
	 * loss in locales that use comma as the decimal separator.
	 *
	  * @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',
			'sort_order',
			'page_length',
			'duration_hh',
			'duration_mm',
			'cover_image_id',
			'audio_sample_id',
			'spinning_video_id',
			'video_hover_mode',
			'autographed_available',
			'is_deleted',
			'edition_number',
		);
		$decimal_cols = array( 'price', 'series_number' );
		$formats      = array();
		foreach ( array_keys( $data ) as $col ) {
			if ( in_array( $col, $int_cols, true ) ) {
				$formats[] = '%d';
			} elseif ( in_array( $col, $decimal_cols, true ) ) {
				$formats[] = '%f';
			} else {
				$formats[] = '%s';
			}
		}
		return $formats;
	}

	/**
	 * Zero out attachment-ID columns on active rows referencing a given attachment.
	 *
	 * Called when a media attachment is deleted from the WordPress library.
	 * Zeroes spinning_video_id, cover_image_id, and audio_sample_id on
	 * active (is_deleted = 0) rows that reference $attachment_id.
	 * Soft-deleted rows are left unchanged.
	 *
	 * @param int $attachment_id WP attachment post ID being deleted.
	 * @return void
	 * @since 1.1.0
	 */
	public static function zero_attachment_columns( int $attachment_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		foreach ( array( 'spinning_video_id', 'cover_image_id', 'audio_sample_id' ) as $col ) {
			$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.UnquotedComplexPlaceholder
					"UPDATE `{$table}` SET `{$col}` = 0 WHERE `{$col}` = %d AND is_deleted = 0",
					$attachment_id
				)
			);
		}
	}
	/**
	 * Hard-delete all format rows for a book, including soft-deleted rows.
	 *
	 * Called from the before_delete_post hook. Removes every row regardless of
	 * is_deleted status so no orphan rows remain after book deletion.
	 * Must be called AFTER MBS_POS_Inventory_DAO::delete_for_post() which
	 * references format row IDs via JOIN.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	/**
	 * Soft-delete all orphaned format rows (post_id = 0) that have not been
	 * soft-deleted yet.
	 *
	 * One-time guard: once the cleanup has run the option
	 * mbs_bug02_orphan_cleanup_done is set and this method becomes a no-op.
	 * The guard is intentional and must not be removed.
	 *
	 * UTC fix (A-005): deleted_at uses current_time('mysql', true) to write
	 * a UTC-normalised timestamp, matching the column convention.
	 *
	 * @since  1.2.0
	 * @return void
	 */
	public static function cleanup_orphaned(): void {
		if ( get_option( 'mbs_bug02_orphan_cleanup_done' ) ) {
			return;
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"UPDATE {$wpdb->prefix}mbs_format_rows
			    SET is_deleted = 1,
			        deleted_at = %s
			  WHERE post_id    = 0
			    AND is_deleted = 0",
			current_time( 'mysql', true )
		) );

		update_option( 'mbs_bug02_orphan_cleanup_done', '1', false );
	}

	/**
	 * Update PUB-tier supply-chain fields on a format row.
	 *
	 * Includes a SHOW COLUMNS guard so callers on legacy installations that
	 * have not yet run MBS_Migration_2_6 are silently skipped rather than
	 * producing a DB error. The guard moves with this method from
	 * MBS_Dummy_Book_Installer::set_format_row_pub_fields() (RF-2).
	 *
	 * @since  1.2.0
	 * @param  int   $row_id Format row ID to update.
	 * @param  array $fields Associative array of column => value pairs
	 *                       (e.g. returnability, price_type, supplier_name).
	 * @return bool  False if the PUB columns are not present; true on update attempt.
	 */
	public static function set_pub_fields( int $row_id, array $fields ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		// Guard: skip silently if PUB columns have not been created yet.
  // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$cols = $wpdb->get_col( 'SHOW COLUMNS FROM ' . $table ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( ! in_array( 'returnability', $cols, true ) ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$table,
			$fields,
			array( 'id' => $row_id ),
			self::get_format_specifiers( $fields ),
			array( '%d' )
		);

		return true;
	}

	public static function hard_delete_for_post( int $post_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';
		// 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
	}

}
