<?php
/**
 * CRUD operations for sort rules.
 *
 * @package Smart_Product_Sort
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class SPSORT_Sort_Rules
 */
class SPSORT_Sort_Rules {

	/**
	 * Cache group name.
	 *
	 * @var string
	 */
	const CACHE_GROUP = 'spsort_sort_rules';

	/**
	 * Get allowed sort_by values mapped to translatable labels.
	 *
	 * @return array<string,string>
	 */
	public static function get_sort_options() {
		$options = array(
			'date'       => __( 'Date', 'smart-product-sort' ),
			'price'      => __( 'Price', 'smart-product-sort' ),
			'name'       => __( 'Name', 'smart-product-sort' ),
			'popularity' => __( 'Popularity', 'smart-product-sort' ),
			'rating'     => __( 'Rating', 'smart-product-sort' ),
			'rand'       => __( 'Random', 'smart-product-sort' ),
			'menu_order' => __( 'Menu Order', 'smart-product-sort' ),
		);

		/**
		 * Filter the available sort options.
		 *
		 * Pro uses this to add stock_status, custom_field, discount, review_count.
		 *
		 * @param array $options Sort options as key => label.
		 */
		return apply_filters( 'spsort_sort_options', $options );
	}

	/**
	 * Get the full table name.
	 *
	 * @return string
	 */
	private static function table_name() {
		global $wpdb;
		return $wpdb->prefix . SPSORT_Install::TABLE;
	}

	/**
	 * Clear all plugin caches.
	 *
	 * @return void
	 */
	private static function flush_cache() {
		wp_cache_delete( 'all', self::CACHE_GROUP );
		wp_cache_delete( 'all_active', self::CACHE_GROUP );

		if ( function_exists( 'wp_cache_flush_group' ) ) {
			wp_cache_flush_group( self::CACHE_GROUP );
		}
	}

	/**
	 * Get all rules, ordered by priority ASC.
	 *
	 * @param bool $active_only Return only active rules.
	 * @return array<int,object>
	 */
	public static function get_all( $active_only = false ) {
		global $wpdb;

		$cache_key = $active_only ? 'all_active' : 'all';
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false !== $cached ) {
			return $cached;
		}

		$table = self::table_name();

		if ( $active_only ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$results = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i WHERE is_active = 1 ORDER BY priority ASC, id ASC', $table )
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$results = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i ORDER BY priority ASC, id ASC', $table )
			);
		}

		wp_cache_set( $cache_key, $results, self::CACHE_GROUP );

		return $results;
	}

	/**
	 * Get a single rule by ID.
	 *
	 * @param int $id Rule ID.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;

		$id        = absint( $id );
		$cache_key = 'rule_' . $id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false !== $cached ) {
			return $cached;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table_name(), $id )
		);

		wp_cache_set( $cache_key, $result, self::CACHE_GROUP );

		return $result;
	}

	/**
	 * Get the active rule for a given category (falls back to global rule).
	 *
	 * @param int|null $category_id WooCommerce product category term ID.
	 * @return object|null
	 */
	public static function get_active_for_category( $category_id = null ) {
		global $wpdb;

		$cache_key = 'active_cat_' . ( $category_id ? absint( $category_id ) : 'global' );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false !== $cached ) {
			return $cached;
		}

		$table = self::table_name();

		// Try category-specific rule first.
		$rule = null;
		if ( $category_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$rule = $wpdb->get_row(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE is_active = 1 AND category_id = %d ORDER BY priority ASC LIMIT 1',
					$table,
					absint( $category_id )
				)
			);
		}

		// Fallback to global rule (no category).
		if ( ! $rule ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$rule = $wpdb->get_row(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE is_active = 1 AND category_id IS NULL ORDER BY priority ASC LIMIT 1',
					$table
				)
			);
		}

		wp_cache_set( $cache_key, $rule, self::CACHE_GROUP );

		return $rule;
	}

	/**
	 * Insert a new rule.
	 *
	 * @param array $data Rule data.
	 * @return int|false Inserted ID or false on failure.
	 */
	public static function insert( array $data ) {
		global $wpdb;

		$clean = self::sanitize( $data );
		if ( ! $clean ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$inserted = $wpdb->insert(
			self::table_name(),
			$clean,
			self::get_formats( $clean )
		);

		if ( $inserted ) {
			self::flush_cache();
			return (int) $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Update an existing rule.
	 *
	 * @param int   $id   Rule ID.
	 * @param array $data New data.
	 * @return bool
	 */
	public static function update( $id, array $data ) {
		global $wpdb;

		$clean = self::sanitize( $data );
		if ( ! $clean ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			self::table_name(),
			$clean,
			array( 'id' => absint( $id ) ),
			self::get_formats( $clean ),
			array( '%d' )
		);

		if ( false !== $updated ) {
			self::flush_cache();
		}

		return false !== $updated;
	}

	/**
	 * Delete a rule by ID.
	 *
	 * @param int $id Rule ID.
	 * @return bool
	 */
	public static function delete( $id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->delete(
			self::table_name(),
			array( 'id' => absint( $id ) ),
			array( '%d' )
		);

		if ( $deleted ) {
			self::flush_cache();
		}

		return (bool) $deleted;
	}

	/**
	 * Sanitize and validate input data.
	 *
	 * @param array $data Raw data.
	 * @return array|false Sanitized data or false on validation error.
	 */
	private static function sanitize( array $data ) {
		$clean = array();

		$clean['rule_name'] = sanitize_text_field( $data['rule_name'] ?? '' );
		if ( empty( $clean['rule_name'] ) ) {
			return false;
		}

		$sort_by = sanitize_key( $data['sort_by'] ?? '' );
		if ( ! array_key_exists( $sort_by, self::get_sort_options() ) ) {
			return false;
		}
		$clean['sort_by'] = $sort_by;

		$clean['sort_order'] = in_array( strtoupper( $data['sort_order'] ?? 'DESC' ), array( 'ASC', 'DESC' ), true )
			? strtoupper( $data['sort_order'] )
			: 'DESC';

		$clean['category_id'] = ! empty( $data['category_id'] ) ? absint( $data['category_id'] ) : null;
		$clean['is_active']   = isset( $data['is_active'] ) ? (int) (bool) $data['is_active'] : 1;
		$clean['priority']    = isset( $data['priority'] ) ? absint( $data['priority'] ) : 10;

		return $clean;
	}

	/**
	 * Return sprintf format strings for wpdb based on column names.
	 *
	 * @param array $data Sanitized data.
	 * @return array<int,string>
	 */
	private static function get_formats( array $data ) {
		$map = array(
			'rule_name'   => '%s',
			'sort_by'     => '%s',
			'sort_order'  => '%s',
			'category_id' => '%d',
			'is_active'   => '%d',
			'priority'    => '%d',
		);

		$formats = array();
		foreach ( array_keys( $data ) as $col ) {
			$formats[] = $map[ $col ] ?? '%s';
		}

		return $formats;
	}
}
