<?php
namespace Rafgrid;

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

/**
 * Supplier price feed sync: CSV from URL with headers sku,price,stock (stock optional).
 */
class Feed {

	/**
	 * @param string $url Feed URL.
	 * @return array|\WP_Error {updated, not_found, errors}
	 */
	public static function sync_from_url( $url ) {
		$url = esc_url_raw( $url );
		if ( '' === $url ) {
			return new \WP_Error( 'rafgrid_bad_url', __( 'Empty feed URL.', 'rafgrid' ) );
		}
		$valid = Security::validate_outbound_url( $url );
		if ( is_wp_error( $valid ) ) {
			return $valid;
		}
		$res = wp_remote_get( $url, array( 'timeout' => 30 ) );
		if ( is_wp_error( $res ) ) {
			return $res;
		}
		if ( 200 !== (int) wp_remote_retrieve_response_code( $res ) ) {
			return new \WP_Error( 'rafgrid_feed_http', __( 'Feed returned a non-200 status.', 'rafgrid' ) );
		}
		return self::sync_from_csv( (string) wp_remote_retrieve_body( $res ) );
	}

	/**
	 * @param string $csv CSV text with header row containing sku and price columns.
	 * @return array {updated, not_found, errors}
	 */
	public static function sync_from_csv( $csv ) {
		$lines = preg_split( "/\r\n|\n|\r/", trim( $csv ) );
		if ( count( $lines ) < 2 ) {
			return array( 'updated' => 0, 'not_found' => 0, 'errors' => array( 'empty feed' ) );
		}
		$header = array_map( static fn( $h ) => strtolower( trim( $h ) ), str_getcsv( array_shift( $lines ) ) );
		$idx    = array_flip( $header );
		if ( ! isset( $idx['sku'] ) || ! isset( $idx['price'] ) ) {
			return array( 'updated' => 0, 'not_found' => 0, 'errors' => array( 'feed must have sku,price headers' ) );
		}
		$updated = 0;
		$missing = 0;
		$errors  = array();
		foreach ( $lines as $line ) {
			if ( '' === trim( (string) $line ) ) {
				continue;
			}
			$cells = str_getcsv( $line );
			$sku   = trim( (string) ( $cells[ $idx['sku'] ] ?? '' ) );
			if ( '' === $sku ) {
				++$missing;
				continue;
			}
			$id = self::find_by_sku( $sku );
			if ( ! $id ) {
				++$missing;
				continue;
			}
			$price = trim( (string) ( $cells[ $idx['price'] ] ?? '' ) );
			if ( '' !== $price && is_numeric( $price ) ) {
				Columns::save_value( (int) $id, '_regular_price', $price );
			}
			if ( isset( $idx['stock'] ) && '' !== trim( (string) ( $cells[ $idx['stock'] ] ?? '' ) ) ) {
				Columns::save_value( (int) $id, '_stock', (int) $cells[ $idx['stock'] ] );
			}
			++$updated;
		}
		return array( 'updated' => $updated, 'not_found' => $missing, 'errors' => $errors );
	}

	/**
	 * Find product ID by SKU: Woo lookup first, raw postmeta fallback
	 * (products created outside WC CRUD may miss the lookup table).
	 *
	 * @param string $sku SKU.
	 * @return int
	 */
	public static function find_by_sku( $sku ): int {
		if ( function_exists( 'wc_get_product_id_by_sku' ) ) {
			$id = (int) wc_get_product_id_by_sku( $sku );
			if ( $id ) {
				return $id;
			}
		}
		$cached = wp_cache_get( 'sku_' . md5( $sku ), 'rafgrid' );
		if ( false !== $cached ) {
			return (int) $cached;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Fallback SKU lookup for products the WooCommerce lookup table misses; cached below.
		$id = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT pm.post_id FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE pm.meta_key = '_sku' AND pm.meta_value = %s AND p.post_type IN ('product','product_variation') LIMIT 1",
				$sku
			)
		);
		wp_cache_set( 'sku_' . md5( $sku ), $id, 'rafgrid', MINUTE_IN_SECONDS );
		return $id;
	}
}