<?php
/**
 * File Path: includes/data/class-mbs-buy-link-dao.php
 *
 * My Book Showroom — Buy Link DAO
 *
 * Data access layer for mbs_buy_links table.
 * Updated to support metabox expectations and renderer needs while preserving
 * all existing functionality.
 *
 */

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

	/**
	 * Get all buy links for a book (shared only on FREE fallback path).
	 */
	public static function get_for_book( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		$results = $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_row_id, retailer_key, custom_store_id, url, locale_us, locale_uk, locale_ca, locale_au, locale_de, locale_jp, embed_snippet, is_direct_author, sort_order, is_deleted, deleted_at, created_at, link_type, link_label, icon_attachment_id FROM {$table} WHERE post_id = %d AND is_deleted = 0 AND format_row_id IS NULL ORDER BY sort_order ASC",
			$post_id
		) );

		return $results ?: array();
	}

	/**
	 * Get all active buy links for a post regardless of format association.
	 * Used by the admin metabox to display every row for editing.
	 */
	public static function get_all_for_post( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		$results = $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_row_id, retailer_key, custom_store_id, url, locale_us, locale_uk, locale_ca, locale_au, locale_de, locale_jp, embed_snippet, is_direct_author, sort_order, is_deleted, deleted_at, created_at, link_type, link_label, icon_attachment_id FROM {$table} WHERE post_id = %d AND is_deleted = 0 ORDER BY sort_order ASC",
			$post_id
		) );

		return $results ?: array();
	}

	/**
	 * Get buy links for a specific format row.
	 */
	public static function get_for_format( int $format_row_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		$results = $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_row_id, retailer_key, custom_store_id, url, locale_us, locale_uk, locale_ca, locale_au, locale_de, locale_jp, embed_snippet, is_direct_author, sort_order, is_deleted, deleted_at, created_at, link_type, link_label, icon_attachment_id FROM {$table} WHERE format_row_id = %d AND is_deleted = 0 ORDER BY sort_order ASC",
			$format_row_id
		) );

		return $results ?: array();
	}

	/**
	 * Insert or update a buy link.
	 * If $data contains a valid 'id', it updates; otherwise inserts.
	 *
	 * D-001: Passes explicit format specifiers. Integer columns require %d.
	 */
	public static function save( array $data ): int|false {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		$defaults = array(
			'post_id'          => 0,
			'format_row_id'    => null,
			'retailer'         => '', // Legacy NOT NULL column — populated from retailer_key below.
			'retailer_key'     => '',
			'custom_store_id'  => null,
			'url'              => '',
			'locale_us'        => null, // S13-13: multi-marketplace locale URLs
			'locale_uk'        => null,
			'locale_ca'        => null,
			'locale_au'        => null,
			'locale_de'        => null,
			'locale_jp'        => null,
			'embed_snippet'    => null,
			'link_type'        => 'buy',
			'link_label'       => null,
			'icon_attachment_id' => null,
			'is_direct_author' => 0,
			'sort_order'       => 0,
			'is_deleted'       => 0,
		);

		$data = wp_parse_args( $data, $defaults );

		// DA-001: When format_row_id is null, remove it from $data entirely so
		// MySQL uses the column DEFAULT (NULL). Leaving it in causes $wpdb to
		// coerce null → 0 via the %d format specifier, which breaks the
		// WHERE format_row_id IS NULL query in get_for_book().
		if ( array_key_exists( 'format_row_id', $data ) && null === $data['format_row_id'] ) {
			unset( $data['format_row_id'] );
		}

		// DA-002: 'retailer' is a legacy NOT NULL column (no DEFAULT in DB schema).
		// All new code uses 'retailer_key'; copy its value into 'retailer' so the
		// NOT NULL constraint is satisfied without requiring a schema migration.
		if ( ( '' === $data['retailer'] || ! isset( $data['retailer'] ) ) && ! empty( $data['retailer_key'] ) ) {
			$data['retailer'] = (string) $data['retailer_key'];
		}

		// Strip null values from $data so $wpdb->insert() only specifies columns
		// we are actually setting; MySQL uses column DEFAULTs for the rest.
		// This prevents INSERT failures when the local DB schema predates a newly
		// added nullable column (e.g. locale_us/uk/ca added in S13-13).
		$data = array_filter( $data, static fn( $v ) => null !== $v );

		if ( ! empty( $data['id'] ) ) {
			// Update existing
			$id = (int) $data['id'];
			unset( $data['id'] );
			$updated = $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' => $id ),
				self::get_format_specifiers( $data ),
				array( '%d' )
			);
			if ( false === $updated ) {
				return false; // F-06: surface a failed UPDATE instead of returning the id.
			}
			return $id;
		}

		// Insert new
		$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;
	}

	/**
	 * Soft delete a buy link (sets is_deleted and deleted_at for consistency
	 * with format rows).
	 */
	public static function soft_delete( int $id ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		$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(
				'is_deleted' => 1,
				'deleted_at' => current_time( 'mysql', true ),
			),
			array( 'id' => $id ),
			array( '%d', '%s' ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Soft delete any buy links 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_buy_links';

		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
		) );
	}

	/**
	 * Soft-delete buy links for a specific format row that are no longer in the submitted list.
	 * Called by MBS_Metabox_Format_Repeater after saving a format row's buy links.
	 *
	 * @param int   $format_row_id  The format row's ID.
	 * @param array $keep_ids       Buy link IDs to preserve.
	 */
	public static function soft_delete_missing_for_format_row( int $format_row_id, array $keep_ids ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		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 format_row_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 ),
			$format_row_id
		) );
	}

	/**
	 * Soft-delete shared (format_row_id IS NULL) buy links for a post that are no longer
	 * in the submitted list. Used by MBS_Metabox_Buy_Links (Global Buy Links metabox).
	 *
	 * @param int   $post_id   The post ID.
	 * @param array $keep_ids  Buy link IDs to preserve.
	 */
	public static function soft_delete_missing_shared_for_post( int $post_id, array $keep_ids ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		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 format_row_id IS NULL
			   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
		) );
	}

	/**
	 * Soft-delete format-level buy links whose parent format row is no longer active.
	 * Called by MBS_Metabox_Format_Repeater after soft-deleting removed format rows.
	 *
	 * @param int   $post_id               The post ID.
	 * @param array $active_format_row_ids  Format row IDs that are still active.
	 */
	public static function soft_delete_orphaned_for_post( int $post_id, array $active_format_row_ids ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		if ( empty( $active_format_row_ids ) ) {
			$active_format_row_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 format_row_id IS NOT NULL
			   AND format_row_id NOT IN (" . implode( ',', array_map( 'intval', $active_format_row_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
		) );
	}

	/**
	 * Returns explicit $wpdb format specifiers for each key in $data.
	 *
	 * D-001: Integer columns in mbs_buy_links mapped to %d.
	 *
	 * @param array $data  Associative array of column→value pairs.
	 * @return string[]
	 */
	private static function get_format_specifiers( array $data ): array {
		$integer_cols = array(
			'id', 'post_id', 'format_row_id', 'sort_order',
			'is_direct_author', 'is_deleted', 'icon_attachment_id',
		);

		$specs = array();
		foreach ( array_keys( $data ) as $col ) {
			$specs[] = in_array( $col, $integer_cols, true ) ? '%d' : '%s';
		}
		return $specs;
	}

	// -------------------------------------------------------------------------
	// Sprint 180: link_type-filtered query methods
	// -------------------------------------------------------------------------

	/**
	 * Get shared buy links only (link_type = 'buy') for a book.
	 *
	 * Use in display paths that should not surface Reader Platform rows.
	 * Replaces the old hardcoded goodreads/storygraph/bookbub filter arrays.
	 *
	 * @param int $post_id Book post ID.
	 * @return array
	 * @since 1.4.0
	 */
	public static function get_buy_links_for_book( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer
		$results = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT id, post_id, format_row_id, retailer_key, custom_store_id, url, locale_us, locale_uk, locale_ca, locale_au, locale_de, locale_jp, embed_snippet, is_direct_author, sort_order, is_deleted, deleted_at, created_at, link_type, link_label, icon_attachment_id FROM {$table} WHERE post_id = %d AND is_deleted = 0 AND format_row_id IS NULL AND link_type = 'buy' ORDER BY sort_order ASC",
			$post_id
		) );
		return $results ?: array();
	}

	/**
	 * Get shared Reader Platform links (link_type = 'platform') for a book.
	 *
	 * Used by section-reader-platforms.php and the Reader Platforms metabox.
	 * Returns global (format_row_id IS NULL) platform rows only.
	 *
	 * @param int $post_id Book post ID.
	 * @return array
	 * @since 1.4.0
	 */
	public static function get_platforms_for_book( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer
		$results = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT id, post_id, format_row_id, retailer_key, custom_store_id, url, embed_snippet, is_direct_author, sort_order, is_deleted, deleted_at, created_at, link_type, link_label, icon_attachment_id FROM {$table} WHERE post_id = %d AND is_deleted = 0 AND format_row_id IS NULL AND link_type = 'platform' ORDER BY sort_order ASC",
			$post_id
		) );
		return $results ?: array();
	}

	// -------------------------------------------------------------------------
	// S13-13: Per-locale URL helpers
	// -------------------------------------------------------------------------

	/**
	 * Return the ordered list of locale column keys for mbs_buy_links.
	 * Used by the buy-links metabox and the front-end locale resolver.
	 *
	 * @return string[]
	 */
	public static function locale_columns(): array {
		return array( 'locale_us', 'locale_uk', 'locale_ca', 'locale_au', 'locale_de', 'locale_jp' );
	}

	/**
	 * Fetch all shared buy links for a post including locale URL columns.
	 * Extends the base SELECT list to include the six locale columns added in 1.9.
	 *
	 * @param int $post_id Book post ID.
	 * @return array[]
	 */
	public static function get_for_book_with_locales( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer; no cache needed for metabox context
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT id, post_id, format_row_id, retailer_key, custom_store_id, url,
				        locale_us, locale_uk, locale_ca, locale_au, locale_de, locale_jp,
				        embed_snippet, is_direct_author, sort_order, is_deleted, deleted_at, created_at,
				        link_type, link_label, icon_attachment_id
				 FROM {$table}
				 WHERE post_id = %d
				   AND is_deleted = 0
				   AND format_row_id IS NULL
				 ORDER BY sort_order ASC",
				$post_id
			),
			ARRAY_A
		) ?? array();
	}
	/**
	 * Hard-delete all buy links for a book and purge associated click records.
	 *
	 * Deletes from mbs_buy_link_clicks first (references mbs_buy_links.id via
	 * post_id), then removes the buy-link 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 hard_delete_for_post( int $post_id ): void {
		global $wpdb;
		$clicks = $wpdb->prefix . 'mbs_buy_link_clicks';
		$links  = $wpdb->prefix . 'mbs_buy_links';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names from $wpdb->prefix
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$clicks} 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
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$links} 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
	}


	/**
	 * Fetch a single active buy-link record by its primary key.
	 *
	 * Returns only non-deleted rows. Used by MBS_Buy_Link_Router to resolve
	 * the /?mbs_click={id} redirect without a direct $wpdb call in the router.
	 *
	 * @param int $id Buy-link row ID.
	 * @return object|null stdClass with id, url, post_id, retailer_key;
	 *                     null if not found or soft-deleted.
	 * @since 1.0.0
	 */
	public static function get_active_by_id( int $id ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_buy_links';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer; PK point lookup
		return $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT id, url, post_id, retailer_key FROM {$table} WHERE id = %d AND is_deleted = 0",
			$id
		) );
	}
}
