<?php
/**
 * Sheet Data Synchronization Class
 *
 * @package ShopExplorer\GoogleSheets
 * @since 1.0.0
 */

namespace SHOPEXPLORER\GoogleSheets;

use SHOPEXPLORER\Models\OrderDataModel;
use SHOPEXPLORER\Models\ProductDataModel;
use SHOPEXPLORER\Models\SyncDataModel;
use SHOPEXPLORER\Exceptions\GoogleSheetsException;
use SHOPEXPLORER\Exceptions\SyncException;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

/**
 * Sheet Data Synchronization class
 *
 * Handles bidirectional synchronization between WooCommerce and Google Sheets
 *
 * @since 1.0.0
 */
class SHOPEX_SheetDataSync {

	/**
	 * Google Sheets Manager instance
	 *
	 * @var GoogleSheetsManager
	 */
	private $sheets_manager;

	/**
	 * Field mappings for different entity types
	 *
	 * @var array
	 */
	private $field_mappings = [];

	/**
	 * Conflict resolution strategy
	 *
	 * @var string
	 */
	private $conflict_resolution = 'timestamp'; // timestamp, sheets_priority, wc_priority

	/**
	 * Constructor
	 */
	public function __construct() {
		$this->sheets_manager = new GoogleSheetsManager();
		$this->init_default_mappings();
	}

	/**
	 * Initialize default field mappings
	 */
	private function init_default_mappings() {
		$this->field_mappings = [
			'order' => [
				'order_id' => 'A',
				'order_number' => 'B',
				'status' => 'C',
				'customer_name' => 'D',
				'customer_email' => 'E',
				'total' => 'F',
				'currency' => 'G',
				'payment_method' => 'H',
				'date_created' => 'I',
				'date_modified' => 'J',
				'billing_address' => 'K',
				'shipping_address' => 'L',
				'items' => 'M',
				'notes' => 'N',
				'vendor_id' => 'O',
			],
			'product' => [
				'product_id' => 'A',
				'name' => 'B',
				'sku' => 'C',
				'stock_quantity' => 'D',
				'price' => 'E',
				'regular_price' => 'F',
				'sale_price' => 'G',
				'stock_status' => 'H',
				'manage_stock' => 'I',
				'type' => 'J',
				'status' => 'K',
				'vendor_id' => 'L',
				'last_modified' => 'M',
			],
		];
	}

	/**
	 * Sync orders to Google Sheets
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param array  $orders Array of OrderDataModel objects
	 * @param string $sheet_name Sheet name (default: 'Orders')
	 * @return SyncDataModel
	 * @throws SyncException
	 */
	public function sync_orders_to_sheets( $spreadsheet_id, $orders, $sheet_name = 'Orders' ) {
		$sync_data = new SyncDataModel( [
			'entity_type' => 'order',
			'operation' => 'sync',
			'direction' => 'to_sheets',
			'source_data' => [ 'count' => count( $orders ) ],
		] );

		try {
			$sync_data->mark_processing();

			// Prepare headers
			$headers = array_keys( $this->field_mappings['order'] );

			// Prepare data rows
			$rows = [ $headers ]; // Start with headers

			foreach ( $orders as $order ) {
				if ( ! $order instanceof OrderDataModel ) {
					continue;
				}

				$row = $this->map_order_to_sheet_row( $order );
				$rows[] = $row;
			}

			// Update the sheet
			$range = $sheet_name . '!A1:' . $this->get_column_letter( count( $headers ) ) . ( count( $rows ) );
			$success = $this->sheets_manager->update_spreadsheet_data( $spreadsheet_id, $range, $rows );

			if ( $success ) {
				$sync_data->mark_completed( [ 'rows_updated' => count( $rows ) - 1 ] );
			} else {
				$sync_data->mark_failed( 'Failed to update spreadsheet data' );
			}
		} catch ( \Exception $e ) {
			return false;

		}

		return $sync_data;
	}

	/**
	 * Sync orders from Google Sheets
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $sheet_name Sheet name (default: 'Orders')
	 * @return array Array of OrderDataModel objects
	 * @throws SyncException
	 */
	public function sync_orders_from_sheets( $spreadsheet_id, $sheet_name = 'Orders' ) {
		try {
			// Get data from sheets
			$range = $sheet_name . '!A:Z';
			$values = $this->sheets_manager->get_spreadsheet_data( $spreadsheet_id, $range );

			if ( empty( $values ) ) {
				return [];
			}

			// First row should be headers
			$headers = array_shift( $values );
			$orders = [];

			foreach ( $values as $row ) {
				if ( empty( array_filter( $row ) ) ) {
					continue; // Skip empty rows
				}

				$order_data = $this->map_sheet_row_to_order( $row, $headers );
				$orders[] = new OrderDataModel( $order_data );
			}

			return $orders;

		} catch ( \Exception $e ) {
			return false;
		}
	}

	/**
	 * Sync products to Google Sheets
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param array  $products Array of ProductDataModel objects
	 * @param string $sheet_name Sheet name (default: 'Products')
	 * @return SyncDataModel
	 * @throws SyncException
	 */
	public function sync_products_to_sheets( $spreadsheet_id, $products, $sheet_name = 'Products' ) {
		$sync_data = new SyncDataModel( [
			'entity_type' => 'product',
			'operation' => 'sync',
			'direction' => 'to_sheets',
			'source_data' => [ 'count' => count( $products ) ],
		] );

		try {
			$sync_data->mark_processing();

			// Prepare headers
			$headers = array_keys( $this->field_mappings['product'] );

			// Prepare data rows
			$rows = [ $headers ]; // Start with headers

			foreach ( $products as $product ) {
				if ( ! $product instanceof ProductDataModel ) {
					continue;
				}

				$row = $this->map_product_to_sheet_row( $product );
				$rows[] = $row;
			}

			// Update the sheet
			$range = $sheet_name . '!A1:' . $this->get_column_letter( count( $headers ) ) . ( count( $rows ) );
			$success = $this->sheets_manager->update_spreadsheet_data( $spreadsheet_id, $range, $rows );

			if ( $success ) {
				$sync_data->mark_completed( [ 'rows_updated' => count( $rows ) - 1 ] );
			} else {
				$sync_data->mark_failed( 'Failed to update spreadsheet data' );
			}
		} catch ( \Exception $e ) {
						return false;
		}

		return $sync_data;
	}

	/**
	 * Sync products from Google Sheets
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $sheet_name Sheet name (default: 'Products')
	 * @return array Array of ProductDataModel objects
	 * @throws SyncException
	 */
	public function sync_products_from_sheets( $spreadsheet_id, $sheet_name = 'Products' ) {
		try {
			// Get data from sheets
			$range = $sheet_name . '!A:Z';
			$values = $this->sheets_manager->get_spreadsheet_data( $spreadsheet_id, $range );

			if ( empty( $values ) ) {
				return [];
			}

			// First row should be headers
			$headers = array_shift( $values );
			$products = [];

			foreach ( $values as $row ) {
				if ( empty( array_filter( $row ) ) ) {
					continue; // Skip empty rows
				}

				$product_data = $this->map_sheet_row_to_product( $row, $headers );
				$products[] = new ProductDataModel( $product_data );
			}

			return $products;

		} catch ( \Exception $e ) {
						return false;
		}
	}

	/**
	 * Bidirectional sync with conflict detection
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $entity_type Entity type (order, product)
	 * @param array  $local_data Local data array
	 * @param string $sheet_name Sheet name
	 * @return array Sync results
	 * @throws SyncException
	 */
	public function bidirectional_sync( $spreadsheet_id, $entity_type, $local_data, $sheet_name = null ) {
		if ( ! $sheet_name ) {
			$sheet_name = ucfirst( $entity_type ) . 's';
		}

		try {
			// Get remote data
			$method_name = "sync_{$entity_type}s_from_sheets";
			$remote_data = $this->$method_name( $spreadsheet_id, $sheet_name );

			// Detect conflicts and resolve
			$sync_results = $this->resolve_conflicts( $local_data, $remote_data, $entity_type );

			// Apply resolved changes
			if ( ! empty( $sync_results['to_sheets'] ) ) {
				$method_name = "sync_{$entity_type}s_to_sheets";
				$this->$method_name( $spreadsheet_id, $sync_results['to_sheets'], $sheet_name );
			}

			return $sync_results;

		} catch ( \Exception $e ) {
						return false;
		}
	}

	/**
	 * Resolve conflicts between local and remote data
	 *
	 * @param array  $local_data Local data
	 * @param array  $remote_data Remote data
	 * @param string $entity_type Entity type
	 * @return array Resolved data
	 */
	private function resolve_conflicts( $local_data, $remote_data, $entity_type ) {
		$results = [
			'to_sheets' => [],
			'to_local' => [],
			'conflicts' => [],
		];

		// Create lookup arrays
		$local_lookup = [];
		$remote_lookup = [];

		foreach ( $local_data as $item ) {
			$id = $entity_type === 'order' ? $item->order_id : $item->product_id;
			$local_lookup[ $id ] = $item;
		}

		foreach ( $remote_data as $item ) {
			$id = $entity_type === 'order' ? $item->order_id : $item->product_id;
			$remote_lookup[ $id ] = $item;
		}

		// Find items that exist in both
		$common_ids = array_intersect( array_keys( $local_lookup ), array_keys( $remote_lookup ) );

		foreach ( $common_ids as $id ) {
			$local_item = $local_lookup[ $id ];
			$remote_item = $remote_lookup[ $id ];

			$resolution = $this->resolve_single_conflict( $local_item, $remote_item, $entity_type );

			if ( $resolution['action'] === 'update_sheets' ) {
				$results['to_sheets'][] = $local_item;
			} elseif ( $resolution['action'] === 'update_local' ) {
				$results['to_local'][] = $remote_item;
			} elseif ( $resolution['action'] === 'conflict' ) {
				$results['conflicts'][] = [
					'id' => $id,
					'local' => $local_item,
					'remote' => $remote_item,
					'reason' => $resolution['reason'],
				];
			}
		}

		// Items only in local (new items to sync to sheets)
		$local_only = array_diff( array_keys( $local_lookup ), array_keys( $remote_lookup ) );
		foreach ( $local_only as $id ) {
			$results['to_sheets'][] = $local_lookup[ $id ];
		}

		// Items only in remote (new items to sync to local)
		$remote_only = array_diff( array_keys( $remote_lookup ), array_keys( $local_lookup ) );
		foreach ( $remote_only as $id ) {
			$results['to_local'][] = $remote_lookup[ $id ];
		}

		return $results;
	}

	/**
	 * Resolve single conflict between local and remote item
	 *
	 * @param object $local_item Local item
	 * @param object $remote_item Remote item
	 * @param string $entity_type Entity type
	 * @return array Resolution result
	 */
	private function resolve_single_conflict( $local_item, $remote_item, $entity_type ) {
		switch ( $this->conflict_resolution ) {
			case 'timestamp':
				$local_time = strtotime( $local_item->last_modified );
				$remote_time = strtotime( $remote_item->last_modified );

				if ( $local_time > $remote_time ) {
					return [
						'action' => 'update_sheets',
						'reason' => 'Local item is newer',
					];
				} elseif ( $remote_time > $local_time ) {
					return [
						'action' => 'update_local',
						'reason' => 'Remote item is newer',
					];
				} else {
					return [
						'action' => 'no_change',
						'reason' => 'Items are identical',
					];
				}

			case 'sheets_priority':
				return [
					'action' => 'update_local',
					'reason' => 'Sheets has priority',
				];

			case 'wc_priority':
				return [
					'action' => 'update_sheets',
					'reason' => 'WooCommerce has priority',
				];

			default:
				return [
					'action' => 'conflict',
					'reason' => 'Unknown resolution strategy',
				];
		}
	}

	/**
	 * Map order to sheet row
	 *
	 * @param OrderDataModel $order Order data model
	 * @return array
	 */
	private function map_order_to_sheet_row( $order ) {
		return [
			$order->order_id,
			$order->order_number,
			$order->status,
			$order->customer_name,
			$order->customer_email,
			$order->total,
			$order->currency,
			$order->payment_method,
			$order->date_created,
			$order->date_modified,
			$this->format_address( $order->billing_address ),
			$this->format_address( $order->shipping_address ),
			$this->format_order_items( $order->items ),
			$order->notes,
			$order->vendor_id ?? '',
		];
	}

	/**
	 * Map sheet row to order data
	 *
	 * @param array $row Sheet row
	 * @param array $headers Headers array
	 * @return array
	 */
	private function map_sheet_row_to_order( $row, $headers ) {
		$data = [];

		foreach ( $headers as $index => $header ) {
			$value = $row[ $index ] ?? '';

			switch ( $header ) {
				case 'order_id':
					$data['id'] = (int) $value;
					break;
				case 'order_number':
					$data['order_number'] = sanitize_text_field( $value );
					break;
				case 'status':
					$data['status'] = sanitize_text_field( $value );
					break;
				case 'customer_name':
					$data['customer_name'] = sanitize_text_field( $value );
					break;
				case 'customer_email':
					$data['customer_email'] = sanitize_email( $value );
					break;
				case 'total':
					$data['total'] = (float) $value;
					break;
				case 'currency':
					$data['currency'] = sanitize_text_field( $value );
					break;
				case 'payment_method':
					$data['payment_method'] = sanitize_text_field( $value );
					break;
				case 'billing_address':
					$data['billing_address'] = $this->parse_address( $value );
					break;
				case 'shipping_address':
					$data['shipping_address'] = $this->parse_address( $value );
					break;
				case 'items':
					$data['items'] = $this->parse_order_items( $value );
					break;
				case 'notes':
					$data['notes'] = sanitize_textarea_field( $value );
					break;
				case 'vendor_id':
					$data['vendor_id'] = $value !== '' ? (int) $value : null;
					break;
				default:
					$data[ $header ] = sanitize_text_field( $value );
					break;
			}
		}

		return $data;
	}

	/**
	 * Map product to sheet row
	 *
	 * @param ProductDataModel $product Product data model
	 * @return array
	 */
	private function map_product_to_sheet_row( $product ) {
		return [
			$product->product_id,
			$product->name,
			$product->sku,
			$product->stock_quantity ?? '',
			$product->price,
			$product->regular_price,
			$product->sale_price ?? '',
			$product->stock_status,
			$product->manage_stock ? 'yes' : 'no',
			$product->type,
			$product->status,
			$product->vendor_id ?? '',
			$product->last_modified,
		];
	}

	/**
	 * Map sheet row to product data
	 *
	 * @param array $row Sheet row
	 * @param array $headers Headers array
	 * @return array
	 */
	private function map_sheet_row_to_product( $row, $headers ) {
		$data = [];

		foreach ( $headers as $index => $header ) {
			$value = $row[ $index ] ?? '';

			switch ( $header ) {
				case 'product_id':
					$data['id'] = (int) $value;
					break;
				case 'name':
					$data['name'] = sanitize_text_field( $value );
					break;
				case 'sku':
					$data['sku'] = sanitize_text_field( $value );
					break;
				case 'stock_quantity':
					$data['stock_quantity'] = $value !== '' ? (int) $value : null;
					break;
				case 'price':
					$data['price'] = (float) $value;
					break;
				case 'regular_price':
					$data['regular_price'] = (float) $value;
					break;
				case 'sale_price':
					$data['sale_price'] = $value !== '' ? (float) $value : null;
					break;
				case 'stock_status':
					$data['stock_status'] = sanitize_text_field( $value );
					break;
				case 'manage_stock':
					$data['manage_stock'] = strtolower( $value ) === 'yes';
					break;
				case 'type':
					$data['type'] = sanitize_text_field( $value );
					break;
				case 'status':
					$data['status'] = sanitize_text_field( $value );
					break;
				case 'vendor_id':
					$data['vendor_id'] = $value !== '' ? (int) $value : null;
					break;
				default:
					$data[ $header ] = sanitize_text_field( $value );
					break;
			}
		}

		return $data;
	}

	/**
	 * Format address for sheet display
	 *
	 * @param array $address Address array
	 * @return string
	 */
	private function format_address( $address ) {
		if ( empty( $address ) ) {
			return '';
		}

		$parts = array_filter( [
			$address['address_1'] ?? '',
			$address['address_2'] ?? '',
			$address['city'] ?? '',
			$address['state'] ?? '',
			$address['postcode'] ?? '',
			$address['country'] ?? '',
		] );

		return implode( ', ', $parts );
	}

	/**
	 * Parse address from sheet string
	 *
	 * @param string $address_string Address string
	 * @return array
	 */
	private function parse_address( $address_string ) {
		if ( empty( $address_string ) ) {
			return [];
		}

		$parts = array_map( 'trim', explode( ',', $address_string ) );

		return [
			'address_1' => $parts[0] ?? '',
			'address_2' => $parts[1] ?? '',
			'city' => $parts[2] ?? '',
			'state' => $parts[3] ?? '',
			'postcode' => $parts[4] ?? '',
			'country' => $parts[5] ?? '',
		];
	}

	/**
	 * Format order items for sheet display
	 *
	 * @param array $items Order items
	 * @return string
	 */
	private function format_order_items( $items ) {
		if ( empty( $items ) ) {
			return '';
		}

		$formatted_items = [];
		foreach ( $items as $item ) {
			/* translators: %s: is the product id */
			$formatted_items[] = sprintf(
				'%s (x%d)',
				$item['name'] ?? 'Unknown',
				$item['quantity'] ?? 1
			);
		}

		return implode( '; ', $formatted_items );
	}

	/**
	 * Parse order items from sheet string
	 *
	 * @param string $items_string Items string
	 * @return array
	 */
	private function parse_order_items( $items_string ) {
		if ( empty( $items_string ) ) {
			return [];
		}

		$items = [];
		$item_strings = explode( ';', $items_string );

		foreach ( $item_strings as $item_string ) {
			if ( preg_match( '/^(.+?)\s*\(x(\d+)\)$/', trim( $item_string ), $matches ) ) {
				$items[] = [
					'name' => trim( $matches[1] ),
					'quantity' => (int) $matches[2],
				];
			}
		}

		return $items;
	}

	/**
	 * Get column letter from number
	 *
	 * @param int $column_number Column number (1-based)
	 * @return string
	 */
	private function get_column_letter( $column_number ) {
		$letter = '';
		while ( $column_number > 0 ) {
			$column_number--;
			$letter = chr( 65 + ( $column_number % 26 ) ) . $letter;
			$column_number = intval( $column_number / 26 );
		}
		return $letter;
	}

	/**
	 * Set field mappings
	 *
	 * @param string $entity_type Entity type
	 * @param array  $mappings Field mappings
	 */
	public function set_field_mappings( $entity_type, $mappings ) {
		$this->field_mappings[ $entity_type ] = $mappings;
	}

	/**
	 * Get field mappings
	 *
	 * @param string $entity_type Entity type
	 * @return array
	 */
	public function get_field_mappings( $entity_type ) {
		return $this->field_mappings[ $entity_type ] ?? [];
	}

	/**
	 * Set conflict resolution strategy
	 *
	 * @param string $strategy Resolution strategy
	 */
	public function set_conflict_resolution( $strategy ) {
		$this->conflict_resolution = $strategy;
	}

	/**
	 * Get conflict resolution strategy
	 *
	 * @return string
	 */
	public function get_conflict_resolution() {
		return $this->conflict_resolution;
	}
}
