<?php
/**
 * Google Sheets Sync Helper
 *
 * Handles batch syncing from a Google Spreadsheet.
 * This class is intentionally stateless and contains
 * no cron or task-management logic.
 *
 * @package BwWinnersGlobalSite
 */

namespace BwWinnersGlobalSite\Sync;

require_once plugin_dir_path( \BwWinnersGlobalSite\PLUGIN_PATH ) . '/vendor/autoload.php';

if ( ! class_exists( __NAMESPACE__ . '\Google_Sync' ) ) {


	class Google_Sync {

		private const BW_WINNERS_CREDENTIALS_DIR = WP_CONTENT_DIR . '/uploads/bw-winners';
		private const BW_WINNERS_CREDENTIALS_NAME = 'google-service-account.json';

		private $service;

		/**
		 * Constructor.
		 *
		 * Initialize Google client, auth, etc.
		 */
		public function __construct() {
			add_action( 'admin_init', [ $this, 'upload_credentials' ] );
		}

		public function save_google_credentials_file( array $file ) {

			if ( ! isset( $file['error'] ) || $file['error'] !== UPLOAD_ERR_OK ) {
				throw new \RuntimeException( 'Upload failed.' );
			}

			// Read and validate JSON
			$json = file_get_contents( $file['tmp_name'] );
			$data = json_decode( $json, true );

			if ( json_last_error() !== JSON_ERROR_NONE ) {
				throw new \RuntimeException( 'Invalid JSON file.' );
			}

			// Validate required Google keys
			$required = [
				'type',
				'project_id',
				'private_key',
				'client_email',
				'token_uri',
			];

			foreach ( $required as $key ) {
				if ( empty( $data[ $key ] ) ) {
					throw new \RuntimeException( 'Invalid Google service account file.' );
				}
			}

			// Ensure directory exists
			if ( ! file_exists( self::BW_WINNERS_CREDENTIALS_DIR ) ) {
				wp_mkdir_p( self::BW_WINNERS_CREDENTIALS_DIR );
			}

			// Ensure .htaccess exists
			$htaccess = trailingslashit( self::BW_WINNERS_CREDENTIALS_DIR ) . '.htaccess';

			if ( ! file_exists( $htaccess ) ) {
				file_put_contents( $htaccess, "Deny from all\n" );
			}

			$path = self::BW_WINNERS_CREDENTIALS_DIR . '/' . self::BW_WINNERS_CREDENTIALS_NAME;

			// Move securely
			if ( ! move_uploaded_file( $file['tmp_name'], $path ) ) {
				throw new \RuntimeException( 'Could not save credentials file.' );
			}
			chmod( $path, 0600 );
		}

		public function upload_credentials () {

			if (
				empty( $_FILES['google_credentials'] ) ||
				empty( $_POST['bw_google_nonce'] ) ||
				! wp_verify_nonce( $_POST['bw_google_nonce'], 'bw_google_upload' )
			) {
				return;
			}

			if ( ! current_user_can( 'manage_options' ) ) {
				return;
			}

			$file = $_FILES['google_credentials'];

			try {
				$this->save_google_credentials_file( $file );
				wp_safe_redirect( add_query_arg( 'uploaded', '1', wp_get_referer() ) );
				exit;
			} catch ( \Throwable $e ) {
				wp_die( esc_html( $e->getMessage() ) );
			}
		}

		private function get_service () {

			if ( ! isset( $this->service ) ) {

				$path = self::BW_WINNERS_CREDENTIALS_DIR . '/' . self::BW_WINNERS_CREDENTIALS_NAME;

				if ( ! $path || ! file_exists( $path ) ) {
					throw new \RuntimeException( 'Google credentials missing.' );
				}

				$client = new \Google_Client();
				$client->setAuthConfig( $path );
				$client->addScope( \Google_Service_Sheets::SPREADSHEETS );

				$this->service = new \Google_Service_Sheets( $client );
			}


			return $this->service;
		}

		public function get_credentials () {

			$path = self::BW_WINNERS_CREDENTIALS_DIR . '/' . self::BW_WINNERS_CREDENTIALS_NAME;

			if ( ! $path || ! file_exists( $path ) ) {
				return null;
			}

			$file_contents = file_get_contents( $path );

			if ( $file_contents === false ) {
				return null;
			}

			$data = json_decode( $file_contents, true );

			if ( $data === null && json_last_error() !== JSON_ERROR_NONE ) {
				return null;
			}

			return [
				'client_email' => $data[ 'client_email' ]
			];
		}

		/**
		 * Sync a single batch of rows from the spreadsheet.
		 *
		 * @param int $offset     Row offset for batching.
		 * @param int $batch_size Number of rows to fetch.
		 *
		 * @return array {
		 *     @type int  $count Number of rows processed.
		 *     @type bool $done  Whether the sync is complete.
		 * }
		 */
		public function sync_batch( string $spreadsheet_id, int $offset, int $batch_size ): array {

			try {
				$service = $this->get_service();

				$spreadsheet = $service->spreadsheets->get( $spreadsheet_id );
				$first_sheet = $spreadsheet->getSheets()[0]->getProperties()->getTitle();

				$header_range = "{$first_sheet}!A1:S1";
				$header_response = $service->spreadsheets_values->get( $spreadsheet_id, $header_range );
				$headers = $header_response->getValues()[0];

				$start_row      = 2 + $offset; // skip header
				$end_row        = $start_row + $batch_size - 1;
				$range          = "{$first_sheet}!A{$start_row}:S{$end_row}";
				$response = $service->spreadsheets_values->get( $spreadsheet_id, $range );
				$values   = $response->getValues();
			} catch ( \Throwable $e ) {
				// Let cron fail this task cleanly
				throw new \RuntimeException( 'Google Sheets fetch failed: ' . $e->getMessage(), 0, $e );
			}

			$count = 0;

			if ( !empty( $headers ) && ! empty( $values ) ) {
				foreach ( $values as $row ) {
					try {
						$data = [];

						foreach ( $headers as $index => $key ) {
							$data[ trim( $key ) ] = $row[ $index ] ?? null;
						}

						if ( ! empty( $data['Award Score'] ) ) {
							$this->add_entry( $data );
						} else if (
							! empty( $data['Award Name'] ) &&
								in_array( trim( $data['Award Name'] ), [ 'Double Gold', 'Gold', 'Silver', 'Bronze' ], true )
						) {
							$this->add_entry( $data );
						} else if ( ! empty( $data['Product ID'] ) ) {
							$this->add_product_award( $data );
						} else if ( ! empty( $data['Brand ID'] ) ) {
							$this->add_brand_award( $data );
						}

					} catch ( \Throwable $e ) {
						// Log and continue — DO NOT rethrow
						error_log( sprintf( '[BW Sync] Row %d failed: %s', $start_row + $row_index, $e->getMessage() ) );
					}

					$count++;
				}
			}

			// Check if this is the last batch
			$done = empty( $values ) || count( $values ) < $batch_size;

			return [
				'count' => $count,
				'done'  => $done,
			];
		}

		public function get_spreadsheets () {

			$spreadsheets = [];

			$google_sheets = \BwWinnersGlobalSite\Options::get_option( 'google_sheets' );

			if ( ! is_array( $google_sheets ) ) {
				return [];
			}

			foreach ($google_sheets as $google_sheet_id ) {
				
				if ( empty( $google_sheet_id ) ) {
					continue;
				}

				try {
					$service = $this->get_service();

					$spreadsheet = $service->spreadsheets->get( $google_sheet_id );

					$spreadsheet_title = $spreadsheet->getProperties()->getTitle();

					$first_sheet = $spreadsheet->getSheets()[0]->getProperties()->getTitle();

					$spreadsheets[$google_sheet_id] = [
						'error' => false,
						'title' => $spreadsheet_title,
						'sheet' => $first_sheet
					];

				} catch ( \Throwable $e ) {
					$spreadsheets[$google_sheet_id] = [
						'error' => [
							'message' => 'Google Sheets fetch failed. ' . $e->getMessage(),
							'details' => $e->getMessage()
						]
					];
				}
			}

			return $spreadsheets;
		}

		public function add_entry ( $data ) {
			$entry_map = [
				'Entry ID' => [ 'entry', 'import_id' ],
				'Award Year' => [ 'entry', 'year' ],
				'Award Score' => [ 'entry', 'score' ],
				'Award Name' => [ 'entry', '_score' ],
				'Competition Product Category' => [ 'entry', 'competition_product_category' ],

				'Product ID' => [ 'product', 'import_id' ],
				'Product Name' => [ 'product', 'name' ],
				'Type' => [ 'product', 'type' ],
				'CategoryID' => [ 'product', '_category_id' ],
				'Category' => [ 'product', '_categories' ],
				'Country' => [ 'product', 'country' ],

				'Brand ID' => [ 'brand', 'import_id' ],
				'Brand Name' => [ 'brand', 'name' ],

				'Competition Name' => [ 'competition', 'name' ],
				'Competition Type' => [ 'competition', 'type' ]
			];

			$entities = [];
			foreach( $entry_map as $key => $path ) {
				$this->set_array_path( $entities, $path, isset( $data[ $key ] ) ? $data[ $key ] : null );
			}

			if ( empty( $entities['entry']['import_id'] ) ) {
				unset( $entities['entry']['import_id'] );
			}

			if ( empty( $entities['product']['import_id'] ) ) {
				unset( $entities['product']['import_id'] );
			}

			if ( empty( $entities['brand']['import_id'] ) ) {
				unset( $entities['brand']['import_id'] );
			}

			$competition_id = $this->update_competition( $entities['competition'] );
			$entities['entry']['competition_id'] = $competition_id;

			$brand_id = $this->update_brand( $entities['brand'] );
			$entities['product']['brand_id'] = $brand_id;

			$product_id = $this->update_product( $entities['product'] );
			$entities['entry']['product_id'] = $product_id;

			$this->update_entry( $entities['entry'] );
		}

		public function add_product_award ( $data ) {
			$product_award_map = [
				'Award Country' => [ 'product_award', 'country' ],
				'Award Region' => [ 'product_award', 'region' ],
				'Award Name' => [ 'product_award', 'name' ],
				'Award Year' => [ 'product_award', 'year' ],

				'Product ID' => [ 'product', 'import_id' ],
				'Product Name' => [ 'product', 'name' ],
				'Type' => [ 'product', 'type' ],
				'CategoryID' => [ 'product', '_category_id' ],
				'Category' => [ 'product', '_categories' ],
				'Country' => [ 'product', 'country' ],

				'Brand ID' => [ 'brand', 'import_id' ],
				'Brand Name' => [ 'brand', 'name' ],

				'Competition Name' => [ 'competition', 'name' ],
				'Competition Type' => [ 'competition', 'type' ]
			];

			$entities = [];
			foreach( $product_award_map as $key => $path ) {
				$this->set_array_path( $entities, $path, isset( $data[ $key ] ) ? $data[ $key ] : null );
			}

			if ( empty( $entities['product']['import_id'] ) ) {
				unset( $entities['product']['import_id'] );
			}

			if ( empty( $entities['brand']['import_id'] ) ) {
				unset( $entities['brand']['import_id'] );
			}

			$competition_id = $this->update_competition( $entities['competition'] );
			$entities['product_award']['competition_id'] = $competition_id;

			$brand_id = $this->update_brand( $entities['brand'] );
			$entities['product']['brand_id'] = $brand_id;

			$product_id = $this->update_product( $entities['product'] );
			$entities['product_award']['product_id'] = $product_id;

			$this->update_product_award( $entities['product_award'] );
		}

		public function add_brand_award ( $data ) {
			$brand_award_map = [
				'Award Country' => [ 'brand_award', 'country' ],
				'Award Region' => [ 'brand_award', 'region' ],
				'Award Name' => [ 'brand_award', 'name' ],
				'Award Year' => [ 'brand_award', 'year' ],

				'Brand ID' => [ 'brand', 'import_id' ],
				'Brand Name' => [ 'brand', 'name' ],

				'Competition Name' => [ 'competition', 'name' ],
				'Competition Type' => [ 'competition', 'type' ]
			];

			$entities = [];
			foreach( $brand_award_map as $key => $path ) {
				$this->set_array_path( $entities, $path, isset( $data[ $key ] ) ? $data[ $key ] : null );
			}

			if ( empty( $entities['brand']['import_id'] ) ) {
				unset( $entities['brand']['import_id'] );
			}

			$competition_id = $this->update_competition( $entities['competition'] );
			$entities['brand_award']['competition_id'] = $competition_id;

			$brand_id = $this->update_brand( $entities['brand'] );
			$entities['brand_award']['brand_id'] = $brand_id;

			$this->update_brand_award( $entities['brand_award'] );

		}

		public function update_competition( $competition ) {
			global $bw_winners_global_site;

			$competition['name'] = preg_replace('/^[\s\d]+|[\s\d]+$/', '', $competition['name'] );

			// check if the competition already has a post

			$competition_id = $bw_winners_global_site->entities->get_competition_id( $competition['name'] );

			if ( ! $competition_id ) {
				// create a new competition post
				$competition_id = wp_insert_post( [
					'post_title' => isset( $competition['name'] ) ? $competition['name'] : '',
					'post_content' => empty( $competition['name'] ) ? '<!-- wp:paragraph --><p></p><!-- /wp:paragraph -->' : '',
					'post_status' => 'private',
					'post_type' => 'competition'
				], true );

				if ( is_wp_error( $competition_id ) ) {
					trigger_error( $competition_id->get_error_message(), E_USER_WARNING );
					return;
				}
			}

			$competition['id'] = $competition_id;

			$competition_id = $bw_winners_global_site->entities->update_competition( $competition );
			
			return $competition_id;
		}

		public function update_brand( $brand ) {
			global $bw_winners_global_site;

			// check if the brand already has a post

			if ( isset( $brand['import_id'] ) ) {
				$brand_id = $bw_winners_global_site->entities->get_brand_id( $brand['import_id'] );
			}

			if ( ! isset( $brand_id ) ) {
				$brand_id = $bw_winners_global_site->entities->get_brand_id_from_name( $brand['name'] );
			}

			if ( ! $brand_id ) {
				// create a new brand post
				$brand_id = wp_insert_post( [
					'post_title' => isset( $brand['name'] ) ? $brand['name'] : '',
					'post_content' => empty( $brand['name'] ) ? '<!-- wp:paragraph --><p></p><!-- /wp:paragraph -->' : '',
					'post_status' => 'private',
					'post_type' => 'brand'
				], true );

				if ( is_wp_error( $brand_id ) ) {
					trigger_error( $brand_id->get_error_message(), E_USER_WARNING );
					return;
				}
			}
			
			$brand['id'] = $brand_id;

			$brand_id = $bw_winners_global_site->entities->update_brand( $brand );

			return $brand_id;
		}

		public function update_product( $product ) {
			global $bw_winners_global_site;

			if ( ! empty( $product['_categories'] ) ) {
				$categories = explode( ',', $product['_categories'] );
				array_filter( array_map( '\trim', $categories ) );
				
				$price = $this->product_categories( $product['_categories'] );

				if ( ! empty( $price ) ) {
					$product['price'] = $price;
				}

				if ( ! empty( $categories ) ) {
					$product['categories'] = implode( ',', $categories );
				}
			}

			// check if the product already has a post

			if ( isset( $product['import_id'] ) ) {
				$product_id = $bw_winners_global_site->entities->get_product_id( $product['import_id'] );
			}

			if ( ! isset( $product_id ) ) {
				$product_id = $bw_winners_global_site->entities->get_product_id_from_name( $product['name'], $product['brand_id'] );
			}

			if ( ! $product_id ) {
				// create a new bw-product post
				$product_id = wp_insert_post( [
					'post_title' => isset( $product['name'] ) ? $product['name'] : '',
					'post_content' => empty( $product['name'] ) ? '<!-- wp:paragraph --><p></p><!-- /wp:paragraph -->' : '',
					'post_status' => 'private',
					'post_type' => 'bw-product'
				], true );

				if ( is_wp_error( $product_id ) ) {
					trigger_error( $product_id->get_error_message(), E_USER_WARNING );
					return;
				}
			}
			
			$product['id'] = $product_id;

			$product_id = $bw_winners_global_site->entities->update_product( $product );

			if ( $product_id && ! empty( $categories ) ) {

				$product_terms = array_map(
					function ( $name ) {
			
						if ( empty( $name ) )  return false;

						$term = get_term_by( 'name', $name, 'product-category' );

						if ($term) {
							return $term->term_id;
						}

						$term = wp_insert_term( $name, 'product-category' );

						if ( is_wp_error( $term ) ) {
							trigger_error( $term->get_error_message(), E_USER_WARNING );
							return false; // failed to create post
						}

						return $term['term_id'];
					},
					$categories
				);

				$added_terms = wp_set_post_terms( $product_id, array_filter($product_terms), 'product-category' );

				if ( is_wp_error( $added_terms ) ) {
					trigger_error( $added_terms->get_error_message(), E_USER_WARNING );
				}
			}


			return $product_id;
		}

		public function update_entry( $entry ) {
			global $bw_winners_global_site;


			if ( empty( $entry['score'] ) && isset( $entry['_score'] ) ) {
				// Set the score based on award

				$options = \BwWinnersGlobalSite\Options::get_options();

				$awardToScore = [
					'Double Gold' => isset ( $options['display_score_as']['double_gold'] ) ? intval( $options['display_score_as']['double_gold'] ) : 96,
					'Gold' =>  isset ( $options['display_score_as']['gold'] ) ? intval( $options['display_score_as']['gold'] ) : 91,
					'Silver' =>  isset ( $options['display_score_as']['silver'] ) ? intval( $options['display_score_as']['silver'] ) : 86,
					'Bronze' =>  isset ( $options['display_score_as']['bronze'] ) ? intval( $options['display_score_as']['bronze'] ) : 81
				];

				if ( isset( $awardToScore[$entry['_score']] ) ) {
					$entry['score'] = $awardToScore[$entry['_score']];
				}
			}

			$entry_id = $bw_winners_global_site->entities->update_entry( $entry );
			return $entry_id;
		}

		public function update_product_award( $product_award ) {
			global $bw_winners_global_site;

			$product_award_id = $bw_winners_global_site->entities->update_product_award( $product_award );
			return $product_award_id;
		}

		public function update_brand_award( $brand_award ) {
			global $bw_winners_global_site;

			$brand_award_id = $bw_winners_global_site->entities->update_brand_award( $brand_award );
			return $brand_award_id;
		}


		private function product_categories ( $categories ) {

			// remove the price categories from the array and return the last one
			if ( is_string( $categories ) && ! empty( $categories ) ) {
				$categories = [ $categories ];
			}
			
			if ( ! is_array( $categories ) ) return;

			$price = null;
			for ( $i = 0; $i < count( $categories ); $i++ ) {
				if ( preg_match( '/\$\d/', $categories[$i] ) ) {
					$price = array_splice( $categories, $i, 1 )[0];
					$i--;
				}
			}
			return $price;
		}

		/**
		 * Set a nested array value using a path.
		 *
		 * Example:
		 *   set_array_path( $arr, [ 'entry', 'score' ], 95 );
		 *
		 * @param array $array Target array (passed by reference).
		 * @param array $path  Path segments.
		 * @param mixed $value Value to set.
		 */
		private function set_array_path( array &$array, array $path, $value ): void {

			if ( $value === null ) {
				return;
			}

			$ref = &$array;

			foreach ( $path as $segment ) {
				if ( ! isset( $ref[ $segment ] ) || ! is_array( $ref[ $segment ] ) ) {
					$ref[ $segment ] = [];
				}

				$ref = &$ref[ $segment ];
			}

			$ref = $value;
		}

		/**
		 * Cleanup records that were not updated during this sync run.
		 *
		 * @param string $started_at UTC datetime when the sync started.
		 *
		 * @return void
		 */
		public function cleanup( string $started_at ): void {
			// TODO:
			// - Remove or soft-delete records
			// - WHERE updated_at < $started_at

			global $bw_winners_global_site;

			$bw_winners_global_site->entities->end_sync( $started_at );
		}
	}
}