<?php
/**
 * Pushes WooCommerce orders to Google Sheets.
 *
 * @package StoreSheet
 */

declare( strict_types=1 );

namespace StoreSheet\Sync\WcToSheets;

defined( 'ABSPATH' ) || exit;

use StoreSheet\API\GoogleSheetsClient;
use StoreSheet\Sync\RowMapper;

class OrderPusher {

	private const TAB = 'Orders';

	/**
	 * Default order statuses synced when the store has not chosen an explicit
	 * `order_statuses` list. Single source of truth — OrderHooks and the settings
	 * sanitizer both reference this so the real-time hook, the scheduled push, and
	 * the UI default never disagree.
	 *
	 * @var string[]
	 */
	public const DEFAULT_STATUSES = array( 'processing', 'completed' );

	/**
	 * WooCommerce core order status slug → stable Sheets tab title. Consumed by
	 * SpreadsheetManager (tab creation) and the per-status routing in push().
	 *
	 * Titles are identity keys in storesheet_row_map — they must never be
	 * localized. Slugs match WC_Order::get_status() (no `wc-` prefix).
	 *
	 * @var array<string,string>
	 */
	public const STATUS_TABS = array(
		'pending'    => 'Orders - Pending',
		'processing' => 'Orders - Processing',
		'on-hold'    => 'Orders - On Hold',
		'completed'  => 'Orders - Completed',
		'cancelled'  => 'Orders - Cancelled',
		'refunded'   => 'Orders - Refunded',
		'failed'     => 'Orders - Failed',
	);

	private GoogleSheetsClient $client;
	private RowMapper $mapper;

	public function __construct( GoogleSheetsClient $client, RowMapper $mapper ) {
		$this->client = $client;
		$this->mapper = $mapper;
	}

	/**
	 * Push a single order. Writes the base Orders row (both modes) and, when the
	 * per-order-status feature is on, mirrors the row to the current status tab
	 * and clears the order's stale row in any other status tab.
	 *
	 * @param int $order_id
	 * @return true|\WP_Error
	 */
	public function push( int $order_id ) {
		$order = wc_get_order( $order_id );
		if ( ! $order instanceof \WC_Order ) {
			return new \WP_Error( 'invalid_order', "Order {$order_id} not found." );
		}

		$settings   = get_option( 'storesheet_settings', array() );
		$per_status = ! empty( $settings['orders_per_status_tabs'] );
		$status     = $order->get_status();

		// Status gate. Per-status ON widens the allowed set to all 7 core
		// statuses (the tabs ARE the status filter); OFF keeps order_statuses.
		$allowed = $per_status
			? array_keys( self::STATUS_TABS )
			: ( $settings['order_statuses'] ?? self::DEFAULT_STATUSES );

		if ( ! in_array( $status, $allowed, true ) ) {
			storesheet()->log( "Order {$order_id} status '{$status}' not in sync list — skipping.", 'debug' );
			return true;
		}

		$base_row_index = $this->get_row_index( self::TAB, $order_id );

		/** This filter is documented in includes/Sync/WcToSheets/ProductPusher.php. */
		if ( apply_filters( 'storesheet_skip_push', false, self::TAB, $base_row_index, $order->get_date_modified(), 'order' ) ) {
			storesheet()->log( "Order {$order_id} conflict skip — not overwriting the Sheets row.", 'debug' );
			return true;
		}

		$row = $this->mapper->order_to_row( $order );

		// 1) Base Orders — authoritative write.
		$result = $this->write_row( self::TAB, $order_id, $row );
		if ( is_wp_error( $result ) ) {
			$this->log_sync( $order_id, 'failed', $result->get_error_message() );
			return $result;
		}
		$this->log_sync( $order_id, 'success' );

		// 2) Per-status mirror — best-effort, non-fatal.
		if ( $per_status && isset( self::STATUS_TABS[ $status ] ) ) {
			$status_tab = self::STATUS_TABS[ $status ];
			$this->ensure_status_tabs_ready();

			$mirror = $this->write_row( $status_tab, $order_id, $row );
			if ( is_wp_error( $mirror ) ) {
				storesheet()->log( "Order {$order_id}: status-tab mirror to '{$status_tab}' failed: " . $mirror->get_error_message(), 'warning' );
			}

			$this->clear_stale_status_rows( $order_id, $status_tab );
		}

		return true;
	}

	/**
	 * Append (new) or batch-update (existing) an order's row on a given tab.
	 *
	 * @param string $tab
	 * @param int    $order_id
	 * @param array  $row
	 * @return array|\WP_Error Client result / WP_Error.
	 */
	private function write_row( string $tab, int $order_id, array $row ) {
		$row_index = $this->get_row_index( $tab, $order_id );

		if ( $row_index ) {
			return $this->client->batch_update(
				array(
					array(
						'range'  => $tab . '!A' . $row_index . ':S' . $row_index,
						'values' => array( $row ),
					),
				)
			);
		}

		$result = $this->client->append( $tab . '!A:S', array( $row ) );
		if ( ! is_wp_error( $result ) ) {
			$new_index = $this->parse_appended_row_index( $result );
			if ( $new_index ) {
				$this->save_row_index( $tab, $order_id, $new_index );
			}
		}
		return $result;
	}

	/**
	 * Create any missing status tabs once per spreadsheet (transient-guarded so
	 * the metadata read happens at most once a day, not on every push). Covers
	 * the adopt / manually-linked-sheet path; the settings toggle also creates
	 * them eagerly on enable.
	 */
	private function ensure_status_tabs_ready(): void {
		$settings = get_option( 'storesheet_settings', array() );
		$sheet_id = $settings['spreadsheet_id'] ?? '';
		if ( '' === $sheet_id ) {
			return;
		}

		$flag = 'storesheet_status_tabs_ready_' . md5( $sheet_id );
		if ( get_transient( $flag ) ) {
			return;
		}

		$result = ( new \StoreSheet\Sheets\SpreadsheetManager( $this->client ) )->ensure_order_status_tabs();
		if ( ! is_wp_error( $result ) ) {
			set_transient( $flag, 1, DAY_IN_SECONDS );
		}
	}

	/**
	 * Clear (blank in place) and unmap the order's row in every status tab
	 * except $keep_tab — this is how a status transition moves the row out of
	 * the tab it used to live in. Best-effort: a clear failure keeps the map
	 * row so the next push retries.
	 *
	 * @param int    $order_id
	 * @param string $keep_tab
	 */
	private function clear_stale_status_rows( int $order_id, string $keep_tab ): void {
		global $wpdb;
		$settings = get_option( 'storesheet_settings', array() );
		$sheet_id = $settings['spreadsheet_id'] ?? '';

		$status_tabs  = array_values( self::STATUS_TABS );
		$placeholders = implode( ', ', array_fill( 0, count( $status_tabs ), '%s' ) );

		$params = array_merge( array( $order_id, $sheet_id ), $status_tabs, array( $keep_tab ) );

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- placeholder count is dynamic (one %s per status tab), not statically countable.
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- {$placeholders} interpolates %s placeholders, not raw data.
				"SELECT tab_name, row_index FROM {$wpdb->prefix}storesheet_row_map WHERE resource = 'order' AND resource_id = %d AND sheet_id = %s AND tab_name IN ( {$placeholders} ) AND tab_name != %s",
				$params
			)
		);

		foreach ( (array) $rows as $stale ) {
			$range  = $stale->tab_name . '!A' . (int) $stale->row_index . ':S' . (int) $stale->row_index;
			$result = $this->client->clear( $range );
			if ( is_wp_error( $result ) ) {
				storesheet()->log( "Order {$order_id}: failed to clear stale row in '{$stale->tab_name}': " . $result->get_error_message(), 'warning' );
				continue;
			}

			$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$wpdb->prefix . 'storesheet_row_map',
				array(
					'tab_name'    => $stale->tab_name,
					'resource'    => 'order',
					'resource_id' => $order_id,
					'sheet_id'    => $sheet_id,
				),
				array( '%s', '%s', '%d', '%s' )
			);
		}
	}

	/**
	 * Push all orders in paginated batches — used for backfill.
	 *
	 * @param int $batch_size
	 * @param int $offset
	 * @return int|\WP_Error Number of orders pushed.
	 */
	public function push_all( int $batch_size = 100, int $offset = 0 ) {
		$orders = wc_get_orders(
			array(
				'limit'   => $batch_size,
				'offset'  => $offset,
				'orderby' => 'ID',
				'order'   => 'ASC',
				'return'  => 'objects',
			)
		);

		if ( empty( $orders ) || ! is_array( $orders ) ) {
			return 0;
		}

		$pushed = 0;
		foreach ( $orders as $order ) {
			$result = $this->push( $order->get_id() );
			if ( ! is_wp_error( $result ) ) {
				++$pushed;
			}
		}

		return $pushed;
	}

	/**
	 * Push orders modified since a given datetime.
	 *
	 * @param string $after  MySQL datetime string.
	 * @param int    $limit
	 * @return int|\WP_Error
	 */
	public function push_recent( string $after, int $limit = 100 ) {
		$orders = wc_get_orders(
			array(
				'limit'         => $limit,
				'date_modified' => '>' . strtotime( $after ),
				'orderby'       => 'date',
				'order'         => 'ASC',
				'return'        => 'objects',
			)
		);

		if ( ! is_array( $orders ) ) {
			return 0;
		}

		$pushed = 0;
		foreach ( $orders as $order ) {
			$result = $this->push( $order->get_id() );
			if ( ! is_wp_error( $result ) ) {
				++$pushed;
			}
		}

		return $pushed;
	}

	// -------------------------------------------------------------------------
	// Helpers
	// -------------------------------------------------------------------------

	private function get_row_index( string $tab, int $order_id ): ?int {
		global $wpdb;
		$settings = get_option( 'storesheet_settings', array() );
		$sheet_id = $settings['spreadsheet_id'] ?? '';

		$row = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				"SELECT row_index FROM {$wpdb->prefix}storesheet_row_map WHERE tab_name = %s AND resource = 'order' AND resource_id = %d AND sheet_id = %s LIMIT 1",
				$tab,
				$order_id,
				$sheet_id
			)
		);

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

	private function save_row_index( string $tab, int $order_id, int $row_index ): void {
		global $wpdb;
		$settings = get_option( 'storesheet_settings', array() );
		$sheet_id = $settings['spreadsheet_id'] ?? '';

		$wpdb->replace( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prefix . 'storesheet_row_map',
			array(
				'tab_name'    => $tab,
				'resource'    => 'order',
				'resource_id' => $order_id,
				'row_index'   => $row_index,
				'sheet_id'    => $sheet_id,
			),
			array( '%s', '%s', '%d', '%d', '%s' )
		);
	}

	private function parse_appended_row_index( array $result ): ?int {
		$range = $result['updates']['updatedRange'] ?? '';
		if ( preg_match( '/(\d+)$/', $range, $m ) ) {
			return (int) $m[1];
		}
		return null;
	}

	private function log_sync( int $order_id, string $status, string $message = '', string $tab = self::TAB ): void {
		global $wpdb;
		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prefix . 'storesheet_sync_log',
			array(
				'direction'   => 'wc_to_sheets',
				'resource'    => 'order',
				'resource_id' => $order_id,
				'tab_name'    => $tab,
				'status'      => $status,
				'message'     => $message,
			),
			array( '%s', '%s', '%d', '%s', '%s', '%s' )
		);
	}
}
