<?php
/**
 * File Path: includes/data/class-mbs-affiliate-store-dao.php
 *
 * Affiliate Store DAO
 *
 * Replaces the mbs_setting_affiliate_custom_stores wp_options JSON blob (RF-012).
 * Table created by MBS_DB_Schema::affiliate_stores() / MBS_Migration_4_22.
 *
 * Store objects received by save_all() follow the JS format produced by
 * admin-earn-more.js: {id, name, url, code, pattern, favicon_url}.
 * The DAO maps these to relational columns:
 *   store_key   <- id (the JS slug+timestamp key, or sanitize_key(name))
 *   store_name  <- name
 *   affiliate_url <- str_replace('{CODE}', code, pattern) when both are set;
 *                   falls back to url.
 *
 * get_all() returns rows in JS-compatible format so MBS_Tab_Affiliate::enqueue()
 * and MBS_Tab_Affiliate_Render::render() can pass them unchanged to the JS layer.
 *
 * @package MyBookShowroom
 * @since   1.3.0
 */

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

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

	// ── Write operations ────────────────────────────────────────────────────

	/**
	 * Replace all stored affiliate stores with the provided list.
	 *
	 * Deletes all existing rows, then inserts each entry from $stores.
	 * Each store array must follow the JS format: {id, name, url, code, pattern, favicon_url}.
	 * The affiliate_url column stores the pre-computed redirect URL (pattern with {CODE}
	 * substituted); this is the value returned to MBS_Affiliate_Rewriter for host-matching.
	 *
	 * Silently skips entries where affiliate_url cannot be derived.
	 *
	 * @param  array $stores Sanitized store arrays from MBS_Tab_Affiliate::save_settings().
	 * @return void
	 * @since  1.3.0
	 */
	public static function save_all( array $stores ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_affiliate_stores';
		$now   = current_time( 'mysql', true ); // UTC (A-005).

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "DELETE FROM `{$table}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture

		foreach ( $stores as $i => $store ) {
			if ( ! is_array( $store ) ) {
				continue;
			}

			// Derive store_key from the JS 'id' field (slug+timestamp), or from name.
			$raw_id    = $store['id'] ?? '';
			$store_key = '' !== $raw_id ? sanitize_key( $raw_id ) : sanitize_key( $store['name'] ?? '' );
			if ( '' === $store_key ) {
				$store_key = 'store_' . ( $i + 1 );
			}

			$store_name  = sanitize_text_field( $store['name'] ?? '' );

			// Build affiliate URL: pattern+code substitution, or raw url as fallback.
			$pattern      = trim( $store['pattern'] ?? '' );
			$code         = trim( $store['code'] ?? '' );
			if ( '' !== $pattern && '' !== $code ) {
				$affiliate_url = esc_url_raw( str_replace( '{CODE}', $code, $pattern ) );
			} else {
				$affiliate_url = esc_url_raw( $store['url'] ?? '' );
			}

			if ( '' === $affiliate_url ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->insert(
				$table,
				array(
					'store_key'     => $store_key,
					'store_name'    => $store_name,
					'affiliate_url' => $affiliate_url,
					'sort_order'    => $i,
					'created_at'    => $now,
					'updated_at'    => $now,
				),
				array( '%s', '%s', '%s', '%d', '%s', '%s' )
			);
		}
	}

	// ── Read operations ─────────────────────────────────────────────────────

	/**
	 * Return all affiliate stores ordered by sort_order.
	 *
	 * Returns each row as an associative array using DB column names.
	 * MBS_Affiliate_Rewriter uses 'affiliate_url' for host-matching and
	 * returns it directly as the rewritten URL.
	 * The JS UI layer uses 'store_key' as the store ID and 'store_name'
	 * as the display label.
	 *
	 * @return array<int, array{store_key: string, store_name: string, affiliate_url: string, sort_order: int}>
	 * @since  1.3.0
	 */
	public static function get_all(): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_affiliate_stores';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT store_key, store_name, affiliate_url, sort_order FROM `{$table}` ORDER BY sort_order ASC",
			ARRAY_A
		);

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